-- =====================================================================
-- pay.ezcalls.com adapter — schema for AI Front Desk (afd_) objects
-- =====================================================================
-- Additive only: creates new afd_ tables and procedures in `prepaid`.
-- No existing table, column or procedure is altered.
--
-- Prerequisite (already done 2026-09-29):
--   prepaid.TransactionSource row 'AI Front Desk' = ID 9
--
-- Run as a user that can CREATE TABLE / CREATE ROUTINE in `prepaid`.
-- Safe to re-run: tables use IF NOT EXISTS, procedures are dropped first.
-- =====================================================================

USE `prepaid`;

-- ---------------------------------------------------------------------
-- Tables
-- ---------------------------------------------------------------------

-- Idempotency keys for API requests that create things or move money.
CREATE TABLE IF NOT EXISTS `afd_Idempotency` (
  `IdemKey`      varchar(64)  NOT NULL,
  `Endpoint`     varchar(100) NOT NULL,
  `RequestHash`  char(64)     NOT NULL,
  `Status`       enum('in_progress','done') NOT NULL DEFAULT 'in_progress',
  `HttpStatus`   smallint unsigned NOT NULL DEFAULT 0,
  `ResponseBody` mediumtext NULL,
  `CreatedAt`    datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CompletedAt`  datetime NULL,
  PRIMARY KEY (`IdemKey`),
  KEY `Idx_afd_Idempotency_Created` (`CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Signatures seen in the last few minutes, to reject replayed API requests.
CREATE TABLE IF NOT EXISTS `afd_ApiReplay` (
  `Signature` char(64) NOT NULL,
  `SeenAt`    datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Signature`),
  KEY `Idx_afd_ApiReplay_SeenAt` (`SeenAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Accounts created through the AI, for signup rate limits and auditing.
CREATE TABLE IF NOT EXISTS `afd_Signup` (
  `ID`        bigint unsigned NOT NULL AUTO_INCREMENT,
  `AccountID` bigint unsigned NOT NULL,
  `CallerANI` varchar(20) NOT NULL DEFAULT '',
  `CallID`    varchar(64) NOT NULL DEFAULT '',
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Idx_afd_Signup_ANI` (`CallerANI`, `CreatedAt`),
  KEY `Idx_afd_Signup_Created` (`CreatedAt`),
  KEY `Idx_afd_Signup_Account` (`AccountID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Single-use payment links. Only a SHA-256 hash of the URL token is stored.
CREATE TABLE IF NOT EXISTS `afd_PaymentLink` (
  `ID`               bigint unsigned NOT NULL AUTO_INCREMENT,
  `PublicID`         varchar(32) NOT NULL,
  `TokenHash`        char(64) NOT NULL,
  `AccountID`        bigint unsigned NOT NULL,
  `Amount`           decimal(10,2) NOT NULL,
  `Purpose`          enum('first_payment','recharge') NOT NULL,
  `Status`           enum('pending','used','failed') NOT NULL DEFAULT 'pending',
  `TransactionLogID` bigint unsigned NULL,
  `CallID`           varchar(64) NOT NULL DEFAULT '',
  `CreatedAt`        datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ExpiresAt`        datetime NOT NULL,
  `UsedAt`           datetime NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UnqIdx_afd_PaymentLink_Public` (`PublicID`),
  UNIQUE KEY `UnqIdx_afd_PaymentLink_Token` (`TokenHash`),
  KEY `Idx_afd_PaymentLink_Account` (`AccountID`, `Status`, `ExpiresAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fraud signals per payment. Written by the payment page (phase 3).
-- Risk = 'low' is required before afd_ReleaseHeldCredit releases a payment.
CREATE TABLE IF NOT EXISTS `afd_RiskSignal` (
  `TransactionLogID`    bigint unsigned NOT NULL,
  `AccountID`           bigint unsigned NOT NULL,
  `Risk`                enum('low','review') NOT NULL,
  `ThreeDSStatus`       varchar(60)  NOT NULL DEFAULT '',
  `LiabilityShifted`    tinyint(1)   NOT NULL DEFAULT 0,
  `Bin`                 varchar(8)   NOT NULL DEFAULT '',
  `IssuingBank`         varchar(100) NOT NULL DEFAULT '',
  `IssuingCountry`      varchar(3)   NOT NULL DEFAULT '',
  `CardType`            varchar(20)  NOT NULL DEFAULT '',
  `PrepaidCard`         varchar(10)  NOT NULL DEFAULT '',
  `AvsPostal`           varchar(2)   NOT NULL DEFAULT '',
  `AvsStreet`           varchar(2)   NOT NULL DEFAULT '',
  `Cvv`                 varchar(2)   NOT NULL DEFAULT '',
  `IpAddress`           varchar(45)  NOT NULL DEFAULT '',
  `IpState`             varchar(10)  NOT NULL DEFAULT '',
  `PhoneState`          varchar(10)  NOT NULL DEFAULT '',
  `CardFingerprint`     varchar(64)  NOT NULL DEFAULT '',
  `CardOnOtherAccounts` int unsigned NOT NULL DEFAULT 0,
  `Reasons`             varchar(1000) NOT NULL DEFAULT '',
  `CreatedAt`           datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TransactionLogID`),
  KEY `Idx_afd_RiskSignal_Account` (`AccountID`),
  KEY `Idx_afd_RiskSignal_Fingerprint` (`CardFingerprint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every outbound verification call result, for auditing.
CREATE TABLE IF NOT EXISTS `afd_PhoneVerification` (
  `ID`        bigint unsigned NOT NULL AUTO_INCREMENT,
  `AccountID` bigint unsigned NOT NULL,
  `Phone`     varchar(20) NOT NULL,
  `Result`    enum('confirmed','denied','no_answer','voicemail','no_input') NOT NULL,
  `CallID`    varchar(64) NOT NULL DEFAULT '',
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Idx_afd_PhoneVerification_Account` (`AccountID`, `CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Procedures
-- ---------------------------------------------------------------------

DELIMITER ;;

-- Idempotency -----------------------------------------------------------

DROP PROCEDURE IF EXISTS `afd_Idempotency_Begin`;;
CREATE PROCEDURE `afd_Idempotency_Begin`(IN p_Key varchar(64), IN p_Endpoint varchar(100), IN p_Hash char(64))
BEGIN
  DECLARE v_Inserted int DEFAULT 0;

  INSERT IGNORE INTO afd_Idempotency (IdemKey, Endpoint, RequestHash)
  VALUES (p_Key, p_Endpoint, p_Hash);
  SET v_Inserted = ROW_COUNT();

  SELECT v_Inserted AS Inserted, Endpoint, RequestHash, Status, HttpStatus, ResponseBody, CreatedAt
  FROM afd_Idempotency
  WHERE IdemKey = p_Key;
END ;;

DROP PROCEDURE IF EXISTS `afd_Idempotency_Complete`;;
CREATE PROCEDURE `afd_Idempotency_Complete`(IN p_Key varchar(64), IN p_HttpStatus smallint, IN p_Body mediumtext)
BEGIN
  UPDATE afd_Idempotency
  SET Status = 'done', HttpStatus = p_HttpStatus, ResponseBody = p_Body, CompletedAt = NOW()
  WHERE IdemKey = p_Key;
END ;;

-- Only for requests that failed before anything was created or charged,
-- so the client may retry with the same key.
DROP PROCEDURE IF EXISTS `afd_Idempotency_Abandon`;;
CREATE PROCEDURE `afd_Idempotency_Abandon`(IN p_Key varchar(64))
BEGIN
  DELETE FROM afd_Idempotency WHERE IdemKey = p_Key AND Status = 'in_progress';
END ;;

-- Replay protection ----------------------------------------------------

DROP PROCEDURE IF EXISTS `afd_ApiReplay_Check`;;
CREATE PROCEDURE `afd_ApiReplay_Check`(IN p_Signature char(64))
BEGIN
  DECLARE v_Fresh int DEFAULT 0;

  DELETE FROM afd_ApiReplay WHERE SeenAt < NOW() - INTERVAL 15 MINUTE LIMIT 1000;

  INSERT IGNORE INTO afd_ApiReplay (Signature) VALUES (p_Signature);
  SET v_Fresh = ROW_COUNT();

  SELECT v_Fresh AS Fresh;
END ;;

-- Registration ---------------------------------------------------------
-- Differs from ez_Account_Insert on purpose:
--   * the login number is generated by the adapter (non-dialable, leading 0),
--     so Pin = LoginNumber + PIN is not a real phone number
--   * email may be NULL (the unique index allows many NULLs, not many '')
-- Like ez_Account_Insert, a US/Canada number (p_RegisteredNumber) is added to
-- ANI for pinless calling, with Verified = 0. Held credit keeps the balance at
-- 0 until the verification call; a "not me" answer removes the ANI row.
-- Like the website, it refuses a phone number that is already on any account
-- in the partition (active or not): as a pinless number (ANI), as the
-- RegisteredNumber, or as the contact Phone (compared on digits only).
-- p_Phone: US/Canada numbers as 10 digits without the leading 1.
-- p_RegisteredNumber: the same 10 digits for US/Canada, '' otherwise
-- (the column is varchar(10)).
-- Duplicate login numbers raise MySQL error 1062; the adapter retries.
DROP PROCEDURE IF EXISTS `afd_Account_Insert`;;
CREATE PROCEDURE `afd_Account_Insert`(
  IN p_AccountTypeID int, IN p_PartitionID int, IN p_ProductID int, IN p_Active int,
  IN p_LoginNumber varchar(10), IN p_Pin varchar(4), IN p_Password varchar(15),
  IN p_FirstName varchar(20), IN p_LastName varchar(20), IN p_Email varchar(100),
  IN p_Phone varchar(20), IN p_RegisteredNumber varchar(10),
  IN p_CallerANI varchar(20), IN p_CallID varchar(64),
  IN p_MaxPerANI24h int, IN p_MaxPerHour int)
BEGIN
  DECLARE v_AniCount int DEFAULT 0;
  DECLARE v_HourCount int DEFAULT 0;
  DECLARE v_AccountID bigint;

  -- Account + ANI + afd_Signup are written together or not at all.
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF p_Phone <> '' AND (
       EXISTS (SELECT 1 FROM ANI WHERE ANI = p_Phone AND PartitionID = p_PartitionID)
    OR EXISTS (SELECT 1 FROM Account
               WHERE PartitionID = p_PartitionID
                 AND (RegisteredNumber = p_Phone
                      OR REGEXP_REPLACE(Phone, '[^0-9]', '') IN (p_Phone, CONCAT('1', p_Phone))))
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:phone_in_use';
  END IF;

  IF p_CallerANI <> '' THEN
    SELECT COUNT(*) INTO v_AniCount
    FROM afd_Signup
    WHERE CallerANI = p_CallerANI AND CreatedAt >= NOW() - INTERVAL 24 HOUR;
  END IF;

  SELECT COUNT(*) INTO v_HourCount
  FROM afd_Signup
  WHERE CreatedAt >= NOW() - INTERVAL 1 HOUR;

  IF v_AniCount >= p_MaxPerANI24h THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:signup_limit_ani';
  END IF;
  IF v_HourCount >= p_MaxPerHour THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:signup_limit_global';
  END IF;

  START TRANSACTION;

  INSERT INTO Account (AccountTypeID, PartitionID, ProductID, Pin, Active, Denomination, Balance,
                       Email, Password, FirstName, LastName, Phone, RegisteredNumber, SecurityCode,
                       PromoCode, HearedFrom, Referrer, IPAddress)
  VALUES (p_AccountTypeID, p_PartitionID, p_ProductID, CONCAT(p_LoginNumber, p_Pin), p_Active, 0, 0,
          NULLIF(p_Email, ''), p_Password, p_FirstName, p_LastName, p_Phone, p_RegisteredNumber, p_Pin,
          '', 'AI Front Desk', 'AI Front Desk', '');

  SET v_AccountID = LAST_INSERT_ID();

  IF p_RegisteredNumber <> '' THEN
    INSERT INTO ANI (PartitionID, AccountID, ANI)
    VALUES (p_PartitionID, v_AccountID, p_RegisteredNumber);
  END IF;

  INSERT INTO afd_Signup (AccountID, CallerANI, CallID) VALUES (v_AccountID, p_CallerANI, p_CallID);

  COMMIT;

  SELECT v_AccountID AS AccountID;
END ;;

-- Saved-card recharge log ---------------------------------------------
-- Like telnyx_InsertTransactionLog, but records TransactionSourceID = p_SourceID
-- (AI Front Desk) and the fee, copying billing details from the most recent
-- log that used the same Braintree token. Returns 0 if no such log exists.
DROP PROCEDURE IF EXISTS `afd_InsertTransactionLog`;;
CREATE PROCEDURE `afd_InsertTransactionLog`(
  IN p_AccountID bigint, IN p_Amount double, IN p_Fee double,
  IN p_BTToken varchar(45), IN p_SourceID tinyint)
BEGIN
  INSERT INTO TransactionLog (AccountID, TransactionSourceID, CCNumber, FirstName, LastName, Phone,
                              Address, City, State, ZipCode, Country, Email, AmountToCharge, Fee,
                              BTToken, Description)
  SELECT p_AccountID, p_SourceID, CCNumber, FirstName, LastName, Phone,
         Address, City, State, ZipCode, Country, Email, p_Amount, p_Fee,
         p_BTToken, 'AI Front Desk recharge'
  FROM TransactionLog
  WHERE AccountID = p_AccountID AND BTToken = p_BTToken
  ORDER BY DateInserted DESC
  LIMIT 1;

  IF ROW_COUNT() = 1 THEN
    SELECT LAST_INSERT_ID() AS TransactionLogID;
  ELSE
    SELECT 0 AS TransactionLogID;
  END IF;
END ;;

-- Release held credit --------------------------------------------------
-- Replaces websp_VerifyAndCreditAccount for AI payments:
--   * locks the account row, so two concurrent calls cannot credit twice
--   * releases only approved, not-yet-credited logs from p_SourceID
--     (website payments stay with staff) that have a 'low' risk signal
--   * if any such payment is flagged 'review', releases nothing and leaves
--     PhoneVerified unchanged
DROP PROCEDURE IF EXISTS `afd_ReleaseHeldCredit`;;
CREATE PROCEDURE `afd_ReleaseHeldCredit`(IN p_AccountID bigint, IN p_SourceID tinyint)
BEGIN
  DECLARE v_Balance decimal(10,5);
  DECLARE v_Review int DEFAULT 0;
  DECLARE v_Total decimal(10,5) DEFAULT 0;
  DECLARE v_Count int DEFAULT 0;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  SELECT Balance INTO v_Balance FROM Account WHERE ID = p_AccountID FOR UPDATE;

  SELECT COUNT(*) INTO v_Review
  FROM TransactionLog tl
  INNER JOIN afd_RiskSignal r ON r.TransactionLogID = tl.ID
  WHERE tl.AccountID = p_AccountID
    AND tl.TransactionSourceID = p_SourceID
    AND tl.ChargeStatusID = 2
    AND tl.AmountCredited = 0
    AND r.Risk = 'review';

  IF v_Review > 0 THEN
    COMMIT;
    SELECT 0 AS ReleasedAmount, 0 AS ReleasedCount, 1 AS HeldForReview;
  ELSE
    SELECT IFNULL(SUM(t.Amt), 0), COUNT(*) INTO v_Total, v_Count
    FROM TransactionLog tl
    INNER JOIN afd_RiskSignal r ON r.TransactionLogID = tl.ID AND r.Risk = 'low'
    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;

    UPDATE TransactionLog tl
    INNER JOIN afd_RiskSignal r ON r.TransactionLogID = tl.ID AND r.Risk = 'low'
    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
    SET tl.AmountCredited = t.Amt
    WHERE tl.AccountID = p_AccountID
      AND tl.TransactionSourceID = p_SourceID
      AND tl.ChargeStatusID = 2
      AND tl.AmountCredited = 0
      AND t.Amt > 0;

    UPDATE Account SET Balance = Balance + v_Total, PhoneVerified = 1 WHERE ID = p_AccountID;

    COMMIT;
    SELECT v_Total AS ReleasedAmount, v_Count AS ReleasedCount, 0 AS HeldForReview;
  END IF;
END ;;

-- Phone verification audit --------------------------------------------

DROP PROCEDURE IF EXISTS `afd_PhoneVerification_Insert`;;
CREATE PROCEDURE `afd_PhoneVerification_Insert`(
  IN p_AccountID bigint, IN p_Phone varchar(20), IN p_Result varchar(20), IN p_CallID varchar(64))
BEGIN
  INSERT INTO afd_PhoneVerification (AccountID, Phone, Result, CallID)
  VALUES (p_AccountID, p_Phone, p_Result, p_CallID);
END ;;

-- Payment links --------------------------------------------------------

DROP PROCEDURE IF EXISTS `afd_PaymentLink_Insert`;;
CREATE PROCEDURE `afd_PaymentLink_Insert`(
  IN p_PublicID varchar(32), IN p_TokenHash char(64), IN p_AccountID bigint,
  IN p_Amount decimal(10,2), IN p_Purpose varchar(20), IN p_ExpiresMinutes int,
  IN p_CallID varchar(64), IN p_MaxOpenLinks int)
BEGIN
  DECLARE v_Open int DEFAULT 0;

  SELECT COUNT(*) INTO v_Open
  FROM afd_PaymentLink
  WHERE AccountID = p_AccountID AND Status = 'pending' AND ExpiresAt > NOW();

  IF v_Open >= p_MaxOpenLinks THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:too_many_open_links';
  END IF;

  INSERT INTO afd_PaymentLink (PublicID, TokenHash, AccountID, Amount, Purpose, CallID, ExpiresAt)
  VALUES (p_PublicID, p_TokenHash, p_AccountID, p_Amount, p_Purpose, p_CallID,
          NOW() + INTERVAL p_ExpiresMinutes MINUTE);

  SELECT PublicID, ExpiresAt FROM afd_PaymentLink WHERE ID = LAST_INSERT_ID();
END ;;

DROP PROCEDURE IF EXISTS `afd_PaymentLink_SelectByPublicID`;;
CREATE PROCEDURE `afd_PaymentLink_SelectByPublicID`(IN p_PublicID varchar(32))
BEGIN
  SELECT pl.PublicID, pl.AccountID, pl.Amount, pl.Purpose, pl.Status, pl.ExpiresAt,
         (pl.Status = 'pending' AND pl.ExpiresAt <= NOW()) AS Expired,
         pl.TransactionLogID,
         IFNULL(tl.AmountCredited, 0) AS AmountCredited,
         IFNULL(r.Risk, '') AS Risk
  FROM afd_PaymentLink pl
  LEFT JOIN TransactionLog tl ON tl.ID = pl.TransactionLogID
  LEFT JOIN afd_RiskSignal r ON r.TransactionLogID = pl.TransactionLogID
  WHERE pl.PublicID = p_PublicID;
END ;;

DELIMITER ;