== 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@gmail.com, +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.