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 = 20;

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'
	DECLARE @SectionIds TABLE (
		SectionId int NOT NULL
	)
	
	INSERT INTO @SectionIds (SectionId)
	SELECT S.SectionID as SectionId
	FROM [cs_Sections] S
	WHERE S.HubSectionID > 0 and S.HubSectionID not in (select sx.SectionID from cs_Sections AS sx)

	DELETE FROM cs_SectionTokens WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_InkData WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	
	DELETE FROM cs_Links WHERE LinkCategoryID in (SELECT L.LinkCategoryID FROM cs_LinkCategories L INNER JOIN @SectionIds S on L.SectionID = S.SectionId)
	DELETE FROM cs_LinkCategories WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_Moderators WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_TrackedSections WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_weblog_Weblogs WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_SectionsRead WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_ForumPingback WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_PostAttachments_TEMP WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	
	DELETE FROM cs_Posts_InCategories WHERE CategoryID in (SELECT L.CategoryID FROM cs_Post_Categories L INNER JOIN @SectionIds S on L.SectionID = S.SectionId)
	DELETE FROM cs_Posts_InCategories WHERE PostID in (SELECT L.PostID FROM cs_Posts L INNER JOIN @SectionIds S on L.SectionID = S.SectionId)
	DELETE FROM cs_Post_Categories_Parents WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_Post_Categories WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_ThreadsRead WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	
	DELETE FROM cs_nntp_Posts WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_nntp_Newsgroups WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_PostsArchive WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_SectionPermissions WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_FavoriteSections WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_SearchBarrel WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_PostEditNotes WHERE PostID in (SELECT L.PostID FROM cs_Posts L INNER JOIN @SectionIds S on L.SectionID = S.SectionId)

	DELETE FROM cs_Posts WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	DELETE FROM cs_ThreadRating WHERE ThreadID in (SELECT L.ThreadID FROM cs_Threads L INNER JOIN @SectionIds S on L.SectionID = S.SectionId)
	DELETE FROM cs_TrackedThreads WHERE ThreadID in (SELECT L.ThreadID FROM cs_Threads L INNER JOIN @SectionIds S on L.SectionID = S.SectionId)
	DELETE FROM cs_Threads WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
	
	DELETE FROM cs_Sections WHERE SectionID in (SELECT sx.SectionId FROM @SectionIds AS sx)
'


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