Subversion Repositories SmartDukaan

package com.spice.profitmandi.web.controller;

import com.google.gson.Gson;

import com.mongodb.DBObject;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

import com.spice.profitmandi.common.model.*;

import com.spice.profitmandi.common.util.FileUtil;

import com.spice.profitmandi.common.util.FormattingUtils;

import com.spice.profitmandi.common.web.util.ResponseSender;

import com.spice.profitmandi.dao.entity.auth.AuthUser;

import com.spice.profitmandi.dao.entity.catalog.BrandCatalog;

import com.spice.profitmandi.dao.entity.fofo.PartnerDailyInvestment;

import com.spice.profitmandi.dao.entity.user.*;

import com.spice.profitmandi.dao.enumuration.cs.EscalationType;

import com.spice.profitmandi.dao.enumuration.dtr.CommunicationType;

import com.spice.profitmandi.dao.enumuration.dtr.LeadSource;

import com.spice.profitmandi.dao.enumuration.dtr.LeadStatus;

import com.spice.profitmandi.dao.model.*;

import com.spice.profitmandi.dao.repository.auth.AuthRepository;

import com.spice.profitmandi.dao.repository.auth.PartnerCollectionPlanRepository;

import com.spice.profitmandi.dao.repository.auth.PartnerCollectionRemarkRepository;

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

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

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

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

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

import com.spice.profitmandi.dao.repository.inventory.StateRepository;

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

import com.spice.profitmandi.service.AuthService;

import com.spice.profitmandi.service.PartnerCollectionService;

import com.spice.profitmandi.service.RbmTargetService;

import com.spice.profitmandi.service.catalog.BrandsService;

import com.spice.profitmandi.service.order.OrderService;

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

import com.spice.profitmandi.web.model.LoginDetails;

import com.spice.profitmandi.web.util.CookiesProcessor;

import com.spice.profitmandi.web.util.MVCResponseSender;

import org.apache.commons.csv.CSVFormat;

import org.apache.commons.csv.CSVParser;

import org.apache.commons.csv.CSVRecord;

import org.apache.commons.io.output.ByteArrayOutputStream;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

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

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

import org.springframework.core.io.InputStreamResource;

import org.springframework.http.HttpHeaders;

import org.springframework.http.HttpStatus;

import org.springframework.http.MediaType;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.ui.Model;

import org.springframework.util.StringUtils;

import org.springframework.web.bind.annotation.*;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.ByteArrayInputStream;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.Reader;

import java.text.DecimalFormat;

import java.time.DayOfWeek;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.LocalTime;

import java.time.format.DateTimeFormatter;

import java.time.temporal.TemporalAdjusters;

import java.util.*;

import java.util.Map.Entry;

import java.util.stream.Collectors;

import java.util.stream.Stream;

@Controller

@Transactional(rollbackFor = Throwable.class)

public class LeadController {

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

    /** Source / createdBy label for leads captured by the AI assistant (chat + voice). */

    private static final String AI_LEAD_SOURCE = "AI Assistant";

    // AI leads are auto-assigned to a random active BGC L1 (the lead desk). Previously a hardcoded

    // round-robin between Khushbu (134) and Archana (126); before that a fixed 53 / Shankar Mushran.

    // 126/134 are themselves BGC L1, so they stay in the pool - it just widens to the whole desk.

    private static final int AI_ASSIGN_FALLBACK = 134;  // Khushbu Saxena - used only if no active BGC L1 resolves

    @Value("${ai.lead.intake.token:}")

    private String aiLeadIntakeToken;

    /**

     * AI-assistant lead intake, hosted in the fofo app so it deploys via {@code fofo_deploy}. Mirrors the

     * profitmandi-web /add/lead contract: shared-secret {@code Auth-Token}, source stamped server-side,

     * idempotent by mobile+source, NOT NULL columns coalesced (so a minimal name+number lead never

     * crashes), stores {@code state} + {@code recordingUrl}, status = pending (Open/New). This path is

     * excluded from fofo's authentication/role/post interceptors in WebConfig.

     */

    @RequestMapping(value = ProfitMandiConstants.URL_AI_LEAD_INTAKE, method = RequestMethod.POST)

    @ResponseBody

