ENH: (gradually) evolving astropy's config and cache directory discovery system #18964

This is the follow up, uber-issue, to #17934 (and a legacy sequel to #10738).

In that PR, we ended up taking an extremely cautious path to preserve bug-for-bug compatibility with the existing (hereafter, "legacy") behavior. This exceptional level of caution is justified for a piece of software we know is relied on in high-stakes downstream packages such as astroquery and sunpy.

However, and as hopefully made evident with #17934, as well as the backlog of issues related to this piece of software, the legacy behavior is causing a lot of hassle, which I'll try to summarize (tentatively, in an exhaustive fashion) here:

• It's very brittle (env vars just end up being ignored in many cases)
  • in particular, it's not platform portable (windows is explicitly not supported at all)
• Even when not ignored, env vars lead to surprising behavior (the global config/cache directories from $HOME/.astropy are never truly shadowed, but merely linked-to)
• It is extremely difficult to test (and in turn, to maintain)
• It excludes some actually useful features from our design space, like
  • ASTROPY_CACHE_DIR: a more targeted substitute for XDG_CACHE_HOME, without the side effect of affecting every other application supporting XDG env vars (recently re-requested at Let user define cache location #17507 and also mentioned as early as Config woes meta-ticket - add ASTROPY_CONFIG_DIR and ASTROPY_CACHE_DIR #10738)
  • true isolation from global machine state (not supporting this makes tests much harder to write correctly, as is visible in the complexity of pytest fixtures introduced in UX: warn when env variables are ignored in cache and config directory discovery #17934)
  • local (project-level) configuration (not requested yet, but a feature I enjoy using which is supported by many tools)

My conclusion is that, in order to overcome all of these problems, we should attempt to overhaul the entire logic, albeit at a very gradual pace.
The new behavior should, first and foremost, be easy to predict, use, and test, but it should also leave room for at least some of the features I listed above.

I propose the following schedule:

• astropy 7.2 only gets UX: warn when env variables are ignored in cache and config directory discovery #17934: exact same behavior as 7.1, with one key difference that env vars are never ignored silently
• early during 8.0's dev cycle, introduce a much simpler (albeit possibly backward-breaking) fundamental
• if the new design survives in some form until 8.0.0rc1, release it there, but explicitly reserve the possibility to change course
• if no major issue is reported during the pre-release phase, ship the new design as part of 8.0.0 final
• allow for new features (e.g. the ones I listed above) in 8.1