IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostMain]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostMain]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DateStamp] [datetime] NOT NULL,
    [RootType] [smallint] NOT NULL,
    [EchoToken] [varchar](255) NOT NULL,
    [HotelCode] [varchar](100) NOT NULL,
    [DateStampResend] [datetime] NULL,
    CONSTRAINT [PK_OTAPostMain] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostDelta]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostDelta]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_OTAPostMainId] [int] NOT NULL,
    [FromDate] [datetime] NOT NULL,
    [ToDate] [datetime] NOT NULL,
    [ProductNumber] [int] NULL,
    [ProductNumberCSV] [varchar](1000) NULL,
    [OnlyTopLevel] [bit] NULL,
    [OldNonExclusiveStatus] [int] NULL,
    [RatePlanId] [int] NULL,
    [RatePlanName] [varchar](100) NULL,
    [InvPerRoomType] [bit] NULL,
    CONSTRAINT [PK_OTAPostDelta] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostDelta]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostDelta_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostDelta] CHECK CONSTRAINT [FK_OTAPostDelta_OTAPostMain]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelAvailNotif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelAvailNotif]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [SpecificRateLevelId] [int] NULL,
    [SyncRequestId] [int] NULL,
    [Specified] [bit] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostHotelAvailNotif] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelAvailNotif]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelAvailNotif_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelAvailNotif] CHECK CONSTRAINT [FK_OTAPostHotelAvailNotif_OTAPostMain]
END

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Group'
    WHERE SysObjects.Name = 'OTAPostHotelBookingRuleNotif'
)
  EXEC sp_rename 'OTAPostHotelBookingRuleNotif.Group', 'IsGroup', 'COLUMN';

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelBookingRuleNotif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelBookingRuleNotif]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [IsGroup] [bit] NULL,
    [SyncRequestId] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostHotelBookingRuleNotif] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelBookingRuleNotif]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelBookingRuleNotif_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelBookingRuleNotif] CHECK CONSTRAINT [FK_OTAPostHotelBookingRuleNotif_OTAPostMain]
END

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Group'
    WHERE SysObjects.Name = 'OTAPostHotelInvAdjust'
)
  EXEC sp_rename 'OTAPostHotelInvAdjust.Group', 'IsGroup', 'COLUMN';

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelInvAdjust]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelInvAdjust]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [IsGroup] [bit] NULL,
    [AdjustReason] [varchar](100) NULL,
    [PromotionCode] [varchar](100) NULL,
    [OneRecordPerDay] [bit] NULL,
    [GroupAction] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostHotelInvAdjust] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelInvAdjust]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelInvAdjust_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelInvAdjust] CHECK CONSTRAINT [FK_OTAPostHotelInvAdjust_OTAPostMain]
END

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Group'
    WHERE SysObjects.Name = 'OTAPostHotelInvCountNotif'
)
  EXEC sp_rename 'OTAPostHotelInvCountNotif.Group', 'IsGroup', 'COLUMN';

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelInvCountNotif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelInvCountNotif]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [SpecificRateLevelId] [int] NULL,
    [SyncRequestId] [int] NULL,
    [Specified] [bit] NULL,
    [IsGroup] [bit] NULL,
    [GroupAction] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostInvCountNotif] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelInvCountNotif]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelInvCountNotif_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelInvCountNotif] CHECK CONSTRAINT [FK_OTAPostHotelInvCountNotif_OTAPostMain]
END

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Group'
    WHERE SysObjects.Name = 'OTAPostHotelRateAmountNotif'
)
  EXEC sp_rename 'OTAPostHotelRateAmountNotif.Group', 'IsGroup', 'COLUMN';

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelRateAmountNotif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelRateAmountNotif]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [IsGroup] [bit] NULL,
    [SyncRequestId] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostHotelRateAmountNotif] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelRateAmountNotif]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelRateAmountNotif_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelRateAmountNotif] CHECK CONSTRAINT [FK_OTAPostHotelRateAmountNotif_OTAPostMain]
