PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Release 2.6 start'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Target Feedback'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[mig_item].[FeedbackItem]')
      AND type = 'U'
)
BEGIN
PRINT N'Creating Table [mig_item].[FeedbackItem]...';
CREATE TABLE [mig_item].[FeedbackItem] (
    [ItemId]        BIGINT        NOT NULL,
    [JobId]         INT           NOT NULL,
    [JobInstanceId] INT           NOT NULL,
    [Discarded]     BIT           NOT NULL,
    [CreationDate]  DATETIME2 (7) CONSTRAINT [DF_FeedbackItem_CreationDate] DEFAULT (sysdatetime()) NOT NULL,
    CONSTRAINT [PK_FeedbackItem] PRIMARY KEY CLUSTERED ([ItemId] ASC, [JobId] ASC)
);
CREATE NONCLUSTERED INDEX [IX_FeedbackItem_Valid]
    ON [mig_item].[FeedbackItem]([ItemId] ASC, [JobId] ASC, [JobInstanceId] ASC)
    INCLUDE([Discarded]);
END
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[mig_item].[FeedbackEventXml]')
      AND type = 'U'
)
BEGIN
PRINT N'Creating Table [mig_item].[FeedbackEventXml]...';
CREATE TABLE [mig_item].[FeedbackEventXml] (
    [ItemId]               BIGINT           NOT NULL,
    [CorrelationIDLast]    UNIQUEIDENTIFIER NOT NULL,
    [CorrelationIDCreator] UNIQUEIDENTIFIER NOT NULL,
    [Hash]                 BINARY (32)      NOT NULL,
    [Deleted]              BIT              NOT NULL,
    [XmlCompressed]        VARBINARY (MAX)  NULL,
    CONSTRAINT [PK_FeedbackEventXml] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);
END
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[mig_item].[FeedbackEventXmlVersion]')
      AND type = 'U'
)
BEGIN
PRINT N'Creating Table [mig_item].[FeedbackEventXmlVersion]...';
CREATE TABLE [mig_item].[FeedbackEventXmlVersion] (
    [VersionId]          BIGINT           IDENTITY (1, 1) NOT NULL,
    [ItemId]             BIGINT           NOT NULL,
    [CorrelationID]      UNIQUEIDENTIFIER NOT NULL,
    [Hash]               BINARY (32)      NOT NULL,
    [XmlPatchCompressed] VARBINARY (MAX)  NULL,
    [CreationDate]       DATETIME2 (7)    NOT NULL,
    CONSTRAINT [PK_FeedbackEventXmlVersion] PRIMARY KEY CLUSTERED ([VersionId] ASC)
);
CREATE NONCLUSTERED INDEX [IX_FeedbackEventXmlVersion_Item]
    ON [mig_item].[FeedbackEventXmlVersion]([ItemId] ASC);
END
GO
PRINT N'Creating View [mig_valid].[FeedbackItem]...';
GO
CREATE OR ALTER view [mig_valid].[FeedbackItem]
as
	select
		A.*
	from
		[mig_item].[FeedbackItem] as A

		inner join [mig_item].[Active] as B
		on	
			A.[ItemId] = B.[ItemId]
		and B.[Step] = 2 -- Feedback
		and	A.[JobId] = B.[JobId] 
		and	A.[JobInstanceId] = B.[JobInstanceId] 

		inner join [mig_item].[Id] as C
		on
			A.[ItemId] = C.[ItemId]
	where
		C.[InvalidatedTime] is null
GO
PRINT N'Creating View [mig_valid].[FeedbackEventXml]...';
GO
CREATE OR ALTER VIEW [mig_valid].[FeedbackEventXml] 
AS
	select  
		A.[ItemId] 
	,	A.[CorrelationIDLast]
	,	A.[CorrelationIDCreator]
	,	cast(decompress(A.[XmlCompressed]) as nvarchar(max)) as [XmlData]
	from  
		[mig_item].[FeedbackEventXml] as A with (NoLock)
		inner join [mig_valid].[Item] as B with (NoLock)
		on 
			A.[ItemId] = B.[ItemId]
	where
		A.[Deleted] = 0
GO
PRINT N'Creating View [mig_valid].[FeedbackEventXmlVersion]...';
GO
CREATE OR ALTER VIEW [mig_valid].[FeedbackEventXmlVersion] 
AS
	select  
		[VersionId] 
	,	[ItemId] 
	,	[CorrelationID]
	,	[Hash]
	,	[CreationDate]
	,	cast(decompress([XmlPatchCompressed]) as nvarchar(max)) as [XmlPatch]
	from  
		[mig_item].[FeedbackEventXmlVersion] with (NoLock)
GO
PRINT N'Creating Procedure [mig_item].[ItemId_GetFromKey]...';
GO
create OR ALTER procedure [mig_item].[ItemId_GetFromKey]
	@keyValue nvarchar(max)
,	@itemId bigint output
as
	set @itemId = (
		select top(1)
			B.[ItemId]
		from	
			[mig_item].[MainKey] as A
			inner join [mig_item].[Item] as B
			on
				A.[KeyId] = B.[KeyId]
		where
			A.[KeyValue] = @keyValue
	)
GO
PRINT N'Creating Procedure [mig_item].[Xml_InsertFeedbackEvent]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertFeedbackEvent]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,	@savingVersions bit
,   @xml varbinary(max) = null
as
	declare @xmlPrevious varbinary(max) 

	if 1 = @savingVersions
	begin 
		select 
			@xmlPrevious = [XmlCompressed] 
		from
			[mig_item].[FeedbackEventXml] with (NoLock)
		where
			[ItemId] = @ItemId
	end

	merge [mig_item].[FeedbackEventXml] as tgt
	using (
		select 
			@itemId as [ItemId]
		,	@correlationID as [CorrelationID]
		,	@hash as [Hash]
		,	@xml as [XmlCompressed]
		) as src
	on 
		tgt.[ItemId] = src.[ItemId]

	when matched then
		update set
			tgt.[CorrelationIDCreator] = @correlationID
		,	tgt.[CorrelationIDLast] = @correlationID
		,	tgt.[Hash] = src.[Hash]
		,	tgt.[Deleted] = 0
		,	tgt.[XmlCompressed] = src.[XmlCompressed]

	when not matched then
		insert (
			[ItemId]
		,	[CorrelationIDCreator]
		,	[CorrelationIDLast]
		,	[Hash]
		,	[Deleted]
		,	[XmlCompressed]
		) values (
			src.[ItemId]
		,	src.[CorrelationID]
		,	src.[CorrelationID]
		,	src.[Hash]
		,	0
		,	src.[XmlCompressed]
		);

	-- Return the previous compressed xml as a result set for efficient
	-- streamed decompressed in client
	select @xmlPrevious as [XmlPrevious]
GO
PRINT N'Creating Procedure [mig_item].[Xml_InsertFeedbackEventVersion]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_InsertFeedbackEventVersion]
	@itemId bigint
,	@correlationID uniqueidentifier
,   @hash binary(32) = 0
,   @xmlPatch varbinary(max) = null
as
	set nocount on;

	insert [mig_item].[FeedbackEventXmlVersion] (
		[ItemId]        
	,	[CorrelationID]
	,	[Hash]
	,	[XmlPatchCompressed]	
	,	[CreationDate]
	) values (
		@itemId
	,	@correlationID
	,   @hash
	,	@xmlPatch
	,	sysdatetime()
	)
GO
PRINT N'Creating Procedure [mig_item].[Xml_StampFeedbackEvent]...';
GO
CREATE OR ALTER procedure [mig_item].[Xml_StampFeedbackEvent]
	@itemId bigint
,   @correlationID uniqueidentifier
,   @hash binary(32) = 0
as
	update [mig_item].[FeedbackEventXml]
	set
		[CorrelationIDLast] = @correlationID
	,	[Deleted] = 0
	where
		[ItemId] = @itemId
	and	[Hash] = @hash

	-- If the update did not hit a row, return false to signal that the hash did not
	-- match (or there was no xml) and the xml therefore must be updated
	select case @@rowCount
		when 0 then cast(0 as bit)
		else cast(1 as bit)
	end
GO
PRINT N'Creating Procedure [mig_item].[Feedback_Insert]...';
GO
create or alter PROCEDURE [mig_item].[Feedback_Insert]
	@itemId bigint
,	@correlationID uniqueidentifier
,	@jobId int
,	@jobInstanceId int
,	@step tinyint
,	@discarded bit
,	@savingVersions bit
as
	set NoCount on

	if not exists (select 1 from [mig_valid].[Item] where [ItemId] = @itemId)
	begin
		declare @text nvarchar(1000) = 'Item Id ' + cast(@itemId as nvarchar(15)) + ' not found'
		raiserror(@text, 16, 1)
	end
	
	exec [mig_garbage].[_InvalidateItem] @itemId, @step, @jobId, @jobInstanceId

	-------------------------------------------
	-- Insert or update Item for this iteration	
	-------------------------------------------
	merge into [mig_item].[FeedbackItem] as tgt
	using (select @itemId as [ItemId]) as src
	on (tgt.[ItemId] = src.[ItemId])
	when matched then
		update set
			tgt.[JobId] = @jobId
		,	tgt.[JobInstanceId] = @jobInstanceId
		,	tgt.[Discarded] = @discarded
	when not matched then
		insert (
			[ItemId]
		,	[JobId]
		,	[JobInstanceId]
		,	[Discarded]
		) values (
			@itemId
		,	@jobId
		,	@jobInstanceId
		,	@discarded
		);

	-------------------------------------------------
	-- Preserve history	
	-------------------------------------------------
	if 1 = @savingVersions
	begin
		insert [mig_item].[History] (
			[ItemId]
		,	[CorrelationID]
		,	[JobId]
		,	[Step]
		,	[Discarded]
		,	[Commit]
		,	[CleanWorkspace]
		,	[CreationDate]
		) values (
			@itemId
		,	@correlationID
		,	@jobId
		,	@step
		,	@discarded
		,	0 -- Commit is always 0 for feedback
		,	1 -- CleanWorkspace is always 1 for feedback
		,	sysdatetime()
		)
	end
GO
PRINT N'Altering Procedure [mig_director].[Event_List]...';
GO
ALTER procedure [mig_director].[Event_List]
	@export bit 
,   @dispositions varchar(max) = null
,   @impacts varchar(max) = null
,	@entities varchar(max) = null
as
	declare @dispositionTable table([Disposition] int, primary key (Disposition))
	declare @impactTable table([Impact] int, primary key (Impact))
	declare @entitiesTable table([EntityID] uniqueidentifier primary key (EntityID))
	
	if @dispositions is not null
	begin
		insert into @dispositionTable
		select distinct convert(int, [Value]) as [Disposition] from [mig_utility].[ParseDelimitedString](@dispositions, ',')
	end

	if @impacts is not null
	begin
		insert into @impactTable
		select distinct convert(int, [Value]) as [Impact] from [mig_utility].[ParseDelimitedString](@impacts, ',')
	end

	if @entities is not null
	begin
		insert into @entitiesTable
		select distinct [Id] from [mig_utility].[ParseGuids](@entities, ',')
	end

	;with 
		systemEvents as (
			select
				C.[EntityID]
			,	A.[Step]
			,	A.[Engine]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Receiver]
			,	A.[Message]		
			,	count(*) as [Count]
			,	count(distinct A.[ItemId]) as [Items]
			from
				[mig_item].[EventSystem] as A with (NoLock)				
				inner join [mig_item].[Id] as B with (noLock)
					on A.[ItemId] = B.[ItemId] and B.[InvalidatedTime] is null
				inner join [mig_item].[ExportKey] as C with (NoLock)
					on A.[ItemId] = C.[ItemId]
				inner join [mig_item].[Active] as D with (NoLock)
					on	A.[ItemId] = D.[ItemId]
					and A.[JobId] = D.[JobId]
					and A.[JobInstanceId] = D.[JobInstanceId]
					and A.[Step] = D.[Step]
			where
					((1 = @export and 0 = A.[Step]) or (0 = @export and A.[Step] in (1, 2))) -- 0: StepCode.Export, 1: StepCode.Import, 2: StepCode.Feedback
					and (@dispositions is null or A.[Disposition] in (select [Disposition] from @dispositionTable))
					and (@impacts is null or A.[Impact] in (select [Impact] from @impactTable))
					and (@entities is null or C.[EntityID] in (select [EntityID] from @entitiesTable))
			group by
				C.[EntityID]
			,	A.[Step]
			,	A.[Engine]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Receiver]
			,	A.[Message]		
		)
	, userEvents as (
			select
				C.[EntityID]
			,	A.[Step]
			,	A.[Engine]
			,	A.[EventID]
			,	A.[EventName]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Receiver]
			,	A.[EventTextID]
			,	count(*) as [Count]
			,	count(distinct A.[ItemId]) as [Items]
			from
				[mig_item].[EventUser] as A with (NoLock)
				inner join [mig_item].[Id] as B with (noLock)
					on	A.[ItemId] = B.[ItemId] and B.[InvalidatedTime] is null
				inner join [mig_item].[ExportKey] as C with (NoLock)
					on	A.[ItemId] = C.[ItemId]
				inner join [mig_item].[Active] as D with (NoLock)
					on	A.[ItemId] = D.[ItemId]
					and A.[JobId] = D.[JobId]
					and A.[JobInstanceId] = D.[JobInstanceId]
					and A.[Step] = D.[Step]
			where
					((1 = @export and 0 = A.[Step]) or (0 = @export and 1 = A.[Step]))
					and (@dispositions is null or A.[Disposition] in (select [Disposition] from @dispositionTable))
					and (@impacts is null or A.[Impact] in (select [Impact] from @impactTable))
					and (@entities is null or C.[EntityID] in (select [EntityID] from @entitiesTable))
			group by
				C.[EntityID]
			,	A.[Step]
			,	A.[Engine]
			,	A.[EventID]
			,	A.[EventName]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Receiver]
			,	A.[EventTextID]
		)
	, allEvents as (
	 		select
	 			A.[EntityID]
			,	A.[Step]
			,	A.[Engine]
			,	A.[EventID]
			,	A.[EventName]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Receiver]
			,	isnull(substring(B.[DefaultText], 1, 2000), 'Text not found (' + lower(convert(varchar(50), A.[EventTextID])) + ')') as [Message]
			,	A.[Count]
			,	A.[Items]
			from
				userEvents as A
				left outer join [mig_director].[SmartText] as B with (NoLock)
					on A.[EventTextID] = B.[ItemID]
			union all
	 		select
				[EntityID]
			,	[Step]
			,	[Engine]
			,	cast(cast(0 as binary) as uniqueidentifier) -- Guid.Empty
			,	'(System)'
			,	[Disposition]
			,	[Impact]
			,	[Receiver]
			,	[Message]
			,	[Count]
			,	[Items]
			from
				systemEvents
		)
		select
			B.[EntityName]
		,	A.[Disposition] 
		,	A.[Impact] 
		,	A.[EntityID]
		,	A.[Step] 
		,	A.[Engine] 
		,	A.[EventID]
		,	A.[EventName]
		,	A.[Receiver] 
		,	A.[Message]
		,	A.[Count]
		,	A.[Items]
		from 
			allEvents as A
			inner join [mig_director].[ExportEntity] as B with (NoLock)
				on A.[EntityID] = B.[EntityID] and B.[IsPresent] = 1
		order by
				B.[EntityName]
			,	A.[Disposition]
			,	A.[Impact]
go
PRINT N'Altering Procedure [mig_item].[Import_GetByEvent]...';
GO
alter procedure [mig_item].[Import_GetByEvent] (
	@userEventlist varchar(max)
,	@systemEventlist varchar(max)
,	@includeDescendants bit
,	@includeAncestors bit
,	@limit bigint
,	@jobId int
)
as
	declare @delimiter varchar(10) = '<>##.##<>'
	declare @count int = 0
	declare @str varchar(max)
	declare @xml xml
	declare @entityID uniqueidentifier
	declare @eventID uniqueidentifier
	declare @message varchar(1024)
	declare @engine tinyint
	declare @itemIds [mig_item].[ItemIdList]

	-- User events
	declare csrUser cursor for select [Value] from [mig_utility].[ParseDelimitedString](@userEventlist, @delimiter)

	open csrUser

	fetch next from csrUser into @str

	while @@fetch_status = 0
	begin

		set @xml = convert(xml, @str)

		set @entityID = @xml.value('(*/EntityID)[1]', 'uniqueidentifier')
		set @eventID = @xml.value('(*/EventID)[1]', 'uniqueidentifier')
		set @engine  = @xml.value('(*/Engine)[1]', 'tinyint')

		insert @itemIds
		select 
			A.[ItemId]
		from
			[mig_valid].[Item] as A with (NoLock)

			inner join [mig_valid].[EventUser] as B
			on	
				A.[ItemId] = B.[ItemId] 
			and	B.[Engine] = @engine
			and	B.[EventID] = @eventID 

			inner join [mig_item].[ExportKey] as C with (NoLock)
			on	
				A.[ItemId] = C.[ItemId]
			and	C.[EntityID] = @entityID
			left outer join @itemIds as D
			on 
				A.[ItemId] = D.[ItemId]
		where
			D.[ItemId] is null
		group by
			A.[ItemId]

		set @count = @count + @@rowcount

		fetch next from csrUser into @str
	end

	close csrUser
	deallocate csrUser

	-- System events
	declare csrSystem cursor for select [Value] from [mig_utility].[ParseDelimitedString](@systemEventlist, @delimiter)

	open csrSystem

	fetch next from csrSystem into @str

	while @@fetch_status = 0
	begin

		set @xml = convert(xml, @str)

		set @entityID = @xml.value('(*/EntityID)[1]', 'uniqueidentifier')
		set @message = @xml.value('(*/Message)[1]', 'varchar(1024)')
		set @engine  = @xml.value('(*/Engine)[1]', 'tinyint')

		insert @itemIds
		select 
			A.[ItemId]
		from
			[mig_valid].[Item] as A with (NoLock)

			inner join [mig_valid].[EventSystem] as B
			on	
				A.[ItemId] = B.[ItemId] 
			and	B.[Engine] = @engine
			and	B.[Message] = @message

			inner join [mig_item].[ExportKey] as C with (NoLock)
			on	
				A.[ItemId] = C.[ItemId] 
			and	C.[EntityID] = @entityID

			left outer join @itemIds as D
			on 
				A.[ItemId] = D.[ItemId]
		where
			D.[ItemId] is null
		group by
			A.[ItemId]

		set @count = @count + @@rowcount

		fetch next from csrSystem into @str
	end

	close csrSystem
	deallocate csrSystem

	-- If too many, delete randomly in an effort to get some of all event types
	if @count > @limit
	begin
		with del as (
			select top(@count - @limit)
				[ItemId]
			from 
				@itemIds
			order by
				[ItemId] % 500
		)
		delete from @itemIds
		from
			@itemIds as A
			inner join del as B
			on 
				A.[ItemId] = B.[ItemId]
	end

	exec [mig_item].[_Import_CalculateLevels] @jobId, @itemIds, @includeDescendants, @includeAncestors
GO
PRINT N'Altering Procedure [mig_director].[Setup_ClearOther]...';
GO
create or alter procedure [mig_director].[Setup_ClearOther]
	@mode tinyint -- 0: ResetTrack, 1: ResetIterationExInititializer, 2: ResetIterationIncInitializer
