#!/usr/bin/env node
/**
 * sync-skills-to-sqlite.js
 *
 * 以 skills/skill_list.json 为权威数据源，全量同步到 data/skills.sqlite
 *
 * 策略：
 *   1. JSON 中有、SQLite 中没有 → INSERT
 *   2. JSON 中有、SQLite 中也有 → UPDATE 所有字段（以 skill_id 匹配，fallback skill_name）
 *   3. SQLite 中有、JSON 中没有 → DELETE（可选，默认开启）
 *   4. Arena 数据也一并同步
 *
 * 用法: node server/scripts/sync-skills-to-sqlite.js [--dry-run] [--keep-orphans]
 *   --dry-run       只打印变更，不实际写入
 *   --keep-orphans   不删除 SQLite 中多余的 skill
 */

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

const SKILLS_DIR = path.resolve(__dirname, '../../skills');
const JSON_PATH = path.join(SKILLS_DIR, 'skill_list.json');

const skillDb = require('../services/skill-db');

function main() {
  const args = process.argv.slice(2);
  const dryRun = args.includes('--dry-run');
  const keepOrphans = args.includes('--keep-orphans');

  console.log('═══════════════════════════════════════════');
  console.log('  Skills JSON → SQLite Full Sync');
  console.log(`  Mode: ${dryRun ? '🔍 DRY-RUN' : '🚀 LIVE'}`);
  console.log(`  Orphans: ${keepOrphans ? '保留' : '删除'}`);
  console.log('═══════════════════════════════════════════');
  console.log('');

  // 1. 读取 JSON
  if (!fs.existsSync(JSON_PATH)) {
    console.error(`❌ skill_list.json not found at ${JSON_PATH}`);
    process.exit(1);
  }

  const raw = JSON.parse(fs.readFileSync(JSON_PATH, 'utf-8'));
  const jsonSkills = Array.isArray(raw) ? raw : (raw.skills || []);
  console.log(`📄 JSON 中共 ${jsonSkills.length} 个 skills`);

  // 2. 获取 SQLite 现有数据
  const db = skillDb.getDb();
  const dbRows = db.prepare('SELECT skill_id, skill_name FROM skills').all();
  const dbSkillIdSet = new Set(dbRows.map(r => r.skill_id));
  const dbSkillNameSet = new Set(dbRows.map(r => r.skill_name));
  console.log(`📊 SQLite 中共 ${dbRows.length} 个 skills`);
  console.log('');

  let inserted = 0;
  let updated = 0;
  let deleted = 0;
  let arenasynced = 0;
  let errors = 0;

  // 构建 JSON 中 skill_id 和 skill_name 集合
  const jsonSkillIdSet = new Set(jsonSkills.map(s => s.skillId));
  const jsonSkillNameSet = new Set(jsonSkills.map(s => s.skillName));

  // 3. 遍历 JSON，INSERT 或 UPDATE
  for (const skill of jsonSkills) {
    try {
      const existsById = dbSkillIdSet.has(skill.skillId);
      const existsByName = dbSkillNameSet.has(skill.skillName);

      if (existsById || existsByName) {
        // ─── UPDATE ───
        if (dryRun) {
          console.log(`  🔄 UPDATE: ${skill.skillName} (${skill.skillId})`);
        } else {
          const row = skillDb.skillToRow(skill);
          // 构建 SET 子句（排除 skill_id，它是主键）
          const updateCols = Object.keys(row).filter(k => k !== 'skill_id');
          const sets = updateCols.map(k => `${k} = ?`).join(', ');
          const params = updateCols.map(k => row[k]);

          if (existsById) {
            params.push(skill.skillId);
            db.prepare(`UPDATE skills SET ${sets} WHERE skill_id = ?`).run(...params);
          } else {
            // skill_name 匹配但 skill_id 不同，更新 skill_id 并更新所有字段
            params.push(skill.skillName);
            db.prepare(`UPDATE skills SET skill_id = ?, ${sets} WHERE skill_name = ?`)
              .run(skill.skillId, ...params);
          }
        }
        updated++;
      } else {
        // ─── INSERT ───
        if (dryRun) {
          console.log(`  ➕ INSERT: ${skill.skillName} (${skill.skillId})`);
        } else {
          const row = skillDb.skillToRow(skill);
          const cols = Object.keys(row);
          const placeholders = cols.map(() => '?').join(', ');
          db.prepare(`INSERT INTO skills (${cols.join(', ')}) VALUES (${placeholders})`)
            .run(...Object.values(row));

          // 记录初始版本
          db.prepare(`INSERT OR IGNORE INTO skill_versions (skill_id, version, bump_type, change_summary) VALUES (?, ?, ?, ?)`)
            .run(skill.skillId, skill.version || '1.0.0', 'sync', `Synced from skill_list.json`);
        }
        inserted++;
      }

      // ─── Arena 同步 ───
      if (skill.arena && !dryRun) {
        // 需要先确保 skill_id 在 skills 表中存在
        skillDb.upsertArena(skill.skillId, skill.arena);
        arenasynced++;
      } else if (skill.arena && dryRun) {
        arenasynced++;
      }
    } catch (err) {
      console.error(`  ❌ ${skill.skillName}: ${err.message}`);
      errors++;
    }
  }

  // 4. 删除 SQLite 中有但 JSON 中没有的 skill（孤儿）
  if (!keepOrphans) {
    for (const dbRow of dbRows) {
      if (!jsonSkillIdSet.has(dbRow.skill_id) && !jsonSkillNameSet.has(dbRow.skill_name)) {
        if (dryRun) {
          console.log(`  🗑️  DELETE: ${dbRow.skill_name} (${dbRow.skill_id})`);
        } else {
          // 先删 arena（外键 CASCADE 应该自动处理，但显式删除更安全）
          db.prepare('DELETE FROM skill_arena WHERE skill_id = ?').run(dbRow.skill_id);
          db.prepare('DELETE FROM skill_versions WHERE skill_id = ?').run(dbRow.skill_id);
          db.prepare('DELETE FROM skill_downloads WHERE skill_id = ?').run(dbRow.skill_id);
          db.prepare('DELETE FROM skill_update_history WHERE skill_id = ?').run(dbRow.skill_id);
          db.prepare('DELETE FROM skills WHERE skill_id = ?').run(dbRow.skill_id);
        }
        deleted++;
      }
    }
  }

  // 5. 结果报告
  console.log('');
  console.log('─── Results ───');
  console.log(`  ➕ Inserted:     ${inserted}`);
  console.log(`  🔄 Updated:      ${updated}`);
  console.log(`  🗑️  Deleted:      ${deleted}`);
  console.log(`  🏟️  Arena synced: ${arenasynced}`);
  console.log(`  ❌ Errors:       ${errors}`);
  console.log('');

  // 6. 验证
  if (!dryRun) {
    const finalCount = 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('─── Verification ───');
    console.log(`  Skills:    ${finalCount} (JSON: ${jsonSkills.length})`);
    console.log(`  Arena:     ${arenaCount}`);
    console.log(`  Versions:  ${versionCount}`);
    console.log('');

    if (finalCount === jsonSkills.length) {
      console.log('✅ Sync complete! SQLite is now in sync with skill_list.json.');
    } else {
      console.log(`⚠️  Mismatch: expected ${jsonSkills.length}, got ${finalCount} in DB.`);
    }
  } else {
    console.log('🔍 Dry-run complete. No changes were made.');
  }
}

main();