宜博 / lemonboss

#!/usr/bin/env node

/**
 * import-crawled-skills.js — 将 data/skills/ 目录下爬取的 ClawHub Skills 批量导入 SQLite
 *
 * 数据源: /Users/yibo/Documents/LemonAI/data/skills/<skill-slug>/details.json
 * 目标:   skills.sqlite (通过 skill-db.js 的 getDb() 初始化)
 *
 * 用法:
 *   node scripts/import-crawled-skills.js                     # 导入全部
 *   node scripts/import-crawled-skills.js --dry-run            # 预览，不实际写入
 *   node scripts/import-crawled-skills.js --batch-size=500     # 自定义批次大小
 *   node scripts/import-crawled-skills.js --force              # 强制覆盖已存在的 skill
 */

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

// ── 路径与参数 ──

// crawled data lives outside the project
const CRAWLED_SKILLS_DIR = path.resolve(__dirname, '../../data/skills');
// DB lives in the project's data/ dir (alongside skill-db.js)
const PROJECT_ROOT = path.resolve(__dirname, '..');
const DATA_DIR = path.resolve(PROJECT_ROOT, '../data');
const DB_PATH = path.join(DATA_DIR, 'skills.sqlite');

const args = process.argv.slice(2);
const DRY_RUN = args.includes('--dry-run');
const FORCE = args.includes('--force');
const BATCH_SIZE = (() => {
  const bsArg = args.find(a => a.startsWith('--batch-size='));
  return bsArg ? parseInt(bsArg.split('=')[1], 10) : 1000;
})();

// ── DB 初始化（直接复用 skill-db 的 schema） ──

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

  // 先 require skill-db 来确保 schema 已创建
  const skillDb = require('../server/services/skill-db');
  const db = skillDb.getDb();
  return db;
}

// ── details.json → DB row 映射 ──

function parseDetailsToRow(dirName, details) {
  const skill = details.skill || {};
  const owner = details.owner || {};
  const latestVersion = details.latestVersion || {};
  const stats = skill.stats || {};
  const llmAnalysis = latestVersion.llmAnalysis || {};
  const moderationInfo = details.moderationInfo || {};

  const slug = details.resolvedSlug || details.requestedSlug || dirName;
  const skillId = skill._id || `crawled-${slug}`;
  const displayName = skill.displayName || slug;
  const summary = skill.summary || '';
  const version = latestVersion.version || '1.0.0';
  const changelog = latestVersion.changelog || '';

  // 安全状态映射
  let securityStatus = 'pending';
  if (moderationInfo.verdict === 'clean' || moderationInfo.verdict === 'safe') {
    securityStatus = 'clean';
  } else if (moderationInfo.isMalwareBlocked) {
    securityStatus = 'blocked';
  } else if (moderationInfo.isSuspicious) {
    securityStatus = 'suspicious';
  } else if (moderationInfo.verdict) {
    securityStatus = moderationInfo.verdict;
  }

  // skill status
  let status = 'enable';
  if (moderationInfo.isRemoved) status = 'removed';
  else if (moderationInfo.isMalwareBlocked) status = 'blocked';
  else if (moderationInfo.isHiddenByMod) status = 'hidden';

  // 时间戳转换
  const createdAt = skill.createdAt
    ? new Date(skill.createdAt).toISOString()
    : new Date().toISOString();
  const updatedAt = skill.updatedAt
    ? new Date(skill.updatedAt).toISOString()
    : createdAt;

  // tags 提取 (ClawHub tags 是 { latest: versionId } 格式，不是数组)
  // We'll use llmAnalysis dimensions as tags instead
  const tags = [];
  if (llmAnalysis.verdict) tags.push(llmAnalysis.verdict);
  if (llmAnalysis.confidence) tags.push(`confidence:${llmAnalysis.confidence}`);

  return {
    skill_id: skillId,
    skill_name: slug,
    directory: path.join(CRAWLED_SKILLS_DIR, dirName),
    description: summary,
    domain: '',
    scope: '',
    status,
    version,
    benchmark_version: '1.0.0',
    industry_avg_score: null,
    elite_avg_score: null,
    current_score: null,
    use_count: stats.installsCurrent || 0,
    download_count: stats.downloads || 0,
    update_count: stats.versions || 1,
    last_used: null,
    last_updated: updatedAt,
    created_at: createdAt,
    keywords: '[]',
    type: 'executor',
    agent_name: owner.displayName || owner.handle || null,
    layer: null,
    dependencies: '[]',
    scene: '',
    author: owner.displayName || owner.handle || 'unknown',
    license: (latestVersion.parsed && latestVersion.parsed.license) || 'MIT',
    source: 'clawhub',
    tags: JSON.stringify(tags),
    roles: '[]',
    scenes: '[]',
    platforms: '[]',
  };
}