,	@jobId int
,	@includeDc bit
as
	-- Store Active row from Initializer
	select *
	into #initActive
	from [mig_item].[Active]
	where [ItemId] = 0

	-- Always include these
	truncate table [mig_director].[GlobalCounter]
	truncate table [mig_director].[ItemSet]
	
	truncate table [mig_garbage].[Log]
	
	truncate table [mig_item].[Audit]
	truncate table [mig_item].[ImportItem]
	truncate table [mig_item].[Dependency]
	truncate table [mig_item].[EventSystem]
	truncate table [mig_item].[EventUser]
	truncate table [mig_item].[ExportItem]
	truncate table [mig_item].[FeedbackItem]
	truncate table [mig_item].[Garbage]
	truncate table [mig_item].[Item]
	truncate table [mig_item].[ItemSet]
	truncate table [mig_item].[Processed]
	truncate table [mig_item].[SavedString]
	truncate table [mig_item].[Identity]

	truncate table [mig_profile].[Item]
	truncate table [mig_profile].[Frame]
	truncate table [mig_profile].[Job]
	truncate table [mig_profile].[Trace]

	truncate table [mig_item].[Active]

	if 0 = @mode -- 'Reset Track' - Just clear everything
	begin
		-- Do not truncate table [mig_director].[Engine] in case the same engines are used for the 
		-- new project without restarting the track

		truncate table [mig_director].[Concurrency]
		truncate table [mig_director].[Valueset]
		truncate table [mig_director].[ValuesetBaseline]
		truncate table [mig_director].[ValuesetProvider]
		truncate table [mig_director].[DirectorOption]
		truncate table [mig_director].[BusinessEntity]
		truncate table [mig_director].[ExportEntity]
		truncate table [mig_director].[ExportEntitySourceDependency]
		truncate table [mig_director].[ExportSourceTable]
		truncate table [mig_director].[ExportSourceTable_Baseline]
		truncate table [mig_director].[ExportSourceView]
		truncate table [mig_director].[ExportSourceView_Baseline]
		truncate table [mig_director].[ExportSourceViewDependency]
		truncate table [mig_director].[RunParameter]
		truncate table [mig_director].[SmartText]
		
		truncate table [mig_item].[History]
		truncate table [mig_item].[ExportXml]
		truncate table [mig_item].[ExportXmlVersion]
		truncate table [mig_item].[ExportEventXml]
		truncate table [mig_item].[ExportEventXmlVersion]
		truncate table [mig_item].[FeedbackEventXml]
		truncate table [mig_item].[FeedbackEventXmlVersion]
		truncate table [mig_item].[ImportEventXml]
		truncate table [mig_item].[ImportEventXmlVersion]
		truncate table [mig_item].[SourceXml]
		truncate table [mig_item].[SourceXmlVersion]
		truncate table [mig_item].[TargetXml]
		truncate table [mig_item].[TargetXmlVersion]
		truncate table [mig_item].[DependencyHistory]
		truncate table [mig_item].[DependencyHistoryDetail]

		truncate table [mig_item].[MainKey]
		truncate table [mig_item].[MainKeyField]
		truncate table [mig_item].[RelationshipKey]
		truncate table [mig_item].[RelationshipValue]
		truncate table [mig_item].[RelationshipBagPropertyValue]
		truncate table [mig_item].[ExportKey]
		truncate table [mig_item].[ExportKeyField]
		truncate table [mig_item].[Id] 
		
		truncate table [mig_tracking].[Baseline]
		truncate table [mig_tracking].[EventParameter]
		truncate table [mig_tracking].[Log]
		truncate table [mig_tracking].[Parameter]
		truncate table [mig_tracking].[PartitionEventCount]
		truncate table [mig_tracking].[RootEntityEvent]
		truncate table [mig_tracking].[RootItemEvent]
		truncate table [mig_tracking].[Statistic]

		truncate table [mig_translation].[TranslationValueset]
		truncate table [mig_translation].[TranslationValuesetRow]
	end
	else -- 'New Data' or 'Reset Iteration': More care must be taken
	begin
		if 1 = @includeDc
		begin
			truncate table [mig_director].[Valueset]
		end

		if 1 = @mode -- Reset iteration. The Initializer is not being run
		begin
			insert [mig_item].[Active]
			select * from #initActive

			-- Too expensive to start deleting relationship items with ItemId > 0. 
			-- Leave this to the GarbageCollector (because Active and Id tables are truncated, 
			-- whatever remains in the 2 Relationship* tables above ItemId = 0 is garbage)
		end
		else
		begin
			if 2 = @mode -- Reset iteration. The initializer being run
			begin
				truncate table [mig_item].[RelationshipKey]
				truncate table [mig_item].[RelationshipValue]
				truncate table [mig_item].[RelationshipBagPropertyValue]
			end
		end
	end
GO
PRINT N'Altering function [mig_garbage].[BuildStatement]...';
GO
CREATE OR ALTER function [mig_garbage].[BuildStatement](
	@seed varchar(max)
,	@tableName varchar(50)
)
returns varchar(max)
as
	begin
	
		declare @stmnt varchar(max)
		
		------------------------------------------------------------------------------------------------------------------
		-- The Id table controls everything. If an Item is invalidated here it means it is no longer exported
		------------------------------------------------------------------------------------------------------------------
		if 'Id' = @tableName
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  where' + char(13)
			+ '    A.[InvalidatedTime] < @newest'
		end
		------------------------------------------------------------------------------------------------------------------
		-- These tables are all 'singletons' = only 1 row per item
		------------------------------------------------------------------------------------------------------------------
		else if @tableName in ('Item', 'Active', 'ExportItem', 'ImportItem', 'FeedbackItem', 'SourceXml', 'ExportXml', 'TargetXml')
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Id] as B' + char(13)
			+ '    on A.[ItemId] = B.[ItemId]' + char(13)
			+ '  where' + char(13)
			+ '  (' + char(13)
			+ '       B.[ItemId] is null' + char(13)				
			+ '    or B.[InvalidatedTime] < @newest' + char(13)
			+ '  )'

			if 'Active' = @tableName
			begin
				set @stmnt = @stmnt + char(13) + '  and A.[ItemId] > 0 -- Keep Active row from Initializer'
			end
		end
		------------------------------------------------------------------------------------------------------------------
		-- The garbage table is collected last
		------------------------------------------------------------------------------------------------------------------
		else if @tableName = 'Garbage'
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Active] as B' + char(13)
			+ '    on A.[ItemId] = B.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId] and A.[Step] = B.[Step]' + char(13)
			+ '  where' + char(13)
			+ '      A.[InvalidatedTime] < @newest' + char(13)
			+ '  and B.[ItemId] is null'
		end
		------------------------------------------------------------------------------------------------------------------
		-- Special care in Relationhip tables to preserve relationships created by Initializer
		------------------------------------------------------------------------------------------------------------------
		else if @tableName in ('RelationshipKey', 'RelationshipValue', 'RelationshipBagPropertyValue')
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Garbage] as B' + char(13)
			+ '    on B.[ItemId] = A.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId] and B.[Step] = A.[Step]' + char(13)
			+ '  left outer join [mig_item].[Id] as C' + char(13)
			+ '    on A.[ItemId] = C.[ItemId]' + char(13)
			+ '  left outer join [mig_item].[Active] as D' + char(13)
			+ '    on A.[ItemId] = D.[ItemId] and A.[JobId] = D.[JobId] and A.[JobInstanceId] = D.[JobInstanceId] and A.[Step] = D.[Step]' + char(13)
			+ '  where' + char(13) 
			+ '    -- Other relationships' + char(13)
			+ '    A.[ItemId] > 0' + char(13)
			+ '    and' + char(13)
			+ '    (' + char(13)
			+ '      B.[InvalidatedTime] < @newest' + char(13)
			+ '      or	(C.[ItemId] is null or C.[InvalidatedTime] < @newest)' + char(13)
			+ '    )' + char(13)
		end
		------------------------------------------------------------------------------------------------------------------
		-- The remaing tables contains real Garbage = More than 1 row per Item, recreated every time
		-- The Processed table does not have a Step column, so it is handled separately
		------------------------------------------------------------------------------------------------------------------
		else if @tableName = 'Processed'
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Garbage] as B' + char(13)
			+ '    on B.[ItemId] = A.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId]' + char(13)
			+ '  left outer join [mig_item].[Id] as C' + char(13)
			+ '    on A.[ItemId] = C.[ItemId]' + char(13)
			+ '  left outer join [mig_item].[Active] as D' + char(13)
			+ '    on A.[ItemId] = D.[ItemId] and A.[JobId] = D.[JobId] and A.[JobInstanceId] = D.[JobInstanceId]' + char(13)
			+ '  where' + char(13) 
			+ '    B.[InvalidatedTime] < @newest' + char(13)
			+ '    or	(C.[ItemId] is null or C.[InvalidatedTime] < @newest)' 
		end
		------------------------------------------------------------------------------------------------------------------
		-- The remaing tables contains real Garbage = More than 1 row per Item, recreated every time
		------------------------------------------------------------------------------------------------------------------
		else 
		begin
			set @stmnt = 
				'  [mig_item].[' + @tableName +'] as A' + char(13)
			+ '  left outer join [mig_item].[Garbage] as B' + char(13)
			+ '    on B.[ItemId] = A.[ItemId] and B.[JobId] = A.[JobId] and B.[JobInstanceId] = A.[JobInstanceId] and B.[Step] = A.[Step]' + char(13)
			+ '  left outer join [mig_item].[Id] as C' + char(13)
			+ '    on A.[ItemId] = C.[ItemId]' + char(13)
			+ '  left outer join [mig_item].[Active] as D' + char(13)
			+ '    on A.[ItemId] = D.[ItemId] and A.[JobId] = D.[JobId] and A.[JobInstanceId] = D.[JobInstanceId] and A.[Step] = D.[Step]' + char(13)
			+ '  where' + char(13) 
			+ '    B.[InvalidatedTime] < @newest' + char(13)
			+ '    or	(C.[ItemId] is null or C.[InvalidatedTime] < @newest)' 
		end
		
		return @seed + char(13)+ 'from' + char(13) + @stmnt
		
	end
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Dynamic Engine Load'
GO
PRINT N'Update table [mig_director].[Job]...';
GO
if not exists (
    select 1
    from sys.columns c
    join sys.tables t on t.object_id = c.object_id
    join sys.schemas s on s.schema_id = t.schema_id
    where s.name = N'mig_director'
      and t.name = N'Job'
      and c.name = N'PreserveOnRestart'
)
begin
	alter table [mig_director].[Job] add [PreserveOnRestart] bit constraint [DF_Job_PreserveOnRestart] default (0) not null;
end
GO
declare @constraintname sysname;
declare @sql nvarchar(max);

-- find the default constraint on [mig_director].[job].[creationdate]
select @constraintname = dc.name
from sys.default_constraints dc
join sys.columns c 
    on c.default_object_id = dc.object_id
join sys.tables t 
    on t.object_id = c.object_id
join sys.schemas s
    on s.schema_id = t.schema_id
where s.name = N'mig_director'
  and t.name = N'Job'
  and c.name = N'CreationDate';

-- drop the constraint if it exists and is unnamed
if @constraintname <> N'DF_Job_CreationDate'
begin
    set @sql = N'alter table [mig_director].[Job] drop constraint [' + @constraintname + N'];';
    exec sp_executesql @sql;

	alter table [mig_director].[job] add constraint [DF_Job_CreationDate] default (sysdatetime()) for [CreationDate];
end
GO
print N'Updating Start/Stop Track job markers...'
GO
update [mig_director].[Job]
set
	[Name] = case [Name]
		when 'Track Started' then 'Start Track'
		when 'Track Stopped' then 'Stop Track'
		else [Name]
	end
,	[PreserveOnRestart] = case [Name]
		when 'Track Started' then 1
		else 0
	end
where
	[Name] in ('Track Started', 'Track Stopped')

-- Insert job instance for start stop markers
insert [mig_director].[JobInstance] (
	[JobId]
,	[Priority]
,	[ExecutionState]
,	[Result]
,	[CreatedBy]
) 
select
	A.[JobId] 
,	0
,	2 -- Completed
,	1 -- Success
,	A.[Header] 
from
	[mig_director].[Job] as A
where
	[JobTypeId] = 0
and	not exists (select 1 from [mig_director].[JobInstance] as B where A.[JobId] = B.[JobId])
GO
PRINT N'Alter procedure [mig_director].[Setup_ClearJobs]...';
GO
ALTER PROCEDURE [mig_director].[Setup_ClearJobs]
AS
	set nocount on
	
	-- Preserve the last Track Start and Engine Loads
	select 
		[JobId]
	,	[JobTypeId]
	,	[Name]
	,	[ClassName]
	,	[Header]
	,	[Parameters]
	,	[BatchSize]
	,	[CanCancel]
	,	[CreationDate]	
	,	[PreserveOnRestart]
	into #preserveJob
	from [mig_director].[Job] as A
	where
		[PreserveOnRestart] = 1
	and [JobId] = (
		select max([JobId])
		from
			[mig_director].[Job] as B
		where
			A.[JobTypeId] = B.[JobTypeId]
		and A.[Name] = B.[Name]
	)

	select 
		A.[JobId]
	,	A.[ClientID]
	,	A.[DebugWait]
	,	A.[Priority]
	,	A.[ExecutionState]
	,	A.[Result]
	,	A.[CreationDate]
	,	A.[CreatedBy]
	into #preserveJobInstance
	from 
		[mig_director].[JobInstance] as A
		inner join #preserveJob as B 
		on 
			A.[JobId] = B.[JobId]

	truncate table [mig_director].[Job]
	truncate table [mig_director].[JobInstance]
	truncate table [mig_director].[JobInstanceLog]
	truncate table [mig_director].[JobInstanceRunParameter]
	truncate table [mig_director].[JobData]
	truncate table [mig_director].[Command]

	-- Reestablish preserved job markers
	declare @newJobs table([OldJobId] int, [NewJobId] int)

	merge [mig_director].[Job] as tgt
	using #preservejob as src
		 on 1 = 0
	when not matched then
	    insert (
	          [JobTypeId]
	        , [Name]
	        , [ClassName]
	        , [Header]
	        , [Parameters]
	        , [BatchSize]
	        , [CanCancel]
	        , [CreationDate]
	        , [PreserveOnRestart]
	    )
	    values (
	          src.[JobTypeId]
	        , src.[Name]
	        , src.[ClassName]
	        , src.[Header]
	        , src.[Parameters]
	        , src.[BatchSize]
	        , src.[CanCancel]
	        , src.[CreationDate]
	        , src.[PreserveOnRestart]
	    )
	output src.[jobid], inserted.[jobid] into @newJobs ([OldJobId], [NewJobId]);

	-- Reestablish connected job instances
	insert into [mig_director].[JobInstance] (
		[JobId]
	,	[ClientID]
	,	[DebugWait]
	,	[Priority]
	,	[ExecutionState]
	,	[Result]
	,	[CreationDate]
	,	[CreatedBy]
	)
	select
		B.[NewJobId]
	,	A.[ClientID]
	,	A.[DebugWait]
	,	A.[Priority]
	,	A.[ExecutionState]
	,	A.[Result]
	,	A.[CreationDate]
	,	A.[CreatedBy]
	from 
		#preservejobinstance as A
		inner join @newjobs as B
	    on 
			B.[OldJobId] = A.[JobId]
		order by 
			A.[CreationDate]
GO
PRINT N'Create procedure [mig_director].[Job_InsertMarker]...';
GO
CREATE or ALTER PROCEDURE [mig_director].[Job_InsertMarker]
	@name varchar(50)
,	@header varchar(50)
,	@user varchar(50)
,	@preserveOnRestart bit
,	@processId int
,	@processName varchar(256)
,	@runtimeVersion varchar(50)
,	@info xml = null
,	@error nvarchar(max) = null
,	@jobInstanceId int output
as
	declare @job table([Id] int)
	declare @now datetime2 = sysdatetime()

	insert [mig_director].[Job] (
		[JobTypeId]
	,	[Name]
	,	[ClassName]
	,	[Header]
	,	[Parameters]
	,	[BatchSize]	
	,	[CanCancel]
	,	[PreserveOnRestart]
	)
	output inserted.[JobId] into @job
	values (
		0
	,	@name
	,	''
	,	@header
	,	isnull(@info, '<Parameters />')
	,	0 
	,	0
	,	@preserveOnRestart
	)

	declare @jobInstance table([Id] int)

	insert [mig_director].[JobInstance] (
		[JobId]
	,	[Priority]
	,	[ExecutionState]
	,	[Result]
	,	[TimeStart]
	,	[TimeEnd]
	,	[ProcessId]
	,	[ProcessName]
	,	[RuntimeVersion]
	,	[CreatedBy]
	) 
	output inserted.[Id] into @jobInstance
	values (
		(select top(1) [Id] from @job)
	,	0
	,	2 -- Completed
	,	case 
			when @error is null then 1 -- Success
			else 2 -- Faulted
		end 
	,	@now
	,	@now
	,	@processId
	,	@processName
	,	@runtimeVersion
	,	@user
	)

	set @jobInstanceId = (select top(1) [Id] from @jobInstance)

	if @error is not null
	begin
		insert [mig_director].[JobInstanceLog] (
			[JobInstanceId]
		,	[LogType]
		,	[Message]
		) 
		values (
			@jobInstanceId
		,	2 -- Error
		,	@error
		)
	end
GO
print N'Dropping procedure [mig_director].[Job_InsertStartStop]...'
GO
DROP PROCEDURE IF EXISTS [mig_director].[Job_InsertStartStop]
GO
print N'Alter procedure [mig_director].[Job_List]...'
GO
ALTER procedure [mig_director].[Job_List]
as
	declare @now datetime2(7) = sysdatetime();
	
	with 
		firstInstance as (
			select
				[JobId]
			,	[TimeStart]
			from 
				[mig_director].[JobInstance] as A
			where
				[Id] = (
					select min([Id]) 
					from 
						[mig_director].[JobInstance] as B
					where
						A.[JobId] = B.[JobId]
				)
		)
	,	lastInstance as (
			select
				[JobId]
			,	[Id] as [lastInstanceId]
			,	[ExecutionState]
			,	[TimeStart]
			,	[TimeEnd]
			,	[CreatedBy]
			from 
				[mig_director].[JobInstance] as A
			where
				[Id] = (
					select max([Id]) 
					from 
						[mig_director].[JobInstance] as B
					where
						A.[JobId] = B.[JobId]
				)
	)
	, elapsed as (
			select
				[JobId]
			,	sum([mig_director].[CalculateElapsedSeconds]([TimeStart], [TimeEnd], [ExecutionState], @now)) as [ElapsedSeconds]
			from 
				[mig_director].[JobInstance]
			group by
				[JobId] 
	)
	select
		F.[JobId]
	,	F.[TimeStart]
	,	L.[ExecutionState]
	,	L.[TimeEnd]
	,	L.[LastInstanceId]
	,	L.[CreatedBy]
	,	E.[ElapsedSeconds]
	,	L.[TimeStart] as [LastStart]
	into #instances
	from 
		firstInstance as F
		inner join lastInstance as L
		on 
			F.[JobId] = L.[JobId]
		inner join elapsed as E 
		on 
			F.[JobId] = E.[JobId]
	
	create index __IX_summary on #instances
	(
		[JobId] asc
	)
	
	select
		A.[JobId]
	,	B.[LastInstanceId]
	,	A.[JobTypeId]
	,	A.[Name]
	,	A.[Header]
	,	A.[CanCancel] as [JobTypeCanCancel]
	,	isnull(cast(C.[ExecutionState] as int), -1) as [ExecutionStateNum]
	,	isnull(C.[Result], 0) as [ResultNum]
	,	C.[Priority] as [PriorityNum]
	,	A.[CreationDate] as [TimeSubmit]
	,	B.[TimeStart]
	,	B.[LastStart]
	,	case 
			when C.[ExecutionState] = 3 then null -- Cancelled
			else B.[TimeEnd]
		end as [TimeEnd]
	,	B.[ElapsedSeconds]
	,	B.[CreatedBy] as [LastSubmitter]
	from
		[mig_director].[Job] as A
		left outer join #instances as B
		on 
			A.[JobId] = B.[JobId]
		left outer join [mig_director].[JobInstance] as C
		on 
			C.[Id] = B.[LastInstanceId]
	order by
		A.[JobId] desc
GO
print N'Updating Start/Stop Track job markers...'
GO
update [mig_director].[Job]
set
	[Name] = case [Name]
		when 'Track Started' then 'Start Track'
		when 'Track Stopped' then 'Stop Track'
		else [Name]
	end
GO
print N'Dropping procedure [mig_director].[Job_InsertStartStop]...'
GO
DROP PROCEDURE IF EXISTS [mig_director].[Job_InsertStartStop]
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- EventSystem streaming source SP and TVP-based bridge-back'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating procedure [mig_tracking].[EventSystem_List]...'
GO

