const mysql = require('mysql2/promise');
require('dotenv').config();

// Pool de connexions pour chaque base de données
const pools = {};

// Configuration des bases de données
const dbConfigs = {
  ggr: {
    host: process.env.DB_GGR_HOST || 'localhost',
    user: process.env.DB_GGR_USER || 'transubd_ggr',
    password: process.env.DB_GGR_PASSWORD || '',
    database: process.env.DB_GGR_NAME || 'transubd_ggr',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0
  },
  parc: {
    host: process.env.DB_PARC_HOST || 'localhost',
    user: process.env.DB_PARC_USER || 'transubd_parc',
    password: process.env.DB_PARC_PASSWORD || '',
    database: process.env.DB_PARC_NAME || 'transubd_GestionParc',
    waitForConnections: true,
    connectionLimit: 5,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0
  },
  grh: {
    host: process.env.DB_GRH_HOST || 'localhost',
    user: process.env.DB_GRH_USER || 'transubd_grh',
    password: process.env.DB_GRH_PASSWORD || '',
    database: process.env.DB_GRH_NAME || 'transubd_GRH',
    waitForConnections: true,
    connectionLimit: 5,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0
  }
};

// Créer les pools de connexions
Object.keys(dbConfigs).forEach(key => {
  pools[key] = mysql.createPool(dbConfigs[key]);
});

// Fonction pour tester les connexions
async function testConnections() {
  for (const [key, pool] of Object.entries(pools)) {
    try {
      await pool.getConnection();
      console.log(`✅ Connexion réussie à la base ${key}`);
    } catch (error) {
      console.error(`❌ Erreur connexion base ${key}:`, error.message);
    }
  }
}

// Fonction pour exécuter une requête
async function query(dbKey, sql, params = []) {
  if (!pools[dbKey]) {
    throw new Error(`Base de données ${dbKey} non configurée`);
  }
  
  try {
    const [rows] = await pools[dbKey].execute(sql, params);
    return rows;
  } catch (error) {
    console.error(`Erreur requête ${dbKey}:`, error.message);
    throw error;
  }
}

// Fonction pour fermer toutes les connexions
async function closeAllConnections() {
  for (const pool of Object.values(pools)) {
    await pool.end();
  }
}

module.exports = {
  query,
  testConnections,
  closeAllConnections,
  pools
};