gistfile1.txt

You work for an airline, and you've been tasked with improving the procedure for reserving and buying seats.
You have the table seats, which describes seats in the airplane. It has the following columns:

seat_no - The unique number of the seat;
status - The status of the seat (0 indicates free, 1 indicates reserved, and 2 indicates purchased);
person_id - The ID of the person who reserved/purchased this seat (0 if the corresponding status is 0).
You also have the table requests, which contains the following columns:

request_id - The unique ID of the request;
request - The description of the request (1 indicates reserve, 2 indicates purchase);
seat_no - The number of the seat that the person want to reserve/purchase;
person_id - The ID of the person who wants to reserve/purchase this seat.
A person can reserve/purchase a free seat and can purchase a seat that they have reserved.

Your task is to return the table seats after the given requests have been performed.

Note: requests are applied from the lowest request_id; it's guaranteed that all values of seat_no in the table requests are presented in the table seats.

Example

For the given tables seats

seat_no	status	person_id
1	1	1
2	1	2
3	0	0
4	2	3
5	0	0
and requests

request_id	request	seat_no	person_id
1	1	3	4
2	2	2	5
3	2	1	1
the output should be

seat_no	status	person_id
1	2	1
2	1	2
3	1	4
4	2	3
5	0	0
The first request is completed because seat number 3 is free. The second request is ignored because seat number 2 is already reserved by another person. The third request is completed because seat number 1 was reserved by this person, so they can purchase it.

[execution time limit] 10 seconds (mysql)

[memory limit] 1 GB

-- Procedure to process seat reservation and purchase requests
-- Requests are applied from lowest request_id to highest
-- Rules:
-- - Reserve (request=1): Can only reserve if seat is free (status=0)
-- - Purchase (request=2): Can purchase if seat is free (status=0) or if person already reserved it (status=1, person_id matches)

DROP PROCEDURE IF EXISTS process_requests;

DELIMITER //

CREATE PROCEDURE process_requests()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_request_id INT;
    DECLARE v_request_type INT;
    DECLARE v_seat_no INT;
    DECLARE v_person_id INT;
    DECLARE v_status INT;
    DECLARE v_seat_person_id INT;
    
    DECLARE cur_requests CURSOR FOR 
        SELECT request_id, request, seat_no, person_id 
        FROM requests 
        ORDER BY request_id;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur_requests;
    
    read_loop: LOOP
        FETCH cur_requests INTO v_request_id, v_request_type, v_seat_no, v_person_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Get current seat status
        SELECT status, person_id INTO v_status, v_seat_person_id
        FROM seats WHERE seat_no = v_seat_no;
        
        -- Handle reserve request (request_type = 1)
        IF v_request_type = 1 THEN
            -- Can reserve only if seat is free (status=0)
            IF v_status = 0 THEN
                UPDATE seats 
                SET status = 1, person_id = v_person_id 
                WHERE seat_no = v_seat_no;
            END IF;
            
        -- Handle purchase request (request_type = 2)
        ELSEIF v_request_type = 2 THEN
            -- Can purchase if:
            -- 1. Seat is free (status=0), OR
            -- 2. Seat is reserved by this person (status=1 AND person_id matches)
            IF v_status = 0 OR (v_status = 1 AND v_seat_person_id = v_person_id) THEN
                UPDATE seats 
                SET status = 2, person_id = v_person_id 
                WHERE seat_no = v_seat_no;
            END IF;
        END IF;
        
    END LOOP;
    
    CLOSE cur_requests;
END //

DELIMITER ;

-- Execute the procedure
CALL process_requests();

-- Return the final seats table
SELECT * FROM seats ORDER BY seat_no;