const PurchaseOrderDetails = require("../../models/PurchaseOrderDetails");
const PurchaseOrders = require("../../models/PurchaseOrders");
const sequelizeConnect = require("../../connection/db");
const {
  generateRandomString,
  generateRandomNumber,
} = require("../../helpers/common");
const SellersCompany = require("../../models/SellersCompany");
const Suppliers = require("../../models/Suppliers");
const Products = require("../../models/Products");
const PurchaseOrderApprovers = require("../../models/PurchaseOrderApprovers");
const PurchaseOrderApprovals = require("../../models/PurchaseOrderApprovals");
const { Op } = require("sequelize");
const PurchaseReceives = require("../../models/PurchaseReceives");
const PurchaseReceiveDetails = require("../../models/PurchaseReceiveDetails");
const Warehouses = require("../../models/Warehouses");
const sequelize = require("sequelize");
const {
  checkPOItemStock,
  sellerEnabledPurchaseOrders,
  getInventoryReport,
  getDetailedInventoryReport,
} = require("../../services/purchase-order.service");
const Sequelize = require("sequelize");
const InventoryTransfer = require("../../models/InventoryTransfer");
const OrderForm = require("../../models/OrderForm");
const OrderFormDetails = require("../../models/OrderFormDetails");
const SellerBadOrderContainer = require("../../models/SellerBadOrderContainer");
const AdminExternalActionsLogs = require("../../models/AdminExternalActionsLogs");
const InventoryTransferDetails = require("../../models/InventoryTransferDetails");
const TaxCodes = require("../../models/TaxCodes");

const ProductImages = require("../../models/ProductImages");
const {
  sellerProductImageBuilder,
} = require("../../helpers/image-src-builder");

exports.getAllSellersCompany = async (req, res, next) => {
  try {
    const sellersCompany = await SellersCompany.findAll({
      attributes: ["id", "company_name"],
    });
    res.status(200).json({ success: true, results: sellersCompany });
  } catch (err) {
    next(err);
  }
};

exports.getAllSuppliers = async (req, res, next) => {
  try {
    const suppliers = await Suppliers.findAll({});
    res.status(200).json({ success: true, results: suppliers });
  } catch (err) {
    next(err);
  }
};

exports.getSellersCompanyProducts = async (req, res, next) => {
  const { sellerCompanyId } = req.query;

  try {
    const sellersCompanyProducts = await Products.findAll({
      where: {
        seller_id: sellerCompanyId,
      },
      attributes: ["id", "parent_id", "menu", "price", "discounted_price"],
    });

    const cleanedProducts = [];
    for (const p of sellersCompanyProducts) {
      let pName = p.menu;
      if (p && p.parent_id) {
        const parentProduct = sellersCompanyProducts.find(
          (pr) => pr.id === p.parent_id,
        );
        if (parentProduct) {
          pName = `${parentProduct.menu} - ${p.menu}`;
          cleanedProducts.push({
            id: p.id,
            menu: pName,
            price: p.price,
            discounted_price: p.discounted_price,
          });
        }
      } else {
        cleanedProducts.push({
          id: p.id,
          menu: pName,
          price: p.price,
          discounted_price: p.discounted_price,
        });
      }
    }

    cleanedProducts.sort((a, b) =>
      a.menu.localeCompare(b.menu, undefined, { sensitivity: "base" }),
    );

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

exports.getWarehouses = async (req, res, next) => {
  try {
    const warehouses = await Warehouses.findAll({
      where: {
        status: true,
      },
    });

    res.status(200).json({ success: true, results: warehouses });
  } catch (err) {
    next(err);
  }
};
exports.createEditPurchaseOrder = async (req, res, next) => {
  const {
    id,
    orderDate,
    remarks,
    sellerCompanyId,
    supplierId,
    poItems = [],
    adminToken,
  } = req.body;

  if (!sellerCompanyId) {
    return res.status(400).json({ error: "Seller company ID is required" });
  }
  if (!supplierId) {
    return res.status(400).json({ error: "Supplier ID is required" });
  }
  if (!orderDate) {
    return res.status(400).json({ error: "Order date is required" });
  }
  if (!Array.isArray(poItems) || poItems.length === 0) {
    return res.status(400).json({ error: "At least one PO item is required" });
  }

  for (const item of poItems) {
    if (!item.productId) {
      return res
        .status(400)
        .json({ error: "Product ID is required for all items" });
    }
    if (!item.quantity || item.quantity <= 0) {
      return res
        .status(400)
        .json({ error: "Valid quantity is required for all items" });
    }
    if (!item.unitPrice || item.unitPrice <= 0) {
      return res
        .status(400)
        .json({ error: "Valid unit price is required for all items" });
    }
  }

  const transaction = await sequelizeConnect.transaction();
  try {
    const sellersCompany = await SellersCompany.findByPk(sellerCompanyId, {
      transaction,
    });
    if (!sellersCompany) {
      await transaction.rollback();
      return res.status(404).json({ error: "Seller company not found" });
    }

    const supplier = await Suppliers.findByPk(supplierId, { transaction });
    if (!supplier) {
      await transaction.rollback();
      return res.status(404).json({ error: "Supplier not found" });
    }

    let purchaseOrder;
    if (id) {
      purchaseOrder = await PurchaseOrders.findByPk(id, { transaction });
      if (!purchaseOrder) {
        await transaction.rollback();
        return res.status(404).json({ error: "Purchase order not found" });
      }

      await purchaseOrder.update(
        {
          supplier_id: supplier.id,
          seller_id: sellersCompany.id,
          order_date: orderDate,
          remarks: remarks,
          updated_by_admin_token: adminToken,
        },
        { transaction },
      );

      const existingItems = await PurchaseOrderDetails.findAll({
        where: { purchase_order_id: id },
        transaction,
      });

      const existingItemIds = existingItems.map((item) => item.id);
      const newItemIds = poItems
        .filter((item) => item.id)
        .map((item) => item.id);

      const itemsToDelete = existingItemIds.filter(
        (id) => !newItemIds.includes(id),
      );
      if (itemsToDelete.length > 0) {
        await PurchaseOrderDetails.destroy({
          where: { id: itemsToDelete },
          transaction,
        });
      }

      for (const item of poItems) {
        const { id: itemId, productId, quantity, unitPrice } = item;

        const totalAmount = parseFloat(unitPrice) * parseFloat(quantity);

        const product = await Products.findByPk(productId, { transaction });
        if (!product) {
          await transaction.rollback();
          return res
            .status(404)
            .json({ error: `Product with ID ${productId} not found` });
        }

        if (itemId) {
          await PurchaseOrderDetails.update(
            {
              product_id: productId,
              quantity: quantity,
              unit_price: unitPrice,
              total_amount: totalAmount,
            },
            {
              where: { id: itemId },
              transaction,
            },
          );
        } else {
          await PurchaseOrderDetails.create(
            {
              purchase_order_id: id,
              product_id: productId,
              quantity: quantity,
              unit_price: unitPrice,
              total_amount: totalAmount,
            },
            { transaction },
          );
        }
      }
    } else {
      const poReferenceNo = `PO-${generateRandomString()}-${generateRandomNumber()}`;

      purchaseOrder = await PurchaseOrders.create(
        {
          po_reference_no: poReferenceNo,
          supplier_id: supplier.id,
          seller_id: sellersCompany.id,
          order_date: orderDate,
          remarks: remarks,
          created_by_admin_token: adminToken,
        },
        { transaction },
      );

      let subTotal = 0;
      let taxTotal = 0;
      let totalAmount = 0;

      for (const item of poItems) {
        const {
          productId,
          quantity,
          unitPrice,
          total_amount,
          taxCode,
          taxRate,
        } = item;

        const parsedTotalAmount = parseFloat(total_amount);
        subTotal += parsedTotalAmount;
        totalAmount += parsedTotalAmount;

        const parsedTaxRate = parseFloat(taxRate) || 0;
        const taxAmount = parsedTotalAmount * parsedTaxRate;
        taxTotal += taxAmount;
        totalAmount += taxAmount;

        const product = await Products.findByPk(productId, { transaction });
        if (!product) {
          await transaction.rollback();
          return res
            .status(404)
            .json({ error: `Product with ID ${productId} not found` });
        }

        await PurchaseOrderDetails.create(
          {
            purchase_order_id: purchaseOrder.id,
            product_id: productId,
            quantity: quantity,
            unit_price: unitPrice,
            tax_code: taxCode || null,
            tax_rate: taxRate || 0,
            total_amount: parsedTotalAmount,
          },
          { transaction },
        );
      }
      purchaseOrder.sub_total = parseFloat(subTotal);
      purchaseOrder.tax = parseFloat(taxTotal);
      purchaseOrder.total_amount = parseFloat(totalAmount);
      await purchaseOrder.save({ transaction });
    }

    const resultOrder = await PurchaseOrders.findByPk(purchaseOrder.id, {
      include: [
        {
          model: PurchaseOrderDetails,
          as: "purchase_order_details",
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["id", "menu", "price", "discounted_price"],
            },
          ],
        },
        {
          model: Suppliers,
          as: "supplier",
        },
        {
          model: SellersCompany,
          as: "seller_company",
          attributes: ["id", "company_name"],
        },
      ],
      transaction,
    });

    await transaction.commit();

    return res
      .status(id ? 200 : 201)
      .json({ success: true, results: resultOrder });
  } catch (err) {
    console.log("ERROR: ", err);
    if (transaction) await transaction.rollback();
    next(err);
  }
};

