/**
 * 批量给表单创建的技能（有 description 但没有 package_key）生成 zip 包
 * 执行：cd cf-worker && node scripts/generate-packages.cjs
 */
const { DatabaseSync } = require('node:sqlite')
const { ZipArchive } = require('archiver')
const path = require('path')
const fs = require('fs')

// ─── 配置 ───
const DB_PATH = path.join(__dirname, '../data/aipro.db')
const UPLOADS_ROOT = path.join(__dirname, '../uploads')

// ─── ID 生成（与 src/utils/id.js 一致） ───
function genId(prefix) {
  const t = Date.now().toString(36)
  const r = Math.random().toString(36).slice(2, 7)
  return `${prefix}_${t}${r}`.slice(0, prefix.length + 1 + 12)
}

// ─── 创建 zip 包（返回 Promise<size>） ───
function createZip(outputPath, fileName, content) {
  return new Promise((resolve, reject) => {
    const output = fs.createWriteStream(outputPath)
    const archive = new ZipArchive({ zlib: { level: 9 } })

    output.on('close', () => resolve(archive.pointer()))
    archive.on('error', reject)

    archive.pipe(output)
    archive.append(content, { name: fileName })
    archive.finalize()
  })
}

async function main() {
  if (!fs.existsSync(DB_PATH)) {
    console.error('数据库不存在:', DB_PATH)
    process.exit(1)
  }

  const db = new DatabaseSync(DB_PATH)
  db.exec('PRAGMA journal_mode = WAL')

  // 查询所有没有 package_key 的技能（有 description 作为内容）
  const skills = db.prepare(`
    SELECT id, name, description
    FROM skills
    WHERE (package_key IS NULL OR package_key = '')
      AND description IS NOT NULL
      AND description != ''
  `).all()

  if (skills.length === 0) {
    console.log('没有需要处理的技能')
    db.close()
    return
  }

  console.log(`找到 ${skills.length} 个需要生成 zip 包的技能`)

  // 准备 statements
  const updateStmt = db.prepare(`
    UPDATE skills
    SET package_key = ?, package_size = ?, package_name = ?, updated_at = strftime('%s','now')
    WHERE id = ?
  `)

  const insertFileStmt = db.prepare(`
    INSERT INTO files (id, r2_key, filename, size, mime, uploader_phone, uploader_email, bound_skill_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `)

  const today = new Date().toISOString().slice(0, 10)
  const dirPath = path.join(UPLOADS_ROOT, 'pkg', today)
  fs.mkdirSync(dirPath, { recursive: true })

  let processed = 0
  let errors = 0

  for (const skill of skills) {
    try {
      // 文件名：用 skill name 做 md 文件名（去除不安全字符）
      const safeName = skill.name.replace(/[^\w\u4e00-\u9fff.\-]+/g, '_')
      const mdFileName = `${safeName}.md`

      // 生成 file id 和路径
      const fid = genId('file')
      const zipFileName = `${fid}_${safeName}.zip`
      const r2Key = `pkg/${today}/${zipFileName}`
      const zipPath = path.join(dirPath, zipFileName)

      // 打包 description 为 .md 文件放进 zip
      const zipSize = await createZip(zipPath, mdFileName, skill.description)

      // 更新数据库
      updateStmt.run(r2Key, zipSize, mdFileName, skill.id)
      insertFileStmt.run(fid, r2Key, `${safeName}.zip`, zipSize, 'application/zip', null, null, skill.id)

      processed++
      console.log(`  ✓ [${processed}/${skills.length}] ${skill.name} → ${r2Key} (${zipSize} bytes)`)
    } catch (err) {
      errors++
      console.error(`  ✗ ${skill.name}: ${err.message}`)
    }
  }

  db.close()
  console.log(`\n完成！处理 ${processed} 个技能，失败 ${errors} 个`)
}

main().catch(err => {
  console.error('脚本执行失败:', err)
  process.exit(1)
})
