The PC had some awesome horror games back in the mid-late 90's, but these two are my favorites.

Realms of the Haunting starts out with a spooky old mansion and some Scooby Doo antics involving ghosts and your dead father and such, but ends up going so far over the top with ancient cults and ancient, intergalactic horrors that it would have made Lovecraft shat his pants. In Bad Mojo you get turned into a roach and have to do roach stuff, like avoid sticky traps and getting eaten. The common theme that runs between both is ridiculously awesome FMV sequences that ooze with ham-fisted, over-the-top acting and epic B-movie cheesiness, the likes of which are rarely seen these days.
Observe, if you will, just a portion of their awesomeness:
[youtube=425,350]zyXo2011YRo[/youtube]
[youtube=425,350]vkMabElIp3s[/youtube]
Simply amazing.

Bad Mojo is easy to find and may still be in print, Got Game released a Redux Edition a few years ago that has hi-res videos, some bonus materials, and Windows XP compatibility. You may still be able to find it new, but if not, it shouldn't cost more than $5. Realms of the Haunting has been out-of-print for a decade, but it's still easy to find. I checked on Amazon Marketplace, and used copies are selling for well under $10 (I've having to rebuy it myself because my "friend" lost my copy several years ago. :'( ). It's a DOS game, but I checked online and people have got it running on DosBox, so it's still playable. Well, technically playable anyway, whether or not you can withstand the ancient 640x480 3D graphics is up to you.