-- =====================================================
-- 指标趋势分析与拐点预警SQL
-- Metric Trend Analysis and Turning Point Warning
-- 数据库: PostgreSQL
-- =====================================================

-- =====================================================
-- 1. 日度指标基础数据
-- =====================================================
WITH daily_metrics AS (
    SELECT 
        DATE(lr.event_time_utc::timestamp) AS metric_date,
        COUNT(DISTINCT lr.worker_name) AS worker_count,
        SUM(lr.duration_minutes) / 60.0 AS total_hours,
        SUM(lr.report_qty) AS total_output,
        CASE WHEN SUM(lr.duration_minutes) > 0 
             THEN SUM(lr.report_qty) / (SUM(lr.duration_minutes) / 60.0) 
             ELSE 0 END AS hourly_efficiency
    FROM fact_labor_report lr
    GROUP BY DATE(lr.event_time_utc::timestamp)
),

daily_quality AS (
    SELECT 
        DATE(qi.event_time_utc::timestamp) AS metric_date,
        SUM(qi.pass_qty) AS pass_qty,
        SUM(qi.fail_qty) AS fail_qty,
        CASE WHEN SUM(qi.pass_qty) + SUM(qi.fail_qty) > 0 
             THEN SUM(qi.fail_qty) * 100.0 / (SUM(qi.pass_qty) + SUM(qi.fail_qty))
             ELSE 0 END AS defect_rate
    FROM fact_quality_inspection qi
    GROUP BY DATE(qi.event_time_utc::timestamp)
),

daily_production AS (
    SELECT 
        DATE(wo.event_time_utc::timestamp) AS metric_date,
        SUM(wo.planned_qty) AS planned_qty,
        SUM(wo.completed_qty) AS completed_qty,
        CASE WHEN SUM(wo.planned_qty) > 0 
             THEN SUM(wo.completed_qty) * 100.0 / SUM(wo.planned_qty)
             ELSE 0 END AS completion_rate
    FROM fact_work_order wo
    GROUP BY DATE(wo.event_time_utc::timestamp)
),

combined_daily AS (
    SELECT 
        COALESCE(dm.metric_date, dq.metric_date, dp.metric_date) AS metric_date,
        COALESCE(dm.worker_count, 0) AS worker_count,
        COALESCE(dm.total_hours, 0) AS total_hours,
        COALESCE(dm.total_output, 0) AS total_output,
        COALESCE(dm.hourly_efficiency, 0) AS hourly_efficiency,
        COALESCE(dq.defect_rate, 0) AS defect_rate,
        COALESCE(dp.completion_rate, 0) AS completion_rate
    FROM daily_metrics dm
    FULL OUTER JOIN daily_quality dq ON dm.metric_date = dq.metric_date
    FULL OUTER JOIN daily_production dp ON dm.metric_date = dp.metric_date
    WHERE COALESCE(dm.metric_date, dq.metric_date, dp.metric_date) IS NOT NULL
),

numbered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY metric_date) AS day_seq
    FROM combined_daily
),

-- =====================================================
-- 2. 移动平均计算 (7日移动平均)
-- =====================================================
moving_avg_step1 AS (
    SELECT 
        metric_date,
        day_seq,
        worker_count,
        total_output,
        hourly_efficiency,
        defect_rate,
        completion_rate,
        AVG(hourly_efficiency) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_efficiency,
        AVG(total_output) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_output,
        AVG(defect_rate) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_defect_rate,
        AVG(completion_rate) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7_completion_rate,
        AVG(day_seq) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_x
    FROM numbered_data
),

moving_avg AS (
    SELECT 
        *,
        LAG(ma7_efficiency, 1) OVER (ORDER BY metric_date) AS prev_ma7_efficiency,
        LAG(ma7_output, 1) OVER (ORDER BY metric_date) AS prev_ma7_output,
        LAG(ma7_defect_rate, 1) OVER (ORDER BY metric_date) AS prev_ma7_defect_rate
    FROM moving_avg_step1
),

