all-project/backend/script/sql/create-collection-batch-tables.sql

-- ==========================================
-- 信息采集批次相关表创建脚本
-- 版本：V1.0.0
-- 创建时间：2025-01-17
-- 说明：创建信息采集批次功能所需的关联表
-- ==========================================

USE hzdyjs_xg;

-- 1. 修改现有批次表结构，增加目标条件字段（如果不存在）
ALTER TABLE xg_collection_batch 
ADD COLUMN IF NOT EXISTS target_grade VARCHAR(50) COMMENT '目标届别(如:2021,2022,2023)' AFTER batch_status,
ADD COLUMN IF NOT EXISTS target_college_ids TEXT COMMENT '目标学院ID列表(JSON格式)' AFTER target_grade,
ADD COLUMN IF NOT EXISTS target_major_ids TEXT COMMENT '目标专业ID列表(JSON格式)' AFTER target_college_ids,
ADD COLUMN IF NOT EXISTS target_class_ids TEXT COMMENT '目标班级ID列表(JSON格式)' AFTER target_major_ids,
ADD COLUMN IF NOT EXISTS remind_before_start INT DEFAULT 24 COMMENT '开始前提醒时间(小时)' AFTER target_class_ids,
ADD COLUMN IF NOT EXISTS remind_before_end INT DEFAULT 24 COMMENT '结束前提醒时间(小时)' AFTER remind_before_start,
ADD COLUMN IF NOT EXISTS auto_close TINYINT DEFAULT 1 COMMENT '是否自动关闭' AFTER remind_before_end,
ADD COLUMN IF NOT EXISTS notification_sent TINYINT DEFAULT 0 COMMENT '是否已发送通知' AFTER auto_close,
ADD COLUMN IF NOT EXISTS created_by_module VARCHAR(50) DEFAULT 'student_info' COMMENT '创建模块' AFTER notification_sent;

