
CREATE OR REPLACE VIEW  apis.apis_dataset AS SELECT 
	TEXT 'dataset' 										AS resource_type ,
	regexp_replace(regexp_replace(
		array_to_string(array(select distinct obstype from apis.apis_products p2 where p1.dataset_id = p2.dataset_id), ' '), 
		'IMAGING','im'),'SPECTROSCOPIC','sp') 	AS  dataproduct_type ,
	regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace( array_to_string(array(select distinct targname from apis.apis_products p3 where p1.dataset_id = p3.dataset_id ), ' '), 'JUPITER','Jupiter'), 'SATURN', 'Saturn'), 'URANUS', 'Uranus'), 'IO', 'Io'), 'EUROPA', 'Europa'), 'GANYMEDE', 'Ganymede'), 'TITAN', 'Titan') AS target_name ,
   dataset_id AS dataset_id,
   dataset_index AS dataset_index,
	TEXT 'planet' 										AS  target_class,
	min(CAST(to_char(datetime::date,'J') AS DOUBLE PRECISION)) 
														AS  t_min,
	max(CAST(to_char(date::date,'J') AS DOUBLE PRECISION) + texptime/86400) 
														AS t_max,
	TEXT 'UTC' 											AS  t_scale,
	CAST(NULL AS DOUBLE PRECISION) 						AS  t_sampling_step_min,
	CAST(NULL AS DOUBLE PRECISION) 						AS  t_sampling_step_max,
	CAST(min(texptime) AS DOUBLE PRECISION)  		AS  t_exp_min,
	CAST(max(texptime) AS DOUBLE PRECISION) 		AS  t_exp_max,
	CAST(min(3E18/maxwave) AS DOUBLE PRECISION) 		AS  spectral_range_min,
	CAST(max(3E18/minwave) AS DOUBLE PRECISION) 		AS  spectral_range_max,
	CAST(min(3E18/spectral_step) AS DOUBLE PRECISION) 						AS  spectral_sampling_step_min,
	CAST(max(3E18/spectral_step) AS DOUBLE PRECISION) 						AS  spectral_sampling_step_max,
	CAST(least(min(3E18/spectral_step),min(3E18/bandwid)) AS DOUBLE PRECISION) 						AS  spectral_resolution_min,
	CAST(greatest(max(3E18/spectral_step),min(3E18/bandwid)) AS DOUBLE PRECISION) 						AS  spectral_resolution_max,
	CAST(NULL AS DOUBLE PRECISION)  						AS  c1min,
	CAST(NULL AS DOUBLE PRECISION) 					AS  c1max,
	CAST(NULL AS DOUBLE PRECISION) 					AS  c2min,
	CAST(NULL AS DOUBLE PRECISION) 					AS  c2max,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c3min,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c3max,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c1_resol_min,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c1_resol_max,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c2_resol_min,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c2_resol_max,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c3_resol_min,
	CAST(NULL AS DOUBLE PRECISION)  					AS  c3_resol_max,
	TEXT 'body' 										AS  spatial_frame_type,
	array_to_string(array(select distinct telescop from apis.apis_products p4 where p1.dataset_id = p4.dataset_id), ' ')		AS  instrument_host_name,
	array_to_string(array(select distinct detector from apis.apis_products p5 where p1.dataset_id = p5.dataset_id), ' ') 	AS  instrument_name,
	TEXT 'obs.image' 									AS  measurement_type,
	CAST(NULL AS TEXT) 									AS  access_url,
	CAST(NULL AS TEXT) 									AS  access_format,
	CAST(NULL AS integer) 								AS  access_estsize,
	processing_level 									AS  processing_level,
	TEXT 'vo paris data centre on behalf of LESIA' 		AS  publisher,
	TEXT 'no information' 								AS  reference,
	TEXT 'Aurora Planetary Imaging and Spectroscopy' 	AS  title,
	TEXT 'no information' 								AS  collection_id,
	TEXT 'planetary aurorae' 							AS target_region
	FROM apis.apis_products p1 GROUP BY dataset_id, dataset_index, processing_level;

\i epn_core.sql
