const mongoose = require("mongoose");
const request = require('request-promise');
const Order = mongoose.model("Orders");
const User = mongoose.model("User");
const SellerAccountHolder = mongoose.model("SellerAccountHolder");
var nodemailer = require("nodemailer");
const moment = require("moment-timezone")

let date_time = new Date();
let date = ("0" + date_time.getDate()).slice(-2);
let month = ("0" + (date_time.getMonth() + 1)).slice(-2);
let year = date_time.getFullYear();

let current_date = year + "-" + month + "-" + date;

const express = require('express');
const app = express();
app.use(express.static('public/' + current_date));

// const { updateShopperrPriceAlgo, getMsrp } = require("../../apps/middlewares/priceAlgo");
// const { userValidation } = require('../../validation')

const axios = require('axios');

const AWS = require('aws-sdk');
const _ = require('lodash');
var fs = require('fs');
const path = require('path');

const xlsx = require('xlsx');

const csv = require('csvtojson');

ID = 'AKIAZQ3DPSAOW34EMAWP';    
SECRET = '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';
// The name of the bucket that you have created
const BUCKET_NAME = 'test-bucket';
const s3 = new AWS.S3({
    accessKeyId: ID,
    secretAccessKey: SECRET
});

const emailConfig = { 
    user: "AKIAZQ3DPSAO5V6HIJ53",
    password: "BLdhU1CZ9RB23yiTq0fuz6RzS5wwJo38OU9CeEmnwOuO",
    host: "email-smtp.us-east-1.amazonaws.com",
  };

