= Advanced Database Development =


----

== Allergy–prescription safety enforcement ==

A patient should never be prescribed a medication that conflicts with one of their documented allergies. This requirement blocks the unsafe insert outright, at the database level before it can happen: a new **prescription_medical_records** row is rejected if the prescription is linked, via **allergy_prescription_restrictions** → **prescription_restriction** to any allergy already on that patient's medical record.

=== Custom domains ===

{{{
CREATE DOMAIN non_negative_currency AS DECIMAL(12,2)
    CHECK (VALUE >= 0);
}}}

=== Triggers ===

{{{
CREATE TABLE IF NOT EXISTS prescription_allergy_conflicts_log (
    log_id BIGSERIAL PRIMARY KEY,
    record_id BIGINT NOT NULL REFERENCES medical_records(record_id),
    prescription_id BIGINT NOT NULL REFERENCES prescriptions(prescription_id),
    allergy_id BIGINT NOT NULL REFERENCES allergies(allergy_id),
    conflict_type TEXT CHECK (conflict_type IN ('ACTIVE', 'RESOLVED')),
    detected_date TIMESTAMP DEFAULT NOW(),
    resolution_notes TEXT,
    resolved_date TIMESTAMP
);

CREATE OR REPLACE FUNCTION t1_prescription_allergy_check()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_patient_id BIGINT;
    v_conflict_allergy_id BIGINT;
    v_allergy_name TEXT;
BEGIN
    SELECT patient_id INTO v_patient_id
    FROM medical_records
    WHERE record_id = NEW.record_id;

    IF v_patient_id IS NULL THEN
        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
    END IF;

    SELECT apr.allergy_id, a.name
    INTO v_conflict_allergy_id, v_allergy_name
    FROM prescription_restriction pr_rest
    JOIN allergy_prescription_restrictions apr ON apr.restriction_id = pr_rest.restriction_id
    JOIN allergies a ON apr.allergy_id = a.allergy_id
    JOIN medical_record_allergies mra ON a.allergy_id = mra.allergy_id
    WHERE pr_rest.prescription_id = NEW.prescription_id
      AND mra.record_id = NEW.record_id
    LIMIT 1;

    IF v_conflict_allergy_id IS NOT NULL THEN
        RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) in patient''s record %',
            NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, NEW.record_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_prescription_allergy_check ON prescription_medical_records;
CREATE TRIGGER trg_prescription_allergy_check
    BEFORE INSERT
    ON prescription_medical_records
    FOR EACH ROW
    EXECUTE FUNCTION t1_prescription_allergy_check();
}}}

=== Views ===

{{{
CREATE OR REPLACE VIEW v_prescription_allergy_conflicts AS
SELECT
    mr.record_id,
    p.patient_id,
    p.first_name,
    p.last_name,
    pmr.prescription_id,
    pr.medication_name,
    a.allergy_id,
    a.name AS allergy_name,
    a.allergy_severity,
    apr.restriction_id,
    pr_rest.description AS restriction_description,
    CASE
        WHEN mra.record_id IS NOT NULL THEN 'ACTIVE_CONFLICT'
        ELSE 'ARCHIVED'
    END AS conflict_status
FROM prescription_medical_records pmr
JOIN medical_records mr ON pmr.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
JOIN prescriptions pr ON pmr.prescription_id = pr.prescription_id
JOIN prescription_restriction pr_rest ON pr.prescription_id = pr_rest.prescription_id
JOIN allergy_prescription_restrictions apr ON pr_rest.restriction_id = apr.restriction_id
JOIN allergies a ON apr.allergy_id = a.allergy_id
LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id
    AND a.allergy_id = mra.allergy_id
WHERE a.allergy_severity IN ('HIGH', 'CRITICAL')
ORDER BY p.patient_id, a.allergy_severity DESC;
}}}

----

== Appointment scheduling integrity ==

