DROP TRIGGER IF EXISTS `MedBuddies_au`;
COMMIT;

DELIMITER //

CREATE TRIGGER `MedBuddies_au` AFTER UPDATE ON `MedBuddies` FOR EACH ROW

BEGIN

  IF new.status = 'active' THEN 

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

  ELSEIF new.status IN ('deleted', 'inactive', 'cancelled') THEN 

    UPDATE `EmailSubscriptions` AS `ES`
    INNER JOIN `EmailCategories` AS `EC` ON `ES`.`category` = `EC`.`_id`
    SET `ES`.`isActive` = 0,
      `ES`.`updatedAt` = NOW()
    WHERE `ES`.`email` = new.email
    AND (
      FIND_IN_SET('buddy', `EC`.`autoSubscribe`)
      OR FIND_IN_SET('buddy', `EC`.`isSubscription`)
    )
    AND `ES`.`isActive` = 1;

  END IF;

END
//

COMMIT //

DELIMITER ;
