CREATE TABLE customers (

customer_id SERIAL PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

phone VARCHAR(20),

address TEXT,

city VARCHAR(50),

state VARCHAR(50),

zip_code VARCHAR(10),

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE categories (

category_id SERIAL PRIMARY KEY,

category_name VARCHAR(100) NOT NULL,

description TEXT

);

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

product_name VARCHAR(200) NOT NULL,

description TEXT,

price DECIMAL(10,2) NOT NULL,

stock_quantity INTEGER NOT NULL DEFAULT 0,

category_id INTEGER REFERENCES categories(category_id),

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

customer_id INTEGER REFERENCES customers(customer_id),

order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

total_amount DECIMAL(10,2) NOT NULL,

status VARCHAR(20) DEFAULT 'pending',

shipping_address TEXT

);

CREATE TABLE order_items (

order_item_id SERIAL PRIMARY KEY,

order_id INTEGER REFERENCES orders(order_id),

product_id INTEGER REFERENCES products(product_id),

quantity INTEGER NOT NULL,

unit_price DECIMAL(10,2) NOT NULL

);

INSERT INTO categories (category_name, description) VALUES

('Electronics', 'Electronic devices and accessories'),

('Clothing', 'Apparel and fashion items'),

('Books', 'Books and educational materials'),

('Home & Garden', 'Home improvement and garden supplies');

INSERT INTO customers (first_name, last_name, email, phone, address, city, state, zip_code) VALUES

('John', 'Doe', 'john.doe@email.com', '555-0101', '123 Main St', 'New York', 'NY', '10001'),

('Jane', 'Smith', 'jane.smith@email.com', '555-0102', '456 Oak Ave', 'Los Angeles', 'CA', '90210'),

('Bob', 'Johnson', 'bob.johnson@email.com', '555-0103', '789 Pine Rd', 'Chicago', 'IL', '60601'),

('Alice', 'Brown', 'alice.brown@email.com', '555-0104', '321 Elm St', 'Houston', 'TX', '77001');

INSERT INTO products (product_name, description, price, stock_quantity, category_id) VALUES

('Laptop', 'High-performance laptop computer', 999.99, 50, 1),

('Smartphone', 'Latest model smartphone', 699.99, 100, 1),

('T-Shirt', 'Cotton t-shirt', 19.99, 200, 2),

('Jeans', 'Denim jeans', 49.99, 150, 2),

('Programming Book', 'Learn programming fundamentals', 39.99, 75, 3),

('Garden Tools Set', 'Complete gardening tool kit', 89.99, 30, 4);

INSERT INTO orders (customer_id, total_amount, status, shipping_address) VALUES

(1, 1019.98, 'completed', '123 Main St, New York, NY 10001'),

(2, 699.99, 'shipped', '456 Oak Ave, Los Angeles, CA 90210'),

(3, 69.98, 'pending', '789 Pine Rd, Chicago, IL 60601'),

(1, 89.99, 'completed', '123 Main St, New York, NY 10001');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES

(1, 1, 1, 999.99),

(1, 3, 1, 19.99),

(2, 2, 1, 699.99),

(3, 3, 2, 19.99),

(3, 5, 1, 39.99),

(4, 6, 1, 89.99);

CREATE INDEX idx_customers_email ON customers(email);

CREATE INDEX idx_products_category ON products(category_id);

CREATE INDEX idx_orders_customer ON orders(customer_id);

CREATE INDEX idx_order_items_order ON order_items(order_id);

CREATE INDEX idx_order_items_product ON order_items(product_id);

CREATE VIEW order_summary AS

SELECT

o.order_id,

c.first_name || ' ' || c.last_name AS customer_name,

o.order_date,

o.total_amount,

o.status,

COUNT(oi.order_item_id) AS total_items

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

LEFT JOIN order_items oi ON o.order_id = oi.order_id

GROUP BY o.order_id, c.first_name, c.last_name, o.order_date, o.total_amount, o.status;