I'd recommend you start playing on random maps using the More Naval AI modmod and spend a lot of time reading the Civilopedia rather than trying to familiarize yourself by playing through the scenarios first. A few of the scenarios are great, but most are less fun than a normal game.
The original "FfH1" was a project that Kael undertook in order to teach himself how to program in Python, C++, and XML. I have never found a link much less played it, but have heard that it still included vanilla civ leaders like Ghandi mixed in with some of the FfH leaders we have since come to know and love. It was never intended to be a finished project for widespread release, but was more a proof of concept that Kael used in order to help attract other talent to join his team and make FfH2 the game he always wanted.
There are many different versions of FfH2 from different stages of development. Some releases added new features that the next version would take away. The different versions of FfH2 were decided into 4 development phases called Light, Fire, Shadow, and Ice. I believe I first found FfH2 when it was in version 0.14, but soon upgraded to version 0.16. Those were both part of the "Light" stage, when basic mechanics that apply to all civs were still being toyed around with. The game really got good during the Fire stage. The most expansive feature rich versions were those that came shortly before the move from vanilla CivIV to BtS. (Although I believe there was one final vanilla Civ IV version released after the BtS version was under development, which was basically the more streamlined BtS mod minus a few new things that BtS enabled.) I still miss some of those features, but the code that implemented them was often rather ugly and inefficient. The first few BtS releases were still part of Fire, but the team would soon progress to Shadow. At the end of Shadow the game was essentially in its current form, as all its mechanics had to be firm before the Ice phase could focus on making scenarios.
While Kael and the team were working on Fire, he was approached by Fireaxis about developing an official scenario to be included in the BtS release. I think there was some talk about included FfH2 itself, but that was soon deemed too expansive and complex for most new players. Firaxis also required complete secrecy about this project, would would not work well with how Kael had been interacting with the fan community to continuously improve FfH2. Kael suggested that the scenario included in BtS instead be a simpler scenario that serves as a prequel to the Age of Rebirth story line of FfH2. Thus, Fall from Heaven: Age of Ice was born.
(I'm personally not a fan of AoI, as I am more of a builder and don't like the style of play needed to win such a scenario. It has some good easter eggs and answered some questions about lore that Kael had kept intentionally obscure previously, but its simpler word was never rich enough to hold my interest.)
The FfH2 team's cooperation with Fireaxis came with both advantages and disadvantages. It let Kael interact with the professionals behind the game engine and convince them to expose a lot more code to python to help modders, and also introduce switches that modders could use to speed up their games by deactivating python checks that did not need. It also however meant that the team could not add any new members who had not already signed a non-disclosure agreement with Firaxis, which is why several modmoders (including Xienwolf, Tholal, and myself) never got an offer to join the team.
The success of FfH2 eventually led to Kael getting a job offer as a vice president of a rival game developer, which left him with no time to continue FfH2.
More Naval AI has basically become the new vanilla FfH2. It does not change any of the lore at all. If you play with the default game options, then its game mechanics are identical to base FfH2 from the players' perspective. However, much of the code under the hood has been altered to help the AI make better decisions, to run a bit more efficiently, to empower modmoders (like myself) to change more things in xml or python, and to log more info about possible errors. There are new mechanics (like puppet states and multiple production per turn), but those have to be activated as game options in a custom game. MNAI also includes a much better version of WorldBuilder, based on Platyping's work. I adapted that code for Tholal so it can change a bunch of things that are unique to FfH2. Unfortunately that code does not always play nicely when you try to load or save scenarios.
Most of the other modmods have experienced a great deal of scope creep, adding new civs, units, and mechanics that complicate things and do not really fit with the spirit of FfH2.
My MagisterModmod introduces new units, spells, and mechanics, but the increasing complexity is driven by the lore rather than a desire to add more stuff. I try to make things function the way they should based on what we know about them from canon, and don't really mind when it makes one civ or strategy overpowered because they are all overpowered in their own ways. I tried making my own DLLs about a decade ago, but am not great at coding C++ or making XML schema files so I prefer to borrow that from More Naval AI. Unfortunately Tholal's last couple releases introduced some instabilities that sometimes cause the game to crash for no apparent reason. (I'm not sure if that happens in base MNAI or is an interaction between his code and mine.) I am still sort of developing my modmod, but have not released anything since January because I want the next version to be stable. I'm hoping Tholal will come back and release an update that will fix everything, but fear that his job and children may keep him too busy indefinitely.