I don't want it to wake up in the middle of the night for no reason.
What Windows have been doing the last couple years is they moved from regular sleep to some poorly implemented standby mode that works more like a phone does where it still runs just very power efficiently and still does stuff in the background. Macs have been doing that for a long time except they actually did it right so it doesn't suck.
Linux doesn't support it yet so you'll get classic stop the world sleep anyway, but either way it'll always be customizable even when connected sleep gets implemented.
Yes there is, "Modern Standby"/S0ix/S2idle.
The CPU is still in a sleep state but less deep than regular S3 suspend, and it can wake up much frequently and silently to do stuff like check emails or whatever.
See "Suspend to idle" : https://wiki.archlinux.org/title/Power_management/Suspend_and_hibernate