The most exciting, difficult, and challenging game I ever played was
AI War: Fleet Command and there's no contest. Hands down best AI I've ever played against. I recommend the original, not the sequel, (which is visually too busy for my eyes.)
Aside from the excellent AI scripting. There's a few things that make it truly difficult without being a cheat.
Autistic variety: There's about 300 unit types but only ~50 are selected for each playthrough. Both for you and the opponent. So both sides deploy a 'work with what you got' strategy that you can't repeat between sessions. You'll have to use your brain because your advantages change with each playthrough.
Escalation: Your opponent has better things to do with its time and ignores you at first; but every action you take increases its perception of you as a threat and it will devote more of its
infinite power to your destruction. You have to weigh the pros and cons of each asset you capture. You will never, at any point, be more powerful than the opponent.
Two Foes: On top of this there are two AIs, with 46 personalities, which strongly alters their behaviour.
I only beat this once, on 7.5 & 8 difficulties, though that's partly because it's a timesink. I captured some enemy technologies which raised the threat level too aggressively. Was able to destroy some data centers to lower that threat and put together a force to destroy one AI's core. That sent the other one into a fury that began collapsing everything I had. So I cobbled together a fleet of shield ships, nested an enormous nuclear warhead under their umbrella, and blitzed it to the second AI's core planet.