studyos/server/db.js

const path = require('path');
const fs = require('fs');

const DATA_DIR = path.resolve(__dirname, '..', 'data');
const DB_PATH = path.join(DATA_DIR, 'studyos.db');

if (!fs.existsSync(DATA_DIR)) {
  fs.mkdirSync(DATA_DIR, { recursive: true });
}

let _sqlDb = null;

function saveToDisk() {
  if (!_sqlDb) return;
  const data = _sqlDb.export();
  fs.writeFileSync(DB_PATH, Buffer.from(data));
}

function flatParams(params) {
  if (params.length === 1 && Array.isArray(params[0])) return params[0];
  return params;
}

function safeAddColumn(table, col, def) {
  try { db.exec(`ALTER TABLE ${table} ADD COLUMN ${col} ${def}`); }
  catch (e) { /* column exists */ }
}

function createWrapper(sqlDb) {
  return {
    prepare(sql) {
      return {
        run(...params) {
          const p = flatParams(params);
          sqlDb.run(sql, p);
          const rowidResult = sqlDb.exec('SELECT last_insert_rowid()');
          const lastInsertRowid = rowidResult.length > 0 ? rowidResult[0].values[0][0] : 0;
          const changes = sqlDb.getRowsModified();
          saveToDisk();
          return { changes, lastInsertRowid };
        },
        get(...params) {
          const p = flatParams(params);
          let stmt = null;
          try {
            stmt = sqlDb.prepare(sql);
            stmt.bind(p);
            let result = null;
            if (stmt.step()) result = stmt.getAsObject();
            return result;
          } finally {
            if (stmt) stmt.free();
          }
        },
        all(...params) {
          const p = flatParams(params);
          let stmt = null;
          try {
            stmt = sqlDb.prepare(sql);
            stmt.bind(p);
            const results = [];
            while (stmt.step()) results.push(stmt.getAsObject());
            return results;
          } finally {
            if (stmt) stmt.free();
          }
        },
      };
    },
    exec(sql) {
      sqlDb.exec(sql);
      saveToDisk();
    },
    pragma(sql) {
      sqlDb.exec('PRAGMA ' + sql + ';');
    },
  };
}

// Placeholder wrapper — methods are replaced by initDB() after sql.js loads.
// This allows synchronous require('db') in route modules before async init.
const db = createWrapper(null);

