SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[FacilityGroup]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[FacilityGroup]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [SortNumber] [int] NOT NULL,
    [GroupType] [int] NOT NULL
    CONSTRAINT [PK_FacilityGroup] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[FacilityItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[FacilityItem]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [SortNumber] [int] NOT NULL,
    [FK_FacilityGroupId] [int] NOT NULL,
    [DataType] [smallint] NOT NULL,
    CONSTRAINT [PK_FacilityItem] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[FacilityItem]  WITH CHECK ADD  CONSTRAINT [FK_FacilityItem_FacilityGroup] FOREIGN KEY([FK_FacilityGroupId])
  REFERENCES [dbo].[FacilityGroup] ([Id])

  ALTER TABLE [dbo].[FacilityItem] CHECK CONSTRAINT [FK_FacilityItem_FacilityGroup]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].RoomsFacilityItemXTab') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[RoomsFacilityItemXTab]
  (
    [FK_RoomNumber] [int] NOT NULL,
    [FK_FacilityItemId] [int] NOT NULL,
    [Value] [varchar](50) NOT NULL,
    CONSTRAINT [PK_RoomsFacilityItemXTab] PRIMARY KEY CLUSTERED 
    (
      [FK_RoomNumber] ASC,
      [FK_FacilityItemId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[RoomsFacilityItemXTab]  WITH CHECK ADD  CONSTRAINT [FK_RoomsFacilityItemXTab_FacilityItem] FOREIGN KEY([FK_FacilityItemId])
  REFERENCES [dbo].[FacilityItem] ([Id])

  ALTER TABLE [dbo].[RoomsFacilityItemXTab] CHECK CONSTRAINT [FK_RoomsFacilityItemXTab_FacilityItem]

  ALTER TABLE [dbo].[RoomsFacilityItemXTab]  WITH CHECK ADD  CONSTRAINT [FK_RoomsFacilityItemXTab_Rooms] FOREIGN KEY([FK_RoomNumber])
  REFERENCES [dbo].[Rooms] ([RoomNumber])

  ALTER TABLE [dbo].[RoomsFacilityItemXTab] CHECK CONSTRAINT [FK_RoomsFacilityItemXTab_Rooms]
END

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Address]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[Address]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StreetName] [varchar](100) NOT NULL,
    [StreetNumber] [varchar](10) NOT NULL,
    [Floor] [varchar](10) NULL,
    [Address2] [varchar](100) NULL,
    [Address3] [varchar](100) NULL,
    [ZipCode] [varchar](10) NULL,
    [City] [varchar](50) NULL,
    [Region] [varchar](50) NULL,
    [FK_CountryNumber] [int] NOT NULL,
    [WGSLongitude] [varchar](25) NULL,
    [WGSLatitude] [varchar](25) NULL,
    CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_Countries] FOREIGN KEY([FK_CountryNumber])
  REFERENCES [dbo].[Countries] ([CountryNumber])

  ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Countries]
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'MinPax'
    WHERE SysObjects.Name = 'RoomTypes'
)
  ALTER TABLE RoomTypes ADD MinPax 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 = 'MaxAdult'
    WHERE SysObjects.Name = 'RoomTypes'
)
  ALTER TABLE RoomTypes ADD MaxAdult 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 = 'NumberOfPets'
    WHERE SysObjects.Name = 'Rooms'
)
  ALTER TABLE Rooms ADD NumberOfPets 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 = 'NumberOfBedRooms'
    WHERE SysObjects.Name = 'Rooms'
)
  ALTER TABLE Rooms ADD NumberOfBedRooms 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_AddressId'
    WHERE SysObjects.Name = 'Rooms'
)
BEGIN
  ALTER TABLE Rooms ADD FK_AddressId INT NULL 
  ALTER TABLE [dbo].[Rooms]  WITH CHECK ADD  CONSTRAINT [FK_Rooms_Address] FOREIGN KEY([FK_AddressId])
  REFERENCES [dbo].[Address] ([Id])
END

-- Create missing FacilityGroups
IF NOT EXISTS (SELECT * FROM FacilityGroup WITH (NOLOCK) WHERE GroupType = 1)
  INSERT INTO FacilityGroup (Name, SortNumber, GroupType) VALUES ('SleepingPlaces', 1, 1)
IF NOT EXISTS (SELECT * FROM FacilityGroup WITH (NOLOCK) WHERE GroupType = 2)
  INSERT INTO FacilityGroup (Name, SortNumber, GroupType) VALUES ('KeyDistances', 2, 2)
IF NOT EXISTS (SELECT * FROM FacilityGroup WITH (NOLOCK) WHERE GroupType = 3)
  INSERT INTO FacilityGroup (Name, SortNumber, GroupType) VALUES ('KeyFacilities', 3, 3)

DECLARE @spType INT
DECLARE @kdType INT
DECLARE @kfType INT

-- Get FacilityGroup id's
SELECT @spType = Id FROM FacilityGroup WITH (NOLOCK) WHERE GroupType = 1
SELECT @kdType = Id FROM FacilityGroup WITH (NOLOCK) WHERE GroupType = 2
SELECT @kfType = Id FROM FacilityGroup WITH (NOLOCK) WHERE GroupType = 3

-- Create Missing FacilityItems
-- DataType: 0=fidtBoolean, 1=fidtDecimal, 2=fidtInteger, 3=fidtString, 4=fidtDistance
-- SleepingPlaces
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @spType AND SortNumber = 1)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('SingleBed', 1, @spType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @spType AND SortNumber = 2)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('ThreeQuarterBed', 2, @spType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @spType AND SortNumber = 3)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('DoubleBed', 3, @spType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @spType AND SortNumber = 4)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Cot', 4, @spType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @spType AND SortNumber = 5)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('LoftRoom', 5, @spType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @spType AND SortNumber = 6)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Unspecified', 6, @spType, 2)

-- KeyDistances
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kdType AND SortNumber = 1)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Water', 1, @kdType, 4)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kdType AND SortNumber = 2)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Shopping', 2, @kdType, 4)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kdType AND SortNumber = 3)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('AlpineSki', 3, @kdType, 4)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kdType AND SortNumber = 4)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('CrossCountrySki', 4, @kdType, 4)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kdType AND SortNumber = 5)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('SkiArea', 5, @kdType, 4)

-- KeyFacilities
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 1)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Pool', 1, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 2)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('PoolIndoor', 2, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 3)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('PoolOutdoor', 3, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 4)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Whirlpool', 4, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 5)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Sauna', 5, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 6)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('WashingMachine', 6, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 7)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Dishwasher', 7, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 8)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('TumbleDryer', 8, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 9)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('FireplaceStove', 9, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 10)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('SatelliteCableTV', 10, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 11)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Internet', 11, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 12)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('SuitableForDisabled', 12, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 13)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Solarium', 13, @kfType, 0)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 14)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Bathrooms', 14, @kfType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 15)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('ResidentialArea', 15, @kfType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 16)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('ParcelArea', 16, @kfType, 2)
IF NOT EXISTS (SELECT * FROM FacilityItem WITH (NOLOCK) WHERE FK_FacilityGroupId = @kfType AND SortNumber = 17)
  INSERT INTO FacilityItem (Name, SortNumber, FK_FacilityGroupId, DataType) VALUES ('Nonsmoking', 17, @kfType, 0)