Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.order;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

import com.spice.profitmandi.common.model.BulkOrderModel;

import com.spice.profitmandi.common.model.LineItemModel;

import com.spice.profitmandi.common.model.ProfitMandiConstants;

import com.spice.profitmandi.common.model.TransactionApprovalModel;

import com.spice.profitmandi.common.util.ExcelUtils;

import com.spice.profitmandi.common.util.Utils;

import com.spice.profitmandi.dao.cart.CartService;

import com.spice.profitmandi.dao.entity.auth.AuthUser;

import com.spice.profitmandi.dao.entity.catalog.Bid;

import com.spice.profitmandi.dao.entity.catalog.Item;

import com.spice.profitmandi.dao.entity.catalog.TagListing;

import com.spice.profitmandi.dao.entity.fofo.FofoStore;

import com.spice.profitmandi.dao.entity.fofo.LoanTransaction;

import com.spice.profitmandi.dao.entity.transaction.LineItem;

import com.spice.profitmandi.dao.entity.transaction.Order;

import com.spice.profitmandi.dao.entity.transaction.Transaction;

import com.spice.profitmandi.dao.entity.transaction.TransactionApproval;

import com.spice.profitmandi.dao.entity.user.StoreTimelinetb;

import com.spice.profitmandi.dao.enumuration.cs.EscalationType;

import com.spice.profitmandi.dao.enumuration.dtr.StoreTimeline;

import com.spice.profitmandi.dao.enumuration.transaction.TransactionApprovalStatus;

import com.spice.profitmandi.dao.model.CartItem;

import com.spice.profitmandi.dao.model.UserCart;

import com.spice.profitmandi.dao.repository.auth.AuthRepository;

import com.spice.profitmandi.dao.repository.catalog.BidRepository;

import com.spice.profitmandi.dao.repository.catalog.ItemRepository;

import com.spice.profitmandi.dao.repository.catalog.LiquidationRepository;

import com.spice.profitmandi.dao.repository.catalog.TagListingRepository;

import com.spice.profitmandi.dao.repository.cs.CsService;

import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;

import com.spice.profitmandi.dao.repository.dtr.UserRepository;

import com.spice.profitmandi.dao.repository.fofo.LoanTransactionRepository;

import com.spice.profitmandi.dao.repository.transaction.OrderRepository;

import com.spice.profitmandi.dao.repository.transaction.SDCreditRequirementRepository;

import com.spice.profitmandi.dao.repository.transaction.TransactionApprovalRepository;

import com.spice.profitmandi.dao.repository.transaction.TransactionRepository;

import com.spice.profitmandi.dao.repository.user.AddressRepository;

import com.spice.profitmandi.dao.service.BidService;

import com.spice.profitmandi.service.catalog.BrandsService;

import com.spice.profitmandi.service.transaction.BlockLoanIdSanctionId;

import com.spice.profitmandi.service.transaction.SDCreditService;

import com.spice.profitmandi.service.transaction.TransactionService;

import com.spice.profitmandi.service.wallet.CommonPaymentService;

import com.spice.profitmandi.service.wallet.WalletService;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.mail.javamail.JavaMailSender;

import org.springframework.stereotype.Service;

import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;

import java.time.LocalDateTime;

import java.util.*;

import java.util.stream.Collectors;

@Service

public class BulkOrderService {

    private static final Logger LOGGER = LogManager.getLogger(BulkOrderService.class);

    @Autowired

    CartService cartService;

    @Autowired

    TransactionService transactionService;

    @Autowired

    CommonPaymentService commonPaymentService;

    @Autowired

    TagListingRepository tagListingRepository;

    @Autowired

    WalletService walletService;

    @Autowired

    UserRepository userRepository;

    @Autowired

    TransactionRepository transactionRepository;

    @Autowired

    TransactionApprovalRepository transactionApprovalRepository;

    @Autowired

    AddressRepository addressRepository;

    @Autowired

    OrderRepository orderRepository;

