const { SELLER_PURCHASE_ORDERS } = require("../helpers/common");
const SellerControlFeatureFlags = require("../models/SellerControlFeatureFlags");
const SellerFeatureFlags = require("../models/SellerFeatureFlags");
const logger = require("../util/logger");
const PurchaseReceiveDetails = require("../models/PurchaseReceiveDetails");
const { Op, col } = require("sequelize");
const OrderFormDetails = require("../models/OrderFormDetails");
const OrderForm = require("../models/OrderForm");
const sequelizeConect = require("../connection/db");
const InventoryTransfer = require("../models/InventoryTransfer");
const Products = require("../models/Products");
const InventoryTransferDetails = require("../models/InventoryTransferDetails");
const sequelize = require("sequelize");
const PurchaseReceives = require("../models/PurchaseReceives");
const PurchaseOrderDetails = require("../models/PurchaseOrderDetails");
const PurchaseOrders = require("../models/PurchaseOrders");

const sellerEnabledPurchaseOrders = async (sellerId) => {
  try {
    let enabledSellerPO = false;

    const enabledFeatureFlag = await SellerFeatureFlags.findOne({
      where: {
        name: SELLER_PURCHASE_ORDERS,
        status: true,
      },
    });

    if (enabledFeatureFlag) {
      const enabledSellerFeatureFlag = await SellerControlFeatureFlags.findOne({
        where: {
          feature_flag_id: enabledFeatureFlag.id,
          seller_company_id: sellerId,
          enabled: true,
          status: true,
        },
      });

      if (enabledSellerFeatureFlag) {
        enabledSellerPO = true;
      }
    }

    return enabledSellerPO;
  } catch (err) {
    logger.error(`ENABLED SELLER PO ERROR: ${err}`);
    return false;
  }
};

