-- =====================================================
-- 交付风险预测：延迟概率与红/黄/绿预警等级
-- 基于历史订单的生产周期、物流延误、设备故障等特征
-- =====================================================

WITH 
-- 1. 全局生产特征（汇总所有工单）
global_production AS (
    SELECT 
        COUNT(*) AS total_wo_count,
        AVG(CASE WHEN planned_qty > 0 THEN completed_qty / planned_qty ELSE 0 END) AS avg_completion_rate,
        SUM(CASE WHEN status IN ('OPEN', 'STARTED') THEN 1 ELSE 0 END) AS pending_wo_count,
        SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_wo_count
    FROM fact_work_order
),

-- 2. 全局质检特征
global_quality AS (
    SELECT 
        COUNT(*) AS total_inspection_count,
        SUM(COALESCE(pass_qty, 0)) AS total_pass_qty,
        SUM(COALESCE(fail_qty, 0)) AS total_fail_qty,
        CASE WHEN SUM(COALESCE(pass_qty, 0) + COALESCE(fail_qty, 0)) > 0 
             THEN SUM(COALESCE(pass_qty, 0))::FLOAT / SUM(COALESCE(pass_qty, 0) + COALESCE(fail_qty, 0))
             ELSE 1 END AS qc_pass_rate
    FROM fact_quality_inspection
),

-- 3. 全局工序不良特征
global_operation AS (
    SELECT 
        COUNT(*) AS total_task_count,
        SUM(COALESCE(good_qty, 0)) AS total_good_qty,
        SUM(COALESCE(bad_qty, 0)) AS total_bad_qty,
        CASE WHEN SUM(COALESCE(good_qty, 0) + COALESCE(bad_qty, 0)) > 0 
             THEN SUM(COALESCE(bad_qty, 0))::FLOAT / SUM(COALESCE(good_qty, 0) + COALESCE(bad_qty, 0))
             ELSE 0 END AS operation_defect_rate
    FROM fact_operation_task
),

-- 4. 客户级别发货统计
customer_shipment AS (
    SELECT 
        customer_id,
        COUNT(*) AS shipment_count,
        SUM(COALESCE(amount, 0)) AS total_shipment_amount
    FROM fact_sales_shipment
    GROUP BY customer_id
),

-- 5. 客户级别退货统计
customer_return AS (
    SELECT 
        customer_id,
        COUNT(*) AS return_count,
        SUM(COALESCE(amount, 0)) AS total_return_amount
    FROM fact_sales_return
    GROUP BY customer_id
),

-- 6. 订单风险评估
order_risk AS (
    SELECT 
        so.sales_order_id,
        so.sales_order_number,
        c.customer_name,
        so.order_date_utc,
        so.deal_amount,
        so.payment_status,
        
        -- 全局生产指标
        gp.avg_completion_rate AS production_completion_rate,
        gp.pending_wo_count,
        gp.total_wo_count AS work_order_count,
        
        -- 全局质检指标
        gq.qc_pass_rate,
        gq.total_fail_qty,
        
        -- 全局工序指标
        go.operation_defect_rate,
        
        -- 客户级别指标
        COALESCE(cs.shipment_count, 0) AS shipment_count,
        COALESCE(cr.return_count, 0) AS return_count,
        CASE WHEN COALESCE(cs.shipment_count, 0) > 0 
             THEN COALESCE(cr.return_count, 0)::FLOAT / cs.shipment_count 
             ELSE 0 END AS return_rate

    FROM fact_sales_order so
    LEFT JOIN dim_customer c ON so.customer_id = c.customer_id AND c.is_current = true
    CROSS JOIN global_production gp
    CROSS JOIN global_quality gq
    CROSS JOIN global_operation go
    LEFT JOIN customer_shipment cs ON so.customer_id = cs.customer_id
    LEFT JOIN customer_return cr ON so.customer_id = cr.customer_id
)

