File size: 4,007 Bytes
1bd1563 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | -- Enterprise AI Assistant - E-commerce Database Schema
CREATE TABLE IF NOT EXISTS customers (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT,
address TEXT,
city TEXT,
state TEXT,
zip_code TEXT,
country TEXT DEFAULT 'US',
segment TEXT CHECK(segment IN ('Consumer', 'Corporate', 'Enterprise')),
lifetime_value REAL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
category TEXT NOT NULL,
subcategory TEXT,
brand TEXT,
price REAL NOT NULL,
cost REAL NOT NULL,
stock_quantity INTEGER DEFAULT 0,
rating REAL DEFAULT 0,
review_count INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
order_date TIMESTAMP NOT NULL,
status TEXT CHECK(status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled', 'Returned')),
total_amount REAL NOT NULL,
discount_amount REAL DEFAULT 0,
shipping_cost REAL DEFAULT 0,
payment_method TEXT CHECK(payment_method IN ('Credit Card', 'Debit Card', 'PayPal', 'Bank Transfer', 'Gift Card')),
shipping_address TEXT,
tracking_number TEXT
);
CREATE TABLE IF NOT EXISTS order_items (
item_id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER NOT NULL REFERENCES orders(order_id),
product_id INTEGER NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
discount_percent REAL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS reviews (
review_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INTEGER NOT NULL REFERENCES products(product_id),
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
rating INTEGER CHECK(rating BETWEEN 1 AND 5),
title TEXT,
body TEXT,
helpful_votes INTEGER DEFAULT 0,
verified_purchase BOOLEAN DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS inventory_log (
log_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INTEGER NOT NULL REFERENCES products(product_id),
change_type TEXT CHECK(change_type IN ('Restock', 'Sale', 'Return', 'Adjustment', 'Damaged')),
quantity_change INTEGER NOT NULL,
previous_stock INTEGER NOT NULL,
new_stock INTEGER NOT NULL,
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS cost_tracking (
tracking_id INTEGER PRIMARY KEY AUTOINCREMENT,
request_id TEXT UNIQUE NOT NULL,
query TEXT NOT NULL,
model_name TEXT NOT NULL,
prompt_tokens INTEGER NOT NULL,
completion_tokens INTEGER NOT NULL,
total_tokens INTEGER NOT NULL,
estimated_cost_usd REAL NOT NULL,
latency_ms REAL,
tools_used TEXT,
guardrail_flags TEXT,
success BOOLEAN DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
CREATE INDEX IF NOT EXISTS idx_orders_date ON orders(order_date);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id);
CREATE INDEX IF NOT EXISTS idx_reviews_product ON reviews(product_id);
CREATE INDEX IF NOT EXISTS idx_reviews_customer ON reviews(customer_id);
CREATE INDEX IF NOT EXISTS idx_inventory_product ON inventory_log(product_id);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_customers_segment ON customers(segment);
CREATE INDEX IF NOT EXISTS idx_cost_tracking_date ON cost_tracking(created_at);
|