async function initDB() {
  const SQL = await import('sql.js');
  const initSqlJs = SQL.default;
  const sqlModule = await initSqlJs();

  let sqlDb;
  if (fs.existsSync(DB_PATH)) {
    const buffer = fs.readFileSync(DB_PATH);
    sqlDb = new sqlModule.Database(buffer);
  } else {
    sqlDb = new sqlModule.Database();
  }

  _sqlDb = sqlDb;

  const real = createWrapper(sqlDb);
  db.prepare = real.prepare;
  db.exec = real.exec;
  db.pragma = real.pragma;

  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');

  db.exec(`
    CREATE TABLE IF NOT EXISTS models (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      api_base TEXT NOT NULL,
      api_key TEXT NOT NULL DEFAULT '',
      provider TEXT NOT NULL CHECK(provider IN ('openai', 'anthropic')),
      is_default_main INTEGER NOT NULL DEFAULT 0,
      is_default_fork INTEGER NOT NULL DEFAULT 0,
      is_default_exam INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS conversations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      type TEXT NOT NULL CHECK(type IN ('main', 'fork')),
      parent_id INTEGER REFERENCES conversations(id) ON DELETE SET NULL,
      model_id INTEGER REFERENCES models(id) ON DELETE SET NULL,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
      role TEXT NOT NULL CHECK(role IN ('user', 'assistant', 'system', 'context_merge')),
      content TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS pdfs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      filename TEXT NOT NULL,
      original_name TEXT NOT NULL,
      content_markdown TEXT NOT NULL DEFAULT '',
      pages INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      reorder_index INTEGER NOT NULL DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS progress (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      topic TEXT NOT NULL UNIQUE,
      exercises_done INTEGER NOT NULL DEFAULT 0,
      exercises_correct INTEGER NOT NULL DEFAULT 0,
      last_session TEXT,
      notes TEXT NOT NULL DEFAULT '[]'
    );

    CREATE TABLE IF NOT EXISTS notes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      content_markdown TEXT NOT NULL DEFAULT '',
      tags TEXT NOT NULL DEFAULT '[]',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      updated_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS config (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL DEFAULT ''
    );

    CREATE TABLE IF NOT EXISTS exams (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      score REAL NOT NULL DEFAULT 0,
      topics TEXT NOT NULL DEFAULT '[]',
      taken_at TEXT NOT NULL DEFAULT (datetime('now')),
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS flashcards (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      question TEXT NOT NULL,
      answer TEXT NOT NULL,
      pdf_id INTEGER REFERENCES pdfs(id) ON DELETE SET NULL,
      message_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
      seen INTEGER NOT NULL DEFAULT 0,
      topic TEXT,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS study_sessions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      session_date TEXT NOT NULL,
      minutes INTEGER NOT NULL DEFAULT 0,
      topic TEXT,
      UNIQUE(session_date, topic)
    );

    CREATE TABLE IF NOT EXISTS topic_relationships (
      from_topic TEXT NOT NULL,
      to_topic TEXT NOT NULL,
      domain TEXT,
      PRIMARY KEY(from_topic, to_topic)
    );

    CREATE TABLE IF NOT EXISTS flashcard_reviews (
      flashcard_id INTEGER PRIMARY KEY REFERENCES flashcards(id) ON DELETE CASCADE,
      ease_factor REAL NOT NULL DEFAULT 2.5,
      interval_days INTEGER NOT NULL DEFAULT 1,
      repetitions INTEGER NOT NULL DEFAULT 0,
      next_review TEXT NOT NULL DEFAULT (date('now')),
      last_review TEXT
    );
  `);

  // Try to create FTS5 virtual tables and sync triggers
  let fts5Available = false;
  try {
    db.exec(`
      CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(content, content='messages', content_rowid='id');
      CREATE VIRTUAL TABLE IF NOT EXISTS pdfs_fts USING fts5(content_markdown, content='pdfs', content_rowid='id');

      CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
        INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
      END;
      CREATE TRIGGER IF NOT EXISTS messages_au AFTER UPDATE ON messages BEGIN
        UPDATE messages_fts SET content = new.content WHERE rowid = new.id;
      END;
      CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
        DELETE FROM messages_fts WHERE rowid = old.id;
      END;

      CREATE TRIGGER IF NOT EXISTS pdfs_ai AFTER INSERT ON pdfs BEGIN
        INSERT INTO pdfs_fts(rowid, content_markdown) VALUES (new.id, new.content_markdown);
      END;
      CREATE TRIGGER IF NOT EXISTS pdfs_au AFTER UPDATE ON pdfs BEGIN
        UPDATE pdfs_fts SET content_markdown = new.content_markdown WHERE rowid = new.id;
      END;
      CREATE TRIGGER IF NOT EXISTS pdfs_ad AFTER DELETE ON pdfs BEGIN
        DELETE FROM pdfs_fts WHERE rowid = old.id;
      END;
    `);
    fts5Available = true;
  } catch (err) {
    console.warn('[db] FTS5 setup failed:', err.message, '— search will use LIKE fallback');
  }
  db._fts5Available = fts5Available;

  const modelCount = db.prepare('SELECT COUNT(*) as count FROM models').get();
  if (!modelCount || modelCount.count === 0) {
    db.prepare(`
      INSERT INTO models (name, api_base, api_key, provider, is_default_main)
      VALUES (?, ?, ?, ?, ?)
    `).run('claude-sonnet-4', 'https://api.anthropic.com', '', 'anthropic', 1);
  }

  const vlmConfig = db.prepare("SELECT value FROM config WHERE key = ?").get('vlm_endpoint');
  if (!vlmConfig) {
    db.prepare('INSERT INTO config (key, value) VALUES (?, ?)').run('vlm_endpoint', 'http://localhost:8080/vlm');
  }

  // Additive schema changes for ai-advanced-batch4
  db.exec(`
    CREATE TABLE IF NOT EXISTS embeddings (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      pdf_id      INTEGER NOT NULL,
      chunk_index INTEGER NOT NULL,
      vector      BLOB    NOT NULL,
      content     TEXT    NOT NULL,
      created_at  TEXT    DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE
    );
    CREATE INDEX IF NOT EXISTS idx_embeddings_pdf ON embeddings(pdf_id);

    CREATE TABLE IF NOT EXISTS shared_conversations (
      token       TEXT PRIMARY KEY,
      conv_id     INTEGER NOT NULL,
      role_label  TEXT    NOT NULL DEFAULT 'compañero',
      created_at  TEXT    DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (conv_id) REFERENCES conversations(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS conversation_participants (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      conversation_id INTEGER NOT NULL,
      user_name       TEXT    NOT NULL,
      joined_at       TEXT    DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
    );
  `);

  safeAddColumn('progress', 'wrong_streak', 'INTEGER DEFAULT 0');
  safeAddColumn('progress', 'global_wrong_streak', 'INTEGER DEFAULT 0');
  safeAddColumn('progress', 'difficulty_level', 'TEXT DEFAULT \'normal\'');

  safeAddColumn('conversations', 'buddy_meta', 'TEXT');

  safeAddColumn('exams', 'questions', 'TEXT');
  safeAddColumn('exams', 'answers', 'TEXT');
  safeAddColumn('exams', 'duration_seconds', 'INTEGER');
  safeAddColumn('exams', 'started_at', 'TEXT');
  safeAddColumn('exams', 'status', 'TEXT DEFAULT \'pending\'');
  safeAddColumn('exams', 'conversation_id', 'INTEGER');

  return db;
}

module.exports = db;
module.exports.initDB = initDB;