-- mire-sagas schema. Two tables on top of mire's event store:
--   - es_saga_awaits   — routing projection: event_type → awaiting (instance, step)
--   - es_saga_signals  — external signal queue, drained by the shard leaseholder
--
-- The instance state itself does NOT live in a saga-specific table —
-- `SagaEnvelope<S>` is a mire aggregate with its own event stream
-- (category = `<saga::name()>`). OCC + replay are provided by the
-- existing event store machinery.

-- es_saga_awaits — reverse index from event_type → awaiting step
--
-- Maintained synchronously with `StepActivated` / `StepCompleted` /
-- `StepFailed` / `StepSkipped` envelope events: an INSERT at activation,
-- a DELETE on resolution, both inside the same transaction as the
-- event append. Used by the runner's poll loop to route arriving
-- response events to the right instance + step in O(log n) — without
-- it routing would have to scan every running saga's stream.
--
-- shard_id is hash(instance_id) mod N (saga-builder-configurable);
-- workers filter their own shard so multiple workers can run the same
-- saga in parallel without colliding.
CREATE TABLE IF NOT EXISTS es_saga_awaits (
    saga_name        TEXT        NOT NULL,
    instance_id      TEXT        NOT NULL,
    step             TEXT        NOT NULL,
    event_type       TEXT        NOT NULL,
    deadline_at      TIMESTAMPTZ,
    is_compensation  BOOLEAN     NOT NULL,
    shard_id         INTEGER     NOT NULL,
    PRIMARY KEY (saga_name, instance_id, step)
);

-- Routing index. Hot path: "an event of type T just arrived — which
-- instances care?" Single seek per arriving event.
CREATE INDEX IF NOT EXISTS idx_saga_awaits_route
    ON es_saga_awaits (saga_name, event_type, shard_id);

-- Timer driver. Partial index — most awaits don't have a deadline.
-- Polled by `WHERE saga_name=$1 AND shard_id=$2 AND deadline_at <= now()`.
CREATE INDEX IF NOT EXISTS idx_saga_awaits_deadline
    ON es_saga_awaits (saga_name, shard_id, deadline_at)
    WHERE deadline_at IS NOT NULL;

-- es_saga_signals — external signal queue.
--
-- Signals are inputs from outside the saga's own event stream (HTTP
-- callbacks, admin actions). Lease ownership of `(saga_name, shard_id)`
-- ensures at most one consumer per partition; no FOR UPDATE / SKIP
-- LOCKED needed at runtime.
--
-- NOTE (review SAGA-14): rows are currently INSERT-only — nothing
-- deletes them yet, so this is an append-only audit log that grows
-- unbounded. Retention/drain lands with the worker-loop work.
CREATE TABLE IF NOT EXISTS es_saga_signals (
    id               BIGSERIAL   PRIMARY KEY,
    saga_name        TEXT        NOT NULL,
    instance_id      TEXT        NOT NULL,
    signal_type      TEXT        NOT NULL,
    payload          JSONB       NOT NULL,
    shard_id         INTEGER     NOT NULL,
    enqueued_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Per-shard FIFO drain. Worker reads its own shard in id order.
CREATE INDEX IF NOT EXISTS idx_saga_signals_pending
    ON es_saga_signals (saga_name, shard_id, id);
