IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.RateLevelInvRule') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[RateLevelInvRule]
  (
    [Id] [int] NOT NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [Infinity] [bit] NULL,
    [RuleType] [int] NOT NULL,
    [NumberOfDays] [int] NOT NULL,
    [NumberOfRooms] [int] NOT NULL,
    [Active] [bit] NOT NULL,
    [Availability] [int] NULL,
    [ProductPriceBasePrice] [money] NULL,
    [DayColor] [int] NULL,
    CONSTRAINT [PK_RateLevelInvRules] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]
END

IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.RateLevelInvRuleRoomTypeXtab') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[RateLevelInvRuleRoomTypeXtab]
  (
    [FK_RateLevelInvRuleId] [int] NOT NULL,
    [FK_RoomTypeNumber] [int] NOT NULL,
    CONSTRAINT [PK_RateLevelInvRuleRoomTypeXtab] PRIMARY KEY CLUSTERED 
    (
      [FK_RateLevelInvRuleId] ASC,
      [FK_RoomTypeNumber] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[RateLevelInvRuleRoomTypeXtab]  WITH CHECK ADD  CONSTRAINT [FK_RateLevelInvRuleRoomTypeXtab_RateLevelInvRules] FOREIGN KEY([FK_RateLevelInvRuleId])
  REFERENCES [dbo].[RateLevelInvRule] ([Id])

  ALTER TABLE [dbo].[RateLevelInvRuleRoomTypeXtab] CHECK CONSTRAINT [FK_RateLevelInvRuleRoomTypeXtab_RateLevelInvRules]

  ALTER TABLE [dbo].[RateLevelInvRuleRoomTypeXtab]  WITH CHECK ADD  CONSTRAINT [FK_RateLevelInvRuleRoomTypeXtab_RoomTypes] FOREIGN KEY([FK_RoomTypeNumber])
  REFERENCES [dbo].[RoomTypes] ([RoomTypeNumber])

  ALTER TABLE [dbo].[RateLevelInvRuleRoomTypeXtab] CHECK CONSTRAINT [FK_RateLevelInvRuleRoomTypeXtab_RoomTypes]
END

IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.RateLevelInvRuleRateLevelChannelXtab') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[RateLevelInvRuleRateLevelChannelXtab]
  (
    [FK_RateLevelInvRuleId] [int] NOT NULL,
    [FK_RateLevelChannelId] [int] NOT NULL,
    CONSTRAINT [PK_RateLevelInvRuleRateLevelChannelXtab] PRIMARY KEY CLUSTERED 
    (
      [FK_RateLevelInvRuleId] ASC,
      [FK_RateLevelChannelId] ASC
    )
  ) ON [PRIMARY]

  IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.RateLevelChannel') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
    ALTER TABLE [dbo].[RateLevelInvRuleRateLevelChannelXtab]  WITH CHECK ADD  CONSTRAINT [FK_RateLevelInvRuleRateLevelChannelXtab_RateLevelChannel] FOREIGN KEY([FK_RateLevelChannelId])
    REFERENCES [dbo].[RateLevelChannel] ([RateLevelChannelId])

    ALTER TABLE [dbo].[RateLevelInvRuleRateLevelChannelXtab] CHECK CONSTRAINT [FK_RateLevelInvRuleRateLevelChannelXtab_RateLevelChannel]
  END

  ALTER TABLE [dbo].[RateLevelInvRuleRateLevelChannelXtab]  WITH CHECK ADD  CONSTRAINT [FK_RateLevelInvRuleRateLevelChannelXtab_RateLevelInvRule] FOREIGN KEY([FK_RateLevelInvRuleId])
  REFERENCES [dbo].[RateLevelInvRule] ([Id])

  ALTER TABLE [dbo].[RateLevelInvRuleRateLevelChannelXtab] CHECK CONSTRAINT [FK_RateLevelInvRuleRateLevelChannelXtab_RateLevelInvRule]
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 = 'Availability'
    WHERE SysObjects.Name = 'RateLevelInvRule'
)
  ALTER TABLE RateLevelInvRule ADD Availability 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 = 'DayColor'
    WHERE SysObjects.Name = 'RateLevelInvRule'
)
  ALTER TABLE RateLevelInvRule ADD DayColor 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 = 'ProductPriceBasePrice'
    WHERE SysObjects.Name = 'RateLevelInvRule'
)
  ALTER TABLE RateLevelInvRule ADD ProductPriceBasePrice money null
GO
