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

CREATE PROCEDURE public.sp_f006videxp (
	"Xml" bytea,
	"FileName" character varying
)
LANGUAGE plpgsql
AS $BODY$
DECLARE
	--меняем исходную кодировку на кодировку бд, убираем указание на старую кодировку
	xml_doc XML = REPLACE(convert_from("Xml", 'WIN1251'), 'encoding="windows-1251"', '') :: XML
	;
	date_zglv TIMESTAMP = (xpath('/packet/zglv/date/text()', xml_doc))[1] :: TEXT
	;

BEGIN
	DROP TABLE IF EXISTS temp_f006;
	
	--парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_f006 AS
	SELECT
		idvid,
		vidname,
		CASE 
			WHEN datebeg = '' 
				THEN date_zglv :: TIMESTAMP 
			ELSE 
				datebeg :: TIMESTAMP  
		END AS datebeg,
		CASE 
			WHEN dateend = '' 
				THEN NULL
			ELSE
				dateend :: TIMESTAMP
		END
	FROM
		XMLTABLE ('/packet/zap' PASSING xml_doc COLUMNS
			idvid INT PATH './IDVID',
			vidname TEXT PATH './VIDNAME',
			datebeg TEXT PATH './DATEBEG',
			dateend TEXT PATH './DATEEND'
		)
	;

	--обновляем существующие записи
	UPDATE ies.t_f006_control_type AS f006
	SET
		vidname = tmp.vidname,
		dateend = tmp.dateend
	FROM
		temp_f006 AS tmp
	WHERE
		f006.idvid = tmp.idvid
		AND f006.datebeg = tmp.datebeg
		AND f006.migratedentityname IS NULL
	;

	--вставляем новые записи, если они есть
	INSERT INTO ies.t_f006_control_type (
		idvid,
		vidname,
		datebeg,
		dateend,
		f006controltypeid
	)
	SELECT
		tmp.idvid,
		tmp.vidname,
		tmp.datebeg,
		tmp.dateend,
		gen_random_uuid()
	FROM
		temp_f006 AS tmp
	LEFT JOIN
		ies.t_f006_control_type AS f006
		ON f006.idvid = tmp.idvid
		AND f006.datebeg = tmp.datebeg
		AND f006.migratedentityname IS NULL
	WHERE
		f006.idvid IS NULL
	;
	
	DROP TABLE IF EXISTS temp_f006;		
END;
$BODY$;