-- ============== USER CREDITS ==============
CREATE TABLE IF NOT EXISTS public.user_credits (
  user_id UUID PRIMARY KEY,
  balance INTEGER NOT NULL DEFAULT 100,
  monthly_allowance INTEGER NOT NULL DEFAULT 100,
  period_start TIMESTAMPTZ NOT NULL DEFAULT date_trunc('month', now()),
  total_used INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.user_credits ENABLE ROW LEVEL SECURITY;

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

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

-- Auto-resets balance when a new month rolls over.
CREATE OR REPLACE FUNCTION public.consume_credits(_user_id UUID, _amount INTEGER)
RETURNS TABLE (balance INTEGER, monthly_allowance INTEGER, total_used INTEGER, period_start TIMESTAMPTZ)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  current_period TIMESTAMPTZ := date_trunc('month', now());
BEGIN
  INSERT INTO public.user_credits (user_id) VALUES (_user_id)
  ON CONFLICT (user_id) DO NOTHING;

  -- Reset on month rollover
  UPDATE public.user_credits
     SET balance = monthly_allowance, period_start = current_period, total_used = 0
   WHERE user_id = _user_id AND period_start < current_period;

  -- Atomic decrement; refuse if not enough balance
  UPDATE public.user_credits
     SET balance = balance - GREATEST(_amount, 0),
         total_used = total_used + GREATEST(_amount, 0)
   WHERE user_id = _user_id AND balance >= GREATEST(_amount, 0);

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Out of credits' USING ERRCODE = 'P0001';
  END IF;

  RETURN QUERY
  SELECT uc.balance, uc.monthly_allowance, uc.total_used, uc.period_start
    FROM public.user_credits uc WHERE uc.user_id = _user_id;
END;
$$;

CREATE OR REPLACE FUNCTION public.ensure_user_credits(_user_id UUID)
RETURNS TABLE (balance INTEGER, monthly_allowance INTEGER, total_used INTEGER, period_start TIMESTAMPTZ)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  current_period TIMESTAMPTZ := date_trunc('month', now());
BEGIN
  INSERT INTO public.user_credits (user_id) VALUES (_user_id)
  ON CONFLICT (user_id) DO NOTHING;

  UPDATE public.user_credits
     SET balance = monthly_allowance, period_start = current_period, total_used = 0
   WHERE user_id = _user_id AND period_start < current_period;

  RETURN QUERY
  SELECT uc.balance, uc.monthly_allowance, uc.total_used, uc.period_start
    FROM public.user_credits uc WHERE uc.user_id = _user_id;
END;
$$;

-- ============== FOCUSED COMPETITORS ==============
CREATE TABLE IF NOT EXISTS public.focused_competitors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  hotel_id UUID NOT NULL,
  competitor_key TEXT NOT NULL,
  competitor_name TEXT NOT NULL,
  snapshot JSONB NOT NULL DEFAULT '{}'::jsonb,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (hotel_id, competitor_key)
);

ALTER TABLE public.focused_competitors ENABLE ROW LEVEL SECURITY;

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

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

-- ============== ACTION NOTES ==============
CREATE TABLE IF NOT EXISTS public.action_notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  hotel_id UUID,
  action_key TEXT NOT NULL,
  note TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.action_notes ENABLE ROW LEVEL SECURITY;

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

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

CREATE INDEX IF NOT EXISTS action_notes_user_action_idx ON public.action_notes(user_id, action_key);
CREATE INDEX IF NOT EXISTS focused_competitors_hotel_idx ON public.focused_competitors(hotel_id);