/* UPDATE USER EMAIL ACCOUNT AS VERIFIED */
/* =============================================================================================================== */
drop procedure if exists verifyEmailAccount;
commit;

delimiter //

CREATE PROCEDURE `verifyEmailAccount`(`p_userId` INT)
BEGIN

DECLARE errno INT;
DECLARE iUserValid INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
	GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: emailVerif', now(), errno; 

	RESIGNAL;
END;

START TRANSACTION;

SELECT COUNT(`_id`)
FROM `Users`
WHERE `_id` = p_userId
INTO iUserValid;

IF iUserValid = 0 OR iUserValid IS NULL THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Cannot validate a user who has not signed up yet.';
END IF;

UPDATE `Users`
SET `emailVerified` = GET_MISC_VALUE('email_verification', 'verified'),
  `updatedAt` = NOW()
WHERE `_id` = p_userId;

COMMIT WORK;

END
//

commit //

delimiter ;

