-- BA/20111020: CR034836/Extending the Campaign code with Product, Allocation profile & Allotment
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'CampaignCodesHotel')
  CREATE TABLE [dbo].[CampaignCodesHotel](
	  [PK_CampaignCode] [smallint] NOT NULL,
	  [FK_HotelCustomerNumber] [int] NOT NULL,
	  [FK_ProductNumber] [int] NULL,
	  [FK_AllocationProfileNumber] [int] NULL,
	  [FK_AllotmentNumber] [int] NULL,
	  [DiscountCustomerType] [smallint] NOT NULL,
   CONSTRAINT [PK_CampaignCodesHotel] PRIMARY KEY CLUSTERED 
  (
	  [PK_CampaignCode] ASC,
	  [FK_HotelCustomerNumber] ASC
  )  ON [PRIMARY]
  ) ON [PRIMARY]
  GO

-- Create new FK_HotelCustomerNumber to the CampaignCodesDiscount table.
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_HotelCustomerNumber'
    WHERE SysObjects.Name = 'CampaignCodesDiscount'
)
  ALTER TABLE CampaignCodesDiscount ADD FK_HotelCustomerNumber INT
  GO

-- Assume that the Discounts belongs to the first hotel in the chain.
IF (SELECT COUNT(*) FROM CampaignCodesDiscount WHERE FK_HotelCustomerNumber IS NULL) > 0
  UPDATE CampaignCodesDiscount 
  SET FK_HotelCustomerNumber = (SELECT TOP 1 CustomerNumber 
                                FROM CUSTOMER 
                                WHERE (HotelNumber>0 OR LocalHotel=1) AND ISNULL(Customer.MarkAsDeleted, 0) = 0 
                                ORDER BY HotelNumber)
  GO

-- Assume that the CampaignCodesHotel table was just created if it's empty
-- and there exists records in the CampaignCodesDiscount table.
-- Create the missing records for the CampaignCodesHotel table. Assuming that 
-- the Discounts belongs to the first hotel in the Chain.
IF 
  ((SELECT COUNT(*) FROM CampaignCodesHotel) <= 0) AND
  ((SELECT COUNT(*) FROM CampaignCodesDiscount) > 0) 
BEGIN
  DECLARE 
    @FirstHotelCustomerNumber INT,
    @PK_CampaignCode          INT,
    @PK_HotelCustomerNumber   INT
  
  SELECT TOP 1 @FirstHotelCustomerNumber = CustomerNumber FROM CUSTOMER WHERE (HotelNumber>0 OR LocalHotel=1) AND ISNULL(Customer.MarkAsDeleted, 0) = 0 ORDER BY HotelNumber
  
  DECLARE DiscountCur CURSOR FOR
    SELECT DISTINCT PK_CampaignCode, FK_HotelCustomerNumber
    FROM CampaignCodesDiscount WITH (NOLOCK)
  
  OPEN DiscountCur

  FETCH NEXT FROM DiscountCur
  INTO @PK_CampaignCode, @PK_HotelCustomerNumber
  
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    INSERT INTO CampaignCodesHotel (PK_CampaignCode, FK_HotelCustomerNumber, DiscountCustomerType)
    VALUES (@PK_CampaignCode, @PK_HotelCustomerNumber, 0 /*atGuest*/)
    
    FETCH NEXT FROM DiscountCur
    INTO @PK_CampaignCode, @PK_HotelCustomerNumber
  END
  
  CLOSE DiscountCur
  DEALLOCATE DiscountCur
END

-- Check if primary key is OK on CampaignCodesDiscount
IF NOT EXISTS
(
  SELECT K.TABLE_NAME, K.COLUMN_NAME, K.CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C 
  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
    ON C.TABLE_NAME = K.TABLE_NAME AND 
       C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND 
       C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND 
       C.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
  WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND 
        K.TABLE_NAME = 'CampaignCodesDiscount' AND
        K.COLUMN_NAME = 'FK_HotelCustomerNumber'
)
  ALTER TABLE [dbo].[CampaignCodesDiscount] DROP CONSTRAINT [FK_CampaignCodesDiscount_CampaignCodes]
  GO

-- Create new primary key on CampaignCodesDiscount if it was dropped.
IF NOT EXISTS
(
  SELECT K.TABLE_NAME, K.COLUMN_NAME, K.CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C 
  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
    ON C.TABLE_NAME = K.TABLE_NAME AND 
       C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG AND 
       C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA AND 
       C.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
  WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY' AND 
        K.TABLE_NAME = 'CampaignCodesDiscount' AND
        K.COLUMN_NAME = 'FK_HotelCustomerNumber'
)
  ALTER TABLE [dbo].[CampaignCodesDiscount] DROP CONSTRAINT PK_CampaignCodesDiscount
  GO

-- Check if primary key Constraint was deleted above. If it was then create a new primary key.
ALTER TABLE CampaignCodesDiscount ALTER COLUMN FK_HotelCustomerNumber INT NOT NULL 
GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'PK_CampaignCodesDiscount')
  ALTER TABLE CampaignCodesDiscount 
  ADD CONSTRAINT PK_CampaignCodesDiscount PRIMARY KEY (PK_CampaignCode, FK_HotelCustomerNumber, DiscountType)
 GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_CampaignCodesDiscount_CampaignCodesHotel')
  ALTER TABLE [dbo].[CampaignCodesDiscount]  WITH CHECK ADD  CONSTRAINT [FK_CampaignCodesDiscount_CampaignCodesHotel] 
  FOREIGN KEY([PK_CampaignCode], [FK_HotelCustomerNumber])
  REFERENCES [dbo].[CampaignCodesHotel] ([PK_CampaignCode], [FK_HotelCustomerNumber])
  GO

IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_CampaignCodesDiscount_CampaignCodes')
ALTER TABLE [dbo].[CampaignCodesDiscount]  WITH CHECK ADD  CONSTRAINT [FK_CampaignCodesDiscount_CampaignCodes] FOREIGN KEY([PK_CampaignCode])
REFERENCES [dbo].[CampaignCodes] ([PK_CampaignCode])
GO
 
