WITH status_with_lag AS (
    -- Step 1: 使用LAG函数获取上一次的空闲格口数，用于识别状态跃迁点
    SELECT
        locker_id,
        district,
        record_time,
        free_slots,
        LAG(free_slots) OVER (PARTITION BY locker_id ORDER BY record_time) AS prev_free_slots
    FROM locker_status_log
),

flagged_status AS (
    -- Step 2: 标记新周期的起点。当上一次是满载(0)或无记录(NULL)，且当前有空闲(>0)时，视为新周期起点
    SELECT
        locker_id,
        district,
        record_time,
        free_slots,
        SUM(
            CASE 
                WHEN (prev_free_slots = 0 OR prev_free_slots IS NULL) AND free_slots > 0 THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY locker_id ORDER BY record_time) AS cycle_id
    FROM status_with_lag
),

completed_cycles AS (
    -- Step 3: 按 cycle_id 分组，计算每个周期的开始时间与结束时间。
    -- 过滤掉未完成的周期（即该组中没有 free_slots = 0 的记录）
    SELECT
        locker_id,
        district,
        cycle_id,
        MIN(CASE WHEN free_slots > 0 THEN record_time END) AS start_time,
        MIN(CASE WHEN free_slots = 0 THEN record_time END) AS end_time
    FROM flagged_status
    WHERE cycle_id > 0
    GROUP BY locker_id, district, cycle_id
    HAVING SUM(CASE WHEN free_slots = 0 THEN 1 ELSE 0 END) > 0
),

cycle_durations AS (
    -- Step 4: 计算每个周期的耗时（单位：分钟）
    SELECT
        locker_id,
        district,
        cycle_id,
        (unix_timestamp(end_time) - unix_timestamp(start_time)) / 60 AS duration_minutes
    FROM completed_cycles
)

-- Step 5: 聚合计算平均耗时，并根据业务规则划分高危爆柜网点
SELECT
    locker_id,
    district,
    ROUND(AVG(duration_minutes), 2) AS avg_fill_duration_minutes,
    COUNT(1) AS completed_cycles,
    CASE 
        WHEN AVG(duration_minutes) <= 60 THEN '极高隐患（1小时内爆柜）'
        WHEN AVG(duration_minutes) <= 120 THEN '高隐患（2小时内爆柜）'
        ELSE '正常'
    END AS risk_level
FROM cycle_durations
GROUP BY locker_id, district
ORDER BY avg_fill_duration_minutes ASC;