Blog

-- Traditional SQL approach - multiple queries and complex joins

-- PostgreSQL complex analytics query with performance challenges
WITH user_segments AS (
  SELECT 
    user_id,
    email,
    registration_date,
    subscription_tier,

    -- User activity aggregation (expensive subquery)
    (SELECT COUNT(*) FROM user_activities ua WHERE ua.user_id = u.user_id) as total_activities,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) as total_orders,
    (SELECT COALESCE(SUM(o.total_amount), 0) FROM orders o WHERE o.user_id = u.user_id) as lifetime_value,

    -- Recent activity indicators (more expensive subqueries)
    (SELECT COUNT(*) FROM user_activities ua 
     WHERE ua.user_id = u.user_id 
       AND ua.created_at >= CURRENT_TIMESTAMP - INTERVAL '30 days') as recent_activities,
    (SELECT COUNT(*) FROM orders o 
     WHERE o.user_id = u.user_id 
       AND o.created_at >= CURRENT_TIMESTAMP - INTERVAL '30 days') as recent_orders,

    -- Engagement scoring (complex calculation)
    CASE 
      WHEN (SELECT COUNT(*) FROM user_activities ua WHERE ua.user_id = u.user_id AND ua.created_at >= CURRENT_TIMESTAMP - INTERVAL '7 days') > 10 THEN 'high'
      WHEN (SELECT COUNT(*) FROM user_activities ua WHERE ua.user_id = u.user_id AND ua.created_at >= CURRENT_TIMESTAMP - INTERVAL '30 days') > 5 THEN 'medium'
      ELSE 'low'
    END as engagement_level

  FROM users u
  WHERE u.status = 'active'
),

order_analytics AS (
  SELECT 
    o.user_id,
    COUNT(*) as order_count,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value,
    MAX(o.created_at) as last_order_date,

    -- Product category analysis (expensive join)
    (SELECT string_agg(DISTINCT p.category, ',') 
     FROM order_items oi 
     JOIN products p ON oi.product_id = p.product_id 
     WHERE oi.order_id = o.order_id) as purchased_categories,

    -- Time-based patterns (complex calculations)
    EXTRACT(DOW FROM o.created_at) as order_day_of_week,
    EXTRACT(HOUR FROM o.created_at) as order_hour,

    -- Seasonality analysis
    CASE 
      WHEN EXTRACT(MONTH FROM o.created_at) IN (12, 1, 2) THEN 'winter'
      WHEN EXTRACT(MONTH FROM o.created_at) IN (3, 4, 5) THEN 'spring'
      WHEN EXTRACT(MONTH FROM o.created_at) IN (6, 7, 8) THEN 'summer'
      ELSE 'fall'
    END as season

  FROM orders o
  WHERE o.status = 'completed'
    AND o.created_at >= CURRENT_TIMESTAMP - INTERVAL '1 year'
  GROUP BY o.user_id, EXTRACT(DOW FROM o.created_at), EXTRACT(HOUR FROM o.created_at),
    CASE 
      WHEN EXTRACT(MONTH FROM o.created_at) IN (12, 1, 2) THEN 'winter'
      WHEN EXTRACT(MONTH FROM o.created_at) IN (3, 4, 5) THEN 'spring'  
      WHEN EXTRACT(MONTH FROM o.created_at) IN (6, 7, 8) THEN 'summer'
      ELSE 'fall'
    END
),

product_preferences AS (
  -- Complex product affinity analysis
  SELECT 
    o.user_id,
    p.category,
    COUNT(*) as category_purchases,
    SUM(oi.quantity * oi.unit_price) as category_spend,

    -- Preference scoring
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY COUNT(*) DESC) as category_rank,

    -- Purchase timing patterns
    AVG(EXTRACT(EPOCH FROM (o.created_at - LAG(o.created_at) OVER (PARTITION BY o.user_id, p.category ORDER BY o.created_at)))) / 86400 as avg_days_between_category_purchases

  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  WHERE o.status = 'completed'
  GROUP BY o.user_id, p.category
),

final_analytics AS (
  SELECT 
    us.user_id,
    us.email,
    us.subscription_tier,
    us.total_activities,
    us.total_orders,
    us.lifetime_value,
    us.engagement_level,

    -- Order analytics
    COALESCE(oa.order_count, 0) as recent_order_count,
    COALESCE(oa.total_spent, 0) as recent_total_spent,
    COALESCE(oa.avg_order_value, 0) as recent_avg_order_value,

    -- Product preferences (expensive array aggregation)
    ARRAY(
      SELECT pp.category 
      FROM product_preferences pp 
      WHERE pp.user_id = us.user_id 
        AND pp.category_rank <= 3
      ORDER BY pp.category_rank
    ) as top_product_categories,

    -- Customer lifetime value prediction (complex calculation)
    CASE
      WHEN us.lifetime_value > 1000 AND us.recent_orders > 2 THEN us.lifetime_value * 1.2
      WHEN us.lifetime_value > 500 AND us.recent_activities > 10 THEN us.lifetime_value * 1.1
      ELSE us.lifetime_value
    END as predicted_ltv,

    -- Churn risk assessment
    CASE
      WHEN us.recent_activities = 0 AND oa.last_order_date < CURRENT_TIMESTAMP - INTERVAL '90 days' THEN 'high'
      WHEN us.recent_activities < 5 AND oa.last_order_date < CURRENT_TIMESTAMP - INTERVAL '45 days' THEN 'medium'
      ELSE 'low'
    END as churn_risk,

    -- Segmentation
    CASE
      WHEN us.lifetime_value > 1000 AND us.engagement_level = 'high' THEN 'vip'
      WHEN us.lifetime_value > 500 OR us.engagement_level = 'high' THEN 'loyal'
      WHEN us.total_orders > 0 THEN 'customer'
      ELSE 'prospect'
    END as user_segment

  FROM user_segments us
  LEFT JOIN order_analytics oa ON us.user_id = oa.user_id
)

