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,
  customerSoa = false,
) {
  // NOTE: Split Payments not included
  try {
    const paymentStatus = ["Verified"];

    if (customerSoa) paymentStatus.push("PDC Received");
    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: false
          ? {
              [Op.in]: [
                "Received",
                "Delivered",
                "For Delivery - Warehouse",
                "For-Delivery Warehouse",
              ],
            }
          : {
              [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: {
              [Op.in]: paymentStatus,
            },
          },
          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: {
          [Op.in]: paymentStatus,
        },
        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) {
        if (!customerSoa) {
          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,
  customerSoa = 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",
      to_be_collected_total: 0,
    };

    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 = [];

    let paymentStatus = ["Verified"];
    if (isSoa) {
      statementBreakdown = await getStatementBreakdown(
        customerId,
        buildCredit?.credit_date_limit,
        customerSoa,
      );
    }

    // if (customerSoa) {
    //   paymentStatus.push("PDC Received");
    // }

    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,
      },
    });

    const toBeCollectedOrderForms = await OrderForm.findAll({
      attributes: [
        [Sequelize.fn("SUM", Sequelize.col("total_amount")), "totalAmount"],
      ],
      where: {
        customer_id: customerId,
        status: {
          [Op.in]: [
            "Verified",
            "Release Order - Delivery",
            "Release Order - Customer Pickup",
            "For Preparation",
            "For Delivery",
          ],
        },
        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;

    let toBeCollectedOrderFormTotal = 0;
    if (
      toBeCollectedOrderForms &&
      toBeCollectedOrderForms[0] &&
      toBeCollectedOrderForms[0].dataValues.totalAmount
    ) {
      toBeCollectedOrderFormTotal =
        toBeCollectedOrderForms[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: {
          [Op.in]: paymentStatus,
        },
      },
      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: {
          [Op.in]: paymentStatus,
        },
        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;
    returnedResponse.to_be_collected_total =
      parseFloat(toBeCollectedOrderFormTotal) || 0;

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