big seconding for the Shadowrun series, I personally say skip the first one (Shadowrun Returns) because it's short, muddled, and kind of more of a proof of concept. Shadowrun Dragonfall is my favourite single player video game of all time, and I particularly like it because of the writing. The story is quite neat, I think it does the slow dive into a conspiracy angle quite well. I thought it was really refreshing how your motivation for most of the jobs is just needing the money and doing what people ask, and several of the scenarios contained genuinely difficult moral choices rather than the usual video game fare of "do you want to kick the puppy or save the puppy" and a lot of the time when there's an obvious third option, the game will let you do it but it's gonna be a lot harder to pull off. Shadowrun Hong Kong is imo a bit more user friendly, as they tidied up the interface and added a couple of nice quality of life changes, but I prefer the writing in Dragonfall.

If newer games are okay to recommend, I've been quite enjoying the story of Scarlet Nexus, but in a very different sort of way. Every single story act has several twists in it, and just going along for the ride and seeing what levels it escalates to next has been quite fun. I'm really near the end at the moment, and boy howdy if you'd explained the story to me after I'd just started playing I would not have believed you.

Morrowind has some alright writing, the main quest is good if you really dig deep into the lore and think a lot about the narrative. I can't really recommend getting into it now unfortunately, but I can very much recommend retroactively spending hours of your childhood obsessed with it.

I'll also second Mass Effect. They're pretty dated, and I'm actually replaying them at the moment (skipped 1 because I just cannot deal with it, but the legendary edition apparently makes it more palatable). Mass Effect 3 had a moment in it that genuinely made me cry when I first played and I'm not embarrassed to say so.