SimCity 4 is the height of development for the series. No other SimCity game comes close to the complexity of interweaving regional and local management. That said, I never played any of the mods. I hope GOG does a mod guide, I'd love to check them out!
This is a real time strategy, but not in vein of others out there. Its scale is huge and ranges are long, as is appropriate for a game that uses everything between postwar T-34s to Leo 2s. Not since Steel Panthers has a game managed to capture large scale mechanized warfare so well -- with the added bonus that this actually captures real time correctly. In place of base building, the game uses requisition points accrued over time based on areas capture and a unit pool. Reinforcements are available if your strategy works initially, but there are a limited number of them. This very effectively avoids the boring stand offs that come out of some other RTS games. The only downside is a weak single player campaign, but we all know these games are for scenarios and multiplayer anyway. If you enjoyed the Close Combat, Combat Mission, and Steel Panthers series, you will love this game as much as I do.