const Rpaf = require("../models/Rpaf");
const OrderForm = require("../models/OrderForm");
const SplitPayments = require("../models/SplitPayments");
const RpafPayment = require("../models/RpafPayment");
const Customers = require("../models/Customers");
const { Op, fn, col } = require("sequelize");
const Sequelize = require("sequelize");
const BuildcreditOverPayments = require("../models/BuildcreditOverPayments");
const BuildcreditOverpaymentsOrders = require("../models/BuildcreditOverPaymentsOrders");
const { formatDate12Hour } = require("./common");
const OrderFormDetails = require("../models/OrderFormDetails");
const Products = require("../models/Products");

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

function addDate(dateOrdered, creditDateLimit) {
  const date = new Date(dateOrdered);

  if (isNaN(date.getTime())) {
      return "Invalid date format.";
  }

  date.setDate(date.getDate() + parseInt(creditDateLimit));
  const newFormattedDate = `${(date.getMonth() + 1).toString().padStart(2, '0')}/${date.getDate().toString().padStart(2, '0')}/${date.getFullYear()}`;
  return newFormattedDate;
}

function calculateDueDate(creditDateLimit, dateDelivered) {
  let dueDate;

  if (creditDateLimit !== "" && creditDateLimit !== null && dateDelivered !== "" && dateDelivered !== null) {
      const dueDateValue = creditDateLimit;
      const sanitizedDueDate = dueDateValue.replace(/\s+/g, '');
      
      const matches = sanitizedDueDate.match(/(\d+)days/i);
      
      if (matches) {
        const days = matches[1];
          dueDate = addDate(dateDelivered, days);
      } else {
          dueDate = "No Credit Date Limit Indicated";
      }
  } else {
      dueDate = "No Credit Date Limit Indicated";

      if(dateDelivered === "" || dateDelivered === null) {
          dueDate = "No Date Delivered";
      }
  }

  return dueDate;
}


