-- =====================================================
-- 供应链风险预警SQL
-- Supply Chain Risk Warning
-- 数据库: PostgreSQL
-- =====================================================

-- =====================================================
-- 1. 供应商历史交期表现分析
-- =====================================================
WITH supplier_delivery AS (
    SELECT 
        po.supplier_id,
        COUNT(*) AS order_count,
        COUNT(pr.purchase_receipt_id) AS receipt_count,
        AVG(
            CASE WHEN pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL
                 THEN EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp)
                 ELSE NULL END
        ) AS avg_delivery_days,
        MAX(
            CASE WHEN pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL
                 THEN EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp)
                 ELSE NULL END
        ) AS max_delivery_days,
        STDDEV(
            CASE WHEN pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL
                 THEN EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp)
                 ELSE NULL END
        ) AS stddev_delivery_days
    FROM fact_purchase_order po
    LEFT JOIN fact_purchase_receipt pr ON po.supplier_id = pr.supplier_id
    GROUP BY po.supplier_id
),

supplier_quality AS (
    SELECT 
        pr.supplier_id,
        COUNT(pr.purchase_receipt_id) AS total_receipts,
        SUM(pr.receipt_qty_total) AS total_qty,
        SUM(pr.amount) AS total_amount
    FROM fact_purchase_receipt pr
    GROUP BY pr.supplier_id
),

supplier_returns AS (
    SELECT 
        pret.supplier_id,
        COUNT(*) AS return_count,
        SUM(CASE WHEN pret.return_reason = '损坏' THEN 1 ELSE 0 END) AS damage_count
    FROM fact_purchase_return pret
    GROUP BY pret.supplier_id
),

supplier_quality_rate AS (
    SELECT 
        sq.supplier_id,
        sq.total_receipts,
        sq.total_qty,
        sq.total_amount,
        COALESCE(sr.return_count, 0) AS return_count,
        COALESCE(sr.damage_count, 0) AS damage_count,
        CASE WHEN sq.total_receipts > 0 
             THEN (sq.total_receipts - COALESCE(sr.return_count, 0)) * 100.0 / sq.total_receipts
             ELSE 100 END AS quality_rate
    FROM supplier_quality sq
    LEFT JOIN supplier_returns sr ON sq.supplier_id = sr.supplier_id
),

supplier_risk AS (
    SELECT 
        s.supplier_id,
        s.supplier_name,
        s.supplier_category,
        COALESCE(sd.order_count, 0) AS order_count,
        COALESCE(sd.receipt_count, 0) AS receipt_count,
        ROUND(COALESCE(sd.avg_delivery_days, 0), 1) AS avg_delivery_days,
        ROUND(COALESCE(sd.max_delivery_days, 0), 1) AS max_delivery_days,
        ROUND(COALESCE(sd.stddev_delivery_days, 0), 1) AS delivery_volatility,
        COALESCE(sqr.total_receipts, 0) AS total_receipts,
        COALESCE(sqr.return_count, 0) AS return_count,
        ROUND(COALESCE(sqr.quality_rate, 100), 1) AS quality_rate,
        CASE 
            WHEN COALESCE(sd.avg_delivery_days, 0) > 60 THEN 40
            WHEN COALESCE(sd.avg_delivery_days, 0) > 45 THEN 30
            WHEN COALESCE(sd.avg_delivery_days, 0) > 30 THEN 20
            ELSE 10
        END +
        CASE 
            WHEN COALESCE(sd.stddev_delivery_days, 0) > 20 THEN 30
            WHEN COALESCE(sd.stddev_delivery_days, 0) > 10 THEN 20
            ELSE 10
        END AS delivery_risk_score,
        CASE 
            WHEN COALESCE(sqr.quality_rate, 100) < 80 THEN 50
            WHEN COALESCE(sqr.quality_rate, 100) < 90 THEN 30
            WHEN COALESCE(sqr.quality_rate, 100) < 95 THEN 15
            ELSE 5
        END AS quality_risk_score
    FROM dim_supplier s
    LEFT JOIN supplier_delivery sd ON s.supplier_id = sd.supplier_id
    LEFT JOIN supplier_quality_rate sqr ON s.supplier_id = sqr.supplier_id
    WHERE s.is_current = 't'
),

