-- Epstein Analysis Server -- SQLite Schema
-- Separate from the OCR provenance DB (epstein_audit.db)

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- Core: one row per JSON file
CREATE TABLE IF NOT EXISTS documents (
  id INTEGER PRIMARY KEY,
  file_key TEXT UNIQUE NOT NULL,
  dataset TEXT NOT NULL,
  full_text TEXT,
  document_type TEXT,
  date TEXT,
  is_photo BOOLEAN DEFAULT 0,
  has_handwriting BOOLEAN,
  has_stamps BOOLEAN,
  ocr_source TEXT,
  additional_notes TEXT,
  page_number TEXT,
  document_number TEXT,
  json_path TEXT NOT NULL,
  char_count INTEGER,
  created_at TEXT DEFAULT (datetime('now'))
);

-- Text chunks for RAG
CREATE TABLE IF NOT EXISTS chunks (
  id INTEGER PRIMARY KEY,
  document_id INTEGER NOT NULL REFERENCES documents(id),
  chunk_index INTEGER NOT NULL,
  content TEXT NOT NULL,
  token_count INTEGER,
  char_start INTEGER,
  char_end INTEGER,
  UNIQUE(document_id, chunk_index)
);

-- Chunk embeddings (Phase 2)
CREATE TABLE IF NOT EXISTS chunk_embeddings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  chunk_id INTEGER UNIQUE NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
  document_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  embedding BLOB NOT NULL,
  embedding_dim INTEGER DEFAULT 768,
  model TEXT DEFAULT 'gemini-embedding-001',
  source_text_hash TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);

-- Summary embeddings (Phase 2)
CREATE TABLE IF NOT EXISTS summary_embeddings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  document_id INTEGER UNIQUE NOT NULL REFERENCES documents(id),
  summary_text TEXT NOT NULL,
  embedding BLOB NOT NULL,
  embedding_dim INTEGER DEFAULT 768,
  model TEXT DEFAULT 'gemini-embedding-001',
  created_at TEXT DEFAULT (datetime('now'))
);

-- Entities from our JSON files
CREATE TABLE IF NOT EXISTS entities (
  id INTEGER PRIMARY KEY,
  document_id INTEGER NOT NULL REFERENCES documents(id),
  entity_type TEXT NOT NULL,
  value TEXT NOT NULL,
  normalized_value TEXT
);

-- From release/persons_registry.json
CREATE TABLE IF NOT EXISTS persons (
  id INTEGER PRIMARY KEY,
  canonical_name TEXT UNIQUE NOT NULL,
  slug TEXT,
  category TEXT,
  aliases TEXT,
  search_terms TEXT,
  sources TEXT,
  notes TEXT
);

-- From release/knowledge_graph_entities.json
CREATE TABLE IF NOT EXISTS kg_entities (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE NOT NULL COLLATE NOCASE,
  entity_type TEXT,
  description TEXT,
  metadata TEXT
);

-- From release/knowledge_graph_relationships.json
CREATE TABLE IF NOT EXISTS kg_relationships (
  id INTEGER PRIMARY KEY,
  source_id INTEGER REFERENCES kg_entities(id),
  target_id INTEGER REFERENCES kg_entities(id),
  relationship_type TEXT NOT NULL,
  weight REAL DEFAULT 1.0,
  evidence TEXT,
  metadata TEXT
);

-- Indexes: documents
CREATE INDEX IF NOT EXISTS idx_documents_dataset ON documents(dataset);
CREATE INDEX IF NOT EXISTS idx_documents_type ON documents(document_type);
CREATE INDEX IF NOT EXISTS idx_documents_is_photo ON documents(is_photo);
CREATE INDEX IF NOT EXISTS idx_documents_date ON documents(date);
CREATE INDEX IF NOT EXISTS idx_documents_ocr_source ON documents(ocr_source);
CREATE INDEX IF NOT EXISTS idx_documents_file_key ON documents(file_key);

-- Indexes: entities
CREATE INDEX IF NOT EXISTS idx_entities_document ON entities(document_id);
CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_entities_value ON entities(value);
CREATE INDEX IF NOT EXISTS idx_entities_type_value ON entities(entity_type, value);
CREATE INDEX IF NOT EXISTS idx_entities_normalized ON entities(normalized_value);

-- Indexes: chunks
CREATE INDEX IF NOT EXISTS idx_chunks_document ON chunks(document_id);

-- Indexes: chunk_embeddings
CREATE INDEX IF NOT EXISTS idx_ce_chunk_id ON chunk_embeddings(chunk_id);
CREATE INDEX IF NOT EXISTS idx_ce_document_id ON chunk_embeddings(document_id);
CREATE INDEX IF NOT EXISTS idx_ce_created_at ON chunk_embeddings(created_at);
CREATE INDEX IF NOT EXISTS idx_ce_source_text_hash ON chunk_embeddings(source_text_hash);

-- Indexes: summary_embeddings
CREATE INDEX IF NOT EXISTS idx_se_document_id ON summary_embeddings(document_id);
CREATE INDEX IF NOT EXISTS idx_se_created_at ON summary_embeddings(created_at);

-- Indexes: persons
CREATE INDEX IF NOT EXISTS idx_persons_category ON persons(category);
CREATE INDEX IF NOT EXISTS idx_persons_slug ON persons(slug);

-- Recovered redacted text (from community analysis)
CREATE TABLE IF NOT EXISTS recovered_redactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    document_id INTEGER,
    file_key TEXT NOT NULL,
    page_number INTEGER NOT NULL,
    num_fragments INTEGER,
    reconstructed_text TEXT,
    document_type TEXT,
    interest_score REAL,
    names_found TEXT,
    dataset_source TEXT,
    FOREIGN KEY (document_id) REFERENCES documents(id)
);

-- Indexes: recovered_redactions
CREATE INDEX IF NOT EXISTS idx_recovered_redactions_file_key ON recovered_redactions(file_key);
CREATE INDEX IF NOT EXISTS idx_recovered_redactions_document_id ON recovered_redactions(document_id);
CREATE INDEX IF NOT EXISTS idx_recovered_redactions_interest_score ON recovered_redactions(interest_score DESC);

-- Indexes: knowledge graph
CREATE INDEX IF NOT EXISTS idx_kg_entities_type ON kg_entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_kg_relationships_source ON kg_relationships(source_id);
CREATE INDEX IF NOT EXISTS idx_kg_relationships_target ON kg_relationships(target_id);
CREATE INDEX IF NOT EXISTS idx_kg_relationships_type ON kg_relationships(relationship_type);
