Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.order;

import com.spice.profitmandi.common.enumuration.ItemType;
import com.spice.profitmandi.common.enumuration.SearchType;
import com.spice.profitmandi.common.enumuration.UpgradeOfferPaymentStatus;
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.*;
import com.spice.profitmandi.common.util.FormattingUtils;
import com.spice.profitmandi.common.util.StringUtils;
import com.spice.profitmandi.common.util.Utils;
import com.spice.profitmandi.common.web.client.RestClient;
import com.spice.profitmandi.dao.cart.SmartCartService;
import com.spice.profitmandi.dao.entity.catalog.Category;
import com.spice.profitmandi.dao.entity.catalog.Item;
import com.spice.profitmandi.dao.entity.catalog.TagListing;
import com.spice.profitmandi.dao.entity.catalog.UpgradeOffer;
import com.spice.profitmandi.dao.entity.dtr.*;
import com.spice.profitmandi.dao.entity.fofo.*;
import com.spice.profitmandi.dao.entity.inventory.State;
import com.spice.profitmandi.dao.entity.transaction.Order;
import com.spice.profitmandi.dao.entity.user.Address;
import com.spice.profitmandi.dao.entity.user.Counter;
import com.spice.profitmandi.dao.entity.user.PrivateDealUser;
import com.spice.profitmandi.dao.entity.warehouse.WarehouseInventoryItem;
import com.spice.profitmandi.dao.enumuration.catalog.SchemeType;
import com.spice.profitmandi.dao.enumuration.dtr.PaymentOptionReferenceType;
import com.spice.profitmandi.dao.enumuration.fofo.ReturnType;
import com.spice.profitmandi.dao.enumuration.fofo.ScanType;
import com.spice.profitmandi.dao.enumuration.fofo.SettlementType;
import com.spice.profitmandi.dao.enumuration.inventory.ScratchedGift;
import com.spice.profitmandi.dao.enumuration.transaction.OrderStatus;
import com.spice.profitmandi.dao.repository.catalog.*;
import com.spice.profitmandi.dao.repository.dtr.*;
import com.spice.profitmandi.dao.repository.fofo.*;
import com.spice.profitmandi.dao.repository.inventory.StateRepository;
import com.spice.profitmandi.dao.repository.transaction.OrderRepository;
import com.spice.profitmandi.dao.repository.user.AddressRepository;
import com.spice.profitmandi.dao.repository.user.CounterRepository;
import com.spice.profitmandi.dao.repository.user.PrivateDealUserRepository;
import com.spice.profitmandi.dao.repository.warehouse.WarehouseInventoryItemRepository;
import com.spice.profitmandi.service.catalog.BrandsService;
import com.spice.profitmandi.service.integrations.bharti.model.PlanVariant;
import com.spice.profitmandi.service.integrations.zest.InsuranceService;
import com.spice.profitmandi.service.integrations.zest.MobileInsurancePlan;
import com.spice.profitmandi.service.PartnerInvestmentService;
import com.spice.profitmandi.service.inventory.InventoryService;
import com.spice.profitmandi.service.inventory.PurchaseReturnService;
import com.spice.profitmandi.service.inventory.SaholicInventoryService;
import com.spice.profitmandi.service.offers.ItemCriteria;
import com.spice.profitmandi.service.pricing.PricingService;
import com.spice.profitmandi.service.scheme.SchemeService;
import com.spice.profitmandi.service.user.RetailerService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Component;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.AbstractMap.SimpleEntry;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Component
public class OrderServiceImpl implements OrderService {

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

    private static Map<String, Integer> serialNumberOrderIdMap = new HashMap<>();

    static {
        serialNumberOrderIdMap.put("862897055749275", 67228);
    }

    @Autowired
    BrandsService brandsService;

    @Autowired
    @Qualifier("fofoInventoryItemRepository")
    private InventoryItemRepository inventoryItemRepository;

    @Autowired
    private StateGstRateRepository stateGstRateRepository;

    @Autowired
    private SaholicInventoryService saholicInventoryService;

    @Autowired
    private LiveDemoBillingRespository liveDemoBillingRespository;

    @Autowired
    private InsuranceService insuranceService;

    @Autowired
    private PartnerInvestmentService partnerInvestmentService;

    @Autowired
    @Qualifier("fofoCurrentInventorySnapshotRepository")
    private CurrentInventorySnapshotRepository currentInventorySnapshotRepository;

    @Autowired
    private InvoiceNumberGenerationSequenceRepository invoiceNumberGenerationSequenceRepository;

    @Autowired
    private PurchaseReturnService purchaseReturnService;

    @Autowired
    private RetailerService retailerService;

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private PurchaseReturnItemRepository purchaseReturnItemRepository;

    @Autowired
    private AddressRepository addressRepository;

    @Autowired
    private FofoLineItemRepository fofoLineItemRepository;

    @Autowired
    private FofoNonSerializeSerialRepository fofoNonSerializeSerialRepository;

    @Autowired
    private WarehouseInventoryItemRepository warehouseInventoryItemRepository;

    @Autowired
    private FofoOrderItemRepository fofoOrderItemRepository;

    @Autowired
    private PaymentOptionRepository paymentOptionRepository;

    @Autowired
    private CustomerReturnItemRepository customerReturnItemRepository;

    @Autowired
    @Qualifier("fofoScanRecordRepository")
    private ScanRecordRepository scanRecordRepository;

    @Autowired
    private FofoOrderRepository fofoOrderRepository;

    @Autowired
    private RetailerRepository retailerRepository;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private UserAccountRepository userAccountRepository;

    @Autowired
    private RetailerRegisteredAddressRepository retailerRegisteredAddressRepository;

    @Autowired
    private CustomerAddressRepository customerAddressRepository;

    @Autowired
    @Qualifier("catalogItemRepository")
    private ItemRepository itemRepository;

    @Autowired
    private InsuranceProviderRepository insuranceProviderRepository;

    @Autowired
    private InsurancePolicyRepository insurancePolicyRepository;

    @Autowired
    private StateRepository stateRepository;

    @Autowired
    private PolicyNumberGenerationSequenceRepository policyNumberGenerationSequenceRepository;

    @Autowired
    private PricingService pricingService;

    @Autowired
    private PrivateDealUserRepository privateDealUserRepository;

    @Autowired
    private TagListingRepository tagListingRepository;

    @Autowired
    private CounterRepository counterRepository;

    @Autowired
    private FofoStoreRepository fofoStoreRepository;

    @Autowired
    private PaymentOptionTransactionRepository paymentOptionTransactionRepository;

    @Autowired
    private SchemeService schemeService;

    private static final List<Integer> orderIdsConsumed = new ArrayList<>();

    @Autowired
    @Qualifier("fofoInventoryService")
    private InventoryService inventoryService;

    @Autowired
    private CustomerCreditNoteRepository customerCreditNoteRepository;

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private CategoryRepository categoryRepository;

    @Autowired
    private HygieneDataRepository hygieneDataRepository;

    @Autowired
    private SessionFactory sessionFactory;

    @Autowired
    private Mongo mongoClient;

    @Autowired
    private PendingOrderRepository pendingOrderRepository;

    @Autowired

    private PendingOrderService pendingOrderService;

    @Autowired
    private PendingOrderItemRepository pendingOrderItemRepository;

    @Autowired
    private ScratchOfferRepository scratchOfferRepository;

    @Autowired
    RestClient restClient;

    @Autowired
    UpSaleOrderRepository upSaleOrderRepository;

    @Autowired
    private CustomerOfferRepository customerOfferRepository;

    @Autowired
    private CustomerOfferItemRepository customerOfferItemRepository;

    @Autowired
    private UpgradeOfferRepository upgradeOfferRepository;

    @Autowired
    private SmartCartService smartCartService;

    @Autowired
    private PartnerTypeChangeService partnerTypeChangeService;

    @Value("${prod}")
    private boolean prodEnv;

    private static final String SMS_GATEWAY = "http://api.pinnacle.in/index.php/sms/send";
    private static final String SENDER = "SMTDKN";

    public static final String APP_DOWNLOAD_BILLING_TEMPLATE_ID = "1507163542403945677";

    public static final String APP_DOWNLOAD_BILLING_OFFER = "Dear Customer, Thank you for purchasing from SmartDukaan pls click %s to download our app to see you invoice and special offers. SmartDukaan";

    static Map<Double, List<ScratchedGift>> GIFT_SERIES = new TreeMap<>(Comparator.reverseOrder());

    // Define eligible partners for LED & Microwave Oven
    private static final Set<Integer> PREMIUM_ELIGIBLE_PARTNERS = new HashSet<>(Arrays.asList(175139615, 175139583));
    private static Map<ScratchedGift, Integer> GIFT_QUANTITIES = new HashMap<>();
    List<Double> PRICE_RANGE = Arrays.asList(0.0, Double.MAX_VALUE);

    static {
        GIFT_QUANTITIES.put(ScratchedGift.ACCESSORIES_50_PERCENT_OFF, 500);
        GIFT_QUANTITIES.put(ScratchedGift.NECK_BAND, 280);
        GIFT_QUANTITIES.put(ScratchedGift.LED, 1);
        GIFT_QUANTITIES.put(ScratchedGift.MICROWAVE_OVEN, 1);
    }

    static {
        GIFT_SERIES.put(0.0, Arrays.asList(ScratchedGift.ACCESSORIES_50_PERCENT_OFF, ScratchedGift.NECK_BAND));
        GIFT_SERIES.put(30001.0, Arrays.asList(ScratchedGift.NECK_BAND, ScratchedGift.MICROWAVE_OVEN, ScratchedGift.LED));
    }

    private void persistNonSerializedWithCustomSerialNumber(CustomFofoOrderItem customFofoOrderItem, int orderItemId) {
        // Create a new instance of FofoNonSerializeSerial
        for (String accSerialNumber : customFofoOrderItem.getCustomSerialNumbers()) {
            if (!accSerialNumber.isEmpty()) {
                FofoNonSerializeSerial nonSerializeSerial = new FofoNonSerializeSerial();

                // Populate the entity with relevant information
                nonSerializeSerial.setOrderItemId(orderItemId);
                nonSerializeSerial.setSerialNumber(accSerialNumber);

                // Save the entity to the database
                fofoNonSerializeSerialRepository.persist(nonSerializeSerial);
            }

        }
    }


