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 = 'OTAMealType'
    WHERE SysObjects.Name = 'ProductExtended'
)
BEGIN
  ALTER TABLE ProductExtended ADD OTAMealType INT NULL 
END
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_CancelPolicyProfileItemId'
    WHERE SysObjects.Name = 'ProductExtended'
)
BEGIN
  ALTER TABLE ProductExtended ADD FK_CancelPolicyProfileItemId INT NULL 
END
GO

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

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

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

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

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ConfirmationMail'
    WHERE SysObjects.Name = 'ReportAutomation'
)
BEGIN
  ALTER TABLE ReportAutomation ADD ConfirmationMail VARCHAR(100) NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FKAllotmentNumber'
    WHERE SysObjects.Name = 'Location'
)
BEGIN
  ALTER TABLE Location ADD FKAllotmentNumber INT NULL 
END
GO
IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    WHERE SysObjects.Name = 'CardTypeExt'
)
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'ProvideBonus'
      WHERE SysObjects.Name = 'CardTypeExt'
  )
  BEGIN
    ALTER TABLE CardTypeExt ADD ProvideBonus BIT NULL 
  END
END
GO

IF EXISTS (SELECT * FROM sys.tables (NOLOCK) WHERE name = 'RateLevelRoomTypeRestrictionXTab')
BEGIN
	IF NOT EXISTS
	(
	  SELECT SysColumns.Name 
	  FROM SysObjects WITH (NOLOCK) 
		INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
		AND SysColumns.Name = 'RoomsAvailableRoomType'
		WHERE SysObjects.Name = 'RateLevelRoomTypeRestrictionXTab'
	)
	BEGIN
	  EXEC('ALTER TABLE RateLevelRoomTypeRestrictionXTab ADD RoomsAvailableRoomType INT NULL')
	END
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'LocationType'
    WHERE SysObjects.Name = 'Location'
)
BEGIN
  ALTER TABLE Location ADD LocationType SMALLINT NULL 
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ExternalWareType'
    WHERE SysObjects.Name = 'GovermentWareType'
)
BEGIN
  ALTER TABLE GovermentWareType ADD ExternalWareType SMALLINT NULL 
END

IF EXISTS (SELECT * FROM sys.tables (NOLOCK) WHERE name = 'RateLevelRoomTypeRestrictionXTab')
BEGIN
	IF NOT EXISTS
	(
	  SELECT SysColumns.Name 
	  FROM SysObjects WITH (NOLOCK) 
		INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
		AND SysColumns.Name = 'RoomsAvailableTotal'
		WHERE SysObjects.Name = 'RateLevelRoomTypeRestrictionXTab'
	)
	BEGIN
	  ALTER TABLE RateLevelRoomTypeRestrictionXTab ADD RoomsAvailableTotal INT NULL 
	END
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'PullReservationSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD PullReservationSend BIT NULL 
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ProfileCreateNotifSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD ProfileCreateNotifSend BIT NULL 
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ProfileReadNotifSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD ProfileReadNotifSend BIT NULL 
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'IsCreate'
    WHERE SysObjects.Name = 'OTAPostProfile'
)
BEGIN
  ALTER TABLE OTAPostProfile ADD IsCreate BIT NULL 
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NoBackgroundImage'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD NoBackgroundImage BIT NULL 
END

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

END
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_ReferenceNumber'
    WHERE SysObjects.Name = 'SMSLog'
)
BEGIN
  ALTER TABLE SMSLog ADD FK_ReferenceNumber INT NULL 
END
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_GuestNumber'
    WHERE SysObjects.Name = 'SMSLog'
)
BEGIN
  ALTER TABLE SMSLog ADD FK_GuestNumber INT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Accept3PartyReservations'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
  ALTER TABLE Customer ADD Accept3PartyReservations BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ControlNo'
    WHERE SysObjects.Name = 'Card'
)
BEGIN
  ALTER TABLE Card ADD ControlNo VARCHAR(25) NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'OwnerCalcVATIncluded'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
  ALTER TABLE Customer ADD OwnerCalcVATIncluded BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'RateIncludeRelated'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD RateIncludeRelated BIT NULL 
END
GO
UPDATE CustomerOTA SET RateIncludeRelated = 1 WHERE RateIncludeRelated IS 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 = 'LoyaltyCertificateRedemptionNoNegativ'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD LoyaltyCertificateRedemptionNoNegativ BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AnonymizeDate'
    WHERE SysObjects.Name = 'Transactions'
)
BEGIN
  ALTER TABLE Transactions ADD AnonymizeDate DATETIME NULL 
END
GO

