server/distributed-print/install/mysql/upgrade/3.4.0.sql

USE teachcloud_db;

ALTER TABLE `exam_student` ADD COLUMN `basic_student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_detail_course_id`;
ALTER TABLE `mark_student`
    ADD COLUMN `basic_student_id` BIGINT(20) NULL COMMENT '考生ID（basic_exam_student表ID）' AFTER `exam_id`,
ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `student_id`;
ALTER TABLE `basic_exam_student` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `exam_card` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `org_id`;
ALTER TABLE `exam_detail_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_detail_id`;
ALTER TABLE `exam_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `org_id`;
ALTER TABLE `mark_document` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_group` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_paper` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `mark_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `teach_course` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `t_b_sync_task` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;
ALTER TABLE `client_status` ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `print_plan_id`;


ALTER TABLE `basic_course` CHANGE COLUMN `org_id` `org_id` BIGINT NULL COMMENT '机构id' ;
ALTER TABLE `basic_course` DROP INDEX `course_schoolId_code_idx` , ADD UNIQUE INDEX `uq_1` USING BTREE (`school_id`, `teaching_room_id`, `code`);
ALTER TABLE `teach_course` CHANGE COLUMN `course_code` `course_code` VARCHAR(20) NULL COMMENT '课程代码' ;

ALTER TABLE `mark_paper_package`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`,
CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;


ALTER TABLE `exam_task_temp`
    ADD COLUMN `teaching_room_name` VARCHAR(100) NULL COMMENT '开课学院' AFTER `course_name`;

ALTER TABLE `exam_task`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(50) NULL COMMENT '课程名称' ;

ALTER TABLE `basic_teach_clazz`
    ADD COLUMN `course_id` BIGINT(20) NULL COMMENT '课程ID（basic_course表ID）' AFTER `exam_id`;

ALTER TABLE `basic_teach_clazz`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ;

ALTER TABLE `basic_exam_student`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编号' ,
    CHANGE COLUMN `clazz_id` `clazz_id` BIGINT NULL COMMENT '教学班id（basic_teach_clazz）' ;

ALTER TABLE `exam_detail_course`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(20) CHARACTER SET 'utf8mb4' NULL COMMENT '课程编码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(50) CHARACTER SET 'utf8mb4' NULL COMMENT '课程名称' ;

ALTER TABLE `mark_student`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '科目代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称',
    CHANGE COLUMN `paper_number` `paper_number` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '试卷类型' ,
    CHANGE COLUMN `student_code` `student_code` VARCHAR(64) CHARACTER SET 'utf8mb4' NULL COMMENT '学号' ,
    CHANGE COLUMN `student_name` `student_name` VARCHAR(64) CHARACTER SET 'utf8mb4' NULL COMMENT '姓名' ;


ALTER TABLE `sys_org` DROP COLUMN `third_relate_id`;

ALTER TABLE `exam_student`
    CHANGE COLUMN `student_name` `student_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '考生姓名' ,
    CHANGE COLUMN `student_code` `student_code` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '考生代码' ;

ALTER TABLE `mark_paper`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;

ALTER TABLE `mark_task`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '课程名称' ;

ALTER TABLE `mark_document`
    CHANGE COLUMN `course_code` `course_code` VARCHAR(32) CHARACTER SET 'utf8mb4' NULL COMMENT '课程代码' ,
    CHANGE COLUMN `course_name` `course_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL COMMENT '科目名称' ;

update exam_task a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update exam_card a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update exam_detail_course a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update teach_course a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update t_b_sync_task a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update basic_exam_student a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update basic_teach_clazz a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;
update mark_student a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_document a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_group a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_paper a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_task a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update mark_paper_package a left join basic_exam be on a.exam_id = be.id left join basic_course bc on be.school_id = bc.school_id and a.course_code = bc.code set a.course_id = bc.id where a.course_id is null;
update client_status a set a.course_id = (select bc.id from basic_course bc where a.school_id = bc.school_id and a.course_code = bc.code) where a.course_id is null;

ALTER TABLE `exam_print_plan` DROP COLUMN `third_relate_name`, DROP COLUMN `third_relate_id`;
ALTER TABLE `exam_student` DROP COLUMN `can_delete`;
ALTER TABLE `basic_exam_student` ADD COLUMN `teach_class_name` VARCHAR(100) NULL AFTER `major`, ADD COLUMN `class_name` VARCHAR(100) NULL AFTER `teach_class_name`;
ALTER TABLE `basic_exam_student` ADD COLUMN `site_number` VARCHAR(10) NULL COMMENT '座位号' AFTER `student_code`, ADD COLUMN `extend_fields` MEDIUMTEXT NULL COMMENT '扩展字段' AFTER `exam_room`;
ALTER TABLE `basic_exam_student`
    CHANGE COLUMN `college` `college_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '学院' ,
    CHANGE COLUMN `major` `major_name` VARCHAR(100) CHARACTER SET 'utf8mb4' NULL DEFAULT NULL COMMENT '专业' ;

