/**
 * Database Configuration
 * MySQL connection pool setup
 */

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

dotenv.config();

// Create connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST || "localhost",
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "",
  database: process.env.DB_NAME || "aerix_task_management",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

// Test database connection
const connectDatabase = async () => {
  try {
    const connection = await pool.getConnection();
    console.log("📊 MySQL Database Pool Created");
    connection.release();
    return true;
  } catch (error) {
    console.error("Database connection failed:", error.message);
    throw error;
  }
};

// Execute query helper
const query = async (sql, params = []) => {
  try {
    const [results] = await pool.execute(sql, params);
    return results;
  } catch (error) {
    console.error("Query error:", error.message);
    throw error;
  }
};

// Transaction helper
const transaction = async (callback) => {
  const connection = await pool.getConnection();
  await connection.beginTransaction();

  try {
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
};

module.exports = {
  pool,
  query,
  transaction,
  connectDatabase,
};
