I have a weird relationship with horror games. I really like horror theming and scary stories in general, but I'm also a candy-ass when it comes to actually playing horror games. Every playthrough is like a battle between my appreciation of the expertly crafted atmosphere and the shriveling that's occurring in my balls. In the end though, I do love playing them.
A particular favorite of mine is Fatal Frame III: The Tormented. This is a game I played a very long time ago, when I was in high school. At the time, I was merely trying to be done with it (because I was terrified) so I figured I'd give it a serious playthrough now that I've grown a few more chest hairs.
Much like when I first played this game, I was absolutely terrified. Every minute I spent in the "manor of sleep" was accompanied by tense muscles and irregular heartbeats, and every time the controller shook to signal the presence of a spirit, I hit the pause button and took a breath. It really does its job as a horror game, and it's quite honestly a solid video game in general.
The thing I appreciated most about this game was its graphical design. The manor of sleep, which is where you spend most of your time in the game, looks incredibly eerie, with its old Japanese styling, damp floors, tight hallways, and too-dark-to-see lighting. Even when there aren't any ghosts nearby, the environment manages to make you feel uneasy about just standing there.
The story and lore are also some of the most intriguing in the survival horror genre. I actually took the time to stop and read all of the diaries and texts that I collected throughout my playthrough because I felt like I was reading a horror story (and like I said, I love those).
Fans generally agree that the second Fatal Frame game is better, but I personally think the third game is just as good. If anything, it stands out as one of the better games in the genre. I'd really recommend giving it a play if you still have a PS2. Just make sure you're up for this kind of thing. I don't want to be responsible for any heart attacks.