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) {
    res.status(500).send("请检查填写的数据库数据是否有误: " + error.message);
  } finally {
    // 确保关闭数据库连接池
    if (pool) {
      await pool.end();
    }
  }
});

app.post("/filterTablesData", async (req, res) => {
  let pool;
  try {
    const { IP, host, baseName, username, password } = req.body.database;
    const filterData = req.body.filterData;

    // 数据库连接配置
    pool = mysql.createPool({
      host: IP,
      user: username,
      password: password,
      database: baseName,
      port: host,
    });

    const connection = await pool.getConnection();
    const resultData = {};

    for (const item of filterData) {
      const parentNode = item.parentNode; // 数据表名
      if (!parentNode) {
        console.warn(
          "parentNode is undefined or null. Skipping this item:",
          item
        );
        continue; // 跳过无效的项
      }
      const checkChildNode = item.checkChildNode; // 要查询的字段

      // 检查表是否存在
      const [tables] = await connection.query("SHOW TABLES");
      const tableNames = tables.map((row) =>
        Object.values(row)[0].toLowerCase()
      );
      if (!tableNames.includes(parentNode.toLowerCase())) {
        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
        .map((field) => `\`${field}\``)
        .join(", ")} FROM \`${parentNode}\` LIMIT 500`;
      console.log("Generated SQL Query:", sqlQuery);
      const [rows] = await connection.query(sqlQuery);
      // 保存结果
      resultData[parentNode] = rows;
    }
    connection.release();
    res.json(resultData);
  } catch (error) {
    console.error(error);
    res.status(500).send(`数据库报错: ${error.message}`);
  } finally {
    if (pool) {
      await pool.end();
    }
  }
});

app.listen(PORT, () => {
  console.log(`Server is running on http://localhost:${PORT}`);
});