-- 创建 goods_collections 表（商品收藏表）
-- 用于存储用户收藏的商品信息

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[goods_collections]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[goods_collections] (
        [id] INT IDENTITY(1,1) NOT NULL,
        [user_id] INT NOT NULL,
        [goods_id] INT NOT NULL,
        [num] INT NOT NULL DEFAULT 0,
        [type] TINYINT NOT NULL DEFAULT 1,
        [created_at] DATETIME2 NOT NULL DEFAULT GETDATE(),
        CONSTRAINT [pk_goods_collections] PRIMARY KEY CLUSTERED ([id] ASC)
    );

    -- 创建索引
    CREATE NONCLUSTERED INDEX [ix_goods_collections_user_id] ON [dbo].[goods_collections] ([user_id] ASC);
    CREATE NONCLUSTERED INDEX [ix_goods_collections_goods_id] ON [dbo].[goods_collections] ([goods_id] ASC);
    CREATE UNIQUE NONCLUSTERED INDEX [uk_goods_collections_user_goods_num] ON [dbo].[goods_collections] ([user_id] ASC, [goods_id] ASC, [num] ASC);

    PRINT 'Table goods_collections created successfully.';
END
ELSE
BEGIN
    PRINT 'Table goods_collections already exists.';
END
GO

-- 添加表注释
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'商品收藏表，存储用户收藏的商品信息', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'goods_collections';
GO