USE [DD_ECM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- [FNCUST_GET_EDMI_ITEM_VALUE]
-- =================================================================
-- Returns item values from [TBEDMI_ITEM_VALUE] with optional filters,
-- fallback handling and EB normalization for INVOICE_REFERENCE specs.
--
-- Returns: Table (max. 10.000 rows)
-- =================================================================

CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_EDMI_ITEM_VALUE] (
	@REFERENCE_GUID		NVARCHAR(250) = NULL,
	@SPEC_NAME			NVARCHAR(100) = NULL
)
RETURNS TABLE
AS
RETURN (

	--====================================-- SPEC fallback configuration --====================================--
	-- FALLBACK_ITEM_VALUE: value used when EB is invalid/missing
	-- ALLOW_FALLBACK:      1 = use fallback, 0 = keep original ITEM_VALUE
	WITH [CFG_INVOICE_REFERENCE_SPEC] AS (
		SELECT N'INVOICE_REFERENCE' AS [SPEC_NAME], CAST(N'EB9999999' AS NVARCHAR(100)) AS [FALLBACK_ITEM_VALUE], CAST(1 AS BIT) AS [ALLOW_FALLBACK]
		UNION ALL SELECT N'INVOICE_REFERENCE2', CAST(NULL AS NVARCHAR(100)), CAST(0 AS BIT)
		UNION ALL SELECT N'INVOICE_REFERENCE3', N'', CAST(0 AS BIT)
	),

	--=======================================-- Source data (filtered) --=======================================--
	[SRC_FILTERED_DATA] AS (
		SELECT
			[GUID],
			[REFERENCE_GUID],
			[ITEM_DESCRIPTION],
			[ITEM_VALUE],
			[CREATEDWHEN],
			[CREATEDWHO],
			[CHANGEDWHEN],
			[GROUP_COUNTER],
			[SPEC_NAME],
			[IS_REQUIRED],
			[CHANGEDWHO],
			[COMMENT]
		FROM [dbo].[TBEDMI_ITEM_VALUE]
		WHERE (@REFERENCE_GUID IS NULL OR [REFERENCE_GUID] = @REFERENCE_GUID)
		  AND (@SPEC_NAME IS NULL OR [SPEC_NAME] = @SPEC_NAME)
	)

	--========================================-- Final output resultset --========================================--
	SELECT TOP (10000)
		[GUID],
		[REFERENCE_GUID],
		[ITEM_DESCRIPTION],
		CASE
			-- Normalize ITEM_VALUE for configured INVOICE_REFERENCE specs only
			WHEN [CHK_SPEC].[IS_INVOICE_REFERENCE_SPEC] = 1
			THEN [NORM_EB].[NORMALIZED_EB_VALUE]
			ELSE [OUT_RESULT_DATA].[ITEM_VALUE]
		END AS [ITEM_VALUE],
		[OUT_RESULT_DATA].[CREATEDWHEN],
		[OUT_RESULT_DATA].[CREATEDWHO],
		[OUT_RESULT_DATA].[CHANGEDWHEN],
		[OUT_RESULT_DATA].[GROUP_COUNTER],
		[OUT_RESULT_DATA].[SPEC_NAME],
		[OUT_RESULT_DATA].[IS_REQUIRED],
		[OUT_RESULT_DATA].[CHANGEDWHO],
		[OUT_RESULT_DATA].[COMMENT],
		[OUT_RESULT_DATA].[IS_FALLBACK],
		CASE
			-- Flag rows where configured EB fallback was actually applied
			WHEN [CHK_SPEC].[IS_INVOICE_REFERENCE_SPEC] = 1
				 AND
				 [CHK_SPEC].[ALLOW_FALLBACK] = 1
				 AND
				 (
					 ([CHK_SPEC].[CONFIGURED_FALLBACK_ITEM_VALUE] IS NULL AND [NORM_EB].[NORMALIZED_EB_VALUE] IS NULL)
					 OR [NORM_EB].[NORMALIZED_EB_VALUE] = [CHK_SPEC].[CONFIGURED_FALLBACK_ITEM_VALUE]
				 )
			THEN CAST(1 AS BIT)
			ELSE CAST(0 AS BIT)
		END AS [IS_EB_FALLBACK]
	FROM (
		-- Real rows from source table
		SELECT
			[GUID],
			[REFERENCE_GUID],
			[ITEM_DESCRIPTION],
			[ITEM_VALUE],
			[CREATEDWHEN],
			[CREATEDWHO],
			[CHANGEDWHEN],
			[GROUP_COUNTER],
			[SPEC_NAME],
			[IS_REQUIRED],
			[CHANGEDWHO],
			[COMMENT],
			CAST(0 AS BIT) AS [IS_FALLBACK]
		FROM [SRC_FILTERED_DATA]

		UNION ALL

		-- Failsafe row if source query returns no row
		SELECT
			CAST(0 AS BIGINT) AS [GUID],
			ISNULL(@REFERENCE_GUID, N'FAILSAFE') AS [REFERENCE_GUID],
			N'NO_DATA_FOUND' AS [ITEM_DESCRIPTION],
			N'' AS [ITEM_VALUE],
			GETDATE() AS [CREATEDWHEN],
			N'SYSTEM' AS [CREATEDWHO],
			NULL AS [CHANGEDWHEN],
			CAST(0 AS INT) AS [GROUP_COUNTER],
			ISNULL(@SPEC_NAME, N'FAILSAFE') AS [SPEC_NAME],
			CAST(0 AS BIT) AS [IS_REQUIRED],
			'SYSTEM' AS [CHANGEDWHO],
			'FAILSAFE_ROW_RETURNED' AS [COMMENT],
			CAST(1 AS BIT) AS [IS_FALLBACK]
		WHERE NOT EXISTS (SELECT 1 FROM [SRC_FILTERED_DATA])
	) AS [OUT_RESULT_DATA]

	--===================================-- Resolve spec configuration --===================================--
	OUTER APPLY (
		SELECT TOP (1)
			v.[SPEC_NAME] AS [MATCHED_SPEC_NAME],
			v.[FALLBACK_ITEM_VALUE] AS [CONFIGURED_FALLBACK_ITEM_VALUE],
			v.[ALLOW_FALLBACK] AS [ALLOW_FALLBACK]
		FROM [CFG_INVOICE_REFERENCE_SPEC] v
		WHERE v.[SPEC_NAME] = UPPER(CONVERT(NVARCHAR(200), ISNULL([OUT_RESULT_DATA].[SPEC_NAME], N'')))
	) AS [CFG_SPEC]

	--=============================-- Derived check flags and fallback value --=============================--
	CROSS APPLY (
		SELECT
			CASE
				WHEN [CFG_SPEC].[MATCHED_SPEC_NAME] IS NULL THEN CAST(0 AS BIT)
				ELSE CAST(1 AS BIT)
			END AS [IS_INVOICE_REFERENCE_SPEC],
			[CFG_SPEC].[CONFIGURED_FALLBACK_ITEM_VALUE] AS [CONFIGURED_FALLBACK_ITEM_VALUE],
			ISNULL([CFG_SPEC].[ALLOW_FALLBACK], CAST(0 AS BIT)) AS [ALLOW_FALLBACK]
	) AS [CHK_SPEC]

	--=============================-- Preprocessing for EB pattern matching --=============================--
	CROSS APPLY (
		SELECT LTRIM(RTRIM(ISNULL([OUT_RESULT_DATA].[ITEM_VALUE], N''))) AS [CLEAN_ITEM_VALUE]
	) AS [VAL_CLEAN]

	CROSS APPLY (
		SELECT N' ' + [VAL_CLEAN].[CLEAN_ITEM_VALUE] + N' ' AS [PADDED_ITEM_VALUE]
	) AS [VAL_PADDED]

	CROSS APPLY (
		-- Match EB + 8 digits with non-digit boundaries left and right
		SELECT PATINDEX(
			N'%[^0-9]EB[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][^0-9]%',
			[VAL_PADDED].[PADDED_ITEM_VALUE]
		) AS [EB_PATTERN_POS]
	) AS [PAT_EB]

	--====================================-- Normalize EB output value --====================================--
	CROSS APPLY (
		SELECT
			CASE
				-- Exact EB value
				WHEN [VAL_CLEAN].[CLEAN_ITEM_VALUE] LIKE N'EB[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
				THEN [VAL_CLEAN].[CLEAN_ITEM_VALUE]

				-- Extract EB value from a longer free-text string
				WHEN [PAT_EB].[EB_PATTERN_POS] > 0
				THEN SUBSTRING([VAL_PADDED].[PADDED_ITEM_VALUE], [PAT_EB].[EB_PATTERN_POS] + 1, 10)

				-- No valid EB found: fallback if allowed, otherwise keep original value
				ELSE CASE
					WHEN [CHK_SPEC].[ALLOW_FALLBACK] = 1 THEN [CHK_SPEC].[CONFIGURED_FALLBACK_ITEM_VALUE]
					ELSE [OUT_RESULT_DATA].[ITEM_VALUE]
				END
			END AS [NORMALIZED_EB_VALUE]
	) AS [NORM_EB]

	--=========================================-- Default result ordering --=========================================--
	ORDER BY [GROUP_COUNTER] ASC
);

/***
-- Test

	SELECT * from dbo.[FNCUST_GET_EDMI_ITEM_VALUE] ('ERw202602090942454286171lfelsburg','INVOICE_TAXPOS_RATE')

***/