-- 1. Products with optional categories
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    COALESCE(c.category_name, 'Uncategorized') AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
ORDER BY c.category_name, p.product_name;

-- 2. Customers who never wrote reviews
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,
    c.registration_date
FROM customers c
LEFT JOIN reviews r ON c.customer_id = r.user_id
WHERE r.review_id IS NULL
ORDER BY c.registration_date;

-- 3. Employee hierarchy (self-join)
SELECT 
    e.employee_id,
    e.employee_name,
    e.title,
    COALESCE(m.employee_name, 'No Manager') AS manager_name,
    m.title AS manager_title
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.employee_id, e.employee_id;

-- 4. All dates with sales data (using date table or generation)
WITH RECURSIVE date_range AS (
    SELECT DATE('2024-01-01') AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < DATE('2024-01-31')
)
SELECT 
    dr.date,
    COUNT(o.order_id) AS orders_count,
    COALESCE(SUM(o.total_amount), 0) AS daily_sales
FROM date_range dr
LEFT JOIN orders o ON DATE(o.order_date) = dr.date
GROUP BY dr.date
ORDER BY dr.date;

-- 5. Products never ordered
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    p.stock_quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL
    AND p.is_active = TRUE
ORDER BY p.product_name;

-- 6. All product-coupon combinations
SELECT 
    p.product_id,
    p.product_name,
    p.price,
    c.coupon_code,
    c.discount_type,
    c.discount_value,
    CASE 
        WHEN c.discount_type = 'percentage' 
        THEN p.price * (1 - c.discount_value/100)
        ELSE p.price - c.discount_value
    END AS price_after_coupon
FROM products p
CROSS JOIN coupons c
WHERE c.is_active = TRUE
    AND c.valid_from <= CURDATE()
    AND c.valid_until >= CURDATE()
ORDER BY p.product_id, c.discount_value DESC;

-- 7. Customers who ordered every product in a category
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT p.product_id
    FROM products p
    WHERE p.category_id = 1  -- Electronics category
    AND NOT EXISTS (
        SELECT 1
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.customer_id = c.customer_id
        AND oi.product_id = p.product_id
    )
);

-- 8. This month vs last month comparison
WITH this_month AS (
    SELECT 
        DATE(order_date) AS date,
        COUNT(*) AS orders,
        SUM(total_amount) AS sales
    FROM orders
    WHERE MONTH(order_date) = MONTH(CURDATE())
        AND YEAR(order_date) = YEAR(CURDATE())
    GROUP BY DATE(order_date)
),
last_month AS (
    SELECT 
        DATE_ADD(DATE(order_date), INTERVAL 1 MONTH) AS date,
        COUNT(*) AS orders,
        SUM(total_amount) AS sales
    FROM orders
    WHERE MONTH(order_date) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
        AND YEAR(order_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
    GROUP BY DATE(order_date)
)
SELECT 
    COALESCE(t.date, l.date) AS date,
    COALESCE(t.orders, 0) AS this_month_orders,
    COALESCE(l.orders, 0) AS last_month_orders,
    COALESCE(t.sales, 0) AS this_month_sales,
    COALESCE(l.sales, 0) AS last_month_sales,
    COALESCE(t.sales, 0) - COALESCE(l.sales, 0) AS difference
FROM this_month t
FULL OUTER JOIN last_month l ON t.date = l.date
ORDER BY date;