--EXEC [MIG_GENERATE_MAPPING_RULES_Sp]
CREATE OR ALTER PROCEDURE [MIG_GENERATE_MAPPING_RULES_Sp]
AS BEGIN
	-- The purpose of this stored procedure is to generate/populate the data in [MIG_MAPPING_RULES] table.

	DECLARE @Itr_ZZ_ID INT
	DECLARE @Itr_MAPPING_RULES_PDM_OBJ_ID NVARCHAR(MAX)
	DECLARE @Itr_MAPPING_RULES_PDM_FLD_ID NVARCHAR(MAX)
	
	DELETE FROM MIG_MAPPING_RULES

	--Create an entry for all users (existing in Users Table)
	INSERT INTO MIG_MAPPING_RULES
	(
		ZZ_ID, 
		TEMPLATE, 
		PDM_OBJ_ID, PDM_OBJ_UI_LABEL, 
		PDM_FLD_ID, PDM_FLD_UI_LABEL, PDM_FLD_UI_TYPE, PDM_FLD_LEN, 
		DIRECTION,
		CAD_OBJ_ID, CAD_OBJ_UI_LABEL, 
		CAD_FLD_ID, CAD_FLD_UI_LABEL, CAD_FLD_UI_TYPE, CAD_FLD_LEN, 
		EXTRA_DATA,
		UPDATED_ON,
		UPDT_CAD_WHEN_NOT_FLD,
		TO_UPPER_CASE
	)
	SELECT  
		ZZ_ID, 
		TEMPLATE, 
		PDM_OBJ_ID, PDM_OBJ_UI_LABEL, 
		PDM_FLD_ID, PDM_FLD_UI_LABEL, PDM_FLD_UI_TYPE, PDM_FLD_LEN, 
		CASE WHEN DIRECTION = 'toPDM' THEN 'toPLM' ELSE DIRECTION END as DIRECTION,
		CAD_OBJ_ID, CAD_OBJ_UI_LABEL, 
		CAD_FLD_ID, CAD_FLD_UI_LABEL, CAD_FLD_UI_TYPE, CAD_FLD_LEN, 
		EXTRA_DATA,
		UPDATED_ON,
		CASE WHEN CHARINDEX('"UPDATECADIFNOTFILLED":"0"', MAPPING_RULES.EXTRA_DATA, 0) > 0 THEN 0 
			 WHEN CHARINDEX('"UPDATECADIFNOTFILLED":"1"', MAPPING_RULES.EXTRA_DATA, 0) > 0 THEN 1 
			 ELSE 0 
		END AS [UPDT_CAD_WHEN_NOT_FLD],
		CASE WHEN CHARINDEX('"MAPPING_TOUPPERCASE":"0"', MAPPING_RULES.EXTRA_DATA, 0) > 0 THEN 0 
			 WHEN CHARINDEX('"MAPPING_TOUPPERCASE":"1"', MAPPING_RULES.EXTRA_DATA, 0) > 0 THEN 1 
			 ELSE 0 
		END AS [TO_UPPER_CASE]
	FROM MAPPING_RULES 

	DECLARE RecordsCursor CURSOR  
	LOCAL FORWARD_ONLY FOR  
		SELECT
			ZZ_ID,
			PDM_OBJ_ID,
			PDM_FLD_ID
		FROM MIG_MAPPING_RULES

	OPEN RecordsCursor  
	WHILE 1 = 1   
	BEGIN
		FETCH NEXT FROM RecordsCursor INTO @Itr_ZZ_ID, @Itr_MAPPING_RULES_PDM_OBJ_ID, @Itr_MAPPING_RULES_PDM_FLD_ID
		IF @@FETCH_STATUS <> 0
			BREAK

		exec MIG_UPDATE_MAPPING_RULES_NEW_PDM_INFO_Sp @Itr_ZZ_ID, @Itr_MAPPING_RULES_PDM_OBJ_ID, @Itr_MAPPING_RULES_PDM_FLD_ID
	END
	CLOSE RecordsCursor

	--Insert template into the [MIG_MAPPING_TEMPLATES] table.
	exec MIG_MAPPING_TEMPLATES_Sp
END

-- exec MIG_GENERATE_MAPPING_RULES_Sp
-- SELECT * FROM MIG_MAPPING_RULES
