-- Confirm the phone BEFORE paying (payment page step 1, "Call me now" -> press 1),
-- and "Email me a new link" on an expired link.
--
-- afd_PaymentLink.ResentAt   when "Email me a new link" was used on this (expired) link;
--                            one resend per link.
-- afd_PaymentLink_StatusByToken now also works for a link that is not paid yet:
--   PhoneVerified  the account's phone was confirmed (press 1), so the card form is shown
--   Expired        an unpaid link past its expiry time
--   ResendAllowed  expired less than 24 hours ago and not resent yet
--   LastResult / LastResultAt  the newest press-1 result since the link's first
--                  "Call me now" (before payment) or since the payment (after it)
--   Purpose, CallID  to create the new link with the same details
-- Same columns as 009 otherwise, so the status page after payment works as before.
-- afd_PaymentLink_MarkResent: claims the one resend (Marked = 1 only the first time).
--
-- Run as a DB admin after 012. Safe to run again.

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

DELIMITER ;;

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.Purpose, pl.CallID, pl.Status, pl.UsedAt, pl.SupportPhone,
         (pl.Status = 'pending' AND pl.ExpiresAt <= NOW()) AS Expired,
         (pl.Status = 'pending' AND pl.ExpiresAt <= NOW() AND pl.ExpiresAt >= NOW() - INTERVAL 24 HOUR AND pl.ResentAt IS NULL) AS ResendAllowed,
         IFNULL(tl.AmountCredited, 0) AS AmountCredited,
         IFNULL(r.Risk, '') AS Risk,
         a.Phone, a.FirstName, a.PhoneVerified,
         (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 pv.CreatedAt >= IFNULL(pl.UsedAt, (SELECT MIN(c.CreatedAt) FROM afd_CallRequest c WHERE c.LinkID = pl.ID))
           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 pv.CreatedAt >= IFNULL(pl.UsedAt, (SELECT MIN(c.CreatedAt) FROM afd_CallRequest c WHERE c.LinkID = pl.ID))
           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_PaymentLink_MarkResent`;;
CREATE PROCEDURE `afd_PaymentLink_MarkResent`(IN p_LinkID bigint)
BEGIN
  UPDATE afd_PaymentLink
  SET ResentAt = NOW()
  WHERE ID = p_LinkID AND ResentAt IS NULL AND Status = 'pending'
    AND ExpiresAt <= NOW() AND ExpiresAt >= NOW() - INTERVAL 24 HOUR;
  SELECT ROW_COUNT() AS Marked;
END ;;

DELIMITER ;

-- With per-procedure grants:
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_PaymentLink_MarkResent` TO 'your_user'@'your_host';