/* SYNC MEDICATIONS */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateMedications;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateMedications`(`p_transaction` VARCHAR(36), `p_medicationCount` INT)

BEGIN

DECLARE errno INT;
DECLARE iMedicationCount INT;

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: syncMeds', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

SELECT COUNT(`remoteId`)
FROM `TempMedications`
WHERE `transaction` = p_transaction
INTO iMedicationCount;

IF iMedicationCount <> p_medicationCount THEN 
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Temp medication count does not match provided medication count.';
END IF;

IF p_medicationCount IS NOT NULL AND p_medicationCount > 0 THEN
  INSERT INTO `Medications`(`remoteId`, `userId`, `dependant`, `medicineId`, `dosage`, `amount`, `remaining`, 
    `reminders`, `frequency`, `weeklyFrequency`, `startDate`, `endDate`, `times`, `isChronic`, `status`, 
    `updatedAt`, `createdAt`)
  SELECT `temp`.`remoteId`, `temp`.`userId`, `temp`.`dependant`, `temp`.`medicineId`, `temp`.`dosage`, 
    `temp`.`amount`, `temp`.`remaining`, `temp`.`reminders`, `temp`.`frequency`, `temp`.`weeklyFrequency`, 
    `temp`.`startDate`, `temp`.`endDate`, `temp`.`times`, `temp`.`isChronic`, `temp`.`status`, 
    `temp`.`updatedAt`, `temp`.`createdAt`
  FROM `TempMedications` `temp`
  WHERE `temp`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `dosage` = `temp`.`dosage`, 
    `remaining` = `temp`.`remaining`, 
    `frequency` = `temp`.`frequency`, 
    `weeklyFrequency` = `temp`.`weeklyFrequency`, 
    `startDate` = `temp`.`startDate`, 
    `times` = `temp`.`times`, 
    `isChronic` = `temp`.`isChronic`, 
    `status` = `temp`.`status`;
END IF;

SELECT DISTINCT `M`.`remoteId`
FROM `Medications` `M`
INNER JOIN `TempMedications` `T` ON `M`.`remoteId` = `T`.`remoteId` AND `M`.`userId` = `T`.`userId`
WHERE `T`.`transaction` = p_transaction;

DELETE FROM `TempMedications` 
WHERE `transaction` = p_transaction;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
