SET NAMES utf8mb4;

-- 所有不可逆 DDL 前先检查旧分类同租户同父级重名；命中时用固定主键冲突显式终止迁移。
CREATE TEMPORARY TABLE `tmp_skill_category_migration_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `tmp_skill_category_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_category`
WHERE `tenant_id` IS NULL
LIMIT 1;

INSERT INTO `tmp_skill_category_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill`
WHERE `tenant_id` IS NULL
LIMIT 1;

INSERT INTO `tmp_skill_category_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_category`
GROUP BY `tenant_id`, IFNULL(`parent_id`, 0), `category_name`
HAVING COUNT(1) > 1
LIMIT 1;

DROP TEMPORARY TABLE `tmp_skill_category_migration_guard`;

-- 旧资源必须归属于有效租户 Skill；孤儿记录、空租户或跨租户记录直接终止迁移。
CREATE TEMPORARY TABLE `tmp_skill_resource_owner_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `tmp_skill_resource_owner_guard` (`guard_key`)
SELECT 1
FROM (
    SELECT reference.`skill_id`, reference.`tenant_id`
    FROM `tb_skill_reference` reference
    UNION ALL
    SELECT script.`skill_id`, script.`tenant_id`
    FROM `tb_skill_script` script
    UNION ALL
    SELECT asset.`skill_id`, asset.`tenant_id`
    FROM `tb_skill_asset` asset
) legacy_resource
LEFT JOIN `tb_skill` skill ON skill.`id` = legacy_resource.`skill_id`
WHERE skill.`id` IS NULL
   OR skill.`tenant_id` IS NULL
   OR (legacy_resource.`tenant_id` IS NOT NULL AND legacy_resource.`tenant_id` <> skill.`tenant_id`)
LIMIT 1;

DROP TEMPORARY TABLE `tmp_skill_resource_owner_guard`;

