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 = 'DaysForward'
    WHERE SysObjects.Name = 'ReportAutomation'
)
BEGIN
  ALTER TABLE ReportAutomation ADD DaysForward INT NULL 
END
GO

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 = '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

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 = 'DaysBackward2'
    WHERE SysObjects.Name = 'ReportAutomation'
)
BEGIN
  ALTER TABLE ReportAutomation ADD DaysBackward2 INT NULL 
END
GO

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 = '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 *
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
    AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
    AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
    AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
  WHERE   C.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND K.COLUMN_NAME = 'FK_WareNumber'
  AND K.TABLE_NAME = 'MISHistory'
)
BEGIN
  DECLARE @DefaultConstraint SYSNAME
  DECLARE @DropStatement NVARCHAR(500)

  SELECT @DefaultConstraint = C.CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME
      AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
      AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
      AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
    WHERE   C.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND K.TABLE_NAME = 'MISHistory'
    
  SET @DropStatement = 'ALTER TABLE MISHistory DROP CONSTRAINT ' + @DefaultConstraint
  EXEC (@DropStatement)

  ALTER TABLE MISHistory ADD CONSTRAINT [PK_MISHistory] PRIMARY KEY (
    [SystemDate] ASC,
    [DateStamp] ASC,
    [FK_HotelCustomerNumber] ASC,
    [FK_DepartmentNumber] ASC,
    [FK_AccountNumber] ASC,
    [FK_SegmentNumber] ASC,
    [FK_PointOfSaleNumber] ASC,
    [FK_PurposeCodeNumber] ASC,
    [FK_GuestTypeNumber] ASC,
    [FK_ProdutNumber] ASC,
    [Room_HallType] ASC,
    [RoomTypeNumber] ASC,
    [ValueType] ASC,
    [FK_WareNumber] ASC,
    [FK_StaffId] ASC,
    [FK_ReferenceNumber] 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 = '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