Agent-02 每日经营分析

-- 每日核心KPI汇总
SELECT 
    toDate(order_date) AS date,
    COUNT(DISTINCT order_no) AS order_count,
    SUM(total_amount) AS daily_sales,
    SUM(total_amount) - SUM(cost_amount) AS daily_profit,
    (SUM(total_amount) - SUM(cost_amount)) / NULLIF(SUM(total_amount), 0) * 100 AS gross_margin_pct,
    SUM(CASE WHEN payment_method = 'credit' THEN total_amount ELSE 0 END) AS credit_sales,
    COUNT(DISTINCT customer_id) AS customer_count
FROM sales_order
WHERE toDate(order_date) = today() - 1
  AND order_status NOT IN ('cancelled', 'returned')
GROUP BY toDate(order_date);

-- 环比计算
WITH today_kpi AS (
    SELECT SUM(total_amount) AS sales FROM sales_order WHERE toDate(order_date) = today() - 1
),
yesterday_kpi AS (
    SELECT SUM(total_amount) AS sales FROM sales_order WHERE toDate(order_date) = today() - 2
)
SELECT 
    (t.sales - y.sales) / NULLIF(y.sales, 0) * 100 AS sales_growth_pct
FROM today_kpi t, yesterday_kpi y;

-- 爆款产品TOP10（含库存可售天数）
SELECT 
    p.product_code,
    p.product_name,
    p.category,
    SUM(s.quantity) AS total_qty,
    SUM(s.quantity * s.unit_price) AS total_sales,
    ROUND(SUM(s.quantity * (s.unit_price - s.cost_price)) / NULLIF(SUM(s.quantity * s.unit_price), 0) * 100, 2) AS profit_margin,
    i.current_stock,
    i.current_stock / NULLIF(s.avg_daily_sales, 0) AS stock_days_remaining
FROM sales_order_detail s
JOIN product_master p ON s.product_id = p.product_id
LEFT JOIN inventory_current i ON s.product_id = i.product_id
LEFT JOIN (
    SELECT product_id, AVG(daily_sales) AS avg_daily_sales
    FROM (
        SELECT product_id, SUM(quantity)/7 AS daily_sales
        FROM sales_order_detail
        WHERE order_date >= today() - 30
        GROUP BY product_id, toDate(order_date)
    )
    GROUP BY product_id
) s2 ON s.product_id = s2.product_id
WHERE toDate(s.order_date) = today() - 1
GROUP BY p.product_code, p.product_name, p.category, i.current_stock, s2.avg_daily_sales
ORDER BY total_sales DESC
LIMIT 10;

-- 库存预警：缺货 + 临期 + 积压
SELECT 
    p.product_code,
    p.product_name,
    i.current_stock,
    i.safe_stock,
    s.avg_daily_sales,
    CASE 
        WHEN i.current_stock = 0 THEN '🔴 缺货'
        WHEN i.current_stock < i.safe_stock THEN '🟠 库存不足'
        WHEN i.current_stock > i.safe_stock * 3 THEN '🟡 积压'
        WHEN p.expiry_date < today() + INTERVAL 30 DAY THEN '🟠 临期'
        ELSE '🟢 正常'
    END AS alert_status
FROM inventory_current i
JOIN product_master p ON i.product_id = p.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity)/30 AS avg_daily_sales
    FROM sales_order_detail
    WHERE order_date >= today() - 30
    GROUP BY product_id
) s ON i.product_id = s.product_id
WHERE i.warehouse_id = 'MAIN_WH'
  AND (
    i.current_stock = 0
    OR i.current_stock < i.safe_stock
    OR i.current_stock > i.safe_stock * 3
    OR (p.expiry_date IS NOT NULL AND p.expiry_date < today() + INTERVAL 30 DAY)
  )
ORDER BY alert_status, i.current_stock ASC;

-- 员工当日绩效排名
SELECT 
    e.employee_name,
    e.department,
    COUNT(DISTINCT s.order_no) AS order_count,
    SUM(s.total_amount) AS total_sales,
    SUM(s.total_amount - s.cost_amount) AS total_profit,
    ROUND(SUM(s.total_amount - s.cost_amount) / NULLIF(SUM(s.total_amount), 0) * 100, 2) AS profit_margin,
    COUNT(DISTINCT s.customer_id) AS customer_count,
    SUM(CASE WHEN s.payment_status = 'collected' THEN s.total_amount ELSE 0 END) / NULLIF(SUM(s.total_amount), 0) * 100 AS collection_rate
FROM sales_order s
JOIN employee_info e ON s.salesperson_id = e.employee_id
WHERE toDate(s.order_date) = today() - 1
  AND s.order_status NOT IN ('cancelled')
GROUP BY e.employee_name, e.department
ORDER BY total_sales DESC
LIMIT 10;