query.js

import { Router } from 'express'
import { v4 as uuidv4 } from 'uuid'
import { executeQuery } from '../db.js'
import { analyzeSql, validateSelectOnly } from '../sql-analyzer.js'
import { setCache, getCache, searchInCache } from '../cache.js'

const router = Router()

/**
 * 预检查SQL（语法 + 执行计划）
 * POST /api/query/check
 */
router.post('/check', async (req, res) => {
  try {
    const { connId, sql } = req.body
    if (!connId || !sql) {
      return res.json({ success: false, message: '缺少参数' })
    }

    // 校验SQL必须为SELECT语句
    const sqlCheck = validateSelectOnly(sql)
    if (!sqlCheck.allowed) {
      return res.json({ success: false, message: sqlCheck.reason })
    }

    const result = await analyzeSql(connId, sql)
    res.json({ success: true, data: result })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

/**
 * 执行SQL查询
 * POST /api/query/execute
 */
router.post('/execute', async (req, res) => {
  try {
    const { connId, sql } = req.body
    if (!connId || !sql) {
      return res.json({ success: false, message: '缺少参数' })
    }

    // 校验SQL必须为SELECT语句
    const sqlCheck = validateSelectOnly(sql)
    if (!sqlCheck.allowed) {
      return res.json({ success: false, message: sqlCheck.reason })
    }

    const startTime = Date.now()
    const data = await executeQuery(connId, sql)
    const duration = Date.now() - startTime

    // 判断是否为查询结果（SELECT返回数组，DML返回结果对象）
    const isResultSet = Array.isArray(data)
    let queryId = null
    let columns = []
    let totalCount = 0

    if (isResultSet && data.length > 0) {
      queryId = uuidv4()
      setCache(queryId, data)
      columns = Object.keys(data[0])
      totalCount = data.length
    } else if (isResultSet) {
      queryId = uuidv4()
      setCache(queryId, [])
      totalCount = 0
    }

    res.json({
      success: true,
      data: {
        queryId,
        isResultSet,
        columns,
        totalCount,
        duration,
        // DML返回影响行数
        affectedRows: !isResultSet ? data.affectedRows : undefined,
        message: !isResultSet
          ? `执行成功，影响 ${data.affectedRows} 行`
          : undefined,
      },
    })
  } catch (e) {
    res.json({ success: false, message: e.message })
  }
})

/**
 * 获取分页数据
 * GET /api/query/page/:queryId?page=1&pageSize=20
 */
router.get('/page/:queryId', (req, res) => {
  const { queryId } = req.params
  const page = Number(req.query.page) || 1
  const pageSize = Number(req.query.pageSize) || 20
  const keyword = req.query.keyword || ''

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

  const start = (page - 1) * pageSize
  const end = start + pageSize
  const pageData = entry.data.slice(start, end)

  res.json({
    success: true,
    data: {
      columns: entry.columns,
      rows: pageData,
      totalCount: entry.totalCount,
      page,
      pageSize,
      totalPages: Math.ceil(entry.totalCount / pageSize),
    },
  })
})

/**
 * 获取全部数据（用于导出）
 * GET /api/query/all/:queryId
 */
router.get('/all/:queryId', (req, res) => {
  const { queryId } = req.params
  const keyword = req.query.keyword || ''

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

  res.json({
    success: true,
    data: {
      columns: entry.columns,
      rows: entry.data,
      totalCount: entry.totalCount,
    },
  })
})

export default router