One problem with having a superior, almost invincible enemy in a series like Star Trek is that sooner or later it reaches the point when the good guys will get the upper hand in the conflict.Pretty much.
They had some moments in VOY (especially pre-Queen as VOY went there without going there) and PIC season 3 but once the baddies' returns stop feeling natural and increasingly contrived... meh.
I was surprised they came back for PIC 3, though they do fix the issues that occurred in the previous two seasons. Maybe they knew there wouldn't be a fourth season so they had to be crammed in. Fairly worth it.
Mostly with some new technology, super weapon or weakness in the invincible enemy itself.
When that happens, the superior invincible enemy will lose everything which made them interesting.
That happened at the end of Best Of Both Worlds and later on when the Borg became humanized and "velourized" by characters like Hugh and The Borg Queen (or Wimp Queen as I call her after First Contact when she was wimping around Picard like a lovesick schoolgirl).
From that on The Borg was just another weird species.
Not to mention that they were rather one-dimensional as a species too, heavily relying on theit technology.
Personally I find the Cardassians and the Founders much more interesting as villains than The Borg. They were not superior when it came to technology and such but they were cunning, treacherous, dangerous and much more interesting.
I expect the Borg to show up on Star Trek: Prodigy because it's going to be screen in the Delta Quadrant the Borg's home space.
Let's hope that The Voth have wiped out The Borg so they don't have to show up in Prodigy.