HHN seems to be one of those events that you ever love it or hate it. You said you're kind of a wimp but not too bad. How do you feel going to your local haunted house? Could you handle that feeling or worse for 5 or 6 hours straight? The thing with HHN is the intensity. Once you're in the park, there's really no escape. Some people thrive on that, others end up freaking out and getting a security escort to the gate.
If you're up for it though, the experience is awesome! Seven haunted houses, plus wandering street monsters, plus live stage shows--if you go, do NOT miss Bill and Ted's Excellent Halloween Adventure, it's one of the highlights of the event. Plus some rides will be open, and I hear they're going to run the coasters in the dark like they did last year. The event is one part terror, one part live entertainment, 100% party. A lot of people from around the country come in specifically for HHN.
Since you'll be here on the weekend before Halloween, expect massive crowds. That's just the way it is. And a large portion will be drunk and anywhere from mildly annoying to downright obnoxious. Prep yourself for it to be like going to a rock concert, go in with that mentality, and you'll have fun with the crowd. But a lot of people don't expect it, and they end up having a miserable time.
As long as you're not so scared that you're prone to panic, I say go for it!! I worked it last year, can't this year cause I'm moving back to New Orleans in mid-October, but I'm still going to get a Frequent Fear Pass and go a few times before my move. It's one of the best Halloween events in the country!