Can anyone make any suggestions on good mods that are cohesive (not full of random, half completed ideas} and where the AI is able to function and so there is meaningful challenge.
well I know K-mod, for what I remember the main difference between that one and advciv is that the later makes new balance changes. You could try that if you just want the basics or maybe CIV4 reimagined which is basically the same premise as advciv in the sense they're built upon K-mod. I've had fun with those two I mention buuuut I haven't played them in ages. Alternatively, if you like scenarios there's Sword of Islam, very enjoyable for me and managed to be challenging. It's very pleasant to play considering it's a finely crafted piece, really, that's a good example of what I call cohesive. Along it you can put any other mods based on RFC though I haven't played many, and some I did get to play were either left in progress or continue on it.
There is a mod I can speak well of considering it's all I've been playing recently, Realism Invictus (yes I know It's been mentioned a lot but I can't let the opportunity pass

), AI there has never been mindblowingly smart for me (not like it's meant to be anyway) but in my last game it's been giving me a handful of surprises. Maybe I just suck in high difficulties but the AI over here seems to be learning how to pull an assault. In my last game the damn thing split it's doomstack and positioned itself in various corners of the map literally ambushing my units that were on the way to defend the road between two of my cities, it can also be a pain in the ass when it's about naval combat. Yeah I'm definitely bad at this but hey give it a try, maybe you will be surprised like me.
Don't think I've mentioned anything that you already don't know... so, have you played
advciv+?

It's the same advciv but with more content, still a WIP, in fact probably forever a WIP, but maybe you'll find it enjoyable.
(What I would give for a proper remake of the original, but this mod is pretty good but it does fall apart towards the end game)
Amazing mod based on a legendary game, not a proper remake certainly but it's good enough for me to enjoy it.