exports.DailySalesReport = async (req, res) => {

    const formattedYesterday = moment().subtract(1, 'days').format('YYYY-MM-DD');

    const formattedYesterdayText = moment().subtract(1, 'days').format('ddd, D MMM YYYY');

    const formattedYesterdayText1 = moment().subtract(1, 'days').format('ddd, D MMM YYYY H:m:s A');

    let firstDayOfMonth, firstDay, firstDayText = '';
    let last_current_date, lastCurrentDayText, lastFirstDay, lastFirstDayText ='';
    const today = new Date();
    if (today.getDate() === 1) {
        firstDayOfMonth = moment().startOf('month');
        firstDay = firstDayOfMonth.subtract(1, 'month').startOf('month');
        firstDayText = firstDay.format('1 MMM');

        last_current_date = moment().subtract(2, 'months').startOf('month').endOf('month').format('YYYY-MM-DD');

        lastCurrentDayText = moment(last_current_date).format('DD MMM');

        lastFirstDay = moment().subtract(2, 'months').startOf('month').format('YYYY-MM-DD');
        lastFirstDayText = moment().subtract(2, 'months').startOf('month').format('D MMM');

        
    }else{
        firstDay = moment().startOf('month').format('YYYY-MM-01');
        firstDayText = moment().startOf('month').format('1 MMM');
        
        last_current_date = moment().subtract(1, 'months').subtract(1, 'days').format('YYYY-MM-DD');
        lastCurrentDayText = moment(last_current_date).format('DD MMM');
        
        lastFirstDay = moment().subtract(1, 'months').startOf('month').format('YYYY-MM-DD');
        lastFirstDayText = moment().subtract(1, 'months').startOf('month').format('D MMM');
    }
 
    // console.log('First day of the month:', firstDay);
    // console.log('Formatted text:', firstDayText);
    // console.log('formattedYesterday text:', formattedYesterday);
    // console.log('formattedYesterdayLast text:', last_current_date);

    // console.log('lastFirstDay text:', lastFirstDay);


    // const startDate = moment(date_purchased).startOf('day').toDate(); // start of the day
    // const endDate = moment(date_purchased).endOf('day').toDate(); // end of the day


    // const orderlist = await orderList(startDate, endDate);

    // $condition2=array(
    //     'date_from'=>$first_day,
    //     'date_to'=>$current_date
    // );

    //formattedYesterday
    let total_price = 0;
    let total_quantity = 0;

    let total_price1 = 0;
    let total_quantity1 = 0;

    let total_price2 = 0;
    let total_quantity2 = 0;

    let total_price_mtd = 0;
    let total_price_lmtd = 0;
    let total_price_bulk_po_mtd = 0;
    let total_price_bulk_po_lmtd = 0;

    let ourTotalPrice = 0;
    let ourTotalQuantity = 0;

    let gstTotalQuantity = 0;
    let gstTotalPrice = 0;

    let oocTotalQuantity = 0;
    let oocTotalPrice = 0;

    let cocoTotalQuantity = 0;
    let cocoTotalPrice = 0;

    let focoTotalQuantity = 0;
    let focoTotalPrice = 0;

    const orderlist2 = await orderList(firstDay, formattedYesterday);

    // console.log(orderlist2)

    // return false;

    const orderlist3 = await orderList(lastFirstDay, last_current_date);
    // console.log(orderlist2);
    
    if(orderlist2.length>0){
        orderlist2.map((item) => {
            total_price1 += parseFloat(item.total_price);
            total_quantity1 += parseInt(item.total_quantity);
        })
    }

    if(orderlist3.length>0){
        orderlist3.map((item) => {
            total_price2 += parseFloat(item.total_price);
            total_quantity2 += parseInt(item.total_quantity);
        })
    }
    // console.log(firstDay, formattedYesterday)
    // console.log(lastFirstDay, last_current_date)

    const orderListBulkPoData = await orderListBulkPo(firstDay, formattedYesterday);
    const orderListBulkPoDataLmtd = await orderListBulkPo(lastFirstDay, last_current_date);
    // console.log(orderListBulkPoDataLmtd);

    // return false;

    const orderlist = await orderList(formattedYesterday, formattedYesterday);

    const sellerData = await getSellerDataList(formattedYesterday);

    // const sellerCompanyData = await getSellerCompanyData(formattedYesterday);
    const delhiGstAccounts = await aggregateOrders(formattedYesterday, 'Sudhir Yadav');
    const amazonOocAccounts = await aggregateOrders(formattedYesterday, 'OOC');
    const cocoAccounts = await aggregateOrders(formattedYesterday, 'COCO');
    const focoAccounts = await aggregateOrders(formattedYesterday, 'FOCO');
    // console.log(delhiGstAccounts)
    // return false;


    let html = '';
    html += `<style>
                table {
                font-family: arial, sans-serif;
                border-collapse: collapse;
                width: 100%;
                font-size: 10px;
                }
                thead th{
                background-color: yellow;
                }
                tfoot th{
                background-color: yellow;
                }
                td, th {
                border: 1px solid #dddddd;
                text-align: left;
                padding: 4px;
                }

                tr:nth-child(even) {
                background-color: #dddddd;
                }
                </style>
            <table >
                <tbody>

                </tbody>
                <tbody>
                <tr>
                    <td width="35%"><h4>Month to Date (${firstDayText} - ${formattedYesterdayText}) :</h4></td>
                    <td width="50%"><h4 style="color:blue">&#8377;${total_price1.toLocaleString()}  (Qty - ${total_quantity1})</h4></td>
                </tr>
                <tr>
                    <td width="35%"><h4>Last Month to Date (${lastFirstDayText} - ${lastCurrentDayText}) : </h4></td>
                    <td width="50%"><h4 style="color:blue">&#8377;${total_price2.toLocaleString()}  (Qty - ${total_quantity2})</h4></td>
                </tr>
                </tbody>
            </table>`; 
    
    html += `<h4>Daily Sales Report </h4>

            <table border="2">
                <tr>
                    <td>
                        <table>
                            <thead>
                            <tr bgcolor="grey">
                                <th><b>Process By</b></th>
                                <th><b>MTD</b></th>
                            </tr>
                            </thead>
                            <tbody>`;
            html += orderlist2.map(item => {
                    total_price_mtd += item.total_price;    
                    return `<tr>
                                <td>${item._id}</td>
                                <td>${item.total_price.toLocaleString()}</td>
                            </tr>`;
                }).join("");

            html +=        `</tbody>
                            <tfoot bgcolor="grey">
                                <tr>
                                    <th><b>Grand Total </b></th>
                                    <th><b>${total_price_mtd.toLocaleString()}</b></th>
                                </tr>
                            </tfoot>
                        </table>
                    </td>
                    <td>
                        <table>
                            <thead>
                                <tr bgcolor="grey">
                                    <th><b>Process By</b></th>
                                    <th><b>LMTD</b></th>
                                </tr>
                            </thead>
                            <tbody>`;
            html += orderlist3.map(item => {
                total_price_lmtd += item.total_price;    
                return `<tr>
                            <td>${item._id}</td>
                            <td>${item.total_price.toLocaleString()}</td>
                        </tr>`;
            }).join("");
            html +=  `</tbody>
                            <tfoot bgcolor="grey">
                            <tr>
                                <th><b>Grand Total </b></th>
                                <th><b>${total_price_lmtd.toLocaleString()}</b></th>
                            </tr>
                            </tfoot>
                        </table>
                    </td>
                </tr>
            </table>`;
            
        html += `<h4>Daily Sales Report Bulk PO </h4>
            <table border="2">
            <tr>
                <td>
                    <table>
                        <thead>
                            <tr bgcolor="grey">
                                <th><b>Process By</b></th>
                                <th><b>MTD</b></th>
                            </tr>
                        </thead>
                        <tbody>`;
                
            html += orderListBulkPoData.map(item => {
                total_price_bulk_po_mtd += item.total_price;    
                return `<tr>
                            <td>${item.process}</td>
                            <td>${item.total_price.toLocaleString()}</td>
                        </tr>`;
            }).join("");
        html += `</tbody>
                        <tfoot bgcolor="grey">
                            <tr>
                                <th><b>Grand Total </b></th>
                                <th><b>${total_price_bulk_po_mtd.toLocaleString()}</b></th>
                            </tr>
                        </tfoot>
                    </table>
                </td>
                <td>
                    <table>
                        <thead>
                            <tr bgcolor="grey">
                                <th><b>Process By</b></th>
                                <th><b>LMTD</b></th>
                            </tr>
                        </thead>
                        <tbody>`;
            html += orderListBulkPoDataLmtd.map(item => {
                total_price_bulk_po_lmtd += item.total_price;    
                return `<tr>
                            <td>${item.process}</td>
                            <td>${item.total_price.toLocaleString()}</td>
                        </tr>`;
            }).join("");
            html += `</tbody>
                    <tfoot bgcolor="grey">
                            <tr>
                                <th><b>Grand Total </b></th>
                                <th><b>${total_price_bulk_po_lmtd}</b></th>
                            </tr>
                        </tfoot>
                    </table>
                </td>
            </tr>
            
        </table>`;

        html += `<h4>Daily Sales Report (${formattedYesterdayText})</h4>
                <table border="2">
                <thead>
                    <tr bgcolor="grey">
                        <th><b>Process By</b></th>
                        <th><b>Sum of Quantity</b></th>
                        <th><b>Sum of TotalPriceIncludingGST</b></th>
                    </tr>
                </thead>
                <tbody>`;
        html += orderlist.map(item => {
            total_price += item.total_price; 
            total_quantity += item.total_quantity
            return `<tr>
                        <td>${item._id}</td>
                        <td>${item.total_quantity}</td>
                        <td>${item.total_price.toLocaleString()}</td>
                    </tr>`;
        }).join("");
                
                
        html += `</tbody>
                <tfoot bgcolor="grey">
                    <tr>
                        <th><b>Grand Total </b></th>
                        <th><b>${total_quantity}</b></th>
                        <th><b>${total_price.toLocaleString()}</b></th>
                    </tr>
                </tfoot>
            </table>
                `;

        let html_table = '';
        html_table += `<style>
                        table {
                        font-family: arial, sans-serif;
                        border-collapse: collapse;
                        width: 100%;
                        font-size: 10px;
                        }
                        thead th{
                        background-color: yellow;
                        }
                        tfoot th{
                        background-color: #d5d9dc;
                        }
                        td, th {
                        border: 1px solid #dddddd;
                        text-align: left;
                        padding: 4px;
                        }

                        tr:nth-child(even) {
                        background-color: #dddddd;
                        }
                        </style>
                        <table>
                            <tr>
                                <td>
                                    <table border="2">
                                        <thead>
                                            <tr>
                                                <th><b>Our Seller</b></th>
                                                <th><b>Quantity</b></th>
                                                <th><b>Price</b></th>
                                            </tr>
                                        </thead>
                                        <tbody>`;
                        sellerData.sort((a, b) => b.final_price - a.final_price);
                        
                        html_table += sellerData.map(item => {
                                    
                                ourTotalQuantity += item.product_quantity    
                                ourTotalPrice += item.final_price    
                            
                                return `<tr>
                                                <td>${item.customer_firstname}</td>
                                                <td>${item.product_quantity}</td>
                                                <td>${item.final_price.toLocaleString()}</td>
                                            </tr>`;
                                }).join("");

                        html_table += ` </tbody>
                                        <tfoot>
                                            <tr>
                                                <th><b>Grand Total </b></th>
                                                <th><b>${ourTotalQuantity}</b></th>
                                                <th><b>${ourTotalPrice.toLocaleString()}</b></th>
                                            </tr>
                                        </tfoot>
                                    </table>`;
            
            html_table += `<table border="2">
                        <thead>
                          <tr>
                            <th>Delhi GST accounts</th>
                            <th>Acc Holder Name</th>
                            <th>Quantity</th>
                            <th>Revenue</th>
                          </tr>
                        </thead>
                        <tbody>`;
            
            delhiGstAccounts.sort((a, b) => b.final_price - a.final_price);
            html_table += delhiGstAccounts.map(item => {
            
                gstTotalQuantity += item.product_quantity    
                gstTotalPrice += item.final_price    
            
                return `<tr>
                                <td>${item.customerName}</td>
                                <td>${item.accountHolderName}</td>
                                <td>${item.product_quantity}</td>
                                <td>${item.final_price.toLocaleString()}</td>
                            </tr>`;
                }).join("");
                               
        html_table += `</tbody>
                        <tfoot>
                          <tr>
                              <th><b>Grand Total </b></th>
                              <th></th>
                              <th><b>${gstTotalQuantity}</b></th>
                              <th><b>${gstTotalPrice.toLocaleString()}</b></th>
                          </tr>
                      </tfoot>
                    </table> `;

        html_table += `</td>
                    <td rowspan="2" style="vertical-align: top;">
                      <table border="2">
                        <thead>
                          <tr>
                            <th>Amazon OOC Accounts</th>
                            <th>Acc Holder Name</th>
                            <th>Quantity</th>
                            <th>Revenue</th>
                          </tr>
                        </thead>
                        <tbody>`;
        
        amazonOocAccounts.sort((a, b) => b.final_price - a.final_price);
        html_table += amazonOocAccounts.map(item => {

                        oocTotalQuantity += item.product_quantity    
                        oocTotalPrice += item.final_price    
                    
                        return `<tr>
                                        <td>${item.customerName}</td>
                                        <td>${item.accountHolderName}</td>
                                        <td>${item.product_quantity}</td>
                                        <td>${item.final_price.toLocaleString()}</td>
                                    </tr>`;
                        }).join("");
        html_table += `</tbody>
                            <tfoot>
                                <tr>
                                    <th><b>Grand Total </b></th>
                                    <th></th>
                                    <th><b>${oocTotalQuantity}</b></th>
                                    <th><b>${oocTotalPrice.toLocaleString()}</b></th>
                                </tr>
                            </tfoot>
                      </table>`;
        
        html_table += `<table border="2">
                        <thead>
                        <tr>
                            <th>COCO</th>
                            <th>Acc Holder Name</th>
                            <th>Quantity</th>
                            <th>Revenue</th>
                        </tr>
                        </thead>
                        <tbody>`;
        
        cocoAccounts.sort((a, b) => b.final_price - a.final_price);
        html_table += cocoAccounts.map(item => {

                        cocoTotalQuantity += item.product_quantity    
                        cocoTotalPrice += item.final_price    
                    
                        return `<tr>
                                        <td>${item.customerName}</td>
                                        <td>${item.accountHolderName}</td>
                                        <td>${item.product_quantity}</td>
                                        <td>${item.final_price.toLocaleString()}</td>
                                    </tr>`;
                        }).join("");
        html_table += `</tbody>
                        <tfoot>
                            <tr>
                                <th><b>Grand Total </b></th>
                                <th></th>
                                <th><b>${cocoTotalQuantity}</b></th>
                                <th><b>${cocoTotalPrice.toLocaleString()}</b></th>
                            </tr>
                        </tfoot>
                  </table>`;
        
        html_table += `<table border="2">
                        <thead>
                        <tr>
                            <th>FOCO</th>
                            <th>Acc Holder Name</th>
                            <th>Quantity</th>
                            <th>Revenue</th>
                        </tr>
                        </thead>
                        <tbody>`;
        
        focoAccounts.sort((a, b) => b.final_price - a.final_price);
        html_table += focoAccounts.map(item => {

                        focoTotalQuantity += item.product_quantity    
                        focoTotalPrice += item.final_price    
                    
                        return `<tr>
                                        <td>${item.customerName}</td>
                                        <td>${item.accountHolderName}</td>
                                        <td>${item.product_quantity}</td>
                                        <td>${item.final_price.toLocaleString()}</td>
                                    </tr>`;
                        }).join("");

        html_table += `</tbody>
                            <tfoot>
                                <tr>
                                    <th><b>Grand Total </b></th>
                                    <th></th>
                                    <th><b>${focoTotalQuantity}</b></th>
                                    <th><b>${focoTotalPrice.toLocaleString()}</b></th>
                                </tr>
                            </tfoot>
                      </table>`;
        
        
        html_table += `</td>
                        </tr>
                            </table>`;
        html += html_table;

        html +=`<p>
				Regards<br/>
				IT Team<br/>
				Shopperr
				</p>
				<p>
				This is autogenerated mail.
				</p>`;


        let transporter = nodemailer.createTransport({
            host: emailConfig.host,
            port: 465,
            secure: true,
            auth: {
              user: emailConfig.user,
              pass: emailConfig.password,
            },
          });
        await transporter.sendMail({
            from: 'Shopperr <support@shopperr.in>',  
            to: 'ashutosh.singh@shopperr.in',  
            // to: 'minakshi.kanoji@shopperr.in',  
            subject: 'Daily Sales Report '+ formattedYesterdayText1,   
            html: html,  
            },   
            function (error, info) {
                if(error){  
                    return res.send({'status' : 1 , 'response' : 'Something went wrong.' }) 
                }else{ 
                    return res.send({'status' : 0 , 'response' : 'Mail successfully send' })
                }
            }
        );
        

    // console.log(html)
}

