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 = 1;
Set @Patch = 5;

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

if not exists (select * from dbo.syscolumns where id = object_id(N'[dbo].[cs_Sections]') and Name = 'IsCommunityAggregated')
begin

exec sp_executesql N'alter table [dbo].[cs_Sections] add IsCommunityAggregated BIT NOT NULL DEFAULT -1'

exec sp_executesql N'DECLARE @PropertyString		NVARCHAR(50)
DECLARE @PropertyDelimiter	NVARCHAR(50)
DECLARE @ValueDelimiter		NVARCHAR(50)

SET @PropertyString = ''%IsCommunityAggregated:S:%''
SET @PropertyDelimiter = ''%:%''
SET @ValueDelimiter = ''%;%''

UPDATE cs_Sections set IsCommunityAggregated = CA.IsCommunityAggregatedProperty FROM cs_Sections S join (
SELECT
SectionID, 0 as IsCommunityAggregatedProperty
from cs_Sections WHERE PATINDEX(@PropertyString, PropertyNames) > 0 AND
LOWER(SUBSTRING(PropertyValues,
CONVERT(int, SUBSTRING(SUBSTRING(PropertyNames, PATINDEX(@PropertyString, PropertyNames) + LEN(@PropertyString)-2, 100),0,PATINDEX(@PropertyDelimiter, SUBSTRING(PropertyNames, PATINDEX(@PropertyString, PropertyNames) + LEN(@PropertyString)-2, 100))))+1,
CONVERT(int, SUBSTRING(SUBSTRING(SUBSTRING(PropertyNames, PATINDEX(@PropertyString, PropertyNames) + LEN(@PropertyString)-2, 100),PATINDEX(@PropertyDelimiter, SUBSTRING(PropertyNames, PATINDEX(@PropertyString, PropertyNames) + LEN(@PropertyString)-2, 100))+1,100),0, PATINDEX(@PropertyDelimiter, SUBSTRING(SUBSTRING(PropertyNames, PATINDEX(@PropertyString, PropertyNames) + LEN(@PropertyString)-2, 100),PATINDEX(@PropertyDelimiter, SUBSTRING(PropertyNames, PATINDEX(@PropertyString, PropertyNames) + LEN(@PropertyString)-2, 100))+1,100)))-1)
+1
)) = ''false'' 
) CA on S.SectionID = CA.SectionID
'
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 

