Yes, the game is worth it just for the mods. Some of the mods for this game are absolutely amazing. Play the regular game for about 2 months. Then google Stainless Steel for a mod that makes the game essentially 100 times better. If you want something fantasy based there is always Third Age:Total War, a mod that I actually thought was a commercial product until I saw it was listed under mods on MODDB.
Edit: Yes it has online play but really the whole point of the game is the awesome singleplayer.