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

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 @HubWikis table ( HubId int NOT NULL, WikiId int NOT NULL )

declare @Pages table ( NewWikiId int NOT NULL, PageId int NOT NULL, PageKey nvarchar(256) NOT NULL, ShouldChangePageKey bit NOT NULL)

set ansi_warnings off

/* determine which Hubs have duplicate Wikis and when the latest enabled Wiki for each Hub, taking the preference for latest */
insert into @HubWikis (HubId, WikiId)
select w.HubId, coalesce(max(case when w.IsEnabled = 1 then w.WikiId else null end), max(w.WikiId))
	from cs_Wiki_Wikis w
	where exists (
		select 1 from cs_Wiki_Wikis w2
		where w.HubId = w2.HubId and w2.HubId is not null
		group by w2.HubId
		having count(*) > 1
	)
	group by w.HubId

set ansi_warnings on

/* determine the pages that are affected by this scenario */
insert into @Pages (NewWikiId, PageId, PageKey, ShouldChangePageKey)
select hw.WikiId, p.PageId, p.PageKey, 0
from cs_Wiki_Wikis w
inner join cs_Wiki_Pages p on w.WikiId = p.WikiId
inner join @HubWikis hw on hw.HubId = w.HubId and w.WikiId <> hw.WikiId
where
	exists (
		select 1 from cs_Wiki_Wikis w2
		inner join cs_Wiki_Pages p2 on w2.WikiId = p2.WikiId
		where w.HubId = w2.HubId
			and w.WikiId <> w2.WikiId
			and p.PageKey = p2.PageKey
	)

/* decide which pagekeys should be updated */
update p set
	p.ShouldChangePageKey = 1
from @Pages p
inner join cs_Wiki_Wikis w1 on w1.WikiId = p.NewWikiId
inner join cs_Wiki_Wikis w2 on w1.HubId = w2.HubId
inner join cs_Wiki_Pages wp on w2.WikiId = wp.WikiId and wp.PageKey = p.PageKey

/* update page keys for pages in duplicated wikis with duplicated page keys to include the PageId */
update wp set
	wp.PageKey = wp.PageKey + nchar(32) + convert(nvarchar, wp.PageId)
from cs_Wiki_Pages wp
inner join @Pages p on wp.PageId = p.PageId and p.ShouldChangePageKey = 1

/* update page keys for page revisions in duplicated wikis with duplicated page keys to include the PageId */
update wpr set
	wpr.PageKey = wpr.PageKey + nchar(32) + convert(nvarchar, wpr.PageId)
from cs_Wiki_PageRevisions wpr
inner join @Pages p on wpr.PageId = p.PageId and p.ShouldChangePageKey = 1

/* update all pages in duplicated wikis to use the latest WikiID and set IsPublished to 0 */
update p set
	p.WikiId = hw.WikiId
	, p.IsPublished = 0
from cs_Wiki_Pages p
inner join cs_Wiki_Wikis w on p.WikiId = w.WikiId
inner join @HubWikis hw on w.HubId = hw.HubId and w.WikiId <> hw.WikiId

/* delete duplicated wikis */
delete cs_Wiki_Wikis
where WikiId in
(
	SELECT w.WikiId
	FROM cs_Wiki_Wikis w
	INNER JOIN @HubWikis hw on w.HubId = hw.HubId and w.WikiId <> hw.WikiId
)
'

exec sp_executesql N'
declare @Pages table (PageId int NOT NULL)

/* determine the pages that are affected by this scenario */
insert into @Pages (PageId)
select p.PageId
from cs_Wiki_Pages p
where
	exists (
		select 1 from cs_Wiki_Pages p2
		where p.WikiId = p2.WikiId
			and p.PageKey = p2.PageKey
		group by
			p2.WikiId, p2.PageKey
		having
			p.PageId > min(p2.PageId)
	)

/* update page keys for pages in duplicated wikis with duplicated page keys to include the PageId */
update wp set
	wp.PageKey = wp.PageKey + nchar(32) + convert(nvarchar, wp.PageId)
from cs_Wiki_Pages wp
inner join @Pages p on wp.PageId = p.PageId

/* update page keys for page revisions in duplicated wikis with duplicated page keys to include the PageId */
update wpr set
	wpr.PageKey = wpr.PageKey + nchar(32) + convert(nvarchar, wpr.PageId)
from cs_Wiki_PageRevisions wpr
inner join @Pages p on wpr.PageId = p.PageId
'

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