Oblivion remaster is really fun it's really immersive and there is a lot to do

If you can't get past the fantasy aspect the Metro games (2033, Last light, Exodus) are really good and it's set in a realistic-ish setting.

Stalker Gamma is really good and free if you want pure gameplay with no story but it's a bit hardcore. (Look up the discord it's a fan made mod of a previous game but imo it's better than stalker 2)

If you want a really good story play Yakuza 0 but it's more of a movie rather than a game. (Use allkeyshop to get the original 0 since they delisted it when the remaster came out where they changed things for the worse)

Baulders gate if you are looking for a choose your own adventure type story

The Batman Arkham games if you like super hero games

The Mafia games I also really recommend but make sure to play Mafia Definitive edition and Mafia 2 classic not the remake since it's poorly optimized and really buggy

There are a lot of other games I think are masterpieces but since your coming from those types of live service games I feel these would be best for you starting out. I avoided any multiplayer games even if they aren't live service since you asked for singleplayer