Grand Theft Auto: Chinatown Wars has a really long, in depth story that will take a while to complete, matching up to the complexity of the other GTAs. Another good one is Phoenix Wright, the cult classic courtroom drama. It gets pretty corny and ridiculous at times, but overall it was a great experience to play through the lengthy story with tons of humor and mystery.
Lol xP. But yeah, figuring out what the story is in this game is a great story in itself. Sadly, most people think it's just a game where you catch bugs with webs..and that's all there is to it.
Bryce Manor had one of the most sophisticated stories I've seen in a videogame. For such a simple game, the execution was outstanding.
I second (and third, and fourth...) what everyone else in the thread has been saying about BaSS, Monkey Island and the Broken Sword games. Also try Crimson Gem Saga and Simon the Sorcerer. Only played Simon the Sorcerer on PC so I can't vouch for the conversion but its writing is up there with LucasArts for quality and humor. If you like Law & Order-style mysteries you should try Cause of Death. The writing is good but cliched and it doesn't have much in the line of gameplay but it's a pretty gripping serial mystery. The first chapter is free along with the current week's story but you have to pay for the other chapters. And I loved Spider's story; it's just not one that's written down.
You should try "Tiny Grave". It is a match three that is story driven and all of the dialog is voiced over. It is funny and tounge in cheek, and it makes for a different experience for a match three. I did work on the game so I'll be up front that I am biased.
I enjoyed... Space Miner Phoenix Wright Spyder Infinity Project MYST. One of the best video game stories in history
It takes real perception to figure out doodle jump though. I would suggest bryce manor for someone like you.
How could I forget Spider: Secret of Bryce Manor? It's an interistingly told story where you must infer the story yourself from clues found while exploring, filling in the gaps where necessary. Cool stuff.
This is a bit out there but, I found the story of Dexter the Game to be very deep, enjoyable and immersive. It's an old game but, it's story is quite solid.