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

class SQLConnectionManager {
	constructor(logger, dbConnect) {
		this.logger = logger;
		this.dbConnect = dbConnect || require("../config/config.json");
		this.pool = this.createDbPool();
	}

	createDbPool() {
		return mysql.createPool({
			host: process.env.MYSQL_HOST || this.dbConnect.host,
			port: process.env.MYSQL_PORT || this.dbConnect.port,
			user: process.env.MYSQL_USER || this.dbConnect.user,
			password: process.env.MYSQL_PASSWORD || this.dbConnect.password,
			database: process.env.MYSQL_DATABASE || this.dbConnect.database,
			table: process.env.MYSQL_TABLE || this.dbConnect.table,
			waitForConnections: true,
			connectionLimit: 10, // Adjust the limit
			queueLimit: 0,
			debug: false, // Disable SQL query logging
		});
	}

	async getConnectionWithRetry(retryCount = 3) {
		for (let attempt = 1; attempt <= retryCount; attempt++) {
			try {
				return await this.pool.getConnection();
			} catch (error) {
				this.logger.warn(
					`Error acquiring database connection (Attempt ${attempt}/${retryCount}):`,
					error.message,
				);
				// Wait for a moment before retrying
				await new Promise((resolve) => setTimeout(resolve, 1000));
			}
		}
		this.logger.error(
			"Failed to acquire database connection after multiple attempts.",
		);
		throw new Error(
			"Failed to acquire database connection after multiple attempts.",
		);
	}

	async getConnection() {
		try {
			if (this.pool._closed) {
				this.logger.error("Connection pool is closed.");
				throw new Error("Connection pool is closed.");
			}

			// Use a timeout mechanism to prevent hanging in case of pool exhaustion
			const connection = await Promise.race([
				this.getConnectionWithRetry(),
				new Promise((_, reject) =>
					setTimeout(() => reject(new Error("Connection timeout")), 10000),
				),
			]);

			return connection;
		} catch (error) {
			this.logger.error("Error acquiring database connection:", error.message);
			throw error;
		}
	}

	async getPoolInfo() {
		try {
			const { _allConnections, _freeConnections, _acquiringConnections } =
				this.pool;
			this.logger.info("Pool Info:", {
				totalConnections: _allConnections.length,
				freeConnections: _freeConnections.length,
				acquiringConnections: _acquiringConnections.length,
			});
			return {
				totalConnections: _allConnections.length,
				freeConnections: _freeConnections.length,
				acquiringConnections: _acquiringConnections.length,
			};
		} catch (error) {
			this.logger.error("Error getting pool information:", error.message);
			throw error;
		}
	}

	async executeQuery(sql, connection = null) {
		const startTime = Date.now(); // Record the start time

		try {
			if (!connection) {
				// If a connection is not provided, acquire one from the pool
				connection = await this.getConnection();
			}

			const [rows] = await connection.query(sql);
			const endTime = Date.now(); // Record the end time
			const executionTime = endTime - startTime; // Calculate the execution time

			this.logger.info(
				`SQL query executed successfully in ${executionTime} ms: ${sql}`,
			);

			return rows;
		} catch (error) {
			this.logger.error("Error executing SQL query:", error.message);
			throw error;
		} finally {
			if (connection) {
				// If the connection was acquired in this function, release it back to the pool
				connection.release();
			}
		}
	}

	async closePool() {
		try {
			await this.pool.end();
			this.logger.info("Connection pool closed.");
		} catch (error) {
			this.logger.error("Error closing connection pool:", error.message);
			throw error;
		}
	}
}

module.exports = SQLConnectionManager;

mysql2/promise is not working, use some other library which is robust