exports.getForApprovalPOs = async (req, res, next) => {
  try {
    const { userId } = req.query;

    const [poApprovers, purchaseOrders] = await Promise.all([
      PurchaseOrderApprovers.findAll({
        where: { status: true },
        attributes: ["id", "seller_id", "user_id", "level"],
      }),
      PurchaseOrders.findAll({
        where: { status: true, approval_status: false },
        include: [
          {
            model: PurchaseOrderApprovals,
            as: "purchase_order_approvals",
            where: { status: true },
            required: false,
            attributes: ["id", "level", "approval_status", "approver_id"],
          },
          {
            model: PurchaseOrderDetails,
            as: "purchase_order_details",
            include: [
              {
                model: Products,
                as: "product",
                attributes: ["id", "menu", "parent_id"],
                include: [
                  {
                    model: Products,
                    as: "parent",
                    attributes: ["id", "menu", "parent_id"],
                  },
                ],
              },
            ],
          },
          {
            model: Suppliers,
            as: "supplier",
          },
          {
            model: SellersCompany,
            as: "seller_company",
            attributes: ["id", "company_name"],
          },
        ],
        attributes: [
          "id",
          "seller_id",
          "po_reference_no",
          "order_date",
          "supplier_id",
          "remarks",
          "sub_total",
          "tax",
          "total_amount",
        ],
      }),
    ]);

    const approversMap = poApprovers.reduce((map, approver) => {
      const key = approver.seller_id || "global";
      if (!map[key]) map[key] = [];
      map[key].push(approver);
      return map;
    }, {});

    const forApprovalPos = purchaseOrders.filter((po) => {
      const sellerApprovers = approversMap[po.seller_id] || [];
      const globalApprovers = approversMap.global || [];
      const applicableApprovers = [...sellerApprovers, ...globalApprovers];

      if (applicableApprovers.length === 0) return false;

      const userHasSellerId = poApprovers.find(
        (poApprover) => poApprover.user_id === userId,
      );

      if (userHasSellerId && userHasSellerId.seller_id) {
        const isUserAnApprover = applicableApprovers.some(
          (a) => a.seller_id == userHasSellerId.seller_id,
        );

        if (!isUserAnApprover) return false;
      } else {
        const isUserAnApprover = applicableApprovers.some(
          (a) => a.user_id == userId,
        );
        if (!isUserAnApprover) return false;
      }

      const maxLevel = Math.max(...applicableApprovers.map((a) => a.level));

      const hasMaxLevelApproval = po.purchase_order_approvals?.some(
        (approval) =>
          approval.level === maxLevel &&
          approval.approval_status === "Approved",
      );

      const userAlreadyApproved = po.purchase_order_approvals?.some(
        (approval) => approval.approver_id == userId,
      );

      return !hasMaxLevelApproval && !userAlreadyApproved;
    });

    res.status(200).json({
      success: true,
      count: forApprovalPos.length,
      results: forApprovalPos.map((po) => ({
        id: po.id,
        po_reference_no: po.po_reference_no,
        order_date: po.order_date,
        seller_id: po.seller_id,
        purchase_order_details: po.purchase_order_details,
        supplier: po.supplier,
        remarks: po.remarks,
        seller_company: po.seller_company,
        sub_total: po.sub_total,
        tax: po.tax,
        total_amount: po.total_amount,
      })),
    });
  } catch (err) {
    console.error("Error in getForApprovalPOs:", err);
    next(err);
  }
};

