And all that is without considering hybrid tech.
ZF-8 apparently removed a lot of the "slushbox" / wet-clutch problems with efficiency. Its apparently the best / peak automatic transmission technology with exceptionally good reviews and specifications, though the main problem seems to be downshifts. But upshifts (1->2->3->4...) are basically solved with ZF-8 and operate at high efficiency.
CVTs are manual-transmission/clutch-like levels of efficiency, though there's so much diversity in CVT implementations I feel like its hard to discuss.
Hybrid Vehicles are either directly-connected / 1-gear systems (ex: Honda: ICE Engine gets a gear and EV engine gets a 2nd gear, no transmission otherwise required), or the Toyota Power-split device (aka: Prius) that has exceptionally good efficiency as you're probably aware.
----------
Manual Transmission's main benefit is economy. Its just a bit of hydraulics controlling a drum-brake like system (aka: the Clutch) with a human controlling the gears, so no need for any complex logic.
I think economy cars / low-cost cars need to go back to manual transmissions. But maybe USA is just too rich and that the $20,000+ price point is high enough to just default to automatic transmissions.
There's a lot of "machine empathy" in manual transmissions, they're exceptionally simple, cheap, and reliable (when used properly).