If you want a game that is both incredibly fun and has replayability that approaches infinite, Civ 3 is definitely for you.
Basically, you select a culture, and build it up from ancient times to the digital future, interacting with other Civs along the way (either conquering them or making alliances with them). You grab resources, build cities, research technology, and conquer other cultures. The strategy is fun and addictive, and the graphics, while not outstanding, are nice, and a huge improvement over Civ 2. There is a bit of a learning curve required to get used to managing your Civ, but you get the hang of it fairly quickly.
I've had this game on my computer for about three years, and about once every ten months I do a playthrough. Really, it's a game that will last a long long time.