PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Delivery feedback naming an item that no longer exists must not abort the whole load.'
PRINT N'-- [mig_item].[Feedback_Insert] now reports an unknown item through the @found output parameter'
PRINT N'-- and writes nothing. It used to raise error 50000, which does not stop the procedure, so the'
PRINT N'-- rows below the check were still written for an item that does not exist.'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering 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
,	@found bit output
as
	set NoCount on

	-- Feedback can name an item that no longer exists: the item may have been invalidated by a
	-- later export synchronize, or already collected as garbage. Report that to the caller and
	-- write nothing at all - the caller decides whether to fail or to skip the item. Raising here
	-- would not stop the procedure (severity 16 is not fatal), so the rows below would be written
	-- for an item that does not exist.
	set @found = 0

	if not exists (select 1 from [mig_valid].[Item] where [ItemId] = @itemId)
	begin
		return
	end

	set @found = 1

	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'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- Restore the unique covering index [IX_Item_Cover] on [mig_item].[Item], removed in 2.6.0.'
PRINT N'-- It serves ItemId lookups with KeyId and PartitionValue, and it enforces that an item has'
PRINT N'-- exactly one row in [mig_item].[Item]. If the table already contains duplicate ItemId rows,'
PRINT N'-- this script stops: the data needs repair before the index can be restored.'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO
IF EXISTS (
	SELECT 1 FROM sys.indexes
	WHERE object_id = OBJECT_ID(N'[mig_item].[Item]') AND [name] = N'IX_Item_Cover'
)
BEGIN
	PRINT N'Index [mig_item].[Item].[IX_Item_Cover] already exists.';
END
ELSE IF EXISTS (
	SELECT 1 FROM [mig_item].[Item] GROUP BY [ItemId] HAVING COUNT(*) > 1
)
BEGIN
	THROW 50000, N'[mig_item].[Item] contains duplicate ItemId rows; repair the data before restoring [IX_Item_Cover].', 1;
END
ELSE
BEGIN
	PRINT N'Creating Index [mig_item].[Item].[IX_Item_Cover]...';
	CREATE UNIQUE NONCLUSTERED INDEX [IX_Item_Cover] ON [mig_item].[Item]
	(
		[ItemId] ASC
	) INCLUDE (
		[KeyId]
	,	[PartitionValue]
	);
END
GO