    public ResponseEntity<?> aiLead(HttpServletRequest request,

                                    @RequestHeader(name = "Auth-Token", required = false) String authToken,

                                    @RequestBody AiLeadRequest aiLeadRequest) {

        LOGGER.info("AI lead intake request: {}", aiLeadRequest);

        if (aiLeadIntakeToken == null || aiLeadIntakeToken.trim().isEmpty()

                || authToken == null || !aiLeadIntakeToken.equals(authToken)) {

            LOGGER.warn("AI lead intake rejected: invalid or missing Auth-Token");

            return aiLeadResponse(HttpStatus.UNAUTHORIZED, "401", "UNAUTHORIZED", "FAILURE", null);

        }

        String rawMobile = aiLeadRequest.getMobile();

        String mobile = rawMobile == null ? "" : rawMobile.replaceAll("\\D", "");

        if (mobile.length() > 10) {

            mobile = mobile.substring(mobile.length() - 10);

        }

        if (mobile.length() != 10) {

            return aiLeadResponse(HttpStatus.BAD_REQUEST, "400", "Mobile number must contain 10 digits", "FAILURE", null);

        }

        String firstName = aiLeadRequest.getFirstName();

        if (firstName == null || firstName.trim().isEmpty()) {

            return aiLeadResponse(HttpStatus.BAD_REQUEST, "400", "First name is required", "FAILURE", null);

        }

        // Idempotency scoped to AI leads only (the AI side retries fire-and-forget posts).

        Lead existing = leadRepository.selectByMobileNumberAndSource(mobile, AI_LEAD_SOURCE);

        if (existing != null) {

            LOGGER.info("AI lead intake: AI lead already exists for mobile {} (id={}), skipping create", mobile, existing.getId());

            return aiLeadResponse(HttpStatus.OK, "200", "OK", "SUCCESS", existing.getId());

        }

        Lead lead = new Lead();

        lead.setLeadMobile(mobile);

        lead.setFirstName(firstName.trim());

        // last_name / city / address / state are NOT NULL in user.lead; coalesce nulls to "" so a minimal lead never crashes.

        lead.setLastName(aiLeadRequest.getLastName() == null ? "" : aiLeadRequest.getLastName().trim());

        String city = aiLeadRequest.getCity() == null ? "" : aiLeadRequest.getCity().trim();

        lead.setCity(city);

        lead.setOutLetName(aiLeadRequest.getOutletName());

        // AI does not collect a separate address; mirror web capture and fall back to city.

        lead.setAddress(city);

        String state = aiLeadRequest.getState();

        lead.setState(state == null ? "" : state.trim());

        // Source stamped server-side; caller cannot override it.

        lead.setSource(AI_LEAD_SOURCE);

        lead.setCreatedBy(AI_LEAD_SOURCE);

        lead.setStatus(LeadStatus.pending);

        lead.setColor("yellow");

        // Optional call-recording URL (voice leads only); store null when absent.

        String recordingUrl = aiLeadRequest.getRecordingUrl();

        if (recordingUrl != null && !recordingUrl.trim().isEmpty()) {

            lead.setRecordingUrl(recordingUrl.trim());

        }

        // Random active BGC L1 - only on a real create, dedup hits return before this point.

        int assignTo = resolveAiLeadAssignee();

        lead.setAssignTo(assignTo);

        lead.setAuthId(lead.getAssignTo());

        lead.setCreatedTimestamp(LocalDateTime.now());

        lead.setUpdatedTimestamp(LocalDateTime.now());

        // Stamp before the insert: region, owner, stage and an SLA clock. Without it the lead is

        // invisible to every SLA report.

        lmsAssignmentService.stamp(lead, "A");

        leadCreationService.createUnattended(lead);

        // Only stamp the code on a lead that was actually written. createUnattended SKIPS the

        // persist when the number collides with an open lead, and a blind persist here would

        // resurrect the duplicate it just refused.

        if (lead.getId() > 0) {

            lead.setLmsCode(lmsAssignmentService.generateLmsCode(lead));

            leadRepository.persist(lead);

        }

        LOGGER.info("AI lead intake: created lead id={} for mobile {}, assigned to authId={}", lead.getId(), mobile, assignTo);

        return aiLeadResponse(HttpStatus.OK, "200", "OK", "SUCCESS", lead.getId());

    }

    /**

     * Picks the owner for an incoming AI lead: a uniformly random <b>active</b> BGC L1 from

     * cs.position (category {@link ProfitMandiConstants#TICKET_CATEGORY_BGC}, escalation L1).

     * <p>

     * BGC is the desk that works AI / inbound leads before they reach field sales, and BGC L1-L3 is

     * already one of the groups offered in the Lead Management "Assign To" list, so a BGC L1 owner is

     * a valid assignee the team can see and reassign. Field Sales (category

     * {@link ProfitMandiConstants#TICKET_CATEGORY_SALES}) is deliberately NOT the pool.

     * <p>

     * Inactive users are filtered out because {@code getAuthUserByCategoryId(int, EscalationType)}

     * does not filter on active, and an inactive owner would strand the lead. Ids are already

     * distinct per user, so holding several regional positions does not raise a person's odds.

     */

    private int resolveAiLeadAssignee() {

        List<AuthUser> bgcL1 = csService.getAuthUserByCategoryId(

                ProfitMandiConstants.TICKET_CATEGORY_BGC, EscalationType.L1);

        List<AuthUser> eligible = bgcL1 == null ? Collections.<AuthUser>emptyList()

                : bgcL1.stream()

                        .filter(x -> Boolean.TRUE.equals(x.isActive()))

                        .collect(Collectors.toList());

        if (eligible.isEmpty()) {

            LOGGER.warn("AI lead intake: no active BGC L1 in cs.position, falling back to auth id {}",

                    AI_ASSIGN_FALLBACK);

            return AI_ASSIGN_FALLBACK;

        }

        AuthUser assignee = eligible.get(

                java.util.concurrent.ThreadLocalRandom.current().nextInt(eligible.size()));

        LOGGER.info("AI lead intake: assigned to BGC L1 {} ({}) picked from {} eligible",

                assignee.getId(), assignee.getFullName(), eligible.size());

        return assignee.getId();

    }