update basic_exam_student bes set bes.teach_class_name = (select btc.clazz_name from basic_teach_clazz btc where bes.clazz_id = btc.id) where bes.teach_class_name is null;

ALTER TABLE `mark_paper` ADD COLUMN `question_status` TINYINT(1) NULL DEFAULT '0' COMMENT '试卷结构是否提交' AFTER `show_object_score`;
update mark_paper set question_status = group_status;

ALTER TABLE `basic_exam_student`
    ADD COLUMN `status` VARCHAR(1) NULL DEFAULT 'N' COMMENT '状态，默认N-正常，D-缓考，F-免考，M-缺考，B-违纪' AFTER `class_name`,
ADD COLUMN `required_fields` MEDIUMTEXT NULL COMMENT '基础字段' AFTER `exam_room`;

ALTER TABLE `exam_card` ADD COLUMN `copy_card_id` BIGINT(20) NULL COMMENT '复制源题卡ID' AFTER `paper_id`;

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('870', '启用/禁用', 'Enable', 'LINK', '624', '10', 'AUTH', '871', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('871', '启用/禁用', '/api/admin/exam/card/enable', 'URL', '624', '7', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1140', '状态', 'Status', 'LINK', '1100', '10', 'AUTH', '1141', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1141', '状态', '/api/admin/basic/exam_student/status', 'URL', '1100', '6', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1142', '任课老师导入', 'ImportTeacher', 'BUTTON', '1100', '11', 'AUTH', '1143', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1143', '任课老师导入', '/api/admin/basic/exam_student/import_teacher', 'URL', '1100', '7', 'AUTH', '1', '1', '1');

CREATE TABLE `exam_task_whole` (
      `id` BIGINT(20) NOT NULL,
      `semester_id` BIGINT(20) NULL COMMENT '学期ID',
      `exam_id` BIGINT(20) NULL COMMENT '考试ID',
      `course_id` BIGINT(20) NULL,
      `paper_number` VARCHAR(100) NULL,
      `attachment_path` VARCHAR(200) NULL COMMENT '文件地址',
      `status` VARCHAR(45) NULL COMMENT '状态',
      `create_time` BIGINT(20) NULL COMMENT '创建时间',
      PRIMARY KEY (`id`));

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1145', '合并PDF查看', '/api/admin/exam/print/get_whole_pdf', 'URL', '47', '5', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1146', '合并PDF生成', 'CreateWholePdf', 'BUTTON', '47', '5', 'AUTH', '1145,1147', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1147', '合并PDF生成', '/api/admin/exam/print/create_whole_pdf', 'URL', '47', '6', 'AUTH', '1', '1', '1');


ALTER TABLE `mark_student` ADD COLUMN `miss_scan` TINYINT(1) NULL DEFAULT 0 COMMENT '是否漏扫' AFTER `invalid`;
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1148', '更新漏扫状态', '/api/scan/student/missScan/update', 'URL', '970', '39', 'AUTH', '1', '1', '1');
UPDATE `sys_privilege` SET `related` = '971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1007,1009,1010,1148' WHERE (`id` = '1006');
UPDATE `sys_privilege` SET `related` = '971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1007,1009,1010,1148' WHERE (`id` = '970');

UPDATE `basic_school` SET has_paper_number = '0' where has_paper_number is null;
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`) VALUES ('242', '查询条件-课程', '/api/admin/client/course/list', 'URL', '199', '18', 'AUTH');
UPDATE `sys_privilege` SET `related` = '200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,241,242,973,974,975' WHERE (`id` = '199');

ALTER TABLE `mark_student`
DROP COLUMN `teacher`,
DROP COLUMN `teacher_id`,
DROP COLUMN `class_name`,
DROP COLUMN `major_name`,
DROP COLUMN `college`,
DROP COLUMN `student1_name`,
DROP COLUMN `course_name`,
DROP COLUMN `course_code`,
DROP INDEX `index3` ,
ADD INDEX `index3` USING BTREE (`exam_id`, `course_id`, `upload_time`);

UPDATE `sys_privilege` SET `related` = '184,414' WHERE (`id` = '408');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1149', '评卷设置（批量）', 'BatchMarkConfig', 'BUTTON', '897', '8', 'AUTH', '1150', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1150', '评卷设置保存', '/api/admin/mark/setting/paper/batch_save', 'URL', '897', '19', 'AUTH', '1', '1', '1');

UPDATE `sys_privilege` SET `related` = '490,543,955,959,1139' WHERE (`id` = '488');

-- drop table if exists exam_detail_course_paper_type;
-- drop table if exists basic_template_org;
-- drop table if exists cloud_user_push_status;
-- drop table if exists exam_card_detail;
-- drop table if exists exam_paper_class_marker;
-- drop table if exists exam_paper_group;
-- drop table if exists exam_paper_group_marker;
-- drop table if exists exam_paper_structure;
-- drop table if exists exam_task_sync;
-- drop table if exists t_sync_exam_log;
-- drop table if exists t_sync_stmms_exam;
-- drop table if exists basic_college;
-- drop table if exists basic_campus;

-- delete脚本需要加上新增的表

-- 升级说明
-- 1. 将通用规则中，教学班改为teachClassName,班级改为className

-- 2024.06.24update*******************3.3.4->3.4.0升级脚本
/******************** Update Table: course_dimension ************************/

/* Remove Indexes */
DROP INDEX course_dimension_unique ON course_dimension;

ALTER TABLE course_dimension DROP COLUMN teach_course_id;

ALTER TABLE course_dimension DROP COLUMN semester_id;

ALTER TABLE course_dimension DROP COLUMN exam_id;

ALTER TABLE course_dimension DROP COLUMN course_id;

ALTER TABLE course_dimension DROP COLUMN course_code;

ALTER TABLE course_dimension DROP COLUMN user_id;

ALTER TABLE course_dimension ADD obe_course_outline_id BIGINT
    COMMENT '课程大纲id' NOT NULL after id;

/* Add Indexes */
CREATE UNIQUE INDEX course_dimension_unique ON course_dimension (obe_course_outline_id, source, code) USING BTREE;


/******************** Update Table: course_evaluation ************************/

ALTER TABLE course_evaluation CHANGE COLUMN course_code course_code VARCHAR(20)
    COMMENT '课程编号' NULL;

ALTER TABLE course_evaluation DROP COLUMN course_id;


/******************** Update Table: course_target ************************/

ALTER TABLE course_target CHANGE COLUMN course_code course_code VARCHAR(20)
    COMMENT '课程编号' NULL;

ALTER TABLE course_target DROP COLUMN course_id;


/******************** Update Table: course_weight ************************/

ALTER TABLE course_weight CHANGE COLUMN course_code course_code VARCHAR(20)
    COMMENT '课程编号' NULL;

ALTER TABLE course_weight DROP COLUMN course_id;

/******************** Add Table: obe_course_dimension ************************/

/* Build Table Structure */

DROP TABLE IF EXISTS `obe_course_dimension`;
CREATE TABLE obe_course_dimension
(
    id BIGINT
        COMMENT 'id' NOT NULL,
    obe_course_outline_id BIGINT
        COMMENT '课程大纲id' NOT NULL,
    school_id BIGINT
        COMMENT '学校id' NOT NULL,
    source VARCHAR(20)
        COMMENT '来源' NOT NULL,
    code VARCHAR(32)
        COMMENT '知识点编号' NOT NULL,
    name VARCHAR(255)
        COMMENT '知识点名称' NOT NULL,
    number INTEGER
        COMMENT '排序' NOT NULL,
    parent_id BIGINT
        COMMENT '父id（顶级知识点父id为0）' NOT NULL,
    create_id BIGINT
        COMMENT '创建人' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程知识点表';

/* Add Primary Key */
ALTER TABLE obe_course_dimension ADD CONSTRAINT pkobe_course_dimension
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_dimension COMMENT = '课程知识点表';

/* Add Indexes */
CREATE UNIQUE INDEX course_dimension_unique ON obe_course_dimension (obe_course_outline_id, source, code) USING BTREE;


/******************** Add Table: obe_course_evaluation ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_evaluation`;
CREATE TABLE obe_course_evaluation
(
    id BIGINT
        COMMENT 'id' NOT NULL,
    obe_course_outline_id BIGINT
        COMMENT '教学课程id' NOT NULL,
    school_id BIGINT
        COMMENT '学校id' NOT NULL,
    type VARCHAR(20)
        COMMENT '类型（默认DEFAULT、自定义CUSTOM）' NOT NULL,
    evaluation VARCHAR(255)
        COMMENT '评价方式' NOT NULL,
    evaluation_desc VARCHAR(255)
        COMMENT '评价方式描述' NULL,
    create_id BIGINT
        COMMENT '创建人id' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人id' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程大纲评价方式表';

/* Add Primary Key */
ALTER TABLE obe_course_evaluation ADD CONSTRAINT pkobe_course_evaluation
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_evaluation COMMENT = '课程大纲评价方式表';

/* Add Indexes */
CREATE UNIQUE INDEX course_evaluation_unique ON obe_course_evaluation (obe_course_outline_id, evaluation) USING BTREE;


/******************** Add Table: obe_course_outline ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_outline`;
CREATE TABLE obe_course_outline
(
    id BIGINT
        COMMENT '课程大纲表id' NOT NULL,
    culture_program_id BIGINT
        COMMENT '培养方案id' NOT NULL,
    school_id BIGINT
        COMMENT '学校id' NOT NULL,
    name VARCHAR(255)
        COMMENT '课程大纲名称' NOT NULL,
    semester_id BIGINT
        COMMENT '修读学期id' NOT NULL,
    course_id BIGINT NOT NULL,
    evaluation_mode VARCHAR(32)
        COMMENT '考核方式' NOT NULL,
    course_type VARCHAR(32)
        COMMENT '课程类别' NOT NULL,
    user_id BIGINT
        COMMENT '课程大纲责任人' NOT NULL,
    weight_setting TINYINT
        COMMENT '权重设置' NULL,
    weight_setting_sign BIGINT
        COMMENT '课程权重设置标识' NULL,
    dimension_sign BIGINT
        COMMENT '知识点标识' NULL,
    create_id BIGINT
        COMMENT '创建人' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程大纲表';

/* Add Primary Key */
ALTER TABLE obe_course_outline ADD CONSTRAINT pkobe_course_outline
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_outline COMMENT = '课程大纲表';

/* Add Indexes */
CREATE UNIQUE INDEX obe_course_outline_unique ON obe_course_outline (culture_program_id, name) USING BTREE;


/******************** Add Table: obe_course_requirement_dimension ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_requirement_dimension`;
CREATE TABLE obe_course_requirement_dimension
(
    id BIGINT
        COMMENT 'id' NOT NULL,
    obe_course_outline_id BIGINT
        COMMENT '课程大纲id' NOT NULL,
    obe_culture_program_requirement_id BIGINT
        COMMENT '指标id' NOT NULL,
    obe_course_dimension_id BIGINT
        COMMENT '知识点id' NOT NULL,
    school_id BIGINT
        COMMENT '学校id' NOT NULL,
    create_id BIGINT
        COMMENT '创建人' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程大纲指标与知识点关联表';

/* Add Primary Key */
ALTER TABLE obe_course_requirement_dimension ADD CONSTRAINT pkobe_course_requirement_dimension
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_requirement_dimension COMMENT = '课程大纲指标与知识点关联表';

/* Add Indexes */
CREATE UNIQUE INDEX obe_course_requirement_dimension_unique ON obe_course_requirement_dimension (obe_course_outline_id, obe_culture_program_requirement_id, obe_course_dimension_id) USING BTREE;


/******************** Add Table: obe_course_requirement_matrix ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_requirement_matrix`;
CREATE TABLE obe_course_requirement_matrix
(
    id BIGINT NOT NULL,
    culture_program_id BIGINT
        COMMENT '培养方案ID' NOT NULL,
    obe_course_id BIGINT
        COMMENT '专业认证课程ID' NOT NULL,
    obe_requirement_id BIGINT
        COMMENT '专业认证毕业要求ID' NOT NULL,
    obe_sub_requirement_id BIGINT
        COMMENT '子节点名称' NULL,
    scale DOUBLE
        COMMENT '指标值' NULL,
    intensity VARCHAR(1)
        COMMENT '强度' NULL,
    create_id BIGINT
        COMMENT '创建人ID' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人ID' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='培养方案毕业要求支撑矩阵';

/* Add Primary Key */
ALTER TABLE obe_course_requirement_matrix ADD CONSTRAINT pkobe_course_requirement_matrix
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_requirement_matrix COMMENT = '培养方案毕业要求支撑矩阵';

/* Add Indexes */
CREATE INDEX uq_professional_id ON obe_course_requirement_matrix (culture_program_id) USING BTREE;


/******************** Add Table: obe_course_requirement_report ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_requirement_report`;
CREATE TABLE obe_course_requirement_report
(
    id BIGINT
        COMMENT '主键' NOT NULL,
    culture_program_id BIGINT UNIQUE
        COMMENT '培养方案id' NOT NULL,
    requirement_radar MEDIUMTEXT
        COMMENT '毕业要求雷达报表' NOT NULL,
    requirement_detail MEDIUMTEXT
        COMMENT '毕业要求详情报表' NOT NULL,
    requirement_degree DOUBLE
        COMMENT '毕业要求达成值' NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程毕业要求报表';

/* Add Primary Key */
ALTER TABLE obe_course_requirement_report ADD CONSTRAINT pkobe_course_requirement_report
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_requirement_report COMMENT = '课程毕业要求报表';

/* Add Indexes */
CREATE UNIQUE INDEX obe_course_requirement_report_unique ON obe_course_requirement_report (culture_program_id) USING BTREE;


/******************** Add Table: obe_course_target ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_target`;
CREATE TABLE obe_course_target
(
    id BIGINT
        COMMENT 'id' NOT NULL,
    obe_course_outline_id BIGINT
        COMMENT '教学课程id' NOT NULL,
    obe_culture_program_requirement_id BIGINT
        COMMENT '培养方案毕业要求指标点id' NULL,
    school_id BIGINT
        COMMENT '学校id' NOT NULL,
    target_name VARCHAR(50)
        COMMENT '课程目标' NULL,
    degree_requirement VARCHAR(255)
        COMMENT '毕业要求' NULL,
    total_weight DECIMAL(6, 2)
        COMMENT '课程目标整体权重' NULL,
    expect_value DOUBLE
        COMMENT '预期值' NULL,
    create_id BIGINT
        COMMENT '创建人' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程大纲目标表';

/* Add Primary Key */
ALTER TABLE obe_course_target ADD CONSTRAINT pkobe_course_target
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_target COMMENT = '课程大纲目标表';

/* Add Indexes */
CREATE UNIQUE INDEX course_target_unique ON obe_course_target (obe_course_outline_id, target_name) USING BTREE;


/******************** Add Table: obe_course_weight ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_course_target`;
CREATE TABLE obe_course_weight
(
    id BIGINT
        COMMENT 'id' NOT NULL,
    obe_course_outline_id BIGINT
        COMMENT '课程大纲id' NOT NULL,
    school_id BIGINT
        COMMENT '学校id' NOT NULL,
    target_id BIGINT
        COMMENT '课程目标表id' NOT NULL,
    evaluation_id BIGINT
        COMMENT '课程评价表id' NOT NULL,
    enable TINYINT DEFAULT 1
        COMMENT '使用状态 1:使用 0:未使用' NOT NULL,
    weight DECIMAL(6, 2)
        COMMENT '权重' NULL,
    target_score DECIMAL(6, 2)
        COMMENT '目标分值' NULL,
    create_id BIGINT
        COMMENT '创建人' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程大纲权重设置表';

/* Add Primary Key */
ALTER TABLE obe_course_weight ADD CONSTRAINT pkobe_course_weight
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_course_weight COMMENT = '课程大纲权重设置表';

/* Add Indexes */
CREATE UNIQUE INDEX course_weight_unique ON obe_course_weight (obe_course_outline_id, target_id, evaluation_id) USING BTREE;


/******************** Add Table: obe_culture_program ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_culture_program`;
CREATE TABLE obe_culture_program
(
    id BIGINT NOT NULL,
    school_id BIGINT
        COMMENT '学校ID' NULL,
    professional_id BIGINT
        COMMENT '专业ID' NULL,
    name VARCHAR(100)
        COMMENT '培养方案名称' NULL,
    target_count INTEGER DEFAULT 0
        COMMENT '培养目标数量' NULL,
    requirement_count INTEGER DEFAULT 0
        COMMENT '毕业要求数量' NULL,
    course_count INTEGER DEFAULT 0
        COMMENT '课程体系数量' NULL,
    description MEDIUMTEXT
        COMMENT '总体描述' NULL,
    expect_value DOUBLE NULL,
    create_id BIGINT
        COMMENT '创建人ID' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人ID' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='培养方案';

/* Add Primary Key */
ALTER TABLE obe_culture_program ADD CONSTRAINT pkobe_culture_program
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_culture_program COMMENT = '培养方案';

/* Add Indexes */
CREATE INDEX idx_1 ON obe_culture_program (professional_id, name) USING BTREE;


/******************** Add Table: obe_culture_program_course ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_culture_program_course`;
CREATE TABLE obe_culture_program_course
(
    id BIGINT NOT NULL,
    culture_program_id BIGINT
        COMMENT '培养方案ID' NOT NULL,
    course_id BIGINT
        COMMENT '课程ID' NULL,
    course_code VARCHAR(45)
        COMMENT '课程代码' NULL,
    course_name VARCHAR(100)
        COMMENT '课程名称' NULL,
    sort_num INTEGER
        COMMENT '排序值' NULL,
    create_id BIGINT
        COMMENT '创建人ID' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人ID' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='培养方案课程体系';

/* Add Primary Key */
ALTER TABLE obe_culture_program_course ADD CONSTRAINT pkobe_culture_program_course
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_culture_program_course COMMENT = '培养方案课程体系';


/******************** Add Table: obe_culture_program_requirement ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_culture_program_requirement`;
CREATE TABLE obe_culture_program_requirement
(
    id BIGINT NOT NULL,
    culture_program_id BIGINT
        COMMENT '培养方案ID' NOT NULL,
    code VARCHAR(50)
        COMMENT '毕业要求code' NULL,
    name VARCHAR(50)
        COMMENT '毕业要求' NOT NULL,
    content MEDIUMTEXT
        COMMENT '内容' NULL,
    parent_id BIGINT
        COMMENT '上级毕业要求' NULL,
    sort_num INTEGER
        COMMENT '排序值' NULL,
    expect_value DOUBLE
        COMMENT '预期值' NULL,
    create_id BIGINT
        COMMENT '创建人ID' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人ID' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='培养方案毕业要求';

/* Add Primary Key */
ALTER TABLE obe_culture_program_requirement ADD CONSTRAINT pkobe_culture_program_requirement
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_culture_program_requirement COMMENT = '培养方案毕业要求';

/* Add Indexes */
CREATE INDEX uq_professional_id ON obe_culture_program_requirement (culture_program_id) USING BTREE;

/******************** Add Table: obe_culture_program_target ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_culture_program_target`;
CREATE TABLE obe_culture_program_target
(
    id BIGINT NOT NULL,
    culture_program_id BIGINT
        COMMENT '培养方案ID' NULL,
    name VARCHAR(100)
        COMMENT '培养目标名称' NULL,
    detail VARCHAR(2000)
        COMMENT '目标拆解详情' NULL,
    create_id BIGINT NULL,
    create_time BIGINT NULL,
    update_id BIGINT NULL,
    update_time BIGINT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='培养方案培养目标';

/* Add Primary Key */
ALTER TABLE obe_culture_program_target ADD CONSTRAINT pkobe_culture_program_target
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_culture_program_target COMMENT = '培养方案培养目标';

/* Add Indexes */
CREATE INDEX idx_1 ON obe_culture_program_target (culture_program_id) USING BTREE;


/******************** Add Table: obe_requirement_target_matrix ************************/

/* Build Table Structure */
DROP TABLE IF EXISTS `obe_requirement_target_matrix`;
CREATE TABLE obe_requirement_target_matrix
(
    id BIGINT NOT NULL,
    culture_program_id BIGINT
        COMMENT '培养方案ID' NULL,
    obe_requirement_id BIGINT
        COMMENT '毕业要求ID' NULL,
    obe_target_id BIGINT
        COMMENT '培养目标ID' NULL,
    enable TINYINT
        COMMENT '是否包含' NULL,
    create_id BIGINT NULL,
    create_time BIGINT NULL,
    update_id BIGINT NULL,
    update_time BIGINT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='培养目标与毕业要求关系矩阵';

/* Add Primary Key */
ALTER TABLE obe_requirement_target_matrix ADD CONSTRAINT pkobe_requirement_target_matrix
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE obe_requirement_target_matrix COMMENT = '培养目标与毕业要求关系矩阵';


/******************** Update Table: t_c_final_score ************************/

/* Remove Indexes */
DROP INDEX t_c_final_score_unique ON t_c_final_score;

DROP INDEX t_c_score_end_exam_unique ON t_c_final_score;

ALTER TABLE t_c_final_score CHANGE COLUMN exam_id exam_id BIGINT
    COMMENT '考试id' NULL;

ALTER TABLE t_c_final_score CHANGE COLUMN course_id course_id BIGINT
    COMMENT '课程id' NOT NULL;

ALTER TABLE t_c_final_score DROP COLUMN teach_course_id;

ALTER TABLE t_c_final_score ADD culture_program_id BIGINT
    COMMENT '培养方案id' NULL after id;

/* Add Indexes */
CREATE UNIQUE INDEX t_c_final_score_unique ON t_c_final_score (culture_program_id, course_id, name, student_code) USING BTREE;


/******************** Remove Table: t_c_paper_positive ************************/
DROP TABLE IF EXISTS t_c_paper_positive;

/******************** Update Table: t_c_paper_struct ************************/

/* Remove Indexes */
DROP INDEX t_c_paper_struct_unique ON t_c_paper_struct;

ALTER TABLE t_c_paper_struct CHANGE COLUMN exam_id exam_id BIGINT
    COMMENT '考试id' NULL;

ALTER TABLE t_c_paper_struct CHANGE COLUMN course_id course_id BIGINT
    COMMENT '课程id' NOT NULL;

ALTER TABLE t_c_paper_struct CHANGE COLUMN course_code course_code VARCHAR(100)
    COMMENT '课程编码' NULL;

ALTER TABLE t_c_paper_struct DROP COLUMN teach_course_id;

ALTER TABLE t_c_paper_struct ADD culture_program_id BIGINT
    COMMENT '培养方案id' NOT NULL after id;

/* Add Indexes */
CREATE UNIQUE INDEX t_c_paper_struct_unique ON t_c_paper_struct (culture_program_id, course_id) USING BTREE;

/******************** Remove Table: t_c_score_end_exam ************************/
DROP TABLE IF EXISTS t_c_score_end_exam;

/******************** Remove Table: t_c_score_normal ************************/
DROP TABLE IF EXISTS t_c_score_normal;

/******************** Update Table: t_c_usual_score ************************/

/* Remove Indexes */
DROP INDEX t_c_usual_score_unique ON t_c_usual_score;

ALTER TABLE t_c_usual_score CHANGE COLUMN exam_id exam_id BIGINT
    COMMENT '考试id' NULL;

ALTER TABLE t_c_usual_score CHANGE COLUMN course_id course_id BIGINT
    COMMENT '课程id' NOT NULL;

ALTER TABLE t_c_usual_score DROP COLUMN teach_course_id;

ALTER TABLE t_c_usual_score ADD culture_program_id BIGINT
    COMMENT '培养方案id' NOT NULL after id;

/* Add Indexes */
CREATE UNIQUE INDEX t_c_usual_score_unique ON t_c_usual_score (culture_program_id, course_id, name, student_code) USING BTREE;

/******************** Update Table: t_p_course ************************/

ALTER TABLE t_p_course DROP COLUMN course_id;

/******************** Update Table: t_p_requirement ************************/

/* Remove Indexes */
DROP INDEX uq_professional_id ON t_p_requirement;

ALTER TABLE t_p_requirement CHANGE COLUMN code code VARCHAR(45)
    COMMENT '毕业要求code' NULL;


/******************** Update Table: t_r_basic_info ************************/

/* Remove Indexes */
DROP INDEX t_r_basic_info_unique ON t_r_basic_info;

ALTER TABLE t_r_basic_info CHANGE COLUMN exam_id exam_id BIGINT
    COMMENT '考试id' NULL;

ALTER TABLE t_r_basic_info CHANGE COLUMN course_id course_id BIGINT
    COMMENT '课程id' NOT NULL;

ALTER TABLE t_r_basic_info CHANGE COLUMN course_code course_code VARCHAR(100)
    COMMENT '课程编码' NULL;

ALTER TABLE t_r_basic_info DROP COLUMN teach_course_id;

ALTER TABLE t_r_basic_info ADD culture_program_id BIGINT
    COMMENT '培养方案id' NOT NULL after id;

ALTER TABLE t_r_basic_info ADD course_en_name VARCHAR(100)
    COMMENT '课程英文名称' NULL after course_name;

ALTER TABLE t_r_basic_info ADD college VARCHAR(200)
    COMMENT '开课学院' NULL after period;

ALTER TABLE t_r_basic_info ADD profession VARCHAR(200)
    COMMENT '开课专业' NULL after college;

ALTER TABLE t_r_basic_info ADD finish_points MEDIUMTEXT
    COMMENT '达成情况' NULL after participant;

ALTER TABLE t_r_basic_info ADD requirement_points MEDIUMTEXT
    COMMENT '课程支撑毕业要求达成情况评价' NULL after finish_points;

ALTER TABLE t_r_basic_info ADD course_suggest MEDIUMTEXT
    COMMENT '课程持续改进' NULL after requirement_points;

/* Add Indexes */
CREATE UNIQUE INDEX t_r_basic_info_unique ON t_r_basic_info (culture_program_id, course_id) USING BTREE;

/******************** Update Table: teach_clazz ************************/

/******************** Update Table: teach_course ************************/

ALTER TABLE teach_course CHANGE COLUMN course_code course_code VARCHAR(20)
    COMMENT '课程编号' NULL;

ALTER TABLE teach_course DROP COLUMN course_id;

/* Build Table Structure */
DROP TABLE IF EXISTS `basic_professional`;
CREATE TABLE basic_professional
(
    id BIGINT NOT NULL,
    school_id BIGINT
        COMMENT '学校ID' NULL,
    name VARCHAR(80)
        COMMENT '专业名称' NULL,
    org_id BIGINT
        COMMENT '所属学院ID' NULL,
    create_id BIGINT
        COMMENT '创建人ID' NULL,
    create_time BIGINT
        COMMENT '创建时间' NULL,
    update_id BIGINT
        COMMENT '更新人ID' NULL,
    update_time BIGINT
        COMMENT '更新时间' NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='专业表';

/* Add Primary Key */
ALTER TABLE basic_professional ADD CONSTRAINT pkbasic_professional
    PRIMARY KEY (id);

/* Add Comments */
ALTER TABLE basic_professional COMMENT = '专业表';

DROP TABLE IF EXISTS t_p_course;

DROP TABLE IF EXISTS t_p_matrix;

DROP TABLE IF EXISTS t_p_professional;

DROP TABLE IF EXISTS t_p_requirement;

DROP TABLE IF EXISTS course_dimension;

DROP TABLE IF EXISTS course_evaluation;

DROP TABLE IF EXISTS course_target;

DROP TABLE IF EXISTS course_weight;

ALTER TABLE teach_course ADD course_id BIGINT NULL COMMENT '课程id';