= 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 prescription outright, at the database level: a new or changed **prescription_medical_records** row is rejected if the prescription is linked, via **prescription_restriction** → **allergy_prescription_restrictions**, to an allergy the patient has on file, either in **patient_allergies** or in **medical_record_allergies** on any of the patient's medical records. The trigger only prevents new conflicts; prescriptions that were recorded before it existed are listed by the **v_prescription_allergy_conflicts** view so they can be reviewed.

=== Custom domains ===

{{{
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;

ALTER TABLE procedures DROP CONSTRAINT IF EXISTS procedures_cost_chk;
ALTER TABLE lab_tests DROP CONSTRAINT IF EXISTS lab_tests_cost_chk;
ALTER TABLE billing DROP CONSTRAINT IF EXISTS billing_cost_chk;
}}}

=== Triggers ===

{{{
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 a.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 a.allergy_id = apr.allergy_id
    WHERE pr_rest.prescription_id = NEW.prescription_id
      AND (
            EXISTS (SELECT 1 FROM patient_allergies pa
                    WHERE pa.patient_id = v_patient_id
                      AND pa.allergy_id = a.allergy_id)
         OR EXISTS (SELECT 1 FROM medical_record_allergies mra
                    JOIN medical_records mr2 ON mr2.record_id = mra.record_id
                    WHERE mr2.patient_id = v_patient_id
                      AND mra.allergy_id = a.allergy_id)
          )
    LIMIT 1;

    IF v_conflict_allergy_id IS NOT NULL THEN
        RAISE EXCEPTION 'PRESCRIPTION_ALLERGY_CONFLICT: Prescription % conflicts with allergy % (%) of patient %',
            NEW.prescription_id, v_conflict_allergy_id, v_allergy_name, v_patient_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 OR UPDATE OF prescription_id, record_id
    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
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
WHERE EXISTS (SELECT 1 FROM patient_allergies pa
              WHERE pa.patient_id = p.patient_id
                AND pa.allergy_id = a.allergy_id)
   OR EXISTS (SELECT 1 FROM medical_record_allergies mra
              JOIN medical_records mr2 ON mr2.record_id = mra.record_id
              WHERE mr2.patient_id = p.patient_id
                AND mra.allergy_id = a.allergy_id)
ORDER BY
    p.patient_id,
    CASE a.allergy_severity
        WHEN 'CRITICAL' THEN 1
        WHEN 'HIGH' THEN 2
        WHEN 'MEDIUM' THEN 3
        ELSE 4
    END;
}}}

----

== Appointment scheduling integrity ==

