SET NOCOUNT ON

IF EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    WHERE SysObjects.Name = 'RoomTypeDifferentialUpdateLog'
)
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name
    FROM SysObjects WITH (NOLOCK)
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
      AND SysColumns.Name = 'ShowRoomOption'
      WHERE SysObjects.Name = 'RoomTypeDifferentialUpdateLog'
  )
    ALTER TABLE RoomTypeDifferentialUpdateLog ADD ShowRoomOption SMALLINT 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 = '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 = 'MarkAsDeleted'
      WHERE SysObjects.Name = 'RoomTypeDifferential'
  )
    ALTER TABLE RoomTypeDifferential ADD MarkAsDeleted 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_RecurringPatternSpecifiedId'
    WHERE SysObjects.Name = 'ReportAutomationLog'
)
  ALTER TABLE ReportAutomationLog ADD FK_RecurringPatternSpecifiedId INT NULL
GO

IF NOT EXISTS
(
  SELECT *
  FROM sys.indexes
  WHERE name='IX_ReportAutomationLogRecurPatternSpecifiedId' AND object_id = OBJECT_ID('ReportAutomationLog')
)
  CREATE NONCLUSTERED INDEX [IX_ReportAutomationLogRecurPatternSpecifiedId] ON [dbo].[ReportAutomationLog]
  (
	  [FK_RecurringPatternSpecifiedId] DESC
  )
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'SSLMode'
    WHERE SysObjects.Name = 'ReportAutomationFTPSetting'
)
  ALTER TABLE ReportAutomationFTPSetting ADD SSLMode BIT 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 = 'TLSMode'
    WHERE SysObjects.Name = 'ReportAutomationFTPSetting'
)
  ALTER TABLE ReportAutomationFTPSetting ADD TLSMode BIT NULL
GO

IF EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    WHERE SysObjects.Name = 'RateLevelChannel'
)
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name
    FROM SysObjects WITH (NOLOCK)
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
      AND SysColumns.Name = 'ChannelType'
      WHERE SysObjects.Name = 'RateLevelChannel'
  )
    ALTER TABLE RateLevelChannel ADD ChannelType 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 = '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 = 'DayType'
      WHERE SysObjects.Name = 'RoomTypeDifferential'
  )
    ALTER TABLE RoomTypeDifferential ADD DayType 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 = '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 = 'Color'
      WHERE SysObjects.Name = 'RoomTypeDifferential'
  )
    ALTER TABLE RoomTypeDifferential ADD Color 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 = 'RatioInPercent'
    WHERE SysObjects.Name = 'AdditionalSalesFromTurnover'
)
  ALTER TABLE AdditionalSalesFromTurnover ADD RatioInPercent BIT 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 = 'DiscountFlat'
    WHERE SysObjects.Name = 'CustomerFieldsPerHotel'
)
BEGIN
  ALTER TABLE CustomerFieldsPerHotel ADD DiscountFlat Money 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 = 'FlatDiscountRate'
    WHERE SysObjects.Name = 'ReservationAddress'
)
  ALTER TABLE ReservationAddress ADD FlatDiscountRate Money 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 = 'CreditorId'
    WHERE SysObjects.Name = 'Invoice'
)
  ALTER TABLE Invoice ADD CreditorId varchar(8) 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 = 'GiroId'
    WHERE SysObjects.Name = 'Invoice'
)
  ALTER TABLE Invoice ADD GiroId varchar(21) 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 = 'StatusRL'
    WHERE SysObjects.Name = 'ReservationRoomline'
)
  ALTER TABLE ReservationRoomline ADD StatusRL int null
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OnlineHallImagesXTab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[OnlineHallImagesXTab](
		[ConferenceRoomNumber] int NOT NULL,
		[ImageName] VARCHAR(255) NOT NULL,
                [SortOrder] int NOT NULL

	CONSTRAINT [PK_OnlineHallsImagesXTab_1] PRIMARY KEY CLUSTERED
	(
		[ConferenceRoomNumber], [ImageName] ASC
	)
)

	ALTER TABLE [dbo].[OnlineHallImagesXTab]  WITH NOCHECK ADD  CONSTRAINT [FK_OnlineHallImages_OnlineHalls] FOREIGN KEY([ConferenceRoomNumber])
	REFERENCES [dbo].[ConferenceRooms] ([ConferenceRoomNumber])

	ALTER TABLE [dbo].[OnlineHallImagesXTab] CHECK CONSTRAINT [FK_OnlineHallImages_OnlineHalls]

END

GO

alter table ConferenceRooms alter column Description VarChar(Max)
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'OnlineBookingTimeInterval'
    WHERE SysObjects.Name = 'ConferenceRooms'
)
  ALTER TABLE ConferenceRooms ADD OnlineBookingTimeInterval 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 = 'UseType'
    WHERE SysObjects.Name = 'RoomTypes'
)
  ALTER TABLE RoomTypes ADD UseType int not null default 0

GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'AutoAssignRoom'
    WHERE SysObjects.Name = 'RoomTypes'
)
  ALTER TABLE RoomTypes ADD AutoAssignRoom BIT not null default 0

GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'AutoCheckIn'
    WHERE SysObjects.Name = 'RoomTypes'
)
  ALTER TABLE RoomTypes ADD AutoCheckIn BIT not null default 0

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_ReservationGroup'
    WHERE SysObjects.Name = 'Reservations'
)
  ALTER TABLE Reservations ADD FK_ReservationGroup 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_ReservationGroup'
    WHERE SysObjects.Name = 'ReservationRoomLine'
)
  ALTER TABLE ReservationRoomLine ADD FK_ReservationGroup 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 = 'MailRule'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD MailRule TINYINT 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 = 'MailRuleConfirmations'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD MailRuleConfirmations TINYINT 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 = 'AutoConfirmed'
    WHERE SysObjects.Name = 'ReservationMain'
)
  ALTER TABLE ReservationMain ADD AutoConfirmed TINYINT NOT NULL DEFAULT 0
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'AutoConfirmed'
    WHERE SysObjects.Name = 'Reservations'
)
  ALTER TABLE Reservations ADD AutoConfirmed TINYINT NOT NULL DEFAULT 0
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'TemplateNumber'
    WHERE SysObjects.Name = 'Reservations'
)
BEGIN
  ALTER TABLE Reservations ADD TemplateNumber INTEGER
  ALTER TABLE Reservations ADD CONSTRAINT FK_Reservations_TemplateNumber
    FOREIGN KEY (TemplateNumber)
    REFERENCES Templates (TemplateNumber)
END
GO

IF EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'ScreenDump'
    WHERE SysObjects.Name = 'ErrorLog'
)
  ALTER TABLE ErrorLog ALTER COLUMN ScreenDump image
GO

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

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'Active'
    WHERE SysObjects.Name = 'Templates'
)
  ALTER TABLE Templates ADD Active BIT NOT NULL DEFAULT 1
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'Invoice'
    WHERE SysObjects.Name = 'Templates'
)
  ALTER TABLE Templates ADD Invoice BIT NOT NULL DEFAULT 0
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'KeyCode'
    WHERE SysObjects.Name = 'ROOMS'
)
  ALTER TABLE Rooms ADD KeyCode VarChar(50) 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 = 'Digicode'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD Digicode VARCHAR(20) 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 = 'OnlineProductSortNumber'
    WHERE SysObjects.Name = 'ProductExtended'
)
  ALTER TABLE ProductExtended ADD OnlineProductSortNumber 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 = 'Parking'
    WHERE SysObjects.Name = 'Rooms'
)
  ALTER TABLE Rooms ADD Parking VARCHAR(20) NULL
GO

-- RQ3043: Alter ReservationAddress.BuyersOrderID from size 15 to 30.
DECLARE @DataSize INT
SELECT @DataSize = COLUMNPROPERTY(OBJECT_ID('ReservationAddress'), 'BuyersOrderID', 'PRECISION')
IF (@DataSize < 30)
ALTER TABLE ReservationAddress
  ALTER COLUMN BuyersOrderID VARCHAR(30) NULL
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ViewLog1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[ViewLog1](
	[FkViewLineId] int NOT NULL,
	[ViewId] int NOT NULL,
    [FkPicassoLog] int NOT NULL

	CONSTRAINT [PK_ViewLog1] PRIMARY KEY CLUSTERED
	(
		[FkViewLineId], [ViewId], [FkPicassoLog] 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 = 'IsDefaultForRateLevel'
    WHERE SysObjects.Name = 'Products'
)
  ALTER TABLE Products ADD IsDefaultForRateLevel BIT NULL
GO

ALTER TABLE Yield ALTER COLUMN FilterColor VARCHAR(20) 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 = 'NumberOfRoomsAllowed'
    WHERE SysObjects.Name = 'RoomTypes'
)
  ALTER TABLE RoomTypes ADD NumberOfRoomsAllowed 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 = 'ReportAutomationType'
    WHERE SysObjects.Name = 'ReportAutomation'
)
  ALTER TABLE ReportAutomation ADD ReportAutomationType SMALLINT NULL
GO

UPDATE ReportAutomation
SET ReportAutomationType = 0
WHERE ReportAutomationType 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 = 'OutboundOverbookData'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD OutboundOverbookData BIT 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 = 'CentralizedAllotment'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD CentralizedAllotment BIT 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 = 'DaysToSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD DaysToSend 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_RoomTypeDifferential'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_RoomTypeDifferential 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_Payment'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_Payment 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_PaymentInhouse'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_PaymentInhouse 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 = 'AlwaysPostInvCountNotif'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD AlwaysPostInvCountNotif BIT 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 = 'InventoryPerCountType'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD InventoryPerCountType BIT 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 = 'AdultRateToSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD AdultRateToSend 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 = 'RateAmountAsRatePlanNotif'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD RateAmountAsRatePlanNotif BIT 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 = 'OTA_HotelResNotifBeforeArrDays'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD OTA_HotelResNotifBeforeArrDays 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 = 'Type_'
    WHERE SysObjects.Name = 'Templates'
)
  ALTER TABLE Templates ADD Type_ 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 = 'WindaId'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD WindaId VARCHAR(20) 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 = 'GWOExpiryDate'
    WHERE SysObjects.Name = 'ReservationMeetingXTab'
)
  ALTER TABLE ReservationMeetingXTab ADD GWOExpiryDate datetime 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_DefaultDayProductNumber'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD FK_DefaultDayProductNumber 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 = 'MaxPax'
    WHERE SysObjects.Name = 'ReservationConferenceLine'
)
  ALTER TABLE ReservationConferenceLine ADD MaxPax 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 = 'FIUSYS_Type'
    WHERE SysObjects.Name = 'ProductFIUSYSXtab'
)
BEGIN
	IF EXISTS(
		SELECT * FROM SysObjects WITH (NOLOCK)
		WHERE SysObjects.Name = 'ProductFIUSYSXtab'
	)
		ALTER TABLE ProductFIUSYSXtab ADD FIUSYS_Type 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 = 'NumberOfRoomsSoldCourse'
    WHERE SysObjects.Name = 'RoomBookingStatus'
)
  ALTER TABLE RoomBookingStatus ADD NumberOfRoomsSoldCourse 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 = 'NumberOfRoomsSoldAttendee'
    WHERE SysObjects.Name = 'RoomBookingStatus'
)
  ALTER TABLE RoomBookingStatus ADD NumberOfRoomsSoldAttendee 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 = 'AddressHidden'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
  ALTER TABLE Customer ADD AddressHidden BIT NULL
  ALTER TABLE Customer ADD CONSTRAINT DF_Customer_AddressHidden DEFAULT 0 FOR AddressHidden
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 = 'AddressHidden'
    WHERE SysObjects.Name = 'ReservationAddress'
)
BEGIN
  ALTER TABLE ReservationAddress ADD AddressHidden BIT NULL
  ALTER TABLE ReservationAddress ADD CONSTRAINT DF_ReservationAddress_AddressHidden DEFAULT 0 FOR AddressHidden
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 = 'InitialNumberOfRooms'
    WHERE SysObjects.Name = 'ReservationRoomline'
)
BEGIN
  ALTER TABLE ReservationRoomline ADD InitialNumberOfRooms 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 = 'IsPiecesUserModified'
    WHERE SysObjects.Name = 'ArrangementLines'
)
BEGIN
  ALTER TABLE ArrangementLines ADD IsPiecesUserModified 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 = 'PiecesModifiedDiff'
    WHERE SysObjects.Name = 'ArrangementLines'
)
BEGIN
  ALTER TABLE ArrangementLines ADD PiecesModifiedDiff 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 = 'Entry'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD Entry 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 = 'CityTax'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD CityTax 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 = 'CityTax'
    WHERE SysObjects.Name = 'PurposeCodes'
)
BEGIN
  ALTER TABLE PurposeCodes ADD CityTax 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 = 'GDPRID'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD GDPRID VARCHAR(50) NULL