exports.SellerAccountHolder = async (req, res) => {

    // console.log(req.files.file)

    const fileContent = req.files.file.data

    let csv_data = await csv().fromString(fileContent.toString());

    await Promise.all(csv_data.map(async (item) => {

        // console.log(item)

        // delyssion@gmail.com
        // Lrecom4th@gmail.com


        //seller_id
        let seller_id = ''
        let customer_login_id = item.customer_login_id;
        let account_holder_name = item.account_holder_name;
        let company_type = item.company_type;
        let market_place = item.market_place;
        let customer_email_address = item.customer_email_address;

        const getUser = await User.findOne({ email: item.customer_email_address});
        
        if(getUser){
            seller_id = getUser._id;

            let sellerHolder = await SellerAccountHolder.find({ merchantId : seller_id })

            if(sellerHolder.length==0){
                let sellerAccountHolder = new SellerAccountHolder({
                    merchantId : seller_id,
                    customerName : customer_login_id,
                    accountHolderName : account_holder_name,
                    companyType : company_type,
                    marketPlace : market_place
                });
    
                await sellerAccountHolder.save();
            }

            

        }else{
            console.log(item.customer_email_address)
        }

    }))

}

// async function getSellerCompanyData(date_purchased){
//     try{
        
//         const startDate = moment(date_purchased).startOf('day').toDate(); // start of the day
//         const endDate = moment(date_purchased).endOf('day').toDate(); // end of the day
        
