CREATE OR REPLACE FUNCTION public.credit_deposit_gateway(_deposit_id uuid, _reference text, _provider text DEFAULT 'printpay')
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
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',
        mpesa_reference = COALESCE(NULLIF(_reference,''), mpesa_reference),
        provider = _provider,
        admin_notes = COALESCE(admin_notes,'') || ' [auto-verified via ' || _provider || ']',
        reviewed_at = now()
    WHERE id = _deposit_id;
END; $function$;

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