I like to play as a mage, possibly a vampire. which mods do you recommend for my experience?
also, I am not object to anything that enhances the MW main quests, especially those related to the 6th house (but also Tribunal and Bloodmoon ideas would be nice).

Main Quest Enhancers is definitelly good choice.
Weapon rotate if you want to show your weapon collection.
The same goes for books with Book rotate.
DX 9 guards adds some atmosphere
Protective guards add a bit of realism(and is compatible with DX 9 guards).

I'm sure others will put more ideas.

Do you have Telvanni leanings? If so, seek out Uvirith's Legacy, Building Up Uvirith's Legacy, and Rise of House Telvanni. Add LGNPC Tel Uvirith if you want your retainers to have some personality (plus it adds several quests).
Mage Guild loyalist? Magical Missions will add quests to the Caldera guild.
Looking for more in the way of spells? Scripted Spells and Elemental Magicka II add plenty of spells to play around with. Additionally, BTB spells will rebalance the vanilla spells, plus several spells that weren't accessable before will be for sale.
Pesky unarmored bug getting you down? The Morrowind Code Patch has a fix (and you really should be using it anyway). Additionally, BTB Settings makes unarmored more effective, so running around everywhere naked becomes more than just a possibility.

I don't play vampire characters, but I've heard some good things about Vampire Embrace.

http://www.gamesas.com/topic/1379647-tel-uvirith-telvanni-stronghold-mods/page__view__findpost__p__20903759 is a nice list of mods for Telvanni.