CREATE TABLE account_routing_codes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    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'
    )),
    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'
    )),
    code text NOT NULL CHECK (code ~ '^[A-Z0-9-]{2,40}$'),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'deleted')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (account_id, country, network, code_type, code)
);

CREATE INDEX account_routing_codes_account_id_status_idx
    ON account_routing_codes (account_id, status);

CREATE TABLE beneficiary_routing_codes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    beneficiary_id uuid NOT NULL REFERENCES beneficiaries(id) ON DELETE CASCADE,
    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'
    )),
    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'
    )),
    code text NOT NULL CHECK (code ~ '^[A-Z0-9-]{2,40}$'),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'deleted')),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (beneficiary_id, country, network, code_type, code)
);

CREATE INDEX beneficiary_routing_codes_beneficiary_id_status_idx
    ON beneficiary_routing_codes (beneficiary_id, status);

ALTER TABLE transfers
    ADD COLUMN beneficiary_routing_codes jsonb NOT NULL DEFAULT '[]'::jsonb;

INSERT INTO account_routing_codes (account_id, code_type, country, network, code)
SELECT id, 'bank_code', 'NL', 'sepa', substring(iban from 5 for 4)
FROM accounts
WHERE iban LIKE 'NL%'
ON CONFLICT DO NOTHING;

INSERT INTO beneficiary_routing_codes (beneficiary_id, code_type, country, network, code)
SELECT id, 'bank_code', 'NL', 'sepa', substring(iban from 5 for 4)
FROM beneficiaries
WHERE iban LIKE 'NL%'
ON CONFLICT DO NOTHING;

CREATE TRIGGER account_routing_codes_set_updated_at
BEFORE UPDATE ON account_routing_codes
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER beneficiary_routing_codes_set_updated_at
BEFORE UPDATE ON beneficiary_routing_codes
FOR EACH ROW EXECUTE FUNCTION set_updated_at();