Subversion Repositories SmartDukaan

package com.spice.profitmandi.web.controller;

import com.google.gson.Gson;

import com.google.gson.reflect.TypeToken;

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.common.web.util.ResponseSender;

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

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

import com.spice.profitmandi.dao.entity.logistics.PublicHolidays;

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

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

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

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

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

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

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

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

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

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

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

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.ui.Model;

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

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

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

import javax.servlet.http.HttpServletRequest;

import java.lang.reflect.Type;

import java.time.DayOfWeek;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.YearMonth;

import java.time.format.DateTimeFormatter;

import java.util.*;

import java.util.stream.Collectors;

@Controller

@Transactional(rollbackFor = Throwable.class)

public class BeatPlanController {

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

	private static final String[] BEAT_COLORS = {

			"#3498DB", "#E74C3C", "#2ECC71", "#9B59B6", "#F39C12",

			"#1ABC9C", "#E67E22", "#34495E", "#16A085", "#C0392B"

	};

	@Autowired

	private CsService csService;

	@Autowired

	private AuthRepository authRepository;

	// Emails that bypass hierarchy and role gates — single source of truth.

	private static final Set<String> SUPER_ADMIN_EMAILS = new HashSet<>(Arrays.asList(

			"tarun.verma@smartdukaan.com"

	));

	@Autowired

	private com.spice.profitmandi.service.AuthService authService;

	@Autowired

	private com.spice.profitmandi.dao.repository.cs.PositionRepository positionRepository;

	@Autowired

	private RetailerService retailerService;

	@Autowired

	private BeatRepository beatRepository;

	@Autowired

	private BeatRouteRepository beatRouteRepository;

	@Autowired

	private BeatScheduleRepository beatScheduleRepository;

	@Autowired

	private LeadRouteRepository leadRouteRepository;

	@Autowired

	private com.spice.profitmandi.dao.service.BeatPlanQueryService beatPlanQueryService;

	@Autowired

	private AuthUserLocationRepository authUserLocationRepository;

	@Autowired

	private LeadRepository leadRepository;

	@Autowired

	private PublicHolidaysRepository publicHolidaysRepository;

	@Autowired

	private com.spice.profitmandi.service.GeocodingService geocodingService;

	@Autowired

	private CookiesProcessor cookiesProcessor;

	@Autowired

	private ResponseSender responseSender;

	@Autowired

	private FofoStoreRepository fofoStoreRepository;

	@Autowired

	private com.spice.profitmandi.dao.repository.logistics.CompanyOfficeRepository companyOfficeRepository;

	@GetMapping(value = "/beatPlan")

	public String beatPlan(HttpServletRequest request, Model model) throws ProfitMandiBusinessException {

		model.addAttribute("escalationTypes", visibleLevelsFor(request));

		return "beat-plan";

	}

	@Autowired

	private com.spice.profitmandi.dao.repository.dtr.LeadLiveLocationRepository leadLiveLocationRepositoryAuto;

	@Autowired

	private com.spice.profitmandi.dao.repository.dtr.LeadActivityRepository leadActivityRepositoryAuto;

	@Autowired

	private com.spice.profitmandi.dao.repository.dtr.UserRepository userRepositoryAuto;

	@Autowired

	private com.spice.profitmandi.common.web.client.RestClient restClientAuto;

	@Autowired

	private com.spice.profitmandi.dao.repository.auth.LocationTrackingRepository locationTrackingRepositoryAuto;

	@Autowired

	private com.spice.profitmandi.dao.repository.dtr.BeatDeferredVisitRepository beatDeferredVisitRepository;

	private static Double parseDoubleOrNull(String s) {

		if (s == null || s.trim().isEmpty()) return null;

		try {

			return Double.parseDouble(s.trim());

		} catch (NumberFormatException e) {

			return null;

		}

	}

