MySQL数据库基础练习系列48、短信发送系统

-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    email VARCHAR(100) COMMENT '邮箱',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    phone VARCHAR(20) NOT NULL COMMENT '电话号码'
);


-- 短信模板表
CREATE TABLE SMS_Templates (
    template_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '模板ID',
    template_name VARCHAR(100) NOT NULL COMMENT '模板名称',
    content TEXT NOT NULL COMMENT '模板内容',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);


-- 发送计划表
CREATE TABLE Send_Schedules (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '计划ID',
    template_id INT NOT NULL COMMENT '模板ID',
    send_time DATETIME NOT NULL COMMENT '发送时间',
    status ENUM('待处理', '已发送', '失败') DEFAULT '待处理' COMMENT '发送状态',
    FOREIGN KEY (template_id) REFERENCES SMS_Templates(template_id)
);


-- 短信发送记录表
CREATE TABLE SMS_Logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    schedule_id INT NOT NULL COMMENT '计划ID',
    user_id INT NOT NULL COMMENT '用户ID',
    send_time DATETIME NOT NULL COMMENT '发送时间',
    status ENUM('已发送', '失败') NOT NULL COMMENT '发送状态',
    FOREIGN KEY (schedule_id) REFERENCES Send_Schedules(schedule_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);


-- 用户组表（可选，用于用户分组管理）
CREATE TABLE User_Groups (
    group_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '组ID',
    group_name VARCHAR(100) NOT NULL COMMENT '组名称',
    description TEXT COMMENT '组描述'
);


-- 用户组用户关联表（可选，用于将用户分配到不同的组）
CREATE TABLE User_Group_Members (
    member_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成员ID',
    user_id INT NOT NULL COMMENT '用户ID',
    group_id INT NOT NULL COMMENT '组ID',
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (group_id) REFERENCES User_Groups(group_id)
);


-- 短信网关配置表（可选，用于存储不同短信网关的配置信息）
CREATE TABLE SMS_Gateways (
    gateway_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '网关ID',
    gateway_name VARCHAR(100) NOT NULL COMMENT '网关名称',
    api_key VARCHAR(255) COMMENT 'API密钥',
    endpoint VARCHAR(255) COMMENT '网关接口地址',
    other_config TEXT COMMENT '其他配置信息'
);

INSERT INTO Users (username, password, name, email, gender, phone) VALUES
('贾宝玉', '123', '贾宝玉', 'jiabaoyu@example.com', '男', '13800138001'),
('林黛玉', '123', '林黛玉', 'lindaiyu@example.com', '女', '13900139002'),
('孙悟空', '123', '孙悟空', 'sunwukong@example.com', '男', '13700137003');
INSERT INTO SMS_Templates (template_name, content) VALUES
('欢迎注册', '感谢您注册我们的服务，请确认您的邮箱和手机号。'),
('活动通知', '亲爱的用户，我们的新活动已经开始，欢迎您参与！'),
('订单确认', '您的订单已经确认，预计XX天后送达。');

-- 假设模板ID分别是1, 2, 3
INSERT INTO Send_Schedules (template_id, send_time, status) VALUES
(1, '2023-04-01 10:00:00', '待处理'),
(2, '2023-04-02 15:00:00', '待处理'),
(3, '2023-04-03 09:00:00', '待处理');

-- 假设计划ID分别是1, 2, 3，用户ID分别是1, 2, 3
INSERT INTO SMS_Logs (schedule_id, user_id, send_time, status) VALUES
(1, 1, '2023-04-01 10:01:00', '已发送'),
(2, 2, '2023-04-02 15:05:00', '已发送'),
(3, 3, '2023-04-03 09:02:00', '失败');

-- 用户组表
INSERT INTO User_Groups (group_name, description) VALUES
('VIP用户', 'VIP用户组，享受更多特权。');


-- 假设上面插入的用户组ID是1，用户ID是1

-- 用户组用户关联表
INSERT INTO User_Group_Members (user_id, group_id) VALUES
(1, 1);
INSERT INTO SMS_Gateways (gateway_name, api_key, endpoint, other_config) VALUES
('短信网关A', 'abc123', 'https://api.gatewayA.com', '配置信息A'),
('短信网关B', 'xyz789', 'https://api.gatewayB.com', '配置信息B');