import mysql from 'mysql2/promise';
import { SystemConfig } from '../types';

// This is a singleton to reuse the connection pool in a server environment (Next.js)

let pool: mysql.Pool | null = null;

// Default config (fallback)
const DEFAULT_CONFIG = {
    host: process.env.DB_HOST || '127.0.0.1',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'password',
    database: process.env.DB_NAME || 'nexus_db',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
};

export const getDbConnection = async (configOverride?: SystemConfig) => {
    if (pool) return pool;

    const config = configOverride ? {
        host: configOverride.dbHost,
        port: parseInt(configOverride.dbPort),
        user: configOverride.dbUser,
        password: configOverride.dbPass,
        database: configOverride.dbName,
    } : DEFAULT_CONFIG;

    try {
        pool = mysql.createPool({
            ...config,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
        
        
        return pool;
    } catch (error) {
        console.error('[MYSQL] Failed to initialize pool:', error);
        throw error;
    }
};

export const query = async (sql: string, params: any[]) => {
    const db = await getDbConnection();
    const [results] = await db.execute(sql, params);
    return results;
};