Subversion Repositories SmartDukaan

package com.spice.profitmandi.service;

import com.spice.profitmandi.common.enumuration.ActivationType;
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.CustomRetailer;
import com.spice.profitmandi.common.model.ProfitMandiConstants;
import com.spice.profitmandi.dao.entity.auth.AuthUser;
import com.spice.profitmandi.dao.entity.auth.PartnerCollectionRemark;
import com.spice.profitmandi.dao.entity.auth.RbmCallSequenceLog;
import com.spice.profitmandi.dao.entity.cs.AgentCallLog;
import com.spice.profitmandi.dao.entity.cs.Position;
import com.spice.profitmandi.dao.entity.cs.Ticket;
import com.spice.profitmandi.dao.entity.fofo.FofoStore;
import com.spice.profitmandi.dao.entity.fofo.MonthlyTarget;
import com.spice.profitmandi.dao.entity.fofo.RetailerContact;
import com.spice.profitmandi.dao.entity.inventory.RbmAchievements;
import com.spice.profitmandi.dao.entity.inventory.RbmTargets;
import com.spice.profitmandi.dao.entity.user.Address;
import com.spice.profitmandi.dao.enumuration.auth.CollectionRemark;
import com.spice.profitmandi.dao.enumuration.cs.EscalationType;
import com.spice.profitmandi.dao.model.*;
import com.spice.profitmandi.dao.repository.auth.AuthRepository;
import com.spice.profitmandi.dao.repository.auth.PartnerCollectionRemarkRepository;
import com.spice.profitmandi.dao.repository.auth.RbmCallSequenceLogRepository;
import com.spice.profitmandi.dao.repository.catalog.RbmAchievementsRepository;
import com.spice.profitmandi.dao.repository.catalog.RbmTargetsRepository;
import com.spice.profitmandi.dao.repository.cs.CsService;
import com.spice.profitmandi.dao.repository.cs.PositionRepository;
import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;
import com.spice.profitmandi.dao.repository.dtr.RetailerContactRepository;
import com.spice.profitmandi.dao.repository.fofo.MonthlyTargetRepository;
import com.spice.profitmandi.dao.repository.logistics.PublicHolidaysRepository;
import com.spice.profitmandi.dao.repository.transaction.LoanRepository;
import com.spice.profitmandi.dao.repository.transaction.OrderRepository;
import com.spice.profitmandi.dao.repository.user.AddressRepository;
import com.spice.profitmandi.service.user.RetailerService;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.NativeQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Component;

import javax.persistence.TypedQuery;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.stream.Collectors;

@Component
public class RbmTargetServiceImpl implements RbmTargetService {
    private static final Logger LOGGER = LogManager.getLogger(RbmTargetServiceImpl.class);

    @Autowired
    SessionFactory sessionFactory;

    @Autowired
    RbmTargetsRepository rbmTargetsRepository;

    @Autowired
    RbmAchievementsRepository rbmAchievementsRepository;

    @Autowired
    MonthlyTargetRepository monthlyTargetRepository;

    @Autowired
    PublicHolidaysRepository publicHolidaysRepository;

    @Autowired
    RetailerService retailerService;

    @Override
    public List<WarehouseRbmTargetModel> getWarehouseWiseRbmMonthlyTarget() {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<WarehouseRbmTargetModel> typedQuerySimilar = session.createNamedQuery("RbmTarget.getWarehouseWiseMonthlyTarget", WarehouseRbmTargetModel.class);

        return typedQuerySimilar.getResultList();

    }

    @Override
    public List<MTDAchievedTargetModel> getDateWiseAchievedTargetOfRbm(LocalDate startDate, LocalDate endDate) {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<MTDAchievedTargetModel> typedQuerySimilar = session.createNamedQuery("RbmTarget.getRbmAchievedMonthlyTarget", MTDAchievedTargetModel.class);
        typedQuerySimilar.setParameter("startDate", startDate);
        typedQuerySimilar.setParameter("endDate", endDate);
        return typedQuerySimilar.getResultList();

    }

    @Override
    public List<TodayAchievedMovementModel> getMovementWiseAchievementByDate(LocalDate startDate, LocalDate endDate) {
        LOGGER.info("start date {}, end date {}", startDate, endDate);
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<TodayAchievedMovementModel> typedQuerySimilar = session.createNamedQuery("RBMTarget.TodayAchivementByMovement", TodayAchievedMovementModel.class);
        typedQuerySimilar.setParameter("startDate", startDate);
        typedQuerySimilar.setParameter("endDate", endDate);
        return typedQuerySimilar.getResultList();

    }

    @Override
    public List<WarehouseMobileStockByMovementModel> getWarehouseMobileStockByMovement() {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<WarehouseMobileStockByMovementModel> typedQuerySimilar = session.createNamedQuery("WarehouseStock.MovementWiseMobileStock", WarehouseMobileStockByMovementModel.class);

        return typedQuerySimilar.getResultList();

    }

    @Override
    public List<SoldCatalogsReportModel> getCatalogSoldReport(LocalDate startDate, LocalDate endDate) {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<SoldCatalogsReportModel> typedQuerySimilar = session.createNamedQuery("CatalogsReport.SoldCatalogsReport", SoldCatalogsReportModel.class);
        typedQuerySimilar.setParameter("startDate", startDate);
        typedQuerySimilar.setParameter("endDate", endDate);
        return typedQuerySimilar.getResultList();

    }


    public int getWorkingDaysCount(LocalDate startDate) {
        Session session = sessionFactory.getCurrentSession();

        // Convert the LocalDate to a format MySQL can interpret
        String startDateString = startDate.toString();

        final NativeQuery<?> nativeQuery = session.createNativeQuery(
                "SELECT (DATEDIFF(LAST_DAY(:startDate), :startDate) + 1) " +
                        " - (FLOOR((DATEDIFF(LAST_DAY(:startDate), :startDate) + (WEEKDAY(:startDate) + 1)) / 7)) " +
                        " - (SELECT COUNT(*) " +
                        " FROM logistics.publicholidays " +
                        " WHERE date BETWEEN :startDate AND LAST_DAY(:startDate) " +
                        " AND WEEKDAY(date) != 6) AS working_days"
        );

        // Set the start date parameter for each placeholder
        nativeQuery.setParameter("startDate", startDateString);

        Object result = nativeQuery.getSingleResult();
        return result != null ? ((Number) result).intValue() : 0;
    }



    @Override
    public List<RbmArrViewModel> getRbmTodayArr() throws Exception {
        LocalDate todayDate = LocalDate.now();
        return getRbmTodayArr(todayDate);
    }

    @Override
    public List<RbmTargetAndAchievementsModel> getRbmTargetsAndAchievemnts(LocalDate startDate, LocalDate endDate) {

        List<RbmTargetsModel> rbmTargetsList = rbmTargetsRepository.selectTargetsModelListByDates(startDate.atStartOfDay(), endDate.atTime(LocalTime.MAX));

        LOGGER.info("rbmTargetsList {}", rbmTargetsList);
        // Group Targtes by RBM and Warehouse
        Map<String, RbmTargetsModel> targetsMap = rbmTargetsList.stream()
                .collect(Collectors.toMap(
                        a -> a.getRbmAuthId() + "-" + a.getWarehouseId(),
                        a -> a,
                        (a1, a2) -> mergeTargets(a1, a2) // Handle duplicates by merging
                ));


        List<RbmAchievementsModel> rbmAchievements = rbmAchievementsRepository.selectAchievementsModelListByDates(startDate.atStartOfDay(), endDate.atTime(LocalTime.MAX));
        LOGGER.info("rbmTargetsList {}", rbmAchievements);
        // Group achievements by RBM and Warehouse
        Map<String, RbmAchievementsModel> achievementMap = rbmAchievements.stream()
                .collect(Collectors.toMap(
                        a -> a.getRbmAuthId() + "-" + a.getWarehouseId(),
                        a -> a,
                        (a1, a2) -> mergeAchievements(a1, a2) // Handle duplicates by merging
                ));

        return targetsMap.keySet().stream()
                .map(key -> {
                    String[] parts = key.split("-");
                    int rbmAuthId = Integer.parseInt(parts[0]);
                    int warehouseId = Integer.parseInt(parts[1]);

                    RbmTargetsModel target = targetsMap.get(key);
                    RbmAchievementsModel achievement = achievementMap.getOrDefault(key, new RbmAchievementsModel());

                    RbmTargetAndAchievementsModel model = new RbmTargetAndAchievementsModel();
                    model.setAuthId(rbmAuthId);
                    model.setRbmName(target.getRbmName());
                    model.setWarehouseName(ProfitMandiConstants.WAREHOUSE_MAP.getOrDefault(warehouseId, "Unknown"));

                    // Set target values
                    model.setHidTarget((long) target.getHidTarget());
                    model.setRunningTarget((long) target.getRunningTarget());
                    model.setFastMovingTarget((long) target.getFastMovingTarget());
                    model.setSlowMovingTarget((long) target.getSlowMovingTarget());
                    model.setOtherMovingTarget((long) target.getOtherTarget());

                    // Set achievement values
                    model.setAchievedHid((long) achievement.getAchievedHidTarget());
                    model.setAchievedRunning((long) achievement.getAchievedRunningTarget());
                    model.setAchievedFastMoving((long) achievement.getAchievedFastMovingTarget());
                    model.setAchievedSlowMoving((long) achievement.getAchievedSlowMovingTarget());
                    model.setAchievedOtherMoving((long) achievement.getAchievedOtherTarget());

                    model.setTotalTarget(
                            (long) target.getHidTarget() +
                                    (long) target.getRunningTarget() +
                                    (long) target.getFastMovingTarget() +
                                    (long) target.getSlowMovingTarget() +
                                    (long) target.getOtherTarget()
                    );
                    model.setTotalAchievemnt(
                            (long) achievement.getAchievedHidTarget() +
                                    (long) achievement.getAchievedRunningTarget() +
                                    (long) achievement.getAchievedFastMovingTarget() +
                                    (long) achievement.getAchievedSlowMovingTarget() +
                                    (long) achievement.getAchievedOtherTarget()
                    );

                    return model;
                })
                .collect(Collectors.toList());

    }

    private RbmTargetsModel mergeTargets(RbmTargetsModel a1, RbmTargetsModel a2) {

        // Merge logic for achievements (aggregate the target and achieved values)
        a1.setHidTarget((a1.getHidTarget()) +
                (a2.getHidTarget()));

        a1.setFastMovingTarget((a1.getFastMovingTarget()) +
                (a2.getFastMovingTarget()));

        a1.setSlowMovingTarget((a1.getSlowMovingTarget()) +
                (a2.getSlowMovingTarget()));

        a1.setRunningTarget((a1.getRunningTarget()) +
                (a2.getRunningTarget()));

        a1.setOtherTarget((a1.getOtherTarget()) +
                (a2.getOtherTarget()));
        return a1;
    }

    private RbmAchievementsModel mergeAchievements(RbmAchievementsModel a1, RbmAchievementsModel a2) {
        // Merge logic for achievements (aggregate the target and achieved values)
        a1.setAchievedHidTarget((a1.getAchievedHidTarget()) +
                (a2.getAchievedHidTarget()));

        a1.setAchievedRunningTarget((a1.getAchievedRunningTarget()) +
                (a2.getAchievedRunningTarget()));

        a1.setAchievedFastMovingTarget((a1.getAchievedFastMovingTarget()) +
                (a2.getAchievedFastMovingTarget()));

        a1.setAchievedSlowMovingTarget((a1.getAchievedSlowMovingTarget()) +
                (a2.getAchievedSlowMovingTarget()));

        a1.setAchievedOtherTarget((a1.getAchievedOtherTarget()) +
                (a2.getAchievedOtherTarget()));

        return a1;
    }