Appointments must obey real scheduling constraints so no scheduling in the past, no doublebooking the same doctor or patient in an overlapping time, no marking an appointment `COMPLETED` before its scheduled time, and valid status transitions only (`SCHEDULED → IN_PROGRESS/COMPLETED/CANCELLED`, `IN_PROGRESS → COMPLETED/CANCELLED`, nothing further out of `COMPLETED`/`CANCELLED`/`NO_SHOW`). A background job autotransitions appointments that remain `SCHEDULED` if they are 45+ minutes past their time to `NO_SHOW`.

=== Schema change  ===

{{{

ALTER TABLE appointments DROP CONSTRAINT appointments_status_chk;
ALTER TABLE appointments ADD CONSTRAINT appointments_status_chk
    CHECK (status IN ('SCHEDULED','COMPLETED','CANCELLED','IN_PROGRESS','NO_SHOW'));
}}}

=== Triggers ===

{{{
CREATE OR REPLACE FUNCTION is_valid_appointment_transition(p_old TEXT, p_new TEXT)
RETURNS BOOLEAN
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE
           WHEN p_old = p_new THEN TRUE
           WHEN p_old = 'SCHEDULED'    AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN TRUE
           WHEN p_old = 'IN_PROGRESS'  AND p_new IN ('COMPLETED', 'CANCELLED') THEN TRUE
           ELSE FALSE
           END;
$$;

CREATE OR REPLACE FUNCTION trigger_appointments_enforce()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_combined_datetime TIMESTAMP;
BEGIN
    v_combined_datetime := NEW.appointment_date::TIMESTAMP + NEW.appointment_time;

    IF TG_OP = 'INSERT' AND v_combined_datetime < NOW() THEN
        RAISE EXCEPTION 'Cannot schedule appointment in the past (appointment_date=%, appointment_time=%)',
            NEW.appointment_date, NEW.appointment_time;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF NOT is_valid_appointment_transition(OLD.status, NEW.status) THEN
            RAISE EXCEPTION 'Appointment status cannot transition from % to %',
                OLD.status, NEW.status;
        END IF;

        IF NEW.status = 'COMPLETED' AND v_combined_datetime > NOW() THEN
            RAISE EXCEPTION 'Cannot mark appointment COMPLETED before its scheduled time (scheduled for %)',
                v_combined_datetime;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trigger_appointments_enforce ON appointments;
CREATE TRIGGER trigger_appointments_enforce
    BEFORE INSERT OR UPDATE
    ON appointments
    FOR EACH ROW
    EXECUTE FUNCTION trigger_appointments_enforce();

CREATE OR REPLACE FUNCTION t1_appointments_no_overlap()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_start TIMESTAMP;
    v_new_end TIMESTAMP;
BEGIN
    IF NEW.status NOT IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED') THEN
        RETURN NEW;
    END IF;

    v_new_start := NEW.appointment_date::TIMESTAMP + NEW.appointment_time;
    v_new_end := v_new_start + INTERVAL '30 minutes';

    IF EXISTS (
        SELECT 1 FROM appointments a
        WHERE a.doctor_id = NEW.doctor_id
          AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED')
          AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end
          AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes')
          AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
    ) THEN
        RAISE EXCEPTION 'Doctor % has overlapping appointment at %', NEW.doctor_id, NEW.appointment_date;
    END IF;

    IF EXISTS (
        SELECT 1 FROM appointments a
        WHERE a.patient_id = NEW.patient_id
          AND a.status IN ('SCHEDULED', 'IN_PROGRESS', 'COMPLETED')
          AND (a.appointment_date::TIMESTAMP + a.appointment_time) < v_new_end
          AND v_new_start < (a.appointment_date::TIMESTAMP + a.appointment_time + INTERVAL '30 minutes')
          AND (TG_OP <> 'UPDATE' OR a.appointment_id <> NEW.appointment_id)
    ) THEN
        RAISE EXCEPTION 'Patient % has overlapping appointment at %', NEW.patient_id, NEW.appointment_date;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trigger_appointments_no_overlap ON appointments;
CREATE TRIGGER trigger_appointments_no_overlap
    BEFORE INSERT OR UPDATE
    ON appointments
    FOR EACH ROW
    EXECUTE FUNCTION t1_appointments_no_overlap();
}}}