exports.approveRejectPO = async (req, res, next) => {
  const transaction = await sequelizeConnect.transaction();
  try {
    const { purchaseOrderId, approvalStatus, remarks, userId, userToken } =
      req.body;

    if (!purchaseOrderId) {
      await transaction.rollback();
      return res.status(400).json({ error: "Purchase order ID is required" });
    }

    if (!["Approved", "Rejected"].includes(approvalStatus)) {
      await transaction.rollback();
      return res.status(400).json({
        error: 'Invalid approval status. Must be "Approved" or "Rejected"',
      });
    }

    const purchaseOrder = await PurchaseOrders.findByPk(purchaseOrderId, {
      transaction,
      include: [
        {
          model: PurchaseOrderApprovals,
          as: "purchase_order_approvals",
          transaction,
        },
      ],
    });

    if (!purchaseOrder) {
      await transaction.rollback();
      return res.status(404).json({ error: "Purchase order not found" });
    }

    if (!purchaseOrder.status) {
      await transaction.rollback();
      return res.status(400).json({ error: "Purchase order is not active" });
    }

    const approverRecords = await PurchaseOrderApprovers.findAll({
      where: {
        user_id: userId,
        status: true,
        [Op.or]: [
          { seller_id: purchaseOrder.seller_id },
          { seller_id: null },
          { seller_id: "" },
        ],
      },
      transaction,
    });

    if (approverRecords.length === 0) {
      await transaction.rollback();
      return res.status(403).json({
        error: "User is not authorized to approve this purchase order",
      });
    }

    const poApprovers = await PurchaseOrderApprovers.findAll({
      where: {
        [Op.or]: [
          { seller_id: purchaseOrder.seller_id },
          { seller_id: null },
          { seller_id: "" },
        ],
        status: true,
      },
      transaction,
    });

    const maxLevel = Math.max(...poApprovers.map((a) => a.level));
    const maxLevelApproval = purchaseOrder.purchase_order_approvals.find(
      (a) => a.level === maxLevel && a.approval_status === "Approved",
    );

    if (maxLevelApproval) {
      await transaction.rollback();
      return res
        .status(400)
        .json({ error: "Purchase order has already been fully approved" });
    }

    const existingApproval = purchaseOrder.purchase_order_approvals.find(
      (a) => a.approver_id == userId,
    );
    if (existingApproval) {
      await transaction.rollback();
      return res.status(400).json({
        error: "User has already approved/rejected this purchase order",
      });
    }

    const userApprovalLevel = Math.max(...approverRecords.map((r) => r.level));

    const approvalRecord = await PurchaseOrderApprovals.create(
      {
        purchase_order_id: purchaseOrderId,
        approver_id: userId,
        level: userApprovalLevel,
        approval_status: approvalStatus,
        remarks: remarks,
        status: true,
      },
      { transaction },
    );

    const approvedLevels = [
      ...purchaseOrder.purchase_order_approvals,
      approvalRecord,
    ]
      .filter((a) => a.approval_status === "Approved")
      .map((a) => a.level);

    const isFinalApproval = Math.max(...approvedLevels) === maxLevel;

    if (isFinalApproval) {
      purchaseOrder.approval_status = true;
      await purchaseOrder.save({ transaction });
    }

    const resultOrder = await PurchaseOrders.findByPk(purchaseOrder.id, {
      include: [
        {
          model: PurchaseOrderDetails,
          as: "purchase_order_details",
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["id", "menu", "price", "discounted_price"],
            },
          ],
        },
        {
          model: Suppliers,
          as: "supplier",
        },
        {
          model: SellersCompany,
          as: "seller_company",
          attributes: ["id", "company_name"],
        },
      ],
      transaction,
    });

    await transaction.commit();

    return res.status(200).json({
      success: true,
      message: `Purchase order ${approvalStatus.toLowerCase()} successfully`,
      data: {
        result: resultOrder,
        approvalId: approvalRecord.id,
        purchaseOrderId: purchaseOrder.id,
        currentStatus: isFinalApproval ? "Fully Approved" : approvalStatus,
        nextAction: isFinalApproval
          ? null
          : `Waiting for level ${userApprovalLevel + 1} approval`,
      },
    });
  } catch (err) {
    await transaction.rollback();
    console.error("Error in approveRejectPO:", err);
    next(err);
  }
};

