/* DELETE EXISTING MED BUDDY */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS deleteMedBuddy;
COMMIT;

DELIMITER //

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

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

  RESIGNAL;
END;

START TRANSACTION;

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

  IF iBuddyExists = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot find Med Buddy to delete';
  END IF;

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

  SELECT `_id`, `name`, `email`, `shareMeds`, `status`
  FROM `MedBuddies`
  WHERE `userId` = p_userId
  AND `status` IN ('active', 'pending');

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
