-- SQL procedure to define amdadb data table.
-- Name: amdadb; Type: SCHEMA; Schema: amdadb; Owner: postgres

SET client_encoding = 'UTF8';

DROP VIEW IF EXISTS amdadb.epn_core CASCADE;
CREATE VIEW amdadb.epn_core AS SELECT
  -- header parameters
  CAST(obs_id || '_cdf' AS TEXT) AS granule_uid,
  dataproduct_type,
  target_name,
  time_min,
  time_max,
  -- important parameters
  access_url,
  target_class,
  target_region,
  spase_region,
  instrument_host_name,
  instrument_name,
  measurement_type,
  spase_measurement_type,
  spatial_frame_type,
  processing_level,
  release_date,
  access_estsize,
  access_format,
  time_sampling_step_min,
  time_sampling_step_max,
  time_exp_min,
  -- redundant or static parameters
  CAST(time_exp_min AS DOUBLE PRECISION) AS time_exp_max,
  CAST('cdf' AS TEXT) AS granule_gid,
  obs_id,
  -- CAST('application/x-netcdf' AS TEXT) AS access_format,
  CAST(release_date AS DATE) AS creation_date,
  CAST(release_date AS DATE) AS modification_date,
  CAST('AMDADB' AS TEXT) AS service_title,
  CAST('CDPP' AS TEXT) AS publisher,
  CAST('UTC' AS TEXT) AS time_scale,
  -- null parameters
  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,
  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 s_region,
  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,
  -- parameters added to prevent warnings in the q.rd validator
  CAST(NULL AS TEXT) AS thumbnail_url,
  CAST(NULL AS TEXT) AS file_name,
  CAST(NULL AS TEXT) AS species,
  CAST(NULL AS TEXT) AS feature_name,
  CAST(NULL AS TEXT) AS bib_reference
FROM amdadb.data_table;