Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.repository.dtr;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;

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

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

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

import com.spice.profitmandi.dao.model.LeadDetailModel;

import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;

import java.util.List;

@Repository

public interface LeadRepository {

    public void persist(Lead lead);

    public List<Lead> selectByAssignAuthIdAndStatus(int authId, LeadStatus status, int offset, int limit);

    public Lead selectById(int id);

    List<Lead> selectAllByColorStatusAndBetweenDate(List<LeadStatus> status, List<String> color, LocalDateTime createdTimestamp, LocalDateTime endDateTimeStamp);

    public List<Lead> selectAllByIds(List<Integer> ids);

    /**

     * Filtered, sorted, paged rows for the dashboard's Lead-pipeline table. A non-empty {@code q}

     * searches every lead and IGNORES the date window; otherwise the window applies.

     */

    public List<Lead> selectPipeline(String q, Integer regionId, Integer agentId, LocalDateTime from, LocalDateTime to,

                                     String sort, String dir, int offset, int limit);

    /** Total rows matching the same filters as {@link #selectPipeline}. */

    public long countPipeline(String q, Integer regionId, Integer agentId, LocalDateTime from, LocalDateTime to);

    public List<Lead> selectByAssignAuthIdAndStatus(int authId, LeadStatus status);

    public List<Lead> selectByAssignAuthIdsAndStatus(List<Integer> authId, LeadStatus status);

    public List<Lead> selectAll(int offset, int limit);

    public List<Lead> selectAllBylistStatus(List<LeadStatus> status) throws ProfitMandiBusinessException;

    public List<Lead> selectAllByStatus(LeadStatus status);

    public List<Lead> selectAllByStatusAndUpdatedTimestamp(List<LeadStatus> status, LocalDateTime updatedTimestamp);

    public List<Lead> selectAllByStatus(List<LeadStatus> status, int offset, int limit);

    List<Lead> selectLeadsScheduledBetweenDate(List<Integer> fofoIds, LocalDateTime startDate, LocalDateTime endDate);

    public long selectCountByStatus(List<LeadStatus> status);

    public long getLeadCount(LeadStatus status, String searchTerm);

    public List<Lead> selectBySearchTerm(List<LeadStatus> status, String searchTerm, int offset, int limit);

    // Global lead search: matches name / mobile / outlet / city across ALL leads (any status, any date).

    public List<Lead> selectByGlobalSearch(String searchTerm, int limit);

    public List<Lead> selectAllByColorStatusAndUpdatedTimestamp(List<LeadStatus> status, List<String> color, LocalDateTime updatedTimestamp);

    public List<Lead> selectAllByColorStatusAndCreatedTimestamp(List<LeadStatus> status, List<String> color, LocalDateTime createdTimestamp);

    List<Lead> selectAllByStatusAndUpdatedTimestampAndAuthId(List<LeadStatus> status, LocalDateTime updatedTimestamp, List<Integer> authIds);

    List<Lead> selectAllByColorStatusAndUpdatedTimestampAndAuthIds(List<LeadStatus> status, List<Integer> authIds, List<String> color, LocalDateTime updatedTimestamp);

    List<Lead> selectAllByColorStatusAndBetweenCreatedDate(List<LeadStatus> status, List<String> color, LocalDateTime startDate, LocalDateTime endDate);

    List<Lead> selectAllByColorStatusAndBetweenCreatedDateAndAuthIds(List<LeadStatus> status, List<Integer> authIds, List<String> color, LocalDateTime startDate, LocalDateTime endDate);

    public void persistLeadDetail(LeadDetailModel leadDetailModel, AuthUser authUser) throws ProfitMandiBusinessException;

    /**

     * Same as {@link #persistLeadDetail(LeadDetailModel, AuthUser)} but with {@code requirePhotos}

     * false the five store photos are optional: whatever ids are present are stored, none are demanded.

     * The LMS Create Lead form captures the shop on a call, before anyone has been to it.

     */

    public void persistLeadDetail(LeadDetailModel leadDetailModel, AuthUser authUser, boolean requirePhotos)

            throws ProfitMandiBusinessException;

    Lead selectByMobileNumber(String mobileNumber);

    /**

     * Every lead on this mobile that is OPEN and actually workable, newest activity first.

     *

     * "Workable" is two conditions, not one. The status must be open (pending/followUp), AND the

     * assignee must still be an active auth_user. The second half matters: 331 open leads are

     * assigned to 11 DEACTIVATED accounts (157 of them to sm@smartdukaan.com alone). Without the

     * owner check each of those would block a fresh enquiry forever, held by an account nobody can

     * log in to and therefore nobody can close. Filtering them out defuses all 331 without

     * retiring a single row, which is what the "no auto-retirement" rule requires.

     *

     * Returns a LIST on purpose. 5,137 mobiles already carry duplicates and 10 carry more than one

     * OPEN lead, so any uniqueness assumption here throws -- which is exactly what

     * selectByMobileNumber does today (GlitchTip #99, #1359).

     */

