
-- 1. Add audit + soft-delete columns to wallet_transactions
ALTER TABLE public.wallet_transactions
  ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN IF NOT EXISTS updated_by uuid,
  ADD COLUMN IF NOT EXISTS deleted_at timestamptz,
  ADD COLUMN IF NOT EXISTS deleted_by uuid,
  ADD COLUMN IF NOT EXISTS transaction_date timestamptz NOT NULL DEFAULT now();

-- 2. Allow admin UPDATE / DELETE on wallet_transactions
DROP POLICY IF EXISTS "Admins can update wallet transactions" ON public.wallet_transactions;
CREATE POLICY "Admins can update wallet transactions"
ON public.wallet_transactions
FOR UPDATE TO authenticated
USING (has_role(auth.uid(), 'admin'::app_role))
WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

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

-- 3. Audit table
CREATE TABLE IF NOT EXISTS public.wallet_transaction_audit (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_id uuid NOT NULL,
  customer_id uuid NOT NULL,
  action text NOT NULL, -- 'create' | 'update' | 'delete' | 'restore'
  previous_data jsonb DEFAULT '{}'::jsonb,
  new_data jsonb DEFAULT '{}'::jsonb,
  performed_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.wallet_transaction_audit ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view wallet audit"
ON public.wallet_transaction_audit
FOR SELECT TO authenticated
USING (is_staff(auth.uid()));

CREATE POLICY "Staff can insert wallet audit"
ON public.wallet_transaction_audit
FOR INSERT TO authenticated
WITH CHECK (is_staff(auth.uid()));

-- 4. Recalculation function
CREATE OR REPLACE FUNCTION public.recalc_referral_wallet(_customer_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_credit numeric := 0;
  v_paid numeric := 0;
  v_adjusted numeric := 0;
  v_manual_pos numeric := 0;
  v_manual_neg numeric := 0;
  v_balance numeric := 0;
BEGIN
  SELECT COALESCE(SUM(amount),0) INTO v_credit
    FROM wallet_transactions
    WHERE customer_id = _customer_id AND deleted_at IS NULL AND type = 'commission_credit';

  SELECT COALESCE(SUM(amount),0) INTO v_paid
    FROM wallet_transactions
    WHERE customer_id = _customer_id AND deleted_at IS NULL AND type = 'withdrawal_debit';

  SELECT COALESCE(SUM(amount),0) INTO v_adjusted
    FROM wallet_transactions
    WHERE customer_id = _customer_id AND deleted_at IS NULL AND type = 'due_adjustment_debit';

  SELECT COALESCE(SUM(amount),0) INTO v_manual_pos
    FROM wallet_transactions
    WHERE customer_id = _customer_id AND deleted_at IS NULL AND type = 'manual_adjustment' AND amount > 0;

  SELECT COALESCE(SUM(ABS(amount)),0) INTO v_manual_neg
    FROM wallet_transactions
    WHERE customer_id = _customer_id AND deleted_at IS NULL AND type = 'manual_adjustment' AND amount < 0;

  v_balance := v_credit + v_manual_pos - v_paid - v_adjusted - v_manual_neg;

  INSERT INTO referral_wallets (customer_id, total_commission, paid_amount, adjusted_amount, balance)
  VALUES (_customer_id, v_credit, v_paid, v_adjusted, v_balance)
  ON CONFLICT (customer_id) DO UPDATE
    SET total_commission = EXCLUDED.total_commission,
        paid_amount = EXCLUDED.paid_amount,
        adjusted_amount = EXCLUDED.adjusted_amount,
        balance = EXCLUDED.balance,
        updated_at = now();
END;
$$;

-- Ensure unique constraint for ON CONFLICT
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'referral_wallets_customer_id_key') THEN
    ALTER TABLE public.referral_wallets ADD CONSTRAINT referral_wallets_customer_id_key UNIQUE (customer_id);
  END IF;
END $$;