-- 7. 最终输出
SELECT 
    sales_order_id,
    sales_order_number,
    customer_name,
    order_date_utc,
    deal_amount,
    payment_status,
    
    -- 风险特征
    work_order_count,
    ROUND(production_completion_rate::NUMERIC, 2) AS production_completion_rate,
    pending_wo_count,
    ROUND(qc_pass_rate::NUMERIC, 2) AS qc_pass_rate,
    ROUND(operation_defect_rate::NUMERIC, 4) AS operation_defect_rate,
    return_count,
    ROUND(return_rate::NUMERIC, 4) AS return_rate,
    
    -- 延迟概率
    ROUND((
        CASE WHEN production_completion_rate < 0.3 THEN 0.30
             WHEN production_completion_rate < 0.5 THEN 0.20
             WHEN production_completion_rate < 0.8 THEN 0.10
             ELSE 0 END
        + CASE WHEN qc_pass_rate < 0.8 THEN 0.25
               WHEN qc_pass_rate < 0.9 THEN 0.15
               WHEN qc_pass_rate < 0.95 THEN 0.08
               ELSE 0 END
        + CASE WHEN operation_defect_rate > 0.1 THEN 0.20
               WHEN operation_defect_rate > 0.05 THEN 0.12
               WHEN operation_defect_rate > 0.02 THEN 0.05
               ELSE 0 END
        + CASE WHEN return_rate > 0.1 THEN 0.15
               WHEN return_rate > 0.05 THEN 0.08
               WHEN return_rate > 0.02 THEN 0.03
               ELSE 0 END
        + CASE WHEN payment_status = 'UNPAID' THEN 0.10
               WHEN payment_status = 'PARTIAL' THEN 0.05
               ELSE 0 END
    )::NUMERIC, 2) AS delay_probability,
    
    -- 红/黄/绿预警
    CASE 
        WHEN (
            CASE WHEN production_completion_rate < 0.3 THEN 0.30 WHEN production_completion_rate < 0.5 THEN 0.20 WHEN production_completion_rate < 0.8 THEN 0.10 ELSE 0 END
            + CASE WHEN qc_pass_rate < 0.8 THEN 0.25 WHEN qc_pass_rate < 0.9 THEN 0.15 WHEN qc_pass_rate < 0.95 THEN 0.08 ELSE 0 END
            + CASE WHEN operation_defect_rate > 0.1 THEN 0.20 WHEN operation_defect_rate > 0.05 THEN 0.12 WHEN operation_defect_rate > 0.02 THEN 0.05 ELSE 0 END
            + CASE WHEN return_rate > 0.1 THEN 0.15 WHEN return_rate > 0.05 THEN 0.08 WHEN return_rate > 0.02 THEN 0.03 ELSE 0 END
            + CASE WHEN payment_status = 'UNPAID' THEN 0.10 WHEN payment_status = 'PARTIAL' THEN 0.05 ELSE 0 END
        ) >= 0.50 THEN 'RED'
        WHEN (
            CASE WHEN production_completion_rate < 0.3 THEN 0.30 WHEN production_completion_rate < 0.5 THEN 0.20 WHEN production_completion_rate < 0.8 THEN 0.10 ELSE 0 END
            + CASE WHEN qc_pass_rate < 0.8 THEN 0.25 WHEN qc_pass_rate < 0.9 THEN 0.15 WHEN qc_pass_rate < 0.95 THEN 0.08 ELSE 0 END
            + CASE WHEN operation_defect_rate > 0.1 THEN 0.20 WHEN operation_defect_rate > 0.05 THEN 0.12 WHEN operation_defect_rate > 0.02 THEN 0.05 ELSE 0 END
            + CASE WHEN return_rate > 0.1 THEN 0.15 WHEN return_rate > 0.05 THEN 0.08 WHEN return_rate > 0.02 THEN 0.03 ELSE 0 END
            + CASE WHEN payment_status = 'UNPAID' THEN 0.10 WHEN payment_status = 'PARTIAL' THEN 0.05 ELSE 0 END
        ) >= 0.25 THEN 'YELLOW'
        ELSE 'GREEN'
    END AS risk_level,
    
    -- 风险原因
    CONCAT_WS(' | ',
        CASE WHEN production_completion_rate < 0.5 THEN '生产进度滞后' END,
        CASE WHEN qc_pass_rate < 0.9 THEN '质检通过率低' END,
        CASE WHEN operation_defect_rate > 0.05 THEN '工序不良率高' END,
        CASE WHEN return_rate > 0.05 THEN '历史退货率高' END,
        CASE WHEN payment_status = 'UNPAID' THEN '未付款' END
    ) AS risk_reasons

FROM order_risk
ORDER BY delay_probability DESC, deal_amount DESC;