exports.createEditPurchaseOrderReceive = async (req, res, next) => {
  try {
    const {
      id,
      purchaseOrderId,
      receiveDate,
      deliveryRefNo,
      deliveryDate = null,
      warehouseId,
      remarks,
      poItems = [],
      userId,
      userToken,
    } = req.body;

    const purchaseOrder = await PurchaseOrders.findOne({
      where: {
        id: purchaseOrderId,
      },
      include: [
        {
          model: PurchaseOrderDetails,
          as: "purchase_order_details",
        },
      ],
    });

    if (!purchaseOrder)
      return res
        .status(400)
        .json({ success: false, error: "Invalid purchase order." });

    const porReferenceNo = `POR-${generateRandomString()}-${generateRandomNumber()}`;

    const purchaseOrderReceive = await PurchaseReceives.create({
      receive_reference_no: porReferenceNo,
      purchase_order_id: purchaseOrderId,
      receive_date: receiveDate,
      delivery_reference_no: deliveryRefNo,
      delivery_date: deliveryDate || null,
      warehouse_id: warehouseId,
      remarks,
      created_by_admin_token: userToken,
    });

    for (const po of poItems) {
      const {
        id: poId,
        purchaseOrderDetailId,
        productId,
        quantityOrdered,
        quantityReceived,
        unitPrice,
        expiryDate = null,
        condition,
      } = po;

      const purchaseReceiveDetail = await PurchaseReceiveDetails.create({
        purchase_receive_id: purchaseOrderReceive.id,
        purchase_order_detail_id: purchaseOrderDetailId,
        product_id: productId,
        quantity_ordered: quantityOrdered,
        quantity_received: quantityReceived,
        unit_price: unitPrice,
        expiry_date: expiryDate || null,
        condition,
      });
    }

    const result = await PurchaseReceives.findByPk(purchaseOrderReceive.id, {
      include: [
        {
          model: PurchaseReceiveDetails,
          as: "purchase_receive_details",
          include: [
            {
              model: Products,
              as: "product",
            },
          ],
        },
      ],
    });

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

exports.getAvailablePOs = async (req, res, next) => {
  try {
    const { sellerId } = req.query;

    const whereCondition = sellerId
      ? { seller_id: sellerId, approval_status: true }
      : { approval_status: true };

    const purchaseOrderApprovers = await PurchaseOrderApprovers.findAll({
      where: { status: true },
      attributes: ["id", "seller_id", "user_id", "level"],
    });

    const approversMap = purchaseOrderApprovers.reduce((map, approver) => {
      const key = approver.seller_id || "global";
      if (!map[key]) map[key] = [];
      map[key].push(approver);
      return map;
    }, {});

    const purchaseOrders = await PurchaseOrders.findAll({
      where: whereCondition,
      include: [
        {
          model: PurchaseOrderDetails,
          as: "purchase_order_details",
          attributes: [
            "id",
            "product_id",
            "quantity",
            "unit_price",
            "total_amount",
          ],
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["id", "menu"],
            },
          ],
        },
        {
          model: PurchaseOrderApprovals,
          as: "purchase_order_approvals",
          where: { approval_status: "Approved" },
          required: false,
          attributes: [
            "id",
            "level",
            "approval_status",
            "approver_id",
            "createdAt",
          ],
        },
        {
          model: SellersCompany,
          as: "seller_company",
          attributes: ["id", "company_name"],
        },
        {
          model: Suppliers,
          as: "supplier",
        },
      ],
      order: [["createdAt", "DESC"]],
    });

    // const fullyApprovedPOs = purchaseOrders.filter((po) => {
    //   const sellerApprovers = approversMap[po.seller_id] || [];
    //   const globalApprovers = approversMap.global || [];
    //   const applicableApprovers = [...sellerApprovers, ...globalApprovers];

    //   if (applicableApprovers.length === 0) return false;

    //   const maxLevelNeeded = Math.max(
    //     ...applicableApprovers.map((a) => a.level)
    //   );
    //   const approvedLevels = po.purchase_order_approvals
    //     .map((approval) => approval.level)
    //     .filter((level) => level !== undefined);

    //   return approvedLevels.includes(maxLevelNeeded);
    // });

    const poDetailIds = purchaseOrders.flatMap((po) =>
      po.purchase_order_details.map((detail) => detail.id),
    );

    const receivedItems = await PurchaseReceiveDetails.findAll({
      where: {
        purchase_order_detail_id: poDetailIds,
      },
      attributes: [
        "purchase_order_detail_id",
        [
          sequelize.fn("SUM", sequelize.col("quantity_received")),
          "total_received",
        ],
      ],
      group: ["purchase_order_detail_id"],
    });

    const receivedMap = receivedItems.reduce((map, item) => {
      map[item.purchase_order_detail_id] =
        parseFloat(item.get("total_received")) || 0;
      return map;
    }, {});

    const response = purchaseOrders.map((po) => {
      const poItems = po.purchase_order_details.map((detail) => {
        const totalReceived = receivedMap[detail.id] || 0;
        return {
          id: detail.id,
          product_id: detail.product_id,
          product: detail.product,
          quantity: parseFloat(detail.quantity),
          unit_price: parseFloat(detail.unit_price),
          total_amount: parseFloat(detail.total_amount),
          quantity_received: totalReceived,
          quantity_balance: parseFloat(detail.quantity) - totalReceived,
        };
      });

      return {
        id: po.id,
        po_reference_no: po.po_reference_no,
        seller_id: po.seller_id,
        seller_company: po.seller_company,
        supplier_id: po.supplier_id,
        supplier: po.supplier,
        order_date: po.order_date,
        status: po.status,
        remarks: po.remarks,
        total_amount: poItems.reduce((sum, item) => sum + item.total_amount, 0),
        item_count: poItems.length,
        last_approval_date:
          po.purchase_order_approvals.length > 0
            ? new Date(
                Math.max(
                  ...po.purchase_order_approvals.map(
                    (a) => new Date(a.createdAt),
                  ),
                ),
              )
            : null,
        poItems,
      };
    });

    res.status(200).json({
      success: true,
      count: response.length,
      data: response,
    });
  } catch (err) {
    console.error("Error in getAvailablePOs:", err);
    next(err);
  }
};

exports.getSellersCompanyProductsWithPoStocks = async (req, res, next) => {
  const { sellerCompanyId, keyword } = req.query;

  if (!sellerCompanyId) {
    return res.status(400).json({
      success: false,
      message: "sellerCompanyId is required",
    });
  }

  try {
    const whereConditions = {
      seller_id: sellerCompanyId,
      product_status: true,
    };

    const additionalWhereConditions = [];

    if (keyword && keyword.trim()) {
      const searchKeyword = `%${keyword.toLowerCase().trim()}%`;
      whereConditions[Sequelize.Op.and] = [
        ...additionalWhereConditions,
        Sequelize.where(Sequelize.fn("LOWER", Sequelize.col("products.menu")), {
          [Sequelize.Op.like]: searchKeyword,
        }),
      ];
    } else if (additionalWhereConditions.length > 0) {
      whereConditions[Sequelize.Op.and] = additionalWhereConditions;
    }

    const sellersCompanyProducts = await Products.findAll({
      where: whereConditions,
      attributes: ["id", "parent_id", "menu", "price", "discounted_price"],
      include: [
        {
          model: Products,
          as: "parent",
          attributes: ["id", "menu", "price", "discounted_price"],
        },
      ],
      limit: 20,
    });

    const productResponse = await Promise.all(
      sellersCompanyProducts.map(async (product) => {
        const itemPoStock = await checkPOItemStock(product.id);
        let pName = product.menu;

        if (product && product.parent_id) {
          pName = `${product.parent.menu} - ${product.menu}`;
        }
        return {
          id: product.id,
          menu: pName,
          price: product.price,
          discounted_price: product.discounted_price,
          stocks: itemPoStock,
        };
      }),
    );

    res.status(200).json({
      success: true,
      results: productResponse,
      count: productResponse.length,
    });
  } catch (err) {
    next(err);
  }
};

