

--SELECT	MASK_ID, '' sgno, -- generate seq no
--		'' as sgty, -- const value / sequence based on MASK like DOC-?????? => DOC CONSTANT ????? => sequence
--		'' as fval, -- empty
--		'DOC' as sval, -- CONSTANT VALUE => DOC
--		0 as slen,
--		'?????' as patn,  -- sequence value in MASK  => If PRIMARY_SIGN = 1 THEN IGNORE >> LAST PATTERN of ?/* and insert sequence with 9999 as in LN [0-9]
--		ALP_MASK as incr,  -- REPLACE '..' with '-' and ***   must ignore small case (a-z) => CONFIRM with umesh/srini 
--		MIN_MASK, -- without CONSTANT
--		MAX_MASK, --      "
--		UPDATED_ON
--FROM ZZ_MASK 


-- Stored Procedure
--    Author:           Rohith
--    Create date:      28/10/2021
--    Description:      To generate segements based on the MASK     
--  Change history
--      20/12/2021  - Rohith - stabilised script
--      24/02/2022  - Rohith - Added new logic to handle MAJOR MASKS


-- EXEC MIG_MASK_SEGMENTS_Sp
CREATE OR ALTER PROCEDURE MIG_MASK_SEGMENTS_Sp
AS
BEGIN

	
	DECLARE @RESULT_SET TABLE(MASK_ID NVARCHAR(15), SEGMENT NVARCHAR(MAX), IS_SEQ_OR_CONST BIT, PATTERN NVARCHAR(MAX) ) 
	
	DECLARE @MASK_ID VARCHAR(15), @MASK VARCHAR(60) , @PATTERN VARCHAR(100)
	
	DECLARE RecordsCursor CURSOR  LOCAL 
	FOR  
		SELECT MSK.MASK_ID, MSK.MASK, MSK.ALP_MASK
		FROM ZZ_MASK MSK
		JOIN MIG_MASK_FLD_MAPPING MAP
			ON MAP.CLASS_NAME = MSK.CLASS_NAME AND 
				MAP.KEY_NAME = MSK.KEY_NAME

	OPEN RecordsCursor  
	WHILE 1 = 1   
	BEGIN
		FETCH NEXT FROM RecordsCursor INTO @MASK_ID, @MASK, @PATTERN
		IF @@FETCH_STATUS <> 0
			BREAK

		INSERT INTO @RESULT_SET (MASK_ID, SEGMENT, IS_SEQ_OR_CONST, PATTERN) 
		EXEC MIG_GENERATE_MASK_SEGEMENTS_Sp @MASK_ID, @MASK , @PATTERN

	END
	
	CLOSE RecordsCursor
	
	DECLARE @INT INT
	SET @INT = 0

	DELETE FROM MIG_MASKS_SEGMENTS
	INSERT INTO MIG_MASKS_SEGMENTS
	SELECT	RES.MASK_ID, ROW_NUMBER() OVER( PARTITION BY RES.MASK_ID  ORDER BY RES.MASK_ID ) as sgno, 
			RES.IS_SEQ_OR_CONST as sgty, 
			'' as fval,
			CASE WHEN RES.IS_SEQ_OR_CONST = 1 THEN '' 
				ELSE RES.SEGMENT 
			END as sval, 
			0 as slen,
			CASE WHEN RES.IS_SEQ_OR_CONST = 0 THEN '' 
				 ELSE RES.SEGMENT
			END as patn,
			CASE WHEN RES.IS_SEQ_OR_CONST = 0 THEN '' 
				 ELSE 
					REPLACE(ISNULL(RES.PATTERN,ALP_MASK), '..', '-') 
			END	as incr,
			CASE WHEN RES.IS_SEQ_OR_CONST = 0 AND MSK.PRIMARY_SIGN = 1 THEN '' -- CONSTANT
				 ELSE 
						REPLACE(MSK.MIN_MASK, 
									REPLACE(  REPLACE(MSK.MASK, '?', '') , '*', ''),
                    			'')
			END as minv,
			CASE WHEN RES.IS_SEQ_OR_CONST = 0 AND MSK.PRIMARY_SIGN = 1 THEN '' -- CONSTANT
				 ELSE 
						REPLACE(MSK.MAX_MASK, 
							REPLACE(  REPLACE(MSK.MASK, '?', '') , '*', ''),
                    	'')
			END as maxv,
			MSK.UPDATED_ON,
			MSK.PRIMARY_SIGN
	FROM @RESULT_SET RES
	LEFT JOIN ZZ_MASK MSK
		ON MSK.MASK_ID = RES.MASK_ID

	-- Update MinVal, MaxVal for MAJOR mask senario
	--SELECT DISTINCT mask, patn, MIN_VAL = 
	UPDATE MS1 SET minv = SUBSTRING(minv, 
			  LEN(
					ISNULL(STUFF((SELECT ', ' + patn
							   FROM MIG_MASKS_SEGMENTS MS2
							   WHERE MS2.mask = MS1.mask 
							   AND	 MS2.patn = MS2.patn  AND MS2.sgno < MS1.sgno
							  FOR XML PATH('')), 1, 2, '') 
							  ,'')
				 )+1, 
			  LEN(patn) ),
	maxv = SUBSTRING(maxv, 
			  LEN(
					ISNULL(STUFF((SELECT ', ' + patn
							   FROM MIG_MASKS_SEGMENTS MS2
							   WHERE MS2.mask = MS1.mask 
							   AND	 MS2.patn = MS2.patn  AND MS2.sgno < MS1.sgno
							  FOR XML PATH('')), 1, 2, '') 
							  ,'')
				 )+1, 
			  LEN(patn) )
	FROM MIG_MASKS_SEGMENTS MS1
	JOIN @RESULT_SET RES
	ON RES.MASK_ID = MS1.mask
	WHERE ISNULL(RES.PATTERN,'')<>''

	-- SELECT @@ROWCOUNT

	-- Update cases like FIND no. for MASK type REVISION (PRIMARY_SIGN = 2) and segment type => sequence ( sgty = 1)
	UPDATE MIG_MASKS_SEGMENTS 
		SET minv = CASE 
						WHEN minv = '' AND incr like '%A-Z%' THEN REPLICATE('A', LEN(patn) ) 
						WHEN minv = '' AND incr like '%0-9%' THEN REPLICATE('0', LEN(patn) )
						ELSE minv END, 
			maxv = CASE 
						WHEN maxv = '' AND incr like '%A-Z%' THEN REPLICATE('Z', LEN(patn) ) 
						WHEN maxv = '' AND incr like '%0-9%' THEN REPLICATE('9', LEN(patn) ) 
						ELSE maxv END
	WHERE PRIMARY_SIGN = 2 and sgty = 1 AND ( minv = '' OR maxv = '' )

	-- UPDATE SEGMENT NUMBER VALUE
	
	;WITH CTE_MASK_MAX_SEG_NUM_SEQ AS (
		SELECT mask, sgno,
			   Row_Number() OVER(PARTITION BY mask ORDER BY sgty DESC, sgno DESC) AS ROW_NUM
			FROM MIG_MASKS_SEGMENTS 
	),

	CTE_LAST_SEQUENCE_SEGMENT AS (

	SELECT SEG.mask, SEG.sgno, ROW_NUM
	FROM MIG_MASKS_SEGMENTS SEG
	INNER JOIN CTE_MASK_MAX_SEG_NUM_SEQ MSQ
	ON SEG.mask = MSQ.mask and SEG.sgno = MSQ.sgno
	GROUP BY SEG.mask, sgty, SEG.sgno, PRIMARY_SIGN, ROW_NUM
	HAVING sgty = 1 AND PRIMARY_SIGN = 1 --AND  ROW_NUM = 1 

	)

	UPDATE SEG SET SEG.sgno = CASE WHEN ROW_NUM = 1 THEN 9999 ELSE (SEG.sgno * 10) END,
	SEG.sgty = CASE WHEN SEG.sgty = 0 THEN 20
					WHEN SEG.sgty = 1 THEN 30 
				END,
	SEG.incr = CASE WHEN ROW_NUM = 1 THEN '[0-9]' ELSE SEG.incr END,
	SEG.minv = CASE WHEN ROW_NUM = 1 THEN
						REPLACE(SEG.patn, '?', '0')
					ELSE SEG.minv END,
	SEG.maxv = CASE WHEN ROW_NUM = 1 THEN
						REPLACE(SEG.patn, '?', '9')
					ELSE SEG.maxv END
	FROM	MIG_MASKS_SEGMENTS SEG
	LEFT JOIN	CTE_LAST_SEQUENCE_SEGMENT SEQ
			ON SEG.mask = SEQ.mask AND SEG.sgno = SEQ.sgno
		
END


-- EXEC MIG_MASK_SEMENTS_Sp