SELECT *
FROM final_analytics
ORDER BY predicted_ltv DESC, engagement_level DESC;

-- Problems with traditional SQL aggregation:
-- 1. Multiple expensive subqueries for each user
-- 2. Complex joins across many tables with poor performance
-- 3. Difficult to optimize with multiple aggregation layers
-- 4. Limited support for complex nested data transformations
-- 5. Poor performance with large datasets due to multiple passes
-- 6. Complex window functions with high memory usage
-- 7. Difficulty handling semi-structured data efficiently
-- 8. Limited parallelization opportunities
-- 9. Complex query plans that are hard to optimize
-- 10. High resource usage for multi-stage analytics

-- MySQL approach (even more limited)
SELECT 
  u.user_id,
  u.email,
  u.subscription_tier,
  COUNT(DISTINCT ua.activity_id) as total_activities,
  COUNT(DISTINCT o.order_id) as total_orders,
  COALESCE(SUM(o.total_amount), 0) as lifetime_value,

  -- Limited aggregation capabilities
  CASE 
    WHEN COUNT(DISTINCT CASE WHEN ua.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN ua.activity_id END) > 10 THEN 'high'
    WHEN COUNT(DISTINCT CASE WHEN ua.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN ua.activity_id END) > 5 THEN 'medium'
    ELSE 'low'
  END as engagement_level,

  -- Basic JSON aggregation (limited functionality)
  JSON_ARRAYAGG(DISTINCT p.category) as purchased_categories

FROM users u
LEFT JOIN user_activities ua ON u.user_id = ua.user_id
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 'completed'
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE u.status = 'active'
GROUP BY u.user_id, u.email, u.subscription_tier;

-- MySQL limitations:
-- - Very limited JSON and array processing capabilities
-- - Poor window function support in older versions
-- - Basic aggregation functions with limited customization
-- - No sophisticated data transformation capabilities
-- - Limited support for complex analytical queries
-- - Poor performance with large result sets
-- - Minimal support for nested data structures

// MongoDB Aggregation Framework - optimized pipeline-based analytics
const { MongoClient } = require('mongodb');

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

// Advanced aggregation pipeline optimization strategies
class MongoAggregationOptimizer {
  constructor(db) {
    this.db = db;
    this.pipelineStats = new Map();
    this.indexRecommendations = [];
  }