    //TODO:Tejus need to check

    @Autowired

    SDCreditRequirementRepository sdCreditRequirementRepository;

    @Autowired

    SDCreditService sdCreditService;

    @Autowired

    private CsService csService;

    @Autowired

    private AuthRepository authRepository;

    @Autowired

    private BidRepository bidRepository;

    @Autowired

    private BidService bidService;

    @Autowired

    ItemRepository itemRepository;

    @Autowired

    private BrandsService brandsService;

    @Autowired

    private LoanTransactionRepository loanTransactionRepository;

    @Autowired

    private LiquidationRepository liquidationRepository;

    @Autowired

    com.spice.profitmandi.dao.repository.user.UserRepository user_userRepository;

    @Autowired

    FofoStoreRepository fofoStoreRepository;

    @Autowired

    JavaMailSender gmailRelaySender;

    @Autowired

    com.spice.profitmandi.service.user.StoreTimelineTatService storeTimelineTatService;

    @Autowired

    com.spice.profitmandi.dao.repository.dtr.PartnerOnBoardingPanelRepository partnerOnBoardingPanelRepository;

    @Autowired

    com.spice.profitmandi.dao.repository.user.StoreTimelinetbRepository storeTimelinetbRepository;

    @Autowired

    com.spice.profitmandi.service.catalog.ComboService comboService;

    @Autowired

    com.spice.profitmandi.dao.repository.cs.PositionRepository positionRepository;

    public void parseBulkOrders(MultipartFile file, int creatorId) throws Exception {

        XSSFWorkbook myWorkBook = new XSSFWorkbook(file.getInputStream());

        myWorkBook.setMissingCellPolicy(Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);

        // Return first sheet from the XLSX workbook

        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());

        List<BulkOrderModel> bulkOrderModels = new ArrayList<>();

        LOGGER.info("mySheet.getLastRowNum() - {}", mySheet.getLastRowNum());

        for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {

            XSSFRow row = mySheet.getRow(rowNumber);

            LOGGER.info("Row - {}", row);

            if (row != null) {

                BulkOrderModel bulkOrderModel = this.createBulkModel(row);

                bulkOrderModels.add(bulkOrderModel);

            } else {

                break;

            }

        }

