CREATE TABLE public.self_examinations (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  hotel_id UUID NOT NULL REFERENCES public.hotels(id) ON DELETE CASCADE,
  user_id UUID NOT NULL,
  website_url TEXT,
  social_profiles JSONB NOT NULL DEFAULT '{}'::jsonb,
  ota_listings JSONB NOT NULL DEFAULT '[]'::jsonb,
  seo_data JSONB NOT NULL DEFAULT '{}'::jsonb,
  reviews JSONB NOT NULL DEFAULT '{}'::jsonb,
  branding JSONB NOT NULL DEFAULT '{}'::jsonb,
  suggestions JSONB NOT NULL DEFAULT '[]'::jsonb,
  manual_notes TEXT,
  manual_overrides JSONB NOT NULL DEFAULT '{}'::jsonb,
  sources JSONB NOT NULL DEFAULT '[]'::jsonb,
  last_scanned_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(hotel_id)
);

ALTER TABLE public.self_examinations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Owners can view their self-examination"
ON public.self_examinations FOR SELECT TO authenticated
USING (auth.uid() = user_id);

CREATE POLICY "Owners can insert their self-examination"
ON public.self_examinations FOR INSERT TO authenticated
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Owners can update their self-examination"
ON public.self_examinations FOR UPDATE TO authenticated
USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Owners can delete their self-examination"
ON public.self_examinations FOR DELETE TO authenticated
USING (auth.uid() = user_id);

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

CREATE INDEX idx_self_examinations_hotel ON public.self_examinations(hotel_id);
CREATE INDEX idx_self_examinations_user ON public.self_examinations(user_id);