/* =============================================================================================================== */

DROP TRIGGER IF EXISTS `MedicationCabinet_au`;
COMMIT;

DELIMITER //

CREATE TRIGGER `MedicationCabinet_au` AFTER UPDATE ON `MedicationCabinet` FOR EACH ROW

BEGIN

  IF new.scripts != old.scripts AND LENGTH(new.scripts) > LENGTH(old.scripts) THEN 
    -- script refill
    INSERT INTO `MedicationLogs`(`action`, `medicationId`, `userId`, `oldRemaining`, `newRemaining`, `oldDosage`, 
      `newDosage`, `oldDays`, `newDays`, `oldTimes`, `newTimes`, `oldIsChronic`, `newIsChronic`, `oldStartDate`, 
      `newStartDate`, `timestamps`, `scriptId`, `createdAt`)
    VALUES ('SCRIPT', new._id, new.userId, old.remaining, new.remaining, old.dosage, new.dosage, old.days, new.days, 
      old.times, new.times, old.isChronic, new.isChronic, old.startDate, new.startDate, new.timestamps, 
      SPLIT_STRING(new.scripts, ',', COUNT_STRING_IN_STRING(new.scripts, ',') + 1), NOW());
      
  ELSEIF (new.remaining - old.remaining) >= 10 THEN 
    -- refill medication
    INSERT INTO `MedicationLogs`(`action`, `medicationId`, `userId`, `oldRemaining`, `newRemaining`, `oldDosage`, 
      `newDosage`, `oldDays`, `newDays`, `oldTimes`, `newTimes`, `oldIsChronic`, `newIsChronic`, `oldStartDate`, 
      `newStartDate`, `timestamps`, `scriptId`, `createdAt`)
    VALUES ('REFILL', new._id, new.userId, old.remaining, new.remaining, old.dosage, new.dosage, old.days, new.days, 
      old.times, new.times, old.isChronic, new.isChronic, old.startDate, new.startDate, new.timestamps, NULL, NOW());
  ELSEIF new.isActive = 0 THEN 
    -- delete medication
    INSERT INTO `MedicationLogs`(`action`, `medicationId`, `userId`, `oldRemaining`, `newRemaining`, `oldDosage`, 
      `newDosage`, `oldDays`, `newDays`, `oldTimes`, `newTimes`, `oldIsChronic`, `newIsChronic`, `oldStartDate`, 
      `newStartDate`, `timestamps`, `scriptId`, `createdAt`)
    VALUES ('DELETE', new._id, new.userId, old.remaining, new.remaining, old.dosage, new.dosage, old.days, new.days, 
      old.times, new.times, old.isChronic, new.isChronic, old.startDate, new.startDate, new.timestamps, NULL, NOW());
  ELSE 
    -- script refill
    INSERT INTO `MedicationLogs`(`action`, `medicationId`, `userId`, `oldRemaining`, `newRemaining`, `oldDosage`, 
      `newDosage`, `oldDays`, `newDays`, `oldTimes`, `newTimes`, `oldIsChronic`, `newIsChronic`, `oldStartDate`, 
      `newStartDate`, `timestamps`, `scriptId`, `createdAt`)
    VALUES ('UPDATE', new._id, new.userId, old.remaining, new.remaining, old.dosage, new.dosage, old.days, new.days, 
      old.times, new.times, old.isChronic, new.isChronic, old.startDate, new.startDate, new.timestamps, NULL, NOW());
  END IF;

END
//

COMMIT //

DELIMITER ;
