Around the turn of the millennium. Games were designed for offline use and had way more immersive campaigns, were shipped by and large ready and bug-free, and so were add-on campaigns.
And since graphics were not as refined as they are now, additional efforts were placed on gameplay.
My top list (by release year):
Diablo II (1996)
Dungeon Keeper (1997)
Half-Life (1998)
Thief: The Dark Project (1998)
Thief 2 (1999)
Dungeon Keeper 2 (1999)
Heroes of Might & Magic 3 (1999)
Gothic II (2002)
Never had a console and don’t get along with controllers whatsoever, so those are all referring to the PC versions.