-- =============================================
-- Create Table: mstProductCompetitorSet
-- Purpose: Stores competitive product relationships (up to 10 per primary product)
-- Created: [Date]
-- =============================================

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mstProductCompetitorSet]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[mstProductCompetitorSet](
        [ProductCompetitorSetID] [INT] IDENTITY(1,1) NOT NULL,
        [strPrimaryProdCode] [NVARCHAR](6) NOT NULL,
        [strCompProdCode] [NVARCHAR](6) NOT NULL,
        [intPosition] [TINYINT] NOT NULL,
        [blnActive] [BIT] NOT NULL DEFAULT(1),
        [strLastUser] [NVARCHAR](50) NULL,
        [dtStamp] [NVARCHAR](14) NULL,
        CONSTRAINT [PK_mstProductCompetitorSet] PRIMARY KEY CLUSTERED ([ProductCompetitorSetID] ASC)
    ) ON [PRIMARY]
    
    -- Foreign Key: Primary Product Code references mstProduct
    ALTER TABLE [dbo].[mstProductCompetitorSet]
    ADD CONSTRAINT [FK_mstProductCompetitorSet_PrimaryProduct] 
    FOREIGN KEY([strPrimaryProdCode])
    REFERENCES [dbo].[mstProduct] ([strProdCode])
    
    -- Foreign Key: Competitive Product Code references mstProduct
    ALTER TABLE [dbo].[mstProductCompetitorSet]
    ADD CONSTRAINT [FK_mstProductCompetitorSet_CompProduct] 
    FOREIGN KEY([strCompProdCode])
    REFERENCES [dbo].[mstProduct] ([strProdCode])
    
    -- Unique Constraint: Prevent duplicate comp products per primary product
    ALTER TABLE [dbo].[mstProductCompetitorSet]
    ADD CONSTRAINT [UQ_mstProductCompetitorSet_PrimaryComp] 
    UNIQUE ([strPrimaryProdCode], [strCompProdCode])
    
    -- Check Constraint: Position must be between 1 and 10
    ALTER TABLE [dbo].[mstProductCompetitorSet]
    ADD CONSTRAINT [CK_mstProductCompetitorSet_Position] 
    CHECK ([intPosition] >= 1 AND [intPosition] <= 10)
    
    -- Check Constraint: Prevent self-reference (primary product cannot be its own competitor)
    ALTER TABLE [dbo].[mstProductCompetitorSet]
    ADD CONSTRAINT [CK_mstProductCompetitorSet_NoSelfReference] 
    CHECK ([strPrimaryProdCode] <> [strCompProdCode])
    
    -- Index: For quick retrieval by primary product and position
    CREATE NONCLUSTERED INDEX [IX_mstProductCompetitorSet_PrimaryPosition] 
    ON [dbo].[mstProductCompetitorSet] ([strPrimaryProdCode] ASC, [intPosition] ASC)
    
    -- Index: For reverse lookups (find which products have this as a competitor)
    CREATE NONCLUSTERED INDEX [IX_mstProductCompetitorSet_CompProduct] 
    ON [dbo].[mstProductCompetitorSet] ([strCompProdCode] ASC)
    
    -- Index: For filtering active records
    CREATE NONCLUSTERED INDEX [IX_mstProductCompetitorSet_Active] 
    ON [dbo].[mstProductCompetitorSet] ([strPrimaryProdCode] ASC, [blnActive] ASC)
    
    PRINT 'Table mstProductCompetitorSet created successfully.'
END
ELSE
BEGIN
    PRINT 'Table mstProductCompetitorSet already exists.'
END
GO

