Policy & Contract Management Data Specifications

CREATE TABLE payers (
    payer_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_code            VARCHAR(30) NOT NULL UNIQUE,
    payer_name_en         VARCHAR(255) NOT NULL,
    payer_name_ar         VARCHAR(255),
    payer_type            VARCHAR(30) NOT NULL DEFAULT 'insurance',
    payer_class           VARCHAR(30) NOT NULL,
    tpa_name              VARCHAR(255),
    dha_license           VARCHAR(50),
    doh_license           VARCHAR(50),
    moh_license           VARCHAR(50),
    trade_license_number  VARCHAR(50),
    payer_classification_source VARCHAR(20),
    eclaim_routing        VARCHAR(30),
    eligibility_endpoint  VARCHAR(500),
    claims_endpoint       VARCHAR(500),
    auth_endpoint         VARCHAR(500),
    contact_email         VARCHAR(255),
    contact_phone         VARCHAR(50),
    address_line1         VARCHAR(255),
    address_line2         VARCHAR(255),
    emirate               VARCHAR(50),
    country               VARCHAR(100) NOT NULL DEFAULT 'United Arab Emirates',
    is_active             BOOLEAN NOT NULL DEFAULT TRUE,
    activation_date       DATE,
    deactivation_date     DATE,
    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 chk_payers_type
        CHECK (payer_type IN ('insurance','tpa','government','self-pay','other')),
    CONSTRAINT chk_payers_class_source
        CHECK (payer_classification_source IS NULL OR payer_classification_source IN ('DHA','DOH','MOH','internal')),
    CONSTRAINT chk_payers_eclaim_routing
        CHECK (eclaim_routing IS NULL OR eclaim_routing IN ('DHA-eClaimLink','DOH-eClaims','direct-portal','paper','other')),
    CONSTRAINT chk_payers_emirate
        CHECK (emirate IS NULL OR emirate IN ('Abu Dhabi','Dubai','Sharjah','Ajman','Umm Al Quwain','Ras Al Khaimah','Fujairah')),
    CONSTRAINT chk_payers_deactivation_after_activation
        CHECK (deactivation_date IS NULL OR activation_date IS NULL OR deactivation_date >= activation_date)
);

COMMENT ON TABLE payers IS 'Insurance payer and TPA master for UAE facilities.';
COMMENT ON COLUMN payers.payer_class IS 'Classification per DHA/DOH payer registries (e.g. THIQA, SAADA, private).';

ALTER TABLE payers
    ADD CONSTRAINT fk_payers_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_payers_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_payers_name_en ON payers (payer_name_en);
CREATE INDEX idx_payers_class ON payers (payer_class);
CREATE INDEX idx_payers_active ON payers (is_active);
CREATE INDEX idx_payers_dha_license ON payers (dha_license);
CREATE INDEX idx_payers_doh_license ON payers (doh_license);

CREATE TABLE insurance_plans (
    plan_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id               BIGINT NOT NULL,
    plan_code              VARCHAR(50) NOT NULL,
    plan_name_en           VARCHAR(255) NOT NULL,
    plan_name_ar           VARCHAR(255),
    plan_type              VARCHAR(30) NOT NULL,
    network_type           VARCHAR(30) NOT NULL,
    coverage_level         VARCHAR(30),
    payer_class            VARCHAR(30),
    annual_maximum         NUMERIC(14,2),
    lifetime_maximum       NUMERIC(14,2),
    copay_percentage       NUMERIC(5,2),
    coinsurance_percentage NUMERIC(5,2),
    deductible             NUMERIC(14,2),
    oop_maximum            NUMERIC(14,2),
    effective_date         DATE NOT NULL,
    expiry_date            DATE,
    is_active              BOOLEAN NOT NULL DEFAULT TRUE,
    requires_referral      BOOLEAN NOT NULL DEFAULT FALSE,
    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_insurance_plans_payer_code UNIQUE (payer_id, plan_code),
    CONSTRAINT chk_insurance_plans_plan_type
        CHECK (plan_type IN ('HMO','PPO','EPO','POS','Indemnity','Government','Other')),
    CONSTRAINT chk_insurance_plans_network_type
        CHECK (network_type IN ('In-Network','Out-of-Network','Tiered','Open')),
    CONSTRAINT chk_insurance_plans_copay_pct
        CHECK (copay_percentage IS NULL OR (copay_percentage >= 0 AND copay_percentage <= 100)),
    CONSTRAINT chk_insurance_plans_coins_pct
        CHECK (coinsurance_percentage IS NULL OR (coinsurance_percentage >= 0 AND coinsurance_percentage <= 100)),
    CONSTRAINT chk_insurance_plans_amounts_non_negative
        CHECK (
            (annual_maximum IS NULL OR annual_maximum >= 0) AND
            (lifetime_maximum IS NULL OR lifetime_maximum >= 0) AND
            (deductible IS NULL OR deductible >= 0) AND
            (oop_maximum IS NULL OR oop_maximum >= 0)
        ),
    CONSTRAINT chk_insurance_plans_expiry_after_effective
        CHECK (expiry_date IS NULL OR expiry_date >= effective_date)
);