-- 2. 创建批次学生关联表(用于快速查询和统计)
CREATE TABLE IF NOT EXISTS xg_collection_batch_student (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    batch_id BIGINT NOT NULL COMMENT '批次ID',
    student_id BIGINT NOT NULL COMMENT '学生ID',
    student_code VARCHAR(50) NOT NULL COMMENT '学号',
    student_name VARCHAR(100) NOT NULL COMMENT '学生姓名',
    college_id BIGINT COMMENT '学院ID',
    college_name VARCHAR(100) COMMENT '学院名称',
    major_id BIGINT COMMENT '专业ID',
    major_name VARCHAR(100) COMMENT '专业名称',
    class_id BIGINT COMMENT '班级ID',
    class_name VARCHAR(100) COMMENT '班级名称',
    grade VARCHAR(50) COMMENT '届别',
    can_edit TINYINT DEFAULT 1 COMMENT '是否可编辑',
    has_submitted TINYINT DEFAULT 0 COMMENT '是否已提交',
    submit_time DATETIME COMMENT '提交时间',
    last_edit_time DATETIME COMMENT '最后编辑时间',
    edit_count INT DEFAULT 0 COMMENT '编辑次数',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    create_by BIGINT COMMENT '创建者',
    update_by BIGINT COMMENT '更新者',
    deleted TINYINT DEFAULT 0 COMMENT '删除标志',
    tenant_id VARCHAR(20) DEFAULT '000000' COMMENT '租户ID',
    INDEX idx_batch_id (batch_id),
    INDEX idx_student_id (student_id),
    INDEX idx_batch_student (batch_id, student_id),
    INDEX idx_college_id (college_id),
    INDEX idx_grade (grade),
    INDEX idx_create_time (create_time),
    INDEX idx_has_submitted (has_submitted),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='信息采集批次学生关联表';

-- 3. 创建批次操作日志表
CREATE TABLE IF NOT EXISTS xg_collection_batch_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    batch_id BIGINT NOT NULL COMMENT '批次ID',
    student_id BIGINT COMMENT '学生ID',
    operation_type VARCHAR(50) NOT NULL COMMENT '操作类型(CREATE,UPDATE,SUBMIT,ADMIN_MODIFY,BATCH_PUBLISH,BATCH_CLOSE)',
    operation_desc TEXT COMMENT '操作描述',
    old_data TEXT COMMENT '修改前数据(JSON格式)',
    new_data TEXT COMMENT '修改后数据(JSON格式)',
    operator_id BIGINT COMMENT '操作人ID',
    operator_name VARCHAR(100) COMMENT '操作人姓名',
    operator_type VARCHAR(50) COMMENT '操作人类型(STUDENT,ADMIN,SYSTEM)',
    ip_address VARCHAR(50) COMMENT 'IP地址',
    user_agent TEXT COMMENT '用户代理',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    tenant_id VARCHAR(20) DEFAULT '000000' COMMENT '租户ID',
    INDEX idx_batch_id (batch_id),
    INDEX idx_student_id (student_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_create_time (create_time),
    INDEX idx_operator_id (operator_id),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='信息采集批次操作日志表';

-- 4. 创建批次权限配置表
CREATE TABLE IF NOT EXISTS xg_collection_batch_permission (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    batch_id BIGINT NOT NULL COMMENT '批次ID',
    permission_type VARCHAR(50) NOT NULL COMMENT '权限类型(EDIT,VIEW,SUBMIT,ADMIN)',
    role_id BIGINT COMMENT '角色ID',
    user_id BIGINT COMMENT '用户ID',
    college_id BIGINT COMMENT '学院ID',
    is_granted TINYINT DEFAULT 1 COMMENT '是否授权',
    grant_time DATETIME COMMENT '授权时间',
    expire_time DATETIME COMMENT '过期时间',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    create_by BIGINT COMMENT '创建者',
    update_by BIGINT COMMENT '更新者',
    deleted TINYINT DEFAULT 0 COMMENT '删除标志',
    tenant_id VARCHAR(20) DEFAULT '000000' COMMENT '租户ID',
    INDEX idx_batch_id (batch_id),
    INDEX idx_permission_type (permission_type),
    INDEX idx_role_id (role_id),
    INDEX idx_user_id (user_id),
    INDEX idx_college_id (college_id),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='信息采集批次权限配置表';

-- 5. 创建批次统计表
CREATE TABLE IF NOT EXISTS xg_collection_batch_statistics (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    batch_id BIGINT NOT NULL COMMENT '批次ID',
    total_students INT DEFAULT 0 COMMENT '总学生数',
    submitted_students INT DEFAULT 0 COMMENT '已提交学生数',
    pending_students INT DEFAULT 0 COMMENT '待提交学生数',
    completion_rate DECIMAL(5,2) DEFAULT 0.00 COMMENT '完成率',
    college_id BIGINT COMMENT '学院ID(按学院统计时使用)',
    college_name VARCHAR(100) COMMENT '学院名称',
    major_id BIGINT COMMENT '专业ID(按专业统计时使用)',
    major_name VARCHAR(100) COMMENT '专业名称',
    class_id BIGINT COMMENT '班级ID(按班级统计时使用)',
    class_name VARCHAR(100) COMMENT '班级名称',
    grade VARCHAR(50) COMMENT '届别',
    statistics_type VARCHAR(50) DEFAULT 'TOTAL' COMMENT '统计类型(TOTAL,COLLEGE,MAJOR,CLASS,GRADE)',
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    tenant_id VARCHAR(20) DEFAULT '000000' COMMENT '租户ID',
    INDEX idx_batch_id (batch_id),
    INDEX idx_college_id (college_id),
    INDEX idx_major_id (major_id),
    INDEX idx_class_id (class_id),
    INDEX idx_grade (grade),
    INDEX idx_statistics_type (statistics_type),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='信息采集批次统计表';

-- 6. 创建批次定时任务表
CREATE TABLE IF NOT EXISTS xg_batch_scheduled_task (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    batch_id BIGINT NOT NULL COMMENT '批次ID',
    task_type VARCHAR(50) NOT NULL COMMENT '任务类型(REMIND_START,REMIND_END,AUTO_CLOSE)',
    task_time DATETIME NOT NULL COMMENT '执行时间',
    task_status VARCHAR(20) DEFAULT 'PENDING' COMMENT '任务状态(PENDING,EXECUTED,FAILED)',
    execute_time DATETIME COMMENT '实际执行时间',
    error_message TEXT COMMENT '错误信息',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    tenant_id VARCHAR(20) DEFAULT '000000' COMMENT '租户ID',
    INDEX idx_batch_id (batch_id),
    INDEX idx_task_type (task_type),
    INDEX idx_task_time (task_time),
    INDEX idx_task_status (task_status),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='信息采集批次定时任务表';

-- 7. 修改学生信息表，添加批次相关字段（如果不存在）
ALTER TABLE xg_student_info 
ADD COLUMN IF NOT EXISTS last_batch_id BIGINT COMMENT '最后参与的批次ID' AFTER remark,
ADD COLUMN IF NOT EXISTS last_edit_batch_id BIGINT COMMENT '最后编辑的批次ID' AFTER last_batch_id,
ADD COLUMN IF NOT EXISTS info_lock_status TINYINT DEFAULT 0 COMMENT '信息锁定状态(0:正常,1:锁定)' AFTER last_edit_batch_id,
ADD COLUMN IF NOT EXISTS lock_reason VARCHAR(500) COMMENT '锁定原因' AFTER info_lock_status,
ADD COLUMN IF NOT EXISTS last_batch_submit_time DATETIME COMMENT '最后批次提交时间' AFTER lock_reason;

-- 8. 创建相关索引
CREATE INDEX IF NOT EXISTS idx_xg_collection_batch_grade ON xg_collection_batch(target_grade);
CREATE INDEX IF NOT EXISTS idx_xg_collection_batch_module ON xg_collection_batch(created_by_module);
CREATE INDEX IF NOT EXISTS idx_xg_student_info_last_batch ON xg_student_info(last_batch_id);
CREATE INDEX IF NOT EXISTS idx_xg_student_info_lock_status ON xg_student_info(info_lock_status);

-- 9. 插入一些测试数据
INSERT INTO xg_collection_batch (id, tenant_id, batch_code, batch_name, batch_type, academic_year, semester, batch_status, start_time, end_time, description, create_by, create_time, update_by, update_time, deleted) VALUES
(1745001000000000001, '000000', 'BATCH_2024_1', '2024-2025学年第一学期信息采集', 'annual', '2024-2025', '1', '2', '2024-09-01 00:00:00', '2024-09-30 23:59:59', '2024-2025学年第一学期学生信息采集工作', 1, NOW(), 1, NOW(), 0),
(1745001000000000002, '000000', 'BATCH_2024_2', '2024-2025学年第二学期信息采集', 'annual', '2024-2025', '2', '1', '2025-02-15 00:00:00', '2025-03-15 23:59:59', '2024-2025学年第二学期学生信息采集工作', 1, NOW(), 1, NOW(), 0)
ON DUPLICATE KEY UPDATE 
    batch_name = VALUES(batch_name),
    description = VALUES(description),
    update_time = NOW();

-- 完成信息
SELECT 'Database tables created successfully!' as message;