The best Trek enemies are the Romulans and Cardassians because they reflect the darkest impulses of humanity. DS9 killing off their respective overpowered secret services added to this IMHO.
The Borg are just space zombies that work best when used very sparingly.
The first season of TNG was pretty bad, but one great episode was "Conspiracy".
That's the one with the alien bugs that infest people and wear them like a skin suit and are plotting to take over Starfleet. It's not a very original idea (Invasion of the Bodysnatchers was already an ancient trope by 198

but it's executed well, with an unusual amount of body horror for Star Trek.
It has a perfectly downbeat ending too. The conspiracy is stopped for now, but the brain bugs get out a signal to their bug buddies, and the show all but ends with "To Be Continued".
So of course we never see them again.
Which is a shame, because unlike the Borg, the Cardies, or the Romulans, who were all pretty much WYSIWYG bad guys, the brain bugs were a creepy, insidious threat that allowed the show to explore paranoia. Probably wouldn't have made a good villain of the week because they look like dung beetles, but the writers could've done something interesting with them trying to complete their plan and get revenge on Picard.
Plus, the part where Remmick was all like "we seek peaceful coexistence" and Picard and Riker are like "nope", and phaser the fuck out of him until his head explodes was genuinely badass.