-- 每日标签统计综合查询（重新设计 - 基于到货交接单）
-- 设计逻辑：
-- 1. 要换多少单：根据 arrival_handover_forms 的 HandoverNumber 关联 label_replace_requests
-- 2. 关联方式：BillOfLadingNumber 或 MasterPackageNumber 与 HandoverNumber 匹配
-- 3. 到货日期本身就是UTC-5，不需要转换
-- 4. 统计维度：日期、当日新增换单数、累计要换的总单数、换单失败未完结订单等

WITH 
-- 步骤1：获取所有到货交接单，日期已是UTC-5
ArrivalFormsWithDate AS (
    SELECT 
        a.Id,
        a.HandoverNumber,
        DATE(a.ReceiptTime) AS 到货日期,
        a.ReceiptTime AS 到货时间
    FROM arrival_handover_forms a
),

-- 步骤2：关联到货交接单与换单请求（只取Label有值的），并记录订单级别的信息，计算考核时间
ArrivalRequests AS (
    SELECT 
        a.到货日期,
        a.到货时间,
        l.Id AS RequestId,
        l.NeutralWaybillNumber,
        l.BillOfLadingNumber,
        l.MasterPackageNumber,
        l.Label,
        l.LabelRetrievedAt,
        l.CustomerId,
        -- 考核时间：标签推送时间与到仓时间比较，哪个最新用哪个
        CASE 
            WHEN l.LabelRetrievedAt IS NULL THEN a.到货时间
            WHEN l.LabelRetrievedAt > a.到货时间 THEN l.LabelRetrievedAt
            ELSE a.到货时间
        END AS 考核时间
    FROM ArrivalFormsWithDate a
    INNER JOIN label_replace_requests l 
        ON l.BillOfLadingNumber = a.HandoverNumber 
        OR l.MasterPackageNumber = a.HandoverNumber
    WHERE l.Label IS NOT NULL AND l.Label != ""
),

-- 步骤3：获取每个订单的扫描记录情况（按天）
DailyScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 当日是否成功,
        MAX(CASE WHEN s.Result != 0 THEN 1 ELSE 0 END) AS 当日是否失败
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ""
    GROUP BY s.NeutralWaybillNumber, DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
),

-- 步骤4：获取每个订单是否曾经成功，以及首次成功日期和时间
OverallScanStatus AS (
    SELECT 
        s.NeutralWaybillNumber,
        MAX(CASE WHEN s.Result = 0 THEN 1 ELSE 0 END) AS 曾成功,
        MIN(CASE WHEN s.Result = 0 THEN DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ELSE NULL END) AS 首次成功日期,
        MIN(CASE WHEN s.Result = 0 THEN s.CreatedAt ELSE NULL END) AS 首次成功时间
    FROM label_scan_history s
    GROUP BY s.NeutralWaybillNumber
),

-- 步骤5：从数据中收集所有日期
AllDates AS (
    SELECT 到货日期 AS 日期 FROM ArrivalRequests
    UNION
    SELECT 日期 FROM DailyScanStatus
    UNION
    SELECT DATE(CONVERT_TZ(l.LabelRetrievedAt, '+00:00', '-05:00')) AS 日期
    FROM label_replace_requests l
    WHERE l.LabelRetrievedAt IS NOT NULL
),

-- 步骤6：去重并排序日期
DistinctDates AS (
    SELECT DISTINCT 日期
    FROM AllDates
    ORDER BY 日期
),

-- 步骤7：获取最新日期
LatestDate AS (
    SELECT MAX(日期) AS 日期
    FROM DistinctDates
),

-- 步骤8：每日基础统计 - 当日新增换单数
DailyBase AS (
    SELECT 
        dd.日期,
        -- 当日新增换单数：当天到货并且推送了标签数据的订单
        COUNT(DISTINCT CASE 
            WHEN ar.到货日期 = dd.日期 AND ar.LabelRetrievedAt IS NOT NULL 
            THEN ar.RequestId 
        END) AS 当日新增换单数,
        -- 当天标签推送数
        COUNT(DISTINCT CASE 
            WHEN DATE(CONVERT_TZ(ar.LabelRetrievedAt, '+00:00', '-05:00')) = dd.日期 
            THEN ar.RequestId 
        END) AS 当日标签推送数
    FROM DistinctDates dd
    CROSS JOIN ArrivalRequests ar
    GROUP BY dd.日期
),

-- 步骤9：每日扫描统计（扫描次数），换单成功数去重
DailyScanMetrics AS (
    SELECT 
        DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS 日期,
        COUNT(*) AS 当日扫描数,
        -- 当日STOP数（同样去重）
        COUNT(DISTINCT CASE WHEN s.Result = 0 AND s.Description LIKE '%成功返回STOP标签%' THEN s.NeutralWaybillNumber END) AS 当日STOP数
    FROM label_scan_history s
    INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
    WHERE l.Label IS NOT NULL AND l.Label != ""
    GROUP BY DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00'))
),