CREATE OR ALTER PROCEDURE [mig_tracking].[EventSystem_List]
as
begin
	set nocount on

	-----------------------------------------------------
	-- System Events (consumed as a streamed TVP by
	-- [mig_tracking].[EventSystem_InsertIfMissing] in
	-- the Project DB; see DbProject.EventSystem_InsertIfMissing).
	-- [Message] is VARCHAR(256) here; the receiving TVP column
	-- [EventSystemInput].[MessageFmt] is NVARCHAR(MAX). The
	-- DbDataReader -> TVP wire format requires matching column
	-- types, so convert + alias here.
	-----------------------------------------------------
	select distinct
		[Engine]
	,	[Step]
	,	[Disposition]
	,	[Impact]
	,	[Receiver]
	,	convert(nvarchar(max), [Message]) as [MessageFmt]
	from [mig_valid].[EventSystem]
end
GO

PRINT N'Altering procedure [mig_tracking].[Actual_List] (drop System Events result set)...'
GO

CREATE OR ALTER procedure [mig_tracking].[Actual_List]
as
begin
	set nocount on

	-----------------------------------------------------
	-- List SmartTexts
	-----------------------------------------------------
	select
		[ItemID]
	,	[Xml]
	from [mig_director].[SmartText]

	-----------------------------------------------------
	--  User Events
	-----------------------------------------------------
	select distinct
		A.[EventId]
	,	A.[EventTextId]
	,	A.[Engine]
	,	A.[Step]
	,	A.[Disposition]
	,	A.[Impact]
	,	A.[Receiver]
	,	A.[EventName]
	,	cast(case
			when B.[ItemID] is null then 1
			else 0
		end as bit) as [TextMissing]
	into #userMessage
	from [mig_valid].[EventUser] as A
	left outer join [mig_director].[SmartText] as B
		on B.[ItemID] = A.[EventTextId]

	select
		[EventId]
	,	[EventTextId]
	,	[Engine]
	,	[Step]
	,	[Disposition]
	,	[Impact]
	,	[Receiver]
	,	[EventName]
	from #userMessage

	-----------------------------------------------------
	-- System Events are sourced from
	-- [mig_tracking].[EventSystem_List] and inserted via
	-- [mig_tracking].[EventSystem_InsertIfMissing] on the
	-- Project DB (see DbProject.EventSystem_InsertIfMissing).
	-----------------------------------------------------

	-----------------------------------------------------
	-- List current PartitionValues
	-----------------------------------------------------
	select distinct
		[PartitionValue]
	from
		[mig_valid].[Item]

	-----------------------------------------------------
	-- Report missing smarttexts for User Events
	-----------------------------------------------------
	select
		[EventTextID]
	,	[EventName]
	from #userMessage
	where [TextMissing] = 1
end
GO

PRINT N'Creating type [mig_tracking].[EventSystemMessageInput]...'
GO

IF TYPE_ID(N'[mig_tracking].[EventSystemMessageInput]') IS NULL
	CREATE TYPE [mig_tracking].[EventSystemMessageInput] AS TABLE
	(
		[MessageID]		uniqueidentifier	not null
	,	[Step]			tinyint				not null
	,	[Engine]		tinyint				not null
	,	[Disposition]	tinyint				not null
	,	[Impact]		tinyint				not null
	,	[Receiver]		tinyint				not null
	,	[MessageFmt]	nvarchar(max)		not null
	)
GO

PRINT N'Creating type [mig_tracking].[EventUserMessageInput]...'
GO

IF TYPE_ID(N'[mig_tracking].[EventUserMessageInput]') IS NULL
	CREATE TYPE [mig_tracking].[EventUserMessageInput] AS TABLE
	(
		[MessageID]		uniqueidentifier	not null
	,	[Step]			tinyint				not null
	,	[Engine]		tinyint				not null
	,	[Disposition]	tinyint				not null
	,	[Impact]		tinyint				not null
	,	[Receiver]		tinyint				not null
	,	[EventID]		uniqueidentifier	not null
	)
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Delivery statistics: Statistic columns, _Delivery_Statistics UDF, Actual_Update pivot extension'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering [mig_tracking].[Statistic] (add [DelDiscarded] column)...'
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[mig_tracking].[Statistic]') AND name = N'DelDiscarded')
	ALTER TABLE [mig_tracking].[Statistic]
		ADD [DelDiscarded] BIGINT CONSTRAINT [DF_Statistic_DelDiscarded] DEFAULT (0) NOT NULL
GO

PRINT N'Altering [mig_tracking].[Statistic] (add [Delivered] column)...'
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[mig_tracking].[Statistic]') AND name = N'Delivered')
	ALTER TABLE [mig_tracking].[Statistic]
		ADD [Delivered] BIGINT CONSTRAINT [DF_Statistic_Delivered] DEFAULT (0) NOT NULL
GO

PRINT N'Creating function [mig_director].[_Delivery_Statistics]...'
GO

CREATE OR ALTER FUNCTION [mig_director].[_Delivery_Statistics]()
returns table
as
	return (
		with list as (
			select
				B.[PartitionValue]
			,	C.[EntityID]
			, count(*) as [Count]
			, sum(
					case
						when F.[JobId] is null then 0
						else 1
					end
				) as [Processed]
			, sum(
					case
						when isnull(F.[Discarded], 1) = 0 then 1 -- 0:Delivered
						else 0
					end
				) as [Delivered]
			, sum(
					case
						when isnull(F.[Discarded], 0) = 1 then 1 -- 1:DeliveryDiscarded
						else 0
					end
				) as [Discarded]
			from
				[mig_item].[Id] as A with (NoLock)
				inner join [mig_item].[Item] as B with (NoLock)
					on	A.[ItemId] = B.[ItemId]
				inner join [mig_item].[ExportKey] as C with (NoLock)
					on	A.[ItemId] = C.[ItemId]
				inner join [mig_item].[ExportItem] as D with (NoLock)
					on	A.[ItemId] = D.[ItemId] and D.[Result] = 0 -- 0:Exported
				inner join [mig_item].[ImportItem] as E with (NoLock)
					on	A.[ItemId] = E.[ItemId] and E.[Result] = 2 and E.[JobId] >= D.[JobId] -- 2:Success
				left outer join [mig_item].[FeedbackItem] as F with (NoLock)
					on	A.[ItemId] = F.[ItemId] and F.[JobId] >= E.[JobId]
			where
				A.[InvalidatedTime] is null
			group by
				B.[PartitionValue]
			,	C.[EntityID]
		)

		select
			C.[PartitionValue]
		,	A.[EntityID]
		,	B.[BusinessEntityName]
		, A.[EntityName]
		, C.[Count]
		, C.[Delivered]
		, C.[Discarded]
		, C.[Count] - C.[Processed] as [Difference]
		from
			[mig_director].[ExportEntity] as A with (NoLock)
			inner join [mig_director].[BusinessEntity] as B with (NoLock)
				on A.[BusinessEntityID] = B.[BusinessEntityID]
			left outer join list as C
				on A.[EntityID] = C.[EntityID]
		where
			A.[IsPresent] = 1 and A.[Ignored] = 0
	)
GO

PRINT N'Altering procedure [mig_tracking].[Actual_Update] (TVP parameters + Delivery pivot)...'
GO

CREATE OR ALTER procedure [mig_tracking].[Actual_Update]
	@systemMessages [mig_tracking].[EventSystemMessageInput] readonly
,   @userMessages [mig_tracking].[EventUserMessageInput] readonly
,	@entitiesToProcess varchar(max) = null
,	@runDate datetime2
,   @user varchar(256)
,   @jobInstanceId int
as
begin
	set nocount on;

	declare @start datetime = getdate()
	declare @logId bigint
	declare @text varchar(max)  =
