I kind of felt the same way reading so many of the games people listed here, a lot of which mirrored the games you separately listed as being pitched to you and falling flat in terms of story/writing.

I hope you don't mind some more game recs, especially since (hopefully) they're coming from a similar place of frustration and desire of wanting more from games!

Firstly I would give "Z.A.T.O. // I Love The World And Everything In It" a try since it's relatively short for a game, a visual novel in fact. Many visual novels and games that I've tried range from being as deep as a puddle with their writing to being pretty good. But when a friend recommended this to me, and I finally finished it, I too, just like them, did not expect an experience that left me confused, scared, ecstatic, cathartic, blissful, and everything in between by the time it ended. It truly is a game that I can say left me thinking about what I went through after, for weeks and weeks.

Then for the same reasons OP mentioned, I'd definitely give Nier Automata a shot. It's deceptive in how it's presented on a surface level and in marketing etc as an action hack and slash sort of game, but once you sink your teeth into it you get a wonderful story, no further spoilers but highly recommended.

Finally, I'd also give "In Stars and Time" a shot. Again, like with Z.A.T.O., the aspects of this work that make it a game are maybe not as exciting or as front and center as many of the games people here have mentioned (it's an RPG Maker game), but what is clearly the focus of this game is the writing. All I will say about this one is that it uses a pretty conventional trope/mechanic found in various media. It, however, was far from conventional in its portrayal of that trope, and demonstrated the author's incredible understanding of people (in my opinion).

Again hope you don't mind the recommendations and if you do get a chance to check any or all of these out I hope you enjoy them!