drop view if exists apis.epn_core;
CREATE OR REPLACE VIEW apis.epn_core AS SELECT
       REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(access_url,'.*/', ''),'.fits',''),'.pdf','_pdf') as granule_uid,
       REGEXP_REPLACE(access_url,'.*/', '') as file_name,
       -- case dataset_id	
       --    when 'original_data' then replace(native_filename,'.fits','_x2d')
       --    when 'processed_data' then replace(native_filename,'.fits','_proc')
       --    when 'cylindric_proj' then replace(native_filename,'.fits','_cyl')
       --    when 'polar_proj_north' then replace(native_filename,'.fits','_pol_n')
        --   when 'polar_proj_south' then replace(native_filename,'.fits','_pol_s')
       -- ELSE CAST(NULL AS TEXT) END
	-- as granule_uid,
   --  rootname pour obs_id
        dataset_id AS granule_gid,  
        cast(rootname as text)  AS obs_id,
	regexp_replace(regexp_replace(regexp_replace(obstype,
		'SPECTROIMAGING', 'sc'),'IMAGING','im'),'SPECTROSCOPIC','sp') 
												AS dataproduct_type,
	initcap(targname)	AS target_name,
    case targname 
      when 'IO' then text 'satellite'    
      when 'EUROPA' then text 'satellite'    
      when 'GANYMEDE' then text 'satellite'    
      when 'CALLISTO' then text 'satellite'    
      when 'TITAN' then text 'satellite'    
      ELSE cast('planet' as text) END as target_class,
   cast(to_char(datetime::timestamp,'J') as double precision) + EXTRACT(EPOCH FROM datetime::time)/86400 -0.5 AS time_min,
   cast(to_char(datetime::timestamp,'J') as double precision) + EXTRACT(EPOCH FROM datetime::time)/86400 + texptime/86400  -0.5 AS time_max,
	CAST(NULL AS DOUBLE PRECISION)										AS time_sampling_step_min,
	CAST(NULL AS DOUBLE PRECISION)										AS time_sampling_step_max,
	texptime								AS time_exp_min,
	texptime								AS time_exp_max,
	2.99792458E18/maxwave								AS spectral_range_min,
	2.99792458E18/minwave								AS spectral_range_max,
	CAST(2.99792458E18/spectral_step AS DOUBLE PRECISION)				AS spectral_sampling_step_min,
	CAST(2.99792458E18/spectral_step AS DOUBLE PRECISION)				AS spectral_sampling_step_max,
	CAST(coalesce(2.99792458E18/spectral_step,2.99792458E18/bandwid) AS DOUBLE PRECISION)		AS spectral_resolution_min,
	CAST(coalesce(2.99792458E18/spectral_step,2.99792458E18/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,
	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,
	phase as phase_min,
	phase as phase_max,
	lower(telescop)									AS instrument_host_name,
	cast(instrume as text)									AS instrument_name,
	measurement_type AS  measurement_type,
	TEXT 'http://voparis-srv.obspm.fr/vo/planeto/apis/dataset/' || access_url		AS access_url,
    case SUBSTR(access_url,length(access_url) - 2,3)
    when 'pdf' then TEXT 'application/pdf'
    when 'its' then TEXT 'image/fits'
    ELSE CAST(NULL AS TEXT) END 
        AS access_format,
	INTEGER '22000'								AS access_estsize,
	processing_level							AS processing_level,
	TEXT 'PADC on behalf of LESIA' 				AS publisher,
	TEXT '2015A&C....11..138L'					AS bib_reference,
	TEXT 'APIS' 								AS service_title,
	CAST(NULL AS TEXT) 							AS original_service_title,
	TEXT 'magnetosphere'						AS target_region,
	TEXT 'planetary aurorae'					AS feature_name,
	ra_targ										AS ra,
	dec_targ									AS dec,
    last_update as release_date,
    date_processed AS creation_date,
    modified_at AS modification_date,

	CAST(NULL AS spoly) AS s_region,
    case (thumbnail_url ~ '^http')
    when true then
	thumbnail_url
    else
	TEXT 'http://voparis-srv.obspm.fr/vo/planeto/apis/dataset/' || thumbnail_url 
    end
	as thumbnail_url,
	TEXT 'http://apis.obspm.fr/spip.php?page=observation&id=' || apis.apis_products.data_id as external_link,
	obsmode				AS obs_mode,
	detector			AS detector_name,
	opt_elem			AS opt_elem,
	filter				AS filter,
	orientat			AS orientation,
	bunit				AS measurement_unit,
	proposid			AS proposal_id,
	pr_inv_l			AS proposal_pi,
	propttl1			AS proposal_title,
	platesc				AS platesc,
	campaign			AS campaign,
	rap					AS target_apparent_radius,
	np_pos				AS north_pole_position,
	hemis1				AS target_primary_hemisphere,
	hemis2				AS target_secondary_hemisphere
FROM apis.apis_products where access_url IS NOT NULL ;


GRANT ALL PRIVILEGES ON SCHEMA apis TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA apis TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA apis TO dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON apis.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON apis.epn_core to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON apis.epn_core to dachsroot WITH GRANT OPTION;
GRANT SELECT ON apis.apis_products to dachsroot WITH GRANT OPTION;
GRANT SELECT ON apis.apis_products to gavo WITH GRANT OPTION;
GRANT SELECT ON apis.apis_products to gavoadmin WITH GRANT OPTION;

