sample_database.sql

-- Sample Business Database for BizAgent Testing
-- This creates a realistic e-commerce business scenario

-- Customers table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'USA',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_purchase TIMESTAMP,
    total_spent DECIMAL(10,2) DEFAULT 0,
    customer_type VARCHAR(20) DEFAULT 'regular' -- regular, vip, wholesale
);

-- Products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    price DECIMAL(10,2) NOT NULL,
    cost DECIMAL(10,2),
    stock_quantity INTEGER DEFAULT 0,
    sku VARCHAR(50) UNIQUE,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 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', -- pending, processing, shipped, delivered, cancelled
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_amount DECIMAL(10,2) DEFAULT 0,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    payment_method VARCHAR(50),
    shipping_address TEXT,
    notes TEXT
);

-- Order items table (junction table for orders and products)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL
);

-- Categories table (for product organization)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id INTEGER REFERENCES categories(id)
);

-- Insert sample customers
INSERT INTO customers (name, email, phone, address, city, state, zip_code, created_at, last_purchase, total_spent, customer_type) VALUES
('Acme Corporation', 'contact@acme.com', '+1-555-0101', '123 Business St', 'New York', 'NY', '10001', '2025-01-15 10:00:00', '2026-03-05 14:30:00', 12500.00, 'vip'),
('Tech Startup Inc', 'hello@techstartup.com', '+1-555-0102', '456 Innovation Ave', 'San Francisco', 'CA', '94105', '2025-02-20 09:15:00', '2026-03-07 11:45:00', 8750.00, 'regular'),
('Local Coffee Shop', 'owner@localcoffee.com', '+1-555-0103', '789 Main St', 'Austin', 'TX', '78701', '2025-03-01 16:20:00', '2026-03-08 08:30:00', 3200.00, 'regular'),
('Global Enterprises', 'info@globalent.com', '+1-555-0104', '321 Corporate Blvd', 'Chicago', 'IL', '60601', '2025-01-10 12:00:00', '2026-03-06 13:15:00', 18900.00, 'wholesale'),
('Small Business LLC', 'admin@smallbiz.com', '+1-555-0105', '654 Commerce Dr', 'Seattle', 'WA', '98101', '2025-02-14 14:45:00', '2026-03-04 10:20:00', 5600.00, 'regular'),
('Retail Chain Corp', 'orders@retailchain.com', '+1-555-0106', '987 Shopping Center', 'Los Angeles', 'CA', '90210', '2025-01-25 11:30:00', '2026-03-08 15:00:00', 15200.00, 'vip'),
('Startup Ventures', 'team@startupventures.com', '+1-555-0107', '147 Tech Park', 'Boston', 'MA', '02101', '2025-03-05 13:00:00', '2026-03-07 16:45:00', 4200.00, 'regular'),
('Manufacturing Inc', 'procurement@manufacturing.com', '+1-555-0108', '258 Industrial Way', 'Detroit', 'MI', '48201', '2025-02-01 08:00:00', '2026-03-03 12:30:00', 23100.00, 'wholesale'),
('Consulting Group', 'billing@consulting.com', '+1-555-0109', '369 Office Plaza', 'Denver', 'CO', '80202', '2025-01-30 15:15:00', '2026-03-02 09:45:00', 7800.00, 'regular'),
('E-commerce Store', 'support@ecommerce.com', '+1-555-0110', '741 Online Ave', 'Miami', 'FL', '33101', '2025-03-10 10:30:00', '2026-03-08 14:00:00', 9600.00, 'vip');

-- Insert sample categories
INSERT INTO categories (name, description) VALUES
('Electronics', 'Electronic devices and accessories'),
('Clothing', 'Apparel and fashion items'),
('Home & Garden', 'Home improvement and garden supplies'),
('Books', 'Books and educational materials'),
('Sports', 'Sports equipment and apparel'),
('Beauty', 'Beauty and personal care products'),
('Automotive', 'Car parts and accessories'),
('Toys', 'Toys and games for children'),
('Food', 'Food and beverage products'),
('Office Supplies', 'Office and stationery items');

