DROP TRIGGER IF EXISTS `Users_ai`;
COMMIT;

DELIMITER //

CREATE TRIGGER `Users_ai` AFTER INSERT ON `Users` FOR EACH ROW

BEGIN

  DECLARE iEmailVerified INT;
  
  SELECT GET_MISC_VALUE('email_verification', 'verified')
  INTO iEmailVerified;

  IF new.emailVerified <> iEmailVerified THEN

    INSERT INTO `EmailSubscriptions`(`email`, `type`, `category`, `isActive`, `createdAt`, `updatedAt`)
    SELECT new.email, 'user', `_id`, 1, NOW(), NOW()
    FROM `EmailCategories`
    WHERE `category` = 'verification'
    ON DUPLICATE KEY
    UPDATE `isActive` = 1,
      `updatedAt` = NOW();

  END IF;

END
//

COMMIT //

DELIMITER ;
