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;

	@Autowired

	private FofoStoreRepository fofoStoreRepository;

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

	@GetMapping(value = "/beatPlan")

	public String beatPlan(HttpServletRequest request, Model model) {

		EscalationType[] escalationTypes = EscalationType.values();

		model.addAttribute("escalationTypes", escalationTypes);

		return "beat-plan";

	}

	@GetMapping(value = "/beatPlanWindow")

	public String beatPlanWindow(HttpServletRequest request, Model model) {

		EscalationType[] escalationTypes = EscalationType.values();

		model.addAttribute("escalationTypes", escalationTypes);

		return "beat-plan-window";

	}

	@Autowired

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

	// ====================== DAY VIEW ======================

	// Inline page (loaded into dashboard #main-content): tabular list of all beats

	// scheduled in a date range across all users. Each row has a View button that

	// opens that user's calendar in a modal.

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

	public String beatPlanDayView(HttpServletRequest request, Model model) {

		EscalationType[] escalationTypes = EscalationType.values();

		model.addAttribute("escalationTypes", escalationTypes);

		return "beat-plan-day-view";

	}

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

				FofoStore fs = storeMap.get(fofoId);

				CustomRetailer cr = retailerMap.get(fofoId);

				stop.put("code", fs != null ? fs.getCode() : null);

				stop.put("name", fs != null && fs.getOutletName() != null ? fs.getOutletName()

						: (cr != null ? cr.getBusinessName() : "Store #" + fofoId));

				if (cr != null && cr.getAddress() != null) {

					stop.put("address", cr.getAddress().getAddressString());

					try {

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

								cr.getAddress().getLine1(), cr.getAddress().getCity(),

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

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

						if (coords != null) {

							stop.put("lat", coords[0]);

							stop.put("lng", coords[1]);

						}

					} catch (Exception ignored) {

					}

				}

				stops.add(stop);

			}

			// Leads

			for (Map<String, Object> ls : b.getLeadStops()) {

				int leadId = (Integer) ls.get("leadId");

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

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

				stop.put("id", leadId);

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

				stop.put("nearestStoreId", ls.get("nearestStoreId"));

				com.spice.profitmandi.dao.entity.user.Lead l = leadMap.get(leadId);

				stop.put("name", l != null ? l.getFirstName() + " " + l.getLastName() : "Lead #" + leadId);

				stop.put("mobile", l != null ? l.getLeadMobile() : null);

				stop.put("city", l != null ? l.getCity() : null);

				com.spice.profitmandi.dao.entity.user.LeadLiveLocation lg = leadGeoMap.get(leadId);

				if (lg != null) {

					stop.put("lat", lg.getLatitude());

					stop.put("lng", lg.getLongitude());

				}

				stops.add(stop);

			}

			beatJson.put("stops", stops);

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

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

			out.add(beatJson);

		}

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

		result.put("beats", out);

		return responseSender.ok(result);

	}

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

	public ResponseEntity<?> getAuthUsers(

			@RequestParam int categoryId,

			@RequestParam EscalationType escalationType) {

		List<AuthUser> authUsers = csService.getAuthUserByCategoryId(categoryId, escalationType);

		List<Map<String, Object>> result = authUsers.stream()

				.filter(au -> au.getActive())

				.map(au -> {

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

					map.put("id", au.getId());

					map.put("name", au.getFirstName() + " " + au.getLastName());

					return map;

				})

				.collect(Collectors.toList());

		return responseSender.ok(result);

	}

	// Returns visits for a beat.

	// - Partner stops (beat_route) belong to the beat template — always returned.

	// - Lead stops (lead_route) belong to a specific run — returned ONLY when planDate

	//   is given and matches the lead's schedule_date. (No planDate = template view.)

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

	public ResponseEntity<?> getBeatVisits(

			@RequestParam String planGroupId,

			@RequestParam(required = false) String planDate) {

		int beatId;

		try {

			beatId = Integer.parseInt(planGroupId);

		} catch (NumberFormatException e) {

			return responseSender.ok(new ArrayList<>());

		}

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

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

		// Partner stops — always (they belong to the beat template)

		for (BeatRoute r : routes) {

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

			map.put("fofoId", r.getFofoId());

			map.put("dayNumber", r.getDayNumber());

			map.put("sequenceOrder", r.getSequenceOrder());

			map.put("visitType", "partner");

			result.add(map);

		}

		// Lead stops — only for the requested run date

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

			LocalDate date = LocalDate.parse(planDate);

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

			for (LeadRoute lr : leads) {

				if ("APPROVED".equals(lr.getStatus())

						&& lr.getScheduleDate() != null

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

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

					map.put("fofoId", lr.getLeadId());

					map.put("dayNumber", 1);

					map.put("sequenceOrder", lr.getSequenceOrder() != null ? lr.getSequenceOrder() : 999);

					map.put("visitType", "lead");

					result.add(map);

				}

			}

		}

		// Sort by dayNumber then sequenceOrder

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

			int cmp = Integer.compare((int) a.get("dayNumber"), (int) b.get("dayNumber"));

			return cmp != 0 ? cmp : Integer.compare((int) a.get("sequenceOrder"), (int) b.get("sequenceOrder"));

		});

		return responseSender.ok(result);

	}

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

	public ResponseEntity<?> getBaseLocation(@RequestParam int authUserId) {

		AuthUserLocation baseLoc = authUserLocationRepository.selectLatestByAuthUserIdAndType(authUserId, "BASE");

		if (baseLoc == null) {

			return responseSender.ok(new HashMap<>());

		}

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

		result.put("id", baseLoc.getId());

		result.put("locationName", baseLoc.getLocationName());

		result.put("latitude", baseLoc.getLatitude());

		result.put("longitude", baseLoc.getLongitude());

		result.put("address", baseLoc.getAddress());

		return responseSender.ok(result);

	}

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

	public ResponseEntity<?> saveBaseLocation(

			@RequestParam int authUserId,

			@RequestParam String locationName,

			@RequestParam String latitude,

			@RequestParam String longitude,

			@RequestParam(required = false) String address) {

		AuthUserLocation loc = new AuthUserLocation();

		loc.setAuthUserId(authUserId);

		loc.setLocationType("BASE");

		loc.setLocationName(locationName);

		loc.setLatitude(latitude);

		loc.setLongitude(longitude);

		loc.setAddress(address);

		loc.setCreatedTimestamp(LocalDateTime.now());

		authUserLocationRepository.persist(loc);

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

		result.put("status", true);

		result.put("id", loc.getId());

		return responseSender.ok(result);

	}

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

	public ResponseEntity<?> getPartners(

			@RequestParam int authUserId,

			@RequestParam int categoryId,

			@RequestParam(required = false) String startLat,

			@RequestParam(required = false) String startLng) throws ProfitMandiBusinessException {

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

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

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

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

			empty.put("partners", new ArrayList<>());

			return responseSender.ok(empty);

		}

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

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

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

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

		for (FofoStore store : fofoStores) {

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

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

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

			partnerData.put("fofoId", store.getId());

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

			partnerData.put("outletName", store.getOutletName());

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

			String geoAddress = null;

			if (retailer != null) {

				partnerData.put("businessName", retailer.getBusinessName());

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

					partnerData.put("address", retailer.getAddress().getAddressString());

					geoAddress = com.spice.profitmandi.service.GeocodingService.buildGeoAddress(

							retailer.getAddress().getLine1(),

							retailer.getAddress().getCity(),

							retailer.getAddress().getState(),

							retailer.getAddress().getPinCode());

				}

			}

			addressesToGeocode.add(geoAddress);

			partners.add(partnerData);

		}

		// Geocode in parallel

		java.util.concurrent.ExecutorService executor = java.util.concurrent.Executors.newFixedThreadPool(

				Math.min(10, Math.max(1, partners.size())));

		List<java.util.concurrent.Future<double[]>> futures = new ArrayList<>();

		for (String addr : addressesToGeocode) {

			futures.add(executor.submit(() -> {

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

					try {

						return geocodingService.geocodeAddress(addr);

					} catch (Exception e) {

						return null;

					}

				}

				return null;

			}));

		}

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

			try {

				double[] coords = futures.get(i).get(10, java.util.concurrent.TimeUnit.SECONDS);

				if (coords != null) {

					partners.get(i).put("latitude", String.valueOf(coords[0]));

					partners.get(i).put("longitude", String.valueOf(coords[1]));

				}

			} catch (Exception e) {

				LOGGER.warn("Geocoding timeout/error for partner {}", partners.get(i).get("code"));

			}

		}

		executor.shutdown();

		if (startLat != null && startLng != null && !startLat.isEmpty() && !startLng.isEmpty()) {

			partners = sortByNearestNeighborFromStart(partners, Double.parseDouble(startLat), Double.parseDouble(startLng));

		} else {

			partners = sortByNearestNeighbor(partners);

		}

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

		response.put("partners", partners);

		return responseSender.ok(response);

	}

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

	public ResponseEntity<?> submitPlan(

			HttpServletRequest request,

			@RequestParam int authUserId,

			@RequestParam String planData) throws Exception {

		LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

		AuthUser currentUser = authRepository.selectByEmailOrMobile(loginDetails.getEmailId());

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

		List<String> dates = (List<String>) plan.get("dates");

		String beatName = (plan.get("beatName") != null ? (String) plan.get("beatName") : "Beat").trim();

		// Duplicate check — same name + same authUserId = duplicate

		List<Beat> existingBeats = beatRepository.selectByAuthUserId(authUserId);

		for (Beat existing : existingBeats) {

			if (existing.getName() != null && beatName.equalsIgnoreCase(existing.getName().trim())) {

				LOGGER.info("Duplicate beat blocked: name='{}' authUserId={} existingId={}", beatName, authUserId, existing.getId());

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

				response.put("status", true);

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

				response.put("duplicate", true);

				response.put("message", "Beat '" + beatName + "' already exists");

				return responseSender.ok(response);

			}

		}

		String beatColor = BEAT_COLORS[Math.abs(beatName.hashCode()) % BEAT_COLORS.length];

		int totalDays = days.size();

		// Create Beat master

		Beat beat = new Beat();

		beat.setName(beatName);

		beat.setAuthUserId(authUserId);

		beat.setBeatColor(beatColor);

		beat.setTotalDays(totalDays);

		beat.setActive(true);

		beat.setCreatedBy(currentUser.getId());

		beat.setCreatedTimestamp(LocalDateTime.now());

		// Set start location from first day

		if (!days.isEmpty()) {

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

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

			beat.setStartLatitude((String) firstDay.get("startLatitude"));

			beat.setStartLongitude((String) firstDay.get("startLongitude"));

		}

		beatRepository.persist(beat);

		// End date of the whole beat = last scheduled day's date

		LocalDate beatEndDate = null;

		if (dates != null) {

			for (int d = dates.size() - 1; d >= 0; d--) {

				if (dates.get(d) != null) {

					beatEndDate = LocalDate.parse(dates.get(d), DateTimeFormatter.ISO_DATE);

					break;

				}

			}

		}

		// Create routes and schedules for each day

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

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

			int dayNumber = d + 1;

			LocalDate planDate = (dates != null && d < dates.size() && dates.get(d) != null)

					? LocalDate.parse(dates.get(d), DateTimeFormatter.ISO_DATE) : null;

			// Auto-determine end action: last day = HOME, others = DAYBREAK

			String endAction = (String) day.get("endAction");

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

				endAction = (dayNumber == totalDays) ? "HOME" : "DAYBREAK";

			}

			// Always create schedule (even if planDate is null — unscheduled beat)

			BeatSchedule schedule = new BeatSchedule();

			schedule.setBeatId(beat.getId());

			schedule.setStartDate(planDate != null ? planDate : LocalDate.of(9999, 12, 31)); // placeholder for unscheduled

			schedule.setEndDate(beatEndDate);

			schedule.setDayNumber(dayNumber);

			schedule.setEndAction(endAction);

			schedule.setStayLocationName((String) day.get("stayLocationName"));

			schedule.setStayLatitude((String) day.get("stayLatitude"));

			schedule.setStayLongitude((String) day.get("stayLongitude"));

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

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

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

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

			schedule.setCreatedTimestamp(LocalDateTime.now());

			beatScheduleRepository.persist(schedule);

			// Routes (stops)

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

			if (visits != null) {

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

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

					BeatRoute route = new BeatRoute();

					route.setBeatId(beat.getId());

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

					route.setSequenceOrder(i);

					route.setDayNumber(dayNumber);

					route.setActive(true);

					beatRouteRepository.persist(route);

				}

			}

		}

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

		response.put("status", true);

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

		response.put("message", "Beat plan submitted successfully");

		return responseSender.ok(response);

	}

	// ============ BULK UPLOAD ============

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

	public String bulkUploadPage(HttpServletRequest request, Model model) {

		return "beat-plan-bulk";

	}

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

	public ResponseEntity<?> downloadTemplate() {

		String csv = "beat_name,auth_user_id,start_date,day_number,partner_codes\n";

		csv += "Jaipur East Route,280,2026-06-02,1,\"RJKAI1478,RJBUN1449,RJDEG1443\"\n";

		csv += ",280,,2,\"RJALR1362,RJBTR1388\"\n";

		csv += ",280,,3,\"RJRSD1518,RJSML356\"\n";

		csv += "Agra Circuit,145,2026-06-05,1,\"UPAGR101,UPAGR102\"\n";

		org.springframework.http.HttpHeaders headers = new org.springframework.http.HttpHeaders();

		headers.add("Content-Disposition", "attachment; filename=beat_plan_template.csv");

		headers.add("Content-Type", "text/csv");

		return new ResponseEntity<>(csv, headers, org.springframework.http.HttpStatus.OK);

	}

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

	public ResponseEntity<?> bulkUploadProcess(

			HttpServletRequest request,

			@RequestParam("file") org.springframework.web.multipart.MultipartFile file,

			@RequestParam(value = "includeSundays", defaultValue = "false") boolean includeSundays) throws Exception {

		LoginDetails loginDetails = cookiesProcessor.getCookiesObject(request);

		AuthUser currentUser = authRepository.selectByEmailOrMobile(loginDetails.getEmailId());

		java.io.Reader reader = new java.io.InputStreamReader(file.getInputStream());

		org.apache.commons.csv.CSVParser parser = new org.apache.commons.csv.CSVParser(reader,

				org.apache.commons.csv.CSVFormat.DEFAULT.withFirstRecordAsHeader().withTrim());

		List<org.apache.commons.csv.CSVRecord> allRecords = parser.getRecords();

		parser.close();

		Map<String, String> lastBeatNameByUser = new HashMap<>();

		Map<String, List<org.apache.commons.csv.CSVRecord>> beatGroups = new LinkedHashMap<>();

		Map<Long, String> resolvedBeatNames = new HashMap<>();

		for (org.apache.commons.csv.CSVRecord record : allRecords) {

			String authId = record.get("auth_user_id").trim();

			String rawName = record.get("beat_name").trim().replaceAll("\\s+", " ");

			if (rawName.isEmpty()) rawName = lastBeatNameByUser.getOrDefault(authId, "Beat");

			else lastBeatNameByUser.put(authId, rawName);

			resolvedBeatNames.put(record.getRecordNumber(), rawName);

			beatGroups.computeIfAbsent(rawName + "|" + authId, k -> new ArrayList<>()).add(record);

		}

		List<FofoStore> allStores = fofoStoreRepository.selectAll();

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

		for (FofoStore store : allStores) codeToId.put(store.getCode(), store.getId());

		LocalDate holidayStart = LocalDate.now();

		List<PublicHolidays> holidays = publicHolidaysRepository.selectAllBetweenDates(holidayStart, holidayStart.plusMonths(6));

		Set<LocalDate> holidayDates = holidays.stream().map(PublicHolidays::getDate).collect(Collectors.toSet());

		int beatsCreated = 0, errors = 0;

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

		for (Map.Entry<String, List<org.apache.commons.csv.CSVRecord>> entry : beatGroups.entrySet()) {

			try {

				String[] keyParts = entry.getKey().split("\\|");

				String beatName = keyParts[0];

				int authUserId = Integer.parseInt(keyParts[1]);

				List<org.apache.commons.csv.CSVRecord> rows = entry.getValue();

				rows.sort((a, b) -> Integer.parseInt(a.get("day_number").trim()) - Integer.parseInt(b.get("day_number").trim()));

				String startDateStr = rows.get(0).get("start_date").trim();

				LocalDate startDate = startDateStr.isEmpty() ? null : LocalDate.parse(startDateStr, DateTimeFormatter.ISO_DATE);

				if (startDate != null && startDate.isBefore(LocalDate.now())) {

					errorMessages.add("Beat '" + beatName + "': start_date in past. Skipped.");

					errors++;

					continue;

				}

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

				if (startDate != null) {

					LocalDate d = startDate;

					while (scheduleDates.size() < rows.size()) {

						if (holidayDates.contains(d)) {

							d = d.plusDays(1);

							continue;

						}

						if (d.getDayOfWeek() == DayOfWeek.SUNDAY && !includeSundays) {

							d = d.plusDays(1);

							continue;

						}

						scheduleDates.add(d);

						d = d.plusDays(1);

					}

				}

				// Duplicate check — skip if a beat with same name already exists for this user

				boolean isDuplicate = beatRepository.selectByAuthUserId(authUserId).stream()

						.anyMatch(b -> b.getName() != null && beatName.equalsIgnoreCase(b.getName().trim()));

				if (isDuplicate) {

					errorMessages.add("Beat '" + beatName + "' already exists for user " + authUserId + ". Skipped.");

					errors++;

					continue;

				}

				String beatColor = BEAT_COLORS[Math.abs(beatName.hashCode()) % BEAT_COLORS.length];

				AuthUserLocation homeLoc = authUserLocationRepository.selectLatestByAuthUserIdAndType(authUserId, "BASE");

				// Create Beat master

				Beat beat = new Beat();

				beat.setName(beatName);

				beat.setAuthUserId(authUserId);

				beat.setBeatColor(beatColor);

				beat.setTotalDays(rows.size());

				beat.setStartLocationName(homeLoc != null ? homeLoc.getLocationName() : "Home");

				beat.setStartLatitude(homeLoc != null ? homeLoc.getLatitude() : null);

				beat.setStartLongitude(homeLoc != null ? homeLoc.getLongitude() : null);

				beat.setActive(true);

				beat.setCreatedBy(currentUser.getId());

				beat.setCreatedTimestamp(LocalDateTime.now());

				beatRepository.persist(beat);

				for (int rowIdx = 0; rowIdx < rows.size(); rowIdx++) {

					org.apache.commons.csv.CSVRecord row = rows.get(rowIdx);

					int dayNumber = Integer.parseInt(row.get("day_number").trim());

					LocalDate planDate = (rowIdx < scheduleDates.size()) ? scheduleDates.get(rowIdx) : null;

					LocalDate bulkEndDate = scheduleDates.isEmpty() ? null : scheduleDates.get(scheduleDates.size() - 1);

					// Always create schedule — placeholder date (9999-12-31) when unscheduled

					BeatSchedule schedule = new BeatSchedule();

					schedule.setBeatId(beat.getId());

					schedule.setStartDate(planDate != null ? planDate : LocalDate.of(9999, 12, 31));

					schedule.setEndDate(bulkEndDate);

					schedule.setDayNumber(dayNumber);

					schedule.setEndAction(rowIdx == rows.size() - 1 ? "HOME" : "DAYBREAK");

					schedule.setCreatedTimestamp(LocalDateTime.now());

					beatScheduleRepository.persist(schedule);

					String[] partnerCodes = row.get("partner_codes").trim().split(",");

					for (int i = 0; i < partnerCodes.length; i++) {

						String code = partnerCodes[i].trim();

						if (code.isEmpty()) continue;

						Integer fofoId = codeToId.get(code);

						if (fofoId == null) {

							errorMessages.add("Code not found: " + code);

							errors++;

							continue;

						}

						BeatRoute route = new BeatRoute();

						route.setBeatId(beat.getId());

						route.setFofoId(fofoId);

						route.setSequenceOrder(i);

						route.setDayNumber(dayNumber);

						route.setActive(true);

						beatRouteRepository.persist(route);

					}

				}

				beatsCreated++;

			} catch (Exception e) {

				errors++;

				errorMessages.add("Error: " + entry.getKey() + " - " + e.getMessage());

			}

		}

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

		response.put("status", true);

		response.put("beatsCreated", beatsCreated);

		response.put("errors", errors);

		response.put("errorMessages", errorMessages);

		return responseSender.ok(response);

	}

	// ============ CALENDAR ============

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

	public ResponseEntity<?> deleteBeat(@RequestParam String planGroupId) {

		int beatId = Integer.parseInt(planGroupId);

		beatRouteRepository.deleteByBeatId(beatId);

		beatScheduleRepository.deleteByBeatId(beatId);

		Beat beat = beatRepository.selectById(beatId);

		if (beat != null) {

			beat.setActive(false);

		}

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

		response.put("status", true);

		response.put("message", "Beat deleted");

		return responseSender.ok(response);

	}

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

	public ResponseEntity<?> getCalendar(

			@RequestParam int authUserId,

			@RequestParam String month) {

		YearMonth ym = YearMonth.parse(month);

		LocalDate startDate = ym.atDay(1);

		LocalDate endDate = ym.atEndOfMonth();

		List<PublicHolidays> holidays = publicHolidaysRepository.selectAllBetweenDates(startDate, endDate);

		List<Map<String, String>> holidayList = holidays.stream().map(h -> {

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

			m.put("date", h.getDate().toString());

			m.put("occasion", h.getOccasion());

			return m;

		}).collect(Collectors.toList());

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

		LocalDate today = LocalDate.now();

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

		for (Beat beat : allBeats) {

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

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

			boolean allNullDates = schedules.isEmpty() || schedules.stream().allMatch(s -> s.getStartDate().getYear() == 9999);

			boolean hasToday = !allNullDates && schedules.stream().anyMatch(s -> s.getStartDate().equals(today));

			boolean allPast = !allNullDates && schedules.stream().filter(s -> s.getStartDate().getYear() != 9999).allMatch(s -> s.getStartDate().isBefore(today));

			boolean allFuture = !allNullDates && schedules.stream().filter(s -> s.getStartDate().getYear() != 9999).allMatch(s -> s.getStartDate().isAfter(today));

			String status;

			if (allNullDates) status = "unscheduled";

			else if (hasToday) status = "running";

			else if (allPast) status = "completed";

			else status = "scheduled";

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

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

			beatInfo.put("beatName", beat.getName() != null ? beat.getName() : "Beat");

			beatInfo.put("beatColor", beat.getBeatColor() != null ? beat.getBeatColor() : "#3498DB");

			beatInfo.put("status", status);

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

			for (BeatSchedule s : schedules) {

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

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

				boolean isUnscheduled = s.getStartDate().getYear() == 9999;

				dayInfo.put("planDate", isUnscheduled ? null : s.getStartDate().toString());

				dayInfo.put("totalKm", s.getTotalDistanceKm());

				dayInfo.put("totalMins", s.getTotalTimeMins());

				long visitCount = routes.stream().filter(r -> r.getDayNumber() == s.getDayNumber()).count();

				dayInfo.put("visitCount", (int) visitCount);

				dayInfoList.add(dayInfo);

			}

			if (schedules.isEmpty()) {

				// No schedule at all — show from routes

				Map<Integer, Long> dayCounts = routes.stream()

						.collect(Collectors.groupingBy(BeatRoute::getDayNumber, Collectors.counting()));

				for (int d = 1; d <= beat.getTotalDays(); d++) {

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

					dayInfo.put("dayNumber", d);

					dayInfo.put("planDate", null);

					dayInfo.put("totalKm", null);

					dayInfo.put("totalMins", null);

					dayInfo.put("visitCount", dayCounts.getOrDefault(d, 0L).intValue());

					dayInfoList.add(dayInfo);

				}

			}

			beatInfo.put("days", dayInfoList);

			scheduledBeats.add(beatInfo);

		}

		Set<String> blockedDates = new HashSet<>();

		for (LocalDate d = startDate; !d.isAfter(endDate); d = d.plusDays(1)) {

			if (d.getDayOfWeek() == DayOfWeek.SUNDAY) blockedDates.add(d.toString());

		}

		for (PublicHolidays h : holidays) blockedDates.add(h.getDate().toString());

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

		response.put("holidays", holidayList);

		response.put("scheduledBeats", scheduledBeats);

		response.put("blockedDates", blockedDates);

		return responseSender.ok(response);

	}

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

	public ResponseEntity<?> scheduleOnCalendar(

			HttpServletRequest request,

			@RequestParam String planGroupId,

			@RequestParam String dates,

			@RequestParam(required = false) String beatName,

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

		int beatId = Integer.parseInt(planGroupId);

		Gson gson = new Gson();

		List<String> dateList = gson.fromJson(dates, new TypeToken<List<String>>() {

		}.getType());

		Beat beat = beatRepository.selectById(beatId);

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

		if (beatName != null) beat.setName(beatName);

		if (beatColor != null && !beatColor.isEmpty()) beat.setBeatColor(beatColor);

		// Delete old schedules and create new

		beatScheduleRepository.deleteByBeatId(beatId);

		LocalDate schEndDate = dateList.isEmpty() ? null : LocalDate.parse(dateList.get(dateList.size() - 1));

		for (int i = 0; i < dateList.size() && i < beat.getTotalDays(); i++) {

			BeatSchedule schedule = new BeatSchedule();

			schedule.setBeatId(beatId);

			schedule.setStartDate(LocalDate.parse(dateList.get(i)));

			schedule.setEndDate(schEndDate);

			schedule.setDayNumber(i + 1);

			schedule.setEndAction(i == dateList.size() - 1 ? "HOME" : "DAYBREAK");

			schedule.setCreatedTimestamp(LocalDateTime.now());

			beatScheduleRepository.persist(schedule);

		}

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

		response.put("status", true);

		response.put("message", "Beat scheduled successfully");

		return responseSender.ok(response);

	}

	// Drag-drop scheduling — adds schedule dates to the EXISTING beat (no new beat created)

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

	public ResponseEntity<?> repeatBeat(

			HttpServletRequest request,

			@RequestParam String sourcePlanGroupId,

			@RequestParam int authUserId,

			@RequestParam String dates) throws Exception {

		int beatId = Integer.parseInt(sourcePlanGroupId);

		Gson gson = new Gson();

		List<String> dateList = gson.fromJson(dates, new TypeToken<List<String>>() {

		}.getType());

		Beat beat = beatRepository.selectById(beatId);

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

		// Remove placeholder (unscheduled) schedule rows

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

		for (BeatSchedule s : existing) {

			if (s.getStartDate() != null && s.getStartDate().getYear() == 9999) {

				beatScheduleRepository.delete(s);

			}

		}

		// Add new real-date schedule rows for the existing beat

		LocalDate repeatEndDate = dateList.isEmpty() ? null : LocalDate.parse(dateList.get(dateList.size() - 1));

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

			BeatSchedule schedule = new BeatSchedule();

			schedule.setBeatId(beatId);

			schedule.setStartDate(LocalDate.parse(dateList.get(i)));

			schedule.setEndDate(repeatEndDate);

			schedule.setDayNumber(i + 1);

			schedule.setEndAction(i == dateList.size() - 1 ? "HOME" : "DAYBREAK");

			schedule.setCreatedTimestamp(LocalDateTime.now());

			beatScheduleRepository.persist(schedule);

		}

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

		response.put("status", true);

		response.put("planGroupId", String.valueOf(beatId));

		response.put("message", "Beat scheduled successfully");

		return responseSender.ok(response);

	}

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

	public ResponseEntity<?> getAvailableSlots(

			@RequestParam int authUserId,

			@RequestParam String month,

			@RequestParam int daysNeeded) {

		YearMonth ym = YearMonth.parse(month);

		LocalDate startDate = ym.atDay(1);

		LocalDate endDate = ym.atEndOfMonth();

		LocalDate today = LocalDate.now();

		Set<LocalDate> blocked = new HashSet<>();

		for (LocalDate d = startDate; !d.isAfter(endDate); d = d.plusDays(1)) {

			if (d.getDayOfWeek() == DayOfWeek.SUNDAY) blocked.add(d);

			if (!d.isAfter(today)) blocked.add(d);

		}

		List<PublicHolidays> holidays = publicHolidaysRepository.selectAllBetweenDates(startDate, endDate);

		for (PublicHolidays h : holidays) blocked.add(h.getDate());

		// Get all scheduled dates for this user

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

		for (Beat b : userBeats) {

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

			for (BeatSchedule s : schedules) blocked.add(s.getStartDate());

		}

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

		for (LocalDate d = startDate.isAfter(today) ? startDate : today.plusDays(1);

			 !d.isAfter(endDate) && available.size() < daysNeeded;

			 d = d.plusDays(1)) {