=== Stored procedures/functions ===

{{{
CREATE OR REPLACE PROCEDURE job_mark_no_show()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE appointments
    SET status = 'NO_SHOW'
    WHERE status = 'SCHEDULED'
      AND (appointment_date::TIMESTAMP + appointment_time) < (NOW() - INTERVAL '45 minutes');
END;
$$;
}}}

=== Views ===

{{{
CREATE OR REPLACE VIEW v_overdue_appointments AS
SELECT
    a.appointment_id, a.patient_id, p.first_name, p.last_name,
    a.doctor_id, d.first_name AS doctor_first_name, d.last_name AS doctor_last_name,
    a.appointment_date, a.appointment_time, a.status,
    NOW() - (a.appointment_date::TIMESTAMP + a.appointment_time) AS time_overdue
FROM appointments a
    JOIN patients p ON a.patient_id = p.patient_id
    JOIN doctors d ON a.doctor_id = d.doctor_id
WHERE a.status = 'SCHEDULED'
  AND (a.appointment_date::TIMESTAMP + a.appointment_time) < (NOW() - INTERVAL '45 minutes')
ORDER BY time_overdue DESC;
}}}

----

== Medical record consistency ==

**Diagnoses, procedures, and referrals** can be created independently but must stay consistent with the patient they actually serve as a mismatch like a diagnosis for one patient attached to a different patient's record, a doctor referring a patient to themselves, is both an **integrity problem** and a clinical **safety risk**. 

=== Triggers ===

{{{
CREATE OR REPLACE FUNCTION t1_diagnosis_record_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_record_patient BIGINT;
    v_diagnosis_patient BIGINT;
BEGIN
    SELECT patient_id INTO v_record_patient FROM medical_records WHERE record_id = NEW.record_id;
    SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id;

    IF v_record_patient IS NULL THEN
        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
    END IF;

    IF v_diagnosis_patient IS NULL THEN
        RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id;
    END IF;

    IF v_record_patient <> v_diagnosis_patient THEN
        RAISE EXCEPTION 'Diagnosis % belongs to patient %, but medical record % belongs to patient %',
            NEW.diagnosis_id, v_diagnosis_patient, NEW.record_id, v_record_patient;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_diagnosis_record_consistency ON diagnosis_medical_records;
CREATE TRIGGER trg_diagnosis_record_consistency
    BEFORE INSERT ON diagnosis_medical_records
    FOR EACH ROW EXECUTE FUNCTION t1_diagnosis_record_consistency();

CREATE OR REPLACE FUNCTION t2_procedure_diagnosis_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_diagnosis_patient BIGINT;
BEGIN
    IF NEW.diagnosis_id IS NOT NULL THEN
        SELECT patient_id INTO v_diagnosis_patient FROM diagnosis WHERE diagnosis_id = NEW.diagnosis_id;

        IF v_diagnosis_patient IS NULL THEN
            RAISE EXCEPTION 'Diagnosis % not found', NEW.diagnosis_id;
        END IF;

        IF NEW.patient_id <> v_diagnosis_patient THEN
            RAISE EXCEPTION 'Procedure belongs to patient %, but diagnosis % belongs to patient %',
                NEW.patient_id, NEW.diagnosis_id, v_diagnosis_patient;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_procedure_diagnosis_consistency ON performed_procedures;
CREATE TRIGGER trg_procedure_diagnosis_consistency
    BEFORE INSERT OR UPDATE ON performed_procedures
    FOR EACH ROW EXECUTE FUNCTION t2_procedure_diagnosis_consistency();

CREATE OR REPLACE FUNCTION t3_referral_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM medical_records WHERE record_id = NEW.record_id) THEN
        RAISE EXCEPTION 'Medical record % not found', NEW.record_id;
    END IF;

    IF NEW.from_doctor_id = NEW.to_doctor_id THEN
        RAISE EXCEPTION 'Doctor % cannot refer to themselves', NEW.from_doctor_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_referral_consistency ON referrals;
CREATE TRIGGER trg_referral_consistency
    BEFORE INSERT ON referrals
    FOR EACH ROW EXECUTE FUNCTION t3_referral_consistency();
}}}

