import { Hono } from 'hono'
import { DB } from '../db.js'
import { genId } from '../utils/id.js'

const SUPER_ADMIN_EMAIL = '494161546@qq.com'
const SUPER_ADMIN_PHONE = '13136092523'
function isSuperAdmin(c) {
  const identity = c.get('identity') || {}
  return identity.email === SUPER_ADMIN_EMAIL || identity.phone === SUPER_ADMIN_PHONE
}

const app = new Hono()

// ─── 内存缓存（categories 数据极少变动，缓存 5 分钟，避免每次页面导航都打 DB）───
const TTL = 5 * 60 * 1000
let flatCache = null, flatCacheAt = 0
let treeCache = null, treeCacheAt = 0

function freshFlat() { return flatCache && (Date.now() - flatCacheAt) < TTL }
function freshTree() { return treeCache && (Date.now() - treeCacheAt) < TTL }

// 给运营工具用：清空缓存（理论上后台改了节点后调用，当前版本暂无 admin 接口，预留）
export function invalidateCategoriesCache() {
  flatCache = null; flatCacheAt = 0
  treeCache = null; treeCacheAt = 0
}

/**
 * GET /api/categories
 * 扁平数组：[{id, parent_id, code, name, level, path, status, sort}]
 * 默认含 archived（前端自己判断禁选 / 灰显）
 */
app.get('/', async (c) => {
  // 不让浏览器/中间代理缓存（防止冷启动瞬间失败被缓存后永久空白；DB 层还有 5 分钟内存缓存保证性能）
  c.header('Cache-Control', 'no-store')
  try {
    if (freshFlat()) return c.json({ data: flatCache })
    const { results } = await DB.prepare(
      `SELECT id, parent_id, code, name, level, path, status, sort
       FROM categories
       ORDER BY sort ASC`
    ).all()
    flatCache = results || []
    flatCacheAt = Date.now()
    return c.json({ data: flatCache })
  } catch (err) {
    console.error('[categories-list-error]', err)
    return c.json({ error: '获取分类列表失败' }, 500)
  }
})

/**
 * GET /api/categories/tree
 * 嵌套树：[{...node, children: [...]}]
 */
app.get('/tree', async (c) => {
  c.header('Cache-Control', 'no-store')
  try {
    if (freshTree()) return c.json({ data: treeCache })
    const { results } = await DB.prepare(
      `SELECT id, parent_id, code, name, level, path, status, sort
       FROM categories
       ORDER BY sort ASC`
    ).all()

    const map = new Map()
    const roots = []
    for (const n of results || []) {
      n.children = []
      map.set(n.id, n)
    }
    for (const n of map.values()) {
      if (n.parent_id && map.has(n.parent_id)) {
        map.get(n.parent_id).children.push(n)
      } else {
        roots.push(n)
      }
    }
    treeCache = roots
    treeCacheAt = Date.now()
    return c.json({ data: treeCache })
  } catch (err) {
    console.error('[categories-tree-error]', err)
    return c.json({ error: '获取分类树失败' }, 500)
  }
})

/**
 * GET /api/categories/:id
 */
app.get('/:id', async (c) => {
  try {
    const id = c.req.param('id')
    const row = await DB.prepare(
      `SELECT * FROM categories WHERE id = ? LIMIT 1`
    ).bind(id).first()
    if (!row) return c.json({ error: 'not_found' }, 404)
    return c.json({ data: row })
  } catch (err) {
    console.error('[categories-get-error]', err)
    return c.json({ error: '获取分类详情失败' }, 500)
  }
})

/**
 * POST /api/categories
 * 创建分类节点
 * body: { name, parent_id?, sort? }
 */
app.post('/', async (c) => {
  try {
    const identity = c.get('identity') || {}
    if (!identity.phone && !identity.email) return c.json({ error: 'unauthenticated' }, 401)
    if (!isSuperAdmin(c)) return c.json({ error: 'forbidden', message: '仅超级管理员可创建分类' }, 403)

    const body = await c.req.json().catch(() => ({}))
    const name = String(body?.name ?? '').trim()
    const parentId = body?.parent_id || null
    const sort = Number.isFinite(Number(body?.sort)) ? Number(body.sort) : 0

    if (!name) return c.json({ error: 'name 必填' }, 400)
    if (name.length > 50) return c.json({ error: 'name 长度需在 1-50 之间' }, 400)

    let level = 1
    let path = name
    if (parentId) {
      const parent = await DB.prepare(
        `SELECT id, level, path FROM categories WHERE id = ? LIMIT 1`
      ).bind(parentId).first()
      if (!parent) return c.json({ error: '父节点不存在' }, 400)
      level = (parent.level || 0) + 1
      path = `${parent.path} / ${name}`
    }

    // path 唯一约束防御
    const dup = await DB.prepare(
      `SELECT id FROM categories WHERE path = ? LIMIT 1`
    ).bind(path).first()
    if (dup) return c.json({ error: '同级下已存在同名节点' }, 409)

    const id = genId('cat')
    const nowSec = Math.floor(Date.now() / 1000)
    await DB.prepare(
      `INSERT INTO categories (id, parent_id, code, name, level, path, status, sort, created_at)
       VALUES (?, ?, ?, ?, ?, ?, 'active', ?, ?)`
    ).bind(id, parentId, '', name, level, path, sort, nowSec).run()

    invalidateCategoriesCache()

    const row = await DB.prepare(
      `SELECT id, parent_id, code, name, level, path, status, sort FROM categories WHERE id = ? LIMIT 1`
    ).bind(id).first()
    return c.json({ data: row })
  } catch (err) {
    console.error('[categories-create-error]', err)
    return c.json({ error: '创建分类失败' }, 500)
  }
})

