-- 添加刷新令牌表的数据迁移脚本
-- 用于实现 Token 刷新机制
-- 执行前请确保已备份数据库

-- 1. 创建 refresh_tokens 表（如果不存在）
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'refresh_tokens')
BEGIN
    CREATE TABLE refresh_tokens (
        Id BIGINT IDENTITY(1,1) PRIMARY KEY,
        AdminUserId BIGINT NOT NULL,
        TokenHash NVARCHAR(256) NOT NULL,
        ExpiresAt DATETIME2 NOT NULL,
        CreatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),
        CreatedByIp NVARCHAR(50) NULL,
        RevokedAt DATETIME2 NULL,
        RevokedByIp NVARCHAR(50) NULL,
        ReplacedByToken NVARCHAR(256) NULL,
        
        CONSTRAINT FK_refresh_tokens_admin_users 
            FOREIGN KEY (AdminUserId) REFERENCES admin_users(Id) ON DELETE CASCADE
    );
    
    PRINT N'refresh_tokens 表已创建';
END
ELSE
BEGIN
    PRINT N'refresh_tokens 表已存在，跳过创建';
END

-- 2. 创建索引（如果不存在）

-- TokenHash 索引 - 用于快速查找 Token
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_refresh_tokens_token_hash' AND object_id = OBJECT_ID('refresh_tokens'))
BEGIN
    CREATE INDEX IX_refresh_tokens_token_hash ON refresh_tokens (TokenHash);
    PRINT N'IX_refresh_tokens_token_hash 索引已创建';
END
ELSE
BEGIN
    PRINT N'IX_refresh_tokens_token_hash 索引已存在，跳过';
END

-- AdminUserId 索引 - 用于查询用户的所有 Token
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_refresh_tokens_admin_user_id' AND object_id = OBJECT_ID('refresh_tokens'))
BEGIN
    CREATE INDEX IX_refresh_tokens_admin_user_id ON refresh_tokens (AdminUserId);
    PRINT N'IX_refresh_tokens_admin_user_id 索引已创建';
END
ELSE
BEGIN
    PRINT N'IX_refresh_tokens_admin_user_id 索引已存在，跳过';
END

-- ExpiresAt 索引 - 用于清理过期 Token
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_refresh_tokens_expires_at' AND object_id = OBJECT_ID('refresh_tokens'))
BEGIN
    CREATE INDEX IX_refresh_tokens_expires_at ON refresh_tokens (ExpiresAt);
    PRINT N'IX_refresh_tokens_expires_at 索引已创建';
END
ELSE
BEGIN
    PRINT N'IX_refresh_tokens_expires_at 索引已存在，跳过';
END

PRINT N'刷新令牌表迁移完成';
