There are more mods that tweak the original game than anything else, but comparatively few of them are of the same scope as the megamods.
The only reason not to buy this off Steam is that you hate Steam and love GoG.
Okay, there are some more esoteric reasons, too.
There are many mods that largely go outside the UFO theme. The most prominent example is Piratez, which is a... piratepunk?... total conversion loosely connected to the original. There is a Halo mod, a War of the Worlds mod, a Dune mod, an Apocalypse conversion (and OpenApoc, but that's a different story), a magical invasion mod instead of ETs, etc. And more tweaking of the OG and TFTD than you can shake a mouse at.
FMP and its OXCE version are possibly the closest to a relatively faithful expansion of the original. It came to be as a community mod pack of the best mods of the time (and that time was quite a while ago), and has been making tiny steps towards being X-Com Files lite after Solarius took over. (XCF is a sort of spiritual successor to FMP.) These steps stop quite a distance away from being a version of XCF, of course.
Probably the biggest modern reimagining of UFO is Reaver's
Harmony mod. It focuses more on variety of tactical/strategic choices rather than content addons like FMP. There are a lot more, in various states of completion.
X-Com Files itself is the closest thing to the original that makes full use of OXC(E)'s modding capabilities and adds tons of content. But do beware that the mod itself has been thematically shifting away from a tale of alien invasion into something different. You'll be mainly fighting an array of shadowy Earth organisations, from gopniks on alien steroids to Dagon cultists to world-spanning conspiracies. Heck, you'll be
working for one of those yourself. Aliens are a lot more scarce and there are several factions of those, including a toned-down version of TFTD, an alternate take on Apocalypse and several Hollow Earth types. And I hope you enjoy shooting cryptids in the snout, because you'll be doing that
a lot in the early game.
As to TFTD, right now the
World of Terrifying Silence seems to be the most polished and popular TFTD reimagining. There are two more of roughly the same scale, but not the polish, and a smattering of smaller ones.
And of you are worried about the game being hard, stay away from Brutal AI (a tweaked version of OXCE).
TLDR: I recommend trying out Reaver's, FMP and XCF for a few in-game months first and see which you like best. They all have different strenghts and what works for one player might not work for another. There are others, but less complete and/or feature-rich, like Awesome Guns, the UN mods, Reaver's other mod, XenoOps and more.