    @Override
    public List<RbmArrViewModel> getRbmTodayArr(LocalDate todayDate) throws Exception {

        LocalDate startDateOfMonthDay1 = LocalDate.now().withDayOfMonth(1);

        List<WarehouseRbmTargetModel> warehouseRbmTargetModelList = this.getWarehouseWiseRbmMonthlyTarget();
        List<WarehouseRbmTargetModel> warehouseRbmTargetModels = warehouseRbmTargetModelList.stream().filter(x -> x.getMonthlyTarget() > 0).collect(Collectors.toList());
        LOGGER.info("warehouseRbmTargetModels {}", warehouseRbmTargetModels);
        List<TodayAchievedMovementModel> todayAchievedMovementModels = getMovementWiseAchievementByDate(todayDate, todayDate.plusDays(1));

        List<MTDAchievedTargetModel> mtdAchievedTargetModels = getDateWiseAchievedTargetOfRbm(startDateOfMonthDay1, todayDate);

        int remainingWorkingDaysCount = (int) getRemainingDaysInMonth(todayDate);

        List<RbmTargets> todayRbmTargetsList = rbmTargetsRepository.selectTargetsByDates(todayDate.atStartOfDay(), todayDate.atTime(LocalTime.MAX));

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

        List<RbmArrViewModel> rbmArrViewModels = new ArrayList<>();

        if (!todayRbmTargetsList.isEmpty()) {

            // OPTIMIZED: Pre-build maps for O(1) lookup instead of O(n) filter in each iteration
            // Map key: "authId-warehouseId"
            Map<String, Double> mtdAchievedMap = mtdAchievedTargetModels.stream()
                    .collect(Collectors.groupingBy(
                            x -> x.getAuthId() + "-" + x.getWarehouseId(),
                            Collectors.summingDouble(MTDAchievedTargetModel::getAcheivedMonthlyTarget)
                    ));

            Map<String, TodayAchievedMovementModel> todayAchievedMap = todayAchievedMovementModels.stream()
                    .collect(Collectors.toMap(
                            x -> x.getAuthId() + "-" + x.getWarehouseId(),
                            x -> x,
                            (a, b) -> a
                    ));

            Map<String, RbmTargets> todayRbmTargetsMap = todayRbmTargetsList.stream()
                    .collect(Collectors.toMap(
                            x -> x.getRbmAuthId() + "-" + x.getWarehouseId(),
                            x -> x,
                            (a, b) -> a
                    ));

            for (WarehouseRbmTargetModel rbmTarget : warehouseRbmTargetModels) {

                String lookupKey = rbmTarget.getAuthId() + "-" + rbmTarget.getWarehouseId();

                float monthlyTarget = rbmTarget.getMonthlyTarget();
                float achievedSoFar = mtdAchievedMap.getOrDefault(lookupKey, 0.0).floatValue();

                float remainingTarget = monthlyTarget - achievedSoFar;

                float todayTarget = (remainingWorkingDaysCount > 0 && remainingTarget > 0) ? remainingTarget / remainingWorkingDaysCount : 0;

                String warehouseName = ProfitMandiConstants.WAREHOUSE_MAP.getOrDefault(rbmTarget.getWarehouseId(), "Unknown");

                LOGGER.info("rbmTarget ==== {}", rbmTarget);

                TodayAchievedMovementModel todayAchievedMovementModel = todayAchievedMap.get(lookupKey);

                RbmTargets todayRbmTargets = todayRbmTargetsMap.get(lookupKey);

                if (todayRbmTargets != null) {
                    LOGGER.info("todayRbmTargets {}", todayRbmTargets);
                    RbmArrViewModel viewModel = new RbmArrViewModel();

                    viewModel.setAuthId(rbmTarget.getAuthId());
                    viewModel.setRbmName(rbmTarget.getRbmName());
                    viewModel.setWarehouseName(warehouseName);
                    viewModel.setTodayTarget(Math.round(todayTarget));
                    viewModel.setMonthlyTarget(Math.round(monthlyTarget));
                    viewModel.setMtdAchievedTarget(Math.round(achievedSoFar));

                    viewModel.setTodayHidTarget(Math.round((todayRbmTargets.getHidTarget())));
                    viewModel.setTodayFastMovingTarget(Math.round(todayRbmTargets.getFastMovingTarget()));
                    viewModel.setTodaySlowMovingTarget(Math.round(todayRbmTargets.getSlowMovingtarget()));
                    viewModel.setTodayRunningTarget(Math.round(todayRbmTargets.getRunningtarget()));
                    viewModel.setTodayOtherMovingTarget(Math.round(todayRbmTargets.getOtherTarget()));

                    if (todayAchievedMovementModel != null) {
                        viewModel.setTodayAchievedHidTarget(Math.round(todayAchievedMovementModel.getHidBilled()));
                        viewModel.setTodayAchievedFastMovingTarget(Math.round(todayAchievedMovementModel.getFastMovingBilled()));
                        viewModel.setTodayAchievedSlowMovingTarget(Math.round(todayAchievedMovementModel.getSlowMovinBilled()));
                        viewModel.setTodayAchievedRunningTarget(Math.round(todayAchievedMovementModel.getRunningBilled()));
                        viewModel.setTodayAchievedOtherMovingTarget(Math.round(todayAchievedMovementModel.getOtherBilled()));
                        viewModel.setTotalAchievedTarget(Math.round(todayAchievedMovementModel.getHidBilled() + todayAchievedMovementModel.getFastMovingBilled() + todayAchievedMovementModel.getSlowMovinBilled() + todayAchievedMovementModel.getRunningBilled() + todayAchievedMovementModel.getOtherBilled()));
                    } else {
                        viewModel.setTodayAchievedHidTarget(0);
                        viewModel.setTodayAchievedFastMovingTarget(0);
                        viewModel.setTodayAchievedSlowMovingTarget(0);
                        viewModel.setTodayAchievedRunningTarget(0);
                        viewModel.setTodayAchievedOtherMovingTarget(0);
                        viewModel.setTotalAchievedTarget(0);
                    }
                    rbmArrViewModels.add(viewModel);
                } else {
                    LOGGER.info("No matching RbmTargets found for AuthId: {} and rbmname {} and WarehouseId: {}", rbmTarget.getAuthId(), rbmTarget.getRbmName(), rbmTarget.getWarehouseId());
                }



            }
        }

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

    @Override
    public void setMovementWiseRbmTargets() {
        LocalDate todayDate = LocalDate.now();

        LocalDate startDateOfMonthDay1 = LocalDate.now().withDayOfMonth(1);

        List<WarehouseRbmTargetModel> warehouseRbmTargetModels = this.getWarehouseWiseRbmMonthlyTarget();

        List<MTDAchievedTargetModel> mtdAchievedTargetModels = getDateWiseAchievedTargetOfRbm(startDateOfMonthDay1, todayDate);


        int remainingWorkingDaysCount = (int) getRemainingDaysInMonth(todayDate);

        List<WarehouseMobileStockByMovementModel> warehouseMobileStockByMovementModels = getWarehouseMobileStockByMovement();

        // OPTIMIZED: Pre-build maps for O(1) lookup instead of O(n) filter in each iteration
        Map<String, Double> mtdAchievedMap = mtdAchievedTargetModels.stream()
                .collect(Collectors.groupingBy(
                        x -> x.getAuthId() + "-" + x.getWarehouseId(),
                        Collectors.summingDouble(MTDAchievedTargetModel::getAcheivedMonthlyTarget)
                ));

        Map<Integer, WarehouseMobileStockByMovementModel> warehouseStockMap = warehouseMobileStockByMovementModels.stream()
                .collect(Collectors.toMap(
                        WarehouseMobileStockByMovementModel::getWarehouseId,
                        x -> x,
                        (a, b) -> a
                ));

        for (WarehouseRbmTargetModel rbmTarget : warehouseRbmTargetModels) {

            String lookupKey = rbmTarget.getAuthId() + "-" + rbmTarget.getWarehouseId();

            float monthlyTarget = rbmTarget.getMonthlyTarget();
            float achievedSoFar = mtdAchievedMap.getOrDefault(lookupKey, 0.0).floatValue();


            float remainingTarget = monthlyTarget - achievedSoFar;
            LOGGER.info("remainingTarget {}", remainingTarget);

            float todayTarget = (remainingWorkingDaysCount > 0 && remainingTarget > 0) ? remainingTarget / remainingWorkingDaysCount : 0;
            LOGGER.info("todayTarget {}", todayTarget);

            // Get the warehouse stock data
            WarehouseMobileStockByMovementModel warehouseMobileStockByMovementModel = warehouseStockMap.get(rbmTarget.getWarehouseId());


            if (warehouseMobileStockByMovementModel != null) {

                // Total stock value for this warehouse
                float totalStockValue = warehouseMobileStockByMovementModel.getTotalAvailabilityPrice();

                // Calculate target allocation based on stock value proportion
                float hidTarget = (warehouseMobileStockByMovementModel.getTotalHidCatalogPrice() / totalStockValue) * todayTarget;
                float fastMovingTarget = (warehouseMobileStockByMovementModel.getTotalFastMovingCatalogPrice() / totalStockValue) * todayTarget;
                float slowMovingTarget = (warehouseMobileStockByMovementModel.getTotalSlowMovingCatalogPrice() / totalStockValue) * todayTarget;
                float runningTarget = (warehouseMobileStockByMovementModel.getTotalRunningCatalogPrice() / totalStockValue) * todayTarget;
                float otherTarget = (warehouseMobileStockByMovementModel.getTotalOtherCategoryCatalogPrice() / totalStockValue) * todayTarget;

                RbmTargets rbmTargets = new RbmTargets();
                rbmTargets.setWarehouseId(rbmTarget.getWarehouseId());
                rbmTargets.setRbmAuthId(rbmTarget.getAuthId());
                rbmTargets.setRbmName(rbmTarget.getRbmName());
                rbmTargets.setRunningtarget(runningTarget);
                rbmTargets.setHidTarget(hidTarget);
                rbmTargets.setFastMovingTarget(fastMovingTarget);
                rbmTargets.setSlowMovingtarget(slowMovingTarget);
                rbmTargets.setOtherTarget(otherTarget);
                rbmTargets.setCreateTimestamp(LocalDateTime.now());

                rbmTargetsRepository.persist(rbmTargets);

            }
        }

    }


    @Override
    public void setMovementWiseRbmAchievement() {
        LocalDate todayDate = LocalDate.now();

        List<TodayAchievedMovementModel> todayAchievedMovementModels = getMovementWiseAchievementByDate(todayDate, todayDate.plusDays(1));


        for (TodayAchievedMovementModel achievement : todayAchievedMovementModels) {

            RbmAchievements rbmAchievements = new RbmAchievements();

            rbmAchievements.setRbmAuthId(achievement.getAuthId());
            rbmAchievements.setRbmName(achievement.getRbmName());
            rbmAchievements.setWarehouseId(achievement.getWarehouseId());
            rbmAchievements.setAchievedHidTarget(achievement.getHidBilled());
            rbmAchievements.setAchievedFastMovingTarget(achievement.getFastMovingBilled());
            rbmAchievements.setAchievedSlowMovingTarget(achievement.getSlowMovinBilled());
            rbmAchievements.setAchievedRunningTarget(achievement.getRunningBilled());
            rbmAchievements.setAchievedOtherTarget(achievement.getOtherBilled());
            rbmAchievements.setCreateTimestamp(LocalDateTime.now());

            rbmAchievementsRepository.persist(rbmAchievements);

        }

    }

    @Override
    public List<Sold15daysOldAgingModel> getAgingSale(LocalDate startDate, LocalDate endDate) {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<Sold15daysOldAgingModel> typedQuerySimilar = session.createNamedQuery("Aging.SoldAgingModel", Sold15daysOldAgingModel.class);
        typedQuerySimilar.setParameter("startDate", startDate);
        typedQuerySimilar.setParameter("endDate", endDate);
        return typedQuerySimilar.getResultList();

    }

    @Override
    public List<RbmBilledFofoIdsModel> getDateWiseBilledFofoIdByRbm(LocalDate startDate, LocalDate endDate) {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<RbmBilledFofoIdsModel> typedQuerySimilar = session.createNamedQuery("RBM.RbmBilledFofoId", RbmBilledFofoIdsModel.class);
        typedQuerySimilar.setParameter("startDate", startDate);
        typedQuerySimilar.setParameter("endDate", endDate);
        return typedQuerySimilar.getResultList();
    }

    @Override
    @Cacheable(value = "rbmWeeklyBilling",
            cacheManager = "fiveMintimeoutCacheManager",
            sync = true)
    public List<RbmWeeklyBillingModel> getWeeklyBillingDataForMonth(LocalDate monthStart, LocalDate monthEnd) {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<RbmWeeklyBillingModel> typedQuery = session.createNamedQuery("RBM.WeeklyBilling", RbmWeeklyBillingModel.class);
        typedQuery.setParameter("startDate", monthStart);
        typedQuery.setParameter("endDate", monthEnd);
        return typedQuery.getResultList();
    }

    public List<Our15DaysOldAgingStock> our15DaysAgingStock() {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<Our15DaysOldAgingStock> typedQuerySimilar = session.createNamedQuery("Aging.15DaysOurStock", Our15DaysOldAgingStock.class);
        return typedQuerySimilar.getResultList();
    }

    @Override
    public List<WarehouseAgingStockModel> getWarehouseWiseAgingStock() {
        Session session = sessionFactory.getCurrentSession();
        final TypedQuery<WarehouseAgingStockModel> typedQuery = session.createNamedQuery("Aging.15DaysWarehouseWiseStock", WarehouseAgingStockModel.class);
        return typedQuery.getResultList();
    }

    @Autowired
    OrderRepository orderRepository;

    @Override
    public double calculateFofoIdTodayTarget(int fofoId, double secondryMtd,LocalDate date) {

        MonthlyTarget monthlyTarget = monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId);
        if (monthlyTarget == null) {
            // Log or handle as needed
            return 0; // or -1 or some fallback
        }

        double remainingTarget = monthlyTarget.getPurchaseTarget() - secondryMtd;
//        double remainingWorkingDays = getWorkingDaysCount(date);
        double remainingWorkingDays = (double) getRemainingDaysInMonth(date);



        if (remainingWorkingDays == 0) return remainingTarget; // Last day
        LOGGER.info("remainingWorkingDays {}", remainingWorkingDays);
        LOGGER.info("remainingTarget {}", remainingTarget);

        return (int) Math.ceil(remainingTarget / remainingWorkingDays);
    }

