-- EXEC MIG_SYNC_RULES_Sp
CREATE OR ALTER PROCEDURE MIG_SYNC_RULES_Sp 
AS 
BEGIN
	TRUNCATE TABLE MIG_SYNC_RULES
	SELECT 
		A.ZZ_ID,
		A.SITE_KEY, 
		CASE WHEN A.COND_OBJECT = 'FILE' THEN 'DATASET'
			ELSE A.COND_OBJECT END AS COND_OBJECT,
		A.COND_NO,
		A.COND_RELATION,   
		1 AS open_br,
		REPLACE(A.COND_ATTR, A.COND_OBJECT + '.', '') AS COND_ATTR,
		A.CONDITION,
		A.COND_VALUE,
		1 AS clos_br
	INTO #TEMP1
	FROM SYNC_RULES A
	WHERE A.COND_RELATION = '' OR A.COND_RELATION IN ('AND', 'OR')

	SELECT * 
	INTO #TEMP2 
	FROM
		(SELECT  ZZ_ID,
			SITE_KEY,
			COND_OBJECT,
			COND_NO,
			COND_RELATION,
			open_br,
			COND_ATTR,
			CONDITION,
			COND_VALUE,
			clos_br
		FROM #TEMP1 WHERE COND_NO = 1
		UNION
		SELECT  T2.ZZ_ID,
				T2.SITE_KEY,
				T2.COND_OBJECT,
				T2.COND_NO,
				T1.COND_RELATION,
				T2.open_br,
				T2.COND_ATTR,
				T2.CONDITION,
				T2.COND_VALUE,
				T2.clos_br
		FROM
			(SELECT ZZ_ID,
					SITE_KEY,
					COND_OBJECT,
					COND_NO,
					COND_RELATION,
					open_br,
					COND_ATTR,
					CONDITION,
					COND_VALUE,
					clos_br FROM #TEMP1 WHERE COND_RELATION <> '') T1
			JOIN
			(SELECT ZZ_ID,
					SITE_KEY,
					COND_OBJECT,
					COND_NO,
					COND_RELATION,
					open_br,
					COND_ATTR,
					CONDITION,
					COND_VALUE,
					clos_br FROM #TEMP1 WHERE COND_RELATION = '')  T2
			ON
			T1.COND_NO+2 = T2.COND_NO
			AND T1.SITE_KEY = T2.SITE_KEY) T
	ORDER BY T.SITE_KEY, T.COND_NO

	INSERT INTO [dbo].[MIG_SYNC_RULES]
           ([zz_id]
		   ,[site_key]
           ,[cnum]
           ,[anor]
           ,[str_anor]
           ,[open_br]
           ,[str_open_br]
           ,[keyn]
           ,[oper]
           ,[str_oper]
           ,[valu]
           ,[clos_br]
           ,[str_clos_br]
           ,[updt])
	SELECT 
		A.ZZ_ID,
		A.SITE_KEY AS site_key,
		ROW_NUMBER() OVER(PARTITION BY A.SITE_KEY ORDER BY A.ZZ_ID) *10 AS cnum,
		CASE WHEN A.COND_RELATION = '' THEN 10
			WHEN A.COND_RELATION = 'AND' THEN 10 
			ELSE 20 END AS anor,
		CASE WHEN A.COND_RELATION = '' THEN 'AND'
			ELSE A.COND_RELATION END AS str_anor,
		A.open_br,
		CASE WHEN A.open_br = 1 THEN '('
			ELSE '' END AS str_open_br,
		M.TAR_FIELD_NAME AS keyn,
		CASE WHEN A.CONDITION = 'EQU' THEN 20
			WHEN A.CONDITION = 'DIF' THEN 30
			WHEN A.CONDITION = '>' THEN 50
			WHEN A.CONDITION = 'GEQ' THEN 60
			WHEN A.CONDITION = 'LES' THEN 70
			WHEN A.CONDITION = 'LEQ' THEN 80
			ELSE 140 END AS oper,
		CASE WHEN A.CONDITION = 'EQU' THEN '='
			WHEN A.CONDITION = 'DIF' THEN '<>'
			WHEN A.CONDITION = '>' THEN '>'
			WHEN A.CONDITION = 'GEQ' THEN '>='
			WHEN A.CONDITION = 'LES' THEN '<'
			WHEN A.CONDITION = 'LEQ' THEN '<='
			END AS str_oper,
		A.COND_VALUE,
		A.clos_br,
		CASE WHEN A.clos_br = 1 THEN ')'
			ELSE '' END AS str_clos_br,
		GETDATE()
	FROM	#TEMP2 A
	JOIN	MIG_PROV_TABLE_FIELD_MAPPING M
		ON	A.COND_OBJECT = M.SRC_BASE_TBL_NAME
		AND A.COND_ATTR = M.SRC_BASE_FIELD_NAME
		AND	M.TAR_TABLE_NAME <> 'pdpdm510'
		AND M.TAR_FIELD_NAME NOT IN ('pdpdm500.bara', 'pdpdm500.bvrn', 'pdpdm500.fnm2')
		AND A.COND_ATTR <> ''
		AND M.TYPE = 'Custom'
	ORDER BY A.SITE_KEY, A.COND_NO

		
	--SELECT 
	--	A.SITE_KEY as site_key, 
	--	A.COND_NO as src_cnum,
	--	ROW_NUMBER() OVER(PARTITION BY A.SITE_KEY ORDER BY A.ZZ_ID) *10 AS cnum,
	--	A.COND_RELATION as anor,   
	--	A.open_br as open_br,
	--	M.TAR_FIELD_NAME AS keyn,
	--	A.CONDITION as oper,
	--	A.COND_VALUE as valu,
	--	1 AS clos_br
	--FROM	#TEMP2 A
	--JOIN	MIG_PROV_TABLE_FIELD_MAPPING M
	--	ON	A.COND_OBJECT = M.SRC_BASE_TBL_NAME
	--	AND A.COND_ATTR = M.SRC_BASE_FIELD_NAME
	--	AND	M.TAR_TABLE_NAME <> 'pdpdm510'
	--	AND A.COND_ATTR <> ''
	--  AND M.TYPE = 'Custom'
	--ORDER BY A.SITE_KEY, A.COND_NO
	

	DROP TABLE #TEMP1
	DROP TABLE #TEMP2

END