supplier_risk_level AS (
    SELECT 
        *,
        delivery_risk_score + quality_risk_score AS total_risk_score,
        CASE 
            WHEN delivery_risk_score + quality_risk_score >= 80 THEN 'HIGH'
            WHEN delivery_risk_score + quality_risk_score >= 50 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS risk_level,
        CASE 
            WHEN delivery_risk_score >= 50 AND quality_risk_score >= 30 THEN 'DELIVERY_AND_QUALITY'
            WHEN delivery_risk_score >= 50 THEN 'DELIVERY_ISSUE'
            WHEN quality_risk_score >= 30 THEN 'QUALITY_ISSUE'
            ELSE 'NORMAL'
        END AS risk_pattern
    FROM supplier_risk
)

SELECT 
    supplier_name,
    supplier_category,
    order_count,
    receipt_count,
    avg_delivery_days,
    max_delivery_days,
    delivery_volatility,
    total_receipts,
    return_count,
    quality_rate,
    delivery_risk_score,
    quality_risk_score,
    total_risk_score,
    risk_level,
    risk_pattern
FROM supplier_risk_level
ORDER BY total_risk_score DESC, supplier_name;


-- =====================================================
-- 补充查询1：高风险订单预警清单
-- =====================================================
WITH supplier_risk_info AS (
    SELECT 
        s.supplier_id,
        s.supplier_name,
        s.supplier_category,
        COALESCE(
            (SELECT AVG(EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po2.doc_date_utc::timestamp))
             FROM fact_purchase_order po2
             LEFT JOIN fact_purchase_receipt pr ON po2.supplier_id = pr.supplier_id
             WHERE po2.supplier_id = s.supplier_id
               AND pr.doc_date_utc IS NOT NULL), 0
        ) AS avg_delivery_days,
        COALESCE(
            (SELECT COUNT(*) FROM fact_purchase_return pret WHERE pret.supplier_id = s.supplier_id), 0
        ) AS return_count,
        COALESCE(
            (SELECT COUNT(*) FROM fact_purchase_receipt pr WHERE pr.supplier_id = s.supplier_id), 0
        ) AS receipt_count
    FROM dim_supplier s
    WHERE s.is_current = 't'
),
order_risk AS (
    SELECT 
        po.purchase_order_number,
        po.doc_date_utc::date AS order_date,
        sri.supplier_name,
        sri.supplier_category,
        ROUND(sri.avg_delivery_days, 1) AS supplier_avg_days,
        sri.return_count,
        sri.receipt_count,
        CASE WHEN sri.receipt_count > 0 
             THEN ROUND((sri.receipt_count - sri.return_count) * 100.0 / sri.receipt_count, 1)
             ELSE 100 END AS quality_rate,
        EXTRACT(DAY FROM CURRENT_TIMESTAMP - po.doc_date_utc::timestamp) AS days_since_order,
        CASE 
            WHEN sri.avg_delivery_days > 45 AND sri.return_count > 0 THEN 'HIGH'
            WHEN sri.avg_delivery_days > 45 OR sri.return_count > 0 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS risk_level
    FROM fact_purchase_order po
    JOIN supplier_risk_info sri ON po.supplier_id = sri.supplier_id
)
SELECT 
    purchase_order_number,
    order_date,
    supplier_name,
    supplier_category,
    supplier_avg_days,
    quality_rate,
    days_since_order,
    risk_level,
    CASE 
        WHEN risk_level = 'HIGH' THEN 'IMMEDIATE_FOLLOWUP'
        WHEN risk_level = 'MEDIUM' THEN 'MONITOR'
        ELSE 'NORMAL'
    END AS action_required
FROM order_risk
WHERE risk_level IN ('HIGH', 'MEDIUM')
ORDER BY 
    CASE risk_level WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 ELSE 3 END,
    days_since_order DESC;


