CREATE TABLE public.competitor_live_data (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  hotel_id uuid NOT NULL,
  competitor_name text NOT NULL,
  price_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  scarcity_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  reviews_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  sources jsonb NOT NULL DEFAULT '[]'::jsonb,
  error text,
  scraped_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (hotel_id, competitor_name)
);

CREATE INDEX idx_competitor_live_hotel ON public.competitor_live_data (hotel_id);
CREATE INDEX idx_competitor_live_stale ON public.competitor_live_data (scraped_at);

ALTER TABLE public.competitor_live_data ENABLE ROW LEVEL SECURITY;

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

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