= UseCase13PrototypeImplementation - 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. After a request for a lab test or a procedure the system calculates the total cost for tests requested on that date for that patient.


{{{
#!sql
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 = 4
  AND plt.test_date = '2026-06-16';
}}}


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

[[Image(uc013-1.png, width=100%)]]

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

3. Billing Admin views the detailed billing record with itemized costs.

[[Image(uc013-2.png, width=100%)]]

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

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

[[Image(uc013-3.png, width=100%)]]

{{{
#!sql
UPDATE billing
SET payment_status = 'PAID',
    payment_date = '2026-06-16'
WHERE bill_id = (SELECT MAX(bill_id) FROM billing WHERE payment_status = 'PENDING');
}}}