'------------------------------------------------------------------
Elapsed  [mig_tracking].[Actual_Update]
------------------------------------------------------------------'

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output

	-- Table to hold EntityID to process
	declare @entities table(EntityID uniqueidentifier, primary key (EntityID))

	if @entitiesToProcess is not null
	begin
		insert into @entities
		select distinct [Id] from [mig_utility].[ParseGuids](@entitiesToProcess, ',')
	end

	-----------------------------------------------------
	-- EventParameter
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert EventParameter', @logId output

	;with
		usrEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventUser])
	,	sysEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventSystem])
	, allEv as (
		select * from usrEv
		union
		select * from sysEv
	)
	insert [mig_tracking].[EventParameter] (
		[HashValue]
	,	[GroupHash]
	,   [ParameterXml]
	,   [RunDate]
	)
	select
		A.[ParameterHash]
	,	A.[GroupHash]
	,   A.[ParameterXml]
	,   @runDate
	from allEv as A
	left outer join [mig_tracking].[EventParameter] as B
	on
		B.[HashValue] = A.[ParameterHash]
	where
		B.[HashValue] is null

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- RootItemEvent
	-----------------------------------------------------
	if @entitiesToProcess is null
	begin
		if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID')
			drop index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent]
		if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent')
			drop index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent]
		if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId')
			drop index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent]

		truncate table [mig_tracking].[RootItemEvent]
	end
	else
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItemEvent', @logId output
		delete [mig_tracking].[RootItemEvent]
		from
			[mig_tracking].[RootItemEvent] as A
			inner join @entities as B
			on
				A.[EntityID] = B.[EntityID]
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	-- User events

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (User)', @logId output

	;with
		[events] as (
			select
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Step]
			,	A.[Engine]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[EventID]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
			,	count(*) AS [EventCount]
			from
				[mig_valid].[EventUser] as A
				inner join [mig_item].[ExportKey] as B
				on
					A.[ItemId] = B.[ItemId]
				inner join [mig_item].[Item] as C
				on
					A.[ItemId] = C.[ItemId]
			where
				(@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities))
			group by
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Engine]
			,	A.[Step]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[EventID]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
		)
	insert [mig_tracking].[RootItemEvent] (
		[RowID]
	,	[EntityID]
	,	[ItemId]
	,	[PartitionValue]
	,	[MessageID]
	,	[ParameterHash]
	,	[GroupHash]
	,	[EventCount]
	,	[RunDate])
	select
		newid() -- To detect problem with join
	,	A.[EntityID]
	,	A.[ItemId]
	,	A.[PartitionValue]
	,	B.[MessageID]
	,	A.[ParameterHash]
	,	A.[GroupHash]
	,	A.[EventCount]
	,	@runDate
	from
		[events] as A
		inner join @userMessages as B
		on
			B.[Step] = A.[Step]
		and	B.[Engine] = A.[Engine]
		and	B.[Disposition] = A.[Disposition]
		and	B.[Impact] = A.[Impact]
		and	B.[Receiver] = A.[Receiver]
		and	B.[EventID] = A.[EventID]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-- System events

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (System)', @logId output

	;with
		[events] as (
			select
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Step]
			,	A.[Engine]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Message]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
			,	count(*) AS [EventCount]
			from
				[mig_valid].[EventSystem] as A
				inner join [mig_item].[ExportKey] as B
				on
					A.[ItemId] = B.[ItemId]
				inner join [mig_item].[Item] as C
				on
					A.[ItemId] = C.[ItemId]
			where
				(@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities))
			group by
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Engine]
			,	A.[Step]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Message]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
		)
	insert [mig_tracking].[RootItemEvent] (
		[RowID]
	,	[EntityID]
	,	[ItemId]
	,	[PartitionValue]
	,	[MessageID]
	,	[ParameterHash]
	,	[GroupHash]
	,	[EventCount]
	,	[RunDate])
	select
		newid() -- To detect problem with join
	,	A.[EntityID]
	,	A.[ItemId]
	,	A.[PartitionValue]
	,	B.[MessageID]
	,	A.[ParameterHash]
	,	A.[GroupHash]
	,	A.[EventCount]
	,	@runDate
	from
		[events] as A
		inner join @systemMessages as B
		on
			B.[Step] = A.[Step]
		and	B.[Engine] = A.[Engine]
		and	B.[Disposition] = A.[Disposition]
		and	B.[Impact] = A.[Impact]
		and	B.[Receiver] = A.[Receiver]
		and	B.[MessageFmt] = A.[Message]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID')
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create EntityID index on RootItemEvent', @logId output
		create nonclustered index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent]
		(
			[EntityID] asc
		)
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent')
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent index on RootItemEvent', @logId output
		create nonclustered index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent]
		(
			[MessageID] asc,
			[EntityID] asc,
			[PartitionValue] asc,
			[ItemId] asc
		) include (
			[ParameterHash]
		)
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId')
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent_ItemId index on RootItemEvent', @logId output
		create nonclustered index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent]
		(
			[ItemId] asc
		)
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	-----------------------------------------------------
	-- RootEntityEvent
	-----------------------------------------------------
	if @entitiesToProcess is null
	begin
		truncate table [mig_tracking].[RootEntityEvent];
	end
	else
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootEntityEvent', @logId output
		delete [mig_tracking].[RootEntityEvent]
		from
			[mig_tracking].[RootEntityEvent] as A
			inner join @entities as B
			on
				A.[EntityID] = B.[EntityID]
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootEntityEvent', @logId output

	insert [mig_tracking].[RootEntityEvent] (
		[EntityID]
	,	[PartitionValue]
	,	[MessageID]
	,	[ParameterHash]
	,	[GroupHash]
	,	[EventCount]
	,	[ItemCount]
	,	[RunDate])
	select
		[EntityID]
	,   [PartitionValue]
	,   [MessageID]
	,   [ParameterHash]
	,	[GroupHash]
	,   sum([EventCount])
	,	count(distinct [ItemId])
	,   @runDate
	from
		[mig_tracking].[RootItemEvent]
		where
			(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
	group by
		[EntityID]
	,   [PartitionValue]
	,   [MessageID]
	,   [ParameterHash]
	,	[GroupHash]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- PartitionEventCount
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, update', @logId output

	declare @addedCounts table(
		[MessageID] uniqueidentifier not null
	,   [EntityID] uniqueidentifier not null
	,   [PartitionValue] nvarchar(50) not null
	,   [InstanceCount] int not null
	,   [ItemCount] int not null
	)

	declare @existingCounts table(
		[MessageID] uniqueidentifier not null
	,   [EntityID] uniqueidentifier not null
	,   [PartitionValue] nvarchar(50) not null
	,   [InstanceCount] int not null
	,   [ItemCount] int not null
	)

	;with
		[latest] as (
			select
				[MessageID]
			,	[EntityID]
			,	[PartitionValue]
			,	[InstanceCount]
			,	[ItemCount]
			,	row_number() over (partition by [MessageID], [EntityID], [PartitionValue] order by [Time] desc) as [rn]
			from
				[mig_tracking].[PartitionEventCount]
		)
	insert @existingCounts
	select
		[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	,	[InstanceCount]
	,	[ItemCount]
	from
		[latest]
	where
		[rn] = 1

	;with
		[newCounts] as (
			select
				[MessageID]
			,	[EntityID]
			,   [PartitionValue]
			,   sum([EventCount]) as [InstanceCount]
			,	count(distinct [ItemId]) as [ItemCount]
			from
				[mig_tracking].[RootItemEvent]
			where
				(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
			group by
				[MessageID]
			,	[EntityID]
			,   [PartitionValue]
		)
	insert [mig_tracking].[PartitionEventCount] (
		[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	,	[InstanceCount]
	,	[ItemCount]
	,   [Time]
	)
	output inserted.[MessageID], inserted.[EntityID], inserted.[PartitionValue], inserted.[InstanceCount], inserted.[ItemCount] into @addedCounts
	select
		coalesce(A.[MessageID], B.[MessageID])
	,   coalesce(A.[EntityID], B.[EntityID])
	,   coalesce(A.[PartitionValue], B.[PartitionValue])
	,   isnull(A.[InstanceCount], 0)
	,   isnull(A.[ItemCount], 0)
	,	@runDate
	from
		[newCounts] as A
		full outer join @existingCounts as B
		on
			A.[MessageID] = B.[MessageID]
		and A.[EntityID] = B.[EntityID]
		and A.[PartitionValue] = B.[PartitionValue]
		where
			(@entitiesToProcess is null or coalesce(A.[EntityID], B.[EntityID]) in (select [EntityID] from @entities))
		and (
				isnull(A.[InstanceCount], 0) <> isnull(B.[InstanceCount], 0)
			or	isnull(A.[ItemCount], 0) <> isnull(B.[ItemCount], 0)
		)

	-- Log count changes
	insert [mig_tracking].[Log]
	select
		A.[MessageID]
	,   A.[EntityID]
	,   A.[PartitionValue]
	,   case
			when B.[InstanceCount] is null then 0 -- Added
			else 1 -- Modified
		end
	,   @runDate
	,   @user
	,   'Instance Count (' + A.[PartitionValue] + ')'
	,   format(isnull(B.[InstanceCount], 0), 'N0')
	,   format(A.[InstanceCount], 'N0')
	from
		@addedCounts as A
		left outer join @existingCounts as B
		on
			A.[MessageID] = B.[MessageID]
		and	A.[EntityID] = B.[EntityID]
		and	A.[PartitionValue] = B.[PartitionValue]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- Statistics
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Statistics', @logId output;

	with
		export as (
			select *
			from
				[mig_director].[_Export_Statistics]()
			where
				[PartitionValue] is not null
		)
	,	import as (
			select *
			from [mig_director].[_Import_Statistics]()
			where
				[PartitionValue] is not null
		)
	,	delivery as (
			select *
			from [mig_director].[_Delivery_Statistics]()
			where
				[PartitionValue] is not null
		)
	,	newStatistic as (
			select
				A.[PartitionValue]
			,	A.[EntityID]
			,   A.[Count] - A.[Dropped] as [Available]
			,   A.[Discarded] as [ExpDiscarded]
			,   A.[Exported]
			,   isnull(B.[Discarded], 0) as [ImpDiscarded]
			,   isnull(B.[Imported], 0) as [Imported]
			,   isnull(C.[Discarded], 0) as [DelDiscarded]
			,   isnull(C.[Delivered], 0) as [Delivered]
			,   A.[Difference] + isnull(B.[Difference], 0) + isnull(C.[Difference], 0) as [Check]
			from
				export as A
				left outer join import as B
				on
					A.[PartitionValue] = B.[PartitionValue]
				and	A.[EntityID] = B.[EntityID]
				left outer join delivery as C
				on
					A.[PartitionValue] = C.[PartitionValue]
				and	A.[EntityID] = C.[EntityID]
			where
				(@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities))
		)
	,	curStatistic as (
			select *
			from
				[mig_tracking].[Statistic] as A
			where
				(@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities))
			and	A.[Time] = (
					select max([Time])
					from
						[mig_tracking].[Statistic]
					where
						[PartitionValue] = A.[PartitionValue]
					and	[EntityID] = A.[EntityID]
				)
		)
	insert [mig_tracking].[Statistic] (
		[PartitionValue]
	,	[EntityID]
	,	[Time]
	,	[Available]
	,	[ExpDiscarded]
	,	[Exported]
	,	[ImpDiscarded]
	,	[Imported]
	,	[DelDiscarded]
	,	[Delivered]
	)
	select
		coalesce(A.[PartitionValue], B.[PartitionValue]) as [PartitionValue]
	,	coalesce(A.[EntityID], B.[EntityID]) as [EntityID]
	,	@runDate as [RunDate]
	,   isnull(A.[Available], 0) as [Available]
	,   isnull(A.[ExpDiscarded], 0) as [ExpDiscarded]
	,   isnull(A.[Exported], 0) as [Exported]
	,   isnull(A.[ImpDiscarded], 0) as [ImpDiscarded]
	,   isnull(A.[Imported], 0) as [Imported]
	,   isnull(A.[DelDiscarded], 0) as [DelDiscarded]
	,   isnull(A.[Delivered], 0) as [Delivered]
	from
		newStatistic as A
		full outer join curStatistic as B
		on
			A.[PartitionValue] = B.[PartitionValue]
		and	A.[EntityID] = B.[EntityID]
	where
		A.[EntityID] is null -- Obsolete
	or	B.[EntityID] is null -- New
	or	B.[Available] <> A.[Available]
	or	B.[ExpDiscarded] <> A.[ExpDiscarded]
	or	B.[Exported] <> A.[Exported]
	or	B.[ImpDiscarded] <> A.[ImpDiscarded]
	or	B.[Imported] <> A.[Imported]
	or	B.[DelDiscarded] <> A.[DelDiscarded]
	or	B.[Delivered] <> A.[Delivered]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- Parameters
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Parameters', @logId output

	truncate table [mig_tracking].[Parameter]

	insert [mig_tracking].[Parameter]
	select
		[ItemID]
	,   [ProjectType]
	,   [Name]
	,   [Value]
	,	[Description]
	from
		[mig_director].[RunParameter]
	where
		[Present] = 1
	and [Value] is not null

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	set @text =
'------------------------------------------------------------------
' + convert(varchar, getdate() - @start, 108) + ' Total
------------------------------------------------------------------'

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output

	-- Return the current non-zero partition counts for status updates in the project DB
	;with
		[latest] as (
			select
				[MessageID]
			,	[EntityID]
			,	[PartitionValue]
			,	[InstanceCount]
			,	row_number() over (partition by [MessageID], [EntityID], [PartitionValue] order by [Time] desc) as [rn]
			from
				[mig_tracking].[PartitionEventCount]
		)
	select
		[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	from
		[latest]
	where
		[rn] = 1
	and	[InstanceCount] > 0
end
GO
PRINT N'Dropping obsolete RootItem and RootItemLink tables...';
GO
DROP VIEW IF EXISTS [mig_tracking].[VwRootItem]
DROP VIEW IF EXISTS [mig_tracking].[VwRootItemLink]
GO
PRINT N'Create and populate table [mig_item].[ExportKeyField]...';
GO
if not exists (
	select 1
	from sys.tables t
	join sys.schemas s on s.schema_id = t.schema_id
	where s.name = N'mig_item'
	  and t.name = N'ExportKeyField'
)
begin
	begin transaction;

	set transaction isolation level serializable;

	set xact_abort on;

	create table [mig_item].[ExportKeyField]
	(
		[ItemId]    bigint         not null,
		[KeyName]   nvarchar(256)  not null,
		[KeyValue]  nvarchar(512)  null,
		constraint [PK_ExportKeyField] primary key ([ItemId], [KeyName])
	);
	create index [IX_ExportKeyField_KeyNameValue] on [mig_item].[ExportKeyField]([KeyName], [KeyValue], [ItemId]);
	create index [IX_ExportKeyField_Value] on [mig_item].[ExportKeyField]([KeyValue], [ItemId]);

	-- seed existing keys
	insert [mig_item].[ExportKeyField] (
		[ItemId]
	,	[KeyName]
	,	[KeyValue]
	)
	select
		e.[ItemId]
	,	k.N.value('local-name(.)', 'nvarchar(256)')
	,	k.N.value('(@value)[1]', 'nvarchar(512)')
	from
		[mig_item].[ExportKey] as e
		cross apply (select try_convert(xml, e.[BreakFields]) as keyXml) as x
		cross apply x.keyXml.nodes('/*/*') as k(N)

	commit transaction;

	set transaction isolation level read committed;
end;
GO
PRINT N'Create and populate table [mig_item].[MainKeyField]...';
GO
IF NOT EXISTS (
	SELECT 1
	FROM sys.tables t
	JOIN sys.schemas s ON s.schema_id = t.schema_id
	WHERE s.name = N'mig_item'
	  AND t.name = N'MainKeyField'
)
BEGIN
	BEGIN TRANSACTION;

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

	SET XACT_ABORT ON;

	create table [mig_item].[MainKeyField]
	(
		[KeyId]      bigint         not null,
		[KeyName]    nvarchar(256)  not null,
		[KeyValue]   nvarchar(512)  null,
		constraint [PK_MainKeyField] primary key ([KeyId], [KeyName])
	)
	create index [IX_MainKeyField_KeyNameValue] on [mig_item].[MainKeyField]([KeyName], [KeyValue], [KeyId])
	create index [IX_MainKeyField_Value] on [mig_item].[MainKeyField]([KeyValue], [KeyId])

	-- seed existing keys
	insert [mig_item].[MainKeyField] (
		[KeyId]
	,	[KeyName]
	,	[KeyValue]
	)
	select
		m.[KeyId]
	,	k.N.value('local-name(.)', 'nvarchar(256)')
	,	k.N.value('(@value)[1]', 'nvarchar(512)')
	from
		[mig_item].[MainKey] as m
		cross apply (select cast(m.[KeyValue] as xml) as keyXml) as x
		cross apply x.keyXml.nodes('/*/*') as k(N)
	where
		x.keyXml is not null;

	COMMIT TRANSACTION;

	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END
GO
PRINT N'Naming unnamed default constraint on table [mig_item].[MainKey]...';
GO
declare @mainKeyConstraintname nvarchar(100) = (
    select dc.name
    from sys.default_constraints dc
    join sys.columns c 
        on c.default_object_id = dc.object_id
    join sys.tables t 
        on t.object_id = c.object_id
    join sys.schemas s
        on s.schema_id = t.schema_id
    where s.name = N'mig_item'
        and t.name = N'MainKey'
        and c.name = N'CreationDate'
)

if @mainKeyConstraintname != 'DF_MainKey_CreationDate'
begin
	declare @mainKeyDropContraintSql nvarchar(4000) = N'alter table [mig_item].[MainKey] drop constraint [' + @mainKeyConstraintname + N'];';
	exec sp_executesql @mainKeyDropContraintSql;

	alter table [mig_item].[MainKey] add constraint [DF_MainKey_CreationDate] default (sysdatetime()) for [CreationDate];
end
GO
PRINT N'Optimizing Indexes and Key Value field on [mig_item].[ExportKey]...';
GO
DROP INDEX IF EXISTS [IX_ExportKey_1] ON [mig_item].[ExportKey];
DROP INDEX IF EXISTS [IX_ExportKey_2] ON [mig_item].[ExportKey];
DROP INDEX IF EXISTS [IX_ExportKey_3] ON [mig_item].[ExportKey];
DROP INDEX IF EXISTS [IX_ExportKey_Delete_2] on [mig_item].[ExportKey];

if not exists (
	select 1
	from sys.indexes i
	join sys.tables t
		on t.object_id = i.object_id
	join sys.schemas s
		on s.schema_id = t.schema_id
	where
		s.name = N'mig_item'
	and	t.name = N'ExportKey'
	and	i.name = N'IX_ExportKey_ItemId'
)
begin
	create nonclustered index [IX_ExportKey_ItemId]
	on [mig_item].[ExportKey] ([ItemId])
	include ([EntityID]);
end
go

if not exists (
	select 1
	from sys.indexes i
	join sys.tables t
		on t.object_id = i.object_id
	join sys.schemas s
		on s.schema_id = t.schema_id
	where
		s.name = N'mig_item'
	and	t.name = N'ExportKey'
	and	i.name = N'IX_ExportKey_EntityID'
)
begin
create nonclustered index [IX_ExportKey_EntityID] on [mig_item].[ExportKey] ([EntityID])
include (
	[ExportID]
,	[ItemId]
,	[JobId]
,	[JobInstanceId]
,	[CreationDate]
,	[BreakFields]
);
end

ALTER TABLE [mig_item].[ExportKey] ALTER COLUMN [BreakFields] NVARCHAR(1024) NOT NULL;

if not exists (
	-- any second (or later) PK column already present?
	select 1
	from sys.key_constraints kc
	join sys.tables t
		on t.object_id = kc.parent_object_id
	join sys.schemas s
		on s.schema_id = t.schema_id
	join sys.indexes i
		on i.object_id = kc.parent_object_id
		and i.index_id = kc.unique_index_id
	join sys.index_columns ic
		on ic.object_id = i.object_id
		and ic.index_id = i.index_id
	where
		s.name = N'mig_item'
	and	t.name = N'ExportKey'
	and	kc.name = N'PK_ExportKey'
	and	kc.[type] = 'PK'
	and	ic.key_ordinal >= 2
)
begin
	set xact_abort on;

	begin transaction;

	alter table [mig_item].[ExportKey]
		drop constraint [PK_ExportKey];

	alter table [mig_item].[ExportKey]
		add constraint [PK_ExportKey]
			primary key clustered (
				[ExportID] asc
			,	[EntityID] asc
			);

	commit transaction;
end;
GO
PRINT N'Optimizing Indexes and Key Value field on [mig_item].[MainKey]...';
GO
DROP INDEX IF EXISTS [UX_MainKey_Covering] ON [mig_item].[MainKey];
ALTER TABLE [mig_item].[MainKey] ALTER COLUMN [KeyValue] NVARCHAR(1024) NOT NULL;
GO
PRINT N'Optimizing Indexes on [mig_item].[Item]...';
GO
DROP INDEX IF EXISTS [IX_Item_Cover] ON [mig_item].[Item];
IF NOT EXISTS (
	SELECT 1
	FROM sys.indexes
	WHERE name = N'IX_Item_ActivePartitions'
	  AND object_id = OBJECT_ID(N'[mig_item].[Item]')
)
	BEGIN
		CREATE NONCLUSTERED INDEX [IX_Item_ActivePartitions]
			ON [mig_item].[Item]([PartitionValue] ASC)
			INCLUDE([ItemId], [KeyId])
			WHERE [PartitionValue] <> N'dropped';
	END
GO
PRINT N'Creating function [mig_item].[_KeyFieldMatch]...';
GO
create or alter function [mig_item].[_KeyFieldMatch] (
	@keyType tinyint
,	@keyField nvarchar(256)
,	@keyValue nvarchar(512)
,	@itemId bigint
,	@keyId bigint
)
-- ---------------------------------------------------------------------------------
-- Inline Table Valued Function - important to keep it inline for performance
-- Implements the key field search for Export, Import and Unload jobs in the Runtime
-- ---------------------------------------------------------------------------------
returns table
as
	return
	(
		select cast(1 as bit) as [IsMatch]
		where
			-- Not a key search
			@keyType is null

		or (
				-- Export Key search (Source)
				@keyType = 0
			and	exists (
				select 1
				from 
					[mig_item].[ExportKeyField] as EKF with (nolock)
				where 
					EKF.[ItemId] = @itemId
				and (@keyField is null or EKF.[KeyName] = @keyField)
				and (
					(@keyValue is null and EKF.[KeyValue] is null)
				or	(@keyValue is not null and EKF.[KeyValue] like @keyValue escape '\')
				)
			)
		)

		or (	
				-- Main Key search (Migration)
				@keyType = 1
			and exists (
				select 1
				from 
					[mig_item].[MainKeyField] as MKF with (nolock)
				where 
					MKF.[KeyId] = @keyId
				and (@keyField is null or MKF.[KeyName] = @keyField)
				and (
					(@keyValue is null and MKF.[KeyValue] is null)
				or	(@keyValue is not null and MKF.[KeyValue] like @keyValue escape '\')
				)
			)
		)
	);
GO
PRINT N'Alter Procedure [mig_item].[Export_GetByEntityID]...';
GO
create or alter procedure [mig_item].[Export_GetByEntityID]
	@entityID uniqueidentifier
,	@limit bigint
,	@keyType tinyint = null -- 0: ExportKey, 1: MainKey, NULL: not a key search
,	@keyField nvarchar(256) = null -- NULL: any field
,	@keyValue nvarchar(512) = null -- the key search term. If null, the search will be IS NULL
,	@mode tinyint
,	@jobId int
as
	set nocount on;

	-- Guard against invalid @keyType
	if @keyType is not null and @keyType not in (0, 1)
	begin
		raiserror('Invalid @keyType. Expected 0 (ExportKey), 1 (MainKey) or NULL.', 16, 1);
		return;
	end;

	-- adjust limit by already processed items for this job
	set @limit = @limit - (
		select count(*)
		from [mig_item].[Processed] with (nolock)
		where [JobId] = @jobId
	);

	-- collect items to export into temp table
	select top(@limit)
		A.[ExportID]
	,	A.[EntityID]
	,	A.[ItemId]
	,	A.[BreakFields]
	into #itemsToExport
	from
		[mig_item].[ExportKey] as A with (nolock)

		-- Left join on [mig_item].[Item] to enable export by Migration Key.
		-- Export by Migration Key is only possible after the first export,
		-- because [mig_item].[Item] rows are created during first export in
		-- procedure [mig_item].[Export_Item_Insert].
		left join [mig_item].[Item] as I with (nolock)
		on	
			A.[ItemId] = I.[ItemId]

		left join [mig_item].[ExportItem] as B with (nolock)
		on 
			A.[ItemId] = B.[ItemId]

		left join [mig_item].[Processed] as C with (nolock)
		on 
			A.[ItemId] = C.[ItemId]
		and	C.[JobId] = @jobId

		cross apply [mig_item].[_KeyFieldMatch](
			@keyType
		,	@keyField
		,	@keyValue
		,	A.[ItemId]
		,	I.[KeyId]
		) as K

	where
		A.[EntityID] = @entityID
	and C.[ItemId] is null
	and	K.[IsMatch] = 1
	and (
		(@mode = 0) -- All
	or	(@mode = 1 and B.[Result] = 2) -- Rejects, 2: Reject
	or	(@mode = 2 and B.[Result] is null) -- Difference
	);

	exec [mig_item].[_Export_CountItems] @jobId;
GO
PRINT N'Alter Procedure [mig_item].[Import_GetByEntityID]...';
GO
create or alter procedure [mig_item].[Import_GetByEntityID]
	@entityIDlist varchar(max) = null
,	@keyType tinyint = null -- 0: ExportKey, 1: MainKey, NULL: not a key search
,	@keyField nvarchar(256) = null -- NULL: any field
,	@keyValue nvarchar(512) = null -- the key search term. If null, the search will be IS NULL
,	@mode tinyint
,	@includeDescendants bit
,	@includeAncestors bit
,	@limit bigint
,	@jobId int
as
begin
	set nocount on;

	-- Guard against invalid @keyType
	if @keyType is not null and @keyType not in (0, 1)
	begin
		raiserror('Invalid @keyType. Expected 0 (ExportKey), 1 (MainKey) or NULL.', 16, 1);
		return;
	end;

	declare @entityIDs table(
		[ID] uniqueidentifier primary key ([ID])
	);

	-- Build entity set
	if @entityIDlist is null
	begin
		insert @entityIDs ([ID])
		select [EntityID]
		from [mig_director].[ExportEntity]
		where [IsPresent] = 1;
	end
	else
	begin
		insert @entityIDs ([ID])
		select convert(uniqueidentifier, [Value])
		from [mig_utility].[ParseDelimitedString](@entityIDlist, ',');
	end;

	-- adjust limit by already processed items for this job
	set @limit = @limit - (
		select count(*)
		from [mig_item].[Processed] with (nolock)
		where [JobId] = @jobId
	);

	declare @itemIds [mig_item].[ItemIdList];

	-- collect items to import
	insert @itemIds
	select top(@limit)
		A.[ItemId]
	from
		[mig_valid].[Item] as A with (nolock)

		inner join [mig_item].[ExportItem] as C with (nolock)
		on  
			A.[ItemId] = C.[ItemId]
		and	C.[Result] = 0 -- 0: Exported

		inner join [mig_item].[ExportKey]  as D with (nolock)
		on 
			A.[ItemId] = D.[ItemId]

		inner join @entityIDs as E
		on  
			D.[EntityID] = E.[ID]

		left join [mig_valid].[ImportItem] as F with (nolock)
		on	
			A.[ItemId] = F.[ItemId]

		cross apply [mig_item].[_KeyFieldMatch](
			@keyType
		,	@keyField
		,	@keyValue
		,	A.[ItemId]
		,	A.[KeyId]
		) as K

	where
		K.[IsMatch] = 1
	and (
		(@mode = 0)                        -- All 
	or	(@mode = 1 and F.[Result] < 2)     -- Rejects, 2: Success
	or	(@mode = 2 and F.[Result] is null) -- Difference
	);

	-- Calculate levels for the resulting item set. Also filters out processed on restart
	exec [mig_item].[_Import_CalculateLevels]
		@jobId
	,	@itemIds
	,	@includeDescendants
	,	@includeAncestors
end;
GO
PRINT N'Alter Procedure [mig_item].[Export_Unload]...';
GO
CREATE OR ALTER procedure [mig_item].[Export_Unload]
	@jobId int
,	@partitionCount int = 1
,	@partitionIndex int = null
,	@entityIDlist varchar(max) = null
,	@keyType tinyint = null -- 0: ExportKey, 1: MainKey, NULL: not a key search
,	@keyField nvarchar(256) = null -- NULL: any field
,	@keyValue nvarchar(512) = null -- the key search term. If null, the search will be IS NULL
as
	-- Guard against invalid @keyType
	if @keyType is not null and @keyType not in (0, 1)
	begin
		raiserror('Invalid @keyType. Expected 0 (ExportKey), 1 (MainKey) or NULL.', 16, 1);
		return;
	end;

	declare @entityIDs table([ID] uniqueidentifier, primary key ([ID]))

	if @entityIDlist is null
	begin
		insert @entityIDs select [EntityID] from [mig_director].[ExportEntity] where [IsPresent] = 1
	end
	else
	begin
		insert @entityIDs select [ID] from [mig_utility].[ParseGuids](@entityIDlist, ',')
	end;
	
	with 
		valid as (
			select 
				A.*
			from  
				[mig_item].[ExportXml] as A with (NoLock)
				inner join [mig_valid].[Item] as B with (NoLock)
				on 
					A.[ItemId] = B.[ItemId]

				cross apply [mig_item].[_KeyFieldMatch](
					@keyType
				,	@keyField
				,	@keyValue
				,	A.[ItemId]
				,	B.[KeyId]
				) as K
			where
				A.[Deleted] = 0
			and K.[IsMatch] = 1
		)

	select 
		B.[EntityID]
	,	A.[ItemId]
	,	A.[XmlCompressed]
	from
		valid as A with (NoLock)
		
		inner join [mig_item].[ExportKey] as B with (NoLock)
		on  
			A.[ItemId] = B.[ItemId] 

		inner join [mig_item].[ExportItem] as C with (NoLock)
		on  
			C.[ItemId] = A.[ItemId] 
		and	C.[Result] = 0 -- 0: Exported

		inner join [mig_item].[Active] as D with (NoLock)
		on  
			D.[ItemId] = C.[ItemId]		
		and D.[JobId] = C.[JobId] 
		and D.[JobInstanceId] = C.[JobInstanceId]
		and D.[Step] = 0 -- 0: Export

		inner join @entityIDs as E
		on
			B.[EntityID] = E.[ID]

		left outer join [mig_item].[Processed] as F -- No NoLock on the Processed table
		on
			F.[JobId] = @jobId
		and	F.[ItemId] = A.[ItemId]
	where
		F.[ItemId] is null
	and	(@partitionIndex is null or A.[ItemId] % @partitionCount = @partitionIndex)
GO
PRINT N'Alter Procedure [mig_item].[Import_Unload]...';
GO
CREATE OR ALTER procedure [mig_item].[Import_Unload]
	@jobId int
,	@partitionCount int = 1
,	@partitionIndex int = null
,	@entityIDlist varchar(max) = null
,	@keyType tinyint = null -- 0: ExportKey, 1: MainKey, NULL: not a key search
,	@keyField nvarchar(256) = null -- NULL: any field
,	@keyValue nvarchar(512) = null -- the key search term. If null, the search will be IS NULL
as
	-- Guard against invalid @keyType
	if @keyType is not null and @keyType not in (0, 1)
	begin
		raiserror('Invalid @keyType. Expected 0 (ExportKey), 1 (MainKey) or NULL.', 16, 1);
		return;
	end;

	declare @entityIDs table([ID] uniqueidentifier, primary key ([ID]))

	if @entityIDlist is null
	begin
		insert @entityIDs select [EntityID] from [mig_director].[ExportEntity] where [IsPresent] = 1
	end
	else
	begin
		insert @entityIDs select [ID] from [mig_utility].[ParseGuids](@entityIDlist, ',')
	end;
	
	with 
		valid as (
			select
				A.*
			from  
				[mig_item].[TargetXml] as A with (NoLock)
				inner join [mig_valid].[Item] as B with (NoLock)
				on 
					A.[ItemId] = B.[ItemId]

				cross apply [mig_item].[_KeyFieldMatch](
					@keyType
				,	@keyField
				,	@keyValue
				,	A.[ItemId]
				,	B.[KeyId]
				) as K
			where
				A.[Deleted] = 0
			and K.[IsMatch] = 1
		)

	select 
		B.[EntityID]
	,	A.[ItemId]
	,	A.[XmlCompressed]
	from
		valid as A with (NoLock)
		
		inner join [mig_item].[ExportKey] as B with (NoLock)
		on  
			A.[ItemId] = B.[ItemId] 

		inner join [mig_item].[ImportItem] as C with (NoLock)
		on  
			C.[ItemId] = B.[ItemId] 
		and C.[Result] = 2 -- 3: Success

		inner join [mig_item].[Active] as D with (NoLock)
		on  
			D.[ItemId] = C.[ItemId]		
		and D.[JobId] = C.[JobId] 
		and D.[JobInstanceId] = C.[JobInstanceId]
		and D.[Step] = 1 -- 1: Import

		inner join [mig_item].[Item] as E with (NoLock)
		on
			B.[ItemId] = E.[ItemId]
				
		inner join @entityIDs as F
		on
			B.[EntityID] = F.[ID]

		left outer join [mig_item].[Processed] as G -- No NoLock on the Processed table
		on
			G.[JobId] = @jobId
		and	G.[ItemId] = A.[ItemId]
	where
		G.[ItemId] is null -- Restart: Not already processed by this job
	and	(@partitionIndex is null or A.[ItemId] % @partitionCount = @partitionIndex)
GO
PRINT N'Alter Procedure [mig_item].[Import_Unload_DependenciesByEntityID]...';
GO
CREATE OR ALTER procedure [mig_item].[Import_Unload_DependenciesByEntityID]
	@limit bigint
,	@jobId int
,	@list varchar(max) -- Comma-separated Guids
,	@includeDescendants bit
,	@includeAncestors bit
,	@keyType tinyint = null -- 0: ExportKey, 1: MainKey, NULL: not a key search
,	@keyField nvarchar(256) = null -- NULL: any field
,	@keyValue nvarchar(512) = null -- the key search term. If null, the search will be IS NULL
as
	-- Guard against invalid @keyType
	if @keyType is not null and @keyType not in (0, 1)
	begin
		raiserror('Invalid @keyType. Expected 0 (ExportKey), 1 (MainKey) or NULL.', 16, 1);
		return;
	end;

	declare @entityIDs table([ID] uniqueidentifier, primary key ([ID]))

	insert @entityIDs select [ID] from [mig_utility].[ParseGuids](@list, ',')
	
	declare @itemIds [mig_item].[ItemIdList];

	with valid as (
		select 
			A.[ItemId]
		from
			[mig_item].[ExportKey] as A with (NoLock)

			inner join [mig_item].[ImportItem] as B with (NoLock)
			on  
				B.[ItemId] = A.[ItemId] 
			and B.[Result] = 2 -- 3: Success

			inner join [mig_item].[Active] as C with (NoLock)
			on  
				C.[ItemId] = B.[ItemId] 
			and C.[JobId] = B.[JobId] 
			and C.[JobInstanceId] = B.[JobInstanceId]
			and C.[Step] = 1 -- 1: Import

			inner join [mig_item].[Item] as D with (NoLock)
			on
				A.[ItemId] = D.[ItemId]

			inner join @entityIDs as E
			on
				A.[EntityID] = E.[ID]

			cross apply [mig_item].[_KeyFieldMatch](
				@keyType
			,	@keyField
			,	@keyValue
			,	A.[ItemId]
			,	D.[KeyId]
			) as K
		where
			K.[IsMatch] = 1
	)
	insert @itemIds
	select top(@limit)
		A.[ItemId]
	from
		valid as A with (NoLock)

		left outer join [mig_item].[Processed] as B -- No NoLock on the Processed table
		on
			B.[JobId] = @jobId
		and	B.[ItemId] = A.[ItemId]
	where
		B.[ItemId] is null -- Restart: Not already processed by this job

	select
		B.[EntityID]
	,	C.[ItemId]  
	,	C.[XmlCompressed] 
	from 
		[mig_item].[_Import_IncludeDependencies](@itemIds, @includeDescendants, @includeAncestors) as A 

		inner join [mig_item].[ExportKey] as B with (NoLock)
		on
			A.[ItemId] = B.[ItemId]

		inner join [mig_item].[TargetXml] as C with (NoLock)
		on 
			A.[ItemId] = C.[ItemId]
GO
PRINT N'Alter Procedure [mig_item].[Import_Unload_DependenciesByEntityID]...';
GO
CREATE OR ALTER procedure [mig_item].[Import_Unload_DependenciesByItemSet] (
	@limit bigint
,	@jobId int
,	@list varchar(max) -- Comma-separated list of set ids
,	@includeDescendants bit
,	@includeAncestors bit
)
as
	declare @setIds table([SetId] bigint, primary key ([SetId]))
	insert @setIds select convert(bigint, [Value]) from [mig_utility].[ParseDelimitedString](@list, ',')

	declare @itemIds [mig_item].[ItemIdList];

	with valid as (
		select 
			B.[ItemId]
		from
			[mig_item].[ItemSet] as A with (NoLock)

			inner join [mig_item].[ExportKey] as B with (NoLock)
			on
				A.[ItemId] = B.[ItemId]

			inner join [mig_item].[ImportItem] as C with (NoLock)
			on  
				C.[ItemId] = A.[ItemId] 
			and C.[Result] = 2 -- 3: Success

			inner join [mig_item].[Active] as D with (NoLock)
			on  
				D.[ItemId] = C.[ItemId] 
			and D.[JobId] = C.[JobId] 
			and D.[JobInstanceId] = C.[JobInstanceId]
			and D.[Step] = 1 -- 1: Import

			inner join [mig_item].[Item] as E with (NoLock)
			on
				A.[ItemId] = E.[ItemId]

			inner join @setIds as F
			on
				A.[SetId] = F.[SetId]
	)
	insert @itemIds
	select top (@limit)
		A.[ItemId]
	from
		valid as A with (NoLock)

		left outer join [mig_item].[Processed] as B -- No NoLock on the Processed table
		on
			B.[JobId] = @jobId
		and	B.[ItemId] = A.[ItemId]
	where
		B.[ItemId] is null -- Restart: Not already processed by this job

	select
		B.[EntityID]
	,	C.[ItemId]  
	,	C.[XmlCompressed] 
	from 
		[mig_item].[_Import_IncludeDependencies](@itemIds, @includeDescendants, @includeAncestors) as A 

		inner join [mig_item].[ExportKey] as B with (NoLock)
		on
			A.[ItemId] = B.[ItemId]

		inner join [mig_item].[TargetXml] as C with (NoLock)
		on 
			A.[ItemId] = C.[ItemId]
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Optimize Entity Synchronize'
GO
PRINT N'Alter procedure [mig_item].[_Export_EntitySynchronizeItems]...' 
GO
CREATE OR ALTER procedure [mig_item].[_Export_EntitySynchronizeItems]
	@jobId int
,	@jobInstanceId int
,	@entityID uniqueidentifier
,	@newCount int output
,	@obsoleteCount int output
as
	set nocount on

	-- make sure the expected #sync temp table exists
	if object_id('tempdb..#sync') is null
	begin
		raiserror('#sync temp table not found. Call [mig_item].[Export_EntitySynchronizeItems] first.', 16, 1)
		return
	end

	declare @time datetime2(7) = sysdatetime()

	--------------------------------------------------------------------------------------------------------------
	-- Process New items
	--------------------------------------------------------------------------------------------------------------
	drop table if exists #newExport
	create table #newExport (
		[ExportID] uniqueidentifier not null primary key
	,   [BreakFields] nvarchar(4000) not null
	)

	-- Determine new items
	insert #newExport
	select
		A.[ExportID]
	,	A.[BreakFields]
	from	
		#sync as A
		left outer join [mig_item].[ExportKey] as B
		on 
			B.[EntityID] = @entityID
		and	B.[ExportID] = A.[ExportID] 
		where 
			B.[ExportID] is null 
				
	-- Generate ItemIds
	drop table if exists #mapExportIDToItemId
	create table #mapExportIDToItemId (	
		[ExportID] uniqueidentifier not null primary key
	,   [ItemId] bigint not null
	)

	-- Using MERGE to be able to reference src.[ExportID] in the OUTPUT clause
	merge [mig_item].[Id] with (rowlock) as tgt
	using #newExport as src
	on 1 = 0 -- force insert for every source row
	when not matched by target then
		insert (
			[CreationDate]
		) values (
			@time
		)
		output
			src.[ExportID]
		,	inserted.[ItemId]
		into #mapExportIDToItemId (
			[ExportID]
		,	[ItemId]
		);

	-- Match ExportIDs and generated ItemIds and insert on ExportKey
	insert [mig_item].[ExportKey] with (RowLock) (
		[ExportID]
	,	[ItemId]
	,	[EntityID]
	,	[BreakFields]
	,	[JobId]
	,	[JobInstanceId]
	)
	select
		A.[ExportID]
	,	B.[ItemId]
	,	@entityID
	,	A.[BreakFields]
	,	@jobId
	,	@jobInstanceId
	from
		#newExport as A
		inner join #mapExportIDToItemId as B 
		on 
			A.[ExportID] = B.[ExportID]

	-- Return the number of new items
	set @newCount = @@rowcount

	-- Normalize key fields for faster searches
	insert [mig_item].[ExportKeyField] (
		[ItemId]
	,	[KeyName]
	,	[KeyValue]
	)
	select
		m.[ItemId]
	,	k.N.value('local-name(.)', 'nvarchar(256)')
	,	k.N.value('(@value)[1]', 'nvarchar(512)')
	from
		#newExport as e
		inner join #mapExportIDToItemId as m
		on 
			e.[ExportID] = m.[ExportID]
		cross apply (select cast(e.[BreakFields] as xml) as keyXml) as x
		cross apply x.keyXml.nodes('/*/*') as k(N);

	--------------------------------------------------------------------------------------------------------------
	-- Process Obsolete items
	--------------------------------------------------------------------------------------------------------------
	declare @obsoleteItems [mig_garbage].[InvalidateSet]

	delete A
	output deleted.[ItemId] into @obsoleteItems([ItemId])
	from	
		[mig_item].[ExportKey] as A with (RowLock)
		left outer join #sync as B
		on 
			A.[ExportID] = B.[ExportID]
	where 
		A.[EntityID] = @entityID
	and	B.[ExportID] is null

	-- Return the number of obsolete items
	set @obsoleteCount = @@rowcount

	-- Delete normalized key fields for obsolete items
	delete A
	from 
		[mig_item].[ExportKeyField] as A with (RowLock)
		inner join @obsoleteItems as B
		on
			A.[ItemId] = B.[ItemId]

	-- Mark as garbage
	exec [mig_garbage].[_SetItemSetObsolete] @obsoleteItems
GO
PRINT N'Alter procedure [mig_garbage].[Tables_List]...' 
GO
CREATE OR ALTER procedure [mig_garbage].[Tables_List]
as
	select 
		A.name 
	from 
		sys.tables as A
	where 
		schema_name(A.schema_id) = 'mig_item'
		and A.name not in (
			'ExportKey' -- Handled by synchronize
		,	'ExportKeyField' -- Handled by synchronize
		,	'History' -- Handled by item history
		) 
		and exists (
			select 1 
			from 
				sys.columns as B 
			where 
					A.object_id = B.object_id 
			and B.name = 'JobId'
		) 
	union select 'Id'
GO
PRINT N'Alter procedure [mig_item].[Export_EntitySynchronizeItems]...' 
GO
CREATE OR ALTER procedure [mig_item].[Export_EntitySynchronizeItems]
	@jobId int
,	@jobInstanceId int
,	@entityID	uniqueidentifier
,	@exportDb varchar(50)
,	@totalCount int output
,	@newCount int output
,	@obsoleteCount int output
as
	set xact_abort on;

	----------------------------------------------------------------------------------------------------------------
	-- Dynamically call the Synchronize procedure in the Export DB and retrieve the sync results in temp table #sync
	----------------------------------------------------------------------------------------------------------------
	declare @sql nvarchar(max) = N'exec ' + @exportDb + N'.[mig_director].[EntityKeyList] @entityId;'

	-- Execute the call
	drop table if exists #sync;
	create table #sync (
		[ExportID] uniqueidentifier primary key
	,	[BreakFields] nvarchar(1024) not null	
	);
	insert into #sync (
		[ExportID]
	,   [BreakFields]
	)
	exec sp_executesql
		@sql
	,   N'@entityId uniqueidentifier'
	,   @entityId = @entityID

	-- Return the total count
	set @totalCount = @@rowcount

	declare @now datetime2(7) = sysdatetime()

	begin try
		begin transaction

			-- Do the synchronize, the #sync table is expected by the called procedure
		exec [mig_item].[_Export_EntitySynchronizeItems] @jobId, @jobInstanceId, @entityID, @newCount output, @obsoleteCount output

		-- Update the ExportEntity LastSynchronizeTime
		update [mig_director].[ExportEntity]
		set
			[LastSynchronizeTime] = @now
		where
			[EntityId] = @entityID

		commit transaction;
	end try
	begin catch
		if @@trancount > 0 rollback transaction;

		declare @errMessage nvarchar(4000) = error_message();
		declare @errSeverity int = error_severity();
		declare @errState int = error_state();

		raiserror(@errMessage, @errSeverity, @errState);
	end catch;
