SET NAMES utf8mb4;

-- 在任何 DDL 之前阻断无法回填租户的历史实例，避免 MySQL DDL 自动提交留下半迁移结构。
CREATE TEMPORARY TABLE `tmp_approval_instance_tenant_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tmp_approval_instance_tenant_guard` (`guard_key`) VALUES (1);

INSERT INTO `tmp_approval_instance_tenant_guard` (`guard_key`)
SELECT 1
FROM `tb_approval_instance` approval
LEFT JOIN `tb_sys_account` applicant ON applicant.`id` = approval.`applicant_id`
WHERE applicant.`id` IS NULL OR applicant.`tenant_id` IS NULL
LIMIT 1;

DROP TEMPORARY TABLE `tmp_approval_instance_tenant_guard`;

ALTER TABLE `tb_approval_instance`
    ADD COLUMN `tenant_id` BIGINT UNSIGNED NULL COMMENT '租户ID' AFTER `id`;

-- 账号使用逻辑删除，历史申请人仍保留在账号表中，可无歧义回填审批实例租户。
UPDATE `tb_approval_instance` approval
JOIN `tb_sys_account` applicant ON applicant.`id` = approval.`applicant_id`
SET approval.`tenant_id` = applicant.`tenant_id`;

ALTER TABLE `tb_approval_instance`
    MODIFY COLUMN `tenant_id` BIGINT UNSIGNED NOT NULL COMMENT '租户ID';

CREATE INDEX `idx_approval_instance_tenant_status`
    ON `tb_approval_instance` (`tenant_id`, `status`, `submitted_at`);
