It's kinda like going to McDonald's for a salad, really I would just surf the Steam store and search with Google and YouTube. I'd still be happy to provide an ass ton of examples though. I'm mostly just going to try to skim through the titles rather than go into too much detail as I know nobody wants to be bored to death reading a big wall of text.
Witcher 3 has some bomb ass story with it's gameplay, with each quest really feeling like it's own adventure. (The DLC for the game is worth it.) Disco Elysium is ground-breaking if you don't mind doing a lot of reading. There's a bit of a rng mechanic but I wouldn't save scum. as it's part of the game. I would go in as blind as possible. If you want incredible stories I would put up with the few flaws it has. Skyrim has some good (although linear) quests and has extensive lore. Choice of games are okay for choose your own adventure.
The Zero escape games are half escape room half story, which has a couple good twists and turns. Another mainstream visual novel game is Danganronpa, probably one of the best in the killing game genre. The last VN I'd recommend is is Steins Gate. Again if you haven't played these go in blind. I've glanced Sunless Sea, check it out and see if that is your thing. Steve Jackson's Sorcery! is like choose your own adventure++. It really shines in weirdness and its setting. I've only played the first part even though there are others but I'd say check it out on youtube, you'll know whether you want to buy it then. Lobotomy Corporation doesn't really have a big overarching plot (mostly) but what's interesting is the supernatual entities you house and learn about. If you like to read about characters, or monsters in this case, check it out. Anyone knows that if you like anything to do with SCP you should at least look at it.
My last general recommendation for games with good stories is Stories: Path of Destinies. (Yeah the title really gives it away) It basically plays out like a fairy tale with animal characters. While it's technically not a choose your own adventure the choices you make in the game are important. You go through a specific choices which leads to one of many endings, only for your MC fox robin hood with an eye-patch to end up back at the start of the story book. You use knowledge from previous paths and retain the strength you had from the other routes.
(I lied, last recommendation is any Tell Tale game, specifically The Wolf Among Us for a good one.)
Sorry if that was way too long but hopefully you found at least one new thing you liked, even though most of my suggestions are super mainstream. Maybe check out the last one if you know all the others. I can give more suggestions if you'd like, though they may not be as story focused or may be niche. I'd just look one up that interests you or I can tell you more about one if you are worried about spoilers.