  async optimizeUserAnalyticsPipeline() {
    console.log('Running optimized user analytics aggregation pipeline...');

    const users = this.db.collection('users');

    // Highly optimized aggregation pipeline
    const pipeline = [
      // Stage 1: Initial filtering - leverage indexes early
      {
        $match: {
          status: 'active',
          registrationDate: { 
            $gte: new Date(Date.now() - 365 * 24 * 60 * 60 * 1000) 
          }
        }
      },

      // Stage 2: Early projection to reduce document size
      {
        $project: {
          _id: 1,
          email: 1,
          subscriptionTier: 1,
          registrationDate: 1,
          lastLoginAt: 1,
          preferences: 1
        }
      },

      // Stage 3: Lookup user activities with optimized pipeline
      {
        $lookup: {
          from: 'user_activities',
          let: { userId: '$_id' },
          pipeline: [
            {
              $match: {
                $expr: { $eq: ['$userId', '$$userId'] },
                createdAt: { 
                  $gte: new Date(Date.now() - 365 * 24 * 60 * 60 * 1000) 
                }
              }
            },
            {
              $group: {
                _id: null,
                totalActivities: { $sum: 1 },
                recentActivities: {
                  $sum: {
                    $cond: {
                      if: { 
                        $gte: ['$createdAt', new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)] 
                      },
                      then: 1,
                      else: 0
                    }
                  }
                },
                weeklyActivities: {
                  $sum: {
                    $cond: {
                      if: { 
                        $gte: ['$createdAt', new Date(Date.now() - 7 * 24 * 60 * 60 * 1000)] 
                      },
                      then: 1,
                      else: 0
                    }
                  }
                },
                activityTypes: { $addToSet: '$activityType' },
                lastActivity: { $max: '$createdAt' },
                avgSessionDuration: { $avg: '$sessionDuration' }
              }
            }
          ],
          as: 'activityStats'
        }
      },

      // Stage 4: Lookup order data with aggregated calculations
      {
        $lookup: {
          from: 'orders',
          let: { userId: '$_id' },
          pipeline: [
            {
              $match: {
                $expr: { $eq: ['$userId', '$$userId'] },
                status: 'completed',
                createdAt: { 
                  $gte: new Date(Date.now() - 365 * 24 * 60 * 60 * 1000) 
                }
              }
            },
            {
              $group: {
                _id: null,
                totalOrders: { $sum: 1 },
                lifetimeValue: { $sum: '$totalAmount' },
                avgOrderValue: { $avg: '$totalAmount' },
                lastOrderDate: { $max: '$createdAt' },
                recentOrders: {
                  $sum: {
                    $cond: {
                      if: { 
                        $gte: ['$createdAt', new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)] 
                      },
                      then: 1,
                      else: 0
                    }
                  }
                },
                recentSpend: {
                  $sum: {
                    $cond: {
                      if: { 
                        $gte: ['$createdAt', new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)] 
                      },
                      then: '$totalAmount',
                      else: 0
                    }
                  }
                },
                orderDaysOfWeek: { $push: { $dayOfWeek: '$createdAt' } },
                orderHours: { $push: { $hour: '$createdAt' } },
                seasonality: {
                  $push: {
                    $switch: {
                      branches: [
                        { case: { $in: [{ $month: '$createdAt' }, [12, 1, 2]] }, then: 'winter' },
                        { case: { $in: [{ $month: '$createdAt' }, [3, 4, 5]] }, then: 'spring' },
                        { case: { $in: [{ $month: '$createdAt' }, [6, 7, 8]] }, then: 'summer' }
                      ],
                      default: 'fall'
                    }
                  }
                }
              }
            }
          ],
          as: 'orderStats'
        }
      },

      // Stage 5: Product preference analysis
      {
        $lookup: {
          from: 'orders',
          let: { userId: '$_id' },
          pipeline: [
            {
              $match: {
                $expr: { $eq: ['$userId', '$$userId'] },
                status: 'completed'
              }
            },
            {
              $unwind: '$items'
            },
            {
              $lookup: {
                from: 'products',
                localField: 'items.productId',
                foreignField: '_id',
                as: 'product'
              }
            },
            {
              $unwind: '$product'
            },
            {
              $group: {
                _id: '$product.category',
                categoryPurchases: { $sum: 1 },
                categorySpend: { $sum: '$items.totalPrice' },
                avgDaysBetweenPurchases: {
                  $avg: {
                    $divide: [
                      { $subtract: ['$createdAt', { $min: '$createdAt' }] },
                      86400000 // milliseconds to days
                    ]
                  }
                }
              }
            },
            {
              $sort: { categoryPurchases: -1 }
            },
            {
              $limit: 5 // Top 5 categories only
            },
            {
              $group: {
                _id: null,
                topCategories: {
                  $push: {
                    category: '$_id',
                    purchases: '$categoryPurchases',
                    spend: '$categorySpend',
                    avgDaysBetween: '$avgDaysBetweenPurchases'
                  }
                }
              }
            }
          ],
          as: 'productPreferences'
        }
      },

      // Stage 6: Flatten and calculate derived metrics
      {
        $addFields: {
          // Extract activity stats
          activityStats: { $arrayElemAt: ['$activityStats', 0] },
          orderStats: { $arrayElemAt: ['$orderStats', 0] },
          productPreferences: { $arrayElemAt: ['$productPreferences', 0] }
        }
      },

      // Stage 7: Advanced calculated fields and scoring
      {
        $addFields: {
          // Engagement scoring
          engagementScore: {
            $add: [
              { $multiply: [{ $ifNull: ['$activityStats.weeklyActivities', 0] }, 2] },
              { $multiply: [{ $ifNull: ['$activityStats.recentActivities', 0] }, 1] },
              { $multiply: [{ $ifNull: ['$orderStats.recentOrders', 0] }, 5] }
            ]
          },

          // Engagement level classification
          engagementLevel: {
            $switch: {
              branches: [
                {
                  case: { $gt: [{ $ifNull: ['$activityStats.weeklyActivities', 0] }, 10] },
                  then: 'high'
                },
                {
                  case: { $gt: [{ $ifNull: ['$activityStats.recentActivities', 0] }, 5] },
                  then: 'medium'
                }
              ],
              default: 'low'
            }
          },

          // Customer lifetime value prediction
          predictedLTV: {
            $switch: {
              branches: [
                {
                  case: {
                    $and: [
                      { $gt: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 1000] },
                      { $gt: [{ $ifNull: ['$orderStats.recentOrders', 0] }, 2] }
                    ]
                  },
                  then: { $multiply: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 1.2] }
                },
                {
                  case: {
                    $and: [
                      { $gt: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 500] },
                      { $gt: [{ $ifNull: ['$activityStats.recentActivities', 0] }, 10] }
                    ]
                  },
                  then: { $multiply: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 1.1] }
                }
              ],
              default: { $ifNull: ['$orderStats.lifetimeValue', 0] }
            }
          },

          // Churn risk assessment
          churnRisk: {
            $switch: {
              branches: [
                {
                  case: {
                    $and: [
                      { $eq: [{ $ifNull: ['$activityStats.recentActivities', 0] }, 0] },
                      {
                        $lt: [
                          { $ifNull: ['$orderStats.lastOrderDate', new Date(0)] },
                          new Date(Date.now() - 90 * 24 * 60 * 60 * 1000)
                        ]
                      }
                    ]
                  },
                  then: 'high'
                },
                {
                  case: {
                    $and: [
                      { $lt: [{ $ifNull: ['$activityStats.recentActivities', 0] }, 5] },
                      {
                        $lt: [
                          { $ifNull: ['$orderStats.lastOrderDate', new Date(0)] },
                          new Date(Date.now() - 45 * 24 * 60 * 60 * 1000)
                        ]
                      }
                    ]
                  },
                  then: 'medium'
                }
              ],
              default: 'low'
            }
          },

          // User segmentation
          userSegment: {
            $switch: {
              branches: [
                {
                  case: {
                    $and: [
                      { $gt: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 1000] },
                      { $eq: ['$engagementLevel', 'high'] }
                    ]
                  },
                  then: 'vip'
                },
                {
                  case: {
                    $or: [
                      { $gt: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 500] },
                      { $eq: ['$engagementLevel', 'high'] }
                    ]
                  },
                  then: 'loyal'
                },
                {
                  case: { $gt: [{ $ifNull: ['$orderStats.totalOrders', 0] }, 0] },
                  then: 'customer'
                }
              ],
              default: 'prospect'
            }
          },

          // Behavioral patterns
          behaviorPattern: {
            $let: {
              vars: {
                dayOfWeekMode: {
                  $arrayElemAt: [
                    {
                      $map: {
                        input: { $range: [1, 8] },
                        as: 'day',
                        in: {
                          day: '$$day',
                          count: {
                            $size: {
                              $filter: {
                                input: { $ifNull: ['$orderStats.orderDaysOfWeek', []] },
                                cond: { $eq: ['$$this', '$$day'] }
                              }
                            }
                          }
                        }
                      }
                    },
                    0
                  ]
                }
              },
              in: {
                preferredOrderDay: '$$dayOfWeekMode.day',
                orderFrequency: {
                  $cond: {
                    if: { $gt: [{ $ifNull: ['$orderStats.totalOrders', 0] }, 1] },
                    then: {
                      $divide: [
                        365,
                        {
                          $divide: [
                            { 
                              $subtract: [
                                { $ifNull: ['$orderStats.lastOrderDate', new Date()] },
                                '$registrationDate'
                              ] 
                            },
                            86400000
                          ]
                        }
                      ]
                    },
                    else: 0
                  }
                }
              }
            }
          }
        }
      },

      // Stage 8: Final projection with optimized field selection
      {
        $project: {
          _id: 1,
          email: 1,
          subscriptionTier: 1,
          registrationDate: 1,

          // Activity metrics
          totalActivities: { $ifNull: ['$activityStats.totalActivities', 0] },
          recentActivities: { $ifNull: ['$activityStats.recentActivities', 0] },
          weeklyActivities: { $ifNull: ['$activityStats.weeklyActivities', 0] },
          activityTypes: { $ifNull: ['$activityStats.activityTypes', []] },
          lastActivity: '$activityStats.lastActivity',
          avgSessionDuration: { $round: [{ $ifNull: ['$activityStats.avgSessionDuration', 0] }, 2] },

          // Order metrics
          totalOrders: { $ifNull: ['$orderStats.totalOrders', 0] },
          lifetimeValue: { $round: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 2] },
          avgOrderValue: { $round: [{ $ifNull: ['$orderStats.avgOrderValue', 0] }, 2] },
          lastOrderDate: '$orderStats.lastOrderDate',
          recentOrders: { $ifNull: ['$orderStats.recentOrders', 0] },
          recentSpend: { $round: [{ $ifNull: ['$orderStats.recentSpend', 0] }, 2] },

          // Product preferences
          topProductCategories: { 
            $ifNull: ['$productPreferences.topCategories', []] 
          },

          // Calculated metrics
          engagementScore: { $round: ['$engagementScore', 0] },
          engagementLevel: 1,
          predictedLTV: { $round: ['$predictedLTV', 2] },
          churnRisk: 1,
          userSegment: 1,
          behaviorPattern: 1,

          // Performance indicators
          isHighValue: { $gte: [{ $ifNull: ['$orderStats.lifetimeValue', 0] }, 1000] },
          isRecentlyActive: { 
            $gte: [{ $ifNull: ['$activityStats.recentActivities', 0] }, 5] 
          },
          isAtRisk: { $eq: ['$churnRisk', 'high'] },

          // Days since last activity/order
          daysSinceLastActivity: {
            $cond: {
              if: { $ne: ['$activityStats.lastActivity', null] },
              then: {
                $divide: [
                  { $subtract: [new Date(), '$activityStats.lastActivity'] },
                  86400000
                ]
              },
              else: 999
            }
          },
          daysSinceLastOrder: {
            $cond: {
              if: { $ne: ['$orderStats.lastOrderDate', null] },
              then: {
                $divide: [
                  { $subtract: [new Date(), '$orderStats.lastOrderDate'] },
                  86400000
                ]
              },
              else: 999
            }
          }
        }
      },

      // Stage 9: Sorting for optimal performance
      {
        $sort: {
          predictedLTV: -1,
          engagementScore: -1,
          lastActivity: -1
        }
      },

      // Stage 10: Optional limit for performance
      {
        $limit: 10000
      }
    ];

    // Execute pipeline with performance tracking
    const startTime = Date.now();
    const results = await users.aggregate(pipeline).toArray();
    const executionTime = Date.now() - startTime;

    console.log(`Aggregation completed in ${executionTime}ms, ${results.length} results`);

    // Track pipeline performance
    this.pipelineStats.set('userAnalytics', {
      executionTime,
      resultCount: results.length,
      pipelineStages: pipeline.length,
      timestamp: new Date()
    });

    return results;
  }

  async optimizeProductAnalyticsPipeline() {
    console.log('Running optimized product analytics aggregation pipeline...');

    const orders = this.db.collection('orders');

    const pipeline = [
      // Stage 1: Filter completed orders from last year
      {
        $match: {
          status: 'completed',
          createdAt: { 
            $gte: new Date(Date.now() - 365 * 24 * 60 * 60 * 1000) 
          }
        }
      },

      // Stage 2: Unwind order items for product-level analysis
      {
        $unwind: '$items'
      },

      // Stage 3: Lookup product details
      {
        $lookup: {
          from: 'products',
          localField: 'items.productId',
          foreignField: '_id',
          as: 'product'
        }
      },

      // Stage 4: Unwind product array
      {
        $unwind: '$product'
      },

      // Stage 5: Add time-based fields for analysis
      {
        $addFields: {
          orderMonth: { $month: '$createdAt' },
          orderDayOfWeek: { $dayOfWeek: '$createdAt' },
          orderHour: { $hour: '$createdAt' },
          season: {
            $switch: {
              branches: [
                { case: { $in: [{ $month: '$createdAt' }, [12, 1, 2]] }, then: 'winter' },
                { case: { $in: [{ $month: '$createdAt' }, [3, 4, 5]] }, then: 'spring' },
                { case: { $in: [{ $month: '$createdAt' }, [6, 7, 8]] }, then: 'summer' }
              ],
              default: 'fall'
            }
          },
          revenue: '$items.totalPrice',
          profit: {
            $subtract: ['$items.totalPrice', { $multiply: ['$items.quantity', '$product.cost'] }]
          },
          profitMargin: {
            $cond: {
              if: { $gt: ['$items.totalPrice', 0] },
              then: {
                $multiply: [
                  {
                    $divide: [
                      { $subtract: ['$items.totalPrice', { $multiply: ['$items.quantity', '$product.cost'] }] },
                      '$items.totalPrice'
                    ]
                  },
                  100
                ]
              },
              else: 0
            }
          }
        }
      },

      // Stage 6: Group by product for comprehensive analytics
      {
        $group: {
          _id: '$items.productId',
          productName: { $first: '$product.name' },
          category: { $first: '$product.category' },
          price: { $first: '$product.price' },
          cost: { $first: '$product.cost' },

          // Volume metrics
          totalSold: { $sum: '$items.quantity' },
          totalOrders: { $sum: 1 },
          uniqueCustomers: { $addToSet: '$userId' },

          // Revenue metrics
          totalRevenue: { $sum: '$revenue' },
          totalProfit: { $sum: '$profit' },
          avgOrderValue: { $avg: '$revenue' },
          avgProfitMargin: { $avg: '$profitMargin' },

          // Time-based patterns
          salesByMonth: {
            $push: {
              month: '$orderMonth',
              quantity: '$items.quantity',
              revenue: '$revenue'
            }
          },
          salesByDayOfWeek: {
            $push: {
              dayOfWeek: '$orderDayOfWeek',
              quantity: '$items.quantity'
            }
          },
          salesByHour: {
            $push: {
              hour: '$orderHour',
              quantity: '$items.quantity'
            }
          },
          salesBySeason: {
            $push: {
              season: '$season',
              quantity: '$items.quantity',
              revenue: '$revenue'
            }
          },

          // Performance indicators
          firstSale: { $min: '$createdAt' },
          lastSale: { $max: '$createdAt' },
          peakSaleMonth: {
            $max: {
              month: '$orderMonth',
              quantity: '$items.quantity'
            }
          }
        }
      },

      // Stage 7: Calculate advanced metrics
      {
        $addFields: {
          uniqueCustomerCount: { $size: '$uniqueCustomers' },
          avgQuantityPerOrder: { $divide: ['$totalSold', '$totalOrders'] },
          revenuePerCustomer: { 
            $divide: ['$totalRevenue', { $size: '$uniqueCustomers' }] 
          },
          daysSinceLastSale: {
            $divide: [
              { $subtract: [new Date(), '$lastSale'] },
              86400000
            ]
          },
          productLifespanDays: {
            $divide: [
              { $subtract: ['$lastSale', '$firstSale'] },
              86400000
            ]
          },

          // Monthly sales distribution
          monthlySalesStats: {
            $let: {
              vars: {
                monthlyAgg: {
                  $reduce: {
                    input: { $range: [1, 13] },
                    initialValue: [],
                    in: {
                      $concatArrays: [
                        '$$value',
                        [{
                          month: '$$this',
                          totalQuantity: {
                            $sum: {
                              $map: {
                                input: {
                                  $filter: {
                                    input: '$salesByMonth',
                                    cond: { $eq: ['$$this.month', '$$this'] }
                                  }
                                },
                                in: '$$this.quantity'
                              }
                            }
                          },
                          totalRevenue: {
                            $sum: {
                              $map: {
                                input: {
                                  $filter: {
                                    input: '$salesByMonth',
                                    cond: { $eq: ['$$this.month', '$$this'] }
                                  }
                                },
                                in: '$$this.revenue'
                              }
                            }
                          }
                        }]
                      ]
                    }
                  }
                }
              },
              in: {
                bestMonth: {
                  $arrayElemAt: [
                    {
                      $filter: {
                        input: '$$monthlyAgg',
                        cond: {
                          $eq: [
                            '$$this.totalQuantity',
                            { $max: '$$monthlyAgg.totalQuantity' }
                          ]
                        }
                      }
                    },
                    0
                  ]
                },
                monthlyTrend: '$$monthlyAgg'
              }
            }
          }
        }
      },

      // Stage 8: Product performance classification
      {
        $addFields: {
          performanceCategory: {
            $switch: {
              branches: [
                {
                  case: {
                    $and: [
                      { $gt: ['$totalRevenue', 10000] },
                      { $gt: ['$avgProfitMargin', 20] },
                      { $gt: ['$uniqueCustomerCount', 100] }
                    ]
                  },
                  then: 'star'
                },
                {
                  case: {
                    $and: [
                      { $gt: ['$totalRevenue', 5000] },
                      { $gt: ['$avgProfitMargin', 10] }
                    ]
                  },
                  then: 'strong'
                },
                {
                  case: {
                    $and: [
                      { $gt: ['$totalRevenue', 1000] },
                      { $gt: ['$totalSold', 10] }
                    ]
                  },
                  then: 'moderate'
                },
                {
                  case: { $lt: ['$daysSinceLastSale', 30] },
                  then: 'active'
                }
              ],
              default: 'underperforming'
            }
          },

          inventoryStatus: {
            $switch: {
              branches: [
                { case: { $gt: ['$daysSinceLastSale', 90] }, then: 'stale' },
                { case: { $gt: ['$daysSinceLastSale', 30] }, then: 'slow_moving' },
                { case: { $lt: ['$daysSinceLastSale', 7] }, then: 'hot' }
              ],
              default: 'normal'
            }
          },

          // Demand predictability
          demandConsistency: {
            $let: {
              vars: {
                monthlyQuantities: '$monthlySalesStats.monthlyTrend.totalQuantity',
                avgMonthly: {
                  $avg: '$monthlySalesStats.monthlyTrend.totalQuantity'
                }
              },
              in: {
                $cond: {
                  if: { $gt: ['$$avgMonthly', 0] },
                  then: {
                    $divide: [
                      {
                        $stdDevPop: '$$monthlyQuantities'
                      },
                      '$$avgMonthly'
                    ]
                  },
                  else: 0
                }
              }
            }
          }
        }
      },

      // Stage 9: Final projection
      {
        $project: {
          productId: '$_id',
          productName: 1,
          category: 1,
          price: 1,
          cost: 1,

          // Sales metrics
          totalSold: 1,
          totalOrders: 1,
          uniqueCustomerCount: 1,
          avgQuantityPerOrder: { $round: ['$avgQuantityPerOrder', 2] },

          // Financial metrics
          totalRevenue: { $round: ['$totalRevenue', 2] },
          totalProfit: { $round: ['$totalProfit', 2] },
          avgOrderValue: { $round: ['$avgOrderValue', 2] },
          avgProfitMargin: { $round: ['$avgProfitMargin', 1] },
          revenuePerCustomer: { $round: ['$revenuePerCustomer', 2] },

          // Performance classification
          performanceCategory: 1,
          inventoryStatus: 1,
          demandConsistency: { $round: ['$demandConsistency', 3] },

          // Time-based insights
          daysSinceLastSale: { $round: ['$daysSinceLastSale', 0] },
          productLifespanDays: { $round: ['$productLifespanDays', 0] },
          bestSellingMonth: '$monthlySalesStats.bestMonth.month',
          bestMonthQuantity: '$monthlySalesStats.bestMonth.totalQuantity',
          bestMonthRevenue: { 
            $round: ['$monthlySalesStats.bestMonth.totalRevenue', 2] 
          },

          // Flags for business decisions
          isTopPerformer: { $eq: ['$performanceCategory', 'star'] },
          needsAttention: { $in: ['$performanceCategory', ['underperforming']] },
          isInventoryRisk: { $in: ['$inventoryStatus', ['stale', 'slow_moving']] },
          isHighDemand: { $eq: ['$inventoryStatus', 'hot'] },
          isPredictableDemand: { $lt: ['$demandConsistency', 0.5] }
        }
      },

      // Stage 10: Sort by business priority
      {
        $sort: {
          totalRevenue: -1,
          totalProfit: -1,
          uniqueCustomerCount: -1
        }
      }
    ];

    const startTime = Date.now();
    const results = await orders.aggregate(pipeline).toArray();
    const executionTime = Date.now() - startTime;

    console.log(`Product analytics completed in ${executionTime}ms, ${results.length} results`);

    this.pipelineStats.set('productAnalytics', {
      executionTime,
      resultCount: results.length,
      pipelineStages: pipeline.length,
      timestamp: new Date()
    });

    return results;
  }

  async analyzeAggregationPerformance(collection, pipeline, sampleSize = 1000) {
    console.log('Analyzing aggregation performance...');

    // Get explain plan for the pipeline
    const explainResult = await collection.aggregate(pipeline, { explain: true }).toArray();

    // Run with different hints and options to compare performance
    const performanceTests = [];

    // Test 1: Default execution
    const test1Start = Date.now();
    const test1Results = await collection.aggregate(pipeline).limit(sampleSize).toArray();
    const test1Time = Date.now() - test1Start;

    performanceTests.push({
      name: 'default',
      executionTime: test1Time,
      resultCount: test1Results.length,
      avgTimePerResult: test1Time / test1Results.length
    });

    // Test 2: With allowDiskUse for large datasets
    const test2Start = Date.now();
    const test2Results = await collection.aggregate(pipeline, { 
      allowDiskUse: true 
    }).limit(sampleSize).toArray();
    const test2Time = Date.now() - test2Start;

    performanceTests.push({
      name: 'allowDiskUse',
      executionTime: test2Time,
      resultCount: test2Results.length,
      avgTimePerResult: test2Time / test2Results.length
    });

    // Test 3: With maxTimeMS limit
    try {
      const test3Start = Date.now();
      const test3Results = await collection.aggregate(pipeline, { 
        maxTimeMS: 30000 
      }).limit(sampleSize).toArray();
      const test3Time = Date.now() - test3Start;

      performanceTests.push({
        name: 'maxTimeMS_30s',
        executionTime: test3Time,
        resultCount: test3Results.length,
        avgTimePerResult: test3Time / test3Results.length
      });
    } catch (error) {
      performanceTests.push({
        name: 'maxTimeMS_30s',
        error: error.message,
        executionTime: 30000,
        resultCount: 0
      });
    }

    // Analyze pipeline stages
    const stageAnalysis = pipeline.map((stage, index) => {
      const stageType = Object.keys(stage)[0];
      return {
        stage: index + 1,
        type: stageType,
        complexity: this.analyzeStageComplexity(stage),
        indexUtilization: this.analyzeIndexUsage(stage),
        optimizationOpportunities: this.identifyOptimizations(stage)
      };
    });

    return {
      explainPlan: explainResult,
      performanceTests: performanceTests,
      stageAnalysis: stageAnalysis,
      recommendations: this.generateOptimizationRecommendations(performanceTests, stageAnalysis)
    };
  }

  analyzeStageComplexity(stage) {
    const stageType = Object.keys(stage)[0];
    const complexityScores = {
      '$match': 1,
      '$project': 2,
      '$addFields': 3,
      '$group': 5,
      '$lookup': 7,
      '$unwind': 3,
      '$sort': 4,
      '$limit': 1,
      '$skip': 1,
      '$facet': 8,
      '$bucket': 6,
      '$sortByCount': 4
    };

    return complexityScores[stageType] || 3;
  }

  analyzeIndexUsage(stage) {
    const stageType = Object.keys(stage)[0];

    if (stageType === '$match') {
      const matchFields = Object.keys(stage[stageType]);
      return {
        canUseIndex: true,
        indexFields: matchFields,
        recommendation: `Ensure compound index exists for fields: ${matchFields.join(', ')}`
      };
    } else if (stageType === '$sort') {
      const sortFields = Object.keys(stage[stageType]);
      return {
        canUseIndex: true,
        indexFields: sortFields,
        recommendation: `Create index with sort field order: ${sortFields.join(', ')}`
      };
    }

    return {
      canUseIndex: false,
      recommendation: 'Stage cannot directly utilize indexes'
    };
  }

  identifyOptimizations(stage) {
    const stageType = Object.keys(stage)[0];
    const optimizations = [];

    switch (stageType) {
      case '$match':
        optimizations.push('Place $match stages as early as possible in pipeline');
        optimizations.push('Use indexes for filter conditions');
        break;
      case '$project':
        optimizations.push('Project only necessary fields to reduce document size');
        optimizations.push('Place projection early to reduce pipeline data volume');
        break;
      case '$lookup':
        optimizations.push('Use pipeline in $lookup for better performance');
        optimizations.push('Ensure foreign collection has appropriate indexes');
        optimizations.push('Consider embedding documents instead of lookups if data size permits');
        break;
      case '$group':
        optimizations.push('Group operations may require memory - consider allowDiskUse');
        optimizations.push('Use $bucket or $bucketAuto for large groupings');
        break;
      case '$sort':
        optimizations.push('Use indexes for sorting when possible');
        optimizations.push('Limit sort data with early $match and $limit stages');
        break;
    }

    return optimizations;
  }

  generateOptimizationRecommendations(performanceTests, stageAnalysis) {
    const recommendations = [];

    // Performance analysis
    const fastest = performanceTests.reduce((prev, current) => 
      prev.executionTime < current.executionTime ? prev : current
    );

    if (fastest.name !== 'default') {
      recommendations.push(`Best performance achieved with ${fastest.name} option`);
    }

    // High complexity stages
    const highComplexityStages = stageAnalysis.filter(s => s.complexity >= 6);
    if (highComplexityStages.length > 0) {
      recommendations.push(`High complexity stages detected: ${highComplexityStages.map(s => s.type).join(', ')}`);
    }

    // Index recommendations
    const indexableStages = stageAnalysis.filter(s => s.indexUtilization.canUseIndex);
    if (indexableStages.length > 0) {
      recommendations.push(`Create indexes for stages: ${indexableStages.map(s => s.type).join(', ')}`);
    }

    // General optimization
    const totalComplexity = stageAnalysis.reduce((sum, s) => sum + s.complexity, 0);
    if (totalComplexity > 30) {
      recommendations.push('Consider breaking pipeline into smaller parts');
      recommendations.push('Use $limit early to reduce dataset size');
    }

    return recommendations;
  }

  async createOptimalIndexes(collection, aggregationPatterns) {
    console.log('Creating optimal indexes for aggregation patterns...');

    const indexRecommendations = [];

    for (const pattern of aggregationPatterns) {
      const { pipeline, frequency, avgExecutionTime } = pattern;

      // Analyze pipeline for index opportunities
      const matchStages = pipeline.filter(stage => stage.$match);
      const sortStages = pipeline.filter(stage => stage.$sort);
      const lookupStages = pipeline.filter(stage => stage.$lookup);

      // Create compound indexes for $match + $sort combinations
      for (const matchStage of matchStages) {
        const matchFields = Object.keys(matchStage.$match);

        for (const sortStage of sortStages) {
          const sortFields = Object.keys(sortStage.$sort);

          // Combine match and sort fields following ESR rule
          const indexSpec = {};

          // Equality fields first
          matchFields.forEach(field => {
            if (typeof matchStage.$match[field] !== 'object') {
              indexSpec[field] = 1;
            }
          });

          // Sort fields next
          sortFields.forEach(field => {
            if (!indexSpec[field]) {
              indexSpec[field] = sortStage.$sort[field];
            }
          });

          // Range fields last
          matchFields.forEach(field => {
            if (typeof matchStage.$match[field] === 'object' && !indexSpec[field]) {
              indexSpec[field] = 1;
            }
          });

          if (Object.keys(indexSpec).length > 1) {
            indexRecommendations.push({
              collection: collection.collectionName,
              indexSpec: indexSpec,
              reason: 'Compound index for $match + $sort optimization',
              frequency: frequency,
              priority: frequency * avgExecutionTime,
              estimatedBenefit: this.estimateIndexBenefit(indexSpec, pattern)
            });
          }
        }
      }

      // Create indexes for $lookup foreign collections
      for (const lookupStage of lookupStages) {
        const { from, foreignField } = lookupStage.$lookup;

        if (foreignField) {
          indexRecommendations.push({
            collection: from,
            indexSpec: { [foreignField]: 1 },
            reason: 'Index for $lookup foreign field',
            frequency: frequency,
            priority: frequency * avgExecutionTime * 0.8,
            estimatedBenefit: 'High - improves lookup performance significantly'
          });
        }
      }
    }

    // Sort by priority and create top indexes
    const topRecommendations = indexRecommendations
      .sort((a, b) => b.priority - a.priority)
      .slice(0, 10);

    for (const rec of topRecommendations) {
      try {
        const targetCollection = this.db.collection(rec.collection);
        const indexName = `idx_agg_${Object.keys(rec.indexSpec).join('_')}`;

        await targetCollection.createIndex(rec.indexSpec, {
          name: indexName,
          background: true
        });

        console.log(`Created index ${indexName} on ${rec.collection}`);

      } catch (error) {
        console.error(`Failed to create index for ${rec.collection}:`, error.message);
      }
    }

    return topRecommendations;
  }

  estimateIndexBenefit(indexSpec, pattern) {
    const fieldCount = Object.keys(indexSpec).length;
    const pipelineComplexity = pattern.pipeline.length;

    if (fieldCount >= 3 && pipelineComplexity >= 5) {
      return 'Very High - Complex compound index for multi-stage pipeline';
    } else if (fieldCount >= 2) {
      return 'High - Compound index provides significant benefit';
    } else {
      return 'Medium - Single field index provides moderate benefit';
    }
  }

  async getPipelinePerformanceMetrics() {
    const metrics = {
      totalPipelines: this.pipelineStats.size,
      pipelines: Array.from(this.pipelineStats.entries()).map(([name, stats]) => ({
        name: name,
        executionTime: stats.executionTime,
        resultCount: stats.resultCount,
        stageCount: stats.pipelineStages,
        throughput: Math.round(stats.resultCount / (stats.executionTime / 1000)),
        lastRun: stats.timestamp
      })),
      indexRecommendations: this.indexRecommendations,

      // Performance categories
      fastPipelines: Array.from(this.pipelineStats.entries())
        .filter(([_, stats]) => stats.executionTime < 1000),
      slowPipelines: Array.from(this.pipelineStats.entries())
        .filter(([_, stats]) => stats.executionTime > 5000),

      // Overall health
      avgExecutionTime: Array.from(this.pipelineStats.values())
        .reduce((sum, stats) => sum + stats.executionTime, 0) / this.pipelineStats.size || 0
    };

    return metrics;
  }
}

// Benefits of MongoDB Aggregation Framework:
// - Single-pass processing eliminates multiple query roundtrips
// - Intelligent pipeline optimization with automatic stage reordering
// - Native index utilization throughout the pipeline stages
// - Memory-efficient streaming processing for large datasets
// - Built-in parallelization across shards in distributed deployments
// - Rich expression language for complex transformations and calculations
// - Integration with MongoDB's query optimizer for optimal execution plans
// - Support for complex nested document operations and transformations
// - Automatic spill-to-disk capabilities for memory-intensive operations
// - Native support for advanced analytics patterns and statistical functions

module.exports = {
  MongoAggregationOptimizer
};