drop view m4ast.epn_core;
CREATE OR REPLACE VIEW m4ast.epn_core AS SELECT
    cast(index as text) AS granule_uid,
    cast(dataset_id as text) as granule_gid,
    cast(index as text) || text '_obs' AS obs_id,
    resource_type as resource_type,
    dataproduct_type as dataproduct_type,
    target_name as target_name,
    target_class as target_class,
    time_min as time_min,
    time_max as time_max,
    time_sampling_step_min as time_sampling_step_min,
    time_sampling_step_max as time_sampling_step_max,
    time_exp_min as time_exp_min,
    time_exp_max as time_exp_max,
    spectral_range_min as spectral_range_min,
    spectral_range_max as spectral_range_max,
    spectral_sampling_step_min as spectral_sampling_step_min,
    spectral_sampling_step_max as spectral_sampling_step_max,
    spectral_resolution_min as spectral_resolution_min,
    spectral_resolution_max as spectral_resolution_max,
    c1min as c1min,
    c2min as c2min,
    c3min as c3min,
    c1max as c1max,
    c2max as c2max,
    c3max as c3max,
    c1_resol_min  as c1_resol_min,
    c2_resol_min as c2_resol_min,
    c3_resol_min as c3_resol_min,
    c1_resol_max as c1_resol_max,
    c2_resol_max as c2_resol_max,
    c3_resol_max as c3_resol_max,
    spatial_frame_type as spatial_frame_type,
    incidence_min as incidence_min,
    incidence_max as incidence_max,
    emergence_min as emergence_min,
    emergence_max as emergence_max,
    phase_min  as phase_min,
    phase_max  as phase_max,
    instrument_host_name  as instrument_host_name,
    instrument_name as instrument_name,
    measurement_type as measurement_type,
    access_url as access_url,
    text 'application/x-votable+xml' access_format,
    access_estsize as access_estsize,
    reference as bib_reference,
    cast(target_distance as double precision) * 149597870.7 as target_distance_min,
    cast(target_distance as double precision) * 149597870.7 as target_distance_max,
    integer '3' as processing_level,
    text 'M4AST' AS service_title,

    CAST(NULL AS timestamp) as release_date,
    CAST(NULL AS timestamp)  AS creation_date,
    CAST(NULL AS timestamp)  AS modification_date

    from m4ast.data_table
    UNION ALL
    SELECT
    cast(index as text) || text '_nativ' AS granule_uid,
    cast(dataset_id as text) as granule_gid,
    cast(index as text) || text '_obs' AS obs_id,
    resource_type as resource_type,
    dataproduct_type as dataproduct_type,
    target_name as target_name,
    target_class as target_class,
    time_min as time_min,
    time_max as time_max,
    time_sampling_step_min as time_sampling_step_min,
    time_sampling_step_max as time_sampling_step_max,
    time_exp_min as time_exp_min,
    time_exp_max as time_exp_max,
    spectral_range_min as spectral_range_min,
    spectral_range_max as spectral_range_max,
    spectral_sampling_step_min as spectral_sampling_step_min,
    spectral_sampling_step_max as spectral_sampling_step_max,
    spectral_resolution_min as spectral_resolution_min,
    spectral_resolution_max as spectral_resolution_max,
    c1min as c1min,
    c2min as c2min,
    c3min as c3min,
    c1max as c1max,
    c2max as c2max,
    c3max as c3max,
    c1_resol_min  as c1_resol_min,
    c2_resol_min as c2_resol_min,
    c3_resol_min as c3_resol_min,
    c1_resol_max as c1_resol_max,
    c2_resol_max as c2_resol_max,
    c3_resol_max as c3_resol_max,
    spatial_frame_type as spatial_frame_type,
    incidence_min as incidence_min,
    incidence_max as incidence_max,
    emergence_min as emergence_min,
    emergence_max as emergence_max,
    phase_min  as phase_min,
    phase_max  as phase_max,
    instrument_host_name  as instrument_host_name,
    instrument_name as instrument_name,
    measurement_type as measurement_type,
    native_access_url as access_url,
    text 'text/plain' as access_format,
    access_estsize as access_estsize,
    reference as bib_reference,
    cast(target_distance as double precision) * 149597870.7 as target_distance_min,
    cast(target_distance as double precision) * 149597870.7 as target_distance_max,
    integer '3' as processing_level,
    text 'M4AST' AS service_title,
    CAST(NULL AS timestamp) as release_date,
    CAST(NULL AS timestamp)  AS creation_date,
    CAST(NULL AS timestamp)  AS modification_date
    from m4ast.data_table ;

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