Use-case 0003 - Approve or reject reservation

Initiating actor:

Reservation Approver

Other actors:

Room Requester

Description of the use-case goals:

The goal of this use case is to allow a reservation approver to review pending reservation requests and record an approval or rejection decision. The system displays pending reservations together with requester, room, and equipment details. The approver then chooses a decision. The database is modified by updating the reservation status and inserting a corresponding approval record in the approvals table.

Scenario:

1. The Reservation Approver opens the list of pending reservations.
2. The system loads pending reservation requests from the database.
3. The system displays reservation details, including requester, room, date, time, requested equipment, and requested quantities.
4. The Reservation Approver selects one reservation.
5. The Reservation Approver chooses whether to approve or reject the reservation and enters an optional note.
6. The system updates the reservation status.
7. The system inserts the approval decision in the approvals table.
8. The system displays the final decision for the selected reservation.

SQL commands used by the system:

List all pending reservations:

SELECT
res.reservation_id,
res.reservation_date,
res.start_time,
res.end_time,
res.status,
u.full_name AS requester_name,
r.room_code,
e.name AS requested_equipment,
req.requested_quantity
FROM project.reservations res
JOIN project.users u
ON u.user_id = res.user_id
LEFT JOIN project.rooms r
ON r.room_id = res.room_id
LEFT JOIN project.reservation_equipment req
ON req.reservation_id = res.reservation_id
LEFT JOIN project.equipment e
ON e.equipment_id = req.equipment_id
WHERE res.status = 'pending'
ORDER BY res.reservation_date, res.start_time, res.reservation_id;

Approve a pending reservation and create an approval record:

BEGIN;

UPDATE project.reservations
SET status = 'approved'
WHERE reservation_id = 1
AND status = 'pending';

INSERT INTO project.approvals (
reservation_id,
approver_id,
decision,
decision_time,
note
)
SELECT
1,
u.user_id,
'approved',
CURRENT_TIMESTAMP,
'Approved after checking room and equipment availability.'
FROM project.users u
WHERE u.username = 'approver1'
AND NOT EXISTS (
SELECT 1
FROM project.approvals a
WHERE a.reservation_id = 1
);

COMMIT;

Show the final approval decision:

SELECT
res.reservation_id,
res.status,
a.decision,
a.decision_time,
a.note,
approver.full_name AS approver_name
FROM project.reservations res
JOIN project.approvals a
ON a.reservation_id = res.reservation_id
JOIN project.users approver
ON approver.user_id = a.approver_id
WHERE res.reservation_id = 1;