CREATE TABLE sdk_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sdk_session_id TEXT UNIQUE NOT NULL,
  claude_session_id TEXT,
  project TEXT NOT NULL,
  prompt_counter INTEGER DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TEXT NOT NULL,
  created_at_epoch INTEGER NOT NULL,
  completed_at TEXT,
  completed_at_epoch INTEGER,
  last_activity_at TEXT,
  last_activity_epoch INTEGER
);

CREATE TABLE observations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  sdk_session_id TEXT NOT NULL,
  claude_session_id TEXT,
  project TEXT NOT NULL,
  prompt_number INTEGER,
  tool_name TEXT NOT NULL,
  correlation_id TEXT,

  -- Hierarchical fields
  title TEXT,
  subtitle TEXT,
  narrative TEXT,
  text TEXT,
  facts TEXT,
  concepts TEXT,
  type TEXT,
  files_read TEXT,
  files_modified TEXT,

  created_at TEXT NOT NULL,
  created_at_epoch INTEGER NOT NULL,

  FOREIGN KEY (sdk_session_id) REFERENCES sdk_sessions(sdk_session_id)
);

CREATE TABLE session_summaries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sdk_session_id TEXT NOT NULL,
  claude_session_id TEXT,
  project TEXT NOT NULL,
  prompt_number INTEGER,

  -- Summary fields
  request TEXT,
  investigated TEXT,
  learned TEXT,
  completed TEXT,
  next_steps TEXT,
  notes TEXT,

  created_at TEXT NOT NULL,
  created_at_epoch INTEGER NOT NULL,

  FOREIGN KEY (sdk_session_id) REFERENCES sdk_sessions(sdk_session_id)
);

CREATE TABLE user_prompts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  sdk_session_id TEXT NOT NULL,
  claude_session_id TEXT,
  project TEXT NOT NULL,
  prompt_number INTEGER,
  prompt_text TEXT NOT NULL,
  created_at TEXT NOT NULL,
  created_at_epoch INTEGER NOT NULL,

  FOREIGN KEY (sdk_session_id) REFERENCES sdk_sessions(sdk_session_id)
);

CREATE VIRTUAL TABLE observations_fts USING fts5(
  title,
  subtitle,
  narrative,
  text,
  facts,
  concepts,
  content='observations',
  content_rowid='id'
);

CREATE VIRTUAL TABLE session_summaries_fts USING fts5(
  request,
  investigated,
  learned,
  completed,
  next_steps,
  notes,
  content='session_summaries',
  content_rowid='id'
);

CREATE VIRTUAL TABLE user_prompts_fts USING fts5(
  prompt_text,
  content='user_prompts',
  content_rowid='id'
);

-- Insert trigger example
CREATE TRIGGER observations_ai AFTER INSERT ON observations BEGIN
  INSERT INTO observations_fts(rowid, title, subtitle, narrative, text, facts, concepts)
  VALUES (new.id, new.title, new.subtitle, new.narrative, new.text, new.facts, new.concepts);
END;

-- Update trigger example
CREATE TRIGGER observations_au AFTER UPDATE ON observations BEGIN
  INSERT INTO observations_fts(observations_fts, rowid, title, subtitle, narrative, text, facts, concepts)
  VALUES('delete', old.id, old.title, old.subtitle, old.narrative, old.text, old.facts, old.concepts);
  INSERT INTO observations_fts(rowid, title, subtitle, narrative, text, facts, concepts)
  VALUES (new.id, new.title, new.subtitle, new.narrative, new.text, new.facts, new.concepts);
END;

-- Delete trigger example
CREATE TRIGGER observations_ad AFTER DELETE ON observations BEGIN
  INSERT INTO observations_fts(observations_fts, rowid, title, subtitle, narrative, text, facts, concepts)
  VALUES('delete', old.id, old.title, old.subtitle, old.narrative, old.text, old.facts, old.concepts);
END;