-- V13: 修复 t_sys_user 唯一索引 — phone/username 改为 (tenant_id, ...) 联合唯一
-- 日期: 2026-04-09
-- 背景: 多租户场景下同一手机号/用户名应可在不同租户下注册，需改为联合唯一索引

-- =====================================================
-- 1. phone: 删除旧的独立唯一索引，创建 (tenant_id, phone) 联合唯一索引
-- =====================================================

-- 安全删除已存在的 phone 唯一索引（索引名可能是 phone 或其他自定义名）
SET @exist := (SELECT COUNT(*) FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
                 AND TABLE_NAME = 't_sys_user'
                 AND INDEX_NAME = 'phone'
                 AND NON_UNIQUE = 0);
SET @sqlstmt := IF(@exist > 0,
    'ALTER TABLE t_sys_user DROP INDEX phone',
    'SELECT ''phone 唯一索引不存在，跳过删除''');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 创建联合唯一索引（IF NOT EXISTS 防止重复创建）
CREATE UNIQUE INDEX uk_sys_user_tenant_phone ON t_sys_user(tenant_id, phone);

-- =====================================================
-- 2. username: 删除旧的独立唯一索引，创建 (tenant_id, username) 联合唯一索引
-- =====================================================

-- 安全删除已存在的 username 唯一索引
SET @exist := (SELECT COUNT(*) FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE()
                 AND TABLE_NAME = 't_sys_user'
                 AND INDEX_NAME = 'username'
                 AND NON_UNIQUE = 0);
SET @sqlstmt := IF(@exist > 0,
    'ALTER TABLE t_sys_user DROP INDEX username',
    'SELECT ''username 唯一索引不存在，跳过删除''');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 创建联合唯一索引
CREATE UNIQUE INDEX uk_sys_user_tenant_username ON t_sys_user(tenant_id, username);
