const OrderForm = require("../models/OrderForm");
const Products = require("../models/Products");
const ProductMenu = require("../models/ProductMenu");
const Customers = require("../models/Customers");
const Sequelize = require("sequelize");
const { Op, fn, col, literal } = require("sequelize");
const ExcelJS = require("exceljs");
const OrderFormDetails = require("../models/OrderFormDetails");
const logger = require("../util/logger");
const Vouchers = require("../models/Vouchers");

const salesReportStatuses = [
  "Received",
  "Delivered",
  "For Delivery - Warehouse",
];
exports.customerSalesReportService = async (dateRange, includeBacklog) => {
  try {
    const customers = await Customers.findAll({
      where: {
        account_status: true,
        status: "Verified",
      },
      attributes: ["id", "first_name", "last_name", "hardware_name"],
    });

    const customerIds = customers.map((customer) => customer.id);

    const excludedStatuses = [
      "Cancelled",
      "Cancel",
      "BUILDCREDIT INTEREST",
      "Denied",
      "Release Order - Expired",
    ];

    if (!includeBacklog) excludedStatuses.push("Backlog Orders");
    const orderForms = await OrderForm.findAll({
      where: {
        customer_id: customerIds,
        date_delivered: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        status: {
          // [Op.notIn]: excludedStatuses,
          [Op.in]: salesReportStatuses,
        },
      },
      attributes: [
        "id",
        "customer_id",
        "reference_no",
        "total_amount",
        [fn("DATE_FORMAT", col("date_delivered"), "%Y-%m"), "month"],
      ],
      raw: true,
    });

    const report = [];

    for (const customer of customers) {
      const customerOrders = orderForms.filter(
        (order) => order.customer_id === customer.id,
      );

      const monthlyOrders = customerOrders.reduce((acc, order) => {
        const month = order.month;
        if (!acc[month]) {
          acc[month] = {
            orders: [],
            totalSales: 0,
          };
        }
        acc[month].orders.push({
          reference_no: order.reference_no,
          total_amount: order.total_amount,
        });
        acc[month].totalSales += parseFloat(order.total_amount);
        return acc;
      }, {});

      for (const month in monthlyOrders) {
        monthlyOrders[month].orders.sort((a, b) => {
          return b.total_amount - a.total_amount;
        });
      }

      report.push({
        customer: {
          id: customer.id,
          name: `${customer.first_name} ${customer.last_name}`,
          hardware_name: customer.hardware_name || "",
        },
        salesByMonth: monthlyOrders,
      });
    }

    const generatedReport = await generateCustomerSalesCSV(report);
    return { report: generatedReport };
  } catch (err) {
    return { error: err.message };
  }
};