GO
PRINT N'Alter procedure [mig_item].[Export_EntityClearAllItems]...' 
GO
CREATE OR ALTER procedure [mig_item].[Export_EntityClearAllItems]
	@jobId int
,	@jobInstanceId int
,	@entityID uniqueidentifier
as
	-- Empty #synch table causes cleanup of all items for EntityID
	drop table if exists #sync;
	create table #sync (
		[ExportID] uniqueidentifier primary key
	,	[BreakFields] nvarchar(4000) not null
	);

	declare @newCount int = 0 
	declare @obsoleteCount int = 0

	exec [mig_item].[_Export_EntitySynchronizeItems] @jobId, @jobInstanceId, @entityID, @newCount output, @obsoleteCount output

	select @obsoleteCount
GO
PRINT N'Alter procedure [mig_item].[Export_Item_Insert]...' 
GO
create or alter procedure [mig_item].[Export_Item_Insert]
	@itemId bigint
,	@correlationID uniqueidentifier
,	@jobId int
,	@jobInstanceId int
,	@exportID uniqueidentifier
,	@result tinyint
,	@businessEntityName varchar(50)
,	@partitionValue nvarchar(50)
,	@keyValue nvarchar(1024)
,	@sourceKey nvarchar(1024)
,	@maxDeadlockRetries int
,	@savingVersions bit
,	@commit bigint
,	@cleanWorkspace bit
,	@duplicate bit output
as
	declare @step tinyint = 0 -- Export
	
	-- Mark previous export for garbage collection
	exec [mig_garbage].[_InvalidateItem] @itemId, @step, @jobId, @jobInstanceId
	
	-- Get KeyId
	declare @keyId bigint
	
	exec [mig_item].[_GetKeyId] @businessEntityName, @keyValue, @maxDeadlockRetries, @keyId output
	
	-- Check for duplicate key
	select
		@duplicate = 1
	from 
		[mig_valid].[Item]
	where
		[KeyId] = @keyId
	and [ItemId] <> @itemId -- Avoid finding yourself !
		
	if 1 = @duplicate
	begin
		-- Insert ExportID in @keyValue and get a new KeyId
		declare @idTxt varchar(50) = lower(convert(varchar(50), @exportID))
		declare @keyXml xml = convert(xml, @keyValue)
		set @keyXml.modify('insert <ExportID value="{sql:variable("@idTxt")}" /> as last into (/*)[1]')  		
		set @keyValue = convert(nvarchar(512), @keyXml)
		exec [mig_item].[_GetKeyId] @businessEntityName, @keyValue, @maxDeadlockRetries, @keyId output
		-- Override @result
		set @result = 2 -- Discarded
	end
			
	-------------------------------------------
	-- Insert or update Item for this iteration	
	-------------------------------------------
	merge into [mig_item].[Item] as tgt
	using (select @itemId as [ItemId]) as src
	on (tgt.[ItemId] = src.[ItemId])
	when matched then
		update set
			tgt.[JobId] = @jobId
		,	tgt.[JobInstanceId] = @jobInstanceId
		,	tgt.[KeyId] = @keyId
		,	tgt.[PartitionValue] = @partitionValue
	when not matched then
		insert (
			[ItemId]
		,	[JobId]
		,	[JobInstanceId]
		,	[KeyId]
		,	[PartitionValue]
		) values (
			@itemId
		,   @jobId
		,	@jobInstanceId
		,	@keyId
		,	@partitionValue
		);
		
	-------------------------------------------------
	-- Insert or update ExportItem for this iteration	
	-------------------------------------------------
	merge into [mig_item].[ExportItem] as tgt
	using (select @itemId as [ItemId]) as src
	on (tgt.[ItemId] = src.[ItemId])
	when matched then
		update set
			tgt.[JobId] = @jobId
		,	tgt.[JobInstanceId] = @jobInstanceId
		,	tgt.[Result] = @result
		,	tgt.[SourceKey] = @sourceKey
		,	tgt.[Commit] = @commit
		,	tgt.[CleanWorkspace] = @cleanWorkspace
	when not matched then
		insert (
			[ItemId]
		,	[JobId]
		,	[JobInstanceId]
		,	[Result]
		,	[SourceKey]
		,	[Commit]
		,	[CleanWorkspace]
		) values (
			@itemId
		,	@jobId
		,	@jobInstanceId
		,	@result
		,	@sourceKey
		,	@commit
		,	@cleanWorkspace
		);

	-------------------------------------------------
	-- Preserve history	
	-------------------------------------------------
	if 1 = @savingVersions
	begin
		insert [mig_item].[History] (
			[ItemId]
		,	[CorrelationID]
		,	[JobId]
		,	[Step]
		,	[PartitionValue]
		,	[KeyId] 		
		,	[Discarded]
		,	[Commit]
		,	[CleanWorkspace]
		,	[CreationDate]
		) values (
			@itemId
		,	@correlationID
		,	@jobId
		,	0 -- Export
		,	@partitionValue
		,	@keyId
		,	case @result
				when 0 then 0 -- Exported 
				else 1 -- Dropped or Discarded
			end
		,	@commit
		,	@cleanWorkspace
		,	sysdatetime()
		)
	end
GO
PRINT N'Alter procedure [mig_item].[_GetKeyId]...' 
GO
CREATE OR ALTER procedure [mig_item].[_GetKeyId]
	@businessEntityName varchar(50)
