-- =====================================================================
-- Update 004 (2026-09-30): payment page (phase 3). Additive only.
--   * afd_PaymentLink: attempt counter + 'processing' status
--   * procedures used by the payment page at /p/{token}
-- Run after 001 and 003. Safe to re-run.
-- =====================================================================

USE `prepaid`;

-- ---------------------------------------------------------------------
-- afd_PaymentLink changes (our own table)
-- ---------------------------------------------------------------------

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = 'prepaid' AND TABLE_NAME = 'afd_PaymentLink' AND COLUMN_NAME = 'Attempts');
SET @sql := IF(@col = 0,
  'ALTER TABLE afd_PaymentLink ADD COLUMN Attempts tinyint unsigned NOT NULL DEFAULT 0 AFTER Status',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

ALTER TABLE afd_PaymentLink
  MODIFY COLUMN `Status` enum('pending','processing','used','failed') NOT NULL DEFAULT 'pending';


DELIMITER ;;

-- Page view: link details by URL token hash. Never returns account data
-- beyond the first name used in the greeting.
DROP PROCEDURE IF EXISTS `afd_PaymentLink_SelectByToken`;;
CREATE PROCEDURE `afd_PaymentLink_SelectByToken`(IN p_TokenHash char(64))
BEGIN
  SELECT pl.PublicID, pl.AccountID, pl.Amount, pl.Purpose, pl.Status, pl.Attempts,
         (pl.ExpiresAt <= NOW()) AS Expired,
         a.FirstName, a.Phone, a.PhoneVerified, a.Active
  FROM afd_PaymentLink pl
  INNER JOIN Account a ON a.ID = pl.AccountID
  WHERE pl.TokenHash = p_TokenHash;
END ;;

-- Claims the link for one payment attempt. Only one attempt can run at a time
-- (pending -> processing). Returns the link row, or no row if the link is
-- unknown, expired, already used/failed, busy, or out of attempts.
DROP PROCEDURE IF EXISTS `afd_PaymentLink_BeginPayment`;;
CREATE PROCEDURE `afd_PaymentLink_BeginPayment`(IN p_TokenHash char(64), IN p_MaxAttempts int)
BEGIN
  UPDATE afd_PaymentLink
  SET Status = 'processing', Attempts = Attempts + 1
  WHERE TokenHash = p_TokenHash
    AND Status = 'pending'
    AND ExpiresAt > NOW()
    AND Attempts < p_MaxAttempts;

  IF ROW_COUNT() = 1 THEN
    SELECT pl.PublicID, pl.AccountID, pl.Amount, pl.Purpose, pl.Attempts
    FROM afd_PaymentLink pl
    WHERE pl.TokenHash = p_TokenHash;
  END IF;
END ;;

-- Ends an attempt: 'used' (paid), 'retry' (declined, may try again),
-- 'failed' (declined and out of attempts, or blocked).
DROP PROCEDURE IF EXISTS `afd_PaymentLink_FinishPayment`;;
CREATE PROCEDURE `afd_PaymentLink_FinishPayment`(
  IN p_PublicID varchar(32), IN p_Outcome varchar(10), IN p_TransactionLogID bigint)
BEGIN
  UPDATE afd_PaymentLink
  SET Status = CASE p_Outcome WHEN 'used' THEN 'used' WHEN 'retry' THEN 'pending' ELSE 'failed' END,
      TransactionLogID = IF(p_TransactionLogID > 0, p_TransactionLogID, TransactionLogID),
      UsedAt = IF(p_Outcome = 'used', NOW(), UsedAt)
  WHERE PublicID = p_PublicID AND Status = 'processing';
END ;;

-- Card payment log for a payment-link attempt (TransactionSourceID = AI Front Desk).
-- Card details are filled in afterwards by ez_UpdateTransactionLog_BT.
DROP PROCEDURE IF EXISTS `afd_InsertTransactionLogCard`;;
CREATE PROCEDURE `afd_InsertTransactionLogCard`(
  IN p_AccountID bigint, IN p_Amount double, IN p_Fee double, IN p_SourceID tinyint,
  IN p_FirstName varchar(50), IN p_LastName varchar(50), IN p_Phone varchar(25),
  IN p_ZipCode varchar(20), IN p_IPAddress varchar(15))
BEGIN
  INSERT INTO TransactionLog (AccountID, TransactionSourceID, FirstName, LastName, Phone, ZipCode,
                              AmountToCharge, Fee, IPAddress, Description)
  VALUES (p_AccountID, p_SourceID, p_FirstName, p_LastName, p_Phone, p_ZipCode,
          p_Amount, p_Fee, p_IPAddress, 'AI Front Desk payment link');

  SELECT LAST_INSERT_ID() AS TransactionLogID;
END ;;

-- How many OTHER accounts have used this card: by Braintree's unique card
-- identifier (AI payments) or by masked number = first 6 + last 4 (all payments).
DROP PROCEDURE IF EXISTS `afd_CardSeenOnOtherAccounts`;;
CREATE PROCEDURE `afd_CardSeenOnOtherAccounts`(
  IN p_Fingerprint varchar(64), IN p_MaskedNumber varchar(16), IN p_AccountID bigint)
BEGIN
  SELECT COUNT(DISTINCT AccountID) AS OtherAccounts
  FROM (
    SELECT AccountID FROM afd_RiskSignal
    WHERE p_Fingerprint <> '' AND CardFingerprint = p_Fingerprint AND AccountID <> p_AccountID
    UNION
    SELECT AccountID FROM TransactionLog
    WHERE p_MaskedNumber <> '' AND CCNumber = p_MaskedNumber AND AccountID <> p_AccountID
  ) seen;
END ;;

DROP PROCEDURE IF EXISTS `afd_RiskSignal_Insert`;;
CREATE PROCEDURE `afd_RiskSignal_Insert`(
  IN p_TransactionLogID bigint, IN p_AccountID bigint, IN p_Risk varchar(10),
  IN p_ThreeDSStatus varchar(60), IN p_LiabilityShifted tinyint,
  IN p_Bin varchar(8), IN p_IssuingBank varchar(100), IN p_IssuingCountry varchar(3),
  IN p_CardType varchar(20), IN p_PrepaidCard varchar(10),
  IN p_AvsPostal varchar(2), IN p_AvsStreet varchar(2), IN p_Cvv varchar(2),
  IN p_IpAddress varchar(45), IN p_IpState varchar(10), IN p_PhoneState varchar(10),
  IN p_CardFingerprint varchar(64), IN p_CardOnOtherAccounts int, IN p_Reasons varchar(1000))
BEGIN
  INSERT INTO afd_RiskSignal (TransactionLogID, AccountID, Risk, ThreeDSStatus, LiabilityShifted,
                              Bin, IssuingBank, IssuingCountry, CardType, PrepaidCard,
                              AvsPostal, AvsStreet, Cvv, IpAddress, IpState, PhoneState,
                              CardFingerprint, CardOnOtherAccounts, Reasons)
  VALUES (p_TransactionLogID, p_AccountID, p_Risk, p_ThreeDSStatus, p_LiabilityShifted,
          p_Bin, p_IssuingBank, p_IssuingCountry, p_CardType, p_PrepaidCard,
          p_AvsPostal, p_AvsStreet, p_Cvv, p_IpAddress, p_IpState, p_PhoneState,
          p_CardFingerprint, p_CardOnOtherAccounts, p_Reasons);
END ;;

DELIMITER ;

-- Only if your DB user's rights are granted procedure by procedure (not EXECUTE ON prepaid.*):
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_PaymentLink_SelectByToken`  TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_PaymentLink_BeginPayment`   TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_PaymentLink_FinishPayment`  TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_InsertTransactionLogCard`   TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_CardSeenOnOtherAccounts`    TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_RiskSignal_Insert`          TO 'your_user'@'your_host';