ALTER TABLE Transactions ALTER COLUMN TextOverwrite VARCHAR(150) NOT 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 = 'CardDataAnonymizeDate'
    WHERE SysObjects.Name = 'Reservations'
)
BEGIN
  ALTER TABLE Reservations ADD CardDataAnonymizeDate DATETIME NULL 
END
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductProductDiscountXTab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[ProductProductDiscountXTab]
  (
    [FK_ProductNumber] [int] NOT NULL,
    [FK_ProductNumberDiscounted] [int] NOT NULL,
    [Discountvalue] [money] NULL,
    [DiscountType] [int] NULL,
    [UserIDNumber] [int] NULL,
    [ChangedDatetime] [datetime] NULL,
    [PaxType] [int] NOT NULL,
    CONSTRAINT [PK_ProductProductDiscountXTab] PRIMARY KEY CLUSTERED 
    (
      [FK_ProductNumber] ASC,
      [FK_ProductNumberDiscounted] ASC,
      [PaxType] ASC
    )
  ) ON [PRIMARY]
END
GO

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

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='ProductProductDiscountXTab' AND COLUMN_NAME ='PaxType' AND IS_NULLABLE = 'YES')
BEGIN
  UPDATE ProductProductDiscountXTab SET PaxType = 0 WHERE PaxType IS NULL
  ALTER TABLE ProductProductDiscountXTab ALTER COLUMN PaxType INT NOT NULL 
END
GO

IF NOT EXISTS (SELECT *
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
  AND TABLE_NAME = 'ProductProductDiscountXTab' AND TABLE_SCHEMA = 'dbo'
  AND COLUMN_NAME = 'PaxType')
BEGIN
  ALTER TABLE ProductProductDiscountXTab DROP CONSTRAINT PK_ProductProductDiscountXTab
  ALTER TABLE ProductProductDiscountXTab ADD CONSTRAINT [PK_ProductProductDiscountXTab] PRIMARY KEY CLUSTERED (FK_ProductNumber ASC, FK_ProductNumberDiscounted ASC, PaxType ASC) 
END
GO

IF NOT EXISTS (SELECT *
  FROM sys.indexes ind 
  INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
  INNER JOIN sys.tables t ON ind.object_id = t.object_id 
  WHERE ind.is_primary_key = 0 
  AND ind.is_unique_constraint = 0 
  AND t.is_ms_shipped = 0 
  AND t.name = 'ProductProductDiscountXTab'
  AND col.name = 'PaxType')
BEGIN
  IF EXISTS (SELECT Name FROM SysIndexes  WITH (NOLOCK) WHERE Name = 'IX_ProductProductDiscountXTab')
    DROP INDEX [IX_ProductProductDiscountXTab] ON dbo.ProductProductDiscountXTab
  CREATE UNIQUE NONCLUSTERED INDEX [IX_ProductProductDiscountXTab] ON [dbo].[ProductProductDiscountXTab]
  (
    [FK_ProductNumberDiscounted] ASC,
    [PaxType] ASC
  )
END
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_AllotmentNumber'
    WHERE SysObjects.Name = 'ProductExtended'
)
BEGIN
  ALTER TABLE ProductExtended ADD FK_AllotmentNumber INT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'UseInWareReportExport'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD UseInWareReportExport BIT NULL 
END
GO

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

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReservationCreateDate'
    WHERE SysObjects.Name = 'Reservations'
)
BEGIN
  ALTER TABLE Reservations ADD ReservationCreateDate DATETIME NULL
END
GO

DECLARE @default_name VARCHAR(256)
SELECT @default_name = [name] 
FROM sys.default_constraints
WHERE parent_object_id = object_id('Reservations')
AND col_name(parent_object_id, parent_column_id) = 'ReservationCreateDate'
IF ISNULL(@default_name, '') = ''
  ALTER TABLE Reservations ADD CONSTRAINT DF_Reservations_ReservationCreateDate DEFAULT GETDATE() FOR ReservationCreateDate;
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'RecurringType'
    WHERE SysObjects.Name = 'RecurringPattern'
)
BEGIN
  ALTER TABLE RecurringPattern ADD RecurringType TINYINT NULL 
END
GO

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

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

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

DECLARE @DataSize INT
SELECT @DataSize = COLUMNPROPERTY(OBJECT_ID('CustomerOTA'), 'HTNGToSystemID', 'PRECISION')
IF (@DataSize < 200)
  ALTER TABLE CustomerOTA ALTER COLUMN HTNGToSystemID VARCHAR(200) NULL
GO

