SET NAMES utf8mb4;

-- V27 已完成旧表到标准资源表的受保护复制，此后应用只写标准表，旧表允许因正常编辑而陈旧。
-- 所有不可逆删除前校验当前权威数据的完整性；任一校验命中都会通过固定主键冲突终止迁移。
CREATE TEMPORARY TABLE `tmp_skill_standard_cleanup_guard` (
    `guard_key` TINYINT NOT NULL,
    PRIMARY KEY (`guard_key`)
) ENGINE=InnoDB;
INSERT INTO `tmp_skill_standard_cleanup_guard` (`guard_key`) VALUES (1);

-- Skill 目录名必须符合 Anthropic Skill 的小写连字符命名规则。
INSERT INTO `tmp_skill_standard_cleanup_guard` (`guard_key`)
SELECT 1
FROM `tb_skill`
WHERE `name` IS NULL OR `name` NOT REGEXP '^[a-z0-9]+(-[a-z0-9]+)*$'
LIMIT 1;

-- 通用资源仍须严格位于所属 Skill 的租户边界内，二进制引用必须可解析。
INSERT INTO `tmp_skill_standard_cleanup_guard` (`guard_key`)
SELECT 1
FROM `tb_skill_resource` resource
LEFT JOIN `tb_skill` skill ON skill.`id` = resource.`skill_id`
LEFT JOIN `tb_skill_content` content ON content.`content_ref` = resource.`content_ref`
WHERE skill.`id` IS NULL
   OR resource.`tenant_id` <> skill.`tenant_id`
   OR (resource.`is_text` = 0 AND (resource.`content_ref` IS NULL OR content.`content_ref` IS NULL))
LIMIT 1;

-- 旧 EasyFlow 私有包暂存不能在删除格式字段后被误当作标准包。
SET @guard_skill_import_format = (
    SELECT IF(COUNT(1) > 0,
              'INSERT INTO `tmp_skill_standard_cleanup_guard` (`guard_key`) SELECT 1 FROM `tb_skill_import_stage` WHERE `format` <> ''STANDARD'' LIMIT 1',
              'SELECT 1')
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_import_stage'
      AND column_name = 'format');
PREPARE guard_skill_import_format_stmt FROM @guard_skill_import_format;
EXECUTE guard_skill_import_format_stmt;
DEALLOCATE PREPARE guard_skill_import_format_stmt;

DROP TEMPORARY TABLE `tmp_skill_standard_cleanup_guard`;

-- 历史 enabled=false 的已发布 Skill 映射为正式下线状态，避免删除字段后扩大可见性。
SET @normalize_disabled_skill = (
    SELECT IF(COUNT(1) > 0,
              'UPDATE `tb_skill` SET `publish_status` = ''OFFLINE'', `current_approval_instance_id` = NULL, `modified` = `modified`, `modified_by` = `modified_by` WHERE `enabled` = 0 AND `publish_status` = ''PUBLISHED''',
              'SELECT 1')
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill'
      AND column_name = 'enabled');
PREPARE normalize_disabled_skill_stmt FROM @normalize_disabled_skill;
EXECUTE normalize_disabled_skill_stmt;
DEALLOCATE PREPARE normalize_disabled_skill_stmt;

-- 删除已下线的能力绑定权限及其角色关系。
DELETE FROM `tb_sys_role_menu` WHERE `menu_id` = 367400000000000024;
DELETE FROM `tb_sys_menu`
WHERE `id` = 367400000000000024
  AND `permission_tag` = '/api/v1/skill/capability';

UPDATE `tb_sys_menu`
SET `menu_title` = '技能库',
    `remark` = '标准 Skill 管理',
    `modified` = `modified`,
    `modified_by` = `modified_by`
WHERE `id` = 367400000000000001
  AND `menu_url` = '/ai/skill';

