-- Phone sign-up, second version (ai-front-desk SignupFlow):
--  * The email code is sent even when the address already has an account. Whether it
--    does is told only AFTER the right code was typed (afd_EmailVerification_Check
--    returns EmailInUse), so only the owner of that inbox ever learns it. Before, the
--    code was refused (afd:email_in_use), which told anyone who asked.
--  * Accounts opened by phone have no name yet: the account holder types it on the
--    payment page, and afd_Account_SaveName stores it once (only while both names
--    are empty, so it never overwrites a name set later on the website).
--    Account.FirstName / LastName stay NOT NULL: an empty string is stored.
--
-- Run as a DB admin after 010. Safe to run again.

DELIMITER ;;

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;

  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.
-- EmailInUse = 1 only for a verified code whose address is already on an account.
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_Email varchar(100);
  DECLARE v_Attempts int;
  DECLARE v_Expired int;
  DECLARE v_Verified int;
  DECLARE v_Result varchar(20);

  START TRANSACTION;

  SELECT ID, CodeHash, Email, Attempts, (ExpiresAt <= NOW()), (VerifiedAt IS NOT NULL)
  INTO v_ID, v_Hash, v_Email, 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,
         (v_Result = 'verified' AND EXISTS (SELECT 1 FROM Account WHERE Email = v_Email)) AS EmailInUse;
END ;;

-- The account holder's name from the payment page, stored only while the account
-- has no name at all. Updated = 1 when it was stored. Never touches Description.
DROP PROCEDURE IF EXISTS `afd_Account_SaveName`;;
CREATE PROCEDURE `afd_Account_SaveName`(IN p_AccountID int, IN p_FirstName varchar(20), IN p_LastName varchar(20))
BEGIN
  UPDATE Account
  SET FirstName = p_FirstName, LastName = p_LastName
  WHERE ID = p_AccountID AND IFNULL(FirstName, '') = '' AND IFNULL(LastName, '') = '';
  SELECT ROW_COUNT() AS Updated;
END ;;

DELIMITER ;

-- With per-procedure grants:
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_Account_SaveName` TO 'your_user'@'your_host';