COMMENT ON TABLE insurance_plans IS 'Insurance products/plans per payer used for eligibility and estimation.';

ALTER TABLE insurance_plans
    ADD CONSTRAINT fk_insurance_plans_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_insurance_plans_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_insurance_plans_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_insurance_plans_payer ON insurance_plans (payer_id);
CREATE INDEX idx_insurance_plans_active ON insurance_plans (is_active, effective_date, expiry_date);
CREATE INDEX idx_insurance_plans_code ON insurance_plans (plan_code);

CREATE TABLE contracts (
    contract_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id                 BIGINT NOT NULL,
    facility_id              BIGINT NOT NULL,
    department_id            BIGINT,
    contract_code            VARCHAR(50) NOT NULL,
    contract_type            VARCHAR(30) NOT NULL,
    rate_methodology         VARCHAR(30) NOT NULL,
    payment_terms_days       INT NOT NULL DEFAULT 30,
    grace_period_days        INT,
    effective_date           DATE NOT NULL,
    expiry_date              DATE NOT NULL,
    auto_renew               BOOLEAN NOT NULL DEFAULT FALSE,
    auto_renew_period_months INT,
    status                   VARCHAR(30) NOT NULL DEFAULT 'draft',
    approved_by              BIGINT,
    approved_date            TIMESTAMP,
    document_path            VARCHAR(500),
    version                  INT NOT NULL DEFAULT 1,
    parent_contract_id       BIGINT,
    termination_date         DATE,
    termination_reason       TEXT,
    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_contracts_code_version UNIQUE (payer_id, facility_id, contract_code, version),
    CONSTRAINT chk_contracts_type
        CHECK (contract_type IN ('fee-for-service','per-diem','case-rate','capitation','percentage-of-tariff','mixed')),
    CONSTRAINT chk_contracts_payment_terms
        CHECK (payment_terms_days > 0),
    CONSTRAINT chk_contracts_grace_period
        CHECK (grace_period_days IS NULL OR grace_period_days >= 0),
    CONSTRAINT chk_contracts_dates
        CHECK (expiry_date > effective_date),
    CONSTRAINT chk_contracts_termination_date
        CHECK (termination_date IS NULL OR termination_date >= effective_date),
    CONSTRAINT chk_contracts_status
        CHECK (status IN ('draft','under_review','pending_signature','active','expired','terminated'))
);

COMMENT ON TABLE contracts IS 'Contracts between payers and facilities, including type, rate methodology, and lifecycle status.';

