-- HQI helio query interface 
-- ce sont des structures, les anglais ont le catalogie
-- mettre tt en lower
-- type de structure AR region active, FL filament , CH choronal hole, 
-- obs join de observation et observatory
-- on conserve les longueur d'onde
-- date obs et end marche pour les images
-- les jd sont pour date_obs
-- exp_time correspond a time_exp_min
-- carre X1Y1 ---- X3 Y3 BR_HG_LONG1_DEG pour le spatial
--       X0Y0 ---- X2 Y2
-- on prend min des x min des y, max des x max des y ce sont des coordonnées heliographique long latitude et ne pas prendre les bvaleurs null
-- snapshot c'est bien la prevue, virer le champ snapshot
-- faire script python qui donne dans annotation l'ucd et l'utype, par contre unité à la main
-- recuperer la description 
-- preparer a partir d'un tableur la construction de q.rd
-- avant le PNST en février
-- DATA_TYPE = dataproduct_type transfo IMAGE en im et DYNAMIC SPECTRUM en ...
-- le format de fichier dans FILE_FORMAT
-- instrument_host_name observat
-- instrument_name instrume
-- user guest passwd guest
-- tt est sur voparis-mysql5-paris
-- récupérer les champs freq et considéré que ce sera en MHz tout le temps

-- 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 mysql il fait un grant select, mais aussi un grant show view
-- GRANT SHOW VIEW ON `hfc1`.* to 'visite'@'145.238.194.15';
-- FLUSH PRIVILEGES;

-- 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_t3 FOREIGN DATA WRAPPER mysql_fdw
-- OPTIONS (address '145.238.193.14', port '3306');
OPTIONS (host '145.238.193.29', port '3306');
-- use a mysql user that have select right
CREATE USER MAPPING FOR PUBLIC
SERVER mysql_svr_t3
-- OPTIONS (username 'visite', password 'visite');
OPTIONS (username 'guest', password 'guest');

DROP SCHEMA hfc1t3 cascade;
CREATE SCHEMA hfc1t3;


