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.IdAmountModel;

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.time.temporal.TemporalAdjusters;

import java.time.DayOfWeek;

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 gmailRelaySender;

    @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 = gmailRelaySender.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);

        gmailRelaySender.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 {

        // Weekly date range: Previous Monday to Sunday

        LocalDate today = LocalDate.now();

        LocalDate previousMonday = today.with(TemporalAdjusters.previous(DayOfWeek.MONDAY));

        LocalDate previousSunday = previousMonday.plusDays(6);

        LocalDateTime startOfWeek = previousMonday.atStartOfDay();

        LocalDateTime endOfWeek = previousSunday.atTime(23, 59, 59);

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

        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("dd-MMM-yyyy");

        // 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 the week

        List<RbmRating> feedbackList = rbmRatingRepository.selectByRbmIdsAndDateRange(rbmIds, startOfWeek, endOfWeek);

        if (feedbackList.isEmpty()) {

            LOGGER.info("No feedback entries found for RBMs for the week.");

            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));

        // Calculate RBM statistics: average rating and unique partner count

        Map<Integer, List<RbmRating>> feedbackByRbm = feedbackList.stream()

                .collect(Collectors.groupingBy(RbmRating::getRbmId));

        List<RbmWeeklyStats> rbmStatsList = new ArrayList<>();

        for (Map.Entry<Integer, List<RbmRating>> entry : feedbackByRbm.entrySet()) {

            int rbmId = entry.getKey();

            List<RbmRating> ratings = entry.getValue();

            double avgRating = ratings.stream()

                    .mapToInt(RbmRating::getRating)

                    .average()

                    .orElse(0.0);

            long uniquePartnerCount = ratings.stream()

                    .map(RbmRating::getFofoId)

                    .distinct()

                    .count();

            String rbmName = rbmNameMap.getOrDefault(rbmId, "Unknown RBM");

            rbmStatsList.add(new RbmWeeklyStats(rbmId, rbmName, avgRating, (int) uniquePartnerCount));

        }

        // Find max partner count for normalization

        int maxPartnerCount = rbmStatsList.stream()

                .mapToInt(RbmWeeklyStats::getPartnerCount)

                .max()

                .orElse(1);

        // Calculate combined score: 65% partner count + 35% rating

        // Score = (0.65 × partnerCount/maxPartnerCount) + (0.35 × avgRating/5)

        for (RbmWeeklyStats stats : rbmStatsList) {

            double partnerScore = (double) stats.getPartnerCount() / maxPartnerCount;

            double ratingScore = stats.getAvgRating() / 5.0;

            double combinedScore = (0.65 * partnerScore) + (0.35 * ratingScore);

            stats.setCombinedScore(combinedScore);

        }

        // Sort by combined score DESC and assign rank

        rbmStatsList.sort((a, b) -> Double.compare(b.getCombinedScore(), a.getCombinedScore()));

        for (int i = 0; i < rbmStatsList.size(); i++) {

            rbmStatsList.get(i).setRank(i + 1);

        }

        // Create map for quick lookup of stats by RBM ID

        Map<Integer, RbmWeeklyStats> rbmStatsMap = rbmStatsList.stream()

                .collect(Collectors.toMap(RbmWeeklyStats::getRbmId, s -> s));

        // Generate HTML content

        StringBuilder emailContent = new StringBuilder();

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

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

        emailContent.append("<p>Here is the <b>Weekly RBM Rating and Feedback Summary</b> for the week: <b>")

                .append(previousMonday.format(dateFormatter))

                .append(" to ")

                .append(previousSunday.format(dateFormatter))

                .append("</b></p>");

        // RBM Ranking Summary Table

        emailContent.append("<h3>RBM Weekly Rankings</h3>");

        emailContent.append("<p style='font-size: 12px; color: #666;'>Rank = 65% Partner Count + 35% Avg Rating</p>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");

        emailContent.append("<tr style='background-color: #4CAF50; color: white;'>")

                .append("<th>Rank</th>")

                .append("<th>RBM Name</th>")

                .append("<th>Partner Count</th>")

                .append("<th>Avg Rating</th>")

                .append("<th>Score</th>")

                .append("</tr>");

        // Already sorted by rank (combined score DESC)

        for (RbmWeeklyStats stats : rbmStatsList) {

            emailContent.append("<tr>")

                    .append("<td style='text-align: center; font-weight: bold;'>").append(stats.getRank()).append("</td>")

                    .append("<td>").append(stats.getRbmName()).append("</td>")

                    .append("<td style='text-align: center;'>").append(stats.getPartnerCount()).append("</td>")

                    .append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getAvgRating())).append("</td>")

                    .append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getCombinedScore())).append("</td>")

                    .append("</tr>");

        }

        emailContent.append("</table>");

        // Detailed Feedback Table

        emailContent.append("<br><h3>Detailed Feedback</h3>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");

        emailContent.append("<tr style='background-color: #2196F3; color: white;'>")

                .append("<th>RBM Name</th>")

                .append("<th>Rank</th>")

                .append("<th>Partner Name</th>")

                .append("<th>Rating</th>")

                .append("<th>Comment</th>")

                .append("<th>Date</th>")

                .append("</tr>");

        for (RbmRating rating : feedbackList) {

            int rbmId = rating.getRbmId();

            String rbmName = rbmNameMap.getOrDefault(rbmId, "Unknown RBM");

            String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");

            RbmWeeklyStats stats = rbmStatsMap.get(rbmId);

            emailContent.append("<tr>")

                    .append("<td>").append(rbmName).append("</td>")

                    .append("<td style='text-align: center; font-weight: bold;'>").append(stats != null ? stats.getRank() : "-").append("</td>")

                    .append("<td>").append(partnerName).append("</td>")

                    .append("<td style='text-align: center;'>").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 = "Weekly RBM Feedback Summary - " + previousMonday.format(dateFormatter) + " to " + previousSunday.format(dateFormatter);

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

        sendTo.add("kamini.sharma@smartdukaan.com");

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

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

        LOGGER.info("Weekly RBM feedback summary email sent for week: {} to {}", previousMonday, previousSunday);

    }

    // Inner class to hold RBM weekly statistics

    private static class RbmWeeklyStats {

        private final int rbmId;

        private final String rbmName;

        private final double avgRating;

        private final int partnerCount;

        private double combinedScore;

        private int rank;

        public RbmWeeklyStats(int rbmId, String rbmName, double avgRating, int partnerCount) {

            this.rbmId = rbmId;

            this.rbmName = rbmName;

            this.avgRating = avgRating;

            this.partnerCount = partnerCount;

        }

        public int getRbmId() { return rbmId; }

        public String getRbmName() { return rbmName; }

        public double getAvgRating() { return avgRating; }

        public int getPartnerCount() { return partnerCount; }

        public double getCombinedScore() { return combinedScore; }

        public void setCombinedScore(double combinedScore) { this.combinedScore = combinedScore; }

        public int getRank() { return rank; }

        public void setRank(int rank) { this.rank = rank; }

    }

    public void sendSalesFeedbackSummaryEmail() throws MessagingException, ProfitMandiBusinessException, IOException {

        // Weekly date range: Previous Monday to Sunday

        LocalDate today = LocalDate.now();

        LocalDate previousMonday = today.with(TemporalAdjusters.previous(DayOfWeek.MONDAY));

        LocalDate previousSunday = previousMonday.plusDays(6);

        LocalDateTime startOfWeek = previousMonday.atStartOfDay();

        LocalDateTime endOfWeek = previousSunday.atTime(23, 59, 59);

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

        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("dd-MMM-yyyy");

        // 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 Sales L1 for the week

        List<SalesRating> feedbackList = salesRatingRepository.selectBySalesL1IdsAndDateRange(salesL1Ids, startOfWeek, endOfWeek);

        if (feedbackList.isEmpty()) {

            LOGGER.info("No feedback entries found for Sales for the week.");

            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 Sales L1 ID to name for quick lookup

        Map<Integer, String> salesL1NameMap = authUsers.stream()

                .collect(Collectors.toMap(AuthUser::getId, AuthUser::getFullName));

        // Calculate Sales L1 statistics: average rating and unique partner count

        Map<Integer, List<SalesRating>> feedbackBySales = feedbackList.stream()

                .collect(Collectors.groupingBy(SalesRating::getSalesL1Id));

        List<SalesWeeklyStats> salesStatsList = new ArrayList<>();

        for (Map.Entry<Integer, List<SalesRating>> entry : feedbackBySales.entrySet()) {

            int salesL1Id = entry.getKey();

            List<SalesRating> ratings = entry.getValue();

            double avgRating = ratings.stream()

                    .mapToInt(SalesRating::getRating)

                    .average()

                    .orElse(0.0);

            long uniquePartnerCount = ratings.stream()

                    .map(SalesRating::getFofoId)

                    .distinct()

                    .count();

            String salesL1Name = salesL1NameMap.getOrDefault(salesL1Id, "Unknown Sales Person");

            salesStatsList.add(new SalesWeeklyStats(salesL1Id, salesL1Name, avgRating, (int) uniquePartnerCount));

        }

        // Find max partner count for normalization

        int maxPartnerCount = salesStatsList.stream()

                .mapToInt(SalesWeeklyStats::getPartnerCount)

                .max()

                .orElse(1);

        // Calculate combined score: 65% partner count + 35% rating

        // Score = (0.65 × partnerCount/maxPartnerCount) + (0.35 × avgRating/5)

        for (SalesWeeklyStats stats : salesStatsList) {

            double partnerScore = (double) stats.getPartnerCount() / maxPartnerCount;

            double ratingScore = stats.getAvgRating() / 5.0;

            double combinedScore = (0.65 * partnerScore) + (0.35 * ratingScore);

            stats.setCombinedScore(combinedScore);

        }

        // Sort by combined score DESC and assign rank

        salesStatsList.sort((a, b) -> Double.compare(b.getCombinedScore(), a.getCombinedScore()));

        for (int i = 0; i < salesStatsList.size(); i++) {

            salesStatsList.get(i).setRank(i + 1);

        }

        // Create map for quick lookup of stats by Sales L1 ID

        Map<Integer, SalesWeeklyStats> salesStatsMap = salesStatsList.stream()

                .collect(Collectors.toMap(SalesWeeklyStats::getSalesL1Id, s -> s));

        // Generate HTML content

        StringBuilder emailContent = new StringBuilder();

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

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

        emailContent.append("<p>Here is the <b>Weekly Sales L1 Rating and Feedback Summary</b> for the week: <b>")

                .append(previousMonday.format(dateFormatter))

                .append(" to ")

                .append(previousSunday.format(dateFormatter))

                .append("</b></p>");

        // Sales L1 Ranking Summary Table

        emailContent.append("<h3>Sales L1 Weekly Rankings</h3>");

        emailContent.append("<p style='font-size: 12px; color: #666;'>Rank = 65% Partner Count + 35% Avg Rating</p>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");

        emailContent.append("<tr style='background-color: #4CAF50; color: white;'>")

                .append("<th>Rank</th>")

                .append("<th>Sales L1 Name</th>")

                .append("<th>Partner Count</th>")

                .append("<th>Avg Rating</th>")

                .append("<th>Score</th>")

                .append("</tr>");

        // Already sorted by rank (combined score DESC)

        for (SalesWeeklyStats stats : salesStatsList) {

            emailContent.append("<tr>")

                    .append("<td style='text-align: center; font-weight: bold;'>").append(stats.getRank()).append("</td>")

                    .append("<td>").append(stats.getSalesL1Name()).append("</td>")

                    .append("<td style='text-align: center;'>").append(stats.getPartnerCount()).append("</td>")

                    .append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getAvgRating())).append("</td>")

                    .append("<td style='text-align: center;'>").append(String.format("%.2f", stats.getCombinedScore())).append("</td>")

                    .append("</tr>");

        }

        emailContent.append("</table>");

        // Detailed Feedback Table

        emailContent.append("<br><h3>Detailed Feedback</h3>");

        emailContent.append("<table border='1' cellspacing='0' cellpadding='5' style='border-collapse: collapse;'>");

        emailContent.append("<tr style='background-color: #2196F3; color: white;'>")

                .append("<th>Sales L1 Name</th>")

                .append("<th>Rank</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) {

            int salesL1Id = rating.getSalesL1Id();

            String salesL1Name = salesL1NameMap.getOrDefault(salesL1Id, "Unknown Sales Person");

            String partnerName = fofoNameMap.getOrDefault(rating.getFofoId(), "Unknown Partner");

            SalesWeeklyStats stats = salesStatsMap.get(salesL1Id);

            PartnerType partnerType = partnerTypeChangeService.getTypeOnDate(rating.getFofoId(), LocalDate.now());

            emailContent.append("<tr>")

                    .append("<td>").append(salesL1Name).append("</td>")

                    .append("<td style='text-align: center; font-weight: bold;'>").append(stats != null ? stats.getRank() : "-").append("</td>")

                    .append("<td>").append(partnerName).append("</td>")

                    .append("<td>").append(partnerType).append("</td>")

                    .append("<td style='text-align: center;'>").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 = "Weekly Sales L1 Feedback Summary - " + previousMonday.format(dateFormatter) + " to " + previousSunday.format(dateFormatter);

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

        sendTo.add("kamini.sharma@smartdukaan.com");

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

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

        LOGGER.info("Weekly Sales L1 feedback summary email sent for week: {} to {}", previousMonday, previousSunday);

    }

    // Inner class to hold Sales L1 weekly statistics

    private static class SalesWeeklyStats {

        private final int salesL1Id;

        private final String salesL1Name;

        private final double avgRating;

        private final int partnerCount;

        private double combinedScore;

        private int rank;

        public SalesWeeklyStats(int salesL1Id, String salesL1Name, double avgRating, int partnerCount) {

            this.salesL1Id = salesL1Id;

            this.salesL1Name = salesL1Name;

            this.avgRating = avgRating;

            this.partnerCount = partnerCount;

        }

        public int getSalesL1Id() { return salesL1Id; }

        public String getSalesL1Name() { return salesL1Name; }

        public double getAvgRating() { return avgRating; }

        public int getPartnerCount() { return partnerCount; }

        public double getCombinedScore() { return combinedScore; }

        public void setCombinedScore(double combinedScore) { this.combinedScore = combinedScore; }

        public int getRank() { return rank; }

        public void setRank(int rank) { this.rank = rank; }

    }

    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 {

        long __biReportStartMs = System.currentTimeMillis();

        LOGGER.info("[BI_REPORT] START batch-optimized generateBiReportExcel at {}", LocalDateTime.now());

        LocalDateTime startOfToday;

        LocalDateTime previousDay;

        if (LocalDate.now().getDayOfMonth() == 1) {

            startOfToday = LocalDate.now().minusDays(1).atStartOfDay();

            previousDay = startOfToday.with(LocalTime.MAX);

        } else {

            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();

                    return !storeCode.equalsIgnoreCase("UPGBN640") && !storeCode.equalsIgnoreCase("HRYN039");

                })

                .map(CustomRetailer::getPartnerId)

                .collect(Collectors.toList());

        Set<Integer> retailerIdSet = new HashSet<>(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(PartnerDailyInvestment::getFofoId, x -> x));

        }

        YearMonth currentMonth;

        LocalDateTime currentMonthStartDate;

        LocalDateTime currentMonthEndDate;

        if (LocalDate.now().getDayOfMonth() == 1) {

            currentMonth = YearMonth.now().minusMonths(1);

            currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();

            currentMonthEndDate = currentMonth.atEndOfMonth().atTime(23, 59, 59);

        } else {

            currentMonth = YearMonth.now();

            currentMonthStartDate = currentMonth.atDay(1).atStartOfDay();

            currentMonthEndDate = LocalDate.now().minusDays(1).atTime(23, 59, 59);

        }

        String currentMonthStringValue = String.valueOf(currentMonth);

        YearMonth lastMonth = currentMonth.minusMonths(1);

        String lastMonthStringValue = String.valueOf(lastMonth);

        LocalDateTime lastMontStartDate = lastMonth.atDay(1).atStartOfDay();

        LocalDateTime lastMonthEndDate = lastMonth.atEndOfMonth().atTime(23, 59, 59);

        YearMonth twoMonthsAgo = currentMonth.minusMonths(2);

        String twoMonthAgoStringValue = String.valueOf(twoMonthsAgo);

        LocalDateTime twoMonthsAgoStartDate = twoMonthsAgo.atDay(1).atStartOfDay();

        LocalDateTime twoMonthsAgoEndDate = twoMonthsAgo.atEndOfMonth().atTime(23, 59, 59);

        LocalDateTime dayBeforeStart = previousDay.toLocalDate().atStartOfDay().minusDays(1);

        LocalDateTime dayBeforeEnd = previousDay.minusDays(1);

        LocalDateTime yesterdayStart = previousDay.toLocalDate().atStartOfDay();

        // Returns / RTO — already batched

        Map<Integer, Long> currentMonthPartnerReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(currentMonthStartDate, currentMonthEndDate)

                .stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        Map<Integer, Long> currentMonthRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(currentMonthStartDate, currentMonthEndDate)

                .stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        Map<Integer, Long> yesterdayReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(yesterdayStart, previousDay)

                .stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        Map<Integer, Long> yesterdayRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(yesterdayStart, previousDay)

                .stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        Map<Integer, Long> dayBeforeYesterdayReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(dayBeforeStart, dayBeforeEnd)

                .stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        Map<Integer, Long> dayBeforeYesterdayRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(dayBeforeStart, dayBeforeEnd)

                .stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        Map<Integer, Long> lastMonthPartnerReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(lastMontStartDate, lastMonthEndDate)

                .stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        Map<Integer, Long> lastMonthRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(lastMontStartDate, lastMonthEndDate)

                .stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        Map<Integer, Long> twoMonthAgoPartnerReturnOrderInfoModelMap = returnOrderInfoRepository.selectAllByBetweenDate(twoMonthsAgoStartDate, twoMonthsAgoEndDate)

                .stream().collect(Collectors.groupingBy(ReturnOrderInfoModel::getRetailerId, Collectors.summingLong(x -> Math.round(x.getRefundAmount()))));

        Map<Integer, Long> twoMonthAgoRtoRefundOrderMap = orderRepository.selectAllRefundOrderDatesBetween(twoMonthsAgoStartDate, twoMonthsAgoEndDate)

                .stream().collect(Collectors.groupingBy(Order::getRetailerId, Collectors.summingLong(x -> Math.round(x.getTotalAmount()))));

        // ---- Batch pre-fetch everything else that was previously per-fofo ----

        // Auth hierarchy:

        //   RBM  = TICKET_CATEGORY_RBM   → L1, fallback L2

        //   BM   = TICKET_CATEGORY_SALES → L4

        //   Sales Manager = TICKET_CATEGORY_SALES → L1, fallback L2, L3, L4, L5

        //   ABM  = removed from report

        Map<Integer, Integer> rbmL1Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L1, retailerIdSet);

        Map<Integer, Integer> rbmL2Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_RBM, EscalationType.L2, retailerIdSet);

        Map<Integer, Integer> bmMap = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L4, retailerIdSet);

        Map<Integer, Integer> salesManagerL1Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L1, retailerIdSet);

        Map<Integer, Integer> salesManagerL2Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L2, retailerIdSet);

        Map<Integer, Integer> salesManagerL3Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L3, retailerIdSet);

        Map<Integer, Integer> salesManagerL4Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L4, retailerIdSet);

        Map<Integer, Integer> salesManagerL5Map = csService.getAuthUserIdsWithoutTicketAssigneeByFofoIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, EscalationType.L5, retailerIdSet);

        Set<Integer> allAuthUserIds = new HashSet<>();

        for (Map<Integer, Integer> m : Arrays.asList(rbmL1Map, rbmL2Map, bmMap, salesManagerL1Map, salesManagerL2Map, salesManagerL3Map, salesManagerL4Map, salesManagerL5Map)) {

            m.values().stream().filter(v -> v != null && v != 0).forEach(allAuthUserIds::add);

        }

        Map<Integer, AuthUser> authUserMap = allAuthUserIds.isEmpty() ? new HashMap<>()

                : authRepository.selectByIds(new ArrayList<>(allAuthUserIds)).stream()

                    .collect(Collectors.toMap(AuthUser::getId, u -> u, (a, b) -> a));

        // AST batch

        Set<Integer> astIds = customRetailers.values().stream()

                .map(CustomRetailer::getAstId).filter(id -> id != null && id != 0).collect(Collectors.toSet());

        Map<Integer, AST> astMap = astIds.isEmpty() ? new HashMap<>()

                : astRepository.selectByIds(new ArrayList<>(astIds)).stream()

                    .collect(Collectors.toMap(AST::getId, a -> a, (a, b) -> a));

        // FofoStore batch (replaces the two duplicate per-fofo calls)

        Map<Integer, FofoStore> fofoStoreMap = fofoStoreRepository.selectByRetailerIds(retailerIds).stream()

                .collect(Collectors.toMap(FofoStore::getId, s -> s, (a, b) -> a));

        // Users + wallet-creation dates

        Map<Integer, User> userMap = userRepository.selectByIds(retailerIds).stream()

                .collect(Collectors.toMap(User::getId, u -> u, (a, b) -> a));

        Map<Integer, LocalDateTime> walletFirstCreatedMap = userWalletHistoryRepository.selectFirstCreatedDatesForFofoIds(retailerIds);

        // Partner type for today

        Map<Integer, PartnerType> partnerTypeMap = partnerTypeChangeService.getTypesForFofoIds(retailerIds, LocalDate.now());

        // Monthly targets

        Map<Integer, Double> currentTargetMap = monthlyTargetRepository.selectByExactDateAndFofoIds(currentMonth, retailerIds).stream()

                .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));

        Map<Integer, Double> lastTargetMap = monthlyTargetRepository.selectByExactDateAndFofoIds(lastMonth, retailerIds).stream()

                .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));

        Map<Integer, Double> twoMonthAgoTargetMap = monthlyTargetRepository.selectByExactDateAndFofoIds(twoMonthsAgo, retailerIds).stream()

                .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));

        // DRR target: RbmTargetService.calculateFofoIdTodayTarget always uses YearMonth.now() internally, regardless of

        // the date arg. On the 1st this differs from currentMonth (previous month) — preserve original semantics.

        Map<Integer, Double> drrTargetMap = YearMonth.now().equals(currentMonth) ? currentTargetMap

                : monthlyTargetRepository.selectByExactDateAndFofoIds(YearMonth.now(), retailerIds).stream()

                    .collect(Collectors.toMap(MonthlyTarget::getFofoId, MonthlyTarget::getPurchaseTarget, (a, b) -> a));

        // Monthly secondary (order value) — batched

        Map<Integer, Double> mtdSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,

                startOfToday.withDayOfMonth(1), previousDay).stream()

                .collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));

        Map<Integer, Double> yesterDaySecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,

                yesterdayStart, previousDay).stream()

                .collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));

        Map<Integer, Double> dayBeforeSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,

                dayBeforeStart, dayBeforeEnd).stream()

                .collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));

        Map<Integer, Double> lastMonthSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,

                lastMontStartDate, lastMonthEndDate).stream()

                .collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));

        Map<Integer, Double> twoMonthAgoSecondaryMap = orderRepository.selectOrderValueBetweenBillingDatesGroupByFofoId(retailerIds,

                twoMonthsAgoStartDate, twoMonthsAgoEndDate).stream()

                .collect(Collectors.toMap(IdAmountModel::getId, IdAmountModel::getAmount, (a, b) -> a));

        // Tertiary MTD / last-month / two-months-ago (all-partners scan via fofoId=0)

        LocalDateTime now = LocalDateTime.now();

        Map<Integer, Double> mtdSaleTillYesterdayMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(startOfToday.withDayOfMonth(1), previousDay, 0, false);

        Map<Integer, Double> lastMonthSaleMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(lastMontStartDate, lastMonthEndDate, 0, false);

        Map<Integer, Double> twoMonthAgoSaleMap = fofoOrderItemRepository.selectSumMopGroupByRetailer(twoMonthsAgoStartDate, twoMonthsAgoEndDate, 0, false);

        // Monthly activated-but-not-billed

        Map<Integer, Map<YearMonth, PartnerWiseActivatedNotBilledTotal>> activatedNotBilledByFofo = new HashMap<>();

        for (PartnerWiseActivatedNotBilledTotal t : activatedImeiRepository.getTotalMonthlyActivatedNotBilledForFofoIds(retailerIds, twoMonthsAgoStartDate)) {

            activatedNotBilledByFofo.computeIfAbsent(t.getFofoId(), k -> new HashMap<>())

                    .put(YearMonth.parse(t.getYearMonth()), t);

        }

        // Brand-wise tertiary MTD (per fofo → brand → amount)

        Map<Integer, Map<String, Double>> brandTertiaryByFofo = fofoOrderItemRepository.selectSumAmountGroupByBrandForFofoIds(currentMonthStartDate, currentMonthEndDate, retailerIds);

        // Brand-wise secondary billed (MTD)

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

        for (BrandWiseModel m : orderRepository.selectAllBilledByCategoryOrderGroupByBrandFofoIds(retailerIds, currentMonthStartDate, currentMonthEndDate, Arrays.asList(10006, 10001))) {

            brandBilledByFofo.computeIfAbsent(m.getFofoId(), k -> new HashMap<>()).merge(m.getBrand(), m.getAmount(), Long::sum);

        }

        // Brand-wise returns + RTO returns (MTD)

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

        for (BrandWiseReturnInfo r : returnOrderInfoRepository.selectAllBrandWiseByBetweenDateForFofoIds(currentMonthStartDate, currentMonthEndDate.plusDays(1), retailerIds)) {

            brandReturnByFofo.computeIfAbsent(r.getRetailerId(), k -> new HashMap<>()).merge(r.getBrand(), r.getReturnAmount(), Double::sum);

        }

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

        for (BrandWiseReturnInfo r : returnOrderInfoRepository.selectAllBrandWiseRTORefundByBetweenDateForFofoIds(currentMonthStartDate, currentMonthEndDate.plusDays(1), retailerIds)) {

            brandRtoReturnByFofo.computeIfAbsent(r.getRetailerId(), k -> new HashMap<>()).merge(r.getBrand(), r.getReturnAmount(), Double::sum);

        }

        // Active loans grouped per fofo, plus batched loanId → sum.

        // The single-fofo selectAllActiveLoan(fofoId) additionally filters pendingAmount > 0 — must preserve.

        Map<Integer, List<Loan>> activeLoansByFofo = loanRepository.selectAllActiveLoan().stream()

                .filter(l -> retailerIdSet.contains(l.getFofoId())

                        && l.getPendingAmount() != null

                        && l.getPendingAmount().doubleValue() > 0)

                .collect(Collectors.groupingBy(Loan::getFofoId));

        Set<Integer> loanIdsForSum = new HashSet<>();

        activeLoansByFofo.values().forEach(list -> list.forEach(l -> loanIdsForSum.add(l.getId())));

        defaultLoans.forEach(l -> loanIdsForSum.add(l.getId()));

        Map<Integer, Double> loanStatementSumByLoanId = loanStatementRepository.sumAmountByLoanIds(new ArrayList<>(loanIdsForSum));

        // Last order per fofo (batched)

        Map<Integer, Integer> lastOrderIdByFofo = orderRepository.getLastOrderByFofoIds(retailerIds);

        Set<Integer> lastOrderIds = lastOrderIdByFofo.values().stream().filter(id -> id != null && id != 0).collect(Collectors.toSet());

        Map<Integer, Order> lastOrderById = new HashMap<>();

        for (Integer oid : lastOrderIds) {

            Order o = orderRepository.selectById(oid);

            if (o != null) lastOrderById.put(oid, o);

        }

        // DRR precomputation — the two dates we ever call with

        long day1RemainingDays = rbmTargetService.getRemainingDaysInMonth(currentMonth.atDay(1));

        long todayRemainingDays = rbmTargetService.getRemainingDaysInMonth(startOfToday.toLocalDate());

        LOGGER.info("[BI_REPORT] batch pre-fetch complete in {}ms; retailers={}, entering per-fofo loop",

                System.currentTimeMillis() - __biReportStartMs, retailerIds.size());

        long __biReportLoopStartMs = System.currentTimeMillis();

        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) {

            // resolve auth-user names from batched maps

            String rbmName = "";

            int rbmL1 = rbmL1Map.getOrDefault(fofoId, 0);

            if (rbmL1 != 0 && authUserMap.get(rbmL1) != null) {

                rbmName = authUserMap.get(rbmL1).getFullName();

            } else {

                int rbmL2 = rbmL2Map.getOrDefault(fofoId, 0);

                if (rbmL2 != 0 && authUserMap.get(rbmL2) != null) {

                    rbmName = authUserMap.get(rbmL2).getFullName();

                }

            }

            String bmName = "";

            int bmId = bmMap.getOrDefault(fofoId, 0);

            if (bmId != 0 && authUserMap.get(bmId) != null) {

                bmName = authUserMap.get(bmId).getFullName();

            }

            // Sales Manager: L1 → L2 → L3 → L4 → L5 fallback in SALES category

            String managerName = "";

            int managerL1 = salesManagerL1Map.getOrDefault(fofoId, 0);

            int managerL2 = salesManagerL2Map.getOrDefault(fofoId, 0);

            int managerL3 = salesManagerL3Map.getOrDefault(fofoId, 0);

            int managerL4 = salesManagerL4Map.getOrDefault(fofoId, 0);

            int managerL5 = salesManagerL5Map.getOrDefault(fofoId, 0);

            String salesL3Name = (managerL3 != 0 && authUserMap.get(managerL3) != null)

                    ? authUserMap.get(managerL3).getFullName() : "";

            if (managerL1 != 0 && authUserMap.get(managerL1) != null) {

                managerName = authUserMap.get(managerL1).getFullName();

            } else if (managerL2 != 0 && authUserMap.get(managerL2) != null) {

                managerName = authUserMap.get(managerL2).getFullName();

            } else if (managerL3 != 0 && authUserMap.get(managerL3) != null) {

                managerName = authUserMap.get(managerL3).getFullName();

            } else if (managerL4 != 0 && authUserMap.get(managerL4) != null) {

                managerName = authUserMap.get(managerL4).getFullName();

            } else if (managerL5 != 0 && authUserMap.get(managerL5) != null) {

                managerName = authUserMap.get(managerL5).getFullName();

            }