const {pdfBuilder} = require("../../helpers/pdfBuilder");
const { getBuildCreditBalance } = require("../../helpers/buildcredit");
const axios = require("axios");
const PdcHistory = require("../../models/PdcHistory");
const RpafPayment = require("../../models/RpafPayment");
const BuildCreditCustomers = require("../../models/Rpaf");
const BuildcreditOverPayments = require("../../models/BuildcreditOverPayments");
const { Sequelize, fn, col } = require("sequelize");
const { Op } = require("sequelize");
const {
  createStatementPDF,
  createStatementMultiplePDF,
} = require("./soa-puppeeter");
const Rpaf = require("../../models/Rpaf");

function formatAmount(amount) {
  try {
    let parsed_amount = parseFloat(amount);

    if (isNaN(parsed_amount)) {
      return "₱0.00";
    }

    let formatted_amount = parsed_amount.toLocaleString("en-US", {
      minimumFractionDigits: 2,
    });

    return `₱${formatted_amount}`;
  } catch (error) {
    return "₱0.00";
  }
}

exports.soaBuilder = async (req, res, next) => {
  try {
    const { customer } = req.query;
    if (!customer)
      return res
        .status(200)
        .json({ success: false, message: "No Customer ID Found" });

    const FILES_URL_CONTAINER =
      "https://api.buildhubware.com/public/soa-assets/buildhub.png";
    // const FILES_URL_CONTAINER = "http://localhost:3008/public/soa-assets/buildhub.png";
    const axiosResponse = await axios.get(`${FILES_URL_CONTAINER}`, {
      responseType: "arraybuffer",
    });
    const imageBase64 = Buffer.from(axiosResponse.data, "binary").toString(
      "base64"
    );

    const date = new Date();
    const dateOptions = {
      weekday: undefined,
      year: "numeric",
      month: "long",
      day: "numeric",
      hour: "numeric",
      minute: "numeric",
      hour12: true,
    };
    const formattedDate = date.toLocaleString("en-US", dateOptions);

    const templatePath = __dirname + "/../../helpers/email-templates/soa.html";

    const buildCreditDetail = await getBuildCreditBalance(customer, true);

    if (!buildCreditDetail)
      return res
        .status(200)
        .json({ success: false, message: "No BuildCredit Record Found" });

    const pdcSentsPromise = PdcHistory.findAll({
      where: {
        customer_id: customer,
      },
      include: [
        {
          model: RpafPayment,
          as: "payment_detail",
          attributes: [],
        },
      ],
      attributes: [
        [fn("SUM", col("payment_detail.amount_paid")), "total_amount_paid"],
      ],
      raw: true,
    });

    const pdcReceivedPromise = RpafPayment.findAll({
      where: {
        customer_id: customer,
        status: "PDC Received",
      },
      attributes: [[fn("SUM", col("amount_paid")), "total_amount_paid_pdc"]],
      raw: true,
    });

    const pdcReceivedBuildcreditOverpaymentsPromise =
      BuildcreditOverPayments.findAll({
        where: {
          customer_id: customer,
          payment_status: "PDC Received",
          status: true,
        },
        attributes: [[fn("SUM", col("amount")), "total_amount_paid_pdc"]],
      });

    const promises = await Promise.all([
      pdcSentsPromise,
      pdcReceivedPromise,
      pdcReceivedBuildcreditOverpaymentsPromise,
    ]);

    const totalAmountPaid = promises[0]?.[0]?.total_amount_paid || 0;

    const pdcReceivedRpafPayment = promises[1]?.[0]?.total_amount_paid_pdc || 0;
    const parsedPdcRpafPayment = parseFloat(pdcReceivedRpafPayment);

    const pdcReceivedOverpayment =
      promises[2]?.[0]?.dataValues.total_amount_paid_pdc || 0;
    const parsedPdcOverpayment = parseFloat(pdcReceivedOverpayment);

    const totalAmountPaidPdc =
      parseFloat(parsedPdcRpafPayment) + parseFloat(parsedPdcOverpayment);

    const data = {
      buildhubLogo: imageBase64,
      companyName: buildCreditDetail?.company_name,
      buildcreditStatus: buildCreditDetail?.buildcredit_status,
      creditTerms: buildCreditDetail?.credit_terms,
      creditLimit: formatAmount(buildCreditDetail?.approved_credit_limit),
      accountBalance: formatAmount(buildCreditDetail?.used_credit_limit),
      availableLine: formatAmount(buildCreditDetail?.current_bal),
      pdcSent: formatAmount(totalAmountPaidPdc),
      abLessPdc: formatAmount(
        buildCreditDetail?.used_credit_limit - parseFloat(totalAmountPaidPdc)
      ),
      alPlusPdc: formatAmount(
        buildCreditDetail?.current_bal + parseFloat(totalAmountPaidPdc)
      ),
      dateNTime: formattedDate,
      statementBreakdown: buildCreditDetail?.statementBreakdown,
    };

    // With Order Breakdown
    const statementPDF = await createStatementPDF(data, imageBase64);
    res.setHeader("Content-Type", "text/html");
    res.status(200).send(statementPDF);

    // Without Order Breakdown
    // const pdfResponse = await pdfBuilder(templatePath, "soa", "pdf-files", "landscape", data);
    // if (pdfResponse) {
    //   const urlToRedirect = `/public/pdf-files/${pdfResponse?.file_name}`;
    //   res.redirect(urlToRedirect);
    // }
  } catch (err) {
    next(err);
  }
};

