Most of my recommendations are story driven hacks, original in their way, like a brand new game.
...alright, the absolute must recommend best hack ever play asap would be Pokemon Unbound but someone already said it so i'll just write the list of all hacks that I've played on my life below here so enjoy this big wall of text.
For GBA:
Try wind1158's hacks (Resolute, Victory Fire, Mega Power, Nameless)
or Vytron's (Saiph, Saiph 2, Sors)
destinedjagold's Ruby Destiny series are my personal favorite, one of the best stories I've seen on pokemon romhacking, especially Roby Destiny 3 - Life of Guardian
another of my favorite storyline hacks is Pokemon Dark Realm, if you like Digimon this is a good one
Sword & Shield Demake got it's last update some weeks ago too.
there are Touhoumon hacks too if you're into that (Another World/World Link)
or Moemon if you like anime-ish moe girls
psst, do you like shitposting or are you a 4channer? Pokemon Clover is the way to go for you, goated hack, but I heard that this one will be getting an update in some weeks for now on, so better wait.
Some old popular hacks like Pokemon Light Platinum, Glazed, Flora Sky, Adventure Red, Dark Rising, Cloud White, Gaia, Ultra Shiny Gold Sigma, Ash Gray, Team Rocket Edition, etc.
Some old gems like Pokemon Aquamarine, Dreams, Sienna, Dark Cry - Legend of Giratina.
some really popular hacks that are closer to the original games are Pokemon The Last Fire Red, Hyper Emerald, Quetzal.
there is Poekmon Weird Type Fun that changes all types in the game while being a vanilla experience, pretty suitable for veterans, you feel like learning pokemon type matchups again.
For NDS:
You can't go wrong with Drayano's hacks, they're QoL and improvments for the mainline DS games (Renegade Platinum, Sacred Gold, Storm SIlver, Blaze Black, Volt White)
And sauceyaTTa's hacks, this guy is amazing, he made the first decomp hack for the DS and he's still making more, his hacks are 'Garbage Gold' and' Mythic Silver'. There is also a new one being developed called Pokemon Iridium that will be getting a demo soon™.
Pokemon Conquest Ultimate is great if you like this spinoff and gen 9 pokes.
psst, do you like Mystery Dungeon Explorers of Sky? do you like funny dialogues with dark humor and tons of shit? then Explorers of Hell is the way for you.
if you don't like the dark humor part then pick the Explorers of Heaven version and enjoy a wide range of gen 5+ pokemon and QoL changes for Mystery Dungeon, like picking a Hisuian Zorua, Deino etc.
for 3DS:
there aren't many hacks out there expect for difficulty hacks with expanded pokedex like diovento's Penumbra Moon and Rutile Ruby
or Pokemon Eternal X and Wilting Y.
If you never played Pokemon Fangames, consider giving it a go, they often deliever a superior quality to romhacks.
I could recommend you Pokemon Reborn and Rejuvenation as fresh starts, they're the best at the moment imo. 100h+ story driven hacks with a amazing world building and characters.
Other really great fangames are Desolation, Insurgence, Uranium, Xenoverse, Pathways, Empire, Empyrean and Pokemon Myth.
----------------------------------------------------------
Pokecommunity and projectpokemon.org are usually the places where these hacks are posted oficially.
Others are posted often on discord servers only (like Explorers of Hell) but iirc there is a dedicated place for Mystery Dungeon Hacks somewhere (like skytemple website) but I don't know if they have this hack specifically.
There are usually websites dedicated for pokemon hacks like 'pokeharbor' so you can try your luck there, cdromance/RGT was never really been a place to pokemon hacks and I would rather keep it that way, don't get me wrong, I would like to see more hacks here but nintendo often target these websites, doing so would raise unwanted awareness from big N.
I think I'm done. Peace. Have fun.