const path = require("path");
const mysql = require("mysql2/promise");
const dotenv = require("dotenv");

// Load SAT_backend/.env when this module is required (works for standalone SAT or combined-server).
dotenv.config({ path: path.join(__dirname, "..", "..", ".env") });

/**
 * Unified EHS stack: Risk Assessment uses DB_* in Risk_assessment_backend/.env; SAT historically used MYSQL_*.
 * If MYSQL_* are unset, reuse DB_* so one file can configure both backends.
 */
const host = process.env.MYSQL_HOST || process.env.DB_HOST || "127.0.0.1";
const user = process.env.MYSQL_USER || process.env.DB_USER || "root";
const password =
  process.env.MYSQL_PASSWORD !== undefined
    ? process.env.MYSQL_PASSWORD
    : process.env.DB_PASSWORD;

/** Single shared MySQL schema for SAT and Risk Assessment (not overridable). */
const HSE_DATABASE = "hse";

// Create a connection pool for better performance
const pool = mysql.createPool({
    host,
    user,
    password,
    database: HSE_DATABASE,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Test the database connection
pool.getConnection()
    .then((connection) => {
        console.log("✅ Database connected successfully!");
        connection.release();
    })
    .catch((error) => {
        console.error("❌ Database connection failed:", error.message);
    });

module.exports = pool;

