-- 检查字段是否存在，不存在则添加StoreQrCode字段
IF NOT EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'sms_Consumer' 
    AND COLUMN_NAME = 'StoreQrCode'
)
BEGIN
    ALTER TABLE sms_Consumer 
    ADD StoreQrCode NVARCHAR(500)
    
    PRINT 'StoreQrCode字段已成功添加到sms_Consumer表'
END
ELSE
BEGIN
    PRINT 'StoreQrCode字段已存在于sms_Consumer表中'
END

-- 添加字段说明（扩展属性）
IF EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'sms_Consumer' 
    AND COLUMN_NAME = 'StoreQrCode'
)
BEGIN
    -- 检查是否已存在扩展属性，存在则更新，不存在则添加
    IF EXISTS (
        SELECT 1 
        FROM sys.extended_properties 
        WHERE major_id = OBJECT_ID('sms_Consumer') 
        AND minor_id = COLUMNPROPERTY(OBJECT_ID('sms_Consumer'), 'StoreQrCode', 'ColumnId')
        AND name = 'MS_Description'
    )
    BEGIN
        EXEC sys.sp_updateextendedproperty 
            @name = N'MS_Description',
            @value = N'门店小程序码',
            @level0type = N'SCHEMA',
            @level0name = N'dbo',
            @level1type = N'TABLE',
            @level1name = N'sms_Consumer',
            @level2type = N'COLUMN',
            @level2name = N'StoreQrCode'
    END
    ELSE
    BEGIN
        EXEC sys.sp_addextendedproperty 
            @name = N'MS_Description',
            @value = N'门店小程序码',
            @level0type = N'SCHEMA',
            @level0name = N'dbo',
            @level1type = N'TABLE',
            @level1name = N'sms_Consumer',
            @level2type = N'COLUMN',
            @level2name = N'StoreQrCode'
    END
    
    PRINT 'StoreQrCode字段说明已成功添加'
END