export.js

import { Router } from 'express'
import xlsx from 'xlsx'
import { getCache, searchInCache } from '../cache.js'

const router = Router()

/**
 * 导出Excel
 * GET /api/export/excel/:queryId?keyword=xxx
 */
router.get('/excel/:queryId', (req, res) => {
  try {
    const { queryId } = req.params
    const keyword = req.query.keyword || ''
    const fileName = req.query.fileName || `query_result_${Date.now()}`

    const entry = keyword ? searchInCache(queryId, keyword) : getCache(queryId)
    if (!entry) {
      return res.json({ success: false, message: '查询结果已过期，请重新执行' })
    }

    // 构建Excel
    const wb = xlsx.utils.book_new()
    const wsData = [entry.columns]

    for (const row of entry.data) {
      const rowData = entry.columns.map(col => {
        const val = row[col]
        // 处理Buffer类型（如BLOB字段）
        if (Buffer.isBuffer(val)) return val.toString('hex')
        // 处理Date类型
        if (val instanceof Date) return val.toISOString().replace('T', ' ').replace(/\.\d+Z$/, '')
        return val
      })
      wsData.push(rowData)
    }

    const ws = xlsx.utils.aoa_to_sheet(wsData)

    // 设置列宽
    ws['!cols'] = entry.columns.map(col => ({
      wch: Math.max(col.length * 2, 12),
    }))

    xlsx.utils.book_append_sheet(wb, ws, '查询结果')

    // 生成Buffer
    const buf = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' })

    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}.xlsx`)
    res.send(buf)
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

/**
 * 导入Excel，返回第一列数据（用于IN条件）
 * POST /api/export/import
 * multipart/form-data，字段名：file
 */
router.post('/import', (req, res) => {
  try {
    // 这里使用简单的base64方式上传，避免引入multer
    const { fileData, fileName } = req.body

    if (!fileData) {
      return res.json({ success: false, message: '未接收到文件数据' })
    }

    // base64转Buffer
    const buf = Buffer.from(fileData, 'base64')
    const wb = xlsx.read(buf, { type: 'buffer' })
    const ws = wb.Sheets[wb.SheetNames[0]]
    const data = xlsx.utils.sheet_to_json(ws, { header: 1 })

    // 取第一列（跳过表头）
    const values = []
    for (let i = 1; i < data.length; i++) {
      const val = data[i] && data[i][0]
      if (val !== undefined && val !== null && String(val).trim() !== '') {
        values.push(String(val).trim())
      }
    }

    res.json({ success: true, data: { values, count: values.length } })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

export default router