exports.inventoryTransfer = async (req, res, next) => {
  const t = await sequelizeConnect.transaction();
  try {
    const { sourceSellerId, targetSellerId, items = [], adminToken } = req.body;

    if (items && Array.isArray(items) && items.length > 0) {
      const inventoryTransfer = await InventoryTransfer.create(
        {
          source_seller_id: sourceSellerId,
          target_seller_id: targetSellerId,
          approval_status: "Pending",
          created_by: adminToken,
        },
        { transaction: t },
      );

      for (const item of items) {
        const { sourceProductId, targetProductId, quantity } = item;

        const sourceProductPromise = Products.findOne({
          where: {
            id: sourceProductId,
            seller_id: sourceSellerId,
          },
          transaction: t,
        });

        const targetProductPromise = Products.findOne({
          where: {
            id: targetProductId,
            seller_id: targetSellerId,
          },
          transaction: t,
        });

        const [sourceProduct, targetProduct] = await Promise.all([
          sourceProductPromise,
          targetProductPromise,
        ]);

        if (!sourceProduct || !targetProduct) {
          await t.rollback();
          return res
            .status(400)
            .json({ success: false, message: "Invalid product(s)" });
        }

        const sourceProductStock = await checkPOItemStock(sourceProduct.id);
        if (quantity > sourceProductStock.availableStock) {
          await t.rollback();
          return res
            .status(400)
            .json({ success: false, message: "Insufficient stock" });
        }

        await InventoryTransferDetails.create(
          {
            inventory_transfer_id: inventoryTransfer.id,
            source_product_id: sourceProduct.id,
            target_product_id: targetProduct.id,
            quantity: quantity,
          },
          { transaction: t },
        );
      }

      const result = await InventoryTransfer.findOne({
        where: {
          id: inventoryTransfer.id,
        },
        include: [
          {
            model: InventoryTransferDetails,
            as: "inventory_transfer_details",
            include: [
              {
                model: Products,
                as: "source_product",
                attributes: ["id", "menu"],
              },
              {
                model: Products,
                as: "target_product",
                attributes: ["id", "menu"],
              },
            ],
          },
          {
            model: Products,
            as: "source_product",
            attributes: ["id", "menu"],
          },
          {
            model: Products,
            as: "target_product",
            attributes: ["id", "menu"],
          },
          {
            model: SellersCompany,
            as: "source_seller",
            attributes: ["id", "company_name"],
          },
          {
            model: SellersCompany,
            as: "target_seller",
            attributes: ["id", "company_name"],
          },
        ],
        transaction: t,
      });

      await t.commit();
      return res.status(200).json({ success: true, result });
    }

    await t.rollback();
    return res
      .status(400)
      .json({ success: false, message: "No items provided" });
  } catch (err) {
    await t.rollback();
    next(err);
  }
};

