import { Hono } from 'hono'
import { requireIdentity } from '../middleware/auth.js'
import { genId } from '../utils/id.js'
import { DB } from '../db.js'
import { mkdirSync, createWriteStream } from 'fs'
import { fileURLToPath } from 'url'
import path from 'path'
import { ZipArchive } from 'archiver'

const __dirname = path.dirname(fileURLToPath(import.meta.url))
// Keep packageDescription aligned with uploads.js / skill-run.js:
// this file lives in src/routes/, so cf-worker/uploads is two levels up.
const UPLOADS_ROOT = process.env.UPLOADS_DIR || path.join(__dirname, '../../uploads')

// 超级管理员（可见全部 skill 含 private）
const SUPER_ADMIN_EMAIL = '494161546@qq.com'
const SUPER_ADMIN_PHONE = '13136092523'
function isSuperAdmin(identity) {
  if (!identity) return false
  return identity.email === SUPER_ADMIN_EMAIL || identity.phone === SUPER_ADMIN_PHONE
}

/**
 * 将 description 打包成 zip，返回 { r2Key, zipSize, mdFileName }
 */
function packageDescription(skillName, description) {
  return new Promise((resolve, reject) => {
    const safeName = skillName.replace(/[^\w\u4e00-\u9fff.\-]+/g, '_')
    const mdFileName = `${safeName}.md`
    const fid = genId('file')
    const today = new Date().toISOString().slice(0, 10)
    const zipFileName = `${fid}_${safeName}.zip`
    const r2Key = `pkg/${today}/${zipFileName}`

    const dirPath = path.join(UPLOADS_ROOT, 'pkg', today)
    mkdirSync(dirPath, { recursive: true })
    const zipPath = path.join(dirPath, zipFileName)

    const output = createWriteStream(zipPath)
    const archive = new ZipArchive({ zlib: { level: 9 } })

    output.on('close', () => resolve({ r2Key, zipSize: archive.pointer(), mdFileName, fid, safeName }))
    archive.on('error', reject)

    archive.pipe(output)
    archive.append(description, { name: mdFileName })
    archive.finalize()
  })
}

const app = new Hono()

/**
 * GET /api/skills?source=upload|create&node_id=&q=&page=&size=
 * 列表（运营消费 / 用户筛选）
 */
app.get('/', async (c) => {
  try {
    const source  = c.req.query('source') || ''
    const nodeId  = c.req.query('node_id') || ''
    const status  = c.req.query('status') || ''
    const q       = c.req.query('q') || ''
    const page    = Math.max(1, parseInt(c.req.query('page') || '1'))
    const size    = Math.min(200, Math.max(1, parseInt(c.req.query('size') || '50')))

    const where = ['1=1']
    const args = []
    if (source) { where.push('source = ?'); args.push(source) }
    if (nodeId) { where.push('node_id = ?'); args.push(nodeId) }
    if (status) { where.push('status = ?'); args.push(status) }
    if (q)      { where.push('(name LIKE ? OR description LIKE ?)'); args.push(`%${q}%`, `%${q}%`) }

    // 可见范围过滤：管理员看全部；普通用户看 public + 自己创建的 private；未登录只看 public
    const identity = c.get('identity') || {}
    if (!isSuperAdmin(identity)) {
      const userPhone = identity.phone || ''
      const userEmail = identity.email || ''
      const hasIdentity = (userPhone || '').trim() || (userEmail || '').trim()
      if (hasIdentity) {
        where.push(`(visibility = 'public' OR (visibility = 'private' AND ((? <> '' AND creator_phone = ?) OR (? <> '' AND creator_email = ?))))`)
        args.push(userPhone, userPhone, userEmail, userEmail)
      } else {
        where.push(`visibility = 'public'`)
      }
    }

    /* SQL-Injection-Safe: parameterized */
    const total = await DB.prepare(
      `SELECT COUNT(*) AS n FROM skills WHERE ${where.join(' AND ')}`
    ).bind(...args).first()

    /* SQL-Injection-Safe: parameterized */
    const { results } = await DB.prepare(
      `SELECT id, name, description, node_id, node_path, source, status, visibility,
              creator_name, creator_phone, creator_email,
              package_key, package_size, package_name,
              download_count, like_count, dislike_count,
              created_at, updated_at
       FROM skills
       WHERE ${where.join(' AND ')}
       ORDER BY (like_count + download_count * 2) DESC, created_at DESC
       LIMIT ? OFFSET ?`
    ).bind(...args, size, (page - 1) * size).all()

    return c.json({ data: results || [], total: total?.n || 0, page, size })
  } catch (err) {
    console.error('[skills-list-error]', err)
    return c.json({ error: '获取技能列表失败' }, 500)
  }
})

