Since this is a topic about plot spoilers in general, if you do not wish to be spoiled it might be best to not read further.
Despite some awful twists I've seen Pariah (2005) has to take cake.
In the end it is revealed that the player character was the the bad guy/traitor all the time and that he knew it too, despite that the game includes several parts where said character stands alone and is wondering who the hell is targeting him. There are scenes where he wonders who gave away his position, when it was he himself who did it.
At the end, Capell (the main character) sacrifices his life to unchain the moon (the reason why the bad guys are so powerful). At the end, when the rebels are fixing things back on Earth, for literally NO REASON AT ALL, Capell's lover (I forgot her name) sees Capell alive and playing with some kids. They see each other and smile and hug and make love or w/e.
Read again: no reason at all. He just alive again. LOL HAPPY JRPG ENDING. <3
@ Star Ocean 3 twist: It wasn't bad; I thought it was pretty mindblowing. Then again, I was really young and hailed JRPGs and their stories as incredible feats... but still, looking bad, it wasn't that bad. :P
Mass Effect 3's plot twist was easily one of the worst I've seen. The whole reason for the Reapers killing off 99.9% of all organic life every 50k years was so incredibly flimsy.
They're preserving organic life by killing off nearly all organic life? Really? I suppose they cleaned it up a little bit through DLC, but the vanilla ending is just awful.
Other big, moronic plot twists include:
Star Ocean 3's ending. You're part of a video game! Go into 4D space and beat up your creators!. I have to give the game credit for the novelty of it all, but it inadvertently made Star Ocean 1 and 2 irrelevant as video game simulations.
I haven't played it yet, but apparently in the new Bionic Commando the hero Super Joe is randomly a traitor and your mechanical arm is really your wife . . . yeeeeeeeah.
In Heavy Rain, Scott Shelby being the killer after there's a murder he couldn't have possibly committed due to the way the scene takes out.
Indigo Prophecy . . . the train goes off the tracks at the end.