-- SQL procedure to define the IKS service datatable  
-- Stephane Erard, LESIA/OVPDC, Nov 2015 (hand-written from older version & adapted from planet_views.sql)
-- Can be used as a template for other light services 

-- Complete version with two views (dataset summary + epn_core)

CREATE OR REPLACE VIEW iks.iks_dataset AS SELECT 
	TEXT 'dataset' 										AS resource_type ,
    TEXT '1' 											AS dataset_id,
	TEXT 'sp'	 										AS dataproduct_type ,
	TEXT '1P'	 										AS target_name ,
	array_to_string(array(select distinct target from iks.data_table), ' ') 	AS alt_target_name,
	TEXT 'comet' 										AS  target_class,
	CAST(NULL AS TEXT) 									AS  observation_id,
	CAST(min(distance) AS DOUBLE PRECISION)  			AS  target_distance_min,
	CAST(max(distance) AS DOUBLE PRECISION)				AS  target_distance_max,
	CAST(NULL AS DOUBLE PRECISION) 						AS  sun_distance,
	CAST(NULL AS DOUBLE PRECISION) 						AS  earth_distance,
	min(CAST(to_char(time_obs::date,'J') AS DOUBLE PRECISION)) 
														AS  time_min,
	max(CAST(to_char(time_obs::date,'J') AS DOUBLE PRECISION)) 
														AS  time_max,
	TEXT 'UTC' 											AS  time_scale,
	CAST(NULL AS DOUBLE PRECISION) 						AS  time_sampling_step_min,
	CAST(NULL AS DOUBLE PRECISION) 						AS  time_sampling_step_max,
	CAST(18. AS DOUBLE PRECISION)	  					AS  time_exp_min,
	CAST(36. AS DOUBLE PRECISION) 						AS  time_exp_max,
	CAST(min(2.99792458E14/sp_max) AS DOUBLE PRECISION) 		AS  spectral_range_min,
	CAST(max(2.99792458E14/sp_min) AS DOUBLE PRECISION) 		AS  spectral_range_max,
	CAST(min(2.99792458E14*(sp_step_min+sp_step_max)/2/(sp_max)^2) AS DOUBLE PRECISION)	AS spectral_sampling_step_min,
	CAST(max(2.99792458E14*(sp_step_min+sp_step_max)/2/(sp_min)^2) AS DOUBLE PRECISION)	AS spectral_sampling_step_max,
	
	CAST(min(2.99792458E14*(sp_res_min+sp_res_max)/2/(sp_max)^2) AS DOUBLE PRECISION)	AS spectral_resolution_min,
	CAST(max(2.99792458E14*(sp_res_min+sp_res_max)/2/(sp_min)^2) AS DOUBLE PRECISION)	AS spectral_resolution_max,
	CAST('0' AS DOUBLE PRECISION)  						AS  c1min,
	CAST('360' AS DOUBLE PRECISION) 					AS  c1max,
	CAST('-90' AS DOUBLE PRECISION) 					AS  c2min,
	CAST('90' 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,
	CAST(NULL AS DOUBLE PRECISION)  					AS  incidence_min,
	CAST(NULL AS DOUBLE PRECISION) 						AS  incidence_max,
	CAST(NULL AS DOUBLE PRECISION)  					AS  emergence_min,
	CAST(NULL AS DOUBLE PRECISION) 						AS  emergence_max,
	CAST(min(phase_ang) AS DOUBLE PRECISION)	  					AS  phase_min,
	CAST(max(phase_ang) AS DOUBLE PRECISION) 						AS  phase_max,

	array_to_string(array(select distinct inst_host_name from iks.data_table), ' ')		AS  instrument_host_name,
	array_to_string(array(select distinct inst_name from iks.data_table), ' ') 	AS  instrument_name,
	TEXT 'phot.flux.density' 							AS  measurement_type,
	CAST(NULL AS TEXT) 									AS  access_url,
	CAST(NULL AS TEXT) 									AS  access_format,
	CAST(NULL AS integer) 								AS  access_estsize,
	CAST(NULL AS TEXT) 									AS  native_access_url,
	CAST(NULL AS TEXT) 									AS  native_access_format,
	CAST(NULL AS TEXT) 									AS  file_name,
	-- array_to_string(array(select distinct filename from iks.data_table), ' ') 	AS  file_name,
	TEXT 'http://cdsads.u-strasbg.fr/abs/1988Icar...76..404C' 					AS  bib_reference,
	TEXT '2013-11-17T10:41:00.00'  					AS  creation_date,
	TEXT '2013-11-17T10:41:00.00'  					AS  modification_date,
	TEXT '3' 										AS  processing_level
	FROM IKS.data_table;

CREATE OR REPLACE VIEW iks.epn_core AS SELECT
	--CAST(tctable.id AS INTEGER) 					AS index,
	CAST(id AS INTEGER) AS index,
	TEXT 'granule'								AS resource_type,
	TEXT '1'									AS dataset_id,
	TEXT 'sp'  									AS dataproduct_type,
	TEXT '1P'	 								AS target_name ,
	CAST(target AS TEXT)						AS alt_target_name,
	TEXT 'comet'								AS target_class,
	CAST(observation_id AS TEXT) 						AS  observation_id,
	distance		 									AS  target_distance_min,
	distance		 									AS  target_distance_max,
	sdistance		 									AS  sun_distance,
	edistance		 									AS  earth_distance,
	-- CAST(to_char(datetime::date,'J') AS double precision) 
    --    cast(to_char(time_::timestamp,'J') as double precision) + EXTRACT(EPOCH FROM time_::time)/86400 -0.5 AS time_min,
    --    cast(to_char(time_::timestamp,'J') as double precision) + EXTRACT(EPOCH FROM time_::time)/86400   -0.5 AS time_max,
	-- CAST(to_char(date::date,'J') AS double precision) + texptime/3600 AS time_max,
--	CAST(to_char(time_obs::date,'J') AS DOUBLE PRECISION)  AS  time_min,
--	CAST(to_char(time_obs::date,'J') AS DOUBLE PRECISION)  AS  time_max,
-- next 2 lines modified 14/5/2015
	cast(to_char(time_obs::timestamp,'J') as double precision) + EXTRACT(EPOCH FROM time_obs::timestamp::time)/86400 -0.5 AS time_min,
	cast(to_char(time_obs::timestamp,'J') as double precision) + EXTRACT(EPOCH FROM time_obs::timestamp::time)/86400 -0.5 AS time_max,


	TEXT 'UTC' 												AS  time_scale,
	CAST(NULL AS DOUBLE PRECISION)							AS time_sampling_step_min,
	CAST(NULL AS DOUBLE PRECISION)							AS time_sampling_step_max,
	CAST(exp_time AS DOUBLE PRECISION)  					AS  time_exp_min,
	CAST(exp_time AS DOUBLE PRECISION) 						AS  time_exp_max,
	CAST((2.99792458E14/sp_max) AS DOUBLE PRECISION) 		AS  spectral_range_min,
	CAST((2.99792458E14/sp_min) AS DOUBLE PRECISION) 		AS  spectral_range_max,	
	CAST((2.99792458E14*(sp_step_min+sp_step_max)/2/(sp_max)^2) AS DOUBLE PRECISION)	AS spectral_sampling_step_min,
	CAST((2.99792458E14*(sp_step_min+sp_step_max)/2/(sp_min)^2) AS DOUBLE PRECISION)	AS spectral_sampling_step_max,

	CAST((2.99792458E14*(sp_res_min+sp_res_max)/2/(sp_max)^2) AS DOUBLE PRECISION)	AS spectral_resolution_min,
	CAST((2.99792458E14*(sp_res_min+sp_res_max)/2/(sp_min)^2) 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,
	CAST(NULL AS DOUBLE PRECISION) as incidence_min,
	CAST(NULL AS DOUBLE PRECISION) as incidence_max,
	CAST(NULL AS DOUBLE PRECISION) as emergence_min,
	CAST(NULL AS DOUBLE PRECISION) as emergence_max,
	CAST(phase_ang AS DOUBLE PRECISION)	 as phase_min,
	CAST(phase_ang AS DOUBLE PRECISION)	 as phase_max,
	array_to_string(array(select distinct inst_host_name from iks.data_table), ' ')	AS  instrument_host_name,
	array_to_string(array(select distinct inst_name from iks.data_table), ' ') 		AS  instrument_name,
	TEXT 'phot.flux.density'					AS measurement_type,
	--	CAST('http://voparis-srv.obspm.fr/vo/planeto/iks/' || filename AS character varying)	AS access_url,
	CAST(a_url AS TEXT)	AS access_url,
	CAST(a_format AS TEXT)		AS access_format,
	INTEGER '19'				AS access_estsize,
	CAST(o_url AS TEXT)			AS native_access_url,
	CAST(o_format AS TEXT)		AS native_access_format,
	CAST(NULL AS TEXT) 			AS file_name,
	CAST(ref AS TEXT) 			AS bib_reference,
	TEXT '2013-11-17T10:41:00.00'  AS  creation_date,
	TEXT '2013-11-17T10:41:00.00'  AS  modification_date,
	integer '3'					AS processing_level
FROM IKS.data_table UNION ALL SELECT
	CAST('1' AS INTEGER) AS index,
	resource_type AS resource_type,
	TEXT '1' AS dataset_id,
	dataproduct_type AS dataproduct_type,
	target_name AS target_name,
	alt_target_name AS alt_target_name,
	target_class AS target_class,
	observation_id  AS  observation_id,
	target_distance_min		AS  target_distance_min,
	target_distance_max		AS  target_distance_max,
	sun_distance		AS  sun_distance,
	earth_distance		AS  earth_distance,
	time_min AS time_min,
	time_max AS time_max,
	TEXT 'UTC' 			AS  time_scale,
	time_sampling_step_min AS time_sampling_step_min,
	time_sampling_step_max AS time_sampling_step_max,
	time_exp_min AS time_exp_min,
	time_exp_max AS time_exp_max,
	spectral_range_min AS spectral_range_min,
	spectral_range_max AS spectral_range_max,
	spectral_sampling_step_min AS spectral_sampling_step_min,
	spectral_sampling_step_max AS spectral_sampling_step_max,
	spectral_resolution_min AS spectral_resolution_min,
	spectral_resolution_max AS spectral_resolution_max,
	c1min AS c1min,
	c1max AS c1max,
	c2min AS c2min,
	c2max AS c2max,
	c3min AS c3min,
	c3max AS c3max,
	c1_resol_min AS c1_resol_min,
	c1_resol_max AS c1_resol_max,
	c2_resol_min AS c2_resol_min,
	c2_resol_max AS c2_resol_max,
	c3_resol_min AS c3_resol_min,
	c3_resol_max AS c3_resol_max,
	spatial_frame_type AS spatial_frame_type,
	CAST(NULL AS DOUBLE PRECISION) as incidence_min,
	CAST(NULL AS DOUBLE PRECISION) as incidence_max,
	CAST(NULL AS DOUBLE PRECISION) as emergence_min,
	CAST(NULL AS DOUBLE PRECISION) as emergence_max,
	CAST(phase_min AS DOUBLE PRECISION)	 as phase_min,
	CAST(phase_max AS DOUBLE PRECISION)	 as phase_max,
	lower(instrument_host_name) AS instrument_host_name,
	instrument_name AS instrument_name,
	measurement_type AS measurement_type,
	access_url AS access_url,
	access_format AS access_format,
	access_estsize AS access_estsize,
	native_access_url AS native_access_url,
	native_access_format AS native_access_format,
	file_name AS file_name,
	bib_reference AS bib_reference,
	TEXT '2013-11-17T10:41:00.00'  		AS  creation_date,
	TEXT '2013-11-17T10:41:00.00'  		AS  modification_date,
	integer '3' AS processing_level
	FROM iks.iks_dataset;
	

GRANT ALL PRIVILEGES ON SCHEMA public TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA public TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON iks.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON iks.epn_core to gavo WITH GRANT OPTION; 


