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.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.inventory.RbmAchievements;
import com.spice.profitmandi.dao.entity.inventory.RbmTargets;
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.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.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.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
    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();

    }

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

    @Override
    public List<RbmCallTargetModel> getRbmCallTargetModels() 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).contains(x.getEscalationType()))
                .collect(Collectors.toList());

        // Separate L1 and L2 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());

        // 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: {}", System.currentTimeMillis() - start, l1AuthIds.size(), l2AuthIds.size());

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

        LocalDateTime startDate = LocalDate.now().atStartOfDay();
        LocalDate firstOfMonth = LocalDate.now().withDayOfMonth(1);
        LocalDate endOfMonth = LocalDate.now().withDayOfMonth(LocalDate.now().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);
            }
        }
        // Fetch escalated tickets for L2 early so their fofoIds are included in batch fetches
        List<Ticket> escalatedTickets = Collections.emptyList();
        Map<Integer, List<Integer>> l2AuthIdToFofoIds = new HashMap<>();
        if (!l2AuthIds.isEmpty()) {
            start = System.currentTimeMillis();
            escalatedTickets = ticketRepository.selectOpenEscalatedTicketsByAuthIds(l2AuthIds);
            LOGGER.info("RBM Call Target - Escalated tickets fetch: {}ms, count: {}", System.currentTimeMillis() - start, escalatedTickets.size());

            for (int l2AuthId : l2AuthIds) {
                List<Integer> l2FofoIds = escalatedTickets.stream()
                        .filter(t -> t.getL2AuthUser() == l2AuthId
                                || t.getL3AuthUser() == l2AuthId
                                || t.getL4AuthUser() == l2AuthId
                                || t.getL5AuthUser() == l2AuthId)
                        .map(Ticket::getFofoId)
                        .distinct()
                        .collect(Collectors.toList());
                l2AuthIdToFofoIds.put(l2AuthId, l2FofoIds);
                allFofoIds.addAll(l2FofoIds);
            }
        }
        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);

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

        // Process L1 RBMs (existing logic)
        for (int rbmAuthId : l1AuthIds) {
            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 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 partners (not Low Sale, not Disputed, not Billing Pending)
                        return ActivationType.ACTIVE.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
            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<>();

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

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

            // 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 = Partners from Today Target that have been contacted today (have remark today)
            List<PartnerCollectionRemark> todayRemarks = remarksByAuthId.getOrDefault(rbmAuthId, Collections.emptyList());
            long valueAchieved = todayRemarks.stream()
                    .map(PartnerCollectionRemark::getFofoId)
                    .filter(todayTargetPartners::contains)
                    .distinct()
                    .count();
            targetModel.setValueTargetAchieved(valueAchieved);

            // 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 (int l2AuthId : l2AuthIds) {
            AuthUser authUser = authUserMap.get(l2AuthId);
            if (authUser == null) {
                continue;
            }

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

            // For L2, use all escalated ticket fofoIds as target (no collection plan filter)
            Set<Integer> l2TargetFofoIds = new HashSet<>(l2FofoIdList);

            RbmCallTargetModel l2Model = new RbmCallTargetModel();
            l2Model.setAuthId(l2AuthId);
            l2Model.setRbmName(authUser.getFullName() + " (L2)");
            l2Model.setL2Position(true);
            l2Model.setL2CallingList(l2FofoIdList.size());
            // Partner count = total assigned partners (same as L1 source)
            List<Integer> l2AssignedFofoIds = rbmToFofoIdsMap.getOrDefault(l2AuthId, Collections.emptyList());
            l2Model.setPartnerCount(l2AssignedFofoIds.size());

            // L2 Target = all escalated ticket fofoIds
            l2Model.setTodayTargetOfCall(l2TargetFofoIds.size());

            // Value Achieved = distinct fofoIds from today's remarks that are in escalated tickets
            List<PartnerCollectionRemark> l2TodayRemarks = remarksByAuthId.getOrDefault(l2AuthId, Collections.emptyList());
            long l2ValueAchieved = l2TodayRemarks.stream()
                    .map(PartnerCollectionRemark::getFofoId)
                    .filter(l2TargetFofoIds::contains)
                    .distinct()
                    .count();
            l2Model.setValueTargetAchieved(l2ValueAchieved);

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

        // Group L1 under their L2 manager using authUser.managerId
        Map<Integer, RbmCallTargetModel> l2ModelsByAuthId = new HashMap<>();
        Map<Integer, RbmCallTargetModel> l1ModelsByAuthId = new HashMap<>();
        for (RbmCallTargetModel m : rbmCallTargetModels) {
            if (m.isL2Position()) {
                l2ModelsByAuthId.put(m.getAuthId(), m);
            } else {
                l1ModelsByAuthId.put(m.getAuthId(), m);
            }
        }

        // Build L2 -> L1 team map using managerId from AuthUser
        Map<Integer, List<RbmCallTargetModel>> l2TeamMap = new LinkedHashMap<>();
        for (RbmCallTargetModel l2Model : l2ModelsByAuthId.values()) {
            l2TeamMap.put(l2Model.getAuthId(), new ArrayList<>());
        }

        Set<Integer> addedL1AuthIds = new HashSet<>();
        for (RbmCallTargetModel l1Model : l1ModelsByAuthId.values()) {
            AuthUser l1User = authUserMap.get(l1Model.getAuthId());
            if (l1User != null && l2TeamMap.containsKey(l1User.getManagerId())) {
                l2TeamMap.get(l1User.getManagerId()).add(l1Model);
                addedL1AuthIds.add(l1Model.getAuthId());
            }
        }

        // Build sorted result: L2 row, then its L1 team (sorted by name)
        List<RbmCallTargetModel> sortedModels = new ArrayList<>();

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

        for (RbmCallTargetModel l2Model : l2Sorted) {
            sortedModels.add(l2Model);
            List<RbmCallTargetModel> team = l2TeamMap.getOrDefault(l2Model.getAuthId(), Collections.emptyList());
            team.sort(Comparator.comparing(RbmCallTargetModel::getRbmName));
            sortedModels.addAll(team);
        }

        // Add any L1 RBMs not mapped to any L2 (sorted by name)
        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 {
        LocalDate today = LocalDate.now();
        LocalDateTime startDate = today.atStartOfDay();
        LocalDate firstOfMonth = today.withDayOfMonth(1);
        LocalDate endOfMonth = today.withDayOfMonth(today.lengthOfMonth()).plusDays(1);

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

        // Check if L1 or 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()));

        // Get fofo IDs for this RBM
        List<Integer> fofoIdList;
        if (isL2) {
            // L2: get fofo IDs from escalated tickets (all escalated tickets are target)
            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());

            // For L2, all escalated tickets are target
            Set<Integer> targetFofoIds = new HashSet<>(fofoIdList);

            // Get today's remarks
            List<PartnerCollectionRemark> todayRemarks = partnerCollectionRemarkRepository
                    .selectAllByAuthIdsOnDate(Collections.singletonList(authId), today);

            // Filter to only target partners and deduplicate
            Map<Integer, PartnerCollectionRemark> uniqueRemarksByFofoId = new LinkedHashMap<>();
            for (PartnerCollectionRemark remark : todayRemarks) {
                if (targetFofoIds.contains(remark.getFofoId())) {
                    uniqueRemarksByFofoId.putIfAbsent(remark.getFofoId(), remark);
                }
            }

            return buildCalledPartnerResult(uniqueRemarksByFofoId);
        }

        // L1 Logic
        Map<String, Set<Integer>> storeGuyMap;
        try {
            storeGuyMap = csService.getAuthUserPartnerIdMapping();
        } catch (ProfitMandiBusinessException e) {
            LOGGER.error("Error fetching store guy map", e);
            return Collections.emptyList();
        }

        if (!storeGuyMap.containsKey(authUser.getEmailId())) {
            return Collections.emptyList();
        }
        fofoIdList = new ArrayList<>(storeGuyMap.get(authUser.getEmailId()));

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

        // Get fofo stores for filtering
        Map<Integer, FofoStore> fofoStoresMap;
        try {
            fofoStoresMap = fofoStoreRepository.selectByRetailerIds(fofoIdList).stream()
                    .collect(Collectors.toMap(FofoStore::getId, x -> x, (a, b) -> a));
        } catch (ProfitMandiBusinessException e) {
            LOGGER.error("Error fetching fofo stores", e);
            return Collections.emptyList();
        }

        // Filter escalated partners for L1
        List<Integer> allRemarkIds = partnerCollectionRemarkRepository.selectMaxRemarkId(fofoIdList);
        if (!allRemarkIds.isEmpty()) {
            Map<Integer, PartnerCollectionRemark> partnerCollectionRemarks = partnerCollectionRemarkRepository.selectByIds(allRemarkIds).stream()
                    .collect(Collectors.toMap(PartnerCollectionRemark::getFofoId, x -> x, (a, b) -> a));
            fofoIdList = 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 stores
        List<Integer> validFofoIds = fofoIdList.stream()
                .filter(fofoId -> {
                    FofoStore store = fofoStoresMap.get(fofoId);
                    if (store == null || store.isInternal()) {
                        return false;
                    }
                    return ActivationType.ACTIVE.equals(store.getActivationType());
                })
                .collect(Collectors.toList());

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

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

        // Get MTD billing data for zero billing check
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);
        Set<Integer> mtdBilledFofoIds = mtdBillingData.stream()
                .filter(RbmWeeklyBillingModel::isMtdBilled)
                .map(RbmWeeklyBillingModel::getFofoId)
                .collect(Collectors.toSet());

        // Build target partner set (PlanToday + CarryForward + ZeroBilling + Untouched)
        Set<Integer> targetFofoIds = new HashSet<>();
        for (Integer fofoId : validFofoIds) {
            int rank = collectionRankMap.getOrDefault(fofoId, 5);
            boolean hasZeroBilling = !mtdBilledFofoIds.contains(fofoId);

            // Same priority logic as in getRbmCallTargetModels
            if (rank == 1 || rank == 2 || hasZeroBilling || rank == 3) {
                targetFofoIds.add(fofoId);
            }
            // rank 4 (FuturePlan) and rank 5 (Normal) are NOT in target
        }

        // Get today's remarks
        List<PartnerCollectionRemark> todayRemarks = partnerCollectionRemarkRepository
                .selectAllByAuthIdsOnDate(Collections.singletonList(authId), today);

        // Filter to only target partners and deduplicate
        Map<Integer, PartnerCollectionRemark> uniqueRemarksByFofoId = new LinkedHashMap<>();
        for (PartnerCollectionRemark remark : todayRemarks) {
            if (targetFofoIds.contains(remark.getFofoId())) {
                uniqueRemarksByFofoId.putIfAbsent(remark.getFofoId(), remark);
            }
        }

        return buildCalledPartnerResult(uniqueRemarksByFofoId);
    }

    private List<CalledPartnerDetailModel> buildCalledPartnerResult(Map<Integer, PartnerCollectionRemark> uniqueRemarksByFofoId) {
        if (uniqueRemarksByFofoId.isEmpty()) {
            return Collections.emptyList();
        }

        Set<Integer> fofoIds = uniqueRemarksByFofoId.keySet();
        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
        List<Long> callLogIds = uniqueRemarksByFofoId.values().stream()
                .filter(r -> r.getAgentCallLogId() > 0)
                .map(PartnerCollectionRemark::getAgentCallLogId)
                .collect(Collectors.toList());

        Map<Long, com.spice.profitmandi.dao.entity.cs.AgentCallLog> callLogMap = new HashMap<>();
        if (!callLogIds.isEmpty()) {
            try {
                List<com.spice.profitmandi.dao.entity.cs.AgentCallLog> callLogs = agentCallLogRepository.findByIds(callLogIds);
                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 : uniqueRemarksByFofoId.values()) {
            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;

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

            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 L1 or L2
        List<Position> positions = positionRepository.selectPositionByAuthIds(Collections.singletonList(authId));
        boolean isL1 = positions.stream()
                .anyMatch(p -> ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
                        && EscalationType.L1.equals(p.getEscalationType()));
        boolean isL2 = positions.stream()
                .anyMatch(p -> ProfitMandiConstants.TICKET_CATEGORY_RBM == p.getCategoryId()
                        && EscalationType.L2.equals(p.getEscalationType()));

        // Get fofo IDs for this RBM
        Map<String, Set<Integer>> storeGuyMap = csService.getAuthUserPartnerIdMapping();
        List<Integer> fofoIdList = new ArrayList<>();

        if (isL2) {
            // L2: get fofo IDs from escalated tickets
            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 if (storeGuyMap.containsKey(authUser.getEmailId())) {
            fofoIdList = new ArrayList<>(storeGuyMap.get(authUser.getEmailId()));
        }

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

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

        // Get fofo stores
        Map<Integer, FofoStore> fofoStoresMap = new HashMap<>();
        try {
            fofoStoresMap = fofoStoreRepository.selectByRetailerIds(fofoIdList).stream()
                    .collect(Collectors.toMap(FofoStore::getId, x -> x, (a, b) -> a));
        } catch (ProfitMandiBusinessException e) {
            LOGGER.error("Error fetching fofo stores", e);
        }

        // For L1 RBMs, filter escalated partners
        if (isL1) {
            List<Integer> allRemarkIds = partnerCollectionRemarkRepository.selectMaxRemarkId(fofoIdList);
            if (!allRemarkIds.isEmpty()) {
                Map<Integer, PartnerCollectionRemark> partnerCollectionRemarks = partnerCollectionRemarkRepository.selectByIds(allRemarkIds).stream()
                        .collect(Collectors.toMap(PartnerCollectionRemark::getFofoId, x -> x, (a, b) -> a));
                fofoIdList = 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());
            }
        }

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

        // MTD billing data
        List<RbmWeeklyBillingModel> mtdBillingData = getWeeklyBillingDataForMonth(firstOfMonth, endOfMonth);
        Set<Integer> mtdBilledFofoIds = mtdBillingData.stream()
                .filter(RbmWeeklyBillingModel::isMtdBilled)
                .map(RbmWeeklyBillingModel::getFofoId)
                .collect(Collectors.toSet());

        // Filter to valid fofo IDs (external, ACTIVE, has collection plan)
        Map<Integer, Integer> finalCollectionRankMap = collectionRankMap;
        Map<Integer, FofoStore> finalFofoStoresMap = fofoStoresMap;
        List<Integer> validFofoIds = fofoIdList.stream()
                .filter(fofoId -> {
                    FofoStore store = finalFofoStoresMap.get(fofoId);
                    if (store == null || store.isInternal()) return false;
                    if (!ActivationType.ACTIVE.equals(store.getActivationType())) return false;
                    return finalCollectionRankMap.containsKey(fofoId);
                })
                .collect(Collectors.toList());

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

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

        // Build rows
        for (Integer fofoId : validFofoIds) {
            int rank = collectionRankMap.getOrDefault(fofoId, 5);
            boolean hasZeroBilling = !mtdBilledFofoIds.contains(fofoId);

            String status;
            if (rank == 1) {
                status = "Plan Today";
            } else if (rank == 2) {
                status = "Carry Forward";
            } else if (rank == 3) {
                status = "Untouched";
            } else if (hasZeroBilling) {
                status = "Zero Billing";
            } 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;
    }

}