= UC0002: Make a Resource Reservation =

'''Initiating actor:''' Teaching Staff

'''Other actors:''' Faculty Administrator (receives the pending reservation for approval)

A teaching staff member wants to reserve a resource (e.g., a classroom or lab) for a specific date and time to conduct a lecture. The system verifies the resource is available during the requested time, checks for scheduling conflicts with existing reservations, and if the slot is free, creates a new reservation with status ''pending'' that an administrator will later approve or reject.

== Scenario ==

 1. The teaching staff member (Elena Stojanova, user_id = 3) navigates to the reservation form and selects the resource ''Classroom 101'' (resource_id = 1). The system displays the resource details and existing reservations for the target week.
{{{
SELECT r.resource_id, r.name, r.description,
       r.available_from, r.available_to, r.available_weekends,
       rt.type_name, l.building, l.room
FROM project.resources r
JOIN project.resource_types rt ON r.type_id = rt.type_id
LEFT JOIN project.locations l ON r.location_id = l.location_id
WHERE r.resource_id = 1;
}}}
 The system also retrieves existing reservations for the target week:
{{{
SELECT res.reservation_id, res.start_time, res.end_time,
       res.status, res.purpose,
       u.first_name || ' ' || u.last_name AS reserved_by
FROM project.reservations res
JOIN project.users u ON res.user_id = u.user_id
WHERE res.resource_id = 1
  AND res.status IN ('approved', 'pending')
  AND res.start_time >= '2026-02-09'
  AND res.start_time < '2026-02-16'
ORDER BY res.start_time;
}}}

 2. The teaching staff member enters the desired reservation: date 2026-02-13, time 10:00 to 12:00, purpose ''Operating Systems Lecture''.

 3. The system validates that the requested time falls within the resource's daily availability window and that the day is a valid weekday.
{{{
SELECT r.resource_id
FROM project.resources r
WHERE r.resource_id = 1
  AND r.available_from <= '10:00'::TIME
  AND r.available_to >= '12:00'::TIME
  AND (r.available_weekends = TRUE
       OR EXTRACT(ISODOW FROM DATE '2026-02-13') <= 5);
}}}

 4. The system checks for conflicting reservations on the same resource at the requested time. No conflicts are found.
{{{
SELECT res.reservation_id, res.start_time, res.end_time,
       res.purpose,
       u.first_name || ' ' || u.last_name AS reserved_by
FROM project.reservations res
JOIN project.users u ON res.user_id = u.user_id
WHERE res.resource_id = 1
  AND res.status IN ('approved', 'pending')
  AND res.start_time < '2026-02-13 12:00:00'
  AND res.end_time > '2026-02-13 10:00:00';
}}}

 5. Since no conflicts exist, the system creates the reservation with status ''pending''.
{{{
INSERT INTO project.reservations
    (start_time, end_time, status, purpose, created_at, user_id, resource_id)
VALUES
    ('2026-02-13 10:00:00', '2026-02-13 12:00:00', 'pending',
     'Operating Systems Lecture', CURRENT_TIMESTAMP, 3, 1)
RETURNING reservation_id, status, created_at;
}}}

 6. The system displays a confirmation screen with the reservation details.
{{{
SELECT res.reservation_id, res.start_time, res.end_time,
       res.status, res.purpose, res.created_at,
       r.name AS resource_name,
       l.building, l.room
FROM project.reservations res
JOIN project.resources r ON res.resource_id = r.resource_id
LEFT JOIN project.locations l ON r.location_id = l.location_id
WHERE res.reservation_id = currval('project.reservations_reservation_id_seq');
}}}

== Alternative Scenario: Conflict Detected ==

 4a. If the teaching staff member had instead requested 2026-02-06 from 09:00 to 11:00, the conflict check would find an overlapping reservation.
{{{
SELECT res.reservation_id, res.start_time, res.end_time,
       res.purpose,
       u.first_name || ' ' || u.last_name AS reserved_by
FROM project.reservations res
JOIN project.users u ON res.user_id = u.user_id
WHERE res.resource_id = 1
  AND res.status IN ('approved', 'pending')
  AND res.start_time < '2026-02-06 11:00:00'
  AND res.end_time > '2026-02-06 09:00:00';
}}}

 4b. The system displays the conflicting reservation (''Morning lecture - Operating Systems'', 08:00-10:00, by Elena Stojanova) and informs the user that the time slot is not available. The user is asked to choose a different time.