131 lines
4.6 KiB
SQL
131 lines
4.6 KiB
SQL
-- 创建用户表
|
|
CREATE TABLE IF NOT EXISTS cf_user (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
role VARCHAR(50),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建商品表
|
|
CREATE TABLE IF NOT EXISTS cf_product (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
shop_id VARCHAR(255),
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
main_image VARCHAR(255),
|
|
platform VARCHAR(50),
|
|
platform_product_id VARCHAR(255),
|
|
price DECIMAL(10,2),
|
|
cost_price DECIMAL(10,2),
|
|
quantity INT,
|
|
status VARCHAR(50),
|
|
phash VARCHAR(255),
|
|
semantic_hash VARCHAR(255),
|
|
vector_embedding TEXT,
|
|
attributes JSON,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建订单表
|
|
CREATE TABLE IF NOT EXISTS cf_order (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
shop_id VARCHAR(255),
|
|
platform VARCHAR(50),
|
|
platform_order_id VARCHAR(255),
|
|
status VARCHAR(50),
|
|
total_amount DECIMAL(10,2),
|
|
currency VARCHAR(10),
|
|
customer_info JSON,
|
|
items JSON,
|
|
shipping_address JSON,
|
|
tracking_number VARCHAR(255),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建支付表
|
|
CREATE TABLE IF NOT EXISTS cf_payment (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
order_id BIGINT,
|
|
payment_method VARCHAR(50),
|
|
amount DECIMAL(10,2),
|
|
currency VARCHAR(10),
|
|
status VARCHAR(50),
|
|
transaction_id VARCHAR(255),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (order_id) REFERENCES cf_order(id)
|
|
);
|
|
|
|
-- 创建物流表
|
|
CREATE TABLE IF NOT EXISTS cf_logistics (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255) NOT NULL,
|
|
order_id BIGINT,
|
|
shipping_method VARCHAR(50),
|
|
tracking_number VARCHAR(255),
|
|
carrier VARCHAR(50),
|
|
status VARCHAR(50),
|
|
estimated_delivery_date DATETIME,
|
|
actual_delivery_date DATETIME,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (order_id) REFERENCES cf_order(id)
|
|
);
|
|
|
|
-- 创建配置表
|
|
CREATE TABLE IF NOT EXISTS cf_config (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255),
|
|
shop_id VARCHAR(255),
|
|
config_key VARCHAR(255) NOT NULL,
|
|
config_value VARCHAR(255) NOT NULL,
|
|
config_type VARCHAR(50),
|
|
description TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建审计表
|
|
CREATE TABLE IF NOT EXISTS cf_audit (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id VARCHAR(255),
|
|
shop_id VARCHAR(255),
|
|
user_id BIGINT,
|
|
action VARCHAR(255),
|
|
resource_type VARCHAR(255),
|
|
resource_id VARCHAR(255),
|
|
ip_address VARCHAR(100),
|
|
user_agent TEXT,
|
|
details TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- 创建索引
|
|
CREATE INDEX IF NOT EXISTS idx_user_tenant_id ON cf_user(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_product_tenant_id ON cf_product(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_product_platform ON cf_product(platform);
|
|
CREATE INDEX IF NOT EXISTS idx_order_tenant_id ON cf_order(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_order_platform ON cf_order(platform);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_tenant_id ON cf_payment(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_payment_order_id ON cf_payment(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_logistics_tenant_id ON cf_logistics(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_logistics_order_id ON cf_logistics(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_config_tenant_id ON cf_config(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_config_shop_id ON cf_config(shop_id);
|
|
CREATE INDEX IF NOT EXISTS idx_config_key ON cf_config(config_key);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_tenant_id ON cf_audit(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_shop_id ON cf_audit(shop_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_user_id ON cf_audit(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_action ON cf_audit(action);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_resource_type ON cf_audit(resource_type);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_created_at ON cf_audit(created_at);
|