Them's fightin' words.
The fact that you listed Haunted Mansion and Pirates makes you lose all credibility. If you really knew your attractions, you would know that neither of those rides officially has any story (well, Pirates before Depp) and any story you have heard has been made up over the years by Cast Members.
You also listed several rides that just follow the story of the movie they are based off (not that this is a bad thing) or Epcot attractions that were non-fiction or not presented as a story.
Tower's story takes you into a Twilight Zone episode, but rather than place you in an existing episode or the movie, you are in an entirely original story created for the ride which borrows and uses elements from the show. The basic premise of the story is made very clear but the details are left open to interpretation, giving it the mystery and intrigue that no other theme park attraction has matched yet.
Also, cheap thrills = Dr. Doom. Not Tower of Terror.
First, I believe every attraction I mentioned does have a story to it, and I believe they all have a better and more complete story than Tower of Terror. Claiming that I lose credibility because I use Pirates of the Caribbean and Haunted Mansion is silly because they DO have stories. They are rather general stories, but they do have them. Since you overexplained your case, I'll overexplain the story of Pirates (I'm using Disneyland's since it has the better story): we begin in presumably present day, where we see the skeletal remains of pirates who appear to have been cursed for all eternity to do whatever it was they were doing when they died - navigating a beached ship, sitting atop a fortune in gold and jewels, playing chess, etc. We then travel back in time to witness glimpses of how they ended up the way they did - from attacking the town, to pillaging the town, to burning the town, to being caught and jailed, and to escaping to continue their debauchery. No, there's not a specific single character we're following, but there's a very definite and, in my mind, obvious storyline. I'm not using anything that's not readily visible in the attraction to describe it. No CMs were involved in the development of this storyline (unless you count me, since I once worked at Disneyland).
I'll grant you that Haunted Mansion is a little less defined, and maybe not the best example of a "complete" story. In fact, it's pretty safe to say it's mainly just a Ghost Host giving you a tour of the place and trying to convince you to join them, but instead a ghost decides to follow you home. I still find its story far more satisfyingly developed and concluded than Tower of Terror's. And as far as "mystery and intrigue" goes, I think the previous incarnation of Haunted Mansion blows the Tower away.
Yeah, I know, them's fightin' words. So, here's how I see Tower of Terror's development after the video, which again, I fully admitted is quite good:
1. You get into a service elevator to be a part of a non-existent "episode" (yeah, I got that. I'm not that dim).
2. You go up a few flights and see the people from the "episode" in a ghostly form acting as if they're trying to convince you to come with them, then the room goes black, turns to stars, and you ascend again.
3. You enter the fifth dimension, and go through the whole "Twilight Zone" opening credit scene, basically rehashing what you already saw in the video downstairs...only now it's all around you.
4. You enter the other shaft, Rod says you're going beyond the fifth dimension, and then it's down and up and down and up and down and up and down until the computer decides you've had enough.
5. At the bottom, you see a fading video of the opening credits AGAIN, and Rod welcomes you back and tells you to avoid haunted hotels in the future (how deep and helpful!).
There's not much in the way of story there. In fact, if I were to point out the one thing this attraction has a lot of, it's repeating itself. You see the opening credits three times in less than a ten minute span of time, you go down and up repeatedly, and you hear that girl singing "It's raining, it's pouring" a lot. To me, none of this says well-developed story, nor does it say greatest attraction out there. It also seems incredibly unfinished to me. You supposedly enter the Twilight Zone, repeatedly plunge toward the ground, and then you walk away from the Zone and the attraction with only a warning to be careful? I wish it worked for me, but it doesn't.
And yes, it's a cheap thrill. Dr. Doom is obviously an even cheaper thrill, but Tower's is still cheap, all the same. You're yanked down and up repeatedly, then - boom! - the ride is over. That's it. Meanwhile, an attraction like Splash Mountain has a thrill that could end up feeling cheap, but instead the drop is followed by a show-stopping scene that wraps up the story and doesn't feel rushed or tacked on.