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

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

DECLARE @ApplicationType INT
SELECT @ApplicationType = ApplicationType FROM cs_ApplicationType WHERE ApplicationName = 'Forum'

DECLARE @Parent CURSOR
SET @Parent = CURSOR LOCAL FAST_FORWARD FOR 
	SELECT DISTINCT ParentID FROM cs_Sections WHERE ApplicationType = @ApplicationType and ParentID > 0

OPEN @Parent
DECLARE @ParentID INT

FETCH NEXT FROM @Parent INTO @ParentID
WHILE (@@FETCH_STATUS = 0)
	BEGIN
		DECLARE @SortOrder INT
		SET @SortOrder = 0

		DECLARE @Section CURSOR
		SET @Section = CURSOR LOCAL FAST_FORWARD FOR
			SELECT SectionID FROM cs_Sections WHERE ParentID = @ParentID Order By [Name]

		Open @Section
		DECLARE @SectionID INT
		FETCH NEXT FROM @Section INTO @SectionID

		WHILE (@@FETCH_STATUS = 0)
			BEGIN
				UPDATE cs_Sections 
				SET SortOrder = @SortOrder
				WHERE SectionID = @SectionID

				SET @SortOrder = @SortOrder + 1
				FETCH NEXT FROM @Section INTO @SectionID
			END
		CLOSE @Section
		DEALLOCATE @Section
	
		FETCH NEXT FROM @Parent INTO @ParentID
	END

CLOSE @Parent
DEALLOCATE @Parent

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