function parseDetailsToArena(skillId, details) {
  const latestVersion = details.latestVersion || {};
  const llmAnalysis = latestVersion.llmAnalysis || {};
  const moderationInfo = details.moderationInfo || {};
  const stats = (details.skill || {}).stats || {};

  return {
    skill_id: skillId,
    elo: 1200,
    elo_margin: 50,
    rank: null,
    rank_spread: null,
    benchmark_runs: 0,
    last_benchmark: null,
    score_success_rate: null,
    score_accuracy: null,
    score_speed: null,
    score_cost: null,
    score_robustness: null,
    score_completeness: null,
    security_status: moderationInfo.verdict || 'pending',
    security_confidence: llmAnalysis.confidence === 'high' ? 0.9
      : llmAnalysis.confidence === 'medium' ? 0.6
      : llmAnalysis.confidence === 'low' ? 0.3
      : null,
    security_last_scan: moderationInfo.updatedAt
      ? new Date(moderationInfo.updatedAt).toISOString()
      : null,
    highlighted: 0,
    trending: stats.downloads > 1000 ? 1 : 0,
    global_rank: null,
  };
}

function parseVersions(skillId, versionsData) {
  if (!versionsData || !versionsData.versions) return [];
  return versionsData.versions.map((v, idx, arr) => ({
    skill_id: skillId,
    version: v.version || '1.0.0',
    previous_version: idx < arr.length - 1 ? (arr[idx + 1].version || null) : null,
    bump_type: idx === arr.length - 1 ? 'initial' : 'minor',
    score: null,
    change_summary: v.changelog || '',
    created_at: v.createdAt ? new Date(v.createdAt).toISOString() : new Date().toISOString(),
  }));
}

// ── 主流程 ──