    @Override
    public long getRemainingDaysInMonth(LocalDate date) {
        LocalDate lastDayOfMonth = YearMonth.from(date).atEndOfMonth();

        long totalDays = ChronoUnit.DAYS.between(date, lastDayOfMonth) + 1;

        // Count Sundays manually
        long sundayCount = 0;
        LocalDate current = date;
        while (!current.isAfter(lastDayOfMonth)) {
            if (current.getDayOfWeek() == DayOfWeek.SUNDAY) {
                sundayCount++;
            }
            current = current.plusDays(1);
        }

        // Public holidays in the range
        long publicHolidays = publicHolidaysRepository
                .selectAllBetweenDates(date, lastDayOfMonth)
                .size();

        long remainingDays = totalDays - sundayCount - publicHolidays;

        LOGGER.info("remainingDays {}", remainingDays);
        LOGGER.info("totalDays {}", totalDays);
        LOGGER.info("sundays {}", sundayCount);
        LOGGER.info("publicHolidays {}", publicHolidays);

        return remainingDays;
    }

    @Autowired
    PositionRepository positionRepository;

    @Autowired
    CsService csService;

    @Autowired
    FofoStoreRepository fofoStoreRepository;

    @Autowired
    AuthRepository authRepository;

    @Autowired
    LoanRepository loanRepository;

    @Autowired
    PartnerCollectionService partnerCollectionService;

    @Autowired
    PartnerCollectionRemarkRepository partnerCollectionRemarkRepository;

    @Autowired
    RbmCallSequenceLogRepository rbmCallSequenceLogRepository;

    @Autowired
    com.spice.profitmandi.dao.repository.cs.TicketRepository ticketRepository;

    @Autowired
    com.spice.profitmandi.dao.repository.cs.AgentCallLogRepository agentCallLogRepository;

    @Autowired
    RetailerContactRepository retailerContactRepository;

    @Autowired
    AddressRepository addressRepository;

    @Autowired
    com.spice.profitmandi.dao.repository.cs.PartnerPositionRepository partnerPositionRepository;

    @Override
    public List<RbmCallTargetModel> getRbmCallTargetModels() throws Exception {
        return getRbmCallTargetModels(LocalDate.now());
    }

    public List<RbmCallTargetModel> getRbmCallTargetModels(LocalDate queryDate) throws Exception {
        long methodStart = System.currentTimeMillis();
        List<RbmCallTargetModel> rbmCallTargetModels = new ArrayList<>();

        // Get all RBM positions (L1 and L2)
        long start = System.currentTimeMillis();
        List<Position> allRbmPositions = positionRepository
                .selectPositionByCategoryId(ProfitMandiConstants.TICKET_CATEGORY_RBM).stream()
                .filter(x -> Arrays.asList(EscalationType.L1, EscalationType.L2, EscalationType.L3).contains(x.getEscalationType()))
                .collect(Collectors.toList());

        // Separate L1, L2 and L3 auth IDs
        List<Integer> l1AuthIds = allRbmPositions.stream()
                .filter(p -> EscalationType.L1.equals(p.getEscalationType()))
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
        List<Integer> l2AuthIds = allRbmPositions.stream()
                .filter(p -> EscalationType.L2.equals(p.getEscalationType()))
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
        List<Integer> l3AuthIds = allRbmPositions.stream()
                .filter(p -> EscalationType.L3.equals(p.getEscalationType()))
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());

        // Union of all auth IDs for batch fetching
        List<Integer> rbmPositionsAuthIds = allRbmPositions.stream()
                .map(Position::getAuthUserId).distinct().collect(Collectors.toList());
        LOGGER.info("RBM Call Target - RBM positions fetch: {}ms, L1: {}, L2: {}, L3: {}", System.currentTimeMillis() - start, l1AuthIds.size(), l2AuthIds.size(), l3AuthIds.size());

        start = System.currentTimeMillis();
        Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMapping();
        LOGGER.info("RBM Call Target - StoreGuyMap fetch: {}ms", System.currentTimeMillis() - start);

        LocalDateTime startDate = queryDate.atStartOfDay();
        LocalDate firstOfMonth = queryDate.withDayOfMonth(1);
        LocalDate endOfMonth = queryDate.withDayOfMonth(queryDate.lengthOfMonth()).plusDays(1);

