-- ============================================
-- Mokee Gateway: BIGINT → UUID 数据库迁移
-- 执行前请备份数据库
-- ============================================

-- 1. 添加临时 UUID 列并生成值
ALTER TABLE sys_system       ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_user         ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_role         ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_menu         ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_api          ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_dict         ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_file         ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_user_system  ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_user_role    ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_role_menu    ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_login_log    ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE sys_operation_log ADD COLUMN new_id VARCHAR(64) NOT NULL DEFAULT '' AFTER id;

UPDATE sys_system       SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_user         SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_role         SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_menu         SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_api          SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_dict         SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_file         SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_user_system  SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_user_role    SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_role_menu    SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_login_log    SET new_id = REPLACE(UUID(),'-','');
UPDATE sys_operation_log SET new_id = REPLACE(UUID(),'-','');

-- 2. 更新所有外键引用（用 new_id 关联）
UPDATE sys_api          a JOIN sys_system       s ON a.system_id = s.id SET a.system_id = s.new_id;
UPDATE sys_role         r JOIN sys_system       s ON r.system_id = s.id SET r.system_id = s.new_id;
UPDATE sys_menu         m JOIN sys_system       s ON m.system_id = s.id SET m.system_id = s.new_id;
UPDATE sys_user_system us JOIN sys_system       s ON us.system_id = s.id SET us.system_id = s.new_id;
UPDATE sys_user_system us JOIN sys_user         u ON us.user_id = u.id     SET us.user_id = u.new_id;
UPDATE sys_user_role   ur JOIN sys_role         r ON ur.role_id = r.id     SET ur.role_id = r.new_id;
UPDATE sys_user_role   ur JOIN sys_user         u ON ur.user_id = u.id     SET ur.user_id = u.new_id;
UPDATE sys_role_menu   rm JOIN sys_role         r ON rm.role_id = r.id     SET rm.role_id = r.new_id;
UPDATE sys_role_menu   rm JOIN sys_menu         m ON rm.menu_id = m.id     SET rm.menu_id = m.new_id;
UPDATE sys_login_log    l JOIN sys_user         u ON l.user_id = u.id      SET l.user_id = u.new_id;
UPDATE sys_login_log    l JOIN sys_system       s ON l.system_id = s.id    SET l.system_id = s.new_id;
UPDATE sys_operation_log o JOIN sys_user        u ON o.user_id = u.id      SET o.user_id = u.new_id;
UPDATE sys_operation_log o JOIN sys_system      s ON o.system_id = s.id    SET o.system_id = s.new_id;
-- menu parent_id 自引用
UPDATE sys_menu child JOIN sys_menu parent ON child.parent_id = parent.id AND parent.new_id != '' SET child.parent_id = parent.new_id;

-- 3. 切换主键
ALTER TABLE sys_system       DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_user         DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_role         DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_menu         DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_api          DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_dict         DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_file         DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_user_system  DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_user_role    DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_role_menu    DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_login_log    DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);
ALTER TABLE sys_operation_log DROP PRIMARY KEY, DROP COLUMN id, CHANGE new_id id VARCHAR(64) NOT NULL, ADD PRIMARY KEY (id);

-- 4. 修改所有 FK 列类型
ALTER TABLE sys_api          MODIFY system_id VARCHAR(64) NOT NULL;
ALTER TABLE sys_role         MODIFY system_id VARCHAR(64) NOT NULL;
ALTER TABLE sys_menu         MODIFY system_id VARCHAR(64) NOT NULL, MODIFY parent_id VARCHAR(64) NOT NULL DEFAULT '0';
ALTER TABLE sys_user_system  MODIFY user_id VARCHAR(64) NOT NULL, MODIFY system_id VARCHAR(64) NOT NULL;
ALTER TABLE sys_user_role    MODIFY user_id VARCHAR(64) NOT NULL, MODIFY role_id VARCHAR(64) NOT NULL;
ALTER TABLE sys_role_menu    MODIFY role_id VARCHAR(64) NOT NULL, MODIFY menu_id VARCHAR(64) NOT NULL;
ALTER TABLE sys_login_log    MODIFY user_id VARCHAR(64), MODIFY system_id VARCHAR(64);
ALTER TABLE sys_operation_log MODIFY user_id VARCHAR(64), MODIFY system_id VARCHAR(64);
ALTER TABLE sys_file         MODIFY system_id VARCHAR(64), MODIFY upload_by VARCHAR(64);

-- 5. 重新创建索引（因列类型变更）
-- sys_api
ALTER TABLE sys_api DROP INDEX uk_system_path_method, ADD UNIQUE KEY uk_system_path_method (system_id, api_path, api_method);
-- sys_role
ALTER TABLE sys_role DROP INDEX uk_system_role_code, ADD UNIQUE KEY uk_system_role_code (system_id, role_code);
-- 其他唯一键涉及 FK 的同样处理...

-- ============================================
-- 迁移完成
-- ============================================