Weekly Lead History Aggregator

Cron: 0 0 6 ? * TUE

Field Breakdown:
┌─────────────┬─────────────────────────────────────┐
│ Field       │ Value & Meaning                      │
├─────────────┼─────────────────────────────────────┤
│ Seconds     │ 0 (at 00 seconds)                   │
│ Minutes     │ 0 (at 00 minutes = top of hour)     │
│ Hours       │ 6 (6:00 AM)                         │
│ Day of Month│ ? (no specific date)                │
│ Month       │ * (every month)                     │
│ Day of Week │ TUE (Tuesday only)                  │
└─────────────┴─────────────────────────────────────┘

Frequency: Once per week (every Tuesday at 6 AM)
Purpose: Weekend review, Monday planning preparation

-- Collect metrics for single telecaller for past week
DECLARE @WeekStart DATETIME = DATEADD(DAY, -7, CAST(GETDATE() as DATE));
DECLARE @WeekEnd DATETIME = CAST(GETDATE() as DATE);
DECLARE @StaffId INT = 1234;
 
SELECT
    @StaffId as StaffId,
    @WeekStart as WeekStartDate,
    COUNT(DISTINCT el.LeadId) as NewLeadsAssigned,
    COUNT(DISTINCT CASE WHEN el.Status = 'SV_FIXED' THEN el.LeadId END) as SVFixed,
    COUNT(DISTINCT CASE WHEN el.Status = 'CP' THEN el.LeadId END) as CallProgress,
    COUNT(DISTINCT CASE WHEN el.Status = 'TRASHED' THEN el.LeadId END) as Trashed,
    COUNT(DISTINCT CASE WHEN el.Status = 'NO_RESPONSE' THEN el.LeadId END) as NoResponse,
    COUNT(DISTINCT fc.FollowupId) as FollowupCreated,
    COUNT(DISTINCT CASE WHEN fc.IsCompleted = 1 THEN fc.FollowupId END) as FollowupCompleted,
 
    -- Conversion Rate
    CAST(
        COUNT(DISTINCT CASE WHEN el.Status = 'SV_FIXED' THEN el.LeadId END)
        as FLOAT
    ) / NULLIF(COUNT(DISTINCT el.LeadId), 0) * 100 as ConversionRate,
 
    -- Follow-up Completion Rate
    CAST(
        COUNT(DISTINCT CASE WHEN fc.IsCompleted = 1 THEN fc.FollowupId END)
        as FLOAT
    ) / NULLIF(COUNT(DISTINCT fc.FollowupId), 0) * 100 as FollowupCompletionRate,
 
    -- Average Response Time (hours)
    AVG(CAST(DATEDIFF(HOUR, el.CreatedDate, cl.CallDate) as FLOAT)) as AvgResponseHours
 
FROM ExternalLeads el
LEFT JOIN CallLog cl ON el.LeadId = cl.LeadId
    AND cl.CallDate = (SELECT MIN(CallDate) FROM CallLog WHERE LeadId = el.LeadId)
LEFT JOIN FollowupCalls fc ON el.LeadId = fc.LeadId
WHERE el.StaffId = @StaffId
    AND (el.CreatedDate BETWEEN @WeekStart AND @WeekEnd
        OR el.ModifiedDate BETWEEN @WeekStart AND @WeekEnd)

-- Aggregate all assigned TCs to manager level
DECLARE @WeekStart DATETIME = DATEADD(DAY, -7, CAST(GETDATE() as DATE));
DECLARE @ManagerId INT = 5678;
 
SELECT
    @ManagerId as ManagerId,
    'BDO' as HierarchyLevel,
    @WeekStart as WeekStartDate,
    SUM(wlh.NewLeadsAssigned) as TotalNewLeads,
    SUM(wlh.SVFixed) as TotalSVFixed,
    SUM(wlh.CallProgress) as TotalCallProgress,
    SUM(wlh.Trashed) as TotalTrashed,
    SUM(wlh.NoResponse) as TotalNoResponse,
 
    -- Team Conversion Rate
    CAST(SUM(wlh.SVFixed) as FLOAT) / NULLIF(SUM(wlh.NewLeadsAssigned), 0) * 100 as TeamConversionRate,
 
    -- Team Follow-up Completion
    CAST(SUM(wlh.FollowupCompleted) as FLOAT) / NULLIF(SUM(wlh.FollowupCreated), 0) * 100 as TeamFollowupRate,
 
    -- Average Response Time (weighted)
    AVG(wlh.AvgResponseHours) as AvgTeamResponseHours
 
FROM WeeklyLeadHistory wlh
INNER JOIN TeleLeaderMapping tlm ON wlh.StaffId = tlm.StaffId
WHERE tlm.LeaderId = @ManagerId
    AND tlm.IsActive = 1
    AND wlh.HierarchyLevel = 'Telecaller'
    AND wlh.WeekStartDate = @WeekStart
GROUP BY @ManagerId, 'BDO', @WeekStart

public async Task Execute(IJobExecutionContext context)
{
    var stopwatch = Stopwatch.StartNew();
    var recordsProcessed = 0;
 
    try
    {
        // Prevent concurrent execution
        if (await _lockService.IsLocked("WeeklyAggregation"))
        {
            _logger.LogWarning("Weekly aggregation already running, skipping");
            return;
        }
 
        await _lockService.Lock("WeeklyAggregation");
 
        var weekStart = GetWeekStart();
        var weekEnd = GetWeekEnd();
 
        // Phase 1: Collect data
        var leads = await _db.ExternalLeads
            .Where(l => l.CreatedDate >= weekStart && l.CreatedDate <= weekEnd)
            .ToListAsync();
 
        recordsProcessed = leads.Count;
 
        // Phase 2: Aggregate by staff
        var staffMetrics = AggregateByStaff(leads);
 
        // Phase 3: Rollup hierarchy
        var hierarchyMetrics = RollupHierarchy(staffMetrics);
 
        // Phase 4: Store results
        await _db.WeeklyLeadHistory.AddRangeAsync(hierarchyMetrics);
        await _db.SaveChangesAsync();
 
        stopwatch.Stop();
 
        _logger.LogInformation(
            $"Weekly aggregation completed: {recordsProcessed} leads processed " +
            $"in {stopwatch.ElapsedMilliseconds}ms");
 
        context.JobDetail.JobDataMap.Put("Status", "SUCCESS");
        context.JobDetail.JobDataMap.Put("RecordsProcessed", recordsProcessed);
    }
    catch (Exception ex)
    {
        _logger.LogError($"Weekly aggregation failed: {ex}");
        context.JobDetail.JobDataMap.Put("Status", "FAILED");
        context.JobDetail.JobDataMap.Put("Error", ex.Message);
        throw;
    }
    finally
    {
        await _lockService.Unlock("WeeklyAggregation");
    }
}