exports.summarySoaBuilder = async (req, res, next) => {
  try {
    const { status = "" } = req.query;

    const availableStatuses = ["Approved", "Case to case", "Suspended"];

    if (status !== "") {
      const isWithinAvailStatuses = availableStatuses.find(
        (_status) => _status === status
      );
      if (!isWithinAvailStatuses)
        return res
          .status(401)
          .json({ success: false, message: "Invalid status filter." });
    }

    const FILES_URL_CONTAINER =
      "https://api.buildhubware.com/public/soa-assets/buildhub.png";
    const axiosResponse = await axios.get(`${FILES_URL_CONTAINER}`, {
      responseType: "arraybuffer",
    });
    const imageBase64 = Buffer.from(axiosResponse.data, "binary").toString(
      "base64"
    );

    const date = new Date();
    const dateOptions = {
      weekday: undefined,
      year: "numeric",
      month: "long",
      day: "numeric",
      hour: "numeric",
      minute: "numeric",
      hour12: true,
    };
    const formattedDate = date.toLocaleString("en-US", dateOptions);

    const templatePath =
      __dirname + "/../../helpers/email-templates/soa-summary.html";

    const queryFilter = {};

    if (status !== "") {
      queryFilter.status = status;
    } else {
      queryFilter.status = {
        [Op.in]: ["Approved", "Case to case", "Suspended"],
      };
    }

    const customerIds = await BuildCreditCustomers.findAll({
      where: queryFilter,
      attributes: ["customer_id"],
    });

    const pdfData = {
      buildCreditCustomerBalances: [],
    };

    for (const customer of customerIds) {
      const pdcSentsPromise = PdcHistory.findAll({
        where: {
          customer_id: customer.customer_id,
        },
        include: [
          {
            model: RpafPayment,
            as: "payment_detail",
            attributes: [],
          },
        ],
        attributes: [
          [fn("SUM", col("payment_detail.amount_paid")), "total_amount_paid"],
        ],
        raw: true,
      });

      const pdcReceivedPromise = RpafPayment.findAll({
        where: {
          customer_id: customer.customer_id,
          status: "PDC Received",
        },
        attributes: [[fn("SUM", col("amount_paid")), "total_amount_paid_pdc"]],
        raw: true,
      });

      const pdcReceivedBuildcreditOverpaymentsPromise =
        BuildcreditOverPayments.findAll({
          where: {
            customer_id: customer.customer_id,
            payment_status: "PDC Received",
            status: true,
          },
          attributes: [[fn("SUM", col("amount")), "total_amount_paid_pdc"]],
        });

      const buildCreditBalancePromise = getBuildCreditBalance(
        customer.customer_id,
        true
      );

      const promises = await Promise.all([
        pdcSentsPromise,
        pdcReceivedPromise,
        buildCreditBalancePromise,
        pdcReceivedBuildcreditOverpaymentsPromise,
      ]);

      const pdcReceivedOverpayment =
        promises[3]?.[0]?.dataValues?.total_amount_paid_pdc || 0;
      const parsedPdcOverpayment = parseFloat(pdcReceivedOverpayment);

      const pdcReceivedPayment = promises[1]?.[0]?.total_amount_paid_pdc || 0;
      const parsedPdcReceivedPayment = parseFloat(pdcReceivedPayment);

      const totalAmountPaidPdc =
        parsedPdcOverpayment + parsedPdcReceivedPayment;
      const buildCreditDetail = promises[2];
      const data = {
        companyName: buildCreditDetail?.company_name,
        buildcreditStatus: buildCreditDetail?.buildcredit_status,
        creditTerms: buildCreditDetail?.credit_terms,
        creditLimit: formatAmount(buildCreditDetail?.approved_credit_limit),
        accountBalance: formatAmount(buildCreditDetail?.used_credit_limit),
        availableLine: formatAmount(buildCreditDetail?.current_bal),
        pdcSent: formatAmount(totalAmountPaidPdc),
        abLessPdc: formatAmount(
          buildCreditDetail?.used_credit_limit - parseFloat(totalAmountPaidPdc)
        ),
        alPlusPdc: formatAmount(
          buildCreditDetail?.current_bal + parseFloat(totalAmountPaidPdc)
        ),
      };

      pdfData.buildCreditCustomerBalances.push(data);
    }

    pdfData.buildCreditCustomerBalances =
      pdfData.buildCreditCustomerBalances.sort((a, b) => {
        return a.companyName.localeCompare(b.companyName);
      });

    pdfData.buildhubLogo = imageBase64;
    pdfData.dateNTime = formattedDate;

    const pdfResponse = await pdfBuilder(
      templatePath,
      "soa",
      "pdf-files",
      "landscape",
      pdfData
    );

    if (pdfResponse) {
      const urlToRedirect = `/public/pdf-files/${pdfResponse?.file_name}`;
      res.redirect(urlToRedirect);
    }

    // res.status(200).json({ success: true, pdfData });
  } catch (err) {
    next(err);
  }
};

