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

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'
ALTER TABLE [dbo].[cs_Threads]
ADD [ThreadType] int NOT NULL CONSTRAINT [DF_cs_Threads_ThreadType] DEFAULT (0)'

EXECUTE sp_executesql N'
set nocount on
set ansi_warnings off
declare @myTable table(id int not null,idx int not null,Item nvarchar(500) collate database_default)
declare @p table(Id int identity(1,1) not null,PostID int not null,ThreadID int not null,namesPtr binary(16) not null,valuesPtr binary(16) not null)
declare @t table(id int identity(1, 1) not null primary key
	,PostID int not null,ThreadID 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
	@frommarker int,@tomarker int,@strlen int,@item nvarchar(500),@PName nvarchar(1000),@PValue nvarchar(1000)
	,@mtId int,@mtx int,@Id int,@PostID int,@ThreadID int,@namesPtr binary(16),@valuesPtr binary(16)

insert into @p(PostID,ThreadID,namesPtr,valuesPtr)
select p.PostID,p.ThreadID,textptr(p.PropertyNames),textptr(p.PropertyValues) from cs_Posts as p
where EXISTS(select 1 from cs_Sections as s where s.SettingsID=p.SettingsID and s.SectionID=p.SectionID AND s.ApplicationType=0)
	AND p.PostID=p.ParentID	AND p.PropertyNames like N''%ThreadType:%''

select @Id=1

while 1=1
begin
	select @PostID=p.PostID,@ThreadID=p.ThreadID from @p as p where p.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_Posts where PostID=@PostID
		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(PostID,ThreadID,PName,PType,PStart,PLength,PValue)
	select
		@PostID,@ThreadID,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
	set @Id=@Id+1
end

update t set t.PValue=substring(p.PropertyValues,t.PStart+1,t.PLength)
from @t t inner join cs_Posts p on t.PostID=p.PostID

update csT set csT.ThreadType=convert(int,t.ThreadTypeS)
from cs_Threads csT inner join(
	select tx.ThreadID,tx.PValue ThreadTypeS from @t as tx
	where tx.PName=N''ThreadType'' AND tx.PValue IS NOT NULL AND datalength(ltrim(rtrim(tx.PValue)))>0 AND ISNUMERIC(ltrim(rtrim(tx.PValue)))=1
) as t on csT.ThreadID=t.ThreadID

delete t from @t t where t.PName=N''ThreadType''

select @Id=1

while 1=1
begin
	select @PostID=p.PostID,@namesPtr=p.namesPtr,@valuesPtr=p.valuesPtr from @p as p where p.Id=@Id
	if @@ROWCOUNT=0 begin break end
	updatetext cs_Posts.PropertyNames  @namesPtr 0 null N''''
	updatetext cs_Posts.PropertyValues @valuesPtr 0 null N''''
	if exists(select 1 from @t t where t.PostID=@PostID)
	begin
		DECLARE pCursor 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.PostID=t2.PostID and t1.id>t2.id
		where t1.PostID=@PostID
		group by t1.id,t1.PostID,t1.PName,t1.PType,t1.PValueLen,t1.PValue
		order by t1.id

		open pCursor
		fetch next from pCursor into @PName,@PValue
		while @@fetch_status = 0
		begin
			updatetext cs_Posts.PropertyNames  @namesPtr null 0 @PName
			updatetext cs_Posts.PropertyValues @valuesPtr null 0 @PValue
			select @PostID,@PName,@PValue
			fetch next from pCursor into @PName,@PValue
		end
		close pCursor
		deallocate pCursor
	end
	set @Id=@Id+1
end

set nocount off
set ansi_warnings on
'

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