/* SYNC SUGARS */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateSugars;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateSugars`(`p_transaction` VARCHAR(36), `p_userId` BIGINT, `p_sugarCount` INT, `p_date` DATETIME)

BEGIN

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

  RESIGNAL;
END;

START TRANSACTION;

SELECT COUNT(`remoteId`)
FROM `TempSugars`
WHERE `transaction` = p_transaction
INTO iSugarCount;

IF iSugarCount <> p_sugarCount THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Temp sugar count does not match provided sugar count.';
END IF;

IF p_sugarCount IS NOT NULL AND p_sugarCount > 0 THEN
  INSERT INTO `Sugars`(`remoteId`, `userId`, `dependant`, `day`, `sugar`, `unit`, `status`, `updatedAt`, `createdAt`)
  SELECT `temp`.`remoteId`, `temp`.`userId`, `temp`.`dependant`, `temp`.`day`, `temp`.`sugar`, `temp`.`unit`, 
    `temp`.`status`, `temp`.`updatedAt`, `temp`.`createdAt`
  FROM `TempSugars` `temp`
  WHERE `temp`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `day` = `temp`.`day`,
    `sugar` = `temp`.`sugar`,
    `unit` = `temp`.`unit`,
    `status` = `temp`.`status`,
    `updatedAt` = `temp`.`updatedAt`;

  DELETE FROM `TempSugars`
  WHERE `transaction` = p_transaction;
END IF;

SELECT *
FROM `Sugars`
WHERE `userId` = p_userId
AND `status` NOT IN ('inactive', 'deleted')
AND `day` > p_date;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
