-- on reproduit en foreign table l'ensemble des tables et des champs utiles
-- on fait une materialized vue
-- on créee une vue epn_core

CREATE EXTENSION postgres_fdw;

drop view dynastvo.epn_core;
drop materialized view dynastvo.vepn_core;
drop foreign table dynastvo.fit;
drop user mapping for gavoadmin server foreign_server;
drop server foreign_server cascade; 

create server foreign_server3
  foreign data wrapper postgres_fdw
--   options(host 'voparis-django-m', port '5432', dbname 'exoplanet');
-- options(host 'mimosa.obspm.fr', port '5432', dbname 'astdyn');
options(host 'gaiafunsso.obspm.fr', port '5432', dbname 'astdyn');
-- options(host '127.0.0.1', port '5432', dbname 'imcce');
create user mapping for postgres
server  foreign_server3
options (user 'vouser', password 'sidaner');
-- options (user 'gavoadmin', password 'toto');

create user mapping for gavoadmin
server  foreign_server3
options (user 'vouser', password 'sidaner');
-- options (user 'gavoadmin', password 'toto');

create user mapping for gavo
server  foreign_server3
options (user 'vouser', password 'sidaner');
-- options (user 'gavoadmin', password 'toto');

create user mapping for lesidaner
server  foreign_server3
options (user 'vouser', password 'sidaner');
-- options (user 'gavoadmin', password 'toto');


drop SCHEMA dynastvo cascade;
CREATE SCHEMA dynastvo;

CREATE TYPE dynastvo.fitconditions AS (
    noba integer,
    nobr integer,
    nobrr integer,
    nobvr integer,
    jdmin double precision,
    jdmax double precision
);

CREATE FOREIGN TABLE dynastvo.designation (
    designation character varying(64) NOT NULL,
    id_identifying_numbers bigint NOT NULL,
    created timestamp without time zone DEFAULT now(),
    modified timestamp without time zone DEFAULT now()
)
   SERVER foreign_server3
   OPTIONS (table_name 'designation', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.designation
  OWNER TO postgres;

ALTER FOREIGN TABLE dynastvo.designation OWNER TO gavoadmin;


CREATE FOREIGN TABLE  dynastvo.fit (
    id integer NOT NULL,
    id_identifying_numbers bigint NOT NULL,
    id_model bigint NOT NULL,
    conditions dynastvo.fitconditions,
    rms double precision NOT NULL,
    date_computed timestamp without time zone,
    mpecs character varying(8),
    created timestamp without time zone DEFAULT now(),
    modified timestamp without time zone DEFAULT now()
)
   SERVER foreign_server3
   OPTIONS (table_name 'fit', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.fit
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.identifying_numbers
(
  id bigserial NOT NULL,
  id_mpc integer NOT NULL,
  id_spk bigint NOT NULL
)
  SERVER foreign_server3
   OPTIONS (table_name 'identifying_numbers', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.identifying_numbers
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.input_errors
(
  file_name character varying(1024),
  line_identifying_numbers integer,
  table_name character varying(64),
  query_type character(1),
  error text,
  "timestamp" timestamp without time zone
)
  SERVER foreign_server3
   OPTIONS (table_name 'input_errors', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.input_errors
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.name
(
  name character varying(64) NOT NULL,
  id_identifying_numbers bigint NOT NULL
)
 SERVER foreign_server3
   OPTIONS (table_name 'name', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.name
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.object_types
(
  id smallint NOT NULL,
  anacronym character varying(16) NOT NULL,
  description character varying(256)
)
 SERVER foreign_server3
   OPTIONS (table_name 'object_types', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.object_types
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.orbit_elements
(
  id_fit integer NOT NULL,
  elements double precision[],
  description text[],
  created timestamp without time zone DEFAULT now(),
  modified timestamp without time zone DEFAULT now()
)
 SERVER foreign_server3
   OPTIONS (table_name 'orbit_elements', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.orbit_elements
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.orbit_elements_errors
(
  id_fit integer NOT NULL,
  errors double precision[],
  description text[]
)
 SERVER foreign_server3
   OPTIONS (table_name 'orbit_elements_errors', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.orbit_elements_errors
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.orbit_properties
(
  id_fit integer NOT NULL,
  id_object_type smallint NOT NULL,
  moid double precision,
  spu double precision
)
 SERVER foreign_server3
   OPTIONS (table_name 'orbit_properties', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.orbit_properties
  OWNER TO postgres;

CREATE FOREIGN TABLE dynastvo.physical_properties
(
  id_fit integer NOT NULL,
  h double precision,
  g double precision
)
 SERVER foreign_server3
   OPTIONS (table_name 'physical_properties', schema_name 'astdynvo');
ALTER FOREIGN TABLE dynastvo.physical_properties
  OWNER TO postgres;



