Subversion Repositories SmartDukaan

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

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

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

    private LeadRecordingAccessRepository leadRecordingAccessRepository;

    @Autowired

    private VonageAgentRepository vonageAgentRepository;

    @Autowired

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

    }

            // Lets the assign picker surface whoever covers this lead's region first.

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