Some rpgs? Mass Effect and Witcher are probably the best in a few years, Jade Empire is good as well. If you haven't played Baldur's Gates there's a nice compilation of them available. Arx Fatalis is also good and seriously underrated, it's a 3d first person one from something like 2003. Not a massive open world type neither streamlined console style. Nice, compact and has a great casting system (seriously, play a mage).
Adventure games? Get ScummVM and play the LucasArts classics, get Grim Fandango. Telltale's new Sam & Max games are good, at least the first season, haven't played the second.
Then there's of course tons and tons of indie games, I can confirm that Braid is absolutely fabulous, World of Goo is nice fun and Crayon Physics is great provided you're not the achiever type.
Also seconding System Shock 2 and Thief (the second one is best). Freelancer is pretty great, an arcade space dogfight game (does not need a joystick) with a great story driven single player. Deus Ex if you haven't yet, I guess it's technically an fps but seriously.
I don't know, this is pretty much listing all the great games that come to my mind except fps and strategy. Any more pointers? Have you already played all the classics suggested here?
Alec only Thief III was released on consoles. Get Thief II.