server/src/database/index.ts

import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';
import { config } from '../config';

let db: Database.Database;

function isValidSqliteFile(filePath: string): boolean {
  if (!fs.existsSync(filePath)) return false;
  try {
    const fd = fs.openSync(filePath, 'r');
    const buf = Buffer.alloc(16);
    fs.readSync(fd, buf, 0, 16, 0);
    fs.closeSync(fd);
    return buf.toString('utf8', 0, 15) === 'SQLite format 3';
  } catch {
    return false;
  }
}

function nukeDatabaseFiles(): void {
  for (const suffix of ['', '-wal', '-shm', '-journal']) {
    try {
      fs.unlinkSync(config.databasePath + suffix);
      console.log(`[db] Removed ${config.databasePath + suffix}`);
    } catch {}
  }
}

function openDatabase(): Database.Database {
  const dbDir = path.dirname(config.databasePath);
  if (!fs.existsSync(dbDir)) {
    fs.mkdirSync(dbDir, { recursive: true });
  }

  // Pre-flight check: if the existing file isn't a valid SQLite file, nuke it.
  if (fs.existsSync(config.databasePath) && !isValidSqliteFile(config.databasePath)) {
    console.error('[db] Existing file is not a valid SQLite database. Removing it.');
    nukeDatabaseFiles();
  }

  let database: Database.Database;
  try {
    database = new Database(config.databasePath);
  } catch (err) {
    console.error('[db] Failed to open database, recreating:', err);
    nukeDatabaseFiles();
    database = new Database(config.databasePath);
  }

  // Try pragmas. If they fail (corruption), nuke and retry.
  try {
    database.pragma('journal_mode = DELETE');
    database.pragma('foreign_keys = ON');
  } catch (err: any) {
    console.error('[db] Pragma failed, recreating database:', err);
    try { database.close(); } catch {}
    nukeDatabaseFiles();
    database = new Database(config.databasePath);
    database.pragma('journal_mode = DELETE');
    database.pragma('foreign_keys = ON');
  }

  // Run integrity check. If fails, nuke and recreate.
  try {
    const integrity = database.pragma('integrity_check') as Array<{ integrity_check: string }>;
    const result = integrity[0]?.integrity_check;
    if (result !== 'ok') {
      console.error(`[db] Integrity check failed: ${result}. Recreating.`);
      database.close();
      nukeDatabaseFiles();
      database = new Database(config.databasePath);
      database.pragma('journal_mode = DELETE');
      database.pragma('foreign_keys = ON');
    }
  } catch (err) {
    console.error('[db] Integrity check threw, recreating:', err);
    try { database.close(); } catch {}
    nukeDatabaseFiles();
    database = new Database(config.databasePath);
    database.pragma('journal_mode = DELETE');
    database.pragma('foreign_keys = ON');
  }

  return database;
}

function migrateSchema(): void {
  const database = db;

  const userCols = database.prepare("PRAGMA table_info(users)").all() as any[];
  const userNames = userCols.map((c: any) => c.name);
  if (!userNames.includes('google_id')) {
    database.exec("ALTER TABLE users ADD COLUMN google_id TEXT");
  }
  if (!userNames.includes('avatar_url')) {
    database.exec("ALTER TABLE users ADD COLUMN avatar_url TEXT");
  }
  if (!userNames.includes('avatar_path')) {
    database.exec("ALTER TABLE users ADD COLUMN avatar_path TEXT");
  }
  database.exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_users_google_id ON users(google_id)");

  const sessionCols = database.prepare("PRAGMA table_info(sessions)").all() as any[];
  const sessionNames = sessionCols.map((c: any) => c.name);
  if (!sessionNames.includes('device_info')) {
    database.exec("ALTER TABLE sessions ADD COLUMN device_info TEXT DEFAULT ''");
  }
  if (!sessionNames.includes('ip')) {
    database.exec("ALTER TABLE sessions ADD COLUMN ip TEXT DEFAULT ''");
  }
  if (!sessionNames.includes('user_agent')) {
    database.exec("ALTER TABLE sessions ADD COLUMN user_agent TEXT DEFAULT ''");
  }
  if (!sessionNames.includes('last_active')) {
    database.exec("ALTER TABLE sessions ADD COLUMN last_active INTEGER DEFAULT 0");
  }
}

export function getDatabase(): Database.Database {
  if (!db) {
    db = openDatabase();
    initializeSchema();
    migrateSchema();
  }
  return db;
}

function initializeSchema(): void {
  const database = db;
  database.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      email TEXT UNIQUE NOT NULL,
      username TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      verified INTEGER DEFAULT 0,
      verification_token TEXT,
      reset_token TEXT,
      reset_token_expires INTEGER,
      google_id TEXT,
      avatar_url TEXT,
      avatar_path TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS projects (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      name TEXT NOT NULL,
      framework TEXT NOT NULL,
      description TEXT,
      encrypted_data TEXT NOT NULL,
      encryption_iv TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      token TEXT UNIQUE NOT NULL,
      device_info TEXT DEFAULT '',
      ip TEXT DEFAULT '',
      user_agent TEXT DEFAULT '',
      last_active INTEGER DEFAULT 0,
      expires_at INTEGER NOT NULL,
      created_at INTEGER NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS project_collaborators (
      project_id TEXT NOT NULL,
      user_id TEXT NOT NULL,
      permission TEXT NOT NULL DEFAULT 'edit' CHECK(permission IN ('view','edit','admin')),
      added_by TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      PRIMARY KEY (project_id, user_id),
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects(user_id);
    CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
    CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
    CREATE INDEX IF NOT EXISTS idx_collaborators_project ON project_collaborators(project_id);
    CREATE INDEX IF NOT EXISTS idx_collaborators_user ON project_collaborators(user_id);
  `);
}

export function closeDatabase(): void {
  if (db) {
    try { db.close(); } catch {}
  }
}