-- 索引不再依赖 enabled，改为围绕列表真实过滤条件组织。
SET @drop_skill_category_index = (
    SELECT IF(COUNT(1) > 0,
              'ALTER TABLE `tb_skill` DROP INDEX `idx_skill_tenant_category`',
              'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill'
      AND index_name = 'idx_skill_tenant_category');
PREPARE drop_skill_category_index_stmt FROM @drop_skill_category_index;
EXECUTE drop_skill_category_index_stmt;
DEALLOCATE PREPARE drop_skill_category_index_stmt;

SET @add_skill_list_index = (
    SELECT IF(COUNT(1) = 0,
              'ALTER TABLE `tb_skill` ADD KEY `idx_skill_tenant_list` (`tenant_id`, `category_id`, `visibility_scope`, `publish_status`, `modified`)',
              'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill'
      AND index_name = 'idx_skill_tenant_list');
PREPARE add_skill_list_index_stmt FROM @add_skill_list_index;
EXECUTE add_skill_list_index_stmt;
DEALLOCATE PREPARE add_skill_list_index_stmt;

SET @drop_skill_resource_sort_index = (
    SELECT IF(COUNT(1) > 0,
              'ALTER TABLE `tb_skill_resource` DROP INDEX `idx_skill_resource_tenant_skill`',
              'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_resource'
      AND index_name = 'idx_skill_resource_tenant_skill');
PREPARE drop_skill_resource_sort_index_stmt FROM @drop_skill_resource_sort_index;
EXECUTE drop_skill_resource_sort_index_stmt;
DEALLOCATE PREPARE drop_skill_resource_sort_index_stmt;

SET @add_skill_resource_index = (
    SELECT IF(COUNT(1) = 0,
              'ALTER TABLE `tb_skill_resource` ADD KEY `idx_skill_resource_tenant_skill` (`tenant_id`, `skill_id`, `normalized_path`)',
              'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_resource'
      AND index_name = 'idx_skill_resource_tenant_skill');
PREPARE add_skill_resource_index_stmt FROM @add_skill_resource_index;
EXECUTE add_skill_resource_index_stmt;
DEALLOCATE PREPARE add_skill_resource_index_stmt;

-- 物理删除已由 V27 完整替代的五张旧包模型表和能力绑定表。
DROP TABLE IF EXISTS `tb_skill_capability_binding`;
DROP TABLE IF EXISTS `tb_skill_reference`;
DROP TABLE IF EXISTS `tb_skill_script`;
DROP TABLE IF EXISTS `tb_skill_asset`;
DROP TABLE IF EXISTS `tb_skill_asset_content`;

-- 仅保留标准包持久化所需字段；动态 DDL 允许 MySQL 非事务 DDL 失败后安全重跑。
SET @drop_skill_legacy_columns = (
    SELECT IF(COUNT(1) = 0,
              'SELECT 1',
              CONCAT('ALTER TABLE `tb_skill` ', GROUP_CONCAT(CONCAT('DROP COLUMN `', column_name, '`') SEPARATOR ', ')))
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill'
      AND column_name IN ('metadata_json', 'enabled', 'source_type', 'capability_hash',
                          'resource_count', 'capability_count', 'reference_count', 'script_count', 'asset_count'));
PREPARE drop_skill_legacy_columns_stmt FROM @drop_skill_legacy_columns;
EXECUTE drop_skill_legacy_columns_stmt;
DEALLOCATE PREPARE drop_skill_legacy_columns_stmt;

SET @drop_skill_resource_derived_columns = (
    SELECT IF(COUNT(1) = 0,
              'SELECT 1',
              CONCAT('ALTER TABLE `tb_skill_resource` ', GROUP_CONCAT(CONCAT('DROP COLUMN `', column_name, '`') SEPARATOR ', ')))
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_resource'
      AND column_name IN ('kind', 'language', 'metadata_json', 'sort_no'));
PREPARE drop_skill_resource_derived_columns_stmt FROM @drop_skill_resource_derived_columns;
EXECUTE drop_skill_resource_derived_columns_stmt;
DEALLOCATE PREPARE drop_skill_resource_derived_columns_stmt;

SET @drop_skill_import_format = (
    SELECT IF(COUNT(1) > 0,
              'ALTER TABLE `tb_skill_import_stage` DROP COLUMN `format`',
              'SELECT 1')
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'tb_skill_import_stage'
      AND column_name = 'format');
PREPARE drop_skill_import_format_stmt FROM @drop_skill_import_format;
EXECUTE drop_skill_import_format_stmt;
DEALLOCATE PREPARE drop_skill_import_format_stmt;
