import express from "express";
import mysql from "mysql2/promise";
import timeout from "connect-timeout";
import bodyParser from "body-parser";
const app = express();
const PORT = 3000;

// 设置请求超时，单位为毫秒（比如：设置 30 秒超时）
app.use(timeout("30s"));
app.use(bodyParser.json()); // 在请求处理之前，先解析JSON
// 处理超时请求
app.use((req, res, next) => {
  if (req.timedout) {
    return res.status(408).send("Request Timeout");
  }
  next();
});

// 获取表和字段信息
app.get("/tables", async (req, res) => {
  let pool;
  try {
    const { IP, host, baseName, username, password } = req.query;
    // 数据库连接配置
    pool = mysql.createPool({
      host: IP,
      user: username,
      password: password,
      database: baseName,
      port: host,
    });
    const connection = await pool.getConnection();
    const [tables] = await connection.query("SHOW TABLES");
    const tableFieldMap = {};
    for (const table of tables) {
      const tableName = Object.values(table)[0];
      const [fields] = await connection.query(`DESCRIBE ${tableName}`);
      tableFieldMap[tableName] = fields.map((field) => field.Field);
    }
    connection.release();
    res.json(tableFieldMap);
  } catch (error) {
    // console.error("Error fetching database structure:", error);
    res.status(500).send("请检查填写的数据库数据是否有误" + error);
  } finally {
    // 确保关闭数据库连接池
    if (pool) {
      await pool.end();
    }
  }
});
app.post("/filterTablesData", async (req, res) => {
  try {
    console.log(req.params, req.body, "req.query.filterData");
    const { IP, host, baseName, username, password } = req.body.database;
    const filterData = req.body.filterData;
    // 数据库连接配置
    const pool = mysql.createPool({
      host: IP,
      user: username,
      password: password,
      database: baseName,
      port: host,
    });
    // 获取数据库连接;
    const connection = await pool.getConnection();
    // 用于存储最终查询结果
    const resultData = {};
    // 遍历 filterData
    for (const item of filterData) {
      const parentNode = item.parentNode; // 数据表名
      const checkChildNode = item.checkChildNode; // 要查询的字段

      // 检查表是否存在
      const [tables] = await connection.query("SHOW TABLES");
      const tableNames = tables.map((row) => Object.values(row)[0]);
      if (!tableNames.includes(parentNode)) {
        continue; // 如果表不存在，跳过
      }

      // 获取表字段信息
      const [columns] = await connection.query(
        `SHOW COLUMNS FROM ${parentNode}`
      );
      const tableFields = columns.map((column) => column.Field);

      // 过滤存在的字段
      const validFields = checkChildNode.filter((field) =>
        tableFields.includes(field)
      );
      if (validFields.length === 0) {
        continue; // 如果没有匹配的字段，跳过
      }

      // 动态查询字段的数据
      const sqlQuery = `SELECT ${validFields.join(
        ", "
      )} FROM ${parentNode} LIMIT 100`; // 限制查询 100 条数据
      const [rows] = await connection.query(sqlQuery);

      // 保存结果
      resultData[parentNode] = rows;
    }
    // 释放连接
    connection.release();
    // 返回最终数据
    res.json(resultData);
  } catch (error) {
    console.error(error);
    res.status(500).send("Error fetching data from database");
  }
});
app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});