-- =====================================================
-- 3. 简化的趋势斜率计算
-- =====================================================
slope_calc AS (
    SELECT 
        *,
        (ma7_efficiency - LAG(ma7_efficiency, 3) OVER (ORDER BY metric_date)) / 3.0 AS slope_efficiency,
        (ma7_output - LAG(ma7_output, 3) OVER (ORDER BY metric_date)) / 3.0 AS slope_output,
        (ma7_defect_rate - LAG(ma7_defect_rate, 3) OVER (ORDER BY metric_date)) / 3.0 AS slope_defect
    FROM moving_avg
),

-- =====================================================
-- 4. 趋势判断与异常检测
-- =====================================================
trend_analysis AS (
    SELECT 
        metric_date,
        hourly_efficiency,
        total_output,
        defect_rate,
        completion_rate,
        ROUND(ma7_efficiency, 2) AS ma7_efficiency,
        ROUND(ma7_output, 2) AS ma7_output,
        ROUND(ma7_defect_rate, 2) AS ma7_defect_rate,
        ROUND(COALESCE(slope_efficiency, 0), 4) AS slope_efficiency,
        ROUND(COALESCE(slope_output, 0), 4) AS slope_output,
        ROUND(COALESCE(slope_defect, 0), 4) AS slope_defect,
        prev_ma7_efficiency,
        prev_ma7_output,
        prev_ma7_defect_rate,
        
        CASE 
            WHEN COALESCE(slope_efficiency, 0) > 0.3 THEN 'RISING'
            WHEN COALESCE(slope_efficiency, 0) < -0.3 THEN 'FALLING'
            ELSE 'STABLE'
        END AS efficiency_trend,
        
        CASE 
            WHEN COALESCE(slope_output, 0) > 3 THEN 'RISING'
            WHEN COALESCE(slope_output, 0) < -3 THEN 'FALLING'
            ELSE 'STABLE'
        END AS output_trend,
        
        CASE 
            WHEN COALESCE(slope_defect, 0) > 0.3 THEN 'RISING'
            WHEN COALESCE(slope_defect, 0) < -0.3 THEN 'FALLING'
            ELSE 'STABLE'
        END AS defect_trend,
        
        CASE 
            WHEN ma7_efficiency > 0 AND ABS(hourly_efficiency - ma7_efficiency) > ma7_efficiency * 0.3 THEN 'ANOMALY'
            ELSE 'NORMAL'
        END AS efficiency_status,
        
        CASE 
            WHEN ma7_output > 0 AND ABS(total_output - ma7_output) > ma7_output * 0.3 THEN 'ANOMALY'
            ELSE 'NORMAL'
        END AS output_status,
        
        CASE 
            WHEN defect_rate > ma7_defect_rate * 1.5 AND defect_rate > 5 THEN 'ANOMALY'
            ELSE 'NORMAL'
        END AS defect_status
        
    FROM slope_calc
)

