= UseCase04 - Doctor Views Patient Medical Records =

== Initiating Actor - `Doctor` ==


== Description ==
A doctor views the complete medical history of a patient including diagnoses, symptoms, allergies, lab results, procedures, prescriptions, and medical reports. The system retrieves all associated clinical data to help the doctor make informed decisions.

== Scenario ==

1. Doctor searches for a patient by name or ID.

{{{
#!sql
SELECT
  p.patient_id,
  p.embg,
  p.date_of_birth,
  p.blood_type,
  p.gender,
  u.first_name,
  u.last_name,
  u.phone_number,
  u.email
FROM patients p
JOIN users u ON p.user_id = u.user_id
WHERE u.last_name = 'Veljanova';
}}}

2. Doctor navigates to the patient's medical record.

{{{
#!sql
SELECT
  mr.record_id,
  mr.patient_id
FROM medical_records mr
WHERE mr.patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova'));
}}}

3. System displays all diagnoses for the patient.

{{{
#!sql
SELECT
  diag.diagnosis_id,
  diag.name,
  diag.description,
  u.first_name AS doctor_first_name,
  u.last_name AS doctor_last_name
FROM diagnosis diag
JOIN doctors d ON diag.doctor_id = d.doctor_id
JOIN users u ON d.user_id = u.user_id
WHERE diag.patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova'));
}}}

4. System displays symptoms recorded in the medical record.

{{{
#!sql
SELECT
  s.name,
  s.description,
  mrs.date_recorded
FROM medical_record_symptoms mrs
JOIN symptoms s ON mrs.symptom_id = s.symptom_id
WHERE mrs.record_id = (
SELECT record_id 
FROM medical_records 
WHERE patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova')));
}}}

5. System displays allergies with reaction and severity information.

{{{
#!sql
SELECT
  a.name,
  a.description,
  mra.reaction,
  mra.severity
FROM medical_record_allergies mra
JOIN allergies a ON mra.allergy_id = a.allergy_id
WHERE mra.record_id = (
SELECT record_id 
FROM medical_records 
WHERE patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova')));
}}}

6. System displays lab results linked to the medical record.

{{{
#!sql
SELECT
  lt.test_name,
  lr.results,
  lr.result_date
FROM medical_record_lab_results mrlr
JOIN lab_results lr ON mrlr.result_id = lr.result_id
JOIN lab_tests lt ON lr.test_id = lt.test_id
WHERE mrlr.record_id = (
SELECT record_id 
FROM medical_records 
WHERE patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova')));
}}}

7. System displays procedures linked to the medical record.

{{{
#!sql
SELECT
  pr.procedure_type,
  pr.description
FROM medical_record_procedures mrp
JOIN procedures pr ON mrp.procedure_id = pr.procedure_id
WHERE mrp.record_id = (
SELECT record_id 
FROM medical_records 
WHERE patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova')));
}}}

8. System displays prescriptions linked to the medical record.

{{{
#!sql
SELECT
  p.name AS prescription_name,
  pmr.dosage,
  pmr.frequency,
  pmr.duration
FROM prescription_medical_record pmr
JOIN prescriptions p ON pmr.prescription_id = p.prescription_id
WHERE pmr.record_id = (
SELECT record_id 
FROM medical_records 
WHERE patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova')));
}}}

9. System displays medical reports created for this patient.

{{{
#!sql
SELECT
  rep.report_id,
  rep.content,
  rep.created_date,
  u.first_name AS doctor_first_name,
  u.last_name AS doctor_last_name
FROM medical_reports rep
JOIN doctors d ON rep.doctor_id = d.doctor_id
JOIN users u ON d.user_id = u.user_id
WHERE rep.record_id = (
SELECT record_id 
FROM medical_records 
WHERE patient_id = (
SELECT patient_id 
FROM patients 
WHERE user_id = (
SELECT user_id 
FROM users 
WHERE username = 'maja.veljanova')));
}}}

10. Doctor reviews the complete patient history and proceeds with clinical decisions.