exports.soaMultipleBuilder = async (req, res, next) => {
  try {
    const { area } = req.query;
    if (!area) {
      return res.status(200).json({ success: false, message: "No area found" });
    }

    // Check if this is a refresh after initial loading page
    if (req.query.refresh) {
      // Process and return the actual PDF
      const FILES_URL_CONTAINER =
        "https://api.buildhubware.com/public/soa-assets/buildhub.png";
      const axiosResponse = await axios.get(FILES_URL_CONTAINER, {
        responseType: "arraybuffer",
      });
      const imageBase64 = Buffer.from(axiosResponse.data, "binary").toString(
        "base64"
      );

      const date = new Date();
      const dateOptions = {
        year: "numeric",
        month: "long",
        day: "numeric",
        hour: "numeric",
        minute: "numeric",
        hour12: true,
      };
      const formattedDate = date.toLocaleString("en-US", dateOptions);

      const buildCreditRecords = await Rpaf.findAll({
        where: {
          status: {
            [Op.in]: ["Approved", "Case to case", "Suspended"],
          },
          [Op.and]: [
            Sequelize.where(
              Sequelize.fn("LOWER", Sequelize.col("area")),
              "LIKE",
              `%${area.toLowerCase()}%`
            ),
          ],
        },
        attributes: ["id", "customer_id"],
      });

      if (!buildCreditRecords || buildCreditRecords.length === 0) {
        return res.status(200).send(`
          <html><body>
            <h2>No BuildCredit records found for area: ${area}</h2>
            <a href="/">Go back</a>
          </body></html>
        `);
      }

      const pdfData = {
        customers: [],
        dateNTime: formattedDate,
        buildhubLogo: imageBase64,
        area: area.toUpperCase(),
      };

      // Process records in batches to avoid memory issues
      const batchSize = 5;
      for (let i = 0; i < buildCreditRecords.length; i += batchSize) {
        const batch = buildCreditRecords.slice(i, i + batchSize);

        const batchResults = await Promise.all(
          batch.map(async (record) => {
            const buildCreditDetail = await getBuildCreditBalance(
              record.customer_id,
              true
            );

            if (!buildCreditDetail) return null;

            const [pdcSents, pdcReceived, pdcReceivedOverpayment] =
              await Promise.all([
                PdcHistory.findAll({
                  where: { customer_id: record.customer_id },
                  include: [
                    {
                      model: RpafPayment,
                      as: "payment_detail",
                      attributes: [],
                    },
                  ],
                  attributes: [
                    [
                      fn("SUM", col("payment_detail.amount_paid")),
                      "total_amount_paid",
                    ],
                  ],
                  raw: true,
                }),
                RpafPayment.findAll({
                  where: {
                    customer_id: record.customer_id,
                    status: "PDC Received",
                  },
                  attributes: [
                    [fn("SUM", col("amount_paid")), "total_amount_paid_pdc"],
                  ],
                  raw: true,
                }),
                BuildcreditOverPayments.findAll({
                  where: {
                    customer_id: record.customer_id,
                    payment_status: "PDC Received",
                    status: true,
                  },
                  attributes: [
                    [fn("SUM", col("amount")), "total_amount_paid_pdc"],
                  ],
                }),
              ]);

            const totalAmountPaidPdc =
              parseFloat(pdcReceived?.[0]?.total_amount_paid_pdc || 0) +
              parseFloat(
                pdcReceivedOverpayment?.[0]?.dataValues
                  ?.total_amount_paid_pdc || 0
              );

            return {
              buildhubLogo: imageBase64,
              companyName: buildCreditDetail?.company_name,
              buildcreditStatus: buildCreditDetail?.buildcredit_status,
              creditTerms: buildCreditDetail?.credit_terms,
              creditLimit: formatAmount(
                buildCreditDetail?.approved_credit_limit
              ),
              accountBalance: formatAmount(
                buildCreditDetail?.used_credit_limit
              ),
              availableLine: formatAmount(buildCreditDetail?.current_bal),
              pdcSent: formatAmount(totalAmountPaidPdc),
              abLessPdc: formatAmount(
                buildCreditDetail?.used_credit_limit - totalAmountPaidPdc
              ),
              alPlusPdc: formatAmount(
                buildCreditDetail?.current_bal + totalAmountPaidPdc
              ),
              dateNTime: formattedDate,
              statementBreakdown: buildCreditDetail?.statementBreakdown,
            };
          })
        );

        pdfData.customers.push(...batchResults.filter(Boolean));
      }

      const statementPDF = await createStatementMultiplePDF(
        pdfData,
        imageBase64
      );
      res.setHeader("Content-Type", "text/html");
      return res.send(statementPDF);
    }

    // Initial request - show loading page with auto-refresh
    const loadingHTML = `
      <!DOCTYPE html>
      <html>
        <head>
          <title>Generating Statement of Account</title>
          <meta http-equiv="refresh" content="2;url=${req.originalUrl}&refresh=true">
          <style>
            body {
              font-family: Arial, sans-serif;
              display: flex;
              flex-direction: column;
              align-items: center;
              justify-content: center;
              height: 100vh;
              margin: 0;
              background-color: #f5f5f5;
            }
            .loader {
              border: 5px solid #f3f3f3;
              border-top: 5px solid #d32f2f;
              border-radius: 50%;
              width: 50px;
              height: 50px;
              animation: spin 1s linear infinite;
              margin-bottom: 20px;
            }
            @keyframes spin {
              0% { transform: rotate(0deg); }
              100% { transform: rotate(360deg); }
            }
            .status {
              font-size: 18px;
              color: #333;
            }
          </style>
        </head>
        <body>
          <div class="loader"></div>
          <div class="status">Generating your Statement of Account...</div>
        </body>
      </html>
    `;

    res.send(loadingHTML);
  } catch (err) {
    next(err);
  }
};