-- Insert sample products
INSERT INTO products (name, description, category, price, cost, stock_quantity, sku, created_at) VALUES
('Wireless Headphones', 'High-quality wireless headphones with noise cancellation', 'Electronics', 199.99, 120.00, 50, 'WH-001', '2025-01-01 00:00:00'),
('Laptop Computer', '15-inch laptop with 16GB RAM and 512GB SSD', 'Electronics', 1299.99, 900.00, 25, 'LT-001', '2025-01-01 00:00:00'),
('Cotton T-Shirt', 'Comfortable 100% cotton t-shirt in various colors', 'Clothing', 24.99, 8.00, 100, 'TS-001', '2025-01-01 00:00:00'),
('Garden Hose', '50-foot durable garden hose with spray nozzle', 'Home & Garden', 39.99, 15.00, 75, 'GH-001', '2025-01-01 00:00:00'),
('Business Book', 'Best-selling business strategy book', 'Books', 29.99, 10.00, 200, 'BK-001', '2025-01-01 00:00:00'),
('Yoga Mat', 'Non-slip yoga mat for fitness enthusiasts', 'Sports', 49.99, 20.00, 60, 'YM-001', '2025-01-01 00:00:00'),
('Face Cream', 'Anti-aging face cream with SPF 30', 'Beauty', 79.99, 25.00, 40, 'FC-001', '2025-01-01 00:00:00'),
('Car Air Freshener', 'Long-lasting car air freshener in multiple scents', 'Automotive', 9.99, 3.00, 150, 'AF-001', '2025-01-01 00:00:00'),
('Building Blocks Set', 'Educational building blocks for children', 'Toys', 34.99, 12.00, 80, 'BB-001', '2025-01-01 00:00:00'),
('Coffee Beans', 'Premium arabica coffee beans, 1lb bag', 'Food', 19.99, 7.00, 120, 'CB-001', '2025-01-01 00:00:00'),
('Office Chair', 'Ergonomic office chair with lumbar support', 'Office Supplies', 299.99, 150.00, 30, 'OC-001', '2025-01-01 00:00:00'),
('Wireless Mouse', 'Ergonomic wireless mouse with USB receiver', 'Electronics', 49.99, 15.00, 90, 'WM-001', '2025-01-01 00:00:00'),
('Running Shoes', 'Lightweight running shoes for athletes', 'Sports', 129.99, 60.00, 45, 'RS-001', '2025-01-01 00:00:00'),
('Hair Shampoo', 'Natural hair shampoo for all hair types', 'Beauty', 14.99, 5.00, 110, 'HS-001', '2025-01-01 00:00:00'),
('Notebook Set', 'Set of 5 professional notebooks', 'Office Supplies', 24.99, 8.00, 85, 'NS-001', '2025-01-01 00:00:00');

