-- =====================================================
-- 一页式决策简报SQL
-- One-Page Decision Brief
-- 数据库: PostgreSQL
-- =====================================================

-- =====================================================
-- 主查询：一页式决策简报
-- =====================================================
WITH 
sales_summary AS (
    SELECT 
        COUNT(*) AS total_orders,
        SUM(deal_amount) AS total_sales_amount,
        AVG(deal_amount) AS avg_order_amount,
        SUM(CASE WHEN payment_status = 'PAID' THEN 1 ELSE 0 END) AS paid_orders,
        SUM(CASE WHEN payment_status = 'PARTIAL' THEN 1 ELSE 0 END) AS partial_orders,
        SUM(CASE WHEN payment_status = 'UNPAID' THEN 1 ELSE 0 END) AS unpaid_orders,
        SUM(CASE WHEN payment_status = 'PAID' THEN deal_amount ELSE 0 END) AS paid_amount,
        SUM(CASE WHEN payment_status = 'UNPAID' THEN deal_amount ELSE 0 END) AS unpaid_amount
    FROM fact_sales_order
),

production_summary AS (
    SELECT 
        COUNT(*) AS total_work_orders,
        SUM(planned_qty) AS total_planned_qty,
        SUM(completed_qty) AS total_completed_qty,
        SUM(CASE WHEN status = 'CLOSED' THEN 1 ELSE 0 END) AS closed_orders,
        SUM(CASE WHEN status = 'STARTED' THEN 1 ELSE 0 END) AS started_orders,
        SUM(CASE WHEN status = 'OPEN' THEN 1 ELSE 0 END) AS open_orders
    FROM fact_work_order
),

ar_summary AS (
    SELECT 
        COUNT(*) AS receipt_count,
        SUM(amount) AS total_receipt_amount,
        AVG(amount) AS avg_receipt_amount
    FROM fact_ar_receipt
),

ap_summary AS (
    SELECT 
        COUNT(*) AS payment_count,
        SUM(amount) AS total_payment_amount,
        AVG(amount) AS avg_payment_amount
    FROM fact_ap_payment
),

invoice_summary AS (
    SELECT 
        COUNT(*) AS invoice_count,
        SUM(invoice_amount) AS total_invoice_amount,
        AVG(invoice_amount) AS avg_invoice_amount
    FROM fact_invoice
),

return_summary AS (
    SELECT 
        COUNT(*) AS return_count,
        SUM(amount) AS total_return_amount,
        AVG(amount) AS avg_return_amount
    FROM fact_sales_return
),

shipment_summary AS (
    SELECT 
        COUNT(*) AS shipment_count,
        SUM(amount) AS total_shipment_amount,
        AVG(amount) AS avg_shipment_amount
    FROM fact_sales_shipment
),

purchase_summary AS (
    SELECT COUNT(*) AS purchase_order_count
    FROM fact_purchase_order
),

quality_summary AS (
    SELECT 
        COUNT(*) AS inspection_count,
        SUM(pass_qty) AS total_pass_qty,
        SUM(fail_qty) AS total_fail_qty
    FROM fact_quality_inspection
),

labor_summary AS (
    SELECT 
        COUNT(DISTINCT worker_name) AS worker_count,
        SUM(duration_minutes) AS total_work_minutes,
        SUM(report_qty) AS total_output_qty
    FROM fact_labor_report
),

scrap_summary AS (
    SELECT COUNT(*) AS scrap_count
    FROM fact_scrap
)

