-- Sample data for SQL Server: Categories, Products, Orders, Order Items
-- Run against prolyticsdev3 database

-- Drop existing objects if they exist (for re-running)
IF OBJECT_ID('dbo.order_items', 'U') IS NOT NULL DROP TABLE dbo.order_items;
IF OBJECT_ID('dbo.orders', 'U') IS NOT NULL DROP TABLE dbo.orders;
IF OBJECT_ID('dbo.products', 'U') IS NOT NULL DROP TABLE dbo.products;
IF OBJECT_ID('dbo.categories', 'U') IS NOT NULL DROP TABLE dbo.categories;

-- Categories
CREATE TABLE categories (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    description NVARCHAR(255) NULL,
    created_at DATETIME2 DEFAULT GETDATE()
);

-- Products
CREATE TABLE products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    category_id INT NOT NULL REFERENCES categories(id),
    name NVARCHAR(200) NOT NULL,
    description NVARCHAR(500) NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at DATETIME2 DEFAULT GETDATE()
);

-- Orders
CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_date DATETIME2 NOT NULL,
    customer_name NVARCHAR(200) NOT NULL,
    customer_email NVARCHAR(200) NULL,
    status NVARCHAR(50) DEFAULT 'Completed',
    total_amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE()
);

-- Order Items (line items)
CREATE TABLE order_items (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id),
    product_id INT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(12,2) NOT NULL,
    created_at DATETIME2 DEFAULT GETDATE()
);

-- Insert Categories
INSERT INTO categories (name, description) VALUES
('Electronics', 'Electronic devices and gadgets'),
('Clothing', 'Apparel and fashion accessories'),
('Home & Garden', 'Home improvement and garden supplies');

-- Insert Products
INSERT INTO products (category_id, name, description, price, stock_quantity) VALUES
(1, 'Wireless Mouse', 'Ergonomic wireless mouse with USB receiver', 29.99, 150),
(1, 'Mechanical Keyboard', 'RGB backlit mechanical keyboard', 89.99, 75),
(1, 'USB-C Hub', '7-in-1 USB-C hub with HDMI and SD card', 49.99, 120),
(2, 'Cotton T-Shirt', 'Organic cotton crew neck t-shirt', 24.99, 300),
(2, 'Denim Jeans', 'Classic fit blue denim jeans', 59.99, 80),
(2, 'Winter Jacket', 'Waterproof insulated winter jacket', 129.99, 45),
(3, 'Garden Hose', '50ft expandable garden hose', 34.99, 60),
(3, 'LED Desk Lamp', 'Adjustable dimmable LED lamp', 44.99, 90);

-- Insert Orders
INSERT INTO orders (order_date, customer_name, customer_email, status, total_amount) VALUES
('2025-02-01 10:30:00', 'Alice Johnson', 'alice@example.com', 'Completed', 149.98),
('2025-02-05 14:15:00', 'Bob Smith', 'bob@example.com', 'Completed', 254.96),
('2025-02-10 09:00:00', 'Carol Williams', 'carol@example.com', 'Completed', 104.97),
('2025-02-15 16:45:00', 'David Brown', 'david@example.com', 'Shipped', 179.97),
('2025-02-20 11:20:00', 'Eve Davis', 'eve@example.com', 'Completed', 89.98);

-- Insert Order Items
INSERT INTO order_items (order_id, product_id, quantity, unit_price, line_total) VALUES
(1, 1, 2, 29.99, 59.98),
(1, 4, 2, 24.99, 49.98),
(1, 7, 1, 34.99, 34.99),
(2, 2, 1, 89.99, 89.99),
(2, 3, 2, 49.99, 99.98),
(2, 6, 1, 129.99, 129.99),
(3, 5, 1, 59.99, 59.99),
(3, 8, 1, 44.99, 44.99),
(4, 2, 1, 89.99, 89.99),
(4, 4, 2, 24.99, 49.98),
(4, 8, 1, 44.99, 44.99),
(5, 1, 2, 29.99, 59.98),
(5, 3, 1, 49.99, 49.99);

PRINT 'Sample data created: 3 categories, 8 products, 5 orders, 13 order items';
