-- E-commerce Database Sample
-- Contains 4 related tables with sample data for testing

-- Create customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address VARCHAR(200),
    city VARCHAR(50),
    country VARCHAR(50),
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Create categories table
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_date DATE DEFAULT CURRENT_DATE
);

-- Create products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category_id INTEGER,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    description TEXT,
    created_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Create orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Insert sample data into categories
INSERT INTO categories (category_name, description) VALUES
('Electronics', 'Electronic devices and gadgets'),
('Clothing', 'Fashion and apparel items'),
('Books', 'Books and educational materials'),
('Home & Garden', 'Home improvement and gardening supplies'),
('Sports', 'Sports equipment and accessories'),
('Beauty', 'Beauty and personal care products');

-- Insert sample data into customers
INSERT INTO customers (first_name, last_name, email, phone, address, city, country) VALUES
('John', 'Smith', 'john.smith@email.com', '+1-555-0101', '123 Main St', 'New York', 'USA'),
('Emma', 'Johnson', 'emma.johnson@email.com', '+1-555-0102', '456 Oak Ave', 'Los Angeles', 'USA'),
('Michael', 'Brown', 'michael.brown@email.com', '+1-555-0103', '789 Pine Rd', 'Chicago', 'USA'),
('Sarah', 'Davis', 'sarah.davis@email.com', '+1-555-0104', '321 Elm St', 'Houston', 'USA'),
('David', 'Wilson', 'david.wilson@email.com', '+1-555-0105', '654 Maple Dr', 'Phoenix', 'USA'),
('Lisa', 'Miller', 'lisa.miller@email.com', '+1-555-0106', '987 Cedar Ln', 'Philadelphia', 'USA'),
('James', 'Garcia', 'james.garcia@email.com', '+1-555-0107', '147 Birch St', 'San Antonio', 'USA'),
('Maria', 'Rodriguez', 'maria.rodriguez@email.com', '+1-555-0108', '258 Walnut Ave', 'San Diego', 'USA'),
('Robert', 'Martinez', 'robert.martinez@email.com', '+1-555-0109', '369 Cherry Rd', 'Dallas', 'USA'),
('Jennifer', 'Anderson', 'jennifer.anderson@email.com', '+1-555-0110', '741 Ash Dr', 'San Jose', 'USA'),
('William', 'Taylor', 'william.taylor@email.com', '+1-555-0111', '852 Poplar St', 'Austin', 'USA'),
('Jessica', 'Thomas', 'jessica.thomas@email.com', '+1-555-0112', '963 Spruce Ave', 'Jacksonville', 'USA'),
('Christopher', 'Jackson', 'christopher.jackson@email.com', '+1-555-0113', '159 Fir Ln', 'Fort Worth', 'USA'),
('Amanda', 'White', 'amanda.white@email.com', '+1-555-0114', '753 Redwood Dr', 'Columbus', 'USA'),
('Daniel', 'Harris', 'daniel.harris@email.com', '+1-555-0115', '486 Sequoia St', 'Charlotte', 'USA'),
('Ashley', 'Martin', 'ashley.martin@email.com', '+1-555-0116', '327 Magnolia Ave', 'San Francisco', 'USA'),
('Matthew', 'Thompson', 'matthew.thompson@email.com', '+1-555-0117', '918 Dogwood Rd', 'Indianapolis', 'USA'),
('Stephanie', 'Garcia', 'stephanie.garcia@email.com', '+1-555-0118', '582 Hickory Dr', 'Seattle', 'USA'),
('Joshua', 'Martinez', 'joshua.martinez@email.com', '+1-555-0119', '674 Sycamore St', 'Denver', 'USA'),
('Michelle', 'Robinson', 'michelle.robinson@email.com', '+1-555-0120', '395 Willow Ave', 'Washington', 'USA'),
('Andrew', 'Clark', 'andrew.clark@email.com', '+1-555-0121', '816 Beech Ln', 'Boston', 'USA'),
('Melissa', 'Rodriguez', 'melissa.rodriguez@email.com', '+1-555-0122', '729 Cypress Dr', 'El Paso', 'USA'),
('Kevin', 'Lewis', 'kevin.lewis@email.com', '+1-555-0123', '541 Juniper St', 'Detroit', 'USA'),
('Nicole', 'Lee', 'nicole.lee@email.com', '+1-555-0124', '683 Palm Ave', 'Nashville', 'USA'),
('Ryan', 'Walker', 'ryan.walker@email.com', '+1-555-0125', '297 Bamboo Rd', 'Portland', 'USA');