Appointments must obey real scheduling constraints: no scheduling in the past (when an appointment is created or moved to a new date or time), no double-booking 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/NO_SHOW`, `IN_PROGRESS → COMPLETED/CANCELLED`, nothing further out of `COMPLETED`/`CANCELLED`/`NO_SHOW`). Each appointment is treated as a 30-minute slot, and only `SCHEDULED`, `IN_PROGRESS` and `COMPLETED` appointments occupy a slot. The **job_mark_no_show** procedure moves appointments that are still `SCHEDULED` 45+ minutes past their time to `NO_SHOW`; it is run with `CALL job_mark_no_show();`, either manually or from a scheduler such as pg_cron, and **v_overdue_appointments** shows the appointments it will pick up.

=== 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', 'NO_SHOW') 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 (NEW.appointment_date, NEW.appointment_time) IS DISTINCT FROM (OLD.appointment_date, OLD.appointment_time)
           AND v_combined_datetime < NOW() THEN
            RAISE EXCEPTION 'Cannot move appointment % into the past (appointment_date=%, appointment_time=%)',
                NEW.appointment_id, NEW.appointment_date, NEW.appointment_time;
        END IF;

        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;
    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;

    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 an overlapping appointment at % %',
            NEW.doctor_id, NEW.appointment_date, NEW.appointment_time;
    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 an overlapping appointment at % %',
            NEW.patient_id, NEW.appointment_date, NEW.appointment_time;
    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. Linking a diagnosis to a different patient's medical record, linking a performed procedure to a diagnosis that belongs to another patient, or a doctor referring a patient to themselves is both an **integrity problem** and a clinical **safety risk**, so these are rejected on insert and on update. The **v_medical_record_overview** view summarises each medical record and flags diagnosis mismatches and self-referrals that were recorded before the triggers existed.

=== 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 OR UPDATE 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 NEW.from_doctor_id = NEW.to_doctor_id THEN
        RAISE EXCEPTION 'Doctor % cannot refer a patient 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 OR UPDATE 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 <> 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 ref.record_id = mr.record_id
    LEFT JOIN medical_record_allergies mra ON mra.record_id = mr.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 recalculated automatically whenever a procedure or lab test is added to or removed from a bill, as the sum of the list prices (`cost`) of its line items, rather than relying on the application to keep it in sync. Line items can only be changed while the bill is `PENDING`, so a `PAID` or `CANCELLED` bill can no longer change its amount. payment_status may only move `PENDING → PAID` or `PENDING → CANCELLED`, never backwards once finalized; when a bill becomes `PAID` without a payment_date, the current date is filled in. Every billing status change and every change to the calculated total caused by adding or removing a billing line item is recorded in billing_audit_log. The existing created_at column is used to track outstanding `PENDING` bills: **v_overdue_billings** lists bills that have been pending for more than 30 days (`CRITICAL` after 60 days), and job_billing_alerts reports how many need follow-up.

=== Schema change ===

{{{
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_old_total DECIMAL;
    v_status TEXT;
    v_procedure_total DECIMAL;
    v_lab_total DECIMAL;
    v_new_total DECIMAL;
BEGIN
    SELECT total_cost, payment_status
    INTO v_old_total, v_status
    FROM billing
    WHERE bill_id = p_bill_id;

    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Bill % not found', p_bill_id;
    END IF;

    IF v_status <> 'PENDING' THEN
        RAISE EXCEPTION 'Bill % is % and its line items can no longer be changed', p_bill_id, v_status;
    END IF;

    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, old_amount, new_amount, change_type)
    VALUES (p_bill_id, v_old_total, 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;
    RAISE NOTICE 'Found % overdue billing records requiring follow-up', v_overdue_count;
END;
$$;
}}}

=== Triggers ===

{{{
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 NEW.payment_status = 'PAID' AND NEW.payment_date IS NULL THEN
        NEW.payment_date := CURRENT_DATE;
    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'
        ELSE 'OVERDUE'
    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. **non_negative_cost** consolidates the `cost >= 0` rule that was repeated on **procedures**, **lab_tests** and **billing**; since it matches an existing rule it is applied immediately and the three old CHECK constraints are dropped. It is created in the Custom domains block of the first section on this page, because the column types must be changed before any view that uses these columns is created (such as **v_overdue_billings** and **mv_revenue_monthly**), and PostgreSQL cannot change the type of a column that a view depends on. **embg_format** (the same rule as the existing `patients_embg_format_chk`), **email_format** and **phone_number_format** are defined for reuse but not yet applied to any column: email_format is stricter than the existing checks (for example, it requires a domain ending such as `.com`, which the admin and lab technician checks do not), so existing data has to be checked before it is applied.

=== 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}$');
}}}

----

== 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 from `PAID` bills, split by procedure vs lab test and grouped by the month of **payment_date**. 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 for the clinic as a whole. The view is refreshed with `CALL medora_job_refresh_revenue_view();`, either manually or from a scheduler such as pg_cron; the refresh procedure was verified by manual invocation.

=== Views ===

{{{
DROP MATERIALIZED VIEW IF EXISTS mv_revenue_monthly CASCADE;

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'
  AND b.payment_date IS NOT NULL
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'
  AND b.payment_date IS NOT NULL
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;
$$;
}}}