Subversion Repositories SmartDukaan

package com.smartdukaan.cron.scheduled;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.BrandStockPrice;
import com.spice.profitmandi.common.model.CustomRetailer;
import com.spice.profitmandi.common.model.ProfitMandiConstants;
import com.spice.profitmandi.common.util.FormattingUtils;
import com.spice.profitmandi.common.util.Utils;
import com.spice.profitmandi.dao.cart.SmartCartService;
import com.spice.profitmandi.dao.entity.auth.AuthUser;
import com.spice.profitmandi.dao.entity.catalog.TagListing;
import com.spice.profitmandi.dao.entity.fofo.*;
import com.spice.profitmandi.dao.entity.logistics.AST;
import com.spice.profitmandi.dao.entity.logistics.ASTRepository;
import com.spice.profitmandi.dao.entity.transaction.*;
import com.spice.profitmandi.dao.entity.user.User;
import com.spice.profitmandi.dao.enumuration.cs.EscalationType;
import com.spice.profitmandi.dao.enumuration.transaction.LoanReferenceType;
import com.spice.profitmandi.dao.model.*;
import com.spice.profitmandi.dao.repository.auth.AuthRepository;
import com.spice.profitmandi.dao.repository.catalog.TagListingRepository;
import com.spice.profitmandi.dao.repository.cs.CsService;
import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;
import com.spice.profitmandi.dao.repository.fofo.*;
import com.spice.profitmandi.dao.repository.inventory.StateRepository;
import com.spice.profitmandi.dao.repository.transaction.*;
import com.spice.profitmandi.dao.repository.user.UserRepository;
import com.spice.profitmandi.dao.service.solr.FofoSolr;
import com.spice.profitmandi.service.PartnerStatsService;
import com.spice.profitmandi.service.RbmTargetService;
import com.spice.profitmandi.service.inventory.*;
import com.spice.profitmandi.service.transaction.SDCreditService;
import com.spice.profitmandi.service.user.RetailerService;
import com.spice.profitmandi.service.wallet.WalletService;
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.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.mail.MessagingException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
import java.io.*;
import java.math.BigDecimal;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;

import static java.util.stream.Collectors.toList;

@Component
@Transactional(rollbackFor = {Throwable.class, ProfitMandiBusinessException.class})
public class ScheduledTasksTest {

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

    @Autowired
    TransactionRepository transactionRepository;

    @Autowired
    @Qualifier(value = "googleMailSender")
    private JavaMailSender googleMailSender;

    @Autowired
    LoanRepository loanRepository;

    @Autowired
    SDCreditService sdCreditService;

    @Autowired
    SmartCartSuggestionRepository smartCartSuggestionRepository;

    @Autowired
    UserRepository userRepository;

    @Autowired
    CsService csService;

    @Autowired
    RbmRatingRepository rbmRatingRepository;

    @Autowired
    private JavaMailSender mailSender;

    @Autowired
    SalesRatingRepository salesRatingRepository;

    @Autowired
    FofoStoreRepository fofoStoreRepository;

    @Autowired
    SmartCartService smartCartService;

    @Autowired
    RetailerService retailerService;

    @Autowired
    ASTRepository astRepository;

    @Autowired
    AuthRepository authRepository;

    @Autowired
    StateRepository stateRepository;

    @Autowired
    MonthlyTargetRepository monthlyTargetRepository;

    @Autowired
    PartnerTypeChangeService partnerTypeChangeService;

    @Autowired
    ReturnOrderInfoRepository returnOrderInfoRepository;

    @Autowired
    OrderRepository orderRepository;

    @Autowired
    FofoOrderItemRepository fofoOrderItemRepository;

    @Autowired
    InventoryService inventoryService;

    @Autowired
    UserWalletRepository userWalletRepository;

    @Autowired
    LoanStatementRepository loanStatementRepository;

    @Autowired
    ActivatedImeiRepository activatedImeiRepository;

    @Autowired
    PartnerDailyInvestmentRepository partnerDailyInvestmentRepository;

    @Autowired
    SaholicInventoryService saholicInventoryService;

    @Autowired
    WalletService walletService;

    @Autowired
    RbmTargetService rbmTargetService;

    @Autowired
    PartnerStatsService partnerStatsService;

    @Autowired
    AgeingService ageingService;

    @Autowired
    TagListingRepository tagListingRepository;

    @Autowired
    UserWalletHistoryRepository userWalletHistoryRepository;

    @Autowired
    FofoSolr fofoSolr;

    public void test() throws Exception {

        fofoSolr.populateTagItems();

        System.out.println("test end");

    }

    public void generateBiReport() throws Exception {
        this.generateBiReportExcel();
    }

    public void createLoanForBillingByTransactionIdAndInvoiceNumber(int transactionId, double invoiceAmount, String invoiceNumber) throws Exception {
        sdCreditService.createLoanForBilling(transactionId, invoiceAmount, invoiceNumber);

    }

    public void loanSettle() throws Exception {
        List<Integer> refrences = Arrays.asList(25807,36003,38938,39506,42219,45084);
        for(Integer ref : refrences){
            List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(ref);
            double amountSum = loanStatements.stream().map(LoanStatement::getAmount).mapToDouble(BigDecimal::doubleValue).sum();
            if(amountSum > 0){
                walletService.addAmountToWallet(loanStatements.get(0).getFofoId(),ref, WalletReferenceType.CREDIT_LIMIT,"Amount reversal against credit limit deduction",(float) amountSum,LocalDateTime.now());

//                Loan statement entry
                    BigDecimal adjustAmount = BigDecimal.valueOf(amountSum).negate(); // or multiply by -1
                    LoanStatement loanStatement = new LoanStatement();
                    loanStatement.setAmount(adjustAmount);
                    loanStatement.setFofoId(loanStatements.get(0).getFofoId());
                    loanStatement.setLoanReferenceType(LoanReferenceType.PRINCIPAL);
                    loanStatement.setCreatedAt(LocalDateTime.now());
                    loanStatement.setDescription("Amount reversal due to access debit against limit");
                    loanStatement.setLoanId(ref);
                    loanStatement.setBusinessDate(LocalDateTime.now());
                    loanStatementRepository.persist(loanStatement);

                    Loan loan = loanRepository.selectByLoanId(ref);
                    loan.setPendingAmount(BigDecimal.valueOf(0));
                    loan.setSettledOn(LocalDateTime.now());
                }


        }
    }



    private void sendMailHtmlFormat(String email[], String body, String cc[], String bcc[], String subject)
            throws MessagingException, ProfitMandiBusinessException, IOException {
        MimeMessage message = mailSender.createMimeMessage();
        MimeMessageHelper helper = new MimeMessageHelper(message);
        helper.setSubject(subject);
        helper.setText(body, true);
        helper.setTo(email);
        if (cc != null) {
            helper.setCc(cc);
        }
        if (bcc != null) {
            helper.setBcc(bcc);

        }

        InternetAddress senderAddress = new InternetAddress("noreply@smartdukaan.com", "Smart Dukaan");
        helper.setFrom(senderAddress);
        mailSender.send(message);
    }

    public Map<Integer,Integer> findLoanTransactionMapingAccordingLoan(List<Integer> loanIds) throws ProfitMandiBusinessException {

        Map<Integer, Integer> transactionLoanMap = new HashMap<>();

        for(int loanId : loanIds){
            Transaction transaction = null;
            Loan loan = loanRepository.selectByLoanId(loanId);
            List<Transaction> transactions = transactionRepository.selectByRetailerId(loan.getFofoId());

            LocalDateTime nearestDateTime = transactions.stream().map(x -> x.getCreateTimestamp())
                    .min(Comparator.comparingLong(x -> Math.abs(ChronoUnit.MILLIS.between(x, loan.getCreatedOn()))))
                    .orElse(null);

            if (nearestDateTime != null && loan.getCreatedOn().plusMinutes(2).isAfter(nearestDateTime) &&
                    loan.getCreatedOn().minusMinutes(1).isBefore(nearestDateTime)) {
                // Here transaction is still null
                transaction = transactions.stream()
                        .filter(x -> x.getCreateTimestamp().equals(nearestDateTime))
                        .findFirst().get();
                transactionLoanMap.put(transaction.getId(), loanId);
            }

        }
        LOGGER.info("transactionLoanMap {}",transactionLoanMap);
        return transactionLoanMap;
    }



