-- =====================================================================
-- Update 008 (2026-10-11): how much money is waiting on an account.
-- Additive only: one new procedure. Safe to re-run.
--
-- A charged payment (ChargeStatusID = 2) whose TransactionLog.AmountCredited
-- is still 0 has not been credited yet: the verification call ("press 1")
-- has not succeeded. Same rows as afd_ReleaseHeldCredit (AI Front Desk
-- source, risk signal), so "waiting" is exactly what pressing 1 releases:
--   HeldAmount     low-risk payments released by pressing 1
--   ReviewAmount   payments flagged for staff review (pressing 1 releases nothing)
-- Website payments are not included: staff verify and credit those.
-- =====================================================================

USE `prepaid`;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_HeldCredit_Select`;;
CREATE PROCEDURE `afd_HeldCredit_Select`(IN p_AccountID bigint, IN p_SourceID tinyint)
BEGIN
  SELECT IFNULL(SUM(CASE WHEN r.Risk = 'low' THEN t.Amt ELSE 0 END), 0)    AS HeldAmount,
         IFNULL(SUM(CASE WHEN r.Risk = 'review' THEN t.Amt ELSE 0 END), 0) AS ReviewAmount
  FROM TransactionLog tl
  INNER JOIN afd_RiskSignal r ON r.TransactionLogID = tl.ID
  INNER JOIN (SELECT TransactionLogID, SUM(Amount) AS Amt
              FROM Transactions
              WHERE AccountID = p_AccountID AND TransactionTypeID = 3
              GROUP BY TransactionLogID) t ON t.TransactionLogID = tl.ID
  WHERE tl.AccountID = p_AccountID
    AND tl.TransactionSourceID = p_SourceID
    AND tl.ChargeStatusID = 2
    AND tl.AmountCredited = 0
    AND t.Amt > 0;
END ;;

DELIMITER ;

-- Only if your DB user's rights are granted procedure by procedure:
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_HeldCredit_Select` TO 'your_user'@'your_host';
