UseCase10 - Schedule Appointment

Initiating Actor - Doctor or System Administrator

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. User selects a patient and a doctor 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 u_p.username = 'maja.veljanova';

2. System retrieves the selected doctor details.

SELECT
  d.doctor_id,
  u_d.first_name,
  u_d.last_name,
  ds.specialization_name,
  d.department_id
FROM doctors d
JOIN doctor_specialization ds 
ON d.specialization_id = ds.specialization_id
JOIN users u_d 
ON d.doctor_id = u_d.doctor_id
WHERE u_d.username = 'elena.kirova';

3. System checks which time slots are already taken for the doctor on the selected date.

SELECT a.appointment_time
FROM appointments a
WHERE a.doctor_id = (
SELECT doctor_id 
FROM users 
WHERE username = 'elena.kirova')
  AND a.appointment_date = '2026-06-12'
  AND a.status != 'CANCELLED';

4. System verifies the patient does not already have an appointment with this doctor at the chosen time.

SELECT 1
FROM appointments a
WHERE a.patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')
  AND a.doctor_id = (SELECT doctor_id FROM users WHERE username = 'elena.kirova')
  AND a.appointment_date = '2026-06-12'
  AND a.appointment_time = '10:00'
  AND a.status != 'CANCELLED';

5. System creates the appointment record.

INSERT INTO appointments (patient_id, doctor_id, appointment_date, appointment_time, status)
VALUES (
  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
  '2026-06-12',
  '10:00',
  'SCHEDULED'
)
RETURNING appointment_id;

6. 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);

7. 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 = (
SELECT patient_id 
FROM users 
WHERE username = 'maja.veljanova')
  AND a.appointment_date >= CURRENT_DATE
ORDER BY a.appointment_date ASC, a.appointment_time ASC;