/**
 * GET /api/skills/:id
 */
app.get('/:id', async (c) => {
  try {
    const id = c.req.param('id')
    const row = await DB.prepare(`SELECT * FROM skills WHERE id = ? LIMIT 1`)
      .bind(id).first()
    if (!row) return c.json({ error: 'not_found' }, 404)

    // 私密 skill：只有创建者本人或超级管理员可访问
    if (row.visibility === 'private') {
      const identity = c.get('identity') || {}
      const isOwner =
        (identity.phone && row.creator_phone && identity.phone === row.creator_phone) ||
        (identity.email && row.creator_email && identity.email === row.creator_email)
      if (!isOwner && !isSuperAdmin(identity)) {
        return c.json({ error: 'not_found' }, 404)
      }
    }
    return c.json({ data: row })
  } catch (err) {
    console.error('[skills-get-error]', err)
    return c.json({ error: '获取技能详情失败' }, 500)
  }
})

/**
 * POST /api/skills
 * 创建 / 强制归位
 * body: { name*, description, node_id*, source* (upload|create), creator_name*,
 *         package_key, package_size, package_name }
 */
app.post('/', async (c) => {
  try {
    const unauth = requireIdentity(c)
    if (unauth) return unauth

    const body = await c.req.json().catch(() => ({}))
    const id = c.get('identity')

    // ─── 必填校验 ───
    const name = (body.name || '').trim()
    const node_id = (body.node_id || '').trim()
    const source = (body.source || '').trim()
    const creator_name = (body.creator_name || '').trim()

    const errs = []
    if (!name) errs.push('name 必填')
    if (!node_id) errs.push('node_id 必填（强制归位流程节点）')
    if (!source || !['upload', 'create'].includes(source)) errs.push('source 必填（upload | create）')
    if (!creator_name) errs.push('creator_name 必填（创建人姓名，请前端表单收集）')
    if (source === 'upload' && !body.package_key) errs.push('source=upload 时 package_key 必填（孤儿检测）')
    if (errs.length) return c.json({ error: 'validation_failed', details: errs }, 422)

    // ─── 反查 node_path（归位校验 + 冗余存储） ───
    const node = await DB.prepare(
      `SELECT id, name, path, status FROM categories WHERE id = ? LIMIT 1`
    ).bind(node_id).first()
    if (!node) return c.json({ error: 'invalid_node', message: 'node_id 不存在' }, 422)
    if (node.status === 'archived') {
      return c.json({ error: 'node_archived', message: '该节点已归档不可选' }, 422)
    }

    const skillId = genId('skill')

    // ─── 自动打包：source=create 且有 description 时，生成 zip ───
    let pkgKey = body.package_key || null
    let pkgSize = body.package_size || null
    let pkgName = body.package_name || null
    const desc = body.description || ''

    if (source === 'create' && desc && !pkgKey) {
      try {
        const pkg = await packageDescription(name, desc)
        pkgKey = pkg.r2Key
        pkgSize = pkg.zipSize
        pkgName = pkg.mdFileName
        // 登记 files 表
        await DB.prepare(
          `INSERT INTO files (id, r2_key, filename, size, mime, uploader_phone, uploader_email, bound_skill_id)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
        ).bind(pkg.fid, pkg.r2Key, `${pkg.safeName}.zip`, pkg.zipSize, 'application/zip', id.phone || null, id.email || null, skillId).run()
      } catch (pkgErr) {
        console.error('[auto-package-error]', pkgErr)
        // 打包失败不阻塞创建，package_key 留空
      }
    }

    const visibility = body.visibility === 'private' ? 'private' : 'public'
    await 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 (?, ?, ?, ?, ?, ?, 'draft', ?, ?, ?, ?, ?, ?, ?, strftime('%s','now'), strftime('%s','now'))`
    ).bind(
      skillId, name, desc, node_id, node.path, source,
      creator_name, id.phone || null, id.email || null,
      pkgKey, pkgSize, pkgName, visibility
    ).run()

    // ─── 文件绑定（反孤儿：上传类型的 package_key） ───
    if (body.package_key) {
      await DB.prepare(
        `UPDATE files SET bound_skill_id = ? WHERE r2_key = ?`
      ).bind(skillId, body.package_key).run().catch(() => {})
    }

    // ─── 操作日志 ───
    c.set('audit', {
      action: 'skill.create',
      actor_name: creator_name,
      target_type: 'skill',
      target_id: skillId,
      target_name: name,
      meta: { source, node_id, node_path: node.path, package_key: body.package_key || null }
    })

    return c.json({ data: { id: skillId, name, node_id, node_path: node.path } }, 201)
  } catch (err) {
    console.error('[skills-create-error]', err)
    return c.json({ error: '创建技能失败' }, 500)
  }
})

/**
 * PUT /api/skills/:id  （部分更新）
 */
app.put('/:id', async (c) => {
  try {
    const unauth = requireIdentity(c)
    if (unauth) return unauth
    const id = c.req.param('id')
    const body = await c.req.json().catch(() => ({}))

    const exist = await DB.prepare(`SELECT * FROM skills WHERE id = ?`).bind(id).first()
    if (!exist) return c.json({ error: 'not_found' }, 404)

    // 所有权校验：只有创建者本人或超级管理员可以编辑
    const identity = c.get('identity') || {}
    const isOwner =
      (identity.phone && exist.creator_phone && identity.phone === exist.creator_phone) ||
      (identity.email && exist.creator_email && identity.email === exist.creator_email)
    if (!isOwner && !isSuperAdmin(identity)) {
      return c.json({ error: 'forbidden', message: '只能编辑自己创建的技能' }, 403)
    }

    const fields = []
    const args = []
    /* SQL-Injection-Safe: parameterized — keys whitelisted from a literal array, values bound */
    for (const k of ['name', 'description', 'status', 'creator_name']) {
      if (body[k] !== undefined) { fields.push(`${k} = ?`); args.push(body[k]) }
    }
    if (body.visibility !== undefined) {
      const v = body.visibility === 'private' ? 'private' : 'public'
      fields.push('visibility = ?'); args.push(v)
    }
    if (body.node_id) {
      const node = await DB.prepare(`SELECT path, status FROM categories WHERE id = ?`)
        .bind(body.node_id).first()
      if (!node) return c.json({ error: 'invalid_node' }, 422)
      if (node.status === 'archived') return c.json({ error: 'node_archived' }, 422)
      fields.push('node_id = ?'); args.push(body.node_id)
      fields.push('node_path = ?'); args.push(node.path)
    }
    if (!fields.length) return c.json({ data: exist })

    fields.push("updated_at = strftime('%s','now')")
    args.push(id)
    await DB.prepare(`UPDATE skills SET ${fields.join(', ')} WHERE id = ?`).bind(...args).run()

    c.set('audit', {
      action: 'skill.update',
      actor_name: body.creator_name || exist.creator_name,
      target_type: 'skill',
      target_id: id,
      target_name: body.name || exist.name,
      meta: { changed: Object.keys(body) }
    })

    const fresh = await DB.prepare(`SELECT * FROM skills WHERE id = ?`).bind(id).first()
    return c.json({ data: fresh })
  } catch (err) {
    console.error('[skills-update-error]', err)
    return c.json({ error: '更新技能失败' }, 500)
  }
})

/**
 * DELETE /api/skills/:id
 */
app.delete('/:id', async (c) => {
  try {
    const unauth = requireIdentity(c)
    if (unauth) return unauth
    const id = c.req.param('id')
    const exist = await DB.prepare(`SELECT id, name, creator_name, creator_phone, creator_email FROM skills WHERE id = ?`).bind(id).first()
    if (!exist) return c.json({ error: 'not_found' }, 404)

    // 所有权校验：只有创建者本人或超级管理员可以删除
    const identity = c.get('identity') || {}
    const isOwner =
      (identity.phone && exist.creator_phone && identity.phone === exist.creator_phone) ||
      (identity.email && exist.creator_email && identity.email === exist.creator_email)
    if (!isOwner && !isSuperAdmin(identity)) {
      return c.json({ error: 'forbidden', message: '只能删除自己创建的技能' }, 403)
    }

    await DB.prepare(`DELETE FROM skills WHERE id = ?`).bind(id).run()
    // 文件解绑（变孤儿，留运营人工清理）
    await DB.prepare(`UPDATE files SET bound_skill_id = NULL WHERE bound_skill_id = ?`).bind(id).run()
      .catch(err => console.error('[skill-delete] unbind files failed:', err))

    c.set('audit', {
      action: 'skill.delete',
      actor_name: exist.creator_name,
      target_type: 'skill',
      target_id: id,
      target_name: exist.name,
    })
    return c.json({ ok: true })
  } catch (err) {
    console.error('[skills-delete-error]', err)
    return c.json({ error: '删除技能失败' }, 500)
  }
})

/**
 * POST /api/skills/:id/download
 * 下载计数（复制命令 / 点击下载时前端主动上报）
 * 去重：同一用户（email/phone）对同一 Skill 只计一次
 */
app.post('/:id/download', async (c) => {
  try {
    const id = c.req.param('id')
    const exist = await DB.prepare(`SELECT id FROM skills WHERE id = ?`).bind(id).first()
    if (!exist) return c.json({ error: 'not_found' }, 404)

    const identity = c.get('identity') || {}
    const userKey = identity.email || identity.phone || ''

    let alreadyDownloaded = false
    if (userKey) {
      // 有身份信息：查重
      const prev = await DB.prepare(
        `SELECT id FROM skill_downloads WHERE skill_id = ? AND user_key = ? LIMIT 1`
      ).bind(id, userKey).first()
      if (prev) {
        alreadyDownloaded = true
      } else {
        // 首次下载：插入记录 + 计数 +1
        const dlId = genId('dl')
        await DB.prepare(
          `INSERT INTO skill_downloads (id, skill_id, user_key) VALUES (?, ?, ?)`
        ).bind(dlId, id, userKey).run()
        await DB.prepare(
          `UPDATE skills SET download_count = download_count + 1 WHERE id = ?`
        ).bind(id).run()
      }
    } else {
      // 匿名用户：无法去重，仍计数
      await DB.prepare(
        `UPDATE skills SET download_count = download_count + 1 WHERE id = ?`
      ).bind(id).run()
    }

    const fresh = await DB.prepare(`SELECT download_count FROM skills WHERE id = ?`).bind(id).first()
    return c.json({ ok: true, download_count: fresh?.download_count ?? 0, already: alreadyDownloaded })
  } catch (err) {
    console.error('[skills-download-count-error]', err)
    return c.json({ error: '记录下载失败' }, 500)
  }
})

/**
 * GET /api/skills/:id/my-vote
 * 查询当前用户对该 skill 的投票状态
 */
app.get('/:id/my-vote', async (c) => {
  try {
    const id = c.req.param('id')
    const identity = c.get('identity') || {}
    const userKey = identity.email || identity.phone || 'anonymous'
    const row = await DB.prepare(
      `SELECT vote FROM skill_votes WHERE skill_id = ? AND user_key = ? LIMIT 1`
    ).bind(id, userKey).first()
    return c.json({ vote: row?.vote || null })
  } catch {
    return c.json({ vote: null })
  }
})

// 投票防重投：进程内简单限流（10 秒窗口）。多实例部署需改造为 Redis 或 DB 持久化。
const voteRateLimit = new Map()
// 定期清理过期键，避免内存泄漏
setInterval(() => {
  const cutoff = Date.now() - 60_000
  for (const [k, t] of voteRateLimit.entries()) {
    if (t < cutoff) voteRateLimit.delete(k)
  }
}, 60_000).unref?.()

/**
 * POST /api/skills/:id/vote
 * 点赞 / 点踩（防重投，同 vote 再点则取消）
 * body: { vote: 'like' | 'dislike' }
 */
app.post('/:id/vote', async (c) => {
  try {
    const id = c.req.param('id')
    const identity = c.get('identity') || {}
    const userKey = identity.email || identity.phone || 'anonymous'
    const body = await c.req.json().catch(() => ({}))
    const vote = body.vote
    if (!['like', 'dislike'].includes(vote)) return c.json({ error: 'invalid_vote', message: 'vote 必须是 like 或 dislike' }, 422)

    const exist = await DB.prepare(`SELECT id FROM skills WHERE id = ?`).bind(id).first()
    if (!exist) return c.json({ error: 'not_found' }, 404)

    // TODO(P3-7): skill_votes 表当前无 updated_at 字段，无法做基于持久化的 10 秒防重投限流；
    //   如需启用：先在 migrations 里给 skill_votes 增加 updated_at INTEGER 字段，
    //   写入时 UPDATE skill_votes SET updated_at = strftime('%s','now')，再启用下方校验逻辑。
    //   暂以 in-memory Map 兜底（同进程内有效，多实例部署需替换为 Redis 或 DB 字段）。
    const now = Date.now()
    const rateKey = `${id}:${userKey}`
    const lastVoteAt = voteRateLimit.get(rateKey) || 0
    if (now - lastVoteAt < 10000) {
      return c.json({ error: '操作太频繁，请稍后再试' }, 429)
    }
    voteRateLimit.set(rateKey, now)

    const prev = await DB.prepare(
      `SELECT vote FROM skill_votes WHERE skill_id = ? AND user_key = ? LIMIT 1`
    ).bind(id, userKey).first()

    if (!prev) {
      // 首次投票：插入记录 + 计数 +1
      const voteId = genId('vote')
      await DB.prepare(`INSERT INTO skill_votes (id, skill_id, user_key, vote) VALUES (?, ?, ?, ?)`)
        .bind(voteId, id, userKey, vote).run()
      await DB.prepare(`UPDATE skills SET ${vote}_count = ${vote}_count + 1 WHERE id = ?`)
        .bind(id).run()
    } else if (prev.vote === vote) {
      // 相同 vote 取消：删除记录 + 计数 -1
      await DB.prepare(`DELETE FROM skill_votes WHERE skill_id = ? AND user_key = ?`)
        .bind(id, userKey).run()
      await DB.prepare(`UPDATE skills SET ${vote}_count = MAX(0, ${vote}_count - 1) WHERE id = ?`)
        .bind(id).run()
      const fresh = await DB.prepare(`SELECT like_count, dislike_count FROM skills WHERE id = ?`).bind(id).first()
      return c.json({ ok: true, vote: null, like_count: fresh?.like_count ?? 0, dislike_count: fresh?.dislike_count ?? 0 })
    } else {
      // 切换 vote：更新记录 + 旧计数 -1，新计数 +1
      const prevType = prev.vote
      await DB.prepare(`UPDATE skill_votes SET vote = ? WHERE skill_id = ? AND user_key = ?`)
        .bind(vote, id, userKey).run()
      await DB.prepare(
        `UPDATE skills SET ${vote}_count = ${vote}_count + 1, ${prevType}_count = MAX(0, ${prevType}_count - 1) WHERE id = ?`
      ).bind(id).run()
    }

    const fresh = await DB.prepare(`SELECT like_count, dislike_count FROM skills WHERE id = ?`).bind(id).first()
    return c.json({ ok: true, vote, like_count: fresh?.like_count ?? 0, dislike_count: fresh?.dislike_count ?? 0 })
  } catch (err) {
    console.error('[skills-vote-error]', err)
    return c.json({ error: '投票失败' }, 500)
  }
})

export default app
