About KC: D, I agree with you, but that’s a problem with most RPGs, you are either a “cozy” game, or there’s doom and gloom going on.
I kind of disagree, I think it's definitely possible to write a mainline story in a way that it allows for downtime for exploration and/or side quests. Cyberpunk 2077's quests often have breaks where you need to wait for an NPC to contact you or you are required to meet them at a time and place that allows for some flexibility. Even just in terms of the way dialogue is written, you can have a quest assigner ask you to meet them in a certain spot when you're ready with a warning that you shouldn't take too long, rather than just instantly progressing the quest. I think Kingdom Come: Deliverance actually did this at some points, where characters would meet you at a different location if you took too long to join them for the journey there. There are ways around it through clever writing, though obviously most RPG's will also have urgent moments where it doesn't make sense to wander off.
Being able to play co-op and the single player storyline at the same time is so cool. It's something I always wanted in the series; the ability to enjoy everything with friends without losing any of the single player features. I played Scarlet/Violet with my girlfriend at launch and it was a very fun way to enjoy the game with her whilst also being able to play at my own pace. But unfortunately the visuals really annoyed her and she lost interest. It's just not that fun exploring a world as barren and ugly as the one in Scarlet/Violet.