IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'colorRGB')
BEGIN
	CREATE TABLE [dbo].[ColorRGB](
		[ColorRGBID] [int] IDENTITY(1,1) NOT NULL,
		[Red] [int] NOT NULL,
		[Green] [int] NOT NULL,
		[Blue] [int] NOT NULL,
	 CONSTRAINT [PK_ColorRGB_New] PRIMARY KEY CLUSTERED 
	(
		[ColorRGBID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[ColorRGB] ADD  DEFAULT ((0)) FOR [Red]
	ALTER TABLE [dbo].[ColorRGB] ADD  DEFAULT ((0)) FOR [Green]
	ALTER TABLE [dbo].[ColorRGB] ADD  DEFAULT ((0)) FOR [Blue]
END


IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'color')
BEGIN
	CREATE TABLE [dbo].[Color](
		[ColorID] [int] IDENTITY(1,1) NOT NULL,
		[ColorName] [varchar](50) NOT NULL,
		[AutoDayColor] [bit] NOT NULL,
		[SortOrder] [int] NOT NULL,
		[SetupSortOrder] [int] NOT NULL,
		[YieldFilterColor] [bit] NOT NULL,
		[FK_ColorRGBID] [int] NOT NULL,
		[YieldFilterText] [varchar](50) NULL,
	 CONSTRAINT [PK_Color] PRIMARY KEY CLUSTERED 
	(
		[ColorID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[Color] ADD  DEFAULT ('') FOR [ColorName]

	ALTER TABLE [dbo].[Color] ADD  DEFAULT ((0)) FOR [AutoDayColor]

	ALTER TABLE [dbo].[Color] ADD  DEFAULT ((0)) FOR [SortOrder]

	ALTER TABLE [dbo].[Color] ADD  DEFAULT ((999)) FOR [SetupSortOrder]

	ALTER TABLE [dbo].[Color] ADD  DEFAULT ((0)) FOR [YieldFilterColor]

	ALTER TABLE [dbo].[Color]  WITH CHECK ADD  CONSTRAINT [FK_Color_ColorRGB] FOREIGN KEY([FK_ColorRGBID])
	REFERENCES [dbo].[ColorRGB] ([ColorRGBID])

	ALTER TABLE [dbo].[Color] CHECK CONSTRAINT [FK_Color_ColorRGB]

END

IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'ColorHotelXTAB')
BEGIN
	CREATE TABLE ColorHotelXTAB (
		ColorHotelXTABID INT IDENTITY(1,1) NOT NULL, 
		FK_ColorID INT NOT NULL,
		HotelCustomerNumber INT NOT NULL,
		FK_ColorRGBID INT NOT NULL,
		OnlineColor BIT Not NULL DEFAULT 0,
		Description VARCHAR(50) NULL,
		CONSTRAINT PK_ColorHotelXTAB  
				   PRIMARY KEY CLUSTERED (ColorHotelXTABID)  
				   WITH (IGNORE_DUP_KEY = OFF),
		CONSTRAINT [FK_ColorHotelXTAB_Color] FOREIGN KEY([FK_ColorID])
		REFERENCES [dbo].[Color] ([ColorID])
	) 

	CREATE UNIQUE INDEX UIX_ColorHotelXtab ON ColorHotelXTAB (FK_ColorID ASC, HotelCustomerNumber ASC)
END

IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'HotelColorXTab')
BEGIN
  CREATE TABLE [dbo].[HotelColorXTab]
  (
    [FKColorId] [int] NOT NULL,
    [FKHotelCustomerNumber] [int] NOT NULL,
    CONSTRAINT [PK_HotelColorXTab] PRIMARY KEY CLUSTERED 
    (
      [FKColorId] ASC,
      [FKHotelCustomerNumber] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[HotelColorXTab]  WITH CHECK ADD  CONSTRAINT [FK_HotelColorXTab_Color] FOREIGN KEY([FKColorId])
  REFERENCES [dbo].[Color] ([ColorID])

  ALTER TABLE [dbo].[HotelColorXTab] CHECK CONSTRAINT [FK_HotelColorXTab_Color]

  ALTER TABLE [dbo].[HotelColorXTab]  WITH CHECK ADD  CONSTRAINT [FK_HotelColorXTab_Customer] FOREIGN KEY([FKHotelCustomerNumber])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[HotelColorXTab] CHECK CONSTRAINT [FK_HotelColorXTab_Customer]

  DECLARE @CustomerNumber INT
  DECLARE @ColorId INT

  DECLARE db_cur CURSOR FOR
    SELECT CustomerNumber
    FROM Customer WITH (NOLOCK)
    WHERE CustomerTypeNumber = 7
    AND ISNULL(HotelNumber, -1) >= 0
    AND ISNULL(MarkAsDeleted, 0) = 0
  OPEN db_cur
  FETCH NEXT FROM db_cur INTO @CustomerNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    DECLARE db_color CURSOR FOR
      SELECT ColorId
      FROM Color WITH (NOLOCK)
    OPEN db_color

    FETCH NEXT FROM db_color INTO @ColorId

    WHILE @@FETCH_STATUS = 0
    BEGIN
      INSERT INTO HotelColorXTab (FKColorId, FKHotelCustomerNumber) VALUES (@ColorId, @CustomerNumber)
      FETCH NEXT FROM db_color INTO @ColorId
    END

    CLOSE db_color
    DEALLOCATE db_color

    FETCH NEXT FROM db_cur INTO @CustomerNumber
  END

  CLOSE db_cur
  DEALLOCATE db_cur
END

IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'YieldColorXTAB')
BEGIN
	CREATE TABLE [dbo].[ProductColorXTAB](
		[ProductNumber] [int] NOT NULL,
		[ColorID] [int] NOT NULL,
	 CONSTRAINT [PK_ProductColorXTAB] PRIMARY KEY CLUSTERED 
	(
		[ProductNumber] ASC,
		[ColorID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[ProductColorXTAB]  WITH NOCHECK ADD  CONSTRAINT [FK_ProductColorXTAB_Color] FOREIGN KEY([ColorID])
	REFERENCES [dbo].[Color] ([ColorID])

	ALTER TABLE [dbo].[ProductColorXTAB] CHECK CONSTRAINT [FK_ProductColorXTAB_Color]

	ALTER TABLE [dbo].[ProductColorXTAB]  WITH NOCHECK ADD  CONSTRAINT [FK_ProductColorXTAB_Products] FOREIGN KEY([ProductNumber])
	REFERENCES [dbo].[Products] ([ProductNumber])

	ALTER TABLE [dbo].[ProductColorXTAB] CHECK CONSTRAINT [FK_ProductColorXTAB_Products]
END

IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'YieldColorXTAB')
BEGIN

	CREATE TABLE [dbo].[YieldColorXTAB](
		[YieldNumber] [int] NOT NULL,
		[ColorID] [int] NOT NULL,
	 CONSTRAINT [PK_YieldColorXTAB] PRIMARY KEY CLUSTERED 
	(
		[YieldNumber] ASC,
		[ColorID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[YieldColorXTAB]  WITH NOCHECK ADD  CONSTRAINT [FK_YieldColorXTAB_Products] FOREIGN KEY([YieldNumber])
	REFERENCES [dbo].[Yield] ([YieldNumber])

	ALTER TABLE [dbo].[YieldColorXTAB] CHECK CONSTRAINT [FK_YieldColorXTAB_Products]
END

IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'SalesChannelExtraFields_old')
BEGIN
	EXEC sp_rename 'dbo.SalesChannelExtraFields.PK_SalesChannelExtraFields', 'PK_SalesChannelExtraFields_old'
	EXEC sp_rename 'dbo.SalesChannelExtraFields', 'SalesChannelExtraFields_old'
	CREATE TABLE [dbo].[SalesChannelExtraFields](
		[CustomerNumber] [int] NOT NULL,
		[FK_ColorID] [int] NOT NULL,
		[Availability] [int] NULL,
	 CONSTRAINT [PK_SalesChannelExtraFields] PRIMARY KEY CLUSTERED 
	(
		[CustomerNumber] ASC,
		[FK_ColorID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[SalesChannelExtraFields] ADD  DEFAULT ((0)) FOR [FK_ColorID]
END


