Civ VI's combat AI is certainly incompetent. Having noticed that the barbarians are a larger threat than other civs, my current hunch is it may be an improvement to the game to simply eliminate walls, or at least set their bonuses extremely low, so that at least the AIs might on rare occasions be able to take cities from each other.
The real answer to a Civ or Civ-like AI that can be a challenge is Soren Johnson. He wrote the Civ III AI (while still early in his career), which is fairly predictable once you've played enough, but is still much more effective than the Civ V or VI AI at being able to, y'know, actually capture a city on occasion. It often accomplishes that through raw numerical strength, but as vorlon_mi wrote, that can still be a powerful force, with the "runaway AI" who beat its neighbor, and then that neighbor's neighbor, etc., being a significant hazard to human opponents as well. It won't fight smarter than them, but if it expanded earlier, that can still be a hazard.
Then Soren lead Civ IV, which IMO is the pinnacle of Civ AI. I've played it for years, and while I did wind up dominating in my most recent game, it was still a near-run thing (on Prince difficulty, i.e. even terms) for a while there. Only a couple more units from the Japanese or French, and they would've had an open line towards more core while my main army was off fighting on some other front. And mods can make the Civ IV AI even better; Civ IV is also the most moddable version of Civ (if the modder knows enough Python/C++). If Firaxis decided that Civ VII should be Civ IV, but 64-bit and with some minor updates, I'd say they couldn't have done better.
Most recently, Soren lead Old World, which is a period-focused Civ-like game that is great for emerging storylines, with story events being much more prominent than in the Civ series. Urlance Woolsbane is right; I used to bash 1 UPT endlessly based on my Civ experience (especially Civ V experience), but skeptically tried Old World, and it works completely differently there, and the combat AI is on par with Civ IV. Especially as a new-ish player, I was losing as many units to the AI as it was losing to me, with my development being stunted as a result of focusing on not losing the war to the AI. But even once I had a few games' experience, in certain geographic situations, the AI can make life difficult. And I've yet to try the "Ruthless AI" option.
Outside of Soren Johnson games... space-based, but Galactic Civilizations II is better than Civ V/VI at AI. I think III was supposed to be better yet in theory but am not sure if it, or IV, was in practice, as I tend to prefer history-focused games.
Paradox Development Studio games also tend to be good for the emergent storytelling. Europa Universalis IV is my favorite, and its diplomatic system (with rivals, alliances, royal marriages, and coalitions against expansionist powers) really helps with that storytelling. Playing Bohemia recently, the Ottomans became a real, and very dangerous, rival expanding into Europe, but that only made my friendships with the Teutonic Order and the (heretical, to us) Papal State more important and deep. Playing the Ottomans a few years ago, I had a mystery as to why the Spanish were suddenly crushing my armies left and right, despite being numerically equal... the AI isn't perfect, but it's competent enough to make things interesting.
github.com