exports.itemSalesReportService = async (dateRange, includeBacklog) => {
  try {
    const excludedStatuses = [
      "Cancelled",
      "Cancel",
      "BUILDCREDIT INTEREST",
      "Denied",
      "Release Order - Expired",
    ];

    if (!includeBacklog) excludedStatuses.push("Backlog Orders");

    const salesReport = await OrderForm.findAll({
      where: {
        date_delivered: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        status: {
          // [Op.notIn]: excludedStatuses,
          [Op.in]: salesReportStatuses,
        },
      },
      attributes: [
        [
          Sequelize.fn("DATE_FORMAT", Sequelize.col("date_delivered"), "%Y-%m"),
          "month",
        ],
        [Sequelize.col("order_form_details.product.menu_id"), "menu_id"],
        [Sequelize.col("order_form_details.product.id"), "product_id"],
        [Sequelize.col("order_form_details.product.menu"), "menu"],
        [Sequelize.col("order_form_details.product.seller_id"), "seller_id"],
        [
          Sequelize.col("order_form_details.product.menus.product_menu"),
          "product_category",
        ],
        [
          Sequelize.fn("SUM", Sequelize.col("order_form_details.quantity")),
          "total_qty",
        ],
        [
          Sequelize.fn("SUM", Sequelize.col("order_form_details.amount")),
          "total_amount",
        ],
      ],
      include: [
        {
          model: OrderFormDetails,
          as: "order_form_details",
          attributes: [],
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["seller_id"],
              include: [
                {
                  model: ProductMenu,
                  as: "menus",
                  attributes: ["product_menu"],
                },
              ],
            },
          ],
        },
      ],
      group: [
        "month",
        "order_form_details.product.menu_id",
        "order_form_details.product.menu",
      ],
      raw: true,
    });

    const formattedReport = salesReport.reduce((acc, row) => {
      const {
        month,
        product_id,
        seller_id,
        menu,
        total_qty,
        total_amount,
        product_category,
      } = row;

      if (
        !month ||
        !product_category ||
        product_id === null ||
        !menu ||
        total_qty === null ||
        total_amount === null
      ) {
        return acc;
      }

      if (!acc[month]) {
        acc[month] = {};
      }

      if (!acc[month][product_category]) {
        acc[month][product_category] = [];
      }

      acc[month][product_category].push({
        product_id: product_id,
        seller_id: seller_id,
        itemName: menu,
        total_qty,
        total_amount,
      });

      return acc;
    }, {});

    Object.keys(formattedReport).forEach((month) => {
      Object.keys(formattedReport[month]).forEach((category) => {
        formattedReport[month][category].sort(
          (a, b) => parseFloat(b.total_amount) - parseFloat(a.total_amount),
        );
      });
    });

    const generatedReport = await generateItemSalesCSV(formattedReport);
    return { report: generatedReport };
  } catch (err) {
    console.error(err);
    return { error: err.message };
  }
};

exports.generalSalesReportService = async (dateRange, includeBacklog) => {
  try {
    const excludedStatuses = [
      "Cancelled",
      "Cancel",
      "BUILDCREDIT INTEREST",
      "Denied",
      "Release Order - Expired",
    ];

    if (!includeBacklog) excludedStatuses.push("Backlog Orders");

    const salesReport = await OrderForm.findAll({
      attributes: [
        [fn("DATE_FORMAT", col("date_delivered"), "%Y-%m"), "month"],
        [fn("SUM", col("total_amount")), "sales"],
        [fn("SUM", col("delivery_fee")), "shipping"],
        [fn("COUNT", col("id")), "order_count"],
        [
          fn(
            "SUM",
            literal(
              `CASE WHEN payment_method = 'Build Credit' OR payment_method = 'RPAF' THEN 1 ELSE 0 END`,
            ),
          ),
          "build_credit_count",
        ],
        [
          fn(
            "SUM",
            literal(
              `CASE WHEN payment_method = 'Bank Transfer' THEN 1 ELSE 0 END`,
            ),
          ),
          "bank_transfer_count",
        ],
      ],
      where: {
        date_delivered: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        status: {
          // [Op.notIn]: excludedStatuses,
          [Op.in]: salesReportStatuses,
        },
      },
      group: [literal("DATE_FORMAT(date_delivered, '%Y-%m')")],
      order: [[literal("DATE_FORMAT(date_delivered, '%Y-%m')"), "ASC"]],
    });

    const report = {};
    salesReport.forEach((row) => {
      report[row.get("month")] = {
        sales: row.get("sales"),
        shipping: row.get("shipping"),
        orderCount: row.get("order_count"),
        buildCreditCount: row.get("build_credit_count"),
        bankTransferCount: row.get("bank_transfer_count"),
      };
    });

    const generatedReport = await generateSalesReportExcel(report);
    return { report: generatedReport };
  } catch (err) {
    return { error: err.message };
  }
};