//         const sellerData = await SellerAccountHolder.aggregate([
//             // Step 1: Perform $lookup to join with the orders collection
//             {
//               $lookup: {
//                 from: "orders",  // Join with orders collection
//                 localField: "merchantId",  // seller_id from seller_account_holder
//                 foreignField: "_id",  // orders_buyer_id from orders collection
//                 as: "orders"  // The result of the join
//               }
//             },
//             {
//               $match: {
//                 "orders.orderDate": { $gte: date_purchased, $lt: date_purchased },  // Exact date match
//                 "orders.orderDate": { $gte: date_purchased, $lt: date_purchased }
//               }
//             },
//             // {
//             //   $unwind: {
//             //     path: "$orders",
//             //     preserveNullAndEmptyArrays: true  // Keep seller_account_holder even if no orders match
//             //   }
//             // },
//             // {
//             //   $group: {
//             //     _id: "$merchantId",  // Group by seller_id
//             //     account_holder_name: { $first: "$accountHolderName" },  // Include account_holder_name from seller_account_holder
//             //     customer_login_id: { $first: "$fName" },  // Include customer_login_id from seller_account_holder
//             //     product_quantity: { $sum: "$orderDetails.quantity" },  // Sum product_quantity
//             //     final_price: { $sum: "$orderDetails.finalPrice" }  // Sum final_price
//             //   }
//             // },
//             // {
//             //   $project: {
//             //     _id: 0,  // Exclude _id
//             //     account_holder_name: 1,  // Include account_holder_name
//             //     seller_account_id: "$_id",  // Rename _id to seller_account_id
//             //     customer_login_id: 1,  // Include customer_login_id
//             //     product_quantity: 1,  // Include product_quantity
//             //     final_price: 1  // Include final_price
//             //   }
//             // }
//           ]);

