102 lines
3.5 KiB
MySQL
102 lines
3.5 KiB
MySQL
|
CREATE TABLE IF NOT EXISTS user_account (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
username TEXT NOT NULL UNIQUE,
|
||
|
|
password_hash TEXT NOT NULL,
|
||
|
|
display_name TEXT NOT NULL,
|
||
|
|
user_type TEXT NOT NULL,
|
||
|
|
status TEXT NOT NULL,
|
||
|
|
last_login_at TEXT,
|
||
|
|
created_by INTEGER,
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_permission (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
user_id INTEGER NOT NULL,
|
||
|
|
permission_code TEXT NOT NULL,
|
||
|
|
permission_name TEXT NOT NULL,
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(user_id, permission_code)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS category (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
parent_id INTEGER,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
sort_no INTEGER NOT NULL DEFAULT 0,
|
||
|
|
requires_detail_page INTEGER NOT NULL DEFAULT 1,
|
||
|
|
category_type TEXT NOT NULL DEFAULT 'PRODUCT',
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS product_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
category_id INTEGER NOT NULL,
|
||
|
|
sku TEXT NOT NULL UNIQUE,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
model_name TEXT,
|
||
|
|
status TEXT NOT NULL DEFAULT 'AVAILABLE',
|
||
|
|
wholesale_price NUMERIC NOT NULL DEFAULT 0,
|
||
|
|
stock_quantity INTEGER NOT NULL DEFAULT 0,
|
||
|
|
cover_asset_id INTEGER,
|
||
|
|
remark TEXT,
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS file_asset (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
business_type TEXT NOT NULL,
|
||
|
|
business_id INTEGER NOT NULL,
|
||
|
|
file_role TEXT NOT NULL,
|
||
|
|
file_name TEXT NOT NULL,
|
||
|
|
mime_type TEXT NOT NULL,
|
||
|
|
file_size INTEGER NOT NULL,
|
||
|
|
content_blob BLOB,
|
||
|
|
preview_blob BLOB,
|
||
|
|
sort_no INTEGER NOT NULL DEFAULT 0,
|
||
|
|
is_primary INTEGER NOT NULL DEFAULT 0,
|
||
|
|
sha256 TEXT,
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS order_record (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
order_no TEXT NOT NULL UNIQUE,
|
||
|
|
status TEXT NOT NULL DEFAULT 'PENDING',
|
||
|
|
total_quantity INTEGER NOT NULL DEFAULT 0,
|
||
|
|
total_amount NUMERIC NOT NULL DEFAULT 0,
|
||
|
|
express_no TEXT,
|
||
|
|
remark TEXT,
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
completed_at TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS order_item (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
order_id INTEGER NOT NULL,
|
||
|
|
product_id INTEGER NOT NULL,
|
||
|
|
product_name_snapshot TEXT NOT NULL,
|
||
|
|
sku_snapshot TEXT NOT NULL,
|
||
|
|
model_name_snapshot TEXT,
|
||
|
|
unit_price NUMERIC NOT NULL DEFAULT 0,
|
||
|
|
quantity INTEGER NOT NULL DEFAULT 0,
|
||
|
|
line_amount NUMERIC NOT NULL DEFAULT 0
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS order_operation_log (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
order_id INTEGER NOT NULL,
|
||
|
|
operation_type TEXT NOT NULL,
|
||
|
|
operation_content TEXT NOT NULL,
|
||
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_permission_user_id ON user_permission(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_category_parent_id ON category(parent_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_product_category_id ON product_item(category_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_file_asset_business ON file_asset(business_type, business_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_order_item_order_id ON order_item(order_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_order_log_order_id ON order_operation_log(order_id);
|