async function getStatementBreakdown(customerId, creditDateLimit) {
  // NOTE: Split Payments not included
  try {
    let orderTotal = 0;
    let paymentsTotal = 0;
    const unpaidOrderForms = [];
    const orderFormsPromise = OrderForm.findAll({
      attributes: ["id", "reference_no", "total_amount", "date_created", "date_delivered"],
      where: {
        customer_id: customerId,
        status: {
          [Op.notIn]: ["Cancelled", "Denied", "Release Order - Expired"],
        },
        payment_method: {
          [Op.in]: ["RPAF", "Build Credit", "BUILDCREDIT INTEREST", "EOS ORDER"],
        },
        is_waived: false,
      },
      include: [
        {
          attributes: ["id", "amount_paid"],
          model: RpafPayment,
          as: "rpaf_payment",
          where: {
            status: "Verified",
          },
          required: false,
        },
        {
          model: OrderFormDetails,
          as: "order_form_details",
          required: false,
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["menu"],
            },
          ],
        },
      ],
    });

    const overpaymentsPromise = BuildcreditOverPayments.findAll({
      where: {
        customer_id: customerId,
        payment_status: "Verified",
        status: true,
      },
      include: [
        {
          model: BuildcreditOverpaymentsOrders,
          as: "buildcredit_overpayment_orders",
          attributes: ["id", "order_reference_no", "reference_no", "amount_paid"],
          where: {
            status: true,
          },
          required: false,
        },
      ],
    });

    const [orderForms, overpayments] = await Promise.all([orderFormsPromise, overpaymentsPromise]);

    // Overpayments
    for (const overpayment of overpayments) {
      const { buildcredit_overpayment_orders, reference_no: overpaymentReferenceNo, amount, datetime } = overpayment;

      const overPaymentTotalAmountPaid = parseFloat(amount);

      let paymentsTotal = overPaymentTotalAmountPaid;
      let remarks = "0 Links Payment";

      if (buildcredit_overpayment_orders?.length > 0) {
        const linkedOrdersTotalAmount = buildcredit_overpayment_orders.reduce(
          (total, payment) => total + parseFloat(payment.amount_paid),
          0
        );

        paymentsTotal -= linkedOrdersTotalAmount;
        remarks = "Payment Remaining Amount";
      }

      if (paymentsTotal > 0) {
        unpaidOrderForms.push({
          order_form_id: "",
          order_reference_no: `OP: ${overpaymentReferenceNo}`,
          order_total_amount: formatAmount(0),
          payments_total: formatAmount(paymentsTotal),
          unpaid_balance: formatAmount(0),
          date: formatDate12Hour(datetime),
          remarks,
        });
      }
    }
    // Overpayments

    for (const orderForm of orderForms) {
      const { id, reference_no, total_amount, rpaf_payment, date_created, date_delivered, order_form_details } =
        orderForm;

      const dueDate = calculateDueDate(creditDateLimit, date_delivered);
      const parsedTotalAmount = parseFloat(total_amount);

      orderTotal += parsedTotalAmount;

      let accumulatedPayments = 0;
      if (rpaf_payment.length > 0) {
        rpaf_payment.map((payment) => {
          accumulatedPayments += parseFloat(payment.amount_paid);
          paymentsTotal += parseFloat(payment.amount_paid);
        });
      }

      // Overpayments
      for (const overpayment of overpayments) {
        const { buildcredit_overpayment_orders, reference_no: overpaymentReferenceNo, amount } = overpayment;

        let overpaymentPaymentRemaining = parseFloat(amount);
        if (buildcredit_overpayment_orders && buildcredit_overpayment_orders.length > 0) {
          const overpaymentsPaymentArray = buildcredit_overpayment_orders.filter(
            (overpaymentPayment) => overpaymentPayment.order_reference_no === reference_no
          );

          if (overpaymentsPaymentArray && overpaymentsPaymentArray.length > 0) {
            overpaymentsPaymentArray.map((payment) => {
              accumulatedPayments += parseFloat(payment.amount_paid);
              overpaymentPaymentRemaining -= parseFloat(payment.amount_paid);
            });
          }
        }
      }
      // Overpayments

      const orderDifference = parsedTotalAmount - accumulatedPayments;

      if (orderDifference > 0) {
        unpaidOrderForms.push({
          order_form_id: id,
          order_reference_no: reference_no,
          order_total_amount: formatAmount(parsedTotalAmount),
          payments_total: formatAmount(accumulatedPayments),
          unpaid_balance: formatAmount(orderDifference),
          date: formatDate12Hour(date_created),
          due_date: dueDate,
          date_delivered: date_delivered ? formatDate12Hour(date_delivered) : "",
          remarks: "",
          order_form_details: order_form_details ? order_form_details : [],
        });
      }

      if (orderDifference < 0) {
        unpaidOrderForms.push({
          order_form_id: id,
          order_reference_no: reference_no,
          order_total_amount: formatAmount(parsedTotalAmount),
          payments_total: formatAmount(accumulatedPayments),
          unpaid_balance: formatAmount(0),
          date: formatDate12Hour(date_created),
          due_date: dueDate,
          date_delivered: date_delivered ? formatDate12Hour(date_delivered) : "",
          remarks: "Overpayment",
          order_form_details: order_form_details ? order_form_details : [],
        });
      }
    }

    unpaidOrderForms.sort((a, b) => {
      const dateA = a.date ? new Date(a.date) : new Date(0);
      const dateB = b.date ? new Date(b.date) : new Date(0);
      return dateB - dateA;
    });

    return unpaidOrderForms;
  } catch (err) {
    throw err;
  }
}

