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.repository.dtr.LeadActivityRepository;
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;

    /** Leads not touched for this many days count as "ageing" (SOP §18.2). */
    private static final int AGEING_DAYS = 7;

    /** Most-recent weeks shown in the SLA owner heat-map. */
    private static final int HEATMAP_WEEKS = 6;

    /** Cap on rows returned to a drill-down drawer. */
    private static final int DRILL_LIMIT = 100;

    @Autowired
    private LeadRepository leadRepository;

    @Autowired
    private RegionRepository regionRepository;

    @Autowired
    private AuthRepository authRepository;

    @Autowired
    private LmsAssignmentService lmsAssignmentService;

    @Autowired
    private LeadActivityRepository leadActivityRepository;

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

    /** KPIs + lifecycle funnel + attention queue for the given region / created-date window. */
    public Map<String, Object> summary(Integer regionId, LocalDateTime from, LocalDateTime to) {
        Map<String, Long> stage = toCountMap(leadRepository.countByDerivedStage(regionId, from, to));
        long total = 0;
        for (Long v : stage.values()) {
            total += v;
        }

        // Cumulative "reached at least this stage" along the happy path (monotonic, drives the funnel bars).
        long[] reached = new long[FUNNEL.length];
        for (int i = 0; i < FUNNEL.length; i++) {
            long sum = 0;
            for (int j = i; j < FUNNEL.length; j++) {
                sum += stage.getOrDefault(FUNNEL[j].name(), 0L);
            }
            reached[i] = sum;
        }

        LocalDateTime now = LocalDateTime.now();
        Object[] att = leadRepository.attentionCounts(regionId, now, now.plusHours(1));
        long breachedNoContact = num(att, 0);
        long hold = num(att, 1);
        long slaUnder1h = num(att, 2);
        long missingDisp = num(att, 3);
        long qualifiedNoBeat = num(att, 4);
        long onboardedPending = num(att, 5);

        // Onboarded value (SOP §18.2) = summed potential of ONBOARDED + ACTIVE leads.
        Map<String, Double> potByStage = toSumMap(leadRepository.sumPotentialByDerivedStage(regionId, from, to));
        double onboardedValue = potByStage.getOrDefault("ONBOARDED", 0d) + potByStage.getOrDefault("ACTIVE", 0d);

        long onboarded = stage.getOrDefault("ONBOARDED", 0L) + stage.getOrDefault("ACTIVE", 0L);
        long assigned = stage.getOrDefault("ASSIGNED", 0L);
        long contacted = stage.getOrDefault("CONTACTED", 0L);
        long qualified = stage.getOrDefault("QUALIFIED", 0L);
        long newCount = stage.getOrDefault("NEW", 0L);

        // KPI tiles (Command Center).
        List<Map<String, Object>> kpis = new ArrayList<>();
        kpis.add(kpi("new", "New", newCount, "in selected period", "navy"));
        kpis.add(kpi("awaiting", "Awaiting 1st contact", assigned, "SLA 3–5 hrs running", "amber"));
        kpis.add(kpi("sla", "SLA at risk / breached", breachedNoContact + slaUnder1h,
                breachedNoContact + " breached · " + slaUnder1h + " < 1 hr left", "red"));
        kpis.add(kpi("contacted", "Contacted", contacted, "dispositioned", "navy"));
        kpis.add(kpi("qualified", "Qualified", qualified, "value validated", "navy"));
        kpis.add(kpi("onboarded", "Onboarded", onboarded, "of " + total + " in scope", "good"));

        // Lifecycle funnel.
        List<Map<String, Object>> funnel = new ArrayList<>();
        long topN = total > 0 ? total : 1;
        // Top of funnel = every lead in scope.
        funnel.add(funnelStep("ALL", "All leads", total, pct(total, topN)));
        for (int i = 1; i < FUNNEL.length; i++) { // skip NEW itself; ALL is the 100% anchor
            LeadStage s = FUNNEL[i];
            funnel.add(funnelStep(s.name(), pretty(s), reached[i], pct(reached[i], topN)));
        }

        // Attention queue.
        List<Map<String, Object>> attention = new ArrayList<>();
        attention.add(attn("BREACHED_NO_CONTACT", "SLA breached — no first contact", "First contact overdue", breachedNoContact, "red"));
        attention.add(attn("HOLD", "Assigned but BM/RSM not mapped", "Region has no active owner", hold, "red"));
        attention.add(attn("SLA_UNDER_1H", "SLA < 1 hr remaining", "First contact due soon", slaUnder1h, "amber"));
        attention.add(attn("MISSING_DISP", "Calls missing a disposition", "Contacted, not dispositioned", missingDisp, "amber"));
        attention.add(attn("QUALIFIED_NO_BEAT", "Qualified, no beat plan yet", "Awaiting an ASM beat stop", qualifiedNoBeat, "navy"));
        attention.add(attn("ONBOARDED_PENDING", "Onboarded — pending RBM handover", "Handover to post-sales", onboardedPending, "violet"));

        Map<String, Object> out = new LinkedHashMap<>();
        out.put("total", total);
        out.put("onboardedValue", onboardedValue);
        out.put("conversionPct", pct(onboarded, topN));
        out.put("kpis", kpis);
        out.put("funnel", funnel);
        out.put("attention", attention);
        return out;
    }

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

    /** One row per region: reached counts across the funnel, conversion % and SLA %. */
    public Map<String, Object> stateFunnel(LocalDateTime from, LocalDateTime to) {
        // regionCode -> (stage -> count)
        Map<String, Map<String, Long>> byRegion = new LinkedHashMap<>();
        for (Object[] row : leadRepository.countByRegionAndDerivedStage(from, to)) {
            String rc = str(row[0]);
            String s = str(row[1]);
            long c = ((Number) row[2]).longValue();
            byRegion.computeIfAbsent(rc, k -> new HashMap<>()).merge(s, c, Long::sum);
        }
        // regionCode -> sla %
        Map<String, Integer> slaByRegion = new HashMap<>();
        for (Object[] row : leadRepository.slaComplianceByRegion(from, to)) {
            String rc = str(row[0]);
            long totalDue = ((Number) row[1]).longValue();
            long met = ((Number) row[2]).longValue();
            slaByRegion.put(rc, (int) pct(met, totalDue));
        }
        // Friendly names from cs.region (keyed by upper region_code).
        Map<String, String> nameByCode = new HashMap<>();
        for (Region r : regionRepository.selectAll()) {
            if (r.getRegionCode() != null && !r.getRegionCode().trim().isEmpty()) {
                nameByCode.put(r.getRegionCode().trim().toUpperCase(), r.getName());
            }
        }

        List<Map<String, Object>> rows = new ArrayList<>();
        for (Map.Entry<String, Map<String, Long>> e : byRegion.entrySet()) {
            String code = e.getKey();
            Map<String, Long> stage = e.getValue();
            long total = 0;
            for (Long v : stage.values()) {
                total += v;
            }
            long contacted = reachedAtLeast(stage, LeadStage.CONTACTED);
            long qualified = reachedAtLeast(stage, LeadStage.QUALIFIED);
            long visited = reachedAtLeast(stage, LeadStage.VISITED);
            long onboarded = reachedAtLeast(stage, LeadStage.ONBOARDED);

            Map<String, Object> r = new LinkedHashMap<>();
            r.put("regionCode", code);
            r.put("regionName", nameByCode.getOrDefault(code, "UNMAPPED".equals(code) ? "Unmapped" : code));
            r.put("newCount", total);
            r.put("contacted", contacted);
            r.put("qualified", qualified);
            r.put("visited", visited);
            r.put("onboarded", onboarded);
            r.put("convPct", pct(onboarded, total));
            r.put("slaPct", slaByRegion.getOrDefault(code, 0));
            rows.add(r);
        }
        rows.sort((a, b) -> Long.compare(((Number) b.get("newCount")).longValue(), ((Number) a.get("newCount")).longValue()));

        Map<String, Object> out = new LinkedHashMap<>();
        out.put("rows", rows);
        return out;
    }

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

    /** BM × recent-week on-time %, for the SLA compliance heat-map. */
    public Map<String, Object> slaHeatmap(LocalDateTime from, LocalDateTime to) {
        // bmId -> (isoYearWeek -> pct)
        Map<Integer, Map<Integer, Integer>> byBm = new LinkedHashMap<>();
        java.util.TreeSet<Integer> weekSet = new java.util.TreeSet<>();
        for (Object[] row : leadRepository.slaComplianceByBmAndWeek(from, to)) {
            Integer bmId = ((Number) row[0]).intValue();
            Integer wk = ((Number) row[1]).intValue();
            long totalDue = ((Number) row[2]).longValue();
            long met = ((Number) row[3]).longValue();
            weekSet.add(wk);
            byBm.computeIfAbsent(bmId, k -> new HashMap<>()).put(wk, (int) pct(met, totalDue));
        }
        // Keep the most recent HEATMAP_WEEKS weeks.
        List<Integer> allWeeks = new ArrayList<>(weekSet);
        List<Integer> weeks = allWeeks.size() > HEATMAP_WEEKS
                ? allWeeks.subList(allWeeks.size() - HEATMAP_WEEKS, allWeeks.size())
                : allWeeks;

        List<Map<String, Object>> bms = new ArrayList<>();
        for (Map.Entry<Integer, Map<Integer, Integer>> e : byBm.entrySet()) {
            AuthUser bm = authRepository.selectById(e.getKey());
            List<Object> cells = new ArrayList<>();
            for (Integer wk : weeks) {
                Integer p = e.getValue().get(wk);
                Map<String, Object> cell = new LinkedHashMap<>();
                cell.put("week", weekLabel(wk));
                cell.put("pct", p); // null → no data that week
                cells.add(cell);
            }
            Map<String, Object> r = new LinkedHashMap<>();
            r.put("bmId", e.getKey());
            r.put("bmName", bm != null ? bm.getFullName() : ("BM #" + e.getKey()));
            r.put("cells", cells);
            bms.add(r);
        }

        List<String> weekLabels = new ArrayList<>();
        for (Integer wk : weeks) {
            weekLabels.add(weekLabel(wk));
        }
        Map<String, Object> out = new LinkedHashMap<>();
        out.put("weeks", weekLabels);
        out.put("bms", bms);
        return out;
    }

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

    /** Open leads stuck (untouched > {@value #AGEING_DAYS} days) bucketed by stage. */
    public Map<String, Object> ageing() {
        LocalDateTime olderThan = LocalDateTime.now().minusDays(AGEING_DAYS);
        List<Map<String, Object>> rows = new ArrayList<>();
        long total = 0;
        for (Object[] row : leadRepository.ageingByDerivedStage(olderThan)) {
            String s = str(row[0]);
            long c = ((Number) row[1]).longValue();
            total += c;
            Map<String, Object> r = new LinkedHashMap<>();
            r.put("stage", s);
            r.put("label", pretty(safeStage(s)));
            r.put("count", c);
            rows.add(r);
        }
        rows.sort((a, b) -> Long.compare(((Number) b.get("count")).longValue(), ((Number) a.get("count")).longValue()));
        Map<String, Object> out = new LinkedHashMap<>();
        out.put("days", AGEING_DAYS);
        out.put("total", total);
        out.put("rows", rows);
        return out;
    }

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

    /** Lead rows for a drill bucket, shaped for the drawer table. */
    public List<Map<String, Object>> drill(String bucketType, String bucketKey, Integer regionId,
                                            LocalDateTime from, LocalDateTime to) {
        LocalDateTime now = LocalDateTime.now();
        List<Integer> ids = leadRepository.selectLeadIdsForBucket(bucketType, bucketKey, regionId,
                now, now.plusHours(1), from, to, DRILL_LIMIT);
        List<Map<String, Object>> out = new ArrayList<>();
        if (ids == null || ids.isEmpty()) {
            return out;
        }
        // selectAllByIds returns id-desc; preserve the bucket's newest-first order.
        Map<Integer, Lead> leadById = new HashMap<>();
        for (Lead l : leadRepository.selectAllByIds(ids)) {
            leadById.put(l.getId(), l);
        }
        // Owner names in one batch.
        java.util.Set<Integer> ownerIds = new java.util.HashSet<>();
        for (Lead l : leadById.values()) {
            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 (Integer id : ids) {
            Lead l = leadById.get(id);
            if (l == null) {
                continue;
            }
            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());
            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);
            }
        }
        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());
            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());
        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;
    }
}