I don't know about mods, but it was a good game in my opinion.
The setting in particular was very good, the story was decent (and logical :D), the graphic design was pretty awesomesauce, the "dialogue" text was also decent, although not great, and the quests ranged from awful to great (specifically, the dwarves one).
As for the factions, they were very diverse and should in theory have allowed good roleplaying, however the game was designed so that you would play several of them.
I agree - I don't understand why Morrowind gets such a bad rap.
Art design/setting is fantastic and unique, main quest is decent, has a large amount of quests (varying in quality), it is really open ended, and it has a nice amount of lore in the form or quests and the many books scattered around.
Of course it had it's bad points such as generic NPCs, dull combat, some boring quests, the ugliest face models I've seen, and that you could join all (I think, don't remember 100%) the factions/guilds. But you CAN just restrict yourself to one, don't see why not.
I only used a few mods but I don't even remember them at this point. I used one that replaced all the hideous low texture faces with some really great ones - Rhedd's Heads I believe it was called, and one that replaced the bodies and added nude models (for realism) - Better Bodies. I also used one that extended the view distance and one that added lights to windows at night (kinda stupid Bethesda left out THAT little detail).
As for gameplay changing mods - I tried Adventures for a bit and I recall liking it but can't seem to remember if I finished the main quest with it or not.
All I can say is give it another chance and play it with both expansions. They add more interesting quests to the game along with new scenery and somewhat more "lively" NPCs. Taken together it makes for a very impressive and believable setting with lots to do if you can actually "get into" the lore and the world around you.
But I'm a bit torn as to how I feel about Morrowind. If anything, witih me it won HUGE points for it's immersiveness alone. I think the alien landscape, the music, the muted color pallete, the weather effects, the size of the world, that to me was it's TRULY UNIQUE contribution to RPGdom.
But it fails in the roleplaying department in that nothing you do really affects the world around you and you aren't given many opportunities to make decisions aside from YES or NO. And your Persuasion skill only decides whether an NPC will talk to you more openly or not since you're not given many dialogue options. Still, it was a lot better than Oblivion - maybe it was just the use of a dialogue tree system, maybe not but it certainly felt dumbed down in Oblivion.