File size: 1,692 Bytes
0b005d6 | 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 | CREATE TABLE IF NOT EXISTS products (
product_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
subcategory TEXT,
brand TEXT,
unit_price REAL NOT NULL,
cost_price REAL NOT NULL,
stock_quantity INTEGER DEFAULT 0,
supplier_country TEXT,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS customers (
customer_id TEXT PRIMARY KEY,
country TEXT NOT NULL,
region TEXT,
customer_segment TEXT,
account_age_days INTEGER DEFAULT 0,
total_lifetime_value REAL DEFAULT 0,
risk_score REAL DEFAULT 0.0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS orders (
order_id TEXT PRIMARY KEY,
customer_id TEXT REFERENCES customers(customer_id),
product_id TEXT REFERENCES products(product_id),
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL,
total_amount REAL NOT NULL,
discount_applied REAL DEFAULT 0.0,
order_status TEXT DEFAULT 'completed',
payment_method TEXT,
is_flagged INTEGER DEFAULT 0,
order_date TEXT NOT NULL,
fulfillment_days INTEGER
);
CREATE TABLE IF NOT EXISTS returns (
return_id TEXT PRIMARY KEY,
order_id TEXT REFERENCES orders(order_id),
customer_id TEXT REFERENCES customers(customer_id),
product_id TEXT REFERENCES products(product_id),
return_reason TEXT,
refund_amount REAL,
return_date TEXT
);
CREATE TABLE IF NOT EXISTS fraud_events (
event_id TEXT PRIMARY KEY,
customer_id TEXT REFERENCES customers(customer_id),
order_id TEXT REFERENCES orders(order_id),
event_type TEXT,
amount_at_risk REAL,
resolved INTEGER DEFAULT 0,
event_date TEXT
);
|