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 patient_id FROM users WHERE username = 'maja.veljanova'
);

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

INSERT INTO medical_report (record_id, doctor_id, description, report_date)
VALUES (
  (SELECT record_id FROM medical_records WHERE patient_id = (SELECT patient_id FROM users WHERE username = 'maja.veljanova')),
  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
  '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 d.doctor_id = u_d.doctor_id
WHERE mr.report_id = (
SELECT MAX(report_id) 
FROM medical_report);