/* SYNC MEDICATIONS AND REMINDERS */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getUserState;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getUserState`(`p_userId` BIGINT, `p_startOfLastWeek` DATETIME, `p_startOfLastMonth` DATETIME, 
  `p_pushToken` TEXT)

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

  RESIGNAL;
END;

START TRANSACTION;

  DROP TEMPORARY TABLE IF EXISTS `t_meds`;

  CREATE TEMPORARY TABLE `t_meds` (
    `remoteId` VARCHAR(36) NOT NULL,
    PRIMARY KEY (`remoteId`)
  ) ENGINE=MEMORY;

  IF p_pushToken IS NOT NULL THEN
    UPDATE `Users`
    SET `pushToken` = p_pushToken,
      `updatedAt` = NOW()
    WHERE `_id` = p_userId;
  END IF;

  /* User data */
  SELECT `_id`, `name`, `surname`, `email`, `mobileNumber`, `gender`, `termsAccepted`, `country`,
    IF(`dateOfBirth` IS NULL, NULL, DATE_FORMAT(`dateOfBirth`, '%Y-%m-%d')) as `dateOfBirth`, `pushToken`
  FROM `Users` 
  WHERE `_id` = p_userId;

  /* Dependant data */
  SELECT `_id`, `name`, `gender`, DATE_FORMAT(`dateOfBirth`, '%Y-%m-%d') AS `dateOfBirth`
  FROM `Dependants`
  WHERE `userId` = p_userId
  AND `status` = 'active';

  /* Med-buddy data */
  SELECT `_id`, `name`, `email`, `shareMeds`, `status`
  FROM `MedBuddies`
  WHERE `userId` = p_userId
  AND `status` IN ('active', 'pending');

  /* Medications to fetch (medications and reminders) */
  INSERT IGNORE INTO `t_meds`(`remoteId`)
  SELECT DISTINCT `M`.`remoteId`
  FROM `Medications` AS `M`
  INNER JOIN `Reminders` AS `R` ON `M`.`remoteId` = `R`.`medicationId`
  WHERE `M`.`userId` = p_userId
  AND `M`.`status` = 'active'
  AND `R`.`status` NOT IN ('deleted', 'inactive')
  AND `R`.`timestamp` >= p_startOfLastWeek
  AND `M`.`dependant` IS NULL;

  INSERT IGNORE INTO `t_meds`(`remoteId`)
  SELECT DISTINCT `M`.`remoteId`
  FROM `Medications` AS `M`
  INNER JOIN `Reminders` AS `R` ON `M`.`remoteId` = `R`.`medicationId`
  INNER JOIN `Dependants` AS `D` ON `M`.`dependant` = `D`.`_id`
  WHERE `M`.`userId` = p_userId
  AND `M`.`status` = 'active'
  AND `R`.`status` NOT IN ('deleted', 'inactive')
  AND `R`.`timestamp` >= p_startOfLastWeek
  AND `D`.`status` = 'active';

  /* Medication data */
  SELECT DISTINCT `M`.`remoteId`, `M`.`dosage`, `M`.`remaining`, `M`.`frequency`, `M`.`weeklyFrequency`, `M`.`times`,  
    `M`.`isChronic`, `M`.`dependant`, DATE_FORMAT(`M`.`startDate`, '%Y-%m-%dT%H:%i:%s.000Z') as `startDate`, `M`.`userId`, 
    `M`.`reminders`, `M`.`status`, `M`.`medicineId`, DATE_FORMAT(`M`.`endDate`, '%Y-%m-%dT%H:%i:%s.000Z') as `endDate`, 
    DATE_FORMAT(`M`.`refill`, '%Y-%m-%dT%H:%i:%s.000Z') as `refill`
  FROM `Medications` AS `M`
  INNER JOIN `t_meds` AS `t` ON `M`.`remoteId` = `t`.`remoteId`
  WHERE `M`.`userId` = p_userId;

  /* Reminder data */
  SELECT DISTINCT `R`.`remoteId`, `R`.`medicationId`, `R`.`medicineId`, `R`.`dosage`, `R`.`dependant`, 
    DATE_FORMAT(`R`.`timestamp`, '%Y-%m-%dT%H:%i:%s.000Z') AS `timestamp`, `R`.`status`, `R`.`skippedReason`,
    DATE_FORMAT(`R`.`actionedAt`, '%Y-%m-%dT%H:%i:%s.000Z') AS `actionedAt`
  FROM `Reminders` AS `R`
  INNER JOIN `t_meds` AS `t` ON `R`.`medicationId` = `t`.`remoteId`
  WHERE `userId` = p_userId
  AND `status` NOT IN ('deleted', 'inactive');

  /* Sugar data */
  SELECT `remoteId`, `sugar`, `unit`, DATE_FORMAT(`day`, '%Y-%m-%dT%H:%i:%s.000Z') AS `day`, `status`, 
    DATE_FORMAT(`createdAt`, '%Y-%m-%dT%H:%i:%s.000Z') AS `createdAt`
  FROM `Sugars` 
  WHERE `userId` = p_userId
  AND `day` >= p_startOfLastWeek
  AND `status` NOT IN ('deleted', 'inactive');

  /* Blood pressure data */
  SELECT `remoteId`, `systolic`, `diastolic`, `unit`, DATE_FORMAT(`day`, '%Y-%m-%dT%H:%i:%s.000Z') AS `day`, 
    `status`, DATE_FORMAT(`createdAt`, '%Y-%m-%dT%H:%i:%s.000Z') AS `createdAt`
  FROM `BloodPressures` 
  WHERE `userId` = p_userId
  AND `day` >= p_startOfLastWeek
  AND `status` NOT IN ('deleted', 'inactive');

  /* Bmi data */
  SELECT `remoteId`, `height`, `heightUnit`, `weight`, `weightUnit`, `bmi`, `status`,
    DATE_FORMAT(`day`, '%Y-%m-%dT%H:%i:%s.000Z') AS `day`, DATE_FORMAT(`createdAt`, '%Y-%m-%dT%H:%i:%s.000Z') AS `createdAt`
  FROM `BMIs` 
  WHERE `userId` = p_userId
  AND `day` >= p_startOfLastMonth
  AND `status` NOT IN ('deleted', 'inactive');

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
