/**
 * Benchmark Database Layer — SQLite via better-sqlite3
 *
 * 提供 benchmark_runs, comparisons, security_scans 三张表的
 * 初始化、插入、查询功能。零配置，单文件部署。
 */

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

// 数据目录
const DATA_DIR = path.resolve(__dirname, '../../../data');
const DB_PATH = path.join(DATA_DIR, 'benchmark_logs.sqlite');

let db = null;

/**
 * 初始化数据库 — 创建表 + 索引
 */
function initDB() {
  if (db) return db;

  // 确保 data/ 目录存在
  if (!fs.existsSync(DATA_DIR)) {
    fs.mkdirSync(DATA_DIR, { recursive: true });
  }

  db = new Database(DB_PATH);

  // 开启 WAL 模式提升并发性能
  db.pragma('journal_mode = WAL');

  // 创建表
  db.exec(`
    -- 评测执行记录
    CREATE TABLE IF NOT EXISTS benchmark_runs (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_name      TEXT    NOT NULL,
      version         TEXT,
      run_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
      success_rate    REAL,
      accuracy        REAL,
      speed_p50       REAL,
      speed_p95       REAL,
      cost_per_run    REAL,
      robustness      REAL,
      completeness    REAL,
      weighted_score  REAL,
      elo_before      INTEGER,
      elo_after       INTEGER,
      test_cases_total  INTEGER,
      test_cases_passed INTEGER,
      error_log       TEXT
    );

    -- Skill 间对比记录 (ELO 计算用)
    CREATE TABLE IF NOT EXISTS comparisons (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_a     TEXT    NOT NULL,
      skill_b     TEXT    NOT NULL,
      scene       TEXT,
      layer       INTEGER,
      winner      TEXT,
      score_a     REAL,
      score_b     REAL,
      compared_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    -- 安全扫描记录
    CREATE TABLE IF NOT EXISTS security_scans (
      id          INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_name  TEXT    NOT NULL,
      scanned_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
      status      TEXT    DEFAULT 'pending',
      confidence  TEXT,
      report      TEXT
    );

    -- 索引
    CREATE INDEX IF NOT EXISTS idx_runs_skill  ON benchmark_runs(skill_name);
    CREATE INDEX IF NOT EXISTS idx_runs_at     ON benchmark_runs(run_at);
    CREATE INDEX IF NOT EXISTS idx_comp_skills ON comparisons(skill_a, skill_b);
    CREATE INDEX IF NOT EXISTS idx_comp_scene  ON comparisons(scene);
    CREATE INDEX IF NOT EXISTS idx_scan_skill  ON security_scans(skill_name);
  `);

  console.log('[BenchmarkDB] Initialized SQLite at', DB_PATH);
  return db;
}

/**
 * 插入一条 benchmark 运行记录
 */
function insertBenchmarkRun(data) {
  const d = initDB();
  const stmt = d.prepare(`
    INSERT INTO benchmark_runs
      (skill_name, version, success_rate, accuracy, speed_p50, speed_p95,
       cost_per_run, robustness, completeness, weighted_score,
       elo_before, elo_after, test_cases_total, test_cases_passed, error_log)
    VALUES
      (@skill_name, @version, @success_rate, @accuracy, @speed_p50, @speed_p95,
       @cost_per_run, @robustness, @completeness, @weighted_score,
       @elo_before, @elo_after, @test_cases_total, @test_cases_passed, @error_log)
  `);

  const result = stmt.run({
    skill_name: data.skillName,
    version: data.version || null,
    success_rate: data.successRate ?? null,
    accuracy: data.accuracy ?? null,
    speed_p50: data.speedP50 ?? null,
    speed_p95: data.speedP95 ?? null,
    cost_per_run: data.costPerRun ?? null,
    robustness: data.robustness ?? null,
    completeness: data.completeness ?? null,
    weighted_score: data.weightedScore ?? null,
    elo_before: data.eloBefore ?? null,
    elo_after: data.eloAfter ?? null,
    test_cases_total: data.testCasesTotal ?? null,
    test_cases_passed: data.testCasesPassed ?? null,
    error_log: data.errorLog ?? null,
  });

  return result.lastInsertRowid;
}

/**
 * 获取某 Skill 的 benchmark 历史
 */
function getSkillHistory(skillName, limit = 30) {
  const d = initDB();
  return d.prepare(`
    SELECT * FROM benchmark_runs
    WHERE skill_name = ?
    ORDER BY run_at DESC
    LIMIT ?
  `).all(skillName, limit);
}

/**
 * 获取某 Skill 的 ELO 变化趋势
 */
function getEloHistory(skillName, limit = 50) {
  const d = initDB();
  return d.prepare(`
    SELECT id, run_at, elo_before, elo_after, weighted_score
    FROM benchmark_runs
    WHERE skill_name = ? AND elo_after IS NOT NULL
    ORDER BY run_at ASC
    LIMIT ?
  `).all(skillName, limit);
}

/**
 * 插入一条对比记录
 */
function insertComparison(data) {
  const d = initDB();
  const stmt = d.prepare(`
    INSERT INTO comparisons (skill_a, skill_b, scene, layer, winner, score_a, score_b)
    VALUES (@skill_a, @skill_b, @scene, @layer, @winner, @score_a, @score_b)
  `);

  return stmt.run({
    skill_a: data.skillA,
    skill_b: data.skillB,
    scene: data.scene || null,
    layer: data.layer || null,
    winner: data.winner || null,
    score_a: data.scoreA ?? null,
    score_b: data.scoreB ?? null,
  });
}

/**
 * 获取最近的对比记录
 */
function getRecentComparisons(limit = 50) {
  const d = initDB();
  return d.prepare(`
    SELECT * FROM comparisons
    ORDER BY compared_at DESC
    LIMIT ?
  `).all(limit);
}

/**
 * 获取全局 benchmark 统计
 */
function getBenchmarkStats() {
  const d = initDB();
  const totalRuns = d.prepare('SELECT COUNT(*) as count FROM benchmark_runs').get();
  const totalComparisons = d.prepare('SELECT COUNT(*) as count FROM comparisons').get();
  const lastRun = d.prepare('SELECT MAX(run_at) as last FROM benchmark_runs').get();

  return {
    totalRuns: totalRuns.count,
    totalComparisons: totalComparisons.count,
    lastRun: lastRun.last,
  };
}

/**
 * 关闭数据库连接
 */
function closeDB() {
  if (db) {
    db.close();
    db = null;
  }
}

module.exports = {
  initDB,
  insertBenchmarkRun,
  getSkillHistory,
  getEloHistory,
  insertComparison,
  getRecentComparisons,
  getBenchmarkStats,
  closeDB,
};