IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAExternalMeta]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAExternalMeta]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [HotelCode] [varchar](100) NOT NULL,
    [ExternalType] [int] NOT NULL,
    [ExternalName] [varchar](200) NOT NULL,
    [ExternalId] [varchar](200) NOT NULL,
    [ExternalId2] [varchar](200) NOT NULL,
    CONSTRAINT [PK_OTAExternalMeta] PRIMARY KEY CLUSTERED 
    (
      [Id] 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 = 'ExternalId2'
    WHERE SysObjects.Name = 'OTAExternalMeta'
)
BEGIN
  ALTER TABLE OTAExternalMeta ADD ExternalId2 VARCHAR(200) NULL 
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAExternalMetaRatePlan]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAExternalMetaRatePlan]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_OTAExternalMetaId] [int] NOT NULL,
    [CurrencyCode] [varchar](3) NULL,
    [MinPax] [int] NULL,
    [MaxPax] [int] NULL,
    [RoomTypeCode] [varchar](100) NULL,
    [RoomTypeId] [varchar](100) NULL,
    [Configuration] [varchar](100) NULL,
    CONSTRAINT [PK_OTAExternalMetaRatePlan] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAExternalMetaRatePlan]  WITH CHECK ADD  CONSTRAINT [FK_OTAExternalMetaRatePlan_OTAExternalMeta] FOREIGN KEY([FK_OTAExternalMetaId])
  REFERENCES [dbo].[OTAExternalMeta] ([Id])

  ALTER TABLE [dbo].[OTAExternalMetaRatePlan] CHECK CONSTRAINT [FK_OTAExternalMetaRatePlan_OTAExternalMeta]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[OTAExternalMetaRoomHall]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[OTAExternalMetaRoomHall]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_OTAExternalMetaId] [int] NOT NULL,
    [IsRoom] [bit] NULL,
    [MaxPax] [int] NULL,
    [MaxPaxAdult] [int] NULL,
    [MaxPaxChildren] [int] NULL,
    [InvBlockCode] [varchar](200) NULL,
    CONSTRAINT [PK_OTAExternalMetaRoomHall] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[OTAExternalMetaRoomHall]  WITH CHECK ADD  CONSTRAINT [FK_OTAExternalMetaRoomHall_OTAExternalMeta] FOREIGN KEY([FK_OTAExternalMetaId])
  REFERENCES [dbo].[OTAExternalMeta] ([Id])

  ALTER TABLE [dbo].[OTAExternalMetaRoomHall] CHECK CONSTRAINT [FK_OTAExternalMetaRoomHall_OTAExternalMeta]
END