Ok, let's see what we have...
Fantasy:
1. Heroes of Might and Magic II or III. The latest expansion pack for Heroes II was the Price of Loyalty. The latest expansion pack for Heroes III was Shadow of Death. While I personally prefer HOMM III, others feel the series was at its best with HOMM II. Either way, these are some of the most enjoyable and challenging turn-based games I've ever played. Don't bother with the Heroes Chronicles - these were basically cheap stand-alone campaigns which were not well developed.
2. Warlords III, Darklords Rising. The Warlords series is a classic, and Warlords III is very good. I personally felt that Warlords II (a DOS game) had better flavor, but the gameplay in either is excellent. As you have to pay upkeep for every unit every turn, there is a built-in limit on army sizes. Also, gameplay tends to be rock-paper-scissors, where every unit has weaknesses against at least one other unit, encouraging combined-arms tactics.
3. Age of Wonders I and II. The first AOW is one of my all-time favorites. Play is closer to Warlords than HOMM, but with a lot more flavor. AOW I had numerous decision points which could change the direction of the campiagn, allowing for a great deal of replayability. Very challenging AI. These games do lend themselves to the "super-hero" tactic, where one or two powerful heroes are (nearly) unstoppable. I haven't had time to play more than a couple scenarios in AOW II, but they were great fun (and the AI kicked my tail - always a sign of a good game).
4. Fantasy General. An old DOS game - very challenging AI. Lousy graphics (though they were fine when the game was released 10+ years ago). I don't know if you could get it to run on a newer machine, or could stand to look at the graphics, but gameplay was (and still is) excellent. The AI knew which units in my army were most valuable and went after them with a determination I couldn't believe. Very tough to beat.
Sci-Fi:
1. Space Empires IV Gold. I have been playing SE4 constantly for a couple years now, and I'm still not tired of it. The game is relatively easy to modify, which helps keep it fresh. My personal favorite is the DevNull mod, but TDM modpack is also good. There are even Star Trek and Babylon 5 mods available. The AI is not the best, but it's acceptable if you don't have the inclination to play over the net, and you can always give it a bit of a boost to make the game more challenging. It's available only at
www.shrapnelgames.com. Be sure to check out their boards (Intel Forums) - some players have been putting together a comprehensive strategy guide that explains not just basic strategies, but how the game crunches certain numbers, how specific facilities and components work, and so on. The guide's not complete yet, but it's already invaluable.
2. Master of Orion II. Another older game, this one has better graphics than SE4, but no mods, so gameplay tends to follow the same path. It's quite challenging and the AI is not bad at all, so I recommend it, especially if you can find it used for $5 or $10.
3. Warhammer, Chaos Gate (?) It's been years since I played this one, though it's available in the Warhammer Universe pack. It is squad-based, reminding me a great deal of the old Breach 2 game. A lot of fun, even when I blew up my own guys too many times with grenades when not paying attention.
4. X-Com. The original, not the later sequels. You'll need something to slow down gameplay on faster computers (the game code doesn't do this automatically). A great squad-level game where Earth is invaded by aliens. Watch out for the Chrysalis aliens - they implant eggs in your troops that turn them into alien-ally zombies! One of the all-time greats.
5. MissionForce: Cyberstorm. Don't bother with the sequel - only the original game is worth your time. Basically you design and construct giant robots (think mechs), clone pilots for them, then send them out to battle robots guided by some supercomputer somewhere. A lot of fun, especially experimenting with different mech designs. This is an oldie but goodie that (if you can find it) won't cost you more than a few bucks.
Historical:
1. My current favorite is Strategic Command, a grand strategic game of World War II in Europe. It's very high level (armies and corps) with turn lengths which vary depending on the month (more time goes by in winter to reflect that less could be accomplished). The AI is not great, but multiplayer can be very tough on either side. This can only be ordered at
www.battlefront.com. BTW, the bulletin boards have a novice strategy guide posted which is a great help in learning the game, as well as discussing various strategies, such as an Allied attack on a neutral Italy. Best of all, this game is relatively simple and can be played in hours rather than weeks.
2. Panzer General and Panzer General II. Some like the PG3 series of games, but I was turned off by the graphics. The gameplay in the first (DOS) game was simple and challenging. The second game was only a bit more complex, with much better graphics, and still challenging AI. All of these are World War II in Europe, but at the operational level.
I know there are others I'm forgetting, but it's late and my brain is tired. Hope this helps!