USE `gateguard`;

DROP PROCEDURE IF EXISTS `sp_updateInvitations`;

DELIMITER $$
CREATE PROCEDURE `sp_updateInvitations` (
    IN p_ID INT,
    IN p_VISITOR_ID INT,
    IN p_ARRIVAL_TIME DATETIME,
	IN p_OTP VARCHAR(6),
    IN p_ACTUAL_ARRIVAL_TIME DATETIME,
    IN p_OWNER_ID INT,	
    IN p_MODIFIED_BY INT,
	OUT v_ERROR_CODE VARCHAR(255),
	OUT v_ERROR_MESSAGE VARCHAR(255)
)
BEGIN
    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;

    UPDATE Invitations
    SET VISITOR_ID = p_VISITOR_ID, 
        ARRIVAL_TIME = p_ARRIVAL_TIME,
		OTP = p_OTP,
		ACTUAL_ARRIVAL_TIME = p_ACTUAL_ARRIVAL_TIME,
		OWNER_ID = p_OWNER_ID
    WHERE ID = p_ID;

    INSERT INTO invitations_audit (
        MODIFIED_BY, 
        MODIFIED_ON, 
        MODIFICATION, 
        ID, 
        VISITOR_ID, 
        ARRIVAL_TIME,
		OTP,
		ACTUAL_ARRIVAL_TIME,
		OWNER_ID
    ) VALUES (
        p_MODIFIED_BY,
        SYSDATE(),
        'UPDATED',
        p_ID,
        p_VISITOR_ID,
        p_ARRIVAL_TIME,
		p_OTP,
        p_ACTUAL_ARRIVAL_TIME,
		p_OWNER_ID
    );

    COMMIT;
END$$
DELIMITER ;
