drop view basecom.epn_core;

drop sequence basecom_seq;
create sequence basecom_seq;

CREATE OR REPLACE VIEW basecom.epn_core AS SELECT
    case
       when  r."Ref" is NULL  then
          nextval('basecom_seq'::regclass)::text
       ELSE 
          regexp_replace(r."Ref",'.fits','_fits')||cast(r.id as TEXT) 
       END
        AS granule_uid,
	text'bascom' 							AS granule_gid,
	replace(r."Ref",'.fits','')::text 			AS obs_id,
	TEXT 'sp' 								AS dataproduct_type,
	CAST (p.name_iau as text)::text				AS target_name,
	TEXT 'comet'							AS target_class,
	r."Jstart"								AS time_min,
	r."Jend"								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,
	r.freq_start			AS spectral_range_min,
	r.freq_stop			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 (r."RA" AS DOUBLE PRECISION) / (60.0*15.0)					AS c1min,
	CAST (r."RA" AS DOUBLE PRECISION) / (60.0*15.0)					AS c1max,
	r."DEC"									AS c2min,
	r."DEC"									AS c2max,
	CAST(NULL AS DOUBLE PRECISION)						AS c3min,
	CAST(NULL AS DOUBLE PRECISION)						AS c3max,
    CAST(NULL AS spoly) AS s_region,
	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 'celestial'							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,
	TEXT 'nancay decameter array'					AS instrument_host_name,
	TEXT 'radio telescope'							AS instrument_name,
	TEXT 'phys.polarization.circular#phys.veloc.orbital;pos.heliocentric#spect.dopplerVeloc#spect.line.width#arith.rate;phys.mass.loss'				AS measurement_type,
	TEXT 'http://voparis-srv.obspm.fr/vo/planeto/basecom/' || r."Ref" 	AS access_url,
	TEXT 'application/fits'								AS access_format,
	INTEGER '11'								AS access_estsize,
	integer '3'								AS processing_level,
	TEXT 'vo paris data centre on behalf of LESIA' 				AS publisher,
	TEXT 'no information'							AS bib_reference,
	TEXT 'bascom'					 	AS service_title,
CAST(NULL AS timestamp) as release_date,
CAST('2013-10-16T08:42:00Z' as timestamp) AS creation_date,
CAST('2013-10-16T08:42:00Z' as timestamp) AS modification_date,
	TEXT 'coma'								AS target_region,
	TEXT 'OH'								AS species,
  cast("p"."nameA"||'#'||"p"."nameB"||'#'||"p"."nameC"||'#'||"p"."alias_iau" as text)  AS alt_target_name,
	"p"."Tperihelion" 							AS "tperihelion",
	"p"."dSun0" 								AS "dsun0",
	regexp_replace(regexp_replace(regexp_replace(regexp_replace("p"."cometType",'N','dynamically new'),'L','long-period comet'),'H','Halley-type comet'),'J','Jupiter-family comet') 							AS "comettype",
	 regexp_replace(regexp_replace("p"."ObsQual",'M','marginal detection'),'D','clear detection') 								AS "obsqual",
	"r"."status" 								AS "status",
	"r"."Tepoch" 								AS "tepoch",
	"r"."Tstart" 								AS "tstart",
	"r"."Tend" 								AS "tend",
	"r"."freq" 								AS "freq",
	"r"."pola"::text AS "pola",
	"r"."dGeo" AS "dgeo",
	"r"."dSun" AS "dsun",
	"r"."rdot" AS "rdot",
	"r"."inv1" AS "inv1",
	"r"."inv2" AS "inv2",
	"r"."Tbg" AS "tbg",
	"r"."Area" AS "line_area",
	"r"."Aerr" AS "aerr",
	"r"."Slin" AS "slin",
	"r"."Serr" AS "serr",
	"r"."V0" AS "vo",
	"r"."V0err" AS "voerr",
	"r"."dV0" AS "dvo",
	"r"."dV0err" AS "dvoerr",
	"r"."Q1OH" AS "q1oh",
	"r"."Q1err" AS "q1err",
	"r"."Q2OH" AS "q2oh",
	"r"."Q2err" AS "q2err",
	"r"."Ref"::text AS "file_name",
	CAST (r."RA" AS DOUBLE PRECISION) / (60.0*15.0) AS "ra",
	"r"."offRA" AS "offra",
	"r"."DEC" AS "dec",
	"r"."offDEC" AS "offdec",
	"r"."SNR" AS "snr", 
	regexp_replace(TEXT 'http://voparis-srv.obspm.fr/vo/planeto/basecom/thumbnail/' || r."Ref"::text,'.fits','.png') 	AS thumbnail_url
	from basecom.comet_param "p", basecom.comet_results "r" where (("p"."fk_key" = "r"."key") and ("r"."priv" = 1)) ;

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