drop view m4ast.epn_core;
CREATE OR REPLACE VIEW m4ast.epn_core AS SELECT
    index AS index,
    resource_type as resource_type,
    dataset_id as dataset_id,
    dataproduct_type as dataproduct_type,
    filename as filename,
    time_obs as time_obs,
    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,
    access_format as access_format,
    access_estsize as access_estsize,
    native_access_url as native_access_url,
    native_format_url as native_access_format, 
    reference as reference,
    target_distance  as target_distance,
    integer '3' as processing_level
    from m4ast.data_table
    UNION ALL
    SELECT
    INTEGER '-1' AS index,
    TEXT 'dataset'  as resource_type,
    text '1'  as dataset_id,
    text 'sp' as dataproduct_type,
    NULL as filename,
    NULL  as time_obs,
    array_to_string(array(select distinct "target_name" from m4ast.data_table), ' ') as target_name,
    TEXT 'asteroid' as target_class,
    min(time_min) as time_min,
    max(time_max) as time_max,
    min(time_sampling_step_min) as time_sampling_step_min,
    max(time_sampling_step_max) as time_sampling_step_max,
    min(time_exp_min) as time_exp_min,
    max(time_exp_max) as time_exp_max,
    min(spectral_range_min) as spectral_range_min,
    max(spectral_range_max) as spectral_range_max,
    min(spectral_sampling_step_min) as spectral_sampling_step_min,
    max(spectral_sampling_step_max) as spectral_sampling_step_max,
    min(spectral_resolution_min) as spectral_resolution_min,
    max(spectral_resolution_max) as spectral_resolution_max,
    min(c1min) as c1min,
    min(c2min) as c2min,
    min(c3min) as c3min,
    max(c1max) as c1max,
    max(c2max) as c2max,
    max(c3max) as c3max,
    NULL  as c1_resol_min,
    NULL as c2_resol_min,
    NULL as c3_resol_min,
    NULL as c1_resol_max,
    NULL as c2_resol_max,
    NULL as c3_resol_max,
    TEXT 'celestial' as spatial_frame_type,
    NULL as incidence_min,
    NULL as incidence_max,
    NULL as emergence_min,
    NULL as emergence_max,
    NULL  as phase_min,
    NULL  as phase_max,
     array_to_string(array(select distinct "instrument_host_name" from m4ast.data_table), ' ')  as instrument_host_name,
    NULL as instrument_name,
    array_to_string(array(select distinct "measurement_type" from m4ast.data_table), ' ') as measurement_type,
    NULL as access_url,
    NULL as access_format,
    NULL as access_estsize,
    NULL as native_access_url,
    NULL as native_access_format, 
     array_to_string(array(select distinct "reference" from m4ast.data_table), ' ') as reference,
    NULL  as target_distance,
    integer '3' as processing_level
    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;
