DROP PROCEDURE IF EXISTS sp_copyAnalysisTemplate;
CREATE PROCEDURE sp_copyAnalysisTemplate
(
	"RootID" uuid
,	"xmlstring" text
,	inout "xml" xml
)
AS
$BODY$
Declare	_xml xml;
Begin
	_xml := replace("xmlstring", '<?xml version="1.0" encoding="UTF-8"?>', '')::xml;
	if "RootID" is not null
	then
		select
		xmlelement
		(
			name "AnalysisTemplates"
		,	(
			select
			xmlagg
			(
				XMLELEMENT
				(
					name "TemplateData"
				,	XMLELEMENT
					(
						name body
					,	xmlforest
						(
							t.AnalysisTemplateID as "AnalysisTemplateID"
				   		,	t.TreeLevel as "TreeLevel"
				   		,	t.ParentID as "ParentID"
				   		,	t.Name as "Name"
				   		,	t.CalcLexem as "CalcLexem"
				   		,	t.AnalysisMeasurement as "AnalysisMeasurement"
				   		,	t.ValidateLexem as "ValidateLexem"
				   		,	t.NormativLexem as "NormativLexem"
				   		,	t.ValueType as "ValueType"
				   		,	t.NormativText as "NormativText"
				   		,	t.FormWidth as "FormWidth"
				   		,	t.FormHeight as "FormHeight"
				   		,	t.ColumnsConfig as "ColumnsConfig"
				   		,	t.ColumnCount as "ColumnCount"
				   		,	t.TabOrder as "TabOrder"
				   		,	t.ShowBorder as "ShowBorder"
				   		,	t.RootParent as "RootParent"
				   		,	t.CheckedState as "CheckedState"
				   		,	t.CalcReadOnly as "CalcReadOnly"
				   		,	m.AnalysisMeasurementID as "AnalysisMeasurementID"
				   		,	m.Name as "AnalysisMeasurementName"
				   		,	m.ShortName as "ShortName"
						)
					)
				,	xmlelement
					(
						name "AnalysisCodes"
					,	(
							select
							xmlagg
							(
								xmlelement
								(
									name "AnalysisCode"
								,	xmlforest
									(
										c.AnalysisCodesID as "AnalysisCodesID"
				   					,	c.Code as "Code"
				   					,	c.CodeSystem as "CodeSystem"
				   					,	c.CodeSystemName as "CodeSystemName"
				   					,	c.AnalysisTemplate as "AnalysisTemplate"											
									)
								)
							)
							from AKUZ.T_ANALYSIS_CODES c
							where c.AnalysisTemplate = t.AnalysisTemplateID 									
						)
					)					
				)
			)
			from AKUZ.T_ANALYSIS_TEMPLATE t 
			left join 
				AKUZ.T_ANALYSIS_MEASUREMENT m 
				on t.AnalysisMeasurement = m.AnalysisMeasurementID			
			where t.RootParent = '63FB7BAF-5D59-4884-BD23-6CEA1FF9C018'::uuid
			)
		);
	elsif _xml is not null
	then		
		-- Generate table #ANALYSIS_TEMPLATE
		Drop Table If Exists TmpANALYSIS_TEMPLATE;
		Create Temp Table TmpANALYSIS_TEMPLATE
		as
		SELECT	(xpath('/body[1]/AnalysisTemplateID[1]/text()', x))[1]::text::UUID as AnalysisTemplateID
		,	(xpath('/body[1]/TreeLevel[1]/text()', x))[1]::text::int as TreeLevel
		,	(xpath('/body[1]/ParentID[1]/text()', x))[1]::text::uuid as ParentID
		,	(xpath('/body[1]/Name[1]/text()', x))[1]::varchar(255) as Name
		,	(xpath('/body[1]/CalcLexem[1]/text()', x))[1]::text as CalcLexem
		,	(xpath('/body[1]/AnalysisMeasurement[1]/text()', x))[1]::text::uuid as AnalysisMeasurement
		,	(xpath('/body[1]/ValidateLexem[1]/text()', x))[1]::text as ValidateLexem
		,	(xpath('/body[1]/NormativLexem[1]/text()', x))[1]::text as NormativLexem							
		,	(xpath('/body[1]/ValueType[1]/text()', x))[1]::text::int as ValueType
		,	(xpath('/body[1]/NormativText[1]/text()', x))[1]::varchar(255) as NormativText
		,	(xpath('/body[1]/FormWidth[1]/text()', x))[1]::text::int as FormWidth
		,	(xpath('/body[1]/FormHeight[1]/text()', x))[1]::text::int as FormHeight
		,	(xpath('/body[1]/ColumnsConfig[1]/text()', x))[1]::varchar(8) as ColumnsConfig
		,	(xpath('/body[1]/ColumnCount[1]/text()', x))[1]::text::int as ColumnCount
		,	(xpath('/body[1]/TabOrder[1]/text()', x))[1]::text::int as TabOrder
		,	(xpath('/body[1]/ShowBorder[1]/text()', x))[1]::text::boolean as ShowBorder
		,	(xpath('/body[1]/RootParent[1]/text()', x))[1]::text::uuid as RootParent
		,	(xpath('/body[1]/CheckedState[1]/text()', x))[1]::text::boolean as CheckedState							
		,	(xpath('/body[1]/CalcReadOnly[1]/text()', x))[1]::text::boolean as CalcReadOnly
		from unnest(xpath('/AnalysisTemplates/TemplateData/body', _xml)) x;
		
		-- Generate table #ANALYSIS_MEASURMENT
		Drop Table If Exists TmpANALYSIS_MEASURMENT;
		Create Temp Table TmpANALYSIS_MEASURMENT
		as
		SELECT	(xpath('/body[1]/AnalysisMeasurementID[1]/text()', x))[1]::text::UUID as AnalysisMeasurementID
		,	(xpath('/body[1]/AnalysisMeasurementName[1]/text()', x))[1]::varchar(255) as AnalysisMeasurementName
		,	(xpath('/body[1]/ShortName[1]/text()', x))[1]::varchar(255) as ShortName
		from unnest(xpath('/AnalysisTemplates/TemplateData/body', _xml)) x;
		
		-- Generate table #ANALYSIS_CODES
		Drop Table If Exists TmpANALYSIS_CODES;
		Create Temp Table TmpANALYSIS_CODES
		as
		SELECT	(xpath('/AnalysisCode[1]/AnalysisMeasurementID[1]/text()', x))[1]::text::UUID as AnalysisMeasurementID
		,	(xpath('/AnalysisCode[1]/AnalysisMeasurementName[1]/text()', x))[1]::varchar(255) as AnalysisMeasurementName
		,	(xpath('/AnalysisCode[1]/ShortName[1]/text()', x))[1]::varchar(255) as ShortName
		from unnest(xpath('/AnalysisTemplates/TemplateData/AnalysisCodes/AnalysisCode', _xml)) x;

		--normalize
	 	with cte as
	 	(
			select *, ROW_NUMBER() OVER(partition by m.Name, m.ShortName,m.AnalysisMeasurementID order by (select 1)) as rn 
			from TmpANALYSIS_MEASUREMENT m
	 	)
	 	delete from cte c
	 	where c.rn <> 1 or c.AnalysisMeasurementID is null;

	 	--insert 
	 	INSERT INTO AKUZ.T_ANALYSIS_MEASUREMENT(AnalysisMeasurementID, Name, ShortName)   
     	select m.AnalysisMeasurementID, m.Name, m.ShortName
		from TmpANALYSIS_MEASUREMENT m
	 	where not exists
		(
			select* from AKUZ.T_ANALYSIS_MEASUREMENT cm where cm.Name = m.Name and cm.ShortName = m.ShortName or cm.AnalysisMeasurementID = m.AnalysisMeasurementID
		);

	 	INSERT INTO AKUZ.T_ANALYSIS_TEMPLATE(AnalysisTemplateID, TreeLevel, ParentID, Name, CalcLexem, AnalysisMeasurement, ValidateLexem, NormativLexem, ValueType
									, NormativText, FormWidth, FormHeight, ColumnsConfig, ColumnCount, TabOrder, ShowBorder, RootParent, CheckedState, CalcReadOnly)
     	select t.AnalysisTemplateID, t.TreeLevel, t.ParentID, t.Name, t.CalcLexem, cm.AnalysisMeasurementID, t.ValidateLexem, t.NormativLexem, t.ValueType
		, t.NormativText, t.FormWidth, t.FormHeight, t.ColumnsConfig, t.ColumnCount, t.TabOrder, t.ShowBorder, t.RootParent, t.CheckedState, t.CalcReadOnly
		from TmpANALYSIS_TEMPLATE t
		left join 
			TmpANALYSIS_MEASUREMENT xm 
			on xm.AnalysisMeasurementID = t.AnalysisMeasurement
		left join lateral
		(
			select cm.AnalysisMeasurementID from AKUZ.T_ANALYSIS_MEASUREMENT cm where cm.Name = xm.Name and cm.ShortName = xm.ShortName limit 1
		) as cm on true
		order by t.TreeLevel;

	 	INSERT INTO AKUZ.T_ANALYSIS_CODES(AnalysisCodesID, Code, CodeSystem, CodeSystemName, AnalysisTemplate)
     	select AnalysisCodesID, Code, CodeSystem, CodeSystemName, AnalysisTemplate
		from TmpANALYSIS_CODES;

		drop table if exists TmpANALYSIS_TEMPLATE;
	 	drop table if exists TmpANALYSIS_MEASUREMENT;
	 	drop table if exists TmpANALYSIS_CODES;
		
	end if;
end;
$BODY$
  LANGUAGE plpgsql;