CREATE FOREIGN TABLE hfc1t3.view_t3_hqi
(
  "INSTITUT" character varying(150) NOT NULL,
  "CODE" character varying(100) NOT NULL,
  "VERSION" character varying(50) NOT NULL,
  "FEATURE_NAME" character varying(100) NOT NULL,
  "ENC_MET" character varying(50),
  "PERSON" character varying(150),
  "CONTACT" character varying(150) NOT NULL,
  "REFERENCE" character varying(150),
  "OBSERVAT" character varying(255) NOT NULL,
  "INSTRUME" character varying(150) NOT NULL,
  "TELESCOP" character varying(150),
  "OBSINST_KEY" character varying(150),
  "UNITS" character varying(100),
  "WAVEMIN" real,
  "WAVEMAX" real,
  "WAVENAME" character varying(50),
  "WAVEUNIT" character varying(10),
  "FREQMIN" real,
  "FREQMAX" real,
  "FREQUNIT" character varying(10),
  "SPECTRAL_DOMAIN" character varying(100),
  "OBS_TYPE" character varying(50),
  "OBS_CAT" character varying(50),
  "DATE_OBS" timestamp without time zone NOT NULL,
  "DATE_END" timestamp without time zone NOT NULL,
  "JDINT" integer,
  "JDFRAC" double precision,
  "C_ROTATION" integer,
  "FILENAME" character varying(100) NOT NULL,
  "URL" text,
  "CDELT1" double precision NOT NULL,
  "CDELT2" double precision NOT NULL,
  "NAXIS1" integer,
  "NAXIS2" integer,
  "CENTER_X" double precision,
  "CENTER_Y" double precision,
  "FILE_FORMAT" character varying(20),
  "DATA_TYPE" character varying(50),
  "QCLK_URL" text,
  "QCLK_FNAME" character varying(200),
  "ID_TYPE_III" integer,
  "FEAT_MAX_INT" float,
  "FEAT_MEAN_INT" float,
  "MULTIPLE" integer,
  "FEAT_FILENAME" character varying(150) NOT NULL,
  "SNAPSHOT" character varying(400),
  "CC_X_PIX" integer NOT NULL,
  "CC_Y_PIX" integer NOT NULL,
  "CC_X_UTC" double precision NOT NULL,
  "CC_Y_MHZ" double precision NOT NULL,
  "CC" text NOT NULL,
  "CC_LENGTH" integer,
  "SKE_CC_X_PIX" integer NOT NULL,
  "SKE_CC_Y_PIX" integer NOT NULL,
  "SKE_CC_X_UTC" double precision NOT NULL,
  "SKE_CC_Y_MHZ" double precision NOT NULL,
  "SKE_CC" text NOT NULL,
  "SKE_CC_LENGTH" integer NOT NULL,
  "BR_X0_PIX" integer,
  "BR_Y0_PIX" integer,
  "BR_X1_PIX" integer,
  "BR_Y1_PIX" integer,
  "BR_X2_PIX" integer,
  "BR_Y2_PIX" integer,
  "BR_X3_PIX" integer,
  "BR_Y3_PIX" integer,
  "BR_X0_UTC" double precision NOT NULL,
  "BR_Y0_MHZ" double precision NOT NULL,
  "BR_X1_UTC" double precision NOT NULL,
  "BR_Y1_MHZ" double precision NOT NULL,
  "BR_X2_UTC" double precision NOT NULL,
  "BR_Y2_MHZ" double precision NOT NULL,
  "BR_X3_UTC" double precision NOT NULL,
  "BR_Y3_MHZ" double precision NOT NULL,
  "TIME_START" timestamp without time zone NOT NULL,
  "TIME_END" timestamp without time zone NOT NULL,
  "DRIFT_START" real NOT NULL,
  "DRIFT_END" real NOT NULL,
  "FIT_A0" real NOT NULL,
  "FIT_A1" real NOT NULL,
  "LVL_TRUST" integer
)
SERVER mysql_svr_t3
-- options (query 'select 
options(dbname 'hfc1', table_name 'VIEW_T3_HQI');
-- INSTITUT,
-- CODE,
-- VERSION,
-- FEATURE_NAME,
-- ENC_MET,
-- PERSON,
-- CONTACT,
-- REFERENCE,
-- OBSERVAT,
-- INSTRUME,
-- TELESCOP,
-- OBSINST_KEY,
-- UNITS,
-- WAVEMIN,
-- WAVEMAX,
-- WAVENAME,
-- WAVEUNIT,
-- FREQMIN,
-- FREQMAX,
-- FREQUNIT,
-- SPECTRAL_DOMAIN,
-- OBS_TYPE,
-- OBS_CAT,
-- DATE_OBS,
-- DATE_END,
-- JDINT,
-- JDFRAC,
-- C_ROTATION,
-- FILENAME,
-- URL,
-- CDELT1,
-- CDELT2,
-- NAXIS1,
-- NAXIS2,
-- CENTER_X,
-- CENTER_Y,
-- FILE_FORMAT,
-- DATA_TYPE,
-- QCLK_URL,
-- QCLK_FNAME,
-- ID_TYPE_III,
-- FEAT_MAX_INT,
-- FEAT_MEAN_INT,
-- MULTIPLE,
-- FEAT_FILENAME,
-- SNAPSHOT,
-- CC_X_PIX,
-- CC_Y_PIX,
-- CC_X_UTC,
-- CC_Y_MHZ,
-- CC,
-- CC_LENGTH,
-- SKE_CC_X_PIX,
-- SKE_CC_Y_PIX,
-- SKE_CC_X_UTC,
-- SKE_CC_Y_MHZ,
-- SKE_CC,
-- SKE_CC_LENGTH,
-- BR_X0_PIX,
-- BR_Y0_PIX,
-- BR_X1_PIX,
-- BR_Y1_PIX,
-- BR_X2_PIX,
-- BR_Y2_PIX,
-- BR_X3_PIX,
-- BR_Y3_PIX,
-- BR_X0_UTC,
-- BR_Y0_MHZ,
-- BR_X1_UTC,
-- BR_Y1_MHZ,
-- BR_X2_UTC,
-- BR_Y2_MHZ,
-- BR_X3_UTC,
-- BR_Y3_MHZ,
-- TIME_START,
-- TIME_END,
-- DRIFT_START,
-- DRIFT_END,
-- FIT_A0,
-- FIT_A1,
-- LVL_TRUST
-- from hfc1.VIEW_T3_HQI');

-- sequence pour l'increment de index
drop sequence epn_core_seqt3 cascade;
create sequence epn_core_seqt3;

CREATE MATERIALIZED VIEW hfc1t3.epn_core AS SELECT

	--      ROW_NUMBER() OVER (ORDER BY resource_type) AS index2,
        to_char(nextval('epn_core_seqt3'::regclass),'999999999999')                           AS granule_uid,
        cast('t3' as text)             AS granule_gid,
        cast('null' as text)           AS obs_id,

--      generate_series(1, select count(*) from view_ar_hqi)                    AS index,
        CASE "DATA_TYPE"
            WHEN 'IMAGE'  THEN CAST('im' AS TEXT)
            WHEN 'SPECTRUM'  THEN CAST('ds' AS TEXT)
            WHEN 'DYNAMIC SPECTRUM'  THEN CAST('ds' AS TEXT)
            WHEN 'DYNAMIC_SPECTRUM'  THEN CAST('ds' AS TEXT)
            WHEN 'SPECTROGRAM'  THEN CAST('sp' AS TEXT)
            ELSE CAST(NULL AS TEXT) END 					AS  dataproduct_type,
        TEXT 'Sun'                                                              AS target_name,
	TEXT 'star'                                                             AS target_class,
	cast(to_char("DATE_OBS"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_OBS"::time)/86400 -0.5 AS time_min,
	cast(to_char("DATE_END"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_END"::time)/86400 -0.5 AS time_max,
--      CAST(to_char(to_timestamp(a."DATE_OBS", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_min,
--      CAST(to_char(to_timestamp(a."DATE_END", '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,
       CAST((299792458E9/"WAVEMAX") AS DOUBLE PRECISION)                       AS spectral_range_min,
       CAST((299792458E9/"WAVEMIN") 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,
        "OBSERVAT"                                                              
AS instrument_host_name,
      "INSTRUME"                                                              AS instrument_name,
       TEXT 'type_III.nubmer_of_components,"type_II.drift'                                                     AS measurement_type,
        "URL"                                                                   AS access_url,
        TEXT 'image/fits'                                                          AS access_format,
        INTEGER '11'                                                            AS access_estsize,
        integer '3'                                                             AS processing_level,
        "INSTITUT"                                                              AS publisher,
        "REFERENCE"                                                             AS reference,
        TEXT 'Helio type 3 event'                                               AS service_title,
        "SNAPSHOT"                                                              AS preview_url,
        "INSTITUT"      AS      INSTITUT,
        "CODE"  AS      CODE,
        "VERSION"       AS      VERSION,
        "FEATURE_NAME"  AS      FEATURE_NAME,
        "ENC_MET"       AS      ENC_MET,
        "PERSON"        AS      PERSON,
        "CONTACT"       AS      CONTACT,
        "OBSERVAT"      AS      OBSERVAT,
        "INSTRUME"      AS      INSTRUME,
       "TELESCOP"      AS      TELESCOP,
        "OBSINST_KEY"   AS      OBSINST_KEY,
        "UNITS" AS      UNITS,
        "WAVEMIN"               AS      WAVEMIN,
        "WAVEMAX"       AS      WAVEMAX,
        "WAVENAME"      AS      WAVENAME,
        "WAVEUNIT"      AS      WAVEUNIT,
        "FREQMIN"         AS      FREQMIN,
        "FREQMAX"         AS      FREQMAX,
        "FREQUNIT"         AS      FREQUNIT,
        "SPECTRAL_DOMAIN"       AS      SPECTRAL_DOMAIN,
"OBS_TYPE"      AS      OBS_TYPE,
"OBS_CAT"       AS      OBS_CAT,
"DATE_OBS"      AS      DATE_OBS,
"DATE_END"      AS      DATE_END,
"JDINT" AS      JDINT,
"JDFRAC"        AS      JDFRAC,
"C_ROTATION"    AS      C_ROTATION,
"FILENAME"      AS      FILENAME,
"URL"   AS      URL,
"CDELT1"        AS      CDELT1,
"CDELT2"        AS      CDELT2,
"NAXIS1"        AS      NAXIS1,
"NAXIS2"        AS      NAXIS2,
"CENTER_X"      AS      CENTER_X,
"CENTER_Y"      AS      CENTER_Y,
"FILE_FORMAT"   AS      FILE_FORMAT,
"DATA_TYPE"     AS      DATA_TYPE,
"QCLK_URL"      AS      QCLK_URL,
"QCLK_FNAME"    AS      QCLK_FNAME,
"ID_TYPE_III"   AS      ID_TYPE_III,
"FEAT_MAX_INT"  AS      FEAT_MAX_INT,
"FEAT_MEAN_INT" AS      FEAT_MEAN_INT,
"MULTIPLE"      AS      MULTIPLE,
"FEAT_FILENAME" AS      FEAT_FILENAME,
"SNAPSHOT"      AS      SNAPSHOT,
"CC_X_PIX"      AS      CC_X_PIX,
"CC_Y_PIX"      AS      CC_Y_PIX,
"CC_X_UTC"      AS      CC_X_UTC,
"CC_Y_MHZ"      AS      CC_Y_MHZ,
"CC"            AS      CC,
"CC_LENGTH"     AS      CC_LENGTH,
"SKE_CC_X_PIX"  AS      SKE_CC_X_PIX,
"SKE_CC_Y_PIX"  AS      SKE_CC_Y_PIX,
"SKE_CC_X_UTC"  AS      SKE_CC_X_UTC,
"SKE_CC_Y_MHZ"  AS      SKE_CC_Y_MHZ,
"SKE_CC"        AS      SKE_CC,
"SKE_CC_LENGTH" AS      SKE_CC_LENGTH,
"BR_X0_PIX"     AS      BR_X0_PIX,
"BR_Y0_PIX"     AS      BR_Y0_PIX,
"BR_X1_PIX"     AS      BR_X1_PIX,
"BR_Y1_PIX"     AS      BR_Y1_PIX,
"BR_X2_PIX"     AS      BR_X2_PIX,
"BR_Y2_PIX"     AS      BR_Y2_PIX,
"BR_X3_PIX"     AS      BR_X3_PIX,
"BR_Y3_PIX"     AS      BR_Y3_PIX,
"BR_X0_UTC"     AS      BR_X0_UTC,
"BR_Y0_MHZ"     AS      BR_Y0_MHZ,
"BR_X1_UTC"     AS      BR_X1_UTC,
"BR_Y1_MHZ"     AS      BR_Y1_MHZ,
"BR_X2_UTC"     AS      BR_X2_UTC,
"BR_Y2_MHZ"     AS      BR_Y2_MHZ,
"BR_X3_UTC"     AS      BR_X3_UTC,
"BR_Y3_MHZ"     AS      BR_Y3_MHZ,
"TIME_START"    AS      TIME_START,
"TIME_END"      AS      TIME_END,
"DRIFT_START"   AS      DRIFT_START,
"DRIFT_END"     AS      DRIFT_END,
"FIT_A0"        AS      FIT_A0,
"FIT_A1"        AS      FIT_A1,
"LVL_TRUST"     AS      LVL_TRUST,
CAST('2014-03-05T17:42:00Z' as timestamp) as release_date,
CAST('2014-03-05T17:42:00Z' as timestamp) AS creation_date,
CAST('2014-03-05T17:42:00Z' as timestamp) AS modification_date,
CAST(NULL AS spoly) AS s_region

        from hfc1t3.view_t3_hqi;

-- 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;


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


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