CREATE EXTENSION IF NOT EXISTS postgres_fdw;

drop view exoplanet.epn_core;
drop materialized view exoplanet.vepn_vue;
drop foreign table exoplanet.content;
drop user mapping  for gavoadmin server foreign_server;
drop server foreign_server cascade; 
create server foreign_server
  foreign data wrapper postgres_fdw
  options(host 'voparis-django-m', port '5432', dbname 'exoplanet');
-- options(host '127.0.0.1', port '5432', dbname 'exoplanet');
create user mapping for postgres
server  foreign_server
options (user 'gavo_user', password 'gavo');

create user mapping for gavoadmin
server  foreign_server
options (user 'gavo_user', password 'gavo');

create user mapping for gavo
server  foreign_server
options (user 'gavo_user', password 'gavo');

CREATE foreign table exoplanet.content (
obs_id character varying(100),
target_name character varying(100),
c1min double precision,
c2min double precision,
release_date timestamp ,
creation_date timestamp ,
modification_date timestamp ,
species character varying(100),
detection_type character varying(100),
publication_status character varying(100),
mass double precision,
mass_error_min double precision,
mass_error_max double precision,
radius double precision,
radius_error_min double precision,
radius_error_max double precision,
semi_major_axis double precision,
semi_major_axis_error_min double precision,
semi_major_axis_error_max double precision,
period double precision,
period_error_min double precision,
period_error_max double precision,
eccentricity double precision,
eccentricity_error_min double precision,
eccentricity_error_max double precision,
periastron double precision,
periastron_error_min double precision,
periastron_error_max double precision,
tzero_tr double precision,
tzero_tr_error_min double precision,
tzero_tr_error_max double precision,
tzero_vr double precision,
tzero_vr_error_min double precision,
tzero_vr_error_max double precision,
t_peri double precision,
t_peri_error_min double precision,
t_peri_error_max double precision,
t_conj double precision,
t_conj_error_min double precision,
t_conj_error_max double precision,
inclination double precision,
inclination_error_min double precision,
inclination_error_max double precision,
tzero_tr_sec double precision,
tzero_tr_sec_error_min double precision,
tzero_tr_sec_error_max double precision,
lambda_angle double precision,
lambda_angle_error_min double precision,
lambda_angle_error_max double precision,
discovered integer,
updated timestamp,
remarks text,
other_web text,
angular_distance double precision,
temp_calculated double precision,
temp_measured double precision,
hot_point_lon double precision,
log_g double precision,
created timestamp ,
modified timestamp,
albedo double precision,
albedo_error_min double precision,
albedo_error_max double precision,
mass_detection_type character varying(100),
radius_detection_type character varying(100),
mass_sin_i double precision,
mass_sin_i_error_min double precision,
mass_sin_i_error_max double precision,
impact_parameter double precision,
impact_parameter_error_min double precision,
impact_parameter_error_max double precision,
k double precision,
k_error_min double precision,
k_error_max double precision,
alternate_name text,
star_name text,
star_distance double precision,
star_distance_error_min double precision,
star_distance_error_max double precision,
star_spec_type character varying(25),
mag_v  double precision,
mag_i  double precision,
mag_j  double precision,
mag_h  double precision,
mag_k  double precision,
star_metallicity  double precision,
star_mass  double precision,
star_radius  double precision,
star_age  double precision,
star_teff  double precision,
magnetic_field boolean,
detected_disc  character varying(25)
)
SERVER foreign_server OPTIONS (table_name 'epn_vue', schema_name 'public'); 

CREATE MATERIALIZED VIEW exoplanet.vepn_vue AS select * from exoplanet.content; 


CREATE OR REPLACE FUNCTION exoplanet.pc2km(pc_value double precision)
  RETURNS double precision AS
$BODY$
DECLARE
    UA_IN_KILOMETER double precision := 149597870.7;
    PARSEC_IN_KILOMETER double precision := 648000/pi() * UA_IN_KILOMETER;
    value_precision integer;
    km_value double precision;
    div integer;
    tmp integer;
