-- Event store foundation tables

-- Streams: one row per aggregate instance, tracks current version
CREATE TABLE IF NOT EXISTS es_streams (
    stream_id        TEXT        PRIMARY KEY,
    stream_category  TEXT        NOT NULL,
    stream_version   BIGINT      NOT NULL DEFAULT 0,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_es_streams_category ON es_streams (stream_category);

-- Events: append-only log with global ordering.
--
-- `transaction_id` records the inserting transaction (xid8). A BIGSERIAL
-- `global_position` is assigned *before* commit, so under concurrent writers a
-- higher position can become visible before a lower one — a position-only
-- subscriber would skip the late-committing lower event. Subscribers instead
-- read by (transaction_id, global_position) and ignore events whose transaction
-- is still in flight (transaction_id < pg_snapshot_xmin(pg_current_snapshot())),
-- which is gapless and lock-free on the write path. Requires Postgres 13+.
--
-- Caveat (review CORE-2/SUB-1): transaction_id is xid-ASSIGNMENT order (a
-- transaction's first write), not commit order. A multi-statement transaction
-- that writes before appending sorts its events early, so raw cursor order can
-- present a stream's v2 before v1. Per-stream global_position IS
-- version-monotonic (appends serialize on the es_streams row lock), and the
-- Subscription layer reorders/holds events to deliver each stream in strict
-- stream_version order — read raw cursor order as "gapless", not "ordered".
CREATE TABLE IF NOT EXISTS es_events (
    global_position  BIGSERIAL   PRIMARY KEY,
    stream_id        TEXT        NOT NULL REFERENCES es_streams(stream_id),
    stream_version   BIGINT      NOT NULL,
    event_type       TEXT        NOT NULL,
    data             JSONB       NOT NULL,
    metadata         JSONB       NOT NULL DEFAULT '{}',
    transaction_id   XID8        NOT NULL DEFAULT pg_current_xact_id(),
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (stream_id, stream_version)
);

CREATE INDEX IF NOT EXISTS idx_es_events_stream ON es_events (stream_id, stream_version);
CREATE INDEX IF NOT EXISTS idx_es_events_type ON es_events (event_type);
-- Drives the subscription cursor: scan committed transactions in order.
CREATE INDEX IF NOT EXISTS idx_es_events_txid ON es_events (transaction_id, global_position);

-- Subscriptions: tracks the last processed (transaction_id, position) per
-- subscriber. The composite cursor pairs with the read query above.
--
-- The replica-coordination columns/tables (es_subscriptions.fence_token and
-- es_projection_leases) are NOT defined here — they live in the append-only
-- 002_projection_leases.sql, applied via an idempotent ALTER so a database
-- created by a pre-lease version of mire is upgraded correctly (review MIG-1).
CREATE TABLE IF NOT EXISTS es_subscriptions (
    subscription_id      TEXT        PRIMARY KEY,
    last_position        BIGINT      NOT NULL DEFAULT 0,
    last_transaction_id  XID8        NOT NULL DEFAULT '0'::xid8,
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);