exports.getBuildCreditBalance = async (customerId, isSoa = false) => {
  try {
    let ___creditLimit = 0,
      ___orderFormTotal = 0,
      ___orderFormSplitTotal = 0,
      ___rpafPaymentTotal = 0,
      ___overAllTotal = 0;
    const returnedResponse = {
      company_name: "",
      credit_terms: "",
      approved_credit_limit: 0,
      used_credit_limit: 0,
      current_bal: 0,
      pdc_sent: 0,
      status: false,
      buildcredit_status: "N/A",
    };

    const statusFilter = isSoa ? ["Approved", "Case to case", "Suspended"] : ["Approved", "Case to case"];
    const buildCredit = await Rpaf.findOne({
      where: {
        customer_id: customerId,
        status: {
          [Op.in]: statusFilter,
        },
      },
    });

    if (!buildCredit) return returnedResponse;

    let statementBreakdown = [];
    if (isSoa) {
      statementBreakdown = await getStatementBreakdown(customerId, buildCredit?.credit_date_limit);
    }
    const creditLimit = parseFloat(buildCredit.credit_limit.replace(/,/g, ""));
    if (creditLimit) ___creditLimit = creditLimit;

    const _orderForms = await OrderForm.findAll({
      attributes: [[Sequelize.fn("SUM", Sequelize.col("total_amount")), "totalAmount"]],
      where: {
        customer_id: customerId,
        status: {
          [Op.notIn]: ["Cancelled", "Denied", "Release Order - Expired"],
        },
        payment_method: {
          [Op.in]: ["RPAF", "Build Credit", "BUILDCREDIT INTEREST", "EOS ORDER"],
        },
        is_waived: false,
      },
    });
    if (_orderForms && _orderForms[0] && _orderForms[0].dataValues.totalAmount)
      ___orderFormTotal = _orderForms[0].dataValues.totalAmount;

    const __orderFormsSplit = await OrderForm.findAll({
      attributes: ["id", "split_payment_id"],
      where: {
        customer_id: customerId,
        status: {
          [Op.notIn]: ["Cancelled", "Denied"],
        },
        payment_method: {
          [Op.in]: ["Split"],
        },
      },
    });

    const orderFormIds = __orderFormsSplit.map((form) => form.split_payment_id);

    const _splitPayments = await SplitPayments.findAll({
      attributes: [[Sequelize.fn("SUM", Sequelize.col("rpaf_payment")), "totalAmount"]],
      where: {
        id: {
          [Op.in]: orderFormIds,
        },
      },
    });

    if (_splitPayments && _splitPayments[0] && _splitPayments[0].dataValues.totalAmount)
      ___orderFormSplitTotal = _splitPayments[0].dataValues.totalAmount;

    const _rpafPayments = await RpafPayment.findAll({
      where: {
        customer_id: customerId,
        status: "Verified",
      },
      include: [
        {
          model: OrderForm,
          as: "order_form_detail",
          attributes: [],
          where: {
            status: {
              [Op.notIn]: ["Cancelled", "Denied"],
            },
          },
          required: true,
        },
      ],
      attributes: [[Sequelize.fn("SUM", Sequelize.col("amount_paid")), "total_amount_paid"]],
    });

    const _buildcreditOverPayments = await BuildcreditOverPayments.findAll({
      where: {
        customer_id: customerId,
        payment_status: "Verified",
        status: true,
      },
      attributes: [[Sequelize.fn("SUM", Sequelize.col("amount")), "total_amount_paid"]],
    });

    if (_rpafPayments && _rpafPayments[0] && _rpafPayments[0].dataValues.total_amount_paid)
      ___rpafPaymentTotal = parseFloat(_rpafPayments[0].dataValues.total_amount_paid) || 0;

    if (
      _buildcreditOverPayments &&
      _buildcreditOverPayments[0] &&
      _buildcreditOverPayments[0].dataValues.total_amount_paid
    )
      ___rpafPaymentTotal += parseFloat(_buildcreditOverPayments[0].dataValues.total_amount_paid) || 0;

    // Buildcredit Beginning Balance
    const customerDetail = await Customers.findOne({
      where: {
        id: customerId,
      },
      attributes: ["buildcredit_beginning_balance"],
    });

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

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

    const pdcOverpayments = pdcReceivedBuildcreditOverpayments?.[0]?.dataValues.total_amount_paid_pdc || 0;

    const _pdcReceived = pdcReceived?.[0]?.total_amount_paid_pdc || 0;
    const parsedPdcOverpayments = parseFloat(pdcOverpayments);
    const parsedPdcPayments = parseFloat(_pdcReceived);

    const totalAmountPaidPdc = parsedPdcPayments + parsedPdcOverpayments;

    const buildcreditBeginningBalance =
      customerDetail?.buildcredit_beginning_balance != null
        ? parseFloat(customerDetail.buildcredit_beginning_balance)
        : 0;

    const orderFormTotal =
      parseFloat(___orderFormTotal) + parseFloat(___orderFormSplitTotal) + buildcreditBeginningBalance;
    const orderFormSubTotal = parseFloat(orderFormTotal) - parseFloat(___rpafPaymentTotal);
    ___overAllTotal = parseFloat(___creditLimit - orderFormSubTotal);

    // if (___overAllTotal < 1) ___overAllTotal = 0;
    returnedResponse.company_name = buildCredit.company_name;
    returnedResponse.credit_terms = buildCredit.credit_date_limit;
    returnedResponse.approved_credit_limit = ___creditLimit;
    returnedResponse.used_credit_limit = parseFloat(___creditLimit - ___overAllTotal);
    returnedResponse.current_bal = ___overAllTotal;
    returnedResponse.pdc_sent = parseFloat(totalAmountPaidPdc);
    returnedResponse.buildcredit_status = buildCredit.status;
    returnedResponse.status = ___overAllTotal > 0;
    returnedResponse.statementBreakdown = isSoa ? statementBreakdown : null;

    console.log(`PAYMENTS TOTAL 2: ${___rpafPaymentTotal}`);
    console.log(`ORDER TOTAL 2: ${orderFormTotal}`);

    return returnedResponse;
  } catch (err) {
    return err;
  }
};
