/*
LEGAL / LICENSE NOTICE

This SQL stored procedure is shared for informational, educational, and reference
purposes only. It is not legal advice, professional consulting advice, or an
official AnalyticsCreator deliverable.

No license, ownership interest, or other rights are granted by this post in any
AnalyticsCreator software, documentation, trademarks, object code, source code,
templates, generated artifacts, or other proprietary materials. AnalyticsCreator
and related rights remain the property of AnalyticsCreator Solutions GmbH or the
applicable rights holder.

If this procedure was generated by, exported from, adapted from, or otherwise
derived from AnalyticsCreator software or documentation, you are responsible for
confirming that your license permits public sharing before publishing or reusing it.

This code is provided "AS IS", without warranty, support, maintenance, or guarantee
that it is correct, secure, complete, performant, or suitable for any specific
environment. Use it at your own risk. Test carefully before production use and
maintain independent backups of all affected databases and repositories.

Do not use, copy, modify, publish, sublicense, or distribute this material in any
way that violates an applicable license agreement, confidentiality obligation,
employment agreement, customer agreement, or law.

Before sharing publicly, remove or anonymize any confidential information,
customer data, credentials, internal schema names, proprietary business logic,
server names, package names, logging procedures, or other sensitive details.
*/

CREATE PROCEDURE [CFG].[HIST_STG_Categories] AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @StartDate datetime = GETDATE()
	DECLARE	
		@StartDateStr varchar(30) = CONVERT(varchar(30), @StartDate, 121),
		@CloseDate datetime = DATEADD(ms, -2, @StartDate),
		@Inserted int = 0, 
		@Deleted int = 0, 
		@Updated int = 0
	
	EXEC [LOG].[PACKAGE_RUN_LOG_ENTRY] 
		@PackageName = 'HIST_STG_Categories', 
		@TableName = '[STG].[Categories]', 
		@StartTimeStr = @StartDateStr, 
		@Inserted = NULL, 
		@Updated = NULL, 
		@Deleted = NULL

	IF NOT EXISTS (SELECT 1 FROM [STG].[Categories] WHERE [DAT_BIS_HIST] = '99991231')
	BEGIN
		INSERT INTO [STG].[Categories](
			[CategoryID],
			[CategoryName],
			[Description],
			[Picture],
			[DAT_VON_HIST],
			[DAT_BIS_HIST])
		SELECT 
			I.[CategoryID],
			I.[CategoryName],
			CONVERT(nvarchar(max), I.[Description]) [Description],
			CONVERT(varbinary(max), I.[Picture]) [Picture], 
			@StartDate, 
			'99991231'
		FROM 
			[IMP].[Categories] I

		SET @Inserted = @Inserted + @@ROWCOUNT

		GOTO FINISH
	END

	CREATE TABLE #T 
	(
		[CategoryID] int,
		[CategoryName] nvarchar(15),
		[Description] nvarchar(max),
		[Picture] varbinary(max),
		[SATZ_ID] bigint, 
		[DAT_VON_HIST] datetime,
		HIST_SCD_TYPE int,
		[DAT_BIS_HIST_PREV] datetime
	)

	INSERT INTO #T([CategoryID],
		[CategoryName],
		[Description],
		[Picture], 
		[SATZ_ID], 
		[DAT_VON_HIST], 
		HIST_SCD_TYPE, 
		[DAT_BIS_HIST_PREV])
	SELECT
		I.[CategoryID],
		I.[CategoryName],
		CONVERT(nvarchar(max), I.[Description]) [Description],
		CONVERT(varbinary(max), I.[Picture]) [Picture], 
		S.[SATZ_ID],
		S.[DAT_VON_HIST],
		2 HIST_SCD_TYPE,
		NULL
	FROM
	(
		SELECT
			[CategoryID],
			[CategoryName],
			CONVERT(nvarchar(max), [Description]) [Description],
			CONVERT(varbinary(max), [Picture]) [Picture]
		FROM
			[IMP].[Categories] I
		EXCEPT SELECT 
			[CategoryID],
			[CategoryName],
			CONVERT(nvarchar(max), [Description]) [Description],
			CONVERT(varbinary(max), [Picture]) [Picture]
		FROM
			[STG].[Categories] S (NOLOCK)
		WHERE 
			[DAT_BIS_HIST] = '99991231'
	) I
	LEFT JOIN [STG].[Categories] S (NOLOCK) ON
		S.[DAT_BIS_HIST] = '99991231' AND
		(S.[CategoryID] = I.[CategoryID])

	UPDATE H
	SET [DAT_BIS_HIST] = @CloseDate
	FROM
		#T T
		INNER JOIN [STG].[Categories] H ON
			H.[SATZ_ID] = T.[SATZ_ID]
	WHERE
		T.[SATZ_ID] IS NOT NULL AND
		T.HIST_SCD_TYPE = 2 AND
		T.[DAT_VON_HIST] < @StartDate

	SET @Updated = @Updated + @@ROWCOUNT

	INSERT INTO [STG].[Categories]([CategoryID],
		[CategoryName],
		[Description],
		[Picture], [DAT_VON_HIST], [DAT_BIS_HIST])
	SELECT 
		[CategoryID],
		[CategoryName],
		[Description],
		[Picture], @StartDate, '99991231'
	FROM 
		#T T
	WHERE
		([SATZ_ID] IS NULL OR HIST_SCD_TYPE = 2)

	SET @Inserted = @Inserted + @@ROWCOUNT

	UPDATE S
	SET 
		[DAT_BIS_HIST] = @CloseDate
	FROM
		[STG].[Categories] S
	WHERE
		[DAT_BIS_HIST] = '99991231' AND
		[DAT_VON_HIST] < @StartDate AND
		NOT EXISTS 
		(
		SELECT 1 FROM
			[IMP].[Categories] I (NOLOCK)
		WHERE
			(S.[CategoryID] = I.[CategoryID])
		)

	SET @Deleted = @Deleted + @@ROWCOUNT
FINISH:

	UPDATE STATISTICS [STG].[Categories]

	EXEC [LOG].[PACKAGE_RUN_LOG_ENTRY] 
		@PackageName = 'HIST_STG_Categories', 
		@TableName = '[STG].[Categories]', 
		@StartTimeStr = @StartDateStr, 
		@Inserted = @Inserted, 
		@Updated = @Updated, 
		@Deleted = @Deleted
END