-- 1) Allow multiple hotels per user
ALTER TABLE public.hotels DROP CONSTRAINT IF EXISTS hotels_user_id_key;
CREATE INDEX IF NOT EXISTS hotels_user_id_idx ON public.hotels(user_id);

-- 2) User preferences
CREATE TABLE IF NOT EXISTS public.user_preferences (
  user_id UUID PRIMARY KEY,
  theme TEXT NOT NULL DEFAULT 'light',
  lang TEXT NOT NULL DEFAULT 'en',
  selected_hotel_id UUID,  -- null = "All hotels"
  notifications_enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.user_preferences ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Owner can view prefs" ON public.user_preferences FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Owner can insert prefs" ON public.user_preferences FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can update prefs" ON public.user_preferences FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can delete prefs" ON public.user_preferences FOR DELETE TO authenticated USING (auth.uid() = user_id);

CREATE TRIGGER user_preferences_set_updated_at
  BEFORE UPDATE ON public.user_preferences
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- 3) Chat sessions + messages
CREATE TABLE IF NOT EXISTS public.chat_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  hotel_id UUID,  -- null = portfolio-level chat
  title TEXT NOT NULL DEFAULT 'New conversation',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.chat_sessions ENABLE ROW LEVEL SECURITY;
CREATE INDEX IF NOT EXISTS chat_sessions_user_idx ON public.chat_sessions(user_id, updated_at DESC);

CREATE POLICY "Owner can view sessions" ON public.chat_sessions FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Owner can insert sessions" ON public.chat_sessions FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can update sessions" ON public.chat_sessions FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can delete sessions" ON public.chat_sessions FOR DELETE TO authenticated USING (auth.uid() = user_id);

CREATE TRIGGER chat_sessions_set_updated_at
  BEFORE UPDATE ON public.chat_sessions
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE TABLE IF NOT EXISTS public.chat_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL REFERENCES public.chat_sessions(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('user','assistant','system')),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.chat_messages ENABLE ROW LEVEL SECURITY;
CREATE INDEX IF NOT EXISTS chat_messages_session_idx ON public.chat_messages(session_id, created_at);

CREATE POLICY "Owner can view messages" ON public.chat_messages FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Owner can insert messages" ON public.chat_messages FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can delete messages" ON public.chat_messages FOR DELETE TO authenticated USING (auth.uid() = user_id);

-- 4) Action progress (per user / per hotel; null hotel_id = portfolio-wide)
CREATE TABLE IF NOT EXISTS public.action_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  hotel_id UUID,
  action_key TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('todo','in_progress','done')) DEFAULT 'todo',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.action_progress ENABLE ROW LEVEL SECURITY;
-- Use a partial unique index because UNIQUE() treats NULL as distinct
CREATE UNIQUE INDEX IF NOT EXISTS action_progress_uniq_hotel ON public.action_progress(user_id, hotel_id, action_key) WHERE hotel_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS action_progress_uniq_portfolio ON public.action_progress(user_id, action_key) WHERE hotel_id IS NULL;

CREATE POLICY "Owner can view progress" ON public.action_progress FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Owner can insert progress" ON public.action_progress FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can update progress" ON public.action_progress FOR UPDATE TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Owner can delete progress" ON public.action_progress FOR DELETE TO authenticated USING (auth.uid() = user_id);

CREATE TRIGGER action_progress_set_updated_at
  BEFORE UPDATE ON public.action_progress
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- 5) Account deletion: removes user-owned data and the auth user.
-- SECURITY DEFINER so it can delete from auth.users; locked to the caller's own uid.
CREATE OR REPLACE FUNCTION public.delete_my_account()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth
AS $$
DECLARE
  uid UUID := auth.uid();
BEGIN
  IF uid IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;
  DELETE FROM public.action_progress WHERE user_id = uid;
  DELETE FROM public.chat_messages WHERE user_id = uid;
  DELETE FROM public.chat_sessions WHERE user_id = uid;
  DELETE FROM public.user_preferences WHERE user_id = uid;
  DELETE FROM public.self_examinations WHERE user_id = uid;
  DELETE FROM public.hotels WHERE user_id = uid;
  DELETE FROM auth.users WHERE id = uid;
END;
$$;

REVOKE ALL ON FUNCTION public.delete_my_account() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.delete_my_account() TO authenticated;