USE teachcloud_db;

-- 2025-04-10
ALTER TABLE `mark_paper` ADD COLUMN `ai_mark` VARCHAR(12) NULL DEFAULT 'NONE' COMMENT 'AI评卷（NONE：不开启，AI_ONLY：仅AI评卷，MAN_MACHINE：人机双评）' AFTER `archive`;

ALTER TABLE `mark_question` ADD COLUMN `ai_mark` VARCHAR(12) NULL DEFAULT 'NONE' COMMENT 'AI评卷（NONE：不开启，AI_ONLY：仅AI评卷，MAN_MACHINE：人机双评）' AFTER `left_count`;

ALTER TABLE `mark_task` ADD COLUMN `ai_marked` TINYINT(1) NULL DEFAULT 0 COMMENT '是否AI评卷' AFTER `reject_reason`;


DROP TABLE IF EXISTS `mark_ocr_student_question`;
CREATE TABLE `mark_ocr_student_question` (
    `id` BIGINT(20) NOT NULL,
    `exam_id` BIGINT(20) NULL COMMENT '考试ID',
    `paper_number` VARCHAR(100) NULL COMMENT '试卷编号',
    `student_id` BIGINT(20) NULL COMMENT '考生ID',
    `question_id` BIGINT(20) NULL COMMENT '题目ID',
    `number` INT(1) NULL COMMENT '图片序号',
    `md5` VARCHAR(45) NULL COMMENT '图片md5',
    `ocr_content` MEDIUMTEXT NULL COMMENT 'ocr识别内容',
    `create_time` BIGINT(20) NULL COMMENT '识别时间',
    PRIMARY KEY (`id`))
    COMMENT = '考生主观题小题OCR识别结果';

