-- 初始存储结构：只保存运行所需的中立数据，不保存 API key、token 等密钥。
CREATE TABLE IF NOT EXISTS sessions (
    id              TEXT PRIMARY KEY,
    agent_id        TEXT,
    status          TEXT NOT NULL,
    metadata_json   TEXT NOT NULL DEFAULT '{}',
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS runs (
    id              TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    status          TEXT NOT NULL,
    revision        INTEGER NOT NULL DEFAULT 0,
    input_json      TEXT NOT NULL DEFAULT '{}',
    output_json     TEXT,
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_runs_session_id ON runs(session_id);

CREATE TABLE IF NOT EXISTS events (
    id              TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    run_id          TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    revision        INTEGER NOT NULL,
    event_type      TEXT NOT NULL,
    payload_json    TEXT NOT NULL,
    created_at      INTEGER NOT NULL,
    UNIQUE(run_id, revision)
);
CREATE INDEX IF NOT EXISTS idx_events_run_revision ON events(run_id, revision);

CREATE TABLE IF NOT EXISTS snapshots (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    run_id          TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    revision        INTEGER NOT NULL,
    state_json      TEXT NOT NULL,
    created_at      INTEGER NOT NULL,
    UNIQUE(run_id, revision)
);

CREATE TABLE IF NOT EXISTS approvals (
    id              TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    run_id          TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    tool_call_id    TEXT,
    status          TEXT NOT NULL,
    request_json    TEXT NOT NULL,
    decision_json   TEXT,
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_approvals_run_id ON approvals(run_id);

CREATE TABLE IF NOT EXISTS tool_calls (
    id              TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    run_id          TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    tool_name       TEXT NOT NULL,
    arguments_json  TEXT NOT NULL,
    result_json     TEXT,
    status          TEXT NOT NULL,
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_tool_calls_run_id ON tool_calls(run_id);

CREATE TABLE IF NOT EXISTS external_sessions (
    id              TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    run_id          TEXT REFERENCES runs(id) ON DELETE SET NULL,
    backend         TEXT NOT NULL,
    external_id     TEXT NOT NULL,
    status          TEXT NOT NULL,
    metadata_json   TEXT NOT NULL DEFAULT '{}',
    created_at      INTEGER NOT NULL,
    updated_at      INTEGER NOT NULL,
    UNIQUE(backend, external_id)
);
CREATE INDEX IF NOT EXISTS idx_external_sessions_session_id ON external_sessions(session_id);

-- RuntimeStore 的 runtime-level 事件流。run-level 表保留给 Host 的业务查询，
-- 这里单独保存完整 RuntimeSnapshot，避免把一个 runtime 拆成多个伪 run。
CREATE TABLE IF NOT EXISTS runtime_states (
    runtime_id      TEXT PRIMARY KEY,
    revision        INTEGER NOT NULL,
    snapshot_json   TEXT NOT NULL,
    updated_at      INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS runtime_events (
    id              TEXT PRIMARY KEY,
    runtime_id      TEXT NOT NULL REFERENCES runtime_states(runtime_id) ON DELETE CASCADE,
    revision        INTEGER NOT NULL,
    occurred_at     INTEGER NOT NULL,
    event_type      TEXT NOT NULL,
    run_id          TEXT,
    detail_json     TEXT NOT NULL,
    UNIQUE(runtime_id, revision)
);
CREATE INDEX IF NOT EXISTS idx_runtime_events_revision ON runtime_events(runtime_id, revision);