api/src/main/resources/sqlScript/20240604_sql.sql

-- ------------------------------------------------
-- 班级课程
-- ------------------------------------------------
DROP TABLE IF EXISTS base_class_course;
CREATE TABLE `base_class_course` (
  `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,
  `class_id` BIGINT DEFAULT NULL COMMENT '班级id(base_class)',
  `course_id` BIGINT DEFAULT NULL COMMENT '课程id(base_course_subject)',
  `textbook_id` BIGINT DEFAULT NULL COMMENT '教材id(textbook)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='班级课程';

-- ------------------------------------------------
-- 教师奖项
-- ------------------------------------------------
DROP TABLE IF EXISTS teacher_award_item;
CREATE TABLE `teacher_award_item` (
  `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 '奖项名称',
  `is_thesis` INT DEFAULT NULL COMMENT '是否论文（1：是 0：否）',
  `is_standard` INT DEFAULT NULL COMMENT '是否频分标准（1：是 0：否）',
  `score` DECIMAL(6,2) DEFAULT NULL COMMENT '分数',
  `parent_id` BIGINT DEFAULT NULL COMMENT '父级id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师奖项';


ALTER TABLE `teacher_award`   
  ADD COLUMN `teacher_award_item_id` BIGINT NULL   COMMENT '奖项id(teacher_award_item)' AFTER `status`;
  
ALTER TABLE `xjr_user`   
  CHANGE `avatar` `avatar` MEDIUMBLOB NULL   COMMENT '头像';
  
ALTER TABLE textbook
    ADD use_type INT NULL COMMENT '使用类型(单位：学期)';

ALTER TABLE `base_student_school_roll`   
  ADD INDEX (`learn_status`);
ALTER TABLE `xjr_dictionary_detail`   
  ADD INDEX (`name`, `code`);

DROP TABLE IF EXISTS `base_system_config`;
CREATE TABLE `base_system_config`  (
  `id` BIGINT NOT NULL,
  `code` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '编号',
  `value` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '值',
  `remark` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '说明',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '系统参数配置' ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `base_user_student`;
CREATE TABLE `base_user_student` (
  `id` BIGINT NOT NULL COMMENT '主键',
  `create_date` DATE DEFAULT NULL COMMENT '创建时间',
  `modify_date` DATE DEFAULT NULL COMMENT '修改时间',
  `user_id` BIGINT NOT NULL COMMENT '家长ID(xjr_user)',
  `user_phone` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '家长手机号',
  `student_id` BIGINT NOT NULL COMMENT '学生ID(xjr_user)',
  `student_nane` VARCHAR(255) DEFAULT NULL COMMENT '学生姓名',
  `student_identity` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '学生身份证号',
  `class_id` BIGINT DEFAULT NULL COMMENT '学生班级id（base_class）',
  `teacher_id` BIGINT DEFAULT NULL COMMENT '学生班主任id(xjr_user)',
  `status` INT DEFAULT NULL COMMENT '流程状态（0:审核中,1:通过,2:拒绝）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='家长学生关联表';

ALTER TABLE wf_textbook_subscription_item
    ADD textbook_id BIGINT NULL COMMENT '教材id';
ALTER TABLE wf_textbook_subscription
    ADD subscription_method INT NULL COMMENT '征订方式（1:按班级征订 2:按教材征订）';

ALTER TABLE `jianyue_data`   
  CHANGE `source_id` `source_id` VARCHAR(100) NULL COMMENT '数据id';


DROP TABLE IF EXISTS `teacher_award`;
CREATE TABLE `teacher_award` (
  `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 '申请人',
  `award_type` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '获奖类型',
  `award_level` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '奖项级别',
  `competition_name` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '奖项名称',
  `award_grade` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '获奖等级',
  `award_score` DECIMAL(6,2) DEFAULT NULL COMMENT '奖项分数',
  `paper_name` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '作品名称',
  `issn` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '国际标准刊号字',
  `media_type` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '媒体类型（期刊、图书、报纸、音像、论文集）',
  `journal_name` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '媒体名称',
  `journal_number` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '国内统一刊号CN',
  `journal_level` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '期刊级别（核心期刊、一般期刊、(校级、内部论文)）',
  `issue_date` DATE DEFAULT NULL COMMENT '发表时间',
  `approval_user_id` BIGINT DEFAULT NULL COMMENT '审核人',
  `remark` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '备注',
  `file_id` BIGINT DEFAULT NULL COMMENT '附件文件id',
  `status` INT NOT NULL DEFAULT '0' COMMENT '状态（1:结束 0:未结束）',
  `teacher_award_item_id` BIGINT DEFAULT NULL COMMENT '奖项id(teacher_award_item)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师获奖登记';


ALTER TABLE teacher_award
    ADD whole_competition_name VARCHAR(256) NULL COMMENT '奖项名称全称';