=== Views ===

{{{
CREATE OR REPLACE VIEW v_medical_record_overview AS
SELECT
    mr.record_id,
    p.patient_id, p.first_name, p.last_name, p.embg,
    COUNT(DISTINCT dmr.diagnosis_id) AS diagnosis_count,
    COUNT(DISTINCT CASE WHEN d.patient_id IS NOT NULL AND d.patient_id <> p.patient_id THEN dmr.diagnosis_id END) AS diagnosis_mismatches,
    COUNT(DISTINCT mrp.procedure_id) AS procedures_count,
    COUNT(DISTINCT mrl.result_id) AS lab_results_count,
    COUNT(DISTINCT ref.referral_id) AS referrals_count,
    COUNT(DISTINCT mra.allergy_id) AS allergies_count,
    COUNT(DISTINCT CASE WHEN ref.from_doctor_id = ref.to_doctor_id THEN ref.referral_id END) AS self_referrals_detected
FROM medical_records mr
    JOIN patients p ON mr.patient_id = p.patient_id
    LEFT JOIN diagnosis_medical_records dmr ON dmr.record_id = mr.record_id
    LEFT JOIN diagnosis d ON d.diagnosis_id = dmr.diagnosis_id
    LEFT JOIN medical_record_procedures mrp ON mrp.record_id = mr.record_id
    LEFT JOIN medical_record_lab_results mrl ON mrl.record_id = mr.record_id
    LEFT JOIN referrals ref ON mr.record_id = ref.record_id
    LEFT JOIN medical_record_allergies mra ON mr.record_id = mra.record_id
GROUP BY mr.record_id, p.patient_id, p.first_name, p.last_name, p.embg;
}}}

----

== Billing integrity ==

Billing records must stay in sync with their line items and payment status must follow a valid state machine. **billing.total_cost** is automatically recalculated whenever a procedure or lab test is added to or removed from a bill, rather than relying on the application to keep it in sync manually. **payment_status** may only move `PENDING → PAID` or `PENDING → CANCELLED` but never backwards once finalized and every status change is written to an audit log.  Since **billing** originally had no "issued" date , a **created_at** column was added so outstanding `PENDING` bills can be tracked for follow-up.

=== Schema change ===

{{{

ALTER TABLE billing ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT NOW();

CREATE TABLE IF NOT EXISTS billing_audit_log (
    audit_id BIGSERIAL PRIMARY KEY,
    bill_id BIGINT NOT NULL REFERENCES billing(bill_id),
    old_amount DECIMAL(12,2),
    new_amount DECIMAL(12,2),
    old_status TEXT,
    new_status TEXT,
    change_type TEXT CHECK (change_type IN ('INSERT', 'UPDATE', 'LINE_ITEM_ADD', 'LINE_ITEM_REMOVE')),
    changed_at TIMESTAMP DEFAULT NOW()
);
}}}

=== Stored procedures/functions ===

