SET NAMES utf8mb4;

-- 本次迁移依赖“无在途审批”的上线窗口，先阻断再执行表结构变更和历史回填。
DROP PROCEDURE IF EXISTS `sp_guard_approval_multi_assignee`;
DELIMITER $$
CREATE PROCEDURE `sp_guard_approval_multi_assignee`()
BEGIN
    IF EXISTS (
        SELECT 1
        FROM `tb_approval_instance`
        WHERE `status` IN ('PENDING', 'PROCESSING')
        LIMIT 1
    ) OR EXISTS (
        SELECT 1
        FROM `tb_approval_task`
        WHERE `status` = 'PENDING'
        LIMIT 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'approval multi-assignee migration requires no active instances or pending tasks';
    END IF;
END$$
DELIMITER ;

CALL `sp_guard_approval_multi_assignee`();
DROP PROCEDURE IF EXISTS `sp_guard_approval_multi_assignee`;

CREATE TABLE IF NOT EXISTS `tb_approval_flow_step_assignee` (
    `id` bigint NOT NULL COMMENT '主键',
    `step_id` bigint NOT NULL COMMENT '审批步骤ID',
    `assignee_type` varchar(16) NOT NULL COMMENT '审批对象类型',
    `target_id` bigint NOT NULL COMMENT '审批对象ID',
    `target_code` varchar(128) DEFAULT NULL COMMENT '审批对象编码',
    `target_name` varchar(128) DEFAULT NULL COMMENT '审批对象名称',
    `include_children` tinyint NOT NULL DEFAULT 0 COMMENT '是否包含子部门',
    `created` datetime DEFAULT NULL COMMENT '创建时间',
    `created_by` bigint DEFAULT NULL COMMENT '创建者',
    `modified` datetime DEFAULT NULL COMMENT '修改时间',
    `modified_by` bigint DEFAULT NULL COMMENT '修改者',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_approval_step_assignee` (`step_id`, `assignee_type`, `target_id`),
    KEY `idx_approval_step_assignee_step` (`step_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审批流程步骤对象关联';

CREATE TABLE IF NOT EXISTS `tb_approval_task_assignee` (
    `id` bigint NOT NULL COMMENT '主键',
    `task_id` bigint NOT NULL COMMENT '审批任务ID',
    `assignee_type` varchar(16) NOT NULL COMMENT '审批对象类型',
    `target_id` bigint NOT NULL COMMENT '审批对象ID',
    `target_code` varchar(128) DEFAULT NULL COMMENT '审批对象编码',
    `target_name` varchar(128) DEFAULT NULL COMMENT '审批对象名称',
    `include_children` tinyint NOT NULL DEFAULT 0 COMMENT '是否包含子部门',
    `created` datetime DEFAULT NULL COMMENT '创建时间',
    `created_by` bigint DEFAULT NULL COMMENT '创建者',
    `modified` datetime DEFAULT NULL COMMENT '修改时间',
    `modified_by` bigint DEFAULT NULL COMMENT '修改者',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_approval_task_assignee` (`task_id`, `assignee_type`, `target_id`),
    KEY `idx_approval_task_assignee_target` (`assignee_type`, `target_id`, `task_id`),
    KEY `idx_approval_task_assignee_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='审批任务对象关联';

-- 现有流程配置都是单对象，按“一条旧记录对应一条关联记录”完整回填。
INSERT INTO `tb_approval_flow_step_assignee` (
    `id`, `step_id`, `assignee_type`, `target_id`, `target_code`, `target_name`,
    `include_children`, `created`, `created_by`, `modified`, `modified_by`
)
SELECT
    step.`id`, step.`id`, step.`assignee_type`, step.`assignee_target_id`,
    step.`assignee_target_code`, step.`assignee_target_name`,
    0, step.`created`, step.`created_by`, step.`modified`, step.`modified_by`
FROM `tb_approval_flow_step` step
WHERE step.`assignee_type` IS NOT NULL
  AND step.`assignee_target_id` IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `tb_approval_flow_step_assignee` relation
      WHERE relation.`step_id` = step.`id`
  );

-- 已完成任务同步回填，保证历史详情继续展示原审批对象。
INSERT INTO `tb_approval_task_assignee` (
    `id`, `task_id`, `assignee_type`, `target_id`, `target_code`, `target_name`,
    `include_children`, `created`, `created_by`, `modified`, `modified_by`
)
SELECT
    task.`id`, task.`id`, task.`assignee_type`, task.`assignee_target_id`,
    task.`assignee_target_code`, task.`assignee_target_name`,
    0, task.`created`, task.`created_by`, task.`modified`, task.`modified_by`
FROM `tb_approval_task` task
WHERE task.`assignee_type` IS NOT NULL
  AND task.`assignee_target_id` IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM `tb_approval_task_assignee` relation
      WHERE relation.`task_id` = task.`id`
  );

DROP PROCEDURE IF EXISTS `sp_verify_approval_multi_assignee`;
DELIMITER $$
CREATE PROCEDURE `sp_verify_approval_multi_assignee`()
BEGIN
    IF EXISTS (
        SELECT 1
        FROM `tb_approval_flow_step` step
        LEFT JOIN `tb_approval_flow_step_assignee` relation ON relation.`step_id` = step.`id`
        WHERE step.`assignee_type` IS NOT NULL
          AND step.`assignee_target_id` IS NOT NULL
          AND relation.`id` IS NULL
        LIMIT 1
    ) OR EXISTS (
        SELECT 1
        FROM `tb_approval_task` task
        LEFT JOIN `tb_approval_task_assignee` relation ON relation.`task_id` = task.`id`
        WHERE task.`assignee_type` IS NOT NULL
          AND task.`assignee_target_id` IS NOT NULL
          AND relation.`id` IS NULL
        LIMIT 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'approval multi-assignee backfill verification failed';
    END IF;
END$$
DELIMITER ;

CALL `sp_verify_approval_multi_assignee`();
DROP PROCEDURE IF EXISTS `sp_verify_approval_multi_assignee`;