-- Insert sample data into products
INSERT INTO products (product_name, category_id, price, stock_quantity, description) VALUES
('iPhone 14 Pro', 1, 999.99, 50, 'Latest Apple smartphone with advanced features'),
('Samsung Galaxy S23', 1, 899.99, 45, 'Premium Android smartphone'),
('MacBook Air M2', 1, 1199.99, 30, 'Lightweight laptop with M2 chip'),
('Dell XPS 13', 1, 1099.99, 25, 'Ultra-portable Windows laptop'),
('Sony WH-1000XM4', 1, 349.99, 75, 'Noise-canceling wireless headphones'),
('Nike Air Max 270', 2, 129.99, 100, 'Comfortable running shoes'),
('Adidas Ultra Boost', 2, 179.99, 80, 'Premium athletic footwear'),
('Levi''s 501 Jeans', 2, 79.99, 120, 'Classic straight-leg denim jeans'),
('H&M Cotton T-Shirt', 2, 19.99, 200, 'Basic cotton t-shirt in various colors'),
('Zara Blazer', 2, 89.99, 60, 'Professional business blazer'),
('The Great Gatsby', 3, 14.99, 150, 'Classic American literature novel'),
('To Kill a Mockingbird', 3, 12.99, 130, 'Pulitzer Prize-winning novel'),
('Python Programming Guide', 3, 49.99, 90, 'Comprehensive Python programming book'),
('JavaScript: The Good Parts', 3, 39.99, 85, 'Essential JavaScript programming guide'),
('Data Science Handbook', 3, 59.99, 70, 'Complete guide to data science'),
('Garden Tool Set', 4, 79.99, 40, 'Complete set of essential gardening tools'),
('Indoor Plant Collection', 4, 129.99, 35, 'Set of 5 popular houseplants'),
('Outdoor Furniture Set', 4, 899.99, 15, '4-piece patio furniture collection'),
('Kitchen Appliance Bundle', 4, 299.99, 25, 'Blender, toaster, and coffee maker set'),
('Home Decor Package', 4, 159.99, 45, 'Decorative items for modern homes'),
('Tennis Racket Pro', 5, 199.99, 30, 'Professional-grade tennis racket'),
('Basketball Official Size', 5, 29.99, 80, 'Official size and weight basketball'),
('Yoga Mat Premium', 5, 49.99, 100, 'Non-slip premium yoga mat'),
('Dumbbells Set 20kg', 5, 149.99, 20, 'Adjustable dumbbell set'),
('Running Shoes Trail', 5, 119.99, 65, 'All-terrain trail running shoes'),
('Skincare Routine Kit', 6, 89.99, 55, 'Complete 5-step skincare routine'),
('Makeup Palette Pro', 6, 79.99, 70, 'Professional makeup palette'),
('Hair Care Bundle', 6, 59.99, 85, 'Shampoo, conditioner, and treatment'),
('Perfume Collection', 6, 199.99, 40, 'Set of 3 popular fragrances'),
('Nail Care Kit', 6, 39.99, 95, 'Complete nail care and polish set');

-- Insert sample data into orders
INSERT INTO orders (customer_id, product_id, quantity, unit_price, total_amount, status) VALUES
(1, 1, 1, 999.99, 999.99, 'completed'),
(2, 5, 1, 349.99, 349.99, 'completed'),
(3, 6, 2, 129.99, 259.98, 'shipped'),
(4, 11, 3, 14.99, 44.97, 'completed'),
(5, 16, 1, 79.99, 79.99, 'pending'),
(1, 21, 1, 199.99, 199.99, 'completed'),
(6, 2, 1, 899.99, 899.99, 'shipped'),
(7, 8, 2, 79.99, 159.98, 'completed'),
(8, 13, 1, 49.99, 49.99, 'completed'),
(9, 18, 1, 899.99, 899.99, 'pending'),
(10, 3, 1, 1199.99, 1199.99, 'completed'),
(2, 9, 5, 19.99, 99.95, 'completed'),
(11, 22, 2, 29.99, 59.98, 'shipped'),
(12, 26, 1, 89.99, 89.99, 'completed'),
(13, 4, 1, 1099.99, 1099.99, 'completed'),
(14, 7, 1, 179.99, 179.99, 'shipped'),
(15, 12, 2, 12.99, 25.98, 'completed'),
(3, 23, 1, 49.99, 49.99, 'completed'),
(16, 27, 1, 79.99, 79.99, 'pending'),
(17, 10, 1, 89.99, 89.99, 'completed'),
(18, 15, 1, 59.99, 59.99, 'shipped'),
(4, 20, 1, 159.99, 159.99, 'completed'),
(19, 24, 1, 149.99, 149.99, 'pending'),
(20, 28, 2, 59.99, 119.98, 'completed'),
(21, 14, 1, 39.99, 39.99, 'shipped'),
(5, 25, 1, 119.99, 119.99, 'completed'),
(22, 29, 1, 199.99, 199.99, 'pending'),
(23, 17, 1, 129.99, 129.99, 'completed'),
(24, 30, 2, 39.99, 79.98, 'shipped'),
(25, 19, 1, 299.99, 299.99, 'completed');
