The Borg could probably be still scary if:
a) They divorce the Borg Queen from the Borg Collective and stick to solely representatives like Locutus
b) The Borg, if they insist on having a Queen, replace the Krige Queen with a new Queen. Why hasn’t a species that is always finding new ways to adapt and improve itself not figured out to get rid of their queen, considering how many times they have been defeated by the Federation? Matalas in an interview said he wanted to cast Emily Hampshire as the Borg Queen in S2, so maybe use that to re-imagine the Borg Queen
c) The Borg should have adapted cloaking tech by now and should use it more often. Maybe the Federation would develop anti-cloaking sensors (straight out of the Armada games), but at least it would be a logical progression.
There’s lots of possibilities. Vidiians. Species 8472. From the novels, the Kinshaya (who terrorize Klingons!). Even the Lihn Zhee (that unique skull in Picard’s trophy room in the Confederation timeline) could be developed into a serious threat.