
DECLARE @itemID int
,@ShoppingListNumber nvarchar(max)
,@RetailerName nvarchar(max)
,@DateCreated nvarchar(max)
,@SearchName nvarchar(max)
,@Barcode nvarchar(max)
,@CostPrice decimal(18,2)
,@RetailerDataItemId int;
SET rowcount 0
select * 
into #tempTable 
from [publ_ShoppingListSearchHistoryItems] 
ORDER BY recId;

SET rowcount 1
select @itemID = itemID
,@ShoppingListNumber = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_ShoppingListNumber
,@RetailerName = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_RetailerName
,@DateCreated = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_DateCreated
,@SearchName = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_SearchName
,@Barcode = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_Barcode
,@CostPrice = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_CostPrice
,@RetailerDataItemId = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_RetailerDataItemId
from #tempTable;

WHILE @@rowcount <> 0
BEGIN
    SET rowcount 0
	delete #tempTable where itemID = @itemID
	DECLARE @RDId int = -1, @RetailerCode nvarchar(max);

	IF LEN(@Barcode) > 0
	BEGIN
		IF LEN(@RetailerName) = 0
		BEGIN
			SELECT @RetailerName = ShoppingListDetails_RetailerName 
			FROM qt_ShoppingLists
			WHERE ShoppingListDetails_ShoppingListNumber = @ShoppingListNumber
		END;
		-- check if exists in retailer data
		SELECT @RDId = itemID 
		FROM publ_RetailerDataItems
		WHERE RetailerDataItems_RetailerDataItems_Barcode = @Barcode
		AND RetailerDataItems_RetailerDataItems_RetailerName = @RetailerName;
		-- if not 
		IF @RDId = -1
		BEGIN
			-- get retailer code -> insert into
			SELECT @RetailerCode = Retailer_RetailerCode 
			FROM qt_Retailers
			WHERE Retailer_RetailerName = @RetailerName;

			INSERT INTO [dbo].[pal_SurfaceItem]
			   ([surfaceId]
			   ,[dateCreated]
			   ,[isActive]
			   ,[createdBy]
			   ,[updatedBy]
			   ,[dateUpdated]
			   ,[isDeleted]
			   ,[isWizardCompleted]
			   ,[lastTabCompleted]
			   ,[userLink]
			   ,[surfaceLinkItems])
			 VALUES
			   (4111
			   ,GETDATE()
			   ,1
			   ,217
			   ,0
			   ,CAST('1900-01-01 00:00:00' AS DATETIME)
			   ,0
			   ,0
			   ,0
			   ,0
			   ,'');
			SET @RDId = SCOPE_IDENTITY();

			INSERT INTO [dbo].[publ_RetailerDataItems]
			   ([itemID]
			   ,[isActive]
			   ,[surfaceId]
			   ,[ParentSurfaceItemId]
			   ,[RetailerDataItems_RetailerDataItems_Barcode]
			   ,[RetailerDataItems_RetailerDataItems_RetailerName]
			   ,[RetailerDataItems_RetailerDataItems_RetailerCode]
			   ,[RetailerDataItems_RetailerDataItems_CostPrice]
			   ,[RetailerDataItems_RetailerDataItems_LastUpdated]
			   ,[RetailerDataItems_RetailerDataItems_UpdatedBy]
			   ,[RetailerDataItems_RetailerDataItems_ImportFileName])
			VALUES
			   (@RDId
			   ,1
			   ,4111
			   ,0
			   ,@Barcode
			   ,@RetailerName
			   ,@RetailerCode
			   ,@CostPrice
			   ,CONVERT(varchar, GETDATE(), 20)
			   ,'Dylan Reevell'
			   ,@SearchName);
			
			UPDATE publ_ShoppingListSearchHistoryItems
			SET ShoppingListSearchHistoryItems_ProductSearchHistoryItems_RetailerDataItemId = @RDId
			WHERE itemID = @itemID;
		END;
		-- if yes
		ELSE
		BEGIN
			-- update row with cost price
			UPDATE publ_RetailerDataItems
			SET RetailerDataItems_RetailerDataItems_CostPrice = @CostPrice,
			RetailerDataItems_RetailerDataItems_LastUpdated = CONVERT(varchar, GETDATE(), 20)			WHERE itemID = @RDId;

			UPDATE publ_ShoppingListSearchHistoryItems
			SET ShoppingListSearchHistoryItems_ProductSearchHistoryItems_RetailerDataItemId = @RDId
			WHERE itemID = @itemID;
		END;
	END;
	SET rowcount 1
	select @itemID = itemID
	,@ShoppingListNumber = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_ShoppingListNumber
	,@RetailerName = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_RetailerName
	,@DateCreated = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_DateCreated
	,@SearchName = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_SearchName
	,@Barcode = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_Barcode
	,@CostPrice = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_CostPrice
	,@RetailerDataItemId = ShoppingListSearchHistoryItems_ProductSearchHistoryItems_RetailerDataItemId
	from #tempTable;

END
set rowcount 0;
DROP TABLE #tempTable;