= UseCase03 - Patient Views Medical Records =

== Initiating Actor - `Patient` ==

== Description ==
A patient views their complete medical record including diagnoses, symptoms, allergies, prescriptions, lab results, and procedures. The system retrieves all associated medical data organized by category.

== Scenario ==

1. Patient logs in and navigates to "Medical Records" section.

{{{
#!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'));
}}}

2. System retrieves all the 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'));
}}}

3. System retrieves symptoms that are 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')));
}}}

4. System retrieves the allergies recorded in the medical record.

{{{
#!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')));
}}}

5. System retrieves the 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')));
}}}

6. System retrieves the 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')));
}}}

7. System retrieves 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')));
}}}

8. System retrieves 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')));
}}}

9. Patient views their complete medical history organized by category.