I just recently discovered that NWN has plentitude of user created mods/modules. And am in process of downloading top dogs from here:

http://nwvault.ign.com/fms/TopRated.php?content=modules

Shadowlords and Dreamcatcher series, Paladin Trilogy and a few others.

But I'm noticing that most top rated games have few votes and some of the most downloaded ones have mediocre ratings. So I hope you guys can recommend me a few mods.

And yeah anything goes, heavy RP, hack and slash, overpowered stuff, good story... Just so I can stop playing this damn life devourer WoW :)

Try the kingmaker mod pack. It's ten dollars at any video game store.

I never finished it, but Crimson Tides of Tethyr had a very fresh feel to it (more dramatic).