USE `gateguard`;

DROP PROCEDURE IF EXISTS `sp_deleteInvitations`;

DELIMITER $$

CREATE PROCEDURE `sp_deleteInvitations` (
    IN p_ID INT,
    IN p_MODIFIED_BY INT,
	OUT v_ERROR_CODE VARCHAR(255),
	OUT v_ERROR_MESSAGE VARCHAR(255)
)
BEGIN
     DECLARE v_VISITOR_ID INT;
     DECLARE v_ARRIVAL_TIME DATETIME;
	 DECLARE v_OTP VARCHAR(6);
     DECLARE v_ACTUAL_ARRIVAL_TIME DATETIME;
     DECLARE v_OWNER_ID INT;	
	

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
         GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
		SET v_ERROR_MESSAGE = @text;
		SET v_ERROR_CODE = CONCAT(@errno, '|', @sqlstate);
		ROLLBACK;
    END;

    SET AUTOCOMMIT=0;
    START TRANSACTION;

    -- Fetch the invitations details before deletion
    SELECT VISITOR_ID, ARRIVAL_TIME, OTP, ACTUAL_ARRIVAL_TIME, OWNER_ID 
    INTO v_VISITOR_ID, v_ARRIVAL_TIME, v_OTP, v_ACTUAL_ARRIVAL_TIME, v_OWNER_ID
    FROM invitations
    WHERE ID = p_ID;

    -- Delete the invitation
    DELETE FROM  invitations
    WHERE ID = p_ID;

    COMMIT;
END$$

DELIMITER ;
