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");

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_created: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        status: {
          [Op.notIn]: excludedStatuses,
        },
      },
      attributes: [
        "id",
        "customer_id",
        "reference_no",
        "total_amount",
        [fn("DATE_FORMAT", col("date_created"), "%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_created: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        status: {
          [Op.notIn]: excludedStatuses,
        },
      },
      attributes: [
        [
          Sequelize.fn("DATE_FORMAT", Sequelize.col("date_created"), "%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_created"), "%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_created: {
          [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
        },
        status: {
          [Op.notIn]: excludedStatuses,
        },
      },
      group: [literal("DATE_FORMAT(date_created, '%Y-%m')")],
      order: [[literal("DATE_FORMAT(date_created, '%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: "Order Ref No", key: "reference_no", width: 20 },
    { header: "Date Created", key: "date_created", width: 18 },
    { header: "Date Delivered", key: "date_delivered", width: 18 },
    { 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 },
  ];

  let rowIndex = 1;

  for (const group of groupedResults) {
    // Agent Header
    const agentHeaderRow = worksheet.addRow([
      `Agent: ${group.agent.name} (Code: ${group.agent.code})`,
    ]);
    agentHeaderRow.font = { bold: true, size: 14 };
    worksheet.mergeCells(`A${rowIndex}:I${rowIndex}`);
    rowIndex++;

    // Agent Totals
    const totalsRow = worksheet.addRow([
      "",
      "",
      "",
      "",
      "TOTALS:",
      group.totals.totalQuantity,
      "",
      "",
      group.totals.totalSales,
    ]);
    totalsRow.font = { bold: true };
    rowIndex++;

    // Column Headers
    worksheet.addRow({});
    worksheet.getRow(rowIndex).values = worksheet.columns.map(
      (col) => col.header
    );
    worksheet.getRow(rowIndex).font = { bold: true, color: { argb: "FFFFFF" } };
    worksheet.getRow(rowIndex).fill = {
      type: "pattern",
      pattern: "solid",
      fgColor: { argb: "FF0000" },
    };
    rowIndex++;

    // Orders
    for (const order of group.orders) {
      const orderRow = worksheet.addRow({
        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}`
          : "",
        status: order.status,
        total_qty: order.total_qty,
        voucher_amount: order.voucher_amount,
        delivery_fee: order.delivery_fee,
        total_amount: order.total_amount,
      });
      orderRow.font = { bold: true }; // highlight main order
      rowIndex++;

      // Now add the order details (products)
      let isFirstDetail = true;

      for (const detail of order.order_form_details) {
        const productName = detail.product?.menu || "N/A";
        const productMenu =
          detail.product?.menus?.product_menu || "Uncategorized";
        const productSku = detail.product?.sku || "N/A";
        const price = detail.product?.price || 0;
        const qty = detail.quantity || 0;
        const subtotal = detail.amount || qty * price;

        if (isFirstDetail) {
          // Order part (bold)
          for (let c = 1; c <= 9; c++) {
            orderRow.getCell(c).font = { bold: true };
          }

          // Detail part (non-bold, maybe italic)
          orderRow.getCell(10).value = `   → ${productName}`;
          orderRow.getCell(11).value = `SKU: ${productSku}`;
          orderRow.getCell(12).value = `Category: ${productMenu}`;
          orderRow.getCell(13).value = `Qty: ${qty}`;
          orderRow.getCell(14).value = `Price: ${price}`;
          orderRow.getCell(15).value = `Total Price: ${subtotal}`;

          for (let c = 10; c <= 15; c++) {
            orderRow.getCell(c).font = { italic: true, bold: false, size: 11 };
          }

          isFirstDetail = false;
        } else {
          const detailRow = worksheet.addRow([
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            "",
            `   → ${productName}`,
            `SKU: ${productSku}`,
            `Category: ${productMenu}`,
            `Qty: ${qty}`,
            `Price: ${price}`,
            `Total Price: ${subtotal}`,
          ]);
          detailRow.font = { italic: true, size: 11 };
          rowIndex++;
        }
      }

      // Spacer between orders
      worksheet.addRow({});
      rowIndex++;
    }

    // Spacer between agents
    worksheet.addRow({});
    rowIndex++;
  }

  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);
  logger.error(`Agent Sales Report Generated: ${fileName} - at ${new Date()}`);
  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_created: {
            [Op.between]: [dateRange.dateStart, dateRange.dateEnd],
          },
          date_delivered: {
            [Op.or]: [
              { [Op.is]: null },
              { [Op.between]: [dateRange.dateStart, dateRange.dateEnd] },
            ],
          },
          status: {
            [Op.notIn]: excludedStatuses,
          },
        },
        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 };
  }
};
