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

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

        for (String[] r : roles) {

            String role = r[0];

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

            String levelStr = r[2];

            // Monthly target per (authId × warehouse) for this role.

            // Use the target date's month so backfills for past months work correctly.

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

            if (monthlyTgts.isEmpty()) continue;

            // MTD achieved per (authId × warehouse) for this role. Same-day date range → MTD.

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

            }

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

            }

        }

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

        for (String[] r : roles) {

            String role = r[0];

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

            String levelStr = r[2];

            for (SalesHierarchyAchievedByMovementModel a :

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

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

            }

        }

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

                CustomRetailer retailer = retailerMap.get(fofoId);

                if (retailer != null) {

                    partyName = retailer.getBusinessName();

                    code = retailer.getCode();

                } else {

                    partyName = "Unknown (" + fofoId + ")";

                }

            } else {

                partyName = "Unknown (" + normalized + ")";