-- Extend commission_rules with new fields
ALTER TABLE public.commission_rules
  ADD COLUMN IF NOT EXISTS level integer NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS category_id uuid,
  ADD COLUMN IF NOT EXISTS product_id uuid,
  ADD COLUMN IF NOT EXISTS starts_at timestamptz,
  ADD COLUMN IF NOT EXISTS ends_at timestamptz;

CREATE INDEX IF NOT EXISTS idx_commission_rules_level ON public.commission_rules(level);
CREATE INDEX IF NOT EXISTS idx_commission_rules_category ON public.commission_rules(category_id);
CREATE INDEX IF NOT EXISTS idx_commission_rules_product ON public.commission_rules(product_id);
CREATE INDEX IF NOT EXISTS idx_commission_rules_dates ON public.commission_rules(starts_at, ends_at);

-- Payout schedule (single config row)
CREATE TABLE IF NOT EXISTS public.commission_payout_schedule (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  auto_enabled boolean NOT NULL DEFAULT false,
  frequency text NOT NULL DEFAULT 'monthly', -- weekly | monthly
  day_of_week smallint NOT NULL DEFAULT 1,   -- 1=Mon..7=Sun (for weekly)
  day_of_month smallint NOT NULL DEFAULT 1,  -- 1..28 (for monthly)
  min_payout_amount numeric NOT NULL DEFAULT 0,
  last_run_at timestamptz,
  next_run_at timestamptz,
  updated_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.commission_payout_schedule ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Staff can view payout schedule" ON public.commission_payout_schedule;
CREATE POLICY "Staff can view payout schedule"
  ON public.commission_payout_schedule FOR SELECT TO authenticated
  USING (is_staff(auth.uid()));

DROP POLICY IF EXISTS "Admins can insert payout schedule" ON public.commission_payout_schedule;
CREATE POLICY "Admins can insert payout schedule"
  ON public.commission_payout_schedule FOR INSERT TO authenticated
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS "Admins can update payout schedule" ON public.commission_payout_schedule;
CREATE POLICY "Admins can update payout schedule"
  ON public.commission_payout_schedule FOR UPDATE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

DROP POLICY IF EXISTS "Admins can delete payout schedule" ON public.commission_payout_schedule;
CREATE POLICY "Admins can delete payout schedule"
  ON public.commission_payout_schedule FOR DELETE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

DROP TRIGGER IF EXISTS trg_commission_payout_schedule_updated_at ON public.commission_payout_schedule;
CREATE TRIGGER trg_commission_payout_schedule_updated_at
  BEFORE UPDATE ON public.commission_payout_schedule
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Seed a single row if empty
INSERT INTO public.commission_payout_schedule (auto_enabled, frequency, day_of_month, min_payout_amount)
SELECT false, 'monthly', 1, 0
WHERE NOT EXISTS (SELECT 1 FROM public.commission_payout_schedule);