-- =====================================================
-- 输出：日度趋势分析明细
-- =====================================================
SELECT 
    metric_date,
    ROUND(hourly_efficiency, 2) AS hourly_output,
    ma7_efficiency AS efficiency_ma7,
    slope_efficiency,
    efficiency_trend,
    efficiency_status,
    CASE 
        WHEN prev_ma7_efficiency IS NOT NULL 
             AND ma7_efficiency > prev_ma7_efficiency AND slope_efficiency < 0 THEN 'TURNING_POINT'
        WHEN prev_ma7_efficiency IS NOT NULL 
             AND ma7_efficiency < prev_ma7_efficiency AND slope_efficiency > 0 THEN 'TURNING_POINT'
        ELSE 'NONE'
    END AS efficiency_turning_point,
    ROUND(total_output, 0) AS daily_output,
    ma7_output AS output_ma7,
    slope_output,
    output_trend,
    output_status,
    CASE 
        WHEN prev_ma7_output IS NOT NULL 
             AND ma7_output > prev_ma7_output AND slope_output < 0 THEN 'TURNING_POINT'
        WHEN prev_ma7_output IS NOT NULL 
             AND ma7_output < prev_ma7_output AND slope_output > 0 THEN 'TURNING_POINT'
        ELSE 'NONE'
    END AS output_turning_point,
    ROUND(defect_rate, 2) AS defect_rate,
    ma7_defect_rate AS defect_rate_ma7,
    slope_defect,
    defect_trend,
    defect_status,
    CASE 
        WHEN prev_ma7_defect_rate IS NOT NULL 
             AND ma7_defect_rate > prev_ma7_defect_rate AND slope_defect < 0 THEN 'TURNING_POINT'
        WHEN prev_ma7_defect_rate IS NOT NULL 
             AND ma7_defect_rate < prev_ma7_defect_rate AND slope_defect > 0 THEN 'TURNING_POINT'
        ELSE 'NONE'
    END AS defect_turning_point
FROM trend_analysis
ORDER BY metric_date DESC;


-- =====================================================
-- 补充查询1：周度趋势汇总
-- =====================================================
WITH weekly_metrics AS (
    SELECT 
        DATE_TRUNC('week', lr.event_time_utc::timestamp)::date AS week_start,
        COUNT(DISTINCT lr.worker_name) AS worker_count,
        SUM(lr.duration_minutes) / 60.0 AS total_hours,
        SUM(lr.report_qty) AS total_output,
        CASE WHEN SUM(lr.duration_minutes) > 0 
             THEN SUM(lr.report_qty) / (SUM(lr.duration_minutes) / 60.0) 
             ELSE 0 END AS hourly_efficiency
    FROM fact_labor_report lr
    GROUP BY DATE_TRUNC('week', lr.event_time_utc::timestamp)
),
weekly_quality AS (
    SELECT 
        DATE_TRUNC('week', qi.event_time_utc::timestamp)::date AS week_start,
        SUM(qi.fail_qty) * 100.0 / NULLIF(SUM(qi.pass_qty) + SUM(qi.fail_qty), 0) AS defect_rate
    FROM fact_quality_inspection qi
    GROUP BY DATE_TRUNC('week', qi.event_time_utc::timestamp)
),
weekly_combined AS (
    SELECT 
        wm.week_start,
        wm.worker_count,
        wm.total_hours,
        wm.total_output,
        wm.hourly_efficiency,
        COALESCE(wq.defect_rate, 0) AS defect_rate
    FROM weekly_metrics wm
    LEFT JOIN weekly_quality wq ON wm.week_start = wq.week_start
),
weekly_with_lag AS (
    SELECT 
        *,
        LAG(hourly_efficiency, 1) OVER (ORDER BY week_start) AS prev_efficiency,
        LAG(total_output, 1) OVER (ORDER BY week_start) AS prev_output,
        LAG(defect_rate, 1) OVER (ORDER BY week_start) AS prev_defect_rate
    FROM weekly_combined
)
SELECT 
    week_start,
    worker_count,
    ROUND(total_hours, 1) AS total_hours,
    ROUND(total_output, 0) AS total_output,
    ROUND(hourly_efficiency, 2) AS hourly_output,
    ROUND(defect_rate, 2) AS defect_rate,
    CASE 
        WHEN prev_efficiency IS NULL THEN 'NONE'
        WHEN hourly_efficiency > prev_efficiency * 1.1 THEN 'RISING'
        WHEN hourly_efficiency < prev_efficiency * 0.9 THEN 'FALLING'
        ELSE 'STABLE'
    END AS efficiency_trend,
    CASE 
        WHEN prev_output IS NULL THEN 'NONE'
        WHEN total_output > prev_output * 1.1 THEN 'RISING'
        WHEN total_output < prev_output * 0.9 THEN 'FALLING'
        ELSE 'STABLE'
    END AS output_trend,
    CASE 
        WHEN prev_defect_rate IS NULL THEN 'NONE'
        WHEN defect_rate > prev_defect_rate * 1.2 THEN 'RISING'
        WHEN defect_rate < prev_defect_rate * 0.8 THEN 'FALLING'
        ELSE 'STABLE'
    END AS defect_trend,
    ROUND((hourly_efficiency - COALESCE(prev_efficiency, hourly_efficiency)) / NULLIF(prev_efficiency, 0) * 100, 1) AS efficiency_wow_pct
