A new independent hospital providing specialist support for people affected by substance use has officially opened its doors in North Wales.