Subversion Repositories SmartDukaan

package com.spice.profitmandi.service;

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

import com.spice.profitmandi.dao.entity.cs.Region;

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

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

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

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

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

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

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

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

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

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

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

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

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

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

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

import org.springframework.stereotype.Service;

import java.time.LocalDateTime;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

/**

 * Read-only aggregation for the LMS management dashboards (SOP §18) — Command Center + Dashboards.

 * Buckets EVERY lead: rows with a real {@link LeadStage} use it; legacy rows derive one in SQL

 * (see {@link LeadRepository#countByDerivedStage}). Returns plain JSON-ready Maps so the controller

 * can {@code gson.toJson(...)} them straight into the page / AJAX endpoints. No mutation, no schema change.

 *

 * <p>Coverage caveat (SOP §18.2): call/recording/order metrics (BGC call volume, RBM servicing, DND) are

 * NOT in the {@code Lead} schema, so those dashboards are surfaced as placeholders by the views. What this

 * service computes is everything derivable from the lead record: stage funnel, SLA compliance, ageing,

 * attention queues and the roll-up KPIs.

 */

@Service

public class LmsDashboardService {

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

    /** Ordered happy-path for cumulative "reached" funnel math. */

    private static final LeadStage[] FUNNEL = LeadStage.HAPPY_PATH;

    /** Hard ceiling on one page of the pipeline table — a caller asking for 10,000 rows gets 500. */

    private static final int MAX_PAGE_SIZE = 500;

    /** Leads not touched for this many days count as "ageing" (SOP §18.2). */

    private static final int AGEING_DAYS = 7;

    /** Most-recent weeks shown in the SLA owner heat-map. */

    private static final int HEATMAP_WEEKS = 6;

    /** Cap on rows returned to a drill-down drawer. */

    private static final int DRILL_LIMIT = 100;

    @Autowired

    private LeadRepository leadRepository;

    @Autowired

    private RegionRepository regionRepository;

    @Autowired

    private AuthRepository authRepository;

    @Autowired

    private LmsAssignmentService lmsAssignmentService;

    @Autowired

    private LeadActivityRepository leadActivityRepository;

    @Autowired

    private LeadCallRepository leadCallRepository;

    @Autowired

    private LeadDndRepository leadDndRepository;

    @Autowired

    private LeadLiveLocationRepository leadLiveLocationRepository;

    // ---- Command Center + shared summary ----------------------------------------------------

    /** KPIs + lifecycle funnel + attention queue for the given region / created-date window. */

    public Map<String, Object> summary(Integer regionId, LocalDateTime from, LocalDateTime to) {

        Map<String, Long> stage = toCountMap(leadRepository.countByDerivedStage(regionId, from, to));

        long total = 0;

        for (Long v : stage.values()) {

            total += v;

        }

        // Cumulative "reached at least this stage" along the happy path (monotonic, drives the funnel bars).

        long[] reached = new long[FUNNEL.length];

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

            long sum = 0;

            for (int j = i; j < FUNNEL.length; j++) {

                sum += stage.getOrDefault(FUNNEL[j].name(), 0L);

            }

            reached[i] = sum;

        }

        LocalDateTime now = LocalDateTime.now();

        Object[] att = leadRepository.attentionCounts(regionId, now, now.plusHours(1));

        long breachedNoContact = num(att, 0);

        long hold = num(att, 1);

        long slaUnder1h = num(att, 2);

        long missingDisp = num(att, 3);

        long qualifiedNoBeat = num(att, 4);

        long onboardedPending = num(att, 5);

        // Onboarded value (SOP §18.2) = summed potential of ONBOARDED + ACTIVE leads.

        Map<String, Double> potByStage = toSumMap(leadRepository.sumPotentialByDerivedStage(regionId, from, to));

        double onboardedValue = potByStage.getOrDefault("ONBOARDED", 0d) + potByStage.getOrDefault("ACTIVE", 0d);