//           return sellerData;

//     }catch(err){

//     }
// }

// async function getSellerList(){

//     try {
    
//         const sellerList = await User.aggregate([
//             {
//             $match: {
//                 _id: { 
//                 $nin: ["66a89b96c2269f9c3942e63d", "66a9e899c2269f9c394316ae", "66d1935ec2269f9c394b8511", "66c4d29ac2269f9c39488325", "668bb9f6c2269f9c393e60bb", "66a73f3dc2269f9c3942abdf"]
//                 }
//             }
//             },
//             {
//             $lookup: {
//                 from: "selleraccountholders",
//                 localField: "_id",
//                 foreignField: "merchantId",
//                 as: "seller_info"
//             }
//             },
//             {
//             $match: {
//                 seller_info: { $size: 0 }
//             }
//             },
//             {
//             $project: {
//                 _id: 1,
//                 email: 1
//             }
//             }
//         ]);
        
//         return sellerList;

//         // return new Promise((resolve, reject) => {
//         //     resolve(sellerList);
//         // })

//     }catch(err){
//         return err;
//     }
// }

async function aggregateOrders(date_purchased, companyType) {
    try {
    
        const startDate = moment(date_purchased).startOf('day').toDate(); // start of the day
        const endDate = moment(date_purchased).endOf('day').toDate(); // end of the day

        const result = await SellerAccountHolder.aggregate([
            {
                $match: {
                  companyType: companyType  // Filter for companyType = 'abc'
                }
            },
            {
              $lookup: {
                from: 'orders', 
                localField: 'merchantId', 
                foreignField: 'merchantId',
                as: 'orders',
                pipeline: [
                  { 
                    $match: {
                      "orderDate": { $gte: startDate, $lte: endDate },
                    }
                  },
                  { 
                    $unwind: "$orderDetails"  // Flatten the orderDetails array
                  },
                  {
                    $group: {
                      _id: "$merchantId",  // Group by order ID or another identifier
                      orders_buyer_id: { $first: "$merchantId" },  // Assuming the 'buyerId' is a field in the order document
                      product_quantity: { $sum: "$orderDetails.quantity" },  // Sum quantity from orderDetails
                      final_price: { $sum: "$orderDetails.finalPrice" }  // Sum finalPrice from orderDetails
                    }
                  }
                ]
              }
            },
            {
              $unwind: "$orders"  // Flatten orders if multiple orders
            },
            {
              $project: {
                accountHolderName: 1,  // Include account holder name from seller_account_holder
                customerName:1,
                orders_buyer_id: "$orders.orders_buyer_id",  // Add buyerId from the lookup
                product_quantity: "$orders.product_quantity",  // Total product quantity
                final_price: "$orders.final_price",  // Total final price
                customer_login_id: 1  // Include customer_login_id from seller_account_holder
              }
            }
          ]);
          
          return result;
    } catch (err) {
      console.error('Error during aggregation:', err);
    }
}

