UseCase05PrototypeImplementation - Doctor Records Symptoms and Creates Diagnosis

Initiating Actor - Doctor

Description

During a patient consultation, a doctor records symptoms for the patient's medical record and creates a diagnosis. The system stores the symptoms and diagnosis linked to both the patient and 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 = 4;

2. Doctor navigates to "Record Symptoms" and system displays all available symptoms to choose from.

SELECT
    s.symptom_id,
    s.name,
    s.description
FROM symptoms s
ORDER BY s.name;

3. Doctor selects a symptom and clicks "Record Symptom".

INSERT INTO medical_record_symptoms (record_id, symptom_id)
VALUES (
    4,
    (SELECT symptom_id FROM symptoms WHERE name = 'HEADACHE')
);

4. Doctor navigates to "Record Diagnosis", selects a diagnosis from the dropdown and submits it.

INSERT INTO diagnosis (diagnosis_id, name, patient_id, doctor_id)
VALUES (
    (SELECT COALESCE(MAX(diagnosis_id), 0) + 1 FROM diagnosis),
    'Essential hypertension',
    4,
    (SELECT d.doctor_id
     FROM doctors d
     JOIN users u ON u.user_id = d.user_id
     WHERE u.username = 'elena.kirova@medora.com')
)
RETURNING diagnosis_id;

5. System displays the updated record with recorded symptoms and diagnoses.

SELECT
    d.diagnosis_id,
    d.name,
    u.first_name AS doctor_first_name,
    u.last_name AS doctor_last_name
FROM diagnosis d
    JOIN doctors doc ON d.doctor_id = doc.doctor_id
    JOIN users u ON u.user_id = doc.user_id
WHERE d.diagnosis_id = (SELECT MAX(diagnosis_id) FROM diagnosis);

SELECT
    s.name,
    s.description,
    mrs.severity
FROM medical_record_symptoms mrs
         JOIN symptoms s ON mrs.symptom_id = s.symptom_id
WHERE mrs.record_id = 4;