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

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

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

		}

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

	}

	@GetMapping(value = "/beatPlanWindow")

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

		model.addAttribute("escalationTypes", visibleLevelsFor(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) throws ProfitMandiBusinessException {

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

		int totalAvailable = all.size();

		// offset + limit so this can be run in batches against large datasets

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

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

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

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

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

		int total = stores.size();

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

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

		for (FofoStore store : stores) {

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

						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 {

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

		result.put("thresholdKm", thresholdKm);

		result.put("totalAvailable", totalAvailable);  // total active stores in DB

		result.put("offset", from);

		result.put("processed", total);                 // stores processed this run

		result.put("nextOffset", to);                   // next offset to resume (or = totalAvailable when done)

		result.put("done", to >= totalAvailable);

		result.put("updated", updated);

		result.put("kept", kept);

		result.put("noAddress", noAddress);

		result.put("noGeocode", noGeocode);

		result.put("errored", errored);

		// Limit changes preview to avoid huge responses

		result.put("changes", changes.size() > 200 ? changes.subList(0, 200) : changes);

		result.put("changesShownCount", Math.min(changes.size(), 200));

		return responseSender.ok(result);

	}

	// ====================== EDIT BEAT ======================

	// Update an existing beat — name + partner stops (routes).

	// Schedules are NOT touched here; manage them via calendar drag-drop.

	@PostMapping(value = "/beatPlan/updateBeat")

	public ResponseEntity<?> updateBeat(

			HttpServletRequest request,

			@RequestParam int beatId,

			@RequestParam String planData) throws Exception {

		Beat beat = beatRepository.selectById(beatId);

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

		Gson gson = new Gson();

		Type type = new TypeToken<Map<String, Object>>() {

		}.getType();

		Map<String, Object> plan = gson.fromJson(planData, type);

		List<Map<String, Object>> days = (List<Map<String, Object>>) plan.get("days");

		if (days == null || days.isEmpty()) return responseSender.badRequest("No days provided");

		// Update name if changed (and not colliding with another beat)

		String newName = plan.get("beatName") != null ? ((String) plan.get("beatName")).trim() : beat.getName();

		if (newName != null && !newName.equalsIgnoreCase(beat.getName())) {

			// Make sure no other ACTIVE beat for this user already uses this name.

			// Soft-deleted beats keep their name in the table; we don't want them

			// to block a legitimate rename.

			boolean collides = beatRepository.selectActiveByAuthUserId(beat.getAuthUserId()).stream()

					.anyMatch(b -> b.getId() != beat.getId()

							&& b.getName() != null

							&& newName.equalsIgnoreCase(b.getName().trim()));

			if (collides) return responseSender.badRequest("Another beat with this name already exists");

			beat.setName(newName);

		}

		// Update start location from first day if present

		Map<String, Object> firstDay = days.get(0);

		if (firstDay.get("startLocationName") != null)

			beat.setStartLocationName((String) firstDay.get("startLocationName"));

		if (firstDay.get("startLatitude") != null) beat.setStartLatitude((String) firstDay.get("startLatitude"));

		if (firstDay.get("startLongitude") != null) beat.setStartLongitude((String) firstDay.get("startLongitude"));

		int oldTotalDays = beat.getTotalDays();

		int newTotalDays = days.size();

		// Hard rule: you cannot grow the number of days on an existing beat.

		// If you need more days, create a new beat. (Shrinking is allowed and

		// the schedules for dropped day numbers are cleaned below.)

		if (newTotalDays > oldTotalDays) {

			return responseSender.badRequest(

					"Cannot increase the number of days on an existing beat. "

							+ "Original: " + oldTotalDays + " day(s), tried: " + newTotalDays + " day(s). "

							+ "Please create a new beat for additional days.");

		}

		beat.setTotalDays(newTotalDays);

		// Replace routes (partner stops). Schedules stay intact (except for

        // dayNumber > newTotalDays cleanup + total km/min refresh below).

		beatRouteRepository.deleteByBeatId(beatId);

		// Collect lead IDs the user kept on the plan

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

		for (int d = 0; d < days.size(); d++) {

			Map<String, Object> day = days.get(d);

			int dayNumber = d + 1;

			List<Map<String, Object>> visits = (List<Map<String, Object>>) day.get("visits");

			if (visits == null) continue;

			int partnerSeq = 0;

			for (int i = 0; i < visits.size(); i++) {

				Map<String, Object> v = visits.get(i);

				if ("lead".equals(v.get("type"))) {

					keptLeadIds.add(((Number) v.get("id")).intValue());

					continue; // leads live in lead_route, handled below

				}

				BeatRoute route = new BeatRoute();

				route.setBeatId(beatId);

				route.setFofoId(((Number) v.get("id")).intValue());

				route.setSequenceOrder(partnerSeq++);

				route.setDayNumber(dayNumber);

				route.setActive(true);

                if (v.get("distanceFromPrevKm") != null)

                    route.setDistanceFromPrevKm(((Number) v.get("distanceFromPrevKm")).doubleValue());

                if (v.get("timeFromPrevMins") != null)

                    route.setTimeFromPrevMins(((Number) v.get("timeFromPrevMins")).intValue());

				beatRouteRepository.persist(route);

			}

		}

		// If the beat shrank, drop schedule rows for day numbers that no longer exist

		if (newTotalDays < oldTotalDays) {

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

			for (BeatSchedule s : currentSchedules) {

				if (s.getDayNumber() > newTotalDays) beatScheduleRepository.delete(s);

			}

		}

        // Refresh the day-level totals on every remaining schedule row so they

        // reflect the post-edit route. Previously updateBeat left these stale

        // (or NULL, for beats created before this fix), which is what the user

        // reported. Keyed by dayNumber so multi-instance beats all get updated.

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

        for (int d = 0; d < days.size(); d++) {

            dayByNumber.put(d + 1, days.get(d));

        }

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

        for (BeatSchedule s : allSchedules) {

            Map<String, Object> day = dayByNumber.get(s.getDayNumber());

            if (day == null) continue;

            if (day.get("totalDistanceKm") != null)

                s.setTotalDistanceKm(((Number) day.get("totalDistanceKm")).doubleValue());

            if (day.get("totalTimeMins") != null)

                s.setTotalTimeMins(((Number) day.get("totalTimeMins")).intValue());

        }

		// Process per-lead actions sent from the editor's removed-leads popup.

		// Each entry: {leadId, action: "cancel"|"reschedule", toDate?: "yyyy-MM-dd"}.

		// - cancel: mark the lead's current APPROVED row for this beat as CANCELLED.

		// - reschedule: cancel here, then create a fresh APPROVED LeadRoute on

		//   whichever beat this user has scheduled on toDate. If no beat exists

		//   on toDate, the whole update fails (so the caller can prompt again).

		int leadsCancelled = 0, leadsRescheduled = 0;

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

		String removedLeadActionsJson = (String) plan.get("removedLeadActions");

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

			Type listType = new TypeToken<List<Map<String, Object>>>() {

			}.getType();

			List<Map<String, Object>> actions = gson.fromJson(removedLeadActionsJson, listType);

			List<LeadRoute> beatLeads = leadRouteRepository.selectByBeatId(beatId);

			for (Map<String, Object> act : actions) {

				int leadId = ((Number) act.get("leadId")).intValue();

				String mode = (String) act.get("action");

				// Find this lead's most-recent APPROVED row on this beat

				LeadRoute current = beatLeads.stream()

						.filter(r -> r.getLeadId() == leadId && "APPROVED".equals(r.getStatus()))

						.findFirst().orElse(null);

				if (current == null) continue; // already removed/cancelled; nothing to do

				if ("reschedule".equalsIgnoreCase(mode)) {

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

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

						leadFailures.add("Lead " + leadId + ": reschedule date missing");

						continue;

					}

					LocalDate toDate = LocalDate.parse(toDateStr);

					// Find ANY beat this user has scheduled on toDate

					Beat targetBeat = null;

					Integer targetDayNumber = null;

					List<Beat> userBeats = beatRepository.selectActiveByAuthUserId(beat.getAuthUserId());

					for (Beat b : userBeats) {

						List<BeatSchedule> sl = beatScheduleRepository.selectByBeatId(b.getId());

						for (BeatSchedule s : sl) {

							if (s.getStartDate() != null && s.getStartDate().equals(toDate)) {

								targetBeat = b;

								targetDayNumber = s.getDayNumber();

								break;

							}

						}

						if (targetBeat != null) break;

					}

					if (targetBeat == null) {

						return responseSender.badRequest(

								"No beat is scheduled for this user on " + toDateStr

										+ ". Pick a different date for lead " + leadId

										+ ", or choose Cancel for it.");

					}

					// Cancel the current attachment to this beat

					current.setStatus("CANCELLED");

					current.setUpdatedTimestamp(LocalDateTime.now());

					// Create the new attachment on the target beat/date

					LeadRoute fresh = new LeadRoute();

					fresh.setBeatId(targetBeat.getId());

					fresh.setLeadId(leadId);

					fresh.setNearestStoreId(current.getNearestStoreId());

					fresh.setScheduleDate(toDate);

					fresh.setSequenceOrder(9999); // append; the planner can reorder

					fresh.setStatus("APPROVED");

					fresh.setRequestedBy(current.getRequestedBy());

					fresh.setApprovedBy(current.getApprovedBy());

					fresh.setApprovedTimestamp(LocalDateTime.now());

					fresh.setCreatedTimestamp(LocalDateTime.now());

					fresh.setUpdatedTimestamp(LocalDateTime.now());

					leadRouteRepository.persist(fresh);

					LeadActivity la = new LeadActivity();

					la.setLeadId(leadId);

					la.setRemark("Rescheduled from beat '" + beat.getName() + "' to '"

							+ targetBeat.getName() + "' on " + toDateStr + " (day " + targetDayNumber + ")");

					la.setAuthId(0);

					la.setCreatedTimestamp(LocalDateTime.now());

					leadActivityRepositoryAuto.persist(la);

					leadsRescheduled++;

				} else {

					// cancel (default)

					current.setStatus("CANCELLED");

					current.setUpdatedTimestamp(LocalDateTime.now());

					LeadActivity la = new LeadActivity();

					la.setLeadId(leadId);

					la.setRemark("Cancelled from beat '" + beat.getName() + "' during edit");

					la.setAuthId(0);

					la.setCreatedTimestamp(LocalDateTime.now());

					leadActivityRepositoryAuto.persist(la);

					leadsCancelled++;

				}

			}

		}

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

		response.put("status", true);

		response.put("planGroupId", String.valueOf(beat.getId()));

		response.put("leadsCancelled", leadsCancelled);

		response.put("leadsRescheduled", leadsRescheduled);

		response.put("leadFailures", leadFailures);

		response.put("message", "Beat updated successfully"

				+ (leadsCancelled > 0 ? " (" + leadsCancelled + " lead(s) cancelled)" : "")

				+ (leadsRescheduled > 0 ? " (" + leadsRescheduled + " lead(s) rescheduled)" : ""));

		return responseSender.ok(response);

	}

	// Used by the edit-mode "removed leads" popup so the date picker can warn

	// upfront when the user picks a date that has no beat for them.

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

	public ResponseEntity<?> userBeatsOnDate(

			@RequestParam int authUserId,

			@RequestParam String date) {

		LocalDate target;

		try {

			target = LocalDate.parse(date);

		} catch (Exception e) {

			return responseSender.badRequest("Invalid date");

		}

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

		List<Beat> userBeats = beatRepository.selectActiveByAuthUserId(authUserId);

		for (Beat b : userBeats) {

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

			for (BeatSchedule s : schedules) {

				if (s.getStartDate() != null && s.getStartDate().equals(target)) {

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

					m.put("beatId", b.getId());

					m.put("beatName", b.getName());

					m.put("dayNumber", s.getDayNumber());

					hits.add(m);

				}

			}

		}

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

		result.put("date", date);

		result.put("authUserId", authUserId);

		result.put("beats", hits);

		return responseSender.ok(result);

	}

	// ====================== BASE LOCATION MANAGEMENT ======================

	// Inline page that lets Sales L3+ pick a user and set their base (home)

	// location via map. Reads use the existing /beatPlan/getBaseLocation, writes

	// go through the L3+-guarded endpoint below.

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

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

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

		return "beat-plan-base-location";

	}

	// Tabular JSON: one row per (beat, scheduled date) in [startDate, endDate].

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

	public ResponseEntity<?> scheduledList(

			@RequestParam(required = false) String startDate,

			@RequestParam(required = false) String endDate) {

		LocalDate start, end;

		try {

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

			end = (endDate == null || endDate.isEmpty()) ? start.plusDays(7) : LocalDate.parse(endDate);

		} catch (Exception e) {

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

		}

		List<com.spice.profitmandi.dao.model.BeatDayDetails> beats =

				beatPlanQueryService.getAllScheduledBeats(start, end);

		// Resolve user names in bulk

		Set<Integer> userIds = beats.stream()

				.map(com.spice.profitmandi.dao.model.BeatDayDetails::getAuthUserId)

				.collect(java.util.stream.Collectors.toSet());

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

		if (!userIds.isEmpty()) {

			authRepository.selectByIds(new ArrayList<>(userIds))

					.forEach(u -> userMap.put(u.getId(), u));

		}

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

		for (com.spice.profitmandi.dao.model.BeatDayDetails b : beats) {

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

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

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

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

			row.put("scheduleDate", b.getScheduleDate().toString());

			row.put("dayNumber", b.getDayNumber());

			row.put("beatId", b.getBeatId());

			row.put("beatName", b.getBeatName());

			row.put("beatColor", b.getBeatColor());

			row.put("partnerCount", b.getPartnerStops().size());

			row.put("leadCount", b.getLeadStops().size());

			row.put("visitCount", b.getPartnerStops().size() + b.getLeadStops().size());

			rows.add(row);

		}

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

		result.put("rows", rows);

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

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

		return responseSender.ok(result);

	}

	// JSON: beats running for (authUserId, date) — enriched with partner/lead names & coords

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

	public ResponseEntity<?> beatPlanDayViewData(

			@RequestParam int authUserId,

			@RequestParam String date) throws ProfitMandiBusinessException {

		LocalDate localDate;

		try {

			localDate = LocalDate.parse(date);

		} catch (Exception e) {

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

		}

		List<com.spice.profitmandi.dao.model.BeatDayDetails> beats =

				beatPlanQueryService.getBeatsForUserOnDate(authUserId, localDate);

		// Collect all partner & lead IDs to fetch metadata in bulk

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

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

		for (com.spice.profitmandi.dao.model.BeatDayDetails b : beats) {

			b.getPartnerStops().forEach(s -> partnerIds.add((Integer) s.get("fofoId")));

			b.getLeadStops().forEach(s -> leadIds.add((Integer) s.get("leadId")));

		}

		// Partners: name + geocoded lat/lng (geocoder is cached in Redis)

		Map<Integer, CustomRetailer> retailerMap = partnerIds.isEmpty()

				? new HashMap<>()

				: retailerService.getFofoRetailers(new ArrayList<>(partnerIds));

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

		if (!partnerIds.isEmpty()) {

			fofoStoreRepository.selectByRetailerIds(new ArrayList<>(partnerIds))

					.forEach(fs -> storeMap.put(fs.getId(), fs));

		}

		// Leads: name + geo

		Map<Integer, com.spice.profitmandi.dao.entity.user.Lead> leadMap = new HashMap<>();

		Map<Integer, com.spice.profitmandi.dao.entity.user.LeadLiveLocation> leadGeoMap = new HashMap<>();

		for (int leadId : leadIds) {

			com.spice.profitmandi.dao.entity.user.Lead l = leadRepository.selectById(leadId);

			if (l != null) leadMap.put(leadId, l);

			com.spice.profitmandi.dao.entity.user.LeadLiveLocation lg =

					leadLiveLocationRepositoryAuto.selectApprovedByLeadId(leadId);

			if (lg != null) leadGeoMap.put(leadId, lg);

		}

		// Enrich each stop

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

		for (com.spice.profitmandi.dao.model.BeatDayDetails b : beats) {

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

			beatJson.put("beatId", b.getBeatId());

			beatJson.put("beatName", b.getBeatName());

			beatJson.put("beatColor", b.getBeatColor());

			beatJson.put("dayNumber", b.getDayNumber());

			beatJson.put("scheduleDate", b.getScheduleDate().toString());

			beatJson.put("endAction", b.getEndAction());

			beatJson.put("totalDistanceKm", b.getTotalDistanceKm());

			beatJson.put("totalTimeMins", b.getTotalTimeMins());

			beatJson.put("startLocationName", b.getStartLocationName());

			beatJson.put("startLatitude", b.getStartLatitude());

			beatJson.put("startLongitude", b.getStartLongitude());

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

			// Partners

			for (Map<String, Object> ps : b.getPartnerStops()) {

				int fofoId = (Integer) ps.get("fofoId");

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

				stop.put("type", "partner");

				stop.put("id", fofoId);

				stop.put("sequenceOrder", ps.get("sequenceOrder"));