DROP FUNCTION IF EXISTS sp_v019methodload;
DROP PROCEDURE IF EXISTS sp_v019methodload;

CREATE PROCEDURE public.sp_v019methodload(
	"Xml" bytea,
	"FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
	--меняем кодировку исходного файла на кодировку базы данных, убираем указание на старую кодировку
	xml_doc xml = REPLACE(convert_from("Xml", 'WIN1251'), 'encoding="windows-1251"', '') :: xml;
BEGIN
	DROP TABLE IF EXISTS temp_v019;

	-- парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_v019 AS
	SELECT 
		idhm,
		hmname,
		diag,
		hvid,
		CASE WHEN  hgr  = '' THEN NULL ELSE hgr::INT END ,
		hmodp,
		CASE WHEN idmodp in ('0','') THEN NULL ELSE idmodp::INT END,
		CASE WHEN  datebeg  = '' THEN NULL ELSE datebeg::TIMESTAMP END , 
		CASE WHEN  dateend  = '' THEN NULL ELSE dateend::TIMESTAMP END 
	FROM
		XMLTABLE ('/packet/zap' PASSING xml_doc COLUMNS 
			idhm INT path './IDHM',
			hmname TEXT path './HMNAME',
			diag TEXT path './DIAG',
			hvid TEXT path './HVID',
			hgr TEXT path './HGR',
			hmodp TEXT path './HMODP',
			idmodp TEXT path './IDMODP',
			datebeg TEXT path './DATEBEG', 
			dateend TEXT path './DATEEND'
		)
	;
	
	--готовим выборку для обновления
	WITH upd AS (
		SELECT
			tmp.*,
			v022.v022modpacid
		FROM
			temp_v019 AS tmp
		LEFT JOIN LATERAL (
			SELECT
				v022.v022modpacid
			FROM
				ies.t_v022_modpac AS v022
			WHERE
				tmp.idmodp = v022.idmpac
				AND tmp.datebeg >= v022.datebeg
				AND (tmp.datebeg <= v022.dateend OR v022.dateend IS NULL)
			LIMIT 1
		) AS v022 ON TRUE
	)
	--обновляем существующие записи
	UPDATE ies.t_v019_vmp_method AS v019
	SET	
		hvid = upd.hvid,
		dateend = upd.dateend,
		hmname = upd.hmname,
		diag = upd.diag,
		hmodp = upd.hmodp
	FROM
		upd
	WHERE
		v019.idhm = upd.idhm
		AND v019.datebeg = upd.datebeg
		AND coalesce(v019.hgr,-1) = coalesce(upd.hgr,-1)
		AND (
			v019.v022modpac = upd.v022modpacid 
			OR (v019.v022modpac IS NULL AND upd.v022modpacid IS NULL)
		)
	;

	--вставляем новые записи, если они есть
	INSERT INTO ies.t_v019_vmp_method (
		v019vmpmethodid,
		idhm,
		hmname,
		diag,
		hvid,
		datebeg,
		dateend,
		hgr,
		hmodp,
		v022modpac
	)
	SELECT
		gen_random_uuid(),
		tmp.idhm,
		tmp.hmname,
		tmp.diag,
		tmp.hvid,
		tmp.datebeg,
		tmp.dateend,
		tmp.hgr,
		tmp.hmodp,
		v022.v022modpacid
	FROM
		temp_v019 AS tmp
	LEFT JOIN LATERAL (
		SELECT
			v022modpacid
		FROM
			ies.t_v022_modpac AS v022
		WHERE
			tmp.idmodp = v022.idmpac
			AND tmp.datebeg >= v022.datebeg
			AND (tmp.datebeg <= v022.dateend OR v022.dateend IS NULL)
	) AS v022 ON TRUE
	LEFT JOIN
		ies.t_v019_vmp_method AS v019
		ON v019.idhm = tmp.idhm
		AND v019.datebeg = tmp.datebeg
		AND coalesce(v019.hgr,-1) = coalesce(tmp.hgr,-1)
		AND (
			v019.v022modpac = v022.v022modpacid
			OR (v019.v022modpac IS NULL AND v022.v022modpacid IS NULL)
		)
	WHERE
		v019.v019vmpmethodid IS NULL
	;
	
	DROP TABLE IF EXISTS tmp_v019;		
END;
$BODY$;