TF2 seems generally pretty bright and light-hearted as far as fps's go.
This thread goes over how to light "dark" environments well, usually by lighting the play areas well and leaving the out of bounds areas dark. As far as "story" lots of Halloween maps have "horror" elements while still staying lighthearted. In doomsday_event, for instance, the mercs are being sacrificed to appease a Sumerian circus god, while in viaduct_event they are being attacked by a disembodied giant eyeball. The darkest non-valve map in my opinion is void's Slaughter, with its whole (human) meat packing plant theme.
Also it depends on how you look at it. Doesn't blowing up an inhabited building create fear? Is there an intrinsic horror to being trapped in a loop of death and regeneration, fighting against your own clones for some voice's benefit, with no end in sight? :shrug: