Billing & Claims Management Data Specifications

CREATE TABLE charges (
    charge_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id             BIGINT NOT NULL,
    encounter_id           BIGINT NOT NULL,
    provider_id            BIGINT NOT NULL,
    facility_id            BIGINT NOT NULL,
    department_id          BIGINT NOT NULL,
    fee_schedule_item_id   BIGINT,
    service_date           DATE NOT NULL,
    cpt_code               VARCHAR(10),
    hcpcs_code             VARCHAR(10),
    icd10_codes            VARCHAR(255),
    modifier_codes         VARCHAR(50),
    quantity               INTEGER NOT NULL DEFAULT 1,
    charge_amount          NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    contracted_rate        NUMERIC(12,2),
    charge_status          VARCHAR(30) NOT NULL DEFAULT 'pending',
    source_module          VARCHAR(50) NOT NULL,
    source_order_id        VARCHAR(50),
    is_uae_vat_applicable  BOOLEAN NOT NULL DEFAULT FALSE,
    vat_rate               NUMERIC(5,2),
    vat_amount             NUMERIC(12,2),
    notes                  TEXT,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by             BIGINT NOT NULL,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by             BIGINT,
    CONSTRAINT fk_charges_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_charges_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_charges_provider
        FOREIGN KEY (provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_charges_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_charges_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_charges_fee_schedule_item
        FOREIGN KEY (fee_schedule_item_id) REFERENCES fee_schedule_items(item_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_charges_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_charges_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_charges_quantity_positive
        CHECK (quantity > 0),
    CONSTRAINT chk_charges_amounts_nonnegative
        CHECK (charge_amount >= 0 AND (contracted_rate IS NULL OR contracted_rate >= 0)),
    CONSTRAINT chk_charges_vat
        CHECK (
            (is_uae_vat_applicable = FALSE AND vat_rate IS NULL AND vat_amount IS NULL)
            OR (is_uae_vat_applicable = TRUE AND vat_rate IS NOT NULL AND vat_amount IS NOT NULL)
        ),
    CONSTRAINT chk_charges_status
        CHECK (charge_status IN ('pending','validated','on_hold','posted','voided'))
);

CREATE INDEX idx_charges_patient ON charges(patient_id);
CREATE INDEX idx_charges_encounter ON charges(encounter_id);
CREATE INDEX idx_charges_provider ON charges(provider_id);
CREATE INDEX idx_charges_status ON charges(charge_status);
CREATE INDEX idx_charges_service_date ON charges(service_date);
CREATE INDEX idx_charges_source ON charges(source_module, source_order_id);
CREATE INDEX idx_charges_facility_dept_date
    ON charges(facility_id, department_id, service_date);

CREATE TABLE charge_details (
    detail_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    charge_id             BIGINT NOT NULL,
    revenue_code          VARCHAR(4),
    cost_center           VARCHAR(50),
    drg_code              VARCHAR(10),
    case_weight           NUMERIC(6,3),
    ndc_code              VARCHAR(20),
    units                 INTEGER,
    service_description_en VARCHAR(255),
    service_description_ar VARCHAR(255),
    additional_attributes JSONB,
    CONSTRAINT fk_charge_details_charge
        FOREIGN KEY (charge_id) REFERENCES charges(charge_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_charge_details_case_weight
        CHECK (case_weight IS NULL OR case_weight >= 0),
    CONSTRAINT chk_charge_details_units
        CHECK (units IS NULL OR units >= 0)
);

CREATE INDEX idx_charge_details_charge ON charge_details(charge_id);
CREATE INDEX idx_charge_details_revenue ON charge_details(revenue_code);

CREATE TABLE claims (
    claim_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    encounter_id          BIGINT NOT NULL,
    payer_id              BIGINT NOT NULL,
    plan_id               BIGINT,
    contract_id           BIGINT,
    claim_type            VARCHAR(20) NOT NULL,
    claim_number          VARCHAR(30) NOT NULL,
    total_charge_amount   NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    total_allowed_amount  NUMERIC(14,2),
    total_paid_amount     NUMERIC(14,2),
    total_adjusted_amount NUMERIC(14,2),
    patient_responsibility NUMERIC(14,2),
    claim_status          VARCHAR(30) NOT NULL DEFAULT 'draft',
    submission_date       DATE,
    adjudication_date     DATE,
    is_resubmission       BOOLEAN NOT NULL DEFAULT FALSE,
    original_claim_number VARCHAR(30),
    emirate_code          VARCHAR(3) NOT NULL,
    notes                 TEXT,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT NOT NULL,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by            BIGINT,
    CONSTRAINT uq_claims_number UNIQUE (claim_number),
    CONSTRAINT fk_claims_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_claims_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_claims_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_claims_plan
        FOREIGN KEY (plan_id) REFERENCES insurance_plans(plan_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_claims_contract
        FOREIGN KEY (contract_id) REFERENCES contracts(contract_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_claims_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_claims_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_claims_type
        CHECK (claim_type IN ('inpatient','outpatient','emergency','day_case','pharmacy')),
    CONSTRAINT chk_claims_status
        CHECK (claim_status IN (
            'draft','ready','submitted','in_review',
            'paid','partially_paid','denied','voided'
        )),
    CONSTRAINT chk_claims_amounts_nonnegative
        CHECK (
            total_charge_amount >= 0
            AND (total_allowed_amount IS NULL OR total_allowed_amount >= 0)
            AND (total_paid_amount IS NULL OR total_paid_amount >= 0)
            AND (total_adjusted_amount IS NULL OR total_adjusted_amount >= 0)
            AND (patient_responsibility IS NULL OR patient_responsibility >= 0)
        ),
    CONSTRAINT chk_claims_emirate
        CHECK (emirate_code IN ('DXB','AUH','SHJ','AJM','RAK','UAQ','FJR'))
);

CREATE INDEX idx_claims_patient ON claims(patient_id);
CREATE INDEX idx_claims_payer_status ON claims(payer_id, claim_status);
CREATE INDEX idx_claims_encounter ON claims(encounter_id);
CREATE INDEX idx_claims_submission_date ON claims(submission_date);
CREATE INDEX idx_claims_emirate ON claims(emirate_code);

CREATE TABLE claim_lines (
    line_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    claim_id           BIGINT NOT NULL,
    charge_id          BIGINT,
    line_number        INTEGER NOT NULL,
    cpt_code           VARCHAR(10) NOT NULL,
    modifier_codes     VARCHAR(50),
    icd10_pointer      VARCHAR(20),
    quantity           INTEGER NOT NULL DEFAULT 1,
    charge_amount      NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    allowed_amount     NUMERIC(12,2),
    paid_amount        NUMERIC(12,2),
    adjustment_amount  NUMERIC(12,2),
    denial_code        VARCHAR(10),
    line_status        VARCHAR(30) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_claim_lines_claim
        FOREIGN KEY (claim_id) REFERENCES claims(claim_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_claim_lines_charge
        FOREIGN KEY (charge_id) REFERENCES charges(charge_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT uq_claim_lines_claim_line
        UNIQUE (claim_id, line_number),
    CONSTRAINT chk_claim_lines_quantity
        CHECK (quantity > 0),
    CONSTRAINT chk_claim_lines_amounts
        CHECK (
            charge_amount >= 0
            AND (allowed_amount IS NULL OR allowed_amount >= 0)
            AND (paid_amount IS NULL OR paid_amount >= 0)
            AND (adjustment_amount IS NULL OR adjustment_amount >= 0)
        ),
    CONSTRAINT chk_claim_lines_status
        CHECK (line_status IN ('pending','paid','partially_paid','denied','adjusted'))
);

CREATE INDEX idx_claim_lines_claim ON claim_lines(claim_id);
CREATE INDEX idx_claim_lines_charge ON claim_lines(charge_id);
CREATE INDEX idx_claim_lines_status ON claim_lines(line_status);
CREATE INDEX idx_claim_lines_cpt ON claim_lines(cpt_code);

CREATE TABLE claim_submissions (
    submission_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    claim_id                  BIGINT NOT NULL,
    submission_channel        VARCHAR(30) NOT NULL,
    submission_datetime       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    batch_id                  VARCHAR(50),
    gateway_status            VARCHAR(30) NOT NULL DEFAULT 'pending',
    gateway_response          TEXT,
    response_datetime         TIMESTAMP,
    rejection_reason          TEXT,
    payer_submission_reference VARCHAR(50),
    CONSTRAINT fk_claim_submissions_claim
        FOREIGN KEY (claim_id) REFERENCES claims(claim_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_claim_submissions_channel
        CHECK (submission_channel IN ('DHA_ECLAIMLINK','DOH_ECLAIMS','PORTAL','PAPER','OTHER')),
    CONSTRAINT chk_claim_submissions_status
        CHECK (gateway_status IN ('pending','accepted','rejected'))
);

CREATE INDEX idx_claim_submissions_claim ON claim_submissions(claim_id);
CREATE INDEX idx_claim_submissions_channel_status
    ON claim_submissions(submission_channel, gateway_status);
CREATE INDEX idx_claim_submissions_batch ON claim_submissions(batch_id);

CREATE TABLE claim_responses (
    response_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    claim_id               BIGINT NOT NULL,
    submission_id          BIGINT,
    remittance_id          BIGINT,
    response_type          VARCHAR(20) NOT NULL,
    response_datetime      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    allowed_amount         NUMERIC(14,2),
    paid_amount            NUMERIC(14,2),
    total_adjustment_amount NUMERIC(14,2),
    adjustment_codes       VARCHAR(255),
    denial_codes           VARCHAR(255),
    patient_responsibility NUMERIC(14,2),
    CONSTRAINT fk_claim_responses_claim
        FOREIGN KEY (claim_id) REFERENCES claims(claim_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_claim_responses_submission
        FOREIGN KEY (submission_id) REFERENCES claim_submissions(submission_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_claim_responses_remittance
        FOREIGN KEY (remittance_id) REFERENCES remittance_advice(remittance_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_claim_responses_type
        CHECK (response_type IN ('initial','adjustment','appeal')),
    CONSTRAINT chk_claim_responses_amounts
        CHECK (
            (allowed_amount IS NULL OR allowed_amount >= 0) AND
            (paid_amount IS NULL OR paid_amount >= 0) AND
            (total_adjustment_amount IS NULL OR total_adjustment_amount >= 0) AND
            (patient_responsibility IS NULL OR patient_responsibility >= 0)
        )
);

CREATE INDEX idx_claim_responses_claim ON claim_responses(claim_id);
CREATE INDEX idx_claim_responses_remittance ON claim_responses(remittance_id);
CREATE INDEX idx_claim_responses_type ON claim_responses(response_type);

CREATE TABLE payments (
    payment_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id          BIGINT NOT NULL,
    remittance_id     BIGINT,
    payment_date      DATE NOT NULL,
    payment_amount    NUMERIC(16,2) NOT NULL DEFAULT 0.00,
    payment_method    VARCHAR(20) NOT NULL,
    check_number      VARCHAR(50),
    eft_reference     VARCHAR(50),
    bank_deposit_date DATE,
    reconciled        BOOLEAN NOT NULL DEFAULT FALSE,
    batch_id          VARCHAR(50),
    created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by        BIGINT NOT NULL,
    CONSTRAINT fk_payments_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_payments_remittance
        FOREIGN KEY (remittance_id) REFERENCES remittance_advice(remittance_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_payments_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_payments_amount
        CHECK (payment_amount >= 0),
    CONSTRAINT chk_payments_method
        CHECK (payment_method IN ('CHEQUE','EFT','CASH','OTHER'))
);

CREATE INDEX idx_payments_payer_date ON payments(payer_id, payment_date);
CREATE INDEX idx_payments_remittance ON payments(remittance_id);
CREATE INDEX idx_payments_reconciled ON payments(reconciled);

CREATE TABLE payment_allocations (
    allocation_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_id                  BIGINT NOT NULL,
    claim_id                    BIGINT NOT NULL,
    claim_line_id               BIGINT,
    allocated_amount            NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    adjustment_amount           NUMERIC(14,2),
    adjustment_code             VARCHAR(10),
    patient_responsibility_amount NUMERIC(14,2),
    created_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_payment_allocations_payment
        FOREIGN KEY (payment_id) REFERENCES payments(payment_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_payment_allocations_claim
        FOREIGN KEY (claim_id) REFERENCES claims(claim_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_payment_allocations_claim_line
        FOREIGN KEY (claim_line_id) REFERENCES claim_lines(line_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_payment_allocations_amounts
        CHECK (
            allocated_amount >= 0
            AND (adjustment_amount IS NULL OR adjustment_amount >= 0)
            AND (patient_responsibility_amount IS NULL OR patient_responsibility_amount >= 0)
        )
);

CREATE INDEX idx_payment_allocations_payment ON payment_allocations(payment_id);
CREATE INDEX idx_payment_allocations_claim ON payment_allocations(claim_id);
CREATE INDEX idx_payment_allocations_claim_line ON payment_allocations(claim_line_id);

CREATE TABLE patient_invoices (
    invoice_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id            BIGINT NOT NULL,
    invoice_date          DATE NOT NULL,
    total_amount          NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    paid_amount           NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    balance               NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    due_date              DATE,
    statement_number      VARCHAR(30) NOT NULL,
    delivery_method       VARCHAR(20),
    sent_datetime         TIMESTAMP,
    status                VARCHAR(30) NOT NULL DEFAULT 'open',
    is_payment_plan       BOOLEAN NOT NULL DEFAULT FALSE,
    payment_plan_details  JSONB,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT NOT NULL,
    CONSTRAINT uq_patient_invoices_statement UNIQUE (statement_number),
    CONSTRAINT fk_patient_invoices_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_patient_invoices_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_patient_invoices_amounts
        CHECK (total_amount >= 0 AND paid_amount >= 0 AND balance >= 0),
    CONSTRAINT chk_patient_invoices_status
        CHECK (status IN ('open','paid','partial','cancelled','written_off')),
    CONSTRAINT chk_patient_invoices_delivery
        CHECK (delivery_method IS NULL OR delivery_method IN ('PORTAL','EMAIL','SMS','PRINT'))
);

CREATE INDEX idx_patient_invoices_patient_status
    ON patient_invoices(patient_id, status);
CREATE INDEX idx_patient_invoices_due_date
    ON patient_invoices(due_date);

CREATE TABLE patient_payments (
    payment_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id       BIGINT NOT NULL,
    invoice_id       BIGINT,
    encounter_id     BIGINT,
    payment_amount   NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    payment_method   VARCHAR(20) NOT NULL,
    payment_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    receipt_number   VARCHAR(30) NOT NULL,
    collected_by     BIGINT NOT NULL,
    payment_location VARCHAR(20) NOT NULL,
    emirate_code     VARCHAR(3) NOT NULL,
    is_refundable    BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_patient_payments_receipt UNIQUE (receipt_number),
    CONSTRAINT fk_patient_payments_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_patient_payments_invoice
        FOREIGN KEY (invoice_id) REFERENCES patient_invoices(invoice_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_patient_payments_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_patient_payments_collected_by
        FOREIGN KEY (collected_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_patient_payments_amount
        CHECK (payment_amount >= 0),
    CONSTRAINT chk_patient_payments_emirate
        CHECK (emirate_code IN ('DXB','AUH','SHJ','AJM','RAK','UAQ','FJR'))
);

CREATE INDEX idx_patient_payments_patient_date
    ON patient_payments(patient_id, payment_datetime);
CREATE INDEX idx_patient_payments_location
    ON patient_payments(payment_location);
CREATE INDEX idx_patient_payments_encounter
    ON patient_payments(encounter_id);

CREATE TABLE refunds (
    refund_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id         BIGINT,
    payer_id           BIGINT,
    original_payment_id BIGINT NOT NULL,
    refund_amount      NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    refund_reason      VARCHAR(255) NOT NULL,
    refund_method      VARCHAR(20) NOT NULL,
    approved_by        BIGINT NOT NULL,
    refund_datetime    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status             VARCHAR(30) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_refunds_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_refunds_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_refunds_original_payment
        FOREIGN KEY (original_payment_id) REFERENCES payments(payment_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_refunds_approved_by
        FOREIGN KEY (approved_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_refunds_amount
        CHECK (refund_amount >= 0),
    CONSTRAINT chk_refunds_status
        CHECK (status IN ('pending','approved','processed','cancelled'))
);

CREATE INDEX idx_refunds_patient ON refunds(patient_id);
CREATE INDEX idx_refunds_payer ON refunds(payer_id);
CREATE INDEX idx_refunds_status ON refunds(status);

CREATE TABLE ar_aging (
    aging_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    snapshot_date    DATE NOT NULL,
    payer_id         BIGINT,
    patient_id       BIGINT,
    claim_id         BIGINT,
    total_outstanding NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    current_0_30     NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    days_31_60       NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    days_61_90       NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    days_91_120      NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    days_over_120    NUMERIC(14,2) NOT NULL DEFAULT 0.00,
    emirate_code     VARCHAR(3) NOT NULL,
    CONSTRAINT fk_ar_aging_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ar_aging_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ar_aging_claim
        FOREIGN KEY (claim_id) REFERENCES claims(claim_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_ar_aging_amounts
        CHECK (
            total_outstanding >= 0 AND
            current_0_30 >= 0 AND
            days_31_60 >= 0 AND
            days_61_90 >= 0 AND
            days_91_120 >= 0 AND
            days_over_120 >= 0
        ),
    CONSTRAINT chk_ar_aging_emirate
        CHECK (emirate_code IN ('DXB','AUH','SHJ','AJM','RAK','UAQ','FJR'))
);

CREATE INDEX idx_ar_aging_snapshot_payer
    ON ar_aging(snapshot_date, payer_id);
CREATE INDEX idx_ar_aging_snapshot_emirate
    ON ar_aging(snapshot_date, emirate_code);

CREATE TABLE charge_capture_rules (
    rule_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_module   VARCHAR(50) NOT NULL,
    source_event_type VARCHAR(50) NOT NULL,
    cpt_code        VARCHAR(10),
    modifier_logic  VARCHAR(255),
    bundling_rules  JSONB,
    effective_date  DATE NOT NULL,
    expiry_date     DATE,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT chk_charge_capture_rules_dates
        CHECK (expiry_date IS NULL OR expiry_date >= effective_date)
);

CREATE INDEX idx_charge_capture_rules_source
    ON charge_capture_rules(source_module, source_event_type)
    WHERE is_active = TRUE;
CREATE INDEX idx_charge_capture_rules_cpt
    ON charge_capture_rules(cpt_code);

CREATE TABLE claim_edits (
    edit_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    edit_type       VARCHAR(30) NOT NULL,
    edit_name       VARCHAR(100) NOT NULL,
    rule_logic_json JSONB NOT NULL,
    severity        VARCHAR(10) NOT NULL,
    action          VARCHAR(20) NOT NULL,
    payer_id        BIGINT,
    payer_specific  BOOLEAN NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT fk_claim_edits_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_claim_edits_severity
        CHECK (severity IN ('error','warning')),
    CONSTRAINT chk_claim_edits_action
        CHECK (action IN ('reject','pend','informational')),
    CONSTRAINT chk_claim_edits_payer_flag
        CHECK ((payer_specific = TRUE AND payer_id IS NOT NULL)
               OR (payer_specific = FALSE))
);

CREATE INDEX idx_claim_edits_type_active
    ON claim_edits(edit_type)
    WHERE is_active = TRUE;
CREATE INDEX idx_claim_edits_payer
    ON claim_edits(payer_id)
    WHERE payer_specific = TRUE AND is_active = TRUE;

CREATE TABLE remittance_advice (
    remittance_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id             BIGINT NOT NULL,
    remittance_date      DATE NOT NULL,
    total_paid           NUMERIC(16,2) NOT NULL DEFAULT 0.00,
    total_claims         INTEGER NOT NULL DEFAULT 0,
    file_reference       VARCHAR(100),
    import_datetime      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status               VARCHAR(30) NOT NULL DEFAULT 'imported',
    bank_account_reference VARCHAR(50),
    CONSTRAINT fk_remittance_advice_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_remittance_advice_amounts
        CHECK (total_paid >= 0 AND total_claims >= 0),
    CONSTRAINT chk_remittance_advice_status
        CHECK (status IN ('imported','processed','error'))
);

CREATE INDEX idx_remittance_advice_payer_date
    ON remittance_advice(payer_id, remittance_date);
CREATE INDEX idx_remittance_advice_status
    ON remittance_advice(status);