-- PostgreSQL schema for Weavegraph session and step checkpointing.
--
-- Two tables:
--   sessions — one row per session; carries a denormalized latest-step snapshot
--              so resume is a single point-lookup with no aggregate.
--   steps    — append-only checkpoint history; one row per barrier crossing.
--
-- All JSON columns are JSONB. Timestamps are TIMESTAMPTZ (UTC).
-- The denormalized last_* columns on sessions are advanced by the application
-- in the same transaction as each step write (no trigger).

CREATE TABLE IF NOT EXISTS sessions (
    id                      TEXT        PRIMARY KEY,
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    concurrency_limit       BIGINT      NOT NULL,
    last_step               BIGINT      NOT NULL DEFAULT 0,
    last_state_json         JSONB,
    last_frontier_json      JSONB,
    last_versions_seen_json JSONB
);

CREATE INDEX IF NOT EXISTS idx_sessions_updated_at
    ON sessions(updated_at DESC);

CREATE TABLE IF NOT EXISTS steps (
    session_id            TEXT        NOT NULL,
    step                  BIGINT      NOT NULL,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    state_json            JSONB       NOT NULL,
    frontier_json         JSONB       NOT NULL,
    versions_seen_json    JSONB       NOT NULL,
    ran_nodes_json        JSONB       NOT NULL,
    skipped_nodes_json    JSONB       NOT NULL,
    updated_channels_json JSONB,
    PRIMARY KEY (session_id, step),
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

-- Covers both DESC (latest-step resume) and ASC (replay) scans.
CREATE INDEX IF NOT EXISTS idx_steps_session_id_step
    ON steps(session_id, step DESC);

-- JSONB containment indexes for query_steps ran_node / skipped_node filters.
CREATE INDEX IF NOT EXISTS idx_steps_ran_nodes_gin
    ON steps USING GIN (ran_nodes_json);

CREATE INDEX IF NOT EXISTS idx_steps_skipped_nodes_gin
    ON steps USING GIN (skipped_nodes_json);
