const Customers = require("../../models/Customers");
const SellerCompany = require("../../models/SellersCompany");
const OrderForm = require("../../models/OrderForm");
const { Op } = require("sequelize");
const moment = require("moment");
const XLSX = require("xlsx");
const Logistics = require("../../models/Logistics");

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

exports.getCustomerDataPoints = async (req, res, next) => {
  try {
    const startDateInput = req.query.startDate || moment().format("YYYY-MM-DD");
    const startDate = moment(startDateInput, "YYYY-MM-DD");

    const customers = await Customers.findAll({
      attributes: ["id", "first_name", "last_name", "address", "industry_classification"],
      order: [["id", "ASC"]],
      include: [
        {
          model: OrderForm,
          as: "order_form_details",
          attributes: ["id", "total_amount", "date_created"],
          where: {
            date_created: {
              [Op.lte]: startDate.format("YYYY-MM-DD"),
            },
            status: {
              [Op.notIn]: excludedStatuses,
            },
          },
          required: false,
        },
      ],
    });

    const customerData = customers.map((customer) => {
      const customerName = `${customer.first_name} ${customer.last_name}`;
      const industryClassification = customer.industry_classification;
      const businessLocation = customer.address;

      const firstOrderDate = customer.order_form_details?.length
        ? moment.min(customer.order_form_details.map((order) => moment(order.date_created)))
        : null;
      const yearsOfRelationship = firstOrderDate ? moment().diff(firstOrderDate, "years") : 0;

      const monthlyTransactions = {};
      let totalTransaction = 0;

      for (let i = 0; i < 12; i++) {
        const monthStart = moment(startDate).subtract(i, "months");
        const monthLabel = monthStart.format("DD-MMM");
        monthlyTransactions[monthLabel] = 0;
      }

      customer.order_form_details?.forEach((order) => {
        const orderDate = moment(order.date_created);
        const orderMonth = orderDate.format("MMM");
        const orderDay = orderDate.date();

        let addedToNextMonth = false;

        for (const monthlyTransaction in monthlyTransactions) {
          const [labelDay, labelMonth] = monthlyTransaction.split("-");

          if (orderMonth === labelMonth && orderDay <= parseInt(labelDay)) {
            const orderAmount = parseFloat(order.total_amount);
            monthlyTransactions[monthlyTransaction] += orderAmount;
            totalTransaction += orderAmount;
            break;
          }

          if (orderMonth === labelMonth && orderDay > parseInt(labelDay)) {
            addedToNextMonth = true;
          }
        }

        if (addedToNextMonth) {
          const nextMonth = moment(orderDate).add(1, "months").format("MMM");
          const _startDay = moment(startDate).format("DD");
          const nextMonthDay = `${_startDay}-${nextMonth}`;

          if (monthlyTransactions[nextMonthDay] !== undefined) {
            const orderAmount = parseFloat(order.total_amount);
            monthlyTransactions[nextMonthDay] += orderAmount;
            totalTransaction += orderAmount;
          }
        }
      });

      return {
        id: `CUSTB-${customer.id}`,
        customer_name: customerName,
        industry_classification: industryClassification,
        business_location: businessLocation,
        years_of_relationship: yearsOfRelationship,
        ...monthlyTransactions,
        total_transactions: parseFloat(totalTransaction),
      };
    });

    const workbook = XLSX.utils.book_new();

    const worksheet = XLSX.utils.json_to_sheet(customerData);

    XLSX.utils.book_append_sheet(workbook, worksheet, "Customers Data");

    const excelBuffer = XLSX.write(workbook, { bookType: "xlsx", type: "buffer" });

    res.setHeader(
      "Content-Disposition",
      `attachment; filename="customers_data_${moment().format("YYYYMMDD_HHmmss")}.xlsx"`
    );
    res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    res.status(200).send(excelBuffer);
  } catch (err) {
    next(err);
  }
};