END

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Group'
    WHERE SysObjects.Name = 'OTAPostHotelRatePlanNotif'
)
  EXEC sp_rename 'OTAPostHotelRatePlanNotif.Group', 'IsGroup', 'COLUMN';

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelRatePlanNotif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelRatePlanNotif]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [IsGroup] [bit] NULL,
    [SyncRequestId] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostHotelRatePlanNotif] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelRatePlanNotif]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelRatePlanNotif_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelRatePlanNotif] CHECK CONSTRAINT [FK_OTAPostHotelRatePlanNotif_OTAPostMain]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostHotelResNotif]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostHotelResNotif]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [ReferenceNumber] [int] NULL,
    [GuestNumber] [int] NULL,
    [Action] [int] NULL,
    [CancelDateTime] [datetime] NULL,
    [FromDate] [datetime] NULL,
    [CancellationNumber] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostHotelResNotif] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostHotelResNotif]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostHotelResNotif_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostHotelResNotif] CHECK CONSTRAINT [FK_OTAPostHotelResNotif_OTAPostMain]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostProfile]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostProfile]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [CustomerNumber] [int] NULL,
    [OldMailAddress] [varchar](260) NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostProfile] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostProfile]  WITH NOCHECK ADD  CONSTRAINT [FK_OTAPostProfile_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostProfile] CHECK CONSTRAINT [FK_OTAPostProfile_OTAPostMain]
END

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    WHERE SysObjects.Name = 'OTAPostHotelBookingRuleNotif'
)
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'Batch'
      WHERE SysObjects.Name = 'OTAPostHotelBookingRuleNotif'
  )
    ALTER TABLE OTAPostHotelBookingRuleNotif ADD Batch BIT 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 = 'OTAPostMain'
)
BEGIN
  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'DateStamp'
      WHERE SysObjects.Name = 'OTAPostMain'
  )
    ALTER TABLE OTAPostMain ADD DateStamp DATETIME NOT NULL 

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

  IF NOT EXISTS
  (
    SELECT SysColumns.Name 
    FROM SysObjects WITH (NOLOCK) 
      INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
      AND SysColumns.Name = 'DateStampResend'
      WHERE SysObjects.Name = 'OTAPostMain'
  )
    ALTER TABLE OTAPostMain ADD DateStampResend DATETIME 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 = 'OTAPostDelta'
)
BEGIN
  IF (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OTAPostDelta' AND COLUMN_NAME = 'FromDate')  = 'DATE'
  BEGIN
    ALTER TABLE OTAPostDelta ALTER COLUMN FromDate DATETIME NULL 
    ALTER TABLE OTAPostDelta ALTER COLUMN ToDate DATETIME NULL 
  END
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostLoyaltyCertificateRedemption]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostLoyaltyCertificateRedemption]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [GetBalance] [bit] NOT NULL,
    [BonusPointAmount] [int] NULL,
    [Amount] [money] NULL,
    [Email] [varchar](300) NULL,
    [CardNumber] [varchar](100) NULL,
    CONSTRAINT [PK_OTAPostLoyaltyCertificateRedemption] PRIMARY KEY CLUSTERED 
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostLoyaltyCertificateRedemption]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostLoyaltyCertificateRedemption_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostLoyaltyCertificateRedemption] CHECK CONSTRAINT [FK_OTAPostLoyaltyCertificateRedemption_OTAPostMain]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAPostPurchaseItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAPostPurchaseItem]
  (
    [FK_OTAPostMainId] [int] NOT NULL,
    [FolioType] [smallint] NULL,
    [FolioNumber] [int] NULL,
    [PkInvoice] [int] NULL,
    [AddressNumber] [int] NULL,
    [Batch] [bit] NULL,
    CONSTRAINT [PK_OTAPostPurchaseItem] PRIMARY KEY CLUSTERED
    (
      [FK_OTAPostMainId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAPostPurchaseItem]  WITH CHECK ADD  CONSTRAINT [FK_OTAPostPurchaseItem_OTAPostMain] FOREIGN KEY([FK_OTAPostMainId])
  REFERENCES [dbo].[OTAPostMain] ([Id])

  ALTER TABLE [dbo].[OTAPostPurchaseItem] CHECK CONSTRAINT [FK_OTAPostPurchaseItem_OTAPostMain]
END