DROP TRIGGER IF EXISTS `EmailQueue_au`;
COMMIT;

DELIMITER //

CREATE TRIGGER `EmailQueue_au` AFTER UPDATE 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 = 'sent' THEN

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

  END IF;

END
//

COMMIT //

DELIMITER ;