-- =====================================================
-- 补充查询2：供应商类别风险分布
-- =====================================================
WITH supplier_stats AS (
    SELECT 
        s.supplier_category,
        COUNT(DISTINCT s.supplier_id) AS supplier_count,
        COUNT(DISTINCT po.purchase_order_id) AS order_count,
        COUNT(DISTINCT pr.purchase_receipt_id) AS receipt_count,
        COUNT(DISTINCT pret.purchase_return_id) AS return_count,
        SUM(pr.amount) AS total_amount
    FROM dim_supplier s
    LEFT JOIN fact_purchase_order po ON s.supplier_id = po.supplier_id
    LEFT JOIN fact_purchase_receipt pr ON s.supplier_id = pr.supplier_id
    LEFT JOIN fact_purchase_return pret ON s.supplier_id = pret.supplier_id
    WHERE s.is_current = 't'
    GROUP BY s.supplier_category
)
SELECT 
    supplier_category,
    supplier_count,
    order_count,
    receipt_count,
    return_count,
    CASE WHEN receipt_count > 0 
         THEN ROUND((receipt_count - return_count) * 100.0 / receipt_count, 1)
         ELSE 100 END AS quality_rate,
    ROUND(COALESCE(total_amount, 0), 2) AS purchase_amount,
    CASE 
        WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count > 10 THEN 'HIGH'
        WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count > 5 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS category_risk_level
FROM supplier_stats
ORDER BY return_count DESC;


-- =====================================================
-- 补充查询3：近期交期异常趋势
-- =====================================================
WITH monthly_delivery AS (
    SELECT 
        DATE_TRUNC('month', pr.doc_date_utc::timestamp)::date AS month_start,
        COUNT(*) AS receipt_count,
        AVG(EXTRACT(DAY FROM pr.doc_date_utc::timestamp - po.doc_date_utc::timestamp)) AS avg_delivery_days
    FROM fact_purchase_receipt pr
    JOIN fact_purchase_order po ON pr.supplier_id = po.supplier_id
    WHERE pr.doc_date_utc IS NOT NULL AND po.doc_date_utc IS NOT NULL
    GROUP BY DATE_TRUNC('month', pr.doc_date_utc::timestamp)
),
monthly_returns AS (
    SELECT 
        DATE_TRUNC('month', pret.doc_date_utc::timestamp)::date AS month_start,
        COUNT(*) AS return_count
    FROM fact_purchase_return pret
    GROUP BY DATE_TRUNC('month', pret.doc_date_utc::timestamp)
),
monthly_combined AS (
    SELECT 
        md.month_start,
        md.receipt_count,
        ROUND(md.avg_delivery_days, 1) AS avg_delivery_days,
        COALESCE(mr.return_count, 0) AS return_count
    FROM monthly_delivery md
    LEFT JOIN monthly_returns mr ON md.month_start = mr.month_start
),
with_trend AS (
    SELECT 
        *,
        LAG(avg_delivery_days, 1) OVER (ORDER BY month_start) AS prev_avg_days,
        LAG(return_count, 1) OVER (ORDER BY month_start) AS prev_return_count
    FROM monthly_combined
)
SELECT 
    month_start,
    receipt_count,
    avg_delivery_days,
    return_count,
    CASE WHEN receipt_count > 0 
         THEN ROUND(return_count * 100.0 / receipt_count, 1)
         ELSE 0 END AS return_rate,
    CASE 
        WHEN prev_avg_days IS NULL THEN 'NONE'
        WHEN avg_delivery_days > prev_avg_days * 1.1 THEN 'INCREASING'
        WHEN avg_delivery_days < prev_avg_days * 0.9 THEN 'DECREASING'
        ELSE 'STABLE'
    END AS delivery_trend,
    CASE 
        WHEN prev_return_count IS NULL THEN 'NONE'
        WHEN return_count > prev_return_count THEN 'INCREASING'
        WHEN return_count < prev_return_count THEN 'DECREASING'
        ELSE 'STABLE'
    END AS return_trend
FROM with_trend
ORDER BY month_start DESC;


