CREATE TABLE IF NOT EXISTS webhook_ingress_dlq (
    id BIGSERIAL PRIMARY KEY,
    attempt_id TEXT NOT NULL,
    idempotency_key TEXT NOT NULL,
    error_class TEXT NOT NULL,
    error_message TEXT NOT NULL,
    attempt_count INTEGER NOT NULL DEFAULT 1,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    event_type TEXT NOT NULL DEFAULT 'unknown',
    payload_format TEXT NOT NULL DEFAULT 'unknown',
    payload JSONB NOT NULL,
    proxy_meta JSONB NULL,
    replay_status TEXT NOT NULL DEFAULT 'pending',
    replay_requested_by TEXT NULL,
    replayed_at TIMESTAMPTZ NULL,
    replay_task_id TEXT NULL,
    replay_notes TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT webhook_ingress_dlq_replay_status_check CHECK (
        replay_status IN ('pending', 'replayed', 'discarded')
    )
);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_dlq_replay_status
    ON webhook_ingress_dlq (replay_status);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_dlq_idempotency_key
    ON webhook_ingress_dlq (idempotency_key);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_dlq_created_at
    ON webhook_ingress_dlq (created_at);
