http://www.poke646.com/ gets my recommandation as best SP mod. It has been downloaded 150k times. It contains an original soundtrack. It contains 20 megs of textures which are so good that they have been bought by a professional game company later on.
Best MP mod is clearly
www.sow.moddb.com 