-- Read Pinduoduo data, normalize fields
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');

-- Read Taobao data, normalize fields
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');

-- Read Shopify data, normalize fields
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;

-- Multi-dimensional profit analysis
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;

-- Weekly revenue trends per platform with WoW change
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,
        -- Week-over-week comparison
        LAG(ROUND(SUM(amount) FILTER (WHERE status = 'completed'), 2))
            OVER (PARTITION BY platform ORDER BY DATE_TRUNC('week', order_time)) AS prev_week_revenue,
        -- WoW growth rate
        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 analysis: Tier SKUs by revenue contribution
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,
        -- Cumulative revenue share for ABC tiering
        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 'Tier A (Cash Cow)'
        WHEN cumulative_revenue_share <= 0.9 THEN 'Tier B (Stable)'
        ELSE 'Tier C (Long Tail)'
    END AS sku_tier,
    -- Refund rate alert
    CASE
        WHEN ROUND(
            1.0 * refund_count / NULLIF(order_count, 0) * 100, 2
        ) > 15 THEN '⚠️ High refund rate'
        ELSE '✅ Normal'
    END AS risk_flag
FROM sku_stats
ORDER BY total_revenue DESC;