DROP TRIGGER IF EXISTS `Medications_ai`;
COMMIT;

DELIMITER //

CREATE TRIGGER `Medications_ai` AFTER INSERT ON `Medications` FOR EACH ROW

BEGIN

  IF new.scripts IS NOT NULL THEN

    UPDATE `BeneficiaryScriptLines`
    SET `status` = 'ACCEPTED',
      `updatedAt` = NOW()
    WHERE FIND_IN_SET(`ID`, new.scripts)
    AND `status` IN ('READY', 'LOADED');

  ELSE
  
    UPDATE `BeneficiaryScriptLines` AS `BSL`
    INNER JOIN `BeneficiaryScripts` AS `BS` ON `BSL`.`beneficiaryScriptId` = `BS`.`ID`
    INNER JOIN `Users` AS `U` ON `BS`.`email` = `U`.`email`
    INNER JOIN `AtoZofMeds`.`TradeNames` AS `TN` ON `BSL`.`nappi` = `TN`.`Nappi`
    SET `BSL`.`status` = 'ACCEPTED',
      `BSL`.`updatedAt` = NOW()
    WHERE `BSL`.`status` IN ('READY', 'LOADED')
    AND `U`.`_id` = new.userId
    AND `TN`.`ID` = new.medicineId;

  END IF;

  INSERT IGNORE INTO `MedicineRiskToCalculate`(`userId`, `medicines`, `trigger`, `status`, `createdAt`, `updatedAt`)
  SELECT new.userId, GROUP_CONCAT(medicineId), 'NEW MEDICINE', 'ready', NOW(), NOW()
  FROM `Medications`
  WHERE `userId` = new.userId
  AND `status` = 'active'
  AND `remaining` > 0
  AND `dependant` IS NULL;

END
//

COMMIT //

DELIMITER ;
