SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

Set @Major = 3;
Set @Minor = 0;
Set @Patch = 8;

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 ##

EXECUTE sp_executesql
	N'INSERT INTO [dbo].[cs_SectionSubscriptions]
		([SectionID],[Email],[SubscriptionType],[UserID],[SettingsID],[DateSubscribed],[SubscriptionFrequency],[IsConfirmed])
	SELECT TS.[SectionID], U.[Email], MIN(TS.[SubscriptionType]) as [SubscriptionType], U.[UserID], TS.[SettingsID],
			MAX(TS.[DateCreated]) as [DateSubscribed], 1 as [SubscriptionFrequency], 1 as [IsConfirmed]
	FROM	[dbo].[cs_TrackedSections] TS
			INNER JOIN [dbo].[cs_Users] U ON TS.[UserID] = U.[UserID]
			INNER JOIN [dbo].[cs_Sections] Sec ON Sec.[SectionID] = TS.[SectionID]
			INNER JOIN [dbo].[cs_ApplicationType] A ON A.[ApplicationType] = Sec.[ApplicationType]
	WHERE TS.[SubscriptionType] = 3 AND A.[ApplicationName] = ''Weblog''
	GROUP BY U.[UserID], TS.[SectionID], U.[Email], TS.[SettingsID]
	Having count(*) = 1'

--## 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 

