The biggest mistake youll make playing CtP2 is expecting it to be civ or expect to get some kind of magical feeling you got when first playing civ1. Its a different franchise.
With the unmodded I had few problems with the game, maybe I didnt like the future eras (just pointless copies of previous era with different names for stuff) and the AI being awful, which is why I started playing MP.
The mods had better AI but to me they were too different from the original or were too heavy in techs/builds/units, but with not enough balance or simple gameplay, none of them exactly fitted what I wanted, something like civ2, well balanced with lots of different routes to take, simple but difficult choices, but without all the insanely tedious micromanagement of civ2. I even got quite far into making a civ2 mod, but didnt finish the AI.
Maybe the heavy historical detail of the main mods was lost on me and thats why I didnt really get into them, but maybe youll find it different.