-- =====================================================================
-- Update 010 (2026-10-11): check the sign-up limit at the START of a phone
-- sign-up (with the phone check), not only at the end. Call 583: the caller
-- gave names, email and the email code, and only then heard "no more new
-- accounts can be opened from this phone".
-- Additive only: one new read-only procedure. Safe to re-run.
-- Same counts as afd_Account_Insert, which still checks again at the end.
-- =====================================================================

USE `prepaid`;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_Signup_Allowed`;;
CREATE PROCEDURE `afd_Signup_Allowed`(IN p_CallerANI varchar(20), IN p_MaxPerANI24h int, IN p_MaxPerHour int)
BEGIN
  DECLARE v_AniCount int DEFAULT 0;
  DECLARE v_HourCount int DEFAULT 0;

  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;

  SELECT (v_AniCount < p_MaxPerANI24h AND v_HourCount < p_MaxPerHour) AS Allowed;
END ;;

DELIMITER ;

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