
-- Referral Wallets
CREATE TABLE public.referral_wallets (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id uuid NOT NULL UNIQUE,
  total_commission numeric NOT NULL DEFAULT 0,
  paid_amount numeric NOT NULL DEFAULT 0,
  balance numeric NOT NULL DEFAULT 0,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);
ALTER TABLE public.referral_wallets ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view referral wallets" ON public.referral_wallets FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert referral wallets" ON public.referral_wallets FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));
CREATE POLICY "Staff can update referral wallets" ON public.referral_wallets FOR UPDATE TO authenticated USING (is_staff(auth.uid()));
CREATE TRIGGER trg_referral_wallets_updated BEFORE UPDATE ON public.referral_wallets FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Withdrawal Requests
CREATE TABLE public.withdrawal_requests (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id uuid NOT NULL,
  amount numeric NOT NULL DEFAULT 0,
  method text NOT NULL DEFAULT 'cash',
  account_number text NOT NULL DEFAULT '',
  note text DEFAULT '',
  status text NOT NULL DEFAULT 'pending',
  processed_by uuid,
  processed_at timestamp with time zone,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);
ALTER TABLE public.withdrawal_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff can view withdrawals" ON public.withdrawal_requests FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert withdrawals" ON public.withdrawal_requests FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));
CREATE POLICY "Admins can update withdrawals" ON public.withdrawal_requests FOR UPDATE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));
CREATE POLICY "Admins can delete withdrawals" ON public.withdrawal_requests FOR DELETE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));
CREATE TRIGGER trg_withdrawal_requests_updated BEFORE UPDATE ON public.withdrawal_requests FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE INDEX idx_withdrawal_requests_customer ON public.withdrawal_requests(customer_id);
CREATE INDEX idx_withdrawal_requests_status ON public.withdrawal_requests(status);

-- Default settings (insert if missing)
INSERT INTO public.business_settings (setting_key, setting_value)
VALUES
  ('referral_enabled', 'true'),
  ('referral_commission_percent', '5'),
  ('referral_commission_type', 'percentage'),
  ('withdrawal_minimum', '100')
ON CONFLICT DO NOTHING;
