-- =====================================================
-- 质控任务自动化改造 - 数据库变更脚本
-- 版本: v1.0
-- 日期: 2026-02-03
-- 说明: 支持自动定时任务和手动任务的差异化处理
-- =====================================================

-- =====================================================
-- 第一部分: Quartz调度器表创建
-- =====================================================
-- 注意: 这些是Quartz调度框架所需的表
-- 从Quartz官方获取: https://github.com/quartz-scheduler/quartz/tree/main/docs/dbTables
-- MySQL版本

-- DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
-- DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
-- DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
-- DROP TABLE IF EXISTS QRTZ_LOCKS;
-- DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
-- DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
-- DROP TABLE IF EXISTS QRTZ_SIMPROP_TRIGGERS;
-- DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
-- DROP TABLE IF EXISTS QRTZ_TRIGGERS;
-- DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
-- DROP TABLE IF EXISTS QRTZ_CALENDARS;

CREATE TABLE QRTZ_JOB_DETAILS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  JOB_NAME VARCHAR(190) NOT NULL,
  JOB_GROUP VARCHAR(190) NOT NULL,
  DESCRIPTION VARCHAR(250) NULL,
  JOB_CLASS_NAME VARCHAR(250) NOT NULL,
  IS_DURABLE VARCHAR(1) NOT NULL,
  IS_NONCONCURRENT VARCHAR(1) NOT NULL,
  IS_UPDATE_DATA VARCHAR(1) NOT NULL,
  REQUESTS_RECOVERY VARCHAR(1) NOT NULL,
  JOB_DATA BLOB NULL,
  PRIMARY KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_TRIGGERS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  TRIGGER_NAME VARCHAR(190) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  JOB_NAME VARCHAR(190) NOT NULL,
  JOB_GROUP VARCHAR(190) NOT NULL,
  DESCRIPTION VARCHAR(250) NULL,
  NEXT_FIRE_TIME BIGINT(13) NULL,
  PREV_FIRE_TIME BIGINT(13) NULL,
  PRIORITY INTEGER NULL,
  TRIGGER_STATE VARCHAR(16) NOT NULL,
  TRIGGER_TYPE VARCHAR(8) NOT NULL,
  START_TIME BIGINT(13) NOT NULL,
  END_TIME BIGINT(13) NULL,
  CALENDAR_NAME VARCHAR(190) NULL,
  MISFIRE_INSTR SMALLINT(2) NULL,
  JOB_DATA BLOB NULL,
  PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
    REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME, JOB_NAME, JOB_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_SIMPLE_TRIGGERS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  TRIGGER_NAME VARCHAR(190) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  REPEAT_COUNT BIGINT(7) NOT NULL,
  REPEAT_INTERVAL BIGINT(12) NOT NULL,
  TIMES_TRIGGERED BIGINT(10) NOT NULL,
  PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_CRON_TRIGGERS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  TRIGGER_NAME VARCHAR(190) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  CRON_EXPRESSION VARCHAR(120) NOT NULL,
  TIME_ZONE_ID VARCHAR(80),
  PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_SIMPROP_TRIGGERS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  TRIGGER_NAME VARCHAR(190) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  STR_PROP_1 VARCHAR(512) NULL,
  STR_PROP_2 VARCHAR(512) NULL,
  STR_PROP_3 VARCHAR(512) NULL,
  INT_PROP_1 INT NULL,
  INT_PROP_2 INT NULL,
  LONG_PROP_1 BIGINT NULL,
  LONG_PROP_2 BIGINT NULL,
  DEC_PROP_1 NUMERIC(13,4) NULL,
  DEC_PROP_2 NUMERIC(13,4) NULL,
  BOOL_PROP_1 VARCHAR(1) NULL,
  BOOL_PROP_2 VARCHAR(1) NULL,
  PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_BLOB_TRIGGERS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  TRIGGER_NAME VARCHAR(190) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  BLOB_DATA BLOB NULL,
  PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
  FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_CALENDARS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  CALENDAR_NAME VARCHAR(190) NOT NULL,
  CALENDAR BLOB NOT NULL,
  PRIMARY KEY (SCHED_NAME, CALENDAR_NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  PRIMARY KEY (SCHED_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_FIRED_TRIGGERS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  ENTRY_ID VARCHAR(95) NOT NULL,
  TRIGGER_NAME VARCHAR(190) NOT NULL,
  TRIGGER_GROUP VARCHAR(190) NOT NULL,
  INSTANCE_NAME VARCHAR(190) NOT NULL,
  FIRED_TIME BIGINT(13) NOT NULL,
  SCHED_TIME BIGINT(13) NOT NULL,
  PRIORITY INTEGER NOT NULL,
  STATE VARCHAR(16) NOT NULL,
  JOB_NAME VARCHAR(190) NULL,
  JOB_GROUP VARCHAR(190) NULL,
  IS_NONCONCURRENT VARCHAR(1) NULL,
  REQUESTS_RECOVERY VARCHAR(1) NULL,
  PRIMARY KEY (SCHED_NAME, ENTRY_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_SCHEDULER_STATE (
  SCHED_NAME VARCHAR(120) NOT NULL,
  INSTANCE_NAME VARCHAR(190) NOT NULL,
  LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
  CHECKIN_INTERVAL BIGINT(13) NOT NULL,
  PRIMARY KEY (SCHED_NAME, INSTANCE_NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE QRTZ_LOCKS (
  SCHED_NAME VARCHAR(120) NOT NULL,
  LOCK_NAME VARCHAR(40) NOT NULL,
  PRIMARY KEY (SCHED_NAME, LOCK_NAME)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 第二部分: 质控任务执行记录表
-- =====================================================

CREATE TABLE IF NOT EXISTS `qc_task_execution` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '执行记录ID',
  `task_id` VARCHAR(64) NOT NULL COMMENT '关联任务ID',
  `task_name` VARCHAR(255) NOT NULL COMMENT '任务名称（快照）',
  `execute_no` INT NOT NULL DEFAULT 1 COMMENT '执行编号（第N次执行）',
  `plan_execute_time` DATETIME NOT NULL COMMENT '计划执行时间',
  `actual_execute_time` DATETIME DEFAULT NULL COMMENT '实际执行时间',

  -- 执行结果统计
  `plan_count` INT DEFAULT 0 COMMENT '计划数量',
  `total_count` INT DEFAULT 0 COMMENT '实际检查数量',
  `completed_count` INT DEFAULT 0 COMMENT '已完成数量',
  `pass_count` INT DEFAULT 0 COMMENT '通过数量',
  `fail_count` INT DEFAULT 0 COMMENT '不通过数量',
  `pass_rate` DECIMAL(5,2) DEFAULT 0.00 COMMENT '通过率(%)',

  -- 时间信息
  `start_time` DATETIME DEFAULT NULL COMMENT '开始执行时间',
  `end_time` DATETIME DEFAULT NULL COMMENT '结束执行时间',
  `duration` INT DEFAULT NULL COMMENT '执行耗时(秒)',

  -- 错误信息
  `error_message` TEXT COMMENT '失败原因',
  `stack_trace` TEXT COMMENT '错误堆栈',

  -- 基础字段
  `creator` VARCHAR(64) DEFAULT NULL COMMENT '创建人',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记',

  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_execute_time` (`plan_execute_time`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='质控任务执行记录表';

-- =====================================================
-- 第三部分: 修改现有的质控任务表
-- =====================================================

-- 添加自动任务相关字段到qc_task表
ALTER TABLE `qc_task`
ADD COLUMN `schedule_config` JSON DEFAULT NULL COMMENT '定时配置（自动任务使用）' AFTER `task_type`,
ADD COLUMN `is_enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用：0禁用/1启用' AFTER `schedule_config`,
ADD COLUMN `next_execute_time` DATETIME DEFAULT NULL COMMENT '下次执行时间（自动任务）' AFTER `is_enabled`,
ADD COLUMN `last_execute_time` DATETIME DEFAULT NULL COMMENT '上次执行时间（自动任务）' AFTER `next_execute_time`;

-- schedule_config JSON结构示例:
-- {
--   "scheduleType": "weekly",      // daily/weekly/monthly/custom
--   "cronExpression": "0 0 10 ? * MON",  // Cron表达式
--   "sampleCount": 100,
--   "sampleType": "random"          // random/sequential
-- }

-- =====================================================
-- 第四部分: 修改检查关联表和结果表
-- =====================================================

-- 修改qc_task_exam表，添加execution_id字段
-- 注意: 这个表需要先查看表结构，根据实际情况添加
-- ALTER TABLE `qc_task_exam`
-- ADD COLUMN `execution_id` BIGINT DEFAULT NULL COMMENT '执行记录ID（自动任务必填，手动任务为空）' AFTER `task_id`,
-- ADD INDEX `idx_execution_id` (`execution_id`);

-- 修改qc_task_image_result表，添加execution_id字段
-- 注意: 这个表需要先查看表结构，根据实际情况添加
-- ALTER TABLE `qc_task_image_result`
-- ADD COLUMN `execution_id` BIGINT DEFAULT NULL COMMENT '执行记录ID（自动任务必填）' AFTER `task_id`,
-- ADD INDEX `idx_execution_id` (`execution_id`);

-- =====================================================
-- 第五部分: 创建索引以优化查询性能
-- =====================================================

-- qc_task表索引
CREATE INDEX `idx_task_type` ON `qc_task` (`task_type`);
CREATE INDEX `idx_institution_id` ON `qc_task` (`institution_id`);
CREATE INDEX `idx_is_enabled` ON `qc_task` (`is_enabled`);

-- =====================================================
-- 说明
-- =====================================================
-- 1. 执行本脚本前请备份数据库
-- 2. Quartz表创建语句来自官方，支持MySQL
-- 3. qc_task_execution表用于记录自动任务的每次执行历史
-- 4. schedule_config字段存储JSON格式的定时任务配置
-- 5. execution_id用于关联执行记录与检查数据、质控结果
-- 6. 手动任务的execution_id为NULL，自动任务的execution_id不为NULL
-- =====================================================