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.entity.auth.VonageAgent;
import com.spice.profitmandi.dao.repository.auth.VonageAgentRepository;
import com.spice.profitmandi.dao.repository.dtr.LeadRecordingAccessRepository;
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. Public so the drawer can say it is showing a slice. */
    public 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 LeadRecordingAccessRepository leadRecordingAccessRepository;

    @Autowired
    private VonageAgentRepository vonageAgentRepository;

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

    // ---- Standard report suite (SOP §18.2) --------------------------------------------------

    /**
     * BGC Daily: calls made, connect rate, average talk time and dispositions — per agent, per day.
     *
     * <p>Real telephony, not the {@code contacted / days} proxy the tile used to show. That proxy
     * was invented before {@code user.lead_call} existed and reported a plausible-looking number
     * that no call had ever contributed to.
     */
    public Map<String, Object> bgcDaily(LocalDateTime from, LocalDateTime to) {
        List<Object[]> rows = leadCallRepository.countDailyByAgent(from, to);
        java.util.Set<Integer> authIds = new java.util.HashSet<>();
        for (Object[] r : rows) {
            if (r[0] != null) authIds.add(((Number) r[0]).intValue());
        }
        Map<Integer, AuthUser> agents = new HashMap<>();
        if (!authIds.isEmpty()) {
            for (AuthUser u : authRepository.selectByIds(new ArrayList<>(authIds))) agents.put(u.getId(), u);
        }

        List<Map<String, Object>> out = new ArrayList<>();
        long totCalls = 0, totConn = 0, totTalk = 0;
        for (Object[] r : rows) {
            int authId = r[0] == null ? 0 : ((Number) r[0]).intValue();
            long calls = ((Number) r[2]).longValue();
            long conn = ((Number) r[3]).longValue();
            long talk = ((Number) r[4]).longValue();
            totCalls += calls; totConn += conn; totTalk += talk;
            AuthUser a = agents.get(authId);
            Map<String, Object> m = new LinkedHashMap<>();
            m.put("authId", authId);
            m.put("agent", a != null ? a.getFullName() : ("#" + authId));
            m.put("day", r[1] == null ? null : r[1].toString());
            m.put("calls", calls);
            m.put("connected", conn);
            m.put("connectPct", calls > 0 ? Math.round(conn * 1000.0 / calls) / 10.0 : 0);
            // Average over CONNECTED calls: dividing by every dial would make a day of unanswered
            // ringing look like short conversations rather than no conversations.
            m.put("avgTalkSeconds", conn > 0 ? Math.round(talk / (double) conn) : 0);
            m.put("longestSeconds", ((Number) r[5]).longValue());
            out.add(m);
        }

        Map<String, Object> res = new LinkedHashMap<>();
        res.put("rows", out);
        res.put("calls", totCalls);
        res.put("connected", totConn);
        res.put("connectPct", totCalls > 0 ? Math.round(totConn * 1000.0 / totCalls) / 10.0 : 0);
        res.put("avgTalkSeconds", totConn > 0 ? Math.round(totTalk / (double) totConn) : 0);
        res.put("talkMinutes", Math.round(totTalk / 60.0));
        return res;
    }

    /**
     * Audit Hygiene (SOP §17): the breaches an audit review actually looks for.
     *
     * <p>Each figure is either measured or reported as unavailable — never estimated. A hygiene
     * report that guesses is worse than one that admits a gap, because the gap is the finding.
     */
    public Map<String, Object> auditHygiene(LocalDateTime from, LocalDateTime to) {
        LocalDateTime now = LocalDateTime.now();
        Map<String, Object> m = new LinkedHashMap<>();

        // Same source the Command Center's attention tiles use, so the two cannot disagree.
        Object[] att = leadRepository.attentionCounts(null, now, now.plusHours(1));
        m.put("slaBreached", num(att, 0));
        m.put("hold", num(att, 1));
        m.put("missingDisposition", num(att, 3));

        m.put("dndRegister", leadDndRepository.count());
        List<Object[]> breaches = leadCallRepository.selectDndBreaches(from, to, 50);
        List<Map<String, Object>> bl = new ArrayList<>();
        for (Object[] b : breaches) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put("callId", ((Number) b[0]).intValue());
            row.put("leadId", ((Number) b[1]).intValue());
            row.put("authId", b[2] == null ? 0 : ((Number) b[2]).intValue());
            row.put("mobile", b[3] == null ? null : b[3].toString());
            row.put("when", b[4] == null ? null : b[4].toString());
            bl.add(row);
        }
        m.put("dndBreaches", bl);
        m.put("dndBreachCount", bl.size());

        m.put("recordingOpens", leadRecordingAccessRepository.countInWindow(from, to, true));
        m.put("recordingDenied", leadRecordingAccessRepository.countInWindow(from, to, false));
        return m;
    }

    // ---- Live call board ---------------------------------------------------------------------

    /**
     * A non-terminal call older than this is not believed. Only the browser's 3-second poll
     * terminalises a VBC call, so an agent who closed the tab mid-call leaves a row that is
     * forever RINGING/ANSWERED with a null ended_at. Without this the board would show them On
     * Call indefinitely — wrong in its single most-read column.
     */
    private static final int LIVE_CALL_STALE_MINUTES = 30;
    /** Just off a call and no outcome logged yet. */
    private static final int WRAP_UP_MINUTES = 2;
    /** No call for this long and the agent reads as idle rather than merely available. */
    private static final int IDLE_MINUTES = 15;

    /**
     * Per-agent live board (the LMS answer to the RBM Call Target Summary).
     *
     * <p>The roster is {@code auth.vonage_agent}, not every user with leads: an agent without a
     * device mapping cannot dial at all, so they have no place on a call board.
     *
     * <p>Status is derived from call activity — Vonage exposes no presence of any kind, so there is
     * deliberately no Login or Break here rather than a guess dressed up as one.
     */
    public Map<String, Object> liveCallBoard() {
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime dayStart = now.toLocalDate().atStartOfDay();

        Map<Integer, Object[]> calls = byAuthId(leadCallRepository.selectLiveAgentBoard(dayStart, now));
        Map<Integer, Object[]> leads = byAuthId(leadRepository.selectAgentLeadCounters(now));
        Map<Integer, Object[]> acts = byAuthId(leadActivityRepository.countAgentActivity(dayStart, now));

        List<VonageAgent> roster = vonageAgentRepository.selectAll();
        java.util.Set<Integer> ids = new java.util.HashSet<>();
        for (VonageAgent a : roster) {
            ids.add(a.getAuthId());
        }
        Map<Integer, AuthUser> users = new HashMap<>();
        if (!ids.isEmpty()) {
            for (AuthUser u : authRepository.selectByIds(new ArrayList<>(ids))) {
                users.put(u.getId(), u);
            }
        }

        List<Map<String, Object>> rows = new ArrayList<>();
        long totCalls = 0, totConn = 0, totTalk = 0, onCallNow = 0;

        for (VonageAgent agent : roster) {
            int authId = agent.getAuthId();
            Object[] c = calls.get(authId);
            Object[] l = leads.get(authId);
            Object[] a = acts.get(authId);
            AuthUser u = users.get(authId);

            long cCalls = c == null ? 0 : num(c, 1);
            long cConn = c == null ? 0 : num(c, 2);
            long cTalk = c == null ? 0 : num(c, 3);
            LocalDateTime lastStarted = c == null ? null : ts(c, 5);
            LocalDateTime lastEnded = c == null ? null : ts(c, 6);
            Integer liveCallId = c == null || c[7] == null ? null : ((Number) c[7]).intValue();
            LocalDateTime liveStarted = c == null ? null : ts(c, 9);

            // A live row past the staleness window is an abandoned tab, not a conversation.
            boolean stale = liveStarted != null && liveStarted.isBefore(now.minusMinutes(LIVE_CALL_STALE_MINUTES));
            boolean live = liveCallId != null && !stale;

            String status;
            LocalDateTime since;
            if (!agent.isActive()) {
                status = "—";
                since = null;
            } else if (live) {
                status = "On Call";
                since = liveStarted;
                onCallNow++;
            } else if (lastEnded != null && lastEnded.isAfter(now.minusMinutes(WRAP_UP_MINUTES))) {
                status = "Wrap Up";
                since = lastEnded;
            } else if (lastEnded != null && lastEnded.isBefore(now.minusMinutes(IDLE_MINUTES))) {
                status = "Idle";
                since = lastEnded;
            } else if (lastEnded != null) {
                status = "Available";
                since = lastEnded;
            } else {
                status = "Available";
                since = null;
            }

            Map<String, Object> r = new LinkedHashMap<>();
            r.put("authId", authId);
            r.put("agent", u != null ? u.getFullName() : agent.getVonageUserName());
            r.put("extension", agent.getVbcExtension());
            r.put("status", status);
            // Computed, never stored — the RBM board does the same rather than persisting a clock.
            r.put("sinceSeconds", since == null ? null : java.time.Duration.between(since, now).getSeconds());
            r.put("staleCall", stale);
            r.put("liveCallId", live ? liveCallId : null);
            r.put("liveLeadId", live && c[8] != null ? ((Number) c[8]).intValue() : null);
            r.put("calls", cCalls);
            r.put("connected", cConn);
            r.put("connectPct", cCalls > 0 ? Math.round(cConn * 1000.0 / cCalls) / 10.0 : 0);
            r.put("talkSeconds", cTalk);
            r.put("longestSeconds", c == null ? 0 : num(c, 4));
            r.put("lastCallAt", lastStarted == null ? null : lastStarted.toString());
            r.put("assigned", l == null ? 0 : num(l, 1));
            r.put("slaBreached", l == null ? 0 : num(l, 2));
            r.put("touched", a == null ? 0 : num(a, 1));
            r.put("dispositioned", a == null ? 0 : num(a, 2));
            rows.add(r);

            totCalls += cCalls; totConn += cConn; totTalk += cTalk;
        }

        Map<String, Object> out = new LinkedHashMap<>();
        out.put("rows", rows);
        out.put("agents", rows.size());
        out.put("onCallNow", onCallNow);
        out.put("calls", totCalls);
        out.put("connected", totConn);
        out.put("connectPct", totCalls > 0 ? Math.round(totConn * 1000.0 / totCalls) / 10.0 : 0);
        out.put("talkMinutes", Math.round(totTalk / 60.0));
        out.put("asOf", now.toString());
        return out;
    }

    /**
     * The full call log for a window — every call, with ring time, talk time, outcome and recording.
     *
     * <p>The agent's extension comes from {@code auth.vonage_agent}, not from the call's
     * {@code from_number}: click2dial addresses the agent by DEVICE, so that column holds an opaque
     * id like {@code VH7NdQbBHcmsR8wFLsiU}, which means nothing to anyone reading a report.
     */
    public Map<String, Object> callLog(LocalDateTime from, LocalDateTime to, int page, int pageSize) {
        int size = Math.min(Math.max(pageSize, 1), MAX_PAGE_SIZE);
        int p = Math.max(page, 1);
        long total = leadCallRepository.countCallLog(from, to);
        List<Object[]> rows = leadCallRepository.selectCallLog(from, to, (p - 1) * size, size);

        java.util.Set<Integer> authIds = new java.util.HashSet<>();
        for (Object[] r : rows) {
            if (r[7] != null) {
                authIds.add(((Number) r[7]).intValue());
            }
        }
        Map<Integer, AuthUser> users = new HashMap<>();
        Map<Integer, String> extByAuth = new HashMap<>();
        if (!authIds.isEmpty()) {
            for (AuthUser u : authRepository.selectByIds(new ArrayList<>(authIds))) {
                users.put(u.getId(), u);
            }
            for (VonageAgent a : vonageAgentRepository.selectAll()) {
                extByAuth.put(a.getAuthId(), a.getVbcExtension());
            }
        }

        List<Map<String, Object>> out = new ArrayList<>();
        for (Object[] r : rows) {
            int authId = r[7] == null ? 0 : ((Number) r[7]).intValue();
            AuthUser u = users.get(authId);
            Map<String, Object> m = new LinkedHashMap<>();
            m.put("callId", ((Number) r[0]).intValue());
            m.put("startedAt", str(ts(r, 1)));
            m.put("answeredAt", str(ts(r, 2)));
            m.put("endedAt", str(ts(r, 3)));
            m.put("status", r[4] == null ? null : r[4].toString());
            // Negative or null when never answered — normalise to null so the UI shows a dash.
            Long ring = r[5] == null ? null : ((Number) r[5]).longValue();
            m.put("ringSeconds", ring != null && ring >= 0 ? ring : null);
            m.put("talkSeconds", num(r, 6));
            m.put("authId", authId);
            m.put("agent", u != null ? u.getFullName() : (authId > 0 ? "#" + authId : "—"));
            m.put("extension", extByAuth.get(authId));
            m.put("leadId", r[8] == null ? null : ((Number) r[8]).intValue());
            m.put("toNumber", r[9] == null ? null : r[9].toString());
            m.put("direction", r[10] == null ? null : r[10].toString());
            m.put("lmsCode", r[11] == null ? null : r[11].toString());
            m.put("retailer", r[12] == null ? null : r[12].toString().trim());
            m.put("outlet", r[13] == null ? null : r[13].toString());
            m.put("recording", r[14] == null ? "none" : r[14].toString());
            m.put("disposition", r[15] == null ? null : r[15].toString());
            out.add(m);
        }

        Map<String, Object> res = new LinkedHashMap<>();
        res.put("rows", out);
        res.put("total", total);
        res.put("page", p);
        res.put("pageSize", size);
        res.put("pages", (int) Math.ceil(total / (double) size));
        return res;
    }

    private String str(LocalDateTime t) {
        return t == null ? null : t.toString();
    }

    /** Index an aggregate result by its first column (the auth id). */
    private Map<Integer, Object[]> byAuthId(List<Object[]> rows) {
        Map<Integer, Object[]> m = new HashMap<>();
        if (rows != null) {
            for (Object[] r : rows) {
                if (r[0] != null) {
                    m.put(((Number) r[0]).intValue(), r);
                }
            }
        }
        return m;
    }

    /** Native queries hand back java.sql.Timestamp; the entity side works in LocalDateTime. */
    private LocalDateTime ts(Object[] row, int i) {
        Object v = row[i];
        if (v == null) {
            return null;
        }
        if (v instanceof java.sql.Timestamp) {
            return ((java.sql.Timestamp) v).toLocalDateTime();
        }
        return v instanceof LocalDateTime ? (LocalDateTime) v : null;
    }

    // ---- 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) {
        String type = bucketType == null ? "" : bucketType.trim().toUpperCase();
        String key = bucketKey;
        // The funnel asks for a stage cumulatively -- "reached CONTACTED" means CONTACTED or anything
        // beyond it, which is exactly how the bar was counted. Expand it here, where the happy path
        // already lives, rather than teaching the repository the order of the lifecycle.
        if ("STAGE_REACHED".equals(type)) {
            type = "STAGE_IN";
            key = stagesFrom(bucketKey);
        }
        LocalDateTime now = LocalDateTime.now();
        List<Integer> ids = leadRepository.selectLeadIdsForBucket(type, key, 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 given stage and every stage after it on the happy path, comma-separated -- the set the
     * cumulative funnel bar counted. An unrecognised stage (a terminal one such as NOT_INTERESTED,
     * which is not on the path) passes through unchanged and matches only itself.
     */
    private String stagesFrom(String stage) {
        String wanted = stage == null ? "" : stage.trim();
        int idx = -1;
        for (int i = 0; i < FUNNEL.length; i++) {
            if (FUNNEL[i].name().equalsIgnoreCase(wanted)) {
                idx = i;
                break;
            }
        }
        if (idx < 0) {
            return wanted;
        }
        StringBuilder sb = new StringBuilder();
        for (int i = idx; i < FUNNEL.length; i++) {
            if (sb.length() > 0) {
                sb.append(',');
            }
            sb.append(FUNNEL[i].name());
        }
        return sb.toString();
    }

    /**
     * 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());
            // Lets the assign picker surface whoever covers this lead's region first.
            r.put("regionId", l.getRegionId());
            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;
    }
}