const express = require("express");
const { spawn } = require("child_process");
const path = require("path");
const { Parser } = require("node-sql-parser");

const app = express();
const PORT = process.env.PORT || 3000;

app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, "../public")));

app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "views"));

const dbConfig = {
  host: process.env.DB_HOST || "mysql",
  user: process.env.DB_USER || "[**REDACTED**]",
  password: process.env.DB_PASSWORD || "[**REDACTED**]",
  database: process.env.DB_NAME || "board_db",
  port: process.env.DB_PORT || 3306,
};

const parser = new Parser();

function executeQuery(query) {
  return new Promise((resolve, reject) => {
    const args = [
      `-h${dbConfig.host}`,
      `-P${dbConfig.port}`,
      `-u${dbConfig.user}`,
      `-p${dbConfig.password}`,
      dbConfig.database,
      "-e",
      query,
      "--batch",
      "--ssl=0",
    ];

    const mysqlProcess = spawn("mariadb", args);

    let stdout = "";
    let stderr = "";

    mysqlProcess.stdout.on("data", (data) => {
      stdout += data.toString();
    });

    mysqlProcess.stderr.on("data", (data) => {
      stderr += data.toString();
    });

    mysqlProcess.on("close", (code) => {
      if (code !== 0) {
        console.error("MySQL execution error:", stderr);
        reject(new Error(stderr || "MySQL execution failed"));
        return;
      }

      if (stderr) {
        console.error("MySQL stderr:", stderr);
        reject(new Error(stderr));
        return;
      }

      console.log(stdout);
      const lines = stdout.trim().split("\n");
      if (lines.length <= 1) {
        resolve([]);
        return;
      }

      const headers = lines[0].split("\t");
      const results = [];

      for (let i = 1; i < lines.length; i++) {
        const values = lines[i].split("\t");
        const row = {};
        headers.forEach((header, index) => {
          row[header] = values[index];
        });
        results.push(row);
      }

      resolve(results);
    });

    mysqlProcess.on("error", (error) => {
      console.error("Process error:", error);
      reject(error);
    });
  });
}

app.get("/", (req, res) => {
  const column = req.query.column || "created_at";
  let direction = req.query.direction || "DESC";

  if (direction !== "ASC" && direction !== "DESC") {
    direction = "DESC";
  }

  const query = `SELECT id, title, author, created_at FROM posts ORDER BY ${column} ${direction}`;

  if (query.includes("!")) {
    return res.status(400).render("error", { message: "Invalid query." });
  }

  try {
    const ast = parser.astify(query);

    if (typeof ast !== "object") {
      throw new Error("Invalid query.");
    }

    if (!ast || ast.type !== "select") {
      throw new Error("Invalid query.");
    }

    const allowedAstKeys = [
      "with",
      "type",
      "options",
      "distinct",
      "columns",
      "into",
      "from",
      "where",
      "groupby",
      "having",
      "orderby",
      "limit",
      "locking_read",
      "window",
      "collate",
    ];
    const astKeys = Object.keys(ast);
    for (const key of astKeys) {
      if (!allowedAstKeys.includes(key)) {
        throw new Error("Invalid query.");
      }
    }

    const allowedColumns = ["id", "title", "author", "created_at"];
    const selectedColumns = ast.columns.map((col) => col.expr.column);

    for (const col of selectedColumns) {
      if (!allowedColumns.includes(col)) {
        throw new Error("Invalid query.");
      }
    }

    if (ast.from && ast.from.length > 0) {
      const tableName = ast.from[0].table;
      if (tableName !== "posts") {
        throw new Error("Invalid query.");
      }
    }

    if (ast.orderby && ast.orderby.length > 0) {
      for (const order of ast.orderby) {
        if (order.expr.type != "column_ref") {
          throw new Error("Invalid query.");
        }
        if (order.expr.table !== null) {
          throw new Error("Invalid query.");
        }
        if (order.expr.collate !== null) {
          throw new Error("Invalid query.");
        }
        if (order.type !== "ASC" && order.type !== "DESC") {
          throw new Error("Invalid query.");
        }

        const allowedKeys = ["expr", "type"];
        const orderKeys = Object.keys(order);
        for (const key of orderKeys) {
          if (!allowedKeys.includes(key)) {
            throw new Error("Invalid query.");
          }
        }

        const allowedExprKeys = ["type", "table", "column", "collate"];
        const exprKeys = Object.keys(order.expr);
        for (const key of exprKeys) {
          if (!allowedExprKeys.includes(key)) {
            throw new Error("Invalid query.");
          }
        }
      }
    }
    if (ast.orderby && ast.orderby.length !== 1) {
      throw new Error("Invalid query.");
    }
    if (
      ast.with !== null ||
      ast.options !== null ||
      ast.distinct !== null ||
      ast.where !== null ||
      ast.groupby !== null ||
      ast.having !== null ||
      ast.limit !== null ||
      ast.locking_read !== null ||
      ast.window !== null ||
      ast.collate !== null
    ) {
      throw new Error("Invalid query.");
    }
    if (ast.into.position !== null) {
      throw new Error("Invalid query.");
    }

    if (ast.into) {
      const allowedIntoKeys = ["position"];
      const intoKeys = Object.keys(ast.into);
      for (const key of intoKeys) {
        if (!allowedIntoKeys.includes(key)) {
          throw new Error("Invalid query.");
        }
      }
    }
  } catch (error) {
    console.error("SQL parsing error:", error.message);
    return res.status(400).render("error", { message: error.message });
  }

  executeQuery(query)
    .then((rows) => {
      res.render("index", {
        posts: rows,
        currentColumn: column,
        currentDirection: direction,
      });
    })
    .catch((error) => {
      console.error("Database error:", error);
      return res
        .status(500)
        .render("error", { message: "Server error occurred." });
    });
});