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

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 @count int, @themeName nvarchar(255)

DECLARE @HubIds TABLE (
	/* this process depends on the IDENTITY value here starting on 1 and incrementing by 1 */
	IndexId int IDENTITY(1, 1) NOT NULL,
	HubId int NOT NULL
)

set @themeName = N''hawaii''

INSERT INTO @HubIds (HubId)
	SELECT T.SectionID as HubId
	FROM [cs_ThemeConfigurationData] T
	INNER JOIN cs_Sections S ON S.SectionID = T.SectionID AND S.ApplicationType = 11
	WHERE T.ThemeName = @themeName AND T.SectionID > -1

SET @count = @@ROWCOUNT

IF @count <> 0
begin
	DECLARE @widgetValue nvarchar(2000), @updatedWidgetValue nvarchar(2100), @valuesLength int, @widgetsValueLength int
	SET @widgetValue = N''<contentFragment type="CommunityServer.Wikis.Controls.PageFilesContentFragment, CommunityServer.Wikis" id="c383f66a-9c76-4b03-b940-c4717e608afb" configuration="fragmentTitle=Attachments&amp;listCssClass=CommonSidebarList" /><contentFragment type="CommunityServer.Wikis.Controls.PageDetailsContentFragment, CommunityServer.Wikis" id="b5920911-a4e3-4c3b-bd1d-fbabbb9a6278" configuration="fragmentTitle=Page+Details" />''
	SELECT @widgetsValueLength = (datalength(@widgetValue) / 2) + 2
	
	DECLARE @startIndex int, @lengthIndex int, @startValue int, @valuesOffset int, @valuesDeleteLength int, @oldLengthValue int
	DECLARE @lengthValue int, @sectionId int, @lengthDeleteLength int, @lengthValueVarchar nvarchar(256)
	DECLARE @valuesPtr varbinary(16), @namesPtr varbinary(16)
	
	DECLARE @otherStartIndexOld int, @otherStartIndex int, @otherStartLength int
	DECLARE @otherStartValue int, @otherStartDeleteLength int, @otherNewStartValue int
	DECLARE @otherNewStartValueVarchar nvarchar(200)
	
	while @count > 0
	begin
		SELECT @sectionId = HubId FROM @HubIds WHERE IndexId = @count
		SELECT @startIndex = CHARINDEX(N''DefaultGroupWidgets'', PropertyNames, 0) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		
		SELECT @startIndex = @startIndex + (datalength(N''DefaultGroupWidgets:S:'')/2)
		SELECT @lengthIndex = CHARINDEX(N'':'', PropertyNames, @startIndex) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		SELECT @startValue = CONVERT(int, SUBSTRING(PropertyNames, @startIndex, @lengthIndex - @startIndex)) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		
		SELECT @startIndex = CHARINDEX(N'':'', PropertyNames, @startIndex) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		SET @startIndex = @startIndex + 1
		
		SELECT @lengthIndex = CHARINDEX(N'':'', PropertyNames, @startIndex) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		SELECT @lengthValue = CONVERT(int, SUBSTRING(PropertyNames, @startIndex, @lengthIndex - @startIndex)) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		
		SELECT @valuesPtr = TEXTPTR([PropertyValues]), @namesPtr = TEXTPTR([PropertyNames]) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
		SET @lengthValueVarchar = CONVERT(nvarchar, @lengthValue)
		SELECT @lengthDeleteLength = datalength(@lengthValueVarchar) / 2
		SET @oldLengthValue = @lengthValue
		SET @lengthValue = @lengthValue + @widgetsValueLength - 2
		SET @lengthValueVarchar = CONVERT(nvarchar, @lengthValue)
		SET @startIndex = @startIndex - 1
		UPDATETEXT [cs_ThemeConfigurationData].[PropertyNames] @namesPtr @startIndex @lengthDeleteLength @lengthValueVarchar
		
		SELECT @valuesOffset = (@startValue + @oldLengthValue) - (datalength(N''</contentFragments>'')/2)
		SELECT @valuesDeleteLength = datalength(N''</contentFragments>'')/2
		SET @updatedWidgetValue = @widgetValue + N''</contentFragments>''
		
		UPDATETEXT [cs_ThemeConfigurationData].[PropertyValues] @valuesPtr @valuesOffset @valuesDeleteLength @updatedWidgetValue
		
		SET @otherStartIndex = @startIndex
		
		while 1 = 1
		begin
			SET @otherStartIndexOld = @otherStartIndex
			SELECT @otherStartIndex = CHARINDEX(N'':S:'', PropertyNames, @startIndex) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
			
			if @otherStartIndex IS NULL OR (@otherStartIndex <= @otherStartIndexOld + 3)
			    break;
			
			SET @otherStartIndex = @otherStartIndex + 3
			
			SELECT @otherStartLength = CHARINDEX(N'':'', PropertyNames, @otherStartIndex) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
			SELECT @otherStartValue = CONVERT(int, SUBSTRING(PropertyNames, @otherStartIndex, @otherStartLength - @otherStartIndex)) FROM [cs_ThemeConfigurationData] WHERE SectionID = @sectionId AND ThemeName = @themeName
			
			SELECT @otherStartDeleteLength = datalength(CONVERT(nvarchar, @otherStartValue)) / 2
			SELECT @otherNewStartValue = @otherStartValue + @widgetsValueLength - 2
			
			SET @otherNewStartValueVarchar = CONVERT(nvarchar, @otherNewStartValue)
			
			SET @otherStartIndex = @otherStartIndex - 1
			UPDATETEXT [cs_ThemeConfigurationData].[PropertyNames] @namesPtr @otherStartIndex @otherStartDeleteLength @otherNewStartValueVarchar
		end
		
		SET @count = @count - 1
	end
end'

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