Subversion Repositories SmartDukaan

package com.spice.profitmandi.service.offers;

import com.google.common.collect.Lists;
import com.google.gson.Gson;
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.CustomRetailer;
import com.spice.profitmandi.common.model.PartnerTargetModel;
import com.spice.profitmandi.common.model.ProfitMandiConstants;
import com.spice.profitmandi.common.util.FileUtil;
import com.spice.profitmandi.common.util.FormattingUtils;
import com.spice.profitmandi.common.util.Utils;
import com.spice.profitmandi.dao.entity.catalog.ItemCriteria;
import com.spice.profitmandi.dao.entity.catalog.Offer;
import com.spice.profitmandi.dao.entity.catalog.TagListing;
import com.spice.profitmandi.dao.entity.catalog.TargetSlabEntity;
import com.spice.profitmandi.dao.entity.fofo.FofoLineItem;
import com.spice.profitmandi.dao.entity.fofo.FofoOrderItem;
import com.spice.profitmandi.dao.entity.fofo.InventoryItem;
import com.spice.profitmandi.dao.entity.fofo.OfferPayout;
import com.spice.profitmandi.dao.entity.transaction.LineItem;
import com.spice.profitmandi.dao.entity.transaction.Order;
import com.spice.profitmandi.dao.entity.transaction.UserWalletHistory;
import com.spice.profitmandi.dao.entity.warehouse.WarehouseInventoryItem;
import com.spice.profitmandi.dao.entity.warehouse.WarehouseScan;
import com.spice.profitmandi.dao.enumuration.catalog.AchievementType;
import com.spice.profitmandi.dao.enumuration.catalog.AmountType;
import com.spice.profitmandi.dao.enumuration.catalog.OfferSchemeType;
import com.spice.profitmandi.dao.enumuration.transaction.SchemePayoutStatus;
import com.spice.profitmandi.dao.model.*;
import com.spice.profitmandi.dao.repository.GenericRepository;
import com.spice.profitmandi.dao.repository.catalog.*;
import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;
import com.spice.profitmandi.dao.repository.fofo.ActivatedImeiRepository;
import com.spice.profitmandi.dao.repository.fofo.InventoryItemRepository;
import com.spice.profitmandi.dao.repository.fofo.OfferPayoutRepository;
import com.spice.profitmandi.dao.repository.transaction.LineItemImeisRepository;
import com.spice.profitmandi.dao.repository.warehouse.AgeingSummaryModel;
import com.spice.profitmandi.dao.repository.warehouse.WarehouseInventoryItemRepository;
import com.spice.profitmandi.dao.repository.warehouse.WarehouseScanRepository;
import com.spice.profitmandi.service.NotificationService;
import com.spice.profitmandi.service.pricecircular.PriceCircularService;
import com.spice.profitmandi.service.scheme.InventoryPayoutModel;
import com.spice.profitmandi.service.user.RetailerService;
import com.spice.profitmandi.service.wallet.WalletService;
import com.spice.profitmandi.service.whatsapp.WhatsappMessageType;
import in.shop2020.model.v1.order.WalletReferenceType;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.CacheManager;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.util.*;
import java.util.stream.Collectors;

@Component
public class OfferServiceImpl implements OfferService {

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

    // Constants
    private static final List<String> TARGETS = Arrays.asList("Target 1", "Target 2", "Target 3", "Target 4", "Target 5");
    private static final int STOCK_AGEING_THRESHOLD_DAYS = 300;

    // Dependencies - Core
    @Autowired
    private Gson gson;

    @Autowired
    private OfferService offerServiceProxy; // self-injection for Spring cache proxy

    @Autowired
    private CacheManager redisCacheManager;

    @Autowired
    private CacheManager redisShortCacheManager;

    // Dependencies - Repositories
    @Autowired
    private GenericRepository genericRepository;

    @Autowired
    private OfferRepository offerRepository;

    @Autowired
    private OfferPayoutRepository offerPayoutRepository;

    @Autowired
    private OfferTargetSlabRepository offerTargetSlabRepository;

    @Autowired
    private ItemCriteriaRepository itemCriteriaRepository;

    @Autowired
    private ItemRepository itemRepository;

    @Autowired
    private TagListingRepository tagListingRepository;

    @Autowired
    private FofoStoreRepository fofoStoreRepository;

    @Autowired
    private ActivatedImeiRepository activatedImeiRepository;

    @Autowired
    private WarehouseScanRepository warehouseScanRepository;

    @Autowired
    private WarehouseInventoryItemRepository warehouseInventoryItemRepository;

    @Autowired
    private LineItemImeisRepository lineItemImeisRepository;

    @Autowired
    private InventoryItemRepository inventoryItemRepository;

    @Autowired
    private OfferEligibleImeiRepository offerEligibleImeiRepository;

    // Dependencies - Services
    @Autowired
    private NotificationService notificationService;

    @Autowired
    private RetailerService retailerService;

    @Autowired
    private PriceCircularService priceCircularService;

    @Autowired
    private WalletService walletService;

    @Override
    public void evictOfferCaches(int offerId) {
        Offer offer = offerRepository.selectById(offerId);
        YearMonth ym = YearMonth.from(offer.getStartDate());
        redisCacheManager.getCache("monthOfferIds").evict(ym);
        redisCacheManager.getCache("offer.definition").evict(offerId);
        redisCacheManager.getCache("catalog.published_yearmonth").evict(ym);
        redisCacheManager.getCache("offer.slabpayout").evict(offerId);
        redisCacheManager.getCache("offer.achievement").clear();
        redisShortCacheManager.getCache("todayOffers").clear();
    }

    @Override
    public void evictOfferDefinitionCache(int offerId) {
        redisCacheManager.getCache("offer.definition").evict(offerId);
    }

    @Override
    public void addOfferService(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {
        LOGGER.info("createOfferRequest  -- {}", createOfferRequest);
        this.createOffer(createOfferRequest);
        this.createTargetSlabs(createOfferRequest);
    }

    private void createOffer(CreateOfferRequest createOfferRequest) {
        Offer offer = new Offer();
        offer.setName(createOfferRequest.getName());
        offer.setTargetType(createOfferRequest.getTargetType());
        offer.setBooster(createOfferRequest.isBooster());
        offer.setPayoutType(createOfferRequest.getPayoutType());
        offer.setSchemeType(createOfferRequest.getSchemeType());
        offer.setBaseCriteia(createOfferRequest.isBaseCriteria());
        offer.setEndDate(createOfferRequest.getEndDate());
        offer.setStartDate(createOfferRequest.getStartDate());
        offer.setBrandSharePercentage(createOfferRequest.getBrandShareTerms());
        offer.setSellinPercentage(createOfferRequest.getSellinPercentage());
        offer.setOfferNotes(createOfferRequest.getOfferNotes());
        offer.setActivationBrands(createOfferRequest.getActivationBrands());
        offer.setTerms(createOfferRequest.getTerms());
        offer.setItemCriteria(gson.toJson(createOfferRequest.getItemCriteria()));
        offer.setPartnerCriteria(gson.toJson(createOfferRequest.getPartnerCriteria()));
        offer.setDiscount(createOfferRequest.isDiscount());
        offer.setReference(createOfferRequest.getReference());
        offerRepository.persist(offer);
        createOfferRequest.setId(offer.getId());
    }

    @Override
    public List<CreateOfferRequest> getPublishedOffers(int fofoId, YearMonth yearMonth) throws ProfitMandiBusinessException {
        // Past months (>1 month ago): achievement is frozen, cache the full result
        if (yearMonth.isBefore(YearMonth.now().minusMonths(1))) {
            return offerServiceProxy.getPublishedOffersCached(fofoId, yearMonth);
        }
        // Current/recent month: assemble live from cached building blocks
        return assemblePublishedOffers(fofoId, yearMonth);
    }

    @Cacheable(value = "publishedOffersWithAchievement.past", cacheManager = "redisCacheManager")
    public List<CreateOfferRequest> getPublishedOffersCached(int fofoId, YearMonth yearMonth) throws ProfitMandiBusinessException {
        return assemblePublishedOffers(fofoId, yearMonth);
    }

    private List<CreateOfferRequest> assemblePublishedOffers(int fofoId, YearMonth yearMonth) throws ProfitMandiBusinessException {
        Map<Integer, List<Offer>> publishedMapByPartner = offerRepository.selectAllPublishedMapByPartner(yearMonth);
        List<Offer> publishedOffers = publishedMapByPartner != null ? publishedMapByPartner.get(fofoId) : null;
        List<CreateOfferRequest> createOfferRequests = new ArrayList<>();
        if (publishedOffers != null) {
            for (Offer offer : publishedOffers) {
                CreateOfferRequest copy = deepCopy(this.getCreateOfferRequest(offer));
                setCriteriaPayoutAchieved(fofoId, copy);
                createOfferRequests.add(copy);
            }
        }
        return createOfferRequests;
    }

    private void setCriteriaPayoutAchieved(int fofoId, CreateOfferRequest createOfferRequest) {
        Map<Integer, Long> criteriaTransactionMap;
        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.SELLIN)) {
            criteriaTransactionMap = offerRepository.getPurchaseSumForPartner(
                    createOfferRequest.getId(), fofoId, createOfferRequest);
        } else {
            criteriaTransactionMap = offerRepository.getSalesSumForPartner(
                    createOfferRequest.getId(), fofoId, createOfferRequest);
        }
        List<com.spice.profitmandi.dao.model.ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest
                .getTargetSlabs().get(0).getItemCriteriaPayouts();

