/* CREATE NEW DEPENDANT */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS createDependant;
COMMIT;

DELIMITER //

CREATE PROCEDURE `createDependant`(`p_userId` BIGINT(20), `p_id` VARCHAR(36), `p_name` VARCHAR(100), 
  `p_gender` VARCHAR(1), `p_dob` DATE)
BEGIN

DECLARE errno INT;
DECLARE iDependantCount INT;
DECLARE vMessage VARCHAR(128);

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

  RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*), CONCAT(p_name, ' is already your dependant.')
  FROM `Dependants`
  WHERE `userId` = p_userId
  AND `name` = p_name
  AND `status` = 'active'
  INTO iDependantCount, vMessage;

  IF iDependantCount <> 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = vMessage;
  END IF;

  SELECT COUNT(*), CONCAT('You have reached your maximum of 3 dependants.')
  FROM `Dependants`
  WHERE `userId` = p_userId
  AND `status` = 'active'
  INTO iDependantCount, vMessage;

  IF iDependantCount >= 3 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = vMessage;
  END IF;

  INSERT INTO `Dependants`(`_id`, `userId`, `name`, `gender`, `dateOfBirth`, `status`, `createdAt`, `updatedAt`)
  VALUES (p_id, p_userId, p_name, p_gender, p_dob, 'active', NOW(), NOW());

  UPDATE `NotificationQueue`
  SET `status` = 'deleted',
    `updatedAt` = NOW()
  WHERE `userId` = p_userId
  AND `templateId` = (
    SELECT `_id`
    FROM `NotificationTemplates`
    WHERE `title` LIKE 'Need to help someone?' 
    AND `isActive` = 1
  )
  AND `status` = 'ready';

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

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
