-- =====================================================================
-- Update 003 (2026-09-30): replaces afd_Account_Insert only. Safe to re-run.
--   * refuses a phone already on any account (ANI, RegisteredNumber, Phone)
--   * saves US/Canada phone as 10 digits in Phone and RegisteredNumber
--   * adds the number to ANI (Verified = 0, ANI.Description left empty),
--     all in one transaction
--   * new parameter p_RegisteredNumber (upload the new adapter code with it)
-- Same definition as in 001_afd_schema.sql.
-- =====================================================================

USE `prepaid`;

DELIMITER ;;

-- Registration ---------------------------------------------------------
-- Differs from ez_Account_Insert on purpose:
--   * Pin = p_LoginNumber + p_Pin. The adapter passes the 10-digit US/Canada phone
--     as p_LoginNumber (like the website), or a generated 0-prefixed number for other countries
--   * 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 ;;

DELIMITER ;

-- Only if your DB user's rights are granted procedure by procedure
-- (not EXECUTE ON prepaid.*): the adapter now also calls ez_ANI_Delete.
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`ez_ANI_Delete` TO 'your_user'@'your_host';