knowledge

import Database from "better-sqlite3";
import { homedir } from "os";
import { mkdirSync } from "fs";
import { join } from "path";

export function getDataDir(configPath: string): string {
  const p = configPath.trim() || "~/knowledge-data";
  return p.startsWith("~/") ? join(homedir(), p.slice(2)) : p;
}

const _dbCache = new Map<string, Database.Database>();

export function getDb(dataDir: string): Database.Database {
  if (_dbCache.has(dataDir)) return _dbCache.get(dataDir)!;
  mkdirSync(dataDir, { recursive: true });
  const db = new Database(join(dataDir, "knowledge.db"));
  db.pragma("journal_mode = WAL");
  db.pragma("foreign_keys = ON");
  db.exec(`
    CREATE TABLE IF NOT EXISTS notes (
      id        INTEGER PRIMARY KEY AUTOINCREMENT,
      title     TEXT    NOT NULL,
      content   TEXT    NOT NULL,
      source    TEXT    NOT NULL DEFAULT '',
      tags      TEXT    NOT NULL DEFAULT '[]',
      createdAt TEXT    NOT NULL,
      updatedAt TEXT    NOT NULL
    );

    CREATE TABLE IF NOT EXISTS concepts (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      name        TEXT    NOT NULL UNIQUE,
      description TEXT    NOT NULL DEFAULT '',
      createdAt   TEXT    NOT NULL,
      updatedAt   TEXT    NOT NULL
    );

    CREATE TABLE IF NOT EXISTS note_concepts (
      noteId    INTEGER NOT NULL,
      conceptId INTEGER NOT NULL,
      PRIMARY KEY (noteId, conceptId),
      FOREIGN KEY (noteId)    REFERENCES notes(id)    ON DELETE CASCADE,
      FOREIGN KEY (conceptId) REFERENCES concepts(id) ON DELETE CASCADE
    );

    CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
      title, content, source, tags,
      content='notes',
      content_rowid='id'
    );

    CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
      INSERT INTO notes_fts(rowid, title, content, source, tags)
      VALUES (new.id, new.title, new.content, new.source, new.tags);
    END;

    CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
      INSERT INTO notes_fts(notes_fts, rowid, title, content, source, tags)
      VALUES ('delete', old.id, old.title, old.content, old.source, old.tags);
      INSERT INTO notes_fts(rowid, title, content, source, tags)
      VALUES (new.id, new.title, new.content, new.source, new.tags);
    END;

    CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
      INSERT INTO notes_fts(notes_fts, rowid, title, content, source, tags)
      VALUES ('delete', old.id, old.title, old.content, old.source, old.tags);
    END;

    CREATE INDEX IF NOT EXISTS idx_notes_createdAt    ON notes(createdAt);
    CREATE INDEX IF NOT EXISTS idx_concepts_name      ON concepts(name);
    CREATE INDEX IF NOT EXISTS idx_nc_noteId          ON note_concepts(noteId);
    CREATE INDEX IF NOT EXISTS idx_nc_conceptId       ON note_concepts(conceptId);
  `);
  _dbCache.set(dataDir, db);
  return db;
}

export interface NoteRow {
  id: number;
  title: string;
  content: string;
  source: string;
  tags: string;
  createdAt: string;
  updatedAt: string;
}

export interface ConceptRow {
  id: number;
  name: string;
  description: string;
  createdAt: string;
  updatedAt: string;
}

export function insertNote(
  db: Database.Database,
  title: string,
  content: string,
  source: string,
  tags: string[],
): number {
  const now = new Date().toISOString();
  const r = db.prepare(
    `INSERT INTO notes (title, content, source, tags, createdAt, updatedAt)
     VALUES (?, ?, ?, ?, ?, ?)`
  ).run(title, content, source, JSON.stringify(tags), now, now);
  return r.lastInsertRowid as number;
}

export function updateNote(
  db: Database.Database,
  id: number,
  patch: Partial<{ title: string; content: string; source: string; tags: string[] }>,
): void {
  const row = db.prepare("SELECT * FROM notes WHERE id = ?").get(id) as NoteRow | undefined;
  if (!row) throw new Error(`Note ID ${id} not found.`);
  const now = new Date().toISOString();
  db.prepare(
    `UPDATE notes SET title=?, content=?, source=?, tags=?, updatedAt=? WHERE id=?`
  ).run(
    patch.title ?? row.title,
    patch.content ?? row.content,
    patch.source ?? row.source,
    patch.tags ? JSON.stringify(patch.tags) : row.tags,
    now,
    id,
  );
}

