I'm am truly very surprised that no one has mentioned old goldsrc mods in any great length, because those were the golden years and mods of games.
In no particular order, you should have and be playing all of these:
The Wastes -
[url]www.moddb.com/mods/the-wastes[/URL]
Fallout as an fps (before that abortion of Bethesda's) minus the RPG elements. Nuff said.
The Specialists -
[url]www.moddb.com/mods/the-specialists[/URL]
THE action movie game. It has everything from Tequila's dual 92fs to Castor Troy's golden colts to a recreation of the lobby scene from The Matrix and a fully fleshed out Kung Fu system that makes most fighting games look like casuals.
Brainbread -
[url]www.moddb.com/mods/brainbread[/URL]
You, your friends, and a few dozen guns against the legions of undead. I'm surprised that the devs didn't sue Valve for copyright infringement.
Jaykin Bacon Source -
Not actually on the source engine, but this mod rips from every game and mod you've ever played and does it with flair and panache. Probably the ultimate party game for your friends on goldsrc it has everything from Metal Gear Solid's stealth box (complete with witty commentary) to Castor Troy's Golden Colts, ripped from The Specialists. Unfortunately, the version on moddb is outdated, so you'll have to do some google-fu for this one (version 5.4).
There are also plenty of other awesome mods such as The Battlegrounds, Pirates Vikings and Knights, Hostile Intent, Snow War, Zombie Panic, The Ship, and many, MANY more, but those should be enough to start you off.
While a lot of these mods are pretty dead me and a couple dozen friends play games every couple nights, so if you want to get in on the action or are just having problems installing any of the mods shoot me a message.