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

    @Autowired

    RetailerContactRepository retailerContactRepository;

    @Autowired

    AddressRepository addressRepository;

    @Autowired

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

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

            }

        }

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

        // 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 call logs)

            long valueAchieved = getCalledCountFromCallLogs(rbmAuthId);

            targetModel.setValueTargetAchieved(valueAchieved);

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