exports.customerSoa = async (req, res, next) => {
  try {
    const { customer } = req.query;
    if (!customer)
      return res
        .status(200)
        .json({ success: false, message: "No Customer ID Found" });

    const FILES_URL_CONTAINER =
      "https://api.buildhubware.com/public/soa-assets/buildhub.png";
    // const FILES_URL_CONTAINER = "http://localhost:3008/public/soa-assets/buildhub.png";
    const axiosResponse = await axios.get(`${FILES_URL_CONTAINER}`, {
      responseType: "arraybuffer",
    });
    const imageBase64 = Buffer.from(axiosResponse.data, "binary").toString(
      "base64"
    );

    const date = new Date();
    const dateOptions = {
      weekday: undefined,
      year: "numeric",
      month: "long",
      day: "numeric",
      hour: "numeric",
      minute: "numeric",
      hour12: true,
    };
    const formattedDate = date.toLocaleString("en-US", dateOptions);

    const templatePath = __dirname + "/../../helpers/email-templates/soa.html";

    const buildCreditDetail = await getBuildCreditBalance(customer, true, true);

    if (!buildCreditDetail)
      return res
        .status(200)
        .json({ success: false, message: "No BuildCredit Record Found" });

    const pdcSentsPromise = PdcHistory.findAll({
      where: {
        customer_id: customer,
      },
      include: [
        {
          model: RpafPayment,
          as: "payment_detail",
          attributes: [],
        },
      ],
      attributes: [
        [fn("SUM", col("payment_detail.amount_paid")), "total_amount_paid"],
      ],
      raw: true,
    });

    const pdcReceivedPromise = RpafPayment.findAll({
      where: {
        customer_id: customer,
        status: "PDC Received",
      },
      attributes: [[fn("SUM", col("amount_paid")), "total_amount_paid_pdc"]],
      raw: true,
    });

    const pdcReceivedBuildcreditOverpaymentsPromise =
      BuildcreditOverPayments.findAll({
        where: {
          customer_id: customer,
          payment_status: "PDC Received",
          status: true,
        },
        attributes: [[fn("SUM", col("amount")), "total_amount_paid_pdc"]],
      });

    const promises = await Promise.all([
      pdcSentsPromise,
      pdcReceivedPromise,
      pdcReceivedBuildcreditOverpaymentsPromise,
    ]);

    const totalAmountPaid = promises[0]?.[0]?.total_amount_paid || 0;

    const pdcReceivedRpafPayment = promises[1]?.[0]?.total_amount_paid_pdc || 0;
    const parsedPdcRpafPayment = parseFloat(pdcReceivedRpafPayment);

    const pdcReceivedOverpayment =
      promises[2]?.[0]?.dataValues.total_amount_paid_pdc || 0;
    const parsedPdcOverpayment = parseFloat(pdcReceivedOverpayment);

    const totalAmountPaidPdc =
      parseFloat(parsedPdcRpafPayment) + parseFloat(parsedPdcOverpayment);

    const data = {
      buildhubLogo: imageBase64,
      companyName: buildCreditDetail?.company_name,
      buildcreditStatus: buildCreditDetail?.buildcredit_status,
      creditTerms: buildCreditDetail?.credit_terms,
      creditLimit: formatAmount(buildCreditDetail?.approved_credit_limit),
      accountBalance: formatAmount(buildCreditDetail?.used_credit_limit),
      availableLine: formatAmount(buildCreditDetail?.current_bal),
      pdcSent: formatAmount(totalAmountPaidPdc),
      abLessPdc: formatAmount(
        buildCreditDetail?.used_credit_limit - parseFloat(totalAmountPaidPdc)
      ),
      alPlusPdc: formatAmount(
        buildCreditDetail?.current_bal + parseFloat(totalAmountPaidPdc)
      ),
      dateNTime: formattedDate,
      statementBreakdown: buildCreditDetail?.statementBreakdown,
    };

    // With Order Breakdown
    const statementPDF = await createStatementPDF(data, imageBase64, true);
    res.setHeader("Content-Type", "text/html");
    res.status(200).send(statementPDF);

    // Without Order Breakdown
    // const pdfResponse = await pdfBuilder(templatePath, "soa", "pdf-files", "landscape", data);
    // if (pdfResponse) {
    //   const urlToRedirect = `/public/pdf-files/${pdfResponse?.file_name}`;
    //   res.redirect(urlToRedirect);
    // }
  } catch (err) {
    next(err);
  }
};