FROM weekly_with_lag
ORDER BY week_start DESC;


-- =====================================================
-- 补充查询2：指标趋势汇总报告
-- =====================================================
WITH 
recent_data AS (
    SELECT 
        DATE(lr.event_time_utc::timestamp) AS metric_date,
        SUM(lr.report_qty) / NULLIF(SUM(lr.duration_minutes) / 60.0, 0) AS hourly_efficiency,
        SUM(lr.report_qty) AS total_output
    FROM fact_labor_report lr
    WHERE lr.event_time_utc::timestamp >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY DATE(lr.event_time_utc::timestamp)
),
recent_quality AS (
    SELECT 
        DATE(qi.event_time_utc::timestamp) AS metric_date,
        SUM(qi.fail_qty) * 100.0 / NULLIF(SUM(qi.pass_qty) + SUM(qi.fail_qty), 0) AS defect_rate
    FROM fact_quality_inspection qi
    WHERE qi.event_time_utc::timestamp >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY DATE(qi.event_time_utc::timestamp)
),
period_stats AS (
    SELECT 
        AVG(CASE WHEN rd.metric_date >= CURRENT_DATE - INTERVAL '7 days' THEN rd.hourly_efficiency END) AS avg_eff_7d,
        AVG(CASE WHEN rd.metric_date >= CURRENT_DATE - INTERVAL '7 days' THEN rd.total_output END) AS avg_output_7d,
        AVG(CASE WHEN rd.metric_date < CURRENT_DATE - INTERVAL '7 days' THEN rd.hourly_efficiency END) AS avg_eff_prev7d,
        AVG(CASE WHEN rd.metric_date < CURRENT_DATE - INTERVAL '7 days' THEN rd.total_output END) AS avg_output_prev7d
    FROM recent_data rd
),
quality_stats AS (
    SELECT 
        AVG(CASE WHEN rq.metric_date >= CURRENT_DATE - INTERVAL '7 days' THEN rq.defect_rate END) AS avg_defect_7d,
        AVG(CASE WHEN rq.metric_date < CURRENT_DATE - INTERVAL '7 days' THEN rq.defect_rate END) AS avg_defect_prev7d
    FROM recent_quality rq
)
SELECT 
    'efficiency_per_hour' AS metric_name,
    ROUND(ps.avg_eff_7d, 2) AS last_7d_avg,
    ROUND(ps.avg_eff_prev7d, 2) AS prev_7d_avg,
    ROUND((ps.avg_eff_7d - ps.avg_eff_prev7d) / NULLIF(ps.avg_eff_prev7d, 0) * 100, 1) AS change_rate_pct,
    CASE 
        WHEN ps.avg_eff_7d > ps.avg_eff_prev7d * 1.05 THEN 'RISING'
        WHEN ps.avg_eff_7d < ps.avg_eff_prev7d * 0.95 THEN 'FALLING'
        ELSE 'STABLE'
    END AS trend,
    CASE 
        WHEN ABS(ps.avg_eff_7d - ps.avg_eff_prev7d) / NULLIF(ps.avg_eff_prev7d, 0) > 0.2 THEN 'ANOMALY'
        ELSE 'NORMAL'
    END AS warning
FROM period_stats ps

UNION ALL