-- 步骤9b：每日换单成功数（去重）
DailySuccessCount AS (
    SELECT 
        scan_date AS 日期,
        COUNT(DISTINCT NeutralWaybillNumber) AS 当日换单成功数
    FROM (
        -- 找出每个包裹每天最新的成功记录
        SELECT 
            s.NeutralWaybillNumber,
            DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) AS scan_date,
            ROW_NUMBER() OVER (PARTITION BY s.NeutralWaybillNumber, DATE(CONVERT_TZ(s.CreatedAt, '+00:00', '-05:00')) ORDER BY s.CreatedAt DESC) AS rn
        FROM label_scan_history s
        INNER JOIN label_replace_requests l ON s.NeutralWaybillNumber = l.NeutralWaybillNumber
        WHERE l.Label IS NOT NULL AND l.Label != "" AND s.Result = 0
    ) AS s
    WHERE rn = 1
    GROUP BY scan_date
),

-- 步骤10：历史日期的换单失败未完结统计（非最新日期）
HistoryUnfinished AS (
    SELECT 
        dd.日期,
        COUNT(DISTINCT CASE
            -- 当天失败并且当天没有成功的订单
            WHEN dss.日期 = dd.日期 AND dss.当日是否失败 = 1 AND dss.当日是否成功 = 0
            THEN dss.NeutralWaybillNumber
        END) AS 换单失败未完结订单
    FROM DistinctDates dd
    LEFT JOIN DailyScanStatus dss ON dd.日期 = dss.日期
    CROSS JOIN LatestDate ld
    WHERE dd.日期 != ld.日期
    GROUP BY dd.日期
),

-- 步骤11：最新日期的换单失败未完结统计（所有历史从未成功的）
LatestUnfinished AS (
    SELECT 
        ld.日期,
        COUNT(DISTINCT CASE
            -- 必须同时满足：
            -- 1. 有过扫描记录（OverallScanStatus中有该订单
            -- 2. 从未成功（曾成功 = 0）
            -- 3. 并且至少有一次失败记录
            WHEN oss.曾成功 = 0
            THEN ar.NeutralWaybillNumber
        END) AS 换单失败未完结订单
    FROM LatestDate ld
    CROSS JOIN ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    GROUP BY ld.日期
),

-- 步骤12：每日换单失败订单统计（当天失败并且当天没成功的订单数）
DailyFailedOrders AS (
    SELECT 
        日期,
        COUNT(DISTINCT CASE
            WHEN 当日是否失败 = 1 AND 当日是否成功 = 0
            THEN NeutralWaybillNumber
        END) AS 当日换单失败
    FROM DailyScanStatus
    GROUP BY 日期
),

-- 步骤13：每日完成订单统计 - 订单必须在我们关联的ArrivalRequests中
DailyCompletedOrders AS (
    SELECT 
        oss.首次成功日期 AS 日期,
        COUNT(DISTINCT oss.NeutralWaybillNumber) AS 当日完成数
    FROM OverallScanStatus oss
    INNER JOIN ArrivalRequests ar ON oss.NeutralWaybillNumber = ar.NeutralWaybillNumber
    WHERE oss.曾成功 = 1
    GROUP BY oss.首次成功日期
),