,	@keyValue nvarchar(1024)
,	@maxDeadlockRetries int
,	@keyId bigint output
as
	set nocount on

	declare @retryCount int = 0
	declare @startTranCount int = @@trancount;
	declare @localTransaction bit = 0;

	lblRetry:
	select	
		@keyId = [KeyId]
	from
		[mig_item].[MainKey]
	where
		[BusinessEntityName] = @businessEntityName
	and	[KeyValue] = @keyValue

	if @keyId is not null
		return

	-- Guarded insert: may race with other sessions inserting the same key
	begin try
		-- Try insert first

		-- Only manage a transaction if there wasn't one already
		if 0 = @startTranCount
		begin
			begin transaction;
			set @localTransaction = 1;
		end

		insert [mig_item].[MainKey] (
			[BusinessEntityName] 
		,	[KeyValue]
		) 
		values (
			@businessEntityName 
		,	@keyValue 
		)
		
		set @keyId = scope_identity()

		-- Normalize key fields for faster searches
		insert [mig_item].[MainKeyField] (
			[KeyId]
		,	[KeyName]
		,	[KeyValue]
		)
		select
			@keyId
		,	k.N.value('local-name(.)', 'nvarchar(256)')
		,	k.N.value('(@value)[1]', 'nvarchar(512)')
		from
			(select cast(@keyValue as xml) as keyXml) as x
			cross apply x.keyXml.nodes('/*/*') as k(N);

		-- If we started a local transaction, commit it
		if @localTransaction = 1 and @@trancount > 0
			commit transaction;

		return
	end try
	begin catch
		-- Insert failed

		-- If we started a local transaction, roll it back
		if @localTransaction = 1 and @@trancount > 0
			rollback transaction;

		declare @err int = error_number()

		-- 2601 Duplicate in unique index for BusinessEntityName and KeyValue
		-- 2627 Unique constraint violation
		if @err in (2601, 2627)
		begin
			-- Another session inserted the same key between our SELECT and INSERT
			-- Go back and do the select again
			goto lblRetry
		end
		-- 1205: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
		-- 1222: Lock request time out period exceeded.
		else if @err in (1205, 1222)
		begin
			if @retryCount < @maxDeadlockRetries 
			begin
				declare @delay time = case @retryCount
					when 0 then '00:00:00.000'
					when 1 then '00:00:00.005'
					when 2 then '00:00:00.010'
					else '00:00:00.020'
				end

				waitfor delay @delay

				set @retryCount = @retryCount + 1

				goto lblRetry
			end
		end

		-- Any other error or out of retries: Rethrow
		declare @errMessage nvarchar(4000) = error_message()
		declare @errSeverity int = error_severity()
		declare @errState int = error_state()

		raiserror(@errMessage, @errSeverity, @errState)
	end catch
GO
PRINT N'Dropping table type [mig_item].[ExportItemList]...' 
GO
DROP TYPE IF EXISTS [mig_item].[ExportItemList]
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Optimize Clear History'
GO
PRINT N'Restructure index on History table to assist item list queries...';
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE name = N'IX_History_Latest'
      AND object_id = OBJECT_ID(N'[mig_item].[History]')
)
BEGIN
    CREATE INDEX [IX_History_Latest] ON [mig_item].[History] ([ItemId] ASC, [Id] DESC) INCLUDE ([Discarded], [Step]);
END
-- [IX_History_Latest] replaces [IX_History_ItemId]
DROP INDEX IF EXISTS [IX_History_ItemId] ON [mig_item].[History]
GO
PRINT N'Drop procedure [mig_director].[ClearItemHistory]...';
GO
DROP PROCEDURE IF EXISTS [mig_director].[ClearItemHistory]
GO
PRINT N'Create table valued function [mig_item].[_History_DeleteSet]...';
GO
CREATE OR ALTER function [mig_item].[_History_DeleteSet](
	@cutOff datetime2 
)
returns table
as
	-- Returns all the history rows to be deleted for a given cutoff. Used both to first count the total rows 
	-- to be deleted and then to execute the deletion in a batched approach

	-- Ensures that history always retains at least one export per item and does not leave orphan imports without their 
	-- preceding exports

	return with
		-- All rows newer than cutoff are kept
		newerThanCutOff as (
			select *
			from
				[mig_item].[History]
			where
				[CreationDate] > @cutOff
		)
		-- When the oldest row for an item is an import, the previous export row from before cutoff must be peeked and kept
	,	orphanImport as (
			select *
			from
				newerThanCutOff as A
			where
				A.[Step] = 1
			and	A.[Id] = (
				select min([Id]) -- Oldest
				from
					newerThanCutOff as B
				where
					A.[ItemId] = B.[ItemId]	
			)
		)
		-- When the oldest row for an item is feedback, the previous import row from before cutoff must be peeked and kept
	,	orphanFeedback as (
			select *
			from
				newerThanCutOff as A
			where
				A.[Step] = 2
			and	A.[Id] = (
				select min([Id]) -- Oldest
				from
					newerThanCutOff as B
				where
					A.[ItemId] = B.[ItemId]	
			)
		)
		-- Peek and keep latest import rows from before cutoff for the orphaned feedback rows above
	,	olderImportToKeep as (
			select A.*
			from
				[mig_item].[History] as A
				inner join orphanFeedback as B
				on
					A.[ItemId] = B.[ItemId]
			where
				A.[Step] = 1
			and	A.[Id] = (
				select max([Id]) -- Newest
				from
					[mig_item].[History] as C
				where
					A.[ItemId] = C.[ItemId]	
				and	A.[Step] = C.[Step]
				and	C.[CreationDate] <= @cutOff -- Before cutoff
			)
		)
		-- Peek and also keep latest export rows from before cutoff for:
		-- * orphaned import rows
		-- * imports kept for orphaned feedback rows
	,	olderExportToKeep as (
			select A.*
			from
				[mig_item].[History] as A
				inner join (
					select ItemId
					from (
						select ItemId from orphanImport
						-- Use UNION (not UNION ALL) to ensure each ItemId is unique across orphanImport and olderImportToKeep.
						union
						select ItemId from olderImportToKeep
					) as X
				) as B
				on
					A.[ItemId] = B.[ItemId]
			where
				A.[Step] = 0
			and	A.[Id] = (
				select max([Id]) -- Newest
				from
					[mig_item].[History] as C
				where
					A.[ItemId] = C.[ItemId]	
				and	A.[Step] = C.[Step]
				and	C.[CreationDate] <= @cutOff -- Before cutoff
			)
		)
		-- All rows to keep
	,	toKeep as (
			select * from newerThanCutOff
			union all
			select * from olderImportToKeep
			union all
			select * from olderExportToKeep
		)

	-- All other rows are to be deleted
	select
		A.[Id]
	,	A.[ItemId]
	,	A.[Step]
	,	A.[CorrelationID]
	,	A.[CreationDate]
	from
		[mig_item].[History] as A
	where
		not exists (
			select B.[Id] 
			from 
				toKeep as B 
			where 
				A.[Id] = B.[Id]
		)
GO
PRINT N'Create procedure [mig_item].[History_ClearBatch]...';
GO
CREATE OR ALTER PROCEDURE [mig_item].[History_ClearBatch]
	@cutOff datetime2 
,	@batchSize int = 100000
,	@deleted int output
AS
	-- -----------------------------------------------------------------------------------------------------------------------------
	-- Deletes up to @batchSize oldest rows in [mig_item].[History] older than @cutOff.
	--
	-- Preserves export/import sequencing via [_History_DeleteSet] (keeps the preceding export for any kept import).
	--
	-- Cascades deletions to related XmlVersion and DependencyHistory tables; reassigns the newest dependency
	-- to the oldest remaining export CorrelationID per item. 
	--
	-- Returns number of deleted rows in @deleted for the caller to iteratively call until all rows older than @cutOff are deleted.
	-- -----------------------------------------------------------------------------------------------------------------------------

	set nocount on;
	set xact_abort on;

	if 0 >= @batchSize
	begin
		declare @msg nvarchar(2048) = N'Invalid batch size: ' + cast(@batchSize as nvarchar(20))+ N' (batch size must be greater than zero).'
		raiserror(@msg, 16, 1)
		return
	end

	-- Capture deleted rows
	drop table if exists #toDelete
	create table #toDelete(
		[Id] bigint not null
	,	[ItemId] bigint not null
	,	[Step] tinyint not null
	,	[CorrelationID] uniqueidentifier not null
	,	constraint PK_toDelete primary key clustered ([Id])
	);

	-- fill the batch (oldest-first)
	insert #toDelete ( 
		[Id]
	,	[ItemId]
	,	[Step]
	,	[CorrelationID] 
	)
	select top(@batchSize)
		[Id]
	,	[ItemId]
	,	[Step]
	,	[CorrelationID]
	from
		[mig_item].[_History_DeleteSet](@cutOff)
	order by
		[Id] asc -- [Id] is an IDENTITY column. Ordering by it gives oldest first
	option (recompile); -- To recompile for different @batchSizes

	-- Set output parameter
	set @deleted = @@rowCount

	if 0 = @deleted
	begin
		-- No rows to be deleted, we're done
		return
	end
	
	-- minimal, high-payoff indexes for the joins later
	create nonclustered index IX_toDelete_step_corr on #toDelete ([step], [correlationid]);
	create nonclustered index IX_toDelete_item on #toDelete ([itemid]);

	begin transaction

	-- Export related tables
	delete A 
	from
		[mig_item].[SourceXmlVersion] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 0 -- Export	

	delete A 
	from
		[mig_item].[ExportXmlVersion] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 0 -- Export	

	delete A 
	from
		[mig_item].[ExportEventXmlVersion] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 0 -- Export	

	-- Import related tables
	delete A 
	from
		[mig_item].[TargetXmlVersion] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 1 -- Import	

	delete A 
	from
		[mig_item].[ImportEventXmlVersion] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 1 -- Import	


	delete A
	from
		[mig_item].[FeedbackXmlVersion] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 1 -- Import	

	---------------------------------------------------------------------------------------------------------
	-- DependencyHistory tables
	drop table if exists #updatedDep;
	create table #updatedDep(
		[OldCorrelationID] uniqueidentifier not null
	,	[NewCorrelationID] uniqueidentifier not null
	,	constraint PK_updatedDep primary key clustered ([OldCorrelationID])
	);

	-- Keep the newest DependencyHistory about to be deleted by moving it to the oldest export's correlation id 
	-- that is being kept

	-- First DependencyHistory
	;with 
		remainingExp as (
			select
				A.[ItemId]
			,	A.[Id]
			,	A.[CorrelationID]
			from
				[mig_item].[History] as A
			where
				A.[Step] = 0 -- Export
			and	not exists (
					select 1
					from #toDelete as B
					where B.[Id] = A.[Id]
				)
		)
	,	oldestExp as (
			select
				A.[ItemId]
			,	A.[CorrelationID]
			from
				remainingExp as A
			where
				[Id] = (
					select min([Id])
					from
						remainingExp as B
					where
						A.[ItemId] = B.[ItemId]
				)
		)
	,	obsoleteDep as (
			select A.*
			from
				[mig_item].[DependencyHistory] as A
				inner join #toDelete as B
				on
					A.[ItemId] = B.[ItemId]
				and	A.[CorrelationID] = B.[CorrelationID]
				and	B.[Step] = 0 -- Export	
		)
	update depHst
	set
		[CorrelationID] = B.[CorrelationID]
	,	[CorrelationIDLast] = B.[CorrelationID]
	output
		deleted.[CorrelationID]
	,	inserted.[CorrelationID]
	into #updatedDep
	from 
		obsoleteDep as A
		inner join oldestExp as B
		on
			A.[ItemId] = B.[ItemId]
		inner join [mig_item].[DependencyHistory] as depHst
		on
			A.[Id] = depHst.[Id]
	where
		A.[Id] = (
			select max([Id])
			from
				obsoleteDep as C
			where
				A.[ItemId] = C.[ItemId]
		)

	-- Then DependencyHistoryDetail
	update [mig_item].[DependencyHistoryDetail]
	set
		[CorrelationID] = B.[NewCorrelationID]
	from
		[mig_item].[DependencyHistoryDetail] as A
		inner join #updatedDep as B
		on
			A.[CorrelationID] = B.[OldCorrelationID]
	
	-- Now delete the remaining obsolete DependencyHistory
	delete [mig_item].[DependencyHistory] 
	from
		[mig_item].[DependencyHistory] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 0 -- Export	

	delete [mig_item].[DependencyHistoryDetail] 
	from
		[mig_item].[DependencyHistoryDetail] as A
		inner join #toDelete as B
		on
			A.[CorrelationID] = B.[CorrelationID]
		and	B.[Step] = 0 -- Export	

	-- Finally delete the History rows for this batch
	delete [mig_item].[History]
	from
		[mig_item].[History] as A
		inner join #toDelete as B
		on 
			A.[Id] = B.[Id]

	commit
GO
PRINT N'Create procedure [mig_item].[History_ClearCount]...';
GO
CREATE OR ALTER PROCEDURE [mig_item].[History_ClearCount]
	@cutOff datetime2 
,	@total bigint output
AS
	set nocount on;

	select
		@total = count_big(*)
	from
		[mig_item].[_History_DeleteSet](@cutOff);
GO
PRINT N'Create procedure [mig_item].[History_Truncate]...';
GO
CREATE OR ALTER PROCEDURE [mig_item].[History_Truncate]
AS
	truncate table [mig_item].[History]

	truncate table [mig_item].[SourceXmlVersion]
	truncate table [mig_item].[ExportXmlVersion]
	truncate table [mig_item].[TargetXmlVersion]

	truncate table [mig_item].[ImportEventXmlVersion]
	truncate table [mig_item].[ExportEventXmlVersion]

	truncate table [mig_item].[FeedbackXmlVersion]

	truncate table [mig_item].[DependencyHistoryDetail]

	-- Update all hash and correlation values to force subsequent creation of new versions even when no changes
	declare @guidEmpty uniqueidentifier = '00000000-0000-0000-0000-000000000000'

	update [mig_item].[SourceXml] 
	set 
		[Hash] = 0
	,	[CorrelationIDLast] = @guidEmpty
	,	[CorrelationIDCreator] = @guidEmpty

	update [mig_item].[ExportXml]  
	set 
		[Hash] = 0
	,	[CorrelationIDLast] = @guidEmpty
	,	[CorrelationIDCreator] = @guidEmpty

	update [mig_item].[TargetXml] 
	set 
		[Hash] = 0
	,	[CorrelationIDLast] = @guidEmpty
	,	[CorrelationIDCreator] = @guidEmpty

	update [mig_item].[ImportEventXml]  
	set 
		[Hash] = 0
	,	[CorrelationIDLast] = @guidEmpty
	,	[CorrelationIDCreator] = @guidEmpty

	update [mig_item].[ExportEventXml] 
	set 
		[Hash] = 0
	,	[CorrelationIDLast] = @guidEmpty
	,	[CorrelationIDCreator] = @guidEmpty

	update [mig_item].[FeedbackEventXml]
	set
		[Hash] = 0
	  ,	[CorrelationIDLast] = @guidEmpty
	  ,	[CorrelationIDCreator] = @guidEmpty

	update [mig_item].[DependencyHistory] 
	set 
		[Hash] = 0
	,	[CorrelationID] = @guidEmpty
	,	[CorrelationIDLast] = @guidEmpty
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Fix SetloadExtension'
GO
PRINT N'Altering Procedure [mig_director].[Export_SourceTable_SetLoadedExtension]...';
GO
ALTER procedure [mig_director].[Export_SourceTable_SetLoadedExtension]
	@tableSchema varchar(128)
,   @tableName varchar(128)
,   @loadedCount int
as
	declare @date1 datetime2(7) = sysdatetime()
	declare @date2 datetime2(7) = dateadd(second, 1, @date1)

	declare @fileName varchar(512) = @tableSchema + '\' + @tableName + '.ExtensionLoad'

	declare @updated table([FullName] varchar(512))

	update [mig_director].[ExportSourceTable]
	set
		[LoadedCount] = @loadedCount
	,	[LoadedDate] = @date2
	output inserted.[FullName] into @updated
	where
		[TableSchema] = @tableSchema
	and	[TableName] = @tableName

	if 0 < @@rowCount
	begin
		declare @fullName varchar(512) = (select top(1) [FullName] from @updated)

		delete [mig_director].[ExportSourceFile]
		where
			[FileName] = @fileName

		insert into [mig_director].[ExportSourceFile] (
			[FileName]
		,	[FullName]
		,	[FileDate]
		,	[SourceTableFullName]
		,	[DateOfLoadedFile]
		,	[LoadedDate]
		,	[LoadedCount]
		) values (
			@fileName
		,	@fileName
		,	@date1
		,	@fullName
		,	@date1
		,	@date1
		,	@loadedCount
		);
	end
GO
PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- PartitionEventCount nonclustered index for ROW_NUMBER scans filtered by EntityID'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating index [IX_PartitionEventCount_EntityID] on [mig_tracking].[PartitionEventCount]...'
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mig_tracking].[PartitionEventCount]') AND name = N'IX_PartitionEventCount_EntityID')
	CREATE NONCLUSTERED INDEX [IX_PartitionEventCount_EntityID]
		ON [mig_tracking].[PartitionEventCount] ([EntityID] ASC, [MessageID] ASC, [PartitionValue] ASC, [Time] DESC)
		INCLUDE ([InstanceCount], [ItemCount])
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Release 2.6 end'
PRINT N'----------------------------------------------------------------------------------------------------------------------';
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Hotfix 2.6.8 refactor (MigrationDb.17.1) -- idempotent, no-op where already applied'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- EventSystem streaming source SP and TVP-based bridge-back'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating procedure [mig_tracking].[EventSystem_List]...'
GO

CREATE OR ALTER PROCEDURE [mig_tracking].[EventSystem_List]
as
begin
	set nocount on

	-----------------------------------------------------
	-- System Events (consumed as a streamed TVP by
	-- [mig_tracking].[EventSystem_InsertIfMissing] in
	-- the Project DB; see DbProject.EventSystem_InsertIfMissing).
	-- [Message] is VARCHAR(256) here; the receiving TVP column
	-- [EventSystemInput].[MessageFmt] is NVARCHAR(MAX). The
	-- DbDataReader -> TVP wire format requires matching column
	-- types, so convert + alias here.
	-----------------------------------------------------
	select distinct
		[Engine]
	,	[Step]
	,	[Disposition]
	,	[Impact]
	,	[Receiver]
	,	convert(nvarchar(max), [Message]) as [MessageFmt]
	from [mig_valid].[EventSystem]
end
GO

PRINT N'Altering procedure [mig_tracking].[Actual_List] (drop System Events result set)...'
GO

CREATE OR ALTER procedure [mig_tracking].[Actual_List]
as
begin
	set nocount on

	-----------------------------------------------------
	-- List SmartTexts
	-----------------------------------------------------
	select
		[ItemID]
	,	[Xml]
	from [mig_director].[SmartText]

	-----------------------------------------------------
	--  User Events
	-----------------------------------------------------
	select distinct
		A.[EventId]
	,	A.[EventTextId]
	,	A.[Engine]
	,	A.[Step]
	,	A.[Disposition]
	,	A.[Impact]
	,	A.[Receiver]
	,	A.[EventName]
	,	cast(case
			when B.[ItemID] is null then 1
			else 0
		end as bit) as [TextMissing]
	into #userMessage
	from [mig_valid].[EventUser] as A
	left outer join [mig_director].[SmartText] as B
		on B.[ItemID] = A.[EventTextId]

	select
		[EventId]
	,	[EventTextId]
	,	[Engine]
	,	[Step]
	,	[Disposition]
	,	[Impact]
	,	[Receiver]
	,	[EventName]
	from #userMessage

	-----------------------------------------------------
	-- System Events are sourced from
	-- [mig_tracking].[EventSystem_List] and inserted via
	-- [mig_tracking].[EventSystem_InsertIfMissing] on the
	-- Project DB (see DbProject.EventSystem_InsertIfMissing).
	-----------------------------------------------------

	-----------------------------------------------------
	-- List current PartitionValues
	-----------------------------------------------------
	select distinct
		[PartitionValue]
	from
		[mig_valid].[Item]

	-----------------------------------------------------
	-- Report missing smarttexts for User Events
	-----------------------------------------------------
	select
		[EventTextID]
	,	[EventName]
	from #userMessage
	where [TextMissing] = 1
end
GO

PRINT N'Creating type [mig_tracking].[EventSystemMessageInput]...'
GO

IF TYPE_ID(N'[mig_tracking].[EventSystemMessageInput]') IS NULL
	CREATE TYPE [mig_tracking].[EventSystemMessageInput] AS TABLE
	(
		[MessageID]		uniqueidentifier	not null
	,	[Step]			tinyint				not null
	,	[Engine]		tinyint				not null
	,	[Disposition]	tinyint				not null
	,	[Impact]		tinyint				not null
	,	[Receiver]		tinyint				not null
	,	[MessageFmt]	nvarchar(max)		not null
	)
GO

PRINT N'Creating type [mig_tracking].[EventUserMessageInput]...'
GO

