API: Infer dtype scalar
This fixes a number of inconsistencies and API issues w.r.t. dtype conversions. This is a reprise of #14145. This removes some code from the core structures & pushes it to internals, where the primitives are made more consistent. This should all us to be a bit more consistent for pandas2 type things.

closes #16402
supersedes #14145
closes #14001