-- 步骤14：24小时换单完成订单统计 - 新逻辑：按到仓时间16点 cutoff判断
Daily24HCompletedOrders AS (
    SELECT 
        -- 统计到完成日期的下一天
        DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 24H内完成数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1 
        AND oss.首次成功时间 IS NOT NULL
        AND ar.到货时间 IS NOT NULL
        AND (
            -- 情况1：到仓时间在当日16点前，完成时间在次日16点前
            (HOUR(ar.到货时间) < 16 
                AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= DATE_ADD(ar.到货时间, INTERVAL 1 DAY) + INTERVAL 16 HOUR
                AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') > ar.到货时间)
            OR
            -- 情况2：到仓时间在当日16点及以后，完成时间在次日23:59:59前
            (HOUR(ar.到货时间) >= 16 
                AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') <= DATE_ADD(DATE(ar.到货时间), INTERVAL 2 DAY) - INTERVAL 1 SECOND
                AND CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00') > ar.到货时间)
        )
    GROUP BY DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00'))
),

-- 步骤14a：当日换单成功统计（分子：到仓12点前且当天完成的包裹）
DailySameDaySuccessOrders AS (
    SELECT 
        ar.到货日期 AS 日期,
        COUNT(DISTINCT ar.NeutralWaybillNumber) AS 当日12点前到且当日完成数
    FROM ArrivalRequests ar
    INNER JOIN OverallScanStatus oss ON ar.NeutralWaybillNumber = oss.NeutralWaybillNumber
    WHERE 
        oss.曾成功 = 1
        AND ar.到货时间 IS NOT NULL
        AND oss.首次成功时间 IS NOT NULL
        -- 到仓时间在当日12点前
        AND HOUR(ar.到货时间) < 12
        -- 完成时间在当日23:59:59前
        AND DATE(CONVERT_TZ(oss.首次成功时间, '+00:00', '-05:00')) = ar.到货日期
    GROUP BY ar.到货日期
),

-- 步骤14b：每日总到仓包裹数（分母：当日23:59前到仓的总包裹数）
DailyTotalArrivedOrders AS (
    SELECT 
        到货日期 AS 日期,
        COUNT(DISTINCT RequestId) AS 当日总到仓包裹数
    FROM ArrivalRequests
    GROUP BY 到货日期
),

-- 步骤15：完整的每日统计基础 - 准备每日的新增和完成，并获取前一日数据
DailyStatsWithPrev AS (
    SELECT 
        db.日期,
        db.当日新增换单数,
        db.当日标签推送数,
        COALESCE(dco.当日完成数, 0) AS 当日完成数,
        COALESCE(dc24h.24H内完成数, 0) AS 24H内完成数,
        COALESCE(dsds.当日12点前到且当日完成数, 0) AS 当日12点前到且当日完成数,
        COALESCE(dtao.当日总到仓包裹数, 0) AS 当日总到仓包裹数,
        -- 获取前一日的新增
        LAG(db.当日新增换单数, 1, 0) OVER (ORDER BY db.日期) AS 前一日新增,
        -- 获取前一日的完成数
        LAG(COALESCE(dco.当日完成数, 0), 1, 0) OVER (ORDER BY db.日期) AS 前一日完成数,
        -- 行号
        ROW_NUMBER() OVER (ORDER BY db.日期) AS rn
    FROM DailyBase db
    LEFT JOIN DailyCompletedOrders dco ON db.日期 = dco.日期
    LEFT JOIN Daily24HCompletedOrders dc24h ON db.日期 = dc24h.日期
    LEFT JOIN DailySameDaySuccessOrders dsds ON db.日期 = dsds.日期
    LEFT JOIN DailyTotalArrivedOrders dtao ON db.日期 = dtao.日期
    ORDER BY db.日期
)

-- 步骤16：计算累计数据并最终输出
SELECT 
    日期,
    当日新增换单数,
    累计要换的总单数,
    换单失败未完结订单,
    当日换单失败,
    当日换单成功数,
    当日STOP数,
    -- 24小时换单成功率（新逻辑）
    CASE 
        WHEN 当日完成数 = 0 THEN '0.00%'
        ELSE CONCAT(ROUND(24H内完成数 / 当日完成数 * 100, 2), '%')
    END AS 24小时换单成功率,
    -- 当日换单成功率（新逻辑）
    CASE 
        WHEN 当日总到仓包裹数 = 0 THEN '0.00%'
        ELSE CONCAT(ROUND(当日12点前到且当日完成数 / 当日总到仓包裹数 * 100, 2), '%')
    END AS 当日换单成功率,
    当日标签推送数,
    当日扫描数,
    数据拉取时间（UTC_5）
FROM (
    SELECT 
        t.日期,
        t.当日新增换单数,
        t.当日12点前到且当日完成数,
        t.当日总到仓包裹数,
        -- 使用变量保持状态，每次计算前一天的累计
        -- 公式：累计 = MAX(0, 前一日累计 + 前一日新增 - 前一日完成)
        -- 第1天直接用当日新增
        @running_total := GREATEST(0, 
            CASE 
                WHEN t.rn = 1 THEN t.当日新增换单数
                ELSE @running_total + t.前一日新增 - t.前一日完成数
            END
        ) AS 累计要换的总单数,
        -- 根据是否是最新日期选择不同的未完结统计
        COALESCE(
            CASE 
                WHEN t.日期 = (SELECT 日期 FROM LatestDate) THEN lu.换单失败未完结订单
                ELSE hu.换单失败未完结订单
            END, 
            0
        ) AS 换单失败未完结订单,
        COALESCE(dfo.当日换单失败, 0) AS 当日换单失败,
        COALESCE(dsc.当日换单成功数, 0) AS 当日换单成功数,
        COALESCE(dsm.当日STOP数, 0) AS 当日STOP数,
        t.24H内完成数,
        t.当日完成数,
        t.当日标签推送数,
        COALESCE(dsm.当日扫描数, 0) AS 当日扫描数,
        (UTC_TIMESTAMP() - INTERVAL 5 HOUR) AS 数据拉取时间（UTC_5）
    FROM DailyStatsWithPrev t
    LEFT JOIN DailyScanMetrics dsm ON t.日期 = dsm.日期
    LEFT JOIN DailySuccessCount dsc ON t.日期 = dsc.日期
    LEFT JOIN HistoryUnfinished hu ON t.日期 = hu.日期
    LEFT JOIN LatestUnfinished lu ON t.日期 = lu.日期
    LEFT JOIN DailyFailedOrders dfo ON t.日期 = dfo.日期
    -- 初始化变量
    CROSS JOIN (SELECT @running_total := 0) AS init
    -- 数据验证：排除无效日期
    WHERE t.日期 IS NOT NULL
    ORDER BY t.日期
) AS subquery
-- 数据验证：排除扫描数为0的日期（如果需要）
-- WHERE 当日扫描数 > 0
ORDER BY 日期 DESC;