-- Database Schema Setup

-- Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(100),
    State VARCHAR(100),
    Country VARCHAR(100),
    ZipCode VARCHAR(20)
);

-- Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    Category VARCHAR(100),
    Price DECIMAL(10, 2)
);

-- Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- OrderDetails Table
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert Sample Data
-- (Add data using INSERT statements here)

-- Challenge 1: Total Sales per Customer
SELECT C.CustomerName, SUM(O.TotalAmount) AS TotalSpent
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerName;

-- Challenge 2: Top-Selling Products
SELECT P.ProductName, SUM(OD.Quantity) AS TotalSold
FROM Products P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
GROUP BY P.ProductName
ORDER BY TotalSold DESC
LIMIT 3;

-- Challenge 3: Monthly Sales Summary
SELECT MONTH(OrderDate) AS Month, SUM(TotalAmount) AS TotalSales
FROM Orders
GROUP BY MONTH(OrderDate);

-- Challenge 4: Customer Order Frequency
SELECT C.CustomerName, COUNT(O.OrderID) AS OrderCount
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerName;

-- Challenge 5: Category-wise Sales
SELECT P.Category, SUM(OD.Quantity) AS TotalProductsSold
FROM Products P
JOIN OrderDetails OD ON P.ProductID = OD.ProductID
GROUP BY P.Category;

-- Bonus Challenge: Stored Procedure to calculate total sales for a customer
CREATE PROCEDURE GetCustomerTotalSales (IN CustID INT)
BEGIN
    SELECT C.CustomerName, SUM(O.TotalAmount) AS TotalSpent
    FROM Customers C
    JOIN Orders O ON C.CustomerID = O.CustomerID
    WHERE C.CustomerID = CustID
    GROUP BY C.CustomerName;
END;