Disclaimer: I haven't played any game seriously since Fallout 3
circa 2008. Before that I was a heavy gamer starting in 1993 with a Gameboy and
the same Super Mario game you mention, and went through a variety of consoles
and games (SNES, PlayStation, PS2, Gameboy advanced, Xbox360, PSP). I always
considered myself more of an RPG gamer than any other genre, despite having
spent a lot of time on many different arrays of games.
The curse of old "retired" gamers
There is a big disease in the gaming industry. Gamers that started in the 90s
(before Delta Force was cool, man I miss this game) would agree with what I'm
about to say. The industry is showing a complete lack of capacity to renew
itself.
Of all the game you mention, platform, FPS, real time strategy, car racing, very
little innovation has happened. The graphics look definitely better now, and the
games feel more realistic, but I doubt the fun factor has gone up.
The only radical revolution (and by radical I mean fundamentally changing the
way we see video games compared to when I started gaming) is massively online
games. This was definitely not around before, at best multiplayer games
consisted of going with your friend to the neighborhood's "network" or playing a
two controller game on the console.
Also Android and iOS are adding a great mobility dimension to gaming, but one
can easily argue that this has been around all along (what's the name of that
orange console where Donkey Kong threw barrels at us and you had to jump over?
EDIT:
Found it.)
Explore new boundaries
Simply put, I would advice against looking for a renewed fun of familiar gaming
experience. An FPS will never feel as good as HL/Delta Force (can I assume you
also played Outlaws?) did 10-15 years ago. The nostalgia will take over and
you'll quickly find yourself cursing at those "damn kids" with no "gaming
spirit". Or something like that.
On the other hand, I would suggest you look at new gaming experiences instead of
trying to get beack memories. Here are, to the extent of my knowledge, the
things you might find intersting but haven't really mentionned in your original
post:
RPG
Classics to check at all cost: Final Fantasy series, Fallout series, Elder
Scroll series. If you like retro gaming, a special mention goes to Chrono
Trigger, the game is absolutely marvelous.
Obiviously, I start with this. RPG's did not start out as video games, most of
them were played with a pen and paper and a number of dice. The concept is still
the same, the most important thing is that you'll enjoy it if you enjoy
pretending you're the character. After all it's a
Role Playing Game.
The fun value comes from the storyline as much as the (often brilliantly
twisted) fighting system. For the record I'm not talking about MMOs. Avoid
these, it'll hurt your life more than anything. If you like to explore vast
universes, enjoy non linear storylines, and you're the kind of guy who finds it
a challenge to get a "100%" completion score that takes over 200h of gameplay,
than this is for you.
RPG have the advantage of not including competition against other players, so
other gamers will generally be very helpful to share tips and reveal
secrets. They (well the good ones at least) have great replay value so you can
relive your story many times.
Bottom line RPGs are much more of a story where you're the protagonist than it
is a game where you beat your opponent.
One huge downsides of the genre: you're not 15 anymore. Do you really have the
time to put into a game that only becomes fascinating after several dozens of
hours gameplay?
Survival horror
Classics to check at all costs: Resident Evil series, Silent Hill
series. Special mention to Clock Towers series.
The genre is not to be confused with FPS. Honestly. In these games, you are
constantly low on ammo, low on health, low on items, ... When you hear a villain
around, you run. You just shut up and run. In this aspect the game reminds much
more of Pacman than anything else.
The first RE game is particularly challenging, to this day it may be one of the
most difficult games I've ever played. RE4 on the other hand, was easier, was
getting away from the guidelines of the genre, but the gameplay was still fun
(the story sucked balls though).
All in all, these games will require patience and cleverness. If you're the kind
of trigger happy guy who wants to bazooka zombies back to Oblivion, don't play
these games. Or get the cheat codes.
Puzzle Games
Classics to check at all costs: Monkey Island series, Myst series. Special
mention goes to Hitman series (yes it's a puzzle).
Go play Myst. Right now. You'll feel smarter once you're done. Actually you'll
feel wiser. Monkey Island are also great hours of fun.
Those kind of games are particularly difficult to play, and will require bending
your mind in such ways, you have to be seriously twisted to solve some of the
Monkey Island puzzles it provides (if I recall correctly you wanted to learn
Haskell right?)
Special mentions
These games fall under no particular category, but I want to mention because of
the innovations they put forward. They're definitely worth checking out:
Shadow of the Colossus. Mostly puzzles. Warning: you'll feel lonely. In a good
way.
Shadows of memory. Dare I say, investigation game? You're solving crimes by
going back in time. Awesome concept.
Little Planet: A little more modern (it's 4 years old nonetheless). It explores
new horizons. And is very fun.
Conclusion
I like single person games, clearly. Don't know if that's what you were looking
for. And yeah I'm most mentionning classics, because, well you've got to know
your classics first ;)
The point is, think in terms of genre, more than "what game to play". An FPS is
an FPS. And you stopped playing those for a reason (whatever it is). Be
adventurous, guaranteed to pay off :)