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

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

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

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

SELECT LEN(@PropertyString)-2

CREATE TABLE #SectionOwners 
(
	SectionID int  NOT NULL,
	OwnerName nvarchar(256) collate database_default NOT NULL
)

CREATE TABLE #SectionOwnerProperty 
(
	SectionID int  NOT NULL,
	Owners nvarchar(4000) collate database_default NOT NULL
)

INSERT INTO #SectionOwnerProperty(SectionID, Owners)
SELECT
SectionID,
 
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
) as Owners from cs_Sections WHERE (ApplicationType <> 0) AND (PATINDEX(@PropertyString, PropertyNames) > 0)

WHILE EXISTS (SELECT SectionID FROM #SectionOwnerProperty WHERE PATINDEX(@ValueDelimiter, Owners) > 0)
BEGIN
	INSERT INTO #SectionOwners (SectionID, OwnerName) SELECT SectionID, LOWER(SUBSTRING(Owners,0,PATINDEX(@ValueDelimiter, Owners)))  FROM #SectionOwnerProperty WHERE PATINDEX(@ValueDelimiter, Owners) > 0 
	UPDATE #SectionOwnerProperty SET Owners = SUBSTRING(Owners,PATINDEX(@ValueDelimiter, Owners)+1, DATALENGTH(Owners) - PATINDEX(@ValueDelimiter, Owners))  FROM #SectionOwnerProperty WHERE PATINDEX(@ValueDelimiter, Owners) > 0 
END

INSERT INTO #SectionOwners (SectionID, OwnerName) SELECT SectionID, LOWER(Owners) FROM #SectionOwnerProperty WHERE PATINDEX(@ValueDelimiter, Owners) = 0 

DROP TABLE #SectionOwnerProperty

SELECT * FROM #SectionOwners
INSERT INTO cs_SectionMembers(SectionID, UserID, MembershipType) SELECT DISTINCT SO.SectionID, U.UserID, 1 FROM  #SectionOwners SO JOIN cs_Users U on U.UserName = SO.OwnerName JOIN cs_UserProfile P on U.UserID = P.UserID JOIN cs_Sections S on SO.SectionID = S.SectionID and P.SettingsID = S.SettingsID LEFT OUTER JOIN cs_SectionMembers SM on SM.SectionID = SO.SectionID and SM.UserID = U.UserID and SM.MembershipType = 1 WHERE SM.MembershipType is null 

DROP TABLE #SectionOwners' 

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

