-- 读取拼多多数据，标准化字段
CREATE OR REPLACE TABLE pdd_raw AS
SELECT
    'pdd' AS platform,
    TRIM(订单号) AS order_id,
    TRIM(商品名称) AS product_name,
    CAST(REGEXP_REPLACE(实付金额, '[¥￥,]', '') AS DOUBLE) AS amount,
    CASE
        WHEN 订单状态 LIKE '%已完成%' THEN 'completed'
        WHEN 订单状态 LIKE '%退款%' THEN 'refunded'
        WHEN 订单状态 LIKE '%取消%' THEN 'cancelled'
        ELSE 'pending'
    END AS status,
    CAST(下单时间 AS TIMESTAMP) AS order_time
FROM read_csv_auto('pinduoduo_orders.csv');

-- 读取淘宝数据，标准化字段
CREATE OR REPLACE TABLE taobao_raw AS
SELECT
    'taobao' AS platform,
    TRIM(order_id) AS order_id,
    TRIM(product_title) AS product_name,
    CAST(REGEXP_REPLACE(pay_amount, '[¥￥$,]', '') AS DOUBLE) AS amount,
    CASE
        WHEN status LIKE '%已完成%' THEN 'completed'
        WHEN status LIKE '%退款%' THEN 'refunded'
        WHEN status LIKE '%取消%' THEN 'cancelled'
        ELSE 'pending'
    END AS status,
    CAST(create_time AS TIMESTAMP) AS order_time
FROM read_csv_auto('taobao_orders.csv');

-- 读取 Shopify 数据，标准化字段
CREATE OR REPLACE TABLE shopify_raw AS
SELECT
    'shopify' AS platform,
    TRIM(order_number) AS order_id,
    TRIM(item_name) AS product_name,
    CAST(REGEXP_REPLACE(total_price, '[€£$,¥￥,]', '') AS DOUBLE) AS amount,
    CASE
        WHEN fulfilled_status IN ('fulfilled', 'shipped') THEN 'completed'
        WHEN fulfilled_status IN ('refunded', 'cancelled') THEN 'refunded'
        ELSE 'pending'
    END AS status,
    CAST(created_at AS TIMESTAMP) AS order_time
FROM read_csv_auto('shopify_orders.csv');

CREATE OR REPLACE TABLE all_orders AS
SELECT * FROM pdd_raw
UNION ALL SELECT * FROM taobao_raw
UNION ALL SELECT * FROM shopify_raw;

-- 多维度盈利分析
WITH platform_summary AS (
    SELECT
        platform,
        COUNT(*) AS total_orders,
        COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
        COUNT(*) FILTER (WHERE status = 'refunded') AS refunded_orders,
        ROUND(SUM(amount) FILTER (WHERE status = 'completed'), 2) AS gross_revenue,
        ROUND(SUM(amount) FILTER (WHERE status = 'refunded'), 2) AS refund_amount,
        ROUND(AVG(amount) FILTER (WHERE status = 'completed'), 2) AS avg_order_value,
        ROUND(
            1.0 * COUNT(*) FILTER (WHERE status = 'refunded')
            / NULLIF(COUNT(*), 0) * 100, 2
        ) AS refund_rate_pct
    FROM all_orders
    GROUP BY platform
)
SELECT * FROM platform_summary
ORDER BY gross_revenue DESC;

-- 按周分析各平台营收趋势，加入环比变化
WITH weekly_revenue AS (
    SELECT
        platform,
        DATE_TRUNC('week', order_time) AS trade_week,
        ROUND(SUM(amount) FILTER (WHERE status = 'completed'), 2) AS revenue,
        COUNT(*) FILTER (WHERE status = 'completed') AS orders,
        -- 环比：与上一周的差异
        LAG(ROUND(SUM(amount) FILTER (WHERE status = 'completed'), 2))
            OVER (PARTITION BY platform ORDER BY DATE_TRUNC('week', order_time)) AS prev_week_revenue,
        -- 周环比增长率
        ROUND(
            (SUM(amount) FILTER (WHERE status = 'completed')
            - LAG(SUM(amount) FILTER (WHERE status = 'completed'))
                OVER (PARTITION BY platform ORDER BY DATE_TRUNC('week', order_time)))
            / NULLIF(LAG(SUM(amount) FILTER (WHERE status = 'completed'))
                OVER (PARTITION BY platform ORDER BY DATE_TRUNC('week', order_time)), 0)
            * 100, 2
        ) AS week_over_week_change
    FROM all_orders
    GROUP BY platform, DATE_TRUNC('week', order_time)
)
SELECT * FROM weekly_revenue
ORDER BY platform, trade_week;

-- ABC 分析：按销售额给 SKU 分级
WITH sku_stats AS (
    SELECT
        product_name,
        platform,
        COUNT(*) AS order_count,
        ROUND(SUM(amount) FILTER (WHERE status = 'completed'), 2) AS total_revenue,
        ROUND(AVG(amount) FILTER (WHERE status = 'completed'), 2) AS avg_price,
        COUNT(*) FILTER (WHERE status = 'refunded') AS refund_count,
        -- 累计销售额占比（用于 ABC 分级）
        SUM(SUM(amount) FILTER (WHERE status = 'completed'))
            OVER (ORDER BY SUM(amount) FILTER (WHERE status = 'completed') DESC)
            / NULLIF(SUM(SUM(amount) FILTER (WHERE status = 'completed')))
            OVER () AS cumulative_revenue_share
    FROM all_orders
    GROUP BY product_name, platform
)
SELECT
    product_name,
    platform,
    order_count,
    total_revenue,
    avg_price,
    refund_count,
    ROUND(cumulative_revenue_share * 100, 2) AS cum_rev_pct,
    CASE
        WHEN cumulative_revenue_share <= 0.7 THEN 'A级（现金牛）'
        WHEN cumulative_revenue_share <= 0.9 THEN 'B级（稳定款）'
        ELSE 'C级（长尾款）'
    END AS sku_tier,
    -- 退货率预警
    CASE
        WHEN ROUND(
            1.0 * refund_count / NULLIF(order_count, 0) * 100, 2
        ) > 15 THEN '⚠️ 退货率过高'
        ELSE '✅ 正常'
    END AS risk_flag
FROM sku_stats
ORDER BY total_revenue DESC;