IF TYPE_ID(N'[mig_tracking].[EventUserMessageInput]') IS NULL
	CREATE TYPE [mig_tracking].[EventUserMessageInput] AS TABLE
	(
		[MessageID]		uniqueidentifier	not null
	,	[Step]			tinyint				not null
	,	[Engine]		tinyint				not null
	,	[Disposition]	tinyint				not null
	,	[Impact]		tinyint				not null
	,	[Receiver]		tinyint				not null
	,	[EventID]		uniqueidentifier	not null
	)
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Delivery statistics: Statistic columns, _Delivery_Statistics UDF, Actual_Update pivot extension'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering [mig_tracking].[Statistic] (add [DelDiscarded] column)...'
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[mig_tracking].[Statistic]') AND name = N'DelDiscarded')
	ALTER TABLE [mig_tracking].[Statistic]
		ADD [DelDiscarded] BIGINT CONSTRAINT [DF_Statistic_DelDiscarded] DEFAULT (0) NOT NULL
GO

PRINT N'Altering [mig_tracking].[Statistic] (add [Delivered] column)...'
GO

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[mig_tracking].[Statistic]') AND name = N'Delivered')
	ALTER TABLE [mig_tracking].[Statistic]
		ADD [Delivered] BIGINT CONSTRAINT [DF_Statistic_Delivered] DEFAULT (0) NOT NULL
GO

PRINT N'Creating function [mig_director].[_Delivery_Statistics]...'
GO

CREATE OR ALTER FUNCTION [mig_director].[_Delivery_Statistics]()
returns table
as
	return (
		with list as (
			select
				B.[PartitionValue]
			,	C.[EntityID]
			, count(*) as [Count]
			, sum(
					case
						when F.[JobId] is null then 0
						else 1
					end
				) as [Processed]
			, sum(
					case
						when isnull(F.[Discarded], 1) = 0 then 1 -- 0:Delivered
						else 0
					end
				) as [Delivered]
			, sum(
					case
						when isnull(F.[Discarded], 0) = 1 then 1 -- 1:DeliveryDiscarded
						else 0
					end
				) as [Discarded]
			from
				[mig_item].[Id] as A with (NoLock)
				inner join [mig_item].[Item] as B with (NoLock)
					on	A.[ItemId] = B.[ItemId]
				inner join [mig_item].[ExportKey] as C with (NoLock)
					on	A.[ItemId] = C.[ItemId]
				inner join [mig_item].[ExportItem] as D with (NoLock)
					on	A.[ItemId] = D.[ItemId] and D.[Result] = 0 -- 0:Exported
				inner join [mig_item].[ImportItem] as E with (NoLock)
					on	A.[ItemId] = E.[ItemId] and E.[Result] = 2 and E.[JobId] >= D.[JobId] -- 2:Success
				left outer join [mig_item].[FeedbackItem] as F with (NoLock)
					on	A.[ItemId] = F.[ItemId] and F.[JobId] >= E.[JobId]
			where
				A.[InvalidatedTime] is null
			group by
				B.[PartitionValue]
			,	C.[EntityID]
		)

		select
			C.[PartitionValue]
		,	A.[EntityID]
		,	B.[BusinessEntityName]
		, A.[EntityName]
		, C.[Count]
		, C.[Delivered]
		, C.[Discarded]
		, C.[Count] - C.[Processed] as [Difference]
		from
			[mig_director].[ExportEntity] as A with (NoLock)
			inner join [mig_director].[BusinessEntity] as B with (NoLock)
				on A.[BusinessEntityID] = B.[BusinessEntityID]
			left outer join list as C
				on A.[EntityID] = C.[EntityID]
		where
			A.[IsPresent] = 1 and A.[Ignored] = 0
	)
GO

PRINT N'Altering procedure [mig_tracking].[Actual_Update] (TVP parameters + Delivery pivot)...'
GO

CREATE OR ALTER procedure [mig_tracking].[Actual_Update]
	@systemMessages [mig_tracking].[EventSystemMessageInput] readonly
,   @userMessages [mig_tracking].[EventUserMessageInput] readonly
,	@entitiesToProcess varchar(max) = null
,	@runDate datetime2
,   @user varchar(256)
,   @jobInstanceId int
as
begin
	set nocount on;

	declare @start datetime = getdate()
	declare @logId bigint
	declare @text varchar(max)  =
'------------------------------------------------------------------
Elapsed  [mig_tracking].[Actual_Update]
------------------------------------------------------------------'

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output

	-- Table to hold EntityID to process
	declare @entities table(EntityID uniqueidentifier, primary key (EntityID))

	if @entitiesToProcess is not null
	begin
		insert into @entities
		select distinct [Id] from [mig_utility].[ParseGuids](@entitiesToProcess, ',')
	end

	-----------------------------------------------------
	-- EventParameter
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert EventParameter', @logId output

	;with
		usrEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventUser])
	,	sysEv as (select distinct [ParameterHash], [GroupHash], [ParameterXml] from [mig_valid].[EventSystem])
	, allEv as (
		select * from usrEv
		union
		select * from sysEv
	)
	insert [mig_tracking].[EventParameter] (
		[HashValue]
	,	[GroupHash]
	,   [ParameterXml]
	,   [RunDate]
	)
	select
		A.[ParameterHash]
	,	A.[GroupHash]
	,   A.[ParameterXml]
	,   @runDate
	from allEv as A
	left outer join [mig_tracking].[EventParameter] as B
	on
		B.[HashValue] = A.[ParameterHash]
	where
		B.[HashValue] is null

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- RootItemEvent
	-----------------------------------------------------
	if @entitiesToProcess is null
	begin
		if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID')
			drop index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent]
		if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent')
			drop index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent]
		if exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId')
			drop index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent]

		truncate table [mig_tracking].[RootItemEvent]
	end
	else
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootItemEvent', @logId output
		delete [mig_tracking].[RootItemEvent]
		from
			[mig_tracking].[RootItemEvent] as A
			inner join @entities as B
			on
				A.[EntityID] = B.[EntityID]
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	-- User events

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (User)', @logId output

	;with
		[events] as (
			select
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Step]
			,	A.[Engine]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[EventID]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
			,	count(*) AS [EventCount]
			from
				[mig_valid].[EventUser] as A
				inner join [mig_item].[ExportKey] as B
				on
					A.[ItemId] = B.[ItemId]
				inner join [mig_item].[Item] as C
				on
					A.[ItemId] = C.[ItemId]
			where
				(@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities))
			group by
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Engine]
			,	A.[Step]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[EventID]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
		)
	insert [mig_tracking].[RootItemEvent] (
		[RowID]
	,	[EntityID]
	,	[ItemId]
	,	[PartitionValue]
	,	[MessageID]
	,	[ParameterHash]
	,	[GroupHash]
	,	[EventCount]
	,	[RunDate])
	select
		newid() -- To detect problem with join
	,	A.[EntityID]
	,	A.[ItemId]
	,	A.[PartitionValue]
	,	B.[MessageID]
	,	A.[ParameterHash]
	,	A.[GroupHash]
	,	A.[EventCount]
	,	@runDate
	from
		[events] as A
		inner join @userMessages as B
		on
			B.[Step] = A.[Step]
		and	B.[Engine] = A.[Engine]
		and	B.[Disposition] = A.[Disposition]
		and	B.[Impact] = A.[Impact]
		and	B.[Receiver] = A.[Receiver]
		and	B.[EventID] = A.[EventID]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-- System events

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootItemEvent (System)', @logId output

	;with
		[events] as (
			select
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Step]
			,	A.[Engine]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Message]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
			,	count(*) AS [EventCount]
			from
				[mig_valid].[EventSystem] as A
				inner join [mig_item].[ExportKey] as B
				on
					A.[ItemId] = B.[ItemId]
				inner join [mig_item].[Item] as C
				on
					A.[ItemId] = C.[ItemId]
			where
				(@entitiesToProcess is null or B.[EntityID] in (select [EntityID] from @entities))
			group by
				B.[EntityID]
			,	C.[PartitionValue]
			,	A.[ItemId]
			,	A.[Engine]
			,	A.[Step]
			,	A.[Receiver]
			,	A.[Disposition]
			,	A.[Impact]
			,	A.[Message]
			,	A.[ParameterHash]
			,	A.[GroupHash]
			,	A.[ParameterXml]
		)
	insert [mig_tracking].[RootItemEvent] (
		[RowID]
	,	[EntityID]
	,	[ItemId]
	,	[PartitionValue]
	,	[MessageID]
	,	[ParameterHash]
	,	[GroupHash]
	,	[EventCount]
	,	[RunDate])
	select
		newid() -- To detect problem with join
	,	A.[EntityID]
	,	A.[ItemId]
	,	A.[PartitionValue]
	,	B.[MessageID]
	,	A.[ParameterHash]
	,	A.[GroupHash]
	,	A.[EventCount]
	,	@runDate
	from
		[events] as A
		inner join @systemMessages as B
		on
			B.[Step] = A.[Step]
		and	B.[Engine] = A.[Engine]
		and	B.[Disposition] = A.[Disposition]
		and	B.[Impact] = A.[Impact]
		and	B.[Receiver] = A.[Receiver]
		and	B.[MessageFmt] = A.[Message]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_EntityID')
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create EntityID index on RootItemEvent', @logId output
		create nonclustered index [IX_RootItemEvent_EntityID] on [mig_tracking].[RootItemEvent]
		(
			[EntityID] asc
		)
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent')
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent index on RootItemEvent', @logId output
		create nonclustered index [IX_RootItemEvent] on [mig_tracking].[RootItemEvent]
		(
			[MessageID] asc,
			[EntityID] asc,
			[PartitionValue] asc,
			[ItemId] asc
		) include (
			[ParameterHash]
		)
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	if not exists (select * from sys.indexes where object_id = object_id(N'[mig_tracking].[RootItemEvent]') and name = N'IX_RootItemEvent_ItemId')
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Create RootItemEvent_ItemId index on RootItemEvent', @logId output
		create nonclustered index [IX_RootItemEvent_ItemId] on [mig_tracking].[RootItemEvent]
		(
			[ItemId] asc
		)
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	-----------------------------------------------------
	-- RootEntityEvent
	-----------------------------------------------------
	if @entitiesToProcess is null
	begin
		truncate table [mig_tracking].[RootEntityEvent];
	end
	else
	begin
		exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Delete RootEntityEvent', @logId output
		delete [mig_tracking].[RootEntityEvent]
		from
			[mig_tracking].[RootEntityEvent] as A
			inner join @entities as B
			on
				A.[EntityID] = B.[EntityID]
		exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0
	end

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Insert RootEntityEvent', @logId output

	insert [mig_tracking].[RootEntityEvent] (
		[EntityID]
	,	[PartitionValue]
	,	[MessageID]
	,	[ParameterHash]
	,	[GroupHash]
	,	[EventCount]
	,	[ItemCount]
	,	[RunDate])
	select
		[EntityID]
	,   [PartitionValue]
	,   [MessageID]
	,   [ParameterHash]
	,	[GroupHash]
	,   sum([EventCount])
	,	count(distinct [ItemId])
	,   @runDate
	from
		[mig_tracking].[RootItemEvent]
		where
			(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
	group by
		[EntityID]
	,   [PartitionValue]
	,   [MessageID]
	,   [ParameterHash]
	,	[GroupHash]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- PartitionEventCount
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'PartitionEventCount, update', @logId output

	declare @addedCounts table(
		[MessageID] uniqueidentifier not null
	,   [EntityID] uniqueidentifier not null
	,   [PartitionValue] nvarchar(50) not null
	,   [InstanceCount] int not null
	,   [ItemCount] int not null
	)

	declare @existingCounts table(
		[MessageID] uniqueidentifier not null
	,   [EntityID] uniqueidentifier not null
	,   [PartitionValue] nvarchar(50) not null
	,   [InstanceCount] int not null
	,   [ItemCount] int not null
	)

	;with
		[latest] as (
			select
				[MessageID]
			,	[EntityID]
			,	[PartitionValue]
			,	[InstanceCount]
			,	[ItemCount]
			,	row_number() over (partition by [MessageID], [EntityID], [PartitionValue] order by [Time] desc) as [rn]
			from
				[mig_tracking].[PartitionEventCount]
		)
	insert @existingCounts
	select
		[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	,	[InstanceCount]
	,	[ItemCount]
	from
		[latest]
	where
		[rn] = 1

	;with
		[newCounts] as (
			select
				[MessageID]
			,	[EntityID]
			,   [PartitionValue]
			,   sum([EventCount]) as [InstanceCount]
			,	count(distinct [ItemId]) as [ItemCount]
			from
				[mig_tracking].[RootItemEvent]
			where
				(@entitiesToProcess is null or [EntityID] in (select [EntityID] from @entities))
			group by
				[MessageID]
			,	[EntityID]
			,   [PartitionValue]
		)
	insert [mig_tracking].[PartitionEventCount] (
		[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	,	[InstanceCount]
	,	[ItemCount]
	,   [Time]
	)
	output inserted.[MessageID], inserted.[EntityID], inserted.[PartitionValue], inserted.[InstanceCount], inserted.[ItemCount] into @addedCounts
	select
		coalesce(A.[MessageID], B.[MessageID])
	,   coalesce(A.[EntityID], B.[EntityID])
	,   coalesce(A.[PartitionValue], B.[PartitionValue])
	,   isnull(A.[InstanceCount], 0)
	,   isnull(A.[ItemCount], 0)
	,	@runDate
	from
		[newCounts] as A
		full outer join @existingCounts as B
		on
			A.[MessageID] = B.[MessageID]
		and A.[EntityID] = B.[EntityID]
		and A.[PartitionValue] = B.[PartitionValue]
		where
			(@entitiesToProcess is null or coalesce(A.[EntityID], B.[EntityID]) in (select [EntityID] from @entities))
		and (
				isnull(A.[InstanceCount], 0) <> isnull(B.[InstanceCount], 0)
			or	isnull(A.[ItemCount], 0) <> isnull(B.[ItemCount], 0)
		)

	-- Log count changes
	insert [mig_tracking].[Log]
	select
		A.[MessageID]
	,   A.[EntityID]
	,   A.[PartitionValue]
	,   case
			when B.[InstanceCount] is null then 0 -- Added
			else 1 -- Modified
		end
	,   @runDate
	,   @user
	,   'Instance Count (' + A.[PartitionValue] + ')'
	,   format(isnull(B.[InstanceCount], 0), 'N0')
	,   format(A.[InstanceCount], 'N0')
	from
		@addedCounts as A
		left outer join @existingCounts as B
		on
			A.[MessageID] = B.[MessageID]
		and	A.[EntityID] = B.[EntityID]
		and	A.[PartitionValue] = B.[PartitionValue]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- Statistics
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Statistics', @logId output;

	with
		export as (
			select *
			from
				[mig_director].[_Export_Statistics]()
			where
				[PartitionValue] is not null
		)
	,	import as (
			select *
			from [mig_director].[_Import_Statistics]()
			where
				[PartitionValue] is not null
		)
	,	delivery as (
			select *
			from [mig_director].[_Delivery_Statistics]()
			where
				[PartitionValue] is not null
		)
	,	newStatistic as (
			select
				A.[PartitionValue]
			,	A.[EntityID]
			,   A.[Count] - A.[Dropped] as [Available]
			,   A.[Discarded] as [ExpDiscarded]
			,   A.[Exported]
			,   isnull(B.[Discarded], 0) as [ImpDiscarded]
			,   isnull(B.[Imported], 0) as [Imported]
			,   isnull(C.[Discarded], 0) as [DelDiscarded]
			,   isnull(C.[Delivered], 0) as [Delivered]
			,   A.[Difference] + isnull(B.[Difference], 0) + isnull(C.[Difference], 0) as [Check]
			from
				export as A
				left outer join import as B
				on
					A.[PartitionValue] = B.[PartitionValue]
				and	A.[EntityID] = B.[EntityID]
				left outer join delivery as C
				on
					A.[PartitionValue] = C.[PartitionValue]
				and	A.[EntityID] = C.[EntityID]
			where
				(@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities))
		)
	,	curStatistic as (
			select *
			from
				[mig_tracking].[Statistic] as A
			where
				(@entitiesToProcess is null or A.[EntityID] in (select [EntityID] from @entities))
			and	A.[Time] = (
					select max([Time])
					from
						[mig_tracking].[Statistic]
					where
						[PartitionValue] = A.[PartitionValue]
					and	[EntityID] = A.[EntityID]
				)
		)
	insert [mig_tracking].[Statistic] (
		[PartitionValue]
	,	[EntityID]
	,	[Time]
	,	[Available]
	,	[ExpDiscarded]
	,	[Exported]
	,	[ImpDiscarded]
	,	[Imported]
	,	[DelDiscarded]
	,	[Delivered]
	)
	select
		coalesce(A.[PartitionValue], B.[PartitionValue]) as [PartitionValue]
	,	coalesce(A.[EntityID], B.[EntityID]) as [EntityID]
	,	@runDate as [RunDate]
	,   isnull(A.[Available], 0) as [Available]
	,   isnull(A.[ExpDiscarded], 0) as [ExpDiscarded]
	,   isnull(A.[Exported], 0) as [Exported]
	,   isnull(A.[ImpDiscarded], 0) as [ImpDiscarded]
	,   isnull(A.[Imported], 0) as [Imported]
	,   isnull(A.[DelDiscarded], 0) as [DelDiscarded]
	,   isnull(A.[Delivered], 0) as [Delivered]
	from
		newStatistic as A
		full outer join curStatistic as B
		on
			A.[PartitionValue] = B.[PartitionValue]
		and	A.[EntityID] = B.[EntityID]
	where
		A.[EntityID] is null -- Obsolete
	or	B.[EntityID] is null -- New
	or	B.[Available] <> A.[Available]
	or	B.[ExpDiscarded] <> A.[ExpDiscarded]
	or	B.[Exported] <> A.[Exported]
	or	B.[ImpDiscarded] <> A.[ImpDiscarded]
	or	B.[Imported] <> A.[Imported]
	or	B.[DelDiscarded] <> A.[DelDiscarded]
	or	B.[Delivered] <> A.[Delivered]

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	-----------------------------------------------------
	-- Parameters
	-----------------------------------------------------
	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 1, 'Parameters', @logId output

	truncate table [mig_tracking].[Parameter]

	insert [mig_tracking].[Parameter]
	select
		[ItemID]
	,   [ProjectType]
	,   [Name]
	,   [Value]
	,	[Description]
	from
		[mig_director].[RunParameter]
	where
		[Present] = 1
	and [Value] is not null

	exec [mig_director].[JobInstance_LogEntryUpdate] @logId, 0

	set @text =
'------------------------------------------------------------------
' + convert(varchar, getdate() - @start, 108) + ' Total
------------------------------------------------------------------'

	exec [mig_director].[JobInstance_LogEntryInsert] @jobInstanceId, 0, @text, @logId output

	-- Return the current non-zero partition counts for status updates in the project DB
	;with
		[latest] as (
			select
				[MessageID]
			,	[EntityID]
			,	[PartitionValue]
			,	[InstanceCount]
			,	row_number() over (partition by [MessageID], [EntityID], [PartitionValue] order by [Time] desc) as [rn]
			from
				[mig_tracking].[PartitionEventCount]
		)
	select
		[MessageID]
	,	[EntityID]
	,	[PartitionValue]
	from
		[latest]
	where
		[rn] = 1
	and	[InstanceCount] > 0
end
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- PartitionEventCount nonclustered index for ROW_NUMBER scans filtered by EntityID'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating index [IX_PartitionEventCount_EntityID] on [mig_tracking].[PartitionEventCount]...'
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[mig_tracking].[PartitionEventCount]') AND name = N'IX_PartitionEventCount_EntityID')
	CREATE NONCLUSTERED INDEX [IX_PartitionEventCount_EntityID]
		ON [mig_tracking].[PartitionEventCount] ([EntityID] ASC, [MessageID] ASC, [PartitionValue] ASC, [Time] DESC)
		INCLUDE ([InstanceCount], [ItemCount])
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Prune obsolete messages: streaming source SPs for currently-occurring User and System events'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating procedure [mig_tracking].[EventUser_GetCurrent]...'
GO

CREATE OR ALTER procedure [mig_tracking].[EventUser_GetCurrent]
	@IncludeExport		bit
,	@IncludeImport		bit
,	@IncludeDelivery	bit
as
	set nocount on

	-----------------------------------------------------
	-- User events currently occurring in this track,
	-- consumed as a streamed TVP by
	-- [mig_tracking].[EventUser_KeepSet_Append] in the
	-- Project DB. Column order must match the receiving
	-- TVP type [EventUserKeyInput] one-for-one (ordinal
	-- mapping, no MessageID — that's resolved on the
	-- Project DB side via the MessageKey tuple).
	-----------------------------------------------------
	select distinct
		[Engine]
	,	[Step]
	,	[Disposition]
	,	[Impact]
	,	[Receiver]
	,	[EventID]
	from
		[mig_valid].[EventUser]
	where
		(@IncludeExport		= 1 and [Step] = 0)
		or (@IncludeImport	= 1 and [Step] = 1)
		or (@IncludeDelivery	= 1 and [Step] = 2)
GO

PRINT N'Creating procedure [mig_tracking].[EventSystem_GetCurrent]...'
GO

CREATE OR ALTER procedure [mig_tracking].[EventSystem_GetCurrent]
	@IncludeExport		bit
,	@IncludeImport		bit
,	@IncludeDelivery	bit
as
	set nocount on

	-----------------------------------------------------
	-- System events currently occurring in this track,
	-- consumed as a streamed TVP by
	-- [mig_tracking].[EventSystem_KeepSet_Append] in the
	-- Project DB. Column order must match the receiving
	-- TVP type [EventSystemKeyInput] one-for-one (ordinal
	-- mapping). [Message] is VARCHAR(256) here; the TVP's
	-- [MessageFmt] is NVARCHAR(MAX) — DbDataReader -> TVP
	-- needs matching column types so convert + alias here.
	-----------------------------------------------------
	select distinct
		[Engine]
	,	[Step]
	,	[Disposition]
	,	[Impact]
	,	[Receiver]
	,	convert(nvarchar(max), [Message]) as [MessageFmt]
	from
		[mig_valid].[EventSystem]
	where
		(@IncludeExport		= 1 and [Step] = 0)
		or (@IncludeImport	= 1 and [Step] = 1)
		or (@IncludeDelivery	= 1 and [Step] = 2)
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Prune obsolete messages: PartitionEventCount cleanup TVP type and SPs'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Creating type [mig_tracking].[MessageIDInput]...'
GO

IF TYPE_ID(N'[mig_tracking].[MessageIDInput]') IS NULL
	CREATE TYPE [mig_tracking].[MessageIDInput] AS TABLE
	(
		[MessageID]		uniqueidentifier	not null
	)
GO

PRINT N'Creating procedure [mig_tracking].[PartitionEventCount_PruneIds_Append]...'
GO

CREATE OR ALTER procedure [mig_tracking].[PartitionEventCount_PruneIds_Append]
	@ids [mig_tracking].[MessageIDInput] readonly
as
	set nocount on

	-- Requires caller-owned temp: #PartitionPruneIds (created by DbTrack.PartitionPruneIds_Init).

	------------------------------------------------------------------------------------------------------
	-- Receives the project-wide #PruneDeleteSet stream as a TVP and inserts the MessageIDs into the
	-- caller-owned #PartitionPruneIds temp on the held DbTrack connection. The Prune Messages job
	-- creates #PartitionPruneIds with a primary key on MessageID; the not-exists predicate keeps the
	-- INSERT idempotent against TVP duplicates.
	------------------------------------------------------------------------------------------------------
	insert into #PartitionPruneIds ([MessageID])
	select i.[MessageID]
	from @ids as i
	where
		not exists (
			select 1
			from #PartitionPruneIds as k
			where k.[MessageID] = i.[MessageID]
		)
GO

PRINT N'Creating procedure [mig_tracking].[PartitionEventCount_PruneBatch]...'
GO

CREATE OR ALTER procedure [mig_tracking].[PartitionEventCount_PruneBatch]
	@BatchSize int
as
	set nocount on

	-- Requires caller-owned temp: #PartitionPruneIds (created by DbTrack.PartitionPruneIds_Init).

	------------------------------------------------------------------------------------------------------
	-- Deletes one batch of [PartitionEventCount] rows whose MessageID matches an entry in the
	-- caller-owned #PartitionPruneIds. All rows for a matched MessageID are eligible regardless of
	-- [Time] — the underlying message is gone, so every historical snapshot is unreachable. Manager
	-- calls this in a loop until rowcount = 0 so per-batch progress can be surfaced via
	-- IJobInstanceLogEntry.Update.
	------------------------------------------------------------------------------------------------------
	delete top (@BatchSize) from [mig_tracking].[PartitionEventCount]
	where [MessageID] in (select [MessageID] from #PartitionPruneIds)

	select @@rowcount as [Rows]
GO

PRINT N'Creating procedure [mig_tracking].[PartitionEventCount_RefreshStatistics]...'
GO

CREATE OR ALTER procedure [mig_tracking].[PartitionEventCount_RefreshStatistics]
as
	set nocount on

	------------------------------------------------------------------------------------------------------
	-- Refreshes optimizer statistics on [PartitionEventCount] after the Prune Messages job's
	-- per-track cleanup. The cleanup can shrink the table by orders of magnitude on projects with
	-- large historical data; explicit full-scan refresh keeps subsequent plans (Impact widget,
	-- Statistic list) calibrated for the post-delete cardinality without waiting for auto-stats.
	------------------------------------------------------------------------------------------------------
	update statistics [mig_tracking].[PartitionEventCount] with fullscan
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Hotfix 2.6.9 refactor (MigrationDb.17.2) -- idempotent, no-op where already applied'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- HS-4981: catch blocks re-raise with THROW (raiserror cannot re-raise severity > 18 and masks the original error);'
PRINT N'-- HS-4981: Export_EntitySynchronizeItems commits in batches (bounded lock count on large entities)'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering procedure [mig_item].[_Export_EntitySynchronizeItems]...'
GO

CREATE OR ALTER procedure [mig_item].[_Export_EntitySynchronizeItems]
	@jobId int
,	@jobInstanceId int
,	@entityID uniqueidentifier
,	@newCount int output
,	@obsoleteCount int output
as
	set nocount on

	-- make sure the expected #sync temp table exists
	if object_id('tempdb..#sync') is null
	begin
		raiserror('#sync temp table not found. Call [mig_item].[Export_EntitySynchronizeItems] first.', 16, 1)
		return
	end

	declare @time datetime2(7) = sysdatetime()

	-- Batched processing: each batch commits its own transaction so the lock count stays
	-- bounded on large entities (HS-4981; 1M+ items in a single transaction exhausted lock
	-- memory). A failed run leaves completed batches in place; a rerun reconciles from the
	-- ExportKey anti-joins below. The caller must NOT hold an enclosing transaction, or the
	-- per-batch commits are nested no-ops and the lock count is unbounded again.
	declare @batchSize int = 50000

	--------------------------------------------------------------------------------------------------------------
	-- Process New items
	--------------------------------------------------------------------------------------------------------------
	drop table if exists #newExport
	create table #newExport (
		[RowNum] int identity(1, 1) not null primary key
	,	[ExportID] uniqueidentifier not null unique
	,   [BreakFields] nvarchar(4000) not null
	)

	-- Determine new items; [RowNum] (identity, arrival order) numbers them for batching
	-- without the sort a row_number() would need
	insert #newExport (
		[ExportID]
	,	[BreakFields]
	)
	select
		A.[ExportID]
	,	A.[BreakFields]
	from
		#sync as A
		left outer join [mig_item].[ExportKey] as B
		on
			B.[EntityID] = @entityID
		and	B.[ExportID] = A.[ExportID]
		where
			B.[ExportID] is null

	-- Return the number of new items
	set @newCount = @@rowcount

	drop table if exists #mapExportIDToItemId
	create table #mapExportIDToItemId (
		[ExportID] uniqueidentifier not null primary key
	,   [ItemId] bigint not null
	)

	-- Loop bound on max(RowNum), not @newCount: identity values are dense for a single
	-- insert, but a gap must never silently skip the tail of the set
	declare @maxRow int = isnull((select max([RowNum]) from #newExport), 0)
	declare @from int = 1
	declare @to int

	while @from <= @maxRow
	begin
		set @to = @from + @batchSize - 1

		-- The map only ever holds the current batch; the joins below are batch-restricted through it
		truncate table #mapExportIDToItemId

		begin transaction

			-- Generate ItemIds
			-- Using MERGE to be able to reference src.[ExportID] in the OUTPUT clause
			merge [mig_item].[Id] with (rowlock) as tgt
			using (
				select [ExportID]
				from #newExport
				where [RowNum] between @from and @to
			) as src
			on 1 = 0 -- force insert for every source row
			when not matched by target then
				insert (
					[CreationDate]
				) values (
					@time
				)
				output
					src.[ExportID]
				,	inserted.[ItemId]
				into #mapExportIDToItemId (
					[ExportID]
				,	[ItemId]
				);

			-- Match ExportIDs and generated ItemIds and insert on ExportKey
			insert [mig_item].[ExportKey] with (RowLock) (
				[ExportID]
			,	[ItemId]
			,	[EntityID]
			,	[BreakFields]
			,	[JobId]
			,	[JobInstanceId]
			)
			select
				A.[ExportID]
			,	B.[ItemId]
			,	@entityID
			,	A.[BreakFields]
			,	@jobId
			,	@jobInstanceId
			from
				#newExport as A
				inner join #mapExportIDToItemId as B
				on
					A.[ExportID] = B.[ExportID]

			-- Normalize key fields for faster searches
			insert [mig_item].[ExportKeyField] (
				[ItemId]
			,	[KeyName]
			,	[KeyValue]
			)
			select
				m.[ItemId]
			,	k.N.value('local-name(.)', 'nvarchar(256)')
			,	k.N.value('(@value)[1]', 'nvarchar(512)')
			from
				#newExport as e
				inner join #mapExportIDToItemId as m
				on
					e.[ExportID] = m.[ExportID]
				cross apply (select cast(e.[BreakFields] as xml) as keyXml) as x
				cross apply x.keyXml.nodes('/*/*') as k(N);

		commit transaction

		set @from = @to + 1
	end

	--------------------------------------------------------------------------------------------------------------
	-- Process Obsolete items
	--------------------------------------------------------------------------------------------------------------
	declare @obsoleteItems [mig_garbage].[InvalidateSet]
	declare @batchCount int

	set @obsoleteCount = 0

	while 1 = 1
	begin
		delete @obsoleteItems

		begin transaction

			-- Determine and remove one batch of obsolete items
			delete top (@batchSize) A
			output deleted.[ItemId] into @obsoleteItems([ItemId])
			from
				[mig_item].[ExportKey] as A with (RowLock)
				left outer join #sync as B
				on
					A.[ExportID] = B.[ExportID]
			where
				A.[EntityID] = @entityID
			and	B.[ExportID] is null

			set @batchCount = @@rowcount
			set @obsoleteCount += @batchCount

			if @batchCount > 0
			begin
				-- Delete normalized key fields for obsolete items
				delete A
				from
					[mig_item].[ExportKeyField] as A with (RowLock)
					inner join @obsoleteItems as B
					on
						A.[ItemId] = B.[ItemId]

				-- Mark as garbage
				exec [mig_garbage].[_SetItemSetObsolete] @obsoleteItems
			end

		commit transaction

		if @batchCount < @batchSize
			break
	end
GO

PRINT N'Altering procedure [mig_item].[Export_EntitySynchronizeItems]...'
GO

CREATE OR ALTER procedure [mig_item].[Export_EntitySynchronizeItems]
	@jobId int
,	@jobInstanceId int
,	@entityID	uniqueidentifier
,	@exportDb varchar(50)
,	@totalCount int output
,	@newCount int output
,	@obsoleteCount int output
as
	set xact_abort on;

	----------------------------------------------------------------------------------------------------------------
	-- Dynamically call the Synchronize procedure in the Export DB and retrieve the sync results in temp table #sync
	----------------------------------------------------------------------------------------------------------------
	declare @sql nvarchar(max) = N'exec ' + @exportDb + N'.[mig_director].[EntityKeyList] @entityId;'

	-- Execute the call
	drop table if exists #sync;
	create table #sync (
		[ExportID] uniqueidentifier primary key
	,	[BreakFields] nvarchar(1024) not null	
	);
	insert into #sync (
		[ExportID]
	,   [BreakFields]
	)
	exec sp_executesql
		@sql
	,   N'@entityId uniqueidentifier'
	,   @entityId = @entityID

	-- Return the total count
	set @totalCount = @@rowcount

	declare @now datetime2(7) = sysdatetime()

	begin try
		-- No enclosing transaction: the synchronize commits in batches internally to keep
		-- the lock count bounded on large entities (HS-4981). A failure leaves completed
		-- batches in place; a rerun reconciles them.
		-- The #sync table is expected by the called procedure
		exec [mig_item].[_Export_EntitySynchronizeItems] @jobId, @jobInstanceId, @entityID, @newCount output, @obsoleteCount output

		-- Update the ExportEntity LastSynchronizeTime (only after a fully completed synchronize)
		update [mig_director].[ExportEntity]
		set
			[LastSynchronizeTime] = @now
		where
			[EntityId] = @entityID
	end try
	begin catch
		if @@trancount > 0 rollback transaction;
		throw;
	end catch;
