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

import com.spice.profitmandi.dao.entity.inventory.SalesTargets;

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

import com.spice.profitmandi.dao.repository.catalog.SalesTargetsRepository;

import com.spice.profitmandi.dao.repository.cs.CsService;

import com.spice.profitmandi.dao.repository.cs.PositionRepository;

import com.spice.profitmandi.dao.repository.dtr.FofoStoreRepository;

import com.spice.profitmandi.dao.repository.dtr.RetailerContactRepository;

import com.spice.profitmandi.dao.repository.fofo.MonthlyTargetRepository;

import com.spice.profitmandi.dao.repository.logistics.PublicHolidaysRepository;

import com.spice.profitmandi.dao.repository.transaction.LoanRepository;

import com.spice.profitmandi.dao.repository.transaction.OrderRepository;

import com.spice.profitmandi.dao.repository.user.AddressRepository;

import com.spice.profitmandi.service.user.RetailerService;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.hibernate.Session;

import org.hibernate.SessionFactory;

import org.hibernate.query.NativeQuery;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.cache.annotation.Cacheable;

import org.springframework.stereotype.Component;

import javax.persistence.TypedQuery;

import java.time.*;

import java.time.format.DateTimeFormatter;

import java.time.temporal.ChronoUnit;

import java.util.*;

import java.util.stream.Collectors;

@Component

public class RbmTargetServiceImpl implements RbmTargetService {

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

    @Autowired

    SessionFactory sessionFactory;

    @Autowired

    RbmTargetsRepository rbmTargetsRepository;

    @Autowired

    SalesTargetsRepository salesTargetsRepository;

    @Autowired

    SalesAchievementsRepository salesAchievementsRepository;

    @Autowired

    AuthService authService;

    @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<RbmBrandSoldModel> getBrandsSoldByRbm(int authId, int warehouseId, LocalDate startDate, LocalDate endDate) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<RbmBrandSoldModel> q = session.createNamedQuery("RbmDrr.BrandSoldByRbm", RbmBrandSoldModel.class);

        q.setParameter("authId", authId);

        q.setParameter("warehouseId", warehouseId);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.getResultList();

    }

    @Override

    public List<RbmBrandPartnerCountModel> getBrandPartnerCountByRbm(int authId, int warehouseId, LocalDate startDate, LocalDate endDate) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<RbmBrandPartnerCountModel> q = session.createNamedQuery("RbmDrr.BrandPartnerCountByRbm", RbmBrandPartnerCountModel.class);

        q.setParameter("authId", authId);

        q.setParameter("warehouseId", warehouseId);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.getResultList();

    }

    @Override

    public List<WarehouseBuyingByMovementModel> getWarehouseBuyingByMovement(int warehouseId, LocalDate startDate, LocalDate endDate) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<WarehouseBuyingByMovementModel> q = session.createNamedQuery("RbmDrr.WarehouseBuyingByMovement", WarehouseBuyingByMovementModel.class);

        q.setParameter("warehouseId", warehouseId);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.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);

            }

        }

    }

    /**

     * Daily writer for Sales-hierarchy targets. Rolls up today's persisted

     * inventory.rbm_targets snapshots (produced by {@link #setMovementWiseRbmTargets})

     * to every active Sales person whose downline includes those RBMs, and

     * inserts one row per (sales, warehouse) into inventory.sales_targets.

     *

     * <p>Chain order matters: run this AFTER pushRbmTargets so today's RBM

     * snapshot rows exist when we roll up.

     */

    @Override

    public void setSalesTargets() {

        setSalesTargetsForDate(LocalDate.now());

    }

    @Override

    public void setSalesTargetsForDate(LocalDate date) {

        LocalDateTime dayStart = date.atStartOfDay();

        LocalDateTime dayEnd = date.plusDays(1).atStartOfDay();

        // Persisted RBM snapshots for the target date (one row per RBM × warehouse).

        List<RbmTargets> todayRbmSnapshots = rbmTargetsRepository.selectTargetsByDates(dayStart, dayEnd);

        if (todayRbmSnapshots.isEmpty()) {

            LOGGER.warn("setSalesTargetsForDate: no RBM snapshots for {}; run pushRbmTargets first", date);

            return;

        }

        // Index snapshots by (rbmAuthId, warehouseId) for O(1) roll-up.

        // rbm_auth_id → warehouseId → snapshot row.

        Map<Integer, Map<Integer, RbmTargets>> byRbmByWh = new HashMap<>();

        for (RbmTargets s : todayRbmSnapshots) {

            byRbmByWh.computeIfAbsent(s.getRbmAuthId(), k -> new HashMap<>()).put(s.getWarehouseId(), s);

        }

        Set<Integer> allSnapshotRbms = byRbmByWh.keySet();

        // Sales roles + their (categoryId, escalationType) mapping — mirrors

        // MonitorController /indent/today_po_table_fragment so both places agree.

        String[][] roles = {

                {"ASM", "4", "L1"}, {"CM", "4", "L2"}, {"DM", "4", "L3"},

                {"BM", "4", "L4"}, {"RSM", "4", "L5"}, {"NSM", "4", "L6"}

        };

        int written = 0;

        // Stamp rows within the target day so date-range queries pick them up

        // for the right day, even during backfill of a past date.

        LocalDate today = LocalDate.now();

        LocalDateTime stamp = date.equals(today) ? LocalDateTime.now() : date.atTime(java.time.LocalTime.NOON);

        for (String[] r : roles) {

            String role = r[0];

            int catId = Integer.parseInt(r[1]);

            EscalationType level = EscalationType.valueOf(r[2]);

            for (AuthUser sales : csService.getAuthUserByCategoryId(catId, level)) {

                if (!sales.isActive()) continue;

                // Downline authIds (includes self; we skip self since Sales users

                // aren't RBMs and won't appear in snapshot).

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

                downline.add(sales.getId());

                List<Integer> reportees = authService.getAllReportees(sales.getId());

                if (reportees != null) downline.addAll(reportees);

                downline.retainAll(allSnapshotRbms);  // keep only RBMs with today's snapshot

                if (downline.isEmpty()) continue;      // no RBMs under this Sales person today

                // Aggregate per warehouse across the downline RBMs.

                Map<Integer, float[]> perWh = new LinkedHashMap<>();  // wh → [hid, fast, run, slow, other]

                for (int rbmAuthId : downline) {

                    Map<Integer, RbmTargets> whMap = byRbmByWh.get(rbmAuthId);

                    if (whMap == null) continue;

                    for (Map.Entry<Integer, RbmTargets> e : whMap.entrySet()) {

                        RbmTargets t = e.getValue();

                        float[] acc = perWh.computeIfAbsent(e.getKey(), k -> new float[5]);

                        acc[0] += t.getHidTarget();

                        acc[1] += t.getFastMovingTarget();

                        acc[2] += t.getRunningtarget();

                        acc[3] += t.getSlowMovingtarget();

                        acc[4] += t.getOtherTarget();

                    }

                }

                for (Map.Entry<Integer, float[]> e : perWh.entrySet()) {

                    float[] acc = e.getValue();

                    SalesTargets st = new SalesTargets();

                    st.setSalesAuthId(sales.getId());

                    st.setSalesName(sales.getFullName());

                    st.setRole(role);

                    st.setWarehouseId(e.getKey());

                    st.setHidTarget(acc[0]);

                    st.setFastMovingTarget(acc[1]);

                    st.setRunningTarget(acc[2]);

                    st.setSlowMovingTarget(acc[3]);

                    st.setOtherTarget(acc[4]);

                    st.setCreateTimestamp(stamp);

                    salesTargetsRepository.persist(st);

                    written++;

                }

            }

        }

        LOGGER.info("setSalesTargetsForDate: wrote {} sales_targets rows for {}", written, date);

    }

    @Override

    public List<SalesTargets> getSalesTargetsByDates(LocalDate startDate, LocalDate endDate) {

        return salesTargetsRepository.selectTargetsByDates(startDate.atStartOfDay(), endDate.atStartOfDay());

    }

    @Override

    public void setSalesAchievements() {

        setSalesAchievementsForDate(LocalDate.now());

    }

    @Override

    public void setSalesAchievementsForDate(LocalDate date) {

        // ACH per (RBM authId × warehouse) for the target date, from the live

        // order SQL. Same source used everywhere else.

        List<TodayAchievedMovementModel> achModels =

                getMovementWiseAchievementByDate(date, date.plusDays(1));

        if (achModels.isEmpty()) {

            LOGGER.warn("setSalesAchievementsForDate: no orders on {}; nothing to snapshot", date);

            return;

        }

        // Sales roles + (categoryId, escalationType) — same mapping used elsewhere.

        String[][] roles = {

                {"ASM", "4", "L1"}, {"CM", "4", "L2"}, {"DM", "4", "L3"},

                {"BM", "4", "L4"}, {"RSM", "4", "L5"}, {"NSM", "4", "L6"}

        };

        // Stamp within the target date so date-range queries pick these rows

        // up for the correct day even during backfill.

        LocalDate today = LocalDate.now();

        LocalDateTime stamp = date.equals(today) ? LocalDateTime.now() : date.atTime(java.time.LocalTime.NOON);

        int written = 0;

        for (String[] r : roles) {

            String role = r[0];

            int catId = Integer.parseInt(r[1]);

            EscalationType level = EscalationType.valueOf(r[2]);

            for (AuthUser sales : csService.getAuthUserByCategoryId(catId, level)) {

                if (!sales.isActive()) continue;

                // Downline authIds (include self).

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

                downline.add(sales.getId());

                List<Integer> reportees = authService.getAllReportees(sales.getId());

                if (reportees != null) downline.addAll(reportees);

                // Aggregate ACH per warehouse across downline RBMs.

                Map<Integer, float[]> perWh = new LinkedHashMap<>();  // wh → [hid, fast, run, slow, other]

                for (TodayAchievedMovementModel a : achModels) {

                    if (!downline.contains(a.getAuthId())) continue;

                    float[] acc = perWh.computeIfAbsent(a.getWarehouseId(), k -> new float[5]);

                    acc[0] += a.getHidBilled();

                    acc[1] += a.getFastMovingBilled();

                    acc[2] += a.getRunningBilled() != null ? a.getRunningBilled() : 0f;

                    acc[3] += a.getSlowMovinBilled() != null ? a.getSlowMovinBilled() : 0f;

                    acc[4] += a.getOtherBilled() != null ? a.getOtherBilled() : 0f;

                }

                if (perWh.isEmpty()) continue;

                for (Map.Entry<Integer, float[]> e : perWh.entrySet()) {

                    float[] acc = e.getValue();

                    SalesAchievements sa = new SalesAchievements();

                    sa.setSalesAuthId(sales.getId());

                    sa.setSalesName(sales.getFullName());

                    sa.setRole(role);

                    sa.setWarehouseId(e.getKey());

                    sa.setAchievedHidTarget(acc[0]);

                    sa.setAchievedFastMovingTarget(acc[1]);

                    sa.setAchievedRunningTarget(acc[2]);

                    sa.setAchievedSlowMovingTarget(acc[3]);

                    sa.setAchievedOtherTarget(acc[4]);

                    sa.setCreateTimestamp(stamp);

                    salesAchievementsRepository.persist(sa);

                    written++;

                }

            }

        }

        LOGGER.info("setSalesAchievementsForDate: wrote {} sales_achievements rows for {}", written, date);

    }

    @Override

    public void setMovementWiseRbmAchievement() {

        LocalDate todayDate = LocalDate.now();

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

        for (TodayAchievedMovementModel achievement : todayAchievedMovementModels) {

            RbmAchievements rbmAchievements = new RbmAchievements();

            rbmAchievements.setRbmAuthId(achievement.getAuthId());

            rbmAchievements.setRbmName(achievement.getRbmName());

            rbmAchievements.setWarehouseId(achievement.getWarehouseId());

            rbmAchievements.setAchievedHidTarget(achievement.getHidBilled());

            rbmAchievements.setAchievedFastMovingTarget(achievement.getFastMovingBilled());

            rbmAchievements.setAchievedSlowMovingTarget(achievement.getSlowMovinBilled());

            rbmAchievements.setAchievedRunningTarget(achievement.getRunningBilled());

            rbmAchievements.setAchievedOtherTarget(achievement.getOtherBilled());

            rbmAchievements.setCreateTimestamp(LocalDateTime.now());

            rbmAchievementsRepository.persist(rbmAchievements);

        }

    }

    @Override

    public List<Sold15daysOldAgingModel> getAgingSale(LocalDate startDate, LocalDate endDate) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<Sold15daysOldAgingModel> typedQuerySimilar = session.createNamedQuery("Aging.SoldAgingModel", Sold15daysOldAgingModel.class);

        typedQuerySimilar.setParameter("startDate", startDate);

        typedQuerySimilar.setParameter("endDate", endDate);

        return typedQuerySimilar.getResultList();

    }

    @Override

    public List<RbmBilledFofoIdsModel> getDateWiseBilledFofoIdByRbm(LocalDate startDate, LocalDate endDate) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<RbmBilledFofoIdsModel> typedQuerySimilar = session.createNamedQuery("RBM.RbmBilledFofoId", RbmBilledFofoIdsModel.class);

        typedQuerySimilar.setParameter("startDate", startDate);

        typedQuerySimilar.setParameter("endDate", endDate);

        return typedQuerySimilar.getResultList();

    }

    @Override

    @Cacheable(value = "rbmWeeklyBilling",

            cacheManager = "fiveMintimeoutCacheManager",

            sync = true)

    public List<RbmWeeklyBillingModel> getWeeklyBillingDataForMonth(LocalDate monthStart, LocalDate monthEnd) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<RbmWeeklyBillingModel> typedQuery = session.createNamedQuery("RBM.WeeklyBilling", RbmWeeklyBillingModel.class);

        typedQuery.setParameter("startDate", monthStart);

        typedQuery.setParameter("endDate", monthEnd);

        return typedQuery.getResultList();

    }

    public List<Our15DaysOldAgingStock> our15DaysAgingStock() {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<Our15DaysOldAgingStock> typedQuerySimilar = session.createNamedQuery("Aging.15DaysOurStock", Our15DaysOldAgingStock.class);

        return typedQuerySimilar.getResultList();

    }

    @Override

    public List<WarehouseAgingStockModel> getWarehouseWiseAgingStock() {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<WarehouseAgingStockModel> typedQuery = session.createNamedQuery("Aging.15DaysWarehouseWiseStock", WarehouseAgingStockModel.class);

        return typedQuery.getResultList();

    }

    @Autowired

    OrderRepository orderRepository;

    @Override

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

        MonthlyTarget monthlyTarget = monthlyTargetRepository.selectByDateAndFofoId(YearMonth.now(), fofoId);

        if (monthlyTarget == null) {

            // Log or handle as needed

            return 0; // or -1 or some fallback

        }

        double remainingTarget = monthlyTarget.getPurchaseTarget() - secondryMtd;