-- Insert sample orders
INSERT INTO orders (customer_id, order_date, status, total_amount, shipping_amount, tax_amount, payment_method, shipping_address) VALUES
(1, '2026-03-01 10:30:00', 'delivered', 1299.99, 15.00, 103.99, 'credit_card', '123 Business St, New York, NY 10001'),
(2, '2026-03-02 14:20:00', 'shipped', 249.98, 10.00, 20.00, 'paypal', '456 Innovation Ave, San Francisco, CA 94105'),
(3, '2026-03-03 09:15:00', 'processing', 79.99, 5.00, 6.40, 'credit_card', '789 Main St, Austin, TX 78701'),
(4, '2026-03-04 16:45:00', 'delivered', 299.99, 20.00, 24.00, 'bank_transfer', '321 Corporate Blvd, Chicago, IL 60601'),
(5, '2026-03-05 11:30:00', 'pending', 49.99, 8.00, 4.00, 'credit_card', '654 Commerce Dr, Seattle, WA 98101'),
(6, '2026-03-06 13:00:00', 'shipped', 199.99, 12.00, 16.00, 'paypal', '987 Shopping Center, Los Angeles, CA 90210'),
(7, '2026-03-07 15:20:00', 'delivered', 34.99, 6.00, 2.80, 'credit_card', '147 Tech Park, Boston, MA 02101'),
(8, '2026-03-08 10:45:00', 'processing', 129.99, 18.00, 10.40, 'bank_transfer', '258 Industrial Way, Detroit, MI 48201'),
(9, '2026-03-02 12:15:00', 'delivered', 24.99, 4.00, 2.00, 'credit_card', '369 Office Plaza, Denver, CO 80202'),
(10, '2026-03-03 14:30:00', 'shipped', 19.99, 3.00, 1.60, 'paypal', '741 Online Ave, Miami, FL 33101'),
(1, '2026-03-05 08:00:00', 'delivered', 49.99, 7.00, 4.00, 'credit_card', '123 Business St, New York, NY 10001'),
(2, '2026-03-06 17:30:00', 'pending', 299.99, 15.00, 24.00, 'credit_card', '456 Innovation Ave, San Francisco, CA 94105'),
(3, '2026-03-07 11:00:00', 'shipped', 14.99, 2.00, 1.20, 'paypal', '789 Main St, Austin, TX 78701'),
(4, '2026-03-08 09:45:00', 'processing', 199.99, 10.00, 16.00, 'bank_transfer', '321 Corporate Blvd, Chicago, IL 60601'),
(5, '2026-03-01 16:20:00', 'delivered', 39.99, 5.00, 3.20, 'credit_card', '654 Commerce Dr, Seattle, WA 98101');

-- Insert sample order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price) VALUES
(1, 2, 1, 1299.99, 1299.99),
(2, 1, 1, 199.99, 199.99),
(2, 3, 1, 24.99, 24.99),
(2, 8, 1, 9.99, 9.99),
(3, 7, 1, 79.99, 79.99),
(4, 11, 1, 299.99, 299.99),
(5, 12, 1, 49.99, 49.99),
(6, 1, 1, 199.99, 199.99),
(7, 9, 1, 34.99, 34.99),
(8, 13, 1, 129.99, 129.99),
(9, 3, 1, 24.99, 24.99),
(10, 10, 1, 19.99, 19.99),
(11, 12, 1, 49.99, 49.99),
(12, 11, 1, 299.99, 299.99),
(13, 14, 1, 14.99, 14.99),
(14, 1, 1, 199.99, 199.99),
(15, 4, 1, 39.99, 39.99);

-- Update customer total_spent based on their orders
UPDATE customers SET total_spent = (
    SELECT COALESCE(SUM(o.total_amount), 0)
    FROM orders o
    WHERE o.customer_id = customers.id
    AND o.status != 'cancelled'
);

-- Update customer last_purchase
UPDATE customers SET last_purchase = (
    SELECT MAX(o.order_date)
    FROM orders o
    WHERE o.customer_id = customers.id
    AND o.status != 'cancelled'
);

-- Create some useful views for analysis
CREATE VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE status != 'cancelled'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;

CREATE VIEW top_products AS
SELECT
    p.name,
    p.category,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.total_price) AS total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status != 'cancelled'
GROUP BY p.id, p.name, p.category
ORDER BY total_revenue DESC;

CREATE VIEW customer_lifetime_value AS
SELECT
    c.name,
    c.email,
    c.created_at,
    c.last_purchase,
    c.total_spent,
    COUNT(o.id) AS total_orders,
    AVG(o.total_amount) AS avg_order_value,
    CASE
        WHEN c.total_spent > 10000 THEN 'High Value'
        WHEN c.total_spent > 5000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS value_segment
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status != 'cancelled'
GROUP BY c.id, c.name, c.email, c.created_at, c.last_purchase, c.total_spent
ORDER BY c.total_spent DESC;