const mongoose = require("mongoose");
const Product = mongoose.model("Products");
const Order = mongoose.model("Orders");
const User = mongoose.model("User");
const EInvoice = mongoose.model("EInvoice");

// const Transaction = mongoose.model("transaction");
const jwt = require("jsonwebtoken");
const bcrypt = require("bcryptjs");
const moment = require('moment-timezone');
const { webAPiValidation } = require("../handlers/webValidation");
const request = require('request-promise');
var fs = require('fs');
const { Parser } = require('json2csv');
const AWS = require('aws-sdk');
const { result } = require('lodash');
const _ = require("lodash");
ID = 'AKIAZQ3DPSAOTI3ZHWEF';    
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
});

exports.codeTest = async (req, res) => {
  const data = [
    { price: 382, seller_name: "f@gmail.com" },
    { price: 420, seller_name: "f@gmail.com" },
    { price: 486, seller_name: "f@gmail.com" },
    { price: 486, seller_name: "f@gmail.com" },
    { price: 547, seller_name: "f@gmail.com" },
    { price: 601, seller_name: "f@gmail.com" },
    { price: 601, seller_name: "f@gmail.com" },
    { price: 751, seller_name: "f@gmail.com" },
    { price: 751, seller_name: "f@gmail.com" },
    { price: 846, seller_name: "f@gmail.com" },
    { price: 1196, seller_name: "f@gmail.com" },
    { price: 1527, seller_name: "f@gmail.com" },
    { price: 432, seller_name: "p@gmail.com" },
    { price: 447, seller_name: "p@gmail.com" },
    { price: 494, seller_name: "p@gmail.com" },
    { price: 582, seller_name: "p@gmail.com" },
    { price: 673, seller_name: "p@gmail.com" },
    { price: 1853, seller_name: "p@gmail.com" },
    { price: 1927, seller_name: "p@gmail.com" },
    { price: 1927, seller_name: "p@gmail.com" },
];

const limit = 5000; // Set your limit here
let sum = 0;
let lastResult = [];
let currentCustomer = '';
let currentArray = null;
let m = 0;

// Iterate through each item in the data array
data.forEach((item) => {
    // Check if we've switched customers
    if (item.seller_name !== currentCustomer) {
        m++;
        // If we switched customers, start a new group
        currentCustomer = item.seller_name;
        sum = 0; // Reset sum for the new customer
        currentArray = { customer: currentCustomer, invoices: [] };
        lastResult.push(currentArray);
        
    }

    // Check if adding the current price exceeds the limit
    if (sum + item.price <= limit) {
        // If it does not exceed, add to the current group's invoices
        currentArray.invoices.push({ increment:m, invoice_total: item.price, seller_name: item.seller_name });
        sum += item.price; // Update the sum
    } else {
        // If it exceeds, start a new group for the next invoice
        m++;
        currentArray = { customer: currentCustomer, invoices: [] };
        currentArray.invoices.push({ increment:m, invoice_total: item.price, seller_name: item.seller_name });
        lastResult.push(currentArray);
        sum = item.price; // Reset sum to the current price
    }
});

// Print the result
console.log(JSON.stringify({ last_result: lastResult }, null, 2));



}