-- =====================================================
-- 补充查询4：风险预警汇总看板
-- =====================================================
WITH risk_summary AS (
    SELECT 
        s.supplier_id,
        s.supplier_name,
        COALESCE(
            (SELECT COUNT(*) FROM fact_purchase_return pret WHERE pret.supplier_id = s.supplier_id), 0
        ) AS return_count,
        COALESCE(
            (SELECT COUNT(*) FROM fact_purchase_receipt pr WHERE pr.supplier_id = s.supplier_id), 0
        ) AS receipt_count
    FROM dim_supplier s
    WHERE s.is_current = 't'
),
risk_counts AS (
    SELECT 
        SUM(CASE WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count > 10 THEN 1 ELSE 0 END) AS high_risk_suppliers,
        SUM(CASE WHEN receipt_count > 0 AND return_count * 100.0 / receipt_count BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS medium_risk_suppliers,
        SUM(CASE WHEN receipt_count = 0 OR return_count * 100.0 / receipt_count < 5 THEN 1 ELSE 0 END) AS low_risk_suppliers,
        COUNT(*) AS total_suppliers
    FROM risk_summary
),
order_stats AS (
    SELECT 
        COUNT(*) AS pending_orders,
        COUNT(CASE WHEN EXTRACT(DAY FROM CURRENT_TIMESTAMP - doc_date_utc::timestamp) > 30 THEN 1 END) AS overdue_orders
    FROM fact_purchase_order
),
return_stats AS (
    SELECT 
        COUNT(*) AS total_returns,
        COUNT(CASE WHEN doc_date_utc::timestamp >= CURRENT_DATE - INTERVAL '30 days' THEN 1 END) AS recent_returns
    FROM fact_purchase_return
)
SELECT 
    'high_risk_suppliers' AS metric_name,
    rc.high_risk_suppliers::text AS metric_value,
    CASE WHEN rc.high_risk_suppliers > 0 THEN 'ATTENTION_NEEDED' ELSE 'NORMAL' END AS status
FROM risk_counts rc
UNION ALL
SELECT 'medium_risk_suppliers', rc.medium_risk_suppliers::text,
    CASE WHEN rc.medium_risk_suppliers > 2 THEN 'MONITOR' ELSE 'NORMAL' END
FROM risk_counts rc
UNION ALL
SELECT 'low_risk_suppliers', rc.low_risk_suppliers::text, 'NORMAL'
FROM risk_counts rc
UNION ALL
SELECT 'pending_orders', os.pending_orders::text,
    CASE WHEN os.pending_orders > 20 THEN 'BACKLOG' ELSE 'NORMAL' END
FROM order_stats os
UNION ALL
SELECT 'overdue_orders_30d', os.overdue_orders::text,
    CASE WHEN os.overdue_orders > 5 THEN 'DELIVERY_WARNING' ELSE 'NORMAL' END
FROM order_stats os
UNION ALL
SELECT 'recent_returns_30d', rs.recent_returns::text,
    CASE WHEN rs.recent_returns > 3 THEN 'QUALITY_WARNING' ELSE 'NORMAL' END
FROM return_stats rs;


-- =====================================================
-- 补充查询5：供应商风险排行榜
-- =====================================================
WITH supplier_metrics AS (
    SELECT 
        s.supplier_id,
        s.supplier_name,
        s.supplier_category,
        COUNT(DISTINCT po.purchase_order_id) AS order_count,
        COUNT(DISTINCT pr.purchase_receipt_id) AS receipt_count,
        COUNT(DISTINCT pret.purchase_return_id) AS return_count,
        SUM(pr.amount) AS total_amount
    FROM dim_supplier s
    LEFT JOIN fact_purchase_order po ON s.supplier_id = po.supplier_id
    LEFT JOIN fact_purchase_receipt pr ON s.supplier_id = pr.supplier_id
    LEFT JOIN fact_purchase_return pret ON s.supplier_id = pret.supplier_id
    WHERE s.is_current = 't'
    GROUP BY s.supplier_id, s.supplier_name, s.supplier_category
),
ranked_suppliers AS (
    SELECT 
        *,
        CASE WHEN receipt_count > 0 
             THEN ROUND(return_count * 100.0 / receipt_count, 1)
             ELSE 0 END AS return_rate,
        ROW_NUMBER() OVER (ORDER BY 
            CASE WHEN receipt_count > 0 THEN return_count * 1.0 / receipt_count ELSE 0 END DESC,
            return_count DESC
        ) AS risk_rank
    FROM supplier_metrics
)
SELECT 
    risk_rank,
    supplier_name,
    supplier_category,
    order_count,
    receipt_count,
    return_count,
    return_rate,
    ROUND(COALESCE(total_amount, 0), 2) AS purchase_amount,
    CASE 
        WHEN return_rate > 10 THEN 'HIGH_RISK'
        WHEN return_rate > 5 THEN 'MEDIUM_RISK'
        WHEN return_count > 0 THEN 'LOW_RISK'
        ELSE 'EXCELLENT'
    END AS risk_assessment
FROM ranked_suppliers
ORDER BY risk_rank
LIMIT 20;