ALTER TABLE transfers
    DROP CONSTRAINT IF EXISTS transfers_status_check;

ALTER TABLE transfers
    ADD CONSTRAINT transfers_status_check
    CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'reversed'));

ALTER TABLE transfers
    ADD COLUMN settlement_attempts integer NOT NULL DEFAULT 0 CHECK (settlement_attempts >= 0),
    ADD COLUMN settlement_provider text,
    ADD COLUMN settlement_reference text,
    ADD COLUMN settlement_error text,
    ADD COLUMN settlement_next_attempt_at timestamptz,
    ADD COLUMN settled_at timestamptz;

CREATE INDEX transfers_sepa_settlement_due_idx
    ON transfers (settlement_next_attempt_at, created_at)
    WHERE transfer_type = 'sepa' AND status = 'pending';

CREATE INDEX transfers_settlement_reference_idx
    ON transfers (settlement_provider, settlement_reference)
    WHERE settlement_reference IS NOT NULL;

CREATE TABLE sepa_settlement_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    transfer_id uuid NOT NULL REFERENCES transfers(id) ON DELETE RESTRICT,
    event_type text NOT NULL CHECK (event_type IN ('queued', 'processing', 'completed', 'failed', 'retried')),
    status_before text,
    status_after text,
    provider text NOT NULL DEFAULT 'local_sepa',
    provider_reference text,
    reason text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX sepa_settlement_events_transfer_created_at_idx
    ON sepa_settlement_events (transfer_id, created_at DESC);

CREATE INDEX sepa_settlement_events_type_created_at_idx
    ON sepa_settlement_events (event_type, created_at DESC);