    public void sendRbmFeedbackSummaryEmail() throws MessagingException, ProfitMandiBusinessException, IOException {
        LocalDateTime startOfMonth = LocalDate.now().withDayOfMonth(1).atStartOfDay();
        LocalDateTime endOfMonth = LocalDateTime.now();
        String[] bcc = {"tarun.verma@smartdukaan.com"};

        // Get all RBM users
        List<AuthUser> authUsers = csService.getAuthUserIds(
                ProfitMandiConstants.TICKET_CATEGORY_RBM,
                Arrays.asList(EscalationType.L1)
        );

        if (authUsers.isEmpty()) {
            LOGGER.info("No RBMs found.");
            return;
        }

        List<Integer> rbmIds = authUsers.stream().map(AuthUser::getId).collect(Collectors.toList());

        // Fetch ratings for all RBMs for current month
        List<RbmRating> feedbackList = rbmRatingRepository.selectByRbmIdsAndDateRange(rbmIds, startOfMonth, endOfMonth);

        if (feedbackList.isEmpty()) {
            LOGGER.info("No feedback entries found for RBMs.");
            return;
        }

        // Sort feedback by createTimeStamp DESC
        feedbackList.sort((a, b) -> b.getCreateTimeStamp().compareTo(a.getCreateTimeStamp()));

        // Fetch and map FOFO (partner) names
        Map<Integer, String> fofoNameMap = new HashMap<>();
        for (RbmRating rating : feedbackList) {
            int fofoId = rating.getFofoId();
            if (!fofoNameMap.containsKey(fofoId)) {
                User fofoUser = userRepository.selectById(fofoId);
                FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

                String partnerName = fofoUser != null ? fofoUser.getName() : "Unknown Partner";
                String storeCode = fofoStore != null ? fofoStore.getCode() : "Unknown Code";

                String displayName = partnerName + " (" + storeCode + ")";
                fofoNameMap.put(fofoId, displayName);
            }
        }

        // Map RBM ID to name for quick lookup
        Map<Integer, String> rbmNameMap = authUsers.stream()
                .collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));

        // Generate HTML content
        StringBuilder emailContent = new StringBuilder();
        emailContent.append("<html><body>");
        emailContent.append("<p>Dear Team,</p>");
        emailContent.append("<p>Here is the <b>latest RBM Rating and Feedback Summary</b> for ")
                .append(LocalDate.now().getMonth()).append(":</p>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5'>");
        emailContent.append("<tr>")
                .append("<th>RBM Name</th>")
                .append("<th>Partner Name</th>")
                .append("<th>Rating</th>")
                .append("<th>Comment</th>")
                .append("<th>Date</th>")
                .append("</tr>");

        for (RbmRating rating : feedbackList) {
            String rbmName = rbmNameMap.getOrDefault(rating.getRbmId(), "Unknown RBM");
            String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");
            emailContent.append("<tr>")
                    .append("<td>").append(rbmName).append("</td>")
                    .append("<td>").append(partnerName).append("</td>")
                    .append("<td>").append(rating.getRating()).append("</td>")
                    .append("<td>").append(rating.getComment() != null ? rating.getComment() : "-").append("</td>")
                    .append("<td>").append(rating.getCreateTimeStamp().toLocalDate()).append("</td>")
                    .append("</tr>");
        }

        emailContent.append("</table>");
        emailContent.append("<br><p>Regards,<br>Smart Dukaan Team</p>");
        emailContent.append("</body></html>");

        String subject = "Monthly RBM Feedback Summary - " + LocalDate.now().getMonth();

        List<String> sendTo = new ArrayList<>();
        sendTo.add("sm@smartdukaan.com"); //
        sendTo.add("chiranjib.sarkar@smartdukaan.com"); //
        sendTo.add("kamini.sharma@smartdukaan.com"); //

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


        this.sendMailHtmlFormat(emailRecipients, emailContent.toString(), null, bcc, subject);

        LOGGER.info("Consolidated RBM feedback summary email sent.");
    }


    public void sendSalesFeedbackSummaryEmail() throws MessagingException, ProfitMandiBusinessException, IOException {
        LocalDateTime startOfMonth = LocalDate.now().withDayOfMonth(1).atStartOfDay();
        LocalDateTime endOfMonth = LocalDateTime.now();
        String[] bcc = {"tarun.verma@smartdukaan.com"};
//        String[] bcc = {"tejus.lohani@smartdukaan.com"};

        // Get all Sales users
        List<AuthUser> authUsers = csService.getAuthUserIds(
                ProfitMandiConstants.TICKET_CATEGORY_SALES,
                Arrays.asList(EscalationType.L1)
        );

        if (authUsers.isEmpty()) {
            LOGGER.info("No sales person found.");
            return;
        }

        List<Integer> salesL1Ids = authUsers.stream().map(AuthUser::getId).collect(Collectors.toList());

        // Fetch ratings for all RBMs for current month
        List<SalesRating> feedbackList = salesRatingRepository.selectBySalesL1IdsAndDateRange(salesL1Ids, startOfMonth, endOfMonth);

        if (feedbackList.isEmpty()) {
            LOGGER.info("No feedback entries found for Sales.");
            return;
        }

        // Sort feedback by createTimeStamp DESC
        feedbackList.sort((a, b) -> b.getCreateTimeStamp().compareTo(a.getCreateTimeStamp()));

        // Fetch and map FOFO (partner) names
        Map<Integer, String> fofoNameMap = new HashMap<>();
        for (SalesRating rating : feedbackList) {
            int fofoId = rating.getFofoId();
            if (!fofoNameMap.containsKey(fofoId)) {
                User fofoUser = userRepository.selectById(fofoId);
                FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);

                String partnerName = fofoUser != null ? fofoUser.getName() : "Unknown Partner";
                String storeCode = fofoStore != null ? fofoStore.getCode() : "Unknown Code";

                String displayName = partnerName + " (" + storeCode + ")";
                fofoNameMap.put(fofoId, displayName);
            }
        }

        // Map RBM ID to name for quick lookup
        Map<Integer, String> salesL1NameMap = authUsers.stream()
                .collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));

        // Generate HTML content
        StringBuilder emailContent = new StringBuilder();
        emailContent.append("<html><body>");
        emailContent.append("<p>Dear Team,</p>");
        emailContent.append("<p>Here is the <b>latest Sales L1 Rating and Feedback Summary</b> for ")
                .append(LocalDate.now().getMonth()).append(":</p>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5'>");
        emailContent.append("<tr>")
                .append("<th>Sales L1 Name</th>")
                .append("<th>Partner Name</th>")
                .append("<th>Partner Category</th>")
                .append("<th>Rating</th>")
                .append("<th>Comment</th>")
                .append("<th>Date</th>")
                .append("</tr>");

        for (SalesRating rating : feedbackList) {
            String salesL1 = salesL1NameMap.getOrDefault(rating.getSalesL1Id(), "Unknown Sales Person");
            String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");
            LOGGER.info("partnerName11- {}",partnerName);
            PartnerType partnerType = partnerTypeChangeService.getTypeOnDate(rating.getFofoId(), LocalDate.now());
            emailContent.append("<tr>")
                    .append("<td>").append(salesL1).append("</td>")
                    .append("<td>").append(partnerName).append("</td>")
                    .append("<td>").append(partnerType).append("</td>")
                    .append("<td>").append(rating.getRating()).append("</td>")
                    .append("<td>").append(rating.getComment() != null ? rating.getComment() : "-").append("</td>")
                    .append("<td>").append(rating.getCreateTimeStamp().toLocalDate()).append("</td>")
                    .append("</tr>");
        }

        emailContent.append("</table>");
        emailContent.append("<br><p>Regards,<br>Smartdukaan Team</p>");
        emailContent.append("</body></html>");

        String subject = "Monthly Sales L1 Feedback Summary Test test - " + LocalDate.now().getMonth();

        List<String> sendTo = new ArrayList<>();
         sendTo.add("sm@smartdukaan.com"); //
         sendTo.add("chiranjib.sarkar@smartdukaan.com"); //
         sendTo.add("kamini.sharma@smartdukaan.com"); //

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


        this.sendMailHtmlFormat(emailRecipients, emailContent.toString(), null, bcc, subject);

        LOGGER.info("Consolidated Sales L1 feedback summary email sent.");
    }

    public Map<String, Set<Integer>> generateBiReportHierarchyWise() throws Exception{
        List<Integer> categoryIds = Arrays.asList(ProfitMandiConstants.TICKET_CATEGORY_RBM, ProfitMandiConstants.TICKET_CATEGORY_SALES,ProfitMandiConstants.TICKET_CATEGORY_ABM,ProfitMandiConstants.TICKET_CATEGORY_BUSINESSINTELLIGENT);
        Map<String, Set<Integer>> storeGuyEntry = csService.getAuthUserPartnerIdMappingByCategoryIds(categoryIds, false);
        return storeGuyEntry;
    }



    public void generateBiReportExcel() throws Exception {

        LocalDateTime startOfToday;
        LocalDateTime previousDay;

        if (LocalDate.now().getDayOfMonth() == 1) {
            // If today is 1st, go to yesterday (i.e., last day of previous month)
            startOfToday = LocalDate.now().minusDays(1).atStartOfDay();
            previousDay = startOfToday.with(LocalTime.MAX);

        } else {
            // Otherwise, use today
            startOfToday = LocalDate.now().atStartOfDay();
            previousDay = startOfToday.with(LocalTime.MAX).minusDays(1);
        }

        Map<Integer, CustomRetailer> customRetailers = retailerService.getAllFofoRetailersInternalFalse();

        List<Integer> retailerIds = customRetailers.values().stream()
                .filter(retailer -> {
                    String storeCode = retailer.getCode(); // adjust method name if different
                    return !storeCode.equalsIgnoreCase("UPGBN640") && !storeCode.equalsIgnoreCase("HRYN039");
                })
                .map(CustomRetailer::getPartnerId)
                .collect(Collectors.toList());

//        List<Integer> retailerIds = Arrays.asList(175139993,175139441,175139442,175140143,175140146);
//        Map<Integer,CustomRetailer> customRetailers = retailerService.getFofoRetailers(retailerIds);

        //partner daily investment
        List<Loan> defaultLoans = sdCreditService.getDefaultLoans();
        Map<Integer,List<Loan>> defaultLoanMap = defaultLoans.stream().collect(Collectors.groupingBy(Loan::getFofoId));

        Map<Integer, PartnerDailyInvestment> partnerDailyInvestmentMap = new HashMap<>();
        List<PartnerDailyInvestment> partnerDailyInvestments = partnerDailyInvestmentRepository
                .selectAll(new ArrayList<>(retailerIds), previousDay.toLocalDate());
        if (!partnerDailyInvestments.isEmpty()) {
            partnerDailyInvestmentMap = partnerDailyInvestments.stream()
                    .collect(Collectors.toMap(x -> x.getFofoId(), x -> x));
        }

    //  this month return data

        YearMonth currentMonth;
        LocalDateTime currentMonthStartDate;
        LocalDateTime currentMonthEndDate;

        if (LocalDate.now().getDayOfMonth() == 1) {
            // If today is the 1st, use previous month
            currentMonth = YearMonth.now().minusMonths(1);
            currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();
            currentMonthEndDate = currentMonth.atEndOfMonth().atTime(23, 59, 59);
        } else {
            // Otherwise, use this month up to yesterday
            currentMonth = YearMonth.now();
            currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();
            currentMonthEndDate = LocalDate.now().minusDays(1).atTime(23, 59, 59);
        }

        String currentMonthStringValue = String.valueOf(currentMonth);

        List<ReturnOrderInfoModel> currentMonthReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(currentMonthStartDate, currentMonthEndDate);
        Map<Integer, Long> currentMonthPartnerReturnOrderInfoModelMap = currentMonthReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> currentMonthRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(currentMonthStartDate, currentMonthEndDate);
        Map<Integer, Long> currentMonthRtoRefundOrderMap = currentMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));


        List<ReturnOrderInfoModel> yesterdayReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(previousDay.toLocalDate().atStartOfDay(), previousDay);
        Map<Integer, Long> yesterdayReturnOrderInfoModelMap = yesterdayReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> yesterdayRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(previousDay.toLocalDate().atStartOfDay(), previousDay);
        Map<Integer, Long> yesterdayRtoRefundOrderMap = yesterdayRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        List<ReturnOrderInfoModel> dayBeforeYesterdayReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(previousDay.toLocalDate().atStartOfDay().minusDays(1), previousDay.minusDays(1));
        Map<Integer, Long> dayBeforeYesterdayReturnOrderInfoModelMap = dayBeforeYesterdayReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> dayBeforeYesterdayRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(previousDay.toLocalDate().atStartOfDay().minusDays(1), previousDay.minusDays(1));
        Map<Integer, Long> dayBeforeYesterdayRtoRefundOrderMap = dayBeforeYesterdayRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));



        //  last month return data
        YearMonth lastMonth = currentMonth.minusMonths(1);
        String lastMonthStringValue = String.valueOf(lastMonth);
        LocalDateTime lastMontStartDate = lastMonth.atDay(1).atStartOfDay();
        LocalDateTime lastMonthEndDate = lastMonth.atEndOfMonth().atTime(23, 59, 59);

        List<ReturnOrderInfoModel> lastMonthReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(lastMontStartDate, lastMonthEndDate);
        Map<Integer, Long> lastMonthPartnerReturnOrderInfoModelMap = lastMonthReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> lastMonthRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(lastMontStartDate, lastMonthEndDate);
        Map<Integer, Long> lastMonthRtoRefundOrderMap = lastMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));


    //  twoMonthsAgo return data
        YearMonth twoMonthsAgo = currentMonth.minusMonths(2);
        String twoMonthAgoStringValue = String.valueOf(twoMonthsAgo);
        LocalDateTime twoMonthsAgoStartDate = twoMonthsAgo.atDay(1).atStartOfDay();
        LocalDateTime twoMonthsAgoEndDate = twoMonthsAgo.atEndOfMonth().atTime(23, 59, 59);

        List<ReturnOrderInfoModel> twoMonthAgoReturnOrderInfoModels = returnOrderInfoRepository.selectAllByBetweenDate(twoMonthsAgoStartDate, twoMonthsAgoEndDate);
        Map<Integer, Long> twoMonthAgoPartnerReturnOrderInfoModelMap = twoMonthAgoReturnOrderInfoModels.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        List<Order> twoMonthRtoRefundOrders = orderRepository.selectAllRefundOrderDatesBetween(twoMonthsAgoStartDate, twoMonthsAgoEndDate);
        Map<Integer, Long> twoMonthAgoRtoRefundOrderMap = twoMonthRtoRefundOrders.stream().collect(Collectors.groupingBy(x -> x.getRetailerId(), Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));


        Map<Integer , String> assessmentMap = new HashMap<>();
        Map<Integer , String> zeroBillingMap = new HashMap<>();
        Map<Integer , Float> billingNeededMap = new HashMap<>();
        Map<Integer , Integer> countAMap = new HashMap<>();

        Map<Integer , BIRetailerModel> biRetailerModelMap = new HashMap<>();

        Map<Integer , FofoInvestmentModel> biInvestmentModelMap = new HashMap<>();

        Map<Integer, Map<YearMonth, BiSecondaryModel>> allRetailerMonthlyData = new HashMap<>();

        Map<Integer,Double> fofoTotalStockPriceMap = new HashMap<>();

        Map<Integer,Map<String, BrandStockPrice>> fofoBrandStockPriceMap = new HashMap<>();

        Map<Integer,Long> fofoTotalMtdSecondaryMap = new HashMap<>();

        Map<Integer,Long> fofoYesterdaySecondaryMap = new HashMap<>();

        Map<Integer,Long> fofoDayBeforeYesterdaySecondaryMap = new HashMap<>();

        Map<Integer,Map<String, Long>> fofoBrandWiseMtdSecondaryMap = new HashMap<>();

        Map<Integer,Double> fofoTotalMtdTertiaryMap = new HashMap<>();

        Map<Integer,Map<String, Double>> fofoBrandMtdTertiaryMap = new HashMap<>();

        for(Integer fofoId: retailerIds){
            String rbmName = "";
            int rbmL1 = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_RBM,EscalationType.L1,fofoId);
            if(rbmL1 != 0){
                 rbmName = authRepository.selectById(rbmL1).getFullName();
            }else {
                int rbmL2 = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L2, fofoId);
                if(rbmL2 != 0){
                    rbmName = authRepository.selectById(rbmL2).getFullName();
                }
            }
            String bmName ="";
            int bmId = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L2,fofoId);
            if(bmId !=0){
                bmName = authRepository.selectById(bmId).getFullName();
            }

            int managerId = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_SALES,EscalationType.L1,fofoId);
            String managerName = " ";
            LOGGER.info("managerId {}",managerId);
            if(managerId != 0){
                 managerName = authRepository.selectById(managerId).getFullName();
            }else {
                managerName = bmName;
            }
            String abmName ="";
            int abmId = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_ABM,EscalationType.L1,fofoId);
            int abmL2Id = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_ABM,EscalationType.L2,fofoId);
            int abmL3Id = csService.getAuthUserIdWithoutTicketAssignee(ProfitMandiConstants.TICKET_CATEGORY_ABM,EscalationType.L3,fofoId);
            if(abmId !=0){
                abmName = authRepository.selectById(abmId).getFullName();
            }else if(abmL2Id != 0){
                abmName = authRepository.selectById(abmL2Id).getFullName();
            }else if(abmL3Id != 0){
                abmName = authRepository.selectById(abmL3Id).getFullName();
            }
            AST ast = astRepository.selectById(customRetailers.get(fofoId).getAstId());

            LOGGER.info("fofoID--11 {}",fofoId);
            PartnerType partnerTypeThisMonth = partnerTypeChangeService.getTypeOnMonth(fofoId, currentMonth);

