UseCase10PrototypeImplementation - Schedule Appointment

Initiating Actor - Doctor

Description

An appointment is scheduled for a patient with a specific doctor on a specific date and time. The system checks for conflicts, creates the appointment record, and makes it visible to both the patient and doctor.

Scenario

1. Doctor selects a patient to schedule an appointment for.

SELECT
    p.patient_id,
    u_p.first_name,
    u_p.last_name,
    p.embg
FROM patients p
JOIN users u_p ON p.patient_id = u_p.patient_id
WHERE p.patient_id = 4;

2. User confirms the time slot and submits the appointment request.

INSERT INTO appointments (patient_id, doctor_id, appointment_date, appointment_time, status)
VALUES (
    4,
    (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
    '2026-06-30',
    '09:40',
    'SCHEDULED'
)
RETURNING appointment_id;

3. System displays the confirmation with full appointment details.

SELECT
    a.appointment_id,
    a.appointment_date,
    a.appointment_time,
    a.status,
    u_d.first_name AS doctor_first_name,
    u_d.last_name AS doctor_last_name,
    u_p.first_name AS patient_first_name,
    u_p.last_name AS patient_last_name
FROM appointments a
JOIN doctors d ON a.doctor_id = d.doctor_id
JOIN users u_d ON d.doctor_id = u_d.doctor_id
JOIN patients p ON a.patient_id = p.patient_id
JOIN users u_p ON p.patient_id = u_p.patient_id
WHERE a.appointment_id = (SELECT MAX(appointment_id) FROM appointments);

4. System displays all upcoming appointments for the patient.

SELECT
    a.appointment_id,
    a.appointment_date,
    a.appointment_time,
    a.status,
    u_d.first_name AS doctor_first_name,
    u_d.last_name AS doctor_last_name
FROM appointments a
JOIN doctors d ON a.doctor_id = d.doctor_id
JOIN users u_d ON d.doctor_id = u_d.doctor_id
WHERE a.patient_id = 4
  AND a.appointment_date >= CURRENT_DATE
ORDER BY a.appointment_date ASC, a.appointment_time ASC;