Changes between Initial Version and Version 1 of UseCase10

Timestamp:

06/12/26 18:18:00 (9 days ago)

Author:

236021

Comment:

--

UseCase10

@@ +1 @@
+= 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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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.
+
+{{{
+#!sql
+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;
+}}}