Blog

-- Traditional PostgreSQL connection management - manual and resource-intensive approaches

-- Basic connection handling with poor resource management
CREATE TABLE connection_usage_log (
    log_id SERIAL PRIMARY KEY,
    connection_id VARCHAR(100) NOT NULL,
    application_name VARCHAR(100),
    database_name VARCHAR(100),
    username VARCHAR(100),

    -- Connection lifecycle tracking
    connection_established TIMESTAMP NOT NULL,
    connection_closed TIMESTAMP,
    connection_duration_seconds INTEGER,

    -- Resource utilization tracking (limited visibility)
    queries_executed INTEGER DEFAULT 0,
    transactions_executed INTEGER DEFAULT 0,
    bytes_transferred BIGINT DEFAULT 0,

    -- Basic performance metrics
    avg_query_time_ms DECIMAL(10,3),
    max_query_time_ms DECIMAL(10,3),
    total_wait_time_ms BIGINT DEFAULT 0,

    -- Connection status tracking
    connection_status VARCHAR(50) DEFAULT 'active',
    last_activity TIMESTAMP,
    idle_time_seconds INTEGER DEFAULT 0,

    -- Error tracking (basic)
    error_count INTEGER DEFAULT 0,
    last_error_message TEXT,
    last_error_timestamp TIMESTAMP
);

-- Manual connection pool simulation (extremely limited functionality)
CREATE TABLE connection_pool_config (
    pool_name VARCHAR(100) PRIMARY KEY,
    database_name VARCHAR(100) NOT NULL,
    min_connections INTEGER DEFAULT 5,
    max_connections INTEGER DEFAULT 25,
    connection_timeout_seconds INTEGER DEFAULT 30,
    idle_timeout_seconds INTEGER DEFAULT 300,

    -- Basic pool settings
    validate_connections BOOLEAN DEFAULT true,
    test_query VARCHAR(200) DEFAULT 'SELECT 1',
    max_lifetime_seconds INTEGER DEFAULT 1800,

    -- Pool status (manually maintained)
    pool_enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Simple connection allocation function (no real pooling)
CREATE OR REPLACE FUNCTION allocate_connection(
    pool_name_param VARCHAR(100),
    application_name_param VARCHAR(100)
) RETURNS TABLE (
    connection_id VARCHAR(100),
    allocation_success BOOLEAN,
    wait_time_ms INTEGER,
    error_message TEXT
) AS $$
DECLARE
    available_connections INTEGER;
    pool_config RECORD;
    new_connection_id VARCHAR(100);
    start_time TIMESTAMP := clock_timestamp();
    wait_timeout TIMESTAMP;
BEGIN

    -- Get pool configuration (basic validation)
    SELECT * INTO pool_config
    FROM connection_pool_config
    WHERE pool_name = pool_name_param AND pool_enabled = true;

    IF NOT FOUND THEN
        RETURN QUERY SELECT 
            NULL::VARCHAR(100),
            false,
            0,
            'Connection pool not found or disabled';
        RETURN;
    END IF;

    -- Count "available" connections (very basic simulation)
    SELECT COUNT(*) INTO available_connections
    FROM connection_usage_log
    WHERE connection_status = 'active'
    AND last_activity > CURRENT_TIMESTAMP - (pool_config.idle_timeout_seconds * INTERVAL '1 second');

    -- Simple allocation logic (doesn't actually create real connections)
    IF available_connections >= pool_config.max_connections THEN
        -- Wait for connection with timeout (simulated blocking)
        wait_timeout := start_time + (pool_config.connection_timeout_seconds * INTERVAL '1 second');

        WHILE clock_timestamp() < wait_timeout LOOP
            -- Check for available connections again
            SELECT COUNT(*) INTO available_connections
            FROM connection_usage_log
            WHERE connection_status = 'active'
            AND last_activity > CURRENT_TIMESTAMP - (pool_config.idle_timeout_seconds * INTERVAL '1 second');

            IF available_connections < pool_config.max_connections THEN
                EXIT;
            END IF;

            -- Simple wait (PostgreSQL doesn't have good sleep in functions)
            PERFORM pg_sleep(0.1);
        END LOOP;

        IF available_connections >= pool_config.max_connections THEN
            RETURN QUERY SELECT 
                NULL::VARCHAR(100),
                false,
                EXTRACT(MILLISECONDS FROM clock_timestamp() - start_time)::INTEGER,
                'Connection pool exhausted - timeout waiting for available connection';
            RETURN;
        END IF;
    END IF;

    -- "Allocate" a connection (just create a log entry)
    new_connection_id := 'conn_' || extract(epoch from now())::BIGINT || '_' || (random() * 10000)::INTEGER;

    INSERT INTO connection_usage_log (
        connection_id,
        application_name,
        database_name,
        username,
        connection_established,
        connection_status,
        last_activity
    ) VALUES (
        new_connection_id,
        application_name_param,
        pool_config.database_name,
        current_user,
        clock_timestamp(),
        'active',
        clock_timestamp()
    );

    RETURN QUERY SELECT 
        new_connection_id,
        true,
        EXTRACT(MILLISECONDS FROM clock_timestamp() - start_time)::INTEGER,
        NULL::TEXT;

EXCEPTION WHEN OTHERS THEN
    RETURN QUERY SELECT 
        NULL::VARCHAR(100),
        false,
        EXTRACT(MILLISECONDS FROM clock_timestamp() - start_time)::INTEGER,
        SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Manual connection health checking (limited effectiveness)
CREATE OR REPLACE FUNCTION check_connection_health() 
RETURNS TABLE (
    pool_name VARCHAR(100),
    total_connections INTEGER,
    active_connections INTEGER,
    idle_connections INTEGER,
    stale_connections INTEGER,
    avg_connection_age_minutes INTEGER,
    pool_utilization_percent DECIMAL(5,2),
    health_status VARCHAR(50)
) AS $$
BEGIN
    RETURN QUERY
    WITH connection_stats AS (
        SELECT 
            cpc.pool_name,
            COUNT(cul.connection_id) as total_connections,
            COUNT(*) FILTER (WHERE cul.connection_status = 'active' 
                           AND cul.last_activity > CURRENT_TIMESTAMP - INTERVAL '5 minutes') as active_connections,
            COUNT(*) FILTER (WHERE cul.connection_status = 'active' 
                           AND cul.last_activity <= CURRENT_TIMESTAMP - INTERVAL '5 minutes') as idle_connections,
            COUNT(*) FILTER (WHERE cul.last_activity < CURRENT_TIMESTAMP - INTERVAL '30 minutes') as stale_connections,
            AVG(EXTRACT(MINUTES FROM CURRENT_TIMESTAMP - cul.connection_established))::INTEGER as avg_age_minutes,
            cpc.max_connections

        FROM connection_pool_config cpc
        LEFT JOIN connection_usage_log cul ON cul.database_name = cpc.database_name
        WHERE cpc.pool_enabled = true
        GROUP BY cpc.pool_name, cpc.max_connections
    )
    SELECT 
        cs.pool_name,
        cs.total_connections,
        cs.active_connections,
        cs.idle_connections,
        cs.stale_connections,
        cs.avg_age_minutes,
        ROUND((cs.total_connections::DECIMAL / cs.max_connections) * 100, 2) as utilization_percent,

        -- Basic health assessment
        CASE 
            WHEN cs.stale_connections > cs.max_connections * 0.5 THEN 'unhealthy'
            WHEN cs.total_connections > cs.max_connections * 0.9 THEN 'stressed'
            WHEN cs.active_connections < 2 THEN 'underutilized'
            ELSE 'healthy'
        END as health_status

    FROM connection_stats cs;
END;
$$ LANGUAGE plpgsql;

-- Basic connection cleanup (manual process)
WITH stale_connections AS (
    SELECT 
        connection_id,
        connection_established,
        last_activity,
        EXTRACT(MINUTES FROM CURRENT_TIMESTAMP - last_activity) as idle_minutes

    FROM connection_usage_log
    WHERE connection_status = 'active'
    AND (
        last_activity < CURRENT_TIMESTAMP - INTERVAL '30 minutes'  -- Long idle
        OR connection_established < CURRENT_TIMESTAMP - INTERVAL '2 hours'  -- Old connections
        OR error_count > 5  -- Error-prone connections
    )
),
cleanup_summary AS (
    UPDATE connection_usage_log
    SET 
        connection_status = 'closed',
        connection_closed = CURRENT_TIMESTAMP,
        connection_duration_seconds = EXTRACT(SECONDS FROM CURRENT_TIMESTAMP - connection_established)::INTEGER
    WHERE connection_id IN (SELECT connection_id FROM stale_connections)
    RETURNING connection_id, connection_duration_seconds
)
SELECT 
    COUNT(*) as connections_cleaned,
    AVG(connection_duration_seconds) as avg_connection_lifetime_seconds,
    SUM(connection_duration_seconds) as total_connection_time_freed,

    -- Limited cleanup statistics
    COUNT(*) FILTER (WHERE connection_duration_seconds > 3600) as long_lived_connections_cleaned,
    COUNT(*) FILTER (WHERE connection_duration_seconds < 300) as short_lived_connections_cleaned

FROM cleanup_summary;

-- Problems with traditional connection management:
-- 1. No real connection pooling - just tracking and simulation
-- 2. Manual resource management with high maintenance overhead  
-- 3. Limited connection lifecycle automation and optimization
-- 4. No built-in load balancing or intelligent connection distribution
-- 5. Poor visibility into connection performance and resource utilization
-- 6. Manual scaling and configuration adjustments required
-- 7. No automatic connection validation or health checking
-- 8. Limited error handling and recovery mechanisms
-- 9. No support for advanced features like read preference or write concern optimization
-- 10. Complex application-level connection management logic required

-- Attempt at connection performance monitoring (very limited)
WITH connection_performance AS (
    SELECT 
        DATE_TRUNC('hour', cul.last_activity) as hour_bucket,
        cul.application_name,
        COUNT(*) as total_connections_used,
        AVG(cul.connection_duration_seconds) as avg_connection_lifetime,
        SUM(cul.queries_executed) as total_queries,

        -- Basic performance calculations
        CASE 
            WHEN SUM(cul.queries_executed) > 0 THEN
                AVG(cul.avg_query_time_ms)
            ELSE NULL
        END as overall_avg_query_time,

        COUNT(*) FILTER (WHERE cul.error_count > 0) as connections_with_errors,
        SUM(cul.error_count) as total_errors

    FROM connection_usage_log cul
    WHERE cul.last_activity >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
    AND cul.connection_status = 'closed'  -- Only analyze completed connections
    GROUP BY DATE_TRUNC('hour', cul.last_activity), cul.application_name
),
hourly_trends AS (
    SELECT 
        cp.*,
        LAG(total_connections_used) OVER (
            PARTITION BY application_name 
            ORDER BY hour_bucket
        ) as prev_hour_connections,
        LAG(overall_avg_query_time) OVER (
            PARTITION BY application_name 
            ORDER BY hour_bucket
        ) as prev_hour_query_time
    FROM connection_performance cp
)
SELECT 
    hour_bucket,
    application_name,
    total_connections_used,
    ROUND(avg_connection_lifetime::NUMERIC, 1) as avg_lifetime_seconds,
    total_queries,
    ROUND(overall_avg_query_time::NUMERIC, 2) as avg_query_time_ms,

    -- Error rates
    connections_with_errors,
    CASE 
        WHEN total_connections_used > 0 THEN
            ROUND((connections_with_errors::DECIMAL / total_connections_used) * 100, 2)
        ELSE 0
    END as connection_error_rate_percent,

    -- Trend indicators (very basic)
    CASE 
        WHEN prev_hour_connections IS NOT NULL AND prev_hour_connections > 0 THEN
            ROUND(((total_connections_used - prev_hour_connections)::DECIMAL / prev_hour_connections) * 100, 1)
        ELSE NULL
    END as connection_usage_change_percent,

    -- Performance assessment
    CASE 
        WHEN overall_avg_query_time > 1000 THEN 'slow'
        WHEN overall_avg_query_time > 500 THEN 'moderate'
        WHEN overall_avg_query_time < 100 THEN 'fast'
        ELSE 'normal'
    END as performance_assessment

FROM hourly_trends
WHERE total_connections_used > 0
ORDER BY hour_bucket DESC, application_name;

-- Traditional limitations:
-- 1. No actual connection pooling implementation - only monitoring and simulation
-- 2. Manual configuration and tuning without automatic optimization
-- 3. Limited connection health monitoring and automated recovery
-- 4. No intelligent load balancing or connection routing
-- 5. Poor integration with application frameworks and ORMs
-- 6. Limited scalability for high-concurrency applications
-- 7. No advanced features like connection warming or pre-allocation
-- 8. Manual error handling and connection recovery logic
-- 9. No built-in support for distributed database topologies
-- 10. Complex troubleshooting and performance analysis requirements

// MongoDB Advanced Connection Pooling - comprehensive connection management with intelligent optimization
const { MongoClient, ServerApiVersion } = require('mongodb');
const { EventEmitter } = require('events');

// Comprehensive MongoDB Connection Pool Manager
class AdvancedConnectionPoolManager extends EventEmitter {
  constructor(connectionString, config = {}) {
    super();
    this.connectionString = connectionString;
    this.pools = new Map();
    this.monitoringIntervals = new Map();

    // Advanced connection pooling configuration
    this.config = {
      // Connection pool size management
      minPoolSize: config.minPoolSize || 5,
      maxPoolSize: config.maxPoolSize || 50,
      maxIdleTimeMS: config.maxIdleTimeMS || 300000, // 5 minutes
      maxConnecting: config.maxConnecting || 10,

      // Connection lifecycle management
      connectTimeoutMS: config.connectTimeoutMS || 30000,
      socketTimeoutMS: config.socketTimeoutMS || 120000,
      serverSelectionTimeoutMS: config.serverSelectionTimeoutMS || 30000,
      heartbeatFrequencyMS: config.heartbeatFrequencyMS || 10000,

      // Performance optimization
      maxStalenessSeconds: config.maxStalenessSeconds || 90,
      readPreference: config.readPreference || 'primaryPreferred',
      readConcern: config.readConcern || { level: 'majority' },
      writeConcern: config.writeConcern || { w: 'majority', j: true },

      // Connection pool monitoring
      enableMonitoring: config.enableMonitoring !== false,
      monitoringInterval: config.monitoringInterval || 30000,
      enableConnectionEvents: config.enableConnectionEvents !== false,
      enablePerformanceTracking: config.enablePerformanceTracking !== false,

      // Advanced features
      enableLoadBalancing: config.enableLoadBalancing || false,
      enableAutomaticFailover: config.enableAutomaticFailover !== false,
      enableConnectionWarming: config.enableConnectionWarming || false,
      enableIntelligentRouting: config.enableIntelligentRouting || false,

      // Error handling and recovery
      maxRetries: config.maxRetries || 3,
      retryDelayMS: config.retryDelayMS || 1000,
      enableCircuitBreaker: config.enableCircuitBreaker || false,
      circuitBreakerThreshold: config.circuitBreakerThreshold || 10,
      circuitBreakerTimeout: config.circuitBreakerTimeout || 60000,

      // Security and authentication
      authSource: config.authSource || 'admin',
      authMechanism: config.authMechanism || 'SCRAM-SHA-256',
      tlsAllowInvalidCertificates: config.tlsAllowInvalidCertificates || false,
      tlsAllowInvalidHostnames: config.tlsAllowInvalidHostnames || false
    };

    // Performance and monitoring metrics
    this.metrics = {
      connectionPools: new Map(),
      performanceHistory: [],
      errorStats: new Map(),
      operationStats: new Map()
    };

    // Circuit breaker state for error handling
    this.circuitBreaker = {
      state: 'closed', // closed, open, half-open
      failures: 0,
      lastFailureTime: null,
      nextRetryTime: null
    };

    this.initializeConnectionPooling();
  }

  async initializeConnectionPooling() {
    console.log('Initializing advanced MongoDB connection pooling system...');

    try {
      // Setup primary connection pool
      await this.createConnectionPool('primary', this.connectionString);

      // Initialize connection monitoring
      if (this.config.enableMonitoring) {
        await this.setupConnectionMonitoring();
      }

      // Setup connection event handlers
      if (this.config.enableConnectionEvents) {
        await this.setupConnectionEventHandlers();
      }

      // Initialize performance tracking
      if (this.config.enablePerformanceTracking) {
        await this.setupPerformanceTracking();
      }

      // Enable connection warming if configured
      if (this.config.enableConnectionWarming) {
        await this.warmupConnectionPool('primary');
      }

      console.log('Advanced connection pooling system initialized successfully');

    } catch (error) {
      console.error('Error initializing connection pooling:', error);
      throw error;
    }
  }

  async createConnectionPool(poolName, connectionString, poolConfig = {}) {
    console.log(`Creating connection pool: ${poolName}`);

    try {
      // Merge pool-specific configuration with global configuration
      const mergedConfig = {
        ...this.config,
        ...poolConfig
      };

      // Configure MongoDB client options with advanced connection pooling
      const clientOptions = {
        // Connection pool configuration
        minPoolSize: mergedConfig.minPoolSize,
        maxPoolSize: mergedConfig.maxPoolSize,
        maxIdleTimeMS: mergedConfig.maxIdleTimeMS,
        maxConnecting: mergedConfig.maxConnecting,

        // Connection timeout configuration
        connectTimeoutMS: mergedConfig.connectTimeoutMS,
        socketTimeoutMS: mergedConfig.socketTimeoutMS,
        serverSelectionTimeoutMS: mergedConfig.serverSelectionTimeoutMS,
        heartbeatFrequencyMS: mergedConfig.heartbeatFrequencyMS,

        // Read and write preferences
        readPreference: mergedConfig.readPreference,
        readConcern: mergedConfig.readConcern,
        writeConcern: mergedConfig.writeConcern,
        maxStalenessSeconds: mergedConfig.maxStalenessSeconds,

        // Advanced features
        loadBalanced: mergedConfig.enableLoadBalancing,
        retryWrites: true,
        retryReads: true,

        // Security configuration
        authSource: mergedConfig.authSource,
        authMechanism: mergedConfig.authMechanism,
        tls: connectionString.includes('ssl=true') || connectionString.includes('+srv'),
        tlsAllowInvalidCertificates: mergedConfig.tlsAllowInvalidCertificates,
        tlsAllowInvalidHostnames: mergedConfig.tlsAllowInvalidHostnames,

        // Monitoring and logging
        monitorCommands: mergedConfig.enablePerformanceTracking,
        serverApi: {
          version: ServerApiVersion.v1,
          strict: true,
          deprecationErrors: false
        }
      };

      // Create MongoDB client with connection pooling
      const client = new MongoClient(connectionString, clientOptions);

      // Establish initial connection
      await client.connect();

      // Store pool information
      const poolInfo = {
        client: client,
        name: poolName,
        connectionString: connectionString,
        configuration: mergedConfig,
        createdAt: new Date(),

        // Connection pool statistics
        stats: {
          connectionsCreated: 0,
          connectionsClosed: 0,
          connectionsInUse: 0,
          connectionsAvailable: 0,
          totalOperations: 0,
          failedOperations: 0,
          averageOperationTime: 0,
          lastConnectionActivity: new Date()
        },

        // Health status
        healthStatus: 'healthy',
        lastHealthCheck: new Date()
      };

      this.pools.set(poolName, poolInfo);

      console.log(`Connection pool '${poolName}' created successfully with ${mergedConfig.maxPoolSize} max connections`);

      return poolInfo;

    } catch (error) {
      console.error(`Error creating connection pool '${poolName}':`, error);
      throw error;
    }
  }

  async setupConnectionEventHandlers() {
    console.log('Setting up comprehensive connection event handlers...');

    for (const [poolName, poolInfo] of this.pools.entries()) {
      const client = poolInfo.client;

      // Connection pool events
      client.on('connectionPoolCreated', (event) => {
        console.log(`Connection pool created: ${poolName}`, {
          address: event.address,
          options: event.options
        });

        this.metrics.connectionPools.set(poolName, {
          ...this.metrics.connectionPools.get(poolName),
          poolCreated: new Date(),
          address: event.address
        });

        this.emit('poolCreated', { poolName, event });
      });

      client.on('connectionCreated', (event) => {
        console.log(`Connection created in pool '${poolName}':`, {
          connectionId: event.connectionId,
          address: event.address
        });

        poolInfo.stats.connectionsCreated++;
        poolInfo.stats.lastConnectionActivity = new Date();

        this.emit('connectionCreated', { poolName, event });
      });

      client.on('connectionReady', (event) => {
        console.log(`Connection ready in pool '${poolName}':`, {
          connectionId: event.connectionId,
          address: event.address
        });

        this.emit('connectionReady', { poolName, event });
      });

      client.on('connectionClosed', (event) => {
        console.log(`Connection closed in pool '${poolName}':`, {
          connectionId: event.connectionId,
          reason: event.reason,
          address: event.address
        });

        poolInfo.stats.connectionsClosed++;
        poolInfo.stats.lastConnectionActivity = new Date();

        this.emit('connectionClosed', { poolName, event });
      });

      client.on('connectionCheckOutStarted', (event) => {
        this.emit('connectionCheckOutStarted', { poolName, event });
      });

      client.on('connectionCheckedOut', (event) => {
        poolInfo.stats.connectionsInUse++;
        poolInfo.stats.connectionsAvailable--;

        this.emit('connectionCheckedOut', { poolName, event });
      });

      client.on('connectionCheckOutFailed', (event) => {
        console.error(`Connection checkout failed in pool '${poolName}':`, {
          reason: event.reason,
          address: event.address
        });

        poolInfo.stats.failedOperations++;

        this.emit('connectionCheckOutFailed', { poolName, event });
      });

      client.on('connectionCheckedIn', (event) => {
        poolInfo.stats.connectionsInUse--;
        poolInfo.stats.connectionsAvailable++;

        this.emit('connectionCheckedIn', { poolName, event });
      });

      // Server discovery and monitoring events
      client.on('serverDescriptionChanged', (event) => {
        console.log(`Server description changed for pool '${poolName}':`, {
          address: event.address,
          newDescription: event.newDescription.type,
          previousDescription: event.previousDescription.type
        });

        this.emit('serverDescriptionChanged', { poolName, event });
      });

      client.on('topologyDescriptionChanged', (event) => {
        console.log(`Topology changed for pool '${poolName}':`, {
          newTopologyType: event.newDescription.type,
          previousTopologyType: event.previousDescription.type
        });

        this.emit('topologyDescriptionChanged', { poolName, event });
      });

      // Command monitoring events (if performance tracking enabled)
      if (this.config.enablePerformanceTracking) {
        client.on('commandStarted', (event) => {
          this.trackCommandStart(poolName, event);
        });

        client.on('commandSucceeded', (event) => {
          this.trackCommandSuccess(poolName, event);
        });

        client.on('commandFailed', (event) => {
          this.trackCommandFailure(poolName, event);
        });
      }
    }
  }

  trackCommandStart(poolName, event) {
    // Store command start time for performance tracking
    if (!this.metrics.operationStats.has(poolName)) {
      this.metrics.operationStats.set(poolName, new Map());
    }

    const poolStats = this.metrics.operationStats.get(poolName);
    poolStats.set(event.requestId, {
      command: event.commandName,
      startTime: Date.now(),
      connectionId: event.connectionId
    });
  }

  trackCommandSuccess(poolName, event) {
    const poolStats = this.metrics.operationStats.get(poolName);
    const commandInfo = poolStats.get(event.requestId);

    if (commandInfo) {
      const duration = Date.now() - commandInfo.startTime;

      // Update pool statistics
      const poolInfo = this.pools.get(poolName);
      poolInfo.stats.totalOperations++;

      // Update average operation time
      const currentAvg = poolInfo.stats.averageOperationTime;
      const totalOps = poolInfo.stats.totalOperations;
      poolInfo.stats.averageOperationTime = ((currentAvg * (totalOps - 1)) + duration) / totalOps;

      // Store performance history
      this.metrics.performanceHistory.push({
        poolName: poolName,
        command: event.commandName,
        duration: duration,
        timestamp: new Date(),
        success: true
      });

      // Clean up tracking
      poolStats.delete(event.requestId);

      this.emit('commandCompleted', {
        poolName,
        command: event.commandName,
        duration,
        success: true
      });
    }
  }

  trackCommandFailure(poolName, event) {
    const poolStats = this.metrics.operationStats.get(poolName);
    const commandInfo = poolStats.get(event.requestId);

    if (commandInfo) {
      const duration = Date.now() - commandInfo.startTime;

      // Update failure statistics
      const poolInfo = this.pools.get(poolName);
      poolInfo.stats.failedOperations++;

      // Update error statistics
      if (!this.metrics.errorStats.has(poolName)) {
        this.metrics.errorStats.set(poolName, new Map());
      }

      const errorStats = this.metrics.errorStats.get(poolName);
      const errorKey = `${event.failure.codeName || 'UnknownError'}`;
      const currentCount = errorStats.get(errorKey) || 0;
      errorStats.set(errorKey, currentCount + 1);

      // Store performance history
      this.metrics.performanceHistory.push({
        poolName: poolName,
        command: event.commandName,
        duration: duration,
        timestamp: new Date(),
        success: false,
        error: event.failure
      });

      // Update circuit breaker if enabled
      if (this.config.enableCircuitBreaker) {
        this.updateCircuitBreaker(event.failure);
      }

      // Clean up tracking
      poolStats.delete(event.requestId);

      this.emit('commandFailed', {
        poolName,
        command: event.commandName,
        duration,
        error: event.failure
      });
    }
  }

  async setupConnectionMonitoring() {
    console.log('Setting up connection pool monitoring...');

    const monitoringInterval = setInterval(async () => {
      try {
        await this.performHealthChecks();
        await this.collectConnectionMetrics();
        await this.optimizeConnectionPools();

      } catch (error) {
        console.error('Error during connection monitoring:', error);
      }
    }, this.config.monitoringInterval);

    this.monitoringIntervals.set('health_monitoring', monitoringInterval);
  }

  async performHealthChecks() {
    for (const [poolName, poolInfo] of this.pools.entries()) {
      try {
        const client = poolInfo.client;

        // Perform ping to check connection health
        const pingStart = Date.now();
        await client.db('admin').admin().ping();
        const pingDuration = Date.now() - pingStart;

        // Update health status
        poolInfo.healthStatus = pingDuration < 1000 ? 'healthy' : 
                              pingDuration < 5000 ? 'degraded' : 'unhealthy';
        poolInfo.lastHealthCheck = new Date();
        poolInfo.lastPingDuration = pingDuration;

        // Emit health status event
        this.emit('healthCheck', {
          poolName,
          healthStatus: poolInfo.healthStatus,
          pingDuration
        });

      } catch (error) {
        console.error(`Health check failed for pool '${poolName}':`, error);

        poolInfo.healthStatus = 'unhealthy';
        poolInfo.lastHealthCheck = new Date();
        poolInfo.lastError = error;

        this.emit('healthCheckFailed', { poolName, error });
      }
    }
  }

  async collectConnectionMetrics() {
    for (const [poolName, poolInfo] of this.pools.entries()) {
      try {
        const client = poolInfo.client;

        // Get server status for connection metrics
        const serverStatus = await client.db('admin').admin().serverStatus();
        const connections = serverStatus.connections;

        // Update pool metrics
        if (!this.metrics.connectionPools.has(poolName)) {
          this.metrics.connectionPools.set(poolName, {});
        }

        const poolMetrics = this.metrics.connectionPools.get(poolName);
        Object.assign(poolMetrics, {
          current: connections.current,
          available: connections.available,
          totalCreated: connections.totalCreated,
          active: connections.active || 0,

          // Pool-specific statistics
          poolUtilization: (poolInfo.stats.connectionsInUse / this.config.maxPoolSize) * 100,
          averageOperationTime: poolInfo.stats.averageOperationTime,
          operationsPerSecond: this.calculateOperationsPerSecond(poolName),
          errorRate: this.calculateErrorRate(poolName),

          lastUpdated: new Date()
        });

      } catch (error) {
        console.warn(`Error collecting metrics for pool '${poolName}':`, error.message);
      }
    }
  }

  calculateOperationsPerSecond(poolName) {
    const now = Date.now();
    const oneSecondAgo = now - 1000;

    const recentOperations = this.metrics.performanceHistory.filter(
      op => op.poolName === poolName && 
            op.timestamp.getTime() > oneSecondAgo
    );

    return recentOperations.length;
  }

  calculateErrorRate(poolName) {
    const now = Date.now();
    const oneMinuteAgo = now - 60000;

    const recentOperations = this.metrics.performanceHistory.filter(
      op => op.poolName === poolName && 
            op.timestamp.getTime() > oneMinuteAgo
    );

    if (recentOperations.length === 0) return 0;

    const failedOperations = recentOperations.filter(op => !op.success);
    return (failedOperations.length / recentOperations.length) * 100;
  }

  async optimizeConnectionPools() {
    for (const [poolName, poolInfo] of this.pools.entries()) {
      try {
        const poolMetrics = this.metrics.connectionPools.get(poolName);
        if (!poolMetrics) continue;

        // Analyze pool performance and suggest optimizations
        const optimizationRecommendations = this.analyzePoolPerformance(poolName, poolMetrics);

        // Apply automatic optimizations if enabled
        if (optimizationRecommendations.length > 0) {
          console.log(`Optimization recommendations for pool '${poolName}':`, optimizationRecommendations);

          this.emit('optimizationRecommendations', {
            poolName,
            recommendations: optimizationRecommendations
          });
        }

      } catch (error) {
        console.warn(`Error optimizing pool '${poolName}':`, error.message);
      }
    }
  }

  analyzePoolPerformance(poolName, poolMetrics) {
    const recommendations = [];

    // High utilization check
    if (poolMetrics.poolUtilization > 90) {
      recommendations.push({
        type: 'scale_up',
        priority: 'high',
        message: 'Pool utilization is very high, consider increasing maxPoolSize',
        currentValue: this.config.maxPoolSize,
        suggestedValue: Math.min(this.config.maxPoolSize * 1.5, 100)
      });
    }

    // Low utilization check
    if (poolMetrics.poolUtilization < 10 && this.config.maxPoolSize > 10) {
      recommendations.push({
        type: 'scale_down',
        priority: 'low',
        message: 'Pool utilization is low, consider decreasing maxPoolSize to save resources',
        currentValue: this.config.maxPoolSize,
        suggestedValue: Math.max(this.config.maxPoolSize * 0.8, 5)
      });
    }

    // High error rate check
    if (poolMetrics.errorRate > 5) {
      recommendations.push({
        type: 'investigate_errors',
        priority: 'high',
        message: 'High error rate detected, investigate connection issues',
        errorRate: poolMetrics.errorRate
      });
    }

    // Slow operations check
    if (poolMetrics.averageOperationTime > 1000) {
      recommendations.push({
        type: 'performance_tuning',
        priority: 'medium',
        message: 'Average operation time is high, consider query optimization or read preference tuning',
        averageTime: poolMetrics.averageOperationTime
      });
    }

    return recommendations;
  }

  async warmupConnectionPool(poolName) {
    console.log(`Warming up connection pool: ${poolName}`);

    try {
      const poolInfo = this.pools.get(poolName);
      if (!poolInfo) {
        throw new Error(`Connection pool '${poolName}' not found`);
      }

      const client = poolInfo.client;
      const minConnections = this.config.minPoolSize;

      // Pre-create minimum number of connections
      const warmupPromises = [];
      for (let i = 0; i < minConnections; i++) {
        warmupPromises.push(
          client.db('admin').admin().ping().catch(error => {
            console.warn(`Warmup connection ${i} failed:`, error.message);
          })
        );
      }

      await Promise.allSettled(warmupPromises);

      console.log(`Connection pool '${poolName}' warmed up successfully`);

      this.emit('poolWarmedUp', { poolName, minConnections });

    } catch (error) {
      console.error(`Error warming up pool '${poolName}':`, error);
      throw error;
    }
  }

  async getConnectionPoolStats(poolName = null) {
    const stats = {};

    const poolsToCheck = poolName ? [poolName] : Array.from(this.pools.keys());

    for (const name of poolsToCheck) {
      const poolInfo = this.pools.get(name);
      const poolMetrics = this.metrics.connectionPools.get(name);

      if (poolInfo) {
        stats[name] = {
          // Basic pool information
          configuration: {
            minPoolSize: this.config.minPoolSize,
            maxPoolSize: this.config.maxPoolSize,
            maxIdleTimeMS: this.config.maxIdleTimeMS,
            readPreference: this.config.readPreference
          },

          // Current pool statistics
          current: poolMetrics ? {
            connections: {
              current: poolMetrics.current || 0,
              available: poolMetrics.available || 0,
              inUse: poolInfo.stats.connectionsInUse,
              created: poolInfo.stats.connectionsCreated,
              closed: poolInfo.stats.connectionsClosed
            },

            performance: {
              utilization: poolMetrics.poolUtilization || 0,
              averageOperationTime: poolInfo.stats.averageOperationTime,
              operationsPerSecond: poolMetrics.operationsPerSecond || 0,
              totalOperations: poolInfo.stats.totalOperations,
              failedOperations: poolInfo.stats.failedOperations,
              errorRate: poolMetrics.errorRate || 0
            },

            health: {
              status: poolInfo.healthStatus,
              lastHealthCheck: poolInfo.lastHealthCheck,
              lastPingDuration: poolInfo.lastPingDuration || null,
              lastError: poolInfo.lastError ? poolInfo.lastError.message : null
            }
          } : null,

          // Historical data
          recentPerformance: this.getRecentPerformanceData(name),
          errorBreakdown: this.getErrorBreakdown(name)
        };
      }
    }

    return poolName ? stats[poolName] : stats;
  }

  getRecentPerformanceData(poolName, minutes = 10) {
    const cutoff = Date.now() - (minutes * 60 * 1000);

    return this.metrics.performanceHistory
      .filter(op => op.poolName === poolName && op.timestamp.getTime() > cutoff)
      .map(op => ({
        command: op.command,
        duration: op.duration,
        timestamp: op.timestamp,
        success: op.success
      }));
  }

  getErrorBreakdown(poolName) {
    const errorStats = this.metrics.errorStats.get(poolName);
    if (!errorStats) return {};

    const breakdown = {};
    for (const [errorType, count] of errorStats.entries()) {
      breakdown[errorType] = count;
    }

    return breakdown;
  }

  updateCircuitBreaker(error) {
    const now = Date.now();

    this.circuitBreaker.failures++;
    this.circuitBreaker.lastFailureTime = now;

    if (this.circuitBreaker.failures >= this.config.circuitBreakerThreshold) {
      if (this.circuitBreaker.state !== 'open') {
        console.warn('Circuit breaker opened due to high failure rate');
        this.circuitBreaker.state = 'open';
        this.circuitBreaker.nextRetryTime = now + this.config.circuitBreakerTimeout;

        this.emit('circuitBreakerOpened', {
          failures: this.circuitBreaker.failures,
          threshold: this.config.circuitBreakerThreshold
        });
      }
    }
  }

  async closeConnectionPool(poolName) {
    console.log(`Closing connection pool: ${poolName}`);

    try {
      const poolInfo = this.pools.get(poolName);
      if (!poolInfo) {
        throw new Error(`Connection pool '${poolName}' not found`);
      }

      // Close the MongoDB client
      await poolInfo.client.close();

      // Remove from pools
      this.pools.delete(poolName);

      // Clean up metrics
      this.metrics.connectionPools.delete(poolName);
      this.metrics.errorStats.delete(poolName);
      this.metrics.operationStats.delete(poolName);

      console.log(`Connection pool '${poolName}' closed successfully`);

      this.emit('poolClosed', { poolName });

    } catch (error) {
      console.error(`Error closing connection pool '${poolName}':`, error);
      throw error;
    }
  }

  async closeAllPools() {
    console.log('Closing all connection pools...');

    const closePromises = [];
    for (const poolName of this.pools.keys()) {
      closePromises.push(this.closeConnectionPool(poolName));
    }

    await Promise.allSettled(closePromises);

    // Clear monitoring intervals
    for (const [name, interval] of this.monitoringIntervals.entries()) {
      clearInterval(interval);
    }
    this.monitoringIntervals.clear();

    console.log('All connection pools closed');
    this.emit('allPoolsClosed');
  }
}

// Benefits of MongoDB Advanced Connection Pooling:
// - Intelligent connection pool sizing with automatic optimization
// - Comprehensive connection lifecycle management and monitoring
// - Advanced performance tracking and metrics collection
// - Built-in error handling with circuit breaker patterns
// - Automatic failover and recovery mechanisms
// - Health monitoring with proactive connection management
// - Load balancing and intelligent connection routing
// - Production-ready connection pooling with minimal configuration
// - Real-time performance analysis and optimization recommendations
// - SQL-compatible connection management through QueryLeaf integration

module.exports = {
  AdvancedConnectionPoolManager
};