You may recognize the name Ben "Yahtzee" Croshaw from The Escapist's Zero Punctuation video game reviews, but before Croshaw was doing videos for a mainstream company, he was doing freelance video game work on his personal website fullyramblomatic.com. He made many amazing "point and click" games with absolutely amazing plots and brilliant gameplay. The most notable of all these games was probably the Trilby series. This series of games has become one of my favorite game series of all time.
The Trilby series has one of the best, most enduring plots of any video game I have ever seen. Everytime I play through these games I have a really hard time pulling myself away from them. You will find yourself playing these games from beginning to end because the plot just will not let you go.
Here are the links to the games in order:
5 Days a Stranger7 Days a SkepticTrilby's Notes6 Days a SacrificeThese games seriously get better and better as you play through them. Even when you don't think that the plot could get any deeper and more intriguing, it does. Aside from the story, the puzzles in the game are also really well thought out and will take a little bit of thought and attention to detail to complete.
The rest of the games on Croshaw's site are also pretty good.
1213 is another one of my favorites. It's not a point and click like the Trilby series, but more of a run/climb/shoot/sneak around type game with plot twists like you wouldn't imagine.
The rest of his games are also worth a look if you find you are interested in his work. You can find a full list of his games at his personal website, here:
http://www.fullyramblomatic.com/games.htm