const checkPOItemStock = async (productId) => {
  let transaction;
  try {
    if (!productId) {
      throw new Error("Invalid productId");
    }

    transaction = await sequelizeConect.transaction();

    const productPromise = Products.findOne({
      where: {
        id: productId,
        product_status: true,
      },
      transaction,
    });

    const purchaseReceiveQtyPromise = PurchaseReceiveDetails.sum(
      "quantity_received",
      {
        where: {
          product_id: productId,
          condition: "Good",
          status: true,
          quantity_received: {
            [Op.not]: null,
            [Op.gte]: 0,
          },
        },
        transaction,
      }
    );

    const alreadyOrderedQtyPromise = OrderFormDetails.sum("quantity", {
      where: {
        product_id: productId,
        quantity: {
          [Op.not]: null,
          [Op.gte]: 0,
        },
        po_tracked: true,
      },
      include: [
        {
          model: OrderForm,
          where: {
            status: {
              [Op.in]: [
                "Delivered",
                "Received",
                "For-Delivery Warehouse",
                "For Delivery - Warehouse",
              ],
              [Op.not]: null,
            },
          },
          as: "order_form",
          required: true,
        },
      ],
      transaction,
    });

    const pendingTransferringStocksPromise = InventoryTransfer.sum("quantity", {
      where: {
        source_product_id: productId,
        quantity: {
          [Op.not]: null,
          [Op.gte]: 0,
        },
        approval_status: "Pending",
      },
      transaction,
    });
    const pendingTransferringStocksv2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          source_product_id: productId,
          quantity: {
            [Op.not]: null,
            [Op.gte]: 0,
          },
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            required: true,
            attributes: ["id", "approval_status"],
            where: {
              approval_status: "Pending",
            },
          },
        ],
        transaction,
      }
    );

    const outTransferedStocksPromise = InventoryTransfer.sum("quantity", {
      where: {
        source_product_id: productId,
        quantity: {
          [Op.not]: null,
          [Op.gte]: 0,
        },
        approval_status: "Approved",
      },
      transaction,
    });

    const outTransferedStocksv2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          source_product_id: productId,
          quantity: {
            [Op.not]: null,
            [Op.gte]: 0,
          },
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            required: true,
            attributes: ["id", "approval_status"],
            where: {
              approval_status: "Approved",
            },
          },
        ],
        transaction,
      }
    );

    const inTransferedStocksPromise = InventoryTransfer.sum("quantity", {
      where: {
        target_product_id: productId,
        quantity: {
          [Op.not]: null,
          [Op.gte]: 0,
        },
        approval_status: "Approved",
      },
      transaction,
    });

    const inTransferedStocksv2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          target_product_id: productId,
          quantity: {
            [Op.not]: null,
            [Op.gte]: 0,
          },
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: "Approved",
            },
          },
        ],
        transaction,
      }
    );

    const [
      product,
      purchaseReceiveQty,
      alreadyOrderedQty,
      pendingTransferringStocks,
      pendingTransferringStocksv2,
      outTransferedStocks,
      outTransferedStocksv2,
      inTransferedStocks,
      inTransferedStocksv2,
    ] = await Promise.all([
      productPromise,
      purchaseReceiveQtyPromise,
      alreadyOrderedQtyPromise,
      pendingTransferringStocksPromise,
      pendingTransferringStocksv2Promise,
      outTransferedStocksPromise,
      outTransferedStocksv2Promise,
      inTransferedStocksPromise,
      inTransferedStocksv2Promise,
    ]);

    if (!product) {
      throw new Error("Invalid productId");
    }
    const itemBeginningStockBalance = isNaN(parseFloat(product.stocks))
      ? 0
      : parseFloat(product.stocks);

    const totalReceived = Math.max(0, Number(purchaseReceiveQty) || 0);
    const totalOrdered = Math.max(0, Number(alreadyOrderedQty) || 0);

    const _inTransferedStocks = Math.max(0, Number(inTransferedStocks) || 0);
    const _inTransferedStocks2 = Math.max(0, Number(inTransferedStocksv2) || 0);

    const _outTransferedStocks = Math.max(0, Number(outTransferedStocks) || 0);
    const _outTransferedStocks2 = Math.max(
      0,
      Number(outTransferedStocksv2) || 0
    );
    const _pendingTransferringStocks = Math.max(
      0,
      Number(pendingTransferringStocks) || 0
    );
    const _pendingTransferringStocks2 = Math.max(
      0,
      Number(pendingTransferringStocksv2) || 0
    );

    const _totalReceived =
      totalReceived +
      _inTransferedStocks +
      _inTransferedStocks2 +
      itemBeginningStockBalance;
    const _totalOut =
      totalOrdered +
      _outTransferedStocks +
      _outTransferedStocks2 +
      _pendingTransferringStocks +
      _pendingTransferringStocks2;

    const availableStock = Math.max(0, _totalReceived - _totalOut);
    await transaction.commit();

    return {
      availableStock,
      totalReceived,
      totalOrdered,
      transferIn: _inTransferedStocks,
      transferOut: _outTransferedStocks,
      productId,
    };
  } catch (err) {
    if (transaction) await transaction.rollback();
    console.error("Error in checkPOItemStock:", err);
    return {
      availableStock: 0,
      totalReceived: 0,
      totalOrdered: 0,
      transferIn: 0,
      transferOut: 0,
      error: err.message,
      productId,
    };
  }
};

