Yeah, no one's even mentioned Fall From Heaven yet. That mod is amazing, like an almost entirely new game.
I will second that, Fall From Heaven ( or Fall Further being the latest and bigggest ) is incredible. Warning - it can be incredibly hard too.
Another excelent mod is Legends of Revolution. Also makes the game harder. Basically Civilizations can revolt and spawn new civs. sometimes right on your doorstep. Let me give you an example: Recent game I am still playing in fact as the Japanese. To my west was my inferior but friendly Celtics. They had maybe 3 cities to my 6. Well, they had a revolt and spawned HITLER of all people. More like an abortion gone bad. Well, I believe since I was friendly with the Celtics, Hitler automatically hated my guts. Very soon after the civ spawed Hitler attacks my capital which was left very weak due to my assault of the puny French to the East.....
Long story short, mods make the replay of this game virtually endless.