Hey globosud! The mod that springs to mind right away is the
Better BAT AI Mod, by Lemon Merchant. Its whole goal is making the AI better, and it builds on a previous mod that also sought to make the AI better. I'm not sure if it's still the state of the art, as I'm mostly in the Civ III area these days, but it's a vast improvement on the unmodded AI.
You are right though, some mods make lots of changes, but not to the AI, and the AI falls flat. I have been disappointed by this on occasion as well.
As for the "why", the gist of it is that writing a competent AI is difficult. Just look at Civilization V and Civilization VI, both of which were, IMO, major regressions from III and IV in terms of the AI's competency, especially in any sort of combat (and IMO, V is the worst of the bunch, although I didn't play VI at launch so perhaps it was worse then). Firaxis changed the rules, and couldn't build an AI that was competent when following those rules.
One can create AI rules for it to follow, but if they are too strict, the AI becomes too predictable and thus exploitable. One example is that if you force the AI to follow certain tech paths, it becomes possible for humans to develop strategies to counter that, and I see that on the Civilization III forums. For example, the Civ III AI highly values government techs, so if you research them first, you can trade them for insane profits. Civ III does force the AI to defend its cities, but that's also exploitable - you can rely on the AI keeping two units in almost every city (more in highly valuable ones), and because of that, a human knows they can have significant numerical advantages even if on paper they are equal, if the human sends more of their troops to the front lines and leaves their interior cities lightly defended or undefended.
And the AI is facing humans who are very clever. It's not possible to teach the AI everything that the human brain can figure out, especially a human brain that has played many games of Civilization. It took until 1997 for a computer to beat the world's top chess player, and Civ is a lot more complex, with a lot more tiles on the board, than chess.
Finally, while I'm sure some tweaks can be made through Python, my understanding is that most AI mods modify the C++ DLLs, which requires a high level of technical expertise. A lot of people have enough technical know-how to create mods and add content via XML, but a much smaller number are good enough at C++ to create functional, non-crashing, bug-free C++ AI modifications.
I'm a software developer myself (though I haven't written C++ in over a decade at this point), and while I'm not an expert in strategy game AI systems, I've written enough code in adjacent domains to have some appreciation for the complexity of it.
If you're up for trying a different game that has pretty darn good AI, I'd recommend Soren Johnson's
Old World. Soren was the AI programmer for Civ IV, which is widely regarded as the best non-modded AI in the series, and Old World's AI is good enough to keep you on your toes. Could it be better? Well, yes, and every patch they make a few changes to make it a little bit better. But it's a refreshing approach compared to more recent Civilization games.