ALTER TABLE contracts
    ADD CONSTRAINT fk_contracts_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_contracts_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_contracts_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_contracts_parent
        FOREIGN KEY (parent_contract_id) REFERENCES contracts(contract_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_contracts_approved_by
        FOREIGN KEY (approved_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_contracts_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_contracts_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_contracts_payer_facility ON contracts (payer_id, facility_id);
CREATE INDEX idx_contracts_status_dates ON contracts (status, effective_date, expiry_date);
CREATE INDEX idx_contracts_active ON contracts (payer_id, facility_id, status)
    WHERE status = 'active';
CREATE INDEX idx_contracts_expiry_alerts ON contracts (expiry_date)
    WHERE status IN ('active','pending_signature','under_review');

CREATE TABLE contract_fee_schedules (
    fee_schedule_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    contract_id       BIGINT NOT NULL,
    schedule_name     VARCHAR(255) NOT NULL,
    base_tariff       VARCHAR(30) NOT NULL,
    tariff_version    VARCHAR(50),
    rate_methodology  VARCHAR(30),
    effective_date    DATE NOT NULL,
    expiry_date       DATE,
    status            VARCHAR(30) NOT NULL DEFAULT 'draft',
    created_by        BIGINT NOT NULL,
    approved_by       BIGINT,
    approved_date     TIMESTAMP,
    notes             TEXT,
    created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by        BIGINT,
    CONSTRAINT chk_contract_fee_schedules_base_tariff
        CHECK (base_tariff IN ('DHA','DOH','Custom','Other')),
    CONSTRAINT chk_contract_fee_schedules_status
        CHECK (status IN ('draft','under_review','active','retired')),
    CONSTRAINT chk_contract_fee_schedules_dates
        CHECK (expiry_date IS NULL OR expiry_date >= effective_date)
);

COMMENT ON TABLE contract_fee_schedules IS 'Fee schedule headers linked to contracts, based on DHA/DOH or custom tariffs.';

ALTER TABLE contract_fee_schedules
    ADD CONSTRAINT fk_fee_schedules_contract
        FOREIGN KEY (contract_id) REFERENCES contracts(contract_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_fee_schedules_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_fee_schedules_approved_by
        FOREIGN KEY (approved_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_fee_schedules_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_fee_schedules_contract ON contract_fee_schedules (contract_id);
CREATE INDEX idx_fee_schedules_status ON contract_fee_schedules (status);
CREATE INDEX idx_fee_schedules_effective ON contract_fee_schedules (effective_date, expiry_date);

CREATE TABLE fee_schedule_items (
    item_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    fee_schedule_id    BIGINT NOT NULL,
    cpt_code           VARCHAR(10) NOT NULL,
    description        TEXT,
    modifier           VARCHAR(10),
    rate_type          VARCHAR(30) NOT NULL DEFAULT 'standard',
    facility_rate      NUMERIC(14,2),
    professional_rate  NUMERIC(14,2),
    unit_limit         INT,
    is_bundled         BOOLEAN NOT NULL DEFAULT FALSE,
    bundle_parent_cpt  VARCHAR(10),
    is_active          BOOLEAN NOT NULL DEFAULT TRUE,
    effective_date     DATE,
    expiry_date        DATE,
    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 chk_fee_items_rate_type
        CHECK (rate_type IN ('standard','discounted','premium','penalty')),
    CONSTRAINT chk_fee_items_rates_non_negative
        CHECK (
            (facility_rate IS NULL OR facility_rate >= 0) AND
            (professional_rate IS NULL OR professional_rate >= 0)
        ),
    CONSTRAINT chk_fee_items_unit_limit
        CHECK (unit_limit IS NULL OR unit_limit >= 0),
    CONSTRAINT chk_fee_items_dates
        CHECK (expiry_date IS NULL OR effective_date IS NULL OR expiry_date >= effective_date)
);

COMMENT ON TABLE fee_schedule_items IS 'CPT-to-rate mappings per contract fee schedule.';

ALTER TABLE fee_schedule_items
    ADD CONSTRAINT fk_fee_items_schedule
        FOREIGN KEY (fee_schedule_id) REFERENCES contract_fee_schedules(fee_schedule_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_fee_items_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_fee_items_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_fee_items_schedule ON fee_schedule_items (fee_schedule_id);
CREATE INDEX idx_fee_items_cpt ON fee_schedule_items (cpt_code, modifier);
CREATE INDEX idx_fee_items_active ON fee_schedule_items (fee_schedule_id, cpt_code)
    WHERE is_active = TRUE;

CREATE TABLE coverage_rules (
    rule_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id              BIGINT NOT NULL,
    plan_id               BIGINT,
    rule_type             VARCHAR(30) NOT NULL,
    cpt_codes             TEXT,
    icd10_codes           TEXT,
    age_min               INT,
    age_max               INT,
    gender                VARCHAR(10),
    frequency_limit       INT,
    frequency_period      VARCHAR(20),
    requires_prior_auth   BOOLEAN NOT NULL DEFAULT FALSE,
    benefit_limit_amount  NUMERIC(14,2),
    benefit_limit_units   INT,
    effective_date        DATE NOT NULL,
    expiry_date           DATE,
    is_active             BOOLEAN NOT NULL DEFAULT TRUE,
    rule_expression       TEXT,
    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 chk_coverage_rules_type
        CHECK (rule_type IN ('medical_necessity','benefit_limit','frequency_limit','exclusion','other')),
    CONSTRAINT chk_coverage_rules_age
        CHECK (
            (age_min IS NULL OR age_min >= 0) AND
            (age_max IS NULL OR (age_max >= 0 AND (age_min IS NULL OR age_max >= age_min)))
        ),
    CONSTRAINT chk_coverage_rules_gender
        CHECK (gender IS NULL OR gender IN ('male','female','other','any')),
    CONSTRAINT chk_coverage_rules_frequency
        CHECK (
            (frequency_limit IS NULL OR frequency_limit >= 0) AND
            (frequency_period IS NULL OR frequency_period IN ('per_day','per_week','per_month','per_year','lifetime'))
        ),
    CONSTRAINT chk_coverage_rules_limits
        CHECK (
            (benefit_limit_amount IS NULL OR benefit_limit_amount >= 0) AND
            (benefit_limit_units IS NULL OR benefit_limit_units >= 0)
        ),
    CONSTRAINT chk_coverage_rules_dates
        CHECK (expiry_date IS NULL OR expiry_date >= effective_date)
);

COMMENT ON TABLE coverage_rules IS 'Payer/plan-specific coverage and medical necessity rules for CPT/ICD-10-AM combinations.';

ALTER TABLE coverage_rules
    ADD CONSTRAINT fk_coverage_rules_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_coverage_rules_plan
        FOREIGN KEY (plan_id) REFERENCES insurance_plans(plan_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_coverage_rules_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_coverage_rules_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_coverage_rules_payer_plan ON coverage_rules (payer_id, plan_id);
CREATE INDEX idx_coverage_rules_active ON coverage_rules (is_active, effective_date, expiry_date);
CREATE INDEX idx_coverage_rules_type ON coverage_rules (rule_type);

CREATE TABLE prior_auth_rules (
    rule_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id                BIGINT NOT NULL,
    plan_id                 BIGINT,
    service_category        VARCHAR(50) NOT NULL,
    cpt_code_range          VARCHAR(50),
    cpt_codes               TEXT,
    auth_required           BOOLEAN NOT NULL DEFAULT TRUE,
    auth_method             VARCHAR(30),
    turnaround_days         INT,
    valid_duration_days     INT,
    documentation_required  TEXT,
    requires_clinical_criteria BOOLEAN NOT NULL DEFAULT FALSE,
    effective_date          DATE NOT NULL,
    expiry_date             DATE,
    is_active               BOOLEAN NOT NULL DEFAULT TRUE,
    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 chk_prior_auth_method
        CHECK (auth_method IS NULL OR auth_method IN ('portal','phone','fax','email','auto-approve')),
    CONSTRAINT chk_prior_auth_turnaround
        CHECK (turnaround_days IS NULL OR turnaround_days >= 0),
    CONSTRAINT chk_prior_auth_valid_duration
        CHECK (valid_duration_days IS NULL OR valid_duration_days >= 0),
    CONSTRAINT chk_prior_auth_dates
        CHECK (expiry_date IS NULL OR expiry_date >= effective_date)
);

COMMENT ON TABLE prior_auth_rules IS 'Prior authorization requirements per payer/plan and service category.';

ALTER TABLE prior_auth_rules
    ADD CONSTRAINT fk_prior_auth_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_prior_auth_plan
        FOREIGN KEY (plan_id) REFERENCES insurance_plans(plan_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_prior_auth_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_prior_auth_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_prior_auth_payer_plan ON prior_auth_rules (payer_id, plan_id);
CREATE INDEX idx_prior_auth_service_cat ON prior_auth_rules (service_category);
CREATE INDEX idx_prior_auth_active ON prior_auth_rules (is_active, effective_date, expiry_date);

CREATE TABLE payer_contacts (
    contact_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id     BIGINT NOT NULL,
    contact_name VARCHAR(255) NOT NULL,
    role         VARCHAR(100) NOT NULL,
    email        VARCHAR(255),
    phone        VARCHAR(50),
    department   VARCHAR(100),
    is_primary   BOOLEAN NOT NULL DEFAULT FALSE,
    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 chk_payer_contacts_email_phone
        CHECK (email IS NOT NULL OR phone IS NOT NULL)
);

COMMENT ON TABLE payer_contacts IS 'Key contacts at payer organizations (contracting, claims, IT, etc.).';

ALTER TABLE payer_contacts
    ADD CONSTRAINT fk_payer_contacts_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_payer_contacts_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_payer_contacts_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_payer_contacts_payer ON payer_contacts (payer_id);
CREATE INDEX idx_payer_contacts_primary ON payer_contacts (payer_id, is_primary);

CREATE TABLE contract_amendments (
    amendment_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    contract_id    BIGINT NOT NULL,
    amendment_type VARCHAR(50) NOT NULL,
    description    TEXT,
    effective_date DATE NOT NULL,
    old_value      TEXT,
    new_value      TEXT,
    approved_by    BIGINT,
    approved_date  TIMESTAMP,
    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 chk_contract_amendments_type
        CHECK (amendment_type IN ('rate_change','scope_change','term_change','other'))
);

COMMENT ON TABLE contract_amendments IS 'Amendments to contracts with old/new values for audit and PDPL accountability.';

ALTER TABLE contract_amendments
    ADD CONSTRAINT fk_contract_amendments_contract
        FOREIGN KEY (contract_id) REFERENCES contracts(contract_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_contract_amendments_approved_by
        FOREIGN KEY (approved_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_contract_amendments_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_contract_amendments_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_contract_amendments_contract ON contract_amendments (contract_id);
CREATE INDEX idx_contract_amendments_effective ON contract_amendments (effective_date);

CREATE TABLE payer_networks (
    network_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id                   BIGINT NOT NULL,
    facility_id                BIGINT NOT NULL,
    network_tier               VARCHAR(30) NOT NULL,
    effective_date             DATE NOT NULL,
    expiry_date                DATE,
    in_network_rate_modifier   NUMERIC(6,3),
    out_of_network_rate_modifier NUMERIC(6,3),
    status                     VARCHAR(30) NOT NULL DEFAULT 'active',
    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_payer_networks UNIQUE (payer_id, facility_id, network_tier, effective_date),
    CONSTRAINT chk_payer_networks_modifiers
        CHECK (
            (in_network_rate_modifier IS NULL OR in_network_rate_modifier >= 0) AND
            (out_of_network_rate_modifier IS NULL OR out_of_network_rate_modifier >= 0)
        ),
    CONSTRAINT chk_payer_networks_status
        CHECK (status IN ('active','inactive','pending')),
    CONSTRAINT chk_payer_networks_dates
        CHECK (expiry_date IS NULL OR expiry_date >= effective_date)
);

COMMENT ON TABLE payer_networks IS 'Network participation and tier per payer/facility with rate modifiers.';

ALTER TABLE payer_networks
    ADD CONSTRAINT fk_payer_networks_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_payer_networks_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_payer_networks_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE,
    ADD CONSTRAINT fk_payer_networks_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE;

CREATE INDEX idx_payer_networks_payer_facility ON payer_networks (payer_id, facility_id);
CREATE INDEX idx_payer_networks_status ON payer_networks (status, effective_date, expiry_date);

CREATE TABLE reimbursement_rates (
    rate_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payer_id              BIGINT NOT NULL,
    contract_id           BIGINT,
    cpt_code              VARCHAR(10) NOT NULL,
    modifier              VARCHAR(10),
    expected_rate         NUMERIC(14,2),
    actual_avg_rate       NUMERIC(14,2),
    variance_pct          NUMERIC(7,2),
    period_start          DATE NOT NULL,
    period_end            DATE NOT NULL,
    claim_count           INT NOT NULL DEFAULT 0,
    total_expected_amount NUMERIC(16,2),
    total_paid_amount     NUMERIC(16,2),
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_reimb_rates_amounts
        CHECK (
            (expected_rate IS NULL OR expected_rate >= 0) AND
            (actual_avg_rate IS NULL OR actual_avg_rate >= 0) AND
            (total_expected_amount IS NULL OR total_expected_amount >= 0) AND
            (total_paid_amount IS NULL OR total_paid_amount >= 0) AND
            (claim_count >= 0)
        ),
    CONSTRAINT chk_reimb_rates_dates
        CHECK (period_end >= period_start)
);

COMMENT ON TABLE reimbursement_rates IS 'Aggregated expected vs actual reimbursement metrics per payer/contract/CPT and period.';

ALTER TABLE reimbursement_rates
    ADD CONSTRAINT fk_reimb_rates_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT fk_reimb_rates_contract
        FOREIGN KEY (contract_id) REFERENCES contracts(contract_id) ON UPDATE CASCADE ON DELETE SET NULL;

CREATE INDEX idx_reimb_rates_payer_period ON reimbursement_rates (payer_id, period_start, period_end);
CREATE INDEX idx_reimb_rates_contract_period ON reimbursement_rates (contract_id, period_start, period_end);
CREATE INDEX idx_reimb_rates_cpt_period ON reimbursement_rates (cpt_code, period_start, period_end);