-- SQLite 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 TEXT. Timestamps are RFC 3339 UTC.
-- Steps are immutable once written; monotonic step numbering is enforced by the
-- application layer, not the schema.

CREATE TABLE IF NOT EXISTS sessions (
    id                      TEXT    PRIMARY KEY,
    created_at              TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at              TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    concurrency_limit       INTEGER NOT NULL,
    last_step               INTEGER NOT NULL DEFAULT 0,
    last_state_json         TEXT,
    last_frontier_json      TEXT,
    last_versions_seen_json TEXT
);

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                  INTEGER NOT NULL,
    created_at            TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    state_json            TEXT    NOT NULL,
    frontier_json         TEXT    NOT NULL,
    versions_seen_json    TEXT    NOT NULL,
    ran_nodes_json        TEXT    NOT NULL,
    skipped_nodes_json    TEXT    NOT NULL,
    updated_channels_json TEXT,
    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);

-- Advance the denormalized snapshot on the parent session row after each insert.
CREATE TRIGGER IF NOT EXISTS trg_steps_after_insert
AFTER INSERT ON steps
BEGIN
    UPDATE sessions
    SET updated_at              = strftime('%Y-%m-%dT%H:%M:%fZ', 'now'),
        last_step               = NEW.step,
        last_state_json         = NEW.state_json,
        last_frontier_json      = NEW.frontier_json,
        last_versions_seen_json = NEW.versions_seen_json
    WHERE id = NEW.session_id;
END;