//            generate retaile detail

            BIRetailerModel biRetailerModel = new BIRetailerModel();
            biRetailerModel.setBmName(bmName);
            biRetailerModel.setCode(customRetailers.get(fofoId).getCode());
            if(ast != null){
                biRetailerModel.setArea(ast.getArea());
            }else {
                biRetailerModel.setArea("-");
            }
            String retailerStatus = "";
            FofoStore fofoStore1 = fofoStoreRepository.selectByRetailerId(fofoId);
            if(!fofoStore1.isActive()){
                retailerStatus = "INACTIVE";
            }else{
                retailerStatus =  String.valueOf(fofoStore1.getActivationType());
            }
            biRetailerModel.setCity(customRetailers.get(fofoId).getAddress().getCity());
            biRetailerModel.setStoreName(customRetailers.get(fofoId).getBusinessName());
            biRetailerModel.setStatus(retailerStatus);
            biRetailerModel.setCategory(String.valueOf(partnerTypeThisMonth));
            biRetailerModel.setSalesManager(managerName);
            biRetailerModel.setRbm(rbmName);
            biRetailerModel.setAbm(abmName);

            biRetailerModelMap.put(fofoId,biRetailerModel);


//            generate secondary data

            List<PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledMonthlyTotals = activatedImeiRepository.getTotalMonthlyActivatedNotBilled(fofoId,twoMonthsAgoStartDate);
            Map<YearMonth , PartnerWiseActivatedNotBilledTotal> partnerWiseActivatedNotBilledTotalMap = partnerWiseActivatedNotBilledMonthlyTotals.stream().collect(Collectors.toMap(x-> YearMonth.parse(x.getYearMonth()),x->x));

