DROP TRIGGER IF EXISTS `Users_au`;
COMMIT;

DELIMITER //

CREATE TRIGGER `Users_au` AFTER UPDATE ON `Users` FOR EACH ROW

BEGIN

  DECLARE iEmailVerified INT;

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

  IF old.emailVerified <> iEmailVerified AND new.emailVerified = iEmailVerified THEN

    UPDATE `EmailSubscriptions`
    SET `isActive` = 0,
      `updatedAt` = NOW()
    WHERE `email` = new.email
    AND `category` = 2;

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

END
//

COMMIT //

DELIMITER ;