function main() {
  console.log('╔════════════════════════════════════════════════════╗');
  console.log('║  ClawHub Skills → SQLite 批量导入                  ║');
  console.log('╚════════════════════════════════════════════════════╝');
  console.log();

  if (DRY_RUN) console.log('⚠️  DRY RUN 模式 — 不会实际写入数据库\n');
  if (FORCE) console.log('⚠️  FORCE 模式 — 已存在的 skill 将被覆盖更新\n');

  // 检查源目录
  if (!fs.existsSync(CRAWLED_SKILLS_DIR)) {
    console.error(`❌ 源目录不存在: ${CRAWLED_SKILLS_DIR}`);
    process.exit(1);
  }

  // 读取所有子目录
  const allDirs = fs.readdirSync(CRAWLED_SKILLS_DIR, { withFileTypes: true })
    .filter(d => d.isDirectory())
    .map(d => d.name);

  console.log(`📁 发现 ${allDirs.length} 个 skill 目录`);

  // 初始化 DB
  const db = initDb();
  console.log(`💾 数据库: ${DB_PATH}`);

  // 获取已有的 skill_name 集合
  const existingNames = new Set(
    db.prepare('SELECT skill_name FROM skills').all().map(r => r.skill_name)
  );
  console.log(`📊 数据库中已有 ${existingNames.size} 个 skill\n`);

  // 准备 SQL 语句
  const skillCols = [
    'skill_id', 'skill_name', 'directory', 'description', 'domain', 'scope',
    'status', 'version', 'benchmark_version', 'industry_avg_score', 'elite_avg_score',
    'current_score', 'use_count', 'download_count', 'update_count', 'last_used',
    'last_updated', 'created_at', 'keywords', 'type', 'agent_name', 'layer',
    'dependencies', 'scene', 'author', 'license', 'source', 'tags',
    'roles', 'scenes', 'platforms',
  ];
  const skillPlaceholders = skillCols.map(() => '?').join(', ');
  const insertSkillSql = `INSERT INTO skills (${skillCols.join(', ')}) VALUES (${skillPlaceholders})`;

  // UPSERT: ON CONFLICT(skill_name) DO UPDATE
  const updateSets = skillCols
    .filter(c => c !== 'skill_name') // Don't update the unique key
    .map(c => `${c} = excluded.${c}`)
    .join(', ');
  const upsertSkillSql = `INSERT INTO skills (${skillCols.join(', ')}) VALUES (${skillPlaceholders})
    ON CONFLICT(skill_name) DO UPDATE SET ${updateSets}`;

  const arenaCols = [
    'skill_id', 'elo', 'elo_margin', 'rank', 'rank_spread', 'benchmark_runs',
    'last_benchmark', 'score_success_rate', 'score_accuracy', 'score_speed',
    'score_cost', 'score_robustness', 'score_completeness', 'security_status',
    'security_confidence', 'security_last_scan', 'highlighted', 'trending', 'global_rank',
  ];
  const arenaPlaceholders = arenaCols.map(() => '?').join(', ');
  const arenaUpdateSets = arenaCols.filter(c => c !== 'skill_id').map(c => `${c} = excluded.${c}`).join(', ');
  const upsertArenaSql = `INSERT INTO skill_arena (${arenaCols.join(', ')}) VALUES (${arenaPlaceholders})
    ON CONFLICT(skill_id) DO UPDATE SET ${arenaUpdateSets}`;

  const versionCols = ['skill_id', 'version', 'previous_version', 'bump_type', 'score', 'change_summary', 'created_at'];
  const versionPlaceholders = versionCols.map(() => '?').join(', ');
  const insertVersionSql = `INSERT INTO skill_versions (${versionCols.join(', ')}) VALUES (${versionPlaceholders})`;

  const stmtUpsertSkill = db.prepare(upsertSkillSql);
  const stmtInsertSkill = db.prepare(insertSkillSql);
  const stmtUpsertArena = db.prepare(upsertArenaSql);
  const stmtInsertVersion = db.prepare(insertVersionSql);

  // 统计
  let imported = 0;
  let skipped = 0;
  let updated = 0;
  let errors = 0;
  let noDetails = 0;

  const startTime = Date.now();

  // 按批次处理
  const totalBatches = Math.ceil(allDirs.length / BATCH_SIZE);

  for (let batchIdx = 0; batchIdx < totalBatches; batchIdx++) {
    const batchStart = batchIdx * BATCH_SIZE;
    const batchEnd = Math.min(batchStart + BATCH_SIZE, allDirs.length);
    const batch = allDirs.slice(batchStart, batchEnd);

    const batchRows = [];
    const batchArena = [];
    const batchVersions = [];

    for (const dirName of batch) {
      const detailsPath = path.join(CRAWLED_SKILLS_DIR, dirName, 'details.json');

      // 没有 details.json 时，从目录名和 SKILL.md 构造基本记录
      if (!fs.existsSync(detailsPath)) {
        noDetails++;
        // 仍然尝试导入（从 SKILL.md 或目录名构造）
        if (existingNames.has(dirName) && !FORCE) { skipped++; continue; }
        let desc = '';
        const skillMdPath = path.join(CRAWLED_SKILLS_DIR, dirName, 'SKILL.md');
        if (fs.existsSync(skillMdPath)) {
          const md = fs.readFileSync(skillMdPath, 'utf8');
          // 取第一行非标题的文本作为描述
          const lines = md.split('\n').filter(l => l.trim() && !l.startsWith('#'));
          desc = (lines[0] || '').trim().slice(0, 500);
        }
        const fallbackRow = {
          skill_id: `skill-local-${dirName}`,
          skill_name: dirName,
          directory: path.join(CRAWLED_SKILLS_DIR, dirName),
          description: desc,
          domain: '', scope: '', status: 'enable', version: '1.0.0',
          benchmark_version: '1.0.0', industry_avg_score: null, elite_avg_score: null,
          current_score: null, use_count: 0, download_count: 0, update_count: 0,
          last_used: null, last_updated: new Date().toISOString(),
          created_at: new Date().toISOString(), keywords: '[]', type: 'executor',
          agent_name: null, layer: null, dependencies: '[]', scene: '',
          author: 'local', license: 'MIT', source: 'local', tags: '[]',
          roles: '[]', scenes: '[]', platforms: '[]',
        };
        batchRows.push({ row: fallbackRow, isUpdate: existingNames.has(dirName) });
        batchArena.push(parseDetailsToArena(fallbackRow.skill_id, {}));
        continue;
      }

      try {
        const raw = fs.readFileSync(detailsPath, 'utf-8');
        const details = JSON.parse(raw);

        const row = parseDetailsToRow(dirName, details);

        // 跳过已存在（非 force 模式）
        if (existingNames.has(row.skill_name) && !FORCE) {
          skipped++;
          continue;
        }

        batchRows.push({ row, isUpdate: existingNames.has(row.skill_name) });

        // Arena 数据
        const arena = parseDetailsToArena(row.skill_id, details);
        batchArena.push(arena);

        // 版本数据
        const versionsPath = path.join(CRAWLED_SKILLS_DIR, dirName, 'versions.json');
        if (fs.existsSync(versionsPath)) {
          try {
            const versionsData = JSON.parse(fs.readFileSync(versionsPath, 'utf-8'));
            const versions = parseVersions(row.skill_id, versionsData);
            batchVersions.push(...versions);
          } catch (e) { /* ignore version parse errors */ }
        }

      } catch (e) {
        errors++;
        if (errors <= 10) {
          console.error(`  ❌ ${dirName}: ${e.message}`);
        }
      }
    }

    // 批量写入 (使用事务)
    if (!DRY_RUN && batchRows.length > 0) {
      const transaction = db.transaction(() => {
        for (const { row, isUpdate } of batchRows) {
          try {
            if (FORCE) {
              stmtUpsertSkill.run(...skillCols.map(c => row[c]));
            } else {
              stmtInsertSkill.run(...skillCols.map(c => row[c]));
            }
            if (isUpdate) updated++;
            else imported++;
            existingNames.add(row.skill_name);
          } catch (e) {
            errors++;
            if (errors <= 10) {
              console.error(`  ❌ INSERT ${row.skill_name}: ${e.message}`);
            }
          }
        }

        for (const arena of batchArena) {
          try {
            stmtUpsertArena.run(...arenaCols.map(c => arena[c]));
          } catch (e) { /* ignore arena errors silently */ }
        }

        // 只在首次导入时写版本历史（避免重复）
        if (!FORCE) {
          for (const ver of batchVersions) {
            try {
              stmtInsertVersion.run(...versionCols.map(c => ver[c]));
            } catch (e) { /* ignore version insert errors */ }
          }
        }
      });

      transaction();
    } else if (DRY_RUN) {
      imported += batchRows.filter(b => !b.isUpdate).length;
      updated += batchRows.filter(b => b.isUpdate).length;
    }

    // 进度
    const progress = ((batchEnd / allDirs.length) * 100).toFixed(1);
    const elapsed = ((Date.now() - startTime) / 1000).toFixed(1);
    const speed = (batchEnd / ((Date.now() - startTime) / 1000)).toFixed(0);
    process.stdout.write(
      `\r  📦 批次 ${batchIdx + 1}/${totalBatches} | ` +
      `进度 ${progress}% | ` +
      `已处理 ${batchEnd}/${allDirs.length} | ` +
      `用时 ${elapsed}s | ` +
      `速度 ${speed}/s`
    );
  }

  console.log('\n');

  // ── 结果汇总 ──
  const totalTime = ((Date.now() - startTime) / 1000).toFixed(2);

  console.log('┌─────────────────────────────────────────┐');
  console.log('│           导入结果                       │');
  console.log('├─────────────────────────────────────────┤');
  console.log(`│  ✅ 新增导入:   ${String(imported).padStart(8)}              │`);
  console.log(`│  🔄 覆盖更新:   ${String(updated).padStart(8)}              │`);
  console.log(`│  ⏭️  跳过已存在: ${String(skipped).padStart(8)}              │`);
  console.log(`│  📭 无 details: ${String(noDetails).padStart(8)}              │`);
  console.log(`│  ❌ 错误:       ${String(errors).padStart(8)}              │`);
  console.log(`│  ⏱️  总耗时:     ${String(totalTime + 's').padStart(8)}              │`);
  console.log('└─────────────────────────────────────────┘');

  // DB 总数
  if (!DRY_RUN) {
    const totalInDb = db.prepare('SELECT COUNT(*) as c FROM skills').get().c;
    const arenaCount = db.prepare('SELECT COUNT(*) as c FROM skill_arena').get().c;
    const versionCount = db.prepare('SELECT COUNT(*) as c FROM skill_versions').get().c;
    console.log(`\n📊 数据库统计:`);
    console.log(`   skills 表:         ${totalInDb} 条`);
    console.log(`   skill_arena 表:    ${arenaCount} 条`);
    console.log(`   skill_versions 表: ${versionCount} 条`);

    // 重建 FTS 索引
    console.log('\n🔍 重建 FTS5 全文搜索索引...');
    try {
      db.exec("INSERT INTO skills_fts(skills_fts) VALUES('rebuild');");
      const ftsCount = db.prepare('SELECT COUNT(*) as c FROM skills_fts').get().c;
      console.log(`   FTS5 索引: ${ftsCount} 条`);
    } catch (e) {
      console.warn(`   FTS5 重建失败: ${e.message}`);
    }
  }

  console.log('\n✨ 完成!');
}

main();