-- Lethe schema v1.
--
-- FTS5 mode choice: REGULAR (no `content=` clause).
--
-- Why not contentless (`content=''`): contentless FTS5 tables do not store
-- UNINDEXED column values either, which defeats `WHERE owner = ?` filtering
-- without a join back to `turns`. UNINDEXED columns are only meaningful in a
-- regular or external-content FTS5 table.
--
-- Why not external content (`content='turns'`): SQLite's
-- `INSERT ... ON CONFLICT ... DO UPDATE` upsert path fires the UPDATE
-- trigger (not INSERT) when the conflict branch is taken, and across SQLite
-- versions the external-content trigger contract has had subtle issues
-- around UPSERT ordering. Phase 7 (ingest) uses upserts heavily, so we
-- avoid that risk here.
--
-- Regular FTS5 stores `content` twice (once in `turns`, once in the FTS
-- shadow table); the storage cost is the price for trigger-correctness
-- robustness across SQLite versions. Triggers are the only writers --
-- never INSERT/UPDATE/DELETE *_fts directly outside this file.
--
-- Trigger semantics: each trigger is keyed by the source-table rowid (every
-- non-WITHOUT-ROWID table has one). On UPDATE we DELETE then INSERT to
-- avoid an UPDATE-in-place edge case that has historically misbehaved in
-- some FTS5 builds. UPSERT (INSERT ... ON CONFLICT DO UPDATE) fires the
-- UPDATE trigger when the conflict branch is taken; the DELETE+INSERT
-- pattern keeps FTS in sync regardless.

CREATE TABLE sessions (
    owner       TEXT NOT NULL,
    tool        TEXT NOT NULL,
    host        TEXT NOT NULL,
    session_id  TEXT NOT NULL,
    started_at  INTEGER NOT NULL,
    ended_at    INTEGER NOT NULL,
    working_dir TEXT,
    source_file TEXT NOT NULL,
    metadata    TEXT,
    PRIMARY KEY (owner, tool, host, session_id)
);

CREATE INDEX sessions_owner_started ON sessions(owner, started_at DESC);

CREATE TABLE turns (
    owner       TEXT NOT NULL,
    tool        TEXT NOT NULL,
    host        TEXT NOT NULL,
    session_id  TEXT NOT NULL,
    turn_id     TEXT NOT NULL,
    seq         INTEGER NOT NULL,
    role        TEXT NOT NULL,
    timestamp   INTEGER NOT NULL,
    content     TEXT NOT NULL,
    model       TEXT,
    tokens_in   INTEGER,
    tokens_out  INTEGER,
    cost_usd    REAL,
    tool_calls  TEXT,
    metadata    TEXT,
    PRIMARY KEY (owner, tool, host, session_id, turn_id),
    FOREIGN KEY (owner, tool, host, session_id)
        REFERENCES sessions(owner, tool, host, session_id)
        ON DELETE CASCADE
);

-- FTS5 over turn prose `content`. UNINDEXED columns let `WHERE owner = ?`
-- filter pre-FTS without a join back to `turns`.
CREATE VIRTUAL TABLE turns_fts USING fts5 (
    content,
    owner       UNINDEXED,
    tool        UNINDEXED,
    host        UNINDEXED,
    session_id  UNINDEXED,
    turn_id     UNINDEXED
);

CREATE TRIGGER turns_fts_insert AFTER INSERT ON turns BEGIN
    INSERT INTO turns_fts(rowid, content, owner, tool, host, session_id, turn_id)
    VALUES (new.rowid, new.content, new.owner, new.tool, new.host, new.session_id, new.turn_id);
END;

CREATE TRIGGER turns_fts_update AFTER UPDATE ON turns BEGIN
    DELETE FROM turns_fts WHERE rowid = old.rowid;
    INSERT INTO turns_fts(rowid, content, owner, tool, host, session_id, turn_id)
    VALUES (new.rowid, new.content, new.owner, new.tool, new.host, new.session_id, new.turn_id);
END;

CREATE TRIGGER turns_fts_delete AFTER DELETE ON turns BEGIN
    DELETE FROM turns_fts WHERE rowid = old.rowid;
END;

-- FTS5 over `tool_calls` JSON text. INSERT trigger fires only when the
-- column is non-NULL; UPDATE trigger always deletes any prior row, then
-- re-inserts only if NEW.tool_calls is non-NULL (covers all four NULL/
-- non-NULL transitions).
CREATE VIRTUAL TABLE tool_outputs_fts USING fts5 (
    tool_calls,
    owner       UNINDEXED,
    tool        UNINDEXED,
    host        UNINDEXED,
    session_id  UNINDEXED,
    turn_id     UNINDEXED
);

CREATE TRIGGER tool_outputs_fts_insert AFTER INSERT ON turns
WHEN new.tool_calls IS NOT NULL BEGIN
    INSERT INTO tool_outputs_fts(rowid, tool_calls, owner, tool, host, session_id, turn_id)
    VALUES (new.rowid, new.tool_calls, new.owner, new.tool, new.host, new.session_id, new.turn_id);
END;

CREATE TRIGGER tool_outputs_fts_update AFTER UPDATE ON turns BEGIN
    DELETE FROM tool_outputs_fts WHERE rowid = old.rowid;
    INSERT INTO tool_outputs_fts(rowid, tool_calls, owner, tool, host, session_id, turn_id)
    SELECT new.rowid, new.tool_calls, new.owner, new.tool, new.host, new.session_id, new.turn_id
    WHERE new.tool_calls IS NOT NULL;
END;

CREATE TRIGGER tool_outputs_fts_delete AFTER DELETE ON turns BEGIN
    DELETE FROM tool_outputs_fts WHERE rowid = old.rowid;
END;