SELECT 
    'daily_output',
    ROUND(ps.avg_output_7d, 0),
    ROUND(ps.avg_output_prev7d, 0),
    ROUND((ps.avg_output_7d - ps.avg_output_prev7d) / NULLIF(ps.avg_output_prev7d, 0) * 100, 1),
    CASE 
        WHEN ps.avg_output_7d > ps.avg_output_prev7d * 1.05 THEN 'RISING'
        WHEN ps.avg_output_7d < ps.avg_output_prev7d * 0.95 THEN 'FALLING'
        ELSE 'STABLE'
    END,
    CASE 
        WHEN ABS(ps.avg_output_7d - ps.avg_output_prev7d) / NULLIF(ps.avg_output_prev7d, 0) > 0.2 THEN 'ANOMALY'
        ELSE 'NORMAL'
    END
FROM period_stats ps

UNION ALL

SELECT 
    'defect_rate_pct',
    ROUND(qs.avg_defect_7d, 2),
    ROUND(qs.avg_defect_prev7d, 2),
    ROUND((qs.avg_defect_7d - qs.avg_defect_prev7d) / NULLIF(qs.avg_defect_prev7d, 0) * 100, 1),
    CASE 
        WHEN qs.avg_defect_7d > qs.avg_defect_prev7d * 1.1 THEN 'RISING'
        WHEN qs.avg_defect_7d < qs.avg_defect_prev7d * 0.9 THEN 'FALLING'
        ELSE 'STABLE'
    END,
    CASE 
        WHEN qs.avg_defect_7d > 8 THEN 'THRESHOLD_EXCEEDED'
        WHEN qs.avg_defect_7d > qs.avg_defect_prev7d * 1.3 THEN 'ANOMALY_RISING'
        ELSE 'NORMAL'
    END
FROM quality_stats qs;


-- =====================================================
-- 补充查询3：拐点预警汇总
-- =====================================================
WITH daily_eff AS (
    SELECT 
        DATE(lr.event_time_utc::timestamp) AS metric_date,
        SUM(lr.report_qty) / NULLIF(SUM(lr.duration_minutes) / 60.0, 0) AS hourly_efficiency
    FROM fact_labor_report lr
    GROUP BY DATE(lr.event_time_utc::timestamp)
),
with_ma_step1 AS (
    SELECT 
        metric_date,
        hourly_efficiency,
        AVG(hourly_efficiency) OVER (ORDER BY metric_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
    FROM daily_eff
),
with_ma AS (
    SELECT 
        metric_date,
        hourly_efficiency,
        ma7,
        LAG(ma7, 1) OVER (ORDER BY metric_date) AS prev_ma7,
        LAG(ma7, 2) OVER (ORDER BY metric_date) AS prev2_ma7
    FROM with_ma_step1
),
turning_points AS (
    SELECT 
        metric_date,
        hourly_efficiency,
        ma7,
        prev_ma7,
        prev2_ma7,
        CASE 
            WHEN prev2_ma7 IS NOT NULL 
                 AND prev_ma7 < prev2_ma7 AND ma7 > prev_ma7 THEN 'UPWARD'
            WHEN prev2_ma7 IS NOT NULL 
                 AND prev_ma7 > prev2_ma7 AND ma7 < prev_ma7 THEN 'DOWNWARD'
            ELSE NULL
        END AS turning_type
    FROM with_ma
)
SELECT 
    metric_date,
    ROUND(hourly_efficiency, 2) AS daily_efficiency,
    ROUND(ma7, 2) AS ma7_line,
    turning_type,
    CASE 
        WHEN turning_type = 'UPWARD' THEN 'MAINTAIN_CURRENT_MEASURES'
        WHEN turning_type = 'DOWNWARD' THEN 'INVESTIGATE_AND_IMPROVE'
        ELSE 'NONE'
    END AS recommendation
FROM turning_points
WHERE turning_type IS NOT NULL
ORDER BY metric_date DESC
LIMIT 10;