-- ------------------------------------------------
-- 系统消息
-- ------------------------------------------------
DROP TABLE IF EXISTS system_update_message;
CREATE TABLE `system_update_message` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `send_range` INT DEFAULT NULL COMMENT '发送范围（1：全体师生，2：全体教职工）',
  `title` VARCHAR(200) DEFAULT NULL COMMENT '消息标题',
  `title_color` VARCHAR(30) DEFAULT NULL COMMENT '标题颜色',
  `content` VARCHAR(1000) DEFAULT NULL COMMENT '消息内容',
  `status` INT DEFAULT 0 COMMENT '状态（0：未发布 1：已发布）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统消息';

-- ------------------------------------------------
-- 系统消息
-- ------------------------------------------------
DROP TABLE IF EXISTS system_update_message_notice;
CREATE TABLE `system_update_message_notice` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `system_update_message_id` BIGINT DEFAULT NULL COMMENT '系统消息（system_update_message）',
  `user_id` VARCHAR(200) DEFAULT NULL COMMENT '用户id（xjr_user）',
  `status` INT DEFAULT 0 COMMENT '状态（0：未读，1：已读）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统消息通知表';

-- ------------------------------------------------
-- 学生插班
-- ------------------------------------------------
DROP TABLE IF EXISTS student_transfer;
CREATE TABLE `student_transfer` (
  `id` BIGINT NOT NULL COMMENT '主键',
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATETIME(3) DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人id',
  `modify_date` DATETIME(3) DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `student_user_id` BIGINT DEFAULT NULL COMMENT '学生用户id',
  `gender` VARCHAR(20) DEFAULT NULL COMMENT '性别',
  `I_D_number`  VARCHAR(100) DEFAULT NULL COMMENT '身份证号',
  `study_status` VARCHAR(20) DEFAULT NULL COMMENT '就读方式(xjr_dictionary_item[stduy_status])',
  `textbook_fees` VARCHAR(50) DEFAULT NULL COMMENT '教材费缴费情况',
  `homestay` VARCHAR(50) DEFAULT NULL COMMENT '住宿费缴费情况',
  `class_id` BIGINT DEFAULT NULL COMMENT '班级id',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `status` INT DEFAULT '0' COMMENT '状态(0:未结束 1:结束)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生插班';

-- ------------------------------------------------
-- 退学申请
-- ------------------------------------------------
DROP TABLE IF EXISTS student_drop_out;
CREATE TABLE `student_drop_out` (
  `id` BIGINT NOT NULL COMMENT '主键',
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATETIME(3) DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人id',
  `modify_date` DATETIME(3) DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `student_user_id` BIGINT DEFAULT NULL COMMENT '学生用户id',
  `grade_id` BIGINT DEFAULT NULL COMMENT '年级id',
  `class_id` BIGINT DEFAULT NULL COMMENT '班级id',
  `I_D_number`  VARCHAR(100) DEFAULT NULL COMMENT '身份证号',
  `drop_date` DATETIME DEFAULT NULL COMMENT '退学日期',
  `reason` VARCHAR(400) DEFAULT NULL COMMENT '请假原因',
  `folder_id` BIGINT DEFAULT NULL COMMENT '附件',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `status` INT DEFAULT '0' COMMENT '状态(0:未结束 1:结束)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='退学申请';

-- ------------------------------------------------
-- 学生请假
-- ------------------------------------------------
DROP TABLE IF EXISTS student_leave;
CREATE TABLE `student_leave` (
  `id` BIGINT NOT NULL COMMENT '主键',
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATETIME(3) DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人id',
  `modify_date` DATETIME(3) DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `student_user_id` BIGINT DEFAULT NULL COMMENT '学生用户id',
  `class_id` BIGINT DEFAULT NULL COMMENT '班级id',
  `start_date` DATETIME DEFAULT NULL COMMENT '开始时间',
  `end_date` DATETIME DEFAULT NULL COMMENT '结束时间',
  `day_duration` FLOAT DEFAULT NULL COMMENT '时长（天）',
  `leave_type` VARCHAR(50) DEFAULT '' COMMENT '请假类型',
  `reason` VARCHAR(400) DEFAULT NULL COMMENT '请假原因',
  `folder_id` BIGINT DEFAULT NULL COMMENT '附件',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `status` INT DEFAULT '0' COMMENT '状态(0:未结束 1:结束)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生请假';

-- ------------------------------------------------
-- 学生转班
-- ------------------------------------------------
DROP TABLE IF EXISTS student_change_class;
CREATE TABLE `student_change_class` (
  `id` BIGINT NOT NULL COMMENT '主键',
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATETIME(3) DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人id',
  `modify_date` DATETIME(3) DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `before_class_id` BIGINT DEFAULT NULL COMMENT '转出班级id',
  `student_user_id` BIGINT DEFAULT NULL COMMENT '学生用户id',
  `gender` VARCHAR(20) DEFAULT NULL COMMENT '性别',
  `I_D_number` VARCHAR(100) DEFAULT NULL COMMENT '身份证号',
  `textbook_fees` VARCHAR(20) DEFAULT NULL COMMENT '教材费缴费情况',
  `homestay` VARCHAR(20) DEFAULT NULL COMMENT '住宿费缴费情况',
  `after_class_id` BIGINT DEFAULT NULL COMMENT '转入班级id',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `status` INT DEFAULT '0' COMMENT '状态(0:未结束 1:结束)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生转班';


ALTER TABLE `base_student_school_roll`   
  CHANGE `roll_number` `roll_number` VARCHAR(30) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL   COMMENT '学籍号',
  CHANGE `archives_number` `archives_number` VARCHAR(30) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL   COMMENT '学生档案编号',
  CHANGE `study_year` `study_year` DECIMAL(6,1) NULL   COMMENT '学制(base_major_set)';

-- ------------------------------------------------
-- 常用功能设置
-- ------------------------------------------------
DROP TABLE IF EXISTS system_menu_commonly_used;
CREATE TABLE `system_menu_commonly_used` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `menu_id` BIGINT DEFAULT NULL COMMENT '菜单id',
  `seq` INT DEFAULT NULL COMMENT '序号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='常用功能设置';


ALTER TABLE `base_course_subject`   
  CHANGE `code` `code` VARCHAR(30) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL   COMMENT '课程学科代码';
  
DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='xjr_user' AND COLUMN_NAME = 'sign_folder_id') THEN
ALTER TABLE `xjr_user`   
  ADD COLUMN `sign_folder_id` BIGINT NULL   COMMENT '签名文件' AFTER `is_change_password`;
    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;

DROP PROCEDURE IF EXISTS createCom;
DELIMITER $$
CREATE
    PROCEDURE createCom()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM information_schema.columns  WHERE table_name='xjr_user' AND COLUMN_NAME = 'sign_password') THEN
ALTER TABLE `xjr_user`   
  ADD COLUMN `sign_password` VARCHAR(100) NULL   COMMENT '签名密码' AFTER `is_change_password`;
    END IF;
END$$
DELIMITER ;
CALL createCom;
DROP PROCEDURE createCom;



-- ------------------------------------------------
-- 数据导出-数据源设置
-- ------------------------------------------------
DROP TABLE IF EXISTS data_expert_source;
CREATE TABLE `data_expert_source` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `sort_code` INT DEFAULT NULL,
  `name` VARCHAR(100) DEFAULT NULL COMMENT '数据源名称',
  `source_type` VARCHAR(50) DEFAULT NULL COMMENT '数据源类型',
  `api_url` VARCHAR(50) DEFAULT NULL COMMENT 'magicapi地址',
  `field_json` LONGTEXT DEFAULT NULL COMMENT '字段配置',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据导出-数据源设置';


-- ------------------------------------------------
-- 数据导出-数据模板
-- ------------------------------------------------
DROP TABLE IF EXISTS data_expert_template;
CREATE TABLE `data_expert_template` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL,
  `create_date` DATETIME(3) DEFAULT NULL,
  `modify_user_id` BIGINT DEFAULT NULL,
  `modify_date` DATETIME(3) DEFAULT NULL,
  `delete_mark` INT DEFAULT NULL,
  `enabled_mark` INT DEFAULT NULL,
  `sort_code` INT DEFAULT NULL,
  `name` VARCHAR(100) DEFAULT NULL COMMENT '模板名称',
  `data_expert_source_id` BIGINT DEFAULT NULL COMMENT '数据源(data_expert_source)',
  `field_json` LONGTEXT DEFAULT NULL COMMENT '字段配置',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='数据导出-数据模板';

-- ------------------------------------------------
-- 寝室申请
-- ------------------------------------------------
DROP TABLE IF EXISTS wf_room_applicant;
CREATE TABLE `wf_room_applicant` (
  `id` BIGINT NOT NULL COMMENT '主键编号',
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATETIME DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人',
  `modify_date` DATETIME DEFAULT NULL COMMENT '修改时间',
  `delete_mark` INT NOT NULL COMMENT '删除标记',
  `enabled_mark` INT NOT NULL COMMENT '有效标志',
  `sort_code` INT DEFAULT NULL COMMENT '序号',
  `applicant_user_id` BIGINT DEFAULT NULL COMMENT '申请人',
  `head_teacher_id` BIGINT DEFAULT NULL COMMENT '班主任用户编号',
  `recede_type` VARCHAR(20) DEFAULT NULL COMMENT '寝室申请类型(xjr_dictionary_item[room_applicant_type])',
  `class_name` VARCHAR(200) DEFAULT NULL COMMENT '班级名称',
  `room_bed_number` VARCHAR(200) DEFAULT NULL COMMENT '寝室+床位名称',
  `start_time` DATE DEFAULT NULL COMMENT '开始时间',
  `nos_reason` VARCHAR(20) DEFAULT NULL COMMENT '离宿事由(xjr_dictionary_item[nos_reason])',
  `remark` VARCHAR(1000) DEFAULT NULL COMMENT '备注',
  `status` INT NOT NULL DEFAULT '0' COMMENT '状态（1:结束 0:未结束）',
  `grade` BIGINT DEFAULT NULL,
  `I_D_number` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='寝室申请';