-- =====================================================================
-- Update 007 (2026-10-10): check a phone number at the START of a phone
-- sign-up, before the caller spells their name and email.
-- Additive only: one new table and one new procedure. Safe to re-run.
--
-- Same "in use" rule as afd_Account_Insert (a registered pinless number, or
-- an account's registered number / phone), so the early answer matches the
-- final one. Every check is logged in afd_PhoneCheck and limited per calling
-- number per 24 hours: each "in use" answer tells the caller that number is a
-- customer, so unlimited checks would let someone test numbers.
-- =====================================================================

USE `prepaid`;

CREATE TABLE IF NOT EXISTS `afd_PhoneCheck` (
  `ID`        bigint unsigned NOT NULL AUTO_INCREMENT,
  `Phone`     varchar(20) NOT NULL,
  `CallerANI` varchar(20) NOT NULL DEFAULT '',
  `CallID`    varchar(64) NOT NULL DEFAULT '',
  `InUse`     tinyint(1)  NOT NULL,
  `CreatedAt` datetime    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Idx_afd_PhoneCheck_CallerANI` (`CallerANI`, `CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_Phone_Check`;;
CREATE PROCEDURE `afd_Phone_Check`(
  IN p_PartitionID int, IN p_Phone varchar(20), IN p_CallerANI varchar(20),
  IN p_CallID varchar(64), IN p_MaxPerANI24h int)
BEGIN
  DECLARE v_Count int DEFAULT 0;
  DECLARE v_InUse tinyint DEFAULT 0;

  IF p_CallerANI <> '' THEN
    SELECT COUNT(*) INTO v_Count FROM afd_PhoneCheck
    WHERE CallerANI = p_CallerANI AND CreatedAt >= NOW() - INTERVAL 24 HOUR;
    IF v_Count >= p_MaxPerANI24h THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'afd:phone_check_limit';
    END IF;
  END IF;

  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
    SET v_InUse = 1;
  END IF;

  INSERT INTO afd_PhoneCheck (Phone, CallerANI, CallID, InUse) VALUES (p_Phone, p_CallerANI, p_CallID, v_InUse);
  SELECT v_InUse AS InUse;
END ;;

DELIMITER ;

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

-- Testing: clear today's checks for your own test number
-- DELETE FROM prepaid.afd_PhoneCheck WHERE CallerANI = '4046105165';