exports.inventoryTransferLists = async (req, res, next) => {
  try {
    const {
      adminToken,
      filter = "Pending",
      limit = 20,
      transactionType = null,
      recordId = null,
      sourceSellerId = null,
      targetSellerId = null,
    } = req.query;

    const additionalFilters = {
      warehouse_transferred_in_status: null,
      warehouse_transferred_out_status: null,
      id: null,
      source_seller_id: null,
      target_seller_id: null,
    };

    if (transactionType === "dispatch") {
      additionalFilters.warehouse_transferred_out_status = true;
      additionalFilters.warehouse_transferred_in_status = false;
    }

    if (transactionType === "receive") {
      additionalFilters.warehouse_transferred_out_status = true;
      additionalFilters.warehouse_transferred_in_status = true;
    }

    if (recordId) additionalFilters.id = recordId;
    if (sourceSellerId) additionalFilters.source_seller_id = sourceSellerId;
    if (targetSellerId) additionalFilters.target_seller_id = targetSellerId;

    Object.keys(additionalFilters).forEach(
      (key) =>
        (additionalFilters[key] === null ||
          additionalFilters[key] === "" ||
          additionalFilters[key] === undefined) &&
        delete additionalFilters[key],
    );

    const parsedLimit = parseInt(limit);
    const rawInventoryTransferLists = await InventoryTransfer.findAll({
      where: {
        approval_status: filter,
        ...additionalFilters,
      },
      include: [
        {
          model: InventoryTransferDetails,
          as: "inventory_transfer_details",
          include: [
            {
              model: Products,
              as: "source_product",
              attributes: ["id", "menu", "photo", "seller_dashboard_src"],
              include: [
                {
                  model: ProductImages,
                  as: "product_images",
                },
              ],
            },
            {
              model: Products,
              as: "target_product",
              attributes: ["id", "menu", "photo", "seller_dashboard_src"],
              include: [
                {
                  model: ProductImages,
                  as: "product_images",
                },
              ],
            },
          ],
        },
        {
          model: Products,
          as: "source_product",
          attributes: ["id", "menu", "photo", "seller_dashboard_src"],
          include: [
            {
              model: ProductImages,
              as: "product_images",
            },
          ],
        },
        {
          model: Products,
          as: "target_product",
          attributes: ["id", "menu", "photo", "seller_dashboard_src"],
          include: [
            {
              model: ProductImages,
              as: "product_images",
            },
          ],
        },
        {
          model: SellersCompany,
          as: "source_seller",
          attributes: ["id", "company_name"],
        },
        {
          model: SellersCompany,
          as: "target_seller",
          attributes: ["id", "company_name"],
        },
      ],
      order: [["createdAt", "DESC"]],
      limit: parsedLimit,
    });
    const inventoryTransferLists = rawInventoryTransferLists.map((item) =>
      item.get({ plain: true }),
    );

    for (const item of inventoryTransferLists) {
      if (item.source_product) {
        const sourceProductPhoto = item.source_product.photo;
        const sourceProductSrc = item.source_product.seller_dashboard_src;

        if (sourceProductPhoto && sourceProductPhoto !== "") {
          item.source_product.photo = sellerProductImageBuilder(
            sourceProductPhoto,
            sourceProductSrc,
          );
        } else {
          item.source_product.photo = imageGetter(item.source_product);
        }
        delete item.source_product.product_images;
      }
      if (item.target_product) {
        const targetProductPhoto = item.target_product.photo;
        const targetProductSrc = item.target_product.seller_dashboard_src;

        if (targetProductPhoto && targetProductPhoto !== "") {
          item.target_product.photo = sellerProductImageBuilder(
            targetProductPhoto,
            targetProductSrc,
          );
        } else {
          item.target_product.photo = imageGetter(item.target_product);
        }

        delete item.target_product.product_images;
      }

      for (const detail of item.inventory_transfer_details) {
        if (detail.source_product) {
          const sourceDetailPhoto = detail.source_product.photo;
          const sourceDetailSrc = detail.source_product.seller_dashboard_src;
          if (sourceDetailPhoto && sourceDetailPhoto !== "") {
            detail.source_product.photo = sellerProductImageBuilder(
              sourceDetailPhoto,
              sourceDetailSrc,
            );
          } else {
            detail.source_product.photo = imageGetter(detail.source_product);
          }
          delete detail.source_product.product_images;
        }

        if (detail.target_product) {
          const targetDetailPhoto = detail.target_product.photo;
          const targetDetailSrc = detail.target_product.seller_dashboard_src;
          if (targetDetailPhoto && targetDetailPhoto !== "") {
            detail.target_product.photo = sellerProductImageBuilder(
              targetDetailPhoto,
              targetDetailSrc,
            );
          } else {
            detail.target_product.photo = imageGetter(detail.target_product);
          }
          delete detail.target_product.product_images;
        }
      }
    }

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

const imageGetter = (product) => {
  if (product && product.product_images && product.product_images.length > 0) {
    return sellerProductImageBuilder(product.product_images[0].file_name, true);
  }
};

const getInventoryTransferData = async (id) => {
  const result = await InventoryTransfer.findOne({
    where: {
      id: id,
    },
    include: [
      {
        model: InventoryTransferDetails,
        as: "inventory_transfer_details",
        include: [
          {
            model: Products,
            as: "source_product",
            attributes: ["id", "menu"],
          },
          {
            model: Products,
            as: "target_product",
            attributes: ["id", "menu"],
          },
        ],
      },
      {
        model: Products,
        as: "source_product",
        attributes: ["id", "menu"],
      },
      {
        model: Products,
        as: "target_product",
        attributes: ["id", "menu"],
      },
      {
        model: SellersCompany,
        as: "source_seller",
        attributes: ["id", "company_name"],
      },
      {
        model: SellersCompany,
        as: "target_seller",
        attributes: ["id", "company_name"],
      },
    ],
  });

  return result;
};

exports.handleInventoryTransferApproval = async (req, res, next) => {
  try {
    const { id, status, adminToken, adminUserId } = req.body;

    const adminApprover = await PurchaseOrderApprovers.findOne({
      where: {
        user_type: "Admin",
        user_id: adminUserId,
        seller_id: null,
        status: true,
      },
    });

    // if (!adminApprover)
    //   return res.status(200).json({ success: false, message: "No access" });

    const inventoryTransfer = await InventoryTransfer.findOne({
      where: {
        id: id,
        approval_status: "Pending",
      },
    });

    const { availableStock } = await checkPOItemStock(
      inventoryTransfer.source_product_id,
    );

    if (status === "Approved") {
      if (availableStock >= inventoryTransfer.quantity) {
        inventoryTransfer.approval_status = "Approved";
        inventoryTransfer.approved_by = adminUserId;
        await inventoryTransfer.save();

        const result = await getInventoryTransferData(id);
        return res.status(200).json({ success: true, result: result });
      } else {
        return res
          .status(200)
          .json({ success: false, message: "Insufficient stock to transfer" });
      }
    } else {
      inventoryTransfer.approval_status = "Reject";
      inventoryTransfer.approved_by = adminUserId;
      await inventoryTransfer.save();
      const result = await getInventoryTransferData(id);
      return res.status(200).json({ success: true, result: result });
    }
  } catch (err) {
    next(err);
  }
};

exports.inventoryReport = async (req, res, next) => {
  try {
    const {
      productId,
      sellerId,
      startDate,
      endDate,
      categoryId = null,
    } = req.body;

    if (!sellerId)
      return res
        .status(200)
        .json({ success: false, message: "Seller is required" });
    if (!startDate || !endDate)
      return res
        .status(200)
        .json({ success: false, message: "Date range filter is required" });

    const isEnabledPO = await sellerEnabledPurchaseOrders(sellerId);

    if (!isEnabledPO)
      return res
        .status(200)
        .json({ success: false, message: "Seller has disabled PO feature." });

    const _whereClause = {
      seller_id: sellerId,
      product_status: true,
    };

    if (categoryId && categoryId !== "") {
      _whereClause["menu_id"] = categoryId;
    }

    const sellerProducts = await Products.findAll({
      where: _whereClause,
    });

    const report = await Promise.all(
      sellerProducts.map((p) =>
        getInventoryReport(p.id, sellerId, startDate, endDate),
      ),
    );

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

exports.bstOrderFormSearch = async (req, res, next) => {
  try {
    const { adminToken, adminUserId, query } = req.body;

    const whereClause = {};
    if (query && query.trim() !== "") {
      whereClause.reference_no = {
        [Op.and]: [
          { [Op.like]: `%${query}%` },
          { [Op.notLike]: "%FORKED-BO%" },
        ],
      };
    } else {
      whereClause.reference_no = {
        [Op.notLike]: "%FORKED-BO%",
      };
    }

    const orderForm = await OrderForm.findAll({
      where: whereClause,
      include: [
        {
          model: OrderFormDetails,
          as: "order_form_details",
          attributes: ["id", "quantity", "discounted_price", "price", "amount"],
          include: [
            {
              model: Products,
              as: "product",
              attributes: ["id", "menu", "price", "discounted_price"],
            },
          ],
        },
      ],
      attributes: ["id", "reference_no", "total_amount", "name"],
      order: [["id", "DESC"]],
      limit: 20,
    });

    const mappedOrderForm = orderForm.map((o) => {
      const mappedOrderFormDetails = (o.order_form_details || []).map((ofd) => {
        const orderItemPrice = ofd.price ? parseFloat(ofd.price) : 0;
        const orderItemDiscountedPrice = ofd.discounted_price
          ? parseFloat(ofd.discounted_price)
          : 0;
        const unitPrice = orderItemDiscountedPrice || orderItemPrice;

        return {
          id: ofd.id,
          quantity: ofd.quantity ? parseFloat(ofd.quantity) : 0,
          price: unitPrice,
          amount: ofd.amount ? parseFloat(ofd.amount) : 0,
          product: ofd.product
            ? {
                id: ofd.product.id,
                name: ofd.product.menu,
                price: ofd.product.price ? parseFloat(ofd.product.price) : 0,
                discountedPrice: ofd.product.discounted_price
                  ? parseFloat(ofd.product.discounted_price)
                  : 0,
              }
            : null,
        };
      });

      return {
        id: o.id,
        name: o.name,
        reference_no: o.reference_no,
        total_amount: o.total_amount ? parseFloat(o.total_amount) : 0,
        order_details: mappedOrderFormDetails,
      };
    });

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

exports.bstDoTransfer = async (req, res, next) => {
  const t = await sequelizeConnect.transaction();
  try {
    const {
      transferQuantity,
      transferAmount,
      orderFormDetailId,
      productId,
      updatedOrderTotal,
      orderReference,
      adminToken,
      adminUserId,
    } = req.body;

    const orderForm = await OrderForm.findOne(
      {
        where: {
          reference_no: orderReference,
        },
      },
      { transaction: t },
    );

    const orderFormDetails = await OrderFormDetails.findOne(
      {
        where: {
          id: orderFormDetailId,
        },
      },
      { transaction: t },
    );

    if (!orderForm) {
      await t.rollback();
      return res
        .status(200)
        .json({ success: false, message: "Invalid Reference No" });
    }

    if (!orderFormDetails) {
      await t.rollback();
      return res
        .status(200)
        .json({ success: false, message: "Invalid Order Form Detail" });
    }
    // if(!adminToken || !adminUserId) return res.status(200).json({success: false, message: "Not Authorized"})

    const orderFormTotalQty = orderForm.total_qty;
    const updatedOrderFormTotalQty =
      parseFloat(orderFormTotalQty) - parseFloat(transferQuantity);
    orderForm.total_qty = updatedOrderFormTotalQty;
    orderForm.total_amount = updatedOrderTotal;
    await orderForm.save({ transaction: t });

    const orderFormDetailTotal = orderFormDetails.amount;
    const updatedOrderFormDetailTotal =
      parseFloat(orderFormDetailTotal) - parseFloat(transferAmount);

    const orderFormDetailQty = orderFormDetails.quantity;
    const updatedOrderFormDetailQty =
      parseFloat(orderFormDetailQty) - parseFloat(transferQuantity);

    orderFormDetails.amount = updatedOrderFormDetailTotal;
    orderFormDetails.quantity = updatedOrderFormDetailQty;
    await orderFormDetails.save({ transaction: t });

    const sellerCompanyContainer = await SellerBadOrderContainer.findOne(
      {
        where: {
          status: true,
        },
      },
      { transaction: t },
    );

    if (!sellerCompanyContainer) {
      await t.rollback();
      return res
        .status(200)
        .json({ success: false, message: "No Seller Container Found." });
    }

    const createdBadOrderForm = await OrderForm.create(
      {
        customer_id: orderForm.customer_id,
        email: orderForm.email,
        name: orderForm.name,
        contact_number: orderForm.contact_number,
        address: orderForm.address,
        longitude: orderForm.longitude,
        latitude: orderForm.latitude,
        agentCode: orderForm.agentCode || null,
        shipping_type: orderForm.shipping_type,
        pickup_date: orderForm.pickup_date,
        payment_method: "Bad Order",
        status: "Bad Order",
        seller_id: sellerCompanyContainer.seller_id,
        reference_no: `${orderForm.reference_no}-FORKED-BO`,
        total_amount: parseFloat(transferAmount),
      },
      { transaction: t },
    );

    const estItemPrice =
      parseFloat(transferAmount) / parseFloat(transferQuantity);

    const orderFormDetail = await OrderFormDetails.create(
      {
        order_form_id: createdBadOrderForm.id,
        product_id: productId,
        quantity: transferQuantity,
        discounted_price: estItemPrice || orderFormDetails.discounted_price,
        price: estItemPrice || orderFormDetails.price,
        amount: transferAmount,
      },
      { transaction: t },
    );

    // Logs
    await AdminExternalActionsLogs.create(
      {
        admin_token: adminToken || "",
        action_reference_no: `REF#: ${createdBadOrderForm.reference_no} - Forked - ${orderForm.reference_no}`,
      },
      { transaction: t },
    );

    const orderFormResult = await OrderForm.findOne(
      {
        where: {
          id: createdBadOrderForm.id,
        },
        attributes: [
          "id",
          "reference_no",
          "total_amount",
          "name",
          "seller_id",
          "status",
          "customer_id",
        ],
        include: [
          {
            model: OrderFormDetails,
            as: "order_form_details",
            attributes: [
              "id",
              "quantity",
              "discounted_price",
              "price",
              "amount",
            ],
            include: [
              {
                model: Products,
                as: "product",
                attributes: ["id", "menu", "price", "discounted_price"],
              },
            ],
          },
        ],
      },
      { transaction: t },
    );

    await t.commit();
    res.status(200).json({
      success: true,
      result: orderFormResult,
    });
  } catch (err) {
    await t.rollback();
    next(err);
  }
};

exports.inventoryReportDetailed = async (req, res, next) => {
  try {
    const {
      productId,
      sellerId,
      startDate,
      endDate,
      categoryId = null,
    } = req.body;

    if (!sellerId)
      return res
        .status(200)
        .json({ success: false, message: "Seller is required" });
    if (!startDate || !endDate)
      return res
        .status(200)
        .json({ success: false, message: "Date range filter is required" });

    const isEnabledPO = await sellerEnabledPurchaseOrders(sellerId);

    if (!isEnabledPO)
      return res
        .status(200)
        .json({ success: false, message: "Seller has disabled PO feature." });

    const _whereClause = {
      id: productId,
      seller_id: sellerId,
      product_status: true,
    };

    const sellerProduct = await Products.findOne({
      where: _whereClause,
    });

    if (!sellerProduct)
      return res
        .status(200)
        .json({ success: false, message: "Product not found" });

    const inventoryReport = await getDetailedInventoryReport(
      productId,
      sellerId,
      startDate,
      endDate,
    );

    res.status(200).json({
      success: true,
      result: {
        product: sellerProduct,
        inventoryReport,
      },
    });
  } catch (err) {
    next(err);
  }
};

exports.inventoryReportDetailedAll = async (req, res, next) => {
  try {
    const { sellerId, startDate, endDate, categoryId = null } = req.body;

    if (!sellerId)
      return res
        .status(200)
        .json({ success: false, message: "Seller is required" });
    if (!startDate || !endDate)
      return res
        .status(200)
        .json({ success: false, message: "Date range filter is required" });

    const isEnabledPO = await sellerEnabledPurchaseOrders(sellerId);

    if (!isEnabledPO)
      return res
        .status(200)
        .json({ success: false, message: "Seller has disabled PO feature." });

    const _whereClause = {
      seller_id: sellerId,
      product_status: true,
      menu_id: categoryId ? categoryId : { [Op.ne]: null },
    };

    const sellerProduct = await Products.findAll({
      where: _whereClause,
    });

    const inventoryReportAllPromises = [];
    for (const product of sellerProduct) {
      inventoryReportAllPromises.push(
        getDetailedInventoryReport(product.id, sellerId, startDate, endDate),
      );
    }
    const inventoryReport = await Promise.all(inventoryReportAllPromises);

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

exports.getAvailableTaxCodes = async (req, res, next) => {
  try {
    const taxCodes = await TaxCodes.findAll({
      where: { status: true },
      attributes: ["id", "name", "rate"],
      order: [["name", "ASC"]],
    });
    res.status(200).json({
      success: true,
      result: taxCodes,
    });
  } catch (err) {
    next(err);
  }
};

exports.generatePOQRCode = async (req, res, next) => {
  res.status(200).json({ success: true, message: "Not implemented yet." });
};

exports.verifyRR = async (req, res, next) => {
  try {
    const { id, referenceNo, type } = req.body;

    const purchaseReceive = await PurchaseReceives.findOne({
      where: {
        id,
        receive_reference_no: referenceNo,
        status: true,
        verified_received: false,
      },
    });

    if (!purchaseReceive) return res.status(200).json({ success: false });

    purchaseReceive.verified_received = true;
    await purchaseReceive.save();

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

const normalizeText = (text = "") => {
  return text
    .toString()
    .toLowerCase()
    .replace(/[^\w\s]/g, " ")
    .replace(/\s+/g, " ")
    .trim();
};

const getWords = (text = "") => {
  return normalizeText(text)
    .split(" ")
    .filter((word) => word.length > 1);
};

const getJaccardSimilarity = (a = "", b = "") => {
  const setA = new Set(getWords(a));
  const setB = new Set(getWords(b));

  if (setA.size === 0 || setB.size === 0) return 0;

  const intersection = [...setA].filter((word) => setB.has(word)).length;
  const union = new Set([...setA, ...setB]).size;

  return union === 0 ? 0 : intersection / union;
};

const calculateMatchScore = (sourceProduct, candidate) => {
  let score = 0;

  if (
    sourceProduct.menu_id &&
    candidate.menu_id &&
    Number(sourceProduct.menu_id) === Number(candidate.menu_id)
  ) {
    score += 100;
  }

  if (
    sourceProduct.secondary_id &&
    candidate.secondary_id &&
    Number(sourceProduct.secondary_id) === Number(candidate.secondary_id)
  ) {
    score += 80;
  }

  if (
    sourceProduct.thirtiary_id &&
    candidate.thirtiary_id &&
    Number(sourceProduct.thirtiary_id) === Number(candidate.thirtiary_id)
  ) {
    score += 80;
  }

  if (
    sourceProduct.type_id &&
    candidate.type_id &&
    Number(sourceProduct.type_id) === Number(candidate.type_id)
  ) {
    score += 30;
  }

  if (
    sourceProduct.product_variation_id &&
    candidate.product_variation_id &&
    Number(sourceProduct.product_variation_id) ===
      Number(candidate.product_variation_id)
  ) {
    score += 40;
  }

  if (
    sourceProduct.product_sub_variation_id &&
    candidate.product_sub_variation_id &&
    Number(sourceProduct.product_sub_variation_id) ===
      Number(candidate.product_sub_variation_id)
  ) {
    score += 40;
  }

  if (
    sourceProduct.sku &&
    candidate.sku &&
    normalizeText(sourceProduct.sku) === normalizeText(candidate.sku)
  ) {
    score += 90;
  }

  if (
    sourceProduct.menu &&
    candidate.menu &&
    normalizeText(sourceProduct.menu) === normalizeText(candidate.menu)
  ) {
    score += 70;
  }

  if (
    sourceProduct.variant &&
    candidate.variant &&
    normalizeText(sourceProduct.variant) === normalizeText(candidate.variant)
  ) {
    score += 30;
  }

  const sourceText = [
    sourceProduct.menu,
    sourceProduct.variant,
    sourceProduct.description,
  ]
    .filter(Boolean)
    .join(" ");

  const candidateText = [
    candidate.menu,
    candidate.variant,
    candidate.description,
  ]
    .filter(Boolean)
    .join(" ");

  const similarity = getJaccardSimilarity(sourceText, candidateText);

  if (similarity >= 0.8) {
    score += 60;
  } else if (similarity >= 0.5) {
    score += 35;
  } else if (similarity >= 0.3) {
    score += 15;
  }

  return score;
};

const formatProductResponse = async (product) => {
  const itemPoStock = await checkPOItemStock(product.id);
  let pName = product.menu;

  if (product && product.parent_id && product.parent) {
    pName = `${product.parent.menu} - ${product.menu}`;
  }

  return {
    id: product.id,
    menu: pName,
    price: product.price,
    discounted_price: product.discounted_price,
    seller_id: product.seller_id,
    stocks: itemPoStock,
  };
};

exports.getSuggestedMatchingProduct = async (req, res, next) => {
  try {
    const { product_id, target_seller_id } = req.query;

    if (!product_id || !target_seller_id) {
      return res.status(400).json({
        success: false,
        message: "product_id and target_seller_id are required",
      });
    }

    const sourceProduct = await Products.findOne({
      where: {
        id: product_id,
        product_status: true,
      },
      include: [
        {
          model: Products,
          as: "parent",
          required: false,
        },
      ],
    });

    if (!sourceProduct) {
      return res.status(404).json({
        success: false,
        message: "Source product not found",
      });
    }

    const source = sourceProduct.toJSON();

    const orConditions = [];

    if (source.menu_id) {
      orConditions.push({ menu_id: source.menu_id });
    }

    if (source.secondary_id && Number(source.secondary_id) !== 0) {
      orConditions.push({ secondary_id: source.secondary_id });
    }

    if (source.thirtiary_id && Number(source.thirtiary_id) !== 0) {
      orConditions.push({ thirtiary_id: source.thirtiary_id });
    }

    if (source.sku) {
      orConditions.push({
        sku: { [Op.like]: source.sku },
      });
    }

    if (source.menu) {
      const menuWords = getWords(source.menu);
      for (const word of menuWords.slice(0, 5)) {
        orConditions.push({
          menu: { [Op.like]: `%${word}%` },
        });
      }
    }

    if (source.variant) {
      const variantWords = getWords(source.variant);
      for (const word of variantWords.slice(0, 3)) {
        orConditions.push({
          variant: { [Op.like]: `%${word}%` },
        });
      }
    }

    const candidateWhere = {
      seller_id: target_seller_id,
      product_status: true,
      id: { [Op.ne]: product_id },
    };

    if (orConditions.length > 0) {
      candidateWhere[Op.or] = orConditions;
    }

    let candidates = await Products.findAll({
      where: candidateWhere,
      include: [
        {
          model: Products,
          as: "parent",
          required: false,
        },
      ],
      limit: 50,
    });

    if (!candidates.length) {
      candidates = [];
    }

    const scoredCandidates = candidates
      .map((candidate) => {
        const candidateJson = candidate.toJSON();
        const score = calculateMatchScore(source, candidateJson);

        return {
          product: candidate,
          match_score: score,
        };
      })
      .filter((item) => item.match_score > 0)
      .sort((a, b) => b.match_score - a.match_score);

    const bestMatchRaw = scoredCandidates[0]?.product || null;
    const alternativesRaw = scoredCandidates
      .slice(1, 5)
      .map((item) => item.product);

    const suggested_match = bestMatchRaw
      ? await formatProductResponse(bestMatchRaw)
      : null;

    const alternatives = await Promise.all(
      alternativesRaw.map((product) => formatProductResponse(product)),
    );

    return res.status(200).json({
      success: true,
      suggested_match,
      alternatives,
    });
  } catch (err) {
    next(err);
  }
};