//            this month secondary target

            double currentSecondaryTarget =  monthlyTargetRepository.selectByDateAndFofoId(currentMonth, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(currentMonth, fofoId).getPurchaseTarget() : 0;


            long currentMonthReturn = currentMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId, 0L) + currentMonthRtoRefundOrderMap.getOrDefault(fofoId, 0L);


            Map<Integer, Double> secondaryMtd = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
                    startOfToday.withDayOfMonth(1), previousDay).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));


            //yesterday secondary
            Map<Integer, Double> dayBeforeYesterdaySecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
                    previousDay.toLocalDate().atStartOfDay().minusDays(1), previousDay.minusDays(1)).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
            double dayBeforeYesterdayAfterReturnSecondary = dayBeforeYesterdaySecondary.getOrDefault(fofoId,0d) - (dayBeforeYesterdayReturnOrderInfoModelMap.getOrDefault(fofoId,0l) + dayBeforeYesterdayRtoRefundOrderMap.getOrDefault(fofoId,0l));
            fofoDayBeforeYesterdaySecondaryMap.put(fofoId, (long) dayBeforeYesterdayAfterReturnSecondary);


            //day before secondary
            Map<Integer, Double> yesterDaySecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
                    previousDay.toLocalDate().atStartOfDay(), previousDay).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));
            double yesterDayAfterReturnSecondary = yesterDaySecondary.getOrDefault(fofoId,0d) - (yesterdayReturnOrderInfoModelMap.getOrDefault(fofoId,0l) + yesterdayRtoRefundOrderMap.getOrDefault(fofoId,0l));
            fofoYesterdaySecondaryMap.put(fofoId, (long) yesterDayAfterReturnSecondary);

            double secondaryAchievedMtd = secondaryMtd.getOrDefault(fofoId, 0.0);

            double currentMonthNetSecondary = secondaryAchievedMtd - currentMonthReturn;

            double currentMonthSecondaryPercent = currentSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((secondaryAchievedMtd / currentSecondaryTarget) * 100));

            double currentMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(currentMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(currentMonth).getTotalUnbilledAmount() : 0d;

//          this month tertiary----------

            LocalDateTime now = LocalDateTime.now();
            double todaySale = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday, now, fofoId, false).get(fofoId);
            double mtdSaleTillYesterDay = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), previousDay, fofoId, false).get(fofoId);
            double mtdSale = mtdSaleTillYesterDay;


//            last month secondary target

            double lastMonthSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId) != null ?  monthlyTargetRepository.selectByDateAndFofoId(lastMonth, fofoId).getPurchaseTarget() : 0;

            long lastMonthReturn = (lastMonthPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + lastMonthRtoRefundOrderMap.getOrDefault(fofoId,0L));

            Map<Integer, Double> lastMonthSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
                    lastMontStartDate, lastMonthEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double lastMonthSecondaryAchieved = lastMonthSecondary.getOrDefault(fofoId, 0.0);

            double lastMonthNetSecondary = lastMonthSecondaryAchieved - lastMonthReturn;

            double lastMonthSecondaryPercent = lastMonthSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((lastMonthSecondaryAchieved / lastMonthSecondaryTarget) * 100));

            double lastMonthUnbilled = partnerWiseActivatedNotBilledTotalMap.get(lastMonth) != null ? partnerWiseActivatedNotBilledTotalMap.get(lastMonth).getTotalUnbilledAmount() : 0d;

//           last month tertiary
            Double lastMonthSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
                    lastMontStartDate, lastMonthEndDate, fofoId, false).get(fofoId);


//            two month ago secondary target

            double twoMonthAgoSecondaryTarget = monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId) != null ? monthlyTargetRepository.selectByDateAndFofoId(twoMonthsAgo, fofoId).getPurchaseTarget() : 0;

            long twoMonthAgoReturn = (twoMonthAgoPartnerReturnOrderInfoModelMap.getOrDefault(fofoId,0L) + twoMonthAgoRtoRefundOrderMap.getOrDefault(fofoId,0L));

            Map<Integer, Double> twoMonthAgoSecondary = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(Arrays.asList(fofoId),
                    twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream().collect(Collectors.toMap(x -> x.getId(), x -> x.getAmount()));

            double twoMonthAgoSecondaryAchieved = twoMonthAgoSecondary.getOrDefault(fofoId, 0.0);

            double twoMonthAgoNetSecondary = twoMonthAgoSecondaryAchieved - twoMonthAgoReturn;

            double twoMonthAgoSecondaryPercent = twoMonthAgoSecondaryTarget == 0 ? 0.0 : Math.round(Math.abs((twoMonthAgoSecondaryAchieved / twoMonthAgoSecondaryTarget) * 100));

            double twoMonthAgoUnbilled = partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo) != null ? partnerWiseActivatedNotBilledTotalMap.get(twoMonthsAgo).getTotalUnbilledAmount() : 0d;

//          second Month Tertiary
            double twoMonthAgoSale = fofoOrderItemRepository.selectSumMopGroupByRetailer(
                    twoMonthsAgoStartDate, twoMonthsAgoEndDate, fofoId, false).get(fofoId);


            Map<YearMonth, BiSecondaryModel> monthlySecondaryModels = new HashMap<>();

            BiSecondaryModel currentMonthSecondaryModel = new BiSecondaryModel(
                    currentSecondaryTarget,
                    secondaryAchievedMtd,
                    currentMonthReturn,
                    currentMonthNetSecondary,
                    currentMonthSecondaryPercent,
                    mtdSale,
                    currentMonthUnbilled // for now, unbilled tertiary value
            );

            BiSecondaryModel lastMonthSecondaryModel = new BiSecondaryModel(
                    lastMonthSecondaryTarget,
                    lastMonthSecondaryAchieved,
                    lastMonthReturn,
                    lastMonthNetSecondary,
                    lastMonthSecondaryPercent,
                    lastMonthSale,
                    lastMonthUnbilled // for now, unbilled tertiary value
            );

            BiSecondaryModel twoMonthAgoSecondaryModel = new BiSecondaryModel(
                    twoMonthAgoSecondaryTarget,
                    twoMonthAgoSecondaryAchieved,
                    twoMonthAgoReturn,
                    twoMonthAgoNetSecondary,
                    twoMonthAgoSecondaryPercent,
                    twoMonthAgoSale,
                    twoMonthAgoUnbilled // for now, unbilled tertiary value
            );

            monthlySecondaryModels.put(currentMonth, currentMonthSecondaryModel);
            monthlySecondaryModels.put(lastMonth, lastMonthSecondaryModel);
            monthlySecondaryModels.put(twoMonthsAgo, twoMonthAgoSecondaryModel);

            allRetailerMonthlyData.put(fofoId, monthlySecondaryModels);

