Admin
283 lines
15 KiB
SQL
283 lines
15 KiB
SQL
-- 网络验证平台数据库结构
|
|
-- 创建数据库
|
|
CREATE DATABASE IF NOT EXISTS verification_platform DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE verification_platform;
|
|
|
|
-- 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
|
|
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
|
|
password VARCHAR(255) NOT NULL COMMENT '密码',
|
|
avatar VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
|
|
signature VARCHAR(255) DEFAULT NULL COMMENT '个性签名',
|
|
points INT DEFAULT 0 COMMENT '积分',
|
|
level INT DEFAULT 1 COMMENT '等级',
|
|
role ENUM('user', 'agent', 'admin') DEFAULT 'user' COMMENT '角色',
|
|
status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
|
|
agent_id BIGINT UNSIGNED DEFAULT NULL COMMENT '所属代理ID',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_username (username),
|
|
INDEX idx_email (email),
|
|
INDEX idx_role (role),
|
|
INDEX idx_status (status),
|
|
INDEX idx_agent_id (agent_id),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
|
|
|
-- 应用表
|
|
CREATE TABLE IF NOT EXISTS applications (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '所有者ID',
|
|
name VARCHAR(100) NOT NULL COMMENT '应用名称',
|
|
description TEXT COMMENT '应用描述',
|
|
billing_type ENUM('subscription', 'time', 'point') NOT NULL COMMENT '计费方式',
|
|
encrypt_type ENUM('aes', 'rc4') NOT NULL COMMENT '加密方式',
|
|
secret_key VARCHAR(255) NOT NULL COMMENT '通信密钥',
|
|
bind_type ENUM('none', 'device', 'ip') NOT NULL COMMENT '绑定方式',
|
|
max_devices INT DEFAULT 1 COMMENT '最大设备数',
|
|
multi_open BOOLEAN DEFAULT FALSE COMMENT '允许多开',
|
|
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_name (name),
|
|
INDEX idx_billing_type (billing_type),
|
|
INDEX idx_status (status),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用表';
|
|
|
|
-- 版本表
|
|
CREATE TABLE IF NOT EXISTS versions (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
application_id BIGINT UNSIGNED NOT NULL COMMENT '应用ID',
|
|
version VARCHAR(50) NOT NULL COMMENT '版本号',
|
|
file_path VARCHAR(255) NOT NULL COMMENT '文件路径',
|
|
file_size BIGINT NOT NULL COMMENT '文件大小',
|
|
force_update BOOLEAN DEFAULT FALSE COMMENT '强制更新',
|
|
description TEXT COMMENT '版本描述',
|
|
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_application_id (application_id),
|
|
INDEX idx_version (version),
|
|
INDEX idx_status (status),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='版本表';
|
|
|
|
-- 卡密类型表
|
|
CREATE TABLE IF NOT EXISTS card_types (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '创建者ID',
|
|
name VARCHAR(100) NOT NULL COMMENT '卡密类型名称',
|
|
billing_type ENUM('subscription', 'time', 'point') NOT NULL COMMENT '计费方式',
|
|
value INT NOT NULL COMMENT '面值',
|
|
price DECIMAL(10,2) NOT NULL COMMENT '价格',
|
|
description TEXT COMMENT '描述',
|
|
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_name (name),
|
|
INDEX idx_billing_type (billing_type),
|
|
INDEX idx_status (status),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='卡密类型表';
|
|
|
|
-- 卡密表
|
|
CREATE TABLE IF NOT EXISTS cards (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
card_type_id BIGINT UNSIGNED NOT NULL COMMENT '卡密类型ID',
|
|
card_key VARCHAR(100) NOT NULL UNIQUE COMMENT '卡密',
|
|
user_id BIGINT UNSIGNED DEFAULT NULL COMMENT '使用者ID',
|
|
agent_id BIGINT UNSIGNED DEFAULT NULL COMMENT '代理ID',
|
|
status ENUM('unused', 'used', 'expired', 'banned') DEFAULT 'unused' COMMENT '状态',
|
|
used_at TIMESTAMP NULL DEFAULT NULL COMMENT '使用时间',
|
|
expire_at TIMESTAMP NULL DEFAULT NULL COMMENT '过期时间',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_card_type_id (card_type_id),
|
|
INDEX idx_card_key (card_key),
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_agent_id (agent_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_expire_at (expire_at),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (card_type_id) REFERENCES card_types(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='卡密表';
|
|
|
|
-- 代理表
|
|
CREATE TABLE IF NOT EXISTS agents (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
|
parent_id BIGINT UNSIGNED DEFAULT NULL COMMENT '上级代理ID',
|
|
level INT DEFAULT 1 COMMENT '代理级别',
|
|
commission DECIMAL(5,4) DEFAULT 0.1000 COMMENT '佣金比例',
|
|
balance DECIMAL(10,2) DEFAULT 0.00 COMMENT '余额',
|
|
status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_parent_id (parent_id),
|
|
INDEX idx_level (level),
|
|
INDEX idx_status (status),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (parent_id) REFERENCES agents(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='代理表';
|
|
|
|
-- 订单表
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
|
order_no VARCHAR(100) NOT NULL UNIQUE COMMENT '订单号',
|
|
amount DECIMAL(10,2) NOT NULL COMMENT '金额',
|
|
status ENUM('pending', 'paid', 'cancelled', 'refunded') DEFAULT 'pending' COMMENT '状态',
|
|
payment_at TIMESTAMP NULL DEFAULT NULL COMMENT '支付时间',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_order_no (order_no),
|
|
INDEX idx_status (status),
|
|
INDEX idx_payment_at (payment_at),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';
|
|
|
|
-- 日志表
|
|
CREATE TABLE IF NOT EXISTS logs (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED DEFAULT NULL COMMENT '用户ID',
|
|
action VARCHAR(100) NOT NULL COMMENT '操作',
|
|
resource VARCHAR(100) DEFAULT NULL COMMENT '资源',
|
|
details TEXT COMMENT '详情',
|
|
ip_address VARCHAR(50) DEFAULT NULL COMMENT 'IP地址',
|
|
user_agent VARCHAR(500) DEFAULT NULL COMMENT '用户代理',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_action (action),
|
|
INDEX idx_resource (resource),
|
|
INDEX idx_created_at (created_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='日志表';
|
|
|
|
-- 工单表
|
|
CREATE TABLE IF NOT EXISTS tickets (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
|
title VARCHAR(200) NOT NULL COMMENT '标题',
|
|
content TEXT NOT NULL COMMENT '内容',
|
|
type ENUM('user', 'agent', 'developer') NOT NULL COMMENT '类型',
|
|
status ENUM('open', 'processing', 'resolved', 'closed') DEFAULT 'open' COMMENT '状态',
|
|
priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal' COMMENT '优先级',
|
|
assigned_to BIGINT UNSIGNED DEFAULT NULL COMMENT '分配给的管理员ID',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_type (type),
|
|
INDEX idx_status (status),
|
|
INDEX idx_priority (priority),
|
|
INDEX idx_assigned_to (assigned_to),
|
|
INDEX idx_created_at (created_at),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工单表';
|
|
|
|
-- 用户设备表
|
|
CREATE TABLE IF NOT EXISTS user_devices (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
|
application_id BIGINT UNSIGNED NOT NULL COMMENT '应用ID',
|
|
device_id VARCHAR(100) NOT NULL COMMENT '设备ID',
|
|
device_name VARCHAR(100) DEFAULT NULL COMMENT '设备名称',
|
|
ip_address VARCHAR(50) DEFAULT NULL COMMENT 'IP地址',
|
|
last_active_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '最后活跃时间',
|
|
status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_application_id (application_id),
|
|
INDEX idx_device_id (device_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_last_active_at (last_active_at),
|
|
UNIQUE KEY uk_user_app_device (user_id, application_id, device_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户设备表';
|
|
|
|
-- 用户变量表
|
|
CREATE TABLE IF NOT EXISTS user_variables (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
|
application_id BIGINT UNSIGNED NOT NULL COMMENT '应用ID',
|
|
var_name VARCHAR(100) NOT NULL COMMENT '变量名',
|
|
var_value TEXT COMMENT '变量值',
|
|
var_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string' COMMENT '变量类型',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_application_id (application_id),
|
|
INDEX idx_var_name (var_name),
|
|
UNIQUE KEY uk_user_app_var (user_id, application_id, var_name),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户变量表';
|
|
|
|
-- 云端数据表
|
|
CREATE TABLE IF NOT EXISTS cloud_data (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
|
application_id BIGINT UNSIGNED NOT NULL COMMENT '应用ID',
|
|
data_type ENUM('constant', 'variable') NOT NULL COMMENT '数据类型',
|
|
key_name VARCHAR(100) NOT NULL COMMENT '键名',
|
|
data_value TEXT COMMENT '数据值',
|
|
description TEXT COMMENT '描述',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
deleted_at TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_application_id (application_id),
|
|
INDEX idx_data_type (data_type),
|
|
INDEX idx_key_name (key_name),
|
|
INDEX idx_deleted_at (deleted_at),
|
|
UNIQUE KEY uk_user_app_key (user_id, application_id, key_name),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='云端数据表';
|
|
|
|
-- 风控黑名单表
|
|
CREATE TABLE IF NOT EXISTS risk_blacklist (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
user_id BIGINT UNSIGNED NOT NULL COMMENT '创建者ID',
|
|
application_id BIGINT UNSIGNED DEFAULT NULL COMMENT '应用ID',
|
|
blacklist_type ENUM('ip', 'device', 'account') NOT NULL COMMENT '黑名单类型',
|
|
target_value VARCHAR(255) NOT NULL COMMENT '目标值',
|
|
reason VARCHAR(255) DEFAULT NULL COMMENT '原因',
|
|
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_application_id (application_id),
|
|
INDEX idx_blacklist_type (blacklist_type),
|
|
INDEX idx_target_value (target_value),
|
|
INDEX idx_status (status),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='风控黑名单表'; |