-- 聊天列表菜单增量，MySQL 8；在后台业务库中手动执行。
-- 复用已有 chat/list/index 菜单；否则创建为原私聊记录菜单的同级菜单。
-- 不固定菜单/角色/租户 ID，不删除现有菜单或授权；执行后重新登录刷新菜单缓存。
-- 原私聊菜单缺失、存在歧义或同级路由冲突时明确报错，整个 DML 事务回滚。
-- 此文件包含存储过程 DDL，请单独执行，不要包在其它迁移事务中。

DELIMITER $$
DROP PROCEDURE IF EXISTS migrate_chat_conversation_menu_20261103$$
CREATE PROCEDURE migrate_chat_conversation_menu_20261103()
BEGIN
    DECLARE source_id BIGINT;
    DECLARE target_id BIGINT;
    DECLARE source_count INT;
    DECLARE target_count INT;
    DECLARE migration_lock INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        IF migration_lock = 1 THEN
            DO RELEASE_LOCK('chat_conversation_menu_20261103');
        END IF;
        RESIGNAL;
    END;

    SELECT GET_LOCK('chat_conversation_menu_20261103', 10) INTO migration_lock;
    IF COALESCE(migration_lock, 0) <> 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot acquire chat menu migration lock';
    END IF;
    START TRANSACTION;

    SELECT COUNT(*), MIN(id) INTO source_count, source_id
    FROM system_menu
    WHERE deleted = b'0' AND type = 2
      AND TRIM(LEADING '/' FROM component) IN ('chat/private-msg/index', 'chat/private-msg/index.vue');
    IF source_count <> 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Expected exactly one active chat/private-msg/index menu';
    END IF;

    SELECT COUNT(*), MIN(id) INTO target_count, target_id
    FROM system_menu
    WHERE deleted = b'0'
      AND TRIM(LEADING '/' FROM component) IN ('chat/list/index', 'chat/list/index.vue');
    IF target_count > 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Multiple active chat/list/index menus exist';
    END IF;

    IF target_count = 0 THEN
        IF EXISTS (SELECT 1 FROM system_menu m JOIN system_menu s ON s.id = source_id
                   WHERE m.parent_id = s.parent_id AND m.path = 'list' AND m.deleted = b'0') THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sibling route list already exists; resolve menu path first';
        END IF;
        INSERT INTO system_menu
            (name, permission, type, sort, parent_id, path, icon, component, component_name,
             status, visible, keep_alive, always_show, creator, updater, deleted)
        SELECT '聊天列表', permission, 2, sort + 1, parent_id, 'list', 'ep:chat-dot-round',
               'chat/list/index', 'ChatList', status, visible, keep_alive, always_show,
               'migration', 'migration', b'0'
        FROM system_menu WHERE id = source_id;
        SET target_id = LAST_INSERT_ID();
    ELSE
        -- 保留已有菜单的路径、父目录、权限和可见性，仅对齐组件位置与缓存组件名。
        UPDATE system_menu SET component = 'chat/list/index', component_name = 'ChatList', updater = 'migration'
        WHERE id = target_id;
    END IF;

    INSERT INTO system_role_menu (role_id, menu_id, creator, updater, deleted, tenant_id)
    SELECT DISTINCT source.role_id, target_id, 'migration', 'migration', b'0', source.tenant_id
    FROM system_role_menu source
    WHERE source.menu_id = source_id AND source.deleted = b'0'
      AND NOT EXISTS (SELECT 1 FROM system_role_menu target
                      WHERE target.role_id = source.role_id AND target.tenant_id = source.tenant_id
                        AND target.menu_id = target_id AND target.deleted = b'0');

    COMMIT;
    DO RELEASE_LOCK('chat_conversation_menu_20261103');
    SELECT target_id AS menu_id, 'chat/list/index' AS component, 'ChatList' AS component_name;
END$$
CALL migrate_chat_conversation_menu_20261103()$$
DROP PROCEDURE migrate_chat_conversation_menu_20261103$$
DELIMITER ;