/**
 * cf-worker/seed/categories.json → seed/categories.sql
 * 生成 INSERT OR REPLACE 语句，保证多次灌库幂等
 */
const fs = require('fs')
const path = require('path')

const IN  = path.resolve(__dirname, '../seed/categories.json')
const OUT = path.resolve(__dirname, '../seed/categories.sql')

function esc(v) {
  if (v === null || v === undefined) return 'NULL'
  if (typeof v === 'number') return String(v)
  return `'${String(v).replace(/'/g, "''")}'`
}

const nodes = JSON.parse(fs.readFileSync(IN, 'utf8'))

const lines = [
  '-- AUTO-GENERATED by scripts/json-to-sql.cjs，请勿手改',
  '-- 灌库：wrangler d1 execute aipro-skill-db --remote --file=./seed/categories.sql',
  'BEGIN;',
]

// 按 level 升序插入，保证 parent 先于 child（FK 不强校验，但行为上更干净）
nodes.sort((a, b) => a.level - b.level || a.sort - b.sort)

for (const n of nodes) {
  lines.push(
    `INSERT OR REPLACE INTO categories (id, parent_id, code, name, level, path, status, sort) VALUES (` +
    [n.id, n.parent_id, n.code, n.name, n.level, n.path, n.status, n.sort].map(esc).join(', ') +
    ');'
  )
}

lines.push('COMMIT;')

fs.writeFileSync(OUT, lines.join('\n'), 'utf8')
console.log(`→ ${OUT}  (${nodes.length} rows)`)