export function deleteNote(db: Database.Database, id: number): void {
  const r = db.prepare("DELETE FROM notes WHERE id = ?").run(id);
  if (r.changes === 0) throw new Error(`Note ID ${id} not found.`);
}

export function getNoteById(db: Database.Database, id: number): NoteRow {
  const row = db.prepare("SELECT * FROM notes WHERE id = ?").get(id) as NoteRow | undefined;
  if (!row) throw new Error(`Note ID ${id} not found.`);
  return row;
}

export function listNotes(
  db: Database.Database,
  opts: { tag?: string; since?: string; limit?: number } = {},
): NoteRow[] {
  let sql = "SELECT * FROM notes";
  const params: unknown[] = [];
  const clauses: string[] = [];
  if (opts.tag) { clauses.push("tags LIKE ?"); params.push(`%"${opts.tag}"%`); }
  if (opts.since) { clauses.push("createdAt >= ?"); params.push(opts.since); }
  if (clauses.length > 0) sql += " WHERE " + clauses.join(" AND ");
  sql += " ORDER BY createdAt DESC";
  if (opts.limit) { sql += " LIMIT ?"; params.push(opts.limit); }
  return db.prepare(sql).all(...params) as NoteRow[];
}

export function searchNotes(db: Database.Database, query: string, limit = 20): NoteRow[] {
  const safeFtsQuery = `"${query.replace(/"/g, '""')}"`;
  const rows = db.prepare(
    `SELECT notes.* FROM notes_fts
     JOIN notes ON notes.id = notes_fts.rowid
     WHERE notes_fts MATCH ?
     ORDER BY rank
     LIMIT ?`
  ).all(safeFtsQuery, limit) as NoteRow[];
  return rows;
}

export function upsertConcept(
  db: Database.Database,
  name: string,
  description: string,
): ConceptRow {
  const now = new Date().toISOString();
  db.prepare(
    `INSERT INTO concepts (name, description, createdAt, updatedAt)
     VALUES (?, ?, ?, ?)
     ON CONFLICT(name) DO UPDATE SET description=excluded.description, updatedAt=excluded.updatedAt`
  ).run(name, description, now, now);
  return db.prepare("SELECT * FROM concepts WHERE name = ?").get(name) as ConceptRow;
}

export function getConceptByName(db: Database.Database, name: string): ConceptRow | undefined {
  return db.prepare("SELECT * FROM concepts WHERE name = ?").get(name) as ConceptRow | undefined;
}

export function listConcepts(db: Database.Database): Array<ConceptRow & { noteCount: number }> {
  return db.prepare(
    `SELECT c.*, COUNT(nc.noteId) as noteCount
     FROM concepts c
     LEFT JOIN note_concepts nc ON nc.conceptId = c.id
     GROUP BY c.id
     ORDER BY noteCount DESC, c.name ASC`
  ).all() as Array<ConceptRow & { noteCount: number }>;
}

export function linkNoteToConcept(db: Database.Database, noteId: number, conceptId: number): void {
  db.prepare(
    `INSERT OR IGNORE INTO note_concepts (noteId, conceptId) VALUES (?, ?)`
  ).run(noteId, conceptId);
}

export function unlinkNoteConcept(db: Database.Database, noteId: number, conceptId: number): void {
  db.prepare("DELETE FROM note_concepts WHERE noteId = ? AND conceptId = ?").run(noteId, conceptId);
}

export function getConceptNotes(db: Database.Database, conceptId: number): NoteRow[] {
  return db.prepare(
    `SELECT n.* FROM notes n
     JOIN note_concepts nc ON nc.noteId = n.id
     WHERE nc.conceptId = ?
     ORDER BY n.createdAt DESC`
  ).all(conceptId) as NoteRow[];
}

export function getNoteConcepts(db: Database.Database, noteId: number): ConceptRow[] {
  return db.prepare(
    `SELECT c.* FROM concepts c
     JOIN note_concepts nc ON nc.conceptId = c.id
     WHERE nc.noteId = ?`
  ).all(noteId) as ConceptRow[];
}

export function getUnlinkedNotes(db: Database.Database, limit = 20): NoteRow[] {
  return db.prepare(
    `SELECT n.* FROM notes n
     WHERE NOT EXISTS (SELECT 1 FROM note_concepts nc WHERE nc.noteId = n.id)
     ORDER BY n.createdAt DESC
     LIMIT ?`
  ).all(limit) as NoteRow[];
}

export function countNotes(db: Database.Database): number {
  const row = db.prepare("SELECT COUNT(*) AS n FROM notes").get() as { n: number };
  return row.n;
}