-- ----------------------------------------------------------------------------------------
-- 白名单管理
-- ----------------------------------------------------------------------------------------   
DROP TABLE IF EXISTS whitelist_management;
CREATE TABLE whitelist_management
(
    id                BIGINT        NOT NULL COMMENT '主键编号'
        PRIMARY KEY,
    create_date       DATETIME      NULL COMMENT '创建时间',
    modify_date       DATETIME      NULL COMMENT '修改时间',
    delete_mark       INT           NOT NULL COMMENT '删除标记',
    enabled_mark      INT           NOT NULL COMMENT '有效标志',
    user_id             BIGINT      NOT NULL COMMENT '用户id',
    NAME              VARCHAR(10)   NOT NULL COMMENT '姓名',
    credential_number        VARCHAR(30)   NOT NULL COMMENT '身份证',
    phone               BIGINT  NOT NULL  COMMENT '手机号'
)
    COMMENT '白名单管理';
-- ----------------------------------------------------------------------------------------
-- 新生维护信息
-- ----------------------------------------------------------------------------------------   
DROP TABLE IF EXISTS base_new_student;   
CREATE TABLE `base_new_student` (
  `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,
  `graduate_school` VARCHAR(50) DEFAULT NULL COMMENT '毕业学校',
  `name` VARCHAR(50) DEFAULT NULL COMMENT '学生姓名',
  `gender` VARCHAR(20) DEFAULT NULL COMMENT '性别(xjr_dictionary_item[gender])',
  `credential_number` VARCHAR(30) DEFAULT NULL COMMENT '身份证号',
  `height` DECIMAL(6,1) DEFAULT NULL COMMENT '身高',
  `weight` DECIMAL(6,1) DEFAULT NULL COMMENT '体重',
  `score` DECIMAL(6,1) DEFAULT NULL COMMENT '成绩',
  `graduate_class` VARCHAR(50) DEFAULT NULL COMMENT '毕业班级',
  `source` VARCHAR(50) DEFAULT NULL COMMENT '学生来源(xjr_dictionary_item[student_type])',
  `stduy_status` VARCHAR(20) DEFAULT NULL COMMENT '住宿类型(xjr_dictionary_item[stduy_status])',
  `mobile` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  `first_ambition` VARCHAR(50) DEFAULT NULL COMMENT '第一志愿',
  `second_ambition` VARCHAR(50) DEFAULT NULL COMMENT '第二志愿',
  `is_adjust` INT DEFAULT NULL COMMENT '是否可调配（0：否，1：是）',
  `status` INT DEFAULT NULL COMMENT '班级状态（0：未分配， 1：已分配）',
  `enrollment_plan_id` BIGINT DEFAULT NULL COMMENT '招生计划id(enrollment_plan)',
  `family_mobile` VARCHAR(20) DEFAULT NULL COMMENT '家庭电话',
  `family_address` VARCHAR(50) DEFAULT NULL COMMENT '家庭地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新生维护信息';


ALTER TABLE `tl`.`base_new_student`   
  ADD COLUMN `first_ambition_id` BIGINT NULL   COMMENT '第一志愿专业id(base_class_major_set)' AFTER `enrollment_plan_id`,
  ADD COLUMN `second_ambition_id` BIGINT NULL   COMMENT '第二志愿专业id(base_class_major_set)' AFTER `first_ambition_id`;

-- ----------------------------------------------------------------------------------------
-- 新生分班任务
-- ----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS banding_task;   
CREATE TABLE `banding_task` (
  `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,
  `grade_id` BIGINT DEFAULT NULL COMMENT '年级id',
  `enroll_type` VARCHAR(50) DEFAULT NULL COMMENT '招生类型',
  `name` VARCHAR(30) 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 banding_rule;   
CREATE TABLE `banding_rule` (
  `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(200) DEFAULT NULL COMMENT '规则名称',
  `code` VARCHAR(50) DEFAULT NULL COMMENT '规则代码',
  `choose_type` VARCHAR(50) DEFAULT NULL COMMENT '选择方式(radtio:单选, multi:多选)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新生分班规则';

DELETE FROM banding_rule;
INSERT INTO banding_rule(id,create_user_id,create_date,NAME,CODE,choose_type)
VALUE(1807614834171953155, 1000000000000000000, NOW(), '使每班的男女生性别比例比较均衡(男女比例“1:1”，数量为奇数是多的随机分配）','BR0001','multi');
INSERT INTO banding_rule(id,create_user_id,create_date,NAME,CODE,choose_type)
VALUE(1807614834171953156, 1000000000000000000, NOW(), '使每班的学生成绩比例比较均衡(根据成绩均衡分班方法进行均衡分班）','BR0002','multi');
INSERT INTO banding_rule(id,create_user_id,create_date,NAME,CODE,choose_type)
VALUE(1807614834171953157, 1000000000000000000, NOW(), '使姓名相同的学生分到不同的班级','BR0003','multi');
INSERT INTO banding_rule(id,create_user_id,create_date,NAME,CODE,choose_type)
VALUE(1807614834171953159, 1000000000000000000, NOW(), '使同专业方向班级人数比例比较均衡（同一个专业方向分多个班级时，使每个班级人数比较均衡，数量为奇数是多的随机分配）','BR0004','radio');
INSERT INTO banding_rule(id,create_user_id,create_date,NAME,CODE,choose_type)
VALUE(1807614834171953160, 1000000000000000000, NOW(), '使同专业班级一个班级排满在排下一个班级（同一个专业方向分多个班级时，先将第一个班级人数分满，在分第二个班级，以此类推）','BR0005','radio');

UPDATE banding_rule SET delete_mark = 0, enabled_mark = 1;

-- ----------------------------------------------------------------------------------------
-- 新生分班任务规则
-- ----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS banding_task_rule;   
CREATE TABLE `banding_task_rule` (
  `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,
  `banding_rule_id` BIGINT DEFAULT NULL COMMENT '新生分班规则(banding_rule)',
  `banding_task_id` BIGINT DEFAULT NULL COMMENT '分班任务(banding_task)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新生分班任务规则';

-- ----------------------------------------------------------------------------------------
-- 新生分班任务专业限制条件
-- ----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS banding_task_major_condition;   
CREATE TABLE `banding_task_major_condition` (
  `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,
  `major_set_id` BIGINT DEFAULT NULL COMMENT '	',
  `height` DECIMAL(6,1) DEFAULT NULL COMMENT '身高',
  `score` DECIMAL(6,1) DEFAULT NULL COMMENT '分数',
  `banding_task_id` BIGINT DEFAULT NULL COMMENT '分班任务(banding_task)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新生分班任务专业限制条件';

-- ----------------------------------------------------------------------------------------
-- 新生分班任务班级配置
-- ----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS banding_task_class;   
CREATE TABLE `banding_task_class` (
  `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(50) DEFAULT NULL COMMENT '规则名称',
  `major_set_id` BIGINT DEFAULT NULL COMMENT '专业方向id',
  `number` INT DEFAULT NULL COMMENT '班级人数',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `classroom_id` BIGINT DEFAULT NULL COMMENT '教室id',
  `is_order_class` INT DEFAULT NULL COMMENT '是否订单班(0:否 1:是)',
  `height` DECIMAL(6,1) DEFAULT NULL COMMENT '身高',
  `score` DECIMAL(6,1) DEFAULT NULL COMMENT '分数',
  `banding_task_id` BIGINT DEFAULT NULL COMMENT '分班任务(banding_task)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新生分班任务班级配置';


-- ----------------------------------------------------------------------------------------
-- 新生分班任务班级学生关系表
-- ----------------------------------------------------------------------------------------
DROP TABLE IF EXISTS banding_task_class_student;   
CREATE TABLE `banding_task_class_student` (
  `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,
  `banding_task_class_id` BIGINT DEFAULT NULL COMMENT '分班表的id',
  `new_student_id` BIGINT DEFAULT NULL COMMENT '新生id',
  `status` INT DEFAULT NULL COMMENT '状态(0:未确认 1:已确认)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新生分班任务班级学生关系表';

DROP TABLE IF EXISTS ods_zzxxgkjcsj;   
CREATE TABLE `ods_zzxxgkjcsj` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATE DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人',
  `modify_date` DATE DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `school_name` VARCHAR(500) DEFAULT NULL COMMENT '学校名称',
  `school_code` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学校代码',
  `school_institution_code` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学校机构代码',
  `school_type` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学校类别',
  `organizer_name` VARCHAR(255) DEFAULT NULL COMMENT '学校举办者名称',
  `school_organizer_nature` VARCHAR(500) DEFAULT NULL COMMENT '学校举办者性质',
  `superintendent` VARCHAR(255) DEFAULT NULL COMMENT '学校负责人姓名',
  `establish_date` DATE DEFAULT NULL COMMENT '建校年月',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学校基本数据子类表';


ALTER TABLE ods_zzxxgkjcsj
ADD  location VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学校所在地行政区域', 
ADD  supervisory_department VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学校主管部门',
ADD  exit_bandwidth VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学校园出口带宽',
ADD  backbone_bandwidth VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '校园主干网带宽',
ADD  wired_access INT DEFAULT NULL COMMENT '有线网络接入数',
ADD  wireless_access INT DEFAULT NULL COMMENT '无线网络接入数',
ADD  multimedia_classrooms INT DEFAULT NULL COMMENT '多媒体教室数',
ADD  is_double_excellence VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '是否双优学校';

DROP TABLE IF EXISTS ods_zzxqjcsj;   
CREATE TABLE `ods_zzxqjcsj` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATE DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人',
  `modify_date` DATE DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `school_id` BIGINT DEFAULT NULL COMMENT '所属学校（ods_zzxxgkjcsj_id）',
  `school_number` VARCHAR(20) DEFAULT NULL COMMENT '校区编号',
  `name` VARCHAR(255) DEFAULT NULL COMMENT '校区名称',
  `name_s` VARCHAR(255) DEFAULT NULL COMMENT '校区简称',
  `administrative_location` VARCHAR(500) DEFAULT NULL COMMENT '校区所在地行政区域',
  `address` VARCHAR(500) DEFAULT NULL COMMENT '地址',
  `phone` BIGINT DEFAULT NULL COMMENT '校区联系电话',
  `postal_code` VARCHAR(100) DEFAULT NULL COMMENT '校区邮政编码',
  `build_date` DATE DEFAULT NULL COMMENT '校区成立日期',
  `director` VARCHAR(255) DEFAULT NULL COMMENT '校区负责人',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='校区基本数据子类表';

DROP TABLE IF EXISTS student_psychological;  
CREATE TABLE `student_psychological` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATE DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人',
  `modify_date` DATE DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `class_id` BIGINT DEFAULT NULL COMMENT '学生班级id',
  `student_id` BIGINT DEFAULT NULL COMMENT '学生',
  `gender` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学生性别',
  `identity_card` VARCHAR(255) DEFAULT NULL COMMENT '身份证号',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `appoint_time` DATETIME DEFAULT NULL,
  `remark` VARCHAR(1000) DEFAULT NULL COMMENT '备注',
  `confirm_time` DATETIME DEFAULT NULL COMMENT '确认时间',
  `location` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '咨询地点',
  `student_conclusion` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '心理咨询结论（学生寄语）',
  `teacher_conclusion` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '心理咨询结论（班主任寄语）',
  `parent_conclusion` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '心理咨询结论（家长寄语）',
  `status` INT DEFAULT NULL COMMENT '流程状态（1：结束，0：未结束）',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生心理咨询表';

DROP TABLE IF EXISTS student_infection; 
CREATE TABLE `student_infection` (
  `id` BIGINT NOT NULL,
  `create_user_id` BIGINT DEFAULT NULL COMMENT '创建人',
  `create_date` DATE DEFAULT NULL COMMENT '创建时间',
  `modify_user_id` BIGINT DEFAULT NULL COMMENT '修改人',
  `modify_date` DATE DEFAULT NULL COMMENT '修改日期',
  `delete_mark` INT DEFAULT NULL COMMENT '删除标记',
  `enabled_mark` INT DEFAULT NULL COMMENT '有效标记',
  `class_id` BIGINT DEFAULT NULL COMMENT '学生班级id',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '班主任id',
  `student_id` BIGINT DEFAULT NULL COMMENT '学生',
  `gender` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学生性别',
  `identity_card` VARCHAR(255) DEFAULT NULL COMMENT '身份证号',
  `infect_time` DATE DEFAULT NULL COMMENT '感染时间',
  `infect_name` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '传染病名称',
  `disposal_method` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '处理方式',
  `status` INT DEFAULT NULL COMMENT '流程状态（1：结束，0：未结束）',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生传染病登记表';

ALTER TABLE `base_class`   
  CHANGE `delete_mark` `delete_mark` INT DEFAULT 0  NOT NULL   COMMENT '删除标记',
  CHANGE `enabled_mark` `enabled_mark` INT DEFAULT 1  NOT NULL   COMMENT '有效标志';
  
ALTER TABLE `banding_task_class`   
  ADD INDEX (`banding_task_id`),
  ADD INDEX (`major_set_id`),
  ADD INDEX (`teacher_id`),
  ADD INDEX (`classroom_id`);

ALTER TABLE `banding_task_class_student`   
  ADD INDEX (`banding_task_class_id`),
  ADD INDEX (`new_student_id`);

ALTER TABLE `banding_task_major_condition`   
  ADD INDEX (`banding_task_id`),
  ADD INDEX (`major_set_id`);

ALTER TABLE `banding_task_rule`   
  ADD INDEX (`banding_rule_id`),
  ADD INDEX (`banding_task_id`);
  
ALTER TABLE `banding_task_class`   
  ADD COLUMN `class_type` VARCHAR(50) NULL   COMMENT '班级类型(xjr_dictionary_item[class_type])' AFTER `banding_task_id`;