DROP TABLE IF EXISTS `mark_ai_question_param`;
CREATE TABLE `mark_ai_question_param` (
                                          `id` bigint NOT NULL COMMENT '主键',
                                          `question_id` bigint NOT NULL COMMENT '题目id',
                                          `exam_id` bigint NOT NULL COMMENT '考试id',
                                          `course_id` bigint NOT NULL COMMENT '科目id',
                                          `paper_number` varchar(100) NOT NULL COMMENT '试卷类型',
                                          `mode` varchar(30) NOT NULL COMMENT '评分模式，POINT：按得分点，LEVEL：按档次',
                                          `main_title` varchar(1000) NOT NULL COMMENT '试题题干',
                                          `min_score` double NOT NULL COMMENT '最小分',
                                          `create_id` bigint DEFAULT NULL COMMENT '创建人id',
                                          `create_time` bigint DEFAULT NULL COMMENT '创建时间',
                                          `update_id` bigint DEFAULT NULL COMMENT '更新人id',
                                          `update_time` bigint DEFAULT NULL COMMENT '更新时间',
                                          PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI评卷参数表';

DROP TABLE IF EXISTS `mark_ai_question_point`;
CREATE TABLE `mark_ai_question_point` (
                                          `id` bigint NOT NULL COMMENT '主键',
                                          `ai_question_id` bigint NOT NULL COMMENT 'AI评卷参数id',
                                          `score` double NOT NULL COMMENT '分值',
                                          `answer` varchar(1000) NOT NULL COMMENT '标答',
                                          PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI评卷得分明细表';

DROP TABLE IF EXISTS `mark_ai_question_level`;
CREATE TABLE `mark_ai_question_level` (
                                          `id` bigint NOT NULL COMMENT '主键',
                                          `ai_question_id` bigint NOT NULL COMMENT 'AI评卷参数id',
                                          `min_score` double NOT NULL COMMENT '最小分值',
                                          `max_score` double NOT NULL COMMENT '最大分值',
                                          `answer` varchar(1000) NOT NULL COMMENT '标答',
                                          PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI评卷得分明细表';

ALTER TABLE `mark_question` ADD COLUMN `person_task` TINYINT(1) NULL DEFAULT 1 COMMENT '是否有人评任务' AFTER `ai_mark`;

ALTER TABLE mark_ai_question_param ADD CONSTRAINT mark_ai_question_param_unique UNIQUE KEY (question_id);

INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1222, 'AI评卷参数新增/修改', '/api/admin/mark/ai_question_param/save', 'URL', 897, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1223, 'AI评卷参数查询', '/api/admin/mark/ai_question_param/info', 'URL', 897, 1, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1224, 'AI智能评卷设置查询', '/api/admin/set/ai/mark/set/select', 'URL', 508, 21, 'AUTH', NULL, 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1225, 'AI智能评卷设置保存', '/api/admin/set/ai/mark/set/save', 'URL', 508, 22, 'AUTH', NULL, 1, 0, 1);

-- 2025-04-14
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1226, 'AI智能评卷导出', '/api/admin/mark/setting/ai/mark/export', 'URL', 917, 3, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1227, 'AI智能评卷统计', 'Export', 'BUTTON', 917, 4, 'AUTH', '1226', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1228, '获取AI智能评卷任务数量', '/api/admin/mark/inspected/ai/mark/getTask/count', 'URL', 946, 6, 'AUTH', NULL, 1, 1, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1229, 'AI智能评卷待复核', 'InspectedAiMark', 'LINK', 946, 9, 'AUTH', '1228', 1, 0, 1);
UPDATE `sys_privilege` SET `related` = '953,954,955,969,1139,1228' WHERE (`id` = '950');

-- 2025-04-15
ALTER TABLE `mark_task` ADD COLUMN `ai_mark_error_msg` VARCHAR(2000) NULL COMMENT 'AI评卷错误信息' AFTER `ai_marked`;
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1230, '校验', 'Verify', 'BUTTON', 47, 3, 'AUTH', '1231', 1, 0, 1);
INSERT INTO sys_privilege
(id, name, url, `type`, parent_id, `sequence`, property, related, enable, default_auth, front_display)
VALUES(1231, '校验code', '/api/admin/exam/print/package_code/verify', 'URL', 47, 10, 'AUTH', NULL, 1, 1, 1);
UPDATE sys_privilege
SET name='AI智能评卷统计', url='ExportAIStats', `type`='BUTTON', parent_id=917, `sequence`=4, property='AUTH', related='1226', enable=1, default_auth=0, front_display=1
WHERE id=1227;
ALTER TABLE basic_school ADD system_logo MEDIUMTEXT NULL COMMENT '系统logo';
ALTER TABLE basic_school ADD tab_name varchar(200) NULL COMMENT '标签标题';
ALTER TABLE `mark_question`
    ADD COLUMN `clear_ocr_result` TINYINT(1) NULL DEFAULT 0 COMMENT '是否需要重新OCR识别' AFTER `person_task`,
ADD COLUMN `enable_ai` TINYINT(1) NULL DEFAULT 1 COMMENT '是否开启/关闭AI评卷任务' AFTER `clear_ocr_result`;


INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1232', '开启/终止智能评卷', 'EnableAiMark', 'LINK', '917', '10', 'AUTH', '1233', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1233', '开启/终止智能评卷', '/api/admin/mark/question/subjective/ai/enable', 'URL', '917', '28', 'AUTH', NULL, '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1234', '重置AI智能评卷任务', 'ResetAiMarkTask', 'LINK', '917', '10', 'AUTH', '1235', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1235', '重置AI智能评卷任务', '/api/admin/mark/question/subjective/ai/reset', 'URL', '917', '29', 'AUTH', '1', '1', '1');

UPDATE sys_privilege
SET name='评卷设置', url='MarkSetting', `type`='MENU', parent_id=486, `sequence`=2, property='AUTH', related='1222,1223', enable=1, default_auth=0, front_display=1
WHERE id=897;

-- 2025-04-16
ALTER TABLE `mark_ocr_student_question`
    ADD COLUMN `image_width` INT NULL DEFAULT 0 COMMENT '图片宽度' AFTER `md5`,
