-- FileDummy Sample SQL File
-- Database: sample_db
-- Generated: 2026-06-21

-- Drop existing tables for clean setup
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 categories table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) UNIQUE NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER DEFAULT 0,
    category_id INTEGER REFERENCES categories(id),
    is_active BOOLEAN DEFAULT true,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create customers table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    country VARCHAR(100) DEFAULT 'USA',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10, 2) DEFAULT 0,
    shipping_address TEXT
);

-- Create order_items table
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL
);

-- Insert sample data
INSERT INTO categories (name, description) VALUES
    ('Electronics', 'Electronic devices and accessories'),
    ('Office', 'Office supplies and furniture'),
    ('Books', 'Physical and digital books');

INSERT INTO products (name, sku, price, stock_quantity, category_id) VALUES
    ('Wireless Mouse', 'MOUSE-001', 29.99, 150, 1),
    ('Mechanical Keyboard', 'KEY-001', 89.99, 75, 1),
    ('USB-C Cable', 'CABLE-001', 12.99, 500, 1),
    ('Office Chair', 'CHAIR-001', 249.99, 30, 2),
    ('Notebook', 'NB-001', 4.99, 1000, 2);

INSERT INTO customers (first_name, last_name, email, phone, city) VALUES
    ('John', 'Doe', 'john.doe@example.com', '555-0101', 'New York'),
    ('Jane', 'Smith', 'jane.smith@example.com', '555-0102', 'London'),
    ('Bob', 'Johnson', 'bob.j@example.com', '555-0103', 'Tokyo');

-- Useful query examples
-- SELECT p.name, c.name AS category, p.price
-- FROM products p
-- JOIN categories c ON p.category_id = c.id
-- WHERE p.is_active = true
-- ORDER BY p.price DESC;