    private ResponseEntity<Map<String, Object>> aiLeadResponse(HttpStatus status, String statusCode,

                                                               String statusMessage, String responseStatus, Object response) {

        Map<String, Object> body = new HashMap<>();

        body.put("statusCode", statusCode);

        body.put("statusMessage", statusMessage);

        body.put("response", response);

        body.put("responseStatus", responseStatus);

        return ResponseEntity.status(status).body(body);

    }

    @Autowired

    private LeadRepository leadRepository;

    @Autowired

    private com.spice.profitmandi.service.LmsAssignmentService lmsAssignmentService;

    @Autowired

    private com.spice.profitmandi.dao.service.lead.LeadCreationService leadCreationService;

    @Autowired

    private LeadActivityRepository leadActivityRepository;

    @Autowired

    private com.spice.profitmandi.dao.repository.dtr.LeadVisitRequestRepository leadVisitRequestRepository;

    @Autowired

    private StateRepository stateRepository;

    @Autowired

    private AuthService authService;

    @Autowired

    private AuthRepository authRepository;

    @Autowired

    private Gson gson;

    @Autowired

    private CookiesProcessor cookiesProcessor;

    @Autowired

    PositionRepository positionRepository;

    @Autowired

    private MVCResponseSender mvcResponseSender;

    @Autowired

    private PartnerCollectionPlanRepository partnerCollectionPlanRepository;

    @Autowired

    private CsService csService;

    @Autowired

    private FofoStoreRepository fofoStoreRepository;

    @Autowired

    private PartnerCollectionService partnerCollectionService;

    @Autowired

    private RetailerService retailerService;

    @Autowired

    private PartnerCollectionRemarkRepository partnerCollectionRemarkRepository;

    @Autowired

    private PartnerDailyInvestmentRepository partnerDailyInvestmentRepository;

    @Autowired

    private UserWalletRepository userWalletRepository;

    @Autowired

    private FranchiseeVisitRepository franchiseeVisitRepository;

    @Autowired

    private FranchiseeActivityRepository franchiseeActivityRepository;

    @Autowired

    private VisitRequestRepository visitRequestRepository;

    @Autowired

    private LeadBrandRepository leadBrandRepository;

    @Autowired

    private LeadDetailRepository leadDetailRepository;

    @Autowired

    private LeadLiveLocationRepository leadLiveLocationRepository;

    @Autowired

    private BeatRouteRepository beatRouteRepository;

    @Autowired

    private LeadRouteRepository leadRouteRepository;

    @Autowired

    private BeatRepository beatRepository;

    @Autowired

    private org.springframework.mail.javamail.JavaMailSender gmailRelaySender;

    @Autowired

    private Mongo mongoClient;

    @Autowired

    private ResponseSender<?> responseSender;

    @Autowired

    private BrandsService brandsService;

    @Autowired

    OrderService orderService;

    @Autowired

    private RbmRatingRepository rbmRatingRepository;

    List<LeadStatus> status = Arrays.asList(LeadStatus.notInterested, LeadStatus.finalized);

    /**

     * Restricted lead view applies only to field-level sales positions (L1-L3: ASM/CM/DM).

     * L4+ sales (BM/RSM/NSM and above, incl. co-founder-level L7) keep the full lead list —

     * a senior head can hold a SALES position for approver/head flows without losing

     * Lead Management visibility.

     */

    private boolean isFieldSales(int authUserId) {

        return positionRepository.selectPositionByAuthId(authUserId).stream()

                .filter(p -> p.getCategoryId() == ProfitMandiConstants.TICKET_CATEGORY_SALES)

                .anyMatch(p -> p.getEscalationType() == null

                        || !p.getEscalationType().isGreaterThanEqualTo(EscalationType.L4));

    }

    @RequestMapping(value = "/getOpenLead", method = RequestMethod.GET)