GO

/*
IF (SELECT CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Customer' AND COLUMN_NAME = 'GDPRID' AND DATA_TYPE = 'VARCHAR') = 30
BEGIN
  --PRINT 'Prolonging'
  ALTER TABLE Customer ALTER COLUMN GDPRID 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 = 'GDPRMailDate'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD GDPRMailDate SMALLDATETIME NULL
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects
               WHERE id = object_id(N'[dbo].[GDPRMail]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE dbo.GDPRMail
  (
    GDPRID VARCHAR(50) NOT NULL,
    GDPRMailDate SMALLDATETIME NOT NULL,
    GDPRMailType TINYINT NOT NULL,
    CustomerNumber INT NULL,
    ReferenceNumber INT NULL,
    GuestNumber SMALLINT NULL,
    CONSTRAINT FK_GDPRMail_CustomerFKey FOREIGN KEY (CustomerNumber) REFERENCES Customer (CustomerNumber),
    CONSTRAINT FK_GDPRMail_ReservationFKey FOREIGN KEY (ReferenceNumber, GuestNumber) REFERENCES Reservations (ReferenceNumber, GuestNumber))

    INSERT INTO GDPRMail (GDPRID, GDPRMailDate, GDPRMailType, CustomerNumber)

      SELECT GDPRID, GDPRMailDate, 1, CUSTOMERNUMBER
      FROM CUSTOMER (NOLOCK)
      WHERE NOT GDPRID IS NULL AND ISNULL(GDPRMailDate, 0) > 0
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 = 'GDPRMailAddress'
    WHERE SysObjects.Name = 'GDPRMail'
)
  ALTER TABLE GDPRMail ADD GDPRMailAddress VARCHAR (100) 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 = 'ExternalID'
    WHERE SysObjects.Name = 'Customer'
)
  ALTER TABLE Customer ADD ExternalID VARCHAR(60) NULL
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'Customer' AND COLUMN_NAME = 'ExternalID' AND DATA_TYPE = 'int')
BEGIN
  ALTER TABLE CUSTOMER ALTER COLUMN EXTERNALID VARCHAR(60)
END

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

UPDATE Terminals SET IntegratedCashBox = 1 WHERE IntegratedCashBox 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 = 'OTAPricePax'
    WHERE SysObjects.Name = 'RoomTypes'
)
BEGIN
  ALTER TABLE RoomTypes ADD OTAPricePax 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 = 'YPSingleRateNumberOfRooms'
    WHERE SysObjects.Name = 'RoomTypes'
)
BEGIN
  ALTER TABLE RoomTypes ADD YPSingleRateNumberOfRooms 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 = 'YPSingleRateNumberOfRooms'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD YPSingleRateNumberOfRooms INT NULL
END
GO

UPDATE CustomerOTA SET YPSingleRateNumberOfRooms = 5 WHERE YPSingleRateNumberOfRooms IS NULL
GO

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.OTAInvCountNotifLog') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name
    FROM SysObjects WITH (NOLOCK)
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
      AND SysColumns.Name = 'SubmitDateStamp'
      WHERE SysObjects.Name = 'OTAInvCountNotifLog'
  )
    ALTER TABLE OTAInvCountNotifLog ADD SubmitDateStamp 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 = 'SendRoomTypeAvailability'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD SendRoomTypeAvailability 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 = 'UseInStockExport'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD UseInStockExport 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 = 'Pinpadlogin'
    WHERE SysObjects.Name = 'Departments'
)
  ALTER TABLE Departments ADD PinpadLogin BIT 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 = 'PaymentBarVisible'
    WHERE SysObjects.Name = 'Terminals'
)
  ALTER TABLE Terminals ADD PaymentBarVisible BIT NULL
GO

IF EXISTS
(
  SELECT name
  FROM SysObjects
  WHERE name = 'Color'
)
	IF NOT EXISTS
	(
	  SELECT SysColumns.Name
	  FROM SysObjects WITH (NOLOCK)
		INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
		AND SysColumns.Name = 'YieldFilterText'
		WHERE SysObjects.Name = 'Color'
	)
	  ALTER TABLE Color ADD YieldFilterText VARCHAR(50) 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 = 'EnterGuestCountAtCheckin'
    WHERE SysObjects.Name = 'Departments'
)
  ALTER TABLE Departments ADD EnterGuestCountAtCheckin BIT 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 = 'CalendarVisible'
    WHERE SysObjects.Name = 'Terminals'
)
  ALTER TABLE Terminals ADD CalendarVisible BIT 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 = 'BarPaymentWidth'
    WHERE SysObjects.Name = 'Departments'
)
  ALTER TABLE Departments ADD BarPaymentWidth 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 = 'BarWareWidth'
    WHERE SysObjects.Name = 'Departments'
)
  ALTER TABLE Departments ADD BarWareWidth 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 = 'AutoCheckout'
    WHERE SysObjects.Name = 'Ware'
)
  ALTER TABLE Ware ADD AutoCheckout VARCHAR(50) 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 = 'FkKitchenNote'
    WHERE SysObjects.Name = 'Ware'
)
  ALTER TABLE Ware ADD FkKitchenNote 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 = 'PacketItemTime'
    WHERE SysObjects.Name = 'ArrangementLines'
)
  ALTER TABLE ArrangementLines ADD PacketItemTime CHAR(5) NULL
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CategoryPosXTab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[CategoryPosXTab](
	[FkCategory] int NOT NULL,
    [FkPos] int NOT NULL

	CONSTRAINT [PK_CategoryPosXTab] PRIMARY KEY CLUSTERED
	(
		[FkCategory], [FkPos]
	)
  )
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[ProductProductChangeXtab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ProductProductChangeXtab](
	[FK_ProductNumber] [int] NOT NULL,
	[FK_NewProductNumber] [int] NOT NULL,
	[Weekday] [int] NOT NULL,
 CONSTRAINT [PK_ProductProductChangeXtab] PRIMARY KEY CLUSTERED
(
	[FK_ProductNumber] ASC,
	[FK_NewProductNumber] ASC,
	[Weekday] ASC
)
) ON [PRIMARY]

ALTER TABLE [dbo].[ProductProductChangeXtab]  WITH NOCHECK ADD  CONSTRAINT [FK_ProductProductChangeXtab_ProductProductChangeXtab] FOREIGN KEY([FK_ProductNumber], [FK_NewProductNumber], [Weekday])
REFERENCES [dbo].[ProductProductChangeXtab] ([FK_ProductNumber], [FK_NewProductNumber], [Weekday])

ALTER TABLE [dbo].[ProductProductChangeXtab] CHECK CONSTRAINT [FK_ProductProductChangeXtab_ProductProductChangeXtab]
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 = 'ResponseXML'
    WHERE SysObjects.Name = 'DKStatSubmitLog'
)
  ALTER TABLE DKStatSubmitLog ADD ResponseXML VARCHAR(MAX) 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 = 'ExternalReceiptId'
    WHERE SysObjects.Name = 'DKStatSubmitLog'
)
  ALTER TABLE DKStatSubmitLog ADD ExternalReceiptId VARCHAR(500) 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 = 'SortNumber2'
    WHERE SysObjects.Name = 'CountryGroups'
)
  ALTER TABLE CountryGroups ADD SortNumber2 INT NULL
GO

UPDATE CountryGroups SET SortNumber2 = GroupSortNumber WHERE SortNumber2 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 = 'CountryGroupNumber2'
    WHERE SysObjects.Name = 'Countries'
)
BEGIN
  ALTER TABLE Countries ADD CountryGroupNumber2 INT NULL
END
GO

UPDATE Countries SET CountryGroupNumber2 = CountryGroupNumber WHERE CountryGroupNumber2 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 = 'DoAnonymize'
    WHERE SysObjects.Name = 'ReservationAddress'
)
  ALTER TABLE ReservationAddress ADD DoAnonymize BIT NOT NULL DEFAULT 0
GO

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

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TimeInterval]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[TimeInterval]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FromTime] [time](0) NOT NULL,
    [ToTime] [time](0) NOT NULL,
    [SortNumber] [int] NULL,
    CONSTRAINT [PK_TimeInterval] PRIMARY KEY CLUSTERED
    (
      [Id] ASC
    )
  ) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[HotelTrendLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[HotelTrendLog]
  (
    [LogDate] [datetime] NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [HotelNumberCSV] [varchar](100) NULL,
    [MISCalc] [bit] NULL
  ) 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 = 'AltResNoPostfix'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD AltResNoPostfix VARCHAR(50) 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 = 'ProfileModifyNotifSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD ProfileModifyNotifSend BIT 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 = 'PicassoDirectCheckoutSMS'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD PicassoDirectCheckoutSMS BIT 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 = 'Aux1'
    WHERE SysObjects.Name = 'OTAAliases'
)
  ALTER TABLE OTAAliases ADD Aux1 VARCHAR(100) 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 = 'Aux2'
    WHERE SysObjects.Name = 'OTAAliases'
)
  ALTER TABLE OTAAliases ADD Aux2 VARCHAR(100) 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 = 'Aux3'
    WHERE SysObjects.Name = 'OTAAliases'
)
  ALTER TABLE OTAAliases ADD Aux3 VARCHAR(100) 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 = 'GDPRMailBlocked'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
  ALTER TABLE Customer ADD GDPRMailBlocked BIT NULL
  ALTER TABLE Customer ADD CONSTRAINT DF_Customer_GDPRMailBlocked DEFAULT 0 FOR GDPRMailBlocked
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 = 'OutboundPerDay'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD OutboundPerDay BIT NULL
GO

-- CampaignCodes

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'BookingDateActive'
    WHERE SysObjects.Name = 'CampaignCodes'
)
  ALTER TABLE CampaignCodes ADD BookingDateActive BIT 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 = 'BookingFromDate'
    WHERE SysObjects.Name = 'CampaignCodes'
)
  ALTER TABLE CampaignCodes ADD BookingFromDate DATETIME 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 = 'BookingToDate'
    WHERE SysObjects.Name = 'CampaignCodes'
)
  ALTER TABLE CampaignCodes ADD BookingToDate DATETIME NULL
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'ArrangementLinesDepDeleted' AND COLUMN_NAME = 'PacketItemTime' AND DATA_TYPE = 'int')
BEGIN
  DROP INDEX [IX_ArrangementLinesDepDeleted_Remove] ON ArrangementLinesDepDeleted

  ALTER TABLE ArrangementLinesDepDeleted ALTER COLUMN PacketItemTime CHAR(5) NULL

  CREATE NONCLUSTERED INDEX [IX_ArrangementLinesDepDeleted_Remove] ON [dbo].[ArrangementLinesDepDeleted]
  (
	[ReferenceNumber] ASC,
	[GuestNumber] ASC,
	[Date] ASC,
	[Time] ASC,
	[DepartureTime] ASC,
	[PacketItemNumber] ASC,
	[WareNumber] ASC,
	[Price] ASC,
	[PacketItemTime] ASC,
	[FK_DepartmentNumber] 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 = 'TimeDT'
    WHERE SysObjects.Name = 'ArrangementLines'
)
  ALTER TABLE ArrangementLines ADD TimeDT DATETIME 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 = 'IsPlaceTextNeeded'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD IsPlaceTextNeeded 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 = 'PlaceTextDescription'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD PlaceTextDescription VARCHAR(MAX) 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 = 'BookTimeInMinutesOnExport'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD BookTimeInMinutesOnExport 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 = 'AllContactsLinkID'
    WHERE SysObjects.Name = 'ReservationAddressHidden'
)
BEGIN

	IF EXISTS(
		SELECT * FROM SysObjects WITH (NOLOCK)
		WHERE SysObjects.Name = 'ReservationAddressHidden'
	)
	BEGIN
		ALTER TABLE ReservationAddressHidden ADD AllContactsLinkID INT NULL

		ALTER TABLE [dbo].[ReservationAddressHidden]  WITH CHECK ADD  CONSTRAINT [FK_ReservationAddressHidden_Customer2] FOREIGN KEY([AllContactsLinkID])
		REFERENCES [dbo].[Customer] ([CustomerNumber])

		ALTER TABLE [dbo].[ReservationAddressHidden] CHECK CONSTRAINT [FK_ReservationAddressHidden_Customer2]
	END
END
GO

IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Ware'
  AND COLUMN_NAME = 'BookTimeInMinutes') = 'TINYINT'
BEGIN
  IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.Ware') AND NAME ='IX_Ware_BookTimeInMinuts')
    DROP INDEX IX_Ware_BookTimeInMinuts ON dbo.Ware;
  ALTER TABLE Ware ALTER COLUMN BookTimeInMinutes SMALLINT NULL
  IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.Ware') AND NAME ='IX_Ware_BookTimeInMinuts')
    CREATE NONCLUSTERED INDEX [IX_Ware_BookTimeInMinuts] ON [dbo].[Ware] ([BookTimeInMinutes] ASC)
END

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'Description3'
    WHERE SysObjects.Name = 'Translation'
)
BEGIN
  ALTER TABLE Translation ADD Description3 VARCHAR(1000) 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 = 'AllContactsLinkID'
    WHERE SysObjects.Name = 'ReservationAddress'
)
BEGIN
  ALTER TABLE ReservationAddress ADD AllContactsLinkID INT NULL
END


IF NOT EXISTS
(
SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='FK_ReservationAddress_Customer2'
)
BEGIN
  ALTER TABLE [dbo].[ReservationAddress]  WITH CHECK ADD  CONSTRAINT [FK_ReservationAddress_Customer2] FOREIGN KEY([AllContactsLinkID])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[ReservationAddress] CHECK CONSTRAINT [FK_ReservationAddress_Customer2]
END

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ReservationCustomerCodeXTAB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT 'FGFGSD'
	CREATE TABLE [dbo].[ReservationCustomerCodeXTAB](
		[CodeNumber] [int] NOT NULL,
		[CustomerNumber] [int] NOT NULL,
		[Kind] [int] NOT NULL,
		[Linked] [bit] NOT NULL,
	 CONSTRAINT [PK_ReservationCustomerCodeXTAB] PRIMARY KEY CLUSTERED
	(
		[CodeNumber] ASC,
		[CustomerNumber] ASC,
		[Kind] 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].[ReservationCustomerCodeXTAB]  WITH CHECK ADD  CONSTRAINT [FK_ReservationCustomerCodeXTAB_Customer] FOREIGN KEY([CustomerNumber])
	REFERENCES [dbo].[Customer] ([CustomerNumber])


	ALTER TABLE [dbo].[ReservationCustomerCodeXTAB] CHECK CONSTRAINT [FK_ReservationCustomerCodeXTAB_Customer]
END

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'IsChangedManually'
    WHERE SysObjects.Name = 'ReservationRoomline'
)
BEGIN
  ALTER TABLE ReservationRoomline ADD IsChangedManually 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 = 'Availability'
    WHERE SysObjects.Name = 'SalesChannelExtraFields'
)
BEGIN
  ALTER TABLE SalesChannelExtraFields ADD Availability 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 = 'SMTPPort'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPPort INT 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 = 'SMTPServer'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPServer VARCHAR(255) 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 = 'SMTPUsername'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPUsername VARCHAR(255) 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 = 'SMTPPassword'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPPassword VARCHAR(255) 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 = 'SMTPUseAuthentication'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPUseAuthentication 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 = 'SMTPUseEHLO'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPUseEHLO 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 = 'SMTPUseSASL'
    WHERE SysObjects.Name = 'Maillog'
)
BEGIN
  ALTER TABLE Maillog ADD SMTPUseSASL 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 = 'SMTPTLSSSLMode'
    WHERE SysObjects.Name = 'Maillog'
)
  ALTER TABLE Maillog ADD SMTPTLSSSLMode BIT 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 = 'IsUserModified'
    WHERE SysObjects.Name = 'ArrangementLines'
)
  ALTER TABLE ArrangementLines ADD IsUserModified BIT NULL
GO

IF EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'BonusPointRatio'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CustomerOTA' AND COLUMN_NAME = 'BonusPointRatio') <> 'float'
    ALTER TABLE CustomerOTA ALTER COLUMN BonusPointRatio FLOAT 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 = 'DoSendMail'
    WHERE SysObjects.Name = 'ReservationMain'
)
  ALTER TABLE ReservationMain ADD DoSendMail BIT NOT NULL DEFAULT (0)
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'DoSendMail'
    WHERE SysObjects.Name = 'Reservations'
)
  ALTER TABLE Reservations ADD DoSendMail BIT NOT NULL DEFAULT (0)
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'SendMailTemplateDefaultType'
    WHERE SysObjects.Name = 'Reservations'
)
  ALTER TABLE Reservations ADD SendMailTemplateDefaultType 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 = 'BonusPointRatio'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD BonusPointRatio FLOAT 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 = 'LoyaltyCertificateRedemptionSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD LoyaltyCertificateRedemptionSend BIT 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 = 'IsMandatory'
    WHERE SysObjects.Name = 'PacketItem_NEW'
)
  ALTER TABLE PacketItem_NEW ADD IsMandatory BIT 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 = 'ReadRQCPRSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD ReadRQCPRSend 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 = 'PriceIsUserOverwritten'
    WHERE SysObjects.Name = 'ReservationConferenceLine'
)
  ALTER TABLE ReservationConferenceLine ADD PriceIsUserOverwritten BIT 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 = 'PacketItemWarenumber'
    WHERE SysObjects.Name = 'ArrangementLines'
)
  ALTER TABLE ArrangementLines ADD PacketItemWarenumber 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 = 'PacketItemWaretext'
    WHERE SysObjects.Name = 'ArrangementLines'
)
  ALTER TABLE ArrangementLines ADD PacketItemWaretext VARCHAR(80) 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 = 'PacketItemPrice'
    WHERE SysObjects.Name = 'ArrangementLines'
)
  ALTER TABLE ArrangementLines ADD PacketItemPrice MONEY 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 = 'PacketItemWarenumber'
    WHERE SysObjects.Name = 'ArrangementLinesDeleted'
)
  ALTER TABLE ArrangementLinesDeleted ADD PacketItemWarenumber 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 = 'PacketItemWaretext'
    WHERE SysObjects.Name = 'ArrangementLinesDeleted'
)
  ALTER TABLE ArrangementLinesDeleted ADD PacketItemWaretext VARCHAR(80) 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 = 'PacketItemPrice'
    WHERE SysObjects.Name = 'ArrangementLinesDeleted'
)
  ALTER TABLE ArrangementLinesDeleted ADD PacketItemPrice MONEY 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 = 'PacketItemWarenumber'
    WHERE SysObjects.Name = 'ArrangementLinesDepDeleted'
)
  ALTER TABLE ArrangementLinesDepDeleted ADD PacketItemWarenumber 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 = 'PacketItemWaretext'
    WHERE SysObjects.Name = 'ArrangementLinesDepDeleted'
)
  ALTER TABLE ArrangementLinesDepDeleted ADD PacketItemWaretext VARCHAR(80) 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 = 'PacketItemPrice'
    WHERE SysObjects.Name = 'ArrangementLinesDepDeleted'
)
  ALTER TABLE ArrangementLinesDepDeleted ADD PacketItemPrice MONEY 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 = 'KeyPinCode'
    WHERE SysObjects.Name = 'ReservationRoomLine'
)

  ALTER TABLE ReservationRoomLine ADD KeyPinCode VARCHAR(50) NULL
GO

IF (SELECT CHARACTER_MAXIMUM_LENGTH
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Reservations' AND COLUMN_NAME = 'AltResNo' AND DATA_TYPE = 'VARCHAR') < 30

  ALTER TABLE Reservations ALTER COLUMN AltResNo VARCHAR(30)
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_ReservationGroup'
    WHERE SysObjects.Name = 'ReservationMeetingXtab'
)
  ALTER TABLE ReservationMeetingXtab ADD FK_ReservationGroup 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 = 'Active'
    WHERE SysObjects.Name = 'Allotments'
)
BEGIN
  ALTER TABLE Allotments ADD Active BIT NOT NULL DEFAULT (1)
END
GO

IF EXists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'ReservationsExt')
	IF NOT EXISTS
	(
	  SELECT SysColumns.Name
	  FROM SysObjects WITH (NOLOCK)
		INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
		AND SysColumns.Name = 'LatestBalance'
		WHERE SysObjects.Name = 'ReservationsExt'
	)
	BEGIN
	  ALTER TABLE ReservationsExt ADD LatestBalance money 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_ProjectCode'
    WHERE SysObjects.Name = 'Allotments'
)
BEGIN
  ALTER TABLE Allotments ADD FK_ProjectCode SMALLINT NULL
  ALTER TABLE Allotments ADD CONSTRAINT FK_Allotments_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 = 'CreationInProgress'
    WHERE SysObjects.Name = 'ReservationMain'
)
BEGIN
  ALTER TABLE ReservationMain ADD CreationInProgress 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 = 'MailType'
    WHERE SysObjects.Name = 'MailLog'
)
BEGIN
  ALTER TABLE Maillog ADD MailType int NULL
END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'ConferenceRooms' AND COLUMN_NAME = 'AllowedBookTimes' AND DATA_TYPE = 'TINYINT')
BEGIN
  ALTER TABLE ConferenceRooms ALTER COLUMN AllowedBookTimes Int NULL
END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'ConferenceRooms' AND COLUMN_NAME = 'Pax' AND DATA_TYPE = 'TINYINT')
BEGIN
  ALTER TABLE ConferenceRooms ALTER COLUMN Pax Int NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Terminals' AND COLUMN_NAME = 'LastLoginInfo')
  ALTER TABLE Terminals ADD LastLoginInfo NVARCHAR(250) NULL
ELSE IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Terminals' AND COLUMN_NAME = 'LastLoginInfo' AND DATA_TYPE = 'NVARCHAR' AND CHARACTER_MAXIMUM_LENGTH <> 250)
  ALTER TABLE Terminals ALTER COLUMN LastLoginInfo NVARCHAR(250)
GO

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'EmailPassword'
    WHERE SysObjects.Name = 'Personel'
)
BEGIN
  ALTER TABLE Personel ADD EmailPassword 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 = 'SignatureTitle'
    WHERE SysObjects.Name = 'Signature'
)
BEGIN
  ALTER TABLE Signature ADD SignatureTitle 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 = 'FilterColorID'
    WHERE SysObjects.Name = 'Yield'
)
BEGIN
  ALTER TABLE Yield ADD FilterColorID 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 = 'WareHasRule'
    WHERE SysObjects.Name = 'ArrangementLines'
)
BEGIN
  ALTER TABLE ArrangementLines ADD WareHasRule 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 = 'NoMandatoryEmail'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
  ALTER TABLE Customer ADD NoMandatoryEmail 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 = 'NoMandatoryEmail'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
  ALTER TABLE Customer ADD NoMandatoryEmail 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 = 'IsArrLineMoved'
    WHERE SysObjects.Name = 'ArrangementLines'
)
BEGIN
  ALTER TABLE ArrangementLines ADD IsArrLineMoved 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 = 'Aux4'
    WHERE SysObjects.Name = 'OTAAliases'
)
BEGIN
  ALTER TABLE OTAAliases ADD Aux4 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 = 'Aux5'
    WHERE SysObjects.Name = 'OTAAliases'
)
BEGIN
  ALTER TABLE OTAAliases ADD Aux5 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 = 'Aux6'
    WHERE SysObjects.Name = 'OTAAliases'
)
BEGIN
  ALTER TABLE OTAAliases ADD Aux6 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 = 'SendReminder'
    WHERE SysObjects.Name = 'ReservationAddress'
)
BEGIN
	ALTER TABLE dbo.ReservationAddress ADD SendReminder BIT NOT NULL DEFAULT 0
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 = 'CleaningStatus'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD CleaningStatus 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 = 'LockedForExternalUpdate'
    WHERE SysObjects.Name = 'Reservations'
)
BEGIN
	ALTER TABLE dbo.Reservations ADD LockedForExternalUpdate BIT NOT NULL DEFAULT 0
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 = 'EmailOffice365LoginUsername'
    WHERE SysObjects.Name = 'PERSONEL'
)
BEGIN
	ALTER TABLE dbo.PERSONEL ADD EmailOffice365LoginUsername 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 = 'EmailOffice365LoginPassword'
    WHERE SysObjects.Name = 'PERSONEL'
)
BEGIN
	ALTER TABLE dbo.PERSONEL ADD EmailOffice365LoginPassword 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 = 'DateOfBirth'
    WHERE SysObjects.Name = 'ReservationAddress'
)
BEGIN
	ALTER TABLE dbo.ReservationAddress ADD 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 = 'DaysBeforeArrival'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD DaysBeforeArrival 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 = 'AllowMonday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowMonday BIT NOT NULL DEFAULT 1
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 = 'AllowTuesday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowTuesday BIT NOT NULL DEFAULT 1
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 = 'AllowWednesday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowWednesday BIT NOT NULL DEFAULT 1
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 = 'AllowThursday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowThursday BIT NOT NULL DEFAULT 1
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 = 'AllowFriday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowFriday BIT NOT NULL DEFAULT 1
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 = 'AllowSaturday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowSaturday BIT NOT NULL DEFAULT 1
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 = 'AllowSunday'
    WHERE SysObjects.Name = 'CampaignCodes'
)
BEGIN
	ALTER TABLE dbo.CampaignCodes ADD AllowSunday BIT NOT NULL DEFAULT 1
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_PaymentMethod'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
	ALTER TABLE dbo.Templates ADD FK_PaymentMethod INT NULL
	ALTER TABLE [dbo].[Templates]  WITH CHECK ADD  CONSTRAINT [FK_Templates_Ware] FOREIGN KEY([FK_PaymentMethod])
		REFERENCES [dbo].[Ware] ([WareNumber])
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 = 'MailAttachments'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
	ALTER TABLE dbo.Templates ADD MailAttachments varchar(MAX) 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 = 'WareOnAllDays'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
	ALTER TABLE dbo.Ware ADD WareOnAllDays 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_KeySystemID'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
	ALTER TABLE dbo.Templates ADD FK_KeySystemID INT NULL
END
GO

if  Exists(SELECT * FROM sys.tables Where Name = 'TemplateSendSchedule')
	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 = 'TemplateSendSchedule'
	)
	BEGIN
	  ALTER TABLE TemplateSendSchedule ADD RecurringType 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 = 'KeyCode'
    WHERE SysObjects.Name = 'Customer'
)
BEGIN
	ALTER TABLE dbo.Customer ADD
		KeyCode varchar(50) NULL
END
GO
IF EXISTS (select *
from Transactions T (nolock)
  left join Folios F (NOLOCK) ON F.FolioNumber = T.FolioNumber
where F.FolioNumber is NULL)
BEGIN
DECLARE @Hcn INT
select @Hcn = ISNULL(Max(T.HotelCustomerNumber), -1)
from Transactions T (nolock)
  left join Folios F (NOLOCK) ON F.FolioNumber = T.FolioNumber
where F.FolioNumber is NULL
SET IDENTITY_INSERT dbo.Folios ON;
INSERT INTO folios (FolioNumber, OpenFolio, BillTypeNumber, HotelCustomerNumber)
select DISTINCT T.FolioNumber, 0, 0, CASE WHEN ISNULL(Max(T.HotelCustomerNumber), -1) = -1 THEN @Hcn ELSE Max(T.HotelCustomerNumber) END
from Transactions T (nolock)
  left join Folios F (NOLOCK) ON F.FolioNumber = T.FolioNumber
where F.FolioNumber is NULL
group by T.FolioNumber
SET IDENTITY_INSERT dbo.Folios OFF;
END
IF NOT EXISTS
(
SELECT *
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='FK_Transactions_Folios'
)
BEGIN
  ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD CONSTRAINT [FK_Transactions_Folios] FOREIGN KEY([FolioNumber])
  REFERENCES [dbo].[Folios] ([FolioNumber])
END
ELSE
BEGIN
  IF EXISTS
  (
    SELECT * FROM sys.foreign_keys
    WHERE Name = 'FK_Transactions_Folios'
    AND is_disabled = 1
  )
    ALTER TABLE Transactions WITH CHECK CHECK CONSTRAINT [FK_Transactions_Folios]
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_ProductNumber'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
  ALTER TABLE Templates ADD FK_ProductNumber 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 = 'ReservationStatus'
    WHERE SysObjects.Name = 'Templates'
)
BEGIN
  ALTER TABLE Templates ADD ReservationStatus INT NOT NULL DEFAULT -1
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'WareBillSortTypesXTab' AND COLUMN_NAME = 'GroupByPeriod')
BEGIN
  ALTER TABLE WareBillSortTypesXTab ADD GroupByPeriod TINYINT NULL
END
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'WaregroupBillSortTypesXTab' AND COLUMN_NAME = 'GroupByPeriod')
BEGIN
  ALTER TABLE WaregroupBillSortTypesXTab ADD GroupByPeriod TINYINT NULL
END
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'WareBillSortTypesXTab' AND COLUMN_NAME = 'FKBillSortPeriod')
BEGIN --Run update until (relation) foreign key field removed
  UPDATE LW SET GroupByPeriod = CASE WHEN P.Name = 'Day' THEN 2 END
  FROM WareBillSortTypesXTab LW 
    JOIN BillSortPeriod P (NOLOCK) ON P.BillSortPeriodNumber = LW.FKBillSortPeriod
  WHERE P.Name = 'Day' AND LW.GroupByPeriod IS NULL
  UPDATE LW SET GroupByPeriod = CASE WHEN P.Name = 'Day' THEN 2 END
  FROM WaregroupBillSortTypesXTab LW 
    JOIN BillSortPeriod P (NOLOCK) ON P.BillSortPeriodNumber = LW.FKBillSortPeriod
  WHERE P.Name = 'Day' AND LW.GroupByPeriod IS 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 = 'SendToOTA'
    WHERE SysObjects.Name = 'Reservations'
)
BEGIN
  ALTER TABLE Reservations ADD SendToOTA BIT NOT NULL DEFAULT 0
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 = 'WareCreateTable'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD WareCreateTable BIT NOT NULL DEFAULT 0
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 = 'SortNumberOnline'
    WHERE SysObjects.Name = 'Ware'
)
BEGIN
  ALTER TABLE Ware ADD SortNumberOnline INT NOT NULL DEFAULT 0
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 = 'StatsNotifSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
BEGIN
  ALTER TABLE CustomerOTA ADD StatsNotifSend 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 = 'IsHallnumberPlenum'
    WHERE SysObjects.Name = 'ArrangementLines'
)
BEGIN
  ALTER TABLE ArrangementLines ADD IsHallnumberPlenum BIT NULL
END
GO
