Let me just say that games these days almost always have happy endings. Unless the game is ridiculously dark from the get go.

On that note my list:

Wing commander (snes, the hardest game I've ever played, if you don't succeed in 90% of the missions the enemies victory looms ever closer. If you do win all the missions the finale makes fighting emerald/ruby/ultimate weapon simultaneously with no materia seem like a piece of cake)

Contra (so i didn't know the cheat)

zelda link to the past (just as your about to save the chick you get sucked into a world where your greatest enemy controlls the ultimate magic that can do anything)

Mercenaries ( by the time your fighting the spades your wondering, "how in God's green earth can I capture these people without killing them?"

that's my list