    public String getOpenLead(HttpServletRequest request, @RequestParam(name = "leadStatus", required = false, defaultValue = "All") List<LeadStatus> leadStatus, @RequestParam(name = "color", required = false, defaultValue = "All") List<String> color, @RequestParam(name = "startDate", required = false) LocalDate startDate, @RequestParam(name = "endDate", required = false) LocalDate endDate, Model model) throws Exception {

        LocalDateTime startDateTime = startDate != null ? startDate.atStartOfDay() : null;

        LocalDateTime endDateTime = endDate != null ? endDate.atTime(23, 59, 59) : null;

        LOGGER.info("startDateTime: " + startDateTime + ", endDateTime: " + endDateTime);

        LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

        String emailId = loginDetails.getEmailId();

        AuthUser authUser = authRepository.selectByEmailOrMobile(emailId);

        boolean isPositionSales = this.isFieldSales(authUser.getId());

        // Statuses as the user picked them (before "All" is expanded below). Re-selected in the

        // filter so the Download button sends the same statuses the table was loaded with.

        List<LeadStatus> selectedLeadStatuses = new ArrayList<>(leadStatus);

        if (leadStatus.contains(LeadStatus.All)) {

            leadStatus.add(LeadStatus.pending);

            leadStatus.add(LeadStatus.notInterested);

            leadStatus.add(LeadStatus.finalized);

        }

        if (color.contains("All")) {

            color.add("yellow");

            color.add("green");

        }

        List<Lead> leads = new ArrayList<>();

        model.addAttribute("colors", color);

        Map<Integer, List<LeadActivity>> leadActivityMap = new HashMap<>();

        List<Integer> authUserIds = null;

        LOGGER.info("color111" + color);

        List<Lead> leadFollowUp = new ArrayList<>();

        if (isPositionSales) {

            authUserIds = authService.getAllReportees(authUser.getId());

            authUserIds.add(authUser.getId());

            LOGGER.info("authIds" + authUserIds);

            LOGGER.info("leadStatus" + leadStatus);

            if (startDateTime == null) {

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndUpdatedTimestampAndAuthIds(leadStatus, authUserIds, color, LocalDateTime.now().minusMonths(1));

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectByAssignAuthIdsAndStatus(authUserIds, LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

            } else {

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndBetweenCreatedDateAndAuthIds(leadStatus, authUserIds, color, startDateTime, endDateTime);

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectByAssignAuthIdsAndStatus(authUserIds, LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

            }

        } else {

            if (startDateTime == null) {

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndUpdatedTimestamp(leadStatus, color, LocalDateTime.now().minusMonths(1));

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectAllByStatus(LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

                LOGGER.info("leadlocalDateTime1" + leads);

            } else {

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndBetweenCreatedDate(leadStatus, color, startDateTime, endDateTime);

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectAllByStatus(LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

                LOGGER.info("leadlocalDateTime2" + leads);

            }

        }

        if (isPositionSales && authUserIds.size() > 0) {

            List<String> leadCreators = new ArrayList<>();

            leadCreators.add("daily-sync");

            leadCreators.addAll(csService.getAuthUserIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, Arrays.asList(EscalationType.L1, EscalationType.L2)).stream().map(x -> x.getFullName()).collect(Collectors.toList()));

            leadCreators.addAll(csService.getAuthUserIds(ProfitMandiConstants.TICKET_CATEGORY_BGC, Arrays.asList(EscalationType.L1, EscalationType.L2, EscalationType.L3)).stream().map(x -> x.getFullName()).collect(Collectors.toList()));

            model.addAttribute("leadCreators", leadCreators);

        }

        List<Lead> weekLast = new ArrayList<>();

        List<Lead> weekThird = new ArrayList<>();

        List<Lead> weekSecond = new ArrayList<>();

        List<Lead> weekFirst = new ArrayList<>();

        LocalDateTime curDate = LocalDate.now().atStartOfDay();

        List<LeadWeakWiseModel> lwDays = new ArrayList<>();

        for (int i = 7; i >= 1; i--) {

            LocalDateTime startOfDay = curDate.minusDays(i);

            LeadWeakWiseModel lm = new LeadWeakWiseModel();

            lm.setMonth(startOfDay.toLocalDate());

            lwDays.add(lm);

        }

        if (!leads.isEmpty()) {

            weekLast.addAll(

                    leads.stream().filter(x -> x.getCreatedTimestamp().isAfter(curDate.minusDays(28)) && x.getCreatedTimestamp().isBefore(curDate.minusDays(21))).collect(Collectors.toList()));

            weekThird.addAll(leads.stream().filter(x -> x.getCreatedTimestamp().isAfter(curDate.minusDays(21)) && x.getCreatedTimestamp().isBefore(curDate.minusDays(14))).collect(Collectors.toList()));

            weekSecond.addAll(leads.stream().filter(x -> x.getCreatedTimestamp().isAfter(curDate.minusDays(14)) && x.getCreatedTimestamp().isBefore(curDate.minusDays(7))).collect(Collectors.toList()));

            weekFirst.addAll(leads.stream().filter(x -> x.getCreatedTimestamp().isAfter(curDate.minusDays(7))).collect(Collectors.toList()));

        }

        LOGGER.info("weekLast" + weekLast);

        LOGGER.info("weekThird" + weekThird);

        LOGGER.info("weekSecond" + weekSecond);

        Map<String, Long> leadLasts = weekLast.stream().collect(Collectors.groupingBy(x -> x.getCreatedBy(), Collectors.counting()));

        Map<String, Long> leadThirds = weekThird.stream().collect(Collectors.groupingBy(x -> x.getCreatedBy(), Collectors.counting()));

        Map<String, Long> leadSeconds = weekSecond.stream().collect(Collectors.groupingBy(x -> x.getCreatedBy(), Collectors.mapping(Lead::getCreatedBy, Collectors.counting())));

        Map<String, Map<LocalDate, Long>> leadFirsts = weekFirst.stream().collect(Collectors.groupingBy(x -> x.getCreatedBy(), Collectors.groupingBy(x -> x.getCreatedTimestamp().toLocalDate(), Collectors.counting())));

        LOGGER.info("leadFirsts" + leadFirsts);

        List<Integer> authIds = new ArrayList<>();

        if (!leads.isEmpty()) {

            authIds.addAll(leads.stream().map(x -> x.getAssignTo()).collect(Collectors.toList()));

            leadActivityMap = leadActivityRepository.selectAllByleadIds(leads.stream().map(x -> x.getId()).collect(Collectors.toList())).stream().collect(Collectors.groupingBy(LeadActivity::getLeadId, Collectors.toList()));

            Map<Integer, AuthUser> authIdAndAuthUserMap = authRepository.selectByIds(authIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

            model.addAttribute("leadActivityMap", leadActivityMap);

            model.addAttribute("authIdAndAuthUserMap", authIdAndAuthUserMap);

            // Pending visit requests per lead — drives the "Visit request pending"

            // badge on the row template. Best-effort: never blocks the list render.

            try {

                List<Integer> leadIds = leads.stream().map(Lead::getId).collect(Collectors.toList());

                List<com.spice.profitmandi.dao.entity.user.LeadVisitRequest> pending =

                        leadVisitRequestRepository.selectByLeadIdsAndStatus(leadIds, "PENDING");

                Set<Integer> pendingSet = pending.stream()

                        .map(com.spice.profitmandi.dao.entity.user.LeadVisitRequest::getLeadId)

                        .collect(Collectors.toSet());

                model.addAttribute("pendingVisitRequestLeadIds", pendingSet);

            } catch (Exception e) {

                LOGGER.warn("pending visit-request lookup failed: {}", e.getMessage());

                model.addAttribute("pendingVisitRequestLeadIds", new HashSet<Integer>());

            }

        }

        ChartLeadModel cm = this.getLeadChart(leads);

        model.addAttribute("chartLead", gson.toJson(cm));

        LOGGER.info("ChartLeadModel" + gson.toJson(cm));

        LOGGER.info("lwDays" + lwDays);

        LOGGER.info("leads" + leads);

        List<String> stateNames = stateRepository.selectAll().stream().map(x -> x.getName()).collect(Collectors.toList());

//        List<AuthUser> authUsers = authRepository.selectAllActiveUser();

        List<AuthUser> authUsers = new ArrayList<>();

        List<AuthUser> salesUsers = csService.getAuthUserIds(ProfitMandiConstants.TICKET_CATEGORY_SALES, Arrays.asList(EscalationType.L1, EscalationType.L2, EscalationType.L3, EscalationType.L4));

        List<AuthUser> abmUsers = csService.getAuthUserIds(ProfitMandiConstants.TICKET_CATEGORY_ABM, Arrays.asList(EscalationType.L1, EscalationType.L2, EscalationType.L3));

        List<AuthUser> bgcUsers = csService.getAuthUserIds(ProfitMandiConstants.TICKET_CATEGORY_BGC, Arrays.asList(EscalationType.L1, EscalationType.L2, EscalationType.L3));

        authUsers.addAll(salesUsers);

        authUsers.addAll(abmUsers);

        authUsers.addAll(bgcUsers);

        LocalDate lastDate = curDate.minusDays(1).toLocalDate();

        List<LocalDate> weekDates = new ArrayList<>();

        weekDates.add(curDate.minusDays(28).toLocalDate());

        weekDates.add(curDate.minusDays(22).toLocalDate());

        weekDates.add(curDate.minusDays(21).toLocalDate());

        weekDates.add(curDate.minusDays(15).toLocalDate());

        weekDates.add(curDate.minusDays(14).toLocalDate());

        weekDates.add(curDate.minusDays(8).toLocalDate());

        weekDates.add(curDate.minusDays(7).toLocalDate());

        LOGGER.info("lastDate" + lastDate);

        model.addAttribute("weekDates", weekDates);

        model.addAttribute("startDate", startDate);

        model.addAttribute("endDate", endDate);

        model.addAttribute("lastDate", lastDate);

        model.addAttribute("stateNames", stateNames);

        model.addAttribute("communicationTypes", CommunicationType.values());

        model.addAttribute("authUsers", authUsers);

        model.addAttribute("leads", leads);

        model.addAttribute("leadStatus", LeadStatus.values());

        model.addAttribute("leadLasts", leadLasts);

        model.addAttribute("leadThirds", leadThirds);

        model.addAttribute("leadSeconds", leadSeconds);

        model.addAttribute("leadFirsts", leadFirsts);

        model.addAttribute("lwDays", lwDays);

        model.addAttribute("selectedLeadStatus", leadStatus.get(0));

        model.addAttribute("selectedLeadStatuses", selectedLeadStatuses);

        boolean showBulkUploader =

                "akhil.kumar@smartdukaan.com".equalsIgnoreCase(emailId);

        model.addAttribute("showBulkUploader", showBulkUploader);

        List<BrandCatalog> brandsDisplays = brandsService.getBrandsToDisplay(3);

        brandsDisplays.addAll(brandsService.getBrandsToDisplay(6));

        model.addAttribute("brandsDisplays", brandsDisplays.stream().distinct().collect(Collectors.toList()));

        LOGGER.info("brandsDisplays" + brandsDisplays);

        // Geo location status map for all leads

        if (!leads.isEmpty()) {

            List<Integer> allLeadIds = leads.stream().map(Lead::getId).collect(Collectors.toList());

            Map<Integer, LeadLiveLocation> geoLocationMap = leadLiveLocationRepository.selectByLeadIds(allLeadIds).stream()

                    .collect(Collectors.toMap(LeadLiveLocation::getLeadId, x -> x, (a, b) -> a));

            model.addAttribute("geoLocationMap", geoLocationMap);

        } else {

            model.addAttribute("geoLocationMap", new HashMap<>());

        }

        model.addAttribute("salesUsers", salesUsers);

        model.addAttribute("bgcUsers", bgcUsers);

        return "lead";

    }

    public ChartLeadModel getLeadChart(List<Lead> leads) throws ProfitMandiBusinessException {

        Map<LeadStatus, Long> leadStatusMap = leads.stream().collect(

                Collectors.groupingBy(x -> x.getStatus(), Collectors.mapping(Lead::getStatus, Collectors.counting())));

        Map<String, Long> hotLeadsMap = leads.stream().collect(

                Collectors.groupingBy(x -> x.getColor(), Collectors.mapping(Lead::getColor, Collectors.counting())));

        LOGGER.info("hotLeadsMap" + hotLeadsMap);

        ChartLeadModel cm = new ChartLeadModel();

        HashSet<LeadStatus> labels = new HashSet<LeadStatus>();

        labels.addAll(leadStatusMap.keySet());

        List<String> hotLeads = new ArrayList<>();

        hotLeads.addAll(leadStatusMap.keySet().stream().map(x -> x.toString()).collect(Collectors.toSet()));

        hotLeads.add("HotLead");

        List<String> hotLeadKeys = new ArrayList<>();

        hotLeadKeys.add("HotLead");

        List<String> hotLeadGreen = new ArrayList<>(hotLeadKeys);

        LOGGER.info("hotLeads" + hotLeads);

        List<LeadStatus> labelList = new ArrayList<>(labels);

        List<String> backgroundColor = new ArrayList<>();

        LOGGER.info("hotLeadKeys" + hotLeadKeys);

        List<Long> values = new ArrayList<>();

        for (String hotLead : hotLeads) {

            if (hotLead.equals("pending")) {

                backgroundColor.add("pink");

                values.add(leadStatusMap.get(LeadStatus.pending));

            }

            if (hotLead.equals("notInterested")) {

                backgroundColor.add("red");

                values.add(leadStatusMap.get(LeadStatus.notInterested));

            }

            if (hotLead.equals("followUp")) {

                backgroundColor.add("#9ACD32");

                values.add(leadStatusMap.get(LeadStatus.followUp));

            }

            if (hotLead.equals("finalized")) {

                backgroundColor.add("blue");

                values.add(leadStatusMap.get(LeadStatus.finalized));

            }

            if (hotLead.equals("HotLead")) {

                backgroundColor.add("green");

                values.add(hotLeadsMap.get("Green"));

            }

        }

        LOGGER.info("labelList" + labelList);

        LOGGER.info("backgroundColor" + backgroundColor);

        LOGGER.info("labelsChartLead" + labels);

        LeadStatusData data = new LeadStatusData();

        data.setData(values);

        data.setBackgroundColor(backgroundColor);

        data.setLabel("DataSet 1");

        PieLables label = new PieLables();

        label.setFontColor("black");

        label.setFontSize(15);

        Legend legend = new Legend();

        legend.setLabels(label);

        legend.setPosition("left");

        List<LeadStatusData> dataList = new ArrayList<>();

        dataList.add(data);

        DataLeadModel datasets = new DataLeadModel();

        datasets.setDatasets(dataList);

        datasets.setLabels(hotLeads);

        OptionModel om = new OptionModel();

        om.setLegend(legend);

        cm.setType("pie");

        cm.setData(datasets);

        cm.setOptions(om);

        return cm;

    }

    /**

     * When the user has picked a date range, follow-up leads must respect it too; otherwise (no range)

     * follow-ups stay undated, as before. Used by both the lead list and the CSV download so they agree.

     */

    private static List<Lead> withinCreatedRange(List<Lead> leads, LocalDateTime start, LocalDateTime end) {

        if (start == null) {

            return leads;

        }

        return leads.stream()

                .filter(l -> l.getCreatedTimestamp() != null

                        && !l.getCreatedTimestamp().isBefore(start)

                        && (end == null || !l.getCreatedTimestamp().isAfter(end)))

                .collect(Collectors.toList());

    }

    @RequestMapping(value = "/downloadDateWiseLead", method = RequestMethod.GET)

    public ResponseEntity<?> getdownloadLeadDataByDate(HttpServletRequest request,

                                                       @RequestParam(name = "leadStatus", required = false, defaultValue = "All") List<LeadStatus> leadStatus,

                                                       @RequestParam(name = "color", required = false, defaultValue = "All") List<String> color,

                                                       @RequestParam(name = "startDate", required = false) LocalDate startDate,

                                                       @RequestParam(name = "endDate", required = false) LocalDate endDate,

                                                       @RequestParam(name = "searchTerm", required = false, defaultValue = "") String searchTerm) throws Exception {

        LoginDetails fofoDetails = cookiesProcessor.getCookiesObject(request);

        String emailId = fofoDetails.getEmailId();

        LocalDateTime startDateTime = startDate != null ? startDate.atStartOfDay() : null;

        LocalDateTime endDateTime = endDate != null ? endDate.atTime(23, 59, 59) : null;

        List<Lead> leads = new ArrayList<>();

        AuthUser authUser = authRepository.selectByEmailOrMobile(emailId);

        if (leadStatus.contains(LeadStatus.All)) {

            leadStatus.add(LeadStatus.pending);

            leadStatus.add(LeadStatus.notInterested);

            leadStatus.add(LeadStatus.finalized);

            // NOTE: do NOT add followUp here. Follow-ups are pulled separately (undated) below,

            // mirroring the on-screen view. Adding it here made the date query get skipped so the

            // download collapsed to follow-ups only (original bug).

        }

        if (color.contains("All")) {

            color.add("yellow");

            color.add("green");

        }

        Map<Integer, List<LeadActivity>> leadActivityMap = new HashMap<>();

        boolean authPositon = this.isFieldSales(authUser.getId());

        List<Integer> authUserIds = null;

        List<Lead> leadFollowUp = new ArrayList<>();

        if (authPositon) {

            authUserIds = authService.getAllReportees(authUser.getId());

            authUserIds.add(authUser.getId());

            if (startDateTime == null) {

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndUpdatedTimestampAndAuthIds(leadStatus, authUserIds, color, LocalDateTime.now().minusMonths(1));

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectByAssignAuthIdsAndStatus(authUserIds, LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

            } else {

                // Date range: non-follow-up statuses created within [start, end], PLUS all follow-ups

                // (undated) — mirrors the on-screen view so the CSV matches what the user sees.

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndBetweenCreatedDateAndAuthIds(leadStatus, authUserIds, color, startDateTime, endDateTime);

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectByAssignAuthIdsAndStatus(authUserIds, LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

            }

        } else {

            if (startDateTime == null) {

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndUpdatedTimestamp(leadStatus, color, LocalDateTime.now().minusMonths(1));

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectAllByStatus(LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

            } else {

                // Date range: non-follow-up statuses created within [start, end], PLUS all follow-ups

                // (undated) — mirrors the on-screen view.

                if (!leadStatus.contains(LeadStatus.followUp)) {

                    leads = leadRepository.selectAllByColorStatusAndBetweenCreatedDate(leadStatus, color, startDateTime, endDateTime);

                }

                if (leadStatus.contains(LeadStatus.followUp) || leadStatus.contains(LeadStatus.All)) {

                    leadFollowUp = leadRepository.selectAllByStatus(LeadStatus.followUp);

                    leads.addAll(withinCreatedRange(leadFollowUp, startDateTime, endDateTime));

                }

            }

        }

        // Build activity map and auth map (SAME AS VIEW ENDPOINT)

        // Guard both lookups on a non-empty result: an empty list would generate an invalid

        // "IN ()" and 500 (e.g. a date range with no leads), so an empty range yields an empty CSV.

        Map<Integer, AuthUser> authIdAndAuthUserMap = new HashMap<>();

        if (!leads.isEmpty()) {

            List<Integer> leadIds = leads.stream().map(Lead::getId).collect(Collectors.toList());

            leadActivityMap = leadActivityRepository.selectAllByleadIds(leadIds).stream()

                    .collect(Collectors.groupingBy(LeadActivity::getLeadId));

            List<Integer> authIds = leads.stream().map(Lead::getAssignTo).collect(Collectors.toList());

            authIdAndAuthUserMap = authRepository.selectByIds(authIds).stream()

                    .collect(Collectors.toMap(AuthUser::getId, x -> x));

        }

        // Build CSV rows (USE DIRECT LEAD PROPERTIES - NO ACTIVITY FALLBACK)

        List<List<?>> rows = new ArrayList<>();

        // Mirror the on-screen table's search box: keep only leads whose exported fields contain the term,

        // so the CSV matches what the user is looking at.

        if (searchTerm != null && !searchTerm.trim().isEmpty()) {

            String term = searchTerm.trim().toLowerCase();

            leads = leads.stream().filter(l -> Stream.of(l.getId(), l.getSource(), l.getFullName(), l.getOutLetName(),

                            l.getLeadMobile(), l.getAddress(), l.getCity(), l.getState(), l.getCreatedBy(), l.getStatus(), l.getColor())

                    .anyMatch(v -> v != null && String.valueOf(v).toLowerCase().contains(term)))

                    .collect(Collectors.toList());

        }

        for (Lead lead : leads) {

            List<LeadActivity> activities = leadActivityMap.get(lead.getId());

            // Use FIRST activity's schedule timestamp if exists

            String scheduleTimestamp = (activities != null && !activities.isEmpty())

                    ? FormattingUtils.format(activities.get(0).getSchelduleTimestamp())

                    : "";

            // DIRECTLY use Lead properties (NO fallback to activity)

            Double potential = lead.getPotential();

            String outLetName = lead.getOutLetName() != null ? lead.getOutLetName() : "";

            // Assignee name (blank if missing)

            String assignTo = "";

            if (authIdAndAuthUserMap.containsKey(lead.getAssignTo())) {

                AuthUser user = authIdAndAuthUserMap.get(lead.getAssignTo());

                assignTo = (user.getFirstName() + " " + user.getLastName()).trim();

            }

            rows.add(Arrays.asList(

                    lead.getId(),

                    lead.getSource(),

                    lead.getFullName(),

                    outLetName,

                    potential,

                    lead.getLeadMobile(),

                    lead.getAddress() != null ? lead.getAddress() : "",

                    lead.getCity() != null ? lead.getCity() : "",

                    lead.getState() != null ? lead.getState() : "",

                    lead.getCreatedBy() != null ? lead.getCreatedBy() : "",

                    lead.getStatus().toString(),

                    FormattingUtils.format(lead.getCreatedTimestamp()),

                    FormattingUtils.format(lead.getUpdatedTimestamp()),

                    scheduleTimestamp,

                    lead.getClosureTimestamp() != null

                            ? FormattingUtils.format(lead.getClosureTimestamp())

                            : "",  // Handle null closure

                    assignTo,

                    lead.getColor() != null ? lead.getColor() : ""

            ));

        }

        // Generate CSV response

        ByteArrayOutputStream baos = FileUtil.getCSVByteStream(

                Arrays.asList("Id", "Source", "Partner Name", "Outlet Name", "Potential", "Mobile", "Address", "City", "State",

                        "Created By", "Status", "Created On", "Updated On", "Scheduled Timestamp",

                        "Closure Timestamp", "Assign To", "Color"),

                rows

        );

        return orderService.downloadReportInCsv(baos, rows, "LeadDetails_");

    }

    @RequestMapping(value = "/getClosedLead", method = RequestMethod.GET)

    public String getClosedLead(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, @RequestParam(name = "searchTerm", required = false, defaultValue = "") String searchTerm, Model model) throws Exception {

        List<AuthUser> authUsers = authRepository.selectAllActiveUser();

        List<Lead> leads = null;

        long size = 0;

        leads = leadRepository.selectAllByStatus(status, offset, limit);

        size = leadRepository.selectCountByStatus(status);

        if (!leads.isEmpty()) {

            List<Integer> authIds = new ArrayList<>();

            for (Lead lead : leads) {

                authIds.add(lead.getAssignTo());

            }

            Map<Integer, AuthUser> authIdAndAuthUserMap = authRepository.selectByIds(authIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

            model.addAttribute("leads", leads);

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("searchTerm", searchTerm);

            model.addAttribute("authIdAndAuthUserMap", authIdAndAuthUserMap);

            model.addAttribute("url", "/getPaginatedClosedLeads");

            if (leads.size() < limit) {

                model.addAttribute("end", offset + leads.size());

            } else {

                model.addAttribute("end", offset + limit);

            }

        } else {

            model.addAttribute("lead", leads);

            model.addAttribute("size", size);

        }

        model.addAttribute("authUsers", authUsers);

        return "lead-close";

    }

    @RequestMapping(value = "/getPaginatedClosedLeads", method = RequestMethod.GET)

    public String getPaginatedClosedLeads(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, Model model) throws ProfitMandiBusinessException {

        List<Lead> leads = null;

        leads = leadRepository.selectAllByStatus(status, offset, limit);

        if (!leads.isEmpty()) {

            List<Integer> authIds = new ArrayList<>();

            for (Lead lead : leads) {

                authIds.add(lead.getAssignTo());

            }

            Map<Integer, AuthUser> authIdAndAuthUserMap = authRepository.selectByIds(authIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

            model.addAttribute("authIdAndAuthUserMap", authIdAndAuthUserMap);

            model.addAttribute("leads", leads);

            model.addAttribute("url", "/getPaginatedClosedLeads");

            List<Integer> allLeadIds = leads.stream().map(Lead::getId).collect(Collectors.toList());

            Map<Integer, LeadLiveLocation> geoLocationMap = leadLiveLocationRepository.selectByLeadIds(allLeadIds).stream()

                    .collect(Collectors.toMap(LeadLiveLocation::getLeadId, x -> x, (a, b) -> a));

            model.addAttribute("geoLocationMap", geoLocationMap);

        } else {

            model.addAttribute("leads", leads);

            model.addAttribute("geoLocationMap", new HashMap<>());

        }

        return "lead-close-paginated";

    }

    @RequestMapping(value = "/searchLeads")

    public String getClosedLeads(HttpServletRequest request, @RequestParam(name = "offset", defaultValue = "0") int offset, @RequestParam(name = "limit", defaultValue = "10") int limit, @RequestParam(name = "searchTerm", required = false, defaultValue = "") String searchTerm, Model model) throws ProfitMandiBusinessException {

        List<AuthUser> authUsers = authRepository.selectAllActiveUser();

        List<Lead> leads = null;

        long size = 0;

        if (!(searchTerm.equals(""))) {

            leads = leadRepository.selectBySearchTerm(status, searchTerm, offset, limit);

            size = leadRepository.selectCountByStatus(status);

            Map<Integer, AuthUser> authIdAndAuthUserMap = new HashMap<>();

            List<Integer> authIds = new ArrayList<>();

            if (!(leads.size() == 0)) {

                for (Lead lead : leads) {

                    authIds.add(lead.getAssignTo());

                }

                authIdAndAuthUserMap = authRepository.selectByIds(authIds).stream().collect(Collectors.toMap(x -> x.getId(), x -> x));

                if (leads.size() < limit) {

                    model.addAttribute("end", offset + leads.size());

                } else {

                    model.addAttribute("end", offset + limit);

                }

            } else {

                model.addAttribute("end", offset + limit);

            }

            model.addAttribute("leads", leads);

            model.addAttribute("authIdAndAuthUserMap", authIdAndAuthUserMap);

            model.addAttribute("start", offset + 1);

            model.addAttribute("size", size);

            model.addAttribute("searchTerm", searchTerm);

        } else {

            leads = leadRepository.selectAllByStatus(status, offset, limit);

            size = leadRepository.selectCountByStatus(status);