const crypto = require('node:crypto')
const fs = require('node:fs')
const path = require('node:path')
const { DatabaseSync } = require('node:sqlite')
const JSZip = require('jszip')

function safeName(value) {
  return String(value || 'skill').replace(/[^\w\u4e00-\u9fff.-]+/g, '_').replace(/^_+|_+$/g, '') || 'skill'
}

function deterministicFileId(skillId) {
  const digest = crypto.createHash('sha256').update(String(skillId)).digest('hex').slice(0, 12)
  return `file_bundle_${digest}`
}

function resolveInside(root, rel) {
  const rootPath = path.resolve(root)
  const target = path.resolve(rootPath, String(rel || ''))
  if (target !== rootPath && !target.startsWith(`${rootPath}${path.sep}`)) {
    throw new Error(`Skill 包路径越界：${rel}`)
  }
  return target
}

function addDirectoryToZip(zip, dir, baseDir) {
  for (const entry of fs.readdirSync(dir, { withFileTypes: true })) {
    const abs = path.join(dir, entry.name)
    if (entry.isDirectory()) addDirectoryToZip(zip, abs, baseDir)
    else if (entry.isFile()) {
      const rel = path.relative(baseDir, abs).split(path.sep).join('/')
      zip.file(rel, fs.readFileSync(abs))
    }
  }
}

async function buildPackageBuffer(packageRoot) {
  if (!fs.existsSync(path.join(packageRoot, 'SKILL.md'))) {
    throw new Error(`缺少 SKILL.md：${packageRoot}`)
  }
  const zip = new JSZip()
  addDirectoryToZip(zip, packageRoot, packageRoot)
  return zip.generateAsync({
    type: 'nodebuffer',
    compression: 'DEFLATE',
    compressionOptions: { level: 9 },
  })
}

async function importBundledSkills({ db, bundleRoot, uploadsRoot, now = new Date() }) {
  const manifestPath = path.join(bundleRoot, 'manifest.json')
  const manifest = JSON.parse(fs.readFileSync(manifestPath, 'utf8'))
  if (!Array.isArray(manifest.skills) || manifest.skills.length !== manifest.skillCount) {
    throw new Error('bundled-skills/manifest.json 数量不一致')
  }

  const category = db.prepare(`
    SELECT id, path FROM categories
    WHERE id = 'cat_15_1_2' AND status = 'active'
    LIMIT 1
  `).get() || db.prepare(`
    SELECT id, path FROM categories WHERE status = 'active' ORDER BY id LIMIT 1
  `).get()
  if (!category) throw new Error('没有可用于归档 Skill 的有效流程节点')

  const existing = db.prepare('SELECT 1 FROM skills WHERE id = ? LIMIT 1')
  const upsertSkill = db.prepare(`
    INSERT INTO skills
      (id, name, description, node_id, node_path, source, status, creator_name,
       creator_phone, creator_email, package_key, package_size, package_name,
       visibility, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, 'upload', 'published', 'AIPROS 内置 Skill 库',
            NULL, NULL, ?, ?, ?, 'public', ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      name = excluded.name,
      description = excluded.description,
      node_id = excluded.node_id,
      node_path = excluded.node_path,
      source = 'upload',
      status = 'published',
      package_key = excluded.package_key,
      package_size = excluded.package_size,
      package_name = excluded.package_name,
      visibility = 'public',
      updated_at = excluded.updated_at
  `)
  const upsertFile = db.prepare(`
    INSERT INTO files
      (id, r2_key, filename, size, mime, uploader_phone, uploader_email, bound_skill_id)
    VALUES (?, ?, ?, ?, 'application/zip', NULL, NULL, ?)
    ON CONFLICT(r2_key) DO UPDATE SET
      filename = excluded.filename,
      size = excluded.size,
      mime = 'application/zip',
      bound_skill_id = excluded.bound_skill_id
  `)

  const epochSeconds = Math.floor(now.getTime() / 1000)
  let imported = 0
  let updated = 0

  for (const skill of manifest.skills) {
    const packageRoot = resolveInside(bundleRoot, skill.packageRoot)
    const bytes = await buildPackageBuffer(packageRoot)
    const fileId = deterministicFileId(skill.id)
    const packageName = `${safeName(skill.name)}.zip`
    const packageKey = `pkg/bundled/${fileId}_${packageName}`
    const packagePath = resolveInside(uploadsRoot, packageKey)
    fs.mkdirSync(path.dirname(packagePath), { recursive: true })
    fs.writeFileSync(packagePath, bytes)

    const wasPresent = !!existing.get(skill.id)
    upsertSkill.run(
      skill.id,
      skill.name,
      skill.description || `本机 Skill：${skill.name}`,
      category.id,
      category.path,
      packageKey,
      bytes.length,
      packageName,
      epochSeconds,
      epochSeconds,
    )
    upsertFile.run(fileId, packageKey, packageName, bytes.length, skill.id)
    if (wasPresent) updated += 1
    else imported += 1
  }

  return { imported, updated, total: manifest.skills.length }
}

async function main() {
  const projectRoot = path.resolve(__dirname, '../..')
  const bundleRoot = process.env.BUNDLED_SKILLS_DIR || path.join(projectRoot, 'bundled-skills')
  if (!fs.existsSync(path.join(bundleRoot, 'manifest.json'))) {
    console.log('[bundled-skills] 未找到 bundled-skills/manifest.json，跳过内置 Skill 导入')
    return
  }
  const dbPath = process.env.DB_PATH || path.join(__dirname, '../data/aipro.db')
  const uploadsRoot = process.env.UPLOADS_DIR || path.join(__dirname, '../uploads')
  const db = new DatabaseSync(dbPath)
  db.exec('PRAGMA journal_mode = WAL')
  db.exec('PRAGMA foreign_keys = ON')
  try {
    const result = await importBundledSkills({ db, bundleRoot, uploadsRoot })
    console.log(`[bundled-skills] 导入 ${result.imported}，更新 ${result.updated}，总计 ${result.total}`)
  } finally {
    db.close()
  }
}

module.exports = { importBundledSkills }

if (require.main === module) {
  main().catch(error => {
    console.error('[bundled-skills] 导入失败：', error)
    process.exit(1)
  })
}
