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 = 0;
Set @Patch = 15;

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('
declare
	@NodeOriginalEscaped nvarchar(128), @NodeNew nvarchar(128), @NodeName nvarchar(128)
	, @id int, @ptrSettings binary(16), @insert_offset int, @delete_length int

declare @t table (
	id int identity(1, 1) not null, ptrSettings binary(16) not null
	, NodeStart int not null, NodeEnd int not null, NodeLength int not null
	, insert_offset AS NodeStart + NodeLength - 1
	, delete_length AS NodeEnd - (NodeStart + NodeLength)
)

set nocount on

select
	@NodeOriginalEscaped = N''default''
	, @NodeNew = N''hawaii''
	, @NodeName = N''DefaultTheme''

select
	db_name() DatabaseName, s.SettingsID, s.Settings
	, case when patindex(
		N''%<'' + @NodeName + N''>''
		+ @NodeOriginalEscaped
		+ N''</'' + @NodeName + N''>%''
		, s.Settings )<>0 then ''Updating'' else ''No Update'' end
from
	cs_ApplicationConfigurationSettings s
where
	s.ApplicationType in (1, 11)

insert into @t (ptrSettings, NodeStart, NodeEnd, NodeLength)
select
	TEXTPTR( s.Settings )
	, patindex( N''%<'' + @NodeName + N''>%'', s.Settings )
	, patindex( N''%</'' + @NodeName + N''>%'', s.Settings )
	, 2 + LEN( @NodeName )
from
	cs_ApplicationConfigurationSettings s
where
	s.Settings IS NOT NULL
	and patindex(
		N''%<'' + @NodeName + N''>''
		+ @NodeOriginalEscaped
		+ N''</'' + @NodeName + N''>%''
		, s.Settings ) <> 0
	and s.ApplicationType in (1, 11)

while 1=1
begin
	select @id = (select min(id) from @t)
	if @id is null break

	select @ptrSettings = t.ptrSettings, @insert_offset = t.insert_offset, @delete_length = t.delete_length
	from @t t where id = @id

	UPDATETEXT cs_ApplicationConfigurationSettings.Settings @ptrSettings @insert_offset @delete_length @NodeNew

	delete from @t where id = @id
end
')

exec('
declare
	@NodeOriginalEscaped nvarchar(128), @NodeNew nvarchar(128), @NodeName nvarchar(128)
	, @id int, @ptrSettingsXML binary(16), @insert_offset int, @delete_length int

declare @t table (
	id int identity(1, 1) not null, ptrSettingsXML binary(16) not null
	, NodeStart int not null, NodeEnd int not null, NodeLength int not null
	, insert_offset AS NodeStart + NodeLength - 1
	, delete_length AS NodeEnd - (NodeStart + NodeLength)
)

set nocount on

select
	@NodeOriginalEscaped = N''default''
	, @NodeNew = N''hawaii''
	, @NodeName = N''DefaultTheme''

select
	db_name() DatabaseName, s.SettingsID, s.SettingsXML
	, case when patindex(
		N''%<'' + @NodeName + N''>''
		+ @NodeOriginalEscaped
		+ N''</'' + @NodeName + N''>%''
		, s.SettingsXML )<>0 then ''Updating'' else ''No Update'' end
from
	cs_SiteSettings s

insert into @t (ptrSettingsXML, NodeStart, NodeEnd, NodeLength)
select
	TEXTPTR( s.SettingsXML )
	, patindex( N''%<'' + @NodeName + N''>%'', s.SettingsXML )
	, patindex( N''%</'' + @NodeName + N''>%'', s.SettingsXML )
	, 2 + LEN( @NodeName )
from
	cs_SiteSettings s
where
	s.SettingsXML IS NOT NULL
	and patindex(
		N''%<'' + @NodeName + N''>''
		+ @NodeOriginalEscaped
		+ N''</'' + @NodeName + N''>%''
		, s.SettingsXML ) <> 0

while 1=1
begin
	select @id = (select min(id) from @t)
	if @id is null break

	select @ptrSettingsXML = t.ptrSettingsXML, @insert_offset = t.insert_offset, @delete_length = t.delete_length
	from @t t where id = @id
	
	UPDATETEXT cs_SiteSettings.SettingsXML @ptrSettingsXML @insert_offset @delete_length @NodeNew

	delete from @t where id = @id
end
')

exec(N'
declare @myTable table (id int not null, idx int not null, Item nvarchar(500) collate database_default)

declare @t table(
	id int identity(1, 1) not null
	, SectionID int not null
	, PName nvarchar(1000) collate database_default not null
	, PType nchar(1) collate database_default not null
	, PStart int not null, PLength int not null
	, PValue nvarchar(1000) collate database_default not null
	, PValueLen as ( len(PValue) )
	)

declare @s table(
	id int identity(1, 1) not null
	, SectionID int not null
	, ptrPNames binary(16) not null
	, PNamesLength int not null
	, ptrPValues binary(16) not null
	)

declare @frommarker int, @tomarker int, @strlen int, @item nvarchar(500)
	, @PName nvarchar(1000), @PValue nvarchar(1000)
	, @OriginalTheme nvarchar(100), @NewTheme nvarchar(100)
	, @id int, @mtId int, @mtx int
	, @SectionID int, @ptrvalPN binary(16), @ptrvalPV binary(16)

set nocount on
set ansi_warnings off

select
	@OriginalTheme = N''default''
	, @NewTheme = N''hawaii''
	, @id = 1

insert into @s (SectionID, ptrPNames, PNamesLength, ptrPValues)
select
	s.SectionID, TEXTPTR(s.PropertyNames), DATALENGTH(s.PropertyNames), TEXTPTR(s.PropertyValues)
from
	cs_Sections s
where
	s.PropertyNames is not null and DATALENGTH(s.PropertyNames) > 0
	and s.PropertyValues is not null and DATALENGTH(s.PropertyValues) > 0
	and s.PropertyNames like ''%Theme:S:%''
	and s.PropertyValues like ''%'' + @OriginalTheme + ''%''
	and s.ApplicationType in (1, 11)

print ''Possibly changing themes on ('' + cast(@@ROWCOUNT as varchar) + '') sections from '' + @OriginalTheme + '' to '' + @NewTheme

while (1 = 1)
begin
	select @SectionID = SectionID, @ptrvalPN = ptrPNames, @ptrvalPV = ptrPValues from @s where id = @id

	if @@rowcount = 0 begin break end

	select @frommarker=1, @tomarker=1, @mtId = 1, @mtx = 1
	delete from @myTable
	while (@tomarker > 0)
	begin
		select @tomarker = CHARINDEX (N'':'', PropertyNames, @frommarker)
			, @strlen = case when @tomarker > 0 then @tomarker - @frommarker else DATALENGTH(PropertyNames) - @frommarker + 1 end
			, @item = ltrim(rtrim(SUBSTRING (PropertyNames, @frommarker, @strlen)))
		from cs_Sections where SectionID = @SectionID
		insert into @myTable values(@mtId, @mtx, @item)
		select @mtId = case when @mtx % 4 = 0 then @mtId + 1 else @mtId end, @mtx = case @mtx when 4 then 1 else @mtx + 1 end, @frommarker = @tomarker + 1
	end

	insert into @t (SectionID, PName, PType, PStart, PLength, PValue)
	select
		@SectionID
		, min(case when idx = 1 then Item end), min(case when idx = 2 then Item end)
		, convert(int, min(case when idx = 3 then Item end)), convert(int, min(case when idx = 4 then Item end))
		, N''''
	from @myTable
	where coalesce(Item, N'''') <> N''''
	group by id

	update t set t.PValue = substring(s.PropertyValues, t.PStart + 1, t.PLength)
	from @t t inner join cs_Sections s on t.SectionID = s.SectionID

	update @t set PValue = @NewTheme where PName = N''Theme'' and PValue = @OriginalTheme and SectionID = @SectionID

	if @@ROWCOUNT > 0
	begin
		print ''Editing theme on SectionID '' + cast(@SectionID as varchar)
		DECLARE sectionCursor CURSOR FOR
		select t1.PName + N'':'' + t1.PType + N'':'' + convert(nvarchar, coalesce(sum(t2.PValueLen), 0)) + N'':'' + convert(nvarchar, t1.PValueLen) + N'':'', t1.PValue
		from @t t1 left outer join @t t2 on t1.SectionID = t2.SectionID and t1.id > t2.id
		where t1.SectionID = @SectionID
		group by t1.id, t1.SectionID, t1.PName, t1.PType, t1.PValueLen, t1.PValue
		order by t1.id

		updatetext cs_Sections.PropertyNames  @ptrvalPN 0 null N''''
		updatetext cs_Sections.PropertyValues @ptrvalPV 0 null N''''

		open sectionCursor
		fetch next from sectionCursor into @PName, @PValue
		while @@fetch_status = 0
		begin
			updatetext cs_Sections.PropertyNames  @ptrvalPN null 0 @PName
			updatetext cs_Sections.PropertyValues @ptrvalPV null 0 @PValue
			fetch next from sectionCursor into @PName, @PValue
		end
		close sectionCursor
		deallocate sectionCursor
	end else begin
		print ''No change for SectionID '' + cast(@SectionID as varchar)
	end

	set @id = @id + 1
end
set ansi_warnings on
set nocount off
')


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