= 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:''' [[Image(0012-1.png)]]

'''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:''' [[Image(0012-2.png)]]

=== Step 3

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

'''Screenshot:''' [[Image(0012-3.png)]]

=== Step 4

'''User action:''' Client clicks "Confirm Appointment".

'''Screenshot:''' [[Image(0012-4.png)]]

'''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:''' [[Image(0012-5.png)]]