create or replace view public.vw_p01_to_p35 as
with base as (
	select * 
	, case 
		when xml_path = '/RDF/Collection/member/Concept' 
			then replace(replace(p.xml_data_value,'http://vocab.nerc.ac.uk/collection/P35/current/',''),'/','') 
		when xml_path = '/RDF/Collection/member/Concept/narrower' 
			then replace(replace(p.xml_data_value,'http://vocab.nerc.ac.uk/collection/P01/current/',''),'/','') 
		end 
		as pcode
	from public.p35xml p
	where p.xml_data_type_description  in ('CHARACTERS','ATTRIBUTE')
	and (
		xml_path in (
		 '/RDF/Collection/member/Concept/identifier'
		,'/RDF/Collection/member/Concept'
		,'/RDF/Collection/member/Concept/notation'
		,'/RDF/Collection/member/Concept/prefLabel'
		,'/RDF/Collection/member/Concept/definition'
		,'/RDF/Collection/member/Concept/narrower') 
	)
)
select p35.xml_row_number
, p35.pcode p35code
, plabel.xml_data_value as p35prefLabel
, pdef.xml_data_value as p35definition
, p01.pcode p01code
from base p35 
left join base p01 on p01.xml_parent_element_id = p35.xml_element_id
left join base plabel on plabel.xml_parent_element_id = p35.xml_element_id and plabel.xml_data_name in ('prefLabel')
left join base pdef on pdef.xml_parent_element_id = p35.xml_element_id and pdef.xml_data_name in ('definition')
where p01.pcode is not null
order by p35.pcode, p01.pcode;


create or replace view public.vw_p35_from_xml as
select p35code
, array_agg(p01code) as p01list
, min(p35prefLabel) as p35prefLabel
, min(p35definition) as p35definition
from vw_p01_to_p35 
group by p35code
order by 1;


create or replace view "public".vw_observationset_parameter as
with obsset as (
	-- observations set with array of p01-codes:
	select *, string_to_array(replace(p01codes, 'SDN:P01::',''), ' | ') as p01list	--, top.cdi, trim(unn.elem) as p01code, unn.nr
	from observationset 	--, unnest(top.p01array) with ordinality unn(elem, nr)
)
, obsset_unnested as (
	--	observation sets expanded (unnested) per p01code:
	select obsset.*, unn.p01code, p35.p35code
	from obsset, unnest(obsset.p01list) with ordinality unn(p01code, position_p01)
	/*left*/ join vw_p01_to_p35 p35 on p35.p01code=unn.p01code
)
, obsset_par as (
	-- observations grouped in observations sets per parameter:
	select odvfile_id, cdi_id, datetime, parameter_id
	from observation obs
	group by odvfile_id, cdi_id, datetime, parameter_id
)
select obsset_par.odvfile_id, obsset_par.cdi_id, obsset_par.datetime, obsset_par.parameter_id	--, par.identifier, obs_unn.p01code
, array_agg(obs_unn.p01code) as p01list
from obsset_par
left join parameter par on par.id=obsset_par.parameter_id
left join obsset_unnested obs_unn 
	on obs_unn.odvfile_id=obsset_par.odvfile_id
	and obs_unn.cdi_id=obsset_par.cdi_id
	and obs_unn.datetime=obsset_par.datetime
	and obs_unn.p35code=par.identifier	-- no match for parameters that are not a p35code
group by obsset_par.odvfile_id, obsset_par.cdi_id, obsset_par.datetime, obsset_par.parameter_id	--, par.identifier, obs_unn.p01code
;