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<WarehouseOpeningByMovementModel> getWarehouseOpeningByMovement(int warehouseId, LocalDate asOfDate) {

        Session session = sessionFactory.getCurrentSession();

        final TypedQuery<WarehouseOpeningByMovementModel> q = session.createNamedQuery(

                "RbmDrr.WarehouseOpeningByMovement", WarehouseOpeningByMovementModel.class);

        q.setParameter("warehouseId", warehouseId);

        q.setParameter("asOfDate", asOfDate);

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

        // Sales target for a person = (own_monthly_target − MTD_own_achievement) /

        // remainingDays, then split by warehouse stock mix — exactly like RBM, but

        // scoped to the Sales person's OWN assigned partners (no RBM roll-up).

        LocalDate monthStart = date.withDayOfMonth(1);

        int remainingWorkingDays = (int) getRemainingDaysInMonth(date);

        // Warehouse-level stock mix (same source used by setMovementWiseRbmTargets).

        Map<Integer, WarehouseMobileStockByMovementModel> whStockByWh = new HashMap<>();

        for (WarehouseMobileStockByMovementModel s : getWarehouseMobileStockByMovement()) {

            whStockByWh.put(s.getWarehouseId(), s);

        }

        String[][] roles = {

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

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

        };

        LocalDate today = LocalDate.now();

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

        int written = 0, zeroTargetSkipped = 0;

        // Cache monthly targets + MTD ACH per level so the RSM/NSM roll-up

        // pass can reuse them without repeating queries.

        Map<String, List<SalesHierarchyMonthlyTargetModel>> mtByLevel = new HashMap<>();

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

        for (String[] r : roles) {

            String role = r[0];

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

            String levelStr = r[2];

            List<SalesHierarchyMonthlyTargetModel> monthlyTgts = getSalesMonthlyTargetByRole(catId, levelStr, date);

            mtByLevel.put(role, monthlyTgts);

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

            for (SalesHierarchyAchievedByMovementModel a :

                    getSalesAchievementByRole(catId, levelStr, monthStart, date.plusDays(1))) {

                double totBilled =

                        a.getHidBilled()

                                + a.getFastMovingBilled()

                                + (a.getRunningBilled() != null ? a.getRunningBilled() : 0)

                                + (a.getSlowMovingBilled() != null ? a.getSlowMovingBilled() : 0)

                                + (a.getOtherBilled() != null ? a.getOtherBilled() : 0);

                mtdByKey.merge(a.getAuthId() + "-" + a.getWarehouseId(), totBilled, Double::sum);

            }

            mtdByLevelKey.put(role, mtdByKey);

            // Direct-role writer — one row per (authId × warehouse) at this role.

            if (monthlyTgts.isEmpty()) continue;

            for (SalesHierarchyMonthlyTargetModel mt : monthlyTgts) {

                if (mt.getMonthlyTarget() <= 0) {

                    zeroTargetSkipped++;

                    continue;

                }

                double achieved = mtdByKey.getOrDefault(mt.getAuthId() + "-" + mt.getWarehouseId(), 0.0);

                double remaining = mt.getMonthlyTarget() - achieved;

                double todayTarget = (remainingWorkingDays > 0 && remaining > 0)

                        ? remaining / remainingWorkingDays : 0;

                WarehouseMobileStockByMovementModel stock = whStockByWh.get(mt.getWarehouseId());

                if (stock == null || stock.getTotalAvailabilityPrice() <= 0) continue;

                float total = stock.getTotalAvailabilityPrice();

                SalesTargets st = new SalesTargets();

                st.setSalesAuthId(mt.getAuthId());

                st.setSalesName(mt.getName());

                st.setRole(role);

                st.setWarehouseId(mt.getWarehouseId());

                st.setHidTarget((float) ((stock.getTotalHidCatalogPrice() / total) * todayTarget));

                st.setFastMovingTarget((float) ((stock.getTotalFastMovingCatalogPrice() / total) * todayTarget));

                st.setSlowMovingTarget((float) ((stock.getTotalSlowMovingCatalogPrice() / total) * todayTarget));

                st.setRunningTarget((float) ((stock.getTotalRunningCatalogPrice() / total) * todayTarget));

                st.setOtherTarget((float) ((stock.getTotalOtherCategoryCatalogPrice() / total) * todayTarget));

                st.setCreateTimestamp(stamp);

                salesTargetsRepository.persist(st);

                written++;

            }

        }

        // Roll-up pass for RSM (L5) and NSM (L6) — uses distinct-partner dedup

        // queries so partners assigned to multiple positions in the downline

        // aren't counted more than once.

        String[] rollUpRoles = {"RSM", "NSM"};

        for (String rollUpRole : rollUpRoles) {

            String levelStr = "RSM".equals(rollUpRole) ? "L5" : "L6";

            for (AuthUser top : csService.getAuthUserByCategoryId(4, EscalationType.valueOf(levelStr))) {

                if (!top.isActive()) continue;

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

                tree.add(top.getId());

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

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

                if (tree.isEmpty()) continue;

                // Distinct-partner monthly TGT per warehouse for the downline.

                Map<Integer, Double> monthlyByWh = new LinkedHashMap<>();

                for (com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel d :

                        getSalesDownlineMonthlyTarget(tree, date)) {

                    monthlyByWh.put(d.getWarehouseId(), (double) d.getMonthlyTarget());

                }

                // Distinct-partner MTD ACH per warehouse for the downline.

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

                for (com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel d :

                        getSalesDownlineAchievement(tree, monthStart, date.plusDays(1))) {

                    double tot = d.getHidBilled() + d.getFastMovingBilled()

                            + (d.getRunningBilled() != null ? d.getRunningBilled() : 0)

                            + (d.getSlowMovingBilled() != null ? d.getSlowMovingBilled() : 0)

                            + (d.getOtherBilled() != null ? d.getOtherBilled() : 0);

                    mtdByWh.put(d.getWarehouseId(), tot);

                }

                for (Map.Entry<Integer, Double> e : monthlyByWh.entrySet()) {

                    int whId = e.getKey();

                    double monthlyTgt = e.getValue();

                    if (monthlyTgt <= 0) {

                        zeroTargetSkipped++;

                        continue;

                    }

                    double achieved = mtdByWh.getOrDefault(whId, 0.0);

                    double remaining = monthlyTgt - achieved;

                    double todayTarget = (remainingWorkingDays > 0 && remaining > 0)

                            ? remaining / remainingWorkingDays : 0;

                    WarehouseMobileStockByMovementModel stock = whStockByWh.get(whId);

                    if (stock == null || stock.getTotalAvailabilityPrice() <= 0) continue;

                    float total = stock.getTotalAvailabilityPrice();

                    SalesTargets st = new SalesTargets();

                    st.setSalesAuthId(top.getId());

                    st.setSalesName(top.getFullName());

                    st.setRole(rollUpRole);

                    st.setWarehouseId(whId);

                    st.setHidTarget((float) ((stock.getTotalHidCatalogPrice() / total) * todayTarget));

                    st.setFastMovingTarget((float) ((stock.getTotalFastMovingCatalogPrice() / total) * todayTarget));

                    st.setSlowMovingTarget((float) ((stock.getTotalSlowMovingCatalogPrice() / total) * todayTarget));

                    st.setRunningTarget((float) ((stock.getTotalRunningCatalogPrice() / total) * todayTarget));

                    st.setOtherTarget((float) ((stock.getTotalOtherCategoryCatalogPrice() / total) * todayTarget));

                    st.setCreateTimestamp(stamp);

                    salesTargetsRepository.persist(st);

                    written++;

                }

            }

        }

        LOGGER.info("setSalesTargetsForDate: wrote {} sales_targets rows for {} ({} rows skipped due to zero monthly target)",

                written, date, zeroTargetSkipped);

    }

    @Override

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

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

    }

    @Override

    public List<SalesHierarchyAchievedByMovementModel> getSalesAchievementByRole(

            int categoryId, String escalationType, LocalDate startDate, LocalDate endDate) {

        Session session = sessionFactory.getCurrentSession();

        TypedQuery<SalesHierarchyAchievedByMovementModel> q = session.createNamedQuery(

                "RbmDrr.SalesHierarchyAchievedByMovement", SalesHierarchyAchievedByMovementModel.class);

        q.setParameter("categoryId", categoryId);

        q.setParameter("escalationType", escalationType);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.getResultList();

    }

    @Override

    public List<SalesHierarchyMonthlyTargetModel> getSalesMonthlyTargetByRole(

            int categoryId, String escalationType, LocalDate refDate) {

        Session session = sessionFactory.getCurrentSession();

        TypedQuery<SalesHierarchyMonthlyTargetModel> q = session.createNamedQuery(

                "RbmDrr.SalesHierarchyMonthlyTarget", SalesHierarchyMonthlyTargetModel.class);

        q.setParameter("categoryId", categoryId);

        q.setParameter("escalationType", escalationType);

        q.setParameter("refDate", refDate);

        return q.getResultList();

    }

    @Override

    public List<com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel>

    getSalesDownlineAchievement(Collection<Integer> authIds, LocalDate startDate, LocalDate endDate) {

        if (authIds == null || authIds.isEmpty()) return new ArrayList<>();

        Session session = sessionFactory.getCurrentSession();

        TypedQuery<com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel> q =

                session.createNamedQuery("RbmDrr.SalesDownlineAchievedByMovement",

                        com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel.class);

        q.setParameter("authIds", authIds);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.getResultList();

    }

    @Override

    public List<com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel>

    getSalesDownlineMonthlyTarget(Collection<Integer> authIds, LocalDate refDate) {

        if (authIds == null || authIds.isEmpty()) return new ArrayList<>();

        Session session = sessionFactory.getCurrentSession();

        TypedQuery<com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel> q =

                session.createNamedQuery("RbmDrr.SalesDownlineMonthlyTarget",

                        com.spice.profitmandi.dao.model.SalesDownlineMonthlyTargetModel.class);

        q.setParameter("authIds", authIds);

        q.setParameter("refDate", refDate);

        return q.getResultList();

    }

    @Override

    public List<com.spice.profitmandi.dao.model.SalesHierarchyBrandSoldModel>

    getSalesBrandsSold(Collection<Integer> authIds, int warehouseId, LocalDate startDate, LocalDate endDate) {

        if (authIds == null || authIds.isEmpty()) return new ArrayList<>();

        Session session = sessionFactory.getCurrentSession();

        TypedQuery<com.spice.profitmandi.dao.model.SalesHierarchyBrandSoldModel> q =

                session.createNamedQuery("RbmDrr.SalesBrandSold",

                        com.spice.profitmandi.dao.model.SalesHierarchyBrandSoldModel.class);

        q.setParameter("authIds", authIds);

        q.setParameter("warehouseId", warehouseId);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.getResultList();

    }

    @Override

    public List<com.spice.profitmandi.dao.model.SalesHierarchyBrandPartnerCountModel>

    getSalesBrandPartnerCount(Collection<Integer> authIds, int warehouseId, LocalDate startDate, LocalDate endDate) {

        if (authIds == null || authIds.isEmpty()) return new ArrayList<>();

        Session session = sessionFactory.getCurrentSession();

        TypedQuery<com.spice.profitmandi.dao.model.SalesHierarchyBrandPartnerCountModel> q =

                session.createNamedQuery("RbmDrr.SalesBrandPartnerCount",

                        com.spice.profitmandi.dao.model.SalesHierarchyBrandPartnerCountModel.class);

        q.setParameter("authIds", authIds);

        q.setParameter("warehouseId", warehouseId);

        q.setParameter("startDate", startDate);

        q.setParameter("endDate", endDate);

        return q.getResultList();

    }

    @Override

    public void setSalesAchievements() {

        setSalesAchievementsForDate(LocalDate.now());

    }

    @Override

    public void setSalesAchievementsForDate(LocalDate date) {

        // Sales ACH per (auth × warehouse) — direct query scoped to each Sales

        // person's OWN partners (no RBM roll-up). Same source-of-truth orders SQL.

        String[][] roles = {

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

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

        };

        LocalDate today = LocalDate.now();

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

        int written = 0;

        // Cache per-level results so the RSM/NSM roll-up pass reuses them.

        Map<String, List<SalesHierarchyAchievedByMovementModel>> byLevel = new HashMap<>();

        for (String[] r : roles) {

            String role = r[0];

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

            String levelStr = r[2];

            List<SalesHierarchyAchievedByMovementModel> list =

                    getSalesAchievementByRole(catId, levelStr, date, date.plusDays(1));

            byLevel.put(role, list);

            for (SalesHierarchyAchievedByMovementModel a : list) {

                SalesAchievements sa = new SalesAchievements();

                sa.setSalesAuthId(a.getAuthId());

                sa.setSalesName(a.getName());

                sa.setRole(role);

                sa.setWarehouseId(a.getWarehouseId());

                sa.setAchievedHidTarget(a.getHidBilled());

                sa.setAchievedFastMovingTarget(a.getFastMovingBilled());

                sa.setAchievedRunningTarget(a.getRunningBilled() != null ? a.getRunningBilled() : 0f);

                sa.setAchievedSlowMovingTarget(a.getSlowMovingBilled() != null ? a.getSlowMovingBilled() : 0f);

                sa.setAchievedOtherTarget(a.getOtherBilled() != null ? a.getOtherBilled() : 0f);

                sa.setCreateTimestamp(stamp);

                salesAchievementsRepository.persist(sa);

                written++;

            }

        }

        // Roll-up pass for RSM (L5) and NSM (L6) — uses distinct-partner dedup

        // query so partners assigned to multiple positions in the downline

        // don't have their orders counted more than once.

        String[] rollUpRoles = {"RSM", "NSM"};

        for (String rollUpRole : rollUpRoles) {

            String levelStr = "RSM".equals(rollUpRole) ? "L5" : "L6";

            for (AuthUser top : csService.getAuthUserByCategoryId(4, EscalationType.valueOf(levelStr))) {

                if (!top.isActive()) continue;

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

                tree.add(top.getId());

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

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

                if (tree.isEmpty()) continue;

                for (com.spice.profitmandi.dao.model.SalesDownlineAchievedByMovementModel d :

                        getSalesDownlineAchievement(tree, date, date.plusDays(1))) {

                    SalesAchievements sa = new SalesAchievements();

                    sa.setSalesAuthId(top.getId());

                    sa.setSalesName(top.getFullName());

                    sa.setRole(rollUpRole);

                    sa.setWarehouseId(d.getWarehouseId());

                    sa.setAchievedHidTarget(d.getHidBilled());

                    sa.setAchievedFastMovingTarget(d.getFastMovingBilled());

                    sa.setAchievedRunningTarget(d.getRunningBilled() != null ? d.getRunningBilled() : 0f);

                    sa.setAchievedSlowMovingTarget(d.getSlowMovingBilled() != null ? d.getSlowMovingBilled() : 0f);

                    sa.setAchievedOtherTarget(d.getOtherBilled() != null ? d.getOtherBilled() : 0f);

                    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