    public void sendAppDownloadBillingOffer(String mobileNumber) throws Exception {
        String sdurl = "http://surl.li/anhfn";
        try {
            if (prodEnv) {
                this.sendSms(APP_DOWNLOAD_BILLING_TEMPLATE_ID, String.format(APP_DOWNLOAD_BILLING_OFFER, sdurl), mobileNumber);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void sendSms(String dltTemplateId, String message, String mobileNumber) throws Exception {
        Map<String, String> map = new HashMap<>();

        map.put("sender", SENDER);
        map.put("messagetype", "TXT");
        map.put("apikey", "b866f7-c6c483-682ff5-054420-ad9e2c");

        map.put("numbers", "91" + mobileNumber);
        LOGGER.info("Message {}", message);
        // OTP Message Template
        map.put("message", message);
        map.put("dlttempid", dltTemplateId);

        String response = restClient.post(SMS_GATEWAY, map, new HashMap<>());
        LOGGER.info(response);

    }


    private void createScratchOffer(int fofoId, String invoiceNumber, int customerId) {

        //ScratchedGift gift = getScratchedGiftRandom(fofoId, customerId);


        //  LocalDateTime endDate = LocalDateTime.of(LocalDate.now().getYear(), LocalDate.now().getMonth(), 27, 21, 00);
        List<ScratchOffer> scratchOffers = scratchOfferRepository.selectBycCustomerIdAndDate(customerId, ProfitMandiConstants.SCRATCH_OFFER_START_DATE, ProfitMandiConstants.SCRATCH_OFFER_END_DATE);
        if (scratchOffers.size() == 0) {
            ScratchOffer so2 = new ScratchOffer();
            so2.setInvoiceNumber(invoiceNumber);
            so2.setScratched(false);
            so2.setCreatedTimestamp(LocalDateTime.now());
            so2.setExpiredTimestamp(ProfitMandiConstants.SCRATCH_OFFER_END_DATE.plusDays(1).atTime(LocalTime.MAX));
            so2.setOfferName(String.valueOf(ScratchedGift.BLNT));
            so2.setCustomerId(customerId);

            LocalDateTime today830PM = LocalDate.now().atTime(20, 30);
            LocalDateTime today9PM = LocalDate.now().atTime(21, 0);
            so2.setUnlockedAt(LocalDateTime.now());

//            if (LocalDateTime.now().isAfter(today830PM)) {
//                so2.setUnlockedAt(today9PM.plusDays(0));
//            } else {
//                so2.setUnlockedAt(today9PM);
//            }
            scratchOfferRepository.persist(so2);
        }
    }


    @Override
    public int createOrder(CreateOrderRequest createOrderRequest, int fofoId, boolean accessoriesDeals) throws Exception {
        LOGGER.info("fofoId -- {} Order Request -- {}", fofoId, createOrderRequest);
        CustomCustomer customCustomer = createOrderRequest.getCustomer();
        Customer customer = customerRepository.selectById(customCustomer.getCustomerId());

        if ((createOrderRequest.getCustomer().getGender() != null && createOrderRequest.getCustomer().getGender().equals("2"))) {
            customer.setGender("Female");
        } else {

            customer.setGender("Male");
        }

        if (!StringUtils.isValidGstNumber(customCustomer.getGstNumber())) {
            LOGGER.error("invalid customer gstNumber {} ", customCustomer.getGstNumber());
            throw new ProfitMandiBusinessException(ProfitMandiConstants.CUSTOMER_GST_NUMBER, customCustomer.getGstNumber(), "VE_1072");
        }

        Map<Integer, Integer> itemIdQuantity = new HashMap<>(); // this is for error
        Map<Integer, CustomFofoOrderItem> itemIdCustomFofoOrderItemMap = new HashMap<>();
        Map<Integer, Float> lineItemPrice = new HashMap<>(); // this is for pricing error

        float totalAmount = 0;
        boolean noGST = false;

        // N+1 fix: Batch fetch all PendingOrderItems before the validation loop
        List<Integer> validationPoiIds = createOrderRequest.getFofoOrderItems().stream()
                .map(CustomFofoOrderItem::getPoiId)
                .filter(id -> id > 0)
                .collect(Collectors.toList());
        Map<Integer, PendingOrderItem> pendingOrderItemMap = new HashMap<>();
        if (!validationPoiIds.isEmpty()) {
            List<PendingOrderItem> pendingOrderItems = pendingOrderItemRepository.selectByIds(validationPoiIds);
            pendingOrderItemMap = pendingOrderItems.stream()
                    .collect(Collectors.toMap(PendingOrderItem::getId, poi -> poi));
        }

        for (CustomFofoOrderItem customFofoOrderItem : createOrderRequest.getFofoOrderItems()) {
            if (customFofoOrderItem.getPoiId() > 0) {
                // N+1 fix: Use pre-fetched map instead of querying per item
                PendingOrderItem pendingOrderItem = pendingOrderItemMap.get(customFofoOrderItem.getPoiId());
                if (pendingOrderItem == null) {
                    throw new ProfitMandiBusinessException("poiId", customFofoOrderItem.getPoiId(), "Pending order item not found");
                }
                if (customFofoOrderItem.getQuantity() > pendingOrderItem.getQuantity()) {
                    throw new ProfitMandiBusinessException("itemIdQuantity", customFofoOrderItem.getItemId(), "Quantity should not be greater than order item quantity");
                }
                if (pendingOrderItem.getQuantity() > customFofoOrderItem.getQuantity()) {
                    pendingOrderService.duplicatePendingOrder(pendingOrderItem, customFofoOrderItem.getQuantity());
                }
            }
            // itemIds.add(customFofoOrderItem.getItemId());
            Set<String> serialNumbers = this.serialNumberDetailsToSerialNumbers(customFofoOrderItem.getSerialNumberDetails());
            if (!serialNumbers.isEmpty() && customFofoOrderItem.getQuantity() != serialNumbers.size()) {
                itemIdQuantity.put(customFofoOrderItem.getItemId(), customFofoOrderItem.getQuantity());
            }
            if (!(customFofoOrderItem.getSellingPrice() > 0)) {
                lineItemPrice.put(customFofoOrderItem.getItemId(), customFofoOrderItem.getSellingPrice());
            } else {
                totalAmount = totalAmount + customFofoOrderItem.getSellingPrice() * customFofoOrderItem.getQuantity();
                for (SerialNumberDetail serialNumberDetail : customFofoOrderItem.getSerialNumberDetails()) {
                    if (serialNumberDetail.getAmount() > 0) {
                        totalAmount = totalAmount + serialNumberDetail.getAmount();
                    }
                }
            }

            itemIdCustomFofoOrderItemMap.put(customFofoOrderItem.getItemId(), customFofoOrderItem);
        }
        if (!itemIdQuantity.isEmpty()) {
            // if item quantity does not match with given serialnumbers size
            LOGGER.error("itemId's quantity should be equal to given serialnumber size {} ", itemIdQuantity);
            throw new ProfitMandiBusinessException("itemIdQuantity", itemIdQuantity, "FFORDR_1001");
            // return "error";
        }

        this.validatePaymentOptionsAndTotalAmount(createOrderRequest.getPaymentOptions(), totalAmount);

        if (!lineItemPrice.isEmpty()) {
            // given fofo line item price must be greater than zero
            LOGGER.error("requested itemId's selling price must greater than 0");
            throw new ProfitMandiBusinessException(ProfitMandiConstants.PRICE, lineItemPrice, "FFORDR_1002");
        }

        List<CurrentInventorySnapshot> currentInventorySnapshots = currentInventorySnapshotRepository.selectByFofoItemIds(fofoId, itemIdCustomFofoOrderItemMap.keySet());

        this.validateCurrentInventorySnapshotQuantities(currentInventorySnapshots, itemIdCustomFofoOrderItemMap);

        List<Item> items = itemRepository.selectByIds(itemIdCustomFofoOrderItemMap.keySet());
        if (items.size() != itemIdCustomFofoOrderItemMap.keySet().size()) {
            LOGGER.error("Requested ItemIds not found in catalog");
            // invalid itemIds
            throw new ProfitMandiBusinessException("invalidItemIds", itemIdCustomFofoOrderItemMap.keySet(), "FFORDR_1003");
        }

        Map<Integer, Item> itemMap = this.toItemMap(items);

        FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

        Set<Integer> nonSerializedItemIds = new HashSet<>();
        Set<String> serialNumbers = new HashSet<>();
        List<InsuranceModel> insuredModels = new ArrayList<>();
        noGST = items.stream().anyMatch(item -> "NOGST".equals(item.getHsnCode()));
        for (CustomFofoOrderItem customFofoOrderItem : createOrderRequest.getFofoOrderItems()) {
            Item item = itemMap.get(customFofoOrderItem.getItemId());
            if (item.getType().equals(ItemType.SERIALIZED)) {
                for (SerialNumberDetail serialNumberDetail : customFofoOrderItem.getSerialNumberDetails()) {
                    serialNumbers.add(serialNumberDetail.getSerialNumber());
                    if (serialNumberDetail.getAmount() > 0) {
                        if (customer.getEmailId() == null || customer.getEmailId().equals("")) {
                            throw new ProfitMandiBusinessException("Email Id is required for insurance", "Email Id is required for insurance", "Email Id is required for insurance");
                        }

                        InsuranceModel im = new InsuranceModel();
                        im.setBrand(item.getBrand());
                        im.setColor(item.getColor());
                        im.setModelName(item.getModelName() + item.getModelNumber());
                        im.setInsuranceAmount(serialNumberDetail.getAmount());
                        im.setDeviceSellingPrice(customFofoOrderItem.getSellingPrice());
                        im.setInsuranceUId(serialNumberDetail.getInsurance());
                        im.setCorrelationId(serialNumberDetail.getCorrelationId());

                        PlanVariant oneAssistpremium = insuranceService.getOneAssistPremiumByVariantId(serialNumberDetail.getInsurance());
                        if (oneAssistpremium != null) {
                            im.setInsuranceId(String.valueOf(oneAssistpremium.getId()));
                        } else {
                            im.setInsuranceId(String.valueOf(insuranceService.getICICIPremiumByVariantId(serialNumberDetail.getInsurance()).getId()));
                        }
                        im.setSerialNumber(serialNumberDetail.getSerialNumber());
                        im.setMemory(serialNumberDetail.getMemory());
                        im.setRam(serialNumberDetail.getRam());
                        im.setMfgDate(serialNumberDetail.getMfgDate());
                        insuredModels.add(im);
                        // Check for free insurance code
                        try {
                            Map<String, List<MobileInsurancePlan>> mobileInsurancePlanMap = insuranceService.getAllPlans(item.getId(), im.getDeviceSellingPrice(), false);
                            MobileInsurancePlan mobileInsurancePlan = mobileInsurancePlanMap.entrySet().stream().flatMap(x -> x.getValue().stream()).filter(x -> x.getProductId().equals(serialNumberDetail.getInsurance())).findFirst().get();
/*                            if (mobileInsurancePlan.getPlanName().equals("OneAssist Damage Protection Plan")) {
                                MobileInsurancePlan freePlan = mobileInsurancePlanMap.get("Prolong Extendended Warranty(SmartDukaan Special Price)").get(0);
                                InsuranceModel imFree = new InsuranceModel();
                                imFree.setBrand(item.getBrand());
                                imFree.setColor(item.getColor());
                                imFree.setModelName(item.getModelName() + item.getModelNumber());
                                imFree.setInsuranceAmount(0);
                                imFree.setDeviceSellingPrice(customFofoOrderItem.getSellingPrice());
                                LOGGER.info("freePlan.getProductId() {}", freePlan.getProductId());
                                imFree.setInsuranceUId(freePlan.getProductId());
                                imFree.setInsuranceId(String.valueOf(insuranceService.getOneAssistPremiumByVariantId(freePlan.getProductId()).getId()));
                                imFree.setSerialNumber(serialNumberDetail.getSerialNumber());
                                imFree.setMemory(serialNumberDetail.getMemory());
                                imFree.setRam(serialNumberDetail.getRam());
                                imFree.setMfgDate(serialNumberDetail.getMfgDate());
                                insuredModels.add(imFree);
                            }*/
                        } catch (Exception e) {
                            LOGGER.error("Exception - {}", e);
                            throw new ProfitMandiBusinessException("problem fetching plans", "problem fetching plans", "problem fetching plans");
                        }
                    }

                }
            } else {
                nonSerializedItemIds.add(customFofoOrderItem.getItemId());
            }
        }

        Map<Integer, Set<InventoryItem>> serializedInventoryItemMap = new HashMap<>();
        Map<Integer, Set<InventoryItem>> nonSerializedInventoryItemMap = new HashMap<>();
        // Map<String, Float> serialNumberItemPrice = new HashMap<>();

        if (!serialNumbers.isEmpty()) {
            List<InventoryItem> serializedInventoryItems = inventoryItemRepository.selectByFofoIdSerialNumbers(fofoId, serialNumbers, false);
            LOGGER.info("serializedInventoryItems {}", serializedInventoryItems);
            for (InventoryItem inventoryItem : serializedInventoryItems) {
                if (inventoryItem.getGoodQuantity() == 1) {
                    if (serializedInventoryItemMap.containsKey(inventoryItem.getItemId())) {
                        serializedInventoryItemMap.get(inventoryItem.getItemId()).add(inventoryItem);
                    } else {
                        Set<InventoryItem> itemIdInventoryItems = new HashSet<>();
                        itemIdInventoryItems.add(inventoryItem);
                        serializedInventoryItemMap.put(inventoryItem.getItemId(), itemIdInventoryItems);
                    }
                }
            }
        }

        if (!nonSerializedItemIds.isEmpty()) {
            List<InventoryItem> nonSerializedInventoryItems = inventoryItemRepository.selectByFofoIdItemIds(fofoId, nonSerializedItemIds);
            LOGGER.info("nonSerializedInventoryItems {}", nonSerializedInventoryItems);
            for (InventoryItem it : nonSerializedInventoryItems) {
                if (it.getGoodQuantity() > 0) {
                    if (nonSerializedInventoryItemMap.containsKey(it.getItemId())) {
                        nonSerializedInventoryItemMap.get(it.getItemId()).add(it);
                    } else {
                        Set<InventoryItem> tmp = new HashSet<>();
                        tmp.add(it);
                        nonSerializedInventoryItemMap.put(it.getItemId(), tmp);
                    }
                }
            }
        }

        this.validateItemsSerializedNonSerialized(items, itemIdCustomFofoOrderItemMap);

        Map<Integer, Set<InventoryItem>> inventoryItemsToBill = new HashMap<>();
        Map<Integer, Integer> inventoryItemIdQuantityUsed = new HashMap<>(); // to keep track of inventoryitem quanity
        // used for scan records insertion

        LOGGER.info("itemMap keys {}", itemMap.keySet());

        // Fetch live demo serial numbers only for this order's serials (not entire table)
        List<String> orderSerials = serializedInventoryItemMap.values().stream()
                .flatMap(Set::stream)
                .map(InventoryItem::getSerialNumber)
                .collect(Collectors.toList());
        Map<String, LiveDemoSerialNumber> liveDemoSerialNumberMap = new HashMap<>();
        if (!orderSerials.isEmpty()) {
            liveDemoSerialNumberMap = liveDemoBillingRespository.selectBySerialNumbers(orderSerials).stream()
                    .collect(Collectors.toMap(LiveDemoSerialNumber::getSerialNumber, ld -> ld, (a, b) -> a));
        }

        // Lets reduce quantity and decide what inventory items to use.
        for (Item item : items) {
            if (item.getType().equals(ItemType.SERIALIZED)) {
                Set<InventoryItem> inventoryItemsSerializedserialized = serializedInventoryItemMap.get(item.getId());
                if (inventoryItemsSerializedserialized == null) {
                    List<String> invalidSerialNumbers = itemIdCustomFofoOrderItemMap.get(item.getId()).getSerialNumberDetails().stream().map(x -> x.getSerialNumber()).collect(Collectors.toList());
                    throw new ProfitMandiBusinessException("invalidSerialNumbers", invalidSerialNumbers, "FFORDR_1004");
                }
                if (itemIdCustomFofoOrderItemMap.get(item.getId()).getSerialNumberDetails().size() != inventoryItemsSerializedserialized.size()) {
                    LOGGER.info("InsuredModels: {}, and Serialized: {}", insuredModels.size(), itemIdCustomFofoOrderItemMap.get(item.getId()).getSerialNumberDetails().size());
                    if (itemIdCustomFofoOrderItemMap.get(item.getId()).getSerialNumberDetails().size() != insuredModels.size()) {
                        List<String> invalidSerialNumbers = itemIdCustomFofoOrderItemMap.get(item.getId()).getSerialNumberDetails().stream().map(x -> x.getSerialNumber()).collect(Collectors.toList());
                        throw new ProfitMandiBusinessException("invalidSerialNumbers", invalidSerialNumbers, "FFORDR_1004");
                    }
                }
                for (InventoryItem inventoryItem : inventoryItemsSerializedserialized) {
                    inventoryItem.setGoodQuantity(0);
                    inventoryItemIdQuantityUsed.put(inventoryItem.getId(), 1);
                    LiveDemoSerialNumber liveDemoSerialNumber = liveDemoSerialNumberMap.get(inventoryItem.getSerialNumber());
                    if (liveDemoSerialNumber != null) {
                        liveDemoBillingRespository.delete(liveDemoSerialNumber);
                    }
                }
                inventoryItemsToBill.put(item.getId(), inventoryItemsSerializedserialized);
            } else {
                Set<InventoryItem> inventoryItemsNonSerialized = nonSerializedInventoryItemMap.get(item.getId());
                int quantityToBill = itemIdCustomFofoOrderItemMap.get(item.getId()).getQuantity();
                int totalLeft = quantityToBill;
                Set<InventoryItem> inventoryItemsNonSerializedUsed = new HashSet<>();
                if (inventoryItemsNonSerialized != null) {
                    for (InventoryItem inventoryItem : inventoryItemsNonSerialized) {
                        if (totalLeft > 0) {
                            int toUse = Math.min(totalLeft, inventoryItem.getGoodQuantity());
                            inventoryItemIdQuantityUsed.put(inventoryItem.getId(), toUse);
                            inventoryItem.setGoodQuantity(inventoryItem.getGoodQuantity() - toUse);
                            totalLeft = totalLeft - toUse;
                            inventoryItemsNonSerializedUsed.add(inventoryItem);
                        }
                    }
                }

                if (totalLeft > 0) {
                    // not enough quanity for non-serialized
                    LOGGER.error("not enough quanity for non-serialized");
                    throw new ProfitMandiBusinessException("notEnoughQuantityForNonSerialized", totalLeft, "FFORDR_1005");
                }
                inventoryItemsToBill.put(item.getId(), inventoryItemsNonSerializedUsed);
            }
        }

        // DP/MOP price validation disabled as of 11 sep 2025 as per tarun sir

        String fofoStoreCode = this.getFofoStoreCode(fofoId);
        String documentNumber = null;
        if (noGST) {
            documentNumber = this.getSecurityDepositNumber(fofoId, fofoStoreCode);

        } else {
            documentNumber = this.getInvoiceNumber(fofoId, fofoStoreCode);
        }

        CustomerAddress customerAddress = null;
        if (customCustomer.getCustomerAddressId() != 0) {
            customerAddress = customer.getCustomerAddress().stream().filter(x -> x.getId() == customCustomer.getCustomerAddressId()).findFirst().get();
        }
        FofoOrder fofoOrder = this.createAndGetFofoOrder(customer.getId(), customCustomer.getGstNumber(), fofoId, documentNumber, totalAmount, customCustomer.getCustomerAddressId(), createOrderRequest.getPoId());
        partnerInvestmentService.evictInvestmentCache(fofoId);

        this.createPaymentOptions(fofoOrder, createOrderRequest.getPaymentOptions());

        int retailerAddressId = retailerRegisteredAddressRepository.selectAddressIdByRetailerId(fofoId);

        Address retailerAddress = addressRepository.selectById(retailerAddressId);

        Integer stateId = null;
        if (customerAddress == null || customerAddress.getState() == null || customerAddress.getState().equals(retailerAddress.getState())) {
            try {
                State state = stateRepository.selectByName(retailerAddress.getState());
                stateId = Long.valueOf(state.getId()).intValue();
            } catch (Exception e) {
                LOGGER.error("Unable to get state rates for state: {}", retailerAddress.getState(), e);
            }
        }

        // N+1 fix: Pre-fetch tagListings and GST rates before the loop
        Map<Integer, TagListing> tagListingMap = tagListingRepository.selectByItemIds(itemIdCustomFofoOrderItemMap.keySet());
        Map<Integer, GstRate> gstRateMap = null;
        if (stateId != null) {
            gstRateMap = stateGstRateRepository.getStateTaxRate(new ArrayList<>(itemMap.keySet()), stateId);
        } else {
            gstRateMap = stateGstRateRepository.getIgstTaxRate(new ArrayList<>(itemMap.keySet()));
        }

        // N+1 fix: Collect created FofoOrderItems during the loop instead of re-querying
        List<FofoOrderItem> fofoItems = new ArrayList<>();
        for (CustomFofoOrderItem customFofoOrderItem : createOrderRequest.getFofoOrderItems()) {
            FofoOrderItem fofoOrderItem = this.createAndGetFofoOrderItem(customFofoOrderItem, fofoOrder.getId(), itemMap, inventoryItemsToBill.get(customFofoOrderItem.getItemId()), tagListingMap, gstRateMap);
            fofoItems.add(fofoOrderItem);

            Item item = itemMap.get(customFofoOrderItem.getItemId());
            if (item.getType().equals(ItemType.NON_SERIALIZED)) {
                if (customFofoOrderItem.getCustomSerialNumbers() != null && !customFofoOrderItem.getCustomSerialNumbers().isEmpty()) {
                    persistNonSerializedWithCustomSerialNumber(customFofoOrderItem, fofoOrderItem.getId());
                } else {
                    LOGGER.info("Custom serial numbers are empty. Not persisting data.");
                }
            }


            Set<InventoryItem> inventoryItems = inventoryItemsToBill.get(customFofoOrderItem.getItemId());

            this.createFofoLineItem(fofoOrderItem.getId(), inventoryItems, inventoryItemIdQuantityUsed);

            this.updateCurrentInventorySnapshot(currentInventorySnapshots, fofoId, customFofoOrderItem.getItemId(), customFofoOrderItem.getQuantity());

            this.updateInventoryItemsAndScanRecord(inventoryItems, fofoId, inventoryItemIdQuantityUsed, fofoOrder.getId());
        }

        // Use existing itemMap instead of querying DB per item (N+1 fix)
        boolean smartPhone = items.stream().anyMatch(Item::isSmartPhone);
        if (smartPhone) {
            LOGGER.info("Smartphone found in order items");
        } else {
            LOGGER.warn("No smartphones found in fofoItems.");
        }


        if (smartPhone) {
            this.createAndGetHygieneData(fofoOrder.getId(), fofoOrder.getFofoId());
        }
        // insurance calculation is insurance flag is enabled
        //
        if (insuredModels.size() > 0) {
            LOGGER.info("Processing insurane for serialNumbers");
            LOGGER.info("InsuranceModels {}", insuredModels);
            if (createOrderRequest.getCustomer() == null || createOrderRequest.getCustomer().getDateOfBirth() == null) {
                throw new ProfitMandiBusinessException("Order", "Customer Date of Birth", "Customer date of birth is required for insurance");
            }
            LocalDate customerDateOfBirth = LocalDate.from(createOrderRequest.getCustomer().getDateOfBirth());
            fofoOrder.setDateOfBirth(customerDateOfBirth);
            for (InsuranceModel insuranceModel : insuredModels) {
                LOGGER.info("G- {}", insuranceModel.getInsuranceId());
                LOGGER.info("insuranceModel- {}", insuranceModel);
                insuranceService.createInsurance(fofoOrder, insuranceModel, false);
            }
        }

        schemeService.processSchemeOut(fofoOrder.getId(), fofoId);

        if (createOrderRequest.getPoId() != 0) {
            PendingOrder po = pendingOrderRepository.selectById(createOrderRequest.getPoId());
            po.setBilledAmount(po.getBilledAmount() + totalAmount);

            // N+1 fix: Batch fetch pending order items instead of querying per item
            List<Integer> poiIds = createOrderRequest.getFofoOrderItems().stream()
                    .map(CustomFofoOrderItem::getPoiId)
                    .filter(id -> id != 0)
                    .collect(Collectors.toList());
            if (!poiIds.isEmpty()) {
                List<PendingOrderItem> pendingOrderItems = pendingOrderItemRepository.selectByIds(poiIds);
                LocalDateTime now = LocalDateTime.now();
                for (PendingOrderItem poi : pendingOrderItems) {
                    poi.setStatus(OrderStatus.BILLED);
                    poi.setBilledTimestamp(now);
                }
            }

            po.setStatus(OrderStatus.BILLED);
        }
        //Process scratch (only if smartphone in order — processScratchOffer re-queries items)
        if (smartPhone) {
            this.processScratchOffer(fofoOrder);
        }

//        persist the data of upgrade offer table
        for (CustomFofoOrderItem customFofoOrderItem : createOrderRequest.getFofoOrderItems()) {
            if (customFofoOrderItem.getCustomerOfferItemId().size() > 0) {
                for (Integer customerOfferItemId : customFofoOrderItem.getCustomerOfferItemId()) {
                    UpgradeOffer upgradeOffer = new UpgradeOffer();
                    upgradeOffer.setOrderId(fofoOrder.getId());
                    upgradeOffer.setCustomerOfferItemId(customerOfferItemId);
                    upgradeOffer.setItemId(customFofoOrderItem.getItemId());

                    Set<SerialNumberDetail> serialNumberDetails = customFofoOrderItem.getSerialNumberDetails();

                    if (!customFofoOrderItem.getSerialNumberDetails().isEmpty()) {
                        String serialNumber = serialNumberDetails.iterator().next().getSerialNumber();
                        upgradeOffer.setSerialNumber(serialNumber);

                        //                Set<String> serialNumbersSet = this.serialNumberDetailsToSerialNumbers(customFofoOrderItem.getSerialNumberDetails());
                        //                LOGGER.info("serialNumbersSet.toString() {}",serialNumbersSet.toString());
                        //                upgradeOffer.setSerialNumber(serialNumbersSet.toString());
                    } else {
                        upgradeOffer.setSerialNumber(null); // Handle case where there is no serial number detail
                    }
                    upgradeOffer.setCreatedTimestamp(LocalDateTime.now());
                    upgradeOffer.setPaymentStatus(UpgradeOfferPaymentStatus.PENDING);
                    upgradeOffer.setStatusDescription(UpgradeOfferPaymentStatus.PENDING.getValue());
                    upgradeOfferRepository.persist(upgradeOffer);
                }

            }
        }

//        enable it fo upsell call - N+1 fix: smartPhone already determined, no need to re-query items
        if (smartPhone && fofoOrder.getId() > 0) {
            List<InsurancePolicy> insurancePolicies = insurancePolicyRepository
                    .selectByRetailerIdInvoiceNumber(fofoOrder.getInvoiceNumber());
            if (insurancePolicies.isEmpty()) {
                UpSaleOrder upSaleOrder = new UpSaleOrder();
                upSaleOrder.setCreatedTimestamp(LocalDateTime.now());
                upSaleOrder.setOrderId(fofoOrder.getId());
                upSaleOrder.setFofoId(fofoOrder.getFofoId());
                upSaleOrderRepository.persist(upSaleOrder);
            }
        }

        // Update Partner Opening Stock current qty - N+1 fix: use existing itemMap and batch update
        if (fofoOrder.getId() > 0) {
            Map<Integer, Integer> itemIdQuantityMap = new HashMap<>();
            for (FofoOrderItem fofoOrderItem : fofoItems) {
                itemIdQuantityMap.merge(fofoOrderItem.getItemId(), fofoOrderItem.getQuantity(), Integer::sum);
            }
            smartCartService.minusOpeningStockBatch(itemIdQuantityMap, fofoOrder.getFofoId());
        }


        return fofoOrder.getId();
    }

    @Override
    public void processScratchOffer(FofoOrder fofoOrder) throws ProfitMandiBusinessException {
        boolean isSmartPhonePurchased = false;
        float maxPurchaseValue = 0;
        List<FofoOrderItem> fofoOrderItems = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());

        // N+1 fix: batch fetch all items instead of querying per order item
        Set<Integer> itemIds = fofoOrderItems.stream()
                .map(FofoOrderItem::getItemId)
                .collect(Collectors.toSet());
        Map<Integer, Item> itemMap = itemRepository.selectByIds(itemIds).stream()
                .collect(Collectors.toMap(Item::getId, item -> item));

        for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
            Item item = itemMap.get(fofoOrderItem.getItemId());

            if (item != null && item.isSmartPhone()) {
                LOGGER.info("fofoItem {}", fofoOrderItem);
                isSmartPhonePurchased = true;
                maxPurchaseValue = Math.max(fofoOrderItem.getSellingPrice(), maxPurchaseValue);
            }
        }
        LocalDate startDate = ProfitMandiConstants.SCRATCH_OFFER_START_DATE;
        LocalDate endDate = ProfitMandiConstants.SCRATCH_OFFER_END_DATE;
        boolean specificPriceOffer = ProfitMandiConstants.SPECIFIC_PRICE_OFFER;
        boolean randomOffer = ProfitMandiConstants.RANDOM_OFFER;

        if (isSmartPhonePurchased) {

            if (LocalDateTime.now().isAfter(startDate.atStartOfDay()) && LocalDateTime.now().isBefore(endDate.atTime(Utils.MAX_TIME))) {
                Customer customer = customerRepository.selectById(fofoOrder.getCustomerId());
                try {
                    this.sendAppDownloadBillingOffer(customer.getMobileNumber());
                } catch (Exception e) {
                    LOGGER.error("Failed to send app download billing offer for customer {}", customer.getMobileNumber(), e);
                }
                if (specificPriceOffer) {
                    this.createSpecificPriceScratchOffer(fofoOrder.getInvoiceNumber(), fofoOrder.getCustomerId(), fofoOrder.getFofoId(), maxPurchaseValue);
                } else if (randomOffer) {
                    this.createRandomScratchOffer(fofoOrder.getInvoiceNumber(), fofoOrder.getCustomerId());
                    LOGGER.info("randomOffer {}", randomOffer);
                } else {
                    this.createScratchOffer(fofoOrder.getFofoId(), fofoOrder.getInvoiceNumber(), fofoOrder.getCustomerId());
                }

            }
        }
    }

    @Override
    public ScratchedGift getSelectedGift(double purchaseAmount, int fofoId) throws ProfitMandiBusinessException {
        Map<ScratchedGift, Long> scratchOfferCountMap = scratchOfferRepository.countOffersByDateRange(
                ProfitMandiConstants.SCRATCH_OFFER_START_DATE, ProfitMandiConstants.SCRATCH_OFFER_END_DATE
        );
        LOGGER.info("scratchOfferCountMap {}", scratchOfferCountMap);

        LocalDateTime startDateTime = ProfitMandiConstants.SCRATCH_OFFER_START_DATE.atStartOfDay();
        LocalDateTime endDateTime = ProfitMandiConstants.SCRATCH_OFFER_START_DATE.atTime(LocalTime.MAX);

        LOGGER.info("start date {}", startDateTime);
        LOGGER.info("end date {}", endDateTime);

        RandomCollection<ScratchedGift> giftRandomCollection = createDynamicGiftSeries(scratchOfferCountMap, purchaseAmount, fofoId);

        if (giftRandomCollection.size() > 0) {
            ScratchedGift selectedGift = giftRandomCollection.next();

            // Ensure one LED for 175139615 and one Oven for 175139583
            if (selectedGift == ScratchedGift.LED || selectedGift == ScratchedGift.MICROWAVE_OVEN) {
                if (!PREMIUM_ELIGIBLE_PARTNERS.contains(fofoId)) {
                    LOGGER.info("Partner {} not eligible for {}", fofoId, selectedGift);
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF; // Default alternate gift
                }

                // Restrict LED to Partner 175139615 and Oven to Partner 175139583
                if ((selectedGift == ScratchedGift.LED && fofoId != 175139615) ||
                        (selectedGift == ScratchedGift.MICROWAVE_OVEN && fofoId != 175139583)) {
                    LOGGER.info("Partner {} not eligible for {}", fofoId, selectedGift);
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
                }

                // Ensure only one LED and one Microwave Oven per day
                long ledCount = scratchOfferCountMap.getOrDefault(ScratchedGift.LED, 0L);
                long ovenCount = scratchOfferCountMap.getOrDefault(ScratchedGift.MICROWAVE_OVEN, 0L);
                if ((selectedGift == ScratchedGift.LED || selectedGift == ScratchedGift.MICROWAVE_OVEN)
                        && (ledCount > 0 && ovenCount > 0)) {
                    LOGGER.info("Both LED and Microwave Oven already given today.");
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
                }

                if ((selectedGift == ScratchedGift.LED && ledCount > 0)) {
                    LOGGER.info("LED already given today.");
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
                }

                if ((selectedGift == ScratchedGift.LED)) {
                    LOGGER.info("LED already given today.");
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
                }

                if ((selectedGift == ScratchedGift.MICROWAVE_OVEN && ovenCount > 0)) {
                    LOGGER.info("Oven already given today.");
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
                }
            }

            //  Ensure only one Neckband per partner per day
            if (selectedGift == ScratchedGift.NECK_BAND) {
                List<FofoOrder> fofoOrders = fofoOrderRepository.selectByFofoId(fofoId, startDateTime, endDateTime, 0, 0);
                List<String> invoiceNumbers = fofoOrders.stream().map(FofoOrder::getInvoiceNumber).collect(Collectors.toList());
                List<ScratchOffer> offers = scratchOfferRepository.selectByInvoiceNumbers(invoiceNumbers);
                LOGGER.info("offers for partner {}", offers);

                boolean neckbandGivenToday = offers.stream().anyMatch(offer -> offer.getOfferName().equals(ScratchedGift.NECK_BAND));
                if (neckbandGivenToday) {
                    LOGGER.info("Neckband already given today for partner {}", fofoId);
                    return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
                }
            }

            return selectedGift;
        }

        return ScratchedGift.ACCESSORIES_50_PERCENT_OFF;
    }


    public RandomCollection<ScratchedGift> createDynamicGiftSeries(Map<ScratchedGift, Long> soldGiftContMap, Double sellingPrice, int fofoId) throws ProfitMandiBusinessException {
        int index = 0;
        RandomCollection<ScratchedGift> randomCollection = new RandomCollection<>();
        PartnerType partnerType = partnerTypeChangeService.getTypeOnDate(fofoId, LocalDate.now());
        LOGGER.info("partnerType {}", partnerType);

        if (partnerType.equals(PartnerType.BRONZE)) {
            LOGGER.info("partnerType if- {}", partnerType);
            sellingPrice = 0.0;
        }

        for (int i = 0; i < PRICE_RANGE.size(); i++) {
            Double price = PRICE_RANGE.get(i);
            Double nextPrice = Double.MAX_VALUE;
            if (i != PRICE_RANGE.size() - 1) {
                nextPrice = PRICE_RANGE.get(i + 1);
            }
            if (sellingPrice >= price && sellingPrice < nextPrice) {
                int divisor = PRICE_RANGE.size() - index;
                LOGGER.info("Processing price range: {}, sellingPrice: {}", price, sellingPrice);

                for (ScratchedGift gift : GIFT_SERIES.get(price)) {
                    int remainingQty = GIFT_QUANTITIES.get(gift) - soldGiftContMap.getOrDefault(gift, 0L).intValue();
                    LOGGER.info("Checking gift: {}, remainingQty: {}", gift, remainingQty);

                    if (remainingQty > 0) {
                        int weight = (remainingQty > divisor) ? remainingQty / divisor : remainingQty;
                        randomCollection.add(weight, gift);
                        LOGGER.info("Added gift: {}, weight: {}", gift, weight);
                    }
                }
                break; // Exit the loop once the correct price range is processed
            }
            index++;
        }

        // If no gifts were added, log and handle potential issues here
        if (randomCollection.size() == 0) {
            LOGGER.info("No gifts added for sellingPrice: {} in createDynamicGiftSeries", sellingPrice);
        }

        LOGGER.info("randomCollectionSize {}, partnerType {}, sellingPrice {}", randomCollection.size(), partnerType, sellingPrice);
        return randomCollection;
    }



    /*static {
        RandomCollection<ScratchedGift> map1 = new RandomCollection<ScratchedGift>().
                add(100d, ScratchedGift.GIFT_BOWL);
        GIFT_SERIES.put(0.0, map1);
        //Map<ScratchedGift, Double> map2 = new HashMap<>();
        RandomCollection<ScratchedGift> map2 = new RandomCollection<ScratchedGift>()
                .add(40d, ScratchedGift.GIFT_BOWL)
                .add(20d, ScratchedGift.NECK_BAND)
                .add(30d, ScratchedGift.FLASKNMUG)
                .add(10d, ScratchedGift.ELECTRIC_KETTLE);
        GIFT_SERIES.put(10001.0, map2);
        RandomCollection<ScratchedGift> map3 = new RandomCollection<ScratchedGift>()
                .add(25d, ScratchedGift.GIFT_BOWL)
                .add(30d, ScratchedGift.NECK_BAND)
                .add(10d, ScratchedGift.SPEAKER)
                .add(25d, ScratchedGift.FLASKNMUG)
                .add(10d, ScratchedGift.ELECTRIC_KETTLE);
        GIFT_SERIES.put(18001.0, map3);
        RandomCollection<ScratchedGift> map4 = new RandomCollection<ScratchedGift>()
                .add(30d, ScratchedGift.NECK_BAND)
                .add(20d, ScratchedGift.SPEAKER)
                .add(20d, ScratchedGift.FLASKNMUG)
                .add(30d, ScratchedGift.ELECTRIC_KETTLE);
        GIFT_SERIES.put(25001.0, map4);
        RandomCollection<ScratchedGift> map5 = new RandomCollection<ScratchedGift>()
                .add(40d, ScratchedGift.SPEAKER)
                .add(60d, ScratchedGift.SMART_WATCH);

        GIFT_SERIES.put(50001.0, map5);
    }*/


    private void createSpecificPriceScratchOffer(String invoiceNumber, int customerId, int fofoId, float purchaseAmount) throws ProfitMandiBusinessException {
        ScratchedGift selectedGift = getSelectedGift(purchaseAmount, fofoId);
        List<ScratchOffer> scratchOffers = scratchOfferRepository.selectBycCustomerIdAndDate(customerId, ProfitMandiConstants.SCRATCH_OFFER_START_DATE, ProfitMandiConstants.SCRATCH_OFFER_END_DATE);
        if (scratchOffers.size() == 0) {
            ScratchOffer so2 = new ScratchOffer();
            so2.setInvoiceNumber(invoiceNumber);
            so2.setScratched(false);
            so2.setCreatedTimestamp(LocalDateTime.now());
            so2.setExpiredTimestamp(ProfitMandiConstants.SCRATCH_OFFER_END_DATE.plusDays(1).atTime(LocalTime.MAX));
            so2.setOfferName(String.valueOf(selectedGift));
            so2.setCustomerId(customerId);
            so2.setUnlockedAt(LocalDateTime.now());
            scratchOfferRepository.persist(so2);
        }
    }

    private void createRandomScratchOffer(String invoiceNumber, int customerId) {
        ScratchedGift selectedGift = getScratchedGiftRandomAccordingQuantity(customerId);
        List<ScratchOffer> scratchOffers = scratchOfferRepository.selectBycCustomerIdAndDate(customerId, ProfitMandiConstants.SCRATCH_OFFER_START_DATE, ProfitMandiConstants.SCRATCH_OFFER_END_DATE);
        if (scratchOffers.size() == 0) {
            ScratchOffer so2 = new ScratchOffer();
            so2.setInvoiceNumber(invoiceNumber);
            so2.setScratched(false);
            so2.setCreatedTimestamp(LocalDateTime.now());
            so2.setExpiredTimestamp(ProfitMandiConstants.SCRATCH_OFFER_END_DATE.plusDays(1).atTime(LocalTime.MAX));
            so2.setOfferName(String.valueOf(selectedGift));
            so2.setCustomerId(customerId);
            so2.setUnlockedAt(LocalDateTime.now());
            scratchOfferRepository.persist(so2);
        }
    }

    private ScratchedGift getScratchedGiftRandom(int fofoId, int customerId) throws ProfitMandiBusinessException {
        Map<Integer, ScratchedGift> giftSeries = new HashMap<>();
        giftSeries.put(1, ScratchedGift.MINI_CHOPPER);
        giftSeries.put(2, ScratchedGift.FRUIT_JUICER);
        giftSeries.put(3, ScratchedGift.STEAM_IRON);


        List<FofoOrder> fofoOrders = fofoOrderRepository.selectByFofoIdBetweenCreatedTimeStamp(fofoId, ProfitMandiConstants.SCRATCH_OFFER_START_DATE.atStartOfDay(),
                ProfitMandiConstants.SCRATCH_OFFER_END_DATE.atTime(Utils.MAX_TIME));

        ScratchedGift gift = ScratchedGift.BLNT;

        Random random = new Random();
        int rand;
        while (true) {
            rand = random.nextInt(4);
            if (rand != 0) break;
        }
        if (fofoOrders.isEmpty()) {
            gift = giftSeries.get(rand);
        } else {

            List<String> invoiceNumbers = fofoOrders.stream().filter(x -> x.getCancelledTimestamp() == null).map(x -> x.getInvoiceNumber()).collect(Collectors.toList());

            List<ScratchOffer> scratchOffers = scratchOfferRepository.selectByInvoiceNumbers(invoiceNumbers);
            if (scratchOffers.isEmpty()) {
                gift = giftSeries.get(rand);
            } else {
                List<ScratchOffer> bigGifts = scratchOffers.stream().filter(x -> !x.getOfferName().equals(ScratchedGift.BLNT) && !x.getOfferName().equals(ScratchedGift.EW)).collect(Collectors.toList());
                if (bigGifts.size() <= 10) {
                    List<Integer> scratchCustomerIds = scratchOffers.stream().map(x -> x.getCustomerId()).collect(Collectors.toList());
                    if (scratchCustomerIds.contains(customerId)) {


                        gift = ScratchedGift.BLNT;

                        LOGGER.info("gift2 {}", gift);

                    } else {

                        int miniChopper = (int) bigGifts.stream().filter(x -> x.getOfferName().equals(ScratchedGift.MINI_CHOPPER)).count();
                        int fruitJuicer = (int) bigGifts.stream().filter(x -> x.getOfferName().equals(ScratchedGift.FRUIT_JUICER)).count();
                        int streanIron = (int) bigGifts.stream().filter(x -> x.getOfferName().equals(ScratchedGift.STEAM_IRON)).count();

                        if (rand == 1) {
                            if (miniChopper < 4) {
                                LOGGER.info("miniChopper {}", miniChopper);


                                gift = giftSeries.get(rand);
                            }
                        }

                        if (rand == 2) {
                            if (fruitJuicer < 3) {

                                LOGGER.info("fruitJuicer {}", fruitJuicer);

                                gift = giftSeries.get(rand);
                            }
                        }

                        if (rand == 3) {
                            if (streanIron < 3) {

                                LOGGER.info("streanIron {}", streanIron);


                                gift = giftSeries.get(rand);

                            }
                        }

                        LOGGER.info("gift4 {}", gift);
                    }
                }
            }


        }
        return gift;
    }

    private ScratchedGift getScratchedGiftRandomAccordingQuantity(int customerId) {
        RandomCollection<ScratchedGift> map1 = new RandomCollection<ScratchedGift>().
                add(50d, ScratchedGift.SOLOR_LAMP)
                .add(100d, ScratchedGift.BLUETOOTH_SPEAKER)
                .add(150d, ScratchedGift.RED_WATER_BOTTLE)
                .add(200d, ScratchedGift.GIFT_BOWL)
                .add(100d, ScratchedGift.EARBUDS);

        ScratchedGift gift;

        List<ScratchOffer> lastScratchOffers = scratchOfferRepository.selectBycCustomerIdAndDate(customerId, ProfitMandiConstants.LAST_SCRATCH_OFFER_START_DATE, ProfitMandiConstants.LAST_SCRATCH_OFFER_END_DATE);

        if (lastScratchOffers.isEmpty()) {
            gift = map1.next();
        } else {
            gift = ScratchedGift.RED_WATER_BOTTLE;
            LOGGER.info("RED_WATER_BOTTLE {}", gift);
        }
        return gift;
    }

    private HygieneData createAndGetHygieneData(int id, int fofoId) {
        HygieneData hygieneData = new HygieneData();
        hygieneData.setOrderId(id);
        hygieneData.setFofoId(fofoId);
        hygieneData.setCreatedTimestamp(LocalDateTime.now());
        hygieneDataRepository.persist(hygieneData);

        return hygieneData;
    }


    @Override
    public String getInvoiceNumber(int fofoId, String fofoStoreCode) {
        InvoiceNumberGenerationSequence invoiceNumberGenerationSequence = null;
        try {
            invoiceNumberGenerationSequence = invoiceNumberGenerationSequenceRepository.selectByFofoId(fofoId);
            invoiceNumberGenerationSequence.setSequence(invoiceNumberGenerationSequence.getSequence() + 1);
        } catch (ProfitMandiBusinessException profitMandiBusinessException) {
            invoiceNumberGenerationSequence = new InvoiceNumberGenerationSequence();
            invoiceNumberGenerationSequence.setFofoId(fofoId);
            invoiceNumberGenerationSequence.setPrefix(fofoStoreCode);
            invoiceNumberGenerationSequence.setSequence(1);
        }
        invoiceNumberGenerationSequenceRepository.persist(invoiceNumberGenerationSequence);
        return invoiceNumberGenerationSequence.getPrefix() + "/" + invoiceNumberGenerationSequence.getSequence();
    }

    private String getSecurityDepositNumber(int fofoId, String fofoStoreCode) {
        InvoiceNumberGenerationSequence invoiceNumberGenerationSequence = null;
        try {
            invoiceNumberGenerationSequence = invoiceNumberGenerationSequenceRepository.selectByFofoId(fofoId);
            invoiceNumberGenerationSequence.setChallanNumberSequence(invoiceNumberGenerationSequence.getChallanNumberSequence() + 1);
        } catch (ProfitMandiBusinessException profitMandiBusinessException) {
            invoiceNumberGenerationSequence = new InvoiceNumberGenerationSequence();
            invoiceNumberGenerationSequence.setFofoId(fofoId);
            invoiceNumberGenerationSequence.setPrefix(fofoStoreCode);
            invoiceNumberGenerationSequence.setChallanNumberSequence(1);
        }
        invoiceNumberGenerationSequenceRepository.persist(invoiceNumberGenerationSequence);
        return invoiceNumberGenerationSequence.getPrefix() + "/SEC" + invoiceNumberGenerationSequence.getChallanNumberSequence();
    }

    private Set<String> serialNumberDetailsToSerialNumbers(Set<SerialNumberDetail> serialNumberDetails) {
        Set<String> serialNumbers = new HashSet<>();
        for (SerialNumberDetail serialNumberDetail : serialNumberDetails) {
            if (serialNumberDetail.getSerialNumber() != null && !serialNumberDetail.getSerialNumber().isEmpty()) {
                serialNumbers.add(serialNumberDetail.getSerialNumber());
            }
        }
        return serialNumbers;
    }

    @Override
    public InvoicePdfModel getInvoicePdfModel(int orderId) throws ProfitMandiBusinessException {
        FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(orderId);
        return this.getInvoicePdfModel(fofoOrder);
    }

    @Override
    @Cacheable(value = "order.dummymodel", cacheManager = "oneDayCacheManager")
    public InvoicePdfModel getDummyPdfModel(String serialNumber) throws ProfitMandiBusinessException {
        List<WarehouseInventoryItem> warehouseInventoryItems = warehouseInventoryItemRepository.selectWarehouseInventoryItemBySerailNumbers(Arrays.asList(serialNumber));
        if (warehouseInventoryItems.size() > 0) {
            WarehouseInventoryItem warehouseInventoryItem = warehouseInventoryItems.get(0);
            int currentQuantity = warehouseInventoryItems.get(0).getCurrentQuantity();
            if (currentQuantity > 0) {
                throw new ProfitMandiBusinessException("Serial Number", serialNumber, "Serial Number exist in our warehouse");
            } else {
                try {
                    InventoryItem inventoryItem = inventoryItemRepository.selectBySerialNumber(serialNumber);
                    if (inventoryItem.getGoodQuantity() > 0) {
                        throw new ProfitMandiBusinessException("Serial Number", serialNumber, "Serial Number is not yet billed by the partner");
                    } else {
                        List<ScanRecord> scanRecords = scanRecordRepository.selectByInventoryItemId(inventoryItem.getId());
                        Optional<ScanRecord> scanRecord = scanRecords.stream().filter(x -> x.getOrderId() != 0).findFirst();
                        if (scanRecord.isPresent()) {
                            FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(scanRecord.get().getOrderId());
                            orderIdsConsumed.add(fofoOrder.getId());
                            return this.getInvoicePdfModel(fofoOrder);
                        } else {
                            throw new ProfitMandiBusinessException("Serial Number", serialNumber, "Serial Number returned by partner, but in transit");
                        }
                    }
                } catch (Exception e) {
                    int itemId = warehouseInventoryItem.getItemId();
                    if (serialNumberOrderIdMap.containsKey(serialNumber)) {
                        FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(serialNumberOrderIdMap.get(serialNumber));
                        InvoicePdfModel pdfModel = this.getInvoicePdfModel(fofoOrder.getId());
                        this.modifyDummyModel(fofoOrder, pdfModel, itemId, serialNumber);
                        return pdfModel;
                    }
                    // Map this serialNumber for dummy billing
                    LocalDateTime grnDate = warehouseInventoryItem.getCreated();
                    Random random = new Random();
                    int randomDays = random.ints(2, 15).findFirst().getAsInt();
                    LocalDateTime saleDate = grnDate.plusDays(randomDays);
                    if (saleDate.isAfter(LocalDate.now().atStartOfDay())) {
                        saleDate = LocalDateTime.now().minusDays(2);
                    }
                    Random offsetRandom = new Random();
                    int offset = offsetRandom.ints(2, 100).findFirst().getAsInt();
                    FofoOrder fofoOrder = fofoOrderRepository.selectFirstOrderAfterDate(saleDate, offset);
                    while (orderIdsConsumed.contains(fofoOrder.getId())) {
                        Random offsetRandom2 = new Random();
                        int offset2 = offsetRandom2.ints(2, 100).findFirst().getAsInt();
                        FofoOrder fofoOrder2 = fofoOrderRepository.selectFirstOrderAfterDate(saleDate, offset2);
                        if (fofoOrder2 != null) {
                            fofoOrder = fofoOrder2;
                        }
                    }
                    InvoicePdfModel pdfModel = this.getInvoicePdfModel(fofoOrder.getId());
                    orderIdsConsumed.add(fofoOrder.getId());
                    this.modifyDummyModel(fofoOrder, pdfModel, itemId, serialNumber);
                    return pdfModel;

                }
            }
        } else {
            throw new ProfitMandiBusinessException("Serial Number", serialNumber, "Serial Number does not exist in our warehouse");
        }
    }

    void modifyDummyModel(FofoOrder fofoOrder, InvoicePdfModel pdfModel, int itemId, String serialNumber) throws
            ProfitMandiBusinessException {

        int retailerAddressId = retailerRegisteredAddressRepository.selectAddressIdByRetailerId(fofoOrder.getFofoId());

        Address retailerAddress = addressRepository.selectById(retailerAddressId);
        Customer customer = customerRepository.selectById(fofoOrder.getCustomerId());

        CustomerAddress customerAddress = customer.getCustomerAddress().stream().filter(x -> x.getId() == fofoOrder.getCustomerAddressId()).findFirst().get();

        Integer stateId = null;
        if (customerAddress.getState().equals(retailerAddress.getState())) {
            try {
                // stateId =
                // Long.valueOf(Utils.getStateInfo(customerAddress.getState()).getId()).intValue();

                stateId = Long.valueOf(stateRepository.selectByName(customerAddress.getState()).getId()).intValue();
            } catch (Exception e) {
                LOGGER.error("Unable to get state rates");
            }
        }
        CustomOrderItem cli = pdfModel.getOrderItems().stream().findFirst().get();
        List<FofoOrderItem> fofoOrderItems = Arrays.asList(this.getDummyFofoOrderItem(itemId, fofoOrder.getId(), serialNumber, stateId));
        pdfModel.setPaymentOptions(pdfModel.getPaymentOptions().stream().limit(1).collect(Collectors.toList()));
        CustomPaymentOption paymentOption = pdfModel.getPaymentOptions().get(0);
        paymentOption.setAmount(fofoOrderItems.get(0).getMop());
        List<CustomOrderItem> customerFofoOrderItems = new ArrayList<>();
        for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
            CustomOrderItem customFofoOrderItem = new CustomOrderItem();
            float totalTaxRate = fofoOrderItem.getIgstRate() + fofoOrderItem.getSgstRate() + fofoOrderItem.getCgstRate();
            float taxableSellingPrice = fofoOrderItem.getSellingPrice() / (1 + totalTaxRate / 100);
            float taxableDiscountPrice = fofoOrderItem.getDiscount() / (1 + totalTaxRate / 100);

            customFofoOrderItem.setAmount(fofoOrderItem.getQuantity() * (taxableSellingPrice - taxableDiscountPrice));
            customFofoOrderItem.setDescription(fofoOrderItem.getBrand() + " " + fofoOrderItem.getModelName() + " " + fofoOrderItem.getModelNumber() + "-" + fofoOrderItem.getColor());
            Set<String> serialNumbers = this.toSerialNumbers(fofoOrderItem.getFofoLineItems());
            // LOGGER.info("serialNumbers {}", serialNumbers);
            // LOGGER.info("serialNumbers is empty {}", serialNumbers.isEmpty());
            if (!serialNumbers.isEmpty()) {
                customFofoOrderItem.setDescription(
                        customFofoOrderItem.getDescription() + "\n IMEIS - " + String.join(", ", serialNumbers));
            }
            customFofoOrderItem.setRate(taxableSellingPrice);
            customFofoOrderItem.setDiscount(taxableDiscountPrice);
            customFofoOrderItem.setQuantity(fofoOrderItem.getQuantity());
            customFofoOrderItem.setNetAmount(
                    (fofoOrderItem.getSellingPrice() - fofoOrderItem.getDiscount()) * fofoOrderItem.getQuantity());
            float igstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getIgstRate()) / 100;
            float cgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getCgstRate()) / 100;
            float sgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getSgstRate()) / 100;
            customFofoOrderItem.setIgstRate(fofoOrderItem.getIgstRate());
            customFofoOrderItem.setIgstAmount(igstAmount);
            customFofoOrderItem.setCgstRate(fofoOrderItem.getCgstRate());
            customFofoOrderItem.setCgstAmount(cgstAmount);
            customFofoOrderItem.setSgstRate(fofoOrderItem.getSgstRate());
            customFofoOrderItem.setSgstAmount(sgstAmount);
            customFofoOrderItem.setHsnCode(fofoOrderItem.getHsnCode());
            customerFofoOrderItems.add(customFofoOrderItem);
        }
        pdfModel.setTotalAmount(paymentOption.getAmount());
        pdfModel.setOrderItems(customerFofoOrderItems);

    }

    @Override
    public InvoicePdfModel getInvoicePdfModel(FofoOrder fofoOrder) throws ProfitMandiBusinessException {

        List<PaymentOptionTransaction> paymentOptionTransactions = paymentOptionTransactionRepository.selectByReferenceIdAndTypes(fofoOrder.getId(), Arrays.asList(PaymentOptionReferenceType.ORDER, PaymentOptionReferenceType.INSURANCE));

        List<CustomPaymentOption> paymentOptions = new ArrayList<>();

        InvoicePdfModel pdfModel = new InvoicePdfModel();


        List<FofoOrderItem> fofoOrderItems = this.getByOrderId(fofoOrder.getId());

        double upgradePartnerDiscount = 0;

       /* for (FofoOrderItem fofoOrderItem : fofoOrderItems) {

            Set<String> serialNumbers = this.toSerialNumbers(fofoOrderItem.getFofoLineItems());
            for (String serialNumber : serialNumbers) {
                UpgradeOffer upgradeOffer = upgradeOfferRepository.selectBySerialNumber(serialNumber);
                if (upgradeOffer != null) {
                    CustomerOfferItem customerOfferItem = customerOfferItemRepository.selectById(upgradeOffer.getCustomerOfferItemId());
                    upgradePartnerDiscount += customerOfferItem.getDealerPayout();
                }
            }
        }*/

        boolean hasSamsungUpgrade = paymentOptionTransactions.stream()
                .anyMatch(transaction ->
                        "SAMSUNG UPGRADE".equals(paymentOptionRepository
                                .selectById(transaction.getPaymentOptionId())
                                .getName()));

        LOGGER.info("paymentOptionTransactions - {}", paymentOptionTransactions);
        LOGGER.info("hasSamsungUpgrade - {}", hasSamsungUpgrade);

        double cashDiscount = paymentOptionTransactions.stream()
                .filter(x -> "CASH DISCOUNT".equals(paymentOptionRepository.selectById(x.getPaymentOptionId()).getName())).mapToDouble(x -> x.getAmount()).findFirst().orElse(0);

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

        for (PaymentOptionTransaction paymentOptionTransaction : paymentOptionTransactions) {
            String paymentOptionName = paymentOptionRepository.selectById(paymentOptionTransaction.getPaymentOptionId()).getName();

            CustomPaymentOption cpi = new CustomPaymentOption();
            LOGGER.info("paymentOptionName {}", paymentOptionName);

            float amountToSet = paymentOptionTransaction.getAmount();

            if ("SAMSUNG UPGRADE".equals(paymentOptionName) && hasSamsungUpgrade) {
                if (cashDiscount > upgradePartnerDiscount) {
                    amountToSet += (float) upgradePartnerDiscount;
                } else {
                    amountToSet += (float) cashDiscount;
                }

            } else if ("CASH".equals(paymentOptionName) && !hasSamsungUpgrade) {
                amountToSet += ((float) cashDiscount - (float) upgradePartnerDiscount);

            } else if ("CASH".equals(paymentOptionName) && hasSamsungUpgrade && (cashDiscount > upgradePartnerDiscount)) {
                amountToSet += ((float) cashDiscount - (float) upgradePartnerDiscount);

            }

            cpi.setAmount(amountToSet);
            cpi.setPaymentOption(paymentOptionName);

            paymentOptions.add(cpi);
        }


        pdfModel.setTitle("Tax Invoice");
        Optional<FofoOrderItem> fofoOrderItemOptional = fofoOrderItems.stream().findAny();
        if (fofoOrderItemOptional.isPresent() && fofoOrderItemOptional.get().equals("NOGST")) {
            pdfModel.setTitle("Security Deposit Receipt");
        }
        pdfModel.setPaymentOptions(paymentOptions);
        pdfModel.setAuther("SmartDukaan");
        pdfModel.setInvoiceDate(FormattingUtils.formatDate(fofoOrder.getCreateTimestamp()));

        // insurance calculation
        List<InsurancePolicy> insurancePolicies = insurancePolicyRepository.selectByRetailerIdInvoiceNumber(fofoOrder.getInvoiceNumber());
        List<CustomInsurancePolicy> customInsurancePolicies = new ArrayList<>();
        final float totalInsuranceTaxRate = 18;
        for (InsurancePolicy insurancePolicy : insurancePolicies) {
            float taxableInsurancePrice = insurancePolicy.getSaleAmount() / (1 + totalInsuranceTaxRate / 100);
            CustomInsurancePolicy customInsurancePolicy = new CustomInsurancePolicy();
            customInsurancePolicy.setDescription(insurancePolicy.getPolicyPlan() + " for Device #" + insurancePolicy.getSerialNumber() + "\n Plan Reference - " + insurancePolicy.getPolicyNumber());
            customInsurancePolicy.setHsnCode("998716");
            customInsurancePolicy.setRate(taxableInsurancePrice);
            customInsurancePolicy.setIgstRate(18);
            customInsurancePolicy.setIgstAmount(taxableInsurancePrice * 18 / 100);
            customInsurancePolicy.setCgstRate(9);
            customInsurancePolicy.setCgstAmount(taxableInsurancePrice * 9 / 100);
            customInsurancePolicy.setSgstRate(9);
            customInsurancePolicy.setSgstAmount(taxableInsurancePrice * 9 / 100);
            customInsurancePolicy.setNetAmount(insurancePolicy.getSaleAmount());
            customInsurancePolicies.add(customInsurancePolicy);
        }
        pdfModel.setInsurancePolicies(customInsurancePolicies);

        Retailer retailer = retailerRepository.selectById(fofoOrder.getFofoId());
        User user = userRepository.selectById(userAccountRepository.selectUserIdByRetailerId(retailer.getId()));
        FofoStore fofoStoreForGst = fofoStoreRepository.selectByRetailerId(retailer.getId());
        CustomRetailer customRetailer = new CustomRetailer();
        customRetailer.setBusinessName(retailer.getName());
        customRetailer.setMobileNumber(user.getMobileNumber());
        customRetailer.setGstNumber(fofoStoreForGst != null ? fofoStoreForGst.getGstNumber() : null);
        Address retailerAddress = addressRepository.selectById(retailerRegisteredAddressRepository.selectAddressIdByRetailerId(retailer.getId()));
        customRetailer.setAddress(this.createCustomAddress(retailerAddress));
        pdfModel.setRetailer(customRetailer);

        pdfModel.setCustomer(getCustomCustomer(fofoOrder, customRetailer.getAddress()));
        pdfModel.setInvoiceNumber(fofoOrder.getInvoiceNumber());
        pdfModel.setTotalAmount(fofoOrder.getTotalAmount());


        List<CustomOrderItem> regularFofoItems = new ArrayList<>();
        List<CustomOrderItem> marginSchemeFofoItems = new ArrayList<>();
        boolean hasMarginSchemeItems = false;
        for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
            float discount = fofoOrderItem.getDiscount();
            CustomOrderItem customFofoOrderItem = new CustomOrderItem();
            float totalTaxRate = fofoOrderItem.getIgstRate() + fofoOrderItem.getSgstRate() + fofoOrderItem.getCgstRate();

            customFofoOrderItem.setDescription(fofoOrderItem.getBrand() + " " + fofoOrderItem.getModelName() + " " + fofoOrderItem.getModelNumber() + "-" + fofoOrderItem.getColor());
            Set<String> serialNumbers = this.toSerialNumbers(fofoOrderItem.getFofoLineItems());
            List<FofoNonSerializeSerial> nonSerializeSerials = fofoNonSerializeSerialRepository.selectByItemIdAndOrderId(fofoOrderItem.getId());
            List<String> customSerialNumbers = nonSerializeSerials.stream().map(FofoNonSerializeSerial::getSerialNumber).collect(Collectors.toList());
            LOGGER.info("nonSerializeSerials {}", nonSerializeSerials);
            if (!serialNumbers.isEmpty()) {
                customFofoOrderItem.setDescription(
                        customFofoOrderItem.getDescription() + "\n IMEIS - " + String.join(", ", serialNumbers));
            }
            if (!customSerialNumbers.isEmpty()) {
                customFofoOrderItem.setDescription(
                        customFofoOrderItem.getDescription() + "\n SerialNumber - " + String.join(", ", customSerialNumbers));
            }
            customFofoOrderItem.setQuantity(fofoOrderItem.getQuantity());
            customFofoOrderItem.setHsnCode(fofoOrderItem.getHsnCode());

            // Check if this is a margin scheme item
            boolean isMarginItem = false;
            try {
                Item item = itemRepository.selectById(fofoOrderItem.getItemId());
                Category category = categoryRepository.selectById(item.getCategoryId());
                isMarginItem = category.isMarginOnly() && !serialNumbers.isEmpty();
            } catch (Exception e) {
                LOGGER.warn("Could not check margin scheme for fofo order item {}", fofoOrderItem.getId(), e);
            }

            if (isMarginItem) {
                // Margin Scheme: GST on margin only
                // Purchase price = what FOFO paid (from fofo.inventory_item.unitPrice)
                float purchasePrice = getFofoPurchasePrice(serialNumbers.iterator().next(), fofoOrder.getFofoId());
                float sellingPrice = fofoOrderItem.getSellingPrice();
                float margin = Math.max(0, sellingPrice - purchasePrice);
                float taxableMargin = margin / (1 + totalTaxRate / 100);

                customFofoOrderItem.setMarginScheme(true);
                customFofoOrderItem.setPurchasePrice(purchasePrice);
                customFofoOrderItem.setSellingPrice(sellingPrice);
                customFofoOrderItem.setMargin(margin);
                customFofoOrderItem.setRate(sellingPrice);
                customFofoOrderItem.setDiscount(0);
                customFofoOrderItem.setAmount(taxableMargin * fofoOrderItem.getQuantity());
                customFofoOrderItem.setNetAmount(fofoOrderItem.getSellingPrice() * fofoOrderItem.getQuantity());

                float igstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getIgstRate()) / 100;
                float cgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getCgstRate()) / 100;
                float sgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getSgstRate()) / 100;
                customFofoOrderItem.setIgstRate(fofoOrderItem.getIgstRate());
                customFofoOrderItem.setIgstAmount(igstAmount);
                customFofoOrderItem.setCgstRate(fofoOrderItem.getCgstRate());
                customFofoOrderItem.setCgstAmount(cgstAmount);
                customFofoOrderItem.setSgstRate(fofoOrderItem.getSgstRate());
                customFofoOrderItem.setSgstAmount(sgstAmount);

                marginSchemeFofoItems.add(customFofoOrderItem);
                hasMarginSchemeItems = true;
            } else {
                // Regular: GST on full selling price
                float taxableSellingPrice = (fofoOrderItem.getSellingPrice() + discount) / (1 + totalTaxRate / 100);
                float taxableDiscountPrice = discount / (1 + totalTaxRate / 100);

                customFofoOrderItem.setAmount(fofoOrderItem.getQuantity() * (taxableSellingPrice - taxableDiscountPrice));
                customFofoOrderItem.setRate(taxableSellingPrice);
                customFofoOrderItem.setDiscount(taxableDiscountPrice);
                customFofoOrderItem.setNetAmount(fofoOrderItem.getSellingPrice() * fofoOrderItem.getQuantity());

                float igstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getIgstRate()) / 100;
                float cgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getCgstRate()) / 100;
                float sgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getSgstRate()) / 100;
                customFofoOrderItem.setIgstRate(fofoOrderItem.getIgstRate());
                customFofoOrderItem.setIgstAmount(igstAmount);
                customFofoOrderItem.setCgstRate(fofoOrderItem.getCgstRate());
                customFofoOrderItem.setCgstAmount(cgstAmount);
                customFofoOrderItem.setSgstRate(fofoOrderItem.getSgstRate());
                customFofoOrderItem.setSgstAmount(sgstAmount);

                regularFofoItems.add(customFofoOrderItem);
            }
        }
        // Regular items first, then margin scheme items
        List<CustomOrderItem> customerFofoOrderItems = new ArrayList<>(regularFofoItems);
        customerFofoOrderItems.addAll(marginSchemeFofoItems);
        pdfModel.setOrderItems(customerFofoOrderItems);
        pdfModel.setHasMarginSchemeItems(hasMarginSchemeItems);
        if (hasMarginSchemeItems) {
            List<String> declarations = new ArrayList<>();
            declarations.add("Items marked under Margin Scheme are taxed under Rule 32(5) of CGST Rules, 2017.");
            declarations.add("GST is charged on the margin (Selling Price - Purchase Price) and not on the full value of supply.");
            declarations.add("Input Tax Credit is not available on margin scheme items.");
            pdfModel.setMarginSchemeDeclarations(declarations);
        }
        String customerAddressStateCode = "";
        String partnerAddressStateCode = stateRepository.selectByName(pdfModel.getRetailer().getAddress().getState()).getCode();
        if (pdfModel.getCustomer() != null && pdfModel.getCustomer().getAddress() != null &&
                pdfModel.getCustomer().getAddress().getState() != null &&
                !pdfModel.getCustomer().getAddress().getState().trim().isEmpty()) {
            customerAddressStateCode = stateRepository.selectByName(pdfModel.getCustomer().getAddress().getState()).getCode();
        }

        pdfModel.setPartnerAddressStateCode(partnerAddressStateCode);
        if (!customerAddressStateCode.equals("")) {
            pdfModel.setCustomerAddressStateCode(customerAddressStateCode);
        }
        pdfModel.setCancelled(fofoOrder.getCancelledTimestamp() != null);
        List<String> tncs = new ArrayList<>();
        tncs.add("I agree that goods received are in good working condition.");
        tncs.add("Goods once sold cannot be exchanged or taken back.");
        tncs.add("Warranty for the goods received by me is the responsibility of the manufacturer only.");
        if (pdfModel.getInsurancePolicies() != null && pdfModel.getInsurancePolicies().size() > 0) {
            tncs.add("Extended Warranty/ Damage Protection related issues are to be handled directly by the respective providers.");
        }
        pdfModel.setTncs(tncs);
        return pdfModel;

    }

    private float getFofoPurchasePrice(String serialNumber, int fofoId) {
        try {
            InventoryItem fofoInventoryItem = inventoryItemRepository.selectBySerialNumberFofoId(serialNumber, fofoId);
            if (fofoInventoryItem != null) {
                return fofoInventoryItem.getUnitPrice();
            }
        } catch (Exception e) {
            LOGGER.error("Could not fetch FOFO purchase price for serial: {}, fofoId: {}", serialNumber, fofoId, e);
        }
        return 0;
    }

    private CustomCustomer getCustomCustomer(FofoOrder fofoOrder, CustomAddress retailerAddress) throws
            ProfitMandiBusinessException {
        Customer customer = customerRepository.selectById(fofoOrder.getCustomerId());
        CustomCustomer customCustomer = new CustomCustomer();
        customCustomer.setFirstName(customer.getFirstName());
        customCustomer.setLastName(customer.getLastName());
        customCustomer.setEmailId(customer.getEmailId());
        customCustomer.setMobileNumber(customer.getMobileNumber());
        customCustomer.setGstNumber(fofoOrder.getCustomerGstNumber());
        if (fofoOrder.getCustomerAddressId() != 0) {
            CustomerAddress customerAddress = customerAddressRepository.selectById(fofoOrder.getCustomerAddressId());
            customCustomer.setAddress(this.createCustomAddress(customerAddress));
        } else {

            customCustomer.setAddress(this.createCustomAddressWithoutId(customCustomer, retailerAddress));
        }
        return customCustomer;

    }

    @Override
    public InvoicePdfModel getInvoicePdfModel(int fofoId, int orderId) throws ProfitMandiBusinessException {
        FofoOrder fofoOrder = fofoOrderRepository.selectByFofoIdAndOrderId(fofoId, orderId);
        return this.getInvoicePdfModel(fofoOrder);
    }

    public String getBillingAddress(CustomerAddress customerAddress) {
        StringBuilder address = new StringBuilder();
        if ((customerAddress.getLine1() != null) && (!customerAddress.getLine1().isEmpty())) {
            address.append(customerAddress.getLine1());
            address.append(", ");
        }

        if ((customerAddress.getLine2() != null) && (!customerAddress.getLine2().isEmpty())) {
            address.append(customerAddress.getLine2());
            address.append(", ");
        }

        if ((customerAddress.getLandmark() != null) && (!customerAddress.getLandmark().isEmpty())) {
            address.append(customerAddress.getLandmark());
            address.append(", ");
        }

        if ((customerAddress.getCity() != null) && (!customerAddress.getCity().isEmpty())) {
            address.append(customerAddress.getCity());
            address.append(", ");
        }

        if ((customerAddress.getState() != null) && (!customerAddress.getState().isEmpty())) {
            address.append(customerAddress.getState());
        }

        if ((customerAddress.getPinCode() != null) && (!customerAddress.getPinCode().isEmpty())) {
            address.append("- ");
            address.append(customerAddress.getPinCode());
        }

        return address.toString();
    }

    @Override
    public List<CartFofo> cartCheckout(String cartJson) throws ProfitMandiBusinessException {
        try {
            JSONObject cartObject = new JSONObject(cartJson);
            Iterator<?> keys = cartObject.keys();

            Set<Integer> itemIds = new HashSet<>();
            List<CartFofo> cartItems = new ArrayList<CartFofo>();

            while (keys.hasNext()) {
                String key = (String) keys.next();
                if (cartObject.get(key) instanceof JSONObject) {
                    LOGGER.info(cartObject.get(key).toString());
                }
                CartFofo cf = new CartFofo();
                cf.setItemId(cartObject.getJSONObject(key).getInt("itemId"));
                cf.setQuantity(cartObject.getJSONObject(key).getInt("quantity"));
                if (cartObject.getJSONObject(key).has("poId")) {

                    cf.setPoId(cartObject.getJSONObject(key).getInt("poId"));
                    cf.setPoItemId(cartObject.getJSONObject(key).getInt("poItemId"));
                }
                if (cf.getQuantity() <= 0) {
                    continue;
                }
                cartItems.add(cf);
                itemIds.add(cartObject.getJSONObject(key).getInt("itemId"));
            }
            Map<Integer, Item> itemMap = new HashMap<Integer, Item>();
            if (itemIds.size() > 0) {
                List<Item> items = itemRepository.selectByIds(itemIds);
                for (Item i : items) {
                    itemMap.put(i.getId(), i);
                }

            }
            for (CartFofo cf : cartItems) {
                Item i = itemMap.get(cf.getItemId());
                if (i == null) {
                    continue;
                }
                cf.setDisplayName(getValidName(i.getBrand()) + " " + getValidName(i.getModelName()) + " " + getValidName(i.getModelNumber()) + " " + getValidName(i.getColor()).replaceAll("\\s+", " "));
                cf.setItemType(i.getType());
            }
            return cartItems;
        } catch (Exception e) {
            LOGGER.error("Unable to Prepare cart to place order...", e);
            throw new ProfitMandiBusinessException("cartData", cartJson, "FFORDR_1006");
        }
    }

    @Override
    public Map<String, Object> getSaleHistory(int fofoId, SearchType searchType, String searchValue, LocalDateTime
            startDate, LocalDateTime endDate, int offset, int limit) throws ProfitMandiBusinessException {
        long countItems = 0;
        List<FofoOrder> fofoOrders = new ArrayList<>();

        if (searchType == SearchType.CUSTOMER_MOBILE_NUMBER && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndCustomerMobileNumber(fofoId, searchValue, null, null, offset, limit);
            countItems = fofoOrderRepository.selectCountByCustomerMobileNumber(fofoId, searchValue, null, null);
        } else if (searchType == SearchType.CUSTOMER_NAME && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndCustomerName(fofoId, searchValue, null, null, offset, limit);
            countItems = fofoOrderRepository.selectCountByCustomerName(fofoId, searchValue, null, null);
        } else if (searchType == SearchType.IMEI && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndSerialNumber(fofoId, searchValue, null, null, offset, limit);
            countItems = fofoOrderRepository.selectCountBySerialNumber(fofoId, searchValue, null, null);
        } else if (searchType == SearchType.ITEM_NAME && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndItemName(fofoId, searchValue, null, null, offset, limit);
            countItems = fofoOrderRepository.selectCountByItemName(fofoId, searchValue, null, null);
        } else if (searchType == SearchType.INVOICE_NUMBER && !searchValue.isEmpty()) {
            fofoOrders = Arrays.asList(fofoOrderRepository.selectByFofoIdAndInvoiceNumber(fofoId, searchValue));
            countItems = fofoOrders.size();
        } else if (searchType == SearchType.DATE_RANGE) {
            fofoOrders = fofoOrderRepository.selectByFofoId(fofoId, startDate, endDate, offset, limit);
            countItems = fofoOrderRepository.selectCountByFofoId(fofoId, startDate, endDate);
        }
        Map<String, Object> map = new HashMap<>();

        map.put("saleHistories", fofoOrders);
        map.put("start", offset + 1);
        map.put("size", countItems);
        map.put("searchType", searchType);
        map.put("searchTypes", SearchType.values());
        map.put("startDate", startDate);
        map.put("searchValue", searchValue);
        map.put(ProfitMandiConstants.END_TIME, endDate);
        if (fofoOrders.size() < limit) {
            map.put("end", offset + fofoOrders.size());
        } else {
            map.put("end", offset + limit);
        }
        return map;
    }

    public ResponseEntity<?> downloadReportInCsv(org.apache.commons.io.output.ByteArrayOutputStream
                                                         baos, List<List<?>> rows, String fileName) {
        final HttpHeaders headers = new HttpHeaders();
        headers.set("Content-Type", "text/csv");

        headers.set("Content-disposition", "inline; filename=" + fileName + ".csv");
        headers.setContentLength(baos.toByteArray().length);

        final InputStream inputStream = new ByteArrayInputStream(baos.toByteArray());
        final InputStreamResource inputStreamResource = new InputStreamResource(inputStream);

        return new ResponseEntity<>(inputStreamResource, headers, HttpStatus.OK);
    }

    @Override
    public Map<String, Object> getSaleHistoryPaginated(int fofoId, SearchType searchType, String
            searchValue, LocalDateTime startDate, LocalDateTime endDate, int offset, int limit) throws
            ProfitMandiBusinessException {
        List<FofoOrder> fofoOrders = new ArrayList<>();

        if (searchType == SearchType.CUSTOMER_MOBILE_NUMBER && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndCustomerMobileNumber(fofoId, searchValue, startDate, endDate, offset, limit);
        } else if (searchType == SearchType.CUSTOMER_NAME && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndCustomerName(fofoId, searchValue, startDate, endDate, offset, limit);
        } else if (searchType == SearchType.IMEI && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndSerialNumber(fofoId, searchValue, startDate, endDate, offset, limit);
        } else if (searchType == SearchType.ITEM_NAME && !searchValue.isEmpty()) {
            fofoOrders = fofoOrderRepository.selectByFofoIdAndItemName(fofoId, searchValue, startDate, endDate, offset, limit);

        } else if (searchType == SearchType.DATE_RANGE) {
            fofoOrders = fofoOrderRepository.selectByFofoId(fofoId, startDate, endDate, offset, limit);
        }
        Map<String, Object> map = new HashMap<>();
        map.put("saleHistories", fofoOrders);
        map.put("searchType", searchType);
        map.put("searchTypes", SearchType.values());
        map.put("startDate", startDate);
        map.put("searchValue", searchValue);
        map.put(ProfitMandiConstants.END_TIME, endDate);
        return map;
    }

    private String getFofoStoreCode(int fofoId) throws ProfitMandiBusinessException {
        FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);
        return fofoStore.getCode();
    }

    private String getValidName(String name) {
        return name != null ? name : "";
    }

    private Set<String> toSerialNumbers(Set<FofoLineItem> fofoLineItems) {
        Set<String> serialNumbers = new HashSet<>();
        for (FofoLineItem fofoLineItem : fofoLineItems) {
            if (fofoLineItem.getSerialNumber() != null && !fofoLineItem.getSerialNumber().isEmpty()) {
                serialNumbers.add(fofoLineItem.getSerialNumber());
            }
        }
        return serialNumbers;
    }

    static final List<String> MOP_VOILATED_BRANDS = Arrays.asList("Live Demo", "Almost New");

    // DP price validation disabled as of 11 sep 2025 as per tarun sir
    private void validateDpPrice(int fofoId, Map<
            Integer, PriceModel> itemIdMopPriceMap, Map<Integer, CustomFofoOrderItem> itemIdCustomFofoLineItemMap) throws
            ProfitMandiBusinessException {
    }

    // MOP price validation disabled as of 11 sep 2025 as per tarun sir
    private void validateMopPrice(int fofoId, Map<
            Integer, PriceModel> itemIdMopPriceMap, Map<Integer, CustomFofoOrderItem> itemIdCustomFofoLineItemMap) throws
            ProfitMandiBusinessException {
    }

    private void updateInventoryItemsAndScanRecord(Set<InventoryItem> inventoryItems, int fofoId, Map<
            Integer, Integer> inventoryItemQuantityUsed, int fofoOrderId) {
        for (InventoryItem inventoryItem : inventoryItems) {
            inventoryItem.setLastScanType(ScanType.SALE);
            inventoryItem.setUpdateTimestamp(LocalDateTime.now());
            ScanRecord scanRecord = new ScanRecord();
            scanRecord.setInventoryItemId(inventoryItem.getId());
            scanRecord.setFofoId(fofoId);
            scanRecord.setOrderId(fofoOrderId);
            // correct this
            scanRecord.setQuantity(inventoryItemQuantityUsed.get(inventoryItem.getId()));
            scanRecord.setType(ScanType.SALE);
            scanRecordRepository.persist(scanRecord);
            purchaseReturnItemRepository.deleteById(inventoryItem.getId());

        }
    }

    private void createFofoLineItem(int fofoOrderItemId, Set<
            InventoryItem> inventoryItems, Map<Integer, Integer> inventoryItemIdQuantityUsed) {
        for (InventoryItem inventoryItem : inventoryItems) {
            FofoLineItem fofoLineItem = new FofoLineItem();
            fofoLineItem.setFofoOrderItemId(fofoOrderItemId);
            fofoLineItem.setSerialNumber(inventoryItem.getSerialNumber());
            fofoLineItem.setInventoryItemId(inventoryItem.getId());
            fofoLineItem.setQuantity(inventoryItemIdQuantityUsed.get(inventoryItem.getId()));
            fofoLineItemRepository.persist(fofoLineItem);
        }
    }

    private FofoOrderItem createAndGetFofoOrderItem(CustomFofoOrderItem customFofoOrderItem, int fofoOrderId, Map<
            Integer, Item> itemMap, Set<InventoryItem> inventoryItems, Map<Integer, TagListing> tagListingMap,
            Map<Integer, GstRate> gstRateMap) throws ProfitMandiBusinessException {
        FofoOrderItem fofoOrderItem = new FofoOrderItem();
        fofoOrderItem.setItemId(customFofoOrderItem.getItemId());
        fofoOrderItem.setQuantity(customFofoOrderItem.getQuantity());
        fofoOrderItem.setSellingPrice(customFofoOrderItem.getSellingPrice());
        fofoOrderItem.setPendingOrderItemId(customFofoOrderItem.getPoiId());
        fofoOrderItem.setOrderId(fofoOrderId);

        // N+1 fix: Use pre-fetched tagListingMap instead of querying per item
        TagListing tl = tagListingMap.get(customFofoOrderItem.getItemId());
        // In case listing gets removed rebill it using the selling price
        if (tl != null) {
            fofoOrderItem.setDp(tl.getSellingPrice());
            fofoOrderItem.setMop(tl.getMop());
        } else {
            fofoOrderItem.setDp(customFofoOrderItem.getSellingPrice());
            fofoOrderItem.setMop(customFofoOrderItem.getSellingPrice());
        }
        fofoOrderItem.setDiscount(customFofoOrderItem.getDiscountAmount());

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

        // Use first inventory item to get HSN code and GST rates
        InventoryItem firstInventoryItem = inventoryItems.iterator().next();
        GstRate gstRate = gstRateMap.get(firstInventoryItem.getItemId());
        if (gstRate != null) {
            fofoOrderItem.setIgstRate(gstRate.getIgstRate());
            fofoOrderItem.setCgstRate(gstRate.getCgstRate());
            fofoOrderItem.setSgstRate(gstRate.getSgstRate());
        }
        fofoOrderItem.setHsnCode(firstInventoryItem.getHsnCode());
        fofoOrderItem.setBrand(item.getBrand());
        fofoOrderItem.setModelName(item.getModelName());
        fofoOrderItem.setModelNumber(item.getModelNumber());
        fofoOrderItem.setColor(item.getColor());
        fofoOrderItemRepository.persist(fofoOrderItem);
        return fofoOrderItem;
    }

    private FofoOrderItem getDummyFofoOrderItem(int itemId, int fofoOrderId, String serialNumber, Integer stateId) throws
            ProfitMandiBusinessException {
        Item item = itemRepository.selectById(itemId);
        TagListing tl = tagListingRepository.selectByItemId(itemId);
        FofoOrderItem fofoOrderItem = new FofoOrderItem();
        fofoOrderItem.setItemId(itemId);
        fofoOrderItem.setQuantity(1);
        fofoOrderItem.setSellingPrice(tl.getMop());
        fofoOrderItem.setOrderId(fofoOrderId);
        // In case listing gets removed rebill it using the selling price
        fofoOrderItem.setDp(tl.getSellingPrice());
        fofoOrderItem.setMop(tl.getMop());
        fofoOrderItem.setDiscount(0);

        Map<Integer, GstRate> itemIdStateTaxRateMap = null;
        if (stateId != null) {
            itemIdStateTaxRateMap = stateGstRateRepository.getStateTaxRate(Arrays.asList(itemId), stateId);
        } else {
            itemIdStateTaxRateMap = stateGstRateRepository.getIgstTaxRate(Arrays.asList(itemId));
        }

        fofoOrderItem.setIgstRate(itemIdStateTaxRateMap.get(itemId).getIgstRate());

        fofoOrderItem.setCgstRate(itemIdStateTaxRateMap.get(itemId).getCgstRate());
        fofoOrderItem.setSgstRate(itemIdStateTaxRateMap.get(itemId).getSgstRate());


        fofoOrderItem.setHsnCode(item.getHsnCode());
        fofoOrderItem.setBrand(item.getBrand());
        fofoOrderItem.setModelName(item.getModelName());
        fofoOrderItem.setModelNumber(item.getModelNumber());
        fofoOrderItem.setColor(item.getColor());

        Set<FofoLineItem> fofoLineItems = new HashSet<>();
        FofoLineItem fli = new FofoLineItem();
        fli.setQuantity(1);
        fli.setSerialNumber(serialNumber);
        fofoLineItems.add(fli);
        fofoOrderItem.setFofoLineItems(fofoLineItems);

        return fofoOrderItem;
    }

    private void updateCurrentInventorySnapshot(List<CurrentInventorySnapshot> currentInventorySnapshots,
                                                int fofoId, int itemId, int quantity) throws ProfitMandiBusinessException {
        for (CurrentInventorySnapshot currentInventorySnapshot : currentInventorySnapshots) {
            if (currentInventorySnapshot.getItemId() == itemId && currentInventorySnapshot.getFofoId() == fofoId) {
                currentInventorySnapshotRepository.updateAvailabilityByItemIdAndFofoId(itemId, fofoId, currentInventorySnapshot.getAvailability() - quantity);
            }
        }
    }

    private void createPaymentOptions(FofoOrder fofoOrder, Set<CustomPaymentOption> customPaymentOptions) throws
            ProfitMandiBusinessException {
        for (CustomPaymentOption customPaymentOption : customPaymentOptions) {
            if (customPaymentOption.getAmount() > 0) {
                PaymentOptionTransaction paymentOptionTransaction = new PaymentOptionTransaction();
                paymentOptionTransaction.setReferenceId(fofoOrder.getId());
                paymentOptionTransaction.setPaymentOptionId(customPaymentOption.getPaymentOptionId());
                paymentOptionTransaction.setReferenceType(PaymentOptionReferenceType.ORDER);
                paymentOptionTransaction.setAmount(customPaymentOption.getAmount());
                paymentOptionTransaction.setFofoId(fofoOrder.getFofoId());
                paymentOptionTransactionRepository.persist(paymentOptionTransaction);
            }
        }
    }

    private FofoOrder createAndGetFofoOrder(int customerId, String customerGstNumber, int fofoId, String
            documentNumber, float totalAmount, int customerAddressId, int poId) {
        // Idempotency: concurrent duplicate requests (double-click, upstream retries) used
        // to deadlock on idx_invoice_number. Fast path — if the row already exists for
        // this (fofo, invoice) return it instead of attempting a duplicate insert.
        FofoOrder existing = findExistingFofoOrder(fofoId, documentNumber);
        if (existing != null) return existing;

        FofoOrder fofoOrder = new FofoOrder();
        fofoOrder.setCustomerGstNumber(customerGstNumber);
        fofoOrder.setCustomerId(customerId);
        fofoOrder.setFofoId(fofoId);
        fofoOrder.setPendingOrderId(poId);
        fofoOrder.setInvoiceNumber(documentNumber);
        fofoOrder.setTotalAmount(totalAmount);
        fofoOrder.setCustomerAddressId(customerAddressId);
        try {
            fofoOrderRepository.persist(fofoOrder);
        } catch (org.springframework.dao.DataIntegrityViolationException dup) {
            // Narrow race: another concurrent request won the insert after our select.
            // Re-fetch and return the winner's row. Requires the uk_fofo_order_fofo_invoice
            // unique key on fofo_order to surface the duplicate as this exception.
            FofoOrder winner = findExistingFofoOrder(fofoId, documentNumber);
            if (winner != null) return winner;
            throw dup;
        }
        return fofoOrder;
    }

    private FofoOrder findExistingFofoOrder(int fofoId, String invoiceNumber) {
        try {
            return fofoOrderRepository.selectByFofoIdAndInvoiceNumber(fofoId, invoiceNumber);
        } catch (ProfitMandiBusinessException ignored) {
            return null;
        }
    }

    private void validateItemsSerializedNonSerialized
            (List<Item> items, Map<Integer, CustomFofoOrderItem> customFofoOrderItemMap) throws
            ProfitMandiBusinessException {
        List<Integer> invalidItemIdSerialNumbers = new ArrayList<Integer>();
        List<Integer> itemIdNonSerializedSerialNumbers = new ArrayList<Integer>();
        for (Item i : items) {
            CustomFofoOrderItem customFofoOrderItem = customFofoOrderItemMap.get(i.getId());
            if (i.getType().equals(ItemType.SERIALIZED)) {
                if (customFofoOrderItem == null || customFofoOrderItem.getSerialNumberDetails().isEmpty()) {
                    invalidItemIdSerialNumbers.add(i.getId());
                }
            } else {
                Set<String> serialNumbers = this.serialNumberDetailsToSerialNumbers(customFofoOrderItem.getSerialNumberDetails());
                if (customFofoOrderItem == null || !serialNumbers.isEmpty()) {
                    itemIdNonSerializedSerialNumbers.add(i.getId());
                }
            }
        }

        if (!invalidItemIdSerialNumbers.isEmpty()) {
            LOGGER.error("Invalid itemId's serialNumbers for serialized{}", invalidItemIdSerialNumbers);
            // itemId's are serialized you are saying these are not serialized
            throw new ProfitMandiBusinessException("invalidItemIdSerialNumbers", invalidItemIdSerialNumbers, "FFORDR_1013");
        }

        if (!itemIdNonSerializedSerialNumbers.isEmpty()) {
            LOGGER.error("Invalid itemId's serialNumbers for non serialized{}", itemIdNonSerializedSerialNumbers);
            // itemId's are non serialized you are saying these are serialized
            throw new ProfitMandiBusinessException("itemIdNonSerializedSerialNumbers", itemIdNonSerializedSerialNumbers, "FFORDR_1014");
        }
    }

    private void validateCurrentInventorySnapshotQuantities
            (List<CurrentInventorySnapshot> currentInventorySnapshots, Map<Integer, CustomFofoOrderItem> itemIdCustomFofoOrderItemMap) throws
            ProfitMandiBusinessException {
        if (itemIdCustomFofoOrderItemMap.keySet().size() != currentInventorySnapshots.size()) {
            throw new ProfitMandiBusinessException("quantiiesSize", currentInventorySnapshots.size(), "");
        }
        List<ItemIdQuantityAvailability> itemIdQuantityAvailabilities = new ArrayList<>(); // this is for error
        LOGGER.info("currentInventorySnapshots " + currentInventorySnapshots);
        LOGGER.info("CustomFofoLineItemMap {}", itemIdCustomFofoOrderItemMap);
        for (CurrentInventorySnapshot currentInventorySnapshot : currentInventorySnapshots) {
            CustomFofoOrderItem customFofoOrderItem = itemIdCustomFofoOrderItemMap.get(currentInventorySnapshot.getItemId());
            LOGGER.info("customFofoOrderItem {}", customFofoOrderItem);
            if (customFofoOrderItem.getQuantity() > currentInventorySnapshot.getAvailability()) {
                ItemIdQuantityAvailability itemIdQuantityAvailability = new ItemIdQuantityAvailability();
                itemIdQuantityAvailability.setItemId(customFofoOrderItem.getItemId());
                Quantity quantity = new Quantity();
                quantity.setAvailable(currentInventorySnapshot.getAvailability());
                quantity.setRequested(customFofoOrderItem.getQuantity());
                itemIdQuantityAvailability.setQuantity(quantity);
                itemIdQuantityAvailabilities.add(itemIdQuantityAvailability);
            }
        }

        if (!itemIdQuantityAvailabilities.isEmpty()) {
            // itemIdQuantity request is not valid
            LOGGER.error("Requested quantities should not be greater than currently available quantities {}", itemIdQuantityAvailabilities);
            throw new ProfitMandiBusinessException("itemIdQuantityAvailabilities", itemIdQuantityAvailabilities, "FFORDR_1015");
        }
    }

    private int getItemIdFromSerialNumber(Map<Integer, CustomFofoOrderItem> itemIdCustomFofoOrderItemMap, String
            serialNumber) {
        int itemId = 0;
        for (Map.Entry<Integer, CustomFofoOrderItem> entry : itemIdCustomFofoOrderItemMap.entrySet()) {
            Set<SerialNumberDetail> serialNumberDetails = entry.getValue().getSerialNumberDetails();
            for (SerialNumberDetail serialNumberDetail : serialNumberDetails) {
                if (serialNumberDetail.getSerialNumber().equals(serialNumber)) {
                    itemId = entry.getKey();
                    break;
                }
            }
        }
        return itemId;
    }

    private Map<Integer, Item> toItemMap(List<Item> items) {
        Function<Item, Integer> itemIdFunction = new Function<Item, Integer>() {
            @Override
            public Integer apply(Item item) {
                return item.getId();
            }
        };
        Function<Item, Item> itemFunction = new Function<Item, Item>() {
            @Override
            public Item apply(Item item) {
                return item;
            }
        };
        return items.stream().collect(Collectors.toMap(itemIdFunction, itemFunction));
    }

    private void setCustomerAddress(CustomerAddress customerAddress, CustomAddress customAddress) {
        customerAddress.setName(customAddress.getName());
        customerAddress.setLastName(customAddress.getLastName());
        customerAddress.setLine1(customAddress.getLine1());
        customerAddress.setLine2(customAddress.getLine2());
        customerAddress.setLandmark(customAddress.getLandmark());
        customerAddress.setCity(customAddress.getCity());
        customerAddress.setPinCode(customAddress.getPinCode());
        customerAddress.setState(customAddress.getState());
        customerAddress.setCountry(customAddress.getCountry());
        customerAddress.setPhoneNumber(customAddress.getPhoneNumber());
    }

    private CustomAddress createCustomAddress(Address address) {
        CustomAddress customAddress = new CustomAddress();
        customAddress.setName(address.getName());
        customAddress.setLine1(address.getLine1());
        customAddress.setLine2(address.getLine2());
        customAddress.setLandmark(address.getLandmark());
        customAddress.setCity(address.getCity());
        customAddress.setPinCode(address.getPinCode());
        customAddress.setState(address.getState());
        customAddress.setCountry(address.getCountry());
        customAddress.setPhoneNumber(address.getPhoneNumber());
        return customAddress;
    }

    private CustomAddress createCustomAddress(CustomerAddress customerAddress) {
        CustomAddress customAddress = new CustomAddress();
        customAddress.setName(customerAddress.getName());
        customAddress.setLastName(customerAddress.getLastName());
        customAddress.setLine1(customerAddress.getLine1());
        customAddress.setLine2(customerAddress.getLine2());
        customAddress.setLandmark(customerAddress.getLandmark());
        customAddress.setCity(customerAddress.getCity());
        customAddress.setPinCode(customerAddress.getPinCode());
        customAddress.setState(customerAddress.getState());
        customAddress.setCountry(customerAddress.getCountry());
        customAddress.setPhoneNumber(customerAddress.getPhoneNumber());
        return customAddress;
    }

    private CustomAddress createCustomAddressWithoutId(CustomCustomer customerAddress, CustomAddress
            retailerAddress) {
        CustomAddress customAddress = new CustomAddress();
        customAddress.setName(customerAddress.getFirstName());
        customAddress.setLastName(customerAddress.getLastName());
        customAddress.setLine1("");
        customAddress.setLine2("");
        customAddress.setLandmark("");
        customAddress.setCity(retailerAddress.getCity());
        customAddress.setPinCode(retailerAddress.getPinCode());
        customAddress.setState(retailerAddress.getState());
        customAddress.setCountry("");
        customAddress.setPhoneNumber(customerAddress.getMobileNumber());
        return customAddress;
    }

    private void validatePaymentOptionsAndTotalAmount(Set<CustomPaymentOption> customPaymentOptions,
                                                      float totalAmount) throws ProfitMandiBusinessException {
        Set<Integer> paymentOptionIds = new HashSet<>();

        float calculatedAmount = 0;
        for (CustomPaymentOption customPaymentOption : customPaymentOptions) {
            paymentOptionIds.add(customPaymentOption.getPaymentOptionId());
            calculatedAmount = calculatedAmount + customPaymentOption.getAmount();
        }
        if (calculatedAmount != totalAmount) {
            LOGGER.warn("Error occured while validating payment options amount - {} != TotalAmount {}", calculatedAmount, totalAmount);
            throw new ProfitMandiBusinessException(ProfitMandiConstants.PAYMENT_OPTION_CALCULATED_AMOUNT, calculatedAmount, "FFORDR_1016");
        }

        List<Integer> foundPaymentOptionIds = paymentOptionRepository.selectIdsByIds(paymentOptionIds);
        if (foundPaymentOptionIds.size() != paymentOptionIds.size()) {
            paymentOptionIds.removeAll(foundPaymentOptionIds);
            throw new ProfitMandiBusinessException(ProfitMandiConstants.PAYMENT_OPTION_ID, paymentOptionIds, "FFORDR_1017");
        }
    }

    @Override
    public List<FofoOrderItem> getByOrderId(int orderId) throws ProfitMandiBusinessException {
        List<FofoOrderItem> fofoOrderItems = fofoOrderItemRepository.selectByOrderId(orderId);
        if (!fofoOrderItems.isEmpty()) {
            List<FofoOrderItem> newFofoOrderItems = new ArrayList<>();
            Map<Integer, Set<FofoLineItem>> fofoOrderItemIdFofoLineItemsMap = this.toFofoOrderItemIdFofoLineItems(fofoOrderItems);
            Iterator<FofoOrderItem> fofoOrderItemsIterator = fofoOrderItems.iterator();
            while (fofoOrderItemsIterator.hasNext()) {
                FofoOrderItem fofoOrderItem = fofoOrderItemsIterator.next();
                fofoOrderItem.setFofoLineItems(fofoOrderItemIdFofoLineItemsMap.get(fofoOrderItem.getId()));
                newFofoOrderItems.add(fofoOrderItem);
                fofoOrderItemsIterator.remove();
            }
            fofoOrderItems = newFofoOrderItems;
        }
        return fofoOrderItems;
    }

    private Set<Integer> toFofoOrderItemIds(List<FofoOrderItem> fofoOrderItems) {
        Function<FofoOrderItem, Integer> fofoOrderItemToFofoOrderItemIdFunction = new Function<FofoOrderItem, Integer>() {
            @Override
            public Integer apply(FofoOrderItem fofoOrderItem) {
                return fofoOrderItem.getId();
            }
        };
        return fofoOrderItems.stream().map(fofoOrderItemToFofoOrderItemIdFunction).collect(Collectors.toSet());
    }

    private Map<Integer, Set<FofoLineItem>> toFofoOrderItemIdFofoLineItems(List<FofoOrderItem> fofoOrderItems) throws
            ProfitMandiBusinessException {
        Set<Integer> fofoOrderItemIds = this.toFofoOrderItemIds(fofoOrderItems);
        List<FofoLineItem> fofoLineItems = fofoLineItemRepository.selectByFofoOrderItemIds(fofoOrderItemIds);
        Map<Integer, Set<FofoLineItem>> fofoOrderItemIdFofoLineItemsMap = new HashMap<>();
        for (FofoLineItem fofoLineItem : fofoLineItems) {
            if (!fofoOrderItemIdFofoLineItemsMap.containsKey(fofoLineItem.getFofoOrderItemId())) {
                Set<FofoLineItem> fofoLineItems2 = new HashSet<>();
                fofoLineItems2.add(fofoLineItem);
                fofoOrderItemIdFofoLineItemsMap.put(fofoLineItem.getFofoOrderItemId(), fofoLineItems2);
            } else {
                fofoOrderItemIdFofoLineItemsMap.get(fofoLineItem.getFofoOrderItemId()).add(fofoLineItem);
            }
        }
        return fofoOrderItemIdFofoLineItemsMap;
    }

    @Override
    public void updateCustomerDetails(CustomCustomer customCustomer, String invoiceNumber) throws
            ProfitMandiBusinessException {
        FofoOrder fofoOrder = fofoOrderRepository.selectByInvoiceNumber(invoiceNumber);
        LOGGER.info("fofoOrder{}", fofoOrder);
        Customer customer = customerRepository.selectById(fofoOrder.getCustomerId());
        LOGGER.info("customer{}", customer);
        customer.setFirstName(customCustomer.getFirstName());
        customer.setLastName(customCustomer.getLastName());
        customer.setMobileNumber(customCustomer.getMobileNumber());
        customer.setEmailId(customCustomer.getEmailId());
        customerRepository.persist(customer);
        if (fofoOrder.getCustomerAddressId() == 0) {
            CustomAddress customAddress = customCustomer.getAddress();

            if (customAddress == null ||
                    isNullOrEmpty(customAddress.getName()) ||
                    isNullOrEmpty(customAddress.getLastName()) ||
                    isNullOrEmpty(customAddress.getLine1()) ||
                    isNullOrEmpty(customAddress.getCity()) ||
                    isNullOrEmpty(customAddress.getPinCode()) ||
                    isNullOrEmpty(customAddress.getState()) ||
//                    isNullOrEmpty(customAddress.getCountry()) ||
                    isNullOrEmpty(customAddress.getPhoneNumber())) {
                throw new IllegalArgumentException("Required customer address fields are missing.");
            }

            CustomerAddress customerAddress = new CustomerAddress();
            customerAddress.setCustomerId(fofoOrder.getCustomerId());
            customerAddress.setName(customAddress.getName());
            customerAddress.setLastName(customAddress.getLastName());
            customerAddress.setLine1(customAddress.getLine1());
            customerAddress.setLine2(customAddress.getLine2());
            customerAddress.setLandmark(customAddress.getLandmark());
            customerAddress.setCity(customAddress.getCity());
            customerAddress.setPinCode(customAddress.getPinCode());
            customerAddress.setState(customAddress.getState());
//            customerAddress.setCountry(customAddress.getCountry());
            customerAddress.setPhoneNumber(customAddress.getPhoneNumber());
            customerAddressRepository.persist(customerAddress);
            fofoOrder.setCustomerAddressId(customerAddress.getId());

        }
        CustomerAddress customerAddress = customerAddressRepository.selectById(fofoOrder.getCustomerAddressId());
        if (!customerAddress.getState().equalsIgnoreCase(customCustomer.getAddress().getState())) {
            List<FofoOrderItem> fofoOrderItems = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());
            resetTaxation(fofoOrder.getFofoId(), customerAddress, fofoOrderItems);
        }
        this.setCustomerAddress(customerAddress, customCustomer.getAddress());
        fofoOrder.setCustomerGstNumber(customCustomer.getGstNumber());
    }

    private boolean isNullOrEmpty(String str) {
        return str == null || str.trim().isEmpty();
    }

    private void resetTaxation(int fofoId, CustomerAddress customerAddress, List<FofoOrderItem> fofoOrderItems) throws
            ProfitMandiBusinessException {
        int retailerAddressId = retailerRegisteredAddressRepository.selectAddressIdByRetailerId(fofoId);

        Address retailerAddress = addressRepository.selectById(retailerAddressId);

        Integer stateId = null;
        if (customerAddress.getState().equalsIgnoreCase(retailerAddress.getState())) {
            try {
                stateId = Long.valueOf(stateRepository.selectByName(customerAddress.getState()).getId()).intValue();
            } catch (Exception e) {
                LOGGER.error("Unable to get state rates");
            }
        }
        List<Integer> itemIds = fofoOrderItems.stream().map(x -> x.getItemId()).collect(Collectors.toList());
        final Map<Integer, GstRate> gstRates;
        if (stateId != null) {
            gstRates = stateGstRateRepository.getStateTaxRate(itemIds, stateId);
        } else {
            gstRates = stateGstRateRepository.getIgstTaxRate(itemIds);
        }
        for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
            GstRate rate = gstRates.get(fofoOrderItem.getItemId());
            fofoOrderItem.setCgstRate(rate.getCgstRate());
            fofoOrderItem.setSgstRate(rate.getSgstRate());
            fofoOrderItem.setIgstRate(rate.getIgstRate());
        }
    }
    @Override
    public CustomerCreditNote badReturn(int fofoId, FoiBadReturnRequest foiBadReturnRequest) throws
            ProfitMandiBusinessException {
        return this.badReturn(null, fofoId, foiBadReturnRequest);
    }
    @Override
    public CustomerCreditNote badReturn(String loginMail,int fofoId, FoiBadReturnRequest foiBadReturnRequest) throws
            ProfitMandiBusinessException {
        FofoOrderItem foi = fofoOrderItemRepository.selectById(foiBadReturnRequest.getFofoOrderItemId());
        FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(foi.getOrderId());
        if (fofoOrder.getFofoId() != fofoId) {
            throw new ProfitMandiBusinessException("Partner Auth", "", "Invalid Order");
        }
        int billedQty = foi.getQuantity() - customerReturnItemRepository.selectAllByOrderItemId(foi.getId()).size();
        if (foiBadReturnRequest.getMarkedBadArr().size() > billedQty) {
            throw new ProfitMandiBusinessException("Cant bad return more than what is billed", "", "Invalid Quantity");
        }
        List<CustomerReturnItem> customerReturnItems = new ArrayList<>();
        for (BadReturnRequest badReturnRequest : foiBadReturnRequest.getMarkedBadArr()) {
            CustomerReturnItem customerReturnItem = new CustomerReturnItem();
            customerReturnItem.setFofoId(fofoId);
            customerReturnItem.setFofoOrderItemId(foiBadReturnRequest.getFofoOrderItemId());
            customerReturnItem.setFofoOrderId(fofoOrder.getId());
            customerReturnItem.setRemarks(badReturnRequest.getRemarks());
            customerReturnItem.setInventoryItemId(badReturnRequest.getInventoryItemId());
            customerReturnItem.setQuantity(1);
            customerReturnItem.setType(ReturnType.BAD);
            // customerReturnItemRepository.persist(customerReturnItem);
            inventoryService.saleReturnInventoryItem(customerReturnItem);
            customerReturnItems.add(customerReturnItem);
        }
        CustomerCreditNote creditNote = generateCreditNote(fofoOrder, customerReturnItems);
        for (CustomerReturnItem customerReturnItem : customerReturnItems) {
            purchaseReturnService.returnInventoryItem(fofoId, false, customerReturnItem.getInventoryItemId(), ReturnType.BAD);
        }
        // This should cancel the order
        fofoOrder.setCancelledTimestamp(LocalDateTime.now());
        partnerInvestmentService.evictInvestmentCache(fofoOrder.getFofoId());
        this.reverseScheme(fofoOrder);
        return creditNote;
    }

    private CustomerCreditNote generateCreditNote(FofoOrder
                                                          fofoOrder, List<CustomerReturnItem> customerReturnItems) throws ProfitMandiBusinessException {

        InvoiceNumberGenerationSequence sequence = invoiceNumberGenerationSequenceRepository.selectByFofoId(fofoOrder.getFofoId());
        sequence.setCreditNoteSequence(sequence.getCreditNoteSequence() + 1);
        invoiceNumberGenerationSequenceRepository.persist(sequence);

        String creditNoteNumber = sequence.getPrefix() + "/" + sequence.getCreditNoteSequence();
        CustomerCreditNote creditNote = new CustomerCreditNote();
        creditNote.setCreditNoteNumber(creditNoteNumber);
        creditNote.setFofoId(fofoOrder.getFofoId());
        creditNote.setFofoOrderId(fofoOrder.getId());
        creditNote.setFofoOrderItemId(customerReturnItems.get(0).getFofoOrderItemId());
        creditNote.setSettlementType(SettlementType.UNSETTLED);
        customerCreditNoteRepository.persist(creditNote);

        for (CustomerReturnItem customerReturnItem : customerReturnItems) {
            customerReturnItem.setCreditNoteId(creditNote.getId());
            customerReturnItemRepository.persist(customerReturnItem);
        }
        // this.returnInventoryItems(inventoryItems, debitNote);

        return creditNote;
    }

    @Override
    public CreditNotePdfModel getCreditNotePdfModel(int customerCreditNoteId) throws ProfitMandiBusinessException {
        CustomerCreditNote creditNote = customerCreditNoteRepository.selectById(customerCreditNoteId);
        return getCreditNotePdfModel(creditNote);
    }

    private CreditNotePdfModel getCreditNotePdfModel(CustomerCreditNote creditNote) throws
            ProfitMandiBusinessException {
        FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(creditNote.getFofoOrderId());
        List<CustomerReturnItem> customerReturnItems = customerReturnItemRepository.selectAllByCreditNoteId(creditNote.getId());
        CustomRetailer customRetailer = retailerService.getFofoRetailer(fofoOrder.getFofoId());
        CustomCustomer customCustomer = getCustomCustomer(fofoOrder, customRetailer.getAddress());

        List<CustomOrderItem> customerFofoOrderItems = new ArrayList<>();

        FofoOrderItem fofoOrderItem = fofoOrderItemRepository.selectById(creditNote.getFofoOrderItemId());
        float totalTaxRate = fofoOrderItem.getIgstRate() + fofoOrderItem.getSgstRate() + fofoOrderItem.getCgstRate();

        CustomOrderItem customFofoOrderItem = new CustomOrderItem();
        customFofoOrderItem.setDescription(fofoOrderItem.getBrand() + " " + fofoOrderItem.getModelName() + " " + fofoOrderItem.getModelNumber() + "-" + fofoOrderItem.getColor());

        List<String> serialNumbers = new ArrayList<>();
        if (ItemType.SERIALIZED.equals(itemRepository.selectById(fofoOrderItem.getItemId()).getType())) {
            Set<Integer> inventoryItemIds = customerReturnItems.stream().map(x -> x.getInventoryItemId()).collect(Collectors.toSet());
            serialNumbers = inventoryItemRepository.selectByIds(inventoryItemIds).stream().map(x -> x.getSerialNumber()).collect(Collectors.toList());
            customFofoOrderItem.setDescription(
                    customFofoOrderItem.getDescription() + "\n IMEIS - " + String.join(", ", serialNumbers));
        }

        // Check if margin scheme item
        boolean isMarginItem = false;
        try {
            Item item = itemRepository.selectById(fofoOrderItem.getItemId());
            Category category = categoryRepository.selectById(item.getCategoryId());
            isMarginItem = category.isMarginOnly() && !serialNumbers.isEmpty();
        } catch (Exception e) {
            LOGGER.warn("Could not check margin scheme for credit note item {}", fofoOrderItem.getId(), e);
        }

        if (isMarginItem) {
            // Margin Scheme credit note: reverse GST on margin only
            float purchasePrice = getFofoPurchasePrice(serialNumbers.get(0), fofoOrder.getFofoId());
            float sellingPrice = fofoOrderItem.getSellingPrice();
            float margin = Math.max(0, sellingPrice - purchasePrice);
            float taxableMargin = margin / (1 + totalTaxRate / 100);

            customFofoOrderItem.setMarginScheme(true);
            customFofoOrderItem.setPurchasePrice(purchasePrice);
            customFofoOrderItem.setSellingPrice(sellingPrice);
            customFofoOrderItem.setMargin(margin);
            customFofoOrderItem.setRate(sellingPrice);
            customFofoOrderItem.setDiscount(0);
            customFofoOrderItem.setAmount(taxableMargin * customerReturnItems.size());
        } else {
            float taxableSellingPrice = fofoOrderItem.getSellingPrice() / (1 + totalTaxRate / 100);
            float taxableDiscountPrice = fofoOrderItem.getDiscount() / (1 + totalTaxRate / 100);
            customFofoOrderItem.setAmount(customerReturnItems.size() * (taxableSellingPrice - taxableDiscountPrice));
            customFofoOrderItem.setRate(taxableSellingPrice);
            customFofoOrderItem.setDiscount(taxableDiscountPrice);
        }

        customFofoOrderItem.setQuantity(customerReturnItems.size());
        customFofoOrderItem.setNetAmount(
                (fofoOrderItem.getSellingPrice() - fofoOrderItem.getDiscount()) * customFofoOrderItem.getQuantity());

        float igstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getIgstRate()) / 100;
        float cgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getCgstRate()) / 100;
        float sgstAmount = (customFofoOrderItem.getAmount() * fofoOrderItem.getSgstRate()) / 100;
        LOGGER.info("fofoOrderItem - {}", fofoOrderItem);
        customFofoOrderItem.setIgstRate(fofoOrderItem.getIgstRate());
        customFofoOrderItem.setIgstAmount(igstAmount);
        customFofoOrderItem.setCgstRate(fofoOrderItem.getCgstRate());
        customFofoOrderItem.setCgstAmount(cgstAmount);
        customFofoOrderItem.setSgstRate(fofoOrderItem.getSgstRate());
        customFofoOrderItem.setSgstAmount(sgstAmount);
        customFofoOrderItem.setHsnCode(fofoOrderItem.getHsnCode());
        customFofoOrderItem.setOrderId(1);
        customerFofoOrderItems.add(customFofoOrderItem);

        InvoicePdfModel pdfModel = new InvoicePdfModel();
        pdfModel.setAuther("NSSPL");
        pdfModel.setCustomer(customCustomer);
        pdfModel.setInvoiceNumber(fofoOrder.getInvoiceNumber());
        pdfModel.setInvoiceDate(FormattingUtils.formatDate(fofoOrder.getCreateTimestamp()));
        pdfModel.setTitle("Credit Note");
        pdfModel.setRetailer(customRetailer);
        pdfModel.setTotalAmount(customFofoOrderItem.getNetAmount());
        pdfModel.setOrderItems(customerFofoOrderItems);
        pdfModel.setHasMarginSchemeItems(isMarginItem);

        CreditNotePdfModel creditNotePdfModel = new CreditNotePdfModel();
        creditNotePdfModel.setCreditNoteDate(FormattingUtils.formatDate(creditNote.getCreateTimestamp()));
        creditNotePdfModel.setCreditNoteNumber(creditNote.getCreditNoteNumber());
        creditNotePdfModel.setPdfModel(pdfModel);
        return creditNotePdfModel;
    }

    // This will remove the order and maintain order record and reverse inventory
    // and scheme
    @Override
    public void cancelOrder(List<String> invoiceNumbers) throws ProfitMandiBusinessException {
        for (String invoiceNumber : invoiceNumbers) {
            // Cancel only when not cancelled
            FofoOrder fofoOrder = fofoOrderRepository.selectByInvoiceNumber(invoiceNumber);
            if (fofoOrder.getCancelledTimestamp() == null) {
                fofoOrder.setCancelledTimestamp(LocalDateTime.now());
                partnerInvestmentService.evictInvestmentCache(fofoOrder.getFofoId());
                PaymentOptionTransaction paymentTransaction = new PaymentOptionTransaction();
                paymentTransaction.setAmount(-fofoOrder.getTotalAmount());
                paymentTransaction.setFofoId(fofoOrder.getFofoId());
                paymentTransaction.setReferenceId(fofoOrder.getId());
                paymentTransaction.setReferenceType(PaymentOptionReferenceType.ORDER);
                paymentTransaction.setPaymentOptionId(1);
                paymentOptionTransactionRepository.persist(paymentTransaction);

                List<FofoOrderItem> fois = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());
                if (fois.size() > 0) {
                    List<InventoryItem> inventoryItems = new ArrayList<>();
                    fois.stream().forEach(x -> {
                        x.getFofoLineItems().stream().forEach(y -> {
                            inventoryService.rollbackInventory(y.getInventoryItemId(), y.getQuantity(), fofoOrder.getFofoId());
                            inventoryItems.add(inventoryItemRepository.selectById(y.getInventoryItemId()));
                        });
                    });
                    // if(invoice)
                    this.reverseScheme(fofoOrder);
                }
                insuranceService.cancelInsurance(fofoOrder);
            }
        }
    }

    @Override
    public void reverseScheme(FofoOrder fofoOrder) throws ProfitMandiBusinessException {
        String reversalReason = "Order Rolledback/Cancelled/Returned for Invoice #" + fofoOrder.getInvoiceNumber();
        List<FofoOrderItem> fois = fofoOrderItemRepository.selectByOrderId(fofoOrder.getId());
        Set<Integer> inventoryItemIds = fois.stream().flatMap(x -> x.getFofoLineItems().stream().map(y -> y.getInventoryItemId())).collect(Collectors.toSet());
        List<InventoryItem> inventoryItems = inventoryItemRepository.selectByIds(inventoryItemIds);
        schemeService.reverseSchemes(inventoryItems, fofoOrder.getId(), reversalReason, SchemeType.OUT_SCHEME_TYPES);
        //schemeService.reverseSchemes(inventoryItems, fofoOrder.getId(), reversalReason, Arrays.asList(SchemeType.INVESTMENT));
        schemeService.reverseSchemes(inventoryItems, fofoOrder.getId(), reversalReason, Arrays.asList(SchemeType.SPECIAL_SUPPORT));

    }

    @Override
    public void reverseActivationScheme(List<Integer> inventoryItemIds) throws ProfitMandiBusinessException {
        List<InventoryItem> inventoryItems = inventoryItemRepository.selectAllByIds(inventoryItemIds);
        for (InventoryItem inventoryItem : inventoryItems) {
            List<FofoLineItem> fofoLineItems = fofoLineItemRepository.selectByInventoryItemId(inventoryItem.getId());
            FofoLineItem fofoLineItem = fofoLineItems.get(0);
            FofoOrderItem fofoOrderItem = fofoOrderItemRepository.selectById(fofoLineItem.getFofoOrderItemId());
            FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(fofoOrderItem.getOrderId());
            String reversalReason = "Scheme rolled back as activation date is invalid for imei " + inventoryItem.getSerialNumber();
            //schemeService.reverseSchemes(Arrays.asList(inventoryItem), fofoOrder.getId(), reversalReason, Arrays.asList(SchemeType.ACTIVATION));
            schemeService.reverseSchemes(Arrays.asList(inventoryItem), fofoOrder.getId(), reversalReason, Arrays.asList(SchemeType.SPECIAL_SUPPORT));

        }

    }

    @Override
    public float getSales(int fofoId, LocalDateTime startDate, LocalDateTime endDate) {
        Float sales = fofoOrderRepository.selectSaleSumGroupByFofoIds(startDate, endDate).get(fofoId);
        return sales == null ? 0f : sales;
    }

    @Override
    public LocalDateTime getMaxSalesDate(int fofoId, LocalDateTime startDate, LocalDateTime endDate) {
        LocalDateTime dateTime = fofoOrderRepository.selectMaxSaleDateGroupByFofoIds(startDate, endDate).get(fofoId);
        return dateTime;
    }

    @Override
    // Only being used internally
    public float getSales(int fofoId, LocalDate onDate) {
        LocalDateTime startTime = LocalDateTime.of(onDate, LocalTime.MIDNIGHT);
        LocalDateTime endTime = LocalDateTime.of(onDate, LocalTime.MIDNIGHT).plusDays(1);
        return this.getSales(fofoId, startTime, endTime);
    }

    @Override
    public float getSales(LocalDateTime onDate) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public float getSales(LocalDateTime startDate, LocalDateTime endDate) {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public boolean applyColorChange(int orderId, int itemId) throws ProfitMandiBusinessException {
        Order order = orderRepository.selectById(orderId);
        saholicInventoryService.reservationCountByColor(itemId, order);

        order.getLineItem().setItemId(itemId);
        Item item = itemRepository.selectById(itemId);
        order.getLineItem().setColor(item.getColor());
        return true;
    }

    @Override
    public FofoOrder getOrderByInventoryItemId(int inventoryItemId) throws Exception {
        List<FofoLineItem> lineItems = fofoLineItemRepository.selectByInventoryItemId(inventoryItemId);
        if (lineItems.size() > 0) {
            FofoOrderItem fofoOrderItem = fofoOrderItemRepository.selectById(lineItems.get(0).getFofoOrderItemId());
            fofoOrderItem.setFofoLineItems(new HashSet<>(lineItems));
            FofoOrder fofoOrder = fofoOrderRepository.selectByOrderId(fofoOrderItem.getOrderId());
            fofoOrder.setOrderItem(fofoOrderItem);
            return fofoOrder;
        } else {
            throw new Exception(String.format("Could not find inventoryItemId - %s", inventoryItemId));
        }
    }

    @Override
    public Map<Integer, Long> carryBagCreditCount(int fofoId) throws ProfitMandiBusinessException {

        FofoStore fs = fofoStoreRepository.selectByRetailerId(fofoId);
        LocalDateTime lastCredit = fs.getBagsLastCredited();
        /*
         * long carryBagCount = 0; List<FofoOrder> fofoOrders =
         * fofoOrderRepository.selectByFofoIdBetweenCreatedTimeStamp(fofoId,
         * lastCredit.atStartOfDay(), LocalDate.now().plusDays(1).atStartOfDay()); for
         * (FofoOrder fo : fofoOrders) { carryBagCount +=
         * fofoOrderItemRepository.selectByOrderId(fo.getId()).stream() .filter(x ->
         * x.getSellingPrice() >= 12000).count();
         *
         * }
         */

        Session session = sessionFactory.getCurrentSession();
        CriteriaBuilder cb = session.getCriteriaBuilder();

        CriteriaQuery<SimpleEntry> query = cb.createQuery(SimpleEntry.class);
        Root<FofoOrder> fofoOrder = query.from(FofoOrder.class);
        Root<FofoOrderItem> fofoOrderItem = query.from(FofoOrderItem.class);
        Root<TagListing> tagListingRoot = query.from(TagListing.class);
        Root<Item> itemRoot = query.from(Item.class);

        Predicate p2 = cb.between(fofoOrder.get(ProfitMandiConstants.CREATE_TIMESTAMP), lastCredit, LocalDate.now().atStartOfDay());
        Predicate p3 = cb.isNull(fofoOrder.get("cancelledTimestamp"));
        Predicate joinPredicate = cb.and(
                cb.equal(fofoOrder.get(ProfitMandiConstants.ID), fofoOrderItem.get(ProfitMandiConstants.ORDER_ID)), cb.equal(fofoOrderItem.get("itemId"), tagListingRoot.get("itemId")), cb.equal(itemRoot.get("id"), tagListingRoot.get("itemId")), cb.equal(fofoOrder.get(ProfitMandiConstants.FOFO_ID), fofoId));
        ItemCriteria itemCriteria = new ItemCriteria();
        itemCriteria.setBrands(brandsService.getBrands(fofoId, null, 3).stream().map(x -> x.getName()).collect(Collectors.toList()));
        float startValue = 12000;
        itemCriteria.setStartPrice(startValue);
        itemCriteria.setEndPrice(0);
        itemCriteria.setFeaturedPhone(false);
        itemCriteria.setSmartPhone(true);
        itemCriteria.setCatalogIds(new ArrayList<>());
        itemCriteria.setExcludeCatalogIds(new ArrayList<>());
        Predicate itemPredicate = itemRepository.getItemPredicate(itemCriteria, cb, itemRoot, tagListingRoot.get("itemId"), tagListingRoot.get("sellingPrice"));
        Predicate finalPredicate = cb.and(itemPredicate, p2, p3, joinPredicate);
        query = query.multiselect(fofoOrder.get(ProfitMandiConstants.FOFO_ID), cb.count(fofoOrder)).where(finalPredicate).groupBy(fofoOrder.get(ProfitMandiConstants.FOFO_ID));
        List<SimpleEntry> simpleEntries = session.createQuery(query).getResultList();
        Map<Integer, Long> returnMap = new HashMap<>();

        for (SimpleEntry simpleEntry : simpleEntries) {
            returnMap.put((Integer) simpleEntry.getKey(), (Long) simpleEntry.getValue());
        }
        return returnMap;

    }

    @Override
    public void createMissingScratchOffers() {
        List<FofoOrder> fofoOrders = fofoOrderRepository.selectFromSaleDate(LocalDate.of(2023, 11, 6).atStartOfDay());
        for (FofoOrder fofoOrder : fofoOrders) {
            if (fofoOrder.getCancelledTimestamp() == null) { // Check if cancelled_timestamp is not null
                try {
                    this.createScratchOffer(fofoOrder.getFofoId(), fofoOrder.getInvoiceNumber(), fofoOrder.getCustomerId());
                } catch (Exception e) {
                    LOGGER.error("Error while processing missing scratch offer invoice orderId", fofoOrder.getId());
                }
            }
        }
    }

    @Override
    public boolean refundOrder(int orderId, String refundedBy, String refundReason) throws
            ProfitMandiBusinessException {
        /*def refund_order(order_id, refunded_by, reason):
        """
        If the order is in RTO_RECEIVED_PRESTINE, DOA_CERT_VALID or DOA_CERT_INVALID state, it does the following:
            1. Creates a refund request for batch processing.
            2. Creates a return order for the warehouse executive to return the shipped material.
            3. Marks the current order as RTO_REFUNDED, DOA_VALID_REFUNDED or DOA_INVALID_REFUNDED final states.

        If the order is in SUBMITTED_FOR_PROCESSING or INVENTORY_LOW state, it does the following:
            1. Creates a refund request for batch processing.
            2. Cancels the reservation of the item in the warehouse.
            3. Marks the current order as the REFUNDED final state.

        For all COD orders, if the order is in INIT, SUBMITTED_FOR_PROCESSING or INVENTORY_LOW state, it does the following:
            1. Cancels the reservation of the item in the warehouse.
            2. Marks the current order as CANCELED.

        In all cases, it updates the reason for cancellation or refund and the person who performed the action.

        Returns True if it is successful, False otherwise.

        Throws an exception if the order with the given id couldn't be found.

        Parameters:
         - order_id
         - refunded_by
         - reason
        """
        LOGGER.info("Refunding order id: {}", orderId);
        Order order = orderRepository.selectById(orderId);

        if order.cod:
        logging.info("Refunding COD order with status " + str(order.status))
        status_transition = refund_status_transition
        if order.status not in status_transition.keys():
        raise TransactionServiceException(114, "This order can't be refunded")

        if order.status in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
        __update_inventory_reservation(order, refund=True)
        order.statusDescription = "Order Cancelled"
            #Shipment Id and Airway Bill No should be none in case of Cancellation
        order.logisticsTransactionId = None
        order.tracking_id = None
        order.airwaybill_no = None
        elif order.status == OrderStatus.BILLED:
        __create_return_order(order)
        order.statusDescription = "Order Cancelled"
        elif order.status in [OrderStatus.RTO_RECEIVED_PRESTINE, OrderStatus.RTO_RECEIVED_DAMAGED, OrderStatus.RTO_LOST_IN_TRANSIT]:
        if order.status != OrderStatus.RTO_LOST_IN_TRANSIT:
        __create_return_order(order)
        order.statusDescription = "RTO Refunded"
        elif order.status in [OrderStatus.LOST_IN_TRANSIT]:
            #__create_return_order(order)
        order.statusDescription = "Lost in Transit Refunded"
        elif order.status in [OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID, OrderStatus.DOA_RECEIVED_DAMAGED, OrderStatus.DOA_LOST_IN_TRANSIT] :
        if order.status != OrderStatus.DOA_LOST_IN_TRANSIT:
        __create_return_order(order)
        __create_refund(order, 0, 'Should be unreachable for now')
        order.statusDescription = "DOA Refunded"
        elif order.status in [OrderStatus.RET_PRODUCT_UNUSABLE, OrderStatus.RET_PRODUCT_USABLE, OrderStatus.RET_RECEIVED_DAMAGED, OrderStatus.RET_LOST_IN_TRANSIT] :
        if order.status != OrderStatus.RET_LOST_IN_TRANSIT:
        __create_return_order(order)
        __create_refund(order, 0, 'Should be unreachable for now')
        order.statusDescription = "Return Refunded"
        elif order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED:
        if order.previousStatus in [OrderStatus.COD_VERIFICATION_PENDING, OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
        __update_inventory_reservation(order, refund=True)
        order.statusDescription = "Order Cancelled on customer request"
        elif order.previousStatus == OrderStatus.BILLED:
        __create_return_order(order)
        order.statusDescription = "Order Cancelled on customer request"
        order.received_return_timestamp = datetime.datetime.now()
    else:
        status_transition = {OrderStatus.LOST_IN_TRANSIT : OrderStatus.LOST_IN_TRANSIT_REFUNDED,
                OrderStatus.RTO_RECEIVED_PRESTINE : OrderStatus.RTO_REFUNDED,
                OrderStatus.RTO_RECEIVED_DAMAGED : OrderStatus.RTO_DAMAGED_REFUNDED,
                OrderStatus.RTO_LOST_IN_TRANSIT : OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED,
                OrderStatus.DOA_CERT_INVALID : OrderStatus.DOA_INVALID_REFUNDED,
                OrderStatus.DOA_CERT_VALID : OrderStatus.DOA_VALID_REFUNDED,
                OrderStatus.DOA_RECEIVED_DAMAGED : OrderStatus.DOA_REFUNDED_RCVD_DAMAGED,
                OrderStatus.DOA_LOST_IN_TRANSIT : OrderStatus.DOA_REFUNDED_LOST_IN_TRANSIT,
                OrderStatus.RET_PRODUCT_UNUSABLE : OrderStatus.RET_PRODUCT_UNUSABLE_REFUNDED,
                OrderStatus.RET_PRODUCT_USABLE : OrderStatus.RET_PRODUCT_USABLE_REFUNDED,
                OrderStatus.RET_RECEIVED_DAMAGED : OrderStatus.RET_REFUNDED_RCVD_DAMAGED,
                OrderStatus.RET_LOST_IN_TRANSIT : OrderStatus.RET_REFUNDED_LOST_IN_TRANSIT,
                OrderStatus.SUBMITTED_FOR_PROCESSING : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.INVENTORY_LOW : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.LOW_INV_PO_RAISED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.LOW_INV_REVERSAL_IN_PROCESS : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.ACCEPTED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.BILLED : OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY,
                OrderStatus.CANCEL_REQUEST_CONFIRMED : OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST,
                OrderStatus.PAYMENT_FLAGGED : OrderStatus.PAYMENT_FLAGGED_DENIED
                     }
        if order.status not in status_transition.keys():
        raise TransactionServiceException(114, "This order can't be refunded")

        if order.status in [OrderStatus.RTO_RECEIVED_PRESTINE, OrderStatus.RTO_RECEIVED_DAMAGED, OrderStatus.RTO_LOST_IN_TRANSIT] :
        if order.status != OrderStatus.RTO_LOST_IN_TRANSIT:
        __create_return_order(order)
        __create_refund(order, order.wallet_amount, 'Order #{0} is RTO refunded'.format(order.id))
        order.statusDescription = "RTO Refunded"
            #Start:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
        try:
        crmServiceClient = CRMClient().get_client()
        ticket =Ticket()
        activity = Activity()

        description = "Creating Ticket for " + order.statusDescription + " Order"
        ticket.creatorId = 1
        ticket.assigneeId = 34
        ticket.category = TicketCategory.RTO_REFUND
        ticket.priority = TicketPriority.MEDIUM
        ticket.status = TicketStatus.OPEN
        ticket.description = description
        ticket.orderId = order.id

        activity.creatorId = 1
        activity.ticketAssigneeId = ticket.assigneeId
        activity.type = ActivityType.OTHER
        activity.description = description
        activity.ticketCategory = ticket.category
        activity.ticketDescription = ticket.description
        activity.ticketPriority = ticket.priority
        activity.ticketStatus = ticket.status

        ticket.customerId= order.customer_id
        ticket.customerEmailId = order.customer_email
        ticket.customerMobileNumber = order.customer_mobilenumber
        ticket.customerName = order.customer_name
        activity.customerId = ticket.customerId
        activity.customerEmailId = order.customer_email
        activity.customerMobileNumber = order.customer_mobilenumber
        activity.customerName = order.customer_name

        crmServiceClient.insertTicket(ticket, activity)

        except:
        print "Ticket for RTO Refund is not created."
            #End:- Added By Manish Sharma for Creating a new Ticket: Category- RTO Refund on 21-Jun-2013
        elif order.status in [OrderStatus.LOST_IN_TRANSIT]:
            #__create_return_order(order)
        __create_refund(order, order.wallet_amount, 'Order #{0} is Lost in Transit'.format(order.id))
        order.statusDescription = "Lost in Transit Refunded"
        elif order.status in [OrderStatus.DOA_CERT_INVALID, OrderStatus.DOA_CERT_VALID, OrderStatus.DOA_RECEIVED_DAMAGED, OrderStatus.DOA_LOST_IN_TRANSIT] :
        if order.status != OrderStatus.DOA_LOST_IN_TRANSIT:
        __create_return_order(order)
        __create_refund(order, 0, 'This should be unreachable')
        order.statusDescription = "DOA Refunded"
        elif order.status in [OrderStatus.RET_PRODUCT_UNUSABLE, OrderStatus.RET_PRODUCT_USABLE, OrderStatus.RET_RECEIVED_DAMAGED, OrderStatus.RET_LOST_IN_TRANSIT] :
        if order.status != OrderStatus.RET_LOST_IN_TRANSIT:
        __create_return_order(order)
        __create_refund(order, 0, 'This should be unreachable')
        order.statusDescription = "Return Refunded"
        elif order.status in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.ACCEPTED]:
        __update_inventory_reservation(order, refund=True)
        order.statusDescription = "Order Refunded"
        elif order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED:
        if order.previousStatus in [OrderStatus.SUBMITTED_FOR_PROCESSING, OrderStatus.INVENTORY_LOW, OrderStatus.LOW_INV_PO_RAISED, OrderStatus.LOW_INV_REVERSAL_IN_PROCESS, OrderStatus.LOW_INV_NOT_AVAILABLE_AT_HOTSPOT, OrderStatus.PAYMENT_FLAGGED, OrderStatus.ACCEPTED]:
        __update_inventory_reservation(order, refund=True)
        order.statusDescription = "Order Cancelled on customer request"
        elif order.previousStatus == OrderStatus.BILLED:
        __create_refund(order, order.wallet_amount,  'Order #{0} Cancelled on customer request'.format(order.id))
        order.statusDescription = "Order Cancelled on customer request"

        elif order.status == OrderStatus.PAYMENT_FLAGGED:
        __update_inventory_reservation(order, refund=True)
        order.statusDescription = "Order Cancelled due to payment flagged"

    # For orders that are cancelled after being billed, we need to scan in the scanned out
    # inventory item and change availability accordingly
        inventoryClient = InventoryClient().get_client()
        warehouse = inventoryClient.getWarehouse(order.warehouse_id)
        if warehouse.billingType == BillingType.OURS or warehouse.billingType == BillingType.OURS_EXTERNAL:
        #Now BILLED orders can also be refunded directly with low inventory cancellations
        if order.status in [OrderStatus.BILLED, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_FROM_WH]:
        __create_refund(order, order.wallet_amount, reason)
        if order.status in [OrderStatus.BILLED, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_FROM_WH] or (order.status == OrderStatus.CANCEL_REQUEST_CONFIRMED and order.previousStatus in [OrderStatus.BILLED, OrderStatus.SHIPPED_TO_LOGST, OrderStatus.SHIPPED_FROM_WH]):
        lineitem = order.lineitems[0]
        catalogClient = CatalogClient().get_client()
        item = catalogClient.getItem(lineitem.item_id)
        warehouseClient = WarehouseClient().get_client()
        if warehouse.billingType == BillingType.OURS:
        if ItemType.SERIALIZED == item.type:
        for serial_number in str(lineitem.serial_number).split(','):
        warehouseClient.scanSerializedItemForOrder(serial_number, ScanType.SALE_RET, order.id, order.fulfilmentWarehouseId, 1, order.warehouse_id)
                else:
        warehouseClient.scanForOrder(None, ScanType.SALE_RET, lineitem.quantity, order.id, order.fulfilmentWarehouseId, order.warehouse_id)
        if warehouse.billingType == BillingType.OURS_EXTERNAL:
        warehouseClient.scanForOursExternalSaleReturn(order.id, lineitem.transfer_price)
        if order.freebieItemId:
        warehouseClient.scanfreebie(order.id, order.freebieItemId, 0, ScanType.SALE_RET)

        order.status = status_transition[order.status]
        order.statusDescription = OrderStatus._VALUES_TO_NAMES[order.status]
        order.refund_timestamp = datetime.datetime.now()
        order.refunded_by = refunded_by
        order.refund_reason = reason
    #to re evaluate the shipping charge if any order is being cancelled.
    #_revaluate_shiping(order_id)
        session.commit()
        return True*/
        return true;
    }

    @Autowired
    DebitNoteRepository debitNoteRepository;

    //initiate refund only if the stock is returned

}