CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql SET search_path = public AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

CREATE TABLE public.deep_dives (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  hotel_id uuid,
  topic text NOT NULL CHECK (topic IN ('industry','competitor')),
  subject_key text NOT NULL,
  language text NOT NULL DEFAULT 'en',
  title text NOT NULL,
  payload jsonb NOT NULL,
  metrics jsonb NOT NULL DEFAULT '[]'::jsonb,
  cover_image text,
  generated_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, topic, subject_key, language)
);

CREATE INDEX deep_dives_user_idx ON public.deep_dives(user_id);
ALTER TABLE public.deep_dives ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users select own deep dives" ON public.deep_dives
FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Users insert own deep dives" ON public.deep_dives
FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own deep dives" ON public.deep_dives
FOR UPDATE TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Users delete own deep dives" ON public.deep_dives
FOR DELETE TO authenticated USING (auth.uid() = user_id);

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