        // Get auth user map
        start = System.currentTimeMillis();
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(rbmPositionsAuthIds).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au));
        LOGGER.info("RBM Call Target - AuthUser fetch: {}ms", System.currentTimeMillis() - start);

        // Batch fetch positions by auth IDs (to check if RBM is L1)
        start = System.currentTimeMillis();
        Map<Integer, List<Position>> positionsByAuthId = positionRepository.selectPositionByAuthIds(rbmPositionsAuthIds).stream()
                .collect(Collectors.groupingBy(Position::getAuthUserId));
        LOGGER.info("RBM Call Target - Positions by AuthId fetch: {}ms", System.currentTimeMillis() - start);

        // Get all fofo IDs for all RBMs
        Set<Integer> allFofoIds = new HashSet<>();
        Map<Integer, List<Integer>> rbmToFofoIdsMap = new HashMap<>();
        for (int rbmAuthId : rbmPositionsAuthIds) {
            AuthUser au = authUserMap.get(rbmAuthId);
            if (au != null && storeGuyMap.containsKey(au.getEmailId())) {
                List<Integer> fofoIds = new ArrayList<>(storeGuyMap.get(au.getEmailId()));
                allFofoIds.addAll(fofoIds);
                rbmToFofoIdsMap.put(rbmAuthId, fofoIds);
            }
        }
        // Initialize L2 calling list map - will be populated after fetching remarks
        Map<Integer, List<Integer>> l2AuthIdToFofoIds = new HashMap<>();
        for (int l2AuthId : l2AuthIds) {
            l2AuthIdToFofoIds.put(l2AuthId, new ArrayList<>());
        }
        // Initialize L3 calling list map - will be populated after fetching remarks
        Map<Integer, List<Integer>> l3AuthIdToFofoIds = new HashMap<>();
        for (int l3AuthId : l3AuthIds) {
            l3AuthIdToFofoIds.put(l3AuthId, new ArrayList<>());
        }
        LOGGER.info("RBM Call Target - Total fofo IDs to process: {}", allFofoIds.size());

        // Get only needed fofo stores (OPTIMIZED - was fetching ALL stores before)
        start = System.currentTimeMillis();
        Map<Integer, FofoStore> fofoStoresMap = new HashMap<>();
        if (!allFofoIds.isEmpty()) {
            try {
                fofoStoresMap = fofoStoreRepository.selectByRetailerIds(new ArrayList<>(allFofoIds)).stream()
                        .collect(Collectors.toMap(FofoStore::getId, x -> x, (a, b) -> a));
            } catch (ProfitMandiBusinessException e) {
                LOGGER.error("Error fetching fofo stores", e);
            }
        }
        LOGGER.info("RBM Call Target - FofoStores fetch (only needed): {}ms, count: {}", System.currentTimeMillis() - start, fofoStoresMap.size());

        // Batch fetch max remark ids for all fofoIds (for escalation filtering)
        start = System.currentTimeMillis();
        Map<Integer, PartnerCollectionRemark> allPartnerCollectionRemarks = new HashMap<>();
        if (!allFofoIds.isEmpty()) {
            List<Integer> allRemarkIds = partnerCollectionRemarkRepository.selectMaxRemarkId(new ArrayList<>(allFofoIds));
            if (!allRemarkIds.isEmpty()) {
                allPartnerCollectionRemarks = partnerCollectionRemarkRepository.selectByIds(allRemarkIds).stream()
                        .collect(Collectors.toMap(PartnerCollectionRemark::getFofoId, x -> x, (a, b) -> a));
            }
        }
        LOGGER.info("RBM Call Target - PartnerCollectionRemarks fetch: {}ms", System.currentTimeMillis() - start);

        // Populate L2 calling list based on partners whose latest remark is RBM_L2_ESCALATION
        // Find the L1 who has the partner and add to that L1's manager (L2) calling list
        for (Map.Entry<Integer, PartnerCollectionRemark> entry : allPartnerCollectionRemarks.entrySet()) {
            Integer fofoId = entry.getKey();
            PartnerCollectionRemark remark = entry.getValue();

            if (CollectionRemark.RBM_L2_ESCALATION.equals(remark.getRemark())) {
                // Find which L1 RBM has this partner assigned
                for (int l1AuthId : l1AuthIds) {
                    List<Integer> l1FofoIds = rbmToFofoIdsMap.getOrDefault(l1AuthId, Collections.emptyList());
                    if (l1FofoIds.contains(fofoId)) {
                        // Get L1's manager (L2)
                        AuthUser l1User = authUserMap.get(l1AuthId);
                        if (l1User != null && l2AuthIdToFofoIds.containsKey(l1User.getManagerId())) {
                            int l2ManagerId = l1User.getManagerId();
                            l2AuthIdToFofoIds.get(l2ManagerId).add(fofoId);
                        }
                        break; // Found the L1 for this fofoId
                    }
                }
            }
        }
        LOGGER.info("RBM Call Target - L2 calling lists populated from RBM_L2_ESCALATION remarks");

        // Populate L3 calling list based on partners whose latest remark is RBM_L3_ESCALATION
        // Find the L1 who originally has the partner, then:
        // Case 1: L1 -> L3 directly (if L1's manager IS L3)
        // Case 2: L1 -> L2 -> L3 (if L1's manager is L2, then L2's manager is L3)
        for (Map.Entry<Integer, PartnerCollectionRemark> entry : allPartnerCollectionRemarks.entrySet()) {
            Integer fofoId = entry.getKey();
            PartnerCollectionRemark remark = entry.getValue();

            if (CollectionRemark.RBM_L3_ESCALATION.equals(remark.getRemark())) {
                // Find which L1 RBM originally has this partner assigned
                for (int l1AuthId : l1AuthIds) {
                    List<Integer> l1FofoIds = rbmToFofoIdsMap.getOrDefault(l1AuthId, Collections.emptyList());
                    if (l1FofoIds.contains(fofoId)) {
                        AuthUser l1User = authUserMap.get(l1AuthId);
                        if (l1User != null) {
                            int l1ManagerId = l1User.getManagerId();
                            // Case 1: L1's manager IS L3 directly (L1 → L3, no L2 in between)
                            if (l3AuthIdToFofoIds.containsKey(l1ManagerId)) {
                                l3AuthIdToFofoIds.get(l1ManagerId).add(fofoId);
                                LOGGER.debug("L3 Calling List (direct): fofoId={} -> L1={} -> L3={}",
                                        fofoId, l1AuthId, l1ManagerId);
                            } else {
                                // Case 2: L1 -> L2 -> L3
                                AuthUser l2User = authUserMap.get(l1ManagerId);
                                if (l2User != null && l3AuthIdToFofoIds.containsKey(l2User.getManagerId())) {
                                    int l3ManagerId = l2User.getManagerId();
                                    l3AuthIdToFofoIds.get(l3ManagerId).add(fofoId);
                                    LOGGER.debug("L3 Calling List: fofoId={} -> L1={} -> L2={} -> L3={}",
                                            fofoId, l1AuthId, l1ManagerId, l3ManagerId);
                                }
                            }
                        }
                        break; // Found the L1 for this fofoId
                    }
                }
            }
        }
        LOGGER.info("RBM Call Target - L3 calling lists populated from RBM_L3_ESCALATION remarks");

        // Batch fetch collection RANK map for all fofoIds (OPTIMIZED - only fetches rank, not full model)
        start = System.currentTimeMillis();
        Map<Integer, Integer> allCollectionRankMap = new HashMap<>();
        if (!allFofoIds.isEmpty()) {
            try {
                allCollectionRankMap = partnerCollectionService.getCollectionRankMap(new ArrayList<>(allFofoIds), startDate);
            } catch (ProfitMandiBusinessException e) {
                LOGGER.error("Error fetching collection rank map for all fofoIds", e);
            }
        }
        LOGGER.info("RBM Call Target - CollectionRankMap fetch (OPTIMIZED): {}ms", System.currentTimeMillis() - start);

        // Get MTD billing data for zero billing calculation and partner counts
        start = System.currentTimeMillis();
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);
        Set<Integer> allMtdBilledFofoIds = mtdBillingData.stream()
                .filter(RbmWeeklyBillingModel::isMtdBilled)
                .map(RbmWeeklyBillingModel::getFofoId)
                .collect(Collectors.toSet());
        // Build partner count and fofoIds per RBM from mtdBillingData (same source as Today ARR page)
        Map<Integer, Set<Integer>> mtdFofoIdsByAuthId = mtdBillingData.stream()
                .filter(RbmWeeklyBillingModel::isTargetedPartner)
                .collect(Collectors.groupingBy(RbmWeeklyBillingModel::getAuthId,
                        Collectors.mapping(RbmWeeklyBillingModel::getFofoId, Collectors.toSet())));
        LOGGER.info("RBM Call Target - MTD Billing fetch: {}ms", System.currentTimeMillis() - start);

        // Batch fetch today's remarks for all auth IDs (to calculate Value Achieved)
        start = System.currentTimeMillis();
        Map<Integer, List<PartnerCollectionRemark>> remarksByAuthId = partnerCollectionRemarkRepository
                .selectAllByAuthIdsOnDate(rbmPositionsAuthIds, LocalDate.now()).stream()
                .collect(Collectors.groupingBy(PartnerCollectionRemark::getAuthId));
        LOGGER.info("RBM Call Target - Today Remarks fetch: {}ms", System.currentTimeMillis() - start);

        // Batch fetch today's out-of-sequence logs for all RBMs
        start = System.currentTimeMillis();
        LocalDateTime todayStart = LocalDate.now().atStartOfDay();
        LocalDateTime todayEnd = LocalDate.now().plusDays(1).atStartOfDay();
        List<RbmCallSequenceLog> outOfSequenceLogs = rbmCallSequenceLogRepository.selectOutOfSequenceByDateRange(todayStart, todayEnd);
        Map<Integer, Long> outOfSequenceCountByAuthId = outOfSequenceLogs.stream()
                .collect(Collectors.groupingBy(RbmCallSequenceLog::getAuthId,
                        Collectors.mapping(RbmCallSequenceLog::getFofoId, Collectors.collectingAndThen(Collectors.toSet(), s -> (long) s.size()))));
        LOGGER.info("RBM Call Target - Out of Sequence fetch: {}ms", System.currentTimeMillis() - start);

        // BATCH FETCH: All call logs for all RBMs (L1 + L2 + L3) in a single query.
        // Replaces the previous N+1 pattern where getCallStats() was called per RBM.
        start = System.currentTimeMillis();
        List<AgentCallLog> allCallLogs = agentCallLogRepository.findByAuthIdsAndDate(rbmPositionsAuthIds, queryDate);
        Map<Long, List<AgentCallLog>> callLogsByAuthId = allCallLogs.stream()
                .collect(Collectors.groupingBy(AgentCallLog::getAuthId));
        LOGGER.info("RBM Call Target - Call logs batch fetch: {}ms ({} logs across {} RBMs)",
                System.currentTimeMillis() - start, allCallLogs.size(), callLogsByAuthId.size());

        // BATCH FETCH: Build a single mobile -> fofoId map from all unique customer numbers across all call logs.
        // Replaces the previous N+1 pattern where findFofoIdByMobile() was called per call log entry.
        start = System.currentTimeMillis();
        Set<String> allNormalizedMobiles = new HashSet<>();
        for (AgentCallLog callLog : allCallLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber != null) {
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
                allNormalizedMobiles.add(normalized);
            }
        }
        Map<String, Integer> mobileToFofoIdMap = buildMobileToFofoIdMap(allNormalizedMobiles);
        LOGGER.info("RBM Call Target - Mobile→FofoId batch fetch: {}ms ({} unique mobiles, {} mapped)",
                System.currentTimeMillis() - start, allNormalizedMobiles.size(), mobileToFofoIdMap.size());

        // Identify users who are both L1 and L2 — they will be shown only as L2
        Set<Integer> l2AuthIdSet = new HashSet<>(l2AuthIds);

        // Process L1 RBMs (skip users who are also L2 — their data will be merged into L2 model)
        for (int rbmAuthId : l1AuthIds) {
            if (l2AuthIdSet.contains(rbmAuthId)) {
                continue; // Will be handled in L2 processing with merged L1 data
            }
            AuthUser authUser = authUserMap.get(rbmAuthId);
            if (authUser == null || !storeGuyMap.containsKey(authUser.getEmailId())) {
                continue;
            }

            List<Integer> fofoIdList = rbmToFofoIdsMap.getOrDefault(rbmAuthId, Collections.emptyList());

            // Check if RBM is L1 (same logic as getSummaryModel)
            List<Position> positions = positionsByAuthId.getOrDefault(authUser.getId(), Collections.emptyList());
            boolean isRBMAndL1 = positions.stream()
                    .anyMatch(position ->
                            ProfitMandiConstants.TICKET_CATEGORY_RBM == position.getCategoryId()
                                    && EscalationType.L1.equals(position.getEscalationType()));

            // Filter escalated partners for L1 RBMs (same logic as getSummaryModel)
            List<Integer> fofoIds = fofoIdList;
            if (isRBMAndL1) {
                Map<Integer, PartnerCollectionRemark> partnerCollectionRemarks = new HashMap<>();
                for (Integer fofoId : fofoIdList) {
                    if (allPartnerCollectionRemarks.containsKey(fofoId)) {
                        partnerCollectionRemarks.put(fofoId, allPartnerCollectionRemarks.get(fofoId));
                    }
                }
                fofoIds = partnerCollectionRemarks.entrySet().stream()
                        .filter(entry -> {
                            PartnerCollectionRemark pcrMap = entry.getValue();
                            return !(CollectionRemark.RBM_L2_ESCALATION.equals(pcrMap.getRemark())
                                    || CollectionRemark.SALES_ESCALATION.equals(pcrMap.getRemark()));
                        })
                        .map(Map.Entry::getKey)
                        .collect(Collectors.toList());
            }

            // Filter to only external, ACTIVE or REVIVAL stores (collection plan not required)
            Map<Integer, Integer> finalAllCollectionRankMap = allCollectionRankMap;
            Map<Integer, FofoStore> finalFofoStoresMap = fofoStoresMap;
            List<Integer> validFofoIds = fofoIds.stream()
                    .filter(fofoId -> {
                        FofoStore store = finalFofoStoresMap.get(fofoId);
                        if (store == null || store.isInternal()) {
                            return false;
                        }
                        // Only include ACTIVE or REVIVAL partners (not Low Sale, not Disputed, not Billing Pending)
                        return ActivationType.ACTIVE.equals(store.getActivationType())
                                || ActivationType.REVIVAL.equals(store.getActivationType());
                    })
                    .collect(Collectors.toList());

            if (validFofoIds.isEmpty()) {
                continue;
            }

            RbmCallTargetModel targetModel = new RbmCallTargetModel();
            targetModel.setAuthId(rbmAuthId);
            targetModel.setRbmName(authUser.getFullName());
            // Use partner count from mtdBillingData (same source as Today ARR page)
            Set<Integer> mtdFofoIds = mtdFofoIdsByAuthId.getOrDefault(rbmAuthId, Collections.emptySet());
            targetModel.setPartnerCount(mtdFofoIds.size());

            // Categorize each partner - each partner belongs to ONE category only
            // Priority: PlanToday > CarryForward > ZeroBilling > Untouched > FuturePlan > Normal
            // Revival is counted separately (just for display, doesn't affect categorization)
            Set<Integer> planTodayPartners = new HashSet<>();
            Set<Integer> carryForwardPartners = new HashSet<>();
            Set<Integer> untouchedPartners = new HashSet<>();
            Set<Integer> zeroBillingPartners = new HashSet<>();
            Set<Integer> futurePlanPartners = new HashSet<>();
            Set<Integer> normalPartners = new HashSet<>();
            Set<Integer> revivalPartners = new HashSet<>();

            for (Integer fofoId : validFofoIds) {
                // Get collection plan rank (from optimized rank map)
                int rank = allCollectionRankMap.getOrDefault(fofoId, 5); // default to Normal if no plan

                // Check if partner has zero billing in MTD
                boolean hasZeroBilling = !allMtdBilledFofoIds.contains(fofoId);

                // Count REVIVAL partners separately (just for display, doesn't affect categorization)
                FofoStore store = finalFofoStoresMap.get(fofoId);
                if (store != null && ActivationType.REVIVAL.equals(store.getActivationType())) {
                    revivalPartners.add(fofoId);
                }

                // Assign to category based on priority
                if (rank == 1) {
                    planTodayPartners.add(fofoId);
                } else if (rank == 2) {
                    carryForwardPartners.add(fofoId);
                } else if (hasZeroBilling) {
                    zeroBillingPartners.add(fofoId);
                } else if (rank == 3) {
                    untouchedPartners.add(fofoId);
                } else if (rank == 4) {
                    futurePlanPartners.add(fofoId);
                } else {
                    normalPartners.add(fofoId);
                }
            }

            // Set counts
            targetModel.setCreditCollection(0); // Credit collection is handled in separate list
            targetModel.setPlanToday(planTodayPartners.size());
            targetModel.setCarryForward(carryForwardPartners.size());
            targetModel.setUntouched(untouchedPartners.size());
            targetModel.setZeroBilling(zeroBillingPartners.size());
            targetModel.setFuturePlan(futurePlanPartners.size());
            targetModel.setNormal(normalPartners.size());
            targetModel.setRevival(revivalPartners.size());

            // Today Target = PlanToday + CarryForward + ZeroBilling + Untouched
            // These are mutually exclusive now, so we can sum them
            long todayTarget = planTodayPartners.size() +
                    carryForwardPartners.size() + zeroBillingPartners.size() + untouchedPartners.size();
            targetModel.setTodayTargetOfCall(todayTarget);

            // Create set of partners in Today Target categories
            Set<Integer> todayTargetPartners = new HashSet<>();
            todayTargetPartners.addAll(planTodayPartners);
            todayTargetPartners.addAll(carryForwardPartners);
            todayTargetPartners.addAll(zeroBillingPartners);
            todayTargetPartners.addAll(untouchedPartners);

            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
            long[] callStats = getCallStatsFromLogs(callLogsByAuthId.get((long) rbmAuthId), mobileToFofoIdMap);
            targetModel.setValueTargetAchieved(callStats[0]);
            targetModel.setTotalRecordingCalls(callStats[1]);
            targetModel.setUniqueRecordingCalls(callStats[2]);

            // Keep todayRemarks for movedToFuture calculation
            List<PartnerCollectionRemark> todayRemarks = remarksByAuthId.getOrDefault(rbmAuthId, Collections.emptyList());

            // Moved to Future = Partners in Future Plan category who have a remark today
            // These are partners who were contacted today but moved to a future date
            Set<Integer> todayRemarkedFofoIds = todayRemarks.stream()
                    .map(PartnerCollectionRemark::getFofoId)
                    .collect(Collectors.toSet());
            long movedToFuture = futurePlanPartners.stream()
                    .filter(todayRemarkedFofoIds::contains)
                    .count();
            targetModel.setMovedToFuture(movedToFuture);

            // Set out of sequence count for this RBM
            targetModel.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(rbmAuthId, 0L));

            rbmCallTargetModels.add(targetModel);
        }

        // Process L2 RBMs (escalated ticket logic with categorization)
        // For users who are both L1 and L2, merge their L1 calling target into L2 model
        for (int l2AuthId : l2AuthIds) {
            AuthUser authUser = authUserMap.get(l2AuthId);
            if (authUser == null) {
                continue;
            }

            List<Integer> l2FofoIdList = l2AuthIdToFofoIds.getOrDefault(l2AuthId, Collections.emptyList());

            // For L2, use unique fofoIds with RBM_L2_ESCALATION remark as target
            Set<Integer> l2TargetFofoIds = new HashSet<>(l2FofoIdList);

            RbmCallTargetModel l2Model = new RbmCallTargetModel();
            l2Model.setAuthId(l2AuthId);
            l2Model.setRbmName(authUser.getFullName() + " (L2)");
            l2Model.setL2Position(true);
            l2Model.setL2CallingList(l2TargetFofoIds.size());
            // Partner count: if user is also L1, use L1 partner count (MTD targeted partners)
            if (l1AuthIds.contains(l2AuthId)) {
                Set<Integer> mtdFofoIds = mtdFofoIdsByAuthId.getOrDefault(l2AuthId, Collections.emptySet());
                l2Model.setPartnerCount(mtdFofoIds.size());
            } else {
                List<Integer> l2AssignedFofoIds = rbmToFofoIdsMap.getOrDefault(l2AuthId, Collections.emptyList());
                l2Model.setPartnerCount(l2AssignedFofoIds.size());
            }

            // If user is also L1, calculate full L1 breakdown and merge into L2 model
            if (l1AuthIds.contains(l2AuthId) && storeGuyMap.containsKey(authUser.getEmailId())) {
                // Get only L1 RBM position partners (not L2 partners) from partner_position
                List<Position> positions = positionsByAuthId.getOrDefault(l2AuthId, Collections.emptyList());
                Set<Integer> l1RbmPositionIds = positions.stream()
                        .filter(p -> ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
                                && EscalationType.L1.equals(p.getEscalationType()))
                        .map(Position::getId)
                        .collect(Collectors.toSet());
                // Fetch partner_position for only L1 RBM positions of this user
                List<Integer> fofoIdList = partnerPositionRepository
                        .selectByPositionIds(new ArrayList<>(l1RbmPositionIds)).stream()
                        .map(pp -> pp.getFofoId())
                        .distinct()
                        .collect(Collectors.toList());
                boolean isRBMAndL1 = !l1RbmPositionIds.isEmpty();
                List<Integer> l1FofoIds = new ArrayList<>(fofoIdList);
                if (isRBMAndL1) {
                    Map<Integer, PartnerCollectionRemark> partnerCollectionRemarks = new HashMap<>();
                    for (Integer fofoId : fofoIdList) {
                        if (allPartnerCollectionRemarks.containsKey(fofoId)) {
                            partnerCollectionRemarks.put(fofoId, allPartnerCollectionRemarks.get(fofoId));
                        }
                    }
                    l1FofoIds = partnerCollectionRemarks.entrySet().stream()
                            .filter(entry -> {
                                PartnerCollectionRemark pcrMap = entry.getValue();
                                return !(CollectionRemark.RBM_L2_ESCALATION.equals(pcrMap.getRemark())
                                        || CollectionRemark.SALES_ESCALATION.equals(pcrMap.getRemark()));
                            })
                            .map(Map.Entry::getKey)
                            .collect(Collectors.toList());
                }
                Map<Integer, FofoStore> finalFofoStoresMap2 = fofoStoresMap;
                List<Integer> validL1FofoIds = l1FofoIds.stream()
                        .filter(fofoId -> {
                            FofoStore store = finalFofoStoresMap2.get(fofoId);
                            if (store == null || store.isInternal()) return false;
                            return ActivationType.ACTIVE.equals(store.getActivationType())
                                    || ActivationType.REVIVAL.equals(store.getActivationType());
                        })
                        .collect(Collectors.toList());

                // Categorize L1 partners — same logic as L1 processing
                Set<Integer> planTodayPartners = new HashSet<>();
                Set<Integer> carryForwardPartners = new HashSet<>();
                Set<Integer> untouchedPartners = new HashSet<>();
                Set<Integer> zeroBillingPartners = new HashSet<>();
                Set<Integer> futurePlanPartners = new HashSet<>();
                Set<Integer> normalPartners = new HashSet<>();
                Set<Integer> revivalPartners = new HashSet<>();

                for (Integer fofoId : validL1FofoIds) {
                    int rank = allCollectionRankMap.getOrDefault(fofoId, 5);
                    boolean hasZeroBilling = !allMtdBilledFofoIds.contains(fofoId);
                    FofoStore store = finalFofoStoresMap2.get(fofoId);
                    if (store != null && ActivationType.REVIVAL.equals(store.getActivationType())) {
                        revivalPartners.add(fofoId);
                    }
                    if (rank == 1) {
                        planTodayPartners.add(fofoId);
                    } else if (rank == 2) {
                        carryForwardPartners.add(fofoId);
                    } else if (hasZeroBilling) {
                        zeroBillingPartners.add(fofoId);
                    } else if (rank == 3) {
                        untouchedPartners.add(fofoId);
                    } else if (rank == 4) {
                        futurePlanPartners.add(fofoId);
                    } else {
                        normalPartners.add(fofoId);
                    }
                }

                // Set L1 breakdown fields on L2 model
                l2Model.setPlanToday(planTodayPartners.size());
                l2Model.setCarryForward(carryForwardPartners.size());
                l2Model.setZeroBilling(zeroBillingPartners.size());
                l2Model.setUntouched(untouchedPartners.size());
                l2Model.setFuturePlan(futurePlanPartners.size());
                l2Model.setNormal(normalPartners.size());
                l2Model.setRevival(revivalPartners.size());

                long l1OwnTarget = planTodayPartners.size() + carryForwardPartners.size()
                        + zeroBillingPartners.size() + untouchedPartners.size();

                // Today Target = own L1 target + L2 escalation
                l2Model.setTodayTargetOfCall(l2TargetFofoIds.size() + l1OwnTarget);

                // Moved to Future from L1 partners
                List<PartnerCollectionRemark> todayRemarks = remarksByAuthId.getOrDefault(l2AuthId, Collections.emptyList());
                Set<Integer> todayRemarkedFofoIds = todayRemarks.stream()
                        .map(PartnerCollectionRemark::getFofoId)
                        .collect(Collectors.toSet());
                long movedToFuture = futurePlanPartners.stream()
                        .filter(todayRemarkedFofoIds::contains)
                        .count();
                l2Model.setMovedToFuture(movedToFuture);
            } else {
                // Pure L2 (not also L1) — target is only L2 escalation
                l2Model.setTodayTargetOfCall(l2TargetFofoIds.size());
            }

            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
            long[] l2CallStats = getCallStatsFromLogs(callLogsByAuthId.get((long) l2AuthId), mobileToFofoIdMap);
            l2Model.setValueTargetAchieved(l2CallStats[0]);
            l2Model.setTotalRecordingCalls(l2CallStats[1]);
            l2Model.setUniqueRecordingCalls(l2CallStats[2]);

            l2Model.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(l2AuthId, 0L));
            rbmCallTargetModels.add(l2Model);
        }

        // Process L3 RBMs (escalated ticket logic with categorization)
        for (int l3AuthId : l3AuthIds) {
            AuthUser authUser = authUserMap.get(l3AuthId);
            if (authUser == null) {
                continue;
            }

            List<Integer> l3FofoIdList = l3AuthIdToFofoIds.getOrDefault(l3AuthId, Collections.emptyList());

            // For L3, use unique fofoIds with RBM_L3_ESCALATION remark as target
            Set<Integer> l3TargetFofoIds = new HashSet<>(l3FofoIdList);

            RbmCallTargetModel l3Model = new RbmCallTargetModel();
            l3Model.setAuthId(l3AuthId);
            l3Model.setRbmName(authUser.getFullName() + " (L3)");
            l3Model.setL3Position(true);
            l3Model.setL3CallingList(l3TargetFofoIds.size());
            // Partner count = total assigned partners (excluding internal)
            Map<Integer, FofoStore> finalFofoStoresMapL3 = fofoStoresMap;
            List<Integer> l3AssignedFofoIds = rbmToFofoIdsMap.getOrDefault(l3AuthId, Collections.emptyList());
            long l3ExternalPartnerCount = l3AssignedFofoIds.stream()
                    .filter(fofoId -> {
                        FofoStore store = finalFofoStoresMapL3.get(fofoId);
                        return store != null && !store.isInternal();
                    })
                    .count();
            l3Model.setPartnerCount(l3ExternalPartnerCount);

            // L3 Target = partners with RBM_L3_ESCALATION as latest remark
            l3Model.setTodayTargetOfCall(l3TargetFofoIds.size());

            // Value Achieved = All distinct partners called today (from pre-fetched call logs)
            long[] l3CallStats = getCallStatsFromLogs(callLogsByAuthId.get((long) l3AuthId), mobileToFofoIdMap);
            l3Model.setValueTargetAchieved(l3CallStats[0]);
            l3Model.setTotalRecordingCalls(l3CallStats[1]);
            l3Model.setUniqueRecordingCalls(l3CallStats[2]);

            l3Model.setOutOfSequenceCount(outOfSequenceCountByAuthId.getOrDefault(l3AuthId, 0L));
            rbmCallTargetModels.add(l3Model);
        }

        // Group models by escalation level
        Map<Integer, RbmCallTargetModel> l3ModelsByAuthId = new HashMap<>();
        Map<Integer, RbmCallTargetModel> l2ModelsByAuthId = new HashMap<>();
        Map<Integer, RbmCallTargetModel> l1ModelsByAuthId = new HashMap<>();
        for (RbmCallTargetModel m : rbmCallTargetModels) {
            if (m.isL3Position()) {
                l3ModelsByAuthId.put(m.getAuthId(), m);
            } else if (m.isL2Position()) {
                l2ModelsByAuthId.put(m.getAuthId(), m);
            } else {
                l1ModelsByAuthId.put(m.getAuthId(), m);
            }
        }

        // Build partner-based hierarchy using partner_position table
        // Map positionId -> Position for quick lookup
        Map<Integer, Position> positionByIdMap = allRbmPositions.stream()
                .collect(Collectors.toMap(Position::getId, p -> p, (a, b) -> a));

        // Get all RBM position IDs and fetch their partner assignments
        List<Integer> allRbmPositionIds = allRbmPositions.stream()
                .map(Position::getId).collect(Collectors.toList());
        List<com.spice.profitmandi.dao.entity.cs.PartnerPosition> allPartnerPositions =
                partnerPositionRepository.selectByPositionIds(allRbmPositionIds);

        // Build partnerId -> map of escalationType -> Set<authUserIds>
        // This tells us for each partner, who is their L1, L2, L3
        Map<Integer, Map<EscalationType, Set<Integer>>> partnerToAuthByLevel = new HashMap<>();
        for (com.spice.profitmandi.dao.entity.cs.PartnerPosition pp : allPartnerPositions) {
            Position pos = positionByIdMap.get(pp.getPositionId());
            if (pos != null && pos.getEscalationType() != null) {
                partnerToAuthByLevel
                        .computeIfAbsent(pp.getFofoId(), k -> new HashMap<>())
                        .computeIfAbsent(pos.getEscalationType(), k -> new HashSet<>())
                        .add(pos.getAuthUserId());
            }
        }

        // Build L2 -> L1 team map based on shared partners
        // If an L1 and L2 share partners (L1 at L1 level, L2 at L2 level), that L1 belongs under that L2
        Map<Integer, List<RbmCallTargetModel>> l2TeamMap = new LinkedHashMap<>();
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
            l2TeamMap.put(l2Model.getAuthId(), new ArrayList<>());
        }

        // Build L3 -> L2 team map based on shared partners
        Map<Integer, List<RbmCallTargetModel>> l3TeamMap = new LinkedHashMap<>();
        for (RbmCallTargetModel l3Model : l3ModelsByAuthId.values()) {
            l3TeamMap.put(l3Model.getAuthId(), new ArrayList<>());
        }

        // For each L1, find which L2 shares the most partners -> that's their L2
        Set<Integer> addedL1AuthIds = new HashSet<>();
        for (RbmCallTargetModel l1Model : l1ModelsByAuthId.values()) {
            Map<Integer, Integer> l2SharedCount = new HashMap<>(); // l2AuthId -> shared partner count
            for (Map.Entry<Integer, Map<EscalationType, Set<Integer>>> entry : partnerToAuthByLevel.entrySet()) {
                Map<EscalationType, Set<Integer>> levelMap = entry.getValue();
                Set<Integer> l1IdsForPartner = levelMap.getOrDefault(EscalationType.L1, Collections.emptySet());
                Set<Integer> l2AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L2, Collections.emptySet());
                if (l1IdsForPartner.contains(l1Model.getAuthId())) {
                    for (int l2Id : l2AuthIdsForPartner) {
                        if (l2ModelsByAuthId.containsKey(l2Id) && l2Id != l1Model.getAuthId()) {
                            l2SharedCount.merge(l2Id, 1, Integer::sum);
                        }
                    }
                }
            }
            // Assign L1 to the L2 with most shared partners
            if (!l2SharedCount.isEmpty()) {
                int bestL2 = l2SharedCount.entrySet().stream()
                        .max(Map.Entry.comparingByValue()).get().getKey();
                l2TeamMap.get(bestL2).add(l1Model);
                addedL1AuthIds.add(l1Model.getAuthId());
            }
        }

        // For each L2, find which L3 shares the most partners -> that's their L3
        Set<Integer> addedL2AuthIds = new HashSet<>();
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
            Map<Integer, Integer> l3SharedCount = new HashMap<>();
            for (Map.Entry<Integer, Map<EscalationType, Set<Integer>>> entry : partnerToAuthByLevel.entrySet()) {
                Map<EscalationType, Set<Integer>> levelMap = entry.getValue();
                Set<Integer> l2AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L2, Collections.emptySet());
                Set<Integer> l3AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L3, Collections.emptySet());
                if (l2AuthIdsForPartner.contains(l2Model.getAuthId())) {
                    for (int l3Id : l3AuthIdsForPartner) {
                        if (l3ModelsByAuthId.containsKey(l3Id)) {
                            l3SharedCount.merge(l3Id, 1, Integer::sum);
                        }
                    }
                }
            }
            if (!l3SharedCount.isEmpty()) {
                int bestL3 = l3SharedCount.entrySet().stream()
                        .max(Map.Entry.comparingByValue()).get().getKey();
                l3TeamMap.get(bestL3).add(l2Model);
                addedL2AuthIds.add(l2Model.getAuthId());
            }
        }

        // For L1s not mapped to any L2, check if they map directly to an L3 via shared partners
        Map<Integer, List<RbmCallTargetModel>> l3DirectL1Map = new LinkedHashMap<>();
        for (RbmCallTargetModel l3Model : l3ModelsByAuthId.values()) {
            l3DirectL1Map.put(l3Model.getAuthId(), new ArrayList<>());
        }
        for (RbmCallTargetModel l1Model : l1ModelsByAuthId.values()) {
            if (addedL1AuthIds.contains(l1Model.getAuthId())) continue;
            Map<Integer, Integer> l3SharedCount = new HashMap<>();
            for (Map.Entry<Integer, Map<EscalationType, Set<Integer>>> entry : partnerToAuthByLevel.entrySet()) {
                Map<EscalationType, Set<Integer>> levelMap = entry.getValue();
                Set<Integer> l1IdsForPartner = levelMap.getOrDefault(EscalationType.L1, Collections.emptySet());
                Set<Integer> l3AuthIdsForPartner = levelMap.getOrDefault(EscalationType.L3, Collections.emptySet());
                if (l1IdsForPartner.contains(l1Model.getAuthId())) {
                    for (int l3Id : l3AuthIdsForPartner) {
                        if (l3ModelsByAuthId.containsKey(l3Id)) {
                            l3SharedCount.merge(l3Id, 1, Integer::sum);
                        }
                    }
                }
            }
            if (!l3SharedCount.isEmpty()) {
                int bestL3 = l3SharedCount.entrySet().stream()
                        .max(Map.Entry.comparingByValue()).get().getKey();
                l3DirectL1Map.get(bestL3).add(l1Model);
                addedL1AuthIds.add(l1Model.getAuthId());
            }
        }

        // Build sorted result: L3 -> L2 -> L1 (with direct L1s under L3 if no L2)
        List<RbmCallTargetModel> sortedModels = new ArrayList<>();

        List<RbmCallTargetModel> l3Sorted = new ArrayList<>(l3ModelsByAuthId.values());
        l3Sorted.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));

        for (RbmCallTargetModel l3Model : l3Sorted) {
            sortedModels.add(l3Model);
            List<RbmCallTargetModel> l2Team = l3TeamMap.getOrDefault(l3Model.getAuthId(), Collections.emptyList());
            l2Team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
            for (RbmCallTargetModel l2Model : l2Team) {
                sortedModels.add(l2Model);
                List<RbmCallTargetModel> l1Team = l2TeamMap.getOrDefault(l2Model.getAuthId(), Collections.emptyList());
                l1Team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
                sortedModels.addAll(l1Team);
            }
            // Add L1s that report directly to this L3 (no L2 in between)
            List<RbmCallTargetModel> directL1Team = l3DirectL1Map.getOrDefault(l3Model.getAuthId(), Collections.emptyList());
            directL1Team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
            sortedModels.addAll(directL1Team);
        }

        // Add L2s not mapped to any L3
        List<RbmCallTargetModel> unmappedL2 = new ArrayList<>();
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
            if (!addedL2AuthIds.contains(l2Model.getAuthId())) {
                unmappedL2.add(l2Model);
            }
        }
        unmappedL2.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
        for (RbmCallTargetModel l2Model : unmappedL2) {
            sortedModels.add(l2Model);
            List<RbmCallTargetModel> team = l2TeamMap.getOrDefault(l2Model.getAuthId(), Collections.emptyList());
            team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
            sortedModels.addAll(team);
        }

        // Add any L1s not mapped to any L2 or L3
        List<RbmCallTargetModel> unmappedL1 = new ArrayList<>();
        for (RbmCallTargetModel m : l1ModelsByAuthId.values()) {
            if (!addedL1AuthIds.contains(m.getAuthId())) {
                unmappedL1.add(m);
            }
        }
        unmappedL1.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
        sortedModels.addAll(unmappedL1);

        LOGGER.info("RBM Call Target - TOTAL TIME: {}ms, RBM count: {}", System.currentTimeMillis() - methodStart, sortedModels.size());
        return sortedModels;
    }

    @Override
    public List<OutOfSequenceDetailModel> getOutOfSequenceDetails(int authId) {

        LocalDate today = LocalDate.now();
        LocalDateTime start = today.atStartOfDay();
        LocalDateTime end = today.plusDays(1).atStartOfDay();

        List<RbmCallSequenceLog> logs =
                rbmCallSequenceLogRepository.selectByAuthIdAndDateRange(authId, start, end);

        Map<Integer, RbmCallSequenceLog> uniqueOosLogsByFofoId = new LinkedHashMap<>();

        for (RbmCallSequenceLog log : logs) {
            if (log.isOutOfSequence()) {
                // Keep only the first occurrence per fofoId (latest entry since ordered by id DESC)
                uniqueOosLogsByFofoId.putIfAbsent(log.getFofoId(), log);
            }
        }

        if (uniqueOosLogsByFofoId.isEmpty()) {
            return Collections.emptyList();
        }

        Set<Integer> fofoIds = uniqueOosLogsByFofoId.keySet();
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
        try {
            retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
        } catch (ProfitMandiBusinessException e) {
            LOGGER.error("Error fetching fofo stores", e);
        }

        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");
        List<OutOfSequenceDetailModel> result = new ArrayList<>();

        for (RbmCallSequenceLog log : uniqueOosLogsByFofoId.values()) {
            CustomRetailer retailer = retailerMap.get(log.getFofoId());
            String partyName = retailer != null
                    ? retailer.getBusinessName()
                    : "Unknown (" + log.getFofoId() + ")";
            String code = retailer != null
                    ? retailer.getCode()
                    : "-";

            String time = log.getCreateTimestamp() != null
                    ? log.getCreateTimestamp().format(timeFormatter)
                    : "-";

            result.add(new OutOfSequenceDetailModel(partyName, code, time));
        }

        return result;
    }

    @Override
    public List<CalledPartnerDetailModel> getCalledPartnerDetails(int authId) throws ProfitMandiBusinessException {
        return getCalledPartnerDetails(authId, LocalDate.now());
    }

    @Override
    public List<CalledPartnerDetailModel> getCalledPartnerDetails(int authId, LocalDate date) throws ProfitMandiBusinessException {
        // Get all call logs for this auth user on this date
        LOGGER.info("getCalledPartnerDetails: authId={}, date={}", authId, date);
        List<AgentCallLog> callLogs = agentCallLogRepository.findByAuthIdAndDate(authId, date);
        LOGGER.info("Found {} call logs for authId={} on date={}", callLogs.size(), authId, date);

        if (callLogs.isEmpty()) {
            return Collections.emptyList();
        }

        // Build a map of normalized customer number -> fofoId
        Map<String, Integer> customerToFofoIdMap = new HashMap<>();
        Set<String> normalizedNumbers = new HashSet<>();

        for (AgentCallLog callLog : callLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber != null) {
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
                normalizedNumbers.add(normalized);
            }
        }

        // For each normalized number, find fofoId from retailer_contact first, then address
        for (String mobile : normalizedNumbers) {
            Integer fofoId = findFofoIdByMobile(mobile);
            if (fofoId != null) {
                customerToFofoIdMap.put(mobile, fofoId);
            }
        }

        // Get unique fofoIds for retailer lookup
        Set<Integer> fofoIds = new HashSet<>(customerToFofoIdMap.values());
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
        if (!fofoIds.isEmpty()) {
            try {
                retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
            } catch (ProfitMandiBusinessException e) {
                LOGGER.error("Error fetching fofo stores", e);
            }
        }

        // Get today's remarks for these fofoIds
        Map<Integer, List<PartnerCollectionRemark>> fofoRemarkMap = new HashMap<>();
        if (!fofoIds.isEmpty()) {
            List<PartnerCollectionRemark> todayRemarks = partnerCollectionRemarkRepository
                    .selectAllByFofoIdsOnDate(new ArrayList<>(fofoIds), date);
            for (PartnerCollectionRemark remark : todayRemarks) {
                fofoRemarkMap.computeIfAbsent(remark.getFofoId(), k -> new ArrayList<>()).add(remark);
            }
        }

        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy hh:mm a");
        List<CalledPartnerDetailModel> result = new ArrayList<>();

        for (com.spice.profitmandi.dao.entity.cs.AgentCallLog callLog : callLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber == null) {
                continue;
            }

            String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
            Integer fofoId = customerToFofoIdMap.get(normalized);

            String partyName = "Unknown";
            String code = "-";

            if (fofoId != null) {
                CustomRetailer retailer = retailerMap.get(fofoId);
                if (retailer != null) {
                    partyName = retailer.getBusinessName();
                    code = retailer.getCode();
                } else {
                    partyName = "Unknown (" + fofoId + ")";
                }
            } else {
                partyName = "Unknown (" + normalized + ")";
            }

            // Get remark if available
            String remarkValue = "-";
            String messageValue = "-";
            String remarkTime = "-";

            if (fofoId != null && fofoRemarkMap.containsKey(fofoId)) {
                List<PartnerCollectionRemark> remarks = fofoRemarkMap.get(fofoId);
                if (!remarks.isEmpty()) {
                    PartnerCollectionRemark remark = remarks.get(0);
                    remarkValue = remark.getRemark() != null ? remark.getRemark().getValue() : "-";
                    messageValue = remark.getMessage() != null ? remark.getMessage() : "-";
                    remarkTime = remark.getCreateTimestamp() != null ? remark.getCreateTimestamp().format(timeFormatter) : "-";
                }
            }

            // Build call log data
            String recordingUrl = callLog.getRecordingUrl();
            String callStatus = callLog.getCallStatus();
            String callDuration = callLog.getCallDuration();
            String callDateTime = null;
            if (callLog.getCallDate() != null && callLog.getCallTime() != null) {
                LocalDateTime callDateTimeObj = LocalDateTime.of(callLog.getCallDate(), callLog.getCallTime());
                callDateTime = callDateTimeObj.format(dateTimeFormatter);
            }

            result.add(new CalledPartnerDetailModel(partyName, code, remarkValue, messageValue, remarkTime,
                    recordingUrl, callStatus, callDuration, callDateTime));
        }

        return result;
    }

    private Integer findFofoIdByMobile(String mobile) {
        // First check retailer_contact
        List<RetailerContact> contacts = retailerContactRepository.selectByMobile(mobile);
        if (contacts != null && !contacts.isEmpty()) {
            return contacts.get(0).getFofoId();
        }

        // Fallback to user.address
        List<Address> addresses = addressRepository.selectAllByPhoneNumber(mobile);
        if (addresses != null && !addresses.isEmpty()) {
            return addresses.get(0).getRetaierId();
        }

        return null;
    }

    private List<CalledPartnerDetailModel> buildCalledPartnerResult(List<PartnerCollectionRemark> allRemarks) {
        if (allRemarks.isEmpty()) {
            return Collections.emptyList();
        }

        // Get unique fofoIds for retailer lookup
        Set<Integer> fofoIds = allRemarks.stream()
                .map(PartnerCollectionRemark::getFofoId)
                .collect(Collectors.toSet());
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
        try {
            retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
        } catch (ProfitMandiBusinessException e) {
            LOGGER.error("Error fetching fofo stores", e);
        }

        // Fetch call logs for remarks that have agentCallLogId
        Map<Long, com.spice.profitmandi.dao.entity.cs.AgentCallLog> callLogMap = new HashMap<>();
        try {
            List<Long> callLogIds = allRemarks.stream()
                    .filter(r -> r.getAgentCallLogId() > 0)
                    .map(PartnerCollectionRemark::getAgentCallLogId)
                    .collect(Collectors.toList());

            if (!callLogIds.isEmpty()) {
                List<com.spice.profitmandi.dao.entity.cs.AgentCallLog> callLogs = agentCallLogRepository.findByIds(callLogIds);
                if (callLogs != null) {
                    callLogMap = callLogs.stream()
                            .collect(Collectors.toMap(com.spice.profitmandi.dao.entity.cs.AgentCallLog::getId, c -> c, (a, b) -> a));
                }
            }
        } catch (Exception e) {
            LOGGER.error("Error fetching call logs by ids", e);
        }

        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy hh:mm a");
        List<CalledPartnerDetailModel> result = new ArrayList<>();

        for (PartnerCollectionRemark remark : allRemarks) {
            CustomRetailer retailer = retailerMap.get(remark.getFofoId());
            String partyName = retailer != null
                    ? retailer.getBusinessName()
                    : "Unknown (" + remark.getFofoId() + ")";
            String code = retailer != null
                    ? retailer.getCode()
                    : "-";

            String remarkValue = remark.getRemark() != null
                    ? remark.getRemark().getValue()
                    : "-";

            String messageValue = remark.getMessage() != null
                    ? remark.getMessage()
                    : "-";

            String time = remark.getCreateTimestamp() != null
                    ? remark.getCreateTimestamp().format(timeFormatter)
                    : "-";

            // Get call log data if available
            String recordingUrl = null;
            String callStatus = null;
            String callDuration = null;
            String callDateTime = null;

            try {
                if (remark.getAgentCallLogId() > 0 && callLogMap.containsKey(remark.getAgentCallLogId())) {
                    com.spice.profitmandi.dao.entity.cs.AgentCallLog callLog = callLogMap.get(remark.getAgentCallLogId());
                    recordingUrl = callLog.getRecordingUrl();
                    callStatus = callLog.getCallStatus();
                    callDuration = callLog.getCallDuration();
                    if (callLog.getCallDate() != null && callLog.getCallTime() != null) {
                        LocalDateTime callDateTimeObj = LocalDateTime.of(callLog.getCallDate(), callLog.getCallTime());
                        callDateTime = callDateTimeObj.format(dateTimeFormatter);
                    }
                }
            } catch (Exception e) {
                LOGGER.error("Error processing call log for remark id: {}", remark.getId(), e);
            }

            result.add(new CalledPartnerDetailModel(partyName, code, remarkValue, messageValue, time,
                    recordingUrl, callStatus, callDuration, callDateTime));
        }

        return result;
    }

    @Override
    public List<List<String>> getRbmCallTargetRawDataByAuthId(int authId) throws Exception {
        List<List<String>> rows = new ArrayList<>();

        // Get auth user
        List<AuthUser> authUsers = authRepository.selectByIds(Collections.singletonList(authId));
        if (authUsers.isEmpty()) {
            return rows;
        }
        AuthUser authUser = authUsers.get(0);

        // Get positions to determine if L2
        List<Position> positions = positionRepository.selectPositionByAuthIds(Collections.singletonList(authId));
        boolean isL2 = positions.stream()
                .anyMatch(p -> ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
                        && EscalationType.L2.equals(p.getEscalationType()));

        LocalDateTime startDate = LocalDate.now().atStartOfDay();
        LocalDate firstOfMonth = LocalDate.now().withDayOfMonth(1);
        LocalDate endOfMonth = LocalDate.now().withDayOfMonth(LocalDate.now().lengthOfMonth()).plusDays(1);

        // Get fofo IDs from mtdBillingData (same source as Partner Count in getRbmCallTargetModels)
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);

        List<Integer> fofoIdList;
        if (isL2) {
            // L2: get fofo IDs from escalated tickets (same as getRbmCallTargetModels)
            List<Ticket> escalatedTickets = ticketRepository.selectOpenEscalatedTicketsByAuthIds(Collections.singletonList(authId));
            fofoIdList = escalatedTickets.stream()
                    .filter(t -> t.getL2AuthUser() == authId
                            || t.getL3AuthUser() == authId
                            || t.getL4AuthUser() == authId
                            || t.getL5AuthUser() == authId)
                    .map(Ticket::getFofoId)
                    .distinct()
                    .collect(Collectors.toList());
        } else {
            // L1: get fofo IDs from mtdBillingData with isTargetedPartner (same as Partner Count)
            fofoIdList = mtdBillingData.stream()
                    .filter(RbmWeeklyBillingModel::isTargetedPartner)
                    .filter(m -> m.getAuthId() == authId)
                    .map(RbmWeeklyBillingModel::getFofoId)
                    .distinct()
                    .collect(Collectors.toList());
        }

        if (fofoIdList.isEmpty()) {
            return rows;
        }

        // MTD billed fofoIds for zero billing check
        Set<Integer> mtdBilledFofoIds = mtdBillingData.stream()
                .filter(RbmWeeklyBillingModel::isMtdBilled)
                .map(RbmWeeklyBillingModel::getFofoId)
                .collect(Collectors.toSet());

        // Collection rank map for status calculation
        Map<Integer, Integer> collectionRankMap = new HashMap<>();
        try {
            collectionRankMap = partnerCollectionService.getCollectionRankMap(fofoIdList, startDate);
        } catch (ProfitMandiBusinessException e) {
            LOGGER.error("Error fetching collection rank map", e);
        }

        // Resolve partner names/codes
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
        if (!fofoIdList.isEmpty()) {
            try {
                retailerMap = retailerService.getFofoRetailers(fofoIdList);
            } catch (ProfitMandiBusinessException e) {
                LOGGER.error("Error fetching fofo retailers for raw data", e);
            }
        }

        String rbmName = authUser.getFullName() + (isL2 ? " (L2)" : "");

        // Build rows for ALL partners (same count as Partner Count)
        for (Integer fofoId : fofoIdList) {
            // Default to rank 5 (Normal) for partners without collection plan
            int rank = collectionRankMap.getOrDefault(fofoId, 5);
            boolean hasZeroBilling = !mtdBilledFofoIds.contains(fofoId);

            // Status assignment with same priority as getRbmCallTargetModels
            String status;
            if (rank == 1) {
                status = "Plan Today";
            } else if (rank == 2) {
                status = "Carry Forward";
            } else if (hasZeroBilling) {
                status = "Zero Billing";
            } else if (rank == 3) {
                status = "Untouched";
            } else if (rank == 4) {
                status = "Future Plan";
            } else {
                status = "Normal";
            }

            CustomRetailer retailer = retailerMap.get(fofoId);
            String partnerName = retailer != null ? retailer.getBusinessName() : "Unknown (" + fofoId + ")";
            String partnerCode = retailer != null ? retailer.getCode() : "-";

            rows.add(Arrays.asList(partnerName, partnerCode, status, rbmName));
        }

        return rows;
    }

    /**
     * Get count of distinct partners called today based on call logs.
     * Maps customerNumber from call log to fofoId using retailer_contact and address.
     * If same fofoId is called multiple times, counts only once.
     * Numbers without fofoId mapping are also counted (by distinct customer number).
     *
     * @param authId the RBM auth ID
     * @return count of distinct partners/numbers called today
     */
    public long getCalledCountFromCallLogs(long authId) {
        return getCalledCountFromCallLogs(authId, LocalDate.now());
    }

    public long getCalledCountFromCallLogs(long authId, LocalDate date) {
        return getCallStats(authId, date)[0];
    }

    /**
     * Batch-builds a mobile (normalized, +91 stripped) -> fofoId mapping for all the given mobiles
     * in just two queries (retailer_contact, then address fallback for unmapped numbers).
     * Replaces the previous per-call-log findFofoIdByMobile() N+1 lookups.
     */
    private Map<String, Integer> buildMobileToFofoIdMap(Set<String> normalizedMobiles) {
        Map<String, Integer> result = new HashMap<>();
        if (normalizedMobiles == null || normalizedMobiles.isEmpty()) {
            return result;
        }
        List<String> mobilesList = new ArrayList<>(normalizedMobiles);

        // First pass: retailer_contact
        List<RetailerContact> contacts = retailerContactRepository.selectByMobiles(mobilesList);
        for (RetailerContact rc : contacts) {
            // Keep the first fofoId we see per mobile (matches old single-mobile behavior of get(0))
            result.putIfAbsent(rc.getMobile(), rc.getFofoId());
        }

        // Second pass: address fallback for mobiles not yet mapped
        List<String> unmapped = mobilesList.stream()
                .filter(m -> !result.containsKey(m))
                .collect(Collectors.toList());
        if (!unmapped.isEmpty()) {
            List<Address> addresses = addressRepository.selectAllByPhoneNumbers(unmapped);
            for (Address addr : addresses) {
                result.putIfAbsent(addr.getPhoneNumber(), addr.getRetaierId());
            }
        }
        return result;
    }

    /**
     * In-memory variant of getCallStats() that uses pre-fetched call logs and mobile→fofoId mapping.
     * Used by the batch-fetched RBM Call Target loop to avoid N+1 query patterns.
     */
    private long[] getCallStatsFromLogs(List<AgentCallLog> callLogs, Map<String, Integer> mobileToFofoIdMap) {
        if (callLogs == null || callLogs.isEmpty()) {
            return new long[]{0, 0, 0};
        }

        Set<Integer> calledFofoIds = new HashSet<>();
        Set<String> calledNumbersWithoutFofoId = new HashSet<>();
        long totalRecordingCalls = 0;
        Set<String> uniqueRecordingNumbers = new HashSet<>();

        for (AgentCallLog callLog : callLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber != null) {
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
                Integer fofoId = mobileToFofoIdMap.get(normalized);
                if (fofoId != null) {
                    calledFofoIds.add(fofoId);
                } else {
                    calledNumbersWithoutFofoId.add(normalized);
                }

                if (callLog.getRecordingUrl() != null && !callLog.getRecordingUrl().isEmpty()
                        && !"None".equalsIgnoreCase(callLog.getRecordingUrl())) {
                    totalRecordingCalls++;
                    uniqueRecordingNumbers.add(normalized);
                }
            }
        }

        long calledCount = calledFofoIds.size() + calledNumbersWithoutFofoId.size();
        return new long[]{calledCount, totalRecordingCalls, uniqueRecordingNumbers.size()};
    }

    /**
     * Returns call stats: [0] = called count, [1] = total recording calls, [2] = unique recording calls
     */
    public long[] getCallStats(long authId, LocalDate date) {
        List<AgentCallLog> callLogs = agentCallLogRepository.findByAuthIdAndDate(authId, date);

        if (callLogs == null || callLogs.isEmpty()) {
            return new long[]{0, 0, 0};
        }

        Set<Integer> calledFofoIds = new HashSet<>();
        Set<String> calledNumbersWithoutFofoId = new HashSet<>();
        long totalRecordingCalls = 0;
        Set<String> uniqueRecordingNumbers = new HashSet<>();

        for (AgentCallLog callLog : callLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber != null) {
                // Normalize the phone number (remove +91 prefix if present)
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;

                // Find fofoId from retailer_contact or address
                Integer fofoId = findFofoIdByMobile(normalized);
                if (fofoId != null) {
                    calledFofoIds.add(fofoId);
                } else {
                    // Number not found in retailer_contact or address, count by distinct number
                    calledNumbersWithoutFofoId.add(normalized);
                }

                // Count calls with recordings
                if (callLog.getRecordingUrl() != null && !callLog.getRecordingUrl().isEmpty()
                        && !"None".equalsIgnoreCase(callLog.getRecordingUrl())) {
                    totalRecordingCalls++;
                    uniqueRecordingNumbers.add(normalized);
                }
            }
        }

        // Total called = distinct fofoIds + distinct numbers without fofoId mapping
        long calledCount = calledFofoIds.size() + calledNumbersWithoutFofoId.size();
        return new long[]{calledCount, totalRecordingCalls, uniqueRecordingNumbers.size()};
    }

    @Override
    public List<List<String>> getAllCallDataByDate(LocalDate date) throws Exception {
        List<List<String>> rows = new ArrayList<>();

        // Add header row
        rows.add(Arrays.asList("RBM Name", "Partner Name", "Code", "Remark", "Call Status", "Call Duration", "Call Date Time", "Recording URL"));

        // Get all call logs for the date
        List<AgentCallLog> allCallLogs = agentCallLogRepository.findAllByDate(date);
        LOGGER.info("getAllCallDataByDate: Found {} call logs for date {}", allCallLogs.size(), date);

        if (allCallLogs.isEmpty()) {
            return rows;
        }

        // Get unique authIds from call logs
        Set<Long> authIds = allCallLogs.stream()
                .map(AgentCallLog::getAuthId)
                .collect(Collectors.toSet());

        // Get auth users for RBM names
        List<Integer> authIdInts = authIds.stream().map(Long::intValue).collect(Collectors.toList());
        Map<Integer, AuthUser> authUserMap = authRepository.selectByIds(authIdInts).stream()
                .collect(Collectors.toMap(AuthUser::getId, au -> au, (a, b) -> a));

        // Build a map of normalized customer number -> fofoId
        Map<String, Integer> customerToFofoIdMap = new HashMap<>();
        Set<String> normalizedNumbers = new HashSet<>();

        for (AgentCallLog callLog : allCallLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber != null) {
                String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
                normalizedNumbers.add(normalized);
            }
        }

        // For each normalized number, find fofoId
        for (String mobile : normalizedNumbers) {
            Integer fofoId = findFofoIdByMobile(mobile);
            if (fofoId != null) {
                customerToFofoIdMap.put(mobile, fofoId);
            }
        }

        // Get unique fofoIds for retailer lookup
        Set<Integer> fofoIds = new HashSet<>(customerToFofoIdMap.values());
        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();
        if (!fofoIds.isEmpty()) {
            try {
                retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));
            } catch (ProfitMandiBusinessException e) {
                LOGGER.error("Error fetching fofo stores", e);
            }
        }

        // Get remarks for these fofoIds on this date
        Map<Integer, List<PartnerCollectionRemark>> fofoRemarkMap = new HashMap<>();
        if (!fofoIds.isEmpty()) {
            List<PartnerCollectionRemark> dateRemarks = partnerCollectionRemarkRepository
                    .selectAllByFofoIdsOnDate(new ArrayList<>(fofoIds), date);
            for (PartnerCollectionRemark remark : dateRemarks) {
                fofoRemarkMap.computeIfAbsent(remark.getFofoId(), k -> new ArrayList<>()).add(remark);
            }
        }

        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy hh:mm a");

        // Build rows
        for (AgentCallLog callLog : allCallLogs) {
            String customerNumber = callLog.getCustomerNumber();
            if (customerNumber == null) {
                continue;
            }

            // Get RBM Name
            AuthUser authUser = authUserMap.get((int) callLog.getAuthId());
            String rbmName = authUser != null ? authUser.getFullName() : "Unknown";

            String normalized = customerNumber.startsWith("+91") ? customerNumber.substring(3) : customerNumber;
            Integer fofoId = customerToFofoIdMap.get(normalized);

            String partyName = "Unknown";
            String code = "-";

            if (fofoId != null) {
                CustomRetailer retailer = retailerMap.get(fofoId);
                if (retailer != null) {
                    partyName = retailer.getBusinessName();
                    code = retailer.getCode();
                } else {
                    partyName = "Unknown (" + fofoId + ")";
                }
            } else {
                partyName = "Unknown (" + normalized + ")";
            }

            // Get remark if available
            String remarkValue = "-";
            if (fofoId != null && fofoRemarkMap.containsKey(fofoId)) {
                List<PartnerCollectionRemark> remarks = fofoRemarkMap.get(fofoId);
                if (!remarks.isEmpty()) {
                    PartnerCollectionRemark remark = remarks.get(0);
                    remarkValue = remark.getRemark() != null ? remark.getRemark().getValue() : "-";
                    if (remark.getMessage() != null && !remark.getMessage().isEmpty()) {
                        remarkValue = remarkValue + " - " + remark.getMessage();
                    }
                }
            }

            // Call status from customerStatus field
            String callStatus = callLog.getCustomerStatus() != null ? callLog.getCustomerStatus() : "-";
            String callDuration = callLog.getCallDuration() != null ? callLog.getCallDuration() : "-";

            String callDateTime = "-";
            if (callLog.getCallDate() != null && callLog.getCallTime() != null) {
                LocalDateTime callDateTimeObj = LocalDateTime.of(callLog.getCallDate(), callLog.getCallTime());
                callDateTime = callDateTimeObj.format(dateTimeFormatter);
            }

            String recordingUrl = callLog.getRecordingUrl() != null ? callLog.getRecordingUrl() : "-";

            rows.add(Arrays.asList(rbmName, partyName, code, remarkValue, callStatus, callDuration, callDateTime, recordingUrl));
        }

        return rows;
    }

}