

drop view bdip.epn_core;
CREATE OR REPLACE VIEW bdip.epn_core AS  SELECT
    REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(filename,'.*/', ''),'.jpg',''),'.TIF','_tif')::text as granule_uid,  
    REGEXP_REPLACE(REGEXP_REPLACE(SUBSTR(filename,length(filename) - 2,3),'jpg','preview'),'TIF','full_resolution')::text AS granule_gid,  
    lower(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(filename,'.*/', ''),'.TIF',''),'.jpg',''))::text  AS obs_id,
	TEXT 'im' 	as dataproduct_type,
	regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(target , 'jupiter','Jupiter'), 'saturn', 'Saturn'), 'mercury', 'Mercury'), 'mars', 'Mars'), 'venus', 'Venus')::text 	as target_name,
	TEXT 'planet' 	as target_class,
	cast(julian_date as double precision)  as time_min,
	cast(julian_date as double precision)  as time_max,
	 CAST(NULL AS DOUBLE PRECISION) 		as time_sampling_step_min,	
	 CAST(NULL AS DOUBLE PRECISION) 		as time_sampling_step_max,	
	 CAST(NULL AS DOUBLE PRECISION) 		as time_exp_min,
	 CAST(NULL AS DOUBLE PRECISION) 		as time_exp_max,
	 CAST(NULL AS DOUBLE PRECISION) 		as spectral_range_min,
	 CAST(NULL AS DOUBLE PRECISION) 		as spectral_range_max,
	 CAST(NULL AS DOUBLE PRECISION) 		as spectral_sampling_step_min,
	 CAST(NULL AS DOUBLE PRECISION)		as spectral_sampling_step_max,
	 CAST(NULL AS DOUBLE PRECISION)		as spectral_resolution_min,
	 CAST(NULL 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(NULL AS DOUBLE PRECISION)   as phase_min,
	CAST(NULL AS DOUBLE PRECISION)   as phase_max,
	CAST("code_uai" AS TEXT) as instrument_host_name,
	instrument::text as instrument_name,
	TEXT 'obs.image'		as measurement_type,
    CASE SUBSTR(filename,length(filename) - 2,3)
        when 'TIF' then TEXT 'http://www.lesia.obspm.fr/BDIP/bdip_tif/' || upper(regexp_replace(lien_image, '.jpg', '.tif'))::text 
        when 'jpg' then TEXT 'http://www.lesia.obspm.fr/BDIP/bdip_jpeg/' || lien_image::text  
        ELSE CAST(NULL AS TEXT) END 
            as access_url,
    CASE SUBSTR(filename,length(filename) - 2,3)
                when 'TIF' then TEXT 'image/tiff'
                when 'jpg' then TEXT 'image/jpeg'
        ELSE CAST(NULL AS TEXT) END 
	 as access_format,
	size as access_estsize, 
	integer '3'			as processing_level,
	TEXT 'LESIA - Observatoire de Paris' as publisher,
	TEXT 'BDIP' 		as service_title,
--	TEXT 'ADU' as units_expression,
--	CAST(NULL AS TEXT)		as units_scalesi,
--	TEXT 'WM-2'		as units_dimequation,
--	TEXT 'planet'	as target_region,
--	TEXT 'Planetographic coordinates' as spatial_coordinate_description,
--	TEXT 'Planetocenter' as spatial_origin,
	ra::double precision as ra,
	dec::double precision as dec,
    CAST('2007-11-23T08:42:00Z' as timestamp) as release_date,
    CAST(date||' '||heure AS timestamp) AS creation_date,
    CAST('2007-11-23T08:42:00Z' as timestamp) AS modification_date,

    CAST(NULL AS spoly) AS s_region,
    TEXT 'http://www.lesia.obspm.fr/BDIP/bdip_icone/' || lien_image  as thumbnail_url
	FROM bdip.bdip_all 
	LEFT OUTER JOIN bdip.observatoires ON bdip.bdip_all.code_obs = observatoires.code_obs
	LEFT OUTER JOIN bdip.bdip_files ON bdip.bdip_all.index = bdip_files.index;


GRANT ALL PRIVILEGES ON SCHEMA bdip TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA bdip TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA bdip TO dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON bdip.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON bdip.epn_core to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON bdip.epn_core to dachsroot WITH GRANT OPTION;
