Well... CtP1... without mods wasn't that wonderful game, in my opinion... but was massively improved by WesW's 'Medmod'... which you can find
HERE. Game balance is massively improved (as is, to a lesser degree the AI) Its makes for a wonderful civ game... probably my favorite of that era... due to the best balance of any civ game yet... in my opinion.
CtP2 is a much more versatile game engine. What can be achieved by modders is vastly improved. The AI... again was weaker than most people like, but with mods the AI has become a real serious challenge, and clever programming by modders has added a lot of depth to the game. Apolyton pack, MedMod and Cradle of Civilization are all fantastic mods... check out
this thread for your options.
MrBaggins