ALTER TABLE public.deposits
  ADD COLUMN IF NOT EXISTS paystack_reference TEXT UNIQUE,
  ADD COLUMN IF NOT EXISTS provider TEXT NOT NULL DEFAULT 'manual';

CREATE OR REPLACE FUNCTION public.credit_deposit_paystack(_deposit_id uuid, _reference text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE d RECORD;
BEGIN
  SELECT * INTO d FROM public.deposits WHERE id = _deposit_id FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'Deposit not found'; END IF;
  IF d.status = 'approved' THEN RETURN; END IF;
  INSERT INTO public.wallets(user_id, balance) VALUES (d.user_id, d.amount)
    ON CONFLICT (user_id) DO UPDATE SET balance = public.wallets.balance + EXCLUDED.balance, updated_at = now();
  UPDATE public.deposits
    SET status = 'approved',
        paystack_reference = _reference,
        provider = 'paystack',
        admin_notes = COALESCE(admin_notes,'') || ' [auto-verified via Paystack]',
        reviewed_at = now()
    WHERE id = _deposit_id;
END; $$;

REVOKE ALL ON FUNCTION public.credit_deposit_paystack(uuid, text) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.credit_deposit_paystack(uuid, text) FROM anon;
REVOKE ALL ON FUNCTION public.credit_deposit_paystack(uuid, text) FROM authenticated;
GRANT EXECUTE ON FUNCTION public.credit_deposit_paystack(uuid, text) TO service_role;