HaniBlindBox/server/HoneyBox/scripts/create_user_refresh_tokens.sql

-- 创建用户刷新令牌表
-- 用于实现双 Token 认证机制（Access Token + Refresh Token）

-- 创建用户刷新令牌表
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'user_refresh_tokens')
BEGIN
    CREATE TABLE user_refresh_tokens (
        id BIGINT IDENTITY(1,1) NOT NULL,
        user_id INT NOT NULL,
        token_hash NVARCHAR(256) NOT NULL,
        expires_at DATETIME2 NOT NULL,
        created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
        created_by_ip NVARCHAR(50) NULL,
        revoked_at DATETIME2 NULL,
        revoked_by_ip NVARCHAR(50) NULL,
        replaced_by_token NVARCHAR(256) NULL,
        CONSTRAINT pk_user_refresh_tokens PRIMARY KEY (id),
        CONSTRAINT fk_user_refresh_tokens_users FOREIGN KEY (user_id) 
            REFERENCES users(id) ON DELETE CASCADE
    );
    
    -- 创建索引
    CREATE INDEX ix_user_refresh_tokens_user_id ON user_refresh_tokens(user_id);
    CREATE INDEX ix_user_refresh_tokens_token_hash ON user_refresh_tokens(token_hash);
    CREATE INDEX ix_user_refresh_tokens_expires_at ON user_refresh_tokens(expires_at);
    
    PRINT N'创建用户刷新令牌表 user_refresh_tokens 成功';
END
ELSE
BEGIN
    PRINT N'用户刷新令牌表 user_refresh_tokens 已存在';
END
GO

-- 添加表注释
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'user_refresh_tokens')
BEGIN
    -- 检查是否已存在表注释
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = 0 
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'用户刷新令牌表，存储 Refresh Token 信息用于双 Token 认证机制', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens';
    END
    
    -- 添加列注释
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'id')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'主键ID', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'id';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'user_id')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'用户ID', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'user_id';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'token_hash')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'Token 哈希值（SHA256）', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'token_hash';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'expires_at')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'过期时间', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'expires_at';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'created_at')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'创建时间', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'created_at';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'created_by_ip')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'创建时的 IP 地址', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'created_by_ip';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'revoked_at')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'撤销时间', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'revoked_at';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'revoked_by_ip')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'撤销时的 IP 地址', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'revoked_by_ip';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('user_refresh_tokens') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('user_refresh_tokens') AND name = 'replaced_by_token')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'被替换的新 Token 哈希值（用于 Token 轮换追踪）', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'user_refresh_tokens',
            @level2type = N'COLUMN', @level2name = N'replaced_by_token';
    END
END
GO

PRINT N'用户刷新令牌表迁移脚本执行完成';
GO