-- ==============================================================
-- 日汇总指标视图 - v_DailyMetricsSummary
-- 用途：一次查询获取所有日汇总指标，替代 11 个独立的 SQL 查询
-- 创建日期：2026-05-17
-- 实际表名：label_replace_requests, label_scan_history, arrival_handover_forms
-- ==============================================================

DROP VIEW IF EXISTS v_DailyMetricsSummary;

CREATE VIEW v_DailyMetricsSummary AS
SELECT 
    -- 日期信息
    CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) AS MetricsDate,
    
    -- 核心数量指标 - 当天新增换单数（有标签的订单）
    COUNT(DISTINCT CASE 
        WHEN CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        AND r.Label IS NOT NULL
        THEN r.Id 
    END) AS DailyNewReplaceCount,
    
    -- 应该换单数 (新增 + 积压未完成)
    COUNT(DISTINCT CASE 
        WHEN CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) <= CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        AND r.Label IS NOT NULL
        THEN r.Id 
    END) AS DailyShouldReplaceCount,
    
    -- 完成数 (成功扫描 Result=0)
    COUNT(DISTINCT CASE 
        WHEN r.Label IS NOT NULL
        AND s.Id IS NOT NULL
        AND s.Result = 0
        AND CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN r.Id 
    END) AS DailySuccessCount,
    
    -- 累计未完成数 (历史数据中未完成的)
    COUNT(DISTINCT CASE 
        WHEN r.Label IS NOT NULL
        AND (s.Id IS NULL OR s.Result != 0)
        AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) < CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN r.Id 
    END) AS CumulativeTotalReplaceCount,
    
    -- STOP 数 (ReplaceStatus = 'N')
    COUNT(DISTINCT CASE 
        WHEN r.ReplaceStatus = 'N'
        AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN r.Id 
    END) AS DailyStopCount,
    
    -- 标签推送数 (Label 不为空的订单)
    COUNT(DISTINCT CASE 
        WHEN r.Label IS NOT NULL
        AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN r.Id 
    END) AS DailyLabelPushCount,
    
    -- 扫描数 (当日扫描记录)
    COUNT(DISTINCT CASE 
        WHEN CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN s.Id 
    END) AS DailyScanCount,
    
    -- 16点前到仓数 (UTC-5 时区)
    COUNT(DISTINCT CASE 
        WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) < 16
        AND CAST(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN ahf.Id 
    END) AS BeforeNoonArrivedCount,
    
    -- 16点后到仓数
    COUNT(DISTINCT CASE 
        WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) >= 16
        AND CAST(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN ahf.Id 
    END) AS AfternoonArrivedCount,
    
    -- 16点前完成数
    COUNT(DISTINCT CASE 
        WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) < 16
        AND CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        AND s.Result = 0
        THEN s.Id 
    END) AS BeforeNoonPassedCount,
    
    -- 16点后完成数
    COUNT(DISTINCT CASE 
        WHEN HOUR(CONVERT_TZ(ahf.ReceiptTime, '+00:00', '-05:00')) >= 16
        AND CAST(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        AND s.Result = 0
        THEN s.Id 
    END) AS AfternoonPassedCount,
    
    -- 失败数 (应该完成但未完成)
    COUNT(DISTINCT CASE 
        WHEN r.Label IS NOT NULL
        AND (s.Id IS NULL OR s.Result != 0)
        AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) = CAST(CONVERT_TZ(NOW(), '+00:00', '-05:00') AS DATE)
        THEN r.Id 
    END) AS DailyFailureCount,
    
    -- 时间戳
    NOW() AS DataFetchTime

FROM label_replace_requests r
LEFT JOIN label_scan_history s ON r.NeutralWaybillNumber = s.NeutralWaybillNumber 
    AND s.Id = (
        SELECT Id FROM label_scan_history 
        WHERE NeutralWaybillNumber = r.NeutralWaybillNumber 
        ORDER BY CreatedAt ASC LIMIT 1
    )
LEFT JOIN arrival_handover_forms ahf ON r.BillOfLadingNumber = ahf.HandoverNumber 
    OR r.MasterPackageNumber = ahf.HandoverNumber

WHERE r.Label IS NOT NULL
    AND CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE) >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)

GROUP BY CAST(CONVERT_TZ(r.CreatedAt, '+00:00', '-05:00') AS DATE);

-- ==============================================================
-- 索引优化（所有表已有足够的索引，无需添加）
-- ==============================================================

-- label_replace_requests 表现有索引：
--   idx_neutral_waybill_number (唯一)，idx_created_at，idx_replace_status
--   idx_customer_id，idx_final_mile_tracking_number
--   idx_customer_id_created_at (复合)

-- label_scan_history 表现有索引：
--   IX_CustomerId，IX_NeutralWaybillNumber，IX_ReferenceNumber
--   IX_FinalMileTrackingNumber，IX_CreatedAt，IX_Result
--   idx_customer_id_neutral_waybill_number (复合)

-- arrival_handover_forms 表现有索引：
--   UQ_HandoverNumber (唯一)

-- 结论：现有索引已足以支持视图查询，无需添加新索引

-- ==============================================================
-- 使用示例
-- ==============================================================
-- SELECT * FROM v_DailyMetricsSummary WHERE MetricsDate = '2026-05-17';