        long onboarded = stage.getOrDefault("ONBOARDED", 0L) + stage.getOrDefault("ACTIVE", 0L);

        long assigned = stage.getOrDefault("ASSIGNED", 0L);

        long contacted = stage.getOrDefault("CONTACTED", 0L);

        long qualified = stage.getOrDefault("QUALIFIED", 0L);

        long newCount = stage.getOrDefault("NEW", 0L);

        // KPI tiles (Command Center).

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

        kpis.add(kpi("new", "New", newCount, "in selected period", "navy"));

        kpis.add(kpi("awaiting", "Awaiting 1st contact", assigned, "SLA 3–5 hrs running", "amber"));

        kpis.add(kpi("sla", "SLA at risk / breached", breachedNoContact + slaUnder1h,

                breachedNoContact + " breached · " + slaUnder1h + " < 1 hr left", "red"));

        kpis.add(kpi("contacted", "Contacted", contacted, "dispositioned", "navy"));

        kpis.add(kpi("qualified", "Qualified", qualified, "value validated", "navy"));

        kpis.add(kpi("onboarded", "Onboarded", onboarded, "of " + total + " in scope", "good"));

        // Lifecycle funnel.

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

        long topN = total > 0 ? total : 1;

        // Top of funnel = every lead in scope.

        funnel.add(funnelStep("ALL", "All leads", total, pct(total, topN)));

        for (int i = 1; i < FUNNEL.length; i++) { // skip NEW itself; ALL is the 100% anchor

            LeadStage s = FUNNEL[i];

            funnel.add(funnelStep(s.name(), pretty(s), reached[i], pct(reached[i], topN)));

        }

        // Attention queue.

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

        attention.add(attn("BREACHED_NO_CONTACT", "SLA breached — no first contact", "First contact overdue", breachedNoContact, "red"));

        attention.add(attn("HOLD", "Assigned but BM/RSM not mapped", "Region has no active owner", hold, "red"));

        attention.add(attn("SLA_UNDER_1H", "SLA < 1 hr remaining", "First contact due soon", slaUnder1h, "amber"));

        attention.add(attn("MISSING_DISP", "Calls missing a disposition", "Contacted, not dispositioned", missingDisp, "amber"));

        attention.add(attn("QUALIFIED_NO_BEAT", "Qualified, no beat plan yet", "Awaiting an ASM beat stop", qualifiedNoBeat, "navy"));

        attention.add(attn("ONBOARDED_PENDING", "Onboarded — pending RBM handover", "Handover to post-sales", onboardedPending, "violet"));

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

        out.put("total", total);

        out.put("onboardedValue", onboardedValue);

        out.put("conversionPct", pct(onboarded, topN));

        out.put("kpis", kpis);

        out.put("funnel", funnel);

        out.put("attention", attention);

