/* DELETE EXISTING DEPENDANT */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS deleteDependant;
COMMIT;

DELIMITER //

CREATE PROCEDURE `deleteDependant`(`p_id` VARCHAR(36), `p_userId` INT)
BEGIN

DECLARE errno INT;
DECLARE v_dependantExists INT;

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

  ROLLBACK;

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

  RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*)
  FROM `Dependants`
  WHERE `_id` = p_id
  AND `userId` = p_userId
  AND `status` = 'active'
  INTO v_dependantExists;

  IF v_dependantExists = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot find dependant to delete';
  END IF;

  UPDATE `Dependants`
  SET `status` = 'deleted',
    `updatedAt` = NOW()
  WHERE `_id` = p_id;

  UPDATE `Medications`
  SET `status` = 'inactive',
    `updatedAt` = NOW()
  WHERE `userId` = p_userId
  AND `dependant` = p_id
  AND `status` = 'active';

  UPDATE `Reminders`
  SET `status` = 'inactive',
    `updatedAt` = NOW()
  WHERE `userId` = p_userId
  AND `dependant` = p_id
  AND `status` = 'active';

  UPDATE `MedicationCabinet`
  SET `isActive` = 0,
    `reminders` = '[]',
    `updatedAt` = NOW()
  WHERE `userId` = p_userId
  AND `dependantId` = p_id
  AND `isActive` = 1;

  DELETE `R`
  FROM `MedicationReminders` AS `R`
  INNER JOIN `MedicationCabinet` AS `M` ON `R`.`medicationId` = `M`.`_id`
  WHERE `M`.`userId` = p_userId
  AND `M`.`dependantId` = p_id
  AND `R`.`isActive` = 1
  AND `R`.`action` = NULL;

  SELECT `_id`, `name`, `gender`, DATE_FORMAT(`dateOfBirth`, '%Y-%m-%d') as `dateOfBirth`, `createdAt`, `updatedAt`
  FROM `Dependants`
  WHERE `userId` = p_userId
  AND `status` = 'active';

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
