SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Major int, @Minor int, @Patch int, @Installed DateTime, @Prereqs int

Set @Major = 4;
Set @Minor = 1;
Set @Patch = 9;

Select @Prereqs = isnull(Count(InstallDate),0)  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch<@Patch

Select @Installed = InstallDate  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch=@Patch

If(@Installed is null AND @Prereqs = @Patch)
	BEGIN
--## Schema Patch ##

/* temp identity */
EXECUTE sp_executesql N'
	ALTER TABLE [dbo].[cs_es_Search_RemoveQueue] ADD [TEMP_ID] [int] identity(1, 1) NOT NULL
'

/* remove any duplicates */
EXECUTE sp_executesql N'
	delete from [dbo].[cs_es_Search_RemoveQueue]
	where exists (
		select 1 from [dbo].[cs_es_Search_RemoveQueue] b
		where
			b.[PostID] = [dbo].[cs_es_Search_RemoveQueue].[PostID]
		group by
			b.[PostID]
		having
			[dbo].[cs_es_Search_RemoveQueue].[TEMP_ID] < max( b.[TEMP_ID] )
	)
'

/* remove the temp identity */
EXECUTE sp_executesql N'
	ALTER TABLE [dbo].[cs_es_Search_RemoveQueue] drop column [TEMP_ID]
'

/* add new column with constraint */
EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_es_Search_RemoveQueue]
	ADD [ApplicationType] [varchar] (50) NOT NULL CONSTRAINT [DF_cs_es_Search_RemoveQueue_ApplicationType] DEFAULT('''')'

/* update items with appropriate application type */
EXECUTE sp_executesql N'
	UPDATE [cs_es_Search_RemoveQueue] SET ApplicationType = 
		A.ApplicationName FROM [cs_es_Search_RemoveQueue] SR
		INNER JOIN [cs_Posts] P ON P.PostID = SR.PostID
		INNER JOIN [cs_Sections] S ON P.SectionID = S.SectionID 
		INNER JOIN [cs_ApplicationType] A ON A.ApplicationType = S.ApplicationType'

/* add new constraint */
EXECUTE sp_executesql N'
ALTER TABLE [dbo].[cs_es_Search_RemoveQueue]
	ADD CONSTRAINT [PK_es_Search_RemoveQueue] PRIMARY KEY CLUSTERED 
(
	[PostID] ASC,
	[ApplicationType] ASC
) WITH FILLFACTOR = 90 ON [PRIMARY]'

--## END Schema Patch ##
Insert into cs_SchemaVersion(Major, Minor, Patch, InstallDate) values (@Major, @Minor, @Patch, GetDate())

Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was applied successfully '

	END
ELSE IF(@Installed is not null)
	BEGIN
Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was already applied on ' + Convert(varchar(50), @Installed)  
	END 
ELSE
	BEGIN
Print 'The patch could not be applied because your current schema is missing previous updates (Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ')' 
	END 
