Anyone who likes a good story should go play 999, I'm surprised no one recommended it on this thread but I've heard great things about it before on TBFP's podcast. It's a Puzzle/Horror game for the DS, extremely anime with some tired tropes you'll quickly identify but quite satisfying to play in spite of it. The premise is unique to anyone who hasn't already read a Deadly Game anime/manga, the story is fun, layered and mysterious. One thing that might put some people off is that the puzzles so far are terribly easy, I haven't got stuck once yet and I've already completed one playthrough (the game has 6 endings which require you to go back and pick one of the other branching paths you didn't choose, I heard some of them are actually fairly hard to get).
Anyways! So my next big question is: what are some great SNES, GBA, DS, PSX horror games I should try out? Try to avoid the most obvious franchises like Resident Evil and Silent Hill because I've played them all, I'm looking for stuff I haven't heard of!
Here's some of my recommendations:
Hell Night (PSX, first person adventure game, pants shittingly scary, almost as bad as Silent Hill 1/2/3 for me, don't let the awful and confusing opening 3D FMVs put you off, its not often a horror game makes me want to stop playing)
Scratches (PC, very neat point and click game with a really engaging story and actually decent reasons for the MC going inside the spooky house and staying, at least at first)
King's Field (PSX, a first person action game with an emergent story that you discover through speaking to NPCs and item descriptions, made by From Software and considered a predecessor of Dark Souls for reasons immediately clear when you start the game and turn left instead of going right and get OHKO'd by a gigantic killer crab; the shitty Runescape 3D graphics, unique enemies, legitimately intriguing backstory, oppressive atmosphere and punishing mechanics combine to make the game a very tense and spooky experience, you can never once afford to let your guard down lest you be clouted across the head by flying bladefish or shish kebab-ed by skeletons waving pointy swords)
Deception III (PSX, third person action game with very unique mechanics, difficult to explain; basically you set traps for evil jerkasses who want to impale you on things)