CREATE TABLE IF NOT EXISTS webhook_ingress_events (
    id BIGSERIAL PRIMARY KEY,
    idempotency_key TEXT NOT NULL,
    attempt_id TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'processing',
    event_type TEXT NOT NULL DEFAULT 'unknown',
    payload_sha256 TEXT NULL,
    first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_error TEXT NULL,
    duplicate_count INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT webhook_ingress_events_idempotency_key_unique UNIQUE (idempotency_key),
    CONSTRAINT webhook_ingress_events_status_check CHECK (
        status IN ('processing', 'processed', 'duplicate', 'failed', 'dlq')
    )
);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_events_attempt_id
    ON webhook_ingress_events (attempt_id);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_events_status
    ON webhook_ingress_events (status);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_events_event_type
    ON webhook_ingress_events (event_type);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_events_payload_sha256
    ON webhook_ingress_events (payload_sha256);

CREATE INDEX IF NOT EXISTS idx_webhook_ingress_events_last_seen_at
    ON webhook_ingress_events (last_seen_at);