    List<Lead> selectActiveByMobile(String mobileNumber);

    /**

     * Last time anything happened to this lead: the later of its own updated/created stamp and its

     * newest lead_activity row. Drives the 6-month staleness test.

     */

    LocalDateTime selectLastActivityAt(int leadId);

    /**

     * Batch mobile lookup for bulk imports — one query instead of one per row.

     *

     * <p>Takes normalised 10-digit numbers and also matches the prefixed forms that exist in the

     * data ({@code 91…}, {@code 0…}): of ~37k leads, 121 are stored 11- or 12-digit, so an exact

     * 10-digit match would miss them and a bulk import would create duplicates for real retailers.

     */

    List<Lead> selectByMobileNumbers(List<String> normalisedMobiles);

    /**

     * Open-lead count per owner, for balancing a bulk assignment against what each agent is already

     * carrying. Returns {@code [authId, count]} pairs; an agent with none is simply absent.

     */

    java.util.List<Object[]> countOpenLeadsByAuthIds(List<Integer> authIds);

    Lead selectByMobileNumberAndSource(String mobileNumber, String source);

    // ---- LMS dashboard aggregation (SOP §18) -------------------------------------------------

    // All aggregates bucket EVERY lead: rows with a real `stage` use it; legacy rows (stage NULL)

    // derive a stage from `status` in SQL, mirroring LeadStage.fromLegacy. Read-only, no schema change.

    /** (derivedStage, count) for the optional region + created-date window. */

    java.util.List<Object[]> countByDerivedStage(Integer regionId, Integer agentId, LocalDateTime from, LocalDateTime to);

    /** (derivedStage, SUM(potential)) for the optional region + window — for the onboarded-value KPI. */

    java.util.List<Object[]> sumPotentialByDerivedStage(Integer regionId, Integer agentId, LocalDateTime from, LocalDateTime to);

    /** (regionCode, derivedStage, count) across all regions in the window — feeds the state funnel. */

    java.util.List<Object[]> countByRegionAndDerivedStage(LocalDateTime from, LocalDateTime to);

    /** (regionCode, totalWithDue, onTime) — first-contact SLA compliance per region. */

    java.util.List<Object[]> slaComplianceByRegion(LocalDateTime from, LocalDateTime to);

    /** (ownerBmId, isoYearWeek, totalWithDue, onTime) — SLA compliance per BM per week, for the heat-map. */

    java.util.List<Object[]> slaComplianceByBmAndWeek(LocalDateTime from, LocalDateTime to);

    /** (derivedStage, count) of OPEN leads not touched since {@code olderThan} — the ageing widget. */

    java.util.List<Object[]> ageingByDerivedStage(LocalDateTime olderThan);

    /** Single row of conditional-sum attention counts (see impl) for the optional region. */

    /** Attention-queue counts for the region and created-date window (same scope as the funnel). */

    Object[] attentionCounts(Integer regionId, Integer agentId, LocalDateTime now, LocalDateTime nowPlus1h,

                             LocalDateTime from, LocalDateTime to);

    /**

     * Per-owner lead counters for the live board: {@code [authId, assigned, slaBreached]}.

     * Breached = open, past its first-contact deadline, never contacted.

     */

    List<Object[]> selectAgentLeadCounters(LocalDateTime now);

    /** Lead ids for a drill-down bucket (STAGE / ATTENTION / REGION), newest first, capped at {@code limit}. */

    java.util.List<Integer> selectLeadIdsForBucket(String bucketType, String bucketKey, Integer regionId, Integer agentId,

                                                   LocalDateTime now, LocalDateTime nowPlus1h,

                                                   LocalDateTime from, LocalDateTime to, int offset, int limit);

    /** Total leads in the same drill bucket, so the drawer can page through all of them. */

    long countLeadsForBucket(String bucketType, String bucketKey, Integer regionId, Integer agentId,

                             LocalDateTime now, LocalDateTime nowPlus1h,

                             LocalDateTime from, LocalDateTime to);

    /**

     * Effective LMS stage per lead id, computed with the same evidence-aware expression the dashboard

     * aggregates use (beat stops, check-ins, LOI + stock payment). Lead.getEffectiveStage() reads only

     * the column and will disagree for the funnel's tail.

     */

    java.util.Map<Integer, String> selectEffectiveStages(java.util.List<Integer> ids);

}