SET NOCOUNT ON

-- Create xtab table
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[WareLinkGroupRecurringPatternXTab]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
  CREATE TABLE [dbo].[WareLinkGroupRecurringPatternXTab]
  (
    [FKWareLinkGroupId] [int] NOT NULL,
    [FKRecurringPatternId] [int] NOT NULL,
    CONSTRAINT [PK_WareLinkGroupRecurringPatternXTab] PRIMARY KEY CLUSTERED 
    (
      [FKWareLinkGroupId] ASC,
      [FKRecurringPatternId] ASC
    )
  ) ON [PRIMARY]

  ALTER TABLE [dbo].[WareLinkGroupRecurringPatternXTab]  WITH CHECK ADD  CONSTRAINT [FK_WareLinkGroupRecurringPatternXTab_RecurringPattern] FOREIGN KEY([FKRecurringPatternId])
  REFERENCES [dbo].[RecurringPattern] ([Id])

  ALTER TABLE [dbo].[WareLinkGroupRecurringPatternXTab] CHECK CONSTRAINT [FK_WareLinkGroupRecurringPatternXTab_RecurringPattern]

  ALTER TABLE [dbo].[WareLinkGroupRecurringPatternXTab]  WITH CHECK ADD  CONSTRAINT [FK_WareLinkGroupRecurringPatternXTab_WareLinkGroup] FOREIGN KEY([FKWareLinkGroupId])
  REFERENCES [dbo].[WareLinkGroup] ([Id])

  ALTER TABLE [dbo].[WareLinkGroupRecurringPatternXTab] CHECK CONSTRAINT [FK_WareLinkGroupRecurringPatternXTab_WareLinkGroup]
END

-- move existing data
IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
  INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
  AND SysColumns.Name = 'FK_RecurringPatternId'
  WHERE SysObjects.Name = 'WareLinkGroup'
)
BEGIN
  DECLARE @SQL VARCHAR(4000)

  SET @SQL = 'INSERT INTO WareLinkGroupRecurringPatternXTab (FKWareLinkGroupId, FKRecurringPatternId) ' + 
    ' SELECT Id, FK_RecurringPatternId ' +
    ' FROM WareLinkGroup WITH (NOLOCK) ' +
    ' WHERE NOT FK_RecurringPatternId IS NULL'
  EXEC (@SQL)

  SET @SQL = 'UPDATE WareLinkGroup SET FK_RecurringPatternId = NULL'
  EXEC (@SQL)
END
GO

IF (OBJECT_ID('FK_WareLinkGroup1_WareLinkGroup1', 'F') IS NOT NULL)
  ALTER TABLE WareLinkGroup DROP CONSTRAINT FK_WareLinkGroup1_WareLinkGroup1
GO

IF EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
  INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
  AND SysColumns.Name = 'FK_RecurringPatternId'
  WHERE SysObjects.Name = 'WareLinkGroup'
)
  ALTER TABLE WareLinkGroup DROP COLUMN FK_RecurringPatternId
GO