-- New runtime database only. Never apply this migration to the legacy memory DB.
BEGIN IMMEDIATE;
PRAGMA application_id = 1263288882; -- KLB2
PRAGMA user_version = 1;

CREATE TABLE releases (
    id TEXT PRIMARY KEY,
    path TEXT NOT NULL
) STRICT;

CREATE TABLE sessions (
    id TEXT PRIMARY KEY,
    state TEXT NOT NULL DEFAULT 'ready' CHECK(state IN ('ready','waiting')),
    wake_at_ms INTEGER,
    hook_revision TEXT REFERENCES releases(id),
    hook_epoch INTEGER NOT NULL DEFAULT 0 CHECK(hook_epoch >= 0),
    CHECK(state = 'waiting' OR wake_at_ms IS NULL)
) STRICT;

CREATE TABLE events (
    seq INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    kind TEXT NOT NULL CHECK(kind IN (
        'input','scratchpad','execution.started','execution.completed',
        'local.sent','delivery.held','kernel.reset','session.waited',
        'session.woke','hooks.activated'
    )),
    source_key TEXT,
    payload TEXT NOT NULL CHECK(json_valid(payload)),
    created_ms INTEGER NOT NULL CHECK(created_ms >= 0),
    UNIQUE(session_id, source_key)
) STRICT;
CREATE INDEX events_session_order ON events(session_id, seq);
CREATE TRIGGER events_no_update BEFORE UPDATE ON events BEGIN
    SELECT RAISE(ABORT, 'historical events are immutable');
END;
CREATE TRIGGER events_no_delete BEFORE DELETE ON events BEGIN
    SELECT RAISE(ABORT, 'historical events are immutable');
END;

CREATE TABLE inbox (
    event_id INTEGER PRIMARY KEY REFERENCES events(seq),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','consumed'))
) STRICT;

CREATE TABLE executions (
    id TEXT PRIMARY KEY,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    generation TEXT NOT NULL,
    hook_revision TEXT NOT NULL REFERENCES releases(id),
    state TEXT NOT NULL CHECK(state IN ('running','ok','error','yielded','interrupted')),
    started_event INTEGER NOT NULL UNIQUE REFERENCES events(seq),
    completed_event INTEGER UNIQUE REFERENCES events(seq)
) STRICT;
CREATE UNIQUE INDEX one_running_execution ON executions(session_id) WHERE completed_event IS NULL;

CREATE TABLE effects (
    id TEXT PRIMARY KEY,
    execution_id TEXT NOT NULL REFERENCES executions(id),
    request_id TEXT NOT NULL,
    text TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('proposed','confirmed','held')),
    event_id INTEGER UNIQUE REFERENCES events(seq),
    reason TEXT,
    UNIQUE(execution_id,request_id),
    CHECK((status = 'confirmed' AND event_id IS NOT NULL AND reason IS NULL)
       OR (status = 'held' AND event_id IS NULL AND reason IS NOT NULL)
       OR (status = 'proposed' AND event_id IS NULL AND reason IS NULL))
) STRICT;

CREATE TABLE hook_traces (
    effect_id TEXT PRIMARY KEY REFERENCES effects(id),
    revision TEXT NOT NULL REFERENCES releases(id),
    decision TEXT NOT NULL CHECK(json_valid(decision)),
    fault TEXT
) STRICT;
COMMIT;