/*
This class is responsible for creating the connection 
to the database (pool) and for executing the .sql file
whenever the server starts.
*/
const mysql = require("mysql2");
const fs = require("fs");
const path = require("path");
require("dotenv").config();

// Initialize the pool instance to read the SQL startup file
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  connectTimeout: 10000, // 10 seconds for connection timeout
  acquireTimeout: 10000, // 10 seconds to acquire a connection from the pool
});

console.log("Database Host: ", process.env.DB_HOST);
console.log("Database user: ", process.env.DB_USER);
console.log("Database name: ", process.env.DB_NAME);

// Function to add a test user
const addTestUser = () => {
  return new Promise((resolve, reject) => {
    const username = "fhurtado14"; // Example test username
    const sql = "INSERT INTO users (username) VALUES (?)";

    pool.query(sql, [username], (err, result) => {
      if (err) {
        console.error("Error inserting test user:", err);
        return reject(err);
      }
      console.log("Test user inserted successfully.");
      resolve(result);
    });
  });
};

// Function to execute the SQL file at initilization
const executeSQLFile = (filePath) => {
  const sql = fs.readFileSync(filePath, "utf8");
  pool.getConnection((err, connection) => {
    if (err) {
      console.error("Error connecting to MySQL:", err.message);
      return;
    }

    // Execute the SQL from the file
    connection.query(sql, (err, result) => {
      if (err) {
        console.error("Error executing SQL file:", err.message);
        connection.release();
        return;
      }
      console.log("SQL file executed successfully.");
      connection.release();

      // Now add the test user
      addTestUser()
        .then(() => console.log("Test user added."))
        .catch((err) => console.error("Failed to add test user:", err));
    });
  });
};

// Execute the SQL file during startup
const schemaFilePath = path.join(__dirname, "./schema.sql");
executeSQLFile(schemaFilePath);

module.exports = { pool: pool.promise() };