/* DECLINE MED BUDDY INVITES THAT ARE OVERDUE */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS declineOverdueBuddyInvites;
COMMIT;

DELIMITER //

CREATE PROCEDURE `declineOverdueBuddyInvites`(`p_date` DATE)
BEGIN

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

  RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*) as count
  FROM `MedBuddies` 
  WHERE `status` = 'pending'
  AND `createdAt` < p_date;

  UPDATE `MedBuddies`
  SET `status` = 'inactive',
    `updatedAt` = NOW()
  WHERE `status` = 'pending'
  AND `createdAt` < p_date;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