DECLARE @DataSize INT
SELECT @DataSize = COLUMNPROPERTY(OBJECT_ID('CustomerOTA'), 'HTNGFromSystemID', 'PRECISION')
IF (@DataSize < 200)
  ALTER TABLE CustomerOTA ALTER COLUMN HTNGFromSystemID VARCHAR(200) 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 = 'OwnerCalcProvision'
    WHERE SysObjects.Name = 'Products'
)
BEGIN
  ALTER TABLE Products ADD OwnerCalcProvision BIT NULL 
END
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_StaffId'
    WHERE SysObjects.Name = 'MISHistory'
)
BEGIN
  ALTER TABLE MISHistory ADD FK_StaffId INT NOT NULL DEFAULT -1
  ALTER TABLE MISHistory WITH NOCHECK ADD CONSTRAINT [FK_MISHistory_Personel] FOREIGN KEY([FK_StaffId]) REFERENCES [dbo].[Personel] ([UserIDNumber])
  ALTER TABLE MISHistory NOCHECK CONSTRAINT [FK_MISHistory_Personel]
END
GO

IF EXISTS (
  SELECT * 
  FROM sys.foreign_keys 
  WHERE Name = 'FK_MISHistory_Personel'
  AND is_disabled=0
)
BEGIN
  ALTER TABLE MISHistory DROP CONSTRAINT FK_MISHistory_Personel
  ALTER TABLE MISHistory WITH NOCHECK ADD CONSTRAINT [FK_MISHistory_Personel] FOREIGN KEY([FK_StaffId]) REFERENCES [dbo].[Personel] ([UserIDNumber])
  ALTER TABLE MISHistory NOCHECK CONSTRAINT [FK_MISHistory_Personel]
END
GO

IF EXISTS
(
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'MISHistory'
  AND COLUMN_NAME = 'FK_StaffId'
  AND IS_NULLABLE = 'YES'
)
BEGIN
  UPDATE MISHistory SET FK_StaffId = -1 WHERE FK_StaffId IS NULL
  ALTER TABLE MISHistory ALTER COLUMN FK_StaffId INT NOT NULL
END
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_ReferenceNumber'
    WHERE SysObjects.Name = 'MISHistory'
)
BEGIN
  ALTER TABLE MISHistory ADD FK_ReferenceNumber INT NOT NULL DEFAULT -1
  ALTER TABLE MISHistory WITH NOCHECK ADD CONSTRAINT [FK_MISHistory_ReservationMain] FOREIGN KEY([FK_ReferenceNumber]) REFERENCES [dbo].[ReservationMain] ([REFERENCENUMBER])
  ALTER TABLE MISHistory NOCHECK CONSTRAINT [FK_MISHistory_ReservationMain]
END
GO

IF EXISTS (
  SELECT * 
  FROM sys.foreign_keys 
  WHERE Name = 'FK_MISHistory_ReservationMain'
  AND is_disabled=0
)
BEGIN
  ALTER TABLE MISHistory DROP CONSTRAINT FK_MISHistory_ReservationMain
  ALTER TABLE MISHistory WITH NOCHECK ADD CONSTRAINT [FK_MISHistory_ReservationMain] FOREIGN KEY([FK_ReferenceNumber]) REFERENCES [dbo].[ReservationMain] ([REFERENCENUMBER])
  ALTER TABLE MISHistory NOCHECK CONSTRAINT [FK_MISHistory_ReservationMain]
END
GO

IF EXISTS
(
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'MISHistory'
  AND COLUMN_NAME = 'FK_ReferenceNumber'
  AND IS_NULLABLE = 'YES'
)
BEGIN
  UPDATE MISHistory SET FK_ReferenceNumber = -1 WHERE FK_ReferenceNumber IS NULL
  ALTER TABLE MISHistory ALTER COLUMN FK_ReferenceNumber INT NOT NULL
END
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_WareNumber'
    WHERE SysObjects.Name = 'MISHistory'
)
BEGIN
  ALTER TABLE MISHistory ADD FK_WareNumber INT NOT NULL DEFAULT -1
  ALTER TABLE MISHistory WITH NOCHECK ADD CONSTRAINT [FK_MISHistory_Ware] FOREIGN KEY([FK_WareNumber]) REFERENCES [dbo].[Ware] ([WareNumber])
  ALTER TABLE MISHistory NOCHECK CONSTRAINT [FK_MISHistory_Ware]
END
GO

