/* UPDATE EXISTING DEPENDANT */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateDependant;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateDependant`(`p_id` VARCHAR(36), `p_name` VARCHAR(100), `p_gender` VARCHAR(1), `p_dob` DATE)
BEGIN

DECLARE errno INT;
DECLARE iDependantExists 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: updateDep', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

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

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

  IF p_name IS NOT NULL THEN 
    UPDATE `Dependants`
    SET `name` = p_name,
      `updatedAt` = NOW()
    WHERE `_id` = p_id;
  END IF;

  IF p_gender IS NOT NULL THEN 
    UPDATE `Dependants`
    SET `gender` = p_gender,
      `updatedAt` = NOW()
    WHERE `_id` = p_id;
  END IF;

  IF p_dob IS NOT NULL THEN 
    UPDATE `Dependants`
    SET `dateOfBirth` = p_dob,
      `updatedAt` = NOW()
    WHERE `_id` = p_id;
  END IF;

  SELECT `_id`, `name`, `gender`, DATE_FORMAT(`dateOfBirth`, '%Y-%m-%d') as `dateOfBirth`
  FROM `Dependants`
  WHERE `_id` = p_id;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
