Final Project: Build a Database

-- Create the e-commerce database
CREATE DATABASE IF NOT EXISTS ecommerce;
USE ecommerce;

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories table (self-referencing for subcategories)
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(10, 2),
    shipping_address TEXT,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Order Items table (junction between orders and products)
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Reviews table
CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Insert categories
INSERT INTO categories (name, parent_id) VALUES
    ('Electronics', NULL),
    ('Clothing', NULL),
    ('Books', NULL),
    ('Laptops', 1),
    ('Smartphones', 1),
    ('Men\'s Clothing', 2),
    ('Women\'s Clothing', 2);

-- Insert users
INSERT INTO users (username, email, password_hash, full_name, address) VALUES
    ('alice', 'alice@email.com', 'hashed_pw_1', 'Alice Johnson', '123 Main St'),
    ('bob', 'bob@email.com', 'hashed_pw_2', 'Bob Smith', '456 Oak Ave'),
    ('charlie', 'charlie@email.com', 'hashed_pw_3', 'Charlie Brown', '789 Pine Rd'),
    ('diana', 'diana@email.com', 'hashed_pw_4', 'Diana Prince', '321 Elm St');

-- Insert products
INSERT INTO products (name, description, price, stock, category_id) VALUES
    ('MacBook Pro', '14-inch laptop with M3 chip', 1999.99, 50, 4),
    ('iPhone 16', 'Latest smartphone with A18 chip', 999.99, 200, 5),
    ('Dell XPS 15', '15-inch premium laptop', 1499.99, 75, 4),
    ('Cotton T-Shirt', 'Comfortable cotton t-shirt', 29.99, 500, 6),
    ('SQL Mastery Book', 'Complete guide to SQL', 49.99, 100, 3),
    ('Summer Dress', 'Floral print summer dress', 59.99, 150, 7);

-- Insert orders
INSERT INTO orders (user_id, status, total_amount, shipping_address) VALUES
    (1, 'delivered', 2029.98, '123 Main St'),
    (2, 'shipped', 999.99, '456 Oak Ave'),
    (1, 'processing', 79.98, '123 Main St'),
    (3, 'pending', 49.99, '789 Pine Rd');

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 1999.99),
    (1, 4, 1, 29.99),
    (2, 2, 1, 999.99),
    (3, 4, 2, 29.99),
    (3, 5, 1, 49.99),
    (4, 5, 1, 49.99);

-- Insert reviews
INSERT INTO reviews (user_id, product_id, rating, comment) VALUES
    (1, 1, 5, 'Amazing laptop! Fast and beautiful display.'),
    (2, 2, 4, 'Great phone, battery could be better.'),
    (1, 4, 3, 'Decent shirt, runs a bit small.'),
    (3, 5, 5, 'Best SQL book I have ever read!'),
    (2, 1, 4, 'Excellent performance but pricey.');

-- 1. Get all products with their category names
SELECT p.name AS product, p.price, c.name AS category
FROM products p
INNER JOIN categories c ON p.category_id = c.id
ORDER BY p.price DESC;

-- 2. Get each customer's total spending
SELECT 
    u.full_name,
    COUNT(DISTINCT o.id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.full_name
ORDER BY total_spent DESC;

-- 3. Get average product rating with review count
SELECT 
    p.name,
    ROUND(AVG(r.rating), 1) AS avg_rating,
    COUNT(r.id) AS review_count
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
ORDER BY avg_rating DESC;

-- 4. Find products that have never been ordered
SELECT p.name, p.price
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

-- 5. Monthly revenue report
SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    COUNT(DISTINCT o.id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status != 'cancelled'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month;

-- 6. Top-rated products (rating >= 4, at least 2 reviews)
SELECT 
    p.name,
    ROUND(AVG(r.rating), 1) AS avg_rating,
    COUNT(r.id) AS reviews
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING AVG(r.rating) >= 4 AND COUNT(r.id) >= 2;