Can you only use the water park if so how much is it

Only if you are a guest of the hotel. They do not offer passes to people off the street who are not staying the night.

Only if you are a guest

I read that you can pay for a day pass for $50 a person and don't have to stay. I have always waited for deals and paid way less than $200 for a room for my family of.4 though. https://www.greatwolf.com/new-england/daypass

Yes you have to spend the night then you will access to almost everything included free.