SELECT 
    'Decision Brief' AS report_title,
    CURRENT_DATE AS report_date,
    
    -- 销售板块
    ss.total_orders AS sales_order_count,
    ROUND(ss.total_sales_amount, 2) AS total_sales_amount,
    ROUND(ss.avg_order_amount, 2) AS avg_order_amount,
    ss.paid_orders AS paid_order_count,
    ss.partial_orders AS partial_paid_count,
    ss.unpaid_orders AS unpaid_order_count,
    ROUND(ss.paid_orders * 100.0 / NULLIF(ss.total_orders, 0), 1) AS payment_completion_rate,
    ROUND(ss.unpaid_amount, 2) AS receivable_amount,
    
    -- 生产板块
    ps.total_work_orders AS work_order_count,
    ps.closed_orders AS completed_work_orders,
    ps.started_orders AS in_progress_work_orders,
    ps.open_orders AS pending_work_orders,
    ROUND(ps.total_planned_qty, 0) AS planned_qty,
    ROUND(ps.total_completed_qty, 0) AS completed_qty,
    ROUND(ps.total_completed_qty * 100.0 / NULLIF(ps.total_planned_qty, 0), 1) AS production_completion_rate,
    
    -- 人效板块
    ls.worker_count AS active_worker_count,
    ROUND(ls.total_work_minutes / 60.0, 1) AS total_work_hours,
    ROUND(ls.total_output_qty, 0) AS total_output,
    ROUND(ls.total_output_qty / NULLIF(ls.worker_count, 0), 1) AS output_per_worker,
    ROUND(ls.total_output_qty / NULLIF(ls.total_work_minutes / 60.0, 0), 2) AS output_per_hour,
    
    -- 质量板块
    qs.inspection_count AS qc_batch_count,
    ROUND(qs.total_pass_qty, 0) AS pass_qty,
    ROUND(qs.total_fail_qty, 0) AS fail_qty,
    ROUND(qs.total_pass_qty * 100.0 / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0), 2) AS pass_rate,
    ROUND(qs.total_fail_qty * 100.0 / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0), 2) AS defect_rate,
    scr.scrap_count AS scrap_record_count,
    
    -- 财务板块
    ar.receipt_count AS ar_receipt_count,
    ROUND(ar.total_receipt_amount, 2) AS total_ar_amount,
    ap.payment_count AS ap_payment_count,
    ROUND(ap.total_payment_amount, 2) AS total_ap_amount,
    ROUND(ar.total_receipt_amount - ap.total_payment_amount, 2) AS net_cash_flow,
    inv.invoice_count AS invoice_count,
    ROUND(inv.total_invoice_amount, 2) AS total_invoice_amount,
    
    -- 物流板块
    sh.shipment_count AS shipment_count,
    ROUND(sh.total_shipment_amount, 2) AS total_shipment_amount,
    pur.purchase_order_count AS purchase_order_count,
    
    -- 售后板块
    ret.return_count AS return_count,
    ROUND(ret.total_return_amount, 2) AS total_return_amount,
    ROUND(ret.total_return_amount * 100.0 / NULLIF(ss.total_sales_amount, 0), 2) AS return_rate,
    
    -- 综合健康度评估
    CASE 
        WHEN (qs.total_pass_qty * 100.0 / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0)) >= 95
             AND (ps.total_completed_qty * 100.0 / NULLIF(ps.total_planned_qty, 0)) >= 80
             AND (ret.total_return_amount * 100.0 / NULLIF(ss.total_sales_amount, 0)) < 5
        THEN 'EXCELLENT'
        WHEN (qs.total_pass_qty * 100.0 / NULLIF(qs.total_pass_qty + qs.total_fail_qty, 0)) >= 90
             AND (ps.total_completed_qty * 100.0 / NULLIF(ps.total_planned_qty, 0)) >= 60
             AND (ret.total_return_amount * 100.0 / NULLIF(ss.total_sales_amount, 0)) < 10
        THEN 'GOOD'
        ELSE 'WARNING'
    END AS health_status

FROM sales_summary ss
CROSS JOIN production_summary ps
CROSS JOIN ar_summary ar
CROSS JOIN ap_summary ap
CROSS JOIN invoice_summary inv
CROSS JOIN return_summary ret
CROSS JOIN shipment_summary sh
CROSS JOIN purchase_summary pur
CROSS JOIN quality_summary qs
CROSS JOIN labor_summary ls
CROSS JOIN scrap_summary scr;


-- =====================================================
-- 补充查询1：本月 vs 上月对比分析
-- =====================================================
WITH 
current_month AS (
    SELECT 
        'current_month' AS period,
        COUNT(*) AS order_count,
        SUM(deal_amount) AS sales_amount
    FROM fact_sales_order
    WHERE DATE_TRUNC('month', order_date_utc::timestamp) = DATE_TRUNC('month', CURRENT_DATE)
),
last_month AS (
    SELECT 
        'last_month' AS period,
        COUNT(*) AS order_count,
        SUM(deal_amount) AS sales_amount
    FROM fact_sales_order
    WHERE DATE_TRUNC('month', order_date_utc::timestamp) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
)
SELECT 
    period,
    order_count,
    ROUND(sales_amount, 2) AS sales_amount
FROM current_month
UNION ALL
SELECT * FROM last_month;


