Drake is a very interesting character, with a lot of potential yet to be realized. He tows that thin line between Pirate and Marine, and is at the center of major politic shifts in the world. His double agent stint means he has to stay on his toes more than most characters, as both pirates and officials will be wary of him.
Losing to CP0 isn't really an issue, it was the manner in which he did. Wano is a large arc, so it's natural Oda would choose certain things to prioritize. The impression I got from his defeat was that Oda needed to clear the board, and that meant sweeping Drake under the rug, as if it was a loose end that needed to be tied up, rather than a major plotline on its own. I hope I am wrong on this, and Oda still has real plans for Drake and SWORD going forward. The fact he was a double agent going back 2 years ago means this Government/Marine feud was happening long before Sakazuki became Fleet Admiral, and it can blow up into something bigger.
He really is a rather interesting character and like you said, as a double agent, he has to doubly careful since he's playing two roles, not just one. He's got enemies on all sides not just during Wano, but the moment he accepted the mission to go rogue, so this isn't something he doesn't already have experience with--it's just much more intense because of just how concentrated it is here.
He also has a lot of good things going for him as well with one of the biggest things is having a tie to Luffy already (that they still don't know about) ontop of actually becoming Luffy's ally and well, his allies don't die unless they actually have to for Luffy's sake.
You're right though. The way that Drake loss was disappointing to several; as the 1% man, this was always coming of course. However, strangely, Usopp's speech and CP0's own hype of Drake makes me think that this isn't the end for him just yet.
As Izo said, if you believe Luffy can win, you need to live to see it. And Drake wouldn't be the first Marine to see the Strawhats pull off a miracle! So why not Drake too.
Also, CPO's words about that being about "as far as humans can go" is interesting. I wonder if that is hinting at anything...
With the reveal of SWORD, I still think we are going to dive deeper into it--but it's just a matter of WHEN. So, I do hope you're wrong too, haha!! 😄