-- Transactional outbox table for reliable event/side-effect delivery.
--
-- Rows are inserted atomically within the same DB transaction as the
-- source state mutation.  A background relay worker leases pending rows,
-- dispatches the associated side effects (CDC events, webhooks, audit
-- writes), and marks them published.  Failed attempts are retried with
-- exponential back-off up to ATHENA_OUTBOX_MAX_ATTEMPTS (default 10).
-- Rows that exceed the limit are left with published_at NULL and
-- last_error set so they can be inspected/DLQ-routed.

CREATE TABLE IF NOT EXISTS public.athena_outbox (
    event_id        uuid        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    aggregate_type  text        NOT NULL,          -- e.g. 'gateway', 'management', 'deferred_write'
    aggregate_id    text        NOT NULL,          -- table_name / entity key for the source mutation
    event_type      text        NOT NULL,          -- e.g. 'mutation.insert', 'mutation.update', 'mutation.delete', 'ddl.applied', 'webhook.trigger'
    payload         jsonb       NOT NULL DEFAULT '{}'::jsonb,
    headers         jsonb       NOT NULL DEFAULT '{}'::jsonb,  -- correlation-id, client_name, request_id, etc.
    created_at      timestamptz NOT NULL DEFAULT now(),
    available_at    timestamptz NOT NULL DEFAULT now(),        -- supports delayed/scheduled delivery
    attempts        integer     NOT NULL DEFAULT 0,
    last_error      text,
    published_at    timestamptz                               -- NULL = not yet published
);

-- Relay worker lease scan: only unpublished rows that are ready.
CREATE INDEX IF NOT EXISTS idx_athena_outbox_relay_scan
    ON public.athena_outbox (published_at, available_at)
    WHERE published_at IS NULL;

-- Supports per-aggregate ordering and idempotency checks.
CREATE INDEX IF NOT EXISTS idx_athena_outbox_aggregate
    ON public.athena_outbox (aggregate_type, aggregate_id, created_at);

-- Supports event-type-based routing in the relay worker.
CREATE INDEX IF NOT EXISTS idx_athena_outbox_event_type
    ON public.athena_outbox (event_type)
    WHERE published_at IS NULL;
