codebase

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

export interface DbProject {
  id: number;
  root: string;
  name: string;
  framework: string;
  language: string;
  entrypoints: string;   // JSON array of strings
  conventions: string;   // JSON array of strings
  createdAt: string;
  updatedAt: string;
}

export interface DbMemoryNote {
  id: number;
  projectId: number;
  category: string;
  content: string;
  createdAt: string;
}

export function openDb(dataPath: string): Database.Database {
  const raw = dataPath.trim() || join(homedir(), "codebase-memory");
  const dir = raw.startsWith("~/") ? join(homedir(), raw.slice(2)) : raw;
  mkdirSync(dir, { recursive: true });
  const db = new Database(join(dir, "codebase.db"));
  db.pragma("journal_mode = WAL");
  db.exec(`
    CREATE TABLE IF NOT EXISTS projects (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      root TEXT UNIQUE NOT NULL,
      name TEXT NOT NULL,
      framework TEXT DEFAULT '',
      language TEXT DEFAULT '',
      entrypoints TEXT DEFAULT '[]',
      conventions TEXT DEFAULT '[]',
      createdAt TEXT NOT NULL,
      updatedAt TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS memory_notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      projectId INTEGER NOT NULL REFERENCES projects(id),
      category TEXT NOT NULL,
      content TEXT NOT NULL,
      createdAt TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_projects_root ON projects(root);
    CREATE INDEX IF NOT EXISTS idx_memory_projectId ON memory_notes(projectId);
    CREATE INDEX IF NOT EXISTS idx_memory_category ON memory_notes(category);
  `);
  return db;
}

const _dbCache = new Map<string, Database.Database>();
export function getDb(dataPath: string): Database.Database {
  let db = _dbCache.get(dataPath);
  if (!db) { db = openDb(dataPath); _dbCache.set(dataPath, db); }
  return db;
}

export function upsertProject(
  db: Database.Database,
  root: string,
  name: string,
  framework: string,
  language: string,
  entrypoints: string[],
  conventions: string[],
): number {
  const now = new Date().toISOString();
  const existing = db.prepare("SELECT id FROM projects WHERE root = ?").get(root) as { id: number } | undefined;
  if (existing) {
    db.prepare(`UPDATE projects SET name=?, framework=?, language=?, entrypoints=?, conventions=?, updatedAt=? WHERE id=?`)
      .run(name, framework, language, JSON.stringify(entrypoints), JSON.stringify(conventions), now, existing.id);
    return existing.id;
  }
  const r = db.prepare(
    "INSERT INTO projects (root, name, framework, language, entrypoints, conventions, createdAt, updatedAt) VALUES (?,?,?,?,?,?,?,?)"
  ).run(root, name, framework, language, JSON.stringify(entrypoints), JSON.stringify(conventions), now, now);
  return r.lastInsertRowid as number;
}

export function getProjectByRoot(db: Database.Database, root: string): DbProject | undefined {
  return db.prepare("SELECT * FROM projects WHERE root = ?").get(root) as DbProject | undefined;
}

export function insertMemoryNote(
  db: Database.Database,
  projectId: number,
  category: string,
  content: string,
): number {
  const r = db.prepare(
    "INSERT INTO memory_notes (projectId, category, content, createdAt) VALUES (?, ?, ?, ?)"
  ).run(projectId, category, content, new Date().toISOString());
  return r.lastInsertRowid as number;
}

export function getMemoryNotes(
  db: Database.Database,
  projectId: number,
  category?: string,
): DbMemoryNote[] {
  if (category) {
    return db.prepare("SELECT * FROM memory_notes WHERE projectId = ? AND category = ? ORDER BY createdAt DESC")
      .all(projectId, category) as DbMemoryNote[];
  }
  return db.prepare("SELECT * FROM memory_notes WHERE projectId = ? ORDER BY category, createdAt DESC")
    .all(projectId) as DbMemoryNote[];
}

export function deleteMemoryNote(db: Database.Database, id: number): boolean {
  const r = db.prepare("DELETE FROM memory_notes WHERE id = ?").run(id);
  return r.changes > 0;
}

export function listProjects(db: Database.Database): Array<Pick<DbProject, "id" | "root" | "name" | "framework" | "language" | "updatedAt">> {
  return db.prepare("SELECT id, root, name, framework, language, updatedAt FROM projects ORDER BY updatedAt DESC")
    .all() as Array<Pick<DbProject, "id" | "root" | "name" | "framework" | "language" | "updatedAt">>;
}