-- bass 2000 cree une vue mysql avec tout les champs nécessaire
-- on cree un table avec FDW puis ou une materialized vue avec ces champs et la conversion de date en 
-- jours Juliens
-- les champs supplementaires tel que le num caringthon sont ajoutes , 
-- il faudra sans doute revoir pour la version 2 les regions, les instrument name et instrument host name
-- exp_time correspond a time_exp_min

-- create a foreighn table in postgres from a mysql view or table
-- you must have postgres 9.1 minimum + foreighn data wraper  https://github.com/dpage/mysql_fdw/releases/tag/REL-1_0_1
-- under postgres sudo -u postgres psql -p 5435 -h localhost gavo  (just because i use port 5435)

-- pour lancer postgres
--  /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &


-- regarder table materialisé

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');
-- OPTIONS (address '145.238.193.14', port '3306');
-- use a mysql user that have select right
CREATE USER MAPPING FOR PUBLIC
SERVER mysql_svr
--OPTIONS (username 'root', password 'kissou1');
OPTIONS (username 'visite', password 'visite');

DROP SCHEMA bass2000 cascade;
CREATE SCHEMA bass2000;

-- DROP TABLE "VIEW_AR_HQI";

CREATE FOREIGN TABLE bass2000.view_bass2000
(
  "obs_id" bigint NOT NULL,
--  "granule_id" int8range NOT NULL,
  "granule_id" bigint NOT NULL,
  "access_url" character varying(200) NOT NULL,
  "access_estsize" integer NOT NULL,
  "access_format" character varying(10) NOT NULL,
  "time_min" timestamp,
  "time_max" timestamp,
  "wlnth_name" character varying(20),
  "spectral_range_min" double precision ,
  "spectral_range_max" double precision ,
  "em_min" double precision,
  "em_max" double precision,
  "u_name" character varying(20) NOT NULL,
  "em_unit" integer,
  "obs_creator_name" character varying(40),
  "description" character varying(70),
  "instrument_name" character varying(50) NOT NULL,
  "hdr_name" character varying(30) NOT NULL,
  "instrument_host_name" character varying(50) NOT NULL,
  "measurement_type" character varying(50) NOT NULL,
  "carrington" bigint,
  "target_region" character varying(20),
  "data_type" integer,
  "imagette"  character varying(50),
  "contact" character varying(40)
)
SERVER mysql_svr
-- options (query 'select 
 options(dbname 'devtest', table_name 'tap_view2');
-- options(dbname 'bass2000', table_name 'tap_view2');

-- select  obs_id,
-- access_url,
-- access_estsize,
-- access_format,
-- time_min,
-- time_max,
-- wlnth_name,
-- spectral_range_min,
-- em_min,
-- em_max,
-- u_name,
-- em_unit,
-- obs_creator_name,
-- description,
-- instrument_name,
-- measurement_type,
-- carrington,
-- target_region,
-- contact
--  from devtest.tap_view3;
-- insert into bass2000.view_bass200 (
-- obs_id,
-- access_url,
-- access_estsize,
-- access_format,
-- time_min,
-- time_max,
-- wlnth_name,
-- spectral_range_min,
-- em_min,
-- em_max,
-- u_name,
-- em_unit,
-- obs_creator_name,
-- description,
-- instrument_name,
-- measurement_type,
-- carrington,
-- target_region,
-- contact) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);

-- sequence pour l'increment de index
-- drop sequence bass2000_seq;
-- create sequence bass2000_seq;



-- drop view bass2000.epn_core;
-- drop sequence epn_core_seq2;
-- create sequence epn_core_seq2;
CREATE MATERIALIZED VIEW bass2000.epn_corev AS SELECT
--	ROW_NUMBER() OVER (ORDER BY resource_type) AS index2,
	cast(granule_id as bigint)  					AS index,
