-- Create orders table
CREATE TABLE shopify_orders (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    financial_status VARCHAR(50),
    fulfillment_status VARCHAR(50),
    total_price DECIMAL(10,2),
    subtotal_price DECIMAL(10,2),
    total_discounts DECIMAL(10,2),
    total_tax DECIMAL(10,2),
    currency VARCHAR(3),
    created_at DATETIME,
    updated_at DATETIME,
    cancelled_at DATETIME,
    referring_site VARCHAR(500),
    landing_site VARCHAR(500),
    source_name VARCHAR(100),
    discount_codes TEXT,
    shipping_country VARCHAR(100),
    shipping_city VARCHAR(100)
);

-- Load CSV
LOAD DATA LOCAL INFILE '/path/to/orders_export.csv'
INTO TABLE shopify_orders
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS;

-- Table structure matching Shopify API response
CREATE TABLE shopify_orders (
    id BIGINT PRIMARY KEY,
    order_number INTEGER,
    customer_id BIGINT,
    email VARCHAR(255),
    total_price DECIMAL(10,2),
    subtotal_price DECIMAL(10,2),
    total_discounts DECIMAL(10,2),
    total_tax DECIMAL(10,2),
    financial_status VARCHAR(50),
    fulfillment_status VARCHAR(50),
    referring_site TEXT,
    landing_site TEXT,
    source_name VARCHAR(100),
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    tags TEXT,
    note TEXT
);

CREATE INDEX idx_orders_created ON shopify_orders(created_at);
CREATE INDEX idx_orders_customer ON shopify_orders(customer_id);
CREATE INDEX idx_orders_status ON shopify_orders(financial_status);

FROM orders
SHOW total_sales, order_count
GROUP BY day
SINCE -30d
ORDER BY day

SELECT 
    CASE 
        WHEN referring_site LIKE '%google%' THEN 'Google'
        WHEN referring_site LIKE '%facebook%' OR referring_site LIKE '%instagram%' THEN 'Meta'
        WHEN referring_site LIKE '%tiktok%' THEN 'TikTok'
        WHEN referring_site IS NULL THEN 'Direct'
        ELSE 'Other'
    END as channel,
    COUNT(DISTINCT customer_id) as new_customers,
    SUM(total_price) as revenue,
    AVG(total_price) as avg_order
FROM shopify_orders
WHERE financial_status = 'paid'
  AND cancelled_at IS NULL
  AND created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY channel
ORDER BY new_customers DESC;

SELECT 
    li1.title as product_a,
    li2.title as product_b,
    COUNT(DISTINCT li1.order_id) as times_together
FROM order_line_items li1
JOIN order_line_items li2 
    ON li1.order_id = li2.order_id 
    AND li1.product_id < li2.product_id
GROUP BY li1.title, li2.title
HAVING times_together >= 5
ORDER BY times_together DESC
LIMIT 20;

SELECT 
    ROUND(AVG(days_between), 0) as avg_days_between_orders,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_between), 0) as median_days
FROM (
    SELECT 
        customer_id,
        DATEDIFF(
            created_at, 
            LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)
        ) as days_between
    FROM shopify_orders
    WHERE financial_status = 'paid' AND cancelled_at IS NULL
) intervals
WHERE days_between IS NOT NULL;