/* SYNC MEDICATIONS AND REMINDERS */
/* =============================================================================================================== */
drop procedure if exists getMedicationSyncPayload;
commit;

delimiter //

CREATE PROCEDURE `getMedicationSyncPayload`(`p_transaction` varchar(36))

BEGIN

DECLARE errno 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: getMedSync', now(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

SELECT `M`.`remoteId`, `M`.`dosage`, `M`.`remaining`, `M`.`frequency`, `M`.`weeklyFrequency`, `M`.`times`, 
  DATE_FORMAT(`M`.`startDate`, '%Y-%m-%dT%H:%i:%s.000Z') as `startDate`, `M`.`isChronic`, `M`.`dependant`, 
  DATE_FORMAT(`M`.`endDate`, '%Y-%m-%dT%H:%i:%s.000Z') as `endDate`,  `M`.`userId`, `M`.`reminders`,
  DATE_FORMAT(`M`.`refill`, '%Y-%m-%dT%H:%i:%s.000Z') as `refill`, `M`.`status`, `M`.`medicineId`
FROM `Medications` `M`
INNER JOIN `TempMedications` `temp` ON `M`.`remoteId` = `temp`.`remoteId` AND `M`.`userId` = `temp`.`userId`
WHERE `temp`.`transaction` = p_transaction;

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

SELECT DISTINCT `R`.`remoteId`, `R`.`medicationId`, `R`.`medicineId`, `R`.`dosage`, `R`.`dependant`, `R`.`status`, 
  DATE_FORMAT(`R`.`timestamp`, '%Y-%m-%dT%H:%i:%s.000Z') AS `timestamp`, `R`.`skippedReason`,
  DATE_FORMAT(`R`.`actionedAt`, '%Y-%m-%dT%H:%i:%s.000Z') AS `actionedAt`
FROM `Reminders` `R`
INNER JOIN `TempReminders` `temp` ON `R`.`remoteId` = `temp`.`remoteId` AND `R`.`userId` = `temp`.`userId` AND `R`.`medicationId` = `temp`.`medicationId`
WHERE `temp`.`transaction` = p_transaction;

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

COMMIT WORK;

END
//

commit //

delimiter ;