async function generateCustomerSalesCSV(customersData) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sales Report");

  customersData.forEach((customerData) => {
    worksheet.addRow([`Customer ID: ${customerData.customer.id}`]);
    worksheet.addRow([`Customer Name: ${customerData.customer.name}`]);
    worksheet.addRow([
      `Hardware Name: ${customerData.customer.hardware_name || "N/A"}`,
    ]);
    worksheet.addRow([]);

    worksheet.columns = [
      { header: "Month", key: "month", width: 15 },
      { header: "Order Reference", key: "reference_no", width: 25 },
      { header: "Total Amount", key: "total_amount", width: 15 },
    ];

    Object.entries(customerData.salesByMonth).forEach(([month, data]) => {
      const { orders, totalSales } = data;

      orders.forEach((order, index) => {
        worksheet.addRow({
          month: index === 0 ? month : "",
          reference_no: order.reference_no,
          total_amount: `₱${parseFloat(order.total_amount).toLocaleString()}`,
        });
      });

      worksheet.addRow({
        month: "",
        reference_no: `Total Sales (${month})`,
        total_amount: `₱${parseFloat(totalSales).toLocaleString()}`,
      });

      worksheet.addRow({});
    });

    worksheet.addRow([]);
  });

  worksheet.getRow(1).font = { bold: true };
  worksheet.getRow(1).alignment = { horizontal: "center" };

  const timestamp = new Date().toISOString().replace(/[-:.]/g, "").slice(0, 15);
  const fileName = `CustomerSalesReport-${timestamp}.xlsx`;
  const filePath = `./public/generated-reports/${fileName}`;
  await workbook.xlsx.writeFile(filePath);
  logger.error(
    `Customer Sales Report Generated: ${fileName} - at ${new Date()}`,
  );
  const buffer = await workbook.xlsx.writeBuffer();
  return { fileName, buffer };
}

async function generateItemSalesCSV(itemData) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sales Report");

  worksheet.mergeCells("A1:F1");
  worksheet.getCell("A1").value = "Sales Report";
  worksheet.getCell("A1").font = { size: 16, bold: true };
  worksheet.getCell("A1").alignment = {
    vertical: "middle",
    horizontal: "center",
  };

  worksheet.mergeCells("A2:F2");
  worksheet.getCell("A2").value =
    `Generated on: ${new Date().toLocaleString()}`;
  worksheet.getCell("A2").font = { italic: true };
  worksheet.getCell("A2").alignment = {
    vertical: "middle",
    horizontal: "center",
  };

  const headerRow = worksheet.addRow([
    "Month",
    "Category",
    "Product ID",
    "Seller ID",
    "Item Name",
    "Total Quantity",
    "Total Amount",
  ]);
  headerRow.font = { bold: true };
  headerRow.alignment = { horizontal: "center", vertical: "middle" };
  headerRow.eachCell((cell) => {
    cell.border = {
      top: { style: "thin" },
      left: { style: "thin" },
      bottom: { style: "thin" },
      right: { style: "thin" },
    };
    cell.fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FFFFCC" },
    };
  });

  Object.entries(itemData).forEach(([month, categories]) => {
    Object.entries(categories).forEach(([category, products]) => {
      products.forEach((product) => {
        const row = worksheet.addRow([
          month,
          category,
          product.product_id,
          product.seller_id,
          product.itemName,
          parseFloat(product.total_qty),
          parseFloat(product.total_amount),
        ]);

        row.eachCell((cell) => {
          cell.border = {
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          };
        });
      });
    });
  });

  worksheet.columns = [
    { width: 15 },
    { width: 20 },
    { width: 15 },
    { width: 15 },
    { width: 40 },
    { width: 15 },
    { width: 15 },
  ];

  worksheet.getColumn(6).numFmt = "#,##0.00";
  worksheet.getColumn(7).numFmt = "₱#,##0.00";

  const timestamp = new Date().toISOString().replace(/[-:.]/g, "").slice(0, 15);
  const fileName = `ItemSalesReport-${timestamp}.xlsx`;
  const filePath = `./public/generated-reports/${fileName}`;

  await workbook.xlsx.writeFile(filePath);
  logger.error(`Item Sales Report Generated: ${fileName} - at ${new Date()}`);
  const buffer = await workbook.xlsx.writeBuffer();
  return { fileName, buffer };
}

