db.js

import mysql from 'mysql2/promise'
import fs from 'fs'
import path from 'path'
import { fileURLToPath } from 'url'

const __dirname = path.dirname(fileURLToPath(import.meta.url))
const CONFIG_FILE = path.join(__dirname, '..', 'connections.json')

// 连接池缓存 { connId: pool }
const pools = new Map()

// 读取连接配置
function loadConnections() {
  try {
    if (fs.existsSync(CONFIG_FILE)) {
      return JSON.parse(fs.readFileSync(CONFIG_FILE, 'utf-8'))
    }
  } catch (e) {
    console.error('读取连接配置失败:', e.message)
  }
  return []
}

// 保存连接配置
function saveConnections(connections) {
  fs.writeFileSync(CONFIG_FILE, JSON.stringify(connections, null, 2), 'utf-8')
}

// 获取所有连接
export function getConnections() {
  return loadConnections()
}

// 新增连接
export function addConnection(conn) {
  const connections = loadConnections()
  const id = `conn_${Date.now()}`
  const newConn = { id, ...conn, createdAt: new Date().toISOString() }
  connections.push(newConn)
  saveConnections(connections)
  return newConn
}

// 删除连接
export function removeConnection(id) {
  const connections = loadConnections()
  const idx = connections.findIndex(c => c.id === id)
  if (idx >= 0) connections.splice(idx, 1)
  saveConnections(connections)
  closePool(id)
  return true
}

// 测试连接
export async function testConnection(conn) {
  let connection
  try {
    connection = await mysql.createConnection({
      host: conn.host,
      port: Number(conn.port) || 3306,
      user: conn.user,
      password: conn.password,
      database: conn.database,
      connectTimeout: 5000,
    })
    await connection.ping()
    return { success: true, message: '连接成功' }
  } catch (e) {
    return { success: false, message: e.message }
  } finally {
    if (connection) await connection.end().catch(() => {})
  }
}

// 获取连接池
export function getPool(connId) {
  if (pools.has(connId)) return pools.get(connId)

  const connections = loadConnections()
  const conn = connections.find(c => c.id === connId)
  if (!conn) throw new Error('连接配置不存在')

  const pool = mysql.createPool({
    host: conn.host,
    port: Number(conn.port) || 3306,
    user: conn.user,
    password: conn.password,
    database: conn.database,
    waitForConnections: true,
    connectionLimit: 5,
    queueLimit: 0,
    connectTimeout: 10000,
  })
  pools.set(connId, pool)
  return pool
}

// 关闭连接池
export function closePool(connId) {
  const pool = pools.get(connId)
  if (pool) {
    pool.end().catch(() => {})
    pools.delete(connId)
  }
}

// 执行SQL查询
export async function executeQuery(connId, sql, params = []) {
  const pool = getPool(connId)
  const [rows] = await pool.query(sql, params)
  return rows
}

// 执行EXPLAIN
export async function executeExplain(connId, sql) {
  const pool = getPool(connId)
  try {
    const [rows] = await pool.query(`EXPLAIN ${sql}`)
    return rows
  } catch {
    // 某些SQL不支持EXPLAIN，尝试EXPLAIN FORMAT=JSON
    try {
      const [rows] = await pool.query(`EXPLAIN FORMAT=JSON ${sql}`)
      return rows
    } catch {
      return null
    }
  }
}