const getInventoryReport = async (productId, sellerId, startDate, endDate) => {
  let transaction;
  try {
    const startOfDay = new Date(startDate);
    startOfDay.setHours(0, 0, 0, 0);

    const endOfDay = new Date(endDate);
    endOfDay.setHours(23, 59, 59, 999);

    transaction = await sequelizeConect.transaction();

    // -------- Opening Stock (before startDate) --------

    const productPromise = Products.findOne({
      where: {
        id: productId,
        product_status: true,
      },
      transaction,
    });

    const receivedBeforePromise = PurchaseReceiveDetails.sum(
      "quantity_received",
      {
        where: {
          product_id: productId,
          condition: "Good",
          status: true,
          createdAt: { [Op.lt]: startOfDay },
        },
        transaction,
      }
    );

    const orderedBeforePromise = OrderFormDetails.sum("quantity", {
      where: {
        product_id: productId,
        po_tracked: true,
      },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            seller_id: sellerId,
            status: {
              [Op.in]: [
                "Delivered",
                "Received",
                "For-Delivery Warehouse",
                "For Delivery - Warehouse",
              ],
            },
            date_created: { [Op.lt]: startOfDay },
          },
        },
      ],
      transaction,
    });

    const transferInBeforePromise = InventoryTransfer.sum("quantity", {
      where: {
        target_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.lt]: startOfDay },
      },
      transaction,
    });

    const transferInBeforev2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          target_product_id: productId,
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: "Approved",
              createdAt: { [Op.lt]: startOfDay },
            },
          },
        ],
        transaction,
      }
    );

    const transferOutBeforePromise = InventoryTransfer.sum("quantity", {
      where: {
        source_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.lt]: startOfDay },
      },
      transaction,
    });

    const transferOutBeforev2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          source_product_id: productId,
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: "Approved",
              createdAt: { [Op.lt]: startOfDay },
            },
          },
        ],
        transaction,
      }
    );

    // -------- Stock In (during date range) --------
    const stockInPromise = Promise.all([
      PurchaseReceiveDetails.sum("quantity_received", {
        where: {
          product_id: productId,
          condition: "Good",
          status: true,
          createdAt: { [Op.between]: [startOfDay, endOfDay] },
        },
        transaction,
      }),
      InventoryTransfer.sum("quantity", {
        where: {
          target_product_id: productId,
          approval_status: "Approved",
          createdAt: { [Op.between]: [startOfDay, endOfDay] },
        },
        transaction,
      }),
      InventoryTransferDetails.sum("inventory_transfer_details.quantity", {
        where: {
          target_product_id: productId,
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: "Approved",
              createdAt: { [Op.between]: [startOfDay, endOfDay] },
            },
          },
        ],
        transaction,
      }),
    ]).then(([receivedSum, transferSum, transferDetailsSum]) => {
      return (
        (receivedSum || 0) + (transferSum || 0) + (transferDetailsSum || 0)
      );
    });

    // -------- Stock Out (during date range) --------
    const stockOutOrdersPromise = OrderFormDetails.sum("quantity", {
      where: {
        product_id: productId,
        po_tracked: true,
      },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            seller_id: sellerId,
            status: {
              [Op.in]: [
                "Delivered",
                "Received",
                "For-Delivery Warehouse",
                "For Delivery - Warehouse",
              ],
            },
            date_created: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    const stockOutTransfersPromise = InventoryTransfer.sum("quantity", {
      where: {
        source_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.between]: [startOfDay, endOfDay] },
      },
      transaction,
    });

    const stockOutTransfersv2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          source_product_id: productId,
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: { [Op.in]: ["Approved", "Pending"] },
              createdAt: { [Op.between]: [startOfDay, endOfDay] },
            },
          },
        ],
        transaction,
      }
    );

    const productDetailPromise = Products.findOne({
      where: {
        id: productId,
      },
      attributes: ["id", "parent_id", "menu", "price"],
      include: [
        {
          model: Products,
          as: "parent",
          attributes: ["id", "menu", "price"],
        },
      ],
      transaction,
    });

    // -------- Bad Stock --------
    const badStockPromise = OrderFormDetails.sum("quantity", {
      where: {
        product_id: productId,
      },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            status: { [Op.in]: ["Bad Order"] },
            date_created: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    // -------- Bad Stock --------

    // -------- Bad Stock --------
    const committedStockPromise = OrderFormDetails.sum("quantity", {
      where: {
        product_id: productId,
      },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            status: {
              [Op.in]: [
                "Pending",
                "Release Order - Delivery",
                "Release Order- Customer Pickup",
                "Verified",
                "For Preparation",
                "Release Order - Customer Pickup",
                "For Delivery",
                "Release Order - Pickup",
              ],
            },
            date_created: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    const orderedStockPromise = PurchaseOrderDetails.sum("quantity", {
      where: {
        product_id: productId,
        status: true,
        createdAt: { [Op.between]: [startOfDay, endOfDay] },
      },
      include: [
        {
          model: PurchaseOrders,
          as: "purchase_order",
          required: true,
          where: {
            status: true,
            approval_status: false,
          },
        },
      ],
      transaction,
    });

    const [
      stockIn,
      stockOutOrders,
      stockOutTransfers,
      stockOutTransfersv2,
      productDetail,
      badStock,
      committedStock,
      product,
      receivedBefore,
      orderedBefore,
      transferInBefore,
      transferInBeforev2,
      transferOutBefore,
      transferOutBeforev2,
      orderedStock,
    ] = await Promise.all([
      stockInPromise,
      stockOutOrdersPromise,
      stockOutTransfersPromise,
      stockOutTransfersv2Promise,
      productDetailPromise,
      badStockPromise,
      committedStockPromise,
      productPromise,
      receivedBeforePromise,
      orderedBeforePromise,
      transferInBeforePromise,
      transferInBeforev2Promise,
      transferOutBeforePromise,
      transferOutBeforev2Promise,
      orderedStockPromise,
    ]);

    const itemBeginningStockBalance = isNaN(parseFloat(product.stocks))
      ? 0
      : parseFloat(product.stocks);
    const combinedTransferInBefore =
      (Number(transferInBefore) || 0) + (Number(transferInBeforev2) || 0);

    const combinedTransferOutBefore =
      (Number(transferOutBefore) || 0) + (Number(transferOutBeforev2) || 0);

    const openingStock =
      (Number(itemBeginningStockBalance) || 0) +
      (Number(receivedBefore) || 0) +
      (Number(combinedTransferInBefore) || 0) -
      ((Number(orderedBefore) || 0) + (Number(combinedTransferOutBefore) || 0));

    const stockOut =
      (Number(stockOutOrders) || 0) +
      (Number(stockOutTransfers) || 0) +
      (Number(stockOutTransfersv2) || 0);
    const badStockCount = Number(badStock) || 0;

    const committedStockCount = Number(committedStock) || 0;
    // -------- Bad Stock --------

    // -------- Closing Stock --------
    const closingStock = openingStock + (Number(stockIn) || 0) - stockOut;

    const orderedStockCount = Number(orderedStock) || 0;

    await transaction.commit();

    let pName = productDetail.menu;

    if (productDetail && productDetail.parent_id) {
      pName = `${productDetail.parent.menu} - ${productDetail.menu}`;
    }
    return {
      productId,
      productName: pName,
      productPrice: productDetail.price,
      sellerId,
      openingStock,
      stockIn,
      stockOut,
      closingStock,
      badStock: badStockCount,
      committedStock: committedStockCount,
      orderedStock: orderedStockCount,
    };
  } catch (err) {
    if (transaction) await transaction.rollback();
    console.error("Error in getInventoryReport:", err);
    throw err;
  }
};

const getDetailedInventoryReport = async (
  productId,
  sellerId,
  startDate,
  endDate
) => {
  let transaction;
  try {
    const startOfDay = new Date(startDate);
    startOfDay.setHours(0, 0, 0, 0);

    const endOfDay = new Date(endDate);
    endOfDay.setHours(23, 59, 59, 999);

    transaction = await sequelizeConect.transaction();

    // ---- Opening Stock ----
    const productPromise = Products.findOne({
      where: { id: productId, product_status: true },
      transaction,
    });

    const receivedBeforePromise = PurchaseReceiveDetails.sum(
      "quantity_received",
      {
        where: {
          product_id: productId,
          condition: "Good",
          status: true,
          createdAt: { [Op.lt]: startOfDay },
        },
        transaction,
      }
    );

    const orderedBeforePromise = OrderFormDetails.sum("quantity", {
      where: {
        product_id: productId,
        po_tracked: true,
      },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            seller_id: sellerId,
            status: {
              [Op.in]: [
                "Delivered",
                "Received",
                "For-Delivery Warehouse",
                "For Delivery - Warehouse",
              ],
            },
            date_created: { [Op.lt]: startOfDay },
          },
        },
      ],
      transaction,
    });

    const transferInBeforePromise = InventoryTransfer.sum("quantity", {
      where: {
        target_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.lt]: startOfDay },
      },
      transaction,
    });

    const transferInBeforev2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          target_product_id: productId,
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: "Approved",
              createdAt: { [Op.lt]: startOfDay },
            },
          },
        ],
        transaction,
      }
    );

    const transferOutBeforePromise = InventoryTransfer.sum("quantity", {
      where: {
        source_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.lt]: startOfDay },
      },
      transaction,
    });

    const transferOutBeforev2Promise = InventoryTransferDetails.sum(
      "inventory_transfer_details.quantity",
      {
        where: {
          source_product_id: productId,
        },
        include: [
          {
            model: InventoryTransfer,
            as: "inventory_transfer",
            attributes: ["id", "approval_status"],
            required: true,
            where: {
              approval_status: "Approved",
              createdAt: { [Op.lt]: startOfDay },
            },
          },
        ],
        transaction,
      }
    );

    // Purchases (Stock In)
    const purchasesPromise = PurchaseReceiveDetails.findAll({
      where: {
        product_id: productId,
        condition: "Good",
        status: true,
        createdAt: { [Op.between]: [startOfDay, endOfDay] },
      },
      include: [
        {
          model: PurchaseReceives,
          as: "purchase_receive",
          attributes: ["id", "receive_reference_no"],
        },
      ],
      transaction,
    });

    // Orders (Stock Out)
    const ordersPromise = OrderFormDetails.findAll({
      where: { product_id: productId, po_tracked: true },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            seller_id: sellerId,
            status: {
              [Op.in]: [
                "Delivered",
                "Received",
                "For-Delivery Warehouse",
                "For Delivery - Warehouse",
              ],
            },
            date_created: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    // Transfers In Legacy
    const transfersInLegacyPromise = InventoryTransfer.findAll({
      where: {
        target_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.between]: [startOfDay, endOfDay] },
      },
      include: [
        {
          model: InventoryTransferDetails,
          as: "inventory_transfer_details",
          required: false, // LEFT JOIN
          attributes: [], // don’t need fields, just check existence
        },
      ],
      having: sequelize.where(
        sequelize.fn("COUNT", sequelize.col("inventory_transfer_details.id")),
        0
      ),
      group: ["inventory_transfer.id"],
      transaction,
    });

    // Transfers In V2
    const transfersInPromise = InventoryTransferDetails.findAll({
      where: { target_product_id: productId },
      include: [
        {
          model: InventoryTransfer,
          as: "inventory_transfer",
          required: true,
          where: {
            approval_status: "Approved",
            createdAt: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    // Transfers Out Legacy
    const transfersOutLegacyPromise = InventoryTransfer.findAll({
      where: {
        source_product_id: productId,
        approval_status: "Approved",
        createdAt: { [Op.between]: [startOfDay, endOfDay] },
      },
      include: [
        {
          model: InventoryTransferDetails,
          as: "inventory_transfer_details",
          required: false, // LEFT JOIN
          attributes: [], // don’t need fields, just check existence
        },
      ],
      having: sequelize.where(
        sequelize.fn("COUNT", sequelize.col("inventory_transfer_details.id")),
        0
      ),
      group: ["inventory_transfer.id"],
      transaction,
    });

    // Transfers Out V2
    const transfersOutPromise = InventoryTransferDetails.findAll({
      where: { source_product_id: productId },
      include: [
        {
          model: InventoryTransfer,
          as: "inventory_transfer",
          required: true,
          where: {
            approval_status: "Approved",
            createdAt: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    // Bad Orders
    const badOrdersPromise = OrderFormDetails.findAll({
      where: { product_id: productId },
      include: [
        {
          model: OrderForm,
          as: "order_form",
          required: true,
          where: {
            status: "Bad Order",
            date_created: { [Op.between]: [startOfDay, endOfDay] },
          },
        },
      ],
      transaction,
    });

    const [
      product,
      receivedBefore,
      orderedBefore,
      transferInBefore,
      transferInBeforev2,
      transferOutBefore,
      transferOutBeforev2,
      purchases,
      orders,
      transfersInLegacy,
      transfersIn,
      transfersOutLegacy,
      transfersOut,
      badOrders,
    ] = await Promise.all([
      productPromise,
      receivedBeforePromise,
      orderedBeforePromise,
      transferInBeforePromise,
      transferInBeforev2Promise,
      transferOutBeforePromise,
      transferOutBeforev2Promise,
      purchasesPromise,
      ordersPromise,
      transfersInLegacyPromise,
      transfersInPromise,
      transfersOutLegacyPromise,
      transfersOutPromise,
      badOrdersPromise,
    ]);

    const itemBeginningStockBalance = parseFloat(product.stocks) || 0;

    const combinedTransferInBefore =
      (Number(transferInBefore) || 0) + (Number(transferInBeforev2) || 0);

    const combinedTransferOutBefore =
      (Number(transferOutBefore) || 0) + (Number(transferOutBeforev2) || 0);

    const openingStock =
      (Number(itemBeginningStockBalance) || 0) +
      (Number(receivedBefore) || 0) +
      (Number(combinedTransferInBefore) || 0) -
      ((Number(orderedBefore) || 0) + (Number(combinedTransferOutBefore) || 0));

    // Collect all stock movement records
    let movements = [];

    purchases.forEach((p) => {
      movements.push({
        date: p.createdAt,
        type: "Stock In - Purchase",
        reference: p?.purchase_receive?.receive_reference_no || p.id,
        quantity: p.quantity_received,
        direction: "IN",
      });
    });

    orders.forEach((o) => {
      movements.push({
        date: o.order_form.date_created,
        type: "Stock Out - Order",
        reference: o.order_form.reference_no,
        quantity: o.quantity,
        direction: "OUT",
      });
    });

    transfersInLegacy.forEach((t) => {
      movements.push({
        date: t.createdAt,
        type: "Stock In - Transfer",
        reference: `Inventory Transfer No.: ${t.id}`,
        quantity: t.quantity,
        direction: "IN",
      });
    });

    transfersIn.forEach((t) => {
      movements.push({
        date: t.inventory_transfer.createdAt,
        type: "Stock In - Transfer",
        reference: `Inventory Transfer No.: ${t.inventory_transfer.id}`,
        quantity: t.quantity,
        direction: "IN",
      });
    });

    transfersOutLegacy.forEach((t) => {
      movements.push({
        date: t.createdAt,
        type: "Stock Out - Transfer",
        reference: `Inventory Transfer No.: ${t.id}`,
        quantity: t.quantity,
        direction: "OUT",
      });
    });

    transfersOut.forEach((t) => {
      movements.push({
        date: t.inventory_transfer.createdAt,
        type: "Stock Out - Transfer",
        reference: `Inventory Transfer No.: ${t.inventory_transfer.id}`,
        quantity: t.quantity,
        direction: "OUT",
      });
    });

    badOrders.forEach((b) => {
      movements.push({
        date: b.order_form.date_created,
        type: "Bad Stock",
        reference: b.order_form.reference_no,
        quantity: b.quantity,
        direction: "-",
      });
    });
    // Sort by date
    movements.sort((a, b) => new Date(a.date) - new Date(b.date));

    // Calculate running balance
    let balance = openingStock;
    let detailedLog = [];
    detailedLog.push({
      date: startOfDay,
      type: "Opening Balance",
      reference: null,
      quantity: 0,
      direction: "OPEN",
      balance,
    });

    for (const move of movements) {
      if (move.direction === "IN") {
        balance += Number(move.quantity);
      } else if (move.direction === "OUT") {
        balance -= Number(move.quantity);
      }
      detailedLog.push({
        ...move,
        balance,
      });
    }

    await transaction.commit();

    return {
      productId,
      productName: product.menu,
      openingStock,
      closingStock: balance,
      transactions: detailedLog,
    };
  } catch (err) {
    if (transaction) await transaction.rollback();
    console.error("Error in getDetailedInventoryReport:", err);
    throw err;
  }
};

module.exports = {
  sellerEnabledPurchaseOrders,
  checkPOItemStock,
  getInventoryReport,
  getDetailedInventoryReport,
};