/**
 * PUT /api/categories/:id
 * 更新分类节点（目前只支持改名）
 * body: { name }
 */
app.put('/:id', async (c) => {
  try {
    const identity = c.get('identity') || {}
    if (!identity.phone && !identity.email) return c.json({ error: 'unauthenticated' }, 401)
    if (!isSuperAdmin(c)) return c.json({ error: 'forbidden', message: '仅超级管理员可修改分类' }, 403)

    const id = c.req.param('id')
    const body = await c.req.json().catch(() => ({}))
    const name = String(body?.name ?? '').trim()

    if (!name) return c.json({ error: 'name 必填' }, 400)
    if (name.length > 50) return c.json({ error: 'name 长度需在 1-50 之间' }, 400)

    const cur = await DB.prepare(
      `SELECT id, parent_id, name, level, path FROM categories WHERE id = ? LIMIT 1`
    ).bind(id).first()
    if (!cur) return c.json({ error: 'not_found' }, 404)

    let newPath = name
    if (cur.parent_id) {
      const parent = await DB.prepare(
        `SELECT path FROM categories WHERE id = ? LIMIT 1`
      ).bind(cur.parent_id).first()
      if (!parent) return c.json({ error: '父节点不存在' }, 400)
      newPath = `${parent.path} / ${name}`
    }

    const oldPath = cur.path
    if (newPath !== oldPath) {
      // 唯一性校验：如果新路径已被其他节点占用
      const dup = await DB.prepare(
        `SELECT id FROM categories WHERE path = ? AND id != ? LIMIT 1`
      ).bind(newPath, id).first()
      if (dup) return c.json({ error: '同级下已存在同名节点' }, 409)
    }

    // 1) 更新自身
    await DB.prepare(
      `UPDATE categories SET name = ?, path = ? WHERE id = ?`
    ).bind(name, newPath, id).run()

    // 2) 更新所有子孙节点的 path 前缀
    if (newPath !== oldPath) {
      // path 形如 "A / B / C"，子孙的 path 以 "oldPath / " 开头
      // 用 SQLite 的 length() 函数取后缀，避免 JS string.length 与 SQLite 计算不一致（Unicode 安全）
      await DB.prepare(
        `UPDATE categories
         SET path = ? || substr(path, length(?) + 1)
         WHERE path LIKE ? || ' / %'`
      ).bind(newPath, oldPath, oldPath).run()

      // 同步刷新 skills 表里冗余的 node_path（自身 + 子孙）
      await DB.prepare(
        `UPDATE skills SET node_path = ? WHERE node_path = ?`
      ).bind(newPath, oldPath).run()
      await DB.prepare(
        `UPDATE skills
         SET node_path = ? || substr(node_path, length(?) + 1)
         WHERE node_path LIKE ? || ' / %'`
      ).bind(newPath, oldPath, oldPath).run()
    }

    invalidateCategoriesCache()

    const row = await DB.prepare(
      `SELECT id, parent_id, code, name, level, path, status, sort FROM categories WHERE id = ? LIMIT 1`
    ).bind(id).first()
    return c.json({ data: row })
  } catch (err) {
    console.error('[categories-update-error]', err)
    return c.json({ error: '更新分类失败' }, 500)
  }
})

/**
 * DELETE /api/categories/:id
 * 删除分类节点；有子节点级联删除；任一节点关联 skill 则 409
 */
app.delete('/:id', async (c) => {
  try {
    const identity = c.get('identity') || {}
    if (!identity.phone && !identity.email) return c.json({ error: 'unauthenticated' }, 401)
    if (!isSuperAdmin(c)) return c.json({ error: 'forbidden', message: '仅超级管理员可删除分类' }, 403)

    const id = c.req.param('id')
    const cur = await DB.prepare(
      `SELECT id FROM categories WHERE id = ? LIMIT 1`
    ).bind(id).first()
    if (!cur) return c.json({ error: 'not_found' }, 404)

    // BFS 收集自身 + 所有子孙
    const allIds = [id]
    let frontier = [id]
    while (frontier.length) {
      const placeholders = frontier.map(() => '?').join(',')
      const { results } = await DB.prepare(
        `SELECT id FROM categories WHERE parent_id IN (${placeholders})`
      ).bind(...frontier).all()
      const next = (results || []).map(r => r.id)
      if (!next.length) break
      allIds.push(...next)
      frontier = next
    }

    // 校验是否有 skill 关联到任一节点
    const ph = allIds.map(() => '?').join(',')
    const used = await DB.prepare(
      `SELECT COUNT(*) AS n FROM skills WHERE node_id IN (${ph})`
    ).bind(...allIds).first()
    if ((used?.n || 0) > 0) {
      return c.json({ error: '该节点（或子孙节点）下存在已发布 Skill，无法删除' }, 409)
    }

    await DB.prepare(
      `DELETE FROM categories WHERE id IN (${ph})`
    ).bind(...allIds).run()

    invalidateCategoriesCache()

    return c.json({ ok: true, deleted: allIds.length })
  } catch (err) {
    console.error('[categories-delete-error]', err)
    return c.json({ error: '删除分类失败' }, 500)
  }
})

/**
 * POST /api/categories/invalidate-cache
 * 超管手动刷新分类缓存
 */
app.post('/invalidate-cache', async (c) => {
  const identity = c.get('identity') || {}
  if (!identity.phone && !identity.email) return c.json({ error: 'unauthenticated' }, 401)
  if (!isSuperAdmin(c)) return c.json({ error: 'forbidden', message: '仅超级管理员可刷新分类缓存' }, 403)
  invalidateCategoriesCache()
  return c.json({ ok: true, message: 'cache invalidated' })
})

export default app
