I think one video game with a great plot is the Metal Gear Solid. A big part of what sets it and its sequel apart from other games is their moral ambiguity. Even though Snake is always on the right side of the law, the people e fights are not truly evil. They have complex motivations and more often they're fighting on the wrong side because they're clued in to the uncaring conspiracy that is operating behind the "good" guys.

This is very much true in the first Metal Gear Solid where each boss battle is a story in itself and everyone you kill will deliver a strange monologue when you off them. As the plot evolves through "codec" radio conversations that drop in treatises on nuclear war and escalation of powers you will then start to wonder if you're on the right side at all. This is due to several of your "allies" covertly manipulating you into doing their bidding the whole time.
Revolver Ocelot is one of the most likable and hateful villains in video game history. He is sadist but he also has charm.

I really love the plot of The Dark Eye: Drakensang. It draws me inside the game and makes me want to continue.

The plot and whole world of The Elder Scrolls: Morrowind is also really compelling. You have your plot to follow but it is up to you to explore, sway from the plot or lose yourself into side plots. You can even continue playing after you have done something which makes the plot impossible (you get a warning though asking whether you want to continue playing or load a save game).