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