wf-platform-equipment-automationV2/docs/migration-multi-schedule-rules.sql

-- ============================================================
-- 智能轮灌系统V2 - 多定时规则支持
-- 数据库迁移脚本
-- 版本：1.0
-- 日期：2026-03-02
-- 说明：支持一个任务配置多个定时规则（模式A和模式B可共存）
-- ============================================================

-- ============================================================
-- 1. 创建任务定时规则表
-- ============================================================
CREATE TABLE IF NOT EXISTS `wfauto_v2_task_schedule_rule` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '规则ID',
  `task_id` BIGINT NOT NULL COMMENT '关联的任务ID',
  `rule_name` VARCHAR(100) COMMENT '规则名称（用于标识和管理）',
  `schedule_type` VARCHAR(20) NOT NULL COMMENT '定时类型：CRON / SIMPLE',

  -- CRON 模式字段
  `cron_expression` VARCHAR(100) COMMENT 'Cron表达式（schedule_type=CRON时必填）',

  -- SIMPLE 模式字段
  `start_time` DATETIME COMMENT '起始时间（schedule_type=SIMPLE时必填）',
  `interval_days` INT COMMENT '执行间隔天数（schedule_type=SIMPLE时必填）',
  `total_times` INT COMMENT '执行总次数（schedule_type=SIMPLE时必填）',
  `executed_count` INT DEFAULT 0 COMMENT '已执行次数',

  -- 状态控制
  `enabled` TINYINT(1) DEFAULT 1 COMMENT '是否启用：1启用 0禁用',
  `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '规则状态：ACTIVE活跃 / COMPLETED已完成 / DISABLED已禁用',

  -- Quartz 调度关联
  `quartz_job_name` VARCHAR(100) COMMENT 'Quartz Job名称（自动生成）',
  `quartz_trigger_name` VARCHAR(100) COMMENT 'Quartz Trigger名称（自动生成）',

  -- 审计字段
  `tenant_id` BIGINT COMMENT '租户ID',
  `created_by` BIGINT COMMENT '创建人ID',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_by` BIGINT COMMENT '更新人ID',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` TINYINT(1) DEFAULT 0 COMMENT '逻辑删除：0未删除 1已删除',

  PRIMARY KEY (`id`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_enabled_status` (`enabled`, `status`),
  KEY `idx_quartz_job` (`quartz_job_name`),
  KEY `idx_deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务定时规则表';

-- ============================================================
-- 2. 调整 irrigation_task 表（删除定时相关字段）
-- ============================================================
-- 注意：以下操作会删除旧的定时配置字段
-- 建议先备份数据，或使用下面的数据迁移脚本

ALTER TABLE `wfauto_v2_irrigation_task`
  DROP COLUMN IF EXISTS `schedule_type`,
  DROP COLUMN IF EXISTS `cron_expression`,
  DROP COLUMN IF EXISTS `start_time`,
  DROP COLUMN IF EXISTS `interval_days`,
  DROP COLUMN IF EXISTS `total_times`,
  DROP COLUMN IF EXISTS `executed_count`;

-- ============================================================
-- 3. 数据迁移（将旧的定时配置迁移到新表）
-- ============================================================
-- 为每个已有定时配置的任务创建对应的定时规则

INSERT INTO `wfauto_v2_task_schedule_rule` (
  `task_id`,
  `rule_name`,
  `schedule_type`,
  `cron_expression`,
  `start_time`,
  `interval_days`,
  `total_times`,
  `executed_count`,
  `enabled`,
  `status`,
  `quartz_job_name`,
  `quartz_trigger_name`,
  `tenant_id`,
  `created_at`,
  `deleted`
)
SELECT
  t.`id` AS task_id,
  CONCAT('迁移规则-', t.`task_name`) AS rule_name,
  t.`schedule_type`,
  t.`cron_expression`,
  t.`start_time`,
  t.`interval_days`,
  t.`total_times`,
  IFNULL(t.`executed_count`, 0) AS executed_count,
  t.`enabled`,
  CASE
    WHEN t.`schedule_type` = 'SIMPLE' AND IFNULL(t.`executed_count`, 0) >= IFNULL(t.`total_times`, 0) AND t.`total_times` > 0
      THEN 'COMPLETED'
    ELSE 'ACTIVE'
  END AS status,
  NULL AS quartz_job_name,  -- 将在应用启动时自动填充
  NULL AS quartz_trigger_name,  -- 将在应用启动时自动填充
  t.`tenant_id`,
  NOW() AS created_at,
  0 AS deleted
FROM `wfauto_v2_irrigation_task` t
WHERE t.`schedule_type` IS NOT NULL
  AND t.`deleted` = 0;

-- 更新迁移后的规则，填充 Quartz 相关字段
UPDATE `wfauto_v2_task_schedule_rule` r
SET
  r.`quartz_job_name` = CONCAT('TASK_', r.`task_id`, '_RULE_', r.`id`),
  r.`quartz_trigger_name` = CONCAT('TRIGGER_', r.`task_id`, '_RULE_', r.`id`)
WHERE r.`quartz_job_name` IS NULL;

-- ============================================================
-- 4. 验证迁移结果
-- ============================================================
-- 检查迁移的规则数量
SELECT
  COUNT(*) AS total_rules,
  SUM(CASE WHEN schedule_type = 'CRON' THEN 1 ELSE 0 END) AS cron_rules,
  SUM(CASE WHEN schedule_type = 'SIMPLE' THEN 1 ELSE 0 END) AS simple_rules,
  SUM(CASE WHEN enabled = 1 THEN 1 ELSE 0 END) AS enabled_rules,
  SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_rules,
  SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS completed_rules
FROM `wfauto_v2_task_schedule_rule`
WHERE deleted = 0;

-- 检查每个任务的规则数量
SELECT
  t.id AS task_id,
  t.task_name,
  COUNT(r.id) AS rule_count
FROM `wfauto_v2_irrigation_task` t
LEFT JOIN `wfauto_v2_task_schedule_rule` r ON r.task_id = t.id AND r.deleted = 0
WHERE t.deleted = 0
GROUP BY t.id, t.task_name
ORDER BY rule_count DESC;

-- ============================================================
-- 5. 回滚脚本（如果迁移失败需要回滚）
-- ============================================================
/*
-- 删除定时规则表
DROP TABLE IF EXISTS `wfauto_v2_task_schedule_rule`;

-- 恢复 irrigation_task 表的定时字段
ALTER TABLE `wfauto_v2_irrigation_task`
  ADD COLUMN `schedule_type` VARCHAR(20) COMMENT '定时类型：CRON / SIMPLE',
  ADD COLUMN `cron_expression` VARCHAR(100) COMMENT 'Cron表达式',
  ADD COLUMN `start_time` DATETIME COMMENT '起始时间',
  ADD COLUMN `interval_days` INT COMMENT '执行间隔天数',
  ADD COLUMN `total_times` INT COMMENT '执行总次数',
  ADD COLUMN `executed_count` INT DEFAULT 0 COMMENT '已执行次数';
*/

-- ============================================================
-- 6. 使用示例
-- ============================================================
/*
-- 示例1：查询任务的所有定时规则
SELECT * FROM `wfauto_v2_task_schedule_rule`
WHERE task_id = 1 AND deleted = 0;

-- 示例2：为任务添加新的定时规则
INSERT INTO `wfauto_v2_task_schedule_rule` (
  task_id, rule_name, schedule_type, cron_expression,
  enabled, status, tenant_id, created_at
) VALUES (
  1, '每周一三五凌晨2点', 'CRON', '0 0 2 ? * MON,WED,FRI',
  1, 'ACTIVE', 1, NOW()
);

-- 示例3：禁用某个规则
UPDATE `wfauto_v2_task_schedule_rule`
SET enabled = 0, status = 'DISABLED', updated_at = NOW()
WHERE id = 1;

-- 示例4：标记SIMPLE规则为已完成
UPDATE `wfauto_v2_task_schedule_rule`
SET status = 'COMPLETED', enabled = 0, updated_at = NOW()
WHERE id = 2 AND schedule_type = 'SIMPLE' AND executed_count >= total_times;
*/