/* DELETE MEDICATION FROM USER'S VIRTUAL MEDICATION CABINET */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS deleteUserMedication;
COMMIT;

DELIMITER //

CREATE PROCEDURE `deleteUserMedication`(`p_userId` BIGINT(20), `p_medicationId` VARCHAR(36))
BEGIN

DECLARE errno INT;
DECLARE t_medicationExists TINYINT;

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

  RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*)
  FROM `MedicationCabinet`
  WHERE `_id` = p_medicationId
  AND `userId` = p_userId
  AND `isActive` = 1
  INTO t_medicationExists;

  IF t_medicationExists = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = "Cannot delete non-existing medication from user's virtual medication cabinet.";
  END IF;

  UPDATE `MedicationCabinet`
  SET `isActive` = 0,
    `reminders` = 0,
    `timestamps` = '[]',
    `updatedAt` = NOW()
  WHERE `_id` = p_medicationId
  AND `userId` = p_userId;

  DELETE FROM `MedicationReminders`
  WHERE `medicationId` = p_medicationId
  AND `isActive` = 1
  AND `action` IS NULL;

  SELECT p_medicationId;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
