CREATE TABLE routing_code_requirements (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    provider text NOT NULL CHECK (provider ~ '^[a-z0-9_:-]{2,80}$'),
    country char(2) NOT NULL CHECK (country ~ '^[A-Z]{2}$'),
    network text NOT NULL CHECK (network IN (
        'ach',
        'fedwire',
        'fps',
        'bacs',
        'sepa',
        'swift',
        'local',
        'eft',
        'interac',
        'imps',
        'rtgs',
        'neft'
    )),
    currency text NOT NULL DEFAULT '*' CHECK (currency = '*' OR currency ~ '^[A-Z]{3}$'),
    code_type text NOT NULL CHECK (code_type IN (
        'aba_routing_number',
        'sort_code',
        'bank_code',
        'clearing_code',
        'bsb',
        'transit_number',
        'institution_number',
        'ifsc',
        'branch_code'
    )),
    required boolean NOT NULL DEFAULT true,
    active boolean NOT NULL DEFAULT true,
    priority integer NOT NULL DEFAULT 100 CHECK (priority >= 0),
    description text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (provider, country, network, currency, code_type)
);

CREATE INDEX routing_code_requirements_route_idx
    ON routing_code_requirements (provider, country, network, currency, active);

INSERT INTO routing_code_requirements (
    provider, country, network, currency, code_type, required, priority, description
) VALUES
    ('local_sepa', 'NL', 'sepa', 'EUR', 'bank_code', true, 10, 'Local SEPA simulator requires the Dutch bank code inferred from the IBAN.'),
    ('example_us_ach', 'US', 'ach', 'USD', 'aba_routing_number', true, 10, 'US ACH rails require a valid ABA routing number.'),
    ('example_us_fedwire', 'US', 'fedwire', 'USD', 'aba_routing_number', true, 10, 'US Fedwire rails require a valid ABA routing number.'),
    ('example_uk_fps', 'GB', 'fps', 'GBP', 'sort_code', true, 10, 'UK Faster Payments requires a sort code.'),
    ('example_uk_bacs', 'GB', 'bacs', 'GBP', 'sort_code', true, 10, 'UK Bacs requires a sort code.'),
    ('example_au_becs', 'AU', 'local', 'AUD', 'bsb', true, 10, 'Australian local payments require a BSB.'),
    ('example_ca_eft', 'CA', 'eft', 'CAD', 'institution_number', true, 10, 'Canadian EFT requires an institution number.'),
    ('example_ca_eft', 'CA', 'eft', 'CAD', 'transit_number', true, 20, 'Canadian EFT requires a transit number.'),
    ('example_in_neft', 'IN', 'neft', 'INR', 'ifsc', true, 10, 'Indian NEFT requires an IFSC.'),
    ('example_in_rtgs', 'IN', 'rtgs', 'INR', 'ifsc', true, 10, 'Indian RTGS requires an IFSC.'),
    ('example_in_imps', 'IN', 'imps', 'INR', 'ifsc', true, 10, 'Indian IMPS requires an IFSC.')
ON CONFLICT DO NOTHING;

CREATE TRIGGER routing_code_requirements_set_updated_at
BEFORE UPDATE ON routing_code_requirements
FOR EACH ROW EXECUTE FUNCTION set_updated_at();