/**
 * 解析流程架构 Excel → cf-worker/seed/categories.json
 *
 * 规则：
 * 1. Excel 共 5 列，列即层级（C0=L1, C1=L2, ...）
 * 2. 空列继承上一行同列的值（向前填充父级）
 * 3. 节点身份 = 从根到自身的路径串
 * 4. 默认全部节点 status=active；如需归档，请在生成后的 JSON 中显式维护
 */

const path = require('path')
const fs = require('fs')
const XLSX = require('xlsx')

const XLSX_PATH = process.env.CATEGORY_XLSX || path.resolve(__dirname, '../seed/categories.xlsx')
const OUT_PATH = path.resolve(__dirname, '../seed/categories.json')

// 简单的 sha1-ish 短 ID（12 字符），保证同样的 path 每次生成同样的 id（D1 重灌幂等）
function shortId(input) {
  let h1 = 0xdeadbeef ^ 0
  let h2 = 0x41c6ce57 ^ 0
  for (let i = 0; i < input.length; i++) {
    const ch = input.charCodeAt(i)
    h1 = Math.imul(h1 ^ ch, 2654435761)
    h2 = Math.imul(h2 ^ ch, 1597334677)
  }
  h1 = Math.imul(h1 ^ (h1 >>> 16), 2246822507) ^ Math.imul(h2 ^ (h2 >>> 13), 3266489909)
  h2 = Math.imul(h2 ^ (h2 >>> 16), 2246822507) ^ Math.imul(h1 ^ (h1 >>> 13), 3266489909)
  const u32 = (4294967296 * (2097151 & h2) + (h1 >>> 0)).toString(36)
  return ('cat_' + u32).slice(0, 16)
}

// 抽取 code（节点名前缀里的编号），抽不到就空串
function extractCode(name) {
  const m = name.match(/^([\d.]+|[一二三四五六七八九十]+)\s*[\.、)]?\s*/)
  if (!m) return ''
  // 必须像 1.0 / 4.1 / 4.1.1 / 12.4.3.1 这种
  const c = m[1]
  if (/^\d/.test(c)) return c.replace(/\.$/, '')
  return ''
}

function parseExcel() {
  const wb = XLSX.readFile(XLSX_PATH)
  const ws = wb.Sheets[wb.SheetNames[0]]
  const rows = XLSX.utils.sheet_to_json(ws, { header: 1, raw: false, defval: '' })

  // 跳过表头第 0 行
  const dataRows = rows.slice(1)

  // forward-fill：每列保留最近一次非空值
  const lastByCol = ['', '', '', '', '']
  const filledRows = []
  for (const r of dataRows) {
    const cells = []
    let hasAny = false
    for (let i = 0; i < 5; i++) {
      let v = (r[i] == null ? '' : String(r[i])).trim().replace(/\s+/g, ' ')
      if (v) {
        lastByCol[i] = v
        // 当前格非空：清空它右边的"祖先记忆"，免得右侧空格继承到旧值
        for (let j = i + 1; j < 5; j++) lastByCol[j] = ''
        hasAny = true
      }
      cells.push(v)
    }
    if (!hasAny) continue
    // 把当前行用 forward-fill 后的"父级"补齐前缀（只补到本行最深列的左边）
    // 找到本行最深的非空列
    let deepest = -1
    for (let i = 4; i >= 0; i--) {
      if (cells[i]) { deepest = i; break }
    }
    if (deepest < 0) continue
    // 对 deepest 左边的空格，用 lastByCol 的值（就是当前 forward-fill 后的值）
    // 注意：因为非空列已更新 lastByCol 并清空了右侧，这里 lastByCol[0..deepest-1] 就是正确祖先
    const filled = []
    for (let i = 0; i < deepest; i++) {
      filled.push(cells[i] || lastByCol[i] || '')
    }
    filled.push(cells[deepest])
    filledRows.push(filled)
  }

  // 现在每一行 filled = [L1名, L2名, ..., 自身名]，长度 1~5
  // 用 path 字符串当 key 去重 + 建父子
  const nodesMap = new Map() // pathKey → node
  function ensureNode(namesPath) {
    const pathKey = namesPath.join(' / ')
    if (nodesMap.has(pathKey)) return nodesMap.get(pathKey)

    const name = namesPath[namesPath.length - 1]
    const level = namesPath.length
    const parentPath = namesPath.slice(0, -1)
    const parent_id = parentPath.length === 0 ? null : ensureNode(parentPath).id
    const id = shortId(pathKey)
    const code = extractCode(name)
    const node = {
      id,
      parent_id,
      code,
      name,
      level,
      path: pathKey,
      status: 'active',
      sort: nodesMap.size
    }
    nodesMap.set(pathKey, node)
    return node
  }

  for (const row of filledRows) {
    // 累积构建：[L1] → [L1,L2] → ...
    for (let i = 1; i <= row.length; i++) {
      ensureNode(row.slice(0, i))
    }
  }

  const all = Array.from(nodesMap.values())

  // 统计
  const byLevel = {}
  for (const n of all) byLevel[n.level] = (byLevel[n.level] || 0) + 1
  const archivedCount = all.filter(n => n.status === 'archived').length
  const maxLevel = Math.max(...all.map(n => n.level))
  console.log('=== seed stats ===')
  console.log('total nodes:', all.length)
  console.log('by level:', byLevel)
  console.log('max level:', maxLevel)
  console.log('archived:', archivedCount)

  // L1 列表（验收）
  const l1 = all.filter(n => n.level === 1).map(n => n.name)
  console.log('L1 列表 (' + l1.length + '):', l1)

  return all
}

function main() {
  const nodes = parseExcel()
  fs.mkdirSync(path.dirname(OUT_PATH), { recursive: true })
  fs.writeFileSync(OUT_PATH, JSON.stringify(nodes, null, 2), 'utf8')
  console.log('\n→ 输出: ' + OUT_PATH)
}

main()
