-- =====================================================================
-- Update 005 (2026-10-02): email verification for phone sign-ups.
--   * afd_EmailVerification: 6-digit codes emailed during the call
--     (only a hash is stored; 10 minutes; 3 tries)
--   * afd_Account_Insert replaced: needs a verified, unused code for the
--     same email and creates the account with EmailVerified = 1
--     (new last parameter p_EmailVerificationID — upload the new adapter
--     code together with this script)
-- Does not use ez_GetEmailVerificationCode / ez_VerifyEmailVerificationCode:
-- those keep the code in Account (so the account must exist first) and
-- the code is kept in plain text with no limit on wrong tries (24 hours).
-- Run after 001-004. Safe to re-run.
-- =====================================================================

USE `prepaid`;

CREATE TABLE IF NOT EXISTS `afd_EmailVerification` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `PublicID` varchar(32) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `CodeHash` char(64) NOT NULL,
  `CallID` varchar(64) NOT NULL DEFAULT '',
  `Attempts` tinyint unsigned NOT NULL DEFAULT 0,
  `ExpiresAt` datetime NOT NULL,
  `VerifiedAt` datetime DEFAULT NULL,
  `UsedAt` datetime DEFAULT NULL,
  `AccountID` bigint unsigned DEFAULT NULL,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UnqIdx_afd_EmailVerification_PublicID` (`PublicID`),
  KEY `Idx_afd_EmailVerification_Email` (`Email`, `CreatedAt`),
  KEY `Idx_afd_EmailVerification_CallID` (`CallID`, `CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DELIMITER ;;

-- New code. Refuses (afd:email_code_limit) when this call or this email has
-- had too many codes recently, so the endpoint can't be used to spam an inbox,
-- and (afd:email_in_use) when the address is already on an account.
DROP PROCEDURE IF EXISTS `afd_EmailVerification_Insert`;;
CREATE PROCEDURE `afd_EmailVerification_Insert`(
  IN p_PublicID varchar(32), IN p_Email varchar(100), IN p_CodeHash char(64),
  IN p_CallID varchar(64), IN p_ExpiresMinutes int,
  IN p_MaxPerCall int, IN p_MaxPerEmailHour int)
BEGIN
  DECLARE v_PerCall int DEFAULT 0;
  DECLARE v_PerEmail int DEFAULT 0;

  SELECT COUNT(*) INTO v_PerCall
  FROM afd_EmailVerification
  WHERE CallID = p_CallID AND CreatedAt >= NOW() - INTERVAL 1 HOUR;

  SELECT COUNT(*) INTO v_PerEmail
  FROM afd_EmailVerification
  WHERE Email = p_Email AND CreatedAt >= NOW() - INTERVAL 1 HOUR;

  IF v_PerCall >= p_MaxPerCall OR v_PerEmail >= p_MaxPerEmailHour THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:email_code_limit';
  END IF;

  -- Account.Email is unique: no point verifying an address that can't be used.
  IF EXISTS (SELECT 1 FROM Account WHERE Email = p_Email) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:email_in_use';
  END IF;

  INSERT INTO afd_EmailVerification (PublicID, Email, CodeHash, CallID, ExpiresAt)
  VALUES (p_PublicID, p_Email, p_CodeHash, p_CallID, NOW() + INTERVAL p_ExpiresMinutes MINUTE);

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

-- Checks a typed code. Result: verified | wrong | expired | too_many_attempts | not_found.
-- A wrong code uses up one try; a verified code stays verified (re-checks are harmless).
DROP PROCEDURE IF EXISTS `afd_EmailVerification_Check`;;
CREATE PROCEDURE `afd_EmailVerification_Check`(
  IN p_PublicID varchar(32), IN p_CodeHash char(64), IN p_MaxAttempts int)
BEGIN
  DECLARE v_ID bigint;
  DECLARE v_Hash char(64);
  DECLARE v_Attempts int;
  DECLARE v_Expired int;
  DECLARE v_Verified int;
  DECLARE v_Result varchar(20);

  START TRANSACTION;

  SELECT ID, CodeHash, Attempts, (ExpiresAt <= NOW()), (VerifiedAt IS NOT NULL)
  INTO v_ID, v_Hash, v_Attempts, v_Expired, v_Verified
  FROM afd_EmailVerification
  WHERE PublicID = p_PublicID
  FOR UPDATE;

  IF v_ID IS NULL THEN
    SET v_Result = 'not_found';
  ELSEIF v_Verified = 1 THEN
    SET v_Result = 'verified';
  ELSEIF v_Attempts >= p_MaxAttempts THEN
    SET v_Result = 'too_many_attempts';
  ELSEIF v_Expired = 1 THEN
    SET v_Result = 'expired';
  ELSE
    UPDATE afd_EmailVerification SET Attempts = Attempts + 1 WHERE ID = v_ID;
    SET v_Attempts = v_Attempts + 1;
    IF v_Hash = p_CodeHash THEN
      UPDATE afd_EmailVerification SET VerifiedAt = NOW() WHERE ID = v_ID;
      SET v_Result = 'verified';
    ELSEIF v_Attempts >= p_MaxAttempts THEN
      SET v_Result = 'too_many_attempts';
    ELSE
      SET v_Result = 'wrong';
    END IF;
  END IF;

  COMMIT;

  SELECT v_Result AS Result, GREATEST(p_MaxAttempts - IFNULL(v_Attempts, 0), 0) AS AttemptsLeft;
END ;;

-- Registration ---------------------------------------------------------
-- Same as 003, plus: the email must have a verified, unused code
-- (p_EmailVerificationID) that was verified in the last 30 minutes. The code
-- is marked used and the account is created with EmailVerified = 1, all in
-- the same transaction.
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,
  IN p_EmailVerificationID varchar(32))
BEGIN
  DECLARE v_AniCount int DEFAULT 0;
  DECLARE v_HourCount int DEFAULT 0;
  DECLARE v_AccountID bigint;
  DECLARE v_VerificationID bigint;

  -- Account + ANI + afd_Signup + the used code 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;

  SELECT ID INTO v_VerificationID
  FROM afd_EmailVerification
  WHERE PublicID = p_EmailVerificationID
    AND Email = p_Email
    AND VerifiedAt IS NOT NULL
    AND VerifiedAt >= NOW() - INTERVAL 30 MINUTE
    AND UsedAt IS NULL
  FOR UPDATE;

  IF v_VerificationID IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:email_not_verified';
  END IF;

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

  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);

  UPDATE afd_EmailVerification SET UsedAt = NOW(), AccountID = v_AccountID WHERE ID = v_VerificationID;

  COMMIT;

  SELECT v_AccountID AS AccountID;
END ;;

-- Email address for sending a payment link (recharges of existing accounts).
DROP PROCEDURE IF EXISTS `afd_Account_EmailSelect`;;
CREATE PROCEDURE `afd_Account_EmailSelect`(IN p_AccountID bigint)
BEGIN
  SELECT IFNULL(Email, '') AS Email, FirstName, EmailVerified, HardBounced
  FROM Account
  WHERE ID = p_AccountID;
END ;;

DELIMITER ;

-- Only if your DB user's rights are granted procedure by procedure
-- (not EXECUTE ON prepaid.*):
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_EmailVerification_Insert` TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_EmailVerification_Check` TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_Account_EmailSelect` TO 'your_user'@'your_host';