Customer makes reservation

Актери

• Customer

Scenario steps

1. Customer logs in.
2. Customer enters reservation details: date and time, number of people and stay length.
3. If an available table is found, proceed with reservation.
4. Applications shows reservation details to the customer.

SQL Queries

SELECT u.email, u.password FROM users u WHERE u.email = ?; -- and password application check

SELECT table_number 
FROM tables 
WHERE seat_capacity >= chosen_capacity 
AND table_number NOT IN (
    SELECT table_number 
    FROM frontstaff_managed_reservations
    WHERE reservation_id IN (
        SELECT id FROM reservations
        WHERE datetime BETWEEN entered_date_time AND entered_date_time+entered_stay
    )
);                   

INSERT INTO reservations (stay_length, datetime, creation_timestamp, number_of_people, user_id)
VALUES (2, '2025-02-10 19:00:00', NOW(), 4, 5);

SELECT r.id, r.datetime, r.number_of_people, t.table_number, u.email
FROM reservations r
JOIN frontstaff_managed_reservations rmf ON r.id = rmf.reservation_id
JOIN tables t ON rmf.table_number = t.table_number
JOIN users u ON r.user_id = u.id
WHERE r.id = 10;