For starters, Silent Hill 1-3. They had plots that had me so interested (and confused) I play through them twice in a row just to make sense of every little detail, and it was definatly worth it!
Now, I am openly going to disagree with what ingsoc said about Half Life, now I am going to start by saying in all fairness I've only played Half life 1 and Half life 2 (none of the episodes). The plot in all cases is largely quite confusing, firstly, it is never quite explained what the purposes of the experiments at the begining of HL1 are or what a 'resonance cascade' is so when you do have the leigons of the bizzare stalking the hallways of the facility it comes across as being somewhat random and can only really be explained by the fact that this is in fact an FPS and they need to slide enemies in somewhere. Secondly, now moving to HL2, other than the occasional referance to a '7 hour war' in newspaper clippings in the lab at the begining it is not explained why aliens (who you never see) have dominated earth, and other than instant situational plot points such as where you are and what you are doing, you are largely in the dark. I'd hate to be the one to break this to Valve but maybe they should stop high fiving each other over the physics engine and remember it is in fact a game they are trying to make here.
Now I can put ranting aside, up until Code Veronica, Resident evil also had a pretty good plot going which, suffice to say, has made me play through most of them time and time again.