The Unofficial Patch is not really a mod, but while the default basic patch only fixes bugs, the optional plus enhancements restore a lot of hidden content, including new quests and new maps!
Really made the game playable, though now there are better alternatives. I would recommend the Camarilla edition, which adds much needed replayability value to the base game without tinkering with it too much and destroying the atmosphere. A great balance I would say.
If for some reason you don't like that mod, then the unofficial patch solo is for you.