Last console generation I was into was the PS2 and a lot of my favorite games from that system have already been listed but if you ever feel like dipping your toe into classic PC gaming I've got a few. You can pick most of em up of GoG for either 5 or 10 bucks including their expansion packs, and they're tweaked to work on modern systems. Best part is, you don't need a 'gaming rig' for any of them. Trust me, if your computer is less than 5 or 6 years old you'll be able to blaze on all of these, and as long as you're not rocking a 486 you'll be able to play 90% of them.
Neverwinter Nights 1, Baulders Gate 1&2. The three of these were the pinnacle of western RPG design at the time. Huge stories, awesome characters, a real sense of progression, ect.
System Shock 2. This was just released on GoG today. Where to start... This game has it all. FPSing, RPG leveling, Suspense, Horror, an absolutely fantastic storyline, and an evil female AI that makes GLADOS look like a wet nurse. http://www.youtube.com/watch?v=5iZMD_eCpEo
Heroes of Might and Magic III. The best of the heroes series. Build up your castles, create armies of fantastic creatures, attack enemy towns.
Alpha Centauri It's Civilization, in Spaaaace! This is one of Sid's best 4x games.
Master of Magic. There have been a ton of attempts to recreate this (hell even Heroes of Might and Magic tries to a certain extent) but no one has pulled it off.
Freespace. You're a man with a space ship and a desire to make money. Go!
Master of Orion II. The greatest 4X space strategy game ever made.
X-Com UFO Defense. (only available on Steam) Another one of those games that has had a lot of people try to emulate it, but everyone falls short. Even the 2012 remake/reimagine/rewhatever by Firaxis couldn't quite pull it off (for me at least). Basically, the world is your sandbox, and aliens are attacking. You need to stop that.
Last up, a few indy games that deserve some love. Some of these are still in development though they all have a 'pay to beta' plan if you're interested.
Armageddon Empires. Warhammer 40K, meets Magic the Gathering, meets Panzer Generals, meets Cthulhu and the Terminator... Yeah, it's basically a card driven hex based wargame where you build custom decks of units, heroes, and facilities. Then you duke it out with 3 AI armies over a post apocalyptic wasteland.
Eador Genisis. Tough to describe. It's got features from a lot of the turn based strategy games I mentioned above kinda rolled into one.
Xenonauts. The best attempt at recreating X-Com I've come across yet. It's still in Alpha (though beta should be out soon).
Starfarer. A very complex action strategy game where you manage a fleet of starships, all the while piloting your own. It's pretty awesome, but it takes some getting used to.
Space Rangers 2. Not really an indy, but still pretty obscure. It's a Russian game in the tradition of Elite or Freespace where you pilot a ship around the galaxy trading, fighting pirates, doing missions for planets and fighting off a huge invasion of artificial intelligences.
