
CREATE TABLE IF NOT EXISTS public.commission_rules (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  commission_type text NOT NULL DEFAULT 'percentage', -- 'percentage' | 'fixed'
  commission_value numeric NOT NULL DEFAULT 0,
  min_sale_amount numeric NOT NULL DEFAULT 0,
  max_sale_amount numeric,
  priority integer NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  note text DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid
);

ALTER TABLE public.commission_rules ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view commission rules"
  ON public.commission_rules FOR SELECT TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Admins can insert commission rules"
  ON public.commission_rules FOR INSERT TO authenticated
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can update commission rules"
  ON public.commission_rules FOR UPDATE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can delete commission rules"
  ON public.commission_rules FOR DELETE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

CREATE TRIGGER update_commission_rules_updated_at
  BEFORE UPDATE ON public.commission_rules
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Seed default global settings if missing
INSERT INTO public.business_settings (setting_key, setting_value)
VALUES
  ('referral_enabled', 'true'),
  ('referral_default_type', 'percentage'),
  ('referral_default_value', '2'),
  ('withdrawal_minimum', '100')
ON CONFLICT DO NOTHING;
