It always bugs me that the Haunted Mansion is a 'retirement home for ghosts' because it just isn't that creepy and it isn't a real story. Here is how I imagine the story should be:
There is a wedding supposed to take place in the mansion, and all of the guests (all 999 of them), mysteriously never return from it (except for three). Before the wedding, the groom discovers Madame Leota and she warns him about the bride and what she has done to past suitors. The groom gets cold feet and tries to leave, but the bride finds out. The bride cuts the grooms head off and makes sure no one else can leave the mansion. Either one of the guests or the bride's father hangs himself. Everyone is doomed to the mansion for an eternity of partying and waiting for the wedding that never occured. I know the idea has been given before, and it would be easy to tell the story as the ride progresses. It could be told through what the Bride, Madame Leota, and the Ghost Host say.