async function getSellerDataList(date_purchased){
    try {
    
        const startDate = moment(date_purchased).startOf('day').toDate(); // start of the day
        const endDate = moment(date_purchased).endOf('day').toDate(); // end of the day

        const orderSummary = await Order.aggregate([
            {
                $lookup: {
                    from: "user",  // The User collection to join with
                    localField: "merchantId",  // Field from Order collection
                    foreignField: "_id",  // Field from User collection
                    as: "user_info"  // The resulting joined information
                }
            },
            {
                $lookup: {
                from: 'users',
                let: { merchantId: { $toObjectId: "$merchantId" } }, // Convert merchantId to ObjectId
                pipeline: [
                    {
                    $match: {
                        $expr: {
                        $eq: ["$_id", "$$merchantId"]
                        }
                    }
                    }
                ],
                as: 'user_info'
                }
            },
            {
                $match: {
                "user_info._id": { $nin: ["66a89b96c2269f9c3942e63d", "66a9e899c2269f9c394316ae", "66d1935ec2269f9c394b8511", "66c4d29ac2269f9c39488325", "668bb9f6c2269f9c393e60bb", "66a73f3dc2269f9c3942abdf"] }, // Exclude specific users
                orderDate: { $gte: startDate, $lt: endDate },
                "user_info.sellerAccountHolder": { $in: [0] }
                }
            },
            {
                $unwind: "$orderDetails"
            },
            {
                $group: {
                _id: "$merchantId",  // Group by merchantId
                orderSerial: { $first: "$orderSerial" },  // Get the first orderSerial
                product_quantity: { $sum: "$orderDetails.quantity" }, // Sum of product quantities
                final_price: { $sum: "$orderDetails.finalPrice" },  // Sum of final prices,
                customer_firstname: { $first: "$user_info.fName" },
                }
            },
            {
                $project: {
                _id: 0,  // Don't include the _id field
                merchantId: "$_id",  // Rename _id to merchantId
                orderSerial: 1,
                product_quantity: 1,
                final_price: 1,
                user_info: 1,
                customer_firstname: 1
                }
            }
            ]);
        

        return orderSummary;

    } catch (err) {
    console.error("Error fetching order summary:", err);
    throw err;
    }
}

