I generally have a few “forever games” that I sink thousands of hours into. Right now that’s Stellaris but in might try to get back into Crusader Kings with the new patch and of course when Civ 7 comes out I’ll be all over that.
I generally prefer my other games to be fairly short, especially for story heavy games. I’ve left hundreds of Civ games unfinished, and it doesn’t really matter, but I do actually want to finish games with a strong narrative, and really long ones can be hard. I never finished either of the Divinity original sin games, for instance, despite enjoying then quite a lot. Same with Witcher 3, though in that case it has more to do with rapidly becoming fatigued with the open world and also starting grad school about 3/4 if the way through and not having much time to play. I’m general though, I’d say about 20-30 hours is ideal for a game that I can’t just replay forever.
I think the genre you are looking for is “immersive sims”. Notable historical examples are Thief, Deus Ex, and System Shock.