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

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 (patient_id, doctor_id, procedure_id, diagnosis_id, procedure_date, notes)
VALUES (
  (SELECT patient_id FROM users WHERE username = 'maja.veljanova'),
  (SELECT doctor_id FROM users WHERE username = 'elena.kirova'),
  (SELECT procedure_id FROM procedures WHERE procedure_type = 'ECG'),
  (SELECT diagnosis_id FROM diagnosis 
WHERE name = 'Essential hypertension' 
AND patient_id = (
SELECT patient_id 
FROM users 
WHERE username = 'maja.veljanova')),
  '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 patient_id FROM users WHERE username = 'maja.veljanova')
  AND pp.procedure_date = '2026-06-10';

5. System creates a billing record for the patient.

INSERT INTO billing (total_cost, payment_status, record_id, admin_id)
VALUES (
  40.00,
  'PENDING',
  (SELECT record_id FROM medical_records WHERE patient_id = (
SELECT patient_id 
FROM users WHERE username = 'maja.veljanova')),
  (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 patient_id 
FROM users 
WHERE username = 'maja.veljanova');