HaniBlindBox/server/HoneyBox/scripts/create_goods_designated_prizes.sql

-- 创建指定中奖配置表
-- 用于配置特定奖品只能被指定用户抽中

-- 创建指定中奖配置表
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'goods_designated_prizes')
BEGIN
    CREATE TABLE goods_designated_prizes (
        id INT IDENTITY(1,1) NOT NULL,
        goods_id INT NOT NULL,
        goods_item_id INT NOT NULL,
        user_id INT NOT NULL,
        is_active BIT NOT NULL DEFAULT 1,
        remark NVARCHAR(200) NULL,
        created_at DATETIME2 NOT NULL DEFAULT GETDATE(),
        updated_at DATETIME2 NULL,
        CONSTRAINT pk_goods_designated_prizes PRIMARY KEY (id),
        CONSTRAINT fk_goods_designated_prizes_goods FOREIGN KEY (goods_id) 
            REFERENCES goods(id) ON DELETE CASCADE,
        CONSTRAINT fk_goods_designated_prizes_goods_items FOREIGN KEY (goods_item_id) 
            REFERENCES goods_items(id) ON DELETE NO ACTION,
        CONSTRAINT fk_goods_designated_prizes_users FOREIGN KEY (user_id) 
            REFERENCES users(id) ON DELETE NO ACTION,
        CONSTRAINT uq_goods_designated_prizes_goods_item UNIQUE (goods_id, goods_item_id)
    );
    
    -- 创建索引
    CREATE INDEX ix_goods_designated_prizes_goods_id ON goods_designated_prizes(goods_id);
    CREATE INDEX ix_goods_designated_prizes_user_id ON goods_designated_prizes(user_id);
    CREATE INDEX ix_goods_designated_prizes_goods_item_id ON goods_designated_prizes(goods_item_id);
    
    PRINT N'创建指定中奖配置表 goods_designated_prizes 成功';
END
ELSE
BEGIN
    PRINT N'指定中奖配置表 goods_designated_prizes 已存在';
END
GO

-- 添加表注释
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'goods_designated_prizes')
BEGIN
    -- 检查是否已存在表注释
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = 0 
        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'goods_designated_prizes';
    END

    -- 添加列注释
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') 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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'id';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') AND name = 'goods_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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'goods_id';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') AND name = 'goods_item_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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'goods_item_id';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') 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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'user_id';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') AND name = 'is_active')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = N'是否启用 1-启用 0-禁用', 
            @level0type = N'SCHEMA', @level0name = N'dbo',
            @level1type = N'TABLE', @level1name = N'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'is_active';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') AND name = 'remark')
        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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'remark';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') 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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'created_at';
    END
    
    IF NOT EXISTS (
        SELECT * FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('goods_designated_prizes') 
        AND minor_id = (SELECT column_id FROM sys.columns WHERE object_id = OBJECT_ID('goods_designated_prizes') AND name = 'updated_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'goods_designated_prizes',
            @level2type = N'COLUMN', @level2name = N'updated_at';
    END
END
GO

PRINT N'指定中奖配置表迁移脚本执行完成';
GO