async function orderList(fromDate, toDate){
    
    const startDate = moment(fromDate).startOf('day').toDate(); // start of the day
    const endDate = moment(toDate).endOf('day').toDate(); // end of the day

    // console.log(startDate)
    // console.log(endDate)

    // return false;
    
    /*const orderList = await Order.aggregate([
        // Unwind the orderDetails array so that each item is treated separately
        {
            $match: {
                orderDate: { $gte: startDate, $lte: endDate }
            }
        },
        {
          $unwind: '$orderDetails',
        },
      
        {
          $group: {
            _id: '$chinaProcess', // Group by chinaProcess
            total_price: { $sum: '$orderDetails.finalPrice' }, // Sum of finalPrice
            total_quantity: { $sum: '$orderDetails.quantity' }, // Sum of quantity
          },
        },
        {
            $project: {
              process: {
                $switch: {
                  branches: [
                    { case: { $eq: ['$_id', 3] }, then: 'Bulk' },
                    { case: { $eq: ['$_id', 0] }, then: 'India' },
                    { case: { $in: ['$_id', [1, 4]] }, then: 'China' }, 
                  ],
                  default: 'Unknown', // Default value if no match
                },
              },
              total_price: 1, // Include total price in the output
              total_quantity: 1, // Include quantity in the output
            },
        },
        // Optionally, you can sort the result by chinaProcess
        {
          $sort: { _id: 1 }, // Sort by chinaProcess (ascending)
        },
    ]);*/

    const orderList = await Order.aggregate([
        {
          $match: {
            orderDate: { $gte: startDate, $lte: endDate },
            merchantId: {$ne:"668fd0f9c2269f9c393ee96e"}
          },
        },
        {
          $unwind: '$orderDetails',
        },
        {
          $group: {
            _id: '$chinaProcess',  // Group by 'chinaProcess' field
            total_price: { $sum: '$orderDetails.finalPrice' },
            total_quantity: { $sum: '$orderDetails.quantity' },
          },
        },
        {
          // Debug step: Let's output the values of chinaProcess (_id) before applying $switch
          $project: {
            chinaProcess: '$_id',  // Output the value of chinaProcess (_id)
            total_price: 1,
            total_quantity: 1,
          },
        },
        {
          $project: {
            process: {
              $switch: {
                branches: [
                  { case: { $eq: ['$_id', 3] }, then: 'Bulk' },
                  { case: { $eq: ['$_id', 0] }, then: 'India' },
                  { case: { $in: ['$_id', [1, 4]] }, then: 'China' },  // Group 1 and 4 as 'China'
                ],
                default: 'Unknown',  // Ensure no undefined results
              },
            },
            total_price: 1,
            total_quantity: 1,
          },
        },
        {
          $group: {
            _id: '$process',  // Group by the 'process' field now set to 'Bulk', 'India', or 'China'
            total_price: { $sum: '$total_price' },
            total_quantity: { $sum: '$total_quantity' },
          },
        },
        {
          $sort: { _id: 1 },
        },
      ]);
      

    return orderList;
}

async function orderListBulkPo(fromDate, toDate){
    
    const startDate = moment(fromDate).startOf('day').toDate(); // start of the day
    const endDate = moment(toDate).endOf('day').toDate(); // end of the day
    
    const orderList = await Order.aggregate([
        {
          $match: {
            // Filter by the date range (date_purchased between date_from and date_to)
            orderDate: {
              $gte: startDate,  // Replace '$date_from' with the actual date value
              $lte: endDate     // Replace '$date_to' with the actual date value
            },
            merchantId: '668fd0f9c2269f9c393ee96e'  // Filter by orders_buyer_id
          }
        },
        {
          $unwind: '$orderDetails',
        },
        {
          $group: {
            _id: null,
            total_price: { $sum: "$orderDetails.finalPrice" },
            quantity: { $sum: "$orderDetails.quantity" }
          }
        },
        {
          $project: {
            _id: 0,            // Exclude the _id field from the result
            total_price: 1,    // Include total_price in the result
            quantity: 1,       // Include quantity in the result
            process: { $literal: "Bulk PO" } // Add the constant value 'Bulk PO' as process
          }
        }
      ]);
      

    return orderList;
}