Use-case 0012 - 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:

1. Client navigates to booking page for unit 101 in Golden Tower.

2. System displays available agents and timeslots:

   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;
   

3. Client selects agent "Teodora" and timeslot "Jan 9, 2025 at 10:00 AM".

4. System displays booking confirmation form with unit, date/time, and agent details.

5. Client enters their information (name, email and phone number): Marko Petrovski, marko.p@…, +389 70 123 456.

6. System creates a client record if there are no previous records with the same email:

   INSERT INTO client (name, email, phone)
   VALUES ('Marko Petrovski', 'marko.p@gmail.com', '+389 70 123 456')
   ON CONFLICT (email) DO NOTHING;
   

7. Client clicks "Confirm Appointment".

8. System verifies that the timeslot is available:

   SELECT status 
   FROM timeslot 
   WHERE timeslot_id = (
       SELECT timeslot_id FROM timeslot 
       WHERE date = '2025-01-10' 
       AND time_start = '10:00:00'
       AND agent_id = (SELECT agent_id FROM agent WHERE name = 'Teodora')
   );
   

9. System creates the appointment:

   INSERT INTO appointment (status, client_id, unit_id, timeslot_id, agent_id)
   VALUES (
       'Scheduled',
       (SELECT client_id FROM client WHERE email = 'marko.p@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 = '2025-01-10' 
        AND time_start = '10:00:00'
        AND agent_id = (SELECT agent_id FROM agent WHERE name = 'Teodora')
        ),
       (SELECT agent_id FROM agent WHERE name = 'Teodora')
   );
   

10. System updates timeslot to booked:

    UPDATE timeslot
    SET status = 'Booked'
    WHERE timeslot_id = (SELECT timeslot_id FROM timeslot 
                         WHERE date = '2025-01-10' 
                         AND time_start = '10:00:00'
                         AND agent_id = (SELECT agent_id FROM agent WHERE name = 'Teodora'));
    

11. System displays confirmation with appointment details.