GO

PRINT N'Altering procedure [mig_item].[_GetKeyId]...'
GO

CREATE OR ALTER procedure [mig_item].[_GetKeyId]
	@businessEntityName varchar(50)
,	@keyValue nvarchar(1024)
,	@maxDeadlockRetries int
,	@keyId bigint output
as
	set nocount on

	declare @retryCount int = 0
	declare @startTranCount int = @@trancount;
	declare @localTransaction bit = 0;

	lblRetry:
	select	
		@keyId = [KeyId]
	from
		[mig_item].[MainKey]
	where
		[BusinessEntityName] = @businessEntityName
	and	[KeyValue] = @keyValue

	if @keyId is not null
		return

	-- Guarded insert: may race with other sessions inserting the same key
	begin try
		-- Try insert first

		-- Only manage a transaction if there wasn't one already
		if 0 = @startTranCount
		begin
			begin transaction;
			set @localTransaction = 1;
		end

		insert [mig_item].[MainKey] (
			[BusinessEntityName] 
		,	[KeyValue]
		) 
		values (
			@businessEntityName 
		,	@keyValue 
		)
		
		set @keyId = scope_identity()

		-- Normalize key fields for faster searches
		insert [mig_item].[MainKeyField] (
			[KeyId]
		,	[KeyName]
		,	[KeyValue]
		)
		select
			@keyId
		,	k.N.value('local-name(.)', 'nvarchar(256)')
		,	k.N.value('(@value)[1]', 'nvarchar(512)')
		from
			(select cast(@keyValue as xml) as keyXml) as x
			cross apply x.keyXml.nodes('/*/*') as k(N);

		-- If we started a local transaction, commit it
		if @localTransaction = 1 and @@trancount > 0
			commit transaction;

		return
	end try
	begin catch
		-- Insert failed

		-- If we started a local transaction, roll it back
		if @localTransaction = 1 and @@trancount > 0
			rollback transaction;

		declare @err int = error_number()

		-- 2601 Duplicate in unique index for BusinessEntityName and KeyValue
		-- 2627 Unique constraint violation
		if @err in (2601, 2627)
		begin
			-- Another session inserted the same key between our SELECT and INSERT
			-- Go back and do the select again
			goto lblRetry
		end
		-- 1205: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
		-- 1222: Lock request time out period exceeded.
		else if @err in (1205, 1222)
		begin
			if @retryCount < @maxDeadlockRetries 
			begin
				declare @delay time = case @retryCount
					when 0 then '00:00:00.000'
					when 1 then '00:00:00.005'
					when 2 then '00:00:00.010'
					else '00:00:00.020'
				end

				waitfor delay @delay

				set @retryCount = @retryCount + 1

				goto lblRetry
			end
		end;

		-- Any other error or out of retries: Rethrow
		-- Bare throw: raiserror cannot re-raise severity > 18 (HS-4981)
		throw;
	end catch
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Hotfix 2.6.10 refactor (MigrationDb.17.3) -- idempotent, no-op where already applied'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- HS-4985: _GetKeyId must not continue inside a doomed transaction (XACT_STATE() = -1): a re-select would'
PRINT N'-- HS-4985: return cleanly and defer error 3930 to the caller''s next write, masking the original error.'
PRINT N'-- HS-4985: the catch now re-raises immediately with bare THROW when the caller transaction is doomed.'
PRINT N'-- HS-4985: Forward-ports the 2.5 fix (MigrationDb.16.1) onto the HS-4981 2.6 _GetKeyId shape.'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering procedure [mig_item].[_GetKeyId]...'
GO
CREATE OR ALTER procedure [mig_item].[_GetKeyId]
	@businessEntityName varchar(50)
,	@keyValue nvarchar(1024)
,	@maxDeadlockRetries int
,	@keyId bigint output
as
	set nocount on

	declare @retryCount int = 0
	declare @startTranCount int = @@trancount;
	declare @localTransaction bit = 0;

	lblRetry:
	select	
		@keyId = [KeyId]
	from
		[mig_item].[MainKey]
	where
		[BusinessEntityName] = @businessEntityName
	and	[KeyValue] = @keyValue

	if @keyId is not null
		return

	-- Guarded insert: may race with other sessions inserting the same key
	begin try
		-- Try insert first

		-- Only manage a transaction if there wasn't one already
		if 0 = @startTranCount
		begin
			begin transaction;
			set @localTransaction = 1;
		end

		insert [mig_item].[MainKey] (
			[BusinessEntityName] 
		,	[KeyValue]
		) 
		values (
			@businessEntityName 
		,	@keyValue 
		)
		
		set @keyId = scope_identity()

		-- Normalize key fields for faster searches
		insert [mig_item].[MainKeyField] (
			[KeyId]
		,	[KeyName]
		,	[KeyValue]
		)
		select
			@keyId
		,	k.N.value('local-name(.)', 'nvarchar(256)')
		,	k.N.value('(@value)[1]', 'nvarchar(512)')
		from
			(select cast(@keyValue as xml) as keyXml) as x
			cross apply x.keyXml.nodes('/*/*') as k(N);

		-- If we started a local transaction, commit it
		if @localTransaction = 1 and @@trancount > 0
			commit transaction;

		return
	end try
	begin catch
		-- Insert failed

		-- If we started a local transaction, roll it back
		if @localTransaction = 1 and @@trancount > 0
			rollback transaction;

		declare @err int = error_number()

		-- HS-4985: a doomed caller transaction cannot be recovered here: reads
		-- still succeed, so the re-select path would return cleanly and defer a
		-- 3930 to the caller's next write, masking the original error. Re-raise
		-- immediately; only the transaction owner can roll back and retry. (A
		-- doomed local transaction was already rolled back above, so
		-- xact_state() is 0 there and retrying remains legitimate.)
		if xact_state() = -1
			throw;

		-- 2601 Duplicate in unique index for BusinessEntityName and KeyValue
		-- 2627 Unique constraint violation
		if @err in (2601, 2627)
		begin
			-- Another session inserted the same key between our SELECT and INSERT
			-- Go back and do the select again
			goto lblRetry
		end
		-- 1205: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
		-- 1222: Lock request time out period exceeded.
		else if @err in (1205, 1222)
		begin
			if @retryCount < @maxDeadlockRetries 
			begin
				declare @delay time = case @retryCount
					when 0 then '00:00:00.000'
					when 1 then '00:00:00.005'
					when 2 then '00:00:00.010'
					else '00:00:00.020'
				end

				waitfor delay @delay

				set @retryCount = @retryCount + 1

				goto lblRetry
			end
		end;

		-- Any other error or out of retries: Rethrow
		-- Bare throw: raiserror cannot re-raise severity > 18 (HS-4981)
		throw;
	end catch
GO