//            brandwiseStock value price

            Map<String, BrandStockPrice> brandStockPriceMap = inventoryService.getBrandWiseStockValue(fofoId);

            fofoBrandStockPriceMap.put(fofoId,brandStockPriceMap);

            double totalStockPrice = brandStockPriceMap.values().stream().mapToDouble(x->x.getTotalValue()).sum();

            fofoTotalStockPriceMap.put(fofoId,totalStockPrice);

            Map<String, Double> brandMtdTertiaryAmount = fofoOrderItemRepository.selectSumAmountGroupByBrand(
                    currentMonthStartDate, currentMonthEndDate, fofoId);


            fofoBrandMtdTertiaryMap.put(fofoId,brandMtdTertiaryAmount);

            double totalMtdTertiaryAmount = brandMtdTertiaryAmount.values().stream().mapToDouble(Double::doubleValue).sum();

            fofoTotalMtdTertiaryMap.put(fofoId,totalMtdTertiaryAmount);

            List<BrandWiseModel> brandWiseMtdSecondary = orderRepository.selectAllBilledByCategoryOrderGroupByBrandFofoId(fofoId, currentMonthStartDate,currentMonthEndDate, Arrays.asList(10006,10001));
            Map<String,Long> brandWiseMtdSecondaryMap = brandWiseMtdSecondary.stream().collect(Collectors.toMap(BrandWiseModel::getBrand,BrandWiseModel::getAmount));

            //retrunInfo
            List<BrandWiseReturnInfo> brandWiseReturnInfos = returnOrderInfoRepository.selectAllBrandWiseByBetweenDate(currentMonthStartDate,currentMonthEndDate.plusDays(1),fofoId);
            Map<String,Double> brandWiseReturnInfoMap = brandWiseReturnInfos.stream().collect(Collectors.toMap(BrandWiseReturnInfo::getBrand, x->x.getReturnAmount()));

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

            //Rto retrunInfo
            List<BrandWiseReturnInfo> brandWiseRTOReturnInfos = returnOrderInfoRepository.selectAllBrandWiseRTORefundByBetweenDate(currentMonthStartDate,currentMonthEndDate.plusDays(1),fofoId);
            Map<String,Double> brandWiseRTOReturnInfoMap = brandWiseRTOReturnInfos.stream().collect(Collectors.toMap(BrandWiseReturnInfo::getBrand, x->x.getReturnAmount()));

            // Step 1: Get union of all brand keys
            Set<String> allBrands = new HashSet<>();
            allBrands.addAll(brandWiseMtdSecondaryMap.keySet());
            allBrands.addAll(brandWiseReturnInfoMap.keySet());
            allBrands.addAll(brandWiseRTOReturnInfoMap.keySet());

            // Step 2: Calculate net secondary for each brand
            Map<String, Long> brandWiseMtdNetSecondaryMap = new HashMap<>();

            for (String brand : allBrands) {
                Long billedAmount = brandWiseMtdSecondaryMap.getOrDefault(brand, 0L);
                Double returnAmount = brandWiseReturnInfoMap.getOrDefault(brand, 0d);
                Double rtoReturnAmount = brandWiseRTOReturnInfoMap.getOrDefault(brand, 0d);

                double netSecondary = billedAmount - (returnAmount + rtoReturnAmount);
                brandWiseMtdNetSecondaryMap.put(brand, (long) Math.round(netSecondary));
            }


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


            fofoBrandWiseMtdSecondaryMap.put(fofoId,brandWiseMtdNetSecondaryMap);

            long mtdTotalSecondary = brandWiseMtdNetSecondaryMap.values().stream().mapToLong(Long::longValue).sum();

            fofoTotalMtdSecondaryMap.put(fofoId,mtdTotalSecondary);

            //            generate investment info
            FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);
            float shortInvestment = partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getShortInvestment() : 0f;
            float agreedInvestment = partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getMinInvestment() : 0f;
            float investmentLevel = partnerDailyInvestmentMap.get(fofoId) != null
                    ? Math.abs(((shortInvestment - agreedInvestment) / agreedInvestment) * 100)
                    : 0f;


            List<Loan> fofoDefaultLoans = new ArrayList<>();
            if(defaultLoanMap != null){
                 fofoDefaultLoans  =  defaultLoanMap.get(fofoId);
                LOGGER.info("fofoDefaultLoans {}",fofoDefaultLoans);
            }

            float defaultLoanAmount = 0f;
            if(fofoDefaultLoans != null ){
                if (!fofoDefaultLoans.isEmpty()) {
                    for (Loan entry : fofoDefaultLoans) {

                        List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());

                        double amount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

                        defaultLoanAmount += amount;
                    }
                }
            }

            List<Loan> activeLoans = loanRepository.selectAllActiveLoan(fofoId);

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

            float activeLoan = 0f;

            for (Loan entry : activeLoans) {

                List<LoanStatement> loanStatements = loanStatementRepository.selectByLoanId(entry.getId());

                double pendingAmount = loanStatements.stream().map(x -> x.getAmount()).collect(Collectors.summingDouble(x -> x.doubleValue()));

                activeLoan += pendingAmount;
            }

            float poValue = partnerDailyInvestmentMap.get(fofoId) != null ?  partnerDailyInvestmentMap.get(fofoId).getUnbilledAmount() : 0f;

            float poAndBilledValue = (float) (currentMonthNetSecondary + poValue);

            double monthDay1Drr = rbmTargetService.calculateFofoIdTodayTarget(fofoId,0d,currentMonth.atDay(1));
            double todayRequiredDrr = 0;

            if(monthDay1Drr != 0d){
                todayRequiredDrr = rbmTargetService.calculateFofoIdTodayTarget(fofoId, currentMonthNetSecondary,startOfToday.toLocalDate());
            }

            double gotDrrPercent = (todayRequiredDrr / monthDay1Drr) * 100;

            long drrPercentDisplay = Math.round(Math.abs(gotDrrPercent));


            int orderId = orderRepository.getLastOrderByFofoId(fofoId);

            // Determine alert level
            String alertLevel = "-";
            int lastPurchaseDays = 0;
            if (orderId != 0) {
                Order order = orderRepository.selectById(orderId);

                LOGGER.info("last billing order - {}",order);

                // Calculate the number of days since the last purchase (billing)
                lastPurchaseDays = (int) Duration.between(order.getCreateTimestamp().plusDays(1), LocalDateTime.now()).toDays();

                if (lastPurchaseDays >= 11) {
                    alertLevel = "Alert for Management";
                } else if (lastPurchaseDays >= 10) {
                    alertLevel = " Alert for RSM/SH";
                } else if (lastPurchaseDays >= 7) {
                    alertLevel = "Must be Billed";
                } else if (lastPurchaseDays >= 3) {
                    alertLevel = "OK";
                } else {
                    alertLevel = "OK";
                }
            }

            //investment modal set all related value
            FofoInvestmentModel fofoInvestmentModel = new FofoInvestmentModel();

            fofoInvestmentModel.setCounterPotential(fofoStore.getCounterPotential());
            fofoInvestmentModel.setShortInvestment(shortInvestment);
            fofoInvestmentModel.setDefaultLoan(defaultLoanAmount);
            fofoInvestmentModel.setInvestmentLevel(investmentLevel);
            fofoInvestmentModel.setActiveLoan(activeLoan);
            fofoInvestmentModel.setPoValue(poValue);
            fofoInvestmentModel.setPoAndBilled(poAndBilledValue);
            fofoInvestmentModel.setAgreedInvestment(agreedInvestment);
            fofoInvestmentModel.setWallet(partnerDailyInvestmentMap.get(fofoId) != null ? partnerDailyInvestmentMap.get(fofoId).getWalletAmount() : 0);
            fofoInvestmentModel.setMonthBeginingDrr(monthDay1Drr);
            fofoInvestmentModel.setRequiredDrr(todayRequiredDrr);
            fofoInvestmentModel.setDrrPercent(drrPercentDisplay);
            fofoInvestmentModel.setLastBillingDone(lastPurchaseDays);
            fofoInvestmentModel.setSlab(alertLevel);

            biInvestmentModelMap.put(fofoId, fofoInvestmentModel);
                String assessment = "";
                if(defaultLoanAmount < 0 ){
                    assessment = "Loan Default";
                }else if(investmentLevel <= 75 && defaultLoanAmount >= 0){
                    assessment = "Low Invest";
                }else {
                    assessment = "-";
                }
                assessmentMap.put(fofoId,assessment);

                String zeroBilling = "";
                if(currentMonthNetSecondary <= 100000 ){
                    zeroBilling = "Zero Billing";
                }else {
                    zeroBilling = "-";
                }
                zeroBillingMap.put(fofoId,zeroBilling);

                float billingNeeded = 0f;
                if(drrPercentDisplay >= 110 && todayRequiredDrr > 0 ){
                    billingNeeded = (float) todayRequiredDrr;
                }else {
                    billingNeeded = 0f;
                }
                billingNeededMap.put(fofoId,billingNeeded);

                int counta = 0;
                if(defaultLoanAmount > 0 || investmentLevel <= 75 || currentMonthNetSecondary <= 100000 || drrPercentDisplay >= 110 ){
                    counta = 1;
                }else {
                    counta = 0;
                }
                countAMap.put(fofoId,counta);

        }

        LOGGER.info("Total BI Retailers processed: {}", biRetailerModelMap.size());

        //generate excel and sent to mail
        List<List<String>> headerGroup = new ArrayList<>();

        List<String> headers1 = Arrays.asList(
                "","","","",
                "Retailer Detail", "","", "", "", "", "", "", "", "","","","","",

                twoMonthAgoStringValue, "", "", "", "", "", "",
                lastMonthStringValue, "", "", "", "", "", "",
                currentMonthStringValue, "", "", "", "", "", "",

                "","", "", "", "", "", "", "", "", "", "", "", "", "",

                "", "", "", "", "", "", "", "", "", "", "", "", "",
                "", "", "", "", "", "", "", "", "", "", "", "", "","",""

        );

        List<String> headers2 = Arrays.asList(
                "Assessment","Zero billing","Billing needed","Counta",
                "BM","Partner Id","Link","Wallet Date","Creation Date","Code","Area",  "City", "Store Name", "Status","Category","Sales Manager", "RBM","ABM",
                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",
                "Tertiary Sale", "Unbilled",
                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",
                "Tertiary Sale", "Unbilled",
                "Secondary Target", "Secondary Achieved", "Returns", "Net Secondary", "Secondary %",
                "Tertiary Sale", "Unbilled",
                "Counter Potential", "Short investment", "Default", "INVESTMENT LEVEL", "Loan", "PO value", "Agreed investment",
                "Wallet", "po+bill", "MONTH BEGINNING DRR", "REQ DRR", "Drr %", "Last billing Done", "Slab",

              "Total Stock",  "Apple","Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New",
              "Total Secondary", "Apple", "Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New",
              "Total Tertiary",  "Apple", "Xiaomi", "Vivo", "Tecno", "Samsung", "Realme", "Oppo", "OnePlus", "POCO", "Lava", "Itel", "Almost New",
                "YesterDay Seconday","Day Before Yesterday Secondary"
        );

        headerGroup.add(headers1);
        headerGroup.add(headers2);


        List<List<?>> rows = new ArrayList<>();
        for (Map.Entry<Integer, BIRetailerModel> entry : biRetailerModelMap.entrySet()) {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd/MM/yyyy");
            Integer fofoId = entry.getKey();
            User user = userRepository.selectById(fofoId);
            LocalDateTime walletCreationDate = userWalletHistoryRepository.selectFirstCreatedDate(fofoId);
            if(walletCreationDate == null){
                walletCreationDate = user.getCreateTimestamp();
            }
            BIRetailerModel retailer = entry.getValue();

            Map<YearMonth, BiSecondaryModel> monthlyData = allRetailerMonthlyData.get(fofoId);

            BiSecondaryModel current = monthlyData.getOrDefault(currentMonth, new BiSecondaryModel(0,0,0,0,0,0,0));
            BiSecondaryModel last = monthlyData.getOrDefault(currentMonth.minusMonths(1), new BiSecondaryModel(0,0,0,0,0,0,0));
            BiSecondaryModel twoAgo = monthlyData.getOrDefault(currentMonth.minusMonths(2), new BiSecondaryModel(0,0,0,0,0,0,0));

            List<Object> row = new ArrayList<>();
            LOGGER.info("fofoId-11 {}",fofoId);

            row.addAll(Arrays.asList(
                    assessmentMap.get(fofoId),
                    zeroBillingMap.get(fofoId),
                    billingNeededMap.get(fofoId),
                    countAMap.get(fofoId),
                    retailer.getBmName(),
                    fofoId ,
                    "https://partners.smartdukaan.com/partnerPerformance?fofoId="+fofoId,
                    walletCreationDate.format(formatter),
                    user.getCreateTimestamp() != null ? (user.getCreateTimestamp()).format(formatter) : "-",
                    retailer.getCode(),
                    retailer.getArea(),
                    retailer.getCity(),
                    retailer.getStoreName(),
                    retailer.getStatus(),
                    retailer.getCategory(),
                    retailer.getSalesManager(),
                    retailer.getRbm(),
                    retailer.getAbm()

            ));


            // Two Months Ago
            row.addAll(Arrays.asList(
                    twoAgo.getSecondaryTarget(),
                    twoAgo.getSecondaryAchieved(),
                    twoAgo.getSecondaryReturn(),
                    twoAgo.getNetSecondary(),
                    twoAgo.getSecondaryAchievedPercent()+"%",
                    twoAgo.getTertiary(),
                    twoAgo.getTertiaryUnBilled()
            ));

            // Last Month
            row.addAll(Arrays.asList(
                    last.getSecondaryTarget(),
                    last.getSecondaryAchieved(),
                    last.getSecondaryReturn(),
                    last.getNetSecondary(),
                    last.getSecondaryAchievedPercent()+"%",
                    last.getTertiary(),
                    last.getTertiaryUnBilled()
            ));

            // Current Month
            row.addAll(Arrays.asList(
                    current.getSecondaryTarget(),
                    current.getSecondaryAchieved(),
                    current.getSecondaryReturn(),
                    current.getNetSecondary(),
                    current.getSecondaryAchievedPercent()+"%",
                    current.getTertiary(),
                    current.getTertiaryUnBilled()
            ));



            FofoInvestmentModel fofoInvestmentModelValue = biInvestmentModelMap.get(fofoId);
            if(fofoInvestmentModelValue != null){
                row.addAll(Arrays.asList(
                        fofoInvestmentModelValue.getCounterPotential(),
                        fofoInvestmentModelValue.getShortInvestment(),
                        fofoInvestmentModelValue.getDefaultLoan(),
                        fofoInvestmentModelValue.getInvestmentLevel() +"%",
                        fofoInvestmentModelValue.getActiveLoan(),
                        fofoInvestmentModelValue.getPoValue(),
                        fofoInvestmentModelValue.getAgreedInvestment(),
                        fofoInvestmentModelValue.getWallet(),
                        fofoInvestmentModelValue.getPoAndBilled(),
                        fofoInvestmentModelValue.getMonthBeginingDrr(),
                        fofoInvestmentModelValue.getRequiredDrr(),
                        fofoInvestmentModelValue.getDrrPercent()+"%",
                        fofoInvestmentModelValue.getLastBillingDone(),
                        fofoInvestmentModelValue.getSlab()
                ));
            }else {
                row.addAll(Arrays.asList(
                        "-","-","-","-","-","-","-","-","-","-","-",""
                ));
            }

            Map<String, BrandStockPrice> brandStockMap = fofoBrandStockPriceMap.get(fofoId);
            row.addAll(Arrays.asList(
                    fofoTotalStockPriceMap.getOrDefault(fofoId, 0.0),
                    brandStockMap.get("Apple") != null ? brandStockMap.get("Apple").getTotalValue() : 0.0,
                    brandStockMap.get("Xiaomi") != null ? brandStockMap.get("Xiaomi").getTotalValue() : 0.0,
                    brandStockMap.get("Vivo") != null ? brandStockMap.get("Vivo").getTotalValue() : 0.0,
                    brandStockMap.get("Tecno") != null ? brandStockMap.get("Tecno").getTotalValue() : 0.0,
                    brandStockMap.get("Samsung") != null ? brandStockMap.get("Samsung").getTotalValue() : 0.0,
                    brandStockMap.get("Realme") != null ? brandStockMap.get("Realme").getTotalValue() : 0.0,
                    brandStockMap.get("Oppo") != null ? brandStockMap.get("Oppo").getTotalValue() : 0.0,
                    brandStockMap.get("OnePlus") != null ? brandStockMap.get("OnePlus").getTotalValue() : 0.0,
                    brandStockMap.get("POCO") != null ? brandStockMap.get("POCO").getTotalValue() : 0.0,
                    brandStockMap.get("Lava") != null ? brandStockMap.get("Lava").getTotalValue() : 0.0,
                    brandStockMap.get("Itel") != null ? brandStockMap.get("Itel").getTotalValue() : 0.0,
                    brandStockMap.get("Almost New") != null ? brandStockMap.get("Almost New").getTotalValue() : 0.0
            ));

            Map<String, Long> brandSecondaryMap = fofoBrandWiseMtdSecondaryMap.get(fofoId);
            row.addAll(Arrays.asList(
                    fofoTotalMtdSecondaryMap.get(fofoId),
                    brandSecondaryMap.getOrDefault("Apple", 0L),
                    brandSecondaryMap.getOrDefault("Xiaomi", 0L),
                    brandSecondaryMap.getOrDefault("Vivo", 0L),
                    brandSecondaryMap.getOrDefault("Tecno", 0L),
                    brandSecondaryMap.getOrDefault("Samsung", 0L),
                    brandSecondaryMap.getOrDefault("Realme", 0L),
                    brandSecondaryMap.getOrDefault("Oppo", 0L),
                    brandSecondaryMap.getOrDefault("OnePlus", 0L),
                    brandSecondaryMap.getOrDefault("POCO", 0L),
                    brandSecondaryMap.getOrDefault("Lava", 0L),
                    brandSecondaryMap.getOrDefault("Itel", 0L),
                    brandSecondaryMap.getOrDefault("Almost New", 0L)
            ));

            Map<String, Double> brandTertiaryMap = fofoBrandMtdTertiaryMap.get(fofoId);
            row.addAll(Arrays.asList(
                    fofoTotalMtdTertiaryMap.get(fofoId),
                    brandTertiaryMap.getOrDefault("Apple", 0d),
                    brandTertiaryMap.getOrDefault("Xiaomi", 0d),
                    brandTertiaryMap.getOrDefault("Vivo", 0d),
                    brandTertiaryMap.getOrDefault("Tecno", 0d),
                    brandTertiaryMap.getOrDefault("Samsung", 0d),
                    brandTertiaryMap.getOrDefault("Realme", 0d),
                    brandTertiaryMap.getOrDefault("Oppo", 0d),
                    brandTertiaryMap.getOrDefault("OnePlus", 0d),
                    brandTertiaryMap.getOrDefault("POCO", 0d),
                    brandTertiaryMap.getOrDefault("Lava", 0d),
                    brandTertiaryMap.getOrDefault("Itel", 0d),
                    brandTertiaryMap.getOrDefault("Almost New", 0d)
            ));

            row.addAll(Arrays.asList(
                    fofoYesterdaySecondaryMap.get(fofoId),
                    fofoDayBeforeYesterdaySecondaryMap.get(fofoId)
            ));
            rows.add(row);
        }

        Map<String, Set<Integer>> storeGuyMap = this.generateBiReportHierarchyWise();

        for (Map.Entry<String, Set<Integer>> storeGuyEntry : storeGuyMap.entrySet()) {
            String storeGuyEmail = storeGuyEntry.getKey();
            Set<Integer> fofoIds = storeGuyEntry.getValue();
            String[] sendToArray = new String[]{storeGuyEmail};

            List<List<?>> filteredRows = rows.stream()
                    .filter(row -> row.size() > 5 && fofoIds.contains((Integer) row.get(5)))
                    .collect(Collectors.toList());
            this.sendMailToUser(headerGroup,filteredRows,sendToArray);
        }

        this.sendMailToUser(headerGroup,rows,new String[]{"ranu.rajput@smartdukaan.com"});


    }

    private  void sendMailToUser(List<List<String>> headerGroup,List<List<?>> rows, String[] sendToArray ) throws Exception {
        // Send to email
//        ByteArrayOutputStream csvStream = FileUtil.getCSVByteStreamWithMultiHeaders(headerGroup, rows);
        ByteArrayOutputStream csvStream = getExcelStreamWithMultiHeaders(headerGroup, rows);
        String fileName = "BI-Retailer-Monthly-Report-" + FormattingUtils.formatDate(LocalDateTime.now()) + ".xlsx";
        Utils.sendMailWithAttachment(googleMailSender, sendToArray, new String[]{}, "BI Retailer Monthly Report", "Please find attached the BI retailer secondary/tertiary monthly report.", fileName, new ByteArrayResource(csvStream.toByteArray()));
    }


    public static ByteArrayOutputStream getExcelStreamWithMultiHeaders(List<List<String>> headerGroup, List<List<?>> rows) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("BI Report");
        CreationHelper creationHelper = workbook.getCreationHelper();
        int rowIndex = 0;

        CellStyle centeredStyle = workbook.createCellStyle();
        centeredStyle.setAlignment(HorizontalAlignment.CENTER); // Center horizontally
        centeredStyle.setVerticalAlignment(VerticalAlignment.CENTER); // Center vertically

    // Optional: bold font
        Font font1 = workbook.createFont();
        font1.setBold(true);
        centeredStyle.setFont(font1);



        // Create styles
        Map<String, CellStyle> headerStyles = new HashMap<>();

        // fontPurpleStyle
        CellStyle purpleStyle = workbook.createCellStyle();
        purpleStyle.setFillForegroundColor(IndexedColors.ROSE.getIndex());
        purpleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        purpleStyle.setFont(font1);
        headerStyles.put("Assessment", purpleStyle);
        headerStyles.put("Zero billing", purpleStyle);
        headerStyles.put("Billing needed", purpleStyle);
        headerStyles.put("Counta", purpleStyle);
        headerStyles.put("MONTH BEGINNING DRR", purpleStyle);
        headerStyles.put("REQ DRR", purpleStyle);
        headerStyles.put("Drr %", purpleStyle);

        // Light Blue
        CellStyle blueStyle = workbook.createCellStyle();
        blueStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        blueStyle.setFont(font1);
        headerStyles.put("Code", blueStyle);
        headerStyles.put("Store Name", blueStyle);
        headerStyles.put("City", blueStyle);
        headerStyles.put("Area", blueStyle);
        headerStyles.put("BM", blueStyle);
        headerStyles.put("RBM", blueStyle);
        headerStyles.put("Sales Manager", blueStyle);
        headerStyles.put("Status", blueStyle);
        headerStyles.put("Category", blueStyle);
        headerStyles.put("Wallet Date", blueStyle);
        headerStyles.put("Creation Date", blueStyle);
        headerStyles.put("Partner Id", blueStyle);

        //for link
        // Create hyperlink style
        CellStyle hyperlinkStyle = workbook.createCellStyle();
        Font hlinkFont = workbook.createFont();
        hlinkFont.setUnderline(Font.U_SINGLE);
        hlinkFont.setColor(IndexedColors.BLUE.getIndex());
        hyperlinkStyle.setFont(hlinkFont);


        // Light Yellow
        CellStyle yellowStyle = workbook.createCellStyle();
        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        yellowStyle.setFont(font1);
        headerStyles.put("Last billing Done", yellowStyle);
        headerStyles.put("Total Stock", yellowStyle);

        // Light Orange
        CellStyle orangeStyle = workbook.createCellStyle();
        orangeStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        orangeStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        orangeStyle.setFont(font1);
        headerStyles.put("Total Tertiary", orangeStyle);
        headerStyles.put("Total Secondary", orangeStyle);
        headerStyles.put("Default", orangeStyle);


        // Light green
        CellStyle lightGreenStyle = workbook.createCellStyle();
        lightGreenStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        lightGreenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        lightGreenStyle.setFont(font1);
        headerStyles.put("Short investment", lightGreenStyle);
        headerStyles.put("INVESTMENT LEVEL", lightGreenStyle);
        headerStyles.put("Loan", lightGreenStyle);
        headerStyles.put("PO value", lightGreenStyle);
        headerStyles.put("Agreed investment", lightGreenStyle);
        headerStyles.put("Wallet", lightGreenStyle);
        headerStyles.put("po+bill", lightGreenStyle);

        // Light Green
        CellStyle secondary1 = createStyle(workbook, IndexedColors.LIGHT_GREEN);
        CellStyle secondary2 = createStyle(workbook, IndexedColors.LIGHT_YELLOW);
        CellStyle secondary3 = createStyle(workbook, IndexedColors.LIGHT_ORANGE);

        Map<String, CellStyle> brandStyles = new HashMap<>();
        brandStyles.put("Apple", createStyle(workbook, IndexedColors.GREY_25_PERCENT));
        brandStyles.put("Xiaomi", createStyle(workbook, IndexedColors.ORANGE));
        brandStyles.put("Vivo", createStyle(workbook, IndexedColors.SKY_BLUE));
        brandStyles.put("Tecno", createStyle(workbook, IndexedColors.LIGHT_BLUE));
        brandStyles.put("Samsung", createStyle(workbook, IndexedColors.ROYAL_BLUE));
        brandStyles.put("Realme", createStyle(workbook, IndexedColors.YELLOW));
        brandStyles.put("Oppo", createStyle(workbook, IndexedColors.LIGHT_GREEN));
        brandStyles.put("OnePlus", createStyle(workbook, IndexedColors.RED));
        brandStyles.put("POCO", createStyle(workbook, IndexedColors.ORANGE));
        brandStyles.put("Lava", createStyle(workbook, IndexedColors.LIGHT_YELLOW));
        brandStyles.put("Itel", createStyle(workbook, IndexedColors.LIGHT_YELLOW));
        brandStyles.put("Almost New", createStyle(workbook, IndexedColors.WHITE));


        CellStyle defaultHeaderStyle = workbook.createCellStyle();
        defaultHeaderStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        defaultHeaderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        defaultHeaderStyle.setFont(font1);

        CellStyle numberStyle = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        numberStyle.setDataFormat(format.getFormat("#,##0")); // or "#,##0.00" for two decimals



        Map<String, Integer> headerCount = new HashMap<>();

        for (int headerRowIndex = 0; headerRowIndex < headerGroup.size(); headerRowIndex++) {
            List<String> headerRow = headerGroup.get(headerRowIndex);
            Row row = sheet.createRow(rowIndex++);

            for (int i = 0; i < headerRow.size(); i++) {
                String headerText = headerRow.get(i);
                sheet.setColumnWidth(i, 25 * 256);
                row.setHeightInPoints(20); // 25-point height
                Cell cell = row.createCell(i);
                cell.setCellValue(headerText);
                cell.setCellStyle(centeredStyle);
                // Count how many times this header has appeared
                int count = headerCount.getOrDefault(headerText, 0) + 1;
                headerCount.put(headerText, count);
                // Apply special style for repeated headers
                if (headerText.equals("Secondary Target") || headerText.equals("Secondary Achieved") || headerText.equals("Returns") || headerText.equals("Net Secondary") || headerText.equals("Secondary %") || headerText.equals("Tertiary Sale") || headerText.equals("Unbilled")) {
                    if (count == 1) {
                        cell.setCellStyle(secondary1);
                    } else if (count == 2) {
                        cell.setCellStyle(secondary2);
                    } else if (count == 3) {
                        cell.setCellStyle(secondary3);
                    }
                }
                // Brand header styling (apply only for the 2nd row of headers)
                else if (headerRowIndex == 1 && brandStyles.containsKey(headerText)) {
                    cell.setCellStyle(brandStyles.get(headerText));
                }else if (headerStyles.containsKey(headerText)) {
                    cell.setCellStyle(headerStyles.get(headerText));
                } else {
                    cell.setCellStyle(defaultHeaderStyle); // default style for others
                }
            }
        }

        // Write data rows
        for (List<?> dataRow : rows) {
            Row row = sheet.createRow(rowIndex++);
            for (int i = 0; i < dataRow.size(); i++) {
                Cell cell = row.createCell(i);
                Object value = dataRow.get(i);

                if (i == 6 && value != null) { // Assuming column 6 is "Link"
                    Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
                    hyperlink.setAddress(value.toString());
                    cell.setCellValue("View Link"); // Display text
                    cell.setHyperlink(hyperlink);
                    cell.setCellStyle(hyperlinkStyle);
                } else if (value instanceof Number) {
                    double numeric = ((Number) value).doubleValue();
                    cell.setCellValue(Math.round(numeric));
                    cell.setCellStyle(numberStyle);
                } else {
                    cell.setCellValue(value != null ? value.toString() : "");
                }
            }

        }

        // Auto-size columns
        if (!rows.isEmpty()) {
            for (int i = 0; i < rows.get(0).size(); i++) {
                sheet.autoSizeColumn(i);
            }
        }

        // Output as ByteArray
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            workbook.close();
            return outputStream;
        } catch (IOException e) {
            throw new RuntimeException("Failed to generate Excel file", e);
        }
    }


    private static CellStyle createStyle(Workbook workbook, IndexedColors color) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(color.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }


    public void stockAlertMailToRetailer() throws Exception {

        Map<Integer, CustomRetailer> customRetailers = retailerService.getFofoRetailers(true);

        List<Integer> retailerIds = customRetailers.values().stream().map(CustomRetailer::getPartnerId).collect(Collectors.toList());

        for(Integer fofoId : retailerIds){
            List<String> statusOrder = Arrays.asList("HID", "FASTMOVING", "RUNNING", "SLOWMOVING", "OTHER");
            FofoStore fofoStore = fofoStoreRepository.selectByRetailerId(fofoId);
            List<PartnerWarehouseStockSummaryModel> partnerWarehouseStockSummaryModels = saholicInventoryService.getSaholicAndPartnerStock(fofoId, fofoStore.getWarehouseId());

            List<PartnerWarehouseStockAgingSummaryModel> partnerWarehouseStockAgingSummaryModelList = new ArrayList<>();

            Set<Integer> catalogIds = partnerWarehouseStockSummaryModels.stream().map(x -> x.getCatalogId()).collect(Collectors.toSet());

            List<Integer> catalogsList = new ArrayList<>(catalogIds);

            Map<Integer, TagListing> tagListingsMap = tagListingRepository.selectAllByCatalogIds(catalogsList);

            List<CatalogAgingModel> catalogAgingModels = ageingService.getCatalogsAgingByWarehouse(catalogIds, fofoStore.getWarehouseId());

            Map<Integer, CatalogAgingModel> catalogAgingModelMap = catalogAgingModels.stream().collect(Collectors.toMap(x -> x.getCatalogId(), x -> x));

            for (PartnerWarehouseStockSummaryModel stockSummary : partnerWarehouseStockSummaryModels) {

                PartnerWarehouseStockAgingSummaryModel partnerWarehouseStockAgingSummaryModel = new PartnerWarehouseStockAgingSummaryModel();
                partnerWarehouseStockAgingSummaryModel.setCatalogId(stockSummary.getCatalogId());
                partnerWarehouseStockAgingSummaryModel.setBrand(stockSummary.getBrand());
                partnerWarehouseStockAgingSummaryModel.setModelNumber(stockSummary.getModelNumber());
                partnerWarehouseStockAgingSummaryModel.setNetAvailability(stockSummary.getShaholicNetAvailability());
                partnerWarehouseStockAgingSummaryModel.setPartnerStockAvailability(stockSummary.getPartnerFullFilledQty());
                partnerWarehouseStockAgingSummaryModel.setPartnerCurrentAvailability(stockSummary.getPartnerCurrentQty());
                partnerWarehouseStockAgingSummaryModel.setPartnerShortageStock(stockSummary.getPartnerShortageQty());
                if (catalogAgingModelMap.get(stockSummary.getCatalogId()) != null) {
                    partnerWarehouseStockAgingSummaryModel.setExceedDays(catalogAgingModelMap.get(stockSummary.getCatalogId()).getExceedDays());
                } else {
                    partnerWarehouseStockAgingSummaryModel.setExceedDays(0);

                }
                partnerWarehouseStockAgingSummaryModel.setStatus(stockSummary.getStatus());

                partnerWarehouseStockAgingSummaryModelList.add(partnerWarehouseStockAgingSummaryModel);
            }

            Set<Integer> existingCatalogIdsInAgingSummaryList = partnerWarehouseStockAgingSummaryModelList.stream()
                    .map(PartnerWarehouseStockAgingSummaryModel::getCatalogId)
                    .collect(Collectors.toSet());
        }

    }

    public void createFofoSmartCartSuggestion(){

        List<Integer> fofoIds = fofoStoreRepository.selectActiveStores().stream().map(x->x.getId()).collect(toList());
        LocalDateTime todayDate = LocalDate.now().atStartOfDay();
        LocalDateTime fortyFiveAgoDate = todayDate.minusDays(45).with(LocalTime.MAX);
        for(Integer fofoId :fofoIds){
            smartCartSuggestionRepository.deleteByFofoId(fofoId);
            List<SoldAllCatalogitemQtyByPartnerModel> soldAllCatalogitemQtyByPartnerModels = smartCartService.getAllSoldCatalogItemByPartner(fofoId,fortyFiveAgoDate,todayDate);
            for(SoldAllCatalogitemQtyByPartnerModel soldAllCatalogitemQtyByPartnerModel : soldAllCatalogitemQtyByPartnerModels){
               SmartCartSuggestion smartCartSuggestion = new SmartCartSuggestion();

                // weekly average = total sold qty / 6 weeks
                long avgWeeklyQty = Math.round((float) soldAllCatalogitemQtyByPartnerModel.getSoldQty() / 6);

                // ensure minimum 2
                long suggestedQty = Math.max(1, avgWeeklyQty);

               smartCartSuggestion.setCatalogId(soldAllCatalogitemQtyByPartnerModel.getCatalogId());
               smartCartSuggestion.setFofoId(fofoId);
               smartCartSuggestion.setSoldQty(soldAllCatalogitemQtyByPartnerModel.getSoldQty());
               smartCartSuggestion.setSuggestedQty(suggestedQty);
               smartCartSuggestion.setCreationDate(LocalDate.now());
               smartCartSuggestionRepository.persist(smartCartSuggestion);
            }
        }

    }


}