IF EXISTS (
  SELECT * 
  FROM sys.foreign_keys 
  WHERE Name = 'FK_MISHistory_Ware'
  AND is_disabled=0
)
BEGIN
  ALTER TABLE MISHistory DROP CONSTRAINT FK_MISHistory_Ware
  ALTER TABLE MISHistory WITH NOCHECK ADD CONSTRAINT [FK_MISHistory_Ware] FOREIGN KEY([FK_WareNumber]) REFERENCES [dbo].[Ware] ([WareNumber])
  ALTER TABLE MISHistory NOCHECK CONSTRAINT [FK_MISHistory_Ware]
END
GO

IF EXISTS
(
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'MISHistory'
  AND COLUMN_NAME = 'FK_WareNumber'
  AND IS_NULLABLE = 'YES'
)
BEGIN
  UPDATE MISHistory SET FK_WareNumber = -1 WHERE FK_WareNumber IS NULL
  ALTER TABLE MISHistory ALTER COLUMN FK_WareNumber INT NOT NULL
END
GO

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

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

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

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

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

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'IsWorkList'
    WHERE SysObjects.Name = 'ReportAutomation'
)
BEGIN
  ALTER TABLE ReportAutomation ADD IsWorkList BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'PullRateSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD PullRateSend BIT NULL 
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AnonymizeDate'
    WHERE SysObjects.Name = 'Invoice'
)
BEGIN
  ALTER TABLE Invoice ADD AnonymizeDate DATETIME NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AnonymizeDate'
    WHERE SysObjects.Name = 'Folios'
)
BEGIN
  ALTER TABLE Folios ADD AnonymizeDate DATETIME NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'SubType2'
    WHERE SysObjects.Name = 'Followup'
)
BEGIN
  ALTER TABLE Followup ADD SubType2 SMALLINT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
  INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
  AND SysColumns.Name = 'ExcludeMonaCheckinNotPaidSMS'
  WHERE SysObjects.Name = 'Rooms'
)
BEGIN
  ALTER TABLE Rooms ADD ExcludeMonaCheckinNotPaidSMS BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
  INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
  AND SysColumns.Name = 'ExcludeMonaCheckoutNotPaidSMS'
  WHERE SysObjects.Name = 'Rooms'
)
BEGIN
  ALTER TABLE Rooms ADD ExcludeMonaCheckoutNotPaidSMS BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
  INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
  AND SysColumns.Name = 'ExcludeMonaCreateNotPaidSMS'
  WHERE SysObjects.Name = 'Rooms'
)
BEGIN
  ALTER TABLE Rooms ADD ExcludeMonaCreateNotPaidSMS BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
  INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
  AND SysColumns.Name = 'ExcludeMonaCreatePerPaymentSMS'
  WHERE SysObjects.Name = 'Rooms'
)
BEGIN
  ALTER TABLE Rooms ADD ExcludeMonaCreatePerPaymentSMS BIT NULL 
END
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_ProductNumberMIS'
  WHERE SysObjects.Name = 'Allotments'
)
BEGIN
  ALTER TABLE Allotments ADD FK_ProductNumberMIS INT NULL 
  ALTER TABLE [dbo].[Allotments]  WITH NOCHECK ADD  CONSTRAINT [FK_Allotments_Products1] FOREIGN KEY([FK_ProductNumberMIS])
  REFERENCES [dbo].[Products] ([ProductNumber])
END
GO

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

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

UPDATE CustomerOTA SET HotelResNotifTimeOut = 2460 WHERE HotelResNotifTimeOut IS 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 = 'MaxNumberOfElement'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD MaxNumberOfElement INT NULL 
END
GO

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

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

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

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'DateOfLastLogin'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD DateOfLastLogin DATETIME NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Hall'
    WHERE SysObjects.Name = 'OTAPostHotelInvCountNotif'
)
BEGIN
  ALTER TABLE OTAPostHotelInvCountNotif ADD Hall BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'HotelInvCountNotifForHalls'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD HotelInvCountNotifForHalls BIT NULL 
END
GO
UPDATE Personel 
SET DateOfLastLogin = GETDATE()
WHERE DateOfLastLogin 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 = 'MonetExtraHallName'
    WHERE SysObjects.Name = 'RESERVATIONCONFERENCELINE'
)
BEGIN
  ALTER TABLE RESERVATIONCONFERENCELINE ADD MonetExtraHallName VARCHAR(255) NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'TwoFactorLogin'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD TwoFactorLogin BIT NULL 
END
GO

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'MultipleRestrictions'
    WHERE SysObjects.Name = 'RateLevelChannel'
)
BEGIN
  ALTER TABLE RateLevelChannel ADD MultipleRestrictions BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AllowFilterPicassoOnlineHall'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD AllowFilterPicassoOnlineHall BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NoPasswordExpire'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD NoPasswordExpire BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ReservationTypeCSV'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
  ALTER TABLE Templates ADD ReservationTypeCSV VARCHAR(100) NULL 