	private static double haversineKm(double lat1, double lng1, double lat2, double lng2) {

		double R = 6371;

		double dLat = Math.toRadians(lat2 - lat1);

		double dLng = Math.toRadians(lng2 - lng1);

		double a = Math.sin(dLat / 2) * Math.sin(dLat / 2)

				+ Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2))

				* Math.sin(dLng / 2) * Math.sin(dLng / 2);

		double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));

		return R * c;

	}

    // Mirrors the JS recalcDay() formula. Used by schedule/repeat endpoints

    // which create fresh BeatSchedule rows — they need to fill totals from the

    // existing beat_route table, not from anything the client posted.

    // Returns {totalKm, totalMins}.

    private double[] computeDayTotals(int beatId, int dayNumber, String endAction) {

        Beat beat = beatRepository.selectById(beatId);

        if (beat == null) return new double[]{0d, 0d};

        List<BeatRoute> dayRoutes = beatRouteRepository.selectByBeatId(beatId).stream()

                .filter(r -> r.getDayNumber() == dayNumber)

                .sorted(java.util.Comparator.comparingInt(BeatRoute::getSequenceOrder))

                .collect(Collectors.toList());

        if (dayRoutes.isEmpty()) return new double[]{0d, 0d};

        List<Integer> fofoIds = dayRoutes.stream().map(BeatRoute::getFofoId).distinct().collect(Collectors.toList());

        Map<Integer, FofoStore> storeMap = new HashMap<>();

        try {

            for (FofoStore fs : fofoStoreRepository.selectByRetailerIds(fofoIds)) {

                storeMap.put(fs.getId(), fs);

            }

        } catch (Exception ignored) { /* fall through with empty map */ }

        double ROAD_FACTOR = 1.3;

        double AVG_SPEED = 30.0; // km/h

        int VISIT_MINS = 30;

        Double prevLat = parseDoubleOrNull(beat.getStartLatitude());

        Double prevLng = parseDoubleOrNull(beat.getStartLongitude());

        double totalKm = 0d;

        for (BeatRoute r : dayRoutes) {

            FofoStore fs = storeMap.get(r.getFofoId());

            if (fs == null) continue;

            Double curLat = parseDoubleOrNull(fs.getLatitude());

            Double curLng = parseDoubleOrNull(fs.getLongitude());

            if (prevLat != null && prevLng != null && curLat != null && curLng != null) {

                totalKm += haversineKm(prevLat, prevLng, curLat, curLng) * ROAD_FACTOR;

            }

            if (curLat != null && curLng != null) {

                prevLat = curLat;

                prevLng = curLng;

            }

        }

        // Return-home leg only when end_action='HOME'

        if ("HOME".equalsIgnoreCase(endAction)) {

            Double homeLat = parseDoubleOrNull(beat.getStartLatitude());

            Double homeLng = parseDoubleOrNull(beat.getStartLongitude());

            if (prevLat != null && prevLng != null && homeLat != null && homeLng != null) {

                totalKm += haversineKm(prevLat, prevLng, homeLat, homeLng) * ROAD_FACTOR;

            }

        }

        double totalMins = (totalKm / AVG_SPEED) * 60.0 + dayRoutes.size() * VISIT_MINS;

        return new double[]{Math.round(totalKm * 1000d) / 1000d, Math.round(totalMins)};

    }

	// ====================== ASSIGN VISIT ======================

	// Day View "Assign Visit" — lets an admin pick parties (stores) for a specific

	// auth user on a specific date and pushes them as visit tasks to the v2

	// /profitmandi-web/v2/beat-tracking/batch endpoint.

	// List of parties (stores) assigned to this auth user + their dtr.users.id.

	// When date+beatId are passed, each party is also tagged with:

	//   inBeat        = is this store part of the scheduled beat's route on that date

	//   existingAgendas[] = agendas already saved for this store on that date (so the

	//                       modal can pre-fill them and let the user refill rather than re-assign)

	@GetMapping(value = "/beatPlan/assignVisit/parties")

	public ResponseEntity<?> assignVisitParties(

			@RequestParam int authUserId,

			@RequestParam(required = false) String date,

			@RequestParam(required = false) Integer beatId) throws Exception {

		AuthUser au = authRepository.selectById(authUserId);

		if (au == null) return responseSender.badRequest("Auth user not found");

		// Map auth_user → dtr.users via email

		Integer dtrUserId = null;

		try {

			com.spice.profitmandi.dao.entity.dtr.User dtrUser =

					userRepositoryAuto.selectByEmailId(au.getEmailId());

			if (dtrUser != null) dtrUserId = dtrUser.getId();

		} catch (Exception ignored) {

		}

		// Parse optional date

		LocalDate parsedDate = null;

		if (date != null && !date.isEmpty()) {

			try {

				parsedDate = LocalDate.parse(date);

			} catch (Exception ignored) {

			}

		}

		// Build (fofoId → dayNumber) of partners already in the beat's scheduled route for this date

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

		if (beatId != null && parsedDate != null) {

			final LocalDate dateF = parsedDate; // capture for lambda (parsedDate is reassigned earlier so not effectively final)

			List<BeatSchedule> schedules = beatScheduleRepository.selectByBeatId(beatId);

			BeatSchedule match = schedules.stream()

					.filter(s -> s.getStartDate() != null && s.getStartDate().equals(dateF))

					.findFirst().orElse(null);

			if (match != null) {

				List<BeatRoute> routes = beatRouteRepository.selectByBeatId(beatId);

				routes.stream()

						.filter(r -> r.getDayNumber() == match.getDayNumber() && r.isActive())

						.forEach(r -> inBeatFofoIds.add(r.getFofoId()));

			}

		}

		// Build (fofoId → existingAgendas) and (fofoId → existingDescription) from

		// any already-saved location_tracking rows for this user on this date.

		// Agenda is stored as task_name = "agenda1, agenda2 | OutletName"

		// so we split on " | " to peel the outlet suffix off, then split agendas by ", ".

		// Description is stored on task_description (free text).

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

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

		Map<Integer, Integer> existingTrackingIdByFofo = new HashMap<>();

		if (dtrUserId != null && parsedDate != null) {

			List<com.spice.profitmandi.dao.entity.auth.LocationTracking> existing =

					locationTrackingRepositoryAuto.findByUserAndDate(dtrUserId, parsedDate);

			for (com.spice.profitmandi.dao.entity.auth.LocationTracking lt : existing) {

				if (!"franchisee-visit".equals(lt.getTaskType())) continue;

				if (existingAgendaByFofo.containsKey(lt.getTaskId())) continue; // first wins

				String taskName = lt.getTaskName() == null ? "" : lt.getTaskName();

				String agendaPart = taskName;

				int pipeIdx = taskName.lastIndexOf(" | ");

				if (pipeIdx > 0) agendaPart = taskName.substring(0, pipeIdx);

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

				for (String a : agendaPart.split(",")) {

					String trimmed = a.trim();

					if (!trimmed.isEmpty()) agendas.add(trimmed);

				}

				existingAgendaByFofo.put(lt.getTaskId(), agendas);

				existingDescByFofo.put(lt.getTaskId(), lt.getTaskDescription() != null ? lt.getTaskDescription() : "");

				existingTrackingIdByFofo.put(lt.getTaskId(), lt.getId());

			}

		}

		Map<Integer, List<Integer>> mapping = csService.getAuthUserIdPartnerIdMapping();

		List<Integer> fofoIds = mapping.get(authUserId);

		List<Map<String, Object>> parties = new ArrayList<>();

		if (fofoIds != null && !fofoIds.isEmpty()) {

			List<FofoStore> stores = fofoStoreRepository.selectByRetailerIds(fofoIds);

			Map<Integer, CustomRetailer> retailerMap = retailerService.getFofoRetailers(fofoIds);

			for (FofoStore store : stores) {

				if (!store.isActive() || store.isClosed()) continue;

				CustomRetailer retailer = retailerMap.get(store.getId());

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

				p.put("fofoStoreId", store.getId());

				p.put("code", store.getCode());

				p.put("outletName", store.getOutletName() != null ? store.getOutletName()

						: (retailer != null ? retailer.getBusinessName() : "Store #" + store.getId()));

				p.put("latitude", store.getLatitude());

				p.put("longitude", store.getLongitude());

				p.put("city", retailer != null && retailer.getAddress() != null ? retailer.getAddress().getCity() : null);

				p.put("inBeat", inBeatFofoIds.contains(store.getId()));

				p.put("existingAgendas", existingAgendaByFofo.getOrDefault(store.getId(), new ArrayList<>()));

				p.put("existingDescription", existingDescByFofo.getOrDefault(store.getId(), ""));

				p.put("existingTrackingId", existingTrackingIdByFofo.get(store.getId()));

				parties.add(p);

			}

			// In-beat first, then by code

			parties.sort((a, b) -> {

				boolean ai = Boolean.TRUE.equals(a.get("inBeat"));

				boolean bi = Boolean.TRUE.equals(b.get("inBeat"));

				if (ai != bi) return ai ? -1 : 1;

				return String.valueOf(a.get("code")).compareToIgnoreCase(String.valueOf(b.get("code")));

			});

		}

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

		result.put("dtrUserId", dtrUserId);

		result.put("authUserId", authUserId);

		result.put("userName", au.getFirstName() + " " + au.getLastName());

		result.put("parties", parties);

		result.put("agendaOptions", com.spice.profitmandi.dao.enumuration.dtr.VisitAgenda.labels());

		return responseSender.ok(result);

	}

	// Submit assignment — accepts a JSON body, builds the v2 payload, posts it

	@PostMapping(value = "/beatPlan/assignVisit/submit")

	public ResponseEntity<?> assignVisitSubmit(

			HttpServletRequest request,

			@org.springframework.web.bind.annotation.RequestBody Map<String, Object> body) throws Exception {

		Integer authUserId = body.get("authUserId") != null ? ((Number) body.get("authUserId")).intValue() : null;

		String planDate = (String) body.get("planDate");

		List<Map<String, Object>> selected = (List<Map<String, Object>>) body.get("parties");

		if (authUserId == null || planDate == null || selected == null || selected.isEmpty()) {

			return responseSender.badRequest("authUserId, planDate and parties are required");

		}

		// Defence-in-depth: Assign Visit is L2-and-above only (mirrors the hidden UI).

		if (!canEditBeat(currentUser(request))) {

			return responseSender.badRequest("Assigning a visit is restricted to L2 and above.");

		}

		AuthUser au = authRepository.selectById(authUserId);

		if (au == null) return responseSender.badRequest("Auth user not found");

		// Map auth → dtr.users.id (this is the userId the v2 endpoint expects)

		com.spice.profitmandi.dao.entity.dtr.User dtrUser;

		try {

			dtrUser = userRepositoryAuto.selectByEmailId(au.getEmailId());

		} catch (Exception e) {

			return responseSender.badRequest("Failed to look up dtr.users for this auth user");

		}

		if (dtrUser == null) {

			return responseSender.badRequest("No dtr.users record found for auth user " + authUserId);

		}

		int dtrUserId = dtrUser.getId();

		// Persist directly via the shared DAO — mirrors BeatTrackingController.createBatch

		// in profitmandi-web. We can't autowire a controller across WARs, but

		// LocationTrackingRepository lives in profitmandi-dao and is shared.

		LocalDate taskDate;

		try {

			taskDate = LocalDate.parse(planDate);

		} catch (Exception e) {

			return responseSender.badRequest("Invalid planDate (expected yyyy-MM-dd): " + planDate);

		}

		// Existing rows for this user on this date — keyed by fofoStoreId.

		// If a row already exists for a party, we UPDATE its agenda instead of

		// creating a duplicate (this is the "refill agenda" path for already-

		// assigned parties).

		Map<Integer, com.spice.profitmandi.dao.entity.auth.LocationTracking> existingByFofo = new HashMap<>();

		for (com.spice.profitmandi.dao.entity.auth.LocationTracking lt :

				locationTrackingRepositoryAuto.findByUserAndDate(dtrUserId, taskDate)) {

			if (!"franchisee-visit".equals(lt.getTaskType())) continue;

			existingByFofo.putIfAbsent(lt.getTaskId(), lt);

		}

		// Defence-in-depth: Assign Visit is only valid for today's run. Hiding the

		// button on the UI isn't enough — block at the API too.

		if (!taskDate.equals(LocalDate.now())) {

			return responseSender.badRequest("Visits can only be assigned for today's date (" + LocalDate.now() + ")");

		}

		LocalDateTime now = LocalDateTime.now();

		int createdCount = 0, updatedCount = 0;

		for (Map<String, Object> p : selected) {

			Integer fofoStoreId = ((Number) p.get("fofoStoreId")).intValue();

			String outletName = (String) p.get("outletName");

			String lat = (String) p.get("latitude");

			String lng = (String) p.get("longitude");

			String description = (String) p.get("description");

			if (description != null) description = description.trim();

			if (description == null) description = "";

			// Multi-agenda: accept agendas[] (new format) or fall back to agenda (legacy single)

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

			Object rawAgendas = p.get("agendas");

			if (rawAgendas instanceof List) {

				for (Object o : (List<?>) rawAgendas) {

					if (o != null) {

						String s = String.valueOf(o).trim();

						if (!s.isEmpty()) agendas.add(s);

					}

				}

			}

			if (agendas.isEmpty()) {

				String single = (String) p.get("agenda");

				if (single != null && !single.trim().isEmpty()) agendas.add(single.trim());

			}

			if (agendas.isEmpty()) agendas.add("Visit");

			String agendaJoined = String.join(", ", agendas);

			String visitLocation = (lat != null && lng != null && !lat.isEmpty() && !lng.isEmpty())

					? (lat + "," + lng) : "0.0000,0.0000";

			String displayName = (outletName != null && !outletName.isEmpty()) ? outletName : ("Store #" + fofoStoreId);

			String newTaskName = agendaJoined + " | " + displayName;

			com.spice.profitmandi.dao.entity.auth.LocationTracking existing = existingByFofo.get(fofoStoreId);

			if (existing != null) {

				// Refill — agenda (task_name), description, and visit location change

				existing.setTaskName(newTaskName);

				existing.setTaskDescription(description);

				existing.setVisitLocation(visitLocation);

				existing.setUpdatedTimestamp(now);

				locationTrackingRepositoryAuto.persist(existing);

				updatedCount++;

				continue;

			}

			com.spice.profitmandi.dao.entity.auth.LocationTracking row =

					new com.spice.profitmandi.dao.entity.auth.LocationTracking();

			row.setUserId(dtrUserId);

			row.setDeviceId("0");

			row.setTaskId(fofoStoreId);

			row.setTaskDate(taskDate);

			row.setTaskName(newTaskName);

			row.setTaskDescription(description);

			row.setTaskType("franchisee-visit");

			row.setMarkType(String.valueOf(ProfitMandiConstants.MARK_TYPE.PENDING));

			row.setAddress("");

			row.setVisitLocation(visitLocation);

			row.setCheckInLatLng("0.0000,0.0000");

			row.setCheckOutLatLng("0.0000,0.0000");

			row.setCheckInTime(java.time.LocalTime.MIDNIGHT);

			row.setCheckOutTime(java.time.LocalTime.MIDNIGHT);

			row.setTransitTime(java.time.LocalTime.MIDNIGHT);

			row.setTimeSpent(java.time.LocalTime.MIDNIGHT);

			row.setEstimatedTime(java.time.LocalTime.MIDNIGHT);

			row.setSessionStartTime(java.time.LocalTime.MIDNIGHT);

			row.setSessionEndTime(java.time.LocalTime.MIDNIGHT);

			row.setTotalDistance("0.0");

			row.setStatus(false);

			row.setCreatedTimestamp(now);

			row.setUpdatedTimestamp(now);

			// Do NOT try/catch this — if persist throws, let it propagate so

			// @Transactional(rollbackFor = Throwable.class) rolls back cleanly.

			locationTrackingRepositoryAuto.persist(row);

			createdCount++;

		}

		LOGGER.info("assignVisit dtrUserId={} created={} updated={}", dtrUserId, createdCount, updatedCount);

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

		result.put("status", true);

		result.put("createdCount", createdCount);

		result.put("updatedCount", updatedCount);

		result.put("dtrUserId", dtrUserId);

		StringBuilder msg = new StringBuilder();

		if (createdCount > 0)

			msg.append(createdCount).append(" new visit").append(createdCount == 1 ? "" : "s").append(" assigned");

		if (updatedCount > 0) {

			if (msg.length() > 0) msg.append(", ");

			msg.append(updatedCount).append(" existing agenda").append(updatedCount == 1 ? "" : "s").append(" refilled");

		}

		msg.append(" for ").append(au.getFirstName()).append(" ").append(au.getLastName());

		result.put("message", msg.toString());

		return responseSender.ok(result);

	}

	// ====================== DEFERRED PARTNERS ======================

	// Heads review partners that weren't visited on their planned day and act on

	// them. The deferral lifecycle lives in user.beat_deferred_visit (separate

	// from the raw location_tracking event log). Detection = explicit

	// (mark_type='DEFERRED') + derived (planned beat_route minus completed visits).

	// Page

	@GetMapping(value = "/beatPlan/deferredView")

	public String deferredView(HttpServletRequest request, Model model) throws ProfitMandiBusinessException {

		model.addAttribute("escalationTypes", visibleLevelsFor(request));

		return "beat-plan-deferred";

	}

	// List (syncs the table first, then returns the head's downline deferrals).

	@GetMapping(value = "/beatPlan/deferred")

	public ResponseEntity<?> deferredList(

			HttpServletRequest request,

			@RequestParam(required = false) String startDate,

			@RequestParam(required = false) String endDate) throws Exception {

		LocalDate start, end;

		try {

			start = (startDate == null || startDate.isEmpty()) ? LocalDate.now().minusDays(7) : LocalDate.parse(startDate);

			end = (endDate == null || endDate.isEmpty()) ? LocalDate.now() : LocalDate.parse(endDate);

		} catch (Exception e) {

			return responseSender.badRequest("Invalid date — expected yyyy-MM-dd");

		}

		LoginDetails ld = cookiesProcessor.getCookiesObject(request);

		AuthUser me = (ld != null) ? authRepository.selectByEmailOrMobile(ld.getEmailId()) : null;

		if (me == null) return responseSender.unauthorized("Not logged in");

		// PURE READ. Deferrals are persisted at the write point (BeatTrackingController,

		// when mark_type='DEFERRED' is recorded) — this endpoint never writes.

		List<BeatDeferredVisit> source;

		if (isSuperAdmin(me)) {

			source = beatDeferredVisitRepository.selectByDateRange(start, end);

		} else {

			Set<Integer> downline = new HashSet<>(authService.getAllReportees(me.getId()));

			downline.add(me.getId());

			source = beatDeferredVisitRepository.selectByAuthUserIdsAndDateRange(new ArrayList<>(downline), start, end);

		}

		List<BeatDeferredVisit> rows = source.stream()

				.filter(r -> "DEFERRED".equals(r.getStatus()))

				.collect(Collectors.toList());

		// ---- nextScheduledDate (info only: does a future run already cover it?) ----

		// Only meaningful for partner visits (leads aren't on beat_route). Purely a

		// hint — the row stays actionable even when auto-covered, since the next run

		// could be far off.

		Map<Integer, Map<Integer, LocalDate>> coverCache = new HashMap<>();

		Map<Integer, LocalDate> nextByRowId = new HashMap<>();

		for (BeatDeferredVisit r : rows) {

			if (!"franchisee-visit".equals(r.getTaskType())) continue;

			Map<Integer, LocalDate> cover = coverCache.computeIfAbsent(r.getAuthUserId(), this::computeFutureCover);

			LocalDate next = cover.get(r.getFofoId());

			if (next != null) nextByRowId.put(r.getId(), next);

		}

		// ---- resolve user names (display name + type already denormalized on the row) ----

		Set<Integer> authIds = rows.stream().map(BeatDeferredVisit::getAuthUserId).collect(Collectors.toSet());

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

		if (!authIds.isEmpty())

			authRepository.selectByIds(new ArrayList<>(authIds)).forEach(u -> userMap.put(u.getId(), u));

		List<Map<String, Object>> out = new ArrayList<>();

		for (BeatDeferredVisit r : rows) {

			AuthUser u = userMap.get(r.getAuthUserId());

			boolean isLead = "lead".equalsIgnoreCase(r.getTaskType());

			boolean isOffice = "office-visit".equalsIgnoreCase(r.getTaskType());

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

			row.put("id", r.getId());

			row.put("authUserId", r.getAuthUserId());

			row.put("userName", u != null ? (u.getFirstName() + " " + u.getLastName()) : ("User #" + r.getAuthUserId()));

			row.put("fofoStoreId", r.getFofoId());

			row.put("name", r.getDisplayName() != null ? r.getDisplayName() : ("#" + r.getFofoId()));

			row.put("type", isLead ? "Lead" : (isOffice ? "Office" : "Visit"));

			row.put("deferredDate", r.getDeferredDate() != null ? r.getDeferredDate().toString() : null);

			row.put("reason", r.getReason());

			row.put("status", r.getStatus());

			LocalDate next = nextByRowId.get(r.getId());

			row.put("nextScheduledDate", next != null ? next.toString() : null);

			out.add(row);

		}

		out.sort((a, c) -> String.valueOf(a.get("deferredDate")).compareTo(String.valueOf(c.get("deferredDate"))));

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

		result.put("rows", out);

		result.put("startDate", start.toString());

		result.put("endDate", end.toString());

		return responseSender.ok(result);

	}

	// Head action on a deferral: reschedule (one-off visit, or into an existing

	// beat-day) or cancel. Never edits the beat template.

	@PostMapping(value = "/beatPlan/deferred/action")

	public ResponseEntity<?> deferredAction(

			HttpServletRequest request,

			@org.springframework.web.bind.annotation.RequestBody Map<String, Object> body) throws Exception {

		Integer deferredId = body.get("deferredId") != null ? ((Number) body.get("deferredId")).intValue() : null;

		String action = (String) body.get("action");

		String toDateStr = (String) body.get("toDate");

		if (deferredId == null || action == null)

			return responseSender.badRequest("deferredId and action are required");

		LoginDetails ld = cookiesProcessor.getCookiesObject(request);

		AuthUser me = (ld != null) ? authRepository.selectByEmailOrMobile(ld.getEmailId()) : null;

		if (me == null) return responseSender.unauthorized("Not logged in");

		BeatDeferredVisit d = beatDeferredVisitRepository.selectById(deferredId);

		if (d == null) return responseSender.badRequest("Deferred record not found");

		LocalDateTime now = LocalDateTime.now();

		if ("cancel".equalsIgnoreCase(action)) {

			d.setStatus(String.valueOf(ProfitMandiConstants.MARK_TYPE.CANCELLED));

			d.setActionBy(me.getId());

			d.setUpdatedTimestamp(now);

			// Optional cancel reason — overlay onto the reason column. Original

			// deferred-reason is preserved as a suffix so we keep the audit trail.

			String cancelReason = body.get("reason") != null ? String.valueOf(body.get("reason")).trim() : "";

			if (!cancelReason.isEmpty()) {

				String prev = d.getReason() != null ? d.getReason() : "";

				d.setReason("Cancelled: " + cancelReason + (prev.isEmpty() ? "" : " | Original: " + prev));

			}

			beatDeferredVisitRepository.persist(d);

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

			ok.put("status", true);

			ok.put("message", "Deferred visit cancelled");

			return responseSender.ok(ok);

		}

		// reschedule_oneoff | reschedule_beat

		if (toDateStr == null || toDateStr.isEmpty())

			return responseSender.badRequest("toDate is required to reschedule");

		LocalDate toDate;

		try {

			toDate = LocalDate.parse(toDateStr);

		} catch (Exception e) {

			return responseSender.badRequest("Invalid toDate (yyyy-MM-dd)");

		}

		// Reschedule must land on a STRICTLY future date — today is already in progress.

		if (!toDate.isAfter(LocalDate.now()))

			return responseSender.badRequest("Reschedule date must be in the future (today is already in progress).");

		if ("reschedule_beat".equalsIgnoreCase(action)) {

			boolean hasBeat = beatRepository.selectActiveByAuthUserId(d.getAuthUserId()).stream()

					.flatMap(b -> beatScheduleRepository.selectByBeatId(b.getId()).stream())

					.anyMatch(s -> s.getStartDate() != null && s.getStartDate().equals(toDate));

			if (!hasBeat)

				return responseSender.badRequest("No beat is scheduled for this user on " + toDateStr + ". Pick another date or use a one-off visit.");

		}

		// Resolve dtr user, then create a PENDING task on toDate. Reuse the

		// denormalized name + type (works for both partner visits and leads — for

		// leads, looking up fofo_store would be the wrong id space). For visits we

		// still try to pull lat/lng for the visit location.

		Integer dtrId = resolveDtrId(d.getAuthUserId(), new HashMap<>());

		if (dtrId == null) return responseSender.badRequest("No dtr.users record for this sales person");

		boolean isLead = "lead".equalsIgnoreCase(d.getTaskType());

		boolean isOffice = "office-visit".equalsIgnoreCase(d.getTaskType());

		String visitLocation = "0.0000,0.0000";

		if (isOffice) {

			// Office stops resolve lat/lng from logistics.company_office.

			try {

				com.spice.profitmandi.dao.entity.logistics.CompanyOffice o = companyOfficeRepository.selectById(d.getFofoId());

				if (o != null) visitLocation = o.getLat() + "," + o.getLng();

			} catch (Exception ignored) {

			}

		} else if (!isLead) {

			try {

				List<FofoStore> ss = fofoStoreRepository.selectByRetailerIds(java.util.Collections.singletonList(d.getFofoId()));

				if (!ss.isEmpty()) {

					FofoStore fs = ss.get(0);

					if (fs.getLatitude() != null && fs.getLongitude() != null

							&& !fs.getLatitude().isEmpty() && !fs.getLongitude().isEmpty()) {

						visitLocation = fs.getLatitude() + "," + fs.getLongitude();

					}

				}

			} catch (Exception ignored) {

			}

		}

		String taskName = d.getDisplayName() != null ? d.getDisplayName()

				: ((d.getReason() != null ? d.getReason() : "Rescheduled") + " | #" + d.getFofoId());

		com.spice.profitmandi.dao.entity.auth.LocationTracking row = new com.spice.profitmandi.dao.entity.auth.LocationTracking();

		row.setUserId(dtrId);

		row.setDeviceId("0");

		row.setTaskId(d.getFofoId());

		row.setTaskDate(toDate);

		row.setTaskName(taskName);

		row.setTaskDescription("Rescheduled from " + d.getDeferredDate());

		row.setTaskType(d.getTaskType() != null ? d.getTaskType() : "franchisee-visit");

		row.setMarkType(String.valueOf(ProfitMandiConstants.MARK_TYPE.PENDING));

		row.setAddress("");

		row.setVisitLocation(visitLocation);

		row.setCheckInLatLng("0.0000,0.0000");

		row.setCheckOutLatLng("0.0000,0.0000");

		row.setCheckInTime(java.time.LocalTime.MIDNIGHT);

		row.setCheckOutTime(java.time.LocalTime.MIDNIGHT);

		row.setTransitTime(java.time.LocalTime.MIDNIGHT);

		row.setTimeSpent(java.time.LocalTime.MIDNIGHT);

		row.setEstimatedTime(java.time.LocalTime.MIDNIGHT);

		row.setSessionStartTime(java.time.LocalTime.MIDNIGHT);

		row.setSessionEndTime(java.time.LocalTime.MIDNIGHT);

		row.setTotalDistance("0.0");

		row.setStatus(false);

		row.setCreatedTimestamp(now);

		row.setUpdatedTimestamp(now);

		locationTrackingRepositoryAuto.persist(row);

		d.setStatus(String.valueOf(ProfitMandiConstants.MARK_TYPE.RESCHEDULED));

		d.setRescheduledToDate(toDate);

		d.setActionBy(me.getId());

		d.setUpdatedTimestamp(now);

		beatDeferredVisitRepository.persist(d);

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

		ok.put("status", true);

		ok.put("message", "Visit rescheduled to " + toDateStr);

		return responseSender.ok(ok);

	}

	// Drop a deferred item into a specific upcoming BEAT run (chosen from the beat

	// calendar). Lead → a lead_route row on that beat/date (renders as a lead stop).

	// Partner visit → appended to that beat's route for the date's day_number.

	// The beat plan calendar then shows it. Marks the deferral RESCHEDULED.

	@PostMapping(value = "/beatPlan/deferred/assignToBeat")

	public ResponseEntity<?> deferredAssignToBeat(

			HttpServletRequest request,

			@org.springframework.web.bind.annotation.RequestBody Map<String, Object> body) throws Exception {

		Integer deferredId = body.get("deferredId") != null ? ((Number) body.get("deferredId")).intValue() : null;

		Integer beatId = body.get("beatId") != null ? ((Number) body.get("beatId")).intValue() : null;

		String dateStr = (String) body.get("date");

		if (deferredId == null || beatId == null || dateStr == null)

			return responseSender.badRequest("deferredId, beatId and date are required");

		LoginDetails ld = cookiesProcessor.getCookiesObject(request);

		AuthUser me = (ld != null) ? authRepository.selectByEmailOrMobile(ld.getEmailId()) : null;

		if (me == null) return responseSender.unauthorized("Not logged in");

		LocalDate date;

		try {

			date = LocalDate.parse(dateStr);

		} catch (Exception e) {

			return responseSender.badRequest("Invalid date (yyyy-MM-dd)");

		}

		// Drop-onto-beat must land on a STRICTLY future date — today is already in progress.

		if (!date.isAfter(LocalDate.now()))

			return responseSender.badRequest("Pick a future date — today is already in progress.");

		BeatDeferredVisit d = beatDeferredVisitRepository.selectById(deferredId);

		if (d == null) return responseSender.badRequest("Deferred record not found");

		// A deferral can only move FORWARD — never onto the day it was deferred or earlier.

		if (d.getDeferredDate() != null && !date.isAfter(d.getDeferredDate())) {

			return responseSender.badRequest("A deferred item can only be moved to a date after "

					+ d.getDeferredDate() + " (it was deferred that day).");

		}

		Beat beat = beatRepository.selectById(beatId);

		if (beat == null) return responseSender.badRequest("Beat not found");

		// The beat must actually run on the chosen date — get that run's day number.

		BeatSchedule sched = beatScheduleRepository.selectByBeatId(beatId).stream()

				.filter(s -> s.getStartDate() != null && s.getStartDate().equals(date))

				.findFirst().orElse(null);

		if (sched == null) return responseSender.badRequest("That beat is not scheduled on " + dateStr);

		LocalDateTime now = LocalDateTime.now();

		boolean isLead = "lead".equalsIgnoreCase(d.getTaskType());

		if (isLead) {

			// Avoid duplicating the same lead on the same beat/date

			boolean exists = leadRouteRepository.selectByBeatId(beatId).stream()

					.anyMatch(lr -> lr.getLeadId() == d.getFofoId()

							&& date.equals(lr.getScheduleDate())

							&& !"CANCELLED".equals(lr.getStatus()));

			if (!exists) {

				LeadRoute lr = new LeadRoute();

				lr.setBeatId(beatId);

				lr.setLeadId(d.getFofoId());

				lr.setScheduleDate(date);

				lr.setSequenceOrder(9999); // append; planner can reorder

				lr.setStatus("APPROVED");

				lr.setApprovedBy(me.getId());

				lr.setApprovedTimestamp(now);

				lr.setCreatedTimestamp(now);

				lr.setUpdatedTimestamp(now);

				leadRouteRepository.persist(lr);

			}

		} else {

			// Partner visit → append to that beat's route for the date's day number,

			// if not already present on that day.

			boolean exists = beatRouteRepository.selectByBeatId(beatId).stream()

					.anyMatch(r -> r.getFofoId() == d.getFofoId() && r.getDayNumber() == sched.getDayNumber() && r.isActive());

			if (!exists) {

				int nextSeq = beatRouteRepository.selectByBeatId(beatId).stream()

						.filter(r -> r.getDayNumber() == sched.getDayNumber())

						.mapToInt(BeatRoute::getSequenceOrder).max().orElse(-1) + 1;

				BeatRoute br = new BeatRoute();

				br.setBeatId(beatId);

				br.setFofoId(d.getFofoId());

				br.setVisitType(com.spice.profitmandi.dao.enumuration.dtr.BeatVisitType.PARTNER);

				br.setDayNumber(sched.getDayNumber());

				br.setSequenceOrder(nextSeq);

				br.setActive(true);

				beatRouteRepository.persist(br);

			}

		}

		d.setStatus(String.valueOf(ProfitMandiConstants.MARK_TYPE.RESCHEDULED));

		d.setRescheduledToDate(date);

		d.setActionBy(me.getId());

		d.setUpdatedTimestamp(now);

		beatDeferredVisitRepository.persist(d);

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

		ok.put("status", true);

		ok.put("message", (isLead ? "Lead" : "Partner") + " added to beat '" + beat.getName() + "' on " + dateStr);

		return responseSender.ok(ok);

	}

	// authUserId -> dtr.users id (via shared email), memoized in the passed cache.

	// Used by the reschedule action to create the new PENDING location_tracking row.

	private Integer resolveDtrId(int authUserId, Map<Integer, Integer> cache) {

		if (cache.containsKey(authUserId)) return cache.get(authUserId);

		Integer dtrId = null;

		try {

			AuthUser au = authRepository.selectById(authUserId);

			if (au != null && au.getEmailId() != null) {

				com.spice.profitmandi.dao.entity.dtr.User u = userRepositoryAuto.selectByEmailId(au.getEmailId());

				if (u != null) dtrId = u.getId();

			}

		} catch (Exception ignored) {

		}

		cache.put(authUserId, dtrId);

		return dtrId;

	}

	// For an auth user: fofoId -> earliest upcoming (>= today) scheduled date where

	// an active beat's route still includes that partner (the "Next Scheduled" hint).

	private Map<Integer, LocalDate> computeFutureCover(int authUserId) {

		LocalDate today = LocalDate.now();

		Map<Integer, LocalDate> cover = new HashMap<>();

		for (Beat b : beatRepository.selectActiveByAuthUserId(authUserId)) {

			LocalDate earliest = null;

			for (BeatSchedule s : beatScheduleRepository.selectByBeatId(b.getId())) {

				LocalDate dt = s.getStartDate();

				if (dt != null && dt.getYear() != 9999 && !dt.isBefore(today)) {

					if (earliest == null || dt.isBefore(earliest)) earliest = dt;

				}

			}

			if (earliest == null) continue;

			for (BeatRoute rt : beatRouteRepository.selectByBeatId(b.getId())) {

				if (!rt.isActive()) continue;

				LocalDate cur = cover.get(rt.getFofoId());

				if (cur == null || earliest.isBefore(cur)) cover.put(rt.getFofoId(), earliest);

			}

		}

		return cover;

	}

	@GetMapping(value = "/beatPlanWindow")

	public String beatPlanWindow(HttpServletRequest request, Model model) throws ProfitMandiBusinessException {

		model.addAttribute("escalationTypes", visibleLevelsFor(request));

		model.addAttribute("canScheduleToday", canScheduleToday(currentUser(request)));

		return "beat-plan-window";

	}

	// Helpers for XLSX bulk upload

	private static String readCell(org.apache.poi.ss.usermodel.Cell cell) {

		if (cell == null) return null;

		switch (cell.getCellType()) {

			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:

				return cell.getStringCellValue();

			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:

				if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {

					return cell.getDateCellValue().toInstant()

							.atZone(java.time.ZoneId.systemDefault()).toLocalDate().toString();

				}

				double n = cell.getNumericCellValue();

				return (n == Math.floor(n)) ? String.valueOf((long) n) : String.valueOf(n);

			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:

				return String.valueOf(cell.getBooleanCellValue());

			case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:

				return cell.getCellFormula();

			default:

				return null;

		}

	}

	// ====================== ONE-TIME LAT/LNG MIGRATION ======================

	// For each active fofo_store, compare its stored lat/lng with the geocoded

	// address lat/lng (cached in Redis). If the gap is > thresholdKm (default 5)

	// OR the store has no lat/lng yet, update the store with the geocoded

	// coordinates. Otherwise keep the existing values.

	//

	// Usage:

	//   GET /beatPlan/migrateStoreLatLng              -> dry run, default 5km, all

	//   GET /beatPlan/migrateStoreLatLng?apply=true   -> actually update

	//   ?thresholdKm=3      -> use a different threshold

	//   ?limit=100          -> process only N stores (for staged runs)

	@GetMapping(value = "/beatPlan/migrateStoreLatLng")

	public ResponseEntity<?> migrateStoreLatLng(

			@RequestParam(required = false, defaultValue = "false") boolean apply,

			@RequestParam(required = false, defaultValue = "5") double thresholdKm,

			@RequestParam(required = false, defaultValue = "0") int limit,

			@RequestParam(required = false, defaultValue = "0") int offset,

			@RequestParam(required = false, defaultValue = "40") int maxSeconds) throws ProfitMandiBusinessException {

		List<FofoStore> all = fofoStoreRepository.selectActiveStores();

		int totalAvailable = all.size();

		int from = Math.max(0, Math.min(offset, totalAvailable));

		// Hard cap (if limit given), else go to the end of the list.

		int hardTo = limit > 0 ? Math.min(from + limit, totalAvailable) : totalAvailable;

		// Time budget: stop processing once we approach the gateway timeout and

		// return nextOffset so the caller can resume. Geocoding is the slow part

		// (network/cache), so a fixed batch size could still time out on a cache-miss

		// run — a wall-clock budget is safer. maxSeconds defaults to 40 (< typical 60s gateway).

		long deadlineMs = System.currentTimeMillis() + Math.max(5, maxSeconds) * 1000L;

		List<FofoStore> stores = all.subList(from, hardTo);

		List<Integer> ids = stores.stream().map(FofoStore::getId).collect(Collectors.toList());

		Map<Integer, CustomRetailer> retailerMap = retailerService.getFofoRetailers(ids);

		int total = 0;            // stores actually processed this call

		int updated = 0, kept = 0, noAddress = 0, noGeocode = 0, errored = 0;

		boolean stoppedOnTime = false;

		int nextIndex = from;     // absolute index of next unprocessed store

		List<Map<String, Object>> changes = new ArrayList<>();

		for (FofoStore store : stores) {

			// Stop before doing more slow geocoding work if we've spent our budget.

			if (System.currentTimeMillis() >= deadlineMs) {

				stoppedOnTime = true;

				break;

			}

			total++;

			nextIndex++;

			try {

				CustomRetailer retailer = retailerMap.get(store.getId());

				if (retailer == null || retailer.getAddress() == null) {

					noAddress++;

					continue;

				}

				String geoAddr = com.spice.profitmandi.service.GeocodingService.buildGeoAddress(

						retailer.getAddress().getLine1(), retailer.getAddress().getCity(),

						retailer.getAddress().getState(), retailer.getAddress().getPinCode());

				if (geoAddr == null || geoAddr.isEmpty()) {

					noAddress++;

					continue;

				}

				double[] coords = geocodingService.geocodeAddress(geoAddr);

				if (coords == null) {

					noGeocode++;

					continue;

				}

				Double existingLat = parseDoubleOrNull(store.getLatitude());

				Double existingLng = parseDoubleOrNull(store.getLongitude());

				boolean shouldUpdate;

				double distKm = -1;

				String reason;

				if (existingLat == null || existingLng == null) {

					shouldUpdate = true;

					reason = "missing existing lat/lng";

				} else {

					distKm = haversineKm(existingLat, existingLng, coords[0], coords[1]);

					shouldUpdate = distKm > thresholdKm;

					reason = shouldUpdate

							? "gap " + Math.round(distKm * 10.0) / 10.0 + "km > " + thresholdKm + "km"

							: "gap " + Math.round(distKm * 10.0) / 10.0 + "km within " + thresholdKm + "km";

				}

				if (shouldUpdate) {

					if (apply) {

						store.setLatitude(String.valueOf(coords[0]));

						store.setLongitude(String.valueOf(coords[1]));

						store.setLatLngUpdatedTimestamp(LocalDateTime.now());

						fofoStoreRepository.persist(store);

					}

					updated++;

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

					ch.put("storeId", store.getId());

					ch.put("code", store.getCode());

					ch.put("oldLat", existingLat);

					ch.put("oldLng", existingLng);

					ch.put("newLat", coords[0]);

					ch.put("newLng", coords[1]);

					ch.put("distKm", distKm >= 0 ? Math.round(distKm * 10.0) / 10.0 : null);

					ch.put("reason", reason);

					changes.add(ch);

				} else {

					// Verified-kept: lat/lng was already within threshold. Still stamp it

					// so "processed vs pending" can be told from lat_lng_updated_timestamp.

					if (apply) {

						store.setLatLngUpdatedTimestamp(LocalDateTime.now());

						fofoStoreRepository.persist(store);

					}

					kept++;

				}

			} catch (Exception e) {

				errored++;

				LOGGER.warn("Geocode/migrate failed for fofoId={}: {}", store.getId(), e.getMessage());

			}

		}

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

		result.put("mode", apply ? "APPLIED" : "DRY RUN — pass &apply=true to actually update");