-- V24 发布快照和迁移前尚未结束的发布审批使用 assets[] 保存二进制引用。
-- 新结构优先使用 resources[]；仅在 resources 不是数组时回退 assets[]，与运行时兼容读取规则一致。
CREATE TEMPORARY TABLE `tmp_skill_snapshot_content_ref` (
    `content_ref` VARCHAR(128) NOT NULL,
    `ref_count` INT NOT NULL,
    PRIMARY KEY (`content_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `tmp_skill_snapshot_content_ref` (`content_ref`, `ref_count`)
SELECT snapshot_ref.`content_ref`, COUNT(1)
FROM (
    SELECT legacy_asset.`content_ref`
    FROM `tb_skill` skill
    JOIN JSON_TABLE(
        COALESCE(skill.`published_snapshot_json`, JSON_OBJECT()),
        '$.assets[*]' COLUMNS (
            `content_ref` VARCHAR(128) PATH '$.contentRef' NULL ON EMPTY NULL ON ERROR
        )
    ) legacy_asset ON TRUE
    WHERE COALESCE(JSON_TYPE(JSON_EXTRACT(skill.`published_snapshot_json`, '$.resources')), '') <> 'ARRAY'
    UNION ALL
    SELECT resource.`content_ref`
    FROM `tb_skill` skill
    JOIN JSON_TABLE(
        COALESCE(skill.`published_snapshot_json`, JSON_OBJECT()),
        '$.resources[*]' COLUMNS (
            `content_ref` VARCHAR(128) PATH '$.contentRef' NULL ON EMPTY NULL ON ERROR
        )
    ) resource ON TRUE
    WHERE JSON_TYPE(JSON_EXTRACT(skill.`published_snapshot_json`, '$.resources')) = 'ARRAY'
    UNION ALL
    SELECT legacy_asset.`content_ref`
    FROM `tb_approval_instance` approval
    JOIN JSON_TABLE(
        COALESCE(approval.`snapshot_json`, JSON_OBJECT()),
        '$.resourceSnapshot.assets[*]' COLUMNS (
            `content_ref` VARCHAR(128) PATH '$.contentRef' NULL ON EMPTY NULL ON ERROR
        )
    ) legacy_asset ON TRUE
    WHERE approval.`resource_type` = 'SKILL'
      AND approval.`action_type` = 'PUBLISH'
      AND approval.`status` IN ('PENDING', 'PROCESSING')
      AND COALESCE(JSON_TYPE(JSON_EXTRACT(approval.`snapshot_json`, '$.resourceSnapshot.resources')), '') <> 'ARRAY'
    UNION ALL
    SELECT resource.`content_ref`
    FROM `tb_approval_instance` approval
    JOIN JSON_TABLE(
        COALESCE(approval.`snapshot_json`, JSON_OBJECT()),
        '$.resourceSnapshot.resources[*]' COLUMNS (
            `content_ref` VARCHAR(128) PATH '$.contentRef' NULL ON EMPTY NULL ON ERROR
        )
    ) resource ON TRUE
    WHERE approval.`resource_type` = 'SKILL'
      AND approval.`action_type` = 'PUBLISH'
      AND approval.`status` IN ('PENDING', 'PROCESSING')
      AND JSON_TYPE(JSON_EXTRACT(approval.`snapshot_json`, '$.resourceSnapshot.resources')) = 'ARRAY'
) snapshot_ref
WHERE snapshot_ref.`content_ref` IS NOT NULL
  AND snapshot_ref.`content_ref` <> ''
GROUP BY snapshot_ref.`content_ref`;

-- 二进制资源必须有可验证的 sha256 内容索引，否则无法安全修复物理文件关系。
CREATE TEMPORARY TABLE `tmp_skill_content_migration_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB;

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

INSERT INTO `tmp_skill_content_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_asset` asset
LEFT JOIN `tb_skill_asset_content` content ON content.`content_ref` = asset.`content_ref`
WHERE content.`content_ref` IS NULL
   OR content.`content_ref` NOT REGEXP '^sha256:[0-9a-f]{64}$'
   OR content.`content_hash` NOT REGEXP '^[0-9a-fA-F]{64}$'
   OR content.`content_ref` <> CONCAT('sha256:', LOWER(content.`content_hash`))
LIMIT 1;

INSERT INTO `tmp_skill_content_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_asset_content` content
WHERE content.`content_ref` NOT REGEXP '^sha256:[0-9a-f]{64}$'
   OR content.`content_hash` NOT REGEXP '^[0-9a-fA-F]{64}$'
   OR content.`content_ref` <> CONCAT('sha256:', LOWER(content.`content_hash`))
LIMIT 1;

INSERT INTO `tmp_skill_content_migration_guard` (`guard_key`)
SELECT 1
FROM `tmp_skill_snapshot_content_ref` snapshot_ref
LEFT JOIN `tb_skill_asset_content` content ON content.`content_ref` = snapshot_ref.`content_ref`
WHERE content.`content_ref` IS NULL
LIMIT 1;

DROP TEMPORARY TABLE `tmp_skill_content_migration_guard`;

-- 在永久 DDL 前构建已修复的内容迁移源；引用数不得低于真实资源数。
CREATE TEMPORARY TABLE `tmp_skill_content_migration_source` (
    `content_ref` VARCHAR(128) NOT NULL,
    `content_hash` VARCHAR(128) NOT NULL,
    `file_path` VARCHAR(1024) NOT NULL,
    `media_type` VARCHAR(128) NULL,
    `size` BIGINT NOT NULL,
    `ref_count` INT NOT NULL,
    `created` DATETIME NULL,
    `modified` DATETIME NULL,
    PRIMARY KEY (`content_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `tmp_skill_content_migration_source` (
    `content_ref`, `content_hash`, `file_path`, `media_type`, `size`, `ref_count`, `created`, `modified`
)
SELECT content.`content_ref`, LOWER(content.`content_hash`), content.`file_path`, content.`media_type`,
       GREATEST(COALESCE(content.`size`, 0),
                COALESCE((SELECT MAX(asset.`size`) FROM `tb_skill_asset` asset
                          WHERE asset.`content_ref` = content.`content_ref`), 0),
                0),
       (SELECT COUNT(1) FROM `tb_skill_asset` asset WHERE asset.`content_ref` = content.`content_ref`)
           + COALESCE((SELECT snapshot_ref.`ref_count` FROM `tmp_skill_snapshot_content_ref` snapshot_ref
                       WHERE snapshot_ref.`content_ref` = content.`content_ref`), 0),
       content.`created`, content.`modified`
FROM `tb_skill_asset_content` content;

-- 合并旧资源并修复可确定的 hash/size/租户；跨表路径、大小写或 ID 冲突会显式终止。
CREATE TEMPORARY TABLE `tmp_skill_resource_migration_source` (
    `id` BIGINT NOT NULL,
    `tenant_id` BIGINT NOT NULL,
    `skill_id` BIGINT NOT NULL,
    `path` VARCHAR(512) NOT NULL,
    `normalized_path` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `kind` VARCHAR(32) NOT NULL,
    `language` VARCHAR(32) NULL,
    `media_type` VARCHAR(128) NULL,
    `is_text` TINYINT(1) NOT NULL,
    `text_content` MEDIUMTEXT NULL,
    `content_ref` VARCHAR(128) NULL,
    `content_hash` VARCHAR(128) NOT NULL,
    `size` BIGINT NOT NULL,
    `metadata_json` JSON NULL,
    `sort_no` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_tmp_skill_resource_path` (`skill_id`, `normalized_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `tmp_skill_resource_migration_source` (
    `id`, `tenant_id`, `skill_id`, `path`, `normalized_path`, `kind`, `language`, `media_type`,
    `is_text`, `text_content`, `content_ref`, `content_hash`, `size`, `metadata_json`, `sort_no`
)
SELECT reference.`id`, COALESCE(reference.`tenant_id`, skill.`tenant_id`), reference.`skill_id`,
       reference.`path`, reference.`path`, 'REFERENCE', 'MARKDOWN', 'text/markdown', 1,
       COALESCE(reference.`content`, ''), NULL,
       LOWER(SHA2(COALESCE(reference.`content`, ''), 256)),
       OCTET_LENGTH(COALESCE(reference.`content`, '')), reference.`metadata_json`, 0
FROM `tb_skill_reference` reference
JOIN `tb_skill` skill ON skill.`id` = reference.`skill_id`
UNION ALL
SELECT script.`id`, COALESCE(script.`tenant_id`, skill.`tenant_id`), script.`skill_id`,
       script.`path`, script.`path`, 'SCRIPT', script.`language`, 'text/plain', 1,
       COALESCE(script.`content`, ''), NULL,
       LOWER(SHA2(COALESCE(script.`content`, ''), 256)),
       OCTET_LENGTH(COALESCE(script.`content`, '')), script.`metadata_json`, 0
FROM `tb_skill_script` script
JOIN `tb_skill` skill ON skill.`id` = script.`skill_id`
UNION ALL
SELECT asset.`id`, COALESCE(asset.`tenant_id`, skill.`tenant_id`), asset.`skill_id`,
       asset.`path`, asset.`path`, 'ASSET', NULL, asset.`media_type`, 0, NULL, asset.`content_ref`,
       content.`content_hash`,
       content.`size`, asset.`metadata_json`, 0
FROM `tb_skill_asset` asset
JOIN `tb_skill` skill ON skill.`id` = asset.`skill_id`
JOIN `tmp_skill_content_migration_source` content ON content.`content_ref` = asset.`content_ref`;

SET @skill_summary_columns_ddl = (
    SELECT CASE
               WHEN COUNT(1) = 0 THEN 'SELECT 1'
               ELSE CONCAT('ALTER TABLE `tb_skill` ', GROUP_CONCAT(stmt ORDER BY ord SEPARATOR ', '))
           END
    FROM (
        SELECT 1 AS ord,
               'ADD COLUMN `capability_hash` VARCHAR(128) NULL COMMENT ''能力配置 hash'' AFTER `package_hash`' AS stmt
        WHERE NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'tb_skill' AND column_name = 'capability_hash'
        )
        UNION ALL
        SELECT 2 AS ord,
               'ADD COLUMN `snapshot_hash` VARCHAR(128) NULL COMMENT ''发布快照 hash'' AFTER `capability_hash`' AS stmt
        WHERE NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'tb_skill' AND column_name = 'snapshot_hash'
        )
        UNION ALL
        SELECT 3 AS ord,
               'ADD COLUMN `resource_count` INT NOT NULL DEFAULT 0 COMMENT ''资源数量'' AFTER `snapshot_hash`' AS stmt
        WHERE NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'tb_skill' AND column_name = 'resource_count'
        )
        UNION ALL
        SELECT 4 AS ord,
               'ADD COLUMN `capability_count` INT NOT NULL DEFAULT 0 COMMENT ''能力数量'' AFTER `resource_count`' AS stmt
        WHERE NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_schema = DATABASE() AND table_name = 'tb_skill' AND column_name = 'capability_count'
        )
    ) changes
);
PREPARE skill_summary_columns_stmt FROM @skill_summary_columns_ddl;
EXECUTE skill_summary_columns_stmt;
DEALLOCATE PREPARE skill_summary_columns_stmt;

SET @skill_category_parent_scope_ddl = (
    SELECT IF(COUNT(1) = 0,
              'ALTER TABLE `tb_skill_category` ADD COLUMN `parent_scope_id` BIGINT GENERATED ALWAYS AS (IFNULL(`parent_id`, 0)) STORED COMMENT ''同级唯一约束辅助列''',
              'SELECT 1')
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_category'
      AND column_name = 'parent_scope_id'
);
PREPARE skill_category_parent_scope_stmt FROM @skill_category_parent_scope_ddl;
EXECUTE skill_category_parent_scope_stmt;
DEALLOCATE PREPARE skill_category_parent_scope_stmt;

-- V24 旧数据可能以 0 表示根分类，统一归一为 NULL，与服务层父级语义保持一致。
UPDATE `tb_skill_category`
SET `parent_id` = NULL, `modified` = `modified`, `modified_by` = `modified_by`
WHERE `parent_id` = 0;

-- MySQL DDL 会自动提交；修复 Flyway 失败记录后重跑时不得因索引已创建再次失败。
SET @skill_category_unique_ddl = (
    SELECT IF(COUNT(1) = 0,
              'ALTER TABLE `tb_skill_category` ADD UNIQUE KEY `uk_skill_category_tenant_parent_name` (`tenant_id`, `parent_scope_id`, `category_name`)',
              'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_category'
      AND index_name = 'uk_skill_category_tenant_parent_name'
);
PREPARE skill_category_unique_stmt FROM @skill_category_unique_ddl;
EXECUTE skill_category_unique_stmt;
DEALLOCATE PREPARE skill_category_unique_stmt;

CREATE TABLE IF NOT EXISTS `tb_skill_resource` (
    `id` BIGINT NOT NULL COMMENT 'ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `skill_id` BIGINT NOT NULL COMMENT 'Skill ID',
    `path` VARCHAR(512) NOT NULL COMMENT '原始逻辑路径',
    `normalized_path` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规范化逻辑路径',
    `kind` VARCHAR(32) NOT NULL COMMENT 'REFERENCE/SCRIPT/ASSET/EXAMPLE/OTHER',
    `language` VARCHAR(32) NULL COMMENT '文本或脚本语言',
    `media_type` VARCHAR(128) NULL COMMENT '媒体类型',
    `is_text` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否为UTF-8文本',
    `text_content` MEDIUMTEXT NULL COMMENT '文本内容',
    `content_ref` VARCHAR(128) NULL COMMENT '二进制内容引用',
    `content_hash` VARCHAR(128) NOT NULL COMMENT '内容hash',
    `size` BIGINT NOT NULL DEFAULT 0 COMMENT '字节数',
    `metadata_json` JSON NULL COMMENT '资源元数据',
    `sort_no` INT NOT NULL DEFAULT 0 COMMENT '排序',
    `created` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `created_by` BIGINT NULL COMMENT '创建人',
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `modified_by` BIGINT NULL COMMENT '修改人',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_skill_resource_path` (`skill_id`, `normalized_path`),
    KEY `idx_skill_resource_tenant_skill` (`tenant_id`, `skill_id`, `sort_no`),
    KEY `idx_skill_resource_content_ref` (`content_ref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Skill 通用资源';

CREATE TABLE IF NOT EXISTS `tb_skill_content` (
    `content_ref` VARCHAR(128) NOT NULL COMMENT '内容引用',
    `content_hash` VARCHAR(128) NOT NULL COMMENT '内容hash',
    `file_path` VARCHAR(1024) NOT NULL COMMENT '存储路径',
    `media_type` VARCHAR(128) NULL COMMENT '媒体类型',
    `size` BIGINT NOT NULL DEFAULT 0 COMMENT '字节数',
    `ref_count` INT NOT NULL DEFAULT 0 COMMENT '引用数',
    `created` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`content_ref`),
    KEY `idx_skill_content_hash` (`content_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Skill 二进制内容索引';

CREATE TABLE IF NOT EXISTS `tb_skill_capability_binding` (
    `id` BIGINT NOT NULL COMMENT 'ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `skill_id` BIGINT NOT NULL COMMENT 'Skill ID',
    `capability_type` VARCHAR(32) NOT NULL COMMENT 'WORKFLOW/PLUGIN_ITEM/MCP',
    `target_id` BIGINT NULL COMMENT '目标资源ID，未解析导入项可为空',
    `target_logical_ref` VARCHAR(512) NULL COMMENT '跨环境目标逻辑引用',
    `runtime_name` VARCHAR(128) NOT NULL COMMENT '运行时名称或MCP命名空间',
    `enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
    `selection_mode` VARCHAR(16) NULL COMMENT 'MCP工具选择模式',
    `selected_tool_names_json` JSON NULL COMMENT '选择的MCP工具名',
    `execution_mode` VARCHAR(16) NULL COMMENT 'SYNC/ASYNC',
    `hitl_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否启用人工确认',
    `hitl_config_json` JSON NULL COMMENT '人工确认非敏感配置',
    `options_json` JSON NULL COMMENT '非敏感扩展配置',
    `sort_no` INT NOT NULL DEFAULT 0 COMMENT '排序',
    `created` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `created_by` BIGINT NULL COMMENT '创建人',
    `modified` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `modified_by` BIGINT NULL COMMENT '修改人',
    PRIMARY KEY (`id`),
    KEY `idx_skill_capability_tenant_skill` (`tenant_id`, `skill_id`, `sort_no`),
    KEY `idx_skill_capability_target` (`capability_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Skill 平台能力绑定';

CREATE TABLE IF NOT EXISTS `tb_skill_import_stage` (
    `import_token` VARCHAR(64) NOT NULL COMMENT '单次导入令牌',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `account_id` BIGINT NOT NULL COMMENT '创建用户ID',
    `file_path` VARCHAR(1024) NOT NULL COMMENT '受控临时包路径',
    `original_name` VARCHAR(255) NULL COMMENT '原始文件名',
    `format` VARCHAR(16) NOT NULL COMMENT 'STANDARD/EASYFLOW',
    `status` VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/PROCESSING',
    `expires_at` DATETIME NOT NULL COMMENT '过期时间',
    `created` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`import_token`),
    KEY `idx_skill_import_stage_expire` (`status`, `expires_at`),
    KEY `idx_skill_import_stage_owner` (`tenant_id`, `account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Skill 导入临时包';

-- 兼容 DDL 已部分提交的旧 V27 重跑，强制新表租户边界列不可为空。
UPDATE `tb_skill_resource` target
JOIN `tmp_skill_resource_migration_source` source
  ON source.`id` = target.`id`
 AND source.`skill_id` = target.`skill_id`
 AND source.`normalized_path` = target.`normalized_path`
SET target.`tenant_id` = source.`tenant_id`,
    target.`modified` = target.`modified`,
    target.`modified_by` = target.`modified_by`
WHERE target.`tenant_id` IS NULL;

UPDATE `tb_skill_capability_binding` binding
JOIN `tb_skill` skill ON skill.`id` = binding.`skill_id`
SET binding.`tenant_id` = skill.`tenant_id`,
    binding.`modified` = binding.`modified`,
    binding.`modified_by` = binding.`modified_by`
WHERE binding.`tenant_id` IS NULL AND skill.`tenant_id` IS NOT NULL;

CREATE TEMPORARY TABLE `tmp_skill_new_table_tenant_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB;
INSERT INTO `tmp_skill_new_table_tenant_guard` (`guard_key`) VALUES (1);
INSERT INTO `tmp_skill_new_table_tenant_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_resource` resource
LEFT JOIN `tb_skill` skill ON skill.`id` = resource.`skill_id`
WHERE resource.`tenant_id` IS NULL
   OR skill.`id` IS NULL
   OR skill.`tenant_id` IS NULL
   OR resource.`tenant_id` <> skill.`tenant_id`
LIMIT 1;
INSERT INTO `tmp_skill_new_table_tenant_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_capability_binding` binding
LEFT JOIN `tb_skill` skill ON skill.`id` = binding.`skill_id`
WHERE binding.`tenant_id` IS NULL
   OR skill.`id` IS NULL
   OR skill.`tenant_id` IS NULL
   OR binding.`tenant_id` <> skill.`tenant_id`
LIMIT 1;
DROP TEMPORARY TABLE `tmp_skill_new_table_tenant_guard`;

ALTER TABLE `tb_skill_resource` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT '租户ID';
ALTER TABLE `tb_skill_resource`
    MODIFY COLUMN `normalized_path` VARCHAR(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '规范化逻辑路径';
ALTER TABLE `tb_skill_capability_binding` MODIFY COLUMN `tenant_id` BIGINT NOT NULL COMMENT '租户ID';

-- 如果上一次迁移在 DDL 自动提交后失败，只允许复用与迁移源完全一致的已写入行。
CREATE TEMPORARY TABLE `tmp_skill_target_migration_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB;

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

INSERT INTO `tmp_skill_target_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_content` target
JOIN `tmp_skill_content_migration_source` source ON source.`content_ref` = target.`content_ref`
WHERE NOT (
    target.`content_hash` <=> source.`content_hash`
    AND target.`file_path` <=> source.`file_path`
    AND target.`media_type` <=> source.`media_type`
    AND target.`size` <=> source.`size`
    AND target.`ref_count` <=> source.`ref_count`
    AND target.`created` <=> source.`created`
    AND target.`modified` <=> source.`modified`
)
LIMIT 1;

INSERT INTO `tmp_skill_target_migration_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_resource` target
JOIN `tmp_skill_resource_migration_source` source
  ON source.`id` = target.`id`
  OR (source.`skill_id` = target.`skill_id` AND source.`normalized_path` = target.`normalized_path`)
WHERE NOT (
    target.`id` <=> source.`id`
    AND target.`tenant_id` <=> source.`tenant_id`
    AND target.`skill_id` <=> source.`skill_id`
    AND target.`path` <=> source.`path`
    AND target.`normalized_path` <=> source.`normalized_path`
    AND target.`kind` <=> source.`kind`
    AND target.`language` <=> source.`language`
    AND target.`media_type` <=> source.`media_type`
    AND target.`is_text` <=> source.`is_text`
    AND target.`text_content` <=> source.`text_content`
    AND target.`content_ref` <=> source.`content_ref`
    AND target.`content_hash` <=> source.`content_hash`
    AND target.`size` <=> source.`size`
    AND target.`metadata_json` <=> source.`metadata_json`
    AND target.`sort_no` <=> source.`sort_no`
)
LIMIT 1;

DROP TEMPORARY TABLE `tmp_skill_target_migration_guard`;

INSERT INTO `tb_skill_content` (
    `content_ref`, `content_hash`, `file_path`, `media_type`, `size`, `ref_count`, `created`, `modified`
)
SELECT source.`content_ref`, source.`content_hash`, source.`file_path`, source.`media_type`,
       source.`size`, source.`ref_count`, source.`created`, source.`modified`
FROM `tmp_skill_content_migration_source` source
WHERE NOT EXISTS (
    SELECT 1 FROM `tb_skill_content` target WHERE target.`content_ref` = source.`content_ref`
);

INSERT INTO `tb_skill_resource` (
    `id`, `tenant_id`, `skill_id`, `path`, `normalized_path`, `kind`, `language`, `media_type`,
    `is_text`, `text_content`, `content_ref`, `content_hash`, `size`, `metadata_json`, `sort_no`
)
SELECT source.`id`, source.`tenant_id`, source.`skill_id`, source.`path`, source.`normalized_path`,
       source.`kind`, source.`language`, source.`media_type`, source.`is_text`, source.`text_content`,
       source.`content_ref`, source.`content_hash`, source.`size`, source.`metadata_json`, source.`sort_no`
FROM `tmp_skill_resource_migration_source` source
WHERE NOT EXISTS (
    SELECT 1 FROM `tb_skill_resource` target WHERE target.`id` = source.`id`
);

DROP TEMPORARY TABLE `tmp_skill_resource_migration_source`;
DROP TEMPORARY TABLE `tmp_skill_content_migration_source`;
DROP TEMPORARY TABLE `tmp_skill_snapshot_content_ref`;

UPDATE `tb_skill` skill
SET `resource_count` = (SELECT COUNT(1) FROM `tb_skill_resource` resource WHERE resource.`skill_id` = skill.`id`),
    `capability_count` = (SELECT COUNT(1) FROM `tb_skill_capability_binding` binding WHERE binding.`skill_id` = skill.`id`),
    `reference_count` = (SELECT COUNT(1) FROM `tb_skill_resource` resource
                         WHERE resource.`skill_id` = skill.`id` AND resource.`kind` = 'REFERENCE'),
    `script_count` = (SELECT COUNT(1) FROM `tb_skill_resource` resource
                      WHERE resource.`skill_id` = skill.`id` AND resource.`kind` = 'SCRIPT'),
    `asset_count` = (SELECT COUNT(1) FROM `tb_skill_resource` resource
                     WHERE resource.`skill_id` = skill.`id`
                       AND resource.`kind` NOT IN ('REFERENCE', 'SCRIPT')
                       AND resource.`is_text` = 0),
    `package_hash` = NULL,
    `capability_hash` = '4f53cda18c2baa0c0354bb5f9a3ecbe5ed12ab4d8e11ba873c2f11161202b945',
    `modified` = `modified`,
    `modified_by` = `modified_by`;
