/* UPDATE MEDICINE IMAGE DATES WITH IMAGE DATES IN TEMP TABLE */
/* =============================================================================================================== */
drop procedure if exists updateMedicineImages;
commit;

delimiter //

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

BEGIN

DECLARE errno INT;
DECLARE dtNow DATETIME;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: uMedImage', now(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

SELECT NOW() 
INTO dtNow;

UPDATE `Medicines` `M`
INNER JOIN `TempMedicineImages` `T` ON `M`.`ID` = `T`.`medicineId` 
SET `M`.`ImageDate` = `T`.`imageDate`,
  `M`.`updatedAt` = dtNow
WHERE `T`.`transaction` = p_transaction
AND (
  IF(`M`.`ImageDate` IS NULL, 1, TIMESTAMPDIFF(SECOND, `M`.`ImageDate`, `T`.`imageDate`)) <> 0
  OR `M`.`ImageDate` IS NULL
);

SELECT COUNT(*) AS `TempMedicineImages`
FROM `TempMedicineImages`
WHERE `transaction` = p_transaction;

SELECT COUNT(*) AS 'Medicines'
FROM `Medicines`
WHERE `updatedAt` = dtNow
AND `ImageDate` IS NOT NULL;

SELECT dtNow AS `dtNow`;

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

COMMIT WORK;

END
//

commit //

delimiter ;
