I personally don't think it would be too fun for your hard work to be incinerated by nuclear blasts. If it was too common an occurrence it would be quite annoying, with the sure knowledge that whatever you do, whatever empire you build, it's going to get destroyed at the end anyway.
Personally, I think the system could be made more interesting if tweaked just a bit. Right now, ICBMs are on constant alert and ready to strike at a moment's notice. Realistic? I honestly don't know but it leads to a situation where it's impossible to score a quick victory and take a few cities before the AI is raining nukes on you.
A "Nuclear Strategy" Screen that could be a sub-screen in the Military Sceen would be idea. From this screen, you would be able to see where all your ICBMs are and pre-set targets for each individual nuke.
Pre-setting a nuke would be as simple as selecting a nuke from the list and clicking the wanted target. From there, the ICBM would have it's target listed beside it's name and be put under a new category in the list (under the name of the Civilization it's targeted at).
After that business is done, the player could set their "Defcon" level. Only three levels would be really necessary. Blue, Orange, than Red. Under Blue, all nukes are on standby (and there's no added maintenance bonus). "Orange" is when a player arms a nuke, set of nukes, or all his or her nukes (I'll explain in a second), and Red is when a nuclear war is ongoing.
As for arming the nukes, this is simple. ICBMs, when built, cannot be fired immediately. Instead, it takes a few turns to "arm" them (meaning ready to fire). The player would be able to arm one, by target nation, or all of them.
Having the nukes ready to fire all the time wouldn't be beneficial. There would be a happiness penalty and possibly even a diplomatic penalty with the other nations. Of course, being at Defcon 1 or Red would keep the penalty and maintenance increase because the enemy hitting your with nukes is punishment enough.
This wouldn't apply to tactical nukes since, if they're still in the game, would be used against targets in the field. However, their range would be too limited and yield too small to be used as strategic weapons anyway. But now that units are going to be spread out, tactical nukes are either going to have to be cheaper (since the number of units they can hit at once are now much smaller) or something but that isn't an issue at the moment.
Then again, since there are no longer large garrison of troops in cities, there should be other reasons to use the nuke. Right now, bomb shelters are....strange since if I recall, it also reduces the destruction of buildings.
Now, in regards to the OP, nuclear war being the end game would be dreadfully boring. Should nuclear war have long lasting effects like killing off millions, destroying infrastructure and buildings, and slow population growth for a long time? Yes but it shouldn't be the end of the world.