{{{


CREATE OR REPLACE FUNCTION recalculate_billing_total(p_bill_id BIGINT, p_change_type TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    v_procedure_total DECIMAL;
    v_lab_total DECIMAL;
    v_new_total DECIMAL;
BEGIN
    SELECT COALESCE(SUM(p.cost), 0) INTO v_procedure_total
    FROM billing_procedures bp
    JOIN procedures p ON p.procedure_id = bp.procedure_id
    WHERE bp.bill_id = p_bill_id;

    SELECT COALESCE(SUM(lt.cost), 0) INTO v_lab_total
    FROM billing_lab_tests blt
    JOIN lab_tests lt ON lt.test_id = blt.test_id
    WHERE blt.bill_id = p_bill_id;

    v_new_total := v_procedure_total + v_lab_total;

    UPDATE billing SET total_cost = v_new_total WHERE bill_id = p_bill_id;

    INSERT INTO billing_audit_log (bill_id, new_amount, change_type)
    VALUES (p_bill_id, v_new_total, p_change_type);
END;
$$;

CREATE OR REPLACE FUNCTION is_valid_billing_transition(p_old TEXT, p_new TEXT)
RETURNS BOOLEAN
LANGUAGE sql
IMMUTABLE
AS $$
SELECT CASE
           WHEN p_old = p_new THEN TRUE
           WHEN p_old = 'PENDING' AND p_new IN ('PAID', 'CANCELLED') THEN TRUE
           ELSE FALSE
           END;
$$;

CREATE OR REPLACE PROCEDURE job_billing_alerts()
LANGUAGE plpgsql
AS $$
DECLARE
    v_overdue_count INT;
BEGIN
    SELECT COUNT(*) INTO v_overdue_count FROM v_overdue_billings WHERE days_outstanding > 30;
    RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count;
END;
$$;
}}}

=== Triggers ===

{{{
-- one shared trigger function, reused for both billing_procedures and billing_lab_tests
-- (both tables have a bill_id column, so NEW.bill_id / OLD.bill_id works either way)
CREATE OR REPLACE FUNCTION t1_billing_line_item_changed()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        PERFORM recalculate_billing_total(OLD.bill_id, 'LINE_ITEM_REMOVE');
        RETURN OLD;
    ELSE
        PERFORM recalculate_billing_total(NEW.bill_id, 'LINE_ITEM_ADD');
        RETURN NEW;
    END IF;
END;
$$;

DROP TRIGGER IF EXISTS trg_billing_procedures_update_total ON billing_procedures;
CREATE TRIGGER trg_billing_procedures_update_total
    AFTER INSERT OR DELETE ON billing_procedures
    FOR EACH ROW
    EXECUTE FUNCTION t1_billing_line_item_changed();

DROP TRIGGER IF EXISTS trg_billing_lab_tests_update_total ON billing_lab_tests;
CREATE TRIGGER trg_billing_lab_tests_update_total
    AFTER INSERT OR DELETE ON billing_lab_tests
    FOR EACH ROW
    EXECUTE FUNCTION t1_billing_line_item_changed();

CREATE OR REPLACE FUNCTION t2_billing_status_transition()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT is_valid_billing_transition(OLD.payment_status, NEW.payment_status) THEN
        RAISE EXCEPTION 'Cannot transition billing status from % to %',
            OLD.payment_status, NEW.payment_status;
    END IF;

    IF OLD.payment_status <> NEW.payment_status THEN
        INSERT INTO billing_audit_log (bill_id, old_status, new_status, change_type)
        VALUES (NEW.bill_id, OLD.payment_status, NEW.payment_status, 'UPDATE');
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_billing_status_transition ON billing;
CREATE TRIGGER trg_billing_status_transition
    BEFORE UPDATE ON billing
    FOR EACH ROW
    EXECUTE FUNCTION t2_billing_status_transition();
}}}

=== Views ===

{{{
CREATE OR REPLACE VIEW v_overdue_billings AS
SELECT
    b.bill_id,
    p.patient_id, p.first_name, p.last_name,
    b.total_cost, b.payment_status, b.created_at,
    CURRENT_DATE - b.created_at::DATE AS days_outstanding,
    CASE
        WHEN CURRENT_DATE - b.created_at::DATE > 60 THEN 'CRITICAL'
        WHEN CURRENT_DATE - b.created_at::DATE > 30 THEN 'OVERDUE'
        ELSE 'PENDING'
    END AS urgency
FROM billing b
JOIN medical_records mr ON b.record_id = mr.record_id
JOIN patients p ON mr.patient_id = p.patient_id
WHERE b.payment_status = 'PENDING'
  AND CURRENT_DATE - b.created_at::DATE >= 30
ORDER BY days_outstanding DESC;
}}}

----

== Custom domains for EMBG and Phone number formats ==

Custom domains replace one-off column checks with reusable types. **embg_format, email_format, and phone_number_format** are new or stricter than before; **non_negative_cost** consolidates a rule already repeated on three tables. non_negative_cost can be applied immediately, since it matches an existing rule. The other three need existing data checked first, since a stricter domain fails if any row doesn't already conform.

=== Custom domains ===

{{{
CREATE DOMAIN embg_format AS TEXT
    CHECK (VALUE ~ '^\d{13}$');

CREATE DOMAIN email_format AS TEXT
    CHECK (
        VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
        AND LENGTH(VALUE) <= 254
    );

CREATE DOMAIN phone_number_format AS TEXT
    CHECK (VALUE ~ '^\+?[\d\s\-().]{7,20}$');

CREATE DOMAIN non_negative_cost AS DECIMAL(12,2)
    CHECK (VALUE >= 0);


ALTER TABLE procedures ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
ALTER TABLE lab_tests ALTER COLUMN cost TYPE non_negative_cost USING cost::non_negative_cost;
ALTER TABLE billing ALTER COLUMN total_cost TYPE non_negative_cost USING total_cost::non_negative_cost;

-- run these BEFORE altering embg / email / phone columns — every one of these
-- should return ZERO rows, otherwise the corresponding ALTER will fail:
--   SELECT patient_id, embg FROM patients WHERE embg !~ '^\d{13}$';
--   SELECT doctor_id, email_address FROM doctors WHERE email_address !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
--   SELECT patient_id, email_address FROM patients WHERE email_address IS NOT NULL AND email_address !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
--   SELECT patient_id, phone_number FROM patients WHERE phone_number IS NOT NULL AND phone_number !~ '^\+?[\d\s\-().]{7,20}$';
}}}

----

== Revenue reporting (materialized view and background refresh job) ==

Revenue reports need expensive joins across **billing, procedures, and lab tests**, so computing them fresh on every request is wasteful. This materialized view precomputes monthly revenue, split by procedure vs lab test. Procedure revenue is attributed down to department, since procedures carries a **doctor_id** directly. Lab revenue can't be: lab_tests has no doctor or department reference, and **billing_lab_tests** links only to the catalog **test_id**, not a specific performed instance so lab rows are reported to the clinic only.The refresh procedure was verified by manual invocation.

=== Views ===

{{{
CREATE MATERIALIZED VIEW mv_revenue_monthly AS
SELECT
    DATE_TRUNC('month', b.payment_date)::DATE AS month,
    dept.department_id,
    dept.department_name,
    'PROCEDURE' AS revenue_type,
    SUM(p.cost) AS revenue,
    COUNT(DISTINCT b.bill_id) AS transaction_count
FROM billing b
    JOIN billing_procedures bp ON bp.bill_id = b.bill_id
    JOIN procedures p ON p.procedure_id = bp.procedure_id
    JOIN doctors doc ON doc.doctor_id = p.doctor_id
    JOIN departments dept ON dept.department_id = doc.department_id
WHERE b.payment_status = 'PAID'
GROUP BY DATE_TRUNC('month', b.payment_date), dept.department_id, dept.department_name

UNION ALL

SELECT
    DATE_TRUNC('month', b.payment_date)::DATE AS month,
    NULL AS department_id,
    NULL AS department_name,
    'LAB' AS revenue_type,
    SUM(lt.cost) AS revenue,
    COUNT(DISTINCT b.bill_id) AS transaction_count
FROM billing b
    JOIN billing_lab_tests blt ON blt.bill_id = b.bill_id
    JOIN lab_tests lt ON lt.test_id = blt.test_id
WHERE b.payment_status = 'PAID'
GROUP BY DATE_TRUNC('month', b.payment_date);

CREATE INDEX idx_mv_revenue_monthly_month ON mv_revenue_monthly (month, revenue_type);

CREATE OR REPLACE VIEW v_current_month_revenue AS
SELECT month, department_id, department_name, revenue_type, revenue, transaction_count
FROM mv_revenue_monthly
WHERE month = DATE_TRUNC('month', CURRENT_DATE)::DATE
ORDER BY revenue_type, revenue DESC;
}}}

=== Stored procedures/functions ===

{{{
CREATE OR REPLACE PROCEDURE medora_job_refresh_revenue_view()
    LANGUAGE plpgsql
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW mv_revenue_monthly;
END;
$$;
}}}