DROP PROCEDURE IF EXISTS public.sp_t_ehr_type_medical_devices_load;
DROP FUNCTION IF EXISTS public.sp_t_ehr_type_medical_devices_load;

CREATE PROCEDURE public.sp_t_ehr_type_medical_devices_load("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
DECLARE 
    T xml;
    tempStrXml text;
BEGIN
	
	select convert_from("Xml", 'UTF-8') into tempStrXml;	
	select replace(tempStrXml, 'encoding="UTF-8"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_implant_load;

	CREATE TEMP TABLE temp_implant_load AS
		SELECT 
		"ORDER"::int,
		COALESCE(EXISTENCE_NPA::boolean, false) AS EXISTENCE_NPA,
		try_cast(btrim(RZN), NULL::int) AS RZN,
		PARENT,
		ID::int,
		COALESCE(ACTUAL::boolean, false) AS ACTUAL,
		NAME,
		NULL::uuid AS PARENT_EhrTypeMedicalDevicesID
	FROM XMLTABLE 
	(
		'/book/entries/entry' PASSING T 
		COLUMNS 
			"ORDER" text PATH './ORDER',
			EXISTENCE_NPA text PATH './EXISTENCE_NPA',
			RZN text PATH './RZN',
			PARENT text PATH './PARENT', 
			ID text PATH './ID',
			ACTUAL text PATH './ACTUAL',
			NAME text PATH './NAME'
	);
	
	INSERT INTO IES.T_EHR_TYPE_MEDICAL_DEVICES
	(
		EhrTypeMedicalDevicesID,
		"order",
		EXISTENCE_NPA,
		RZN,
		PARENT,
		ID,
		ACTUAL,
		NAME
	)
	SELECT 
		gen_random_uuid(),
		t1."ORDER",
		t1.EXISTENCE_NPA,
		t1.RZN,
		NULL::uuid,
		t1.ID,
		t1.ACTUAL,
		t1.NAME
	FROM temp_implant_load t1
	WHERE NOT EXISTS (SELECT 1 FROM IES.T_EHR_TYPE_MEDICAL_DEVICES t2 WHERE t2.ID = t1.ID);
	
	UPDATE temp_implant_load t1
	SET PARENT_EhrTypeMedicalDevicesID = t_parent.EhrTypeMedicalDevicesID
	FROM IES.T_EHR_TYPE_MEDICAL_DEVICES t_parent
	WHERE
		t1.PARENT IS NOT NULL
		AND btrim(t1.PARENT) <> ''
		AND t_parent.ID = try_cast(btrim(t1.PARENT), NULL::int);
	
	UPDATE IES.T_EHR_TYPE_MEDICAL_DEVICES t2 
	SET 
		"order" = t1."ORDER",
		EXISTENCE_NPA = t1.EXISTENCE_NPA,
		RZN = t1.RZN,
		PARENT = t1.PARENT_EhrTypeMedicalDevicesID,
		ACTUAL = t1.ACTUAL,
		NAME = t1.NAME
	FROM temp_implant_load t1
	WHERE t1.ID = t2.ID;
	
	UPDATE IES.T_EHR_TYPE_MEDICAL_DEVICES t2
	SET PARENT = t1.PARENT_EhrTypeMedicalDevicesID
	FROM temp_implant_load t1
	WHERE t1.ID = t2.ID
		AND t1.PARENT_EhrTypeMedicalDevicesID IS NOT NULL
		AND t2.PARENT IS NULL;
	
	DROP TABLE IF EXISTS temp_implant_load;
	
	RETURN;
END
$BODY$;
