ALTER TABLE routing_code_requirements
    DROP CONSTRAINT IF EXISTS routing_code_requirements_provider_country_network_currency_code_type_key,
    ADD COLUMN rule_source text NOT NULL DEFAULT 'seed_example'
        CHECK (rule_source IN ('seed_example', 'provider_contract', 'internal_policy')),
    ADD COLUMN contract_reference text,
    ADD COLUMN contract_version text,
    ADD COLUMN contract_signed_at date,
    ADD COLUMN effective_from date NOT NULL DEFAULT CURRENT_DATE,
    ADD COLUMN effective_to date,
    ADD COLUMN approval_status text NOT NULL DEFAULT 'approved'
        CHECK (approval_status IN ('draft', 'approved', 'retired')),
    ADD COLUMN approved_by_admin_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
    ADD COLUMN approved_at timestamptz,
    ADD COLUMN retired_at timestamptz,
    ADD COLUMN metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD CONSTRAINT routing_code_requirements_effective_range_check
        CHECK (effective_to IS NULL OR effective_to >= effective_from);

CREATE UNIQUE INDEX routing_code_requirements_contract_rule_uniq
    ON routing_code_requirements (
        provider,
        country,
        network,
        currency,
        code_type,
        COALESCE(contract_reference, ''),
        COALESCE(contract_version, ''),
        effective_from
    );

UPDATE routing_code_requirements
SET rule_source = CASE
        WHEN provider = 'local_sepa' THEN 'internal_policy'
        ELSE 'seed_example'
    END,
    contract_reference = CASE
        WHEN provider = 'local_sepa' THEN 'local-sepa-simulator'
        ELSE 'example-only-not-for-production'
    END,
    contract_version = 'v1',
    contract_signed_at = created_at::date,
    effective_from = created_at::date,
    approval_status = 'approved',
    approved_at = created_at;

CREATE INDEX routing_code_requirements_contract_lookup_idx
    ON routing_code_requirements (
        provider, country, network, currency, approval_status, rule_source, active, effective_from, effective_to
    );

CREATE INDEX routing_code_requirements_contract_reference_idx
    ON routing_code_requirements (provider, contract_reference, contract_version)
    WHERE contract_reference IS NOT NULL;