SET NOCOUNT ON

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_DefaultProductNumber'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_DefaultProductNumber INT NULL 
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_DefaultRoomType'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_DefaultRoomType INT NULL 
GO

DECLARE @SystemDate CHAR(8)
SELECT @SystemDate = SYSTEMDATE FROM System WITH (NOLOCK) WHERE NUMBER = 1

UPDATE CustomerOTA SET FK_DefaultProductNumber = 
(
  SELECT TOP 1 Products.ProductNumber 
  FROM Products WITH (NOLOCK)
  INNER JOIN ProductHotelXTAB WITH (NOLOCK) ON ProductHotelXTAB.ProductNumber = Products.ProductNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ProductHotelXTab.HotelCustomerNumber
    AND Customer.CustomerNumber = CustomerOTA.CustomerNumber 
    AND Customer.CustomerTypeNumber = 7
  WHERE Products.DayProduct = 0
  AND Products.MarkAsDeleted = 0
  AND Products.ProductDate <= @SystemDate
  AND Products.ProductEndDate >= @SystemDate
  AND ISNULL(Products.IsOwnerProduct, 0) = 0
  ORDER BY Products.ProductNumber
)
WHERE FK_DefaultProductNumber IS NULL

-- Handle Customer OTA records that isn't a Hotel. Don't know if it is a error solely in KA db
UPDATE CustomerOTA SET FK_DefaultProductNumber = 
(
  SELECT TOP 1 Products.ProductNumber 
  FROM Products WITH (NOLOCK)
  WHERE Products.DayProduct = 0
  AND Products.MarkAsDeleted = 0
  AND Products.ProductDate <= @SystemDate
  AND Products.ProductEndDate >= @SystemDate
  AND ISNULL(Products.IsOwnerProduct, 0) = 0
  ORDER BY Products.ProductNumber
)
WHERE FK_DefaultProductNumber IS NULL

-- Handle if no room product
UPDATE CustomerOTA SET FK_DefaultProductNumber = 
(
  SELECT TOP 1 Products.ProductNumber 
  FROM Products WITH (NOLOCK)
  WHERE Products.DayProduct = 1
  AND Products.MarkAsDeleted = 0
  AND Products.ProductDate <= @SystemDate
  AND Products.ProductEndDate >= @SystemDate
  AND ISNULL(Products.IsOwnerProduct, 0) = 0
  ORDER BY Products.ProductNumber
)
WHERE FK_DefaultProductNumber IS NULL

UPDATE CustomerOTA SET FK_DefaultRoomType = 
(
  SELECT TOP 1 RoomTypes.RoomTypeNumber 
  FROM RoomTypes WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = RoomTypes.HotelNumber 
    AND Customer.CustomerTypeNumber = 7
	AND Customer.CustomerNumber = CustomerOTA.CustomerNumber
  ORDER BY SortNumber
)
WHERE FK_DefaultRoomType IS NULL

-- Handle Customer OTA records that isn't a Hotel. Don't know if it is a error solely in KA db
UPDATE CustomerOTA SET FK_DefaultRoomType = 
(
  SELECT TOP 1 RoomTypes.RoomTypeNumber 
  FROM RoomTypes WITH (NOLOCK)
  ORDER BY SortNumber
)
WHERE FK_DefaultRoomType IS NULL


IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FK_DefaultExtra'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_DefaultExtra INT NULL 
GO
