UseCase08 - Doctor Requests Procedure

Initiating Actor - Doctor

Description

A doctor requests a medical procedure for a patient. The system creates a performed procedure record linking the doctor, patient, and procedure, optionally links to a diagnosis.

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. System displays all available procedures.

SELECT
  pr.procedure_id,
  pr.procedure_type,
  pr.description,
  pr.cost
FROM procedures pr
ORDER BY pr.procedure_type;

3. Doctor selects a procedure, specifies the date, optionally links a diagnosis, and submits the request.

INSERT INTO performed_procedures (performed_id, patient_id, doctor_id, procedure_id, diagnosis_id, procedure_date, notes)
VALUES (
  (SELECT COALESCE(MAX(performed_id), 0) + 1 FROM performed_procedures),
  (SELECT p.patient_id
   FROM patients p
   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'),
  (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG'),
  (SELECT diagnosis_id
   FROM diagnosis
   WHERE name = 'Essential hypertension'
     AND patient_id = (
         SELECT p.patient_id
         FROM patients p
         JOIN users u ON u.user_id = p.user_id
         WHERE u.username = '1402994123456')
   ORDER BY diagnosis_id
   LIMIT 1),
  '2026-06-10',
  'ECG for hypertension monitoring'
)
RETURNING performed_id;

4. System calculates the total cost of procedures scheduled for that date.

SELECT SUM(pr.cost) AS total_cost
FROM performed_procedures pp
JOIN procedures pr ON pp.procedure_id = pr.procedure_id
WHERE pp.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
)
  AND pp.procedure_date = '2026-06-10';

5. System creates a billing record for the patient.

INSERT INTO billing (bill_id, total_cost, payment_status, record_id, admin_id)
VALUES (
  (SELECT COALESCE(MAX(bill_id), 0) + 1 FROM billing),
  40.00,
  'PENDING',
  (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 admin_id FROM admin LIMIT 1)
)
RETURNING bill_id;

6. System links the billing record to the performed procedure.

INSERT INTO billing_procedures (bill_id, procedure_id)
VALUES (
  (SELECT MAX(bill_id) FROM billing),
  (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG')
);

7. System confirms the request and displays all procedures for the patient.

SELECT
  pp.performed_id,
  pr.procedure_type,
  pr.description,
  pp.procedure_date,
  pp.notes
FROM performed_procedures pp
JOIN procedures pr ON pp.procedure_id = pr.procedure_id
WHERE pp.patient_id = (
    SELECT p.patient_id
    FROM patients p
    JOIN users u ON u.user_id = p.user_id
    WHERE u.username = '1402994123456'
);