exports.getSellerDataPoints = async (req, res, next) => {
  try {
    const startDateInput = req.query.startDate || moment().format("YYYY-MM-DD");
    const startDate = moment(startDateInput, "YYYY-MM-DD");

    const sellersCompany = await SellerCompany.findAll({
      attributes: ["id", "company_name", "address"],
      order: [["id", "ASC"]],
      include: [
        {
          model: OrderForm,
          as: "order_form_details",
          attributes: ["id", "total_amount", "date_created"],
          where: {
            date_created: {
              [Op.lte]: startDate.format("YYYY-MM-DD"),
            },
            status: {
              [Op.notIn]: excludedStatuses,
            },
          },
          required: false,
        },
      ],
    });

    const sellerData = sellersCompany.map((seller) => {
      const sellerName = `${seller.company_name}`;
      const industryClassification = "SELLER";
      const businessLocation = seller.address;

      const firstOrderDate = seller.order_form_details?.length
        ? moment.min(seller.order_form_details.map((order) => moment(order.date_created)))
        : null;
      const yearsOfRelationship = firstOrderDate ? moment().diff(firstOrderDate, "years") : 0;

      const monthlyTransactions = {};
      let totalTransaction = 0;

      for (let i = 0; i < 12; i++) {
        const monthStart = moment(startDate).subtract(i, "months");
        const monthLabel = monthStart.format("DD-MMM");
        monthlyTransactions[monthLabel] = 0;
      }

      seller.order_form_details?.forEach((order) => {
        const orderDate = moment(order.date_created);
        const orderMonth = orderDate.format("MMM");
        const orderDay = orderDate.date();

        let addedToNextMonth = false;

        for (const monthlyTransaction in monthlyTransactions) {
          const [labelDay, labelMonth] = monthlyTransaction.split("-");

          if (orderMonth === labelMonth && orderDay <= parseInt(labelDay)) {
            const orderAmount = parseFloat(order.total_amount);
            monthlyTransactions[monthlyTransaction] += orderAmount;
            totalTransaction += orderAmount;
            break;
          }

          if (orderMonth === labelMonth && orderDay > parseInt(labelDay)) {
            addedToNextMonth = true;
          }
        }

        if (addedToNextMonth) {
          const nextMonth = moment(orderDate).add(1, "months").format("MMM");
          const _startDay = moment(startDate).format("DD");
          const nextMonthDay = `${_startDay}-${nextMonth}`;

          if (monthlyTransactions[nextMonthDay] !== undefined) {
            const orderAmount = parseFloat(order.total_amount);
            monthlyTransactions[nextMonthDay] += orderAmount;
            totalTransaction += orderAmount;
          }
        }
      });

      return {
        id: `SELLERB-${seller.id}`,
        seller_name: sellerName,
        industry_classification: industryClassification,
        business_location: businessLocation,
        years_of_relationship: yearsOfRelationship,
        ...monthlyTransactions,
        total_transactions: parseFloat(totalTransaction),
      };
    });

    const workbook = XLSX.utils.book_new();

    const worksheet = XLSX.utils.json_to_sheet(sellerData);

    XLSX.utils.book_append_sheet(workbook, worksheet, "Sellers Data");

    const excelBuffer = XLSX.write(workbook, { bookType: "xlsx", type: "buffer" });

    res.setHeader(
      "Content-Disposition",
      `attachment; filename="sellers_data_${moment().format("YYYYMMDD_HHmmss")}.xlsx"`
    );
    res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    res.status(200).send(excelBuffer);
  } catch (err) {
    next(err);
  }
};

exports.getLogisticsDataPoints = async (req, res, next) => {
  try {
    const startDateInput = req.query.startDate || moment().format("YYYY-MM-DD");
    const startDate = moment(startDateInput, "YYYY-MM-DD");

    const logistics = await Logistics.findAll({
      attributes: ["id", "company_name", "address"],
      order: [["id", "ASC"]],
      include: [
        {
          model: OrderForm,
          as: "order_form_details",
          attributes: ["id", "delivery_fee", "date_created"],
          where: {
            date_created: {
              [Op.lte]: startDate.format("YYYY-MM-DD"),
            },
            status: {
              [Op.notIn]: excludedStatuses,
            },
          },
          required: false,
        },
      ],
    });

    const logisticsData = logistics.map((logistics) => {
      const logisticsName = `${logistics.company_name}`;
      const industryClassification = "Hauler";
      const businessLocation = logistics.address;

      const firstOrderDate = logistics.order_form_details?.length
        ? moment.min(logistics.order_form_details.map((order) => moment(order.date_created)))
        : null;
      const yearsOfRelationship = firstOrderDate ? moment().diff(firstOrderDate, "years") : 0;

      const monthlyTransactions = {};
      let totalTransaction = 0;

      for (let i = 0; i < 12; i++) {
        const monthStart = moment(startDate).subtract(i, "months");
        const monthLabel = monthStart.format("DD-MMM");
        monthlyTransactions[monthLabel] = 0;
      }

      logistics.order_form_details?.forEach((order) => {
        const orderDate = moment(order.date_created);
        const orderMonth = orderDate.format("MMM");
        const orderDay = orderDate.date();

        let addedToNextMonth = false;

        for (const monthlyTransaction in monthlyTransactions) {
          const [labelDay, labelMonth] = monthlyTransaction.split("-");

          if (orderMonth === labelMonth && orderDay <= parseInt(labelDay)) {
            const orderAmount = parseFloat(order.delivery_fee);
            monthlyTransactions[monthlyTransaction] += orderAmount;
            totalTransaction += orderAmount;
            break;
          }

          if (orderMonth === labelMonth && orderDay > parseInt(labelDay)) {
            addedToNextMonth = true;
          }
        }

        if (addedToNextMonth) {
          const nextMonth = moment(orderDate).add(1, "months").format("MMM");
          const _startDay = moment(startDate).format("DD");
          const nextMonthDay = `${_startDay}-${nextMonth}`;

          if (monthlyTransactions[nextMonthDay] !== undefined) {
            const orderAmount = parseFloat(order.delivery_fee);
            monthlyTransactions[nextMonthDay] += orderAmount;
            totalTransaction += orderAmount;
          }
        }
      });

      return {
        id: `LOGISB-${logistics.id}`,
        logistics_name: logisticsName,
        industry_classification: industryClassification,
        business_location: businessLocation,
        years_of_relationship: yearsOfRelationship,
        ...monthlyTransactions,
        total_transactions: parseFloat(totalTransaction),
      };
    });

    const workbook = XLSX.utils.book_new();

    const worksheet = XLSX.utils.json_to_sheet(logisticsData);

    XLSX.utils.book_append_sheet(workbook, worksheet, "Logistics Data");

    const excelBuffer = XLSX.write(workbook, { bookType: "xlsx", type: "buffer" });

    res.setHeader(
      "Content-Disposition",
      `attachment; filename="logistics_data_${moment().format("YYYYMMDD_HHmmss")}.xlsx"`
    );
    res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    res.status(200).send(excelBuffer);
  } catch (err) {
    next(err);
  }
};
