/* SYNC BMI */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateBMIs;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateBMIs`(`p_transaction` VARCHAR(36), `p_userId` BIGINT, `p_bmiCount` INT, `p_date` DATETIME)

BEGIN

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

  RESIGNAL;
END;

START TRANSACTION;

SELECT COUNT(`remoteId`)
FROM `TempBMIs`
WHERE `transaction` = p_transaction
INTO iBMICount;

IF iBMICount <> p_bmiCount THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Temp bmi count does not match provided bmi count.';
END IF;

IF p_bmiCount IS NOT NULL AND p_bmiCount > 0 THEN
  INSERT INTO `BMIs`(`remoteId`, `userId`, `dependant`, `day`, `height`, `heightUnit`, `weight`, 
    `weightUnit`, `bmi`, `status`, `updatedAt`, `createdAt`)
  SELECT `temp`.`remoteId`, `temp`.`userId`, `temp`.`dependant`, `temp`.`day`, `temp`.`height`, `temp`.`heightUnit`, 
    `temp`.`weight`, `temp`.`weightUnit`, `temp`.`bmi`, `temp`.`status`, `temp`.`updatedAt`, `temp`.`createdAt`
  FROM `TempBMIs` `temp`
  WHERE `temp`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `day` = `temp`.`day`,
    `weight` = `temp`.`weight`,
    `weightUnit` = `temp`.`weightUnit`,
    `height` = `temp`.`height`,
    `heightUnit` = `temp`.`heightUnit`,
    `bmi` = `temp`.`bmi`,
    `status` = `temp`.`status`,
    `updatedAt` = `temp`.`updatedAt`;

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

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

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
