DROP TRIGGER IF EXISTS `EmailQueue_ai`;
COMMIT;

DELIMITER //

CREATE TRIGGER `EmailQueue_ai` AFTER INSERT ON `EmailQueue` FOR EACH ROW

BEGIN

  DECLARE templateId INT;

  SELECT `_id` 
  FROM `EmailTemplates`
  WHERE `name` = 'accountVerification'
  INTO templateId;

  IF new.templateId = templateId AND new.status = 'ready' THEN

    UPDATE `Users`
    SET `emailVerified` = GET_MISC_VALUE('email_verification', 'queued'),
      `updatedAt` = NOW()
    WHERE `email` = new.email
    AND `status` = 'active';

  END IF;

END
//

COMMIT //

DELIMITER ;
