UseCase13 - Create / Update Billing Record

Initiating Actor - System Administrator / Billing Admin

Description

The system automatically creates and updates billing records when lab tests or procedures are requested. Billing admins can view pending records, inspect itemized costs, and update payment status when payment is received.

Scenario

1. Doctor requests a lab test and the system calculates the total cost for tests requested on that date.

SELECT SUM(lt.cost) AS total_cost
FROM performed_lab_tests plt
JOIN lab_tests lt ON plt.test_id = lt.test_id
WHERE plt.patient_id = (
SELECT patient_id 
FROM users 
WHERE username = 'maja.veljanova')
  AND plt.test_date = '2026-06-05';

2. System creates a billing record for the patient on that date.

INSERT INTO billing (total_cost, payment_status, record_id, admin_id)
VALUES (
  75.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;

3. System links the performed lab test to the billing record.

INSERT INTO billing_lab_tests (bill_id, test_id)
VALUES (
  (SELECT MAX(bill_id) FROM billing),
  (SELECT test_id FROM lab_tests WHERE test_name = 'Complete Blood Count')
);

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

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

5. Billing admin logs in and retrieves all pending billing records.

SELECT
  b.bill_id,
  u_p.first_name AS patient_first_name,
  u_p.last_name AS patient_last_name,
  b.total_cost,
  b.payment_status
FROM billing b
JOIN medical_records mr ON b.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
JOIN users u_p ON p.patient_id = u_p.patient_id
WHERE b.payment_status = 'PENDING'
ORDER BY b.bill_id;

6. Admin views the detailed billing record with itemized costs.

SELECT
  b.bill_id,
  b.total_cost,
  b.payment_status,
  u_p.first_name AS patient_first_name,
  u_p.last_name AS patient_last_name,
  p.embg,
  lt.test_name,
  lt.cost AS test_cost,
  pr.procedure_type,
  pr.cost AS procedure_cost
FROM billing b
JOIN medical_records mr ON b.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
JOIN users u_p ON p.patient_id = u_p.patient_id
LEFT JOIN billing_lab_tests blt ON b.bill_id = blt.bill_id
LEFT JOIN lab_tests lt ON blt.test_id = lt.test_id
LEFT JOIN billing_procedures bp ON b.bill_id = bp.bill_id
LEFT JOIN procedures pr ON bp.procedure_id = pr.procedure_id
WHERE b.bill_id = (SELECT MAX(bill_id) FROM billing);

7. Admin marks the billing record as paid once payment is received.

UPDATE billing
SET payment_status = 'PAID',
    payment_date = '2026-06-09'
WHERE bill_id = (SELECT MAX(bill_id) FROM billing WHERE payment_status = 'PENDING');