END
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_GuestTypeNumber'
    WHERE SysObjects.Name = 'MISTable'
)
BEGIN
  ALTER TABLE MISTable ADD FK_GuestTypeNumber INT NULL
END
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_PurposeCodeNumber'
    WHERE SysObjects.Name = 'MISTable'
)
BEGIN
  ALTER TABLE MISTable ADD FK_PurposeCodeNumber INT NULL
END
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_CountryNumber'
    WHERE SysObjects.Name = 'MISTable'
)
BEGIN
  ALTER TABLE MISTable ADD FK_CountryNumber INT NULL
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'OnlineDescription'
    WHERE SysObjects.Name = 'OnlineWareDelivery'
)
BEGIN
  ALTER TABLE OnlineWareDelivery ADD OnlineDescription NVARCHAR(MAX)
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'AllotmentCSV'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
  ALTER TABLE Templates ADD AllotmentCSV VARCHAR(100) NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ConfirmDateStamp'
    WHERE SysObjects.Name = 'ReservationsExt'
)
BEGIN
  ALTER TABLE ReservationsExt ADD ConfirmDateStamp DATETIME NULL 
END
GO

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'DateOfBirth'
    WHERE SysObjects.Name = 'ArrangementLinesTicket'
)
BEGIN
  ALTER TABLE ArrangementLinesTicket ADD DateOfBirth DATETIME NULL 
END
ELSE
BEGIN
  IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ArrangementLinesTicket' AND COLUMN_NAME = 'DateOfBirth') = 'DATE'
    ALTER TABLE ArrangementLinesTicket ALTER COLUMN DateOfBirth DATETIME NULL
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'DateOfBirthUsed'
    WHERE SysObjects.Name = 'WareTicket'
)
BEGIN
  ALTER TABLE WareTicket ADD DateOfBirthUsed BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'DateOfBirthRequired'
    WHERE SysObjects.Name = 'WareTicket'
)
BEGIN
  ALTER TABLE WareTicket ADD DateOfBirthRequired BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ShowAdult'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD ShowAdult BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ShowChild1'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD ShowChild1 BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ShowChild2'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD ShowChild2 BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ChangeAdult'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD ChangeAdult BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ChangeChild1'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD ChangeChild1 BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ChangeChild2'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD ChangeChild2 BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'EchoTokenResend'
    WHERE SysObjects.Name = 'OTAPostMain'
)
BEGIN
  ALTER TABLE OTAPostMain ADD EchoTokenResend VARCHAR(255) NULL
END
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_CampaignCodeId'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD FK_CampaignCodeId SMALLINT NULL
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'ExternalControlCode'
    WHERE SysObjects.Name = 'GiftToken'
)
BEGIN
  ALTER TABLE GiftToken ADD ExternalControlCode VARCHAR(100) NULL
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'HideTablet'
    WHERE SysObjects.Name = 'GDPRApprove'
)
BEGIN
  ALTER TABLE GDPRApprove ADD HideTablet BIT NULL
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'NumberOfTickets'
    WHERE SysObjects.Name = 'WareAddon'
)
BEGIN
  ALTER TABLE WareAddon ADD NumberOfTickets INT NULL
END
GO
UPDATE WareAddon SET NumberOfTickets = 1 WHERE NumberOfTickets IS NULL
ALTER TABLE WareAddon ALTER COLUMN NumberOfTickets INT NOT NULL

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

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

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

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'UseForStatistic'
    WHERE SysObjects.Name = 'ConferenceRoomSetup'
)
BEGIN
  ALTER TABLE ConferenceRoomSetup ADD UseForStatistic BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'HotelStayInfoChainCode'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD HotelStayInfoChainCode VARCHAR(50)
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'HotelStayInfoHotelCodeContext'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD HotelStayInfoHotelCodeContext VARCHAR(50)
END

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'FK_ProjectCode'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
  ALTER TABLE CampaignCodes ADD FK_ProjectCode SMALLINT NULL
  ALTER TABLE CampaignCodes ADD CONSTRAINT FK_CampaignCodes_ProjectCodes
    FOREIGN KEY (FK_ProjectCode) REFERENCES ProjectCodes (PK_ProjectCode)
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'TicketDirectCheckin'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD TicketDirectCheckin BIT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'TicketScanQuantity'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD TicketScanQuantity TINYINT NULL 
END
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'MetaSearchUseYield'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD MetaSearchUseYield BIT NULL 
END