ADD COLUMN `image_height` INT NULL DEFAULT 0 COMMENT '图片高度' AFTER `image_width`;

-- 2025-04-17
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('1236', '查看任务轨迹', 'MarkTaskTrack', 'LINK', '917', '11', 'AUTH', '1237', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('1237', '查看任务轨迹', '/api/admin/mark/task/track', 'URL', '917', '30', 'AUTH', '1', '1', '1');

UPDATE `sys_privilege` SET `name` = '成绩下载（广药）' WHERE (`id` = '545');
UPDATE `sys_privilege` SET `name` = '成绩下载（广药）' WHERE (`id` = '546');
ALTER TABLE `mark_ocr_student_question` ADD INDEX `idx_1` (`exam_id` ASC, `paper_number` ASC, `student_id` ASC, `question_id` ASC);

INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('244', '关联考生', 'AutoRelateStudent', 'BUTTON', '42', '7', 'AUTH', '245', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('245', '关联考生', '/api/admin/exam/task/relate_students', 'URL', '42', '10', 'AUTH', '1', '1', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `related`, `enable`, `default_auth`, `front_display`) VALUES ('246', '下载试卷清单', 'ExportPaperList', 'BUTTON', '42', '8', 'AUTH', '247', '1', '0', '1');
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('247', '下载试卷清单', '/api/admin/exam/task/export_paper_list', 'URL', '42', '11', 'AUTH', '1', '1', '1');

ALTER TABLE `mark_task`
DROP INDEX `index1` ,
ADD INDEX `index1` USING BTREE (`exam_id`, `paper_number`, `question_id`);

ALTER TABLE `mark_task`
    ADD COLUMN `ai_mark_error_type` VARCHAR(10) NULL COMMENT 'AI评卷错误类型' AFTER `ai_mark_error_msg`,
ADD COLUMN `retry_count` INT NULL DEFAULT 0 COMMENT '重试次数' AFTER `ai_mark_error_type`;

ALTER TABLE `mark_task` ADD COLUMN `next_retry_time` BIGINT(20) NULL COMMENT '下一次重试时间' AFTER `retry_count`;
ALTER TABLE `mark_student` ADD COLUMN `ai_mark_abnormal` TINYINT(1) NULL DEFAULT 0 COMMENT 'ai评卷异常' AFTER `version`;
INSERT INTO `sys_privilege` (`id`, `name`, `url`, `type`, `parent_id`, `sequence`, `property`, `enable`, `default_auth`, `front_display`) VALUES ('496', 'AI阅卷（开关）', 'OpenAiMark', 'BUTTON', '897', '4', 'AUTH', '1', '0', '1');
UPDATE `sys_privilege` SET `related` = '67,68' WHERE (`id` = '273');

ALTER TABLE `t_sync_data`
    ADD COLUMN `xn` VARCHAR(45) NULL COMMENT '学年（考务）' AFTER `exam_id`,
ADD COLUMN `xq` VARCHAR(45) NULL COMMENT '学期（考务）' AFTER `xn`;

ALTER TABLE `basic_message` CHANGE COLUMN `mobile_number` `mobile_number` VARCHAR(11) NULL COMMENT '电话号码' ;

INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108680,2,1222,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108681,2,1223,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108682,2,1226,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108683,2,1227,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108684,2,1228,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108685,2,1229,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108686,2,1230,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108687,2,1231,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108688,2,1232,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108689,2,1233,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108690,2,1234,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108691,2,1235,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108692,2,1236,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108693,2,1237,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108694,2,244,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108695,2,245,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108696,2,246,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108697,2,247,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108698,2,897,1,NULL);

INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108699,3,1228,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108700,3,1236,1,NULL);
INSERT INTO `sys_role_privilege` (`id`,`role_id`,`privilege_id`,`enable`,`school_id`) VALUES (1914588152174108701,3,1237,1,NULL);