exports.exportExcelNewAction = async (req,res) => {
  
  try {

      const currentDay = Number(req?.query?.currentDay) || moment().date();
      let startDate, endDate, batchMonth, batchYear, voucherDate;

      // await EInvoice.deleteMany({});

      console.log(req.body)
      console.log(req.method)

      if (req.method === "GET") {
        if (currentDay === 1) {
            // startDate = moment().subtract(1, 'months').date(29).format('YYYY-MM-DD') + 'T00:00:00Z';
            // endDate = moment().subtract(1, 'months').endOf('month').toDate();
            startDate = moment().subtract(1, 'months').date(29).format('YYYY-MM-DD');
            endDate = moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD');
            batchMonth = moment().subtract(1, 'months').format('MM');
            voucherDate = moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD');
            batchYear = batchMonth === '01' ? moment().subtract(1, 'years').format('YYYY') : moment().format('YYYY');
        } else if (currentDay === 8) {
            // startDate = moment().startOf('month').toDate();
            // endDate = moment().date(7).endOf('day').toDate();
            startDate = moment().startOf('month').format('YYYY-MM-DD');
            endDate = moment().date(7).endOf('day').format('YYYY-MM-DD');
            batchMonth = moment().format('MM');
            batchYear = moment().format('YYYY');
            voucherDate = moment().format('YYYY-MM-DD');
        } else if (currentDay === 15) {
            // startDate = moment().date(8).format('YYYY-MM-DD') + 'T00:00:00Z';
            // endDate = moment().date(14).format('YYYY-MM-DD') + 'T23:59:59Z';
            startDate = moment().date(8).format('YYYY-MM-DD');
            endDate = moment().date(14).format('YYYY-MM-DD');

            batchMonth = moment().format('MM');
            batchYear = moment().format('YYYY');
            voucherDate = moment().format('YYYY-MM-DD');
        } else if (currentDay === 22) {
            // startDate = moment().date(15).startOf('day').toDate();
            // endDate = moment().date(21).endOf('day').toDate();
            startDate = moment().date(15).startOf('day').format('YYYY-MM-DD');
            endDate = moment().date(21).endOf('day').format('YYYY-MM-DD');

            batchMonth = moment().format('MM');
            batchYear = moment().format('YYYY');
            voucherDate = moment().format('YYYY-MM-DD');
        } else if (currentDay === 29) {
            //2024-10-28T18:29:59.999Z
            // startDate =moment().date(22).format('YYYY-MM-DD') + 'T00:00:00Z';
            // endDate = moment().date(28).format('YYYY-MM-DD') + 'T23:59:59Z';

            startDate =moment().date(22).format('YYYY-MM-DD');
            endDate = moment().date(28).format('YYYY-MM-DD');

            batchMonth = moment().format('MM');
            batchYear = moment().format('YYYY');
            voucherDate = moment().format('YYYY-MM-DD');
        } else {
            // return res.status(400).send('Invalid date for export');
            console.log('Invalid date for export');
            return false;
        }
        
        // return res.status(400).json({ error: "Request body is empty" });
      }else{
        if (Object.keys(req.body).length === 0) {
          console.log("Request body is empty");
        } else {
          startDate = req.body.startDate;
          endDate = req.body.endDate;
          batchMonth = req.body.batchMonth;
          batchYear = req.body.batchYear;
          voucherDate = req.body.voucherDate;
          
          // return res.status(400).json({ error: "Request body is not empty" });
        }
      }

      // console.log(moment.tz(`${startDate} 00:00:00`, "DD", "Asia/Kolkata"))
      const startDateFormat = new Date(startDate);
      const dayForInvoice = String(startDateFormat.getDate()).padStart(2, '0');
      
      // console.log(moment().startOf('month').format('YYYY-MM-DD') )
      
      // console.log(moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD'))
      // return false;
      // startDate = new Date("2024-12-29T00:00:00.000Z");
      // endDate = new Date('2024-12-31T23:59:59Z');
      // batchMonth = '12';
      // // batchYear = moment().format('YYYY');
      // batchYear = '2024';
      // voucherDate = '2024-12-31';

      // console.log(startDate)
      // return false;

      // const dirExcel = `${process.env.INVOICE_ADMIN_URL}Invoice_data_list${moment().format('YYYYMM')}07.xls`;

      // if (fs.existsSync(dirExcel)) {
      //   return res.status(200).json({message: `${dirExcel} exists.`});
      // }


      // Convert the date to the start of the day (00:00:00) in Asia/Kolkata time zone
      const startOfDayKolkata = moment.tz(`${startDate} 00:00:00`, "YYYY-MM-DD HH:mm:ss", "Asia/Kolkata");

      // Convert the date to the end of the day (23:59:59) in Asia/Kolkata time zone
      const endOfDayKolkata = moment.tz(`${endDate} 23:59:59`, "YYYY-MM-DD HH:mm:ss", "Asia/Kolkata");

      // Convert both start and end times to UTC
      const startOfDayUTC = startOfDayKolkata.utc().toDate();
      const endOfDayUTC = endOfDayKolkata.utc().toDate();

      // console.log(startOfDayUTC)
      // console.log(endOfDayUTC)


      // const getOrders1 = await Order.find({
      //     // pStatus: { $in: ['PD', 'PP', 'PR'] },
      //     orderDate: { $gte: startOfDayUTC, $lte: endOfDayUTC },
      //     // merchantId: { $nin: ['6371e830aa051e6d673cd97f', '653aa21a40a92c499f3a5a26', '62f238091e5826109e17b557', '668fd0f9c2269f9c393ee96e'] },
      //     source: 1,
      //     // orderSerial:"1002F2OC101Z010A0000",
      //     // "order_shipping": { $exists: true, $ne: [] }
      // });

      // console.log(getOrders1)
      // return false;
      
      const getOrders = await Order.aggregate([
        {
          $match: {
            pStatus: { $in: ['PD', 'PP', 'PR'] },
            orderDate: { $gte: startOfDayUTC, $lte: endOfDayUTC },
            merchantId: { $nin: ['6371e830aa051e6d673cd97f', '653aa21a40a92c499f3a5a26', '62f238091e5826109e17b557', '668fd0f9c2269f9c393ee96e'] },
            source: { $in: [1, 2] },
            // orderSerial:"1002F2OC101Z010A0000",
            "order_shipping": { $exists: true, $ne: [] }
          },
        },
        {
          $unwind: '$orderDetails', // Unwind the orderDetails array
        },
        {
          $lookup: {
            from: 'users',
            let: { merchantId: { $toObjectId: "$merchantId" } }, // Convert merchantId to ObjectId
            pipeline: [
              {
                $match: {
                  $expr: {
                    $eq: ["$_id", "$$merchantId"]
                  }
                }
              }
            ],
            as: 'customerInfo'
          }
        },
        {
          $lookup: {
            from: 'products',
            localField: 'orderDetails.sku',
            foreignField: 'skus',
            as: 'productInfo',
          },
        },
        {
          $sort: { orderDate: 1 },
        },
      ]);

      if (getOrders.length === 0) {
        // return res.status(200).json({message: 'The orders are not found'});
        console.log('The orders are not found')
        return;
      }

      // const getOrdersData = await Order.find({
      //       pStatus: { $in: ['PD', 'PP', 'PR'] },
      //       orderDate: { $gte: startDate, $lte: endDate },
      //       merchantId: { $nin: ['6371e830aa051e6d673cd97f', '653aa21a40a92c499f3a5a26', '62f238091e5826109e17b557', '668fd0f9c2269f9c393ee96e'] },
      //       source: 1,
      //       // "order_shipping": { $exists: true, $ne: [] }
      // })

      // console.log(getOrders.length)

      // return res.status(200).json({getOrders});
      // console.log(getOrdersData.length)
      // return false;

      const lastResult = [];

      for (const order of getOrders) {

        const customerId = order.merchantId;
        const customerInf = order.customerInfo[0] || {};
        let state_code = '';
        let state_name = '';

        if (customerInf.gstNumber) {
          state_code = customerInf.gstNumber.substring(0, 2);
          state_name = customerInf.state;
        }

        const today = moment();
        let thisYear = today.format('YY');
        let nextYear = today.add(1, 'year').format('YY');
        
        if (today.isBefore(moment().month(3).date(1))) {
          thisYear = today.subtract(1, 'year').format('YY');
          nextYear = today.add(1, 'year').format('YY');
        }

        const voucher_type = 'Sales';
        const TaxInvoiceNOCommer = `OS/${thisYear}-${nextYear}`;

        // console.log(order.productInfo[0])

        const product = order.productInfo[0] || { hsn: '33049110', gst: 18 };
        const hsn_code = product.hsn?product.hsn:'33049110';
        const gst = product.gst?parseInt(product.gst):18;

        const consumerPrice = order?.orderDetails?.finalPrice || 0;
        const consumerQty = order?.orderDetails?.quantity || 0;

        // if(consumerPrice==0){
          // console.log(order.orderSerial+"--"+consumerPrice)
        // }
        

        const basic = consumerPrice / (100 + gst) * 100;
        const amount = basic;
        const rate = basic / consumerQty;
        const igstt = amount * gst / 100;
        const invoice_total = consumerPrice;

        const code_num = customerInf.gstNumber ? customerInf.gstNumber.substring(0, 2) : '';
        let igst = '';
        let cgst = '';
        let sgst = '';
        let sales_ledger = '';

        if (code_num == '06') {
          igst = '0';
          cgst = (igstt / 2).toFixed(2);
          sgst = (igstt / 2).toFixed(2);
          sales_ledger = 'Local Sale';
        } else {
          igst = igstt.toFixed(2);
          cgst = '0';
          sgst = '0';
          sales_ledger = 'Interstate Sale';
        }

        lastResult.push({
          orderSerial: order.orderSerial,
          voucher_type,
          invoice_id: TaxInvoiceNOCommer,
          date_purchased: voucherDate,
          orders_buyer_id: order.merchantId,
          seller_name: customerInf.email,
          state: state_name,
          state_code: state_code,
          sales_ledger,
          item_name: `${hsn_code}-${Math.round(gst)}%`,
          description1: order?.orderDetails?.sku,
          godown: 'Main Location',
          quantity: order?.orderDetails?.quantity,
          rate: rate,
          final_price: rate,
          amount,
          igst,
          cgst,
          sgst,
          invoice_total,
          gst,
          total_tax: amount * gst / 100,
          hsn_code,
          customers_name: order.customers_email_address,
          state_name,
          pd_name: order?.orderDetails?.title,
          product_quantity: order?.orderDetails?.quantity,
          product_price: amount,
          orders_status_payment: order.pStatus,
          billing_company_name: order.recipientName,
          bill_to_place: state_name,
          ship_to_place: state_name,
        });
      }

      // console.log(lastResult);
      // return res.status(200).json({lastResult});
      // return false;

      lastResult.sort((a, b) => {
        // Sort by orders_buyer_id first (ascending)
        if (a.orders_buyer_id < b.orders_buyer_id) return -1;
        if (a.orders_buyer_id > b.orders_buyer_id) return 1;

        // If orders_buyer_id is the same, sort by invoice_total (ascending)
        if (a.invoice_total < b.invoice_total) return -1;
        if (a.invoice_total > b.invoice_total) return 1;

        // If both are equal, keep order unchanged
        return 0;
      });

      // return res.status(200).json({lastResult});
      // return false;

      let j = 0;
      let k = 0;
      const last_result = [];
      const customerArr = [];
      let count = 0;
      let newCount = 1;

    
        lastResult.forEach(order => {
            if (customerArr.includes(order.orders_buyer_id)) {
                // If buyer_id is already in customerArr
                last_result[j] = last_result[j] || []; // Initialize if undefined
                last_result[j].push({
                  voucher_type: order.voucher_type,
                  invoice_id: order.invoice_id,
                  date_purchased: order.date_purchased,
                  invoice_total: order.invoice_total,
                  seller_name: order.seller_name,
                  state: order.state_name,
                  sales_ledger: order.sales_ledger,
                  item_name: `${order.hsn_code}-${Math.round(order.gst)}%`,
                  description1: order.pd_name,
                  description2: '',
                  description3: '',
                  description4: '',
                  description5: '',
                  godown: 'Main Location',
                  quantity: order.product_quantity,
                  rate: order.final_price,
                  amount: order.amount,
                  shipping_charge: '',
                  output_igst: order.igst,
                  output_cgst: order.cgst,
                  output_sgst: order.sgst,
                  packing_charges: '',
                  round_off: '',
                  narration: '',
                  reason_for_issuing: '',
                  buyer_debit_note: '',
                  date: '',
                  gst: order.gst,
                  billing_company_name: order.billing_company_name,
                  ship_to_place: order.ship_to_place,
                  bill_to_place: order.bill_to_place,
                  orderSerial:order.orderSerial
                });
            } else {
                // If buyer_id is not in customerArr
                j++;
                customerArr.push(order.orders_buyer_id);
                last_result[j] = last_result[j] || []; // Initialize if undefined
                last_result[j].push({
                  voucher_type: order.voucher_type,
                  invoice_id: order.invoice_id,
                  date_purchased: order.date_purchased,
                  invoice_total: order.invoice_total,
                  seller_name: order.seller_name,
                  state: order.state_name,
                  sales_ledger: order.sales_ledger,
                  item_name: `${order.hsn_code}-${Math.round(order.gst)}%`,
                  description1: order.pd_name,
                  description2: '',
                  description3: '',
                  description4: '',
                  description5: '',
                  godown: 'Main Location',
                  quantity: order.product_quantity,
                  rate: order.final_price,
                  amount: order.amount,
                  shipping_charge: '',
                  output_igst: order.igst,
                  output_cgst: order.cgst,
                  output_sgst: order.sgst,
                  packing_charges: '',
                  round_off: '',
                  narration: '',
                  reason_for_issuing: '',
                  buyer_debit_note: '',
                  date: '',
                  gst: order.gst,
                  billing_company_name: order.billing_company_name,
                  ship_to_place: order.ship_to_place,
                  bill_to_place: order.bill_to_place,
                  orderSerial:order.orderSerial
                });
            }
        });

      const fetchLatestInvoice = await EInvoice.findOne().sort({ _id: -1 });
      
      let previousMonth = null;
      let previousSellerName = "";

      let m = 0;
      if (fetchLatestInvoice) {
        // Convert the Date object to a string format (e.g., 'YYYY-MM-DD')
        const voucherDateString = moment(fetchLatestInvoice.voucher_date).format('YYYY-MM-DD');
        const voucherdate = voucherDateString.split('-');
        const invoice = fetchLatestInvoice.invoice_no.split('/');

        // Store the month of the latest invoice
        previousMonth = voucherdate[1];

        if (moment().date() === 8 || dayForInvoice=='01') {
          m = 0;
        } else {
          m = parseInt(invoice[3]);
        }
      } else {
        m = 0;
      }

      // console.log(m);
      // console.log(fetchLatestInvoice);
      // return false;

      let i = 0;
      // m += 1;  // Initialize m outside the loop
      //const lastResult = [];
      const lastResultNew = [];
      let count1 = 0;
      let sum = 0;
      let limit = 49500;
      let currentArray = null;
      let currentCustomer = '';

      if (last_result && Array.isArray(last_result)) { // Check if last_result is defined and an array
        for (const [k, arr] of last_result.entries()) {
            // let sum = 0;
            if (!Array.isArray(arr)) {
                console.error(`The element at index ${k} is not an array.`);
                continue;
            }

            //m++; // Increment m at the beginning of each outer loop iteration to start a new batch
            let currentBatchNumber = m;

            for (const [key, val] of arr.entries()) {

                // Get the current month from val.date_purchased
                const currentMonth = moment(val.voucher_date).format('MM');

                const invoiceId = `${val.invoice_id}/${batchMonth}`;
                // console.log(val.seller_name+'----'+key)

                if (val.seller_name !== currentCustomer) {
                  m++;
                  // If we switched customers, start a new group
                  currentCustomer = val.seller_name;
                  sum = 0; // Reset sum for the new customer
                  currentArray = []; // Initialize the array for invoices
                  lastResultNew.push({ invoices: currentArray }); // Wrap it in an object
              }

              if (sum + val.invoice_total <= limit) {
                  // If it does not exceed, add to the current group's invoices
                  currentArray.push({ 
                          invoice_id: invoiceId+'/'+m,
                          voucher_type: val.voucher_type,
                          date_purchased: val.date_purchased, // Use val.date_purchased instead of voucher_date
                          invoice_total: val.invoice_total,
                          seller_name: val.seller_name,
                          state: val.state,
                          sales_ledger: val.sales_ledger,
                          item_name: val.item_name,
                          description1: val.description1,
                          description2: val.description2,
                          description3: val.description3,
                          description4: val.description4,
                          description5: val.description5,
                          godown: val.godown,
                          quantity: val.quantity,
                          rate: parseFloat(val.rate).toFixed(2),
                          amount: parseFloat(val.amount).toFixed(2),
                          shipping_charge: val.shipping_charge,
                          output_igst: val.output_igst,
                          output_cgst: val.output_cgst,
                          output_sgst: val.output_sgst,
                          packing_charges: val.packing_charges,
                          round_off: val.round_off,
                          narration: val.narration,
                          reason_for_issuing: val.reason_for_issuing,
                          buyer_debit_note: val.buyer_debit_note,
                          date: val.date,
                          gst: val.gst,
                          billing_company_name: val.billing_company_name,
                          batch_month: batchMonth,
                          batch_year: batchYear,
                          bill_to_place: val.bill_to_place,
                          ship_to_place: val.ship_to_place,
                          invoicenum:m,
                          orderSerial:val.orderSerial
                  });
                  sum += val.invoice_total; // Update the sum
              } else {
                m++;  
                // If it exceeds, start a new group for the next invoice
                  currentArray = []; // Initialize a new array for the new invoice
                  currentArray.push({ 
                        invoice_id: invoiceId+'/'+m,
                        voucher_type: val.voucher_type,
                        date_purchased: val.date_purchased, // Use val.date_purchased instead of voucher_date
                        invoice_total: val.invoice_total,
                        seller_name: val.seller_name,
                        state: val.state,
                        sales_ledger: val.sales_ledger,
                        item_name: val.item_name,
                        description1: val.description1,
                        description2: val.description2,
                        description3: val.description3,
                        description4: val.description4,
                        description5: val.description5,
                        godown: val.godown,
                        quantity: val.quantity,
                        rate: parseFloat(val.rate).toFixed(2),
                        amount: parseFloat(val.amount).toFixed(2),
                        shipping_charge: val.shipping_charge,
                        output_igst: val.output_igst,
                        output_cgst: val.output_cgst,
                        output_sgst: val.output_sgst,
                        packing_charges: val.packing_charges,
                        round_off: val.round_off,
                        narration: val.narration,
                        reason_for_issuing: val.reason_for_issuing,
                        buyer_debit_note: val.buyer_debit_note,
                        date: val.date,
                        gst: val.gst,
                        billing_company_name: val.billing_company_name,
                        batch_month: batchMonth,
                        batch_year: batchYear,
                        bill_to_place: val.bill_to_place,
                        ship_to_place: val.ship_to_place,
                        invoicenum:m,
                        orderSerial:val.orderSerial
                  });
                  lastResultNew.push({ invoices: currentArray }); // Wrap it in an object
                  sum = val.invoice_total; // Reset sum to the current price
                  
              }

            }
          }
        }

      // console.log(JSON.stringify(lastResultNew));
      // return res.status(200).json({lastResultNew});
      // return false;
      if(lastResultNew.length>0){
        const promises = [];

        // lastResultNew.forEach(async (group, groupIndex) => {
          lastResultNew.forEach((group, groupIndex) => {
          // console.log(`Group ${groupIndex + 1}:`);
          
          // Iterate through each invoice in the group
          group.invoices.forEach(value => {
              // console.log(`  Increment: ${value.increment}, Invoice Total: ${value.invoice_total}, Seller Name: ${value.seller_name}`);
              // console.log(value.orderSerial+'----'+value.description1)

              let description_1 = '';
              if(value.description1==null || value.description1==''){
                description_1 = ''
              }else{
                description_1 = value.description1
              }
              
              if(description_1!=''){
                description_1 = value.description1.replace(/'/g, "");
              }else{
                description_1 = value.description1;
              }
              
                const invoiceData = {
                  voucher_type: value.voucher_type,
                  invoice_no: value.invoice_id,
                  voucher_date: value.date_purchased,
                  party_name: value.seller_name,
                  state_of_supply: value.state || null,
                  sales_ledger: value.sales_ledger,
                  item_name: value.item_name,
                  description_1,
                  description_2: value.description2,
                  description_3: value.description3,
                  description_4: value.description4,
                  description_5: value.description5,
                  godown: value.godown,
                  quantity: value.quantity,
                  rate: value.rate,
                  amount: value.amount,
                  shipping_charge: value.shipping_charge,
                  output_igst: value.output_igst,
                  output_cgst: value.output_cgst,
                  output_sgst: value.output_sgst,
                  packing_charges: value.packing_charges,
                  round_off: value.round_off,
                  invoice_total: value.invoice_total,
                  narration: value.narration,
                  reason_for_issue_note: value.reason_for_issuing,
                  buyer_debit_note_no: value.buyer_debit_note,
                  batch_month: value.batch_month,
                  batch_year: value.batch_year,
                  billing_company_name: value.billing_company_name,
                  bill_to_place: value.bill_to_place,
                  ship_to_place: value.ship_to_place,
                  invoicenum:value.invoicenum,
                  // orderSerial:value.orderSerial
              };

              // console.log(invoiceData)
              // try {
              //     await EInvoice.create(invoiceData);
              //     // console.log(invoiceData)
              // } catch (error) {
              //     console.error(`Failed to insert invoice data for invoice ID ${value.invoice_id}:`, error);
              // }

              
              const promise = EInvoice.create(invoiceData).catch(error => {
                console.error(`Failed to insert invoice data for invoice ID ${value.invoice_id}:`, error);
              });

              promises.push(promise);
              
          });
        });
        // return res.status(200).json({message: "Done"})

        await Promise.all(promises);

        console.log("Done")
        return;
      } else {
        // return res.status(200).json({message: "lastResultNew is not defined or not an array."})
        console.log("lastResultNew is not defined or not an array.")
        return;
      }

    } catch (error) {
      console.log(error);
      // return res.status(500).json({message: error.message})
      console.log(error.message)
      return;
    }
  
};

exports.exportExcelNewAction_new2 = async (req, res) => {
  try {

    const currentDay = Number(req?.query?.currentDay) || moment().date();
    let startDate, endDate, batchMonth, batchYear, voucherDate;

    if (currentDay === 1) {        
      startDate = moment().subtract(1, 'months').date(29).startOf('day').toDate();
      endDate = moment().subtract(1, 'months').endOf('month').toDate();
      batchMonth = moment().subtract(1, 'months').format('MM');
      voucherDate = moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD');
      batchYear = batchMonth === '01' ? moment().subtract(1, 'years').format('YYYY') : moment().format('YYYY');
    } else if (currentDay === 10) {
      startDate = moment().startOf('month').toDate();
      endDate = moment().date(7).endOf('day').toDate();
      batchMonth = moment().format('MM');
      batchYear = moment().format('YYYY');
      voucherDate = moment().format('YYYY-MM-DD');
    } else if (currentDay === 15) {
      startDate = moment().date(8).startOf('day').toDate();
      endDate = moment().date(14).endOf('day').toDate();
      batchMonth = moment().format('MM');
      batchYear = moment().format('YYYY');
      voucherDate = moment().format('YYYY-MM-DD');
    } else if (currentDay === 22) {
      startDate = moment().date(15).startOf('day').toDate();
      endDate = moment().date(21).endOf('day').toDate();
      batchMonth = moment().format('MM');
      batchYear = moment().format('YYYY');
      voucherDate = moment().format('YYYY-MM-DD');
    } else if (currentDay === 29) {
      startDate = moment().date(22).startOf('day').toDate();
      endDate = moment().date(28).endOf('day').toDate();
      batchMonth = moment().format('MM');
      batchYear = moment().format('YYYY');
      voucherDate = moment().format('YYYY-MM-DD');
    } else {
      return res.status(400).send('Invalid date for export');
    }

    const dirExcel = `${process.env.INVOICE_ADMIN_URL}Invoice_data_list${moment().format('YYYYMM')}07.xls`;
    if (fs.existsSync(dirExcel)) {
      return res.status(200).json({ message: `${dirExcel} exists.` });
    }

    const batchSize = 1; // Define the chunk size
    let skip = 0;
    let hasMoreData = true;

    while (hasMoreData) {

      const getOrders = await Order.aggregate([
        {
          $match: {
            pStatus: { $in: ['PD', 'PP', 'PR'] },
            orderDate: { $gte: startDate, $lte: endDate },
            source: 1,
          },
        },
        { $unwind: '$orderDetails' },
        {
          $lookup: {
            from: 'users',
            let: { merchantId: { $toObjectId: "$merchantId" } },
            pipeline: [
              { $match: { $expr: { $eq: ["$_id", "$$merchantId"] } } }
            ],
            as: 'customerInfo'
          },
        },
        {
          $lookup: {
            from: 'products',
            localField: 'orderDetails.sku',
            foreignField: 'skus',
            as: 'productInfo',
          },
        },
        { $sort: { orderDate: 1 } },
        { $skip: skip },
        { $limit: batchSize },
      ]);

      if (getOrders.length === 0) {
        hasMoreData = false; // Exit the loop if there are no more records
        break;
      }

      const lastResult = [];

      for (const order of getOrders) {

        const customerId = order.merchantId;
        const customerInf = order.customerInfo[0] || {};
        let state_code = '';
        let state_name = '';

        if (customerInf.gstNumber) {
          state_code = customerInf.gstNumber.substring(0, 2);
          state_name = customerInf.state;
        }

        const today = moment();
        let thisYear = today.format('YY');
        let nextYear = today.add(1, 'year').format('YY');
        
        if (today.isBefore(moment().month(3).date(1))) {
          thisYear = today.subtract(1, 'year').format('YY');
          nextYear = today.add(1, 'year').format('YY');
        }

        const voucher_type = 'Sales';
        const TaxInvoiceNOCommer = `OS/${thisYear}-${nextYear}`;

        const product = order.productInfo[0] || { hsn: '33049110', gst: 18 };
        const hsn_code = product.hsn;
        const gst = product.gst;

        const basic = order.orderDetails.consumerPrice / (100 + gst) * 100;
        const amount = basic;
        const rate = basic / order.orderDetails.quantity;
        const igstt = amount * gst / 100;
        const invoice_total = order.orderDetails.consumerPrice;

        const code_num = customerInf.gstNumber ? customerInf.gstNumber.substring(0, 2) : '';
        let igst = '';
        let cgst = '';
        let sgst = '';
        let sales_ledger = '';

        if (code_num == '06') {
          igst = '0';
          cgst = (igstt / 2).toFixed(2);
          sgst = (igstt / 2).toFixed(2);
          sales_ledger = 'Local Sale';
        } else {
          igst = igstt.toFixed(2);
          cgst = '0';
          sgst = '0';
          sales_ledger = 'Interstate Sale';
        }

        lastResult.push({
          voucher_type,
          invoice_id: TaxInvoiceNOCommer,
          date_purchased: voucherDate,
          orders_buyer_id: order.merchantId,
          seller_name: customerInf.email,
          state: state_name,
          state_code: state_code,
          sales_ledger,
          item_name: `${hsn_code}-${Math.round(gst)}%`,
          description1: order?.orderDetails?.sku,
          godown: 'Main Location',
          quantity: order?.orderDetails?.quantity,
          rate: rate,
          final_price: rate,
          amount,
          igst,
          cgst,
          sgst,
          invoice_total,
          gst,
          total_tax: amount * gst / 100,
          hsn_code,
          customers_name: order.customers_email_address,
          state_name,
          pd_name: order?.orderDetails?.title,
          product_quantity: order?.orderDetails?.quantity,
          product_price: amount,
          orders_status_payment: order.pStatus,
          billing_company_name: order.recipientName,
        });
      }

      lastResult.sort((a, b) => {
        // Sort by orders_buyer_id first
        if (a.orders_buyer_id < b.orders_buyer_id) return -1;
        if (a.orders_buyer_id > b.orders_buyer_id) return 1;

        // If orders_buyer_id is the same, sort by invoice_total
        if (a.invoice_total < b.invoice_total) return -1;
        if (a.invoice_total > b.invoice_total) return 1;

        return 0;
      });

      let j = 0;
      let k = 0;
      const last_result = [];
      const customerArr = [];
      let count = 0;

      for (const order of lastResult) {
        if (customerArr.includes(order.orders_buyer_id)) {
            last_result[j].push({
                voucher_type: order.voucher_type,
                invoice_id: order.invoice_id,
                date_purchased: order.date_purchased,
                invoice_total: order.invoice_total,
                seller_name: order.seller_name,
                state: order.state_name,
                sales_ledger: order.sales_ledger,
                item_name: `${order.hsn_code}-${Math.round(order.gst)}%`,
                description1: order.pd_name,
                description2: '',
                description3: '',
                description4: '',
                description5: '',
                godown: 'Main Location',
                quantity: order.product_quantity,
                rate: order.final_price,
                amount: order.amount,
                shipping_charge: '',
                output_igst: order.igst,
                output_cgst: order.cgst,
                output_sgst: order.sgst,
                packing_charges: '',
                round_off: '',
                narration: '',
                reason_for_issuing: '',
                buyer_debit_note: '',
                date: '',
                gst: order.gst,
                billing_company_name: order.billing_company_name
            });
        } else {
            j++;
            customerArr.push(order.orders_buyer_id);
            last_result[j] = [{
                voucher_type: order.voucher_type,
                invoice_id: order.invoice_id,
                date_purchased: order.date_purchased,
                invoice_total: order.invoice_total,
                seller_name: order.seller_name,
                state: order.state_name,
                sales_ledger: order.sales_ledger,
                item_name: `${order.hsn_code}-${Math.round(order.gst)}%`,
                description1: order.pd_name,
                description2: '',
                description3: '',
                description4: '',
                description5: '',
                godown: 'Main Location',
                quantity: order.product_quantity,
                rate: order.final_price,
                amount: order.amount,
                shipping_charge: '',
                output_igst: order.igst,
                output_cgst: order.cgst,
                output_sgst: order.sgst,
                packing_charges: '',
                round_off: '',
                narration: '',
                reason_for_issuing: '',
                buyer_debit_note: '',
                date: '',
                gst: order.gst,
                billing_company_name: order.billing_company_name
            }];
        }
      }

      // console.log(lastResult);
      // return res.status(200).json({lastResult});
      // return false;

      const fetchLatestInvoice = await EInvoice.findOne().sort({ _id: -1 });
      let m = 0;

      let previousMonth = null;
      let previousSellerName = "";

      if (fetchLatestInvoice) {
        // Convert the Date object to a string format (e.g., 'YYYY-MM-DD')
        const voucherDateString = moment(fetchLatestInvoice.voucher_date).format('YYYY-MM-DD');
        const voucherdate = voucherDateString.split('-');
        const invoice = fetchLatestInvoice.invoice_no.split('/');

        // Store the month of the latest invoice
        previousMonth = voucherdate[1];

        if (moment().date() === 8) {
          m = 0;
        } else {
          m = parseInt(invoice[3]);
        }
      } else {
        m = 0;
      }

      // console.log(m+1);
      // console.log(fetchLatestInvoice);
      // return false;

      let i = 0;
      m += 1;  // Initialize m outside the loop
      //const lastResult = [];
      const lastResultNew = [];
      let sum = 0;

      if (last_result && Array.isArray(last_result)) { // Check if last_result is defined and an array
          for (const [k, arr] of last_result.entries()) {
              // let sum = 0;
              if (!Array.isArray(arr)) {
                  console.error(`The element at index ${k} is not an array.`);
                  continue;
              }

              //m++; // Increment m at the beginning of each outer loop iteration to start a new batch
              let currentBatchNumber = m;

              for (const [key, val] of arr.entries()) {

                  // Get the current month from val.date_purchased
                  const currentMonth = moment(val.voucher_date).format('MM');

                  // Check if the month has changed
                  // if (previousMonth && currentMonth !== previousMonth) {
                  //     m = 1;  // Reset batch number to 1 if the month changes
                  //     currentBatchNumber = m;
                  //     previousMonth = currentMonth;  // Update previousMonth to the current month
                  // }

                  sum += val.invoice_total;

                  //check if the seller has changed
                  if (val.seller_name !== previousSellerName && previousSellerName !== "") {
                      m++;  // Increment the batch number
                      currentBatchNumber = m;  // Update the current batch number
                      sum = 0;  // Reset the sum when the seller changes
                  }

                  //update invoice 49500
                  if (sum > 49500) {
                      i++;
                      m++;
                      currentBatchNumber = m;
                      //sum = 0; // Reset sum for the new batch
                      sum = val.invoice_total;
                  }

                  const invoiceId = `${val.invoice_id}/${batchMonth}/${currentBatchNumber}`;

                  // Store the current seller name to compare in the next iteration
                  previousSellerName = val.seller_name;

                  //console.log(`${val.invoice_total} + ${sum} = ${val.invoice_total+sum}`, '---', invoiceId);

                  //sum += val.invoice_total;

                  // if (!lastResult[k]) {
                  //     lastResult[k] = [];
                  // }
                  // if (!lastResult[k][i]) {
                  //     lastResult[k][i] = [];
                  // }
                  if (!lastResultNew[k]) {
                      lastResultNew[k] = [];
                  }
                  if (!lastResultNew[k][i]) {
                      lastResultNew[k][i] = [];
                  }

                  lastResultNew[k][i].push({
                      invoice_id: invoiceId,
                      voucher_type: val.voucher_type,
                      date_purchased: val.date_purchased, // Use val.date_purchased instead of voucher_date
                      invoice_total: val.invoice_total,
                      seller_name: val.seller_name,
                      state: val.state,
                      sales_ledger: val.sales_ledger,
                      item_name: val.item_name,
                      description1: val.description1,
                      description2: val.description2,
                      description3: val.description3,
                      description4: val.description4,
                      description5: val.description5,
                      godown: val.godown,
                      quantity: val.quantity,
                      rate: parseFloat(val.rate).toFixed(2),
                      amount: parseFloat(val.amount).toFixed(2),
                      shipping_charge: val.shipping_charge,
                      output_igst: val.output_igst,
                      output_cgst: val.output_cgst,
                      output_sgst: val.output_sgst,
                      packing_charges: val.packing_charges,
                      round_off: val.round_off,
                      narration: val.narration,
                      reason_for_issuing: val.reason_for_issuing,
                      buyer_debit_note: val.buyer_debit_note,
                      date: val.date,
                      gst: val.gst,
                      billing_company_name: val.billing_company_name,
                      batch_month: batchMonth,
                      batch_year: batchYear
                  });
              }
          }
      }

      //console.log(lastResultNew);
      //return res.status(200).json({lastResultNew});
      // return false;

      i = 1;
      if (lastResultNew && Array.isArray(lastResultNew)) {
        
        for (const [key, arr] of lastResultNew.entries()) {
            if (Array.isArray(arr)) {
                for (const arr1 of arr) {
                    if (Array.isArray(arr1)) {
                        for (const value of arr1) {
                            if (value) { // Ensure value is not null or undefined
                                i++;

                                const description_1 = value.description1.replace(/'/g, "");

                                const invoiceData = {
                                    voucher_type: value.voucher_type,
                                    invoice_no: value.invoice_id,
                                    voucher_date: value.date_purchased,
                                    party_name: value.seller_name,
                                    state_of_supply: value.state || null,
                                    sales_ledger: value.sales_ledger,
                                    item_name: value.item_name,
                                    description_1,
                                    description_2: value.description2,
                                    description_3: value.description3,
                                    description_4: value.description4,
                                    description_5: value.description5,
                                    godown: value.godown,
                                    quantity: value.quantity,
                                    rate: value.rate,
                                    amount: value.amount,
                                    shipping_charge: value.shipping_charge,
                                    output_igst: value.output_igst,
                                    output_cgst: value.output_cgst,
                                    output_sgst: value.output_sgst,
                                    packing_charges: value.packing_charges,
                                    round_off: value.round_off,
                                    invoice_total: value.invoice_total,
                                    narration: value.narration,
                                    reason_for_issue_note: value.reason_for_issuing,
                                    buyer_debit_note_no: value.buyer_debit_note,
                                    batch_month: value.batch_month,
                                    batch_year: value.batch_year,
                                    billing_company_name: value.billing_company_name
                                };

                                // console.log(invoiceData, 'Final Data');

                                try {
                                    await EInvoice.create(invoiceData);
                                } catch (error) {
                                    console.error(`Failed to insert invoice data for invoice ID ${value.invoice_id}:`, error);
                                }
                            }
                        }
                    }
                }
            }
        }

      }

      // Increment the skip to fetch the next batch in the next loop iteration
      skip += batchSize;
    }

    return res.status(200).json({ message: "Done" });
    
  } catch (error) {
    console.error(error);
    return res.status(500).json({ message: error.message });
  }
};

exports.exportExcelNewAction_old = async (req,res) => {
  
  try {

      const currentDay = Number(req?.query?.currentDay) || moment().date();
      let startDate, endDate, batchMonth, batchYear, voucherDate;

      // if (currentDay === 1) {        
      //   startDate = moment().subtract(1, 'months').date(29).startOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   endDate = moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD HH:mm:ss');
      //   batchMonth = moment().subtract(1, 'months').format('MM');
      //   voucherDate = moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD');
      //   batchYear = batchMonth === '01' ? moment().subtract(1, 'years').format('YYYY') : moment().format('YYYY');
      // } else if (currentDay === 8) {
      //   startDate = moment().startOf('month').format('YYYY-MM-DD HH:mm:ss');
      //   endDate = moment().date(7).endOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   batchMonth = moment().format('MM');
      //   batchYear = moment().format('YYYY');
      //   voucherDate = moment().format('YYYY-MM-DD');
      // } else if (currentDay === 15) {
      //   startDate = moment().date(8).startOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   endDate = moment().date(14).endOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   batchMonth = moment().format('MM');
      //   batchYear = moment().format('YYYY');
      //   voucherDate = moment().format('YYYY-MM-DD');
      // } else if (currentDay === 22) {
      //   startDate = moment().date(15).startOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   endDate = moment().date(21).endOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   batchMonth = moment().format('MM');
      //   batchYear = moment().format('YYYY');
      //   voucherDate = moment().format('YYYY-MM-DD');
      // } else if (currentDay === 29) {
      //   startDate = moment().date(22).startOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   endDate = moment().date(28).endOf('day').format('YYYY-MM-DD HH:mm:ss');
      //   batchMonth = moment().format('MM');
      //   batchYear = moment().format('YYYY');
      //   voucherDate = moment().format('YYYY-MM-DD');
      // } else {
      //   return res.status(400).send('Invalid date for export');
      // }

      if (currentDay === 1) {        
          startDate = moment().subtract(1, 'months').date(29).startOf('day').toDate();
          endDate = moment().subtract(1, 'months').endOf('month').toDate();
          batchMonth = moment().subtract(1, 'months').format('MM');
          voucherDate = moment().subtract(1, 'months').endOf('month').format('YYYY-MM-DD');
          batchYear = batchMonth === '01' ? moment().subtract(1, 'years').format('YYYY') : moment().format('YYYY');
      } else if (currentDay === 8) {
          startDate = moment().startOf('month').toDate();
          endDate = moment().date(7).endOf('day').toDate();
          batchMonth = moment().format('MM');
          batchYear = moment().format('YYYY');
          voucherDate = moment().format('YYYY-MM-DD');
      } else if (currentDay === 15) {
          startDate = moment().date(8).startOf('day').toDate();
          endDate = moment().date(14).endOf('day').toDate();
          batchMonth = moment().format('MM');
          batchYear = moment().format('YYYY');
          voucherDate = moment().format('YYYY-MM-DD');
      } else if (currentDay === 22) {
          startDate = moment().date(15).startOf('day').toDate();
          endDate = moment().date(21).endOf('day').toDate();
          batchMonth = moment().format('MM');
          batchYear = moment().format('YYYY');
          voucherDate = moment().format('YYYY-MM-DD');
      } else if (currentDay === 29) {
          startDate = moment().date(22).startOf('day').toDate();
          endDate = moment().date(28).endOf('day').toDate();
          batchMonth = moment().format('MM');
          batchYear = moment().format('YYYY');
          voucherDate = moment().format('YYYY-MM-DD');
      } else {
          return res.status(400).send('Invalid date for export');    
      }

      const dirExcel = `${process.env.INVOICE_ADMIN_URL}Invoice_data_list${moment().format('YYYYMM')}07.xls`;

      if (fs.existsSync(dirExcel)) {
        return res.status(200).json({message: `${dirExcel} exists.`});
      }

      // startDate = new Date('2024-06-01T00:00:00Z');
      // endDate = new Date('2024-06-07T23:59:59Z');
      // batchMonth = '06';
      // voucherDate = new Date('2024-06-08T00:00:00Z');
      // batchYear = '2024';

      // startDate = new Date('2024-06-08T00:00:00Z');
      // endDate = new Date('2024-06-14T23:59:59Z');
      // batchMonth = '06';
      // voucherDate = new Date('2024-06-15T00:00:00Z');
      // batchYear = '2024';

      const getOrders = await Order.aggregate([
        {
          $match: {
            pStatus: { $in: ['PD', 'PP', 'PR'] },
            orderDate: { $gte: startDate, $lte: endDate },
            // merchantId: { $nin: ['63974a766862885aadd627b4', '62a98a36162d2eb97b56e4ed', '63974a766862885aadd627b4'] },
            source: 1
          }
        },
        {
          $unwind: '$orderDetails'  // Unwind the orderDetails array
        },
        {
          $sort: { orderDate: 1 }
        }
      ]);

      if (getOrders.length === 0) {
        return res.status(200).json({message: 'The orders are not found'});
      }

      // return res.status(200).json({getOrders});
      // return false;

      const lastResult = [];

      for (const order of getOrders) {

        const customerId = order.merchantId;
        const customerInf = await User.findOne({_id: mongoose.Types.ObjectId(customerId)});
        let state_code = '';
        let state_name = '';

        if (customerInf && customerInf.gstNumber) {
          state_code = customerInf.gstNumber.substring(0, 2);
          state_name = customerInf.state;
        }

        const today = moment();
        let thisYear = today.format('YY');
        let nextYear = today.add(1, 'year').format('YY');
        
        if (today.isBefore(moment().month(3).date(1))) {
          thisYear = today.subtract(1, 'year').format('YY');
          nextYear = today.add(1, 'year').format('YY');
        }

        const voucher_type = 'Sales';
        const TaxInvoiceNOCommer = `OS/${thisYear}-${nextYear}`;

        //const seller_details = await TaxInvoiceService_live.getSellerFromCustomer(order.orders_seller_id);

        let hsn_code = '';
        let gst = '';

        const getProduct = await Product.findOne({skus: { $in: [order.orderDetails.sku] }});

        if (getProduct) {
          hsn_code = getProduct.hsn;
          gst = getProduct.gst;
        } else {
          hsn_code = '33049110';
          gst = 18;
        }

        const basic = order.orderDetails.consumerPrice / (100 + gst) * 100;
        const amount = basic;
        const rate = basic / order.orderDetails.quantity;

        const igstt = amount * gst / 100;
        const invoice_total = order.orderDetails.consumerPrice;

        const code_num = customerInf && customerInf.gstNumber ? customerInf.gstNumber.substring(0, 2) : '';
        let igst = '';
        let cgst = '';
        let sgst = '';
        let sales_ledger = '';

        if (code_num == '06') {
          igst = '0';
          cgst = (igstt / 2).toFixed(2);
          sgst = (igstt / 2).toFixed(2);
          sales_ledger = 'Local Sale';
        } else {
          igst = igstt.toFixed(2);
          cgst = '0';
          sgst = '0';
          sales_ledger = 'Interstate Sale';
        }

        lastResult.push({
          voucher_type,
          invoice_id: TaxInvoiceNOCommer,
          // date_purchased: order.orderDate,
          date_purchased: voucherDate,
          orders_buyer_id: order.merchantId,
          seller_name: customerInf?.email,
          state: state_name,
          state_code: state_code,
          sales_ledger,
          item_name: `${hsn_code}-${Math.round(gst)}%`,
          description1: order?.orderDetails?.sku,
          description2: '',
          description3: '',
          description4: '',
          description5: '',
          godown: 'Main Location',
          quantity: order?.orderDetails?.quantity,
          rate: rate,
          final_price: rate,
          amount,
          shipping_charge: '',
          igst,
          cgst,
          sgst,
          packing_charges: '',
          round_off: '',
          invoice_total,
          narration: '',
          reason_for_issuing: '',
          buyer_debit_note: '',
          date: '',
          gst,
          total_tax: amount * gst / 100,
          hsn_code,
          customers_name: order.customers_email_address,
          state_name,
          pd_name: order?.orderDetails?.title,
          product_quantity: order?.orderDetails?.quantity,
          product_price: amount,
          orders_status_payment: order.pStatus,
          billing_company_name: order.recipientName,
        });
      }

      lastResult.sort((a, b) => {
        // Sort by orders_buyer_id first
        if (a.orders_buyer_id < b.orders_buyer_id) return -1;
        if (a.orders_buyer_id > b.orders_buyer_id) return 1;

        // If orders_buyer_id is the same, sort by invoice_total
        if (a.invoice_total < b.invoice_total) return -1;
        if (a.invoice_total > b.invoice_total) return 1;

        return 0;
      });      

      let j = 0;
      let k = 0;
      const last_result = [];
      const customerArr = [];
      let count = 0;

      for (const order of lastResult) {
        if (customerArr.includes(order.orders_buyer_id)) {
            last_result[j].push({
                voucher_type: order.voucher_type,
                invoice_id: order.invoice_id,
                date_purchased: order.date_purchased,
                invoice_total: order.invoice_total,
                seller_name: order.seller_name,
                state: order.state_name,
                sales_ledger: order.sales_ledger,
                item_name: `${order.hsn_code}-${Math.round(order.gst)}%`,
                description1: order.pd_name,
                description2: '',
                description3: '',
                description4: '',
                description5: '',
                godown: 'Main Location',
                quantity: order.product_quantity,
                rate: order.final_price,
                amount: order.amount,
                shipping_charge: '',
                output_igst: order.igst,
                output_cgst: order.cgst,
                output_sgst: order.sgst,
                packing_charges: '',
                round_off: '',
                narration: '',
                reason_for_issuing: '',
                buyer_debit_note: '',
                date: '',
                gst: order.gst,
                billing_company_name: order.billing_company_name
            });
        } else {
            j++;
            customerArr.push(order.orders_buyer_id);
            last_result[j] = [{
                voucher_type: order.voucher_type,
                invoice_id: order.invoice_id,
                date_purchased: order.date_purchased,
                invoice_total: order.invoice_total,
                seller_name: order.seller_name,
                state: order.state_name,
                sales_ledger: order.sales_ledger,
                item_name: `${order.hsn_code}-${Math.round(order.gst)}%`,
                description1: order.pd_name,
                description2: '',
                description3: '',
                description4: '',
                description5: '',
                godown: 'Main Location',
                quantity: order.product_quantity,
                rate: order.final_price,
                amount: order.amount,
                shipping_charge: '',
                output_igst: order.igst,
                output_cgst: order.cgst,
                output_sgst: order.sgst,
                packing_charges: '',
                round_off: '',
                narration: '',
                reason_for_issuing: '',
                buyer_debit_note: '',
                date: '',
                gst: order.gst,
                billing_company_name: order.billing_company_name
            }];
        }
      }

      // console.log(lastResult);
      // return res.status(200).json({lastResult});
      // return false;

      // const fetchLatestInvoice = await EInvoice.findOne({ billing_company_name: 'Shopperr' }).sort({ _id: -1 });
      const fetchLatestInvoice = await EInvoice.findOne().sort({ _id: -1 });
      let m = 0;

      let previousMonth = null;
      let previousSellerName = "";

      if (fetchLatestInvoice) {
        // Convert the Date object to a string format (e.g., 'YYYY-MM-DD')
        const voucherDateString = moment(fetchLatestInvoice.voucher_date).format('YYYY-MM-DD');
        const voucherdate = voucherDateString.split('-');
        const invoice = fetchLatestInvoice.invoice_no.split('/');

        // Store the month of the latest invoice
        previousMonth = voucherdate[1];

        if (moment().date() === 8) {
          m = 0;
        } else {
          m = parseInt(invoice[3]);
        }
      } else {
        m = 0;
      }

      // console.log(m+1);
      // console.log(fetchLatestInvoice);
      // return false;

      let i = 0;
      m += 1;  // Initialize m outside the loop
      //const lastResult = [];
      const lastResultNew = [];
      let sum = 0;

      if (last_result && Array.isArray(last_result)) { // Check if last_result is defined and an array
          for (const [k, arr] of last_result.entries()) {
              // let sum = 0;
              if (!Array.isArray(arr)) {
                  console.error(`The element at index ${k} is not an array.`);
                  continue;
              }

              //m++; // Increment m at the beginning of each outer loop iteration to start a new batch
              let currentBatchNumber = m;

              for (const [key, val] of arr.entries()) {

                  // Get the current month from val.date_purchased
                  const currentMonth = moment(val.voucher_date).format('MM');

                  // Check if the month has changed
                  // if (previousMonth && currentMonth !== previousMonth) {
                  //     m = 1;  // Reset batch number to 1 if the month changes
                  //     currentBatchNumber = m;
                  //     previousMonth = currentMonth;  // Update previousMonth to the current month
                  // }

                  sum += val.invoice_total;

                  //check if the seller has changed
                  if (val.seller_name !== previousSellerName && previousSellerName !== "") {
                      m++;  // Increment the batch number
                      currentBatchNumber = m;  // Update the current batch number
                      sum = 0;  // Reset the sum when the seller changes
                  }

                  //update invoice 49500
                  if (sum > 49500) {
                      i++;
                      m++;
                      currentBatchNumber = m;
                      //sum = 0; // Reset sum for the new batch
                      sum = val.invoice_total;
                  }

                  const invoiceId = `${val.invoice_id}/${batchMonth}/${currentBatchNumber}`;

                  // Store the current seller name to compare in the next iteration
                  previousSellerName = val.seller_name;

                  //console.log(`${val.invoice_total} + ${sum} = ${val.invoice_total+sum}`, '---', invoiceId);

                  //sum += val.invoice_total;

                  // if (!lastResult[k]) {
                  //     lastResult[k] = [];
                  // }
                  // if (!lastResult[k][i]) {
                  //     lastResult[k][i] = [];
                  // }
                  if (!lastResultNew[k]) {
                      lastResultNew[k] = [];
                  }
                  if (!lastResultNew[k][i]) {
                      lastResultNew[k][i] = [];
                  }

                  lastResultNew[k][i].push({
                      invoice_id: invoiceId,
                      voucher_type: val.voucher_type,
                      date_purchased: val.date_purchased, // Use val.date_purchased instead of voucher_date
                      invoice_total: val.invoice_total,
                      seller_name: val.seller_name,
                      state: val.state,
                      sales_ledger: val.sales_ledger,
                      item_name: val.item_name,
                      description1: val.description1,
                      description2: val.description2,
                      description3: val.description3,
                      description4: val.description4,
                      description5: val.description5,
                      godown: val.godown,
                      quantity: val.quantity,
                      rate: parseFloat(val.rate).toFixed(2),
                      amount: parseFloat(val.amount).toFixed(2),
                      shipping_charge: val.shipping_charge,
                      output_igst: val.output_igst,
                      output_cgst: val.output_cgst,
                      output_sgst: val.output_sgst,
                      packing_charges: val.packing_charges,
                      round_off: val.round_off,
                      narration: val.narration,
                      reason_for_issuing: val.reason_for_issuing,
                      buyer_debit_note: val.buyer_debit_note,
                      date: val.date,
                      gst: val.gst,
                      billing_company_name: val.billing_company_name,
                      batch_month: batchMonth,
                      batch_year: batchYear
                  });
              }
          }
      }

      // console.log(lastResultNew);
      // return res.status(200).json({lastResultNew});
      // return false;


      // let i = 1;
      i = 1;
      if (lastResultNew && Array.isArray(lastResultNew)) {
        
        for (const [key, arr] of lastResultNew.entries()) {
            if (Array.isArray(arr)) {
                for (const arr1 of arr) {
                    if (Array.isArray(arr1)) {
                        for (const value of arr1) {
                            if (value) { // Ensure value is not null or undefined
                                i++;

                                const description_1 = value.description1.replace(/'/g, "");

                                const invoiceData = {
                                    voucher_type: value.voucher_type,
                                    invoice_no: value.invoice_id,
                                    voucher_date: value.date_purchased,
                                    party_name: value.seller_name,
                                    state_of_supply: value.state || null,
                                    sales_ledger: value.sales_ledger,
                                    item_name: value.item_name,
                                    description_1,
                                    description_2: value.description2,
                                    description_3: value.description3,
                                    description_4: value.description4,
                                    description_5: value.description5,
                                    godown: value.godown,
                                    quantity: value.quantity,
                                    rate: value.rate,
                                    amount: value.amount,
                                    shipping_charge: value.shipping_charge,
                                    output_igst: value.output_igst,
                                    output_cgst: value.output_cgst,
                                    output_sgst: value.output_sgst,
                                    packing_charges: value.packing_charges,
                                    round_off: value.round_off,
                                    invoice_total: value.invoice_total,
                                    narration: value.narration,
                                    reason_for_issue_note: value.reason_for_issuing,
                                    buyer_debit_note_no: value.buyer_debit_note,
                                    batch_month: value.batch_month,
                                    batch_year: value.batch_year,
                                    billing_company_name: value.billing_company_name
                                };

                                // console.log(invoiceData, 'Final Data');

                                try {
                                    await EInvoice.create(invoiceData);
                                } catch (error) {
                                    console.error(`Failed to insert invoice data for invoice ID ${value.invoice_id}:`, error);
                                }
                            }
                        }
                    }
                }
            }
        }

        return res.status(200).json({message: "Done"})

      } else {
          return res.status(200).json({message: "lastResultNew is not defined or not an array."})
      }

    } catch (error) {
      console.log(error);
      return res.status(500).json({message: error.message})
    }
  
};

exports.getBatch = async (req,res) => {
      const { year, month } = req.query;

      if (!year || !month) {
          return res.status(200).json({ message: 'Year and Month are required', error: true });
      }

      const invoices = await EInvoice.distinct("voucher_date", {
        batch_year: year,
        batch_month: month
      });

      let dropdownOptions = '';
      
      if(invoices.length>0){
        let count = 1
        invoices.forEach((item) => {
          const date = new Date(item);

          // Extract the year, month, and day
          const year = date.getFullYear();
          const month = String(date.getMonth() + 1).padStart(2, '0');  // getMonth() returns 0-indexed months
          const day = String(date.getDate()).padStart(2, '0');

          // Format in "YYYY-MM-DD"
          const formattedDate = `${year}-${month}-${day}`;

          dropdownOptions += `<option ${count === 1 ? 'selected' : ''} value="${formattedDate}">Batch ${count}</option>`;
          count++;
        })
      }else{
        return res.status(200).json({ message: 'No batches found for the specified date range.', error: true });
      }

      return res.status(200).json({dropdown: `${dropdownOptions}`, error: false});

      // console.log(dropdownOptions)
}

exports.getBatch_old = async (req,res) => {
  
  const userKey = req?.headers?.userkey || null;
  const userToken = req?.headers?.usertoken || null;

  // if(userKey == ID && userToken == SECRET){
    
    try {

      const { year, month } = req.query;

        // Validate year and month inputs
        if (!year || !month) {
            return res.status(200).json({ message: 'Year and Month are required', error: true });
        }

        // Parse year and month
        const yearInt = parseInt(year);
        const monthInt = parseInt(month);

        // Initialize batch configurations
        const batchConfig = [
            { startDay: 1, endDay: 7 },
            { startDay: 8, endDay: 14 },
            { startDay: 15, endDay: 21 },
            { startDay: 22, endDay: 28 },
            { startDay: 29, endDay: moment([yearInt, monthInt - 1]).daysInMonth() }  // Last day of the month
        ];

        let batches = {};
        let dropdownOptions = '';

        // Loop over the batch configurations and find invoices in each batch
        for (let i = 0; i < batchConfig.length; i++) {
            const { startDay, endDay } = batchConfig[i];

            // Define start and end dates for the batch
            const startDate = moment([yearInt, monthInt - 1, startDay]).startOf('day').toDate();
            const endDate = moment([yearInt, monthInt - 1, endDay]).endOf('day').toDate();

            const invoices = await EInvoice.find({
                voucher_date: {
                    $gte: startDate,
                    $lte: endDate
                },
                batch_year: year,
                batch_month: month
            }).sort({ voucher_date: 1 });

            // console.log(invoices)


            if (invoices.length > 0) {
                // Create a dropdown option for the current batch
                // dropdownOptions += `<option value="Batch ${i + 1}">Batch ${i + 1} (${moment(startDate).format('DD MMM')} - ${moment(endDate).format('DD MMM')})</option>`;
                console.log(i)
                dropdownOptions += `<option ${i === 0 ? 'selected' : ''} value="Batch ${i + 1}">Batch ${i + 1}</option>`;
            }

            // Assign invoices to the corresponding batch
            batches[`Batch ${i + 1}`] = invoices;
        }

        // If batch data found, return dropdown options
        if (dropdownOptions) {
            return res.status(200).json({dropdown: `${dropdownOptions}`, error: false});
        } else {
            return res.status(200).json({ message: 'No batches found for the specified date range.', error: true });
        }

    } catch (error) {
      console.log(error);
      return res.status(500).json({message: error.message, error: true})
    }
    
  // } else {
  //   return res.status(500).json({message: "Authentication Error", error: true})
  // }
  
};

// Function to get date range for the batch
function getDateRangeForBatch(year, month, batch) {
    const batchConfig = {
        'Batch 1': { startDay: 1, endDay: 7 },
        'Batch 2': { startDay: 8, endDay: 14 },
        'Batch 3': { startDay: 15, endDay: 21 },
        'Batch 4': { startDay: 22, endDay: 28 },
        'Batch 5': { startDay: 29, endDay: moment([year, month - 1]).daysInMonth() },  // Last day of the month
    };

    const config = batchConfig[batch];

    if (config) {
        const startDate = moment([year, month - 1, config.startDay]).startOf('day').toDate();
        const endDate = moment([year, month - 1, config.endDay]).endOf('day').toDate();
        return { startDate, endDate };
    }

    throw new Error('Invalid batch specified');
}

// Function to fetch data based on batch
async function getDataForBatch(year, month, batch) {
    // const { startDate, endDate } = getDateRangeForBatch(year, month, batch);

    startDate =batch + 'T00:00:00Z';
    endDate = batch + 'T23:59:59Z';

    const data = await EInvoice.find({
        voucher_date: {
            $gte: startDate,
            $lte: endDate
        }
    }).sort({invoicenum:1}).lean();
    

    // const data = await EInvoice.find({
    //     batch_year: year,
    //     batch_month: month
    // }).sort({ invoice_no: 1 }).lean();

    // Format voucher_date to 'Y-m-d'
    data.forEach(item => {
        item.voucher_date = moment(item.voucher_date).format('YYYY-MM-DD');
    });

    return data;

}

// Function to convert data to CSV
function convertToCSV(data) {
    const fields = [
        'voucher_type', 'invoice_no', 'voucher_date', 'party_name', 'state_of_supply',
        'sales_ledger', 'item_name', 'description_1', 'description_2', 'description_3',
        'description_4', 'description_5', 'godown', 'quantity', 'rate', 'amount',
        'shipping_charge', 'output_igst', 'output_cgst', 'output_sgst', 'packing_charges',
        'round_off', 'invoice_total', 'narration', 'reason_for_issue_note', 'buyer_debit_note_no',
        'date', 'bill_to_place', 'ship_to_place',
        'batch_month', 'batch_year', 'billing_company_name'
    ];
    const json2csvParser = new Parser({ fields });
    return json2csvParser.parse(data);
}

exports.exportCsv = async (req,res) => {
  
  const userKey = req?.headers?.userkey || null;
  const userToken = req?.headers?.usertoken || null;

  // if(userKey == ID && userToken == SECRET){
    
    try {

      const { year, month, batch } = req.query;

        // Validate inputs
        if (!year || !month || !batch) {
            return res.status(400).json({ error: 'Year, Month, and Batch are required' });
        }

        // Fetch data for the specified batch
        const data = await getDataForBatch(year, month, batch);

        // If no data found, return a message
        if (!data.length) {
            return res.status(400).json({ message: 'No data found for the specified batch.' });
        }

        // Convert the data to CSV format
        const csvData = convertToCSV(data);

        // Set headers for CSV download
        res.setHeader('Content-Disposition', 'attachment; filename="eInvoiceData.csv"');
        res.setHeader('Content-Type', 'text/csv');

        // Send CSV data as response
        return res.send(csvData);

    } catch (error) {
      console.log(error);
      return res.status(500).json({message: error.message, error: true})
    }
    
  // } else {
  //   return res.status(500).json({message: "Authentication Error", error: true})
  // }
  
};

exports.getYearList = async (req,res) => {
  
  const userKey = req?.headers?.userkey || null;
  const userToken = req?.headers?.usertoken || null;
  
  // if(userKey == ID && userToken == SECRET){
    console.log('userToken')
    try {

      const { year, month, batch } = req.query;

      const years = await EInvoice.aggregate([
        {
            $group: {
                _id: '$batch_year'
            }
        },
        {
            $sort: {
                _id: 1  // Sort by year in ascending order
            }
        }
      ]);

      const yearList = years.map(year => year._id);

      res.json({
        success: true,
        years: yearList
      });

    } catch (error) {
      console.log(error);
      return res.status(500).json({message: error.message, error: true})
    }
    
  // } else {
  //   return res.status(500).json({message: "Authentication Error", error: true})
  // }
  
};