//        double remainingWorkingDays = getWorkingDaysCount(date);

        double remainingWorkingDays = (double) getRemainingDaysInMonth(date);

        if (remainingWorkingDays == 0) return remainingTarget; // Last day

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

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

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

    }

    @Override

    public long getRemainingDaysInMonth(LocalDate date) {

        LocalDate lastDayOfMonth = YearMonth.from(date).atEndOfMonth();

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

        // Count Sundays manually

        long sundayCount = 0;

        LocalDate current = date;

        while (!current.isAfter(lastDayOfMonth)) {

            if (current.getDayOfWeek() == DayOfWeek.SUNDAY) {

                sundayCount++;

            }

            current = current.plusDays(1);

        }

        // Public holidays in the range

        long publicHolidays = publicHolidaysRepository

                .selectAllBetweenDates(date, lastDayOfMonth)

                .size();

        long remainingDays = totalDays - sundayCount - publicHolidays;

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

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

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

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

        return remainingDays;

    }

    @Autowired

    PositionRepository positionRepository;

    @Autowired

    CsService csService;

    @Autowired

    FofoStoreRepository fofoStoreRepository;

    @Autowired

    AuthRepository authRepository;

    @Autowired

    LoanRepository loanRepository;

    @Autowired

    PartnerCollectionService partnerCollectionService;

    @Autowired

    PartnerCollectionRemarkRepository partnerCollectionRemarkRepository;

    @Autowired

    RbmCallSequenceLogRepository rbmCallSequenceLogRepository;

    @Autowired

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

    @Autowired

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

    @Autowired

    RetailerContactRepository retailerContactRepository;

    @Autowired

    AddressRepository addressRepository;

    @Autowired

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

    @Override

    public List<RbmCallTargetModel> getRbmCallTargetModels() throws Exception {

        return getRbmCallTargetModels(LocalDate.now());

    }

    // Matches "No Answer", "NO_ANSWER", "no-answer", "noanswer", etc.

    private static boolean isNoAnswerStatus(String status) {

        if (status == null) return false;

        String s = status.trim().toLowerCase().replace('_', ' ').replace('-', ' ').replaceAll("\\s+", " ");

        return s.equals("no answer") || s.equals("noanswer");

    }

    @Override

    public List<OutOfSequenceDetailModel> getOutOfSequenceDetails(int authId) {

        LocalDate today = LocalDate.now();

        LocalDateTime start = today.atStartOfDay();

        LocalDateTime end = today.plusDays(1).atStartOfDay();

        List<RbmCallSequenceLog> logs =

                rbmCallSequenceLogRepository.selectByAuthIdAndDateRange(authId, start, end);

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

        for (RbmCallSequenceLog log : logs) {

            if (log.isOutOfSequence()) {

                // Keep only the first occurrence per fofoId (latest entry since ordered by id DESC)

                uniqueOosLogsByFofoId.putIfAbsent(log.getFofoId(), log);

            }

        }

        if (uniqueOosLogsByFofoId.isEmpty()) {

            return Collections.emptyList();

        }

        Set<Integer> fofoIds = uniqueOosLogsByFofoId.keySet();

        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();

        try {

            retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));

        } catch (ProfitMandiBusinessException e) {

            LOGGER.error("Error fetching fofo stores", e);

        }

        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");

        List<OutOfSequenceDetailModel> result = new ArrayList<>();

        for (RbmCallSequenceLog log : uniqueOosLogsByFofoId.values()) {

            CustomRetailer retailer = retailerMap.get(log.getFofoId());

            String partyName = retailer != null

                    ? retailer.getBusinessName()

                    : "Unknown (" + log.getFofoId() + ")";

            String code = retailer != null

                    ? retailer.getCode()

                    : "-";

            String time = log.getCreateTimestamp() != null

                    ? log.getCreateTimestamp().format(timeFormatter)

                    : "-";

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

        }

        return result;

    }

    @Override

    public List<CalledPartnerDetailModel> getCalledPartnerDetails(int authId) throws ProfitMandiBusinessException {

        return getCalledPartnerDetails(authId, LocalDate.now());

    }

    @Override

    public List<CalledPartnerDetailModel> getCalledPartnerDetails(int authId, LocalDate date) throws ProfitMandiBusinessException {

        // Get all call logs for this auth user on this date

        LOGGER.info("getCalledPartnerDetails: authId={}, date={}", authId, date);

        List<AgentCallLog> callLogs = agentCallLogRepository.findByAuthIdAndDate(authId, date);

        LOGGER.info("Found {} call logs for authId={} on date={}", callLogs.size(), authId, date);

        if (callLogs.isEmpty()) {

            return Collections.emptyList();

        }

        // Build a map of normalized customer number -> fofoId

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

        Set<String> normalizedNumbers = new HashSet<>();

        for (AgentCallLog callLog : callLogs) {

            String customerNumber = callLog.getCustomerNumber();

            if (customerNumber != null) {

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

                normalizedNumbers.add(normalized);

            }

        }

        // For each normalized number, find fofoId from retailer_contact first, then address

        for (String mobile : normalizedNumbers) {

            Integer fofoId = findFofoIdByMobile(mobile);

            if (fofoId != null) {

                customerToFofoIdMap.put(mobile, fofoId);

            }

        }

        // Get unique fofoIds for retailer lookup

        Set<Integer> fofoIds = new HashSet<>(customerToFofoIdMap.values());

        Map<Integer, CustomRetailer> retailerMap = Collections.emptyMap();

        if (!fofoIds.isEmpty()) {

            try {

                retailerMap = retailerService.getFofoRetailers(new ArrayList<>(fofoIds));

            } catch (ProfitMandiBusinessException e) {

                LOGGER.error("Error fetching fofo stores", e);

            }

        }

        // Get today's remarks for these fofoIds

        Map<Integer, List<PartnerCollectionRemark>> fofoRemarkMap = new HashMap<>();

        if (!fofoIds.isEmpty()) {

            List<PartnerCollectionRemark> todayRemarks = partnerCollectionRemarkRepository

                    .selectAllByFofoIdsOnDate(new ArrayList<>(fofoIds), date);

            for (PartnerCollectionRemark remark : todayRemarks) {

                fofoRemarkMap.computeIfAbsent(remark.getFofoId(), k -> new ArrayList<>()).add(remark);

            }

        }

        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm a");

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

        List<CalledPartnerDetailModel> result = new ArrayList<>();

        for (com.spice.profitmandi.dao.entity.cs.AgentCallLog callLog : callLogs) {

            String customerNumber = callLog.getCustomerNumber();

            if (customerNumber == null) {

                continue;

            }

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

            Integer fofoId = customerToFofoIdMap.get(normalized);

            String partyName = "Unknown";

            String code = "-";

            if (fofoId != null) {