DROP TRIGGER IF EXISTS `Medications_au`;
COMMIT;

DELIMITER //

CREATE TRIGGER `Medications_au` AFTER UPDATE ON `Medications` FOR EACH ROW

BEGIN

  IF new.status = 'active' THEN

    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
      AND new.remaining = (old.remaining + `BSL`.`qtyDispensed`);

    END IF;

  END IF;

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

END
//

COMMIT //

DELIMITER ;