async function generateSalesReportExcel(salesReport) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sales Report");

  worksheet.columns = [
    {
      header: "Month",
      key: "month",
      width: 15,
      style: { font: { bold: true } },
    },
    {
      header: "Total Sales",
      key: "sales",
      width: 20,
      style: { font: { bold: true }, alignment: { horizontal: "right" } },
    },
    {
      header: "Shipping Fees",
      key: "shipping",
      width: 15,
      style: { font: { bold: true }, alignment: { horizontal: "right" } },
    },
    {
      header: "Order Count",
      key: "orderCount",
      width: 15,
      style: { font: { bold: true }, alignment: { horizontal: "right" } },
    },
    {
      header: "Build Credit Count",
      key: "buildCreditCount",
      width: 20,
      style: { font: { bold: true }, alignment: { horizontal: "right" } },
    },
    {
      header: "Bank Transfer Count",
      key: "bankTransferCount",
      width: 20,
      style: { font: { bold: true }, alignment: { horizontal: "right" } },
    },
  ];

  for (const month in salesReport) {
    const row = salesReport[month];
    worksheet.addRow({
      month: month,
      sales: parseFloat(row.sales).toLocaleString(),
      shipping: parseFloat(row.shipping).toLocaleString(),
      orderCount: row.orderCount.toLocaleString(),
      buildCreditCount: row.buildCreditCount.toLocaleString(),
      bankTransferCount: row.bankTransferCount.toLocaleString(),
    });
  }

  worksheet.getRow(1).eachCell((cell) => {
    cell.alignment = { horizontal: "center", vertical: "middle" };
    cell.border = {
      top: { style: "thin" },
      left: { style: "thin" },
      bottom: { style: "thin" },
      right: { style: "thin" },
    };
  });

  worksheet.eachRow((row, rowIndex) => {
    row.eachCell((cell, colIndex) => {
      if (rowIndex === 1) return;
      cell.border = {
        top: { style: "thin" },
        left: { style: "thin" },
        bottom: { style: "thin" },
        right: { style: "thin" },
      };
      if (colIndex !== 1) {
        cell.alignment = { horizontal: "right" };
      }
    });
  });

  const timestamp = new Date().toISOString().replace(/[-:.]/g, "").slice(0, 15);
  const fileName = `GeneralSalesReport-${timestamp}.xlsx`;
  const filePath = `./public/generated-reports/${fileName}`;

  await workbook.xlsx.writeFile(filePath);
  logger.error(
    `General Sales Report Generated: ${fileName} - at ${new Date()}`,
  );
  const buffer = await workbook.xlsx.writeBuffer();
  return { fileName, buffer };
}