-- =====================================================
-- 补充查询2：客户贡献度TOP10
-- =====================================================
SELECT 
    c.customer_name,
    COUNT(so.sales_order_id) AS order_count,
    ROUND(SUM(so.deal_amount), 2) AS total_order_amount,
    ROUND(SUM(so.deal_amount) * 100.0 / (SELECT SUM(deal_amount) FROM fact_sales_order), 2) AS contribution_rate,
    SUM(CASE WHEN so.payment_status = 'PAID' THEN 1 ELSE 0 END) AS paid_order_count,
    SUM(CASE WHEN so.payment_status = 'UNPAID' THEN so.deal_amount ELSE 0 END) AS receivable_amount
FROM fact_sales_order so
INNER JOIN dim_customer c ON so.customer_id = c.customer_id AND c.is_current = 't'
GROUP BY c.customer_name
ORDER BY total_order_amount DESC
LIMIT 10;


-- =====================================================
-- 补充查询3：产品类别业绩分析
-- =====================================================
SELECT 
    p.product_category,
    COUNT(DISTINCT wo.work_order_id) AS work_order_count,
    ROUND(SUM(wo.planned_qty), 0) AS planned_qty,
    ROUND(SUM(wo.completed_qty), 0) AS completed_qty,
    ROUND(SUM(wo.completed_qty) * 100.0 / NULLIF(SUM(wo.planned_qty), 0), 1) AS completion_rate,
    COUNT(DISTINCT lr.worker_name) AS worker_count,
    ROUND(SUM(lr.duration_minutes) / 60.0, 1) AS total_work_hours
FROM dim_product p
LEFT JOIN fact_work_order wo ON p.product_id = wo.product_id
LEFT JOIN fact_labor_report lr ON p.product_id = lr.product_id
WHERE p.is_current = 't'
GROUP BY p.product_category
ORDER BY completed_qty DESC;


-- =====================================================
-- 补充查询4：关键预警指标
-- =====================================================
SELECT 
    'warning_metrics' AS category,
    'unpaid_order_amount' AS metric_name,
    ROUND(SUM(CASE WHEN payment_status = 'UNPAID' THEN deal_amount ELSE 0 END), 2) AS current_value,
    50000 AS threshold,
    CASE 
        WHEN SUM(CASE WHEN payment_status = 'UNPAID' THEN deal_amount ELSE 0 END) > 50000 
        THEN 'EXCEEDED' ELSE 'NORMAL' 
    END AS status
FROM fact_sales_order

UNION ALL

SELECT 
    'warning_metrics',
    'defect_rate_pct',
    ROUND(SUM(fail_qty) * 100.0 / NULLIF(SUM(pass_qty) + SUM(fail_qty), 0), 2),
    5,
    CASE 
        WHEN SUM(fail_qty) * 100.0 / NULLIF(SUM(pass_qty) + SUM(fail_qty), 0) > 5 
        THEN 'EXCEEDED' ELSE 'NORMAL' 
    END
FROM fact_quality_inspection

UNION ALL

SELECT 
    'warning_metrics',
    'production_completion_rate',
    ROUND(SUM(completed_qty) * 100.0 / NULLIF(SUM(planned_qty), 0), 2),
    70,
    CASE 
        WHEN SUM(completed_qty) * 100.0 / NULLIF(SUM(planned_qty), 0) < 70 
        THEN 'LOW' ELSE 'NORMAL' 
    END
FROM fact_work_order

UNION ALL

SELECT 
    'warning_metrics',
    'return_rate_pct',
    ROUND((SELECT SUM(amount) FROM fact_sales_return) * 100.0 / 
          NULLIF((SELECT SUM(deal_amount) FROM fact_sales_order), 0), 2),
    5,
    CASE 
        WHEN (SELECT SUM(amount) FROM fact_sales_return) * 100.0 / 
             NULLIF((SELECT SUM(deal_amount) FROM fact_sales_order), 0) > 5 
        THEN 'EXCEEDED' ELSE 'NORMAL' 
    END;


-- =====================================================
-- 补充查询5：月度趋势汇总
-- =====================================================
SELECT 
    TO_CHAR(DATE_TRUNC('month', order_date_utc::timestamp), 'YYYY-MM') AS month,
    COUNT(*) AS order_count,
    ROUND(SUM(deal_amount), 2) AS sales_amount,
    ROUND(AVG(deal_amount), 2) AS avg_order_amount,
    SUM(CASE WHEN payment_status = 'PAID' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN payment_status = 'UNPAID' THEN 1 ELSE 0 END) AS unpaid_count
FROM fact_sales_order
GROUP BY DATE_TRUNC('month', order_date_utc::timestamp)
ORDER BY month DESC
LIMIT 12;