        return out;

    }

    // ---- Dashboards: state funnel -----------------------------------------------------------

    /** One row per region: reached counts across the funnel, conversion % and SLA %. */

    public Map<String, Object> stateFunnel(LocalDateTime from, LocalDateTime to) {

        // regionCode -> (stage -> count)

        Map<String, Map<String, Long>> byRegion = new LinkedHashMap<>();

        for (Object[] row : leadRepository.countByRegionAndDerivedStage(from, to)) {

            String rc = str(row[0]);

            String s = str(row[1]);

            long c = ((Number) row[2]).longValue();

            byRegion.computeIfAbsent(rc, k -> new HashMap<>()).merge(s, c, Long::sum);

        }

        // regionCode -> sla %

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

        for (Object[] row : leadRepository.slaComplianceByRegion(from, to)) {

            String rc = str(row[0]);

            long totalDue = ((Number) row[1]).longValue();

            long met = ((Number) row[2]).longValue();

            slaByRegion.put(rc, (int) pct(met, totalDue));

        }

        // Friendly names from cs.region (keyed by upper region_code).

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

        for (Region r : regionRepository.selectAll()) {

            if (r.getRegionCode() != null && !r.getRegionCode().trim().isEmpty()) {

                nameByCode.put(r.getRegionCode().trim().toUpperCase(), r.getName());

            }

        }

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

        for (Map.Entry<String, Map<String, Long>> e : byRegion.entrySet()) {

            String code = e.getKey();

            Map<String, Long> stage = e.getValue();

            long total = 0;

            for (Long v : stage.values()) {

                total += v;

            }

            long contacted = reachedAtLeast(stage, LeadStage.CONTACTED);

            long qualified = reachedAtLeast(stage, LeadStage.QUALIFIED);

            long visited = reachedAtLeast(stage, LeadStage.VISITED);

            long onboarded = reachedAtLeast(stage, LeadStage.ONBOARDED);

            Map<String, Object> r = new LinkedHashMap<>();

            r.put("regionCode", code);

            r.put("regionName", nameByCode.getOrDefault(code, "UNMAPPED".equals(code) ? "Unmapped" : code));

            r.put("newCount", total);

            r.put("contacted", contacted);

            r.put("qualified", qualified);

            r.put("visited", visited);

            r.put("onboarded", onboarded);

            r.put("convPct", pct(onboarded, total));

            r.put("slaPct", slaByRegion.getOrDefault(code, 0));

            rows.add(r);

        }

        rows.sort((a, b) -> Long.compare(((Number) b.get("newCount")).longValue(), ((Number) a.get("newCount")).longValue()));

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

        out.put("rows", rows);

        return out;

    }

    // ---- Dashboards: SLA owner heat-map -----------------------------------------------------

    /** BM × recent-week on-time %, for the SLA compliance heat-map. */

    public Map<String, Object> slaHeatmap(LocalDateTime from, LocalDateTime to) {

        // bmId -> (isoYearWeek -> pct)

        Map<Integer, Map<Integer, Integer>> byBm = new LinkedHashMap<>();

        java.util.TreeSet<Integer> weekSet = new java.util.TreeSet<>();

        for (Object[] row : leadRepository.slaComplianceByBmAndWeek(from, to)) {

            Integer bmId = ((Number) row[0]).intValue();

            Integer wk = ((Number) row[1]).intValue();

            long totalDue = ((Number) row[2]).longValue();

            long met = ((Number) row[3]).longValue();

            weekSet.add(wk);

            byBm.computeIfAbsent(bmId, k -> new HashMap<>()).put(wk, (int) pct(met, totalDue));

        }

        // Keep the most recent HEATMAP_WEEKS weeks.

        List<Integer> allWeeks = new ArrayList<>(weekSet);

        List<Integer> weeks = allWeeks.size() > HEATMAP_WEEKS

                ? allWeeks.subList(allWeeks.size() - HEATMAP_WEEKS, allWeeks.size())

                : allWeeks;

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

        for (Map.Entry<Integer, Map<Integer, Integer>> e : byBm.entrySet()) {

            AuthUser bm = authRepository.selectById(e.getKey());

            List<Object> cells = new ArrayList<>();

            for (Integer wk : weeks) {

                Integer p = e.getValue().get(wk);

                Map<String, Object> cell = new LinkedHashMap<>();

                cell.put("week", weekLabel(wk));

                cell.put("pct", p); // null → no data that week

                cells.add(cell);

            }

            Map<String, Object> r = new LinkedHashMap<>();

            r.put("bmId", e.getKey());

            r.put("bmName", bm != null ? bm.getFullName() : ("BM #" + e.getKey()));

            r.put("cells", cells);

            bms.add(r);

        }

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

        for (Integer wk : weeks) {

            weekLabels.add(weekLabel(wk));

        }

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

        out.put("weeks", weekLabels);

        out.put("bms", bms);

        return out;

    }

    // ---- Dashboards: ageing -----------------------------------------------------------------

    /** Open leads stuck (untouched > {@value #AGEING_DAYS} days) bucketed by stage. */

    public Map<String, Object> ageing() {

        LocalDateTime olderThan = LocalDateTime.now().minusDays(AGEING_DAYS);

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

        long total = 0;

        for (Object[] row : leadRepository.ageingByDerivedStage(olderThan)) {

            String s = str(row[0]);

            long c = ((Number) row[1]).longValue();

            total += c;

            Map<String, Object> r = new LinkedHashMap<>();

            r.put("stage", s);

            r.put("label", pretty(safeStage(s)));

            r.put("count", c);

            rows.add(r);

        }

        rows.sort((a, b) -> Long.compare(((Number) b.get("count")).longValue(), ((Number) a.get("count")).longValue()));

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

        out.put("days", AGEING_DAYS);

        out.put("total", total);

        out.put("rows", rows);

        return out;

    }

    // ---- Drill-down -------------------------------------------------------------------------

    /** Lead rows for a drill bucket, shaped for the drawer table. */

    public List<Map<String, Object>> drill(String bucketType, String bucketKey, Integer regionId,

                                            LocalDateTime from, LocalDateTime to) {

        LocalDateTime now = LocalDateTime.now();

        List<Integer> ids = leadRepository.selectLeadIdsForBucket(bucketType, bucketKey, regionId,

                now, now.plusHours(1), from, to, DRILL_LIMIT);

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

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

            return out;

        }

        // selectAllByIds returns id-desc; preserve the bucket's newest-first order.

        Map<Integer, Lead> leadById = new HashMap<>();

        for (Lead l : leadRepository.selectAllByIds(ids)) {

            leadById.put(l.getId(), l);

        }

        List<Lead> ordered = new ArrayList<>(ids.size());

        for (Integer id : ids) {

            Lead l = leadById.get(id);

            if (l != null) {

                ordered.add(l);

            }

        }

        return toRows(ordered);

    }

    /**

     * The Lead-pipeline table: every matching lead, not a client-side slice of the first page.

     *

     * <p>Returns the page plus the true total, so the table can report "50 of 229" rather than

     * implying the twelve rows it drew are all there are. A non-empty {@code q} searches across all

     * leads and ignores the date window — see {@code LeadRepository.selectPipeline}.

     */

    public Map<String, Object> pipeline(String q, Integer regionId, LocalDateTime from, LocalDateTime to,

                                        String sort, String dir, int page, int pageSize) {

        int size = Math.min(Math.max(pageSize, 1), MAX_PAGE_SIZE);

        int p = Math.max(page, 1);

        boolean searching = q != null && !q.trim().isEmpty();

        long total = leadRepository.countPipeline(q, regionId, from, to);

        List<Lead> leads = leadRepository.selectPipeline(q, regionId, from, to, sort, dir, (p - 1) * size, size);

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

        out.put("rows", toRows(leads));

        out.put("total", total);

        out.put("page", p);

        out.put("pageSize", size);

        out.put("pages", (int) Math.ceil(total / (double) size));

        out.put("sort", sort);

        out.put("dir", dir);

        // The caller renders a different caption when the date filter is not in play, so it has to

        // be told which mode the server actually used rather than inferring it.

        out.put("searching", searching);

        return out;

    }

    /**

     * Free-text lookup for the dashboard search box: LMS id / retailer / business / mobile / city.

     * Same row shape as {@link #drill}, so results render in the same drawer table.

     */

    public List<Map<String, Object>> search(String term) {

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

            return new ArrayList<>();

        }

        return toRows(leadRepository.selectByGlobalSearch(term.trim(), DRILL_LIMIT));

    }

    /** Shared drawer-row mapping for {@link #drill} and {@link #search}; owner names fetched in one batch. */

    private List<Map<String, Object>> toRows(List<Lead> leads) {

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

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

            return out;

        }

        // Owner names in one batch.

        java.util.Set<Integer> ownerIds = new java.util.HashSet<>();

        for (Lead l : leads) {

            if (l.getAssignTo() > 0) {

                ownerIds.add(l.getAssignTo());

            }

        }

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

        if (!ownerIds.isEmpty()) {

            for (AuthUser u : authRepository.selectByIds(new ArrayList<>(ownerIds))) {

                owners.put(u.getId(), u);

            }

        }

        for (Lead l : leads) {

            LeadStage eff = l.getEffectiveStage();

            AuthUser owner = owners.get(l.getAssignTo());

            Map<String, Object> r = new LinkedHashMap<>();

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

            r.put("lmsCode", l.getLmsCode() != null ? l.getLmsCode() : ("Lead #" + l.getId()));

            r.put("name", trimName(l.getFirstName(), l.getLastName()));

            r.put("outlet", l.getOutLetName());

            r.put("path", l.getCreationPath());

            r.put("stage", eff != null ? eff.name() : null);

            r.put("stageLabel", pretty(eff));

            r.put("owner", owner != null ? owner.getFullName() : (l.getAssignTo() > 0 ? "#" + l.getAssignTo() : "—"));

            r.put("slaState", lmsAssignmentService.slaState(l));

            r.put("value", l.getPotential());

            // Both are sortable columns on the pipeline table, so the row has to carry them.

            r.put("city", l.getCity());

            r.put("createdBy", l.getCreatedBy());

            out.add(r);

        }

        return out;

    }

    // ---- Lead Record (native, sts-mockup styled — read-only JSON) ---------------------------

    /** Full record for one lead: details + derived stage + SLA + append-only trail. Null if not found. */

    public Map<String, Object> record(int leadId) {

        Lead l = leadRepository.selectById(leadId);

        if (l == null) {

            return null;

        }

        LeadStage eff = l.getEffectiveStage();

        int stageIndex = -1;

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

            if (FUNNEL[i] == eff) {

                stageIndex = i;

            }

        }

        boolean terminal = eff == LeadStage.NOT_INTERESTED || eff == LeadStage.DROPPED;

        AuthUser owner = l.getAssignTo() > 0 ? authRepository.selectById(l.getAssignTo()) : null;

        AuthUser bm = (l.getOwnerBmId() != null && l.getOwnerBmId() > 0) ? authRepository.selectById(l.getOwnerBmId()) : null;

        // Append-only trail, newest first.

        List<LeadActivity> acts = leadActivityRepository.selectBYLeadId(leadId);

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

            LocalDateTime ta = a.getCreatedTimestamp(), tb = b.getCreatedTimestamp();

            if (ta == null && tb == null) return 0;

            if (ta == null) return 1;

            if (tb == null) return -1;

            return tb.compareTo(ta);

        });

        // Actor names in one batch.

        java.util.Set<Integer> ids = new java.util.HashSet<>();

        for (LeadActivity a : acts) {

            if (a.getAuthId() > 0) ids.add(a.getAuthId());

        }

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

        if (!ids.isEmpty()) {

            for (AuthUser u : authRepository.selectByIds(new ArrayList<>(ids))) {

                actors.put(u.getId(), u);

            }

        }

        // Calls keyed by id, so a disposition in the trail can carry the recording of the call it came

        // from. A disposition is the agent's account of the conversation; having to hunt for the audio

        // in a separate list to check it against the recording is the friction worth removing.

        Map<Integer, LeadCall> callById = new HashMap<>();

        List<LeadCall> calls = leadCallRepository.selectByLeadId(leadId);

        if (calls != null) {

            for (LeadCall c : calls) {

                callById.put(c.getId(), c);

            }

        }

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

        for (LeadActivity a : acts) {

            AuthUser act = actors.get(a.getAuthId());

            Map<String, Object> t = new LinkedHashMap<>();

            t.put("when", a.getCreatedTimestamp() == null ? null : a.getCreatedTimestamp().toString());

            t.put("actor", act != null ? act.getFullName() : (a.getAuthId() > 0 ? "#" + a.getAuthId() : "System"));

            t.put("type", a.getCommunicationType() == null ? null : a.getCommunicationType().name());

            t.put("body", a.getRemark());

            t.put("scheduled", a.getSchelduleTimestamp() == null ? null : a.getSchelduleTimestamp().toString());

            // Either handle is enough to play: the gated endpoint serves our archived copy when there

            // is one and proxies the vendor's otherwise, so an agent is not made to wait on the

            // archival sweep to hear a call. The URL is never the raw vendor one — playback goes

            // through /lms/recording/{id}, which enforces the §15 matrix and logs the open.

            LeadCall call = a.getLeadCallId() == null ? null : callById.get(a.getLeadCallId());

            if (call != null && (call.getRecordingObjectKey() != null || call.getRecordingUuid() != null)) {

                t.put("callId", call.getId());

                t.put("durationSeconds", call.getDurationSeconds());

                t.put("archived", call.getRecordingObjectKey() != null);

            }

            trail.add(t);

        }

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

        m.put("id", l.getId());

        m.put("lmsCode", l.getLmsCode());

        m.put("name", trimName(l.getFirstName(), l.getLastName()));

        m.put("outlet", l.getOutLetName());

        m.put("stage", eff != null ? eff.name() : null);

        m.put("stageLabel", pretty(eff));

        m.put("stageIndex", stageIndex);

        m.put("terminal", terminal);

        m.put("created", l.getCreatedTimestamp() == null ? null : l.getCreatedTimestamp().toString());

        m.put("createdBy", owner != null ? owner.getFullName() : null); // best-effort

        m.put("path", l.getCreationPath());

        m.put("contact", l.getLeadMobile());

        m.put("location", joinLoc(l.getAddress(), l.getCity(), l.getState()));

        m.put("regionCode", l.getRegionCode());

        m.put("value", l.getPotential());

        m.put("source", l.getSource());

        m.put("disposition", l.getDisposition() == null ? null : l.getDisposition().name());

        m.put("ownerBm", bm != null ? bm.getFullName() : null);

        m.put("owner", owner != null ? owner.getFullName() : null);

        m.put("assignmentStatus", l.getAssignmentStatus());

        m.put("slaState", lmsAssignmentService.slaState(l));

        m.put("firstContactDue", l.getFirstContactDue() == null ? null : l.getFirstContactDue().toString());

        m.put("firstContactedAt", l.getFirstContactedAt() == null ? null : l.getFirstContactedAt().toString());

        m.put("unreachableCount", l.getUnreachableCount());

        // The dashboard's record tab acts on the lead rather than only displaying it, so it needs

        // the same three things the in-app screen reads before it offers a control:

        //   - the parts of the address, not just the joined string, or an edit would have to

        //     re-split "addr, city, state" and would corrupt any value containing a comma;

        //   - the region id, so the edit form can preselect it (regionCode alone cannot);

        //   - whether the number is blocked and whether the geo-pin is approved, so the call and

        //     beat controls can say why they are unavailable instead of failing on submit.

        m.put("regionId", l.getRegionId());

        m.put("address", l.getAddress());

        m.put("city", l.getCity());

        m.put("state", l.getState());

        LeadDnd dnd = leadDndRepository.selectByMobile(l.getLeadMobile());

        m.put("dnd", dnd != null);

        m.put("dndReason", dnd == null ? null : dnd.getReason());

        // The pin itself, not just a yes/no: the record card offers a map link when it is approved

        // and pre-fills the manual-verify form with the existing coords when it is not, so nudging

        // a slightly-off pin does not mean typing it from scratch.

        LeadLiveLocation geo = leadLiveLocationRepository.selectByLeadId(leadId);

        m.put("geoApproved", geo != null && geo.getStatus() != null

                && "APPROVED".equalsIgnoreCase(geo.getStatus().toString()));

        m.put("geoStatus", geo == null || geo.getStatus() == null ? null : geo.getStatus().toString());

        if (geo != null && (geo.getLatitude() != 0 || geo.getLongitude() != 0)) {

            m.put("geoLat", geo.getLatitude());

            m.put("geoLng", geo.getLongitude());

        }

        m.put("geoPhotoId", geo == null ? 0 : geo.getImageDocumentId());

        m.put("trail", trail);

        return m;

    }

    private String joinLoc(String a, String c, String s) {

        StringBuilder sb = new StringBuilder();

        for (String p : new String[]{a, c, s}) {

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

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

                sb.append(p.trim());

            }

        }

        return sb.toString();

    }

    // ---- helpers ----------------------------------------------------------------------------

    private long reachedAtLeast(Map<String, Long> stage, LeadStage at) {

        long sum = 0;

        boolean counting = false;

        for (LeadStage s : FUNNEL) {

            if (s == at) {

                counting = true;

            }

            if (counting) {

                sum += stage.getOrDefault(s.name(), 0L);

            }

        }

        return sum;

    }

    private Map<String, Long> toCountMap(List<Object[]> rows) {

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

        for (Object[] r : rows) {

            if (r[0] == null) {

                continue;

            }

            m.merge(String.valueOf(r[0]), ((Number) r[1]).longValue(), Long::sum);

        }

        return m;

    }

    private Map<String, Double> toSumMap(List<Object[]> rows) {

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

        for (Object[] r : rows) {

            if (r[0] == null) {

                continue;

            }

            double v = r[1] == null ? 0d : ((Number) r[1]).doubleValue();

            m.merge(String.valueOf(r[0]), v, Double::sum);

        }

        return m;

    }

    private long num(Object[] row, int i) {

        if (row == null || i >= row.length || row[i] == null) {

            return 0L;

        }

        return ((Number) row[i]).longValue();

    }

    private long pct(long part, long whole) {

        if (whole <= 0) {

            return 0;

        }

        return Math.round((part * 100.0) / whole);

    }

    private String str(Object o) {

        return o == null ? "" : String.valueOf(o);

    }

    private Map<String, Object> kpi(String key, String label, long value, String foot, String tone) {

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

        m.put("key", key);

        m.put("label", label);

        m.put("value", value);

        m.put("foot", foot);

        m.put("tone", tone);

        return m;

    }

    private Map<String, Object> funnelStep(String stage, String label, long count, long pct) {

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

        m.put("stage", stage);

        m.put("label", label);

        m.put("count", count);

        m.put("pct", pct);

        return m;

    }

    private Map<String, Object> attn(String key, String title, String detail, long count, String tone) {

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

        m.put("key", key);

        m.put("title", title);

        m.put("detail", detail);

        m.put("count", count);

        m.put("tone", tone);

        return m;

    }

    private String trimName(String first, String last) {

        String f = first == null ? "" : first.trim();

        String l = last == null ? "" : last.trim();

        return (f + " " + l).trim();

    }

    private LeadStage safeStage(String s) {

        try {

            return LeadStage.valueOf(s);

        } catch (Exception e) {

            return null;

        }

    }

    /** "NEW" → "New", "BEAT_PLANNED" → "Beat planned". */

    private String pretty(LeadStage s) {

        if (s == null) {

            return "—";

        }

        String[] parts = s.name().toLowerCase().split("_");

        StringBuilder sb = new StringBuilder();

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

            if (parts[i].isEmpty()) {

                continue;

            }

            if (i == 0) {

                sb.append(Character.toUpperCase(parts[i].charAt(0))).append(parts[i].substring(1));

            } else {

                sb.append(' ').append(parts[i]);

            }

        }

        return sb.toString();

    }

    /** ISO yyyyww (YEARWEEK mode 3) → a short "w<week>" label. */

    private String weekLabel(int isoYearWeek) {

        int week = isoYearWeek % 100;

        return "W" + week;

    }

}