/* ADD MEDICATION TO USER'S VIRTUAL MEDICATION CABINET */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS createUserMedication;
COMMIT;

DELIMITER //

CREATE PROCEDURE `createUserMedication`(`p_userId` BIGINT(20), `p_dependantId` VARCHAR(36), `p_medicineId` INT, 
  `p_amount` DECIMAL(10, 1), `p_dosage` DECIMAL(10, 1), `p_days` VARCHAR(66), `p_times` VARCHAR(66), 
  `p_startDate` DATE, `p_isChronic` INT, `p_timestamps` TEXT)
BEGIN

DECLARE errno INT;
DECLARE v_medicationExists TINYINT;
DECLARE v_medicineExists TINYINT;
DECLARE v_medicationId VARCHAR(36);
DECLARE v_lastDose DECIMAL(10, 1);
DECLARE v_lastIndex INT;
DECLARE v_remindersLoaded TINYINT;
DECLARE v_loopIndex INTEGER DEFAULT 1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;

  ROLLBACK;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: createMed', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*)
  FROM `MedicationCabinet`
  WHERE `userId` = p_userId
  AND IF(p_dependantId IS NULL, `dependantId` IS NULL, `dependantId` = p_dependantId)
  AND `medicineId` = p_medicineId
  AND `isActive` = 1
  INTO v_medicationExists;

  IF v_medicationExists > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = "Cannot add medication already in user's virtual medication cabinet.";
  END IF;

  SELECT COUNT(*), UUID(), p_amount % p_dosage, COUNT_STRING_IN_STRING(p_timestamps, ',') + 1
  FROM `Medicines` 
  WHERE `ID` = p_medicineId
  INTO v_medicineExists, v_medicationId, v_lastDose, v_lastIndex;

  IF v_medicineExists = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = "Cannot add invalid medicine.";
  END IF;

  DROP TEMPORARY TABLE IF EXISTS `t_tempReminders`;
  CREATE TEMPORARY TABLE `t_tempReminders` (
    `index` INTEGER NOT NULL,
    `reminderTimestamp` DATETIME NOT NULL,
    PRIMARY KEY (`index`)
  ) ENGINE=MEMORY;

  reminder_loop: LOOP         
    INSERT INTO `t_tempReminders`(`index`, `reminderTimestamp`)
    SELECT v_loopIndex, FROM_UNIXTIME(SPLIT_STRING(p_timestamps, ',', v_loopIndex)/1000);

    SET v_loopIndex = v_loopIndex + 1;

    IF v_loopIndex > v_lastIndex THEN
      LEAVE reminder_loop;
    END IF;
  END LOOP;

  INSERT INTO `MedicationCabinet`(`_id`, `userId`, `dependantId`, `medicineId`, `remaining`, `dosage`, `days`, 
    `times`, `isChronic`, `startDate`, `refillDate`, `endDate`, `reminders`, `timestamps`, `isActive`, `scripts`, 
    `createdAt`, `updatedAt`)
  SELECT v_medicationId, p_userId, p_dependantId, p_medicineId, p_amount, p_dosage, p_days, p_times, p_isChronic,
    MIN(`reminderTimestamp`), NULL, MAX(`reminderTimestamp`), COUNT(*), p_timestamps, 1, NULL, NOW(), NOW()
  FROM `t_tempReminders`;

  INSERT INTO `MedicationReminders`(`_id`, `medicationId`, `order`, `timestamp`, `dose`, `isActive`, `action`, 
    `adherence`, `actionedAt`, `createdAt`, `updatedAt`)
  SELECT UUID(), v_medicationId, `index`, `reminderTimestamp`, 
    IF(v_lastDose > 0 AND v_lastIndex = `index`, v_lastDose, p_dosage), 1, NULL, NULL, NULL, NOW(), NOW()
  FROM `t_tempReminders`;

  SELECT `M`.`remaining` = SUM(`R`.`dose`)
  FROM `MedicationCabinet` AS `M`
  INNER JOIN `MedicationReminders` AS `R` ON `M`.`_id` = `R`.`medicationId`
  WHERE `M`.`_id` = v_medicationId
  AND `M`.`isActive` = 1
  AND `R`.`isActive` = 1
  GROUP BY `M`.`remaining`, `R`.`medicationId`
  INTO v_remindersLoaded;

  IF v_remindersLoaded = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = "Medication amount doesn't match reminder count.";
  END IF;

  SELECT `M`.`_id`, `M`.`userId`, `M`.`dependantId`, `M`.`remaining`, `M`.`dosage`, `M`.`days`, `M`.`times`, 
    `M`.`isChronic`, `M`.`startDate`, `M`.`refillDate`, `M`.`endDate`, `M`.`reminders`, `M`.`isActive`, 
    `M`.`createdAt`, `M`.`updatedAt`, `T`.`ID` AS `medicineId`, `T`.`TradeName`, `T`.`FullName`, `T`.`TradeStr`, 
    `T`.`TradeForm`, `T`.`Schedule`, `T`.`TradeManufacturer`
  FROM `MedicationCabinet` AS `M`
  INNER JOIN `Medicines` AS `T` ON `M`.`medicineId` = `T`.`ID`
  WHERE `M`.`_id` = v_medicationId;

  SELECT *
  FROM `MedicationReminders`
  WHERE `medicationId` = v_medicationId;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
