/* INVITE A MED BUDDY */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS inviteMedBuddy;
COMMIT;

DELIMITER //

CREATE PROCEDURE `inviteMedBuddy`(`p_id` VARCHAR(36), `p_userId` INT, `p_name` VARCHAR(255), `p_email` VARCHAR(255), 
  `p_shareMeds` TINYINT)
BEGIN

DECLARE errno INT;
DECLARE iBuddyCount INT;
DECLARE vMessage VARCHAR(128);
DECLARE vUserEmail VARCHAR(255);
DECLARE vUserName VARCHAR(100) DEFAULT NULL;

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

	RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*), CONCAT(p_email, ' is already your Med-Buddy.')
  FROM `MedBuddies`
  WHERE `userId` = p_userId
  AND `email` = p_email
  AND `status` = 'active'
  INTO iBuddyCount, vMessage;

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

  SELECT COUNT(*), CONCAT('Your invite for ', p_email, ' is still pending.')
  FROM `MedBuddies`
  WHERE `userId` = p_userId
  AND `email` = p_email
  AND `status` = 'pending'
  INTO iBuddyCount, vMessage;

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

  SELECT COUNT(*), CONCAT('You have reached your maximum of 2 Med Buddies.')
  FROM `MedBuddies`
  WHERE `userId` = p_userId
  AND `status` IN ('active', 'pending')
  INTO iBuddyCount, vMessage;

  IF iBuddyCount >= 2 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = vMessage;
  END IF;

  SELECT `email`, `name`, 
    'You cannot invite yourself to be your own Med Buddy. Try inviting a family member or a friend.'
  FROM `Users`
  WHERE `_id` = p_userId
  INTO vUserEmail, vUserName, vMessage;

  IF vUserEmail LIKE p_email THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = vMessage;
  END IF;

  INSERT INTO `MedBuddies`(`_id`, `userId`, `name`, `email`, `shareMeds`, `status`, `createdAt`, `updatedAt`)
  VALUES (p_id, p_userId, p_name, p_email, p_shareMeds, 'pending', NOW(), NOW());

  CALL addEmailToQueue(p_email, 2, NOW(), DATE_ADD(NOW(), INTERVAL 2 DAY), 
    CONCAT('{"token":"', p_id, '","name":"', IFNULL(vUserName, vUserEmail), '"}'));

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

  SELECT * 
  FROM `MedBuddies` 
  WHERE `_id` = p_id;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;