async function generateAgentSalesReportExcel(groupedResults) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Agent Sales Report");

  worksheet.properties.defaultRowHeight = 20;

  worksheet.columns = [
    { header: "SEALs", key: "seals", width: 25 },
    { header: "Order Ref No", key: "reference_no", width: 20 },
    { header: "Date Created", key: "date_created", width: 20 },
    { header: "Date Delivered", key: "date_delivered", width: 20 },
    { header: "Customer", key: "customer", width: 25 },
    { header: "Status", key: "status", width: 15 },
    { header: "Quantity", key: "total_qty", width: 12 },
    { header: "Voucher", key: "voucher_amount", width: 12 },
    { header: "Delivery Fee", key: "delivery_fee", width: 12 },
    { header: "Total Amount", key: "total_amount", width: 15 },
    { header: "SKU", key: "sku", width: 18 },
    { header: "Item Code", key: "item_code", width: 18 },
    { header: "Category", key: "category", width: 20 },
    { header: "QTY", key: "detail_qty", width: 10 },
    { header: "SRP", key: "srp", width: 12 },
    { header: "Total Price", key: "total_price", width: 15 },
  ];

  const rows = [];

  const sortedGroups = groupedResults.sort((a, b) =>
    (a.agent?.name || "").localeCompare(b.agent?.name || ""),
  );


  for (const group of sortedGroups) {
    const agentName = group.agent?.name || "";

    const sortedOrders = (group.orders || []).sort(
      (a, b) => new Date(b.date_created) - new Date(a.date_created),
    );

    for (const order of sortedOrders) {
      const sortedDetails = (order.order_form_details || []).sort((a, b) =>
        (a.product?.menu || "").localeCompare(b.product?.menu || ""),
      );

      if (sortedDetails.length > 0) {
        sortedDetails.forEach((detail, index) => {
          const isFirstDetail = index === 0;

          const productName = detail.product?.menu || "N/A";
          const itemCode =
            detail.product?.item_code || detail.product?.sku || "N/A";
          const category = detail.product?.menus?.product_menu || "Uncategorized";
          const qty = detail.quantity || 0;
          const srp = detail.product?.price || 0;
          const totalPrice = detail.amount || qty * srp;

          rows.push({
            seals: isFirstDetail ? agentName : "",
            reference_no: isFirstDetail ? order.reference_no : "",
            date_created:
              isFirstDetail && order.date_created
                ? new Date(order.date_created).toLocaleString()
                : "",
            date_delivered:
              isFirstDetail && order.date_delivered
                ? new Date(order.date_delivered).toLocaleString()
                : "",
            customer:
              isFirstDetail && order.customer_detail
                ? `${order.customer_detail.first_name || ""} ${order.customer_detail.last_name || ""}`.trim()
                : "",
            status: isFirstDetail ? order.status : "",
            total_qty: isFirstDetail ? order.total_qty : "",
            voucher_amount: isFirstDetail ? order.voucher_amount : "",
            delivery_fee: isFirstDetail ? order.delivery_fee : "",
            total_amount: isFirstDetail ? order.total_amount : "",
            sku: productName,
            item_code: itemCode,
            category: category,
            detail_qty: qty,
            srp: srp,
            total_price: totalPrice,
          });
        });
      } else {
        rows.push({
          seals: agentName,
          reference_no: order.reference_no,
          date_created: order.date_created
            ? new Date(order.date_created).toLocaleString()
            : "",
          date_delivered: order.date_delivered
            ? new Date(order.date_delivered).toLocaleString()
            : "",
          customer: order.customer_detail
            ? `${order.customer_detail.first_name || ""} ${order.customer_detail.last_name || ""}`.trim()
            : "",
          status: order.status,
          total_qty: order.total_qty,
          voucher_amount: order.voucher_amount,
          delivery_fee: order.delivery_fee,
          total_amount: order.total_amount,
          sku: "",
          item_code: "",
          category: "",
          detail_qty: "",
          srp: "",
          total_price: "",
        });
      }
    }
  }

  worksheet.addRows(rows);

  worksheet.getRow(1).font = { bold: true };
  worksheet.getRow(1).fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "FF0000" },
  };

  const timestamp = new Date().toISOString().replace(/[-:.]/g, "").slice(0, 15);
  const fileName = `AgentSalesReport-${timestamp}.xlsx`;
  const filePath = `./public/generated-reports/${fileName}`;

  await workbook.xlsx.writeFile(filePath);
  const buffer = await workbook.xlsx.writeBuffer();

  return { fileName, buffer };
}
exports.agentSalesReportService = async (dateRange, includeBacklog) => {
  try {
    const excludedStatuses = [
      "Cancelled",
      "Cancel",
      "BUILDCREDIT INTEREST",
      "Denied",
      "Release Order - Expired",
    ];
    if (!includeBacklog) excludedStatuses.push("Backlog Orders");

    const agents = await Customers.findAll({
      where: {
        type: "Agent",
        agent_code: {
          [Op.and]: [{ [Op.ne]: null }, { [Op.ne]: "" }],
        },
      },
      attributes: ["id", "first_name", "last_name", "agent_code"],
    });

    const groupedResults = [];

    for (const agent of agents) {
      const agentOrders = await OrderForm.findAll({
        attributes: [
          "id",
          "reference_no",
          "date_created",
          "date_delivered",
          "total_amount",
          "status",
          "total_qty",
          "voucher_amount",
          "delivery_fee",
        ],
        where: {
          agentCode: agent.agent_code,
          date_delivered: {
            [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
          },
          // date_delivered: {
          //   [Op.or]: [
          //     { [Op.is]: null },
          //     { [Op.between]: [dateRange.dateStart, dateRange.dateEnd] },
          //   ],
          // },
          status: {
            // [Op.notIn]: excludedStatuses,
            [Op.in]: salesReportStatuses,
          },
        },
        include: [
          {
            model: Customers,
            as: "customer_detail",
            attributes: ["id", "first_name", "last_name", "hardware_name"],
          },
          {
            model: OrderFormDetails,
            as: "order_form_details",
            attributes: [
              "id",
              "order_form_id",
              "product_id",
              "quantity",
              "amount",
            ],
            include: [
              {
                model: Products,
                as: "product",
                attributes: ["id", "menu", "price", "sku"],
                include: [
                  {
                    model: ProductMenu,
                    as: "menus",
                    attributes: ["id", "product_menu"],
                  },
                ],
              },
            ],
          },
        ],
      });

      groupedResults.push({
        agent: {
          id: agent.id,
          name: `${agent.first_name} ${agent.last_name}`,
          code: agent.agent_code,
        },
        orders: agentOrders,
        totals: {
          totalSales: agentOrders.reduce(
            (sum, o) => sum + (o.total_amount || 0),
            0,
          ),
          totalQuantity: agentOrders.reduce(
            (sum, o) => sum + (o.total_qty || 0),
            0,
          ),
          totalOrders: agentOrders.length,
        },
      });
    }

    const generatedReport = await generateAgentSalesReportExcel(groupedResults);
    return { report: generatedReport };
  } catch (err) {
    return { error: err.message };
  }
};
async function generateVoucherSalesReportExcel(groupedResults) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Voucher Sales Report");

  worksheet.properties.defaultRowHeight = 20;

  worksheet.columns = [
    { header: "Voucher", key: "voucher", width: 25 },
    { header: "Order Ref No", key: "reference_no", width: 20 },
    { header: "Date Created", key: "date_created", width: 20 },
    { header: "Date Delivered", key: "date_delivered", width: 20 },
    { header: "Customer", key: "customer", width: 25 },
    { header: "Status", key: "status", width: 15 },
    { header: "Quantity", key: "total_qty", width: 12 },
    { header: "Voucher Amount", key: "voucher_amount", width: 12 },
    { header: "Delivery Fee", key: "delivery_fee", width: 12 },
    { header: "Total Amount", key: "total_amount", width: 15 },
    { header: "SKU", key: "sku", width: 18 },
    { header: "Item Code", key: "item_code", width: 18 },
    { header: "Category", key: "category", width: 20 },
    { header: "QTY", key: "detail_qty", width: 10 },
    { header: "SRP", key: "srp", width: 12 },
    { header: "Total Price", key: "total_price", width: 15 },
  ];

  const rows = [];

  const sortedGroups = groupedResults.sort((a, b) =>
    (a.voucher?.name || "").localeCompare(b.voucher?.name || ""),
  );

  for (const group of sortedGroups) {
    const voucherName = group.voucher?.name || "";

    const sortedOrders = (group.orders || []).sort(
      (a, b) => new Date(b.date_created) - new Date(a.date_created),
    );

    for (const order of sortedOrders) {
      const sortedDetails = (order.order_form_details || []).sort((a, b) =>
        (a.product?.menu || "").localeCompare(b.product?.menu || ""),
      );

      if (sortedDetails.length > 0) {
        sortedDetails.forEach((detail, index) => {
          const isFirstDetail = index === 0;

          const productName = detail.product?.menu || "N/A";
          const itemCode =
            detail.product?.item_code || detail.product?.sku || "N/A";
          const category =
            detail.product?.menus?.product_menu || "Uncategorized";
          const qty = detail.quantity || 0;
          const srp = detail.product?.price || 0;
          const totalPrice = detail.amount || qty * srp;

          rows.push({
            voucher: isFirstDetail ? voucherName : "",
            reference_no: isFirstDetail ? order.reference_no : "",
            date_created:
              isFirstDetail && order.date_created
                ? new Date(order.date_created).toLocaleString()
                : "",
            date_delivered:
              isFirstDetail && order.date_delivered
                ? new Date(order.date_delivered).toLocaleString()
                : "",
            customer:
              isFirstDetail && order.customer_detail
                ? `${order.customer_detail.first_name || ""} ${order.customer_detail.last_name || ""}`.trim()
                : "",
            status: isFirstDetail ? order.status : "",
            total_qty: isFirstDetail ? order.total_qty : "",
            voucher_amount: isFirstDetail ? order.voucher_amount : "",
            delivery_fee: isFirstDetail ? order.delivery_fee : "",
            total_amount: isFirstDetail ? order.total_amount : "",
            sku: productName,
            item_code: itemCode,
            category: category,
            detail_qty: qty,
            srp: srp,
            total_price: totalPrice,
          });
        });
      } else {
        rows.push({
          voucher: voucherName,
          reference_no: order.reference_no,
          date_created: order.date_created
            ? new Date(order.date_created).toLocaleString()
            : "",
          date_delivered: order.date_delivered
            ? new Date(order.date_delivered).toLocaleString()
            : "",
          customer: order.customer_detail
            ? `${order.customer_detail.first_name || ""} ${order.customer_detail.last_name || ""}`.trim()
            : "",
          status: order.status,
          total_qty: order.total_qty,
          voucher_amount: order.voucher_amount,
          delivery_fee: order.delivery_fee,
          total_amount: order.total_amount,
          sku: "",
          item_code: "",
          category: "",
          detail_qty: "",
          srp: "",
          total_price: "",
        });
      }
    }
  }

  worksheet.addRows(rows);

  worksheet.getRow(1).font = { bold: true };
  worksheet.getRow(1).fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: "FF0000" },
  };

  const timestamp = new Date().toISOString().replace(/[-:.]/g, "").slice(0, 15);
  const fileName = `VoucherSalesReport-${timestamp}.xlsx`;
  const filePath = `./public/generated-reports/${fileName}`;

  await workbook.xlsx.writeFile(filePath);
  const buffer = await workbook.xlsx.writeBuffer();

  return { fileName, buffer };
}
exports.voucherSalesReportService = async (
  dateRange,
  voucherCode,
  includeBacklog,
) => {
  try {
    const excludedStatuses = [
      "Cancelled",
      "Cancel",
      "BUILDCREDIT INTEREST",
      "Denied",
      "Release Order - Expired",
    ];
    if (!includeBacklog) excludedStatuses.push("Backlog Orders");

    const groupedResults = [];

    const voucher = await Vouchers.findOne({
      where: {
        voucher_code: voucherCode,
      },
    });
    if (!voucher) {
      return {
        error: "Voucher not found",
      };
    }

    const voucherOrders = await OrderForm.findAll({
      attributes: [
        "id",
        "reference_no",
        "date_created",
        "date_delivered",
        "total_amount",
        "status",
        "total_qty",
        "voucher_amount",
        "delivery_fee",
      ],
      where: {
        voucher_id_used: voucher.id,
        date_delivered: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        // date_delivered: {
        //   [Op.or]: [
        //     { [Op.is]: null },
        //     { [Op.between]: [dateRange.dateStart, dateRange.dateEnd] },
        //   ],
        // },
        status: {
          [Op.in]: salesReportStatuses,
        },
      },
      include: [
        {
          model: Customers,
          as: "customer_detail",
          attributes: ["id", "first_name", "last_name", "hardware_name"],
        },
        {
          model: OrderFormDetails,
          as: "order_form_details",
          attributes: [
            "id",
            "order_form_id",
            "product_id",
            "quantity",
            "amount",
          ],
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["id", "menu", "price", "sku"],
              include: [
                {
                  model: ProductMenu,
                  as: "menus",
                  attributes: ["id", "product_menu"],
                },
              ],
            },
          ],
        },
      ],
    });

    groupedResults.push({
      voucher: {
        id: voucher.id,
        name: voucher.voucher_code,
      },
      orders: voucherOrders,
      totals: {
        totalSales: voucherOrders.reduce(
          (sum, o) => sum + (o.total_amount || 0),
          0,
        ),
        totalQuantity: voucherOrders.reduce(
          (sum, o) => sum + (o.total_qty || 0),
          0,
        ),
        totalOrders: voucherOrders.length,
      },
    });
    const generatedReport =
      await generateVoucherSalesReportExcel(groupedResults);
    return { report: generatedReport };
  } catch (err) {
    return { error: err.message };
  }
};
