CREATE OR REPLACE FUNCTION public.debit_wallet(_user_id uuid, _amount numeric, _reference text, _type text, _description text, _metadata jsonb DEFAULT '{}'::jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  bal numeric;
  newbal numeric;
  tx_id uuid;
BEGIN
  IF _amount IS NULL OR _amount <= 0 THEN
    RAISE EXCEPTION 'Invalid debit amount';
  END IF;

  SELECT balance INTO bal FROM public.wallets WHERE user_id = _user_id FOR UPDATE;
  IF bal IS NULL THEN RAISE EXCEPTION 'Wallet not found'; END IF;
  IF bal < _amount THEN RAISE EXCEPTION 'Insufficient wallet balance'; END IF;

  newbal := bal - _amount;
  UPDATE public.wallets SET balance = newbal, updated_at = now() WHERE user_id = _user_id;

  INSERT INTO public.transactions (
    user_id, type, status, amount, balance_before, balance_after,
    reference, provider, description, metadata
  ) VALUES (
    _user_id, _type::public.tx_type, 'pending'::public.tx_status, _amount, bal, newbal,
    _reference, 'billsline', _description, coalesce(_metadata, '{}'::jsonb)
  ) RETURNING id INTO tx_id;

  RETURN jsonb_build_object('transaction_id', tx_id, 'balance_before', bal, 'balance_after', newbal);
END;
$$;

CREATE OR REPLACE FUNCTION public.credit_wallet(_user_id uuid, _amount numeric, _reference text, _external_reference text, _provider text, _description text, _type text DEFAULT 'funding'::text, _metadata jsonb DEFAULT '{}'::jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  bal numeric;
  newbal numeric;
  existing_id uuid;
  pend record;
BEGIN
  IF _amount IS NULL OR _amount <= 0 THEN
    RAISE EXCEPTION 'Invalid credit amount';
  END IF;

  IF _external_reference IS NOT NULL THEN
    SELECT id INTO existing_id FROM public.transactions
      WHERE external_reference = _external_reference LIMIT 1;
    IF existing_id IS NOT NULL THEN
      RETURN jsonb_build_object('credited', false, 'duplicate', true, 'transaction_id', existing_id);
    END IF;
  END IF;

  SELECT balance INTO bal FROM public.wallets WHERE user_id = _user_id FOR UPDATE;
  IF bal IS NULL THEN
    INSERT INTO public.wallets (user_id, balance) VALUES (_user_id, 0)
      ON CONFLICT (user_id) DO NOTHING;
    SELECT balance INTO bal FROM public.wallets WHERE user_id = _user_id FOR UPDATE;
    bal := coalesce(bal, 0);
  END IF;

  newbal := bal + _amount;
  UPDATE public.wallets SET balance = newbal, updated_at = now() WHERE user_id = _user_id;

  SELECT * INTO pend FROM public.transactions
    WHERE reference = _reference AND user_id = _user_id LIMIT 1;

  IF pend.id IS NOT NULL THEN
    IF pend.status::text = 'success' THEN
      UPDATE public.wallets SET balance = bal, updated_at = now() WHERE user_id = _user_id;
      RETURN jsonb_build_object('credited', false, 'duplicate', true, 'transaction_id', pend.id);
    END IF;
    UPDATE public.transactions SET
      status = 'success'::public.tx_status,
      amount = _amount,
      balance_before = bal,
      balance_after = newbal,
      external_reference = coalesce(_external_reference, external_reference),
      provider = coalesce(_provider, provider),
      description = coalesce(_description, description),
      metadata = coalesce(_metadata, metadata),
      updated_at = now()
    WHERE id = pend.id;
    RETURN jsonb_build_object('credited', true, 'duplicate', false, 'transaction_id', pend.id, 'balance', newbal);
  END IF;

  INSERT INTO public.transactions (
    user_id, type, status, amount, balance_before, balance_after,
    reference, external_reference, provider, description, metadata
  ) VALUES (
    _user_id, _type::public.tx_type, 'success'::public.tx_status, _amount, bal, newbal,
    _reference, _external_reference, _provider, _description, coalesce(_metadata, '{}'::jsonb)
  ) RETURNING id INTO existing_id;

  RETURN jsonb_build_object('credited', true, 'duplicate', false, 'transaction_id', existing_id, 'balance', newbal);
END;
$$;

CREATE OR REPLACE FUNCTION public.refund_wallet(_user_id uuid, _amount numeric, _reference text, _reason text)
RETURNS numeric
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE bal numeric; newbal numeric; st text;
BEGIN
  SELECT status::text INTO st FROM public.transactions WHERE reference = _reference AND user_id = _user_id;
  IF st IS NULL OR st = 'failed' OR st = 'success' THEN
    RETURN NULL;
  END IF;

  SELECT balance INTO bal FROM public.wallets WHERE user_id = _user_id FOR UPDATE;
  newbal := coalesce(bal, 0) + _amount;
  UPDATE public.wallets SET balance = newbal, updated_at = now() WHERE user_id = _user_id;
  UPDATE public.transactions SET status = 'failed'::public.tx_status,
    description = coalesce(_reason, description), balance_after = newbal, updated_at = now()
    WHERE reference = _reference AND user_id = _user_id;
  RETURN newbal;
END;
$$;