-- BA: Renaming the old OTALog save us a convert script to the new system and
-- ensures that we keep the old history for debugging, etc.
--
-- Important: DO NOT Convert new OTALog again if the conversion already took place.
--            At some point old OTALog.81 will be erased but including OTABaseLog as
--            a criteria will ensure that the conversion is stopped.
IF
  (NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'OTALog_81')) and 
  (NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'OTALogBase'))
BEGIN
  EXEC sp_rename 'OTALog', 'OTALog_81';

-- Aviod problem with the naming of the primary key.
  ALTER TABLE OTALog_81
  DROP CONSTRAINT PK_OTALog
END

-- BA/20160310
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'RequestorID'
    WHERE SysObjects.Name = 'Reservations'
)
ALTER TABLE Reservations ADD RequestorID INT NULL 
GO

-- BA/20160310
IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_Reservations_OTARequestor')
ALTER TABLE [dbo].[Reservations]  WITH NOCHECK ADD  CONSTRAINT [FK_Reservations_OTARequestor] FOREIGN KEY([RequestorID])
REFERENCES [dbo].[OTARequestor] ([RequestorID])
GO


-- BA/20160310: Add RequestorID to RoomBookingStatus



IF NOT EXISTS
(
	SELECT SysColumns.Name 
	FROM SysObjects(NOLOCK) 
	INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
	AND SysColumns.Name = 'RequestorID'
	WHERE SysObjects.Name = 'RoomBookingStatus'
)
BEGIN
	ALTER TABLE RoomBookingStatus ADD RequestorID INT NOT NULL DEFAULT 0

	IF NOT EXISTS 
	  (SELECT * 
	   FROM 
		 INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
		 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
	   WHERE 
		 Col.Constraint_Name = Tab.Constraint_Name AND 
		 Col.Table_Name = Tab.Table_Name AND 
		 Constraint_Type = 'PRIMARY KEY' AND 
		 Col.Table_Name = 'RoomBookingStatus' AND 
		 Col.Column_Name = 'RequestorID')
	BEGIN
	  ALTER TABLE RoomBookingStatus DROP CONSTRAINT PK_ROOMBOOKINGSTATUS
	  IF EXISTS
		(
		SELECT SysColumns.Name 
		FROM SysObjects(NOLOCK) 
		INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
		AND SysColumns.Name = 'ProductNumber'
		WHERE SysObjects.Name = 'RoomBookingStatus'
		)
		ALTER TABLE RoomBookingStatus ADD CONSTRAINT PK_ROOMBOOKINGSTATUS PRIMARY KEY 
		([DateStamp] ASC, 
		 [RoomTypeNumber] ASC, 
		 [SegmentNumber] ASC, 
		 [PointOfSaleNumber] ASC, 
		 [HotelNumber] ASC, 
		 [SoldRoomType] ASC,
  		 [ProductNumber] ASC,
		 [ReservationGroup] ASC,
  		 [RequestorID] ASC)
	ELSE 
		ALTER TABLE RoomBookingStatus ADD CONSTRAINT PK_ROOMBOOKINGSTATUS PRIMARY KEY 
		([DateStamp] ASC, 
		 [RoomTypeNumber] ASC, 
		 [SegmentNumber] ASC, 
		 [PointOfSaleNumber] ASC, 
		 [HotelNumber] ASC, 
		 [SoldRoomType] ASC,
		 [ReservationGroup] ASC,
  		 [RequestorID] ASC)
	END
END