-- Kompanion knowledge store (sqlite)
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

CREATE TABLE IF NOT EXISTS entries (
  id INTEGER PRIMARY KEY,
  ts TEXT NOT NULL,
  aspect TEXT,
  tags TEXT,
  text TEXT NOT NULL
);

CREATE VIRTUAL TABLE IF NOT EXISTS entries_fts USING fts5(text, content="entries", content_rowid="id");
CREATE TRIGGER IF NOT EXISTS entries_ai AFTER INSERT ON entries BEGIN
  INSERT INTO entries_fts(rowid, text) VALUES (new.id, new.text);
END;
CREATE TRIGGER IF NOT EXISTS entries_ad AFTER DELETE ON entries BEGIN
  INSERT INTO entries_fts(entries_fts, rowid, text) VALUES(delete, old.id, old.text);
END;
CREATE TRIGGER IF NOT EXISTS entries_au AFTER UPDATE ON entries BEGIN
  INSERT INTO entries_fts(entries_fts, rowid, text) VALUES(delete, old.id, old.text);
  INSERT INTO entries_fts(rowid, text) VALUES (new.id, new.text);
END;

CREATE TABLE IF NOT EXISTS sources (
  id INTEGER PRIMARY KEY,
  file TEXT NOT NULL,
  sha TEXT,
  lineno INTEGER
);

CREATE TABLE IF NOT EXISTS vectors (
  id INTEGER PRIMARY KEY,
  entry_id INTEGER REFERENCES entries(id) ON DELETE CASCADE,
  model TEXT NOT NULL,
  dim INTEGER NOT NULL,
  vec BLOB NOT NULL
);

CREATE TABLE IF NOT EXISTS ledger_head (
  id INTEGER PRIMARY KEY CHECK (id=1),
  head_sha TEXT
);