IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CustomerLoyaltyItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[CustomerLoyaltyItem]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerLoyaltyType] [int] NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](1000) NULL,
    CONSTRAINT [PK_CustomerLoyaltyItem] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CustomerLoyalty]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE CustomerLoyalty
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CustomerLoyalty]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[CustomerLoyalty]
  (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FK_CustomerNumber] [int] NOT NULL,
    [FK_CustomerLoyaltyItemLevel] [int] NULL,
    [FK_CustomerLoyaltyItemProgram] [int] NULL,
    [EnrolmentDate] [datetime] NULL,
    [ExpireDate] [datetime] NULL,
    [MembershipCardNumber] [varchar](100) NULL,
    [PrimaryLoyaltyIndicator] [bit] NULL,
    CONSTRAINT [PK_CustomerLoyalty] PRIMARY KEY CLUSTERED 
    (
      [Id] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[CustomerLoyalty]  WITH CHECK ADD  CONSTRAINT [FK_CustomerLoyalty_Customer] FOREIGN KEY([FK_CustomerNumber])
  REFERENCES [dbo].[Customer] ([CustomerNumber])

  ALTER TABLE [dbo].[CustomerLoyalty] CHECK CONSTRAINT [FK_CustomerLoyalty_Customer]

  ALTER TABLE [dbo].[CustomerLoyalty]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemLevel] FOREIGN KEY([FK_CustomerLoyaltyItemLevel])
  REFERENCES [dbo].[CustomerLoyaltyItem] ([Id])

  ALTER TABLE [dbo].[CustomerLoyalty] NOCHECK CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemLevel]

  ALTER TABLE [dbo].[CustomerLoyalty]  WITH NoCHECK ADD  CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemProgram] FOREIGN KEY([FK_CustomerLoyaltyItemProgram])
  REFERENCES [dbo].[CustomerLoyaltyItem] ([Id])

  ALTER TABLE [dbo].[CustomerLoyalty] NOCHECK CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemProgram]
END
ELSE
BEGIN
  ALTER TABLE CustomerLoyalty ALTER COLUMN FK_CustomerLoyaltyItemLevel INT NULL
  ALTER TABLE CustomerLoyalty ALTER COLUMN FK_CustomerLoyaltyItemProgram INT NULL

  IF EXISTS
  (
    SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = 'FK_CustomerLoyalty_CustomerLoyaltyItemLevel'
  )
  BEGIN
    ALTER TABLE CustomerLoyalty DROP CONSTRAINT FK_CustomerLoyalty_CustomerLoyaltyItemLevel
  END

  ALTER TABLE [dbo].[CustomerLoyalty]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemLevel] FOREIGN KEY([FK_CustomerLoyaltyItemLevel]) REFERENCES [dbo].[CustomerLoyaltyItem] ([Id])
  ALTER TABLE [dbo].[CustomerLoyalty] NOCHECK CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemLevel]

  IF EXISTS
  (
    SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = 'FK_CustomerLoyalty_CustomerLoyaltyItemProgram'
  )
  BEGIN
    ALTER TABLE CustomerLoyalty DROP CONSTRAINT FK_CustomerLoyalty_CustomerLoyaltyItemProgram
  END

  ALTER TABLE [dbo].[CustomerLoyalty]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemProgram] FOREIGN KEY([FK_CustomerLoyaltyItemProgram]) REFERENCES [dbo].[CustomerLoyaltyItem] ([Id])
  ALTER TABLE [dbo].[CustomerLoyalty] NOCHECK CONSTRAINT [FK_CustomerLoyalty_CustomerLoyaltyItemProgram]
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 = 'HotelStayInfoNotifSend'
    WHERE SysObjects.Name = 'CustomerOTA'
)
  ALTER TABLE CustomerOTA ADD HotelStayInfoNotifSend BIT NULL
GO