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. */

    /** Default drawer page size (drill) and the hard cap on search results. */

    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. Stages come

     * from LeadRepository's evidence-aware expression: BEAT_PLANNED / VISITED / ONBOARDED are read off

     * lead_route, location_tracking and LOI + stock payment, never off the hand-set stage column.

     */

    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), from, to);

        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, "LOI + stock payment · 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 beatPlanned = reachedAtLeast(stage, LeadStage.BEAT_PLANNED);

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

            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 and same created-date window as the Command Center's attention tiles, so the

        // two cannot disagree.

        Object[] att = leadRepository.attentionCounts(null, now, now.plusHours(1), from, to);

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

    /**

     * One page of lead rows for a drill bucket, shaped for the drawer table, plus the true total so

     * the drawer can page through every lead behind a bar instead of stopping at the first 100.

     */

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

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

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

        long total = leadRepository.countLeadsForBucket(type, key, regionId, now, now.plusHours(1), from, to);

        List<Integer> ids = leadRepository.selectLeadIdsForBucket(type, key, regionId,

                now, now.plusHours(1), from, to, (p - 1) * size, size);

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

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

        out.put("leads", rows);

        out.put("total", total);

        out.put("page", p);

        out.put("pageSize", size);

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

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

            }

        }

        rows.addAll(toRows(ordered));

        return out;

    }

    /**

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

            }

        }

        // Evidence-aware stage in one batch, so a row says the same thing as the bar it came from.

        List<Integer> leadIds = new ArrayList<>(leads.size());

        for (Lead l : leads) {

            leadIds.add(l.getId());

        }

        Map<Integer, String> stages = leadRepository.selectEffectiveStages(leadIds);

        for (Lead l : leads) {

            LeadStage eff = effectiveStage(l, stages);

            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 = effectiveStage(l, leadRepository.selectEffectiveStages(java.util.Collections.singletonList(leadId)));

        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) {