        for (com.spice.profitmandi.dao.model.ItemCriteriaPayout itemCriteriaPayout : itemCriteriaPayouts) {
            itemCriteriaPayout.setNextSlab(null);
            long saleAmount = 0;
            if (criteriaTransactionMap != null
                    && criteriaTransactionMap.containsKey(itemCriteriaPayout.getItemCriteria().getId())) {
                saleAmount = criteriaTransactionMap.containsKey(0) ? criteriaTransactionMap.get(0) : criteriaTransactionMap.get(itemCriteriaPayout.getItemCriteria().getId());
            }
            itemCriteriaPayout.setAchievedValue(saleAmount);
            List<com.spice.profitmandi.service.offers.PayoutSlab> payoutSlabs = itemCriteriaPayout.getPayoutSlabs();
            for (int i = 0; i < payoutSlabs.size(); i++) {
                com.spice.profitmandi.service.offers.PayoutSlab beginSlab = payoutSlabs.get(i);
                com.spice.profitmandi.service.offers.PayoutSlab endSlab = null;
                if (payoutSlabs.size() - 1 > i) {
                    endSlab = payoutSlabs.get(i + 1);
                }
                if (beginSlab.getOnwardsAmount() > saleAmount) {
                    itemCriteriaPayout.setShortValue(beginSlab.getOnwardsAmount() - saleAmount);
                    itemCriteriaPayout.setNextSlab(beginSlab);
                    break;
                }
                if (endSlab != null) {
                    if (beginSlab.getOnwardsAmount() <= saleAmount && endSlab.getOnwardsAmount() > saleAmount) {
                        itemCriteriaPayout.setCurrentSlab(beginSlab);
                        beginSlab.setSelected(true);
                        itemCriteriaPayout.setNextSlab(endSlab);
                        itemCriteriaPayout.setShortValue(endSlab.getOnwardsAmount() - saleAmount);
                        break;
                    }
                } else {
                    beginSlab.setSelected(true);
                    itemCriteriaPayout.setCurrentSlab(beginSlab);
                }
            }
        }
    }

    @Override
    @Cacheable(value = "monthOfferIds", cacheManager = "redisCacheManager")
    public Set<Integer> getMonthOfferIds(YearMonth yearMonth) throws ProfitMandiBusinessException {
        List<Offer> allOffers = offerRepository.selectAll(yearMonth, false);
        return allOffers.stream().map(Offer::getId).collect(Collectors.toSet());
    }

    @Override
    public Map<Integer, CreateOfferRequest> getAllOffers(YearMonth yearMonth) throws ProfitMandiBusinessException {
        Set<Integer> ids = offerServiceProxy.getMonthOfferIds(yearMonth);
        Map<Integer, CreateOfferRequest> result = new HashMap<>();
        for (Integer id : ids) {
            result.put(id, offerServiceProxy.getOfferDefinition(id));
        }
        return result;
    }

    private CreateOfferRequest deepCopy(CreateOfferRequest source) {
        return gson.fromJson(gson.toJson(source), CreateOfferRequest.class);
    }

    @Override
    @Cacheable(value = "offer.definition", cacheManager = "redisCacheManager", key = "#offerId")
    public CreateOfferRequest getOfferDefinition(int offerId) throws ProfitMandiBusinessException {
        Offer offer = offerRepository.selectById(offerId);
        return this.getCreateOfferRequest(offer);
    }

    @Override
    public CreateOfferRequest getOffer(int fofoId, int offerId) throws ProfitMandiBusinessException {
        CreateOfferRequest definition = offerServiceProxy.getOfferDefinition(offerId);
        if (fofoId > 0) {
            CreateOfferRequest copy = deepCopy(definition);
            copy.setCriteriaQtyAmountModel(this.getCriteriaQtyAmounModelMap(copy, fofoId));
            return copy;
        }
        return definition;
    }

    @Override
    public CreateOfferRequest getCreateOfferRequest(Offer fromOffer) throws ProfitMandiBusinessException {
        CreateOfferRequest createOfferRequest = new CreateOfferRequest();
        createOfferRequest.setName(fromOffer.getName());
        createOfferRequest.setDiscount(fromOffer.isDiscount());
        createOfferRequest.setTargetType(fromOffer.getTargetType());
        createOfferRequest.setPayoutType(fromOffer.getPayoutType());
        createOfferRequest.setSchemeType(fromOffer.getSchemeType());
        createOfferRequest.setActivationBrands(fromOffer.getActivationBrands());
        createOfferRequest.setEndDate(fromOffer.getEndDate());
        createOfferRequest.setProcessTimestamp(fromOffer.getProcessedTimestamp());
        createOfferRequest.setStartDate(fromOffer.getStartDate());
        createOfferRequest.setBooster(fromOffer.isBooster());
        createOfferRequest.setActive(fromOffer.isActive());
        createOfferRequest.setSellinPercentage(fromOffer.getSellinPercentage());
        createOfferRequest.setBrandShareTerms(fromOffer.getBrandSharePercentage());
        createOfferRequest.setOfferNotes(fromOffer.getOfferNotes());
        createOfferRequest.setTerms(fromOffer.getTerms());
        createOfferRequest.setBaseCriteria(fromOffer.isBaseCriteia());
        createOfferRequest.setItemCriteria(
                gson.fromJson(fromOffer.getItemCriteria(), com.spice.profitmandi.service.offers.ItemCriteria.class));
        createOfferRequest.setPartnerCriteria(gson.fromJson(fromOffer.getPartnerCriteria(), PartnerCriteria.class));
        createOfferRequest.setId(fromOffer.getId());
        createOfferRequest.setCreatedOn(fromOffer.getCreatedTimestamp());
        createOfferRequest.setReference(fromOffer.getReference());
        createOfferRequest.setItemCriteriaString(
                itemCriteriaRepository.getItemCriteriaDescription(createOfferRequest.getItemCriteria()));
        List<com.spice.profitmandi.dao.model.TargetSlab> targetSlabs = offerTargetSlabRepository
                .getByOfferId(fromOffer.getId());
        createOfferRequest.setPartnerCriteriaString(
                this.getPartnerCriteriaString(createOfferRequest.getPartnerCriteria()));
        createOfferRequest.setTargetSlabs(targetSlabs);
        if (!targetSlabs.isEmpty() && !targetSlabs.get(0).getItemCriteriaPayouts().isEmpty()) {
            for (com.spice.profitmandi.dao.model.ItemCriteriaPayout itemCriteriaPayout : targetSlabs.get(0).getItemCriteriaPayouts()) {
                if (!itemCriteriaPayout.getPayoutSlabs().isEmpty()) {
                    itemCriteriaPayout.setNextSlab(itemCriteriaPayout.getPayoutSlabs().get(0));
                }
            }
        }
        return createOfferRequest;
    }

    @Override
    public void reverseAdditionalSelloutSchemes(List<InventoryItem> inventoryItems, String reversalReason) throws ProfitMandiBusinessException {
        Map<Integer, List<InventoryItem>> fofoInventoryItemsMap = inventoryItems.stream().collect(Collectors.groupingBy(x -> x.getFofoId()));
        for (Map.Entry<Integer, List<InventoryItem>> fofoInventoryItemsEntry : fofoInventoryItemsMap.entrySet()) {
            int fofoId = fofoInventoryItemsEntry.getKey();
            List<InventoryItem> partnerInventoryItems = fofoInventoryItemsEntry.getValue();
            List<OfferPayout> offerPayouts = new ArrayList<>();
            LOGGER.info("Reversing schemes for fofoId {}, InventoryItems {}", fofoId, inventoryItems);
            //As of now we are not handling non-serialised items
            List<InventoryItem> serializedItems = partnerInventoryItems.stream().filter(x -> x.getSerialNumber() != null).collect(Collectors.toList());
            if (serializedItems.size() > 0) {
                // Batch fetch: collect all serial numbers and fetch in one call per fofoId
                Set<String> serialNumbers = serializedItems.stream().map(InventoryItem::getSerialNumber).collect(Collectors.toSet());
                offerPayouts = offerPayoutRepository.selectAllBySerialNumbers(fofoId, serialNumbers)
                        .stream()
                        .filter(x -> x.getRejectTimestamp() == null)
                        .collect(Collectors.toList());
                for (OfferPayout offerPayout : offerPayouts) {
                    offerPayout.setStatus(SchemePayoutStatus.REJECTED);
                    offerPayout.setRejectTimestamp(LocalDateTime.now());
                }
                Map<Long, Double> offerPayoutAmountMap = offerPayouts.stream().collect(Collectors.groupingBy(x -> x.getOfferId(), Collectors.summingDouble(x -> x.getAmount())));
                for (Map.Entry<Long, Double> offerPayoutEntry : offerPayoutAmountMap.entrySet()) {
                    List<UserWalletHistory> histories = walletService.getAllByReference(fofoId, offerPayoutEntry.getKey().intValue(), WalletReferenceType.ADDITIONAL_SCHEME);
                    if (histories.size() > 0) {

                        walletService.rollbackAmountFromWallet(fofoId, offerPayoutEntry.getValue().floatValue(), offerPayoutEntry.getKey().intValue(),
                                WalletReferenceType.ADDITIONAL_SCHEME, reversalReason, LocalDateTime.now());
                    }
                }
            }
        }
    }

    @Override
    public void restoreAdditionalSelloutSchemes(List<InventoryItem> inventoryItems, String restoreReason)
            throws ProfitMandiBusinessException {
        Map<Integer, List<InventoryItem>> fofoInventoryItemsMap = inventoryItems.stream()
                .collect(Collectors.groupingBy(InventoryItem::getFofoId));
        for (Map.Entry<Integer, List<InventoryItem>> entry : fofoInventoryItemsMap.entrySet()) {
            int fofoId = entry.getKey();
            List<InventoryItem> partnerItems = entry.getValue();

            List<InventoryItem> serializedItems = partnerItems.stream()
                    .filter(x -> x.getSerialNumber() != null)
                    .collect(Collectors.toList());
            if (!serializedItems.isEmpty()) {
                Set<String> serialNumbers = serializedItems.stream()
                        .map(InventoryItem::getSerialNumber).collect(Collectors.toSet());
                List<OfferPayout> offerPayouts = offerPayoutRepository
                        .selectAllBySerialNumbers(fofoId, serialNumbers).stream()
                        .filter(x -> x.getRejectTimestamp() != null
                                && SchemePayoutStatus.REJECTED.equals(x.getStatus()))
                        .collect(Collectors.toList());

                for (OfferPayout offerPayout : offerPayouts) {
                    offerPayout.setStatus(SchemePayoutStatus.CREDITED);
                    offerPayout.setRejectTimestamp(null);
                }

                Map<Long, Double> offerPayoutAmountMap = offerPayouts.stream()
                        .collect(Collectors.groupingBy(OfferPayout::getOfferId,
                                Collectors.summingDouble(OfferPayout::getAmount)));
                for (Map.Entry<Long, Double> offerEntry : offerPayoutAmountMap.entrySet()) {
                    if (offerEntry.getValue() > 0) {
                        walletService.addAmountToWallet(fofoId, offerEntry.getKey().intValue(),
                                WalletReferenceType.ADDITIONAL_SCHEME, restoreReason,
                                offerEntry.getValue().floatValue(), LocalDateTime.now());
                    }
                }
            }
        }
    }

    private void createTargetSlabs(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {
        List<com.spice.profitmandi.dao.model.TargetSlab> targetSlabs = createOfferRequest.getTargetSlabs().stream()
                .filter(x -> x.validate()).collect(Collectors.toList());
        if (!targetSlabs.isEmpty()) {
            Collections.sort(targetSlabs);
            for (com.spice.profitmandi.dao.model.TargetSlab targetSlab : targetSlabs) {
                if (targetSlab.validate()) {
                    List<ItemCriteriaPayout> itemCriteriaPayouts = targetSlab.getItemCriteriaPayouts();
                    for (ItemCriteriaPayout itemCriteriaPayout : itemCriteriaPayouts) {
                        ItemCriteria itemCriteria = itemCriteriaRepository.selectById(itemCriteriaPayout.getItemCriteria().getId());
                        if (itemCriteriaPayout.getItemCriteria().getId() > 0) {
                            itemCriteriaPayout.getItemCriteria().setId(0);
                        } else {
                            String itemCriteriaJson = gson.toJson(itemCriteriaPayout.getItemCriteria());
                            itemCriteria = itemCriteriaRepository.selectByCriteria(itemCriteriaJson);
                            // ItemCriteria Only once
                            if (itemCriteria == null) {
                                itemCriteria = new ItemCriteria();
                                itemCriteria.setCriteria(gson.toJson(itemCriteriaPayout.getItemCriteria()));
                                itemCriteriaRepository.persist(itemCriteria);
                            }
                        }
                        for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {
                            TargetSlabEntity targetSlabEntity = new TargetSlabEntity();
                            targetSlabEntity.setOfferId(createOfferRequest.getId());
                            targetSlabEntity.setOnwardsTarget(targetSlab.getOnwardsAmount());
                            targetSlabEntity.setTargetDescription(targetSlab.getTargetDescription());
                            targetSlabEntity.setPayoutTarget(payoutSlab.getOnwardsAmount());
                            targetSlabEntity.setPayoutValue(payoutSlab.getPayoutAmount());
                            targetSlabEntity.setItemCriteriaId(itemCriteria.getId());
                            targetSlabEntity.setAmountType(itemCriteriaPayout.getAmountType());
                            targetSlabEntity.setStartDate(itemCriteriaPayout.getStartDate());
                            targetSlabEntity.setEndDate(itemCriteriaPayout.getEndDate());
                            offerTargetSlabRepository.persist(targetSlabEntity);
                        }
                    }
                }
            }

        } else {
            throw new ProfitMandiBusinessException("Invalid Target Slabs", "Invalid Target", "");
        }

    }

    @Override
    public List<CreateOfferRequest> getPublishedOffers(LocalDate date, int fofoId, int catalogId) throws ProfitMandiBusinessException {
        Map<Integer, List<Offer>> publishedMapByPartner = offerRepository.selectAllPublishedMapByPartner(YearMonth.from(date));
        List<Offer> publishedOffers = publishedMapByPartner != null ? publishedMapByPartner.get(fofoId) : null;
        List<CreateOfferRequest> createOfferRequests = new ArrayList<>();
        if (publishedOffers != null) {
            for (Offer offer : publishedOffers) {
                boolean found = false;
                List<com.spice.profitmandi.dao.model.TargetSlab> targetSlabs = offerTargetSlabRepository
                        .getByOfferId(offer.getId());
                CreateOfferRequest createOfferRequest = this.getCreateOfferRequest(offer);
                createOfferRequest.setTargetSlabs(targetSlabs);
                this.setCriteriaPayoutAchieved(fofoId, createOfferRequest);
                Set<com.spice.profitmandi.service.offers.ItemCriteria> itemCriteriaSet = targetSlabs.stream()
                        .flatMap(x -> x.getItemCriteriaPayouts().stream()).map(x -> x.getItemCriteria()).distinct()
                        .filter(x -> itemRepository.containsModel(x, catalogId)).collect(Collectors.toSet());

                for (com.spice.profitmandi.dao.model.TargetSlab targetSlab : targetSlabs) {
                    targetSlab.setItemCriteriaPayouts(targetSlab.getItemCriteriaPayouts().stream()
                            .filter(x -> itemCriteriaSet.contains(x.getItemCriteria())).collect(Collectors.toList()));
                    if (targetSlab.getItemCriteriaPayouts().size() > 0) {
                        found = true;
                    }
                }
                if (found) {
                    createOfferRequests.add(createOfferRequest);
                }
            }
        }

        return createOfferRequests;
    }

    @Override
    public void createOffers(InputStream fileInputStream) throws Exception {
        Map<CreateOfferRequest, List<PartnerTargetModel>> offerPartnerTargetMap = this.parseFromExcel(fileInputStream);
        Set<YearMonth> ymListToEvict = new HashSet<>();
        for (Map.Entry<CreateOfferRequest, List<PartnerTargetModel>> partnerTargetEntry : offerPartnerTargetMap
                .entrySet()) {
            CreateOfferRequest createOfferRequest = partnerTargetEntry.getKey();
            Map<List<Integer>, List<Integer>> targetPartnerMap = partnerTargetEntry.getValue().stream()
                    .collect(Collectors.groupingBy(PartnerTargetModel::getTargets,
                            Collectors.mapping(PartnerTargetModel::getFofoId, Collectors.toList())));
            for (Map.Entry<List<Integer>, List<Integer>> targetPartnersEntry : targetPartnerMap.entrySet()) {
                List<Integer> fofoIds = createOfferRequest.getPartnerCriteria().getFofoIds();
                List<Integer> filterFofoIds = targetPartnersEntry.getValue().stream().filter(x -> !fofoIds.contains(x))
                        .collect(Collectors.toList());
                LOGGER.info("FofoIds {}", filterFofoIds);
                List<Integer> targets = targetPartnersEntry.getKey();
                LOGGER.info("Targets  {}", targets);

                createOfferRequest.getPartnerCriteria().setFofoIds(filterFofoIds);
                int counter = 0;
                List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0)
                        .getItemCriteriaPayouts();
                List<PayoutSlab> payoutSlabs = itemCriteriaPayouts.stream().flatMap(x -> x.getPayoutSlabs().stream()).collect(Collectors.toList());
                for (PayoutSlab payoutSlab : payoutSlabs) {
                    payoutSlab.setOnwardsAmount(targets.get(counter));
                    counter++;
                }
                this.addOfferService(createOfferRequest);
            }
            ymListToEvict.add(YearMonth.from(createOfferRequest.getStartDate()));
        }
        for (YearMonth ymToEvict : ymListToEvict) {
            redisCacheManager.getCache("monthOfferIds").evict(ymToEvict);
        }
    }

    private Map<CreateOfferRequest, List<PartnerTargetModel>> parseFromExcel(InputStream inputStream)
            throws ProfitMandiBusinessException {

        Map<CreateOfferRequest, List<PartnerTargetModel>> offerPartnerTargetMap = new HashMap<>();
        XSSFWorkbook myWorkBook = null;
        try {
            myWorkBook = new XSSFWorkbook(inputStream);
            myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
            for (int i = 0; i < myWorkBook.getNumberOfSheets(); i++) {

                XSSFSheet mySheet = myWorkBook.getSheetAt(i);
                int offerId = Integer.parseInt(mySheet.getSheetName());
                CreateOfferRequest existingOffer = this.getOffer(0, offerId);
                List<PartnerTargetModel> partnerTargetModels = new ArrayList<>();
                offerPartnerTargetMap.put(existingOffer, partnerTargetModels);
                this.printIterator(mySheet);
                LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
                for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
                    XSSFRow row = mySheet.getRow(rowNumber);
                    LOGGER.info("row {}", row);
                    PartnerTargetModel partnerTargetModel = new PartnerTargetModel();

                    if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
                        partnerTargetModel.setFofoId((int) row.getCell(0).getNumericCellValue());
                    } else {
                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
                                "FOFO ID", row.getCell(0).toString(), "TGLSTNG_VE_1010");
                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                        throw profitMandiBusinessException;
                    }
                    List<Integer> targets = new ArrayList<>();
                    long targetsSize = existingOffer.getTargetSlabs().get(0).getItemCriteriaPayouts().stream().flatMap(x -> x.getPayoutSlabs().stream()).collect(Collectors.counting());
                    for (int cellNumber = 1; cellNumber <= targetsSize; cellNumber++) {
                        if (row.getCell(cellNumber) != null
                                && row.getCell(cellNumber).getCellTypeEnum() == CellType.NUMERIC) {
                            targets.add((int) row.getCell(cellNumber).getNumericCellValue());
                        } else {
                            ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
                                    "Target Column issue at " + rowNumber + ", " + cellNumber, row.getCell(cellNumber),
                                    "Invalid target");
                            LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                            throw profitMandiBusinessException;
                        }
                    }
                    partnerTargetModel.setTargets(targets);
                    partnerTargetModels.add(partnerTargetModel);
                }
            }
            myWorkBook.close();
        } catch (ProfitMandiBusinessException pbse) {
            throw pbse;
        } catch (IOException ioException) {
            ioException.printStackTrace();
            throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
                    "EXL_VE_1000");
        } finally {
            if (myWorkBook != null) {
                try {
                    myWorkBook.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        LOGGER.info("offerPartnerTargetMap {}", offerPartnerTargetMap);
        return offerPartnerTargetMap;
    }

    private void printIterator(XSSFSheet sheet) {
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            StringBuilder rowContent = new StringBuilder();
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        rowContent.append(cell.getNumericCellValue()).append("\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        rowContent.append(cell.getStringCellValue()).append("\t");
                        break;
                }
            }
            LOGGER.debug("Row content: {}", rowContent);
        }
    }

    @Override
    public Map<Integer, CreateOfferRequest> getPublishedOffers(LocalDate date, int fofoId, String brand) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public List<CreateOfferRequest> getActiveOffers(int fofoId) throws ProfitMandiBusinessException {
        List<Offer> activeOffers = offerRepository.selectCurrentlyActiveForPartner(fofoId);
        List<CreateOfferRequest> result = new ArrayList<>();

        for (Offer offer : activeOffers) {
            CreateOfferRequest copy = deepCopy(this.getCreateOfferRequest(offer));
            setCriteriaPayoutAchieved(fofoId, copy);
            result.add(copy);
        }

        return result;
    }

    @Override
    @Cacheable(value = "slabPayoutMap1", cacheManager = "oneDayCacheManager")
    public Map<Integer, Map<Integer, Long>> getSlabPayoutMap(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {
        Map<Integer, Map<Integer, Long>> catalogSlabPayoutMap = new HashMap<>();
        com.spice.profitmandi.dao.model.TargetSlab targetSlab = createOfferRequest.getTargetSlabs().get(0);
        List<ItemCriteriaPayout> payouts = targetSlab.getItemCriteriaPayouts();

        for (ItemCriteriaPayout itemCriteriaPayout : payouts) {
            com.spice.profitmandi.service.offers.ItemCriteria itemCriteria = itemCriteriaPayout.getItemCriteria();
            List<Integer> catalogIds = itemRepository.getCatalogIds(itemCriteria);
            for (PayoutSlab payoutSlab : Lists.reverse(itemCriteriaPayout.getPayoutSlabs())) {
                if (itemCriteriaPayout.getAmountType().equals(AmountType.FIXED)) {
                    for (Integer catalogId : catalogIds) {
                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {
                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());
                        }
                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),
                                (long) payoutSlab.getPayoutAmount());
                    }
                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.SLAB_FIXED)) {
                    for (Integer catalogId : catalogIds) {
                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {
                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());
                        }
                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),
                                (long) (payoutSlab.getPayoutAmount() / payoutSlab.getOnwardsAmount()));
                    }
                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.PERCENTAGE)) {
                    Map<Integer, TagListing> tagListingsMap = tagListingRepository.selectAllByCatalogIds(catalogIds);
                    for (Map.Entry<Integer, TagListing> tagListingEntry : tagListingsMap.entrySet()) {
                        TagListing tagListing = tagListingEntry.getValue();
                        if (!catalogSlabPayoutMap.containsKey(tagListingEntry.getKey())) {
                            catalogSlabPayoutMap.put(tagListingEntry.getKey(), new LinkedHashMap<>());
                        }
                        catalogSlabPayoutMap.get(tagListingEntry.getKey()).put(payoutSlab.getOnwardsAmount(),
                                (long) (tagListing.getSellingPrice() * payoutSlab.getPayoutAmount() / 100));
                    }
                }
            }
        }
        return catalogSlabPayoutMap;
    }

    @Override
    @Cacheable(value = "offer.slabpayout", cacheManager = "redisCacheManager", key = "#createOfferRequest.id")
    public Map<Integer, Map<Integer, AmountModel>> getSlabPayoutMapNew(CreateOfferRequest createOfferRequest) {
        Map<Integer, Map<Integer, AmountModel>> catalogSlabPayoutMap = new HashMap<>();
        com.spice.profitmandi.dao.model.TargetSlab targetSlab = createOfferRequest.getTargetSlabs().get(0);
        List<ItemCriteriaPayout> payouts = targetSlab.getItemCriteriaPayouts();

        for (ItemCriteriaPayout itemCriteriaPayout : payouts) {
            com.spice.profitmandi.service.offers.ItemCriteria itemCriteria = itemCriteriaPayout.getItemCriteria();
            List<Integer> catalogIds = itemRepository.getCatalogIds(itemCriteria);
            for (PayoutSlab payoutSlab : Lists.reverse(itemCriteriaPayout.getPayoutSlabs())) {
                if (itemCriteriaPayout.getAmountType().equals(AmountType.FIXED)) {
                    for (Integer catalogId : catalogIds) {
                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {
                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());
                        }
                        AmountModel amountModel = new AmountModel();
                        amountModel.setAmountType(AmountType.FIXED);
                        amountModel.setAmount(payoutSlab.getPayoutAmount());
                        amountModel.setDiscount(createOfferRequest.isDiscount());
                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),
                                amountModel);
                    }
                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.SLAB_FIXED)) {
                    for (Integer catalogId : catalogIds) {
                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {
                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());
                        }

                        AmountModel amountModel = new AmountModel();
                        amountModel.setAmountType(AmountType.FIXED);
                        amountModel.setDiscount(createOfferRequest.isDiscount());
                        amountModel.setAmount(payoutSlab.getPayoutAmount() / payoutSlab.getOnwardsAmount());
                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),
                                amountModel);
                    }
                } else if (itemCriteriaPayout.getAmountType().equals(AmountType.PERCENTAGE)) {
                    for (Integer catalogId : catalogIds) {
                        if (!catalogSlabPayoutMap.containsKey(catalogId)) {
                            catalogSlabPayoutMap.put(catalogId, new LinkedHashMap<>());
                        }
                        AmountModel amountModel = new AmountModel();
                        amountModel.setAmountType(AmountType.PERCENTAGE);
                        amountModel.setAmount(payoutSlab.getPayoutAmount());
                        amountModel.setDiscount(createOfferRequest.isDiscount());
                        catalogSlabPayoutMap.get(catalogId).put(payoutSlab.getOnwardsAmount(),
                                amountModel);
                    }
                }
            }
        }
        return catalogSlabPayoutMap;
    }


    private double getAmount(InventoryPayoutModel inventoryPayoutModel, AmountModel amountModel) {
        double rollout = 0;
        if (amountModel.getAmountType().equals(AmountType.PERCENTAGE)) {
            double effectiveDp = inventoryPayoutModel.getDp() - inventoryPayoutModel.getFixedAmount();
            double totalPercentage = inventoryPayoutModel.getPercentageAmount() + amountModel.getAmount();
            rollout = effectiveDp * (totalPercentage / (100 + totalPercentage) - (inventoryPayoutModel.getPercentageAmount() / (100 + inventoryPayoutModel.getPercentageAmount())));
        } else {
            rollout = amountModel.getAmount() * (100 / (100 + inventoryPayoutModel.getPercentageAmount()));
        }
        return rollout;
    }

    @Override
    public Map<Integer, QtyAmountModel> getCriteriaQtyAmounModelMap(CreateOfferRequest createOfferRequest, int fofoId) throws ProfitMandiBusinessException {
        Map<Integer, QtyAmountModel> qtyAmountMap;
        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.SELLIN)) {
            qtyAmountMap = getCriteriaQtyAmountModelMapForSellin(createOfferRequest, fofoId);
        } else {
            qtyAmountMap = getCriteriaQtyAmountModelMapForSelloutAndActivation(createOfferRequest, fofoId);
        }
        LOGGER.info("Qty Amount Map {}", qtyAmountMap);
        return qtyAmountMap;
    }

    private PayoutSlab findEligibleSlab(List<PayoutSlab> slabs, int achievedValue) {
        PayoutSlab eligibleSlab = null;
        for (PayoutSlab slab : slabs) {
            if (slab.getOnwardsAmount() <= achievedValue) {
                eligibleSlab = slab;
            } else {
                break;
            }
        }
        return eligibleSlab;
    }

    private void setSlabProgress(ItemCriteriaPayout itemCriteriaPayout, PayoutSlab eligibleSlab, int achievedValue) {
        List<PayoutSlab> slabs = itemCriteriaPayout.getPayoutSlabs();
        itemCriteriaPayout.setCurrentSlab(eligibleSlab);
        if (eligibleSlab == null) {
            if (!slabs.isEmpty()) {
                itemCriteriaPayout.setNextSlab(slabs.get(0));
                itemCriteriaPayout.setShortValue(slabs.get(0).getOnwardsAmount() - achievedValue);
            }
        } else {
            int idx = slabs.indexOf(eligibleSlab);
            if (idx < slabs.size() - 1) {
                PayoutSlab next = slabs.get(idx + 1);
                itemCriteriaPayout.setNextSlab(next);
                itemCriteriaPayout.setShortValue(next.getOnwardsAmount() - achievedValue);
            } else {
                itemCriteriaPayout.setNextSlab(null);
                itemCriteriaPayout.setShortValue(0);
            }
        }
    }

    private double calculateFinalPayout(PayoutSlab eligibleSlab, AmountType amountType,
                                        int valueForPercentage, int valueForFixed) {
        if (eligibleSlab == null) return 0;
        eligibleSlab.setSelected(true);
        float payoutRate = eligibleSlab.getPayoutAmount();
        if (AmountType.PERCENTAGE.equals(amountType)) {
            return (payoutRate * valueForPercentage) / 100;
        } else if (AmountType.FIXED.equals(amountType)) {
            return payoutRate * valueForFixed;
        } else {
            return payoutRate;
        }
    }

    private QtyAmountModel calculateSellinQtyAndValue(List<Order> orders) {
        int qty = orders.stream().collect(Collectors.summingInt(x ->
                x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()));
        int value = orders.stream().collect(Collectors.summingInt(x ->
                Math.round(x.getLineItem().getUnitPrice() *
                        (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))));
        return new QtyAmountModel(qty, value);
    }

    private Map<Integer, QtyAmountModel> getCriteriaQtyAmountModelMapForSellin(CreateOfferRequest createOfferRequest, int fofoId) throws ProfitMandiBusinessException {
        Map<Integer, QtyAmountModel> criteriaQtyAmountMap = new HashMap<>();

        Map<Integer, List<Order>> criteriaOrdersMap = offerRepository.getPartnerWiseSellin(fofoId, createOfferRequest, true);

        List<Order> baseCriteriaOrders = criteriaOrdersMap.get(0);
        Integer userBaseQty = null;
        Integer userBaseValue = null;
        if (baseCriteriaOrders != null && baseCriteriaOrders.size() > 0) {
            QtyAmountModel baseModel = calculateSellinQtyAndValue(baseCriteriaOrders);
            userBaseQty = baseModel.getQty();
            userBaseValue = (int) baseModel.getAmount();
            criteriaQtyAmountMap.put(0, baseModel);
        } else {
            criteriaQtyAmountMap.put(0, new QtyAmountModel(0, 0));
        }

        LOGGER.info("Processing sellin");
        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {
            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();

            QtyAmountModel qtyAmountModel = new QtyAmountModel(0, 0);
            criteriaQtyAmountMap.put(criteriaId, qtyAmountModel);

            List<Order> orders = criteriaOrdersMap.get(criteriaId);
            if (orders == null || orders.isEmpty()) continue;

            QtyAmountModel criteriaModel = calculateSellinQtyAndValue(orders);
            int criteriaQty = criteriaModel.getQty();
            int totalBaseQty = criteriaQty;
            int totalBaseValue = (int) criteriaModel.getAmount();
            if (userBaseQty != null) {
                totalBaseQty = userBaseQty;
                totalBaseValue = userBaseValue;
            }

            int purchasedValue;
            if (createOfferRequest.getTargetType().equals(AchievementType.VALUE)) {
                purchasedValue = totalBaseValue;
            } else {
                purchasedValue = totalBaseQty;
            }
            qtyAmountModel.setQty(totalBaseQty);
            qtyAmountModel.setAmount(totalBaseValue);

            PayoutSlab eligibleSlab = findEligibleSlab(itemCriteriaPayout.getPayoutSlabs(), purchasedValue);
            setSlabProgress(itemCriteriaPayout, eligibleSlab, purchasedValue);
            // For FIXED (per pc) payout, use per-criteria qty not base value
            int sellinValueForFixed = itemCriteriaPayout.getAmountType().equals(AmountType.FIXED) ? criteriaQty : purchasedValue;
            double finalPayout = calculateFinalPayout(eligibleSlab, itemCriteriaPayout.getAmountType(),
                    purchasedValue, sellinValueForFixed);
            qtyAmountModel.setFinalPayout(finalPayout);
        }
        return criteriaQtyAmountMap;
    }

    private Map<Integer, QtyAmountModel> getCriteriaQtyAmountModelMapForSelloutAndActivation(CreateOfferRequest createOfferRequest, int fofoId) {
        Map<Integer, Map<Integer, List<FofoOrderItem>>> criteriaPartnerwiseTertiary = offerRepository.getCriteriaWisePartnerTertiary(createOfferRequest, fofoId);
        Map<Integer, QtyAmountModel> qtyAmountMap;
        final Map<String, LocalDateTime> activatedImeisActivationDateMap;
        qtyAmountMap = new HashMap<>();
        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
            //All Serialnumbers for all eligible retailers
            List<String> serialNumbers = criteriaPartnerwiseTertiary.entrySet().stream().flatMap(x -> x.getValue().entrySet().stream().flatMap(foiList -> foiList.getValue().stream()))
                    .flatMap(y -> y.getFofoLineItems().stream()).filter(fli -> fli.getSerialNumber() != null)
                    .map(x -> x.getSerialNumber()).collect(Collectors.toList());
            if (serialNumbers.size() > 0) {
            /*if (imeisAllowedManually != null) {
                serialNumbers = serialNumbers.stream().filter(x -> imeisAllowedManually.contains(x)).collect(Collectors.toList());
            }*/
                activatedImeisActivationDateMap = activatedImeiRepository.selectBySerialNumbers(serialNumbers).stream()
                        .filter(x -> createOfferRequest.isWithinRange(x.getActivationTimestamp()))
                        .collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x.getActivationTimestamp()));
            } else {
                activatedImeisActivationDateMap = null;
            }
        } else {
            activatedImeisActivationDateMap = null;
        }

        QtyAmountModel qtyAmountModel = null;
        if (createOfferRequest.isBaseCriteria()) {
            Map<Integer, List<FofoOrderItem>> baseCriteriaMap = criteriaPartnerwiseTertiary.get(0);
            List<FofoOrderItem> fofoOrderItems = baseCriteriaMap == null ? null : baseCriteriaMap.get(fofoId);
            int totalBaseQty = fofoOrderItems == null ? 0 : fofoOrderItems.stream().filter(x->createOfferRequest.isWithinRange(x.getCreateTimestamp())).collect(Collectors.summingInt(x -> (x.getQuantity())));
            int totalBaseValue = fofoOrderItems == null ? 0 : fofoOrderItems.stream().filter(x->createOfferRequest.isWithinRange(x.getCreateTimestamp())).mapToInt(x -> (Math.round(x.getDp())) * x.getQuantity()).sum();
            qtyAmountModel = new QtyAmountModel(totalBaseQty, totalBaseValue);
            if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
                Map<Integer, Integer> inventoryItemValueMap = new HashMap<>();
                if (fofoOrderItems != null) {
                    for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
                        fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(
                                lineItem -> {
                                    if (activatedImeisActivationDateMap == null || activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {
                                        inventoryItemValueMap.put(lineItem.getInventoryItemId(), (int) fofoOrderItem.getDp());
                                    }
                                }
                        );
                    }
                }
                qtyAmountModel.setActivationQty(inventoryItemValueMap.size());
                qtyAmountModel.setActivationAmount(inventoryItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue())));
                qtyAmountMap.put(0, qtyAmountModel);
            }
        }

        //CriteriaPayoutWise
        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {
            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();
            Map<Integer, List<FofoOrderItem>> ordersMap = criteriaPartnerwiseTertiary.get(criteriaId);

            QtyAmountModel criteriaQtyAmountModel = new QtyAmountModel(0, 0);
            qtyAmountMap.put(criteriaId, criteriaQtyAmountModel);

            if (ordersMap == null) continue;

            List<FofoOrderItem> fofoOrderItems = ordersMap.get(fofoId);
            if (fofoOrderItems == null || fofoOrderItems.isEmpty()) continue;

            int totalQty = fofoOrderItems.stream().filter(x -> createOfferRequest.isWithinRange(x.getCreateTimestamp())).collect(Collectors.summingInt(x -> (x.getQuantity())));
            int totalValue = fofoOrderItems.stream().filter(x -> createOfferRequest.isWithinRange(x.getCreateTimestamp())).collect(Collectors.summingInt(x -> Math.round(x.getDp()) * (x.getQuantity())));
            int eligibleQty = totalQty;
            int eligibleValue = totalValue;
            criteriaQtyAmountModel.setQty(totalQty);
            criteriaQtyAmountModel.setAmount(totalValue);

            Map<FofoLineItem, Integer> lineItemValueMap = new HashMap<>();
            if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
                for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
                    fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(
                            lineItem -> {
                                if (activatedImeisActivationDateMap == null) {
                                    lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());
                                } else if (activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {
                                    if (itemCriteriaPayout.isWithinRange(activatedImeisActivationDateMap.get(lineItem.getSerialNumber()))) {
                                        lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());
                                    }
                                }
                            }
                    );
                }
                eligibleQty = lineItemValueMap.size();
                eligibleValue = lineItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue()));
                criteriaQtyAmountModel.setActivationQty(eligibleQty);
                criteriaQtyAmountModel.setActivationAmount(eligibleValue);
            }

            int eligibleBaseQty = eligibleQty;
            int eligibleBaseValue = eligibleValue;
            if (createOfferRequest.isBaseCriteria()) {
                eligibleBaseQty = qtyAmountModel.getActivationQty();
                eligibleBaseValue = (int) qtyAmountModel.getActivationAmount();
            }

            int eligibleSale = eligibleBaseValue;
            int eligibleCriteriaSale = eligibleValue;
            int eligibleCriteriaSaleDp = eligibleValue;

            if (createOfferRequest.getTargetType().equals(AchievementType.QUANTITY)) {
                eligibleSale = eligibleBaseQty;
                eligibleCriteriaSale = eligibleQty;
            }

            // For FIXED (per pc) payout, always use qty regardless of target type
            int valueForFixed = itemCriteriaPayout.getAmountType().equals(AmountType.FIXED) ? eligibleQty : eligibleCriteriaSale;

            PayoutSlab eligibleSlab = findEligibleSlab(itemCriteriaPayout.getPayoutSlabs(), eligibleSale);
            setSlabProgress(itemCriteriaPayout, eligibleSlab, eligibleSale);
            double finalPayout = calculateFinalPayout(eligibleSlab, itemCriteriaPayout.getAmountType(),
                    eligibleCriteriaSaleDp, valueForFixed);
            if (eligibleSlab != null) {
                LOGGER.info("Eligible Slab {}", eligibleSlab);
            }
            criteriaQtyAmountModel.setFinalPayout(finalPayout);
            LOGGER.info("Final Payout - {}", finalPayout);
        }
        return qtyAmountMap;
    }

    @Override
    public void processActivationtOffer(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {
        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByOfferId(createOfferRequest.getId());
        List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts();
        Map<Integer, ItemCriteriaPayout> itemCriteriaPayoutMap = itemCriteriaPayouts.stream().collect(Collectors.toMap(x -> x.getItemCriteria().getId(), x -> x));
        LOGGER.info("itemCriteriaPayoutMap {}", itemCriteriaPayoutMap);
        LOGGER.info("offerPayouts {}", offerPayouts);
        Map<Integer, Map<Integer, Double>> fixedSlabCriteriaPartnerMap = offerPayouts.stream().filter(x -> itemCriteriaPayoutMap.get((int) x.getCriteriaId()).getAmountType().equals(AmountType.SLAB_FIXED))
                .collect(Collectors.groupingBy(x -> (int) x.getCriteriaId(), Collectors.groupingBy(x -> (int) x.getFofoId(), Collectors.summingDouble(x -> x.getSlabAmount()))));


        Map<String, Double> serialNumberPaid = offerPayouts.stream().filter(x -> x.getRejectTimestamp() == null).collect(Collectors.groupingBy(x -> x.getSerialNumber(), Collectors.summingDouble(x -> x.getSlabAmount())));
        Map<Integer, Map<Integer, List<FofoOrderItem>>> criteriaPartnerwiseTertiary = offerRepository.getCriteriaWisePartnerTertiary(createOfferRequest);

        Map<String, AgeingSummaryModel> ageingSummaryModelsMap = null;
        final Map<String, LocalDateTime> activatedImeisActivationDateMap;
        //Find Only eligible imeis if any
        List<String> imeisAllowedManually = offerEligibleImeiRepository.selectEligibleImeisByOfferId(createOfferRequest.getId());

        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
            //All Serialnumbers for all eligible retailers
            List<String> serialNumbers = criteriaPartnerwiseTertiary.entrySet().stream().flatMap(x -> x.getValue().entrySet().stream().flatMap(foiList -> foiList.getValue().stream()))
                    .flatMap(y -> y.getFofoLineItems().stream()).filter(fli -> fli.getSerialNumber() != null)
                    .map(x -> x.getSerialNumber()).collect(Collectors.toList());
            if (serialNumbers.size() > 0) {
                if (imeisAllowedManually != null) {
                    serialNumbers = serialNumbers.stream().filter(x -> imeisAllowedManually.contains(x)).collect(Collectors.toList());
                }
                activatedImeisActivationDateMap = activatedImeiRepository.selectBySerialNumbers(serialNumbers).stream()
                        .filter(x -> createOfferRequest.isWithinRange(x.getActivationTimestamp()))
                        .collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x.getActivationTimestamp()));
                ageingSummaryModelsMap = warehouseInventoryItemRepository.findStockAgeingByFofoIdSerialNumbers(0, serialNumbers).stream().collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x, (u, v) -> v));
            } else {
                activatedImeisActivationDateMap = null;
            }
        } else {
            activatedImeisActivationDateMap = null;
        }

        Map<Integer, Integer> userBaseQtyMap = new HashMap<>();
        Map<Integer, Integer> userBaseValueMap = new HashMap<>();
        Map<Integer, Integer> eligibleBaseQtyMap = new HashMap<>();
        Map<Integer, Integer> eligibleBaseValueMap = new HashMap<>();

        if (createOfferRequest.isBaseCriteria()) {
            Map<Integer, List<FofoOrderItem>> foiMap = criteriaPartnerwiseTertiary.get(0);
            for (Map.Entry<Integer, List<FofoOrderItem>> orderEntry : foiMap.entrySet()) {
                int fofoId = orderEntry.getKey();
                List<FofoOrderItem> fofoOrderItems = orderEntry.getValue();
                int totalBaseQty = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (x.getQuantity())));
                int totalBaseValue = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (Math.round(x.getDp())) * x.getQuantity()));
                userBaseQtyMap.put(fofoId, totalBaseQty);
                userBaseValueMap.put(fofoId, totalBaseValue);
                if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
                    Map<Integer, Integer> inventoryItemValueMap = new HashMap<>();
                    for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
                        fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(
                                lineItem -> {
                                    if (activatedImeisActivationDateMap == null || activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {
                                        inventoryItemValueMap.put(lineItem.getInventoryItemId(), (int) fofoOrderItem.getDp());
                                    }
                                }
                        );
                    }
                    eligibleBaseQtyMap.put(fofoId, inventoryItemValueMap.size());
                    eligibleBaseValueMap.put(fofoId, inventoryItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue())));
                } else {
                    eligibleBaseQtyMap.put(fofoId, totalBaseQty);
                    eligibleBaseValueMap.put(fofoId, totalBaseValue);

                }

            }
        }

        //As of now eligible sale is considered to be without special criteria like brand/
        //CriteriaPayoutWise
        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {
            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();
            Map<Integer, List<FofoOrderItem>> ordersMap = criteriaPartnerwiseTertiary.get(criteriaId);
            //Partywise iteration
            for (Map.Entry<Integer, List<FofoOrderItem>> orderEntry : ordersMap.entrySet()) {
                double totalRolloutAmount = 0;
                int fofoId = orderEntry.getKey();
                List<FofoOrderItem> fofoOrderItems = orderEntry.getValue();
                OfferRowModel offerRowModel = new OfferRowModel();
                int totalQty = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (x.getQuantity())));
                int totalValue = fofoOrderItems.stream().collect(Collectors.summingInt(x -> Math.round(x.getDp()) * (x.getQuantity())));
                int eligibleQty = totalQty;
                int eligibleValue = totalValue;
                Map<FofoLineItem, Integer> lineItemValueMap = new HashMap<>();
                if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
                    for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
                        fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(
                                lineItem -> {
                                    if (activatedImeisActivationDateMap == null) {
                                        lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());

                                    } else if (activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {
                                        if (itemCriteriaPayout.isWithinRange(activatedImeisActivationDateMap.get(lineItem.getSerialNumber()))) {
                                            lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());
                                        }
                                    }

                                }
                        );
                    }
                    eligibleQty = lineItemValueMap.size();
                    eligibleValue = lineItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue()));
                    offerRowModel.setEligibleImeis(lineItemValueMap.keySet().stream().map(x -> x.getSerialNumber()).collect(Collectors.toList()));
                }

                int totalBaseQty = totalQty;
                int totalBaseValue = totalValue;
                int eligibleBaseQty = eligibleQty;
                int eligibleBaseValue = eligibleValue;
                if (createOfferRequest.isBaseCriteria()) {
                    totalBaseQty = userBaseQtyMap.get(fofoId) == null ? 0 : userBaseQtyMap.get(fofoId);
                    totalBaseValue = userBaseValueMap.get(fofoId) == null ? 0 : userBaseValueMap.get(fofoId);
                    eligibleBaseQty = eligibleBaseQtyMap.get(fofoId) == null ? 0 : eligibleBaseQtyMap.get(fofoId);
                    eligibleBaseValue = eligibleBaseValueMap.get(fofoId) == null ? 0 : eligibleBaseValueMap.get(fofoId);
                }


                int totalSale = totalBaseValue;
                int eligibleSale = eligibleBaseValue;
                int eligibleCriteriaSale = eligibleValue;
                int eligibleCriteriaSaleDp = eligibleValue;

                if (createOfferRequest.getTargetType().equals(AchievementType.QUANTITY)) {
                    totalSale = totalBaseQty;
                    eligibleSale = eligibleBaseQty;
                    eligibleCriteriaSale = eligibleQty;
                }

                offerRowModel.setTotalSale(totalSale);
                offerRowModel.setEligibleSale(eligibleSale);
                float eligiblePayoutValue = 0;
                float previousPayoutValue = 0;
                double finalPayout;
                for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {
                    if (payoutSlab.getOnwardsAmount() <= eligibleSale) {
                        LOGGER.info("Eligible Sale {}", eligibleSale);
                        offerRowModel.setPayoutTargetAchieved(payoutSlab.getOnwardsAmount());
                        eligiblePayoutValue = payoutSlab.getPayoutAmount();
                        previousPayoutValue = Math.min(eligiblePayoutValue, previousPayoutValue);
                        offerRowModel.setPayoutValue(eligiblePayoutValue);
                    } else {
                        break;
                    }
                }
                if (AmountType.PERCENTAGE.equals(itemCriteriaPayout.getAmountType())) {
                    finalPayout = (eligiblePayoutValue * eligibleCriteriaSaleDp) / 100;
                } else if (AmountType.FIXED.equals(itemCriteriaPayout.getAmountType())) {
                    // FIXED = per piece, always use qty regardless of target type
                    finalPayout = eligiblePayoutValue * eligibleQty;
                } else {
                    Map<Integer, Double> fofoCriteriaMap = fixedSlabCriteriaPartnerMap.get(criteriaId);
                    LOGGER.info("fofoCriteriaMap {}", fofoCriteriaMap);
                    finalPayout = eligiblePayoutValue;
                    if (fofoCriteriaMap != null) {
                        if (fofoCriteriaMap.containsKey(fofoId)) {
                            //double totalPayout = fofoCriteriaMap.get(fofoId);
                            finalPayout = eligiblePayoutValue - previousPayoutValue;
                            if (Math.abs(finalPayout) < Utils.DOUBLE_EPSILON) {
                                finalPayout = 0;
                            }
                        }
                    }
                }

                LOGGER.info("Final Payout - {}", finalPayout);

                if (finalPayout > 0) {
                    List<Integer> inventoryItemIds = lineItemValueMap.keySet().stream().map(x -> x.getInventoryItemId()).collect(Collectors.toList());
                    int totalDpValue = lineItemValueMap.values().stream().collect(Collectors.summingInt(x -> x));
                    LOGGER.info("Total DP Value {}", totalDpValue);
                    Map<Integer, InventoryItem> inventoryItemsMap = inventoryItemRepository.selectAllByIds(inventoryItemIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));
                    for (Map.Entry<FofoLineItem, Integer> lineItemValueEntry : lineItemValueMap.entrySet()) {
                        FofoLineItem fofoLineItem = lineItemValueEntry.getKey();
                        double amount = 0;
                        if (AmountType.SLAB_FIXED.equals(itemCriteriaPayout.getAmountType())) {
                            amount = (eligiblePayoutValue * lineItemValueEntry.getValue()) / totalDpValue;
                        } else {
                            if (ageingSummaryModelsMap.containsKey(fofoLineItem.getSerialNumber())) {
                                if (ageingSummaryModelsMap.get(fofoLineItem.getSerialNumber()).isAgedAbove(STOCK_AGEING_THRESHOLD_DAYS)) {
                                    continue;
                                }
                            }
                            amount = eligiblePayoutValue;
                        }
                        if (serialNumberPaid.containsKey(fofoLineItem.getSerialNumber())) {
                            amount = amount - serialNumberPaid.get(fofoLineItem.getSerialNumber());
                        }
                        LOGGER.info("Amount - {}", amount);
                        //ignore reasonably small
                        if (amount < Utils.DOUBLE_EPSILON) continue;
                        AmountModel amountModel = new AmountModel();
                        amountModel.setAmountType(itemCriteriaPayout.getAmountType());
                        amountModel.setAmount(amount);
                        amountModel.setDiscount(createOfferRequest.isDiscount());
                        InventoryPayoutModel inventoryPayoutModel = priceCircularService.getPayouts(inventoryItemsMap.get(fofoLineItem.getInventoryItemId()));
                        double rolloutAmount = inventoryPayoutModel.getRolloutAmount(amountModel);
                        totalRolloutAmount += rolloutAmount;

                        OfferPayout offerPayout = new OfferPayout(fofoId, createOfferRequest.getId(), criteriaId, amount, fofoLineItem.getSerialNumber(), rolloutAmount, SchemePayoutStatus.CREDITED, createOfferRequest.getDescription(), LocalDateTime.now());
                        offerPayout.setInventoryItemId(fofoLineItem.getInventoryItemId());
                        offerPayoutRepository.persist(offerPayout);
                    }
                    walletService.addAmountToWallet((int) fofoId, createOfferRequest.getId(), WalletReferenceType.ADDITIONAL_SCHEME, createOfferRequest.getDescription(), (float) totalRolloutAmount, LocalDateTime.now());
                }
            }
        }
        Offer offer = offerRepository.selectById(createOfferRequest.getId());
        offer.setProcessedTimestamp(LocalDateTime.now());
    }

    @Override
    @Transactional(readOnly = true)
    public List<OfferPartnerPayoutData> calculateOfferPayouts(CreateOfferRequest createOfferRequest) throws ProfitMandiBusinessException {
        List<OfferPartnerPayoutData> result = new ArrayList<>();

        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByOfferId(createOfferRequest.getId());
        List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts();
        Map<Integer, ItemCriteriaPayout> itemCriteriaPayoutMap = itemCriteriaPayouts.stream().collect(Collectors.toMap(x -> x.getItemCriteria().getId(), x -> x));

        Map<Integer, Map<Integer, Double>> fixedSlabCriteriaPartnerMap = offerPayouts.stream()
                .filter(x -> itemCriteriaPayoutMap.containsKey((int) x.getCriteriaId()) &&
                        itemCriteriaPayoutMap.get((int) x.getCriteriaId()).getAmountType().equals(AmountType.SLAB_FIXED))
                .collect(Collectors.groupingBy(x -> (int) x.getCriteriaId(),
                        Collectors.groupingBy(x -> (int) x.getFofoId(), Collectors.summingDouble(x -> x.getSlabAmount()))));

        Map<String, Double> serialNumberPaid = offerPayouts.stream()
                .filter(x -> x.getRejectTimestamp() == null)
                .collect(Collectors.groupingBy(x -> x.getSerialNumber(), Collectors.summingDouble(x -> x.getSlabAmount())));

        Map<Integer, Map<Integer, List<FofoOrderItem>>> criteriaPartnerwiseTertiary = offerRepository.getCriteriaWisePartnerTertiary(createOfferRequest);

        Map<String, AgeingSummaryModel> ageingSummaryModelsMap = null;
        final Map<String, LocalDateTime> activatedImeisActivationDateMap;
        List<String> imeisAllowedManually = offerEligibleImeiRepository.selectEligibleImeisByOfferId(createOfferRequest.getId());

        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
            List<String> serialNumbers = criteriaPartnerwiseTertiary.entrySet().stream()
                    .flatMap(x -> x.getValue().entrySet().stream().flatMap(foiList -> foiList.getValue().stream()))
                    .flatMap(y -> y.getFofoLineItems().stream()).filter(fli -> fli.getSerialNumber() != null)
                    .map(x -> x.getSerialNumber()).collect(Collectors.toList());
            if (serialNumbers.size() > 0) {
                if (imeisAllowedManually != null) {
                    serialNumbers = serialNumbers.stream().filter(x -> imeisAllowedManually.contains(x)).collect(Collectors.toList());
                }
                activatedImeisActivationDateMap = activatedImeiRepository.selectBySerialNumbers(serialNumbers).stream()
                        .filter(x -> createOfferRequest.isWithinRange(x.getActivationTimestamp()))
                        .collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x.getActivationTimestamp()));
                ageingSummaryModelsMap = warehouseInventoryItemRepository.findStockAgeingByFofoIdSerialNumbers(0, serialNumbers)
                        .stream().collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x, (u, v) -> v));
            } else {
                activatedImeisActivationDateMap = null;
            }
        } else {
            activatedImeisActivationDateMap = null;
        }

        Map<Integer, Integer> userBaseQtyMap = new HashMap<>();
        Map<Integer, Integer> userBaseValueMap = new HashMap<>();
        Map<Integer, Integer> calcEligibleBaseQtyMap = new HashMap<>();
        Map<Integer, Integer> calcEligibleBaseValueMap = new HashMap<>();

        if (createOfferRequest.isBaseCriteria()) {
            Map<Integer, List<FofoOrderItem>> foiMap = criteriaPartnerwiseTertiary.get(0);
            if (foiMap != null) {
                for (Map.Entry<Integer, List<FofoOrderItem>> orderEntry : foiMap.entrySet()) {
                    int fofoId = orderEntry.getKey();
                    List<FofoOrderItem> fofoOrderItems = orderEntry.getValue();
                    int totalBaseQty = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (x.getQuantity())));
                    int totalBaseValue = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (Math.round(x.getDp())) * x.getQuantity()));
                    userBaseQtyMap.put(fofoId, totalBaseQty);
                    userBaseValueMap.put(fofoId, totalBaseValue);
                    if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
                        Map<Integer, Integer> inventoryItemValueMap = new HashMap<>();
                        for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
                            fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(
                                    lineItem -> {
                                        if (activatedImeisActivationDateMap == null || activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {
                                            inventoryItemValueMap.put(lineItem.getInventoryItemId(), (int) fofoOrderItem.getDp());
                                        }
                                    }
                            );
                        }
                        calcEligibleBaseQtyMap.put(fofoId, inventoryItemValueMap.size());
                        calcEligibleBaseValueMap.put(fofoId, inventoryItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue())));
                    } else {
                        calcEligibleBaseQtyMap.put(fofoId, totalBaseQty);
                        calcEligibleBaseValueMap.put(fofoId, totalBaseValue);
                    }
                }
            }
        }

        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {
            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();
            Map<Integer, List<FofoOrderItem>> ordersMap = criteriaPartnerwiseTertiary.get(criteriaId);
            if (ordersMap == null) continue;

            for (Map.Entry<Integer, List<FofoOrderItem>> orderEntry : ordersMap.entrySet()) {
                int fofoId = orderEntry.getKey();
                List<FofoOrderItem> fofoOrderItems = orderEntry.getValue();
                int totalQty = fofoOrderItems.stream().collect(Collectors.summingInt(x -> (x.getQuantity())));
                int totalValue = fofoOrderItems.stream().collect(Collectors.summingInt(x -> Math.round(x.getDp()) * (x.getQuantity())));
                int eligibleQty = totalQty;
                int eligibleValue = totalValue;
                Map<FofoLineItem, Integer> lineItemValueMap = new HashMap<>();
                if (createOfferRequest.getSchemeType().equals(OfferSchemeType.ACTIVATION)) {
                    for (FofoOrderItem fofoOrderItem : fofoOrderItems) {
                        fofoOrderItem.getFofoLineItems().stream().filter(x -> x.getSerialNumber() != null).forEach(
                                lineItem -> {
                                    if (activatedImeisActivationDateMap == null) {
                                        lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());
                                    } else if (activatedImeisActivationDateMap.containsKey(lineItem.getSerialNumber())) {
                                        if (itemCriteriaPayout.isWithinRange(activatedImeisActivationDateMap.get(lineItem.getSerialNumber()))) {
                                            lineItemValueMap.put(lineItem, (int) fofoOrderItem.getDp());
                                        }
                                    }
                                }
                        );
                    }
                    eligibleQty = lineItemValueMap.size();
                    eligibleValue = lineItemValueMap.entrySet().stream().collect(Collectors.summingInt(x -> x.getValue()));
                }

                int eligibleBaseQty = eligibleQty;
                int eligibleBaseValue = eligibleValue;
                if (createOfferRequest.isBaseCriteria()) {
                    eligibleBaseQty = calcEligibleBaseQtyMap.get(fofoId) == null ? 0 : calcEligibleBaseQtyMap.get(fofoId);
                    eligibleBaseValue = calcEligibleBaseValueMap.get(fofoId) == null ? 0 : calcEligibleBaseValueMap.get(fofoId);
                }

                int eligibleSale = eligibleBaseValue;
                int eligibleCriteriaSaleDp = eligibleValue;
                if (createOfferRequest.getTargetType().equals(AchievementType.QUANTITY)) {
                    eligibleSale = eligibleBaseQty;
                }

                float eligiblePayoutValue = 0;
                float previousPayoutValue = 0;
                double finalPayout;
                for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {
                    if (payoutSlab.getOnwardsAmount() <= eligibleSale) {
                        eligiblePayoutValue = payoutSlab.getPayoutAmount();
                        previousPayoutValue = Math.min(eligiblePayoutValue, previousPayoutValue);
                    } else {
                        break;
                    }
                }
                if (AmountType.PERCENTAGE.equals(itemCriteriaPayout.getAmountType())) {
                    finalPayout = (eligiblePayoutValue * eligibleCriteriaSaleDp) / 100;
                } else if (AmountType.FIXED.equals(itemCriteriaPayout.getAmountType())) {
                    finalPayout = eligiblePayoutValue * eligibleQty;
                } else {
                    Map<Integer, Double> fofoCriteriaMap = fixedSlabCriteriaPartnerMap.get(criteriaId);
                    finalPayout = eligiblePayoutValue;
                    if (fofoCriteriaMap != null && fofoCriteriaMap.containsKey(fofoId)) {
                        finalPayout = eligiblePayoutValue - previousPayoutValue;
                        if (Math.abs(finalPayout) < Utils.DOUBLE_EPSILON) {
                            finalPayout = 0;
                        }
                    }
                }

                if (finalPayout > 0) {
                    result.add(new OfferPartnerPayoutData(
                            fofoId, criteriaId, finalPayout, eligiblePayoutValue,
                            lineItemValueMap, itemCriteriaPayout, serialNumberPaid,
                            ageingSummaryModelsMap, createOfferRequest.isDiscount()));
                }
            }
        }

        return result;
    }

    @Override
    public void processSellin(CreateOfferRequest createOfferRequest) throws Exception {

        //List<OfferRowModel> offerRowModels = new ArrayList<>();
        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByOfferId(createOfferRequest.getId());
        Map<String, Double> serialNumberPaid = offerPayouts.stream().filter(x -> x.getRejectTimestamp() == null).collect(Collectors.groupingBy(x -> x.getSerialNumber(), Collectors.summingDouble(x -> x.getSlabAmount())));
        LOGGER.info("serialNumberPaid {}", serialNumberPaid);
        List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts();
        Map<Integer, ItemCriteriaPayout> itemCriteriaPayoutMap = itemCriteriaPayouts.stream().collect(Collectors.toMap(x -> x.getItemCriteria().getId(), x -> x));
        Map<Integer, Map<Integer, Double>> fixedSlabCriteriaPartnerMap = offerPayouts.stream().filter(x -> itemCriteriaPayoutMap.get((int) x.getCriteriaId()).getAmountType().equals(AmountType.SLAB_FIXED))
                .collect(Collectors.groupingBy(x -> (int) x.getCriteriaId(), Collectors.groupingBy(x -> (int) x.getFofoId(), Collectors.summingDouble(x -> x.getSlabAmount()))));
        Set<Long> fofoIds = offerPayouts.stream().map(x -> x.getFofoId()).collect(Collectors.toSet());

        Map<Integer, Map<Integer, List<Order>>> criteriaOrdersMap = offerRepository.getPartnerWiseSellin(createOfferRequest, true);


        Map<Integer, List<Order>> userBaseCriteriaOrdersMap = null;
        if (criteriaOrdersMap.get(0) != null && criteriaOrdersMap.get(0).size() > 0) {
            userBaseCriteriaOrdersMap = criteriaOrdersMap.get(0).entrySet().stream()
                    .filter(x -> !fofoIds.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));
        }


        Map<Integer, Integer> userBaseQtyMap = null;
        Map<Integer, Integer> userBaseValueMap = null;
        if (userBaseCriteriaOrdersMap != null) {
            userBaseQtyMap = new HashMap<>();
            userBaseValueMap = new HashMap<>();
            for (Map.Entry<Integer, List<Order>> orderEntry : userBaseCriteriaOrdersMap.entrySet()) {
                int fofoId = orderEntry.getKey();
                List<Order> orders = orderEntry.getValue();
                int totalBaseQty = orders.stream().collect(Collectors.summingInt(x -> (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty())));
                int totalBaseValue = orders.stream().collect(Collectors.summingInt(x ->
                        Math.round(x.getLineItem().getUnitPrice() * (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))
                ));
                userBaseQtyMap.put(fofoId, totalBaseQty);
                userBaseValueMap.put(fofoId, totalBaseValue);
            }
        }
        LOGGER.info("Base Qty Map - {}", userBaseQtyMap);
        LOGGER.info("Base Value Map - {}", userBaseValueMap);

        LOGGER.info("Processing sellin");
        List<OfferPayout> offerPayoutsNew = new ArrayList<>();
        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {
            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();
            Map<Integer, List<Order>> ordersMap = criteriaOrdersMap.get(criteriaId).entrySet().stream()
                    .filter(x -> !fofoIds.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));
            for (Map.Entry<Integer, List<Order>> orderEntry : ordersMap.entrySet()) {
                int fofoId = orderEntry.getKey();
                //CustomRetailer retailer = retailerService.getAllFofoRetailers().get(fofoId);
                List<Order> orders = orderEntry.getValue();
                //Set<String> returnedSerialNumbers = orders.stream().filter(x -> x.getSerialNumber() != null).flatMap(x -> x.getReturnedImeis().stream()).collect(Collectors.toSet());
                int totalQty = orders.stream().collect(Collectors.summingInt(x -> (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty())));
                int totalValue = orders.stream().collect(Collectors.summingInt(x -> Math.round(x.getLineItem().
                        getUnitPrice() * (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))));
                int totalBaseQty = totalQty;
                int totalBaseValue = totalValue;
                if (userBaseCriteriaOrdersMap != null) {
                    if (userBaseQtyMap.containsKey(fofoId)) {
                        totalBaseQty = userBaseQtyMap.get(fofoId);
                        totalBaseValue = userBaseValueMap.get(fofoId);
                    } else {
                        continue;
                    }
                }

                int purchasedValue;
                if (createOfferRequest.getTargetType().equals(AchievementType.VALUE)) {
                    purchasedValue = totalBaseValue;
                } else {
                    purchasedValue = totalBaseQty;
                }

                PayoutSlab eligibleSlab = null;

                for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {
                    if (payoutSlab.getOnwardsAmount() <= purchasedValue) {
                        eligibleSlab = payoutSlab;
                    } else {
                        break;
                    }
                }
                if (eligibleSlab == null) continue;
                List<Integer> orderIds = orders.stream().map(x -> x.getId()).collect(Collectors.toList());
                Map<Integer, LineItem> lineItemMap = orders.stream().collect(Collectors.toMap(x -> x.getLineItem().getId(), x -> x.getLineItem()));
                List<WarehouseScan> warehouseScans = warehouseScanRepository.selectAllByOrderIds(orderIds);
                LOGGER.info("Warehosue scans --> {}", warehouseScans);
                List<Integer> warehouseInventoryItemIds = warehouseScans.stream().collect(Collectors.groupingBy(x -> x.getInventoryItemId())).entrySet()
                        .stream().filter(x -> x.getValue().size() == 1).map(x -> x.getKey()).collect(Collectors.toList());

                List<OfferPayout> partnerPayouts = offerPayouts.stream().filter(x -> x.getFofoId() == fofoId).collect(Collectors.toList());
                Set<String> serialNumbersRejected = partnerPayouts.stream().filter(x -> x.getStatus().equals(SchemePayoutStatus.REJECTED)).map(x -> x.getSerialNumber()).collect(Collectors.toSet());

                Set<String> billedNotCancelledSerialNumbers;
                LOGGER.info("warehouseInventoryItemIds {}", warehouseInventoryItemIds);
                if (warehouseInventoryItemIds.size() > 0) {
                    List<WarehouseInventoryItem> warehouseInventoryItems = warehouseInventoryItemRepository.selectAllByIds(warehouseInventoryItemIds);
                    billedNotCancelledSerialNumbers = warehouseInventoryItems.stream().map(x -> x.getSerialNumber()).collect(Collectors.toSet());
                } else {
                    billedNotCancelledSerialNumbers = new HashSet<>();
                }
                LOGGER.info("billedNotCancelledSerialNumbers {}", billedNotCancelledSerialNumbers);

                Map<String, LineItem> serialNumberMap = lineItemImeisRepository.selectByLineItemIds(new ArrayList<>(lineItemMap.keySet()))
                        .stream().filter(x -> !serialNumbersRejected.contains(x.getSerialNumber())).collect(Collectors.toMap(x -> x.getSerialNumber(), x -> lineItemMap.get(x.getLineItemId()), (x1, x2) -> x1.getId() > x2.getId() ? x1 : x2));

                List<String> manuallyEligibleImeis = offerEligibleImeiRepository.selectEligibleImeisByOfferId(createOfferRequest.getId());

                serialNumberMap = serialNumberMap.entrySet().stream().filter(x -> billedNotCancelledSerialNumbers.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));

                //Just a validation need to revaluate this
                Map<String, InventoryItem> serialNumberInventoryItemMap = this.getInventoryItemMap(fofoId, serialNumberMap);
                if (serialNumberInventoryItemMap.size() < billedNotCancelledSerialNumbers.size()) continue;

                LOGGER.info("serialNumberMap {}", serialNumberMap);
                if (manuallyEligibleImeis != null) {
                    serialNumberMap = serialNumberMap.entrySet().stream().filter(x -> manuallyEligibleImeis.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));
                    if (serialNumberMap.size() == 0) continue;
                    serialNumberInventoryItemMap = this.getInventoryItemMap(fofoId, serialNumberMap);
                }
                if (serialNumberMap.size() == 0) continue;
                //Continue if all imeis havent been grned


                float eligiblePayoutValue = eligibleSlab.getPayoutAmount();
                double finalPayout;
                if (AmountType.PERCENTAGE.equals(itemCriteriaPayout.getAmountType())) {
                    finalPayout = (eligiblePayoutValue * purchasedValue) / 100;
                } else if (AmountType.FIXED.equals(itemCriteriaPayout.getAmountType())) {
                    // FIXED = per piece, use per-criteria qty not base value
                    finalPayout = eligiblePayoutValue * totalQty;
                } else {
                    Map<Integer, Double> fofoCriteriaPayoutMap = fixedSlabCriteriaPartnerMap.get(criteriaId);
                    LOGGER.info("fofoCriteriaMap {}", fofoCriteriaPayoutMap);
                    finalPayout = eligiblePayoutValue;
                    if (fofoCriteriaPayoutMap != null && fofoCriteriaPayoutMap.containsKey(fofoId)) {
                        finalPayout = eligiblePayoutValue - fofoCriteriaPayoutMap.get(fofoId);
                        if (Math.abs(finalPayout) < Utils.DOUBLE_EPSILON) {
                            finalPayout = 0;
                        }
                    }
                }

                LOGGER.info("Or - {}", finalPayout);

                if (finalPayout > 0) {
                    double totalRolloutAmount = 0;
                    int totalDpValue = serialNumberInventoryItemMap.entrySet().stream().mapToInt(x -> (int) x.getValue().getUnitPrice()).sum();
                    for (Map.Entry<String, InventoryItem> serialNumberInventoryItemEntry : serialNumberInventoryItemMap.entrySet()) {
                        double amount = 0;
                        LOGGER.info("Total DP Value {}", totalDpValue);
                        if (AmountType.SLAB_FIXED.equals(itemCriteriaPayout.getAmountType())) {
                            amount = (eligiblePayoutValue * serialNumberInventoryItemEntry.getValue().getUnitPrice()) / totalDpValue;
                            LOGGER.info("Amount - {}", amount);
                        } else {
                            amount = eligiblePayoutValue;
                            if (serialNumberPaid.containsKey(serialNumberInventoryItemEntry.getKey())) {
                                LOGGER.info("serial number slab amount {}", serialNumberPaid.get(serialNumberInventoryItemEntry.getKey()));
                                amount = amount - serialNumberPaid.get(serialNumberInventoryItemEntry.getKey());
                            }
                        }
                        LOGGER.info("Amount - {}", amount);
                        //ignore reasonably small
                        if (Math.abs(amount) < Utils.DOUBLE_EPSILON) continue;
                        AmountModel amountModel = new AmountModel();
                        amountModel.setAmountType(itemCriteriaPayout.getAmountType());
                        amountModel.setAmount(amount);
                        amountModel.setDiscount(createOfferRequest.isDiscount());
                        InventoryPayoutModel inventoryPayoutModel = priceCircularService.getPayouts(serialNumberInventoryItemEntry.getValue());
                        double rolloutAmount = inventoryPayoutModel.getRolloutAmount(amountModel);
                        totalRolloutAmount += rolloutAmount;

                        OfferPayout offerPayout = new OfferPayout(fofoId, createOfferRequest.getId(), criteriaId, amount, serialNumberInventoryItemEntry.getKey(), rolloutAmount, SchemePayoutStatus.CREDITED, createOfferRequest.getDescription(), LocalDateTime.now());
                        offerPayout.setInventoryItemId(serialNumberInventoryItemEntry.getValue().getId());
                        offerPayoutRepository.persist(offerPayout);
                    }
                    walletService.addAmountToWallet(fofoId, createOfferRequest.getId(), WalletReferenceType.ADDITIONAL_SCHEME, createOfferRequest.getDescription(), (float) totalRolloutAmount, LocalDateTime.now());
                }
            }
        }
        Offer offer = offerRepository.selectById(createOfferRequest.getId());
        offer.setProcessedTimestamp(LocalDateTime.now());
    }

    @Override
    @Transactional(readOnly = true)
    public List<SellinPartnerPayoutData> calculateSellinPayouts(CreateOfferRequest createOfferRequest) throws Exception {
        List<SellinPartnerPayoutData> result = new ArrayList<>();

        List<OfferPayout> offerPayouts = offerPayoutRepository.selectAllByOfferId(createOfferRequest.getId());
        Map<String, Double> serialNumberPaid = offerPayouts.stream()
                .filter(x -> x.getRejectTimestamp() == null)
                .collect(Collectors.groupingBy(x -> x.getSerialNumber(), Collectors.summingDouble(x -> x.getSlabAmount())));

        List<ItemCriteriaPayout> itemCriteriaPayouts = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts();
        Map<Integer, ItemCriteriaPayout> itemCriteriaPayoutMap = itemCriteriaPayouts.stream()
                .collect(Collectors.toMap(x -> x.getItemCriteria().getId(), x -> x));
        Map<Integer, Map<Integer, Double>> fixedSlabCriteriaPartnerMap = offerPayouts.stream()
                .filter(x -> itemCriteriaPayoutMap.containsKey((int) x.getCriteriaId()) &&
                        itemCriteriaPayoutMap.get((int) x.getCriteriaId()).getAmountType().equals(AmountType.SLAB_FIXED))
                .collect(Collectors.groupingBy(x -> (int) x.getCriteriaId(),
                        Collectors.groupingBy(x -> (int) x.getFofoId(), Collectors.summingDouble(x -> x.getSlabAmount()))));
        Set<Long> fofoIds = offerPayouts.stream().map(x -> x.getFofoId()).collect(Collectors.toSet());

        Map<Integer, Map<Integer, List<Order>>> criteriaOrdersMap = offerRepository.getPartnerWiseSellin(createOfferRequest, true);

        Map<Integer, List<Order>> userBaseCriteriaOrdersMap = null;
        if (criteriaOrdersMap.get(0) != null && criteriaOrdersMap.get(0).size() > 0) {
            userBaseCriteriaOrdersMap = criteriaOrdersMap.get(0).entrySet().stream()
                    .filter(x -> !fofoIds.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));
        }

        Map<Integer, Integer> userBaseQtyMap = null;
        Map<Integer, Integer> userBaseValueMap = null;
        if (userBaseCriteriaOrdersMap != null) {
            userBaseQtyMap = new HashMap<>();
            userBaseValueMap = new HashMap<>();
            for (Map.Entry<Integer, List<Order>> orderEntry : userBaseCriteriaOrdersMap.entrySet()) {
                int fofoId = orderEntry.getKey();
                List<Order> orders = orderEntry.getValue();
                int totalBaseQty = orders.stream().collect(Collectors.summingInt(x -> (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty())));
                int totalBaseValue = orders.stream().collect(Collectors.summingInt(x ->
                        Math.round(x.getLineItem().getUnitPrice() * (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))
                ));
                userBaseQtyMap.put(fofoId, totalBaseQty);
                userBaseValueMap.put(fofoId, totalBaseValue);
            }
        }

        for (ItemCriteriaPayout itemCriteriaPayout : createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()) {
            int criteriaId = itemCriteriaPayout.getItemCriteria().getId();
            if (criteriaOrdersMap.get(criteriaId) == null) continue;
            Map<Integer, List<Order>> ordersMap = criteriaOrdersMap.get(criteriaId).entrySet().stream()
                    .filter(x -> !fofoIds.contains(x.getKey())).collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));

            for (Map.Entry<Integer, List<Order>> orderEntry : ordersMap.entrySet()) {
                int fofoId = orderEntry.getKey();
                List<Order> orders = orderEntry.getValue();
                int totalQty = orders.stream().collect(Collectors.summingInt(x -> (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty())));
                int totalValue = orders.stream().collect(Collectors.summingInt(x -> Math.round(x.getLineItem().
                        getUnitPrice() * (x.getLineItem().getQuantity() - x.getLineItem().getReturnQty()))));
                int totalBaseQty = totalQty;
                int totalBaseValue = totalValue;
                if (userBaseCriteriaOrdersMap != null) {
                    if (userBaseQtyMap.containsKey(fofoId)) {
                        totalBaseQty = userBaseQtyMap.get(fofoId);
                        totalBaseValue = userBaseValueMap.get(fofoId);
                    } else {
                        continue;
                    }
                }

                int purchasedValue;
                if (createOfferRequest.getTargetType().equals(AchievementType.VALUE)) {
                    purchasedValue = totalBaseValue;
                } else {
                    purchasedValue = totalBaseQty;
                }

                PayoutSlab eligibleSlab = null;
                for (PayoutSlab payoutSlab : itemCriteriaPayout.getPayoutSlabs()) {
                    if (payoutSlab.getOnwardsAmount() <= purchasedValue) {
                        eligibleSlab = payoutSlab;
                    } else {
                        break;
                    }
                }
                if (eligibleSlab == null) continue;

                List<Integer> orderIds = orders.stream().map(x -> x.getId()).collect(Collectors.toList());
                Map<Integer, LineItem> lineItemMap = orders.stream().collect(Collectors.toMap(x -> x.getLineItem().getId(), x -> x.getLineItem()));
                List<WarehouseScan> warehouseScans = warehouseScanRepository.selectAllByOrderIds(orderIds);
                List<Integer> warehouseInventoryItemIds = warehouseScans.stream().collect(Collectors.groupingBy(x -> x.getInventoryItemId())).entrySet()
                        .stream().filter(x -> x.getValue().size() == 1).map(x -> x.getKey()).collect(Collectors.toList());

                List<OfferPayout> partnerPayouts = offerPayouts.stream().filter(x -> x.getFofoId() == fofoId).collect(Collectors.toList());
                Set<String> serialNumbersRejected = partnerPayouts.stream().filter(x -> x.getStatus().equals(SchemePayoutStatus.REJECTED)).map(x -> x.getSerialNumber()).collect(Collectors.toSet());

                Set<String> billedNotCancelledSerialNumbers;
                if (warehouseInventoryItemIds.size() > 0) {
                    List<WarehouseInventoryItem> warehouseInventoryItems = warehouseInventoryItemRepository.selectAllByIds(warehouseInventoryItemIds);
                    billedNotCancelledSerialNumbers = warehouseInventoryItems.stream().map(x -> x.getSerialNumber()).collect(Collectors.toSet());
                } else {
                    billedNotCancelledSerialNumbers = new HashSet<>();
                }

                Map<String, LineItem> serialNumberMap = lineItemImeisRepository.selectByLineItemIds(new ArrayList<>(lineItemMap.keySet()))
                        .stream().filter(x -> !serialNumbersRejected.contains(x.getSerialNumber()))
                        .collect(Collectors.toMap(x -> x.getSerialNumber(), x -> lineItemMap.get(x.getLineItemId()), (x1, x2) -> x1.getId() > x2.getId() ? x1 : x2));

                List<String> manuallyEligibleImeis = offerEligibleImeiRepository.selectEligibleImeisByOfferId(createOfferRequest.getId());

                serialNumberMap = serialNumberMap.entrySet().stream().filter(x -> billedNotCancelledSerialNumbers.contains(x.getKey()))
                        .collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));

                Map<String, InventoryItem> serialNumberInventoryItemMap = this.getInventoryItemMap(fofoId, serialNumberMap);
                if (serialNumberInventoryItemMap.size() < billedNotCancelledSerialNumbers.size()) continue;

                if (manuallyEligibleImeis != null) {
                    serialNumberMap = serialNumberMap.entrySet().stream().filter(x -> manuallyEligibleImeis.contains(x.getKey()))
                            .collect(Collectors.toMap(x -> x.getKey(), x -> x.getValue()));
                    if (serialNumberMap.size() == 0) continue;
                    serialNumberInventoryItemMap = this.getInventoryItemMap(fofoId, serialNumberMap);
                }
                if (serialNumberMap.size() == 0) continue;

                float eligiblePayoutValue = eligibleSlab.getPayoutAmount();
                double finalPayout;
                if (AmountType.PERCENTAGE.equals(itemCriteriaPayout.getAmountType())) {
                    finalPayout = (eligiblePayoutValue * purchasedValue) / 100;
                } else if (AmountType.FIXED.equals(itemCriteriaPayout.getAmountType())) {
                    finalPayout = eligiblePayoutValue * totalQty;
                } else {
                    Map<Integer, Double> fofoCriteriaPayoutMap = fixedSlabCriteriaPartnerMap.get(criteriaId);
                    finalPayout = eligiblePayoutValue;
                    if (fofoCriteriaPayoutMap != null && fofoCriteriaPayoutMap.containsKey(fofoId)) {
                        finalPayout = eligiblePayoutValue - fofoCriteriaPayoutMap.get(fofoId);
                        if (Math.abs(finalPayout) < Utils.DOUBLE_EPSILON) {
                            finalPayout = 0;
                        }
                    }
                }

                if (finalPayout > 0) {
                    result.add(new SellinPartnerPayoutData(
                            fofoId, criteriaId, finalPayout, eligiblePayoutValue,
                            serialNumberInventoryItemMap, itemCriteriaPayout,
                            serialNumberPaid, createOfferRequest.isDiscount()));
                }
            }
        }

        return result;
    }

    private void rollbackPayout(int fofoId, int offerId, List<
            OfferPayout> partnerPayouts, Set<String> returnedSerialNumbers) throws ProfitMandiBusinessException {
        LOGGER.info("Rollback payout called for offerId {}", offerId);
        float amountToRollback = 0;
        int totalImeis = 0;
        for (OfferPayout partnerPayout : partnerPayouts) {
            LOGGER.info("serialNumberInventoryItemMap - {}", returnedSerialNumbers);
            LOGGER.info("Serial Number - {}", partnerPayout.getSerialNumber());
            if (returnedSerialNumbers.contains(partnerPayout.getSerialNumber()) && partnerPayout.getStatus().equals(SchemePayoutStatus.CREDITED)) {
                partnerPayout.setRejectTimestamp(LocalDateTime.now());
                partnerPayout.setStatus(SchemePayoutStatus.REJECTED);
                amountToRollback += partnerPayout.getAmount();
                totalImeis += 1;
            }
        }
        if (amountToRollback > 0) {
            LOGGER.info("Amount to Rollback - {}", amountToRollback);
            walletService.rollbackAmountFromWallet(fofoId, amountToRollback, offerId, WalletReferenceType.ADDITIONAL_SCHEME, "Stock Returned, total " + totalImeis + "pc(s)", LocalDateTime.now());
        }
    }

    private Map<String, InventoryItem> getInventoryItemMap(int fofoId, Map<String, LineItem> serialNumberMap) throws
            ProfitMandiBusinessException {
        if (serialNumberMap.isEmpty()) {
            return new HashMap<>();
        }
        List<InventoryItem> inventoryItems = inventoryItemRepository.selectBySerialNumbers(serialNumberMap.keySet());
        inventoryItems = inventoryItems.stream().filter(x -> x.getFofoId() == fofoId).collect(Collectors.toList());
        Map<String, InventoryItem> inventoryItemMap = inventoryItems.stream().collect(Collectors.toMap(x -> x.getSerialNumber(), x -> x, (u, v) -> v.getCreateTimestamp().isAfter(u.getCreateTimestamp()) ? v : u));
        return inventoryItemMap;
    }


    @Override
    public String getPartnerCriteriaString(PartnerCriteria partnerCriteria) throws ProfitMandiBusinessException {
        StringBuilder sb = new StringBuilder();
        if (partnerCriteria.getFofoIds().size() > 0) {
            List<Integer> fofoIds = partnerCriteria.getFofoIds();
            appendPartnerCodes(fofoIds, sb);

        } else {
            sb.append("All");
            if (partnerCriteria.getPartnerTypes().size() > 0) {
                sb.append(" ").append(String.join(", ", partnerCriteria.getPartnerTypes().stream().map(x -> x.getValue()).collect(Collectors.toList())));
            }
            sb.append(" partners ");
            if (partnerCriteria.getRegionIds().size() > 0) {
                sb.append("from ");
                sb.append(String.join(", ", partnerCriteria.getRegionIds().stream().map(x -> ProfitMandiConstants.WAREHOUSE_MAP.get(x)).collect(Collectors.toList())));
            }
            if (partnerCriteria.getExcludeFofoIds() != null && partnerCriteria.getExcludeFofoIds().size() > 0) {
                sb.append("<b>excluding</b> ");
                appendPartnerCodes(partnerCriteria.getExcludeFofoIds(), sb);
            }
        }
        return sb.toString();
    }

    @Override
    public ByteArrayOutputStream createCSVOfferReport(CreateOfferRequest createOfferRequest) throws Exception {
        List<List<?>> listOfRows = new ArrayList<>();
        ByteArrayOutputStream baos = null;
        Collection<OfferRowModel> offerRowModels = offerRepository.getOfferRows(createOfferRequest);
        ItemCriteriaPayout itemCriteriaPayout = createOfferRequest.getTargetSlabs().get(0).getItemCriteriaPayouts().get(0);
        // Batch fetch all retailers once to avoid N+1 queries
        Map<Integer, CustomRetailer> allRetailers = retailerService.getAllFofoRetailers();
        if (createOfferRequest.getSchemeType().equals(OfferSchemeType.SELLIN)) {
            for (OfferRowModel offerRowModel : offerRowModels) {
                CustomRetailer customRetailer = allRetailers.get(offerRowModel.getFofoId());
                listOfRows.add(Arrays.asList(
                        createOfferRequest.getId()
                        , createOfferRequest.getName()
                        , createOfferRequest.getTargetType()
                        , createOfferRequest.getSchemeType()
                        , itemCriteriaPayout.getStartDate()
                        , itemCriteriaPayout.getEndDate()
//                        , createOfferRequest.getBrandShareTerms()
                        , createOfferRequest.getSellinPercentage()
                        , "--"
                        , createOfferRequest.getItemCriteriaString()
                        , createOfferRequest.getStartDate()
                        , createOfferRequest.getEndDate()
                        , createOfferRequest.getCreatedOn()
                        , customRetailer.getPartnerId()
                        , customRetailer.getBusinessName()
                        , customRetailer.getCode()
                        , offerRowModel.getTotalBasePurchaseValue()
                        , offerRowModel.getAchievedTarget()
                        , offerRowModel.getPayoutPurchaseValue()
                        , offerRowModel.getPayout()
                        , offerRowModel.getPayoutType()
                        , offerRowModel.getFinalPayout()
                        , String.join(", ", offerRowModel.getEligibleImeis())));
            }
            baos = FileUtil.getCSVByteStream(
                    Arrays.asList(
                            "Id"
                            , "Name"
                            , "Target Type"
                            , "Scheme Type"
                            , "Payout Start"
                            , "Payout End"
//                            , "Brand %"
                            , "Sellin %"
                            , "Partner Criteria"
                            , "Item Criteria"
                            , "Start"
                            , "End"
                            , "Created"
                            , "Partner Id"
                            , "Partner Name"
                            , "Partner Code"
                            , "Base Purchase"
                            , "Achieved Target"
                            , "Eligible Purchase"
                            , "Slab Amount"
                            , "Slab Amount Type"
                            , "Payout Value(Rs.)"
                            , "Eligible IMEIs"
                            , "Billing Pending Imeis"
                    ), listOfRows);
        } else {
            for (OfferRowModel offerRowModel : offerRowModels) {
                CustomRetailer customRetailer = allRetailers.get(offerRowModel.getFofoId());
                listOfRows.add(Arrays.asList(
                                createOfferRequest.getId()
                                , createOfferRequest.getName()
                                , createOfferRequest.getTargetType()
                                , createOfferRequest.getSchemeType()
                                , itemCriteriaPayout.getStartDate()
                                , itemCriteriaPayout.getEndDate()
//                                , createOfferRequest.getBrandShareTerms()
                                , createOfferRequest.getSellinPercentage()
                                , createOfferRequest.getPartnerCriteriaString()
                                , createOfferRequest.getItemCriteriaString()
                                , createOfferRequest.getStartDate()
                                , createOfferRequest.getEndDate()
                                , createOfferRequest.getCreatedOn()
                                , customRetailer.getPartnerId()
                                , customRetailer.getBusinessName()
                                , customRetailer.getCode()
                                , offerRowModel.getTotalSale()
                                , offerRowModel.getEligibleSale()
                                , offerRowModel.getPayoutTargetAchieved()
                                , offerRowModel.getPayoutValue()
                                , offerRowModel.getPayoutType()
                                , offerRowModel.getPayoutValueDp()
                                , offerRowModel.getFinalPayout()
                                , String.join(", ", offerRowModel.getEligibleImeis())
//                        ,String.join(", ", offerRowModel.getBaseCriteria())
                        )
                );
            }
            baos = FileUtil.getCSVByteStream(
                    Arrays.asList(
                            "Id"
                            , "Name"
                            , "Target Type"
                            , "Scheme Type"
                            , "Payout Start"
                            , "Payout End"
//                            , "Brand %"
                            , "Sellin %"
                            , "Partner Criteria"
                            , "Item Criteria"
                            , "Start"
                            , "End"
                            , "Created"
                            , "Partner Id"
                            , "Partner Name"
                            , "Partner Code"
                            , "Total Sale"
                            , "Eligible Sale"
                            , "Payout Target Achieved"
                            , "Payout Amount"
                            , "Payout Amount Type"
                            , "Payout Value DP"
                            , "Amount to be credited"
                            , "Eligible Activated Imeis"
                    ), listOfRows);
        }
        return baos;
    }

    @Override
    public void sendWhatsapp(Offer offer, List<Integer> fofoIds, String imageUrl) throws Exception {
        if (fofoIds == null) {
            fofoIds = fofoStoreRepository.selectActiveStores().stream().map(x -> x.getId()).collect(Collectors.toList());
        }
        final List<Integer> finalFofoIds = fofoIds;
        //List<String> mobileNumbers = retailerService.getAllFofoRetailers().entrySet().stream().filter(x -> finalFofoIds.contains(x.getKey())).map(x -> x.getValue().getMobileNumber()).collect(Collectors.toList());
        List<String> mobileNumbers = new ArrayList<>();
        mobileNumbers.add("9911565032");
        //mobileNumbers.add("9990381569");
        //mobileNumbers.add("8383849914");
        String message = "%s\n" +
                "On %s of select models\n" +
                "From %s to %s\n" +
                "\n" +
                "Happy Selling\n" +
                "Team Smartdukaan";
        //TV's mobile
        for (String mobileNumber : mobileNumbers) {
            notificationService.sendWhatsappMediaMessage(String.format(message, offer.getName(),
                    offer.getSchemeType().toString(), FormattingUtils.formatDate(offer.getStartDate()),
                    FormattingUtils.formatDate(offer.getEndDate())), mobileNumber, imageUrl, "offer-" + offer.getId() + ".png", WhatsappMessageType.IMAGE);
        }
    }

    private void appendPartnerCodes(List<Integer> fofoIds, StringBuilder sb) throws ProfitMandiBusinessException {
        Map<Integer, CustomRetailer> customRetailersMap = retailerService.getAllFofoRetailers();
        List<String> businessNames = fofoIds.stream().map(x -> customRetailersMap.get(x)).filter(x -> x != null).map(x -> x.getBusinessName()).collect(Collectors.toList());
        sb.append(String.join(", ", businessNames));
    }

    @Override
    public void deleteOffer(int offerId) throws ProfitMandiBusinessException {
        Offer offer = offerRepository.selectById(offerId);
        if (offer == null) {
            throw new ProfitMandiBusinessException("Offer not found", "Offer not found", "");
        }
        if (offer.isActive()) {
            throw new ProfitMandiBusinessException("Cannot delete a published offer", "Cannot delete a published offer", "");
        }
        this.evictOfferCaches(offerId);
        List<TargetSlabEntity> targetSlabs = offerTargetSlabRepository.selectByOfferIds(Collections.singletonList(offerId));
        for (TargetSlabEntity targetSlab : targetSlabs) {
            genericRepository.delete(targetSlab);
        }
        genericRepository.delete(offer);
        LOGGER.info("Deleted offer {} and {} target slabs", offerId, targetSlabs.size());
    }

    @Override
    public List<Offer> publishAllUnpublished(YearMonth yearMonth) throws ProfitMandiBusinessException {
        List<Offer> allOffers = offerRepository.selectAll(yearMonth, false);
        List<Offer> unpublished = allOffers.stream().filter(o -> !o.isActive()).collect(Collectors.toList());
        for (Offer offer : unpublished) {
            offer.setActive(true);
        }
        LOGGER.info("Published {} unpublished offers for {}", unpublished.size(), yearMonth);
        return unpublished;
    }

    @Override
    public void removePartnersFromOffer(int offerId, List<Integer> fofoIds) throws ProfitMandiBusinessException {
        Offer offer = offerRepository.selectById(offerId);
        if (offer == null) {
            throw new ProfitMandiBusinessException("Offer not found", "Offer not found", "");
        }
        PartnerCriteria partnerCriteria = gson.fromJson(offer.getPartnerCriteria(), PartnerCriteria.class);
        if (partnerCriteria.getFofoIds() != null) {
            partnerCriteria.getFofoIds().removeAll(fofoIds);
        }
        offer.setPartnerCriteria(gson.toJson(partnerCriteria));
        genericRepository.persist(offer);
        LOGGER.info("Removed partners {} from offer {}", fofoIds, offerId);
    }

    @Override
    public void addPartnersToOffer(int offerId, List<Integer> fofoIds) throws ProfitMandiBusinessException {
        Offer offer = offerRepository.selectById(offerId);
        if (offer == null) {
            throw new ProfitMandiBusinessException("Offer not found", "Offer not found", "");
        }
        PartnerCriteria partnerCriteria = gson.fromJson(offer.getPartnerCriteria(), PartnerCriteria.class);
        List<Integer> existingFofoIds = partnerCriteria.getFofoIds();
        if (existingFofoIds == null) {
            existingFofoIds = new ArrayList<>();
        }
        for (int fofoId : fofoIds) {
            if (!existingFofoIds.contains(fofoId)) {
                existingFofoIds.add(fofoId);
            }
        }
        partnerCriteria.setFofoIds(existingFofoIds);
        offer.setPartnerCriteria(gson.toJson(partnerCriteria));
        genericRepository.persist(offer);
        LOGGER.info("Added partners {} to offer {}", fofoIds, offerId);
    }

    @Override
    public int cloneOfferForPartners(int sourceOfferId, List<Integer> fofoIds, List<Integer> targets) throws ProfitMandiBusinessException {
        Offer sourceOffer = offerRepository.selectById(sourceOfferId);
        if (sourceOffer == null) {
            throw new ProfitMandiBusinessException("Source offer not found", "Source offer not found", "");
        }
        CreateOfferRequest clonedRequest = this.getCreateOfferRequest(sourceOffer);
        clonedRequest.setId(0);
        clonedRequest.setActive(false);

        PartnerCriteria newPartnerCriteria = new PartnerCriteria();
        newPartnerCriteria.setFofoIds(new ArrayList<>(fofoIds));
        newPartnerCriteria.setExcludeFofoIds(new ArrayList<>());
        newPartnerCriteria.setRegionIds(clonedRequest.getPartnerCriteria().getRegionIds() != null
                ? new ArrayList<>(clonedRequest.getPartnerCriteria().getRegionIds()) : new ArrayList<>());
        newPartnerCriteria.setPartnerTypes(clonedRequest.getPartnerCriteria().getPartnerTypes() != null
                ? new ArrayList<>(clonedRequest.getPartnerCriteria().getPartnerTypes()) : new ArrayList<>());
        clonedRequest.setPartnerCriteria(newPartnerCriteria);

        if (targets != null && !targets.isEmpty() && !clonedRequest.getTargetSlabs().isEmpty()) {
            List<PayoutSlab> payoutSlabs = clonedRequest.getTargetSlabs().get(0).getItemCriteriaPayouts()
                    .stream().flatMap(x -> x.getPayoutSlabs().stream()).collect(Collectors.toList());
            int counter = 0;
            for (PayoutSlab payoutSlab : payoutSlabs) {
                if (counter < targets.size()) {
                    payoutSlab.setOnwardsAmount(targets.get(counter));
                    counter++;
                }
            }
        }

        this.addOfferService(clonedRequest);
        LOGGER.info("Cloned offer {} to new offer {} for partners {}", sourceOfferId, clonedRequest.getId(), fofoIds);
        return clonedRequest.getId();
    }

    @Override
    public void updateOfferTargets(int offerId, List<Integer> newTargets) throws ProfitMandiBusinessException {
        Offer offer = offerRepository.selectById(offerId);
        if (offer == null) {
            throw new ProfitMandiBusinessException("Offer not found", "Offer not found", "");
        }
        List<TargetSlabEntity> targetSlabs = offerTargetSlabRepository.selectEntitiesByOfferId(offerId);
        targetSlabs.sort(Comparator.comparing(TargetSlabEntity::getPayoutTarget));

        // Group slabs by unique payout_target (multiple item_criteria share the same target value)
        LinkedHashMap<Integer, List<TargetSlabEntity>> groupedByTarget = new LinkedHashMap<>();
        for (TargetSlabEntity slab : targetSlabs) {
            groupedByTarget.computeIfAbsent(slab.getPayoutTarget(), k -> new ArrayList<>()).add(slab);
        }
        List<Integer> uniqueTargets = new ArrayList<>(groupedByTarget.keySet());

        if (newTargets.size() != uniqueTargets.size()) {
            throw new ProfitMandiBusinessException(
                    "Target count mismatch. Expected " + uniqueTargets.size() + " but got " + newTargets.size(),
                    "Target count mismatch", "");
        }

        // Update each group: all slabs sharing the same old target get the new target value
        for (int i = 0; i < uniqueTargets.size(); i++) {
            int oldTarget = uniqueTargets.get(i);
            int newTarget = newTargets.get(i);
            if (oldTarget != newTarget) {
                for (TargetSlabEntity slab : groupedByTarget.get(oldTarget)) {
                    genericRepository.updateById(TargetSlabEntity.class, "payoutTarget", newTarget, slab.getId());
                }
            }
        }
        LOGGER.info("Updated targets for offer {} to {}", offerId, newTargets);
    }

    @Override
    public void updateOfferSlabs(com.spice.profitmandi.dao.model.UpdateOfferSlabsRequest request) throws ProfitMandiBusinessException {
        Offer offer = offerRepository.selectById(request.getOfferId());
        if (offer == null) {
            throw new ProfitMandiBusinessException("Offer not found", "Offer not found", "");
        }

        List<TargetSlabEntity> existingSlabs = offerTargetSlabRepository.selectEntitiesByOfferId(request.getOfferId());
        Set<Integer> validSlabIds = existingSlabs.stream().map(TargetSlabEntity::getId).collect(Collectors.toSet());

        for (com.spice.profitmandi.dao.model.UpdateSlabRequest slab : request.getSlabs()) {
            if (!validSlabIds.contains(slab.getId())) {
                throw new ProfitMandiBusinessException("Invalid slab ID " + slab.getId(),
                        "Slab does not belong to offer " + request.getOfferId(), "");
            }
            genericRepository.updateById(TargetSlabEntity.class, "payoutTarget", slab.getPayoutTarget(), slab.getId());
            genericRepository.updateById(TargetSlabEntity.class, "payoutValue", slab.getPayoutValue(), slab.getId());
        }
        LOGGER.info("Updated slabs for offer {}: {}", request.getOfferId(), request.getSlabs());
    }
}