--	generate_series(1, select count(*) from view_ar_hqi) 			AS index,
	CASE "data_type"
		WHEN '0' THEN CAST('im' AS TEXT) 
		WHEN '1' THEN CAST('im' AS TEXT) 
		WHEN '2' THEN CAST('im' AS TEXT) 
		WHEN '3' THEN CAST('im' AS TEXT) 
		WHEN '8' THEN CAST('im' AS TEXT) 
		WHEN '9'  THEN CAST('im' AS TEXT)
		WHEN '14' THEN CAST('im' AS TEXT) 
		WHEN '17' THEN CAST('im' AS TEXT) 
		WHEN '18' THEN CAST('im' AS TEXT) 
		WHEN '19' THEN CAST('im' AS TEXT) 
		WHEN '20' THEN CAST('im' AS TEXT) 
		WHEN '21' THEN CAST('im' AS TEXT) 
		WHEN '22' THEN CAST('im' AS TEXT) 
		WHEN '23' THEN CAST('im' AS TEXT) 
		WHEN '24' THEN CAST('im' AS TEXT) 
		WHEN '25' THEN CAST('im' AS TEXT) 
		WHEN '27' THEN CAST('im' AS TEXT) 
		WHEN '28' THEN CAST('im' AS TEXT) 
		WHEN '29' THEN CAST('im' AS TEXT) 
		WHEN '30' THEN CAST('im' AS TEXT) 
		WHEN '31' THEN CAST('im' AS TEXT) 
		WHEN '32' THEN CAST('im' AS TEXT) 
		WHEN '33' THEN CAST('im' AS TEXT) 
		WHEN '35' THEN CAST('im' AS TEXT) 
		WHEN '36' THEN CAST('im' AS TEXT) 
		WHEN '39' THEN CAST('im' AS TEXT) 
		WHEN '40' THEN CAST('im' AS TEXT) 
		WHEN '43' THEN CAST('im' AS TEXT) 
		WHEN '44' THEN CAST('im' AS TEXT) 
		WHEN '45' THEN CAST('im' AS TEXT) 
		WHEN '4'  THEN CAST('mo' AS TEXT)
		WHEN '5'  THEN CAST('mo' AS TEXT)
		WHEN '6' THEN CAST('mo' AS TEXT)
		WHEN '10' THEN CAST('mo' AS TEXT)
		WHEN '26'  THEN CAST('mo' AS TEXT)
		WHEN '34'  THEN CAST('mo' AS TEXT)
		WHEN '37'  THEN CAST('mo' AS TEXT)
		WHEN '38'  THEN CAST('mo' AS TEXT)
		WHEN '41'  THEN CAST('mo' AS TEXT)
		WHEN '7' THEN CAST('ts' AS TEXT) 
		WHEN '11' THEN CAST('ts' AS TEXT)
		WHEN '13' THEN CAST('ts' AS TEXT) 
		WHEN '12' THEN CAST('cu' AS TEXT)
		WHEN '42' THEN CAST('cu' AS TEXT)
		WHEN '46' THEN CAST('cu' AS TEXT)
		WHEN '15'  THEN CAST('ds' AS TEXT) 
		WHEN '16' THEN CAST('ds' AS TEXT) 
		ELSE CAST(NULL AS TEXT) END
										AS  dataproduct_type,
	TEXT 'Sun'								AS target_name,
	TEXT 'star'								AS target_class,
	cast(to_char("time_min"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "time_min"::time)/86400 -0.5 AS time_min,
	cast(to_char("time_max"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "time_max"::time)/86400 -0.5 AS time_max,
	time_max::timestamp AS time_max_iso,

--	CAST(to_char(to_timestamp("time_min", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_min,
--	CAST(to_char(to_timestamp("time_max", 'YYYY-MM-DD HH:MI:SS')::date,'J') 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,
	spectral_range_min 			AS spectral_range_min,
	spectral_range_max			AS spectral_range_max,
	instrument_host_name								AS instrument_host_name,
	instrument_name								AS instrument_name,
	CASE "data_type"
		WHEN '0' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '5' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '6' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '7' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '8' THEN CAST('em.radio.100-200MHz' AS TEXT) 
		WHEN '9' THEN CAST('em.radio.200-400MHz' AS TEXT)
		WHEN '10' THEN CAST('em.radio' AS TEXT)
		WHEN '11' THEN CAST('em.radio' AS TEXT)
		WHEN '12' THEN CAST('em.radio' AS TEXT)
		WHEN '13' THEN CAST('em.radio' AS TEXT)
		WHEN '14' THEN CAST('em.radio' AS TEXT)
		WHEN '15' THEN CAST('em.radio' AS TEXT)
		WHEN '16' THEN CAST('em.radio' AS TEXT)
		WHEN '17' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '23' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '26' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '28' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '29' THEN CAST('em.radio.100-200MHz' AS TEXT) 
		WHEN '30' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '38' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '39' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '40' THEN CAST('em.line.Halpha' AS TEXT) 
		WHEN '43' THEN CAST('em.line.Halpha' AS TEXT) 
		ELSE CAST(NULL AS TEXT) END
	 							AS measurement_type,
	access_url 									AS access_url,
	access_format								AS access_format,
	cast(access_estsize/1024 as integer)								AS access_estsize,
	integer '3'								AS processing_level,
	CAST('Observatoire de Paris' AS TEXT)								AS publisher,
	CAST(NULL AS TEXT)								AS reference,
	TEXT 'Bass 2000'					 	AS service_title,
	target_region 								AS target_region,
	CAST(NULL AS TEXT)								AS preview_url,
	obs_id	AS	obs_id,
	granule_id	AS	granule_id,
	obs_creator_name	AS	obs_creator_name,
	hdr_name	AS	hdr_name,
	carrington	AS	carrington,
	wlnth_name	AS	wlnth_name,
    text 'http://bass2000.obspm.fr/php/load_imagette.php?name=' || cast(imagette as text) as thumbnail_url,
	contact	AS	contact
from bass2000.view_bass2000;

-- on passe de 1mn à 8s avec materialized view
-- drop table l'ancienne
-- alter table helio.epn_core2 rename to epn_core3;
-- alter view helio.epn_core alter column index set default 0;


drop view bass2000.epn_core;
CREATE or REPLACE VIEW bass2000.epn_core AS SELECT 

cast(granule_id as text) AS granule_uid,  
text 'bass2000' AS granule_gid,  
cast(obs_id as text)  AS obs_id,
dataproduct_type AS dataproduct_type, 
text 'Sun' AS target_name, 
text 'star' AS target_class, 
time_min AS time_min,
time_max 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,
spectral_range_min AS spectral_range_min,
spectral_range_max 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,
cast(NULL as TEXT) 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,
instrument_host_name::text AS instrument_host_name,
instrument_name::text AS instrument_name, 
measurement_type::text AS measurement_type,
access_url::text AS access_url,
text 'image/fits' AS access_format,
cast(NULL as integer) AS access_estsize,
integer '3' AS processing_level,
text 'PADC on behalf of LESIA' AS publisher,
cast(NULL as TEXT) AS bib_reference,
text 'bass2000' AS service_title,
target_region::text AS target_region,
text  'UTC' AS time_scale,
time_max_iso as release_date,
time_max_iso AS creation_date,
time_max_iso AS modification_date,
CAST(NULL AS spoly) AS s_region,
thumbnail_url::text AS thumbnail_url,
obs_creator_name::text    AS  obs_creator_name,
hdr_name::text    AS  hdr_name,
carrington  AS  carrington,
wlnth_name::text  AS  wlnth_name,
contact::text AS  contact

-- astdynvo.name.name as target_name,
FROM bass2000.epn_corev;








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


-- ALTER TABLE "VIEW_AR_HQI"
--  OWNER TO pierre;
