Use-case 0012 Implementation - Book Appointment for Viewing

Initiating actor: Client

Other actors: Agent

Description:

A client books an appointment to physically view an apartment unit. This reserves a timeslot with an agent and marks the slot as booked.

Scenario

Step 1

User action: Client navigates to booking page for unit 101 in Golden Tower.

Screenshot:

System action: System displays available agents and timeslots.

Query:

SET search_path TO project;

SELECT 
    a.agent_id,
    a.name AS agent_name,
    a.email,
    t.timeslot_id,
    t.date,
    t.time_start,
    t.time_end
FROM agent a
JOIN timeslot t ON a.agent_id = t.agent_id
WHERE t. status = 'Available'
AND t.date >= CURRENT_DATE
ORDER BY a.name, t.date, t.time_start;

Step 2

User action: Client selects agent "Bojana" and timeslot "June 1, 2026 at 11:00 AM".

Screenshot:

Step 3

User action: Client enters their information: Luka Krstikj, lukakr@…, +389 70 123 456.

Screenshot:

Step 4

User action: Client clicks "Confirm Appointment".

Screenshot:

System action: System verifies timeslot is still available.

Query:

SET search_path TO project;

SELECT status 
FROM timeslot 
WHERE timeslot_id = (
    SELECT timeslot_id FROM timeslot 
    WHERE date = '2026-06-01' 
    AND time_start = '11:00:00'
    AND agent_id = (SELECT agent_id FROM agent WHERE name = 'Bojana')
);

Step 5

System action: System creates client record if needed.

Query:

SET search_path TO project;

INSERT INTO client (name, email, phone)
VALUES ('Luka Krstikj', 'lukakr@gmail.com', '+389 70 123 456')
ON CONFLICT (email) DO UPDATE 
SET name = 'Luka Krstikj', phone = '+389 70 123 456'
RETURNING client_id;

Step 6

System action: System creates the appointment.

Query:

SET search_path TO project;

INSERT INTO appointment (status, client_id, unit_id, timeslot_id, agent_id)
VALUES (
    'Scheduled',
    (SELECT client_id FROM client WHERE email = 'lukakr@gmail.com'),
    (SELECT unit_id FROM unit WHERE unit_number = '101' 
     AND floor_id = (SELECT floor_id FROM floor 
                     WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower') 
                     AND floor_number = 1)),
    (SELECT timeslot_id FROM timeslot 
     WHERE date = '2026-06-01' 
     AND time_start = '11:00:00'
     AND agent_id = (SELECT agent_id FROM agent WHERE name = 'Bojana')),
    (SELECT agent_id FROM agent WHERE name = 'Bojana')
)
RETURNING appointment_id;

Step 7

System action: System updates timeslot to booked.

Query:

SET search_path TO project;

UPDATE timeslot
SET status = 'Booked'
WHERE timeslot_id = (SELECT timeslot_id FROM timeslot 
                     WHERE date = '2026-06-01' 
                     AND time_start = '11:00:00'
                     AND agent_id = (SELECT agent_id FROM agent WHERE name = 'Bojana'));

Step 8

System action: System displays confirmation with appointment details.

Query:

SET search_path TO project;

SELECT 
    ap.appointment_id,
    c.name AS client_name,
    c.email AS client_email,
    c. phone AS client_phone,
    u.unit_number,
    b.name AS building_name,
    f.floor_number,
    t.date,
    t.time_start,
    t.time_end,
    a.name AS agent_name,
    a.email AS agent_email
FROM appointment ap
JOIN client c ON ap.client_id = c. client_id
JOIN unit u ON ap.unit_id = u. unit_id
JOIN floor f ON u.floor_id = f. floor_id
JOIN building b ON f.building_id = b. building_id
JOIN timeslot t ON ap.timeslot_id = t.timeslot_id
JOIN agent a ON ap. agent_id = a.agent_id
WHERE ap.appointment_id = (SELECT appointment_id FROM appointment 
                           WHERE client_id = (SELECT client_id FROM client WHERE email = 'lukakr@gmail.com')
                           ORDER BY appointment_id DESC LIMIT 1);

Screenshot: