MongoDB Indexing Strategies and Compound Indexes: Advanced Performance Optimization for Scalable Database Operations

-- Traditional PostgreSQL indexing with limited flexibility and optimization challenges

-- Basic single-column indexes with poor compound query support
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_status ON users (status);
CREATE INDEX idx_users_created_at ON users (created_at);
CREATE INDEX idx_users_country ON users (country);

-- Simple compound index with fixed column order limitations
CREATE INDEX idx_users_status_country ON users (status, country);

-- Complex query requiring multiple index scans and poor optimization
SELECT 
  u.user_id,
  u.email,
  u.first_name,
  u.last_name,
  u.status,
  u.country,
  u.created_at,
  u.last_login_at,
  COUNT(o.order_id) as order_count,
  SUM(o.total_amount) as total_spent,
  MAX(o.order_date) as last_order_date
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.status IN ('active', 'premium', 'trial')
  AND u.country IN ('US', 'CA', 'UK', 'AU', 'DE', 'FR')
  AND u.created_at >= CURRENT_DATE - INTERVAL '2 years'
  AND u.last_login_at >= CURRENT_DATE - INTERVAL '30 days'
  AND (u.email LIKE '%@gmail.com' OR u.email LIKE '%@hotmail.com')
  AND u.subscription_tier IS NOT NULL
GROUP BY u.user_id, u.email, u.first_name, u.last_name, u.status, u.country, u.created_at, u.last_login_at
HAVING COUNT(o.order_id) > 0
ORDER BY total_spent DESC, last_order_date DESC
LIMIT 100;

-- PostgreSQL EXPLAIN showing inefficient index usage:
-- 
-- Limit  (cost=45234.67..45234.92 rows=100 width=128) (actual time=1247.123..1247.189 rows=100 loops=1)
--   ->  Sort  (cost=45234.67..45789.23 rows=221824 width=128) (actual time=1247.121..1247.156 rows=100 loops=1)
--         Sort Key: (sum(o.total_amount)) DESC, (max(o.order_date)) DESC
--         Sort Method: top-N heapsort  Memory: 67kB
--         ->  HashAggregate  (cost=38234.56..40456.80 rows=221824 width=128) (actual time=1156.789..1201.234 rows=12789 loops=1)
--               Group Key: u.user_id, u.email, u.first_name, u.last_name, u.status, u.country, u.created_at, u.last_login_at
--               ->  Hash Left Join  (cost=12345.67..32890.45 rows=221824 width=96) (actual time=89.456..567.123 rows=87645 loops=1)
--                     Hash Cond: (u.user_id = o.user_id)
--                     ->  Bitmap Heap Scan on users u  (cost=3456.78..8901.23 rows=45678 width=88) (actual time=34.567..123.456 rows=23456 loops=1)
--                           Recheck Cond: ((status = ANY ('{active,premium,trial}'::text[])) AND 
--                                         (country = ANY ('{US,CA,UK,AU,DE,FR}'::text[])) AND 
--                                         (created_at >= (CURRENT_DATE - '2 years'::interval)) AND 
--                                         (last_login_at >= (CURRENT_DATE - '30 days'::interval)))
--                           Filter: ((subscription_tier IS NOT NULL) AND 
--                                   ((email ~~ '%@gmail.com'::text) OR (email ~~ '%@hotmail.com'::text)))
--                           Rows Removed by Filter: 12789
--                           Heap Blocks: exact=1234 lossy=234
--                           ->  BitmapOr  (cost=3456.78..3456.78 rows=45678 width=0) (actual time=33.890..33.891 rows=0 loops=1)
--                                 ->  Bitmap Index Scan on idx_users_status_country  (cost=0.00..1234.56 rows=15678 width=0) (actual time=12.345..12.345 rows=18901 loops=1)
--                                       Index Cond: ((status = ANY ('{active,premium,trial}'::text[])) AND 
--                                                   (country = ANY ('{US,CA,UK,AU,DE,FR}'::text[])))
--                                 ->  Bitmap Index Scan on idx_users_created_at  (cost=0.00..1890.23 rows=25678 width=0) (actual time=18.234..18.234 rows=34567 loops=1)
--                                       Index Cond: (created_at >= (CURRENT_DATE - '2 years'::interval))
--                                 ->  Bitmap Index Scan on idx_users_last_login  (cost=0.00..331.99 rows=4322 width=0) (actual time=3.311..3.311 rows=8765 loops=1)
--                                       Index Cond: (last_login_at >= (CURRENT_DATE - '30 days'::interval))
--                     ->  Hash  (cost=7890.45..7890.45 rows=234567 width=24) (actual time=54.889..54.889 rows=198765 loops=1)
--                           Buckets: 262144  Batches: 1  Memory Usage: 11234kB
--                           ->  Seq Scan on orders o  (cost=0.00..7890.45 rows=234567 width=24) (actual time=0.234..28.901 rows=198765 loops=1)
-- Planning Time: 4.567 ms
-- Execution Time: 1247.567 ms

-- Problems with traditional PostgreSQL indexing:
-- 1. Multiple bitmap index scans required due to lack of comprehensive compound index
-- 2. Expensive BitmapOr operations combining multiple index results
-- 3. Large number of rows removed by filter conditions not supported by indexes
-- 4. Complex compound indexes difficult to design for multiple query patterns
-- 5. Index bloat and maintenance overhead with many single-column indexes
-- 6. Poor support for partial indexes and conditional filtering
-- 7. Limited flexibility in query optimization and index selection
-- 8. Difficulty optimizing for mixed equality/range/pattern matching conditions

-- Attempt to create better compound index
CREATE INDEX idx_users_comprehensive ON users (
  status, country, created_at, last_login_at, subscription_tier, email
);

-- Problems with large compound indexes:
-- 1. Index becomes very large and expensive to maintain
-- 2. Only efficient for queries that follow exact prefix patterns
-- 3. Wasted space for queries that don't use all index columns
-- 4. Update performance degradation due to large index maintenance
-- 5. Limited effectiveness for partial field matching (email patterns)
-- 6. Poor selectivity when early columns have low cardinality

-- MySQL limitations are even more restrictive
CREATE INDEX idx_users_limited ON users (status, country, created_at);
-- MySQL compound index limitations:
-- - Maximum 16 columns per compound index
-- - 767 bytes total key length limit (InnoDB)
-- - Poor optimization for range queries on non-leading columns
-- - Limited partial index support
-- - Inefficient covering index implementation
-- - Basic query optimizer with limited compound index utilization

-- Alternative approach with covering indexes (PostgreSQL)
CREATE INDEX idx_users_covering ON users (status, country, created_at) 
INCLUDE (email, first_name, last_name, last_login_at, subscription_tier);

-- Covering index problems:
-- 1. Large storage overhead for included columns
-- 2. Still limited by leading column selectivity
-- 3. Expensive maintenance operations
-- 4. Complex index design decisions
-- 5. Poor performance for non-matching query patterns

// MongoDB Advanced Indexing Strategies - comprehensive compound index management and optimization
const { MongoClient } = require('mongodb');

const client = new MongoClient('mongodb://localhost:27017');
const db = client.db('advanced_ecommerce_platform');

// Advanced MongoDB indexing strategy and compound index optimization system
class MongoIndexOptimizer {
  constructor(db) {
    this.db = db;
    this.collections = {
      users: db.collection('users'),
      orders: db.collection('orders'),
      products: db.collection('products'),
      analytics: db.collection('analytics'),
      sessions: db.collection('sessions')
    };

    // Index optimization configuration
    this.indexingStrategies = {
      equalityFirst: true,        // ESR pattern - Equality, Sort, Range
      sortOptimization: true,     // Optimize for sort operations
      partialIndexes: true,       // Use partial indexes for selective filtering
      coveringIndexes: true,      // Create covering indexes where beneficial
      textSearchIndexes: true,    // Advanced text search capabilities
      geospatialIndexes: true,    // Location-based indexing
      ttlIndexes: true           // Time-based data expiration
    };

    this.performanceTargets = {
      maxQueryTimeMs: 100,
      minIndexSelectivity: 0.1,
      maxIndexSizeMB: 500,
      maxIndexesPerCollection: 10
    };

    this.indexAnalytics = new Map();
  }

  async implementComprehensiveIndexingStrategy(collectionName, queryPatterns) {
    console.log(`Implementing comprehensive indexing strategy for ${collectionName}...`);

    const collection = this.collections[collectionName];
    const existingIndexes = await collection.listIndexes().toArray();

    const indexingPlan = {
      collection: collectionName,
      queryPatterns: queryPatterns,
      existingIndexes: existingIndexes,
      recommendedIndexes: [],
      optimizationActions: [],
      performanceProjections: {}
    };

    // Analyze query patterns for optimal index design
    const queryAnalysis = await this.analyzeQueryPatterns(queryPatterns);

    // Generate compound index recommendations
    const compoundIndexes = await this.generateCompoundIndexes(queryAnalysis);

    // Design partial indexes for selective filtering
    const partialIndexes = await this.generatePartialIndexes(queryAnalysis);

    // Create covering indexes for frequently accessed projections
    const coveringIndexes = await this.generateCoveringIndexes(queryAnalysis);

    // Specialized indexes for specific data types and operations
    const specializedIndexes = await this.generateSpecializedIndexes(queryAnalysis);

    indexingPlan.recommendedIndexes = [
      ...compoundIndexes,
      ...partialIndexes, 
      ...coveringIndexes,
      ...specializedIndexes
    ];

    // Validate index recommendations against performance targets
    const validatedPlan = await this.validateIndexingPlan(collection, indexingPlan);

    // Execute index creation with comprehensive monitoring
    const implementationResult = await this.executeIndexingPlan(collection, validatedPlan);

    // Performance validation and optimization
    const performanceValidation = await this.validateIndexPerformance(collection, validatedPlan, queryPatterns);

    return {
      plan: validatedPlan,
      implementation: implementationResult,
      performance: performanceValidation,
      summary: {
        totalIndexes: validatedPlan.recommendedIndexes.length,
        compoundIndexes: compoundIndexes.length,
        partialIndexes: partialIndexes.length,
        coveringIndexes: coveringIndexes.length,
        specializedIndexes: specializedIndexes.length,
        estimatedPerformanceImprovement: this.calculatePerformanceImprovement(validatedPlan)
      }
    };
  }

  async analyzeQueryPatterns(queryPatterns) {
    console.log(`Analyzing ${queryPatterns.length} query patterns for index optimization...`);

    const analysis = {
      fieldUsage: new Map(),           // How often each field is used
      fieldCombinations: new Map(),    // Common field combinations
      filterTypes: new Map(),          // Types of filters (equality, range, etc.)
      sortPatterns: new Map(),         // Sort field combinations
      projectionPatterns: new Map(),   // Frequently requested projections
      selectivityEstimates: new Map()  // Estimated field selectivity
    };

    for (const pattern of queryPatterns) {
      // Analyze filter conditions
      this.analyzeFilterConditions(pattern.filter || {}, analysis);

      // Analyze sort requirements
      this.analyzeSortPatterns(pattern.sort || {}, analysis);

      // Analyze projection requirements
      this.analyzeProjectionPatterns(pattern.projection || {}, analysis);

      // Track query frequency for weighting
      const frequency = pattern.frequency || 1;
      this.updateFrequencyWeights(analysis, frequency);
    }

    // Calculate field selectivity estimates
    await this.estimateFieldSelectivity(analysis);

    // Identify optimal field combinations
    const optimalCombinations = this.identifyOptimalFieldCombinations(analysis);

    return {
      ...analysis,
      optimalCombinations: optimalCombinations,
      indexingRecommendations: this.generateIndexingRecommendations(analysis, optimalCombinations)
    };
  }

  analyzeFilterConditions(filter, analysis) {
    Object.entries(filter).forEach(([field, condition]) => {
      if (field.startsWith('$')) return; // Skip operators

      // Track field usage frequency
      const currentUsage = analysis.fieldUsage.get(field) || 0;
      analysis.fieldUsage.set(field, currentUsage + 1);

      // Categorize filter types
      const filterType = this.categorizeFilterType(condition);
      const currentFilterTypes = analysis.filterTypes.get(field) || new Set();
      currentFilterTypes.add(filterType);
      analysis.filterTypes.set(field, currentFilterTypes);

      // Track field combinations for compound indexes
      const otherFields = Object.keys(filter).filter(f => f !== field && !f.startsWith('$'));
      if (otherFields.length > 0) {
        const combination = [field, ...otherFields].sort().join(',');
        const currentCombinations = analysis.fieldCombinations.get(combination) || 0;
        analysis.fieldCombinations.set(combination, currentCombinations + 1);
      }
    });
  }

  categorizeFilterType(condition) {
    if (typeof condition === 'object' && condition !== null) {
      const operators = Object.keys(condition);

      if (operators.includes('$gte') || operators.includes('$gt') || 
          operators.includes('$lte') || operators.includes('$lt')) {
        return 'range';
      } else if (operators.includes('$in')) {
        return condition.$in.length <= 10 ? 'selective_in' : 'large_in';
      } else if (operators.includes('$regex')) {
        return 'pattern_match';
      } else if (operators.includes('$exists')) {
        return 'existence';
      } else if (operators.includes('$ne')) {
        return 'negation';
      } else {
        return 'complex';
      }
    } else {
      return 'equality';
    }
  }

  analyzeSortPatterns(sort, analysis) {
    if (Object.keys(sort).length === 0) return;

    const sortKey = Object.entries(sort)
      .map(([field, direction]) => `${field}:${direction}`)
      .join(',');

    const currentSort = analysis.sortPatterns.get(sortKey) || 0;
    analysis.sortPatterns.set(sortKey, currentSort + 1);
  }

  analyzeProjectionPatterns(projection, analysis) {
    if (!projection || Object.keys(projection).length === 0) return;

    const projectedFields = Object.keys(projection).filter(field => projection[field] === 1);
    const projectionKey = projectedFields.sort().join(',');

    if (projectionKey) {
      const currentProjection = analysis.projectionPatterns.get(projectionKey) || 0;
      analysis.projectionPatterns.set(projectionKey, currentProjection + 1);
    }
  }

  async generateCompoundIndexes(analysis) {
    console.log('Generating optimal compound index recommendations...');

    const compoundIndexes = [];

    // Sort field combinations by frequency and potential impact
    const sortedCombinations = Array.from(analysis.fieldCombinations.entries())
      .sort(([, a], [, b]) => b - a)
      .slice(0, 20); // Consider top 20 combinations

    for (const [fieldCombination, frequency] of sortedCombinations) {
      const fields = fieldCombination.split(',');

      // Apply ESR (Equality, Sort, Range) pattern optimization
      const optimizedIndex = this.optimizeIndexWithESRPattern(fields, analysis);

      if (optimizedIndex && this.validateIndexUtility(optimizedIndex, analysis)) {
        compoundIndexes.push({
          type: 'compound',
          name: `idx_${optimizedIndex.fields.map(f => f.field).join('_')}`,
          specification: this.buildIndexSpecification(optimizedIndex.fields),
          options: optimizedIndex.options,
          reasoning: optimizedIndex.reasoning,
          estimatedImpact: this.estimateIndexImpact(optimizedIndex, analysis),
          queryPatterns: this.identifyMatchingQueries(optimizedIndex, analysis),
          priority: this.calculateIndexPriority(optimizedIndex, frequency, analysis)
        });
      }
    }

    // Sort by priority and return top recommendations
    return compoundIndexes
      .sort((a, b) => b.priority - a.priority)
      .slice(0, this.performanceTargets.maxIndexesPerCollection);
  }

  optimizeIndexWithESRPattern(fields, analysis) {
    console.log(`Optimizing index for fields: ${fields.join(', ')} using ESR pattern...`);

    const optimizedFields = [];
    const fieldAnalysis = new Map();

    // Analyze each field's characteristics
    fields.forEach(field => {
      const filterTypes = analysis.filterTypes.get(field) || new Set();
      const usage = analysis.fieldUsage.get(field) || 0;
      const selectivity = analysis.selectivityEstimates.get(field) || 0.5;

      fieldAnalysis.set(field, {
        filterTypes: Array.from(filterTypes),
        usage: usage,
        selectivity: selectivity,
        isEquality: filterTypes.has('equality') || filterTypes.has('selective_in'),
        isRange: filterTypes.has('range'),
        isSort: this.isFieldUsedInSort(field, analysis),
        sortDirection: this.getSortDirection(field, analysis)
      });
    });

    // Step 1: Equality fields first (highest selectivity first)
    const equalityFields = fields
      .filter(field => fieldAnalysis.get(field).isEquality)
      .sort((a, b) => fieldAnalysis.get(b).selectivity - fieldAnalysis.get(a).selectivity);

    equalityFields.forEach(field => {
      const fieldInfo = fieldAnalysis.get(field);
      optimizedFields.push({
        field: field,
        direction: 1,
        type: 'equality',
        selectivity: fieldInfo.selectivity,
        reasoning: `Equality filter with ${(fieldInfo.selectivity * 100).toFixed(1)}% selectivity`
      });
    });

    // Step 2: Sort fields (maintaining sort direction)
    const sortFields = fields
      .filter(field => fieldAnalysis.get(field).isSort && !fieldAnalysis.get(field).isEquality)
      .sort((a, b) => fieldAnalysis.get(b).usage - fieldAnalysis.get(a).usage);

    sortFields.forEach(field => {
      const fieldInfo = fieldAnalysis.get(field);
      optimizedFields.push({
        field: field,
        direction: fieldInfo.sortDirection || 1,
        type: 'sort',
        selectivity: fieldInfo.selectivity,
        reasoning: `Sort field with ${fieldInfo.usage} usage frequency`
      });
    });

    // Step 3: Range fields last (lowest selectivity impact)
    const rangeFields = fields
      .filter(field => fieldAnalysis.get(field).isRange && 
                      !fieldAnalysis.get(field).isEquality && 
                      !fieldAnalysis.get(field).isSort)
      .sort((a, b) => fieldAnalysis.get(b).selectivity - fieldAnalysis.get(a).selectivity);

    rangeFields.forEach(field => {
      const fieldInfo = fieldAnalysis.get(field);
      optimizedFields.push({
        field: field,
        direction: 1,
        type: 'range',
        selectivity: fieldInfo.selectivity,
        reasoning: `Range filter with ${(fieldInfo.selectivity * 100).toFixed(1)}% selectivity`
      });
    });

    // Validate and return optimized index
    if (optimizedFields.length === 0) return null;

    return {
      fields: optimizedFields,
      options: this.generateIndexOptions(optimizedFields, analysis),
      reasoning: `ESR-optimized compound index: ${optimizedFields.length} fields arranged for optimal query performance`,
      estimatedSelectivity: this.calculateCompoundSelectivity(optimizedFields),
      supportedQueryTypes: this.identifySupportedQueryTypes(optimizedFields, analysis)
    };
  }

  async generatePartialIndexes(analysis) {
    console.log('Generating partial index recommendations for selective filtering...');

    const partialIndexes = [];

    // Identify fields with high selectivity potential
    const selectiveFields = Array.from(analysis.selectivityEstimates.entries())
      .filter(([field, selectivity]) => selectivity < this.performanceTargets.minIndexSelectivity)
      .sort(([, a], [, b]) => a - b); // Lower selectivity first (more selective)

    for (const [field, selectivity] of selectiveFields) {
      const filterTypes = analysis.filterTypes.get(field) || new Set();
      const usage = analysis.fieldUsage.get(field) || 0;

      // Generate partial filter conditions
      const partialFilters = this.generatePartialFilterConditions(field, filterTypes, analysis);

      for (const partialFilter of partialFilters) {
        const partialIndex = {
          type: 'partial',
          name: `idx_${field}_${partialFilter.suffix}`,
          specification: { [field]: 1 },
          options: {
            partialFilterExpression: partialFilter.expression,
            background: true
          },
          reasoning: partialFilter.reasoning,
          estimatedReduction: partialFilter.estimatedReduction,
          applicableQueries: partialFilter.applicableQueries,
          priority: this.calculatePartialIndexPriority(field, usage, selectivity, partialFilter)
        };

        if (this.validatePartialIndexUtility(partialIndex, analysis)) {
          partialIndexes.push(partialIndex);
        }
      }
    }

    return partialIndexes
      .sort((a, b) => b.priority - a.priority)
      .slice(0, Math.floor(this.performanceTargets.maxIndexesPerCollection / 3));
  }

  generatePartialFilterConditions(field, filterTypes, analysis) {
    const partialFilters = [];

    // Status/category fields with selective values
    if (filterTypes.has('equality') || filterTypes.has('selective_in')) {
      partialFilters.push({
        expression: { [field]: { $in: ['active', 'premium', 'verified'] } },
        suffix: 'active_premium',
        reasoning: `Partial index for high-value ${field} categories`,
        estimatedReduction: 0.7,
        applicableQueries: [`${field} equality matches for active/premium users`]
      });
    }

    // Date fields with recency focus
    if (filterTypes.has('range') && field.includes('date') || field.includes('time')) {
      partialFilters.push({
        expression: { [field]: { $gte: new Date(Date.now() - 90 * 24 * 60 * 60 * 1000) } },
        suffix: 'recent_90d',
        reasoning: `Partial index for recent ${field} within 90 days`,
        estimatedReduction: 0.8,
        applicableQueries: [`Recent ${field} range queries`]
      });
    }

    // Numeric fields with value thresholds
    if (filterTypes.has('range') && (field.includes('amount') || field.includes('count') || field.includes('score'))) {
      partialFilters.push({
        expression: { [field]: { $gt: 0 } },
        suffix: 'positive_values',
        reasoning: `Partial index excluding zero/null ${field} values`,
        estimatedReduction: 0.6,
        applicableQueries: [`${field} range queries for positive values`]
      });
    }

    return partialFilters;
  }

  async generateCoveringIndexes(analysis) {
    console.log('Generating covering index recommendations for query optimization...');

    const coveringIndexes = [];

    // Analyze projection patterns to identify covering index opportunities
    const projectionAnalysis = Array.from(analysis.projectionPatterns.entries())
      .sort(([, a], [, b]) => b - a)
      .slice(0, 10); // Top 10 projection patterns

    for (const [projectionKey, frequency] of projectionAnalysis) {
      const projectedFields = projectionKey.split(',');

      // Find queries that could benefit from covering indexes
      const candidateQueries = this.identifyConveringIndexCandidates(projectedFields, analysis);

      if (candidateQueries.length > 0) {
        const coveringIndex = this.designCoveringIndex(projectedFields, candidateQueries, analysis);

        if (coveringIndex && this.validateCoveringIndexBenefit(coveringIndex, analysis)) {
          coveringIndexes.push({
            type: 'covering',
            name: `idx_covering_${coveringIndex.keyFields.join('_')}`,
            specification: coveringIndex.specification,
            options: coveringIndex.options,
            reasoning: coveringIndex.reasoning,
            coveredQueries: candidateQueries.length,
            projectedFields: projectedFields,
            estimatedImpact: this.estimateCoveringIndexImpact(coveringIndex, frequency),
            priority: this.calculateCoveringIndexPriority(coveringIndex, frequency, candidateQueries.length)
          });
        }
      }
    }

    return coveringIndexes
      .sort((a, b) => b.priority - a.priority)
      .slice(0, Math.floor(this.performanceTargets.maxIndexesPerCollection / 4));
  }

  designCoveringIndex(projectedFields, candidateQueries, analysis) {
    // Analyze filter and sort patterns from candidate queries
    const filterFields = new Set();
    const sortFields = new Map();

    candidateQueries.forEach(query => {
      Object.keys(query.filter || {}).forEach(field => {
        if (!field.startsWith('$')) {
          filterFields.add(field);
        }
      });

      Object.entries(query.sort || {}).forEach(([field, direction]) => {
        sortFields.set(field, direction);
      });
    });

    // Design optimal key structure
    const keyFields = [];
    const includeFields = [];

    // Add filter fields to key (equality first, then range)
    const equalityFields = Array.from(filterFields).filter(field => {
      const filterTypes = analysis.filterTypes.get(field) || new Set();
      return filterTypes.has('equality') || filterTypes.has('selective_in');
    });

    const rangeFields = Array.from(filterFields).filter(field => {
      const filterTypes = analysis.filterTypes.get(field) || new Set();
      return filterTypes.has('range');
    });

    // Add equality fields to key
    equalityFields.forEach(field => {
      keyFields.push(field);
    });

    // Add sort fields to key
    sortFields.forEach((direction, field) => {
      if (!keyFields.includes(field)) {
        keyFields.push(field);
      }
    });

    // Add range fields to key
    rangeFields.forEach(field => {
      if (!keyFields.includes(field)) {
        keyFields.push(field);
      }
    });

    // Add remaining projected fields as included fields
    projectedFields.forEach(field => {
      if (!keyFields.includes(field)) {
        includeFields.push(field);
      }
    });

    if (keyFields.length === 0) return null;

    // Build index specification
    const specification = {};
    keyFields.forEach(field => {
      const direction = sortFields.get(field) || 1;
      specification[field] = direction;
    });

    return {
      keyFields: keyFields,
      includeFields: includeFields,
      specification: specification,
      options: {
        background: true,
        // Include non-key fields for covering capability
        ...(includeFields.length > 0 && { includeFields: includeFields })
      },
      reasoning: `Covering index with ${keyFields.length} key fields and ${includeFields.length} included fields`,
      estimatedCoverage: this.calculateQueryCoverage(keyFields, includeFields, candidateQueries)
    };
  }

  async generateSpecializedIndexes(analysis) {
    console.log('Generating specialized index recommendations...');

    const specializedIndexes = [];

    // Text search indexes for string fields with pattern matching
    const textFields = this.identifyTextSearchFields(analysis);
    textFields.forEach(textField => {
      specializedIndexes.push({
        type: 'text',
        name: `idx_text_${textField.field}`,
        specification: { [textField.field]: 'text' },
        options: {
          background: true,
          default_language: 'english',
          weights: { [textField.field]: textField.weight }
        },
        reasoning: `Text search index for ${textField.field} pattern matching`,
        applicableQueries: textField.queries,
        priority: textField.priority
      });
    });

    // Geospatial indexes for location data
    const geoFields = this.identifyGeospatialFields(analysis);
    geoFields.forEach(geoField => {
      specializedIndexes.push({
        type: 'geospatial',
        name: `idx_geo_${geoField.field}`,
        specification: { [geoField.field]: '2dsphere' },
        options: {
          background: true,
          '2dsphereIndexVersion': 3
        },
        reasoning: `Geospatial index for ${geoField.field} location queries`,
        applicableQueries: geoField.queries,
        priority: geoField.priority
      });
    });

    // TTL indexes for time-based data expiration
    const ttlFields = this.identifyTTLFields(analysis);
    ttlFields.forEach(ttlField => {
      specializedIndexes.push({
        type: 'ttl',
        name: `idx_ttl_${ttlField.field}`,
        specification: { [ttlField.field]: 1 },
        options: {
          background: true,
          expireAfterSeconds: ttlField.expireAfterSeconds
        },
        reasoning: `TTL index for automatic ${ttlField.field} data expiration`,
        expirationPeriod: ttlField.expirationPeriod,
        priority: ttlField.priority
      });
    });

    // Sparse indexes for fields with many null values
    const sparseFields = this.identifySparseFields(analysis);
    sparseFields.forEach(sparseField => {
      specializedIndexes.push({
        type: 'sparse',
        name: `idx_sparse_${sparseField.field}`,
        specification: { [sparseField.field]: 1 },
        options: {
          background: true,
          sparse: true
        },
        reasoning: `Sparse index for ${sparseField.field} excluding null values`,
        nullPercentage: sparseField.nullPercentage,
        priority: sparseField.priority
      });
    });

    return specializedIndexes
      .sort((a, b) => b.priority - a.priority)
      .slice(0, Math.floor(this.performanceTargets.maxIndexesPerCollection / 2));
  }

  async executeIndexingPlan(collection, plan) {
    console.log(`Executing indexing plan for ${collection.collectionName}...`);

    const results = {
      successful: [],
      failed: [],
      skipped: [],
      totalTime: 0
    };

    const startTime = Date.now();

    for (const index of plan.recommendedIndexes) {
      try {
        console.log(`Creating index: ${index.name}`);

        // Check if index already exists
        const existingIndexes = await collection.listIndexes().toArray();
        const indexExists = existingIndexes.some(existing => existing.name === index.name);

        if (indexExists) {
          console.log(`Index ${index.name} already exists, skipping...`);
          results.skipped.push({
            name: index.name,
            reason: 'Index already exists'
          });
          continue;
        }

        // Create the index
        const indexStartTime = Date.now();
        await collection.createIndex(index.specification, {
          name: index.name,
          ...index.options
        });
        const indexCreationTime = Date.now() - indexStartTime;

        results.successful.push({
          name: index.name,
          type: index.type,
          specification: index.specification,
          creationTime: indexCreationTime,
          estimatedImpact: index.estimatedImpact
        });

        console.log(`Index ${index.name} created successfully in ${indexCreationTime}ms`);

      } catch (error) {
        console.error(`Failed to create index ${index.name}:`, error.message);
        results.failed.push({
          name: index.name,
          type: index.type,
          error: error.message,
          specification: index.specification
        });
      }
    }

    results.totalTime = Date.now() - startTime;

    console.log(`Index creation completed in ${results.totalTime}ms`);
    console.log(`Successful: ${results.successful.length}, Failed: ${results.failed.length}, Skipped: ${results.skipped.length}`);

    return results;
  }

  async validateIndexPerformance(collection, plan, queryPatterns) {
    console.log('Validating index performance with test queries...');

    const validation = {
      queries: [],
      summary: {
        totalQueries: queryPatterns.length,
        improvedQueries: 0,
        avgImprovementPct: 0,
        significantImprovements: 0
      }
    };

    for (const pattern of queryPatterns.slice(0, 20)) { // Test top 20 patterns
      try {
        // Execute query with explain to get performance metrics
        const collection_handle = this.collections[collection.collectionName] || collection;

        let cursor;
        if (pattern.aggregation) {
          cursor = collection_handle.aggregate(pattern.aggregation);
        } else {
          cursor = collection_handle.find(pattern.filter || {});
          if (pattern.sort) cursor.sort(pattern.sort);
          if (pattern.limit) cursor.limit(pattern.limit);
          if (pattern.projection) cursor.project(pattern.projection);
        }

        const explainResult = await cursor.explain('executionStats');

        const queryValidation = {
          pattern: pattern.name || 'Unnamed query',
          executionTimeMs: explainResult.executionStats?.executionTimeMillis || 0,
          totalDocsExamined: explainResult.executionStats?.totalDocsExamined || 0,
          totalDocsReturned: explainResult.executionStats?.totalDocsReturned || 0,
          indexesUsed: this.extractIndexNames(explainResult),
          efficiency: this.calculateQueryEfficiency(explainResult),
          grade: this.assignPerformanceGrade(explainResult),
          improvement: this.calculateImprovement(pattern, explainResult)
        };

        validation.queries.push(queryValidation);

        if (queryValidation.improvement > 0) {
          validation.summary.improvedQueries++;
          validation.summary.avgImprovementPct += queryValidation.improvement;
        }

        if (queryValidation.improvement > 50) {
          validation.summary.significantImprovements++;
        }

      } catch (error) {
        console.warn(`Query validation failed for pattern: ${pattern.name}`, error.message);
        validation.queries.push({
          pattern: pattern.name || 'Unnamed query',
          error: error.message,
          success: false
        });
      }
    }

    if (validation.summary.improvedQueries > 0) {
      validation.summary.avgImprovementPct /= validation.summary.improvedQueries;
    }

    console.log(`Performance validation completed: ${validation.summary.improvedQueries}/${validation.summary.totalQueries} queries improved`);
    console.log(`Average improvement: ${validation.summary.avgImprovementPct.toFixed(1)}%`);
    console.log(`Significant improvements: ${validation.summary.significantImprovements}`);

    return validation;
  }

  // Helper methods for advanced index analysis and optimization

  buildIndexSpecification(fields) {
    const spec = {};
    fields.forEach(field => {
      spec[field.field] = field.direction;
    });
    return spec;
  }

  generateIndexOptions(fields, analysis) {
    return {
      background: true,
      ...(this.shouldUsePartialFilter(fields, analysis) && {
        partialFilterExpression: this.buildOptimalPartialFilter(fields, analysis)
      })
    };
  }

  isFieldUsedInSort(field, analysis) {
    for (const [sortPattern] of analysis.sortPatterns) {
      if (sortPattern.includes(`${field}:`)) {
        return true;
      }
    }
    return false;
  }

  getSortDirection(field, analysis) {
    for (const [sortPattern] of analysis.sortPatterns) {
      const fieldPattern = sortPattern.split(',').find(pattern => pattern.startsWith(`${field}:`));
      if (fieldPattern) {
        return parseInt(fieldPattern.split(':')[1]) || 1;
      }
    }
    return 1;
  }

  calculateCompoundSelectivity(fields) {
    // Estimate compound selectivity using field independence assumption
    return fields.reduce((selectivity, field) => {
      return selectivity * (field.selectivity || 0.1);
    }, 1);
  }

  validateIndexUtility(index, analysis) {
    // Validate that index provides meaningful benefit
    const estimatedSelectivity = this.calculateCompoundSelectivity(index.fields);
    const supportedQueries = this.identifyMatchingQueries(index, analysis);

    return estimatedSelectivity < 0.5 && supportedQueries.length > 0;
  }

  identifyMatchingQueries(index, analysis) {
    // Simplified query matching logic
    const matchingQueries = [];
    const indexFields = new Set(index.fields.map(f => f.field));

    // Check field combinations that would benefit from this index
    for (const [fieldCombination, frequency] of analysis.fieldCombinations) {
      const queryFields = new Set(fieldCombination.split(','));
      const overlap = [...indexFields].filter(field => queryFields.has(field));

      if (overlap.length >= 2) { // At least 2 fields overlap
        matchingQueries.push({
          fields: fieldCombination,
          frequency: frequency,
          coverage: overlap.length / indexFields.size
        });
      }
    }

    return matchingQueries;
  }

  calculateIndexPriority(index, frequency, analysis) {
    const baseScore = frequency * 10;
    const selectivityBonus = (1 - index.estimatedSelectivity) * 50;
    const fieldCountPenalty = index.fields.length * 5;

    return Math.max(0, baseScore + selectivityBonus - fieldCountPenalty);
  }

  calculatePerformanceImprovement(plan) {
    // Simplified improvement estimation
    const baseImprovement = plan.recommendedIndexes.length * 15; // 15% per index
    const compoundBonus = plan.recommendedIndexes.filter(idx => idx.type === 'compound').length * 25;
    const partialBonus = plan.recommendedIndexes.filter(idx => idx.type === 'partial').length * 35;

    return Math.min(90, baseImprovement + compoundBonus + partialBonus);
  }

  extractIndexNames(explainResult) {
    const indexes = new Set();

    const extractFromStage = (stage) => {
      if (stage.indexName) {
        indexes.add(stage.indexName);
      }
      if (stage.inputStage) {
        extractFromStage(stage.inputStage);
      }
      if (stage.inputStages) {
        stage.inputStages.forEach(extractFromStage);
      }
    };

    if (explainResult.executionStats?.executionStages) {
      extractFromStage(explainResult.executionStats.executionStages);
    }

    return Array.from(indexes);
  }

  calculateQueryEfficiency(explainResult) {
    const stats = explainResult.executionStats;
    if (!stats) return 0;

    const examined = stats.totalDocsExamined || 0;
    const returned = stats.totalDocsReturned || 0;

    return examined > 0 ? returned / examined : 1;
  }

  assignPerformanceGrade(explainResult) {
    const efficiency = this.calculateQueryEfficiency(explainResult);
    const executionTime = explainResult.executionStats?.executionTimeMillis || 0;
    const hasIndexScan = this.extractIndexNames(explainResult).length > 0;

    let score = 0;

    // Efficiency scoring
    if (efficiency >= 0.8) score += 40;
    else if (efficiency >= 0.5) score += 30;
    else if (efficiency >= 0.2) score += 20;
    else if (efficiency >= 0.1) score += 10;

    // Execution time scoring
    if (executionTime <= 50) score += 35;
    else if (executionTime <= 100) score += 25;
    else if (executionTime <= 250) score += 15;
    else if (executionTime <= 500) score += 5;

    // Index usage scoring
    if (hasIndexScan) score += 25;

    if (score >= 85) return 'A';
    else if (score >= 70) return 'B';
    else if (score >= 50) return 'C';
    else if (score >= 30) return 'D';
    else return 'F';
  }

  calculateImprovement(pattern, explainResult) {
    // Simplified improvement calculation
    const efficiency = this.calculateQueryEfficiency(explainResult);
    const executionTime = explainResult.executionStats?.executionTimeMillis || 0;
    const hasIndexScan = this.extractIndexNames(explainResult).length > 0;

    let improvementScore = 0;

    if (hasIndexScan) improvementScore += 30;
    if (efficiency > 0.5) improvementScore += 40;
    if (executionTime < 100) improvementScore += 30;

    return Math.min(100, improvementScore);
  }

  // Additional helper methods for specialized index types

  identifyTextSearchFields(analysis) {
    const textFields = [];

    analysis.filterTypes.forEach((types, field) => {
      if (types.has('pattern_match') && 
          (field.includes('name') || field.includes('title') || field.includes('description'))) {
        textFields.push({
          field: field,
          weight: analysis.fieldUsage.get(field) || 1,
          queries: [`Text search on ${field}`],
          priority: (analysis.fieldUsage.get(field) || 0) * 10
        });
      }
    });

    return textFields;
  }

  identifyGeospatialFields(analysis) {
    const geoFields = [];

    analysis.fieldUsage.forEach((usage, field) => {
      if (field.includes('location') || field.includes('coordinates') || 
          field.includes('lat') || field.includes('lng') || field.includes('geo')) {
        geoFields.push({
          field: field,
          queries: [`Geospatial queries on ${field}`],
          priority: usage * 15
        });
      }
    });

    return geoFields;
  }

  identifyTTLFields(analysis) {
    const ttlFields = [];

    analysis.fieldUsage.forEach((usage, field) => {
      if (field.includes('expires') || field.includes('expire') || 
          field === 'createdAt' || field === 'updatedAt') {
        ttlFields.push({
          field: field,
          expireAfterSeconds: this.getExpireAfterSeconds(field),
          expirationPeriod: this.getExpirationPeriod(field),
          priority: usage * 5
        });
      }
    });

    return ttlFields;
  }

  identifySparseFields(analysis) {
    const sparseFields = [];

    // Fields that are likely to have many null values
    const potentialSparseFields = ['phone', 'middle_name', 'company', 'notes', 'optional_field'];

    analysis.fieldUsage.forEach((usage, field) => {
      if (potentialSparseFields.some(sparse => field.includes(sparse))) {
        sparseFields.push({
          field: field,
          nullPercentage: 0.6, // Estimated
          priority: usage * 8
        });
      }
    });

    return sparseFields;
  }

  getExpireAfterSeconds(field) {
    const expirationMap = {
      'session': 86400,        // 1 day
      'temp': 3600,           // 1 hour  
      'cache': 1800,          // 30 minutes
      'token': 3600,          // 1 hour
      'verification': 86400,   // 1 day
      'expires': 0            // Use field value
    };

    for (const [key, seconds] of Object.entries(expirationMap)) {
      if (field.includes(key)) {
        return seconds;
      }
    }

    return 86400; // Default 1 day
  }

  getExpirationPeriod(field) {
    const expireAfter = this.getExpireAfterSeconds(field);
    if (expireAfter >= 86400) return `${Math.floor(expireAfter / 86400)} days`;
    if (expireAfter >= 3600) return `${Math.floor(expireAfter / 3600)} hours`;
    return `${Math.floor(expireAfter / 60)} minutes`;
  }

  async estimateFieldSelectivity(analysis) {
    // Simplified selectivity estimation
    // In production, this would use actual data sampling

    analysis.fieldUsage.forEach((usage, field) => {
      let estimatedSelectivity = 0.5; // Default

      // Status/enum fields typically have low cardinality
      if (field.includes('status') || field.includes('type') || field.includes('category')) {
        estimatedSelectivity = 0.1;
      }
      // ID fields have high cardinality
      else if (field.includes('id') || field.includes('_id')) {
        estimatedSelectivity = 0.9;
      }
      // Email fields have high cardinality
      else if (field.includes('email')) {
        estimatedSelectivity = 0.8;
      }
      // Date fields vary based on range
      else if (field.includes('date') || field.includes('time')) {
        estimatedSelectivity = 0.3;
      }

      analysis.selectivityEstimates.set(field, estimatedSelectivity);
    });
  }

  identifyOptimalFieldCombinations(analysis) {
    const combinations = [];

    // Sort combinations by frequency and expected performance impact
    const sortedCombinations = Array.from(analysis.fieldCombinations.entries())
      .sort(([, a], [, b]) => b - a);

    sortedCombinations.forEach(([combination, frequency]) => {
      const fields = combination.split(',');
      const totalSelectivity = fields.reduce((product, field) => {
        return product * (analysis.selectivityEstimates.get(field) || 0.5);
      }, 1);

      combinations.push({
        fields: fields,
        frequency: frequency,
        selectivity: totalSelectivity,
        score: frequency * (1 - totalSelectivity) * 100,
        reasoning: `Combination of ${fields.length} fields with ${frequency} usage frequency`
      });
    });

    return combinations
      .sort((a, b) => b.score - a.score)
      .slice(0, 15);
  }

  generateIndexingRecommendations(analysis, optimalCombinations) {
    return {
      topFieldCombinations: optimalCombinations.slice(0, 5),
      highUsageFields: Array.from(analysis.fieldUsage.entries())
        .sort(([, a], [, b]) => b - a)
        .slice(0, 10)
        .map(([field, usage]) => ({ field, usage })),
      selectiveFields: Array.from(analysis.selectivityEstimates.entries())
        .filter(([, selectivity]) => selectivity < 0.2)
        .sort(([, a], [, b]) => a - b)
        .map(([field, selectivity]) => ({ field, selectivity })),
      commonSortPatterns: Array.from(analysis.sortPatterns.entries())
        .sort(([, a], [, b]) => b - a)
        .slice(0, 5)
        .map(([pattern, frequency]) => ({ pattern, frequency }))
    };
  }
}

// Benefits of MongoDB Advanced Indexing Strategies:
// - Comprehensive compound index design using ESR (Equality, Sort, Range) optimization patterns
// - Intelligent partial indexing for selective filtering and reduced storage overhead
// - Sophisticated covering index generation for complete query optimization
// - Specialized index support for text search, geospatial, TTL, and sparse data patterns
// - Automated index performance validation and impact measurement
// - Production-ready index creation with background processing and error handling
// - Advanced query pattern analysis and field combination optimization
// - Integration with MongoDB's native indexing capabilities and query optimizer
// - Comprehensive performance monitoring and index effectiveness tracking
// - SQL-compatible index management through QueryLeaf integration

module.exports = {
  MongoIndexOptimizer
};