USE classicmodels;

-- =====================================================
-- HANDS-ON 1: Find customers who have not placed any orders
-- =====================================================

-- Solution 1: Using LEFT JOIN
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE o.customerNumber IS NULL;

-- Solution 2: Using NOT IN with subquery
SELECT * 
FROM customers
WHERE customerNumber 
NOT IN (
	SELECT customerNumber 
	FROM orders
);

-- =====================================================
-- HANDS-ON 2A: List of 10 customers who buy the most
-- =====================================================

SELECT 
    c.customerNumber,
    c.customerName,
    c.country,
    SUM(od.quantityOrdered * od.priceEach) AS totalPurchaseValue
FROM customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName, c.country
ORDER BY totalPurchaseValue DESC
LIMIT 10;

-- =====================================================
-- HANDS-ON 2B: Find orders with total values > 60K
-- =====================================================

SELECT 
    o.orderNumber,
    o.orderDate,
    o.customerNumber,
    c.customerName,
    SUM(od.quantityOrdered * od.priceEach) AS orderTotalValue
FROM orders o
INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
INNER JOIN customers c ON o.customerNumber = c.customerNumber
GROUP BY o.orderNumber, o.orderDate, o.customerNumber, c.customerName
HAVING orderTotalValue > 60000
ORDER BY orderTotalValue DESC;

-- =====================================================
-- HANDS-ON 3: Customer Grouping (Platinum/Gold/Silver)
-- Platinum: orders > 100K
-- Gold: orders between 10K and 100K
-- Silver: orders < 10K
-- =====================================================

SELECT 
    c.customerNumber,
    c.customerName,
    SUM(od.quantityOrdered * od.priceEach) AS totalSales,
    CASE
        WHEN SUM(od.quantityOrdered * od.priceEach) > 100000 THEN 'Platinum'
        WHEN SUM(od.quantityOrdered * od.priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        ELSE 'Silver'
    END AS customerGroup
FROM customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY c.customerNumber, c.customerName
ORDER BY totalSales DESC;

-- =====================================================
-- HANDS-ON 4: Find the company's monthly sales
-- =====================================================

SELECT 
    YEAR(o.orderDate) AS orderYear,
    MONTH(o.orderDate) AS orderMonth,
    DATE_FORMAT(o.orderDate, '%Y-%m') AS yearMonth,
    COUNT(DISTINCT o.orderNumber) AS totalOrders,
    SUM(od.quantityOrdered * od.priceEach) AS monthlySales
FROM orders o
INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY YEAR(o.orderDate), MONTH(o.orderDate), DATE_FORMAT(o.orderDate, '%Y-%m')
ORDER BY orderYear, orderMonth;

-- Alternative version with month names
SELECT 
    YEAR(o.orderDate) AS orderYear,
    MONTHNAME(o.orderDate) AS monthName,
    DATE_FORMAT(o.orderDate, '%Y-%m') AS yearMonth,
    COUNT(DISTINCT o.orderNumber) AS totalOrders,
    ROUND(SUM(od.quantityOrdered * od.priceEach), 2) AS monthlySales
FROM orders o
INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY YEAR(o.orderDate), MONTH(o.orderDate), MONTHNAME(o.orderDate), DATE_FORMAT(o.orderDate, '%Y-%m')
ORDER BY orderYear, MONTH(o.orderDate);

-- =====================================================
-- HANDS-ON 5: Find amount customers owe and remaining credit
-- Amount owed = Total order value - Total payments made
-- Remaining credit = Credit limit - Amount owed
-- =====================================================

SELECT 
    c.customerNumber,
    c.customerName,
    c.creditLimit,
    COALESCE(SUM(od.quantityOrdered * od.priceEach), 0) AS totalOrderValue,
    COALESCE(SUM(p.amount), 0) AS totalPayments,
    COALESCE(SUM(od.quantityOrdered * od.priceEach), 0) - COALESCE(SUM(p.amount), 0) AS amountOwed,
    c.creditLimit - (COALESCE(SUM(od.quantityOrdered * od.priceEach), 0) - COALESCE(SUM(p.amount), 0)) AS remainingCredit
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
LEFT JOIN orderdetails od ON o.orderNumber = od.orderNumber
LEFT JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerNumber, c.customerName, c.creditLimitA
HAVING amountOwed > 0
ORDER BY amountOwed DESC;

-- Simplified version showing only customers with outstanding balance
SELECT 
    c.customerNumber,
    c.customerName,
    c.creditLimit,
    ROUND(COALESCE(orderTotals.totalOrders, 0), 2) AS totalOrderValue,
    ROUND(COALESCE(paymentTotals.totalPayments, 0), 2) AS totalPayments,
    ROUND(COALESCE(orderTotals.totalOrders, 0) - COALESCE(paymentTotals.totalPayments, 0), 2) AS amountOwed,
    ROUND(c.creditLimit - (COALESCE(orderTotals.totalOrders, 0) - COALESCE(paymentTotals.totalPayments, 0)), 2) AS remainingCredit
FROM customers c
LEFT JOIN (
    SELECT 
        o.customerNumber,
        SUM(od.quantityOrdered * od.priceEach) AS totalOrders
    FROM orders o
    INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
    GROUP BY o.customerNumber
) AS orderTotals ON c.customerNumber = orderTotals.customerNumber
LEFT JOIN (
    SELECT 
        customerNumber,
        SUM(amount) AS totalPayments
    FROM payments
    GROUP BY customerNumber
) AS paymentTotals ON c.customerNumber = paymentTotals.customerNumber
WHERE COALESCE(orderTotals.totalOrders, 0) - COALESCE(paymentTotals.totalPayments, 0) > 0
ORDER BY amountOwed DESC;

-- =====================================================
-- BONUS QUERIES: Additional useful queries from the lecture
-- =====================================================

-- Self-join example: Find employees and their managers
SELECT 
    CONCAT(e.firstName, ' ', e.lastName) AS employee,
    e.jobTitle AS employeeTitle,
    CONCAT(m.firstName, ' ', m.lastName) AS manager,
    m.jobTitle AS managerTitle
FROM employees e
LEFT JOIN employees m ON e.reportsTo = m.employeeNumber
ORDER BY manager, employee;

-- Window function example: Sales ranking by employee and year
SELECT 
    fiscal_year,
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) AS total_year_sales,
    RANK() OVER (PARTITION BY fiscal_year ORDER BY sale DESC) AS sales_rank
FROM (
    SELECT 
        YEAR(o.orderDate) AS fiscal_year,
        CONCAT(e.firstName, ' ', e.lastName) AS sales_employee,
        SUM(od.quantityOrdered * od.priceEach) AS sale
    FROM orders o
    INNER JOIN orderdetails od ON o.orderNumber = od.orderNumber
    INNER JOIN customers c ON o.customerNumber = c.customerNumber
    INNER JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
    GROUP BY YEAR(o.orderDate), e.employeeNumber, e.firstName, e.lastName
) AS sales_data
ORDER BY fiscal_year, sales_rank;