        this.generatePurchaseOrder(bulkOrderModels, creatorId, ProfitMandiConstants.PO_TYPE.MANUAL, ProfitMandiConstants.BID_CRON_ENUM.TODAY);

    }

    public ProfitMandiConstants.BID_ENUM generatePurchaseOrder(List<BulkOrderModel> bulkOrderModels, int creatorId, ProfitMandiConstants.PO_TYPE type, ProfitMandiConstants.BID_CRON_ENUM scheduleType) throws Exception {

        Map<Integer, List<BulkOrderModel>> fofoBulkOrdersMap = bulkOrderModels.stream().collect(Collectors.groupingBy(x -> x.getFofoId()));

        boolean approvalRequired = false;

        ProfitMandiConstants.BID_ENUM finalBidStatus = ProfitMandiConstants.BID_ENUM.CLOSED;

        // Combo enforcement is gated to RBM/SALES creators only (creatorId is constant for the whole file).

        boolean enforceCombo = isRbmOrSalesCreator(creatorId);

        for (Map.Entry<Integer, List<BulkOrderModel>> fofoBulkOrderEntry : fofoBulkOrdersMap.entrySet()) {

            int fofoId = fofoBulkOrderEntry.getKey();

            List<BulkOrderModel> fofoBulkOrderModels = fofoBulkOrderEntry.getValue();

            Map<Integer, Long> orderItemCountMap = fofoBulkOrderModels.stream().collect(Collectors.groupingBy(x -> x.getItemId(), Collectors.counting()));

            if (orderItemCountMap.entrySet().stream().filter(x -> x.getValue() > 1).count() > 0) {

                throw new ProfitMandiBusinessException("Fofo ID", fofoId, "Duplicate in items");

            }

            boolean hasZeroQuantity = fofoBulkOrderModels.stream().filter(x -> x.getQuantity() <= 0).count() > 0;

            if (hasZeroQuantity) {

                throw new ProfitMandiBusinessException("Item Quantity", "", "Should be greater than 0");

            }

            // Batch-fetch per-fofo data once (instead of per-item)

            FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

            List<String> partnerIneligibleBrands = brandsService.partnerIneligibleBrands(fofoId);

            // Batch-fetch items and tag listings for all items in this fofo's order (2 queries instead of 2N)

            Set<Integer> allItemIds = fofoBulkOrderModels.stream().map(BulkOrderModel::getItemId).collect(Collectors.toSet());

            Map<Integer, TagListing> tagListingMap = tagListingRepository.selectByItemIds(allItemIds);

            Map<Integer, Item> itemMap = itemRepository.selectByIds(allItemIds).stream()

                    .collect(Collectors.toMap(Item::getId, item -> item));

            List<CartItem> cartItems = new ArrayList<>();

            double totalPayableAmount = 0;

            BigDecimal totalPayableAmountBD = new BigDecimal(0);

            for (BulkOrderModel fofoBulkOrderModel : fofoBulkOrderModels) {

                CartItem cartItem = new CartItem();

                cartItem.setQuantity(fofoBulkOrderModel.getQuantity());

                cartItem.setItemId(fofoBulkOrderModel.getItemId());

                TagListing tagListing = tagListingMap.get(fofoBulkOrderModel.getItemId());

                if (tagListing == null) {

                    String message = "Pricing Does not exist for " + fofoBulkOrderModel.getItemId() + "(" + fofoBulkOrderModel.getDescription() + ")";

                    throw new ProfitMandiBusinessException(message, message, message);

                }

                Item item = itemMap.get(fofoBulkOrderModel.getItemId());

                if (item == null) {

                    throw new ProfitMandiBusinessException("Item not found", fofoBulkOrderModel.getItemId(), "Item does not exist: " + fofoBulkOrderModel.getItemId());

                }

                if (!fofoStore.isInternal()) {

                    if (partnerIneligibleBrands.contains(item.getBrand())) {

                        throw new ProfitMandiBusinessException("Brand is not allowed", "Brand ( " + item.getBrand() + ") is not allowed for this partner", "");

                    }

                }

                double itemSellingPrice = tagListing.getSellingPrice();

                boolean isActualPrice = fofoBulkOrderModel.getItemPrice() == itemSellingPrice;

                boolean isPriceZero = fofoBulkOrderModel.getItemPrice() == 0d;

                double customSellingPrice = fofoBulkOrderModel.getItemPrice();

                int itemId = cartItem.getItemId();

                if (isPriceZero || isActualPrice) {

                    cartItem.setSellingPrice(itemSellingPrice);

                } else {

                    if (customSellingPrice <= tagListing.getMrp() || customSellingPrice <= tagListing.getMop()) {

                        cartItem.setSellingPrice(customSellingPrice);

                        approvalRequired = true;

                    } else {

                        throw new ProfitMandiBusinessException("Given price is greater than selling price for item Id - ", itemId, " it should be less or equal of DP");

                    }

                }

                totalPayableAmountBD = totalPayableAmountBD.add(new BigDecimal(String.valueOf(cartItem.getSellingPrice())).multiply(new BigDecimal(cartItem.getQuantity())));

                cartItems.add(cartItem);

            }

            totalPayableAmount = totalPayableAmountBD.doubleValue();

            Bid bid = null;

            if (type.equals(ProfitMandiConstants.PO_TYPE.AUTO)) {

                bid = bidRepository.selectById(fofoBulkOrderModels.get(0).getRowIndex());

                approvalRequired = false;

                totalPayableAmount = totalPayableAmountBD.doubleValue() - ProfitMandiConstants.BID_CHARGES;

            }

            LOGGER.info("totalAmount of item " + totalPayableAmount);

            double walletAmount = walletService.getWalletAmount(fofoId);

            BigDecimal creditAvailability = sdCreditService.getAvailableAmount(fofoId);

            double netAmountInHand = creditAvailability.doubleValue() + walletAmount;

            LOGGER.info("netAmountInHand - " + netAmountInHand);

            if (totalPayableAmount > ProfitMandiConstants.MAX_NEGATIVE_WALLET_VALUE && netAmountInHand < totalPayableAmount) {

                if (type.equals(ProfitMandiConstants.PO_TYPE.MANUAL)) {

                    throw new ProfitMandiBusinessException("Skipping order due to insufficient balance for id - ", fofoId, String.valueOf(fofoId));

                } else {

                    if (scheduleType.equals(ProfitMandiConstants.BID_CRON_ENUM.TODAY)) {

                        finalBidStatus = bidService.sendMailToRBM(netAmountInHand, totalPayableAmount, fofoId);

                        LOGGER.info("Skipping order due to insufficient balance for id - "+ fofoId+ " Sending mail to RBM");

                        //throw new ProfitMandiBusinessException("Skipping order due to insufficient balance for id - ", fofoId, " ,Sending mail to RBM");

                    } else {

                        finalBidStatus = bidService.cancelYesterdayProcessBid(bid);

                        LOGGER.info("Skipping order due to insufficient balance for id - "+ fofoId+ " Cancelling the BID");

                        //throw new ProfitMandiBusinessException("Skipping order due to insufficient balance for id - ", fofoId, " ,Cancelling the BID");

                    }

                }

            }

            UserCart userCart = cartService.setCartItems(fofoId, cartItems);

            // RBM/SALES bulk orders must satisfy combo requirements: if a combo main is present without its

            // required sides, validateCombo throws "Missing required Qty for Combo" (restrict-only; never injects a side).

            if (enforceCombo) {

                comboService.validateCombo(userCart);

            }

            // createtransactionInternally set the value in transaction table

            double creditAmountRequired = totalPayableAmount - walletAmount;

            int loanId = 0;

            try {

                if (creditAmountRequired > ProfitMandiConstants.MAX_NEGATIVE_WALLET_VALUE) {

                    LOGGER.info("Creating new loan for: {}",userCart.getUserId());

                    BlockLoanIdSanctionId loan = sdCreditService.createSDDirectOrder(userCart.getUserId(), totalPayableAmount, 0);

                    loanId = loan.getLoanId();

                }

            } catch (Exception exception){

                if (type.equals(ProfitMandiConstants.PO_TYPE.AUTO)) {

                    finalBidStatus = bidService.sendMailToRBM(netAmountInHand, totalPayableAmount, fofoId);

                    LOGGER.info("Skipping order due to insufficient balance for id - "+ fofoId+ " Cancelling the BID");

                    //throw new ProfitMandiBusinessException("Skipping order unable to create load for id - ", fofoId, " ,Sending mail to RBM");

                }

            }

            LOGGER.info("finalBidStatus: {}",finalBidStatus);

            if (finalBidStatus.equals(ProfitMandiConstants.BID_ENUM.CLOSED)) {

                // Block all PO creation while a first PO is pending approval for this partner

                if (transactionApprovalRepository.hasPendingFirstPoByRetailerId(fofoId)) {

                    if (type.equals(ProfitMandiConstants.PO_TYPE.MANUAL)) {

                        throw new ProfitMandiBusinessException(

                                "First PO is pending approval",

                                fofoId,

                                "First PO is already pending approval for this partner. Please wait for approval or rejection before creating another order.");

                    } else {

                        LOGGER.warn("Skipping AUTO PO for fofoId={}: first PO is pending approval", fofoId);

                        continue;

                    }

                }

                // First PO if: no transactions at all, OR all previous first-PO approvals were REJECTED

                boolean isFirstPO = !transactionRepository.hasTransactionsByRetailerId(fofoId)

                        || transactionApprovalRepository.allFirstPoRejectedByRetailerId(fofoId);

                if (isFirstPO) {

                    // Block if a first PO is already pending approval

                    if (transactionApprovalRepository.hasPendingFirstPoByRetailerId(fofoId)) {

                        throw new ProfitMandiBusinessException(

                                "First PO is pending approval",

                                fofoId,

                                "First PO is already pending approval. Please wait for approval or rejection before creating another order.");

                    }

                    // Block first PO if FULL_STOCK_PAYMENT is not done (for LOI-flow partners only)

                    if (fofoStore != null && !fofoStore.isInternal() && fofoStore.getCode() != null) {

                        com.spice.profitmandi.dao.entity.fofo.PartnerOnBoardingPanel pob =

                                partnerOnBoardingPanelRepository.selectByCode(fofoStore.getCode());

                        if (pob != null) {

                            StoreTimelinetb fspEntry = storeTimelinetbRepository.selectByOnboardingIdAndEvent(

                                    pob.getId(), StoreTimeline.FULL_STOCK_PAYMENT);

                            if (fspEntry == null) {

                                LOGGER.warn("PO creation blocked for fofoId={}, onboardingId={}: FULL_STOCK_PAYMENT not done", fofoId, pob.getId());

                                throw new ProfitMandiBusinessException(

                                        "Full Stock Payment is required before creating PO",

                                        fofoStore.getCode(),

                                        "Full Stock Payment must be completed before first PO can be created");

                            }

                            // Block first PO until the business agreement e-sign has been verified

                            // (by Legal / Gaurav Sharma) — sits between FULL_STOCK_PAYMENT and PO creation.

                            StoreTimelinetb esignEntry = storeTimelinetbRepository.selectByOnboardingIdAndEvent(

                                    pob.getId(), StoreTimeline.AGREEMENT_ESIGN);

                            if (esignEntry == null) {

                                LOGGER.warn("PO creation blocked for fofoId={}, onboardingId={}: AGREEMENT_ESIGN not done", fofoId, pob.getId());

                                throw new ProfitMandiBusinessException(

                                        "Business agreement e-sign is required before creating PO",

                                        fofoStore.getCode(),

                                        "Business agreement e-sign must be verified before first PO can be created");

                            }

                        }

                    }

                }

                LOGGER.info("totalPayableAmount - {}", totalPayableAmount);

                int transactionId = transactionService.createTransactionInternally(userCart, totalPayableAmount, 0);

                //Set here created by

                Transaction transaction = transactionRepository.selectById(transactionId);

                transaction.setCreatedBy(creatorId);

                LOGGER.info("transaction created by {}", transaction.getCreatedBy());

                commonPaymentService.payThroughWallet(transactionId);

                // First PO always requires approval

                if (isFirstPO) {

                    approvalRequired = true;

                }

                if (approvalRequired) {

                    this.createApproval(transactionId, isFirstPO);

                    if (loanId > 0) {

                        LoanTransaction loanTransaction = new LoanTransaction();

                        loanTransaction.setLoanId(loanId);

                        loanTransaction.setTransactionId(transactionId);

                        loanTransactionRepository.persist(loanTransaction);

                    }

                } else {

                    transactionService.processTransaction(transactionId, loanId);

                }

                // Send approval email and track timeline for first PO

                if (isFirstPO) {

                    try {

                        sendFirstPOApprovalEmail(fofoStore, transactionId, totalPayableAmount, creatorId);

                    } catch (Exception e) {

                        LOGGER.error("Failed to send first PO approval email for fofoId: " + fofoId, e);

                    }

                    // Track PO_CREATION on timeline for first PO (direct call like BILLING — bypasses BLOCKER_MAP

                    // since the PO is actually being created, the timeline must record it)

                    try {

                        if (fofoStore != null && fofoStore.getCode() != null) {

                            com.spice.profitmandi.dao.entity.fofo.PartnerOnBoardingPanel pob =

                                    partnerOnBoardingPanelRepository.selectByCode(fofoStore.getCode());

                            if (pob != null) {

                                storeTimelineTatService.createStoreTimelinetb(pob.getId(), StoreTimeline.PO_CREATION);

                            }

                        }

                    } catch (Exception e) {

                        LOGGER.error("Failed to track PO_CREATION timeline for fofoId: " + fofoId, e);

                    }

                }

            }

        }

        return finalBidStatus;

    }

    /** True if the bulk-order creator holds an RBM or SALES position — the only creators combo enforcement applies to. Fail-open (returns false) if positions can't be resolved. */

    private boolean isRbmOrSalesCreator(int creatorId) {

        try {

            return positionRepository.selectPositionByAuthId(creatorId).stream()

                    .anyMatch(p -> p.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_RBM

                            || p.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_SALES);

        } catch (Exception e) {

            LOGGER.warn("Combo-enforce gate: could not resolve positions for creator {} - {}", creatorId, e.getMessage());

            return false;

        }

    }

    public void createApproval(int transactionId, boolean firstPo) {

        TransactionApproval transactionApproval = new TransactionApproval();

        transactionApproval.setId(transactionId);

        transactionApproval.setStatus(TransactionApprovalStatus.PENDING);

        transactionApproval.setFirstPo(firstPo);

        transactionApprovalRepository.persist(transactionApproval);

    }

    private BulkOrderModel createBulkModel(XSSFRow row) throws ProfitMandiBusinessException {

        BulkOrderModel bulkOrderModel = new BulkOrderModel();

        int i = 0;

        bulkOrderModel.setRowIndex(row.getRowNum());

        try {

            Cell partnerName = row.getCell(i++);

            if (partnerName == null)

                bulkOrderModel.setPartnerName("");

            else

                bulkOrderModel.setPartnerName(partnerName.getStringCellValue().trim());

            Cell description = row.getCell(i++);

            if (description == null)

                bulkOrderModel.setDescription("");

            else

                bulkOrderModel.setDescription(description.getStringCellValue().trim());

            bulkOrderModel.setFofoId((int) row.getCell(i++).getNumericCellValue());

            bulkOrderModel.setItemId((int) row.getCell(i++).getNumericCellValue());

            bulkOrderModel.setItemPrice(row.getCell(i++).getNumericCellValue());

            bulkOrderModel.setQuantity((int) row.getCell(i++).getNumericCellValue());

        } catch (Throwable e) {

            LOGGER.info(e.getCause());

            throw new ProfitMandiBusinessException("Field", "Field at row - " + row.getRowNum() + ", column - " + (i - 1), "Invalid field value at - " + ExcelUtils.toAlphabet(i - 1) + (row.getRowNum() + 1) + ", " + ExcelUtils.getCellValue(row.getCell(i - 1)));

        }

        LOGGER.info(bulkOrderModel);

        return bulkOrderModel;

    }

    // create model for transaction Approval so that finance team see all order and approve

    public List<TransactionApprovalModel> getAllPendingTransactionApproval() throws ProfitMandiBusinessException {

        List<TransactionApproval> transactionApprovals = transactionApprovalRepository.selectAllPending();

        LOGGER.info("list of Approval transaction Id " + transactionApprovals);

        List<TransactionApprovalModel> approvalModelList = new ArrayList<>();

        for (TransactionApproval transactionApproval : transactionApprovals) {

            List<Order> orderList = orderRepository.selectAllByTransactionId(transactionApproval.getId());

            Transaction transaction = transactionRepository.selectById(transactionApproval.getId());

            List<LineItemModel> lineItemModelList = new ArrayList<>();

            // Fetch stock availability for all items in this order

            Map<Integer, List<com.spice.profitmandi.model.WarehouseItemQtyModel>> stockMap = Collections.emptyMap();

            try {

                int retailerId = transaction.getRetailerId();

                FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(retailerId);

                if (fofoStore != null) {

                    List<Integer> itemIds = orderList.stream()

                            .map(o -> o.getLineItem().getItemId())

                            .collect(java.util.stream.Collectors.toList());

                    stockMap = orderRepository.getItemAvailability(fofoStore.getWarehouseId(), itemIds);

                }

            } catch (Exception e) {

                LOGGER.error("Failed to fetch stock availability for transactionId: {}", transactionApproval.getId(), e);

            }

            for (Order order : orderList) {

                LineItem lineItem = order.getLineItem();

                LineItemModel lineItemModel = new LineItemModel();

                lineItemModel.setItemId(lineItem.getItemId());

                lineItemModel.setItemName(lineItem.getItem().getItemDescription());

                lineItemModel.setItemQuantity(lineItem.getQuantity());

                lineItemModel.setSellingPrice(lineItem.getUnitPrice());

                lineItemModel.setDp(tagListingRepository.selectByItemId(lineItem.getItemId()).getSellingPrice());

                // Set available stock (show 0 if negative — negative means over-committed)

                List<com.spice.profitmandi.model.WarehouseItemQtyModel> itemStock = stockMap.get(lineItem.getItemId());

                if (itemStock != null && !itemStock.isEmpty()) {

                    int totalAvailable = itemStock.stream().mapToInt(s -> s.getNetAvailability()).sum();

                    lineItemModel.setAvailableStock(Math.max(0, totalAvailable));

                }

                lineItemModelList.add(lineItemModel);

            }

            AuthUser authUser = authRepository.selectById(transaction.getCreatedBy());

            TransactionApprovalModel transactionApprovalModel = new TransactionApprovalModel();

            String retailerName = " ";

            retailerName = orderList.get(0).getRetailerName();

            transactionApprovalModel.setRetailerName(retailerName);

            if (authUser == null) {

                transactionApprovalModel.setCreatedBy(retailerName);

            } else {

                transactionApprovalModel.setCreatedBy(authUser.getFullName());

            }

            transactionApprovalModel.setCreatedOn(transaction.getCreateTimestamp());

            transactionApprovalModel.setTransactionId(transactionApproval.getId());

            transactionApprovalModel.setLineItemModels(lineItemModelList);

            transactionApprovalModel.setFirstPo(transactionApproval.isFirstPo());

            approvalModelList.add(transactionApprovalModel);

        }

        return approvalModelList;

    }

    public List<TransactionApprovalModel> getBulkOrderApprovalReport(LocalDateTime startDate, LocalDateTime endDate) throws ProfitMandiBusinessException {

        List<TransactionApproval> transactionApprovals = transactionApprovalRepository.selectAllByDateRange(startDate, endDate);

        LOGGER.info("Approval report: found {} records", transactionApprovals.size());

        List<TransactionApprovalModel> approvalModelList = new ArrayList<>();

        for (TransactionApproval transactionApproval : transactionApprovals) {

            List<Order> orderList = orderRepository.selectAllByTransactionId(transactionApproval.getId());

            Transaction transaction = transactionRepository.selectById(transactionApproval.getId());

            List<LineItemModel> lineItemModelList = new ArrayList<>();

            for (Order order : orderList) {

                LineItem lineItem = order.getLineItem();

                LineItemModel lineItemModel = new LineItemModel();

                lineItemModel.setItemId(lineItem.getItemId());

                lineItemModel.setItemName(lineItem.getItem().getItemDescription());

                lineItemModel.setItemQuantity(lineItem.getQuantity());

                lineItemModel.setSellingPrice(lineItem.getUnitPrice());

                lineItemModel.setDp(tagListingRepository.selectByItemId(lineItem.getItemId()).getSellingPrice());

                lineItemModelList.add(lineItemModel);

            }

            AuthUser authUser = authRepository.selectById(transaction.getCreatedBy());

            TransactionApprovalModel model = new TransactionApprovalModel();

            String retailerName = orderList.isEmpty() ? "" : orderList.get(0).getRetailerName();

            model.setRetailerName(retailerName);

            if (authUser == null) {

                model.setCreatedBy(retailerName);

            } else {

                model.setCreatedBy(authUser.getFullName());

            }

            model.setCreatedOn(transaction.getCreateTimestamp());

            model.setTransactionId(transactionApproval.getId());

            model.setLineItemModels(lineItemModelList);

            model.setStatus(transactionApproval.getStatus().name());

            model.setApprovedBy(transactionApproval.getApprovedBy());

            model.setApprovedOn(transactionApproval.getApprovedOn());

            model.setRemark(transactionApproval.getRemark());

            model.setFirstPo(transactionApproval.isFirstPo());

            approvalModelList.add(model);

        }

        return approvalModelList;

    }

    private void sendFirstPOApprovalEmail(FofoStore fofoStore, int transactionId, double totalAmount, int creatorId) throws Exception {

        String partnerName = "Partner";

        if (fofoStore.getUserAddress() != null && fofoStore.getUserAddress().getName() != null) {

            partnerName = fofoStore.getUserAddress().getName();

        }

        String storeCode = fofoStore.getCode() != null ? fofoStore.getCode() : "";

        String createdByName = "";

        if (creatorId > 0) {

            AuthUser creator = authRepository.selectById(creatorId);

            if (creator != null) {

                createdByName = creator.getFullName();

            }

        }

        String subject = "First PO Created - Approval Required - " + partnerName + " (" + storeCode + ")";

        StringBuilder sb = new StringBuilder();

        sb.append("<html><body>");

        sb.append("<p>Dear Team,</p>");

        sb.append("<p>The <strong>first Purchase Order</strong> has been created for the below partner. Please review and approve.</p><br/>");

        sb.append("<table style='border:1px solid black; border-collapse: collapse;'>");

        sb.append("<tbody>");

        sb.append("<tr>");

        sb.append("<th style='border:1px solid black; padding: 5px;'>Partner Name</th>");

        sb.append("<th style='border:1px solid black; padding: 5px;'>Store Code</th>");

        sb.append("<th style='border:1px solid black; padding: 5px;'>Transaction ID</th>");

        sb.append("<th style='border:1px solid black; padding: 5px;'>Total Amount</th>");

        sb.append("<th style='border:1px solid black; padding: 5px;'>Created By</th>");

        sb.append("</tr>");

        sb.append("<tr>");

        sb.append("<td style='border:1px solid black; padding: 5px;'>").append(partnerName).append("</td>");

        sb.append("<td style='border:1px solid black; padding: 5px;'>").append(storeCode).append("</td>");

        sb.append("<td style='border:1px solid black; padding: 5px;'>").append(transactionId).append("</td>");

        sb.append("<td style='border:1px solid black; padding: 5px;'>").append(String.format("%.2f", totalAmount)).append("</td>");

        sb.append("<td style='border:1px solid black; padding: 5px;'>").append(createdByName).append("</td>");

        sb.append("</tr>");

        sb.append("</tbody></table>");

        sb.append("<br/><p>Please approve this order from the <strong>Transaction Approvals</strong> panel.</p>");

        sb.append("<br/><p>Regards,<br/>Smart Dukaan</p>");

        sb.append("</body></html>");

        // Send to Sales L3

        List<AuthUser> salesL3Users = csService.getAuthUserByCategoryId(

                ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L4);

        List<String> sendTo = new ArrayList<>();

        if (!salesL3Users.isEmpty()) {

            sendTo.addAll(salesL3Users.stream().map(AuthUser::getEmailId).collect(Collectors.toList()));

        }

        if (!sendTo.isEmpty()) {

            String[] emailArray = sendTo.toArray(new String[0]);

            String[] bccArray = {"tarun.verma@smartdukaan.com"};

            Utils.sendMailWithAttachments(gmailRelaySender, emailArray, null, bccArray, subject, sb.toString(), true);

            LOGGER.info("First PO approval email sent for fofoId: {} transactionId: {}", fofoStore.getId(), transactionId);

        }

    }

}