REF: Fix maybe_promote
- [x] closes #23833
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`

This PR is the culmination of ongoing work since the start of November, and is therefore a bit on the bigger side, with several notes to make.

Things started out with me wanting to unify `.update` for Series/DF (#22358), resp. aiming towards a beefed-up `update`/`combine_first`/`coalesce` (#22812). While tackling the former (#23192), I encountered some problems with `df.update` upcasting stuff unnecessarily (#23606), and while trying to fix it, I ran into problems with `maybe_upcast_putmask` (#23823), which were directly caused by the utterly broken (and completely untested) `maybe_promote` (#23833).

I started with writing some tests (#23982), which turned out to be not so trivial, because there's a lot of complexity, and the correct behaviour wasn't alwasy immediate (also encountered some fun numpy bugs in the process: e.g. numpy/numpy#12525, numpy/numpy#12550)

I set out to write out a PR to fix those tests then, with the obvious goal of getting the test suite to pass - already that required a full rewrite of the method. I cracked my own tests after a while, but the test suite eluded me. As it turns out, `maybe_promote` mixes two very different behaviours - scalar values get cast to the new dtype, whereas arrays return their missing value marker. I tried kludging around this for a while, and decided it wasn't possible without creating a franken-solution.

The next step was to *separate*  these two different behaviours into different functions, `maybe_promote_with_scalar` and `maybe_promote_with_array`, where `maybe_promote` is then just a thin wrapper that switches between the two. Actually also `maybe_promote_with_scalar` is just a fairly thin wrapper around `maybe_promote_with_array`, so that the actual many-cased promotion logic does not have to be implemented twice.

Often, the call-sites in the code just need the one or the other, and this could later be broken up correspondingly.

I updated the tests in #23982 (taking care to fully capture all the xfails there) and based this PR on that. This should give already an overview of what changed. In many cases, the current behaviour is broken, but I did make a few design decisions worth noting:
* `maybe_promote_with_array` consistently returns the missing value marker for the updated dtype. Since integer dtypes (plus bools and bytes) cannot hold `np.nan`, these cases now return `None`.
* all promotion logic is as conservative as possible, also within subtypes. For arrays, promotion always goes by value, and never by dtype. That means that, for example:
```
    >>> maybe_promote(np.dtype('uint8'), fill_value=np.iinfo('uint8').max + 1)
    (dtype('uint16'), 256)
    >>> maybe_promote(np.dtype('uint8'), fill_value=np.array([-1], dtype='int64'))
    (dtype('int16'), None)
```
* all promotion logic is as type-safe as possible, which means that [x] only stays [x] if the `fill_value` is of type [x] as well, where x is one of (datetime, timedelta, bool, bytes). Datetimetz must additionally match the timezone.
* all scalar `fill_values` now truly get cast to the updated dtype (before there were lots of ambiguities around int/float/complex/datetime/timedelta subtypes)
* I have changed the behavior that strings get interpreted for datetimes/timedeltas. Since this is an untested private method, and the test suite still passes just fine, I think this is actually a good thing, because it's too much in one method. String to datetime/timedelta *should* need an explicit cast, IMO.
```
    >>> # master
    >>> maybe_promote(np.dtype('datetime64[ns]'), '2018-01-01')
    (dtype('<M8[ns]'), 1514764800000000000)
    >>> # PR
    >>> maybe_promote(np.dtype('datetime64[ns]'), '2018-01-01')
    (dtype('O'), '2018-01-01')
    >>> # master
    >>> maybe_promote(np.dtype('timedelta64[ns]'), '1 day')
    (dtype('<m8[ns]'), 86400000000000)
    >>> # PR
    >>> maybe_promote(np.dtype('timedelta64[ns]'), '1 day')
    (dtype('O'), '1 day')
```
* `iNaT` is considered a missing value from the POV of `maybe_promote_with_array` in all situations. This takes one single integer out of the usable `int64`-range, but I think this is much cleaner.

There's still a few issues with `lib.infer_dtype` (e.g. #23554, of which I already fixed the complex case #25382), most notably that it cannot infer `datetime64tz` yet. Actually, through this PR, I'm learning how broken that method is *as well*, but fixing that will have to wait for some other time. Among other things, it currently faceplants for `PeriodArray` / `IntervalArray` (#23553). I haven't added tests for these types here, but ~9000 tests is already better than nothing, I hope. ;-)

Another point that could/should be considered is how EAs should deal with this (#24246).