schoolNews/schoolNewsServ/.bin/mysql/sql/createTableMessage.sql

-- =====================================================
-- 消息通知模块 - 数据库表结构
-- 包含3张表：消息主体表、消息接收对象表、用户消息表
-- =====================================================

-- 1. 消息主体表
DROP TABLE IF EXISTS tb_sys_message;
CREATE TABLE tb_sys_message (
    -- 基础标识
    id VARCHAR(50) PRIMARY KEY  COMMENT '主键ID',
    message_id VARCHAR(50) NOT NULL UNIQUE COMMENT '消息唯一标识',

    -- 消息内容
    title VARCHAR(200) NOT NULL COMMENT '消息标题',
    content TEXT NOT NULL COMMENT '消息内容',
    message_type VARCHAR(20) NOT NULL DEFAULT 'notification' COMMENT '消息类型：notification-通知/announcement-公告/warning-预警',
    priority VARCHAR(20) NOT NULL DEFAULT 'normal' COMMENT '优先级：normal-普通/important-重要/urgent-紧急',

    -- 发送人信息
    sender_id VARCHAR(50) NOT NULL COMMENT '发送人用户ID',
    sender_name VARCHAR(100) COMMENT '发送人姓名（冗余字段）',
    sender_dept_id VARCHAR(50) NOT NULL COMMENT '发送人部门ID',
    sender_dept_name VARCHAR(100) COMMENT '发送人部门名称（冗余字段）',

    -- 发送时间控制（定时发送功能）
    send_mode VARCHAR(20) NOT NULL DEFAULT 'immediate' COMMENT '发送模式：immediate-立即发送/scheduled-定时发送',
    scheduled_time DATETIME COMMENT '计划发送时间（sendMode=scheduled时必填）',
    actual_send_time DATETIME COMMENT '实际发送时间',

    -- 状态管理
    status VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT '状态：draft-草稿/pending-待发送/sending-发送中/sent-已发送/failed-失败/cancelled-已取消',

    -- 统计信息
    target_user_count INT DEFAULT 0 COMMENT '目标用户总数',
    sent_count INT DEFAULT 0 COMMENT '已发送数量',
    success_count INT DEFAULT 0 COMMENT '发送成功数量',
    failed_count INT DEFAULT 0 COMMENT '发送失败数量',
    read_count INT DEFAULT 0 COMMENT '已读数量',

    -- 失败处理和重试机制
    retry_count INT DEFAULT 0 COMMENT '当前重试次数',
    max_retry_count INT DEFAULT 3 COMMENT '最大重试次数',
    last_error TEXT COMMENT '最后错误信息',

    -- 基础字段
    creator VARCHAR(50) COMMENT '创建人ID',
    updater VARCHAR(50) COMMENT '更新人ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    delete_time DATETIME COMMENT '删除时间',
    deleted BIT(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-未删除，1-已删除',

    -- 索引
    INDEX idx_message_id (message_id),
    INDEX idx_sender_id (sender_id),
    INDEX idx_sender_dept_id (sender_dept_id),
    INDEX idx_status (status),
    INDEX idx_send_mode (send_mode),
    INDEX idx_scheduled_time (scheduled_time),
    INDEX idx_create_time (create_time),
    INDEX idx_delete_time (delete_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='消息主体表';


-- 2. 消息发送方式接收对象表
DROP TABLE IF EXISTS tb_sys_message_target;
CREATE TABLE tb_sys_message_target (
    -- 基础标识
    id VARCHAR(50) PRIMARY KEY  COMMENT '主键ID',
    message_id VARCHAR(50) NOT NULL COMMENT '消息ID（关联tb_sys_message.message_id）',

    -- 发送方式
    send_method VARCHAR(100) NOT NULL COMMENT '发送方式：system-系统消息/email-邮件/sms-短信（多选时逗号分隔，如：system,email）',

    -- 接收对象
    target_type VARCHAR(20) NOT NULL COMMENT '接收对象类型：dept-部门/role-角色/user-人员',
    target_id VARCHAR(50) NOT NULL COMMENT '接收对象ID（部门ID/角色ID/用户ID）',
    target_name VARCHAR(100) COMMENT '接收对象名称（冗余字段，便于展示）',

    -- 作用域部门（关键字段：限制角色的部门范围）
    scope_dept_id VARCHAR(50) NOT NULL COMMENT '作用域部门ID：dept时与target_id相同；role时表示该角色限定在哪个部门及其子部门范围内；user时为用户所属部门ID',

    -- 基础字段
    creator VARCHAR(50) COMMENT '创建人ID',
    updater VARCHAR(50) COMMENT '更新人ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    delete_time DATETIME COMMENT '删除时间',
    deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-未删除，1-已删除',

    -- 索引
    INDEX idx_messageID (message_id),
    INDEX idx_target_type (target_type),
    INDEX idx_scope_dept_id (scope_dept_id),
    INDEX idx_delete_time (delete_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='消息发送方式接收对象表';


-- 3. 用户接收消息表
DROP TABLE IF EXISTS tb_sys_message_user;
CREATE TABLE tb_sys_message_user (
    -- 基础标识
    id VARCHAR(50) PRIMARY KEY  COMMENT '主键ID',
    message_id VARCHAR(50) NOT NULL COMMENT '消息ID（关联tb_sys_message.message_id）',
    user_id VARCHAR(50) NOT NULL COMMENT '接收用户ID',

    -- 发送方式
    send_method VARCHAR(20) NOT NULL COMMENT '实际发送方式：system-系统消息/email-邮件/sms-短信',

    -- 阅读状态
    is_read tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已读：0-未读，1-已读',
    read_time DATETIME COMMENT '阅读时间',

    -- 发送状态
    send_status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '发送状态：pending-待发送/success-发送成功/failed-发送失败',
    fail_reason TEXT COMMENT '失败原因',

    -- 基础字段
    creator VARCHAR(50) COMMENT '创建人ID',
    updater VARCHAR(50) COMMENT '更新人ID',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    delete_time DATETIME COMMENT '删除时间',
    deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除：0-未删除，1-已删除',

    -- 索引
    INDEX idx_message_id (message_id),
    INDEX idx_user_id (user_id),
    INDEX idx_is_read (is_read),
    INDEX idx_send_status (send_status),
    INDEX idx_deleted (deleted),
    INDEX idx_user_read (user_id, is_read, deleted) COMMENT '用户未读消息查询索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户接收消息表';


-- =====================================================
-- 初始化说明
-- =====================================================
-- 1. 执行本SQL脚本创建3张表
-- 2. 执行message_menu.sql插入菜单权限数据
-- 3. 表结构说明：
--    - tb_sys_message：存储消息主体信息，支持定时发送
--    - tb_sys_message_target：存储接收对象配置，scopeDeptID限制权限范围
--    - tb_sys_message_user：存储每个用户的消息记录，支持已读/未读状态
-- =====================================================