5_indexes_and_aggregation.js

const { connect, closeConnection } = require('./0_connection');

// Index Operations
async function indexExamples() {
    const db = await connect();
    try {
        // Create a single field index
        // Docs: https://www.mongodb.com/docs/manual/indexes/
        await db.collection('products').createIndex(
            { name: 1 },  // 1 for ascending, -1 for descending
            {
                name: "idx_product_name",
                background: true  // Create index in background
            }
        );

        // Create a compound index
        await db.collection('products').createIndex(
            {
                category: 1,
                price: -1
            },
            {
                name: "idx_category_price",
                background: true
            }
        );

        // Create a unique index
        await db.collection('users').createIndex(
            { email: 1 },
            {
                unique: true,
                name: "idx_unique_email"
            }
        );

        // List all indexes on a collection
        const productIndexes = await db.collection('products').listIndexes().toArray();
        const userIndexes = await db.collection('users').listIndexes().toArray();

        console.log('Indexes created:', {
            productIndexes,
            userIndexes
        });

    } catch (error) {
        console.error('Error in index operations:', error);
    }
}

// Aggregation Pipeline Examples
async function aggregationExamples() {
    const db = await connect();
    try {
        // Basic aggregation - Average price by category
        // Docs: https://www.mongodb.com/docs/manual/aggregation/
        const avgPriceByCategory = await db.collection('products').aggregate([
            {
                $group: {
                    _id: "$category",
                    averagePrice: { $avg: "$price" },
                    totalProducts: { $sum: 1 }
                }
            },
            {
                $sort: { averagePrice: -1 }
            }
        ]).toArray();

        // Complex aggregation - Sales analysis
        const salesAnalysis = await db.collection('orders').aggregate([
            // Unwind the products array
            { $unwind: "$products" },

            // Group by product
            {
                $group: {
                    _id: "$products.product_id",
                    totalQuantity: { $sum: "$products.quantity" },
                    totalRevenue: {
                        $sum: {
                            $multiply: ["$products.quantity", "$products.price"]
                        }
                    },
                    averageOrderValue: { $avg: "$products.price" }
                }
            },

            // Lookup product details
            {
                $lookup: {
                    from: "products",
                    localField: "_id",
                    foreignField: "_id",
                    as: "product_details"
                }
            },

            // Reshape the output
            {
                $project: {
                    productName: { $arrayElemAt: ["$product_details.name", 0] },
                    category: { $arrayElemAt: ["$product_details.category", 0] },
                    totalQuantity: 1,
                    totalRevenue: 1,
                    averageOrderValue: 1
                }
            },

            // Sort by revenue
            { $sort: { totalRevenue: -1 } }
        ]).toArray();

        // User statistics with multiple stages
        const userStats = await db.collection('users').aggregate([
            // Match active users
            {
                $match: {
                    last_login: {
                        $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) // Last 30 days
                    }
                }
            },

            // Group by age range
            {
                $bucket: {
                    groupBy: "$age",
                    boundaries: [0, 18, 25, 35, 50, 65, 120],
                    default: "Unknown",
                    output: {
                        count: { $sum: 1 },
                        averageOrderCount: { $avg: "$order_count" },
                        users: { $push: { username: "$username", age: "$age" } }
                    }
                }
            }
        ]).toArray();

        console.log('Aggregation results:', {
            avgPriceByCategory,
            salesAnalysis,
            userStats
        });

    } catch (error) {
        console.error('Error in aggregation operations:', error);
    } finally {
        await closeConnection();
    }
}

// Run all examples if this file is run directly
if (require.main === module) {
    async function runAll() {
        await indexExamples();
        await aggregationExamples();
    }
    runAll();
}

module.exports = {
    indexExamples,
    aggregationExamples
};