
-- Wallet transactions log
CREATE TABLE public.wallet_transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL,
  type text NOT NULL DEFAULT 'commission_credit', -- commission_credit | withdrawal_debit | due_adjustment_debit | manual_adjustment
  amount numeric NOT NULL DEFAULT 0,
  balance_after numeric NOT NULL DEFAULT 0,
  reference_id text DEFAULT '',
  note text DEFAULT '',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.wallet_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view wallet transactions" ON public.wallet_transactions
  FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Staff can insert wallet transactions" ON public.wallet_transactions
  FOR INSERT TO authenticated WITH CHECK (is_staff(auth.uid()));

CREATE INDEX idx_wallet_tx_customer ON public.wallet_transactions(customer_id, created_at DESC);

-- Customer due adjustments using wallet balance
CREATE TABLE public.customer_due_adjustments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL,
  invoice_id text DEFAULT '',
  amount numeric NOT NULL DEFAULT 0,
  note text DEFAULT '',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.customer_due_adjustments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view due adjustments" ON public.customer_due_adjustments
  FOR SELECT TO authenticated USING (is_staff(auth.uid()));
CREATE POLICY "Admins can insert due adjustments" ON public.customer_due_adjustments
  FOR INSERT TO authenticated WITH CHECK (has_role(auth.uid(), 'admin'::app_role));
CREATE POLICY "Admins can delete due adjustments" ON public.customer_due_adjustments
  FOR DELETE TO authenticated USING (has_role(auth.uid(), 'admin'::app_role));

CREATE INDEX idx_due_adj_customer ON public.customer_due_adjustments(customer_id, created_at DESC);

-- Pending balance on wallet
ALTER TABLE public.referral_wallets
  ADD COLUMN IF NOT EXISTS pending_balance numeric NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS adjusted_amount numeric NOT NULL DEFAULT 0;
