CREATE OR REPLACE FUNCTION bookTheSeat(showID INTEGER, Variadic arrSeatID INTEGER[])
    returns TABLE (
        movie_title text,
        cinema_name text,
        cinema_hall_number text,
        seat_row text,
        seat_number text,
        show_start_time TIMESTAMP,
        show_end_time TIMESTAMP,
        ticket_id INTEGER,
        booking_id INTEGER
        )
AS $$

DECLARE
    seatID INTEGER;
    bookingID INTEGER;
    COUNT INTEGER;
    BEGIN
 INSERT INTO booking (show_id) VALUES
        (showID);
 bookingID = CURRVAL('booking_id_seq');

 FOR i IN 1..array_length(arrSeatID, 1) LOOP
        seatID = arrSeatID[i];
        SELECT COUNT(*) INTO COUNT FROM seat s
            join cinema_hall ch ON ch.id = s.cinema_hall_id
            join show sh ON sh.cinema_hall_id = ch.id
                AND sh.id = showID
            WHERE s.id = seatID;

        IF COUNT <> 1
        THEN RAISE EXCEPTION 'The seat % not from this cinema hall', seatID;
        END IF;

        INSERT INTO tickets (booking_id, seat_id, show_id) VALUES
            (bookingID, seatID, showID);
        COUNT := 0;
 END LOOP;

 RETURN query
    SELECT m.title, c.name, ch.NUMBER, s.ROW, s.NUMBER, sh.start_time, sh.end_time, t.id, b.id
        FROM booking b
        join tickets t ON t.booking_id = b.id
        join show sh ON sh.id = t.show_id
        join movie m ON m.id = sh.movie_id
        join cinema_hall ch ON ch.id = sh.cinema_hall_id
        join cinema c ON c.id = ch.cinema_id
        join seat s ON s.id = t.seat_id
    WHERE b.id = bookingID;

END; $$

language 'plpgsql';