/**
 * SECURE DATABASE CONNECTION WITH CONNECTION POOLING
 * Production-ready MySQL connection with auto-reconnection
 */

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

// Create connection pool (better for production)
const pool = mysql.createPool({
  connectionLimit: parseInt(process.env.DB_CONNECTION_LIMIT || '10'),
  host: process.env.DB_HOST || '127.0.0.1',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USERNAME || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_DATABASE || '',
  charset: 'utf8mb4',
  waitForConnections: true,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
  // Connection error handling
  acquireTimeout: 10000,
  connectTimeout: 10000,
  // Auto-reconnect
  reconnect: true
});

// Promisify for async/await support
const promisePool = pool.promise();

// Test connection on startup
pool.getConnection((err, connection) => {
  if (err) {
    console.error('\x1b[31m%s\x1b[0m', '[mysql.createPool] Connection error:', err.message);
    
    // Don't crash the app, just log the error
    // The pool will retry connections automatically
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.error('Database connection was closed.');
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
      console.error('Database has too many connections.');
    }
    if (err.code === 'ECONNREFUSED') {
      console.error('Database connection was refused.');
    }
  } else {
    console.log('\x1b[32m%s\x1b[0m', '[mysql.createPool] Connected successfully');
    console.log('\x1b[36m%s\x1b[0m', `Database: ${process.env.DB_DATABASE}`);
    console.log('\x1b[36m%s\x1b[0m', `Connection pool size: ${process.env.DB_CONNECTION_LIMIT || '10'}`);
    connection.release();
  }
});

// Handle pool errors
pool.on('error', (err) => {
  console.error('\x1b[31m%s\x1b[0m', '[mysql.createPool] Pool error:', err.message);
  
  if (err.code === 'PROTOCOL_CONNECTION_LOST') {
    console.log('Attempting to reconnect to database...');
  } else {
    throw err;
  }
});

// Graceful shutdown
process.on('SIGTERM', () => {
  console.log('SIGTERM signal received: closing database connection pool');
  pool.end((err) => {
    if (err) {
      console.error('Error closing pool:', err);
    } else {
      console.log('Pool closed successfully');
    }
    process.exit(err ? 1 : 0);
  });
});

// Export both callback-based pool and promise-based pool
module.exports = {
  pool,           // For callback-based queries
  promisePool,    // For async/await queries
  db: pool        // Alias for backward compatibility
};
