-- Migration 0005: gateway retry state for package change events

ALTER TABLE supply_intelligence_package_change_events
    ADD COLUMN IF NOT EXISTS retry_count INTEGER NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS last_retry_at TIMESTAMPTZ NULL,
    ADD COLUMN IF NOT EXISTS next_retry_at TIMESTAMPTZ NULL,
    ADD COLUMN IF NOT EXISTS last_failure_category TEXT NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS last_failure_detail TEXT NOT NULL DEFAULT '';

CREATE INDEX IF NOT EXISTS idx_supply_intelligence_package_events_retry_due
    ON supply_intelligence_package_change_events (ack_status, next_retry_at, occurred_at DESC);