123 lines
5.7 KiB
SQL
Executable File
123 lines
5.7 KiB
SQL
Executable File
-- 数据库初始化脚本
|
|
-- 创建数据库
|
|
CREATE DATABASE IF NOT EXISTS redemption_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE redemption_system;
|
|
|
|
-- 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
|
|
disabled TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0=启用 1=禁用',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- 插入默认管理员 (密码: admin123)
|
|
INSERT INTO users (username, password, role) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');
|
|
|
|
-- 邀请码表
|
|
CREATE TABLE IF NOT EXISTS invite_codes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
used_by INT DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- 插入默认邀请码
|
|
INSERT INTO invite_codes (code) VALUES ('DEFAULT2024');
|
|
|
|
-- 产品表
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL COMMENT '产品ID',
|
|
name VARCHAR(100) NOT NULL,
|
|
status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
|
|
api_url VARCHAR(500) DEFAULT NULL COMMENT '接口地址',
|
|
token VARCHAR(500) DEFAULT NULL COMMENT '接口认证Token',
|
|
remark VARCHAR(255) DEFAULT NULL COMMENT '备注',
|
|
updated_at DATETIME DEFAULT NULL,
|
|
updated_by INT DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
INSERT INTO products (code, name) VALUES ('default', '默认产品');
|
|
|
|
-- 用户-产品关联表
|
|
CREATE TABLE IF NOT EXISTS user_products (
|
|
user_id INT NOT NULL,
|
|
product_id INT NOT NULL,
|
|
PRIMARY KEY (user_id, product_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- 管理员关联到默认产品
|
|
INSERT INTO user_products (user_id, product_id)
|
|
SELECT u.id, p.id FROM users u, products p
|
|
WHERE u.role = 'admin' AND p.code = 'default'
|
|
AND NOT EXISTS (SELECT 1 FROM user_products up WHERE up.user_id = u.id AND up.product_id = p.id);
|
|
|
|
-- 兑换码表
|
|
CREATE TABLE IF NOT EXISTS redemption_codes (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
product_id INT DEFAULT NULL COMMENT '所属产品',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
user_id INT DEFAULT NULL COMMENT '创建人(管理员ID)',
|
|
name VARCHAR(100) NOT NULL COMMENT '兑换码名称',
|
|
batch_no VARCHAR(100) NOT NULL COMMENT '批次号',
|
|
type INT NOT NULL DEFAULT 1 COMMENT '兑换码类型',
|
|
code VARCHAR(200) NOT NULL COMMENT '兑换码',
|
|
value DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '兑换码面值',
|
|
status TINYINT NOT NULL DEFAULT 1 COMMENT '1未领取 2已领取',
|
|
expired_at DATETIME DEFAULT NULL COMMENT '过期时间',
|
|
claim_user_id INT DEFAULT NULL COMMENT '领取人ID',
|
|
claimed_at DATETIME DEFAULT NULL COMMENT '领取时间',
|
|
price_tier1 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价一档',
|
|
price_tier2 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价二挡',
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (claim_user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- 领取记录表
|
|
CREATE TABLE IF NOT EXISTS claim_records (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
product_id INT DEFAULT NULL COMMENT '所属产品',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
user_id INT DEFAULT NULL COMMENT '用户ID',
|
|
code_name VARCHAR(100) NOT NULL COMMENT '兑换码名称',
|
|
batch_no VARCHAR(100) NOT NULL COMMENT '批次号',
|
|
code_type INT NOT NULL DEFAULT 1 COMMENT '兑换码类型',
|
|
code VARCHAR(200) NOT NULL COMMENT '兑换码',
|
|
value DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '兑换码面值',
|
|
status TINYINT NOT NULL DEFAULT 1 COMMENT '1未使用 2已使用 3过期',
|
|
expired_at DATETIME DEFAULT NULL COMMENT '过期时间',
|
|
used_at DATETIME DEFAULT NULL COMMENT '使用时间',
|
|
updated_at DATETIME DEFAULT NULL COMMENT '更新时间',
|
|
price_tier1 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价一档',
|
|
price_tier2 DECIMAL(10,2) DEFAULT NULL COMMENT '出货价二挡',
|
|
claim_user VARCHAR(50) DEFAULT NULL COMMENT '领取人(用户名)',
|
|
claimed_at DATETIME DEFAULT NULL COMMENT '领取时间',
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- 工单表
|
|
CREATE TABLE IF NOT EXISTS work_orders (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
product_id INT DEFAULT NULL COMMENT '所属产品',
|
|
content TEXT NOT NULL COMMENT '工单内容',
|
|
code VARCHAR(200) DEFAULT NULL COMMENT '关联兑换码',
|
|
attachment VARCHAR(500) DEFAULT NULL COMMENT '附件路径',
|
|
creator_id INT DEFAULT NULL COMMENT '发起人ID',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发起时间',
|
|
status ENUM('未处理', '已处理', '已驳回') NOT NULL DEFAULT '未处理' COMMENT '状态',
|
|
processed_at DATETIME DEFAULT NULL COMMENT '处理时间',
|
|
processor_id INT DEFAULT NULL COMMENT '处理人ID',
|
|
FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (processor_id) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE UNIQUE INDEX idx_code ON redemption_codes(code);
|