-- =====================================================
-- EDUSHARK TRAINING - Week 2 Practice Database (SQLite)
-- E-commerce Database for SQL Learning
-- =====================================================

PRAGMA foreign_keys = ON;

-- =====================================================
-- DROP EXISTING TABLES (child first)
-- =====================================================
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS categories;

-- =====================================================
-- CREATE TABLES
-- =====================================================

-- Categories Table (FIX: AUTOINCREMENT syntax for SQLite)
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_name TEXT NOT NULL UNIQUE
);

-- Customers Table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE,
    phone      TEXT,
    city       TEXT,
    state      TEXT,
    join_date  DATE
);

-- Products Table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    category_id  INTEGER NOT NULL,
    price        NUMERIC NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Orders Table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    status      TEXT DEFAULT 'Pending',
    total_amount NUMERIC,
    shipping_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order Items Table
CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price NUMERIC NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- =====================================================
-- INSERT DATA - CATEGORIES
-- =====================================================
INSERT INTO categories (category_name) VALUES
('Electronics'),
('Clothing'),
('Home & Kitchen'),
('Sports & Fitness'),
('Books'),
('Beauty & Personal Care');

-- =====================================================
-- INSERT DATA - CUSTOMERS
-- =====================================================
INSERT INTO customers (first_name, last_name, email, phone, city, state, join_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@email.com', '9876543210', 'Mumbai', 'Maharashtra', '2023-01-15'),
('Priya', 'Patel', 'priya.patel@email.com', '9876543211', 'Delhi', 'Delhi', '2023-02-20'),
('Amit', 'Kumar', 'amit.kumar@email.com', '9876543212', 'Bangalore', 'Karnataka', '2023-03-10'),
('Sneha', 'Gupta', 'sneha.gupta@email.com', '9876543213', 'Chennai', 'Tamil Nadu', '2023-01-25'),
('Vikram', 'Singh', 'vikram.singh@email.com', '9876543214', 'Hyderabad', 'Telangana', '2023-04-05'),
('Anita', 'Desai', 'anita.desai@email.com', NULL, 'Pune', 'Maharashtra', '2023-02-14'),
('Rajesh', 'Nair', 'rajesh.nair@email.com', '9876543216', 'Kochi', 'Kerala', '2023-05-01'),
('Meera', 'Iyer', 'meera.iyer@email.com', '9876543217', 'Bangalore', 'Karnataka', '2023-03-22'),
('Suresh', 'Reddy', 'suresh.reddy@email.com', '9876543218', 'Hyderabad', 'Telangana', '2023-06-10'),
('Kavita', 'Joshi', 'kavita.joshi@email.com', NULL, 'Mumbai', 'Maharashtra', '2023-04-18'),
('Arjun', 'Menon', 'arjun.menon@email.com', '9876543220', 'Trivandrum', 'Kerala', '2023-07-05'),
('Deepa', 'Krishnan', 'deepa.krishnan@email.com', '9876543221', 'Chennai', 'Tamil Nadu', '2023-05-28'),
('Sanjay', 'Verma', 'sanjay.verma@email.com', '9876543222', 'Delhi', 'Delhi', '2023-08-12'),
('Pooja', 'Agarwal', 'pooja.agarwal@email.com', '9876543223', 'Kolkata', 'West Bengal', '2023-06-20'),
('Kiran', 'Rao', 'kiran.rao@email.com', NULL, 'Bangalore', 'Karnataka', '2023-09-01'),
('Nitin', 'Shah', 'nitin.shah@email.com', '9876543225', 'Ahmedabad', 'Gujarat', '2023-07-15'),
('Rekha', 'Pillai', 'rekha.pillai@email.com', '9876543226', 'Kochi', 'Kerala', '2023-08-25'),
('Vinod', 'Saxena', 'vinod.saxena@email.com', '9876543227', 'Lucknow', 'Uttar Pradesh', '2023-10-05'),
('Geeta', 'Choudhury', 'geeta.choudhury@email.com', '9876543228', 'Kolkata', 'West Bengal', '2023-09-18'),
('Manoj', 'Tiwari', 'manoj.tiwari@email.com', NULL, 'Patna', 'Bihar', '2023-11-01'),
('Swati', 'Bhatt', 'swati.bhatt@email.com', '9876543230', 'Ahmedabad', 'Gujarat', '2023-10-20'),
('Arun', 'Das', 'arun.das@email.com', '9876543231', 'Guwahati', 'Assam', '2023-12-05'),
('Lakshmi', 'Naidu', 'lakshmi.naidu@email.com', '9876543232', 'Visakhapatnam', 'Andhra Pradesh', '2023-11-15'),
('Prakash', 'Hegde', 'prakash.hegde@email.com', '9876543233', 'Mangalore', 'Karnataka', '2024-01-10'),
('Sunita', 'Mishra', 'sunita.mishra@email.com', NULL, 'Bhubaneswar', 'Odisha', '2024-01-25'),
('Ashok', 'Jain', 'ashok.jain@email.com', '9876543235', 'Jaipur', 'Rajasthan', '2024-02-14'),
('Vandana', 'Kapoor', 'vandana.kapoor@email.com', '9876543236', 'Chandigarh', 'Punjab', '2024-02-28'),
('Mohan', 'Lal', 'mohan.lal@email.com', '9876543237', 'Shimla', 'Himachal Pradesh', '2024-03-10'),
('Anjali', 'Srinivasan', 'anjali.srinivasan@email.com', '9876543238', 'Coimbatore', 'Tamil Nadu', '2024-03-22'),
('Ramesh', 'Kulkarni', 'ramesh.kulkarni@email.com', NULL, 'Nagpur', 'Maharashtra', '2024-04-05'),
('Divya', 'Nayak', 'divya.nayak@email.com', '9876543240', 'Mangalore', 'Karnataka', '2024-04-18'),
('Sunil', 'Patil', 'sunil.patil@email.com', '9876543241', 'Nashik', 'Maharashtra', '2024-05-01'),
('Ritu', 'Bansal', 'ritu.bansal@email.com', '9876543242', 'Ludhiana', 'Punjab', '2024-05-15'),
('Gopal', 'Menon', 'gopal.menon@email.com', '9876543243', 'Kozhikode', 'Kerala', '2024-05-28'),
('Usha', 'Rangan', 'usha.rangan@email.com', NULL, 'Madurai', 'Tamil Nadu', '2024-06-10'),
('Dinesh', 'Kamath', 'dinesh.kamath@email.com', '9876543245', 'Udupi', 'Karnataka', '2024-06-22'),
('Pallavi', 'Deshpande', 'pallavi.deshpande@email.com', '9876543246', 'Pune', 'Maharashtra', '2024-07-05'),
('Harish', 'Sethi', 'harish.sethi@email.com', '9876543247', 'Amritsar', 'Punjab', '2024-07-18'),
('Nandini', 'Bose', 'nandini.bose@email.com', '9876543248', 'Durgapur', 'West Bengal', '2024-08-01'),
('Venu', 'Gopalan', 'venu.gopalan@email.com', NULL, 'Thrissur', 'Kerala', '2024-08-15'),
('Seema', 'Malhotra', 'seema.malhotra@email.com', '9876543250', 'Noida', 'Uttar Pradesh', '2024-08-28'),
('Girish', 'Shetty', 'girish.shetty@email.com', '9876543251', 'Hubli', 'Karnataka', '2024-09-10'),
('Bharati', 'Chakraborty', 'bharati.chakraborty@email.com', '9876543252', 'Siliguri', 'West Bengal', '2024-09-22'),
('Naveen', 'Yadav', 'naveen.yadav@email.com', '9876543253', 'Gurgaon', 'Haryana', '2024-10-05'),
('Padma', 'Subramanian', 'padma.subramanian@email.com', NULL, 'Salem', 'Tamil Nadu', '2024-10-18'),
('Kishore', 'Thakur', 'kishore.thakur@email.com', '9876543255', 'Indore', 'Madhya Pradesh', '2024-11-01'),
('Asha', 'Raghavan', 'asha.raghavan@email.com', '9876543256', 'Mysore', 'Karnataka', '2024-11-15'),
('Tarun', 'Khanna', 'tarun.khanna@email.com', '9876543257', 'Faridabad', 'Haryana', '2024-11-28'),
('Gayatri', 'Pandey', 'gayatri.pandey@email.com', '9876543258', 'Varanasi', 'Uttar Pradesh', '2024-12-10'),
('Mahesh', 'Babu', 'mahesh.babu@email.com', NULL, 'Vijayawada', 'Andhra Pradesh', '2024-12-22');

-- =====================================================
-- INSERT DATA - PRODUCTS
-- =====================================================
INSERT INTO products (product_name, category_id, price, stock_quantity) VALUES
('Samsung Galaxy S24', 1, 74999.00, 50),
('iPhone 15 Pro', 1, 129900.00, 30),
('OnePlus 12', 1, 64999.00, 45),
('Sony WH-1000XM5 Headphones', 1, 29990.00, 60),
('Apple MacBook Air M3', 1, 114900.00, 25),
('Dell XPS 15 Laptop', 1, 149990.00, 20),
('Samsung 55" Smart TV', 1, 54990.00, 35),
('Apple iPad Pro 12.9"', 1, 112900.00, 28),
('JBL Flip 6 Speaker', 1, 12999.00, 80),
('Logitech MX Master 3S Mouse', 1, 9995.00, 100),

('Levi''s Men''s Jeans', 2, 2999.00, 150),
('Nike Air Max Shoes', 2, 12995.00, 75),
('Adidas Originals T-Shirt', 2, 1999.00, 200),
('Allen Solly Formal Shirt', 2, 1799.00, 180),
('Van Heusen Blazer', 2, 5999.00, 60),
('Puma Running Shorts', 2, 1499.00, 120),
('Raymond Suit', 2, 15999.00, 40),
('Woodland Leather Boots', 2, 4999.00, 85),
('FabIndia Kurta Set', 2, 2499.00, 90),
('W Women''s Dress', 2, 1899.00, 110),

('Prestige Pressure Cooker 5L', 3, 2499.00, 100),
('Philips Air Fryer', 3, 8999.00, 55),
('Butterfly Mixer Grinder', 3, 3999.00, 70),
('Pigeon Induction Cooktop', 3, 2199.00, 90),
('Milton Water Bottle Set', 3, 799.00, 200),
('Borosil Casserole Set', 3, 1499.00, 120),
('Lifelong Vacuum Cleaner', 3, 4999.00, 45),
('Havells Table Fan', 3, 1999.00, 80),
('Prestige Electric Kettle', 3, 999.00, 150),
('Cello Opalware Dinner Set', 3, 2999.00, 60),

('Yonex Badminton Racket', 4, 3499.00, 70),
('SG Cricket Bat', 4, 4999.00, 50),
('Nivia Football', 4, 899.00, 100),
('Fitness Equipment Dumbbell Set', 4, 5999.00, 40),
('Yoga Mat Premium', 4, 1299.00, 120),
('Fitbit Charge 6', 4, 14999.00, 55),
('Resistance Bands Set', 4, 799.00, 150),
('Skipping Rope Pro', 4, 499.00, 200),
('Kettlebell 10kg', 4, 2499.00, 60),
('TRX Suspension Trainer', 4, 8999.00, 30),

('The Psychology of Money', 5, 399.00, 300),
('Atomic Habits', 5, 499.00, 250),
('Rich Dad Poor Dad', 5, 350.00, 280),
('Sapiens', 5, 599.00, 200),
('The Alchemist', 5, 299.00, 350),

('Lakme Absolute Foundation', 6, 899.00, 100),
('Maybelline Mascara', 6, 499.00, 150),
('Nivea Men''s Face Wash', 6, 249.00, 200),
('L''Oreal Paris Shampoo', 6, 399.00, 180),
('Biotique Bio Aloe Vera Gel', 6, 199.00, 250);

-- =====================================================
-- INSERT DATA - ORDERS
-- (Your inserts are fine for SQLite as-is)
-- =====================================================
INSERT INTO orders (customer_id, order_date, status, total_amount, shipping_date) VALUES
(1, '2024-01-05', 'Delivered', 74999.00, '2024-01-10'),
(2, '2024-01-08', 'Delivered', 5998.00, '2024-01-12'),
(3, '2024-01-10', 'Delivered', 29990.00, '2024-01-15'),
(4, '2024-01-12', 'Delivered', 2999.00, '2024-01-16'),
(5, '2024-01-15', 'Delivered', 12995.00, '2024-01-20'),
(6, '2024-01-18', 'Delivered', 8999.00, '2024-01-23'),
(7, '2024-01-20', 'Delivered', 1999.00, '2024-01-24'),
(8, '2024-01-22', 'Delivered', 54990.00, '2024-01-28'),
(9, '2024-01-25', 'Delivered', 3499.00, '2024-01-29'),
(10, '2024-01-28', 'Delivered', 4999.00, '2024-02-02');
-- NOTE: keep the rest of your orders insert block exactly the same (too long to repeat here)

-- =====================================================
-- INSERT DATA - ORDER ITEMS
-- =====================================================
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 74999.00),
(2, 11, 2, 2999.00);
-- NOTE: keep the rest of your order_items insert block exactly the same

-- =====================================================
-- VERIFY DATA
-- =====================================================
SELECT 'Database setup complete!' AS status;

SELECT 'categories' AS table_name, COUNT(*) AS row_count FROM categories
UNION ALL
SELECT 'customers', COUNT(*) FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;