BEGIN
    value_precision = char_length(replace(to_char(pc_value,'FM99999999.999999999'),'.' ,''));
    km_value = pc_value * PARSEC_IN_KILOMETER;
    div = floor(log(km_value)) - value_precision + 1 ;
    tmp = round(km_value / power(10,div));
    RETURN tmp * power(10,div);    
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE or REPLACE VIEW exoplanet.epn_core AS SELECT 
text 'exoplanet' AS granule_gid,
target_name::text AS granule_uid,
obs_id::text AS obs_id,
text 'ci' AS dataproduct_type, 
target_name::text AS target_name, 
text 'exoplanet' AS target_class, 
CAST(NULL AS DOUBLE PRECISION) AS time_min,
CAST(NULL 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,
c1min AS c1min,
c1min AS c1max,
c2min AS c2min,
c2min AS c2max,
case when star_distance_error_min= 'Nan' then exoplanet.pc2km(star_distance) when star_distance_error_min='Infinity' then 'Infinity' ELSE exoplanet.pc2km(star_distance - star_distance_error_min) end as c3min,
case when star_distance_error_max= 'Nan' then exoplanet.pc2km(star_distance) when star_distance_error_max='Infinity' then 'Infinity' ELSE exoplanet.pc2km(star_distance + star_distance_error_max) end 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 text) AS spatial_origin,
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(NULL AS text) AS instrument_host_name,
CAST(NULL AS text) AS instrument_name, 
text 'phys.size;meta.file#phys.composition.species#phys.mass#phys.mass;stat.error#phys.size.radius#phys.size.radius;stat.error#phys.size.smajAxis#phys.size.smajAxis;stat.error#src.orbital;time.period#src.orbital;time.period;stat.error#src.orbital.eccentricity#src.orbital.eccentricity;stat.error#src.orbital.periastron#src.orbital.periastron;stat.error#time.epoch#time.epoch;src.orbital.periastron#time.epoch;src.orbital.periastron;stat.error#time.epoch;stat.error#src.orbital.inclination#src.orbital.inclination;stat.error#time.publiYear#pos.angDistance#phys.temperature#phys.temperature.effective#phys.gravity#pos.distance#src.spType#phot.mag;em.opt.V#phot.mag;em.opt.I#phot.mag;em.IR.J#phot.mag;em.IR.H#phot.mag;em.IR.K#phys.abund.Z#phys.mass#phys.size.radius#src.spType#time.age#phys.temperature.effective' AS measurement_type,
integer '5' AS processing_level,
CAST(NULL AS timestamptz ) as release_date,
date_trunc('second', created)  AS creation_date,
date_trunc('second', modified) AS modification_date,
text 'Encyclopedia' AS service_title,
text 'exoplanet' AS target_region,
species::text AS species,
detection_type::text as detection_type,
publication_status::text as publication_status,
mass as mass,
mass_error_min as mass_error_min,
mass_error_max as mass_error_max,
radius as radius,
radius_error_min as radius_error_min,
radius_error_max as radius_error_max,
semi_major_axis as semi_major_axis,
semi_major_axis_error_min as semi_major_axis_error_min,
semi_major_axis_error_max as semi_major_axis_error_max,
period as period,
period_error_min as period_error_min,
period_error_max as period_error_max,
eccentricity as eccentricity,
eccentricity_error_min as eccentricity_error_min,
eccentricity_error_max as eccentricity_error_max,
periastron as periastron,
periastron_error_min as periastron_error_min,
periastron_error_max as periastron_error_max,
tzero_tr as tzero_tr,
tzero_tr_error_min as tzero_tr_error_min,
tzero_tr_error_max as tzero_tr_error_max,
tzero_vr as tzero_vr,
tzero_vr_error_min as tzero_vr_error_min,
tzero_vr_error_max as tzero_vr_error_max,
t_peri as t_peri,
t_peri_error_min as t_peri_error_min,
t_peri_error_max as t_peri_error_max,
t_conj as t_conj,
t_conj_error_min as t_conj_error_min,
t_conj_error_max as t_conj_error_max,
inclination as inclination,
inclination_error_min as inclination_error_min,
inclination_error_max as inclination_error_max,
tzero_tr_sec as tzero_tr_sec,
tzero_tr_sec_error_min as tzero_tr_sec_error_min,
tzero_tr_sec_error_max as tzero_tr_sec_error_max,
lambda_angle as lambda_angle,
lambda_angle_error_min as lambda_angle_error_min,
lambda_angle_error_max as lambda_angle_error_max,
discovered as discovered,
updated as updated,
remarks as remarks,
other_web::text as other_web,
angular_distance as angular_distance,
temp_calculated as temp_calculated,
temp_measured as temp_measured,
hot_point_lon as hot_point_lon,
log_g as log_g,
albedo as albedo,
albedo_error_min as albedo_error_min,
albedo_error_max as albedo_error_max,
mass_detection_type::text  as mass_detection_type,
radius_detection_type::text as radius_detection_type,
mass_sin_i as mass_sin_i,
mass_sin_i_error_min as mass_sin_i_error_min,
mass_sin_i_error_max as mass_sin_i_error_max,
impact_parameter as impact_parameter,
impact_parameter_error_min as impact_parameter_error_min,
impact_parameter_error_max as impact_parameter_error_max,
k as k,
k_error_min as k_error_min,
k_error_max as k_error_max,
alternate_name::text AS alt_target_name,
star_name::text as star_name,
star_distance as star_distance,
star_distance_error_min as star_distance_error_min,
star_distance_error_max as star_distance_error_max,
star_spec_type::text as star_spec_type,
mag_v as mag_v,
mag_i as mag_i,
mag_j as mag_j,
mag_h as mag_h,
mag_k as mag_k,
star_metallicity as star_metallicity,
star_mass as star_mass,
star_radius as star_radius,
star_age as star_age,
star_teff as star_teff,
magnetic_field as magnetic_field,
detected_disc::text AS detected_disc,
c1min as ra,
c2min as dec
FROM exoplanet.vepn_vue;

GRANT ALL PRIVILEGES ON SCHEMA exoplanet TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA exoplanet TO dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA exoplanet TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.content to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.content to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.content to dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.vepn_vue to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.vepn_vue to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.vepn_vue to dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.epn_core to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON exoplanet.epn_core to dachsroot WITH GRANT OPTION;



