-- =====================================================================
-- Update 009 (2026-10-11): status page after a payment, with a
-- "Call me now" button for the verification call.
-- Additive only: one new table, one new column and new procedures. Safe to re-run.
-- afd_PaymentLink.SupportPhone: the number the page and email show for help.
-- ai-front-desk sends it with each link (per tenant), so nothing is hard-coded here.
--
-- The payment link keeps working for payment_page.status_hours (default 24)
-- after the payment: it then shows whether the money was added, or a button
-- that asks ai-front-desk to call the account's phone again (press 1).
-- The automatic call right after the payment is call 1; each button press
-- is logged here and counts toward payment_page.max_calls (default 3).
-- =====================================================================

USE `prepaid`;

CREATE TABLE IF NOT EXISTS `afd_CallRequest` (
  `ID`        bigint unsigned NOT NULL AUTO_INCREMENT,
  `LinkID`    bigint unsigned NOT NULL,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Idx_afd_CallRequest_Link` (`LinkID`, `CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @col := (SELECT COUNT(*) FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'afd_PaymentLink' AND COLUMN_NAME = 'SupportPhone');
SET @sql := IF(@col = 0, 'ALTER TABLE afd_PaymentLink ADD COLUMN SupportPhone varchar(20) NOT NULL DEFAULT '''' AFTER CallID', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_PaymentLink_SetSupportPhone`;;
CREATE PROCEDURE `afd_PaymentLink_SetSupportPhone`(IN p_PublicID varchar(32), IN p_Phone varchar(20))
BEGIN
  UPDATE afd_PaymentLink SET SupportPhone = p_Phone WHERE PublicID = p_PublicID;
END ;;

-- Everything the status page needs, by link token. Times are the database's
-- own clock (DbNow), so they compare correctly whatever the PHP time zone is.
DROP PROCEDURE IF EXISTS `afd_PaymentLink_StatusByToken`;;
CREATE PROCEDURE `afd_PaymentLink_StatusByToken`(IN p_TokenHash char(64))
BEGIN
  SELECT pl.ID AS LinkID, pl.PublicID, pl.AccountID, pl.Amount, pl.Status, pl.UsedAt, pl.SupportPhone,
         IFNULL(tl.AmountCredited, 0) AS AmountCredited,
         IFNULL(r.Risk, '') AS Risk,
         a.Phone, a.FirstName,
         (SELECT COUNT(*) FROM afd_CallRequest c WHERE c.LinkID = pl.ID) AS CallRequests,
         (SELECT MAX(c.CreatedAt) FROM afd_CallRequest c WHERE c.LinkID = pl.ID) AS LastRequestAt,
         (SELECT pv.Result FROM afd_PhoneVerification pv
           WHERE pv.AccountID = pl.AccountID AND pl.UsedAt IS NOT NULL AND pv.CreatedAt >= pl.UsedAt
           ORDER BY pv.CreatedAt DESC, pv.ID DESC LIMIT 1) AS LastResult,
         (SELECT pv.CreatedAt FROM afd_PhoneVerification pv
           WHERE pv.AccountID = pl.AccountID AND pl.UsedAt IS NOT NULL AND pv.CreatedAt >= pl.UsedAt
           ORDER BY pv.CreatedAt DESC, pv.ID DESC LIMIT 1) AS LastResultAt,
         NOW() AS DbNow
  FROM afd_PaymentLink pl
  INNER JOIN Account a ON a.ID = pl.AccountID
  LEFT JOIN TransactionLog tl ON tl.ID = pl.TransactionLogID
  LEFT JOIN afd_RiskSignal r ON r.TransactionLogID = pl.TransactionLogID
  WHERE pl.TokenHash = p_TokenHash;
END ;;

DROP PROCEDURE IF EXISTS `afd_CallRequest_Insert`;;
CREATE PROCEDURE `afd_CallRequest_Insert`(IN p_LinkID bigint)
BEGIN
  INSERT INTO afd_CallRequest (LinkID) VALUES (p_LinkID);
  SELECT LAST_INSERT_ID() AS RequestID;
END ;;

DROP PROCEDURE IF EXISTS `afd_CallRequest_Delete`;;
CREATE PROCEDURE `afd_CallRequest_Delete`(IN p_RequestID bigint)
BEGIN
  DELETE FROM afd_CallRequest WHERE ID = p_RequestID;
END ;;

DELIMITER ;

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