You mean like Legacy of Kain, right? If we're talking about awesome plot, you can't just NOT mention LoK.
As for the KH plot.
Well, yeah. The first one had a simple enough plot, it worked. Then CoM came, and it had a bit more complicated, but still good plot. In fact, it was the only thing that pushed me forward in playing it (THOSE [tornado fang]ing CARDS YOU HAVE TO LOOK FOR TO OPEN THE DOORS WHY CAN'T THE ENEMIES DROP THE CARD I WANT WHY WHY,lfewl,alf,al).
And then... Kingdom Hearts 2 came and everything slowly started falling apart.
The plot became a so bad it's good.
Heh.
KH2 had potencial. Pretty much everything bad with it was that Sora couldn't do [parasitic bomb] and was basically irrelevant to the story, and the Organization. The Organization ruined everything really.
And yes, I'm a big fan of the Legacy of Kain games, and I specially love the way how Defiance cleared everything up so nicely. There are other amazing games, though. Planescape: Torment is amazing for an RPG, as well as the first two Fallouts. KOTOR has the best story in all of the Star Wars legacy, with a cast of characters that is absolutely wonderful and quite deep if you dig into their worlds time and time again. Snatcher has a very cool story, for example. And there are lots of point and click games which might fit into the role, such as The Longest Journey. And for all of its present fuckupness and canon problems, the first MGS game had one hell of a story.
And if I might plug one absolutely AMAZING title, Alan Wake has one hell of a story, which actually challenges you and treats you as an intelligent human being instead of giving you retarded details every seconds and twists of the dumb variety.
No, I think it just became bad. At this point, he's just throwing any plot element he can and is going to pull an M. Night Shamalamadingdong on everyone in the last game.
[parasitic bomb], Sora's already the biggest Mary Sue in history.
I think it's the opposite. From KH2 onwards, Sora became the dumbest shitbag in the whole game. Goofy is the smart one on the team who makes all of the logical connections, for [tornado fang]'s sake. GOOFY. Sora is completely disconnected from all of the story. It's no longer the story about a little boy with a big key and a fantastic adventure. It's Nomura's leather bullshit story about bishonen who act like pretentous assholes and never let you in on the story until the last moment just for kicks. Sora just plays the part of killing the big bad at the end, after he's pulled a fuckton of gambits on you.