-- 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;