UseCase12 - Create Medical Report

Initiating Actor - Doctor

Description

A doctor creates a medical report for a patient documenting consultation findings. The system records the report with the doctor, date, and description entered by the doctor.

Scenario

1. Doctor opens a patient's medical record.

SELECT
  mr.record_id,
  mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);

2. Doctor navigates to "Add new report" and enters the report description and date.

INSERT INTO medical_report (report_id, record_id, doctor_id, description, report_date)
VALUES (
  (SELECT COALESCE(MAX(report_id), 0) + 1 FROM medical_report),
  (SELECT mr.record_id
   FROM medical_records mr
   JOIN patients p ON mr.patient_id = p.patient_id
   JOIN users u ON u.user_id = p.user_id
   WHERE u.username = '1402994123456'),
  (SELECT d.doctor_id
   FROM doctors d
   JOIN users u ON u.user_id = d.user_id
   WHERE u.username = 'elena.kirova@medora.com'),
  'Patient presents with hypertension. Recent ECG shows normal sinus rhythm.',
  '2026-05-30'
)
RETURNING report_id;

3. System displays the completed report with all entered information.

SELECT
  mr.report_id,
  mr.description,
  mr.report_date,
  u_d.first_name AS doctor_first_name,
  u_d.last_name AS doctor_last_name
FROM medical_report mr
JOIN doctors d ON mr.doctor_id = d.doctor_id
JOIN users u_d ON u_d.user_id = d.user_id
WHERE mr.report_id = (
    SELECT MAX(report_id)
    FROM medical_report
);