宜博 / lemonboss

/**
 * Skill Database — SQLite 替代 skill_list.json
 *
 * 表结构：
 *   skills          主表（所有 Skill 元数据）
 *   skill_versions   版本历史（每次 bumpVersion 记录快照）
 *   skill_downloads  下载记录（关联用户）
 *   skill_arena      Arena 评分数据（1:1 关联 skills）
 *
 * API 完全兼容 SkillRegistry 类接口，可渐进替换。
 */

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');
const crypto = require('crypto');
const config = require('../config');
const { initSkillDir, getTargetDir, resolveSkillDir } = require('./skill-dir-manager');

const DATA_DIR = path.resolve(__dirname, '../../../data');
const DB_PATH = process.env.SKILLS_DB_PATH || path.join(DATA_DIR, 'skills.sqlite');

let db = null;

function getDb() {
  if (db) return db;

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

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

  // ─── 主表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skills (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT UNIQUE NOT NULL,
      skill_name      TEXT UNIQUE NOT NULL,
      directory       TEXT,
      description     TEXT DEFAULT '',
      domain          TEXT DEFAULT '',
      scope           TEXT DEFAULT '',
      status          TEXT DEFAULT 'training',
      version         TEXT DEFAULT '1.0.0',
      benchmark_version TEXT DEFAULT '1.0.0',
      industry_avg_score REAL,
      elite_avg_score REAL,
      current_score   REAL,
      use_count       INTEGER DEFAULT 0,
      download_count  INTEGER DEFAULT 0,
      update_count    INTEGER DEFAULT 0,
      last_used       DATETIME,
      last_updated    DATETIME DEFAULT CURRENT_TIMESTAMP,
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
      keywords        TEXT DEFAULT '[]',
      type            TEXT DEFAULT 'executor',
      agent_name      TEXT,
      layer           INTEGER,
      dependencies    TEXT DEFAULT '[]',
      scene           TEXT DEFAULT '',
      author          TEXT DEFAULT 'LemonAI',
      license         TEXT DEFAULT 'MIT',
      source          TEXT DEFAULT 'local',
      tags            TEXT DEFAULT '[]'
    );

    CREATE INDEX IF NOT EXISTS idx_skills_name ON skills(skill_name);
    CREATE INDEX IF NOT EXISTS idx_skills_domain ON skills(domain);
    CREATE INDEX IF NOT EXISTS idx_skills_status ON skills(status);
  `);

  // ─── 三维标签字段迁移 (roles/scenes/platforms) ───
  try {
    db.exec(`ALTER TABLE skills ADD COLUMN roles TEXT DEFAULT '[]'`);
  } catch (e) { /* column likely already exists */ }
  try {
    db.exec(`ALTER TABLE skills ADD COLUMN scenes TEXT DEFAULT '[]'`);
  } catch (e) { /* column likely already exists */ }
  try {
    db.exec(`ALTER TABLE skills ADD COLUMN platforms TEXT DEFAULT '[]'`);
  } catch (e) { /* column likely already exists */ }

  // ─── Arena 评分表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_arena (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT UNIQUE NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      elo             REAL DEFAULT 1200,
      elo_margin      REAL DEFAULT 50,
      rank            INTEGER,
      rank_spread     REAL,
      benchmark_runs  INTEGER DEFAULT 0,
      last_benchmark  DATETIME,
      score_success_rate  REAL,
      score_accuracy      REAL,
      score_speed         REAL,
      score_cost          REAL,
      score_robustness    REAL,
      score_completeness  REAL,
      security_status     TEXT DEFAULT 'pending',
      security_confidence REAL,
      security_last_scan  DATETIME,
      highlighted     INTEGER DEFAULT 0,
      trending        INTEGER DEFAULT 0,
      global_rank     INTEGER
    );
  `);

  // ─── 版本历史表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_versions (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      version         TEXT NOT NULL,
      previous_version TEXT,
      bump_type       TEXT,
      score           REAL,
      change_summary  TEXT DEFAULT '',
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_versions_skill ON skill_versions(skill_id);
  `);

  // ─── 下载记录表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_downloads (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      user_id         INTEGER,
      user_email      TEXT,
      download_version TEXT,
      ip_address      TEXT,
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_downloads_skill ON skill_downloads(skill_id);
    CREATE INDEX IF NOT EXISTS idx_downloads_user ON skill_downloads(user_id);
  `);

  // ─── 更新历史表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_update_history (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      from_version    TEXT NOT NULL,
      to_version      TEXT NOT NULL,
      count           INTEGER DEFAULT 1,
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_update_hist_skill ON skill_update_history(skill_id);
  `);

  // ─── Hunt 数据源配置表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_hunt_sources (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      source_type     TEXT,
      source_url      TEXT,
      fetch_interval  TEXT DEFAULT 'on_demand',
      last_fetched    DATETIME,
      data_count      INTEGER DEFAULT 0,
      config_json     TEXT DEFAULT '{}',
      UNIQUE(skill_id, source_url)
    );

    CREATE INDEX IF NOT EXISTS idx_hunt_sources_skill ON skill_hunt_sources(skill_id);
  `);

  // ─── Digest 版本记录表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_digests (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      version         TEXT NOT NULL,
      digest_type     TEXT,
      file_path       TEXT,
      file_size       INTEGER,
      checksum        TEXT,
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_digests_skill ON skill_digests(skill_id);
  `);

  // ─── Evolve 测试记录表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_evolve_runs (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      version         TEXT NOT NULL,
      run_type        TEXT DEFAULT 'benchmark',
      status          TEXT DEFAULT 'pending',
      score           REAL,
      score_delta     REAL,
      test_count      INTEGER,
      pass_count      INTEGER,
      started_at      DATETIME,
      completed_at    DATETIME,
      result_path     TEXT
    );

    CREATE INDEX IF NOT EXISTS idx_evolve_runs_skill ON skill_evolve_runs(skill_id);
  `);

  // ─── Production 日志聚合表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_production_stats (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      date            TEXT NOT NULL,
      requests        INTEGER DEFAULT 0,
      downloads       INTEGER DEFAULT 0,
      errors          INTEGER DEFAULT 0,
      avg_latency_ms  REAL,
      UNIQUE(skill_id, date)
    );

    CREATE INDEX IF NOT EXISTS idx_prod_stats_skill ON skill_production_stats(skill_id);
  `);

  // ─── FTS5 全文搜索索引 ───
  try {
    db.exec(`
      CREATE VIRTUAL TABLE IF NOT EXISTS skills_fts USING fts5(
        skill_name, description, domain, keywords, tags,
        content='skills', content_rowid='id'
      );
    `);

    // FTS5 同步触发器 — INSERT/UPDATE/DELETE 时自动更新索引
    db.exec(`
      CREATE TRIGGER IF NOT EXISTS skills_ai AFTER INSERT ON skills BEGIN
        INSERT INTO skills_fts(rowid, skill_name, description, domain, keywords, tags)
        VALUES (new.id, new.skill_name, new.description, new.domain, new.keywords, new.tags);
      END;
    `);
    db.exec(`
      CREATE TRIGGER IF NOT EXISTS skills_ad AFTER DELETE ON skills BEGIN
        INSERT INTO skills_fts(skills_fts, rowid, skill_name, description, domain, keywords, tags)
        VALUES ('delete', old.id, old.skill_name, old.description, old.domain, old.keywords, old.tags);
      END;
    `);
    db.exec(`
      CREATE TRIGGER IF NOT EXISTS skills_au AFTER UPDATE ON skills BEGIN
        INSERT INTO skills_fts(skills_fts, rowid, skill_name, description, domain, keywords, tags)
        VALUES ('delete', old.id, old.skill_name, old.description, old.domain, old.keywords, old.tags);
        INSERT INTO skills_fts(rowid, skill_name, description, domain, keywords, tags)
        VALUES (new.id, new.skill_name, new.description, new.domain, new.keywords, new.tags);
      END;
    `);

    // 确保 FTS5 索引与 skills 表内容同步
    db.exec(`INSERT INTO skills_fts(skills_fts) VALUES('rebuild');`);
  } catch (e) {
    // FTS5 may not be available on all builds, silently skip
    console.warn('[skill-db] FTS5 not available:', e.message);
  }

  // ─── 收藏表 ───
  db.exec(`
    CREATE TABLE IF NOT EXISTS skill_favorites (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      skill_id        TEXT NOT NULL REFERENCES skills(skill_id) ON DELETE CASCADE,
      user_id         TEXT NOT NULL,
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(skill_id, user_id)
    );

    CREATE INDEX IF NOT EXISTS idx_favorites_user ON skill_favorites(user_id);
    CREATE INDEX IF NOT EXISTS idx_favorites_skill ON skill_favorites(skill_id);
  `);

  // ─── 收藏计数字段迁移 ───
  try {
    db.exec(`ALTER TABLE skills ADD COLUMN favorite_count INTEGER DEFAULT 0`);
  } catch (e) { /* column likely already exists */ }

  // ─── 训练 skill 用户隔离字段迁移 ───
  try {
    db.exec(`ALTER TABLE skills ADD COLUMN user_id TEXT DEFAULT NULL`);
  } catch (e) { /* column likely already exists */ }
  try {
    db.exec(`ALTER TABLE skills ADD COLUMN storage_type TEXT DEFAULT 'marketplace'`);
  } catch (e) { /* column likely already exists */ }

  return db;
}


// ═══════════════════════════════════════════════════
//  Helper: JSON ↔ Row 转换
// ═══════════════════════════════════════════════════

/** 将 JSON skill 对象转为 DB 行格式 */
function skillToRow(skill) {
  return {
    skill_id: skill.skillId,
    skill_name: skill.skillName,
    directory: skill.directory || null,
    description: skill.description || '',
    domain: skill.domain || '',
    scope: skill.scope || '',
    status: skill.status || 'training',
    version: skill.version || '1.0.0',
    benchmark_version: skill.benchmarkVersion || '1.0.0',
    industry_avg_score: skill.industryAvgScore ?? null,
    elite_avg_score: skill.eliteAvgScore ?? null,
    current_score: skill.currentScore ?? null,
    use_count: skill.useCount || 0,
    download_count: skill.downloadCount || 0,
    update_count: skill.updateCount || 0,
    last_used: skill.lastUsed || null,
    last_updated: skill.lastUpdated || new Date().toISOString(),
    created_at: skill.createdAt || new Date().toISOString(),
    keywords: JSON.stringify(skill.keywords || []),
    type: skill.type || 'executor',
    agent_name: skill.agentName || null,
    layer: skill.layer ?? null,
    dependencies: JSON.stringify(skill.dependencies || []),
    scene: skill.scene || '',
    author: skill.author || 'ArenaSK',
    license: skill.license || 'MIT',
    source: skill.source || 'local',
    tags: JSON.stringify(skill.tags || []),
    roles: JSON.stringify(skill.roles || []),
    scenes: JSON.stringify(skill.scenes || []),
    platforms: JSON.stringify(skill.platforms || []),
    user_id: skill.userId || null,
    storage_type: skill.storageType || 'marketplace',
  };
}

/** 将 DB 行转回 JSON skill 对象（兼容旧接口） */
function rowToSkill(row) {
  if (!row) return null;
  const skill = {
    skillId: row.skill_id,
    skillName: row.skill_name,
    directory: row.directory,
    description: row.description,
    domain: row.domain,
    scope: row.scope,
    status: row.status,
    version: row.version,
    benchmarkVersion: row.benchmark_version,
    industryAvgScore: row.industry_avg_score,
    eliteAvgScore: row.elite_avg_score,
    currentScore: row.current_score,
    useCount: row.use_count,
    downloadCount: row.download_count,
    updateCount: row.update_count,
    lastUsed: row.last_used,
    lastUpdated: row.last_updated,
    createdAt: row.created_at,
    keywords: safeJsonParse(row.keywords, []),
    type: row.type,
    agentName: row.agent_name,
    layer: row.layer,
    dependencies: safeJsonParse(row.dependencies, []),
    scene: row.scene,
    author: row.author,
    license: row.license,
    source: row.source,
    tags: safeJsonParse(row.tags, []),
    roles: safeJsonParse(row.roles, []),
    scenes: safeJsonParse(row.scenes, []),
    platforms: safeJsonParse(row.platforms, []),
    userId: row.user_id || null,
    storageType: row.storage_type || 'marketplace',
  };
  return skill;
}

/** 将 arena DB 行转为嵌套 JSON */
function rowToArena(row) {
  if (!row) return null;
  return {
    elo: row.elo,
    eloMargin: row.elo_margin,
    rank: row.rank,
    rankSpread: row.rank_spread,
    benchmarkRuns: row.benchmark_runs,
    lastBenchmark: row.last_benchmark,
    scores: {
      successRate: row.score_success_rate,
      accuracy: row.score_accuracy,
      speed: row.score_speed,
      cost: row.score_cost,
      robustness: row.score_robustness,
      completeness: row.score_completeness,
    },
    securityScan: {
      status: row.security_status,
      confidence: row.security_confidence,
      lastScan: row.security_last_scan,
    },
    highlighted: !!row.highlighted,
    trending: !!row.trending,
    globalRank: row.global_rank,
  };
}

function safeJsonParse(str, fallback) {
  try { return JSON.parse(str); } catch { return fallback; }
}

/** 获取完整 skill + arena */
function getFullSkill(skillName) {
  const d = getDb();
  const row = d.prepare('SELECT * FROM skills WHERE skill_name = ?').get(skillName);
  if (!row) return null;
  const skill = rowToSkill(row);
  const arenaRow = d.prepare('SELECT * FROM skill_arena WHERE skill_id = ?').get(row.skill_id);
  if (arenaRow) skill.arena = rowToArena(arenaRow);
  return skill;
}


// ═══════════════════════════════════════════════════
//  CRUD — SkillRegistry 兼容接口
// ═══════════════════════════════════════════════════

function registerSkill(meta) {
  const d = getDb();
  const existing = d.prepare('SELECT 1 FROM skills WHERE skill_name = ?').get(meta.skillName);
  if (existing) throw new Error(`Skill "${meta.skillName}" already exists`);

  const skillId = `skill-${Date.now()}`;
  const now = new Date().toISOString();
  const targetDir = meta.directory
    || (meta.skillsDir ? path.join(meta.skillsDir, meta.skillName) : getTargetDir(meta.skillName));

  // Initialize lifecycle directory structure
  try {
    initSkillDir(meta.skillName);
  } catch (e) {
    console.warn(`[skill-db] Failed to init skill dir for ${meta.skillName}:`, e.message);
  }

  const row = skillToRow({
    skillId,
    skillName: meta.skillName,
    directory: targetDir,
    description: meta.description || '',
    domain: meta.domain || '',
    scope: meta.scope || '',
    status: 'training',
    version: '1.0.0',
    benchmarkVersion: '1.0.0',
    keywords: meta.keywords || [],
    layer: meta.layer || null,
    dependencies: meta.dependencies || [],
    source: meta.source || (require('./skill-dir-manager').isCodeSkill(meta.skillName) ? 'local' : 'crawled'),
    createdAt: now,
    lastUpdated: now,
  });

  const cols = Object.keys(row);
  const placeholders = cols.map(() => '?').join(', ');
  d.prepare(`INSERT INTO skills (${cols.join(', ')}) VALUES (${placeholders})`).run(...Object.values(row));

  // Record initial version
  d.prepare(`INSERT INTO skill_versions (skill_id, version, bump_type, change_summary) VALUES (?, ?, ?, ?)`)
    .run(skillId, '1.0.0', 'initial', 'Initial registration');

  return getFullSkill(meta.skillName);
}

function updateSkill(skillName, updates) {
  const d = getDb();
  const row = d.prepare('SELECT * FROM skills WHERE skill_name = ?').get(skillName);
  if (!row) throw new Error(`Skill "${skillName}" not found`);

  // Build dynamic SET clause
  const setters = [];
  const params = [];

  // Map camelCase to snake_case for common fields
  const fieldMap = {
    directory: 'directory', description: 'description', domain: 'domain', scope: 'scope',
    status: 'status', version: 'version', benchmarkVersion: 'benchmark_version',
    industryAvgScore: 'industry_avg_score', eliteAvgScore: 'elite_avg_score',
    currentScore: 'current_score', useCount: 'use_count',
    downloadCount: 'download_count', updateCount: 'update_count',
    lastUsed: 'last_used', type: 'type', agentName: 'agent_name',
    layer: 'layer', scene: 'scene', author: 'author', license: 'license',
    source: 'source',
    userId: 'user_id',
    storageType: 'storage_type',
  };

  for (const [key, val] of Object.entries(updates)) {
    if (key === 'arena') {
      // Arena is handled separately
      upsertArena(row.skill_id, val);
      continue;
    }
    const col = fieldMap[key];
    if (col) {
      setters.push(`${col} = ?`);
      params.push(val);
    } else if (key === 'keywords' || key === 'dependencies' || key === 'tags' || key === 'roles' || key === 'scenes' || key === 'platforms') {
      const snakeKey = key; // same name in DB
      setters.push(`${snakeKey} = ?`);
      params.push(JSON.stringify(val));
    }
  }

  setters.push('last_updated = ?');
  params.push(new Date().toISOString());
  params.push(skillName);

  if (setters.length > 0) {
    d.prepare(`UPDATE skills SET ${setters.join(', ')} WHERE skill_name = ?`).run(...params);
  }

  return getFullSkill(skillName);
}

function upsertArena(skillId, arena) {
  const d = getDb();
  const existing = d.prepare('SELECT 1 FROM skill_arena WHERE skill_id = ?').get(skillId);
  const scores = arena.scores || {};
  const sec = arena.securityScan || {};

  const vals = {
    skill_id: skillId,
    elo: arena.elo ?? 1200,
    elo_margin: arena.eloMargin ?? 50,
    rank: arena.rank ?? null,
    rank_spread: arena.rankSpread ?? null,
    benchmark_runs: arena.benchmarkRuns ?? 0,
    last_benchmark: arena.lastBenchmark ?? null,
    score_success_rate: scores.successRate ?? null,
    score_accuracy: scores.accuracy ?? null,
    score_speed: scores.speed ?? null,
    score_cost: scores.cost ?? null,
    score_robustness: scores.robustness ?? null,
    score_completeness: scores.completeness ?? null,
    security_status: sec.status ?? 'pending',
    security_confidence: sec.confidence ?? null,
    security_last_scan: sec.lastScan ?? null,
    highlighted: arena.highlighted ? 1 : 0,
    trending: arena.trending ? 1 : 0,
    global_rank: arena.globalRank ?? null,
  };

  if (existing) {
    const sets = Object.keys(vals).filter(k => k !== 'skill_id').map(k => `${k} = ?`);
    const params = Object.keys(vals).filter(k => k !== 'skill_id').map(k => vals[k]);
    params.push(skillId);
    d.prepare(`UPDATE skill_arena SET ${sets.join(', ')} WHERE skill_id = ?`).run(...params);
  } else {
    const cols = Object.keys(vals);
    const placeholders = cols.map(() => '?').join(', ');
    d.prepare(`INSERT INTO skill_arena (${cols.join(', ')}) VALUES (${placeholders})`).run(...Object.values(vals));
  }
}

function renameSkill(oldName, newName, skillsDir) {
  if (!newName || !newName.trim()) throw new Error('新名称不能为空');
  newName = newName.trim().replace(/\s+/g, '_');
  if (oldName === newName) return getFullSkill(oldName);

  const d = getDb();
  const existing = d.prepare('SELECT 1 FROM skills WHERE skill_name = ?').get(newName);
  if (existing) throw new Error(`Skill "${newName}" 已存在`);

  // Rename directory (use resolveSkillDir to find actual location)
  const oldDir = skillsDir ? path.join(skillsDir, oldName) : resolveSkillDir(oldName);
  if (oldDir && fs.existsSync(oldDir)) {
    const newDir = path.join(path.dirname(oldDir), newName);
    fs.renameSync(oldDir, newDir);
  }

  d.prepare('UPDATE skills SET skill_name = ?, last_updated = ? WHERE skill_name = ?')
    .run(newName, new Date().toISOString(), oldName);

  return getFullSkill(newName);
}

function getSkill(skillName) {
  return getFullSkill(skillName);
}

function getSkillById(skillId) {
  const d = getDb();
  const row = d.prepare('SELECT * FROM skills WHERE skill_id = ?').get(skillId);
  if (!row) return null;
  const skill = rowToSkill(row);
  const arenaRow = d.prepare('SELECT * FROM skill_arena WHERE skill_id = ?').get(skillId);
  if (arenaRow) skill.arena = rowToArena(arenaRow);
  return skill;
}

function listSkills({ limit, offset = 0, status, domain, layer, storageType, orderBy = 'created_at', order = 'DESC' } = {}) {
  const d = getDb();

  // Build dynamic WHERE clause
  const conditions = [];
  const params = [];
  if (status) { conditions.push('s.status = ?'); params.push(status); }
  if (domain) { conditions.push('s.domain = ?'); params.push(domain); }
  if (layer != null) { conditions.push('s.layer = ?'); params.push(layer); }
  if (storageType === 'marketplace') { conditions.push("(s.storage_type IS NULL OR s.storage_type = 'marketplace')"); }
  else if (storageType) { conditions.push('s.storage_type = ?'); params.push(storageType); }

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  // Validate orderBy to prevent injection
  const allowedOrderBy = ['created_at', 'skill_name', 'use_count', 'download_count', 'current_score', 'last_updated'];
  const safeOrderBy = allowedOrderBy.includes(orderBy) ? orderBy : 'created_at';
  const safeOrder = order === 'ASC' ? 'ASC' : 'DESC';

  let sql = `SELECT s.*, a.elo, a.rank, a.benchmark_runs, a.highlighted, a.trending,
             a.score_success_rate, a.score_accuracy, a.score_speed, a.score_cost,
             a.score_robustness, a.score_completeness, a.global_rank, a.elo_margin,
             a.rank_spread, a.last_benchmark, a.security_status, a.security_confidence,
             a.security_last_scan
             FROM skills s LEFT JOIN skill_arena a ON s.skill_id = a.skill_id
             ${where}
             ORDER BY s.${safeOrderBy} ${safeOrder}`;

  if (limit != null) {
    sql += ' LIMIT ? OFFSET ?';
    params.push(limit, offset);
  }

  const rows = d.prepare(sql).all(...params);
  return rows.map(row => {
    const skill = rowToSkill(row);
    // Arena data is joined, build arena object if present
    if (row.elo != null) {
      skill.arena = {
        elo: row.elo,
        eloMargin: row.elo_margin,
        rank: row.rank,
        rankSpread: row.rank_spread,
        benchmarkRuns: row.benchmark_runs,
        lastBenchmark: row.last_benchmark,
        scores: {
          successRate: row.score_success_rate,
          accuracy: row.score_accuracy,
          speed: row.score_speed,
          cost: row.score_cost,
          robustness: row.score_robustness,
          completeness: row.score_completeness,
        },
        securityScan: {
          status: row.security_status,
          confidence: row.security_confidence,
          lastScan: row.security_last_scan,
        },
        highlighted: !!row.highlighted,
        trending: !!row.trending,
        globalRank: row.global_rank,
      };
    }
    return skill;
  });
}

/** 获取 skill 总数（用于分页） */
function countSkills({ status, domain, layer, storageType } = {}) {
  const d = getDb();
  const conditions = [];
  const params = [];
  if (status) { conditions.push('status = ?'); params.push(status); }
  if (domain) { conditions.push('domain = ?'); params.push(domain); }
  if (layer != null) { conditions.push('layer = ?'); params.push(layer); }
  if (storageType === 'marketplace') { conditions.push("(storage_type IS NULL OR storage_type = 'marketplace')"); }
  else if (storageType) { conditions.push('storage_type = ?'); params.push(storageType); }
  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
  return d.prepare(`SELECT COUNT(*) as count FROM skills ${where}`).get(...params).count;
}

function removeSkill(skillName) {
  const d = getDb();
  const result = d.prepare('DELETE FROM skills WHERE skill_name = ?').run(skillName);
  return result.changes > 0;
}

function bumpVersion(skillName, type = 'minor', score = null) {
  const skill = getFullSkill(skillName);
  if (!skill) throw new Error(`Skill "${skillName}" not found`);

  const [major, minor, patch] = (skill.version || '1.0.0').split('.').map(Number);
  let newVersion;
  if (type === 'patch') {
    newVersion = `${major}.${minor}.${patch + 1}`;
  } else {
    newVersion = `${major}.${minor + 1}.0`;
  }

  const updates = { version: newVersion };
  if (score !== null) updates.currentScore = score;

  // Record version history
  const d = getDb();
  d.prepare(`INSERT INTO skill_versions (skill_id, version, previous_version, bump_type, score) VALUES (?, ?, ?, ?, ?)`)
    .run(skill.skillId, newVersion, skill.version, type, score);

  return updateSkill(skillName, updates);
}

function incrementUseCount(skillName) {
  const d = getDb();
  d.prepare('UPDATE skills SET use_count = use_count + 1, last_used = ? WHERE skill_name = ?')
    .run(new Date().toISOString(), skillName);
}

function incrementDownloadCount(skillName) {
  const d = getDb();
  d.prepare('UPDATE skills SET download_count = download_count + 1 WHERE skill_name = ?')
    .run(skillName);
}

function recordDownload(skillName, { userId, userEmail, version, ipAddress } = {}) {
  const d = getDb();
  const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!row) return;
  d.prepare(`INSERT INTO skill_downloads (skill_id, user_id, user_email, download_version, ip_address) VALUES (?, ?, ?, ?, ?)`)
    .run(row.skill_id, userId || null, userEmail || null, version || null, ipAddress || null);
  incrementDownloadCount(skillName);
}

function recordUpdate(skillName, fromVersion, toVersion) {
  const d = getDb();
  const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!row) return;

  // Upsert update history
  const existing = d.prepare('SELECT id, count FROM skill_update_history WHERE skill_id = ? AND from_version = ? AND to_version = ?')
    .get(row.skill_id, fromVersion, toVersion);

  if (existing) {
    d.prepare('UPDATE skill_update_history SET count = count + 1 WHERE id = ?').run(existing.id);
  } else {
    d.prepare('INSERT INTO skill_update_history (skill_id, from_version, to_version) VALUES (?, ?, ?)')
      .run(row.skill_id, fromVersion, toVersion);
  }

  d.prepare('UPDATE skills SET update_count = update_count + 1 WHERE skill_name = ?').run(skillName);
}


// ═══════════════════════════════════════════════════
//  Search — FTS5 + SQL 优化
// ═══════════════════════════════════════════════════

/**
 * FTS5 全文搜索，带 SQL 回退
 * @param {string} query - 搜索关键词
 * @param {object} options
 * @param {string} [options.domain] - 按 domain 过滤
 * @param {string} [options.type] - 按 type 过滤
 * @param {string} [options.status] - 按 status 过滤，默认 'enable'
 * @param {number} [options.limit] - 限制返回数量，默认 20
 * @param {number} [options.offset] - 偏移，默认 0
 */
function searchSkills(query = '', { domain, type, status = 'enable', limit = 20, offset = 0 } = {}) {
  const d = getDb();

  // 无查询词时，直接返回列表
  if (!query || !query.trim()) {
    return listSkills({ status, domain, limit, offset, orderBy: 'download_count' }).map(toPublicInfo);
  }

  // 尝试 FTS5 搜索
  try {
    return _ftsSearch(query, { domain, type, status, limit, offset });
  } catch (e) {
    // FTS5 不可用时回退到 SQL LIKE
    return _likeSearch(query, { domain, type, status, limit, offset });
  }
}

/** FTS5 全文搜索 */
function _ftsSearch(query, { domain, type, status, limit, offset }) {
  const d = getDb();

  // 给 FTS5 的查询词加 * 前缀匹配
  const ftsQuery = query.trim().split(/\s+/).map(w => `"${w}"*`).join(' ');

  const conditions = ["s.status = ?"];
  const params = [status];
  if (domain) { conditions.push('s.domain = ?'); params.push(domain); }
  if (type) { conditions.push('s.type = ?'); params.push(type); }

  const sql = `
    SELECT s.*, rank AS fts_rank
    FROM skills_fts fts
    JOIN skills s ON s.id = fts.rowid
    WHERE skills_fts MATCH ?
    AND ${conditions.join(' AND ')}
    ORDER BY fts_rank
    LIMIT ? OFFSET ?
  `;

  const rows = d.prepare(sql).all(ftsQuery, ...params, limit, offset);
  return rows.map(row => toPublicInfo(rowToSkill(row)));
}

/** SQL LIKE 回退搜索 */
function _likeSearch(query, { domain, type, status, limit, offset }) {
  const d = getDb();
  const q = `%${query.trim()}%`;

  const conditions = ['s.status = ?'];
  const params = [status];
  if (domain) { conditions.push('s.domain = ?'); params.push(domain); }
  if (type) { conditions.push('s.type = ?'); params.push(type); }

  const sql = `
    SELECT s.*,
      CASE
        WHEN s.skill_name LIKE ? THEN 10
        WHEN s.domain LIKE ? THEN 8
        WHEN s.keywords LIKE ? THEN 7
        WHEN s.description LIKE ? THEN 5
        ELSE 1
      END AS relevance
    FROM skills s
    WHERE (s.skill_name LIKE ? OR s.description LIKE ? OR s.domain LIKE ? OR s.keywords LIKE ? OR s.tags LIKE ?)
    AND ${conditions.join(' AND ')}
    ORDER BY relevance DESC, s.download_count DESC
    LIMIT ? OFFSET ?
  `;

  const rows = d.prepare(sql).all(
    q, q, q, q,   // relevance CASE
    q, q, q, q, q, // WHERE LIKE
    ...params,
    limit, offset
  );
  return rows.map(row => toPublicInfo(rowToSkill(row)));
}

/**
 * 精确 + 语义匹配查找（SQL 优化版）
 */
function findSkill(domain, keywords = [], skillName = '') {
  const d = getDb();

  // Tier 1: Exact match by name
  if (skillName) {
    const row = d.prepare("SELECT * FROM skills WHERE skill_name = ? AND status IN ('enable','training')").get(skillName);
    if (row) return { match: 'exact', skill: rowToSkill(row) };
  }

  // Tier 1: Exact keyword match
  if (keywords.length > 0) {
    const rows = d.prepare("SELECT * FROM skills WHERE status IN ('enable','training')").all();
    for (const row of rows) {
      const sk = safeJsonParse(row.keywords, []);
      const overlap = keywords.filter(k => sk.includes(k));
      if (overlap.length === keywords.length) return { match: 'exact', skill: rowToSkill(row) };
    }
  }

  // Tier 2: Domain match
  if (domain) {
    const row = d.prepare("SELECT * FROM skills WHERE domain = ? AND status IN ('enable','training') LIMIT 1").get(domain);
    if (row) return { match: 'semantic', skill: rowToSkill(row) };
  }

  // Tier 2: Partial keyword match (≥50%)
  if (keywords.length > 0) {
    const rows = d.prepare("SELECT * FROM skills WHERE status IN ('enable','training')").all();
    for (const row of rows) {
      const sk = safeJsonParse(row.keywords, []);
      const overlap = keywords.filter(k => sk.includes(k));
      if (overlap.length / keywords.length >= 0.5) return { match: 'semantic', skill: rowToSkill(row) };
    }
  }

  return { match: 'none', skill: null };
}

function toPublicInfo(skill) {
  const { directory, _searchScore, _cache, ...rest } = skill;
  return rest;
}




// ═══════════════════════════════════════════════════
//  新增查询接口
// ═══════════════════════════════════════════════════

/** 获取 skill 的版本历史 */
function getVersionHistory(skillName) {
  const d = getDb();
  const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!row) return [];
  return d.prepare('SELECT * FROM skill_versions WHERE skill_id = ? ORDER BY created_at DESC').all(row.skill_id);
}

/** 获取 skill 的下载记录 */
function getDownloadHistory(skillName, { limit = 50 } = {}) {
  const d = getDb();
  const row = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!row) return [];
  return d.prepare('SELECT * FROM skill_downloads WHERE skill_id = ? ORDER BY created_at DESC LIMIT ?').all(row.skill_id, limit);
}



/** 获取统计信息 */
function getStats() {
  const d = getDb();
  const total = d.prepare('SELECT COUNT(*) as count FROM skills').get().count;
  const enabled = d.prepare("SELECT COUNT(*) as count FROM skills WHERE status = 'enable'").get().count;
  const byDomain = d.prepare('SELECT domain, COUNT(*) as count FROM skills GROUP BY domain ORDER BY count DESC').all();
  const byType = d.prepare('SELECT type, COUNT(*) as count FROM skills GROUP BY type ORDER BY count DESC').all();
  const totalDownloads = d.prepare('SELECT COALESCE(SUM(download_count), 0) as total FROM skills').get().total;
  const totalUses = d.prepare('SELECT COALESCE(SUM(use_count), 0) as total FROM skills').get().total;
  return { total, enabled, byDomain, byType, totalDownloads, totalUses };
}


// ═══════════════════════════════════════════════════
//  收藏 (Favorites) 功能
// ═══════════════════════════════════════════════════

function addFavorite(skillName, userId) {
  const d = getDb();
  const skill = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!skill) throw new Error(`Skill "${skillName}" not found`);

  d.prepare('INSERT OR IGNORE INTO skill_favorites (skill_id, user_id) VALUES (?, ?)').run(skill.skill_id, userId);
  d.prepare('UPDATE skills SET favorite_count = (SELECT COUNT(*) FROM skill_favorites WHERE skill_id = ?) WHERE skill_id = ?').run(skill.skill_id, skill.skill_id);

  return { skillName, userId, favorited: true };
}

function removeFavorite(skillName, userId) {
  const d = getDb();
  const skill = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!skill) throw new Error(`Skill "${skillName}" not found`);

  d.prepare('DELETE FROM skill_favorites WHERE skill_id = ? AND user_id = ?').run(skill.skill_id, userId);
  d.prepare('UPDATE skills SET favorite_count = (SELECT COUNT(*) FROM skill_favorites WHERE skill_id = ?) WHERE skill_id = ?').run(skill.skill_id, skill.skill_id);

  return { skillName, userId, favorited: false };
}

function isFavorited(skillName, userId) {
  const d = getDb();
  const skill = d.prepare('SELECT skill_id FROM skills WHERE skill_name = ?').get(skillName);
  if (!skill) return false;
  const row = d.prepare('SELECT 1 FROM skill_favorites WHERE skill_id = ? AND user_id = ?').get(skill.skill_id, userId);
  return !!row;
}

function getUserFavorites(userId, { limit = 50, offset = 0 } = {}) {
  const d = getDb();
  const rows = d.prepare(`
    SELECT s.*, f.created_at as favorited_at
    FROM skill_favorites f
    JOIN skills s ON s.skill_id = f.skill_id
    WHERE f.user_id = ?
    ORDER BY f.created_at DESC
    LIMIT ? OFFSET ?
  `).all(userId, limit, offset);
  return rows.map(row => ({ ...rowToSkill(row), favoritedAt: row.favorited_at }));
}

function getFavoriteCount(skillName) {
  const d = getDb();
  const row = d.prepare('SELECT favorite_count FROM skills WHERE skill_name = ?').get(skillName);
  return row?.favorite_count || 0;
}

function getUserDownloads(userId, { limit = 50, offset = 0 } = {}) {
  const d = getDb();
  const rows = d.prepare(`
    SELECT s.*, dl.created_at as downloaded_at, dl.download_version
    FROM skill_downloads dl
    JOIN skills s ON s.skill_id = dl.skill_id
    WHERE dl.user_id = ?
    ORDER BY dl.created_at DESC
    LIMIT ? OFFSET ?
  `).all(userId, limit, offset);
  return rows.map(row => ({ ...rowToSkill(row), downloadedAt: row.downloaded_at, downloadVersion: row.download_version }));
}

function getUserTrainedSkills(userId, { limit = 50, offset = 0 } = {}) {
  const d = getDb();
  // 优先基于 user_id 字段查询 (v6.0)
  const byUserId = d.prepare(`
    SELECT * FROM skills WHERE user_id = ? AND storage_type = 'trained'
    ORDER BY last_updated DESC LIMIT ? OFFSET ?
  `).all(String(userId), limit, offset);
  if (byUserId.length > 0) {
    return byUserId.map(row => ({ ...rowToSkill(row), trainedAt: row.last_updated, trainScore: row.current_score }));
  }
  // 回退: 从 evolve_runs 查 (兼容旧数据)
  const rows = d.prepare(`
    SELECT s.*, e.completed_at as trained_at, e.score, e.status as run_status
    FROM skill_evolve_runs e
    JOIN skills s ON s.skill_id = e.skill_id
    WHERE e.status = 'completed'
    ORDER BY e.completed_at DESC
    LIMIT ? OFFSET ?
  `).all(limit, offset);
  return rows.map(row => ({ ...rowToSkill(row), trainedAt: row.trained_at, trainScore: row.score }));
}


module.exports = {
  getDb,
  // SkillRegistry 兼容
  registerSkill,
  updateSkill,
  renameSkill,
  getSkill,
  getSkillById,
  listSkills,
  countSkills,
  removeSkill,
  bumpVersion,
  incrementUseCount,
  incrementDownloadCount,
  recordUpdate,
  searchSkills,
  findSkill,
  toPublicInfo,
  // 新增
  recordDownload,
  upsertArena,
  getVersionHistory,
  getDownloadHistory,
  getStats,
  // 收藏
  addFavorite,
  removeFavorite,
  isFavorited,
  getUserFavorites,
  getFavoriteCount,
  getUserDownloads,
  getUserTrainedSkills,
  // 内部工具
  skillToRow,
  rowToSkill,
  rowToArena,
};