drop view m4ast.epn_core;
CREATE OR REPLACE VIEW m4ast.epn_core AS SELECT
    granule_uid::text AS granule_uid,
    text 'formatted' as granule_gid,
    granule_uid::text || text '_obs' AS obs_id,
    text 'sp' as dataproduct_type,
    target_name::text as target_name,
    case target_name
        when 'Ceres' then text 'dwarf_planet'
        when 'Pluto' then text 'dwarf_planet'
        when 'Eris' then text 'dwarf_planet'
        when 'Haumea' then text 'dwarf_planet'
        when 'Makemake' then text 'dwarf_planet'
        ELSE 'asteroid'::text
        END AS target_class,
    time_obs_jd as time_min,
    time_obs_jd 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,
    spectral_range_max as spectral_range_min,
    spectral_range_min as spectral_range_max,
    spectral_sampling_step_min as spectral_sampling_step_min,
    spectral_sampling_step_max 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 c2min,
    cast (NULL as double precision) as c3min,
    cast (NULL as double precision) as c1max,
    cast (NULL as double precision) as c2max,
    cast (NULL as double precision) as c3max,
    cast (NULL as double precision)  as c1_resol_min,
    cast (NULL as double precision) as c2_resol_min,
    cast (NULL as double precision) as c3_resol_min,
    cast (NULL as double precision) as c1_resol_max,
    cast (NULL as double precision) as c2_resol_max,
    cast (NULL as double precision) as c3_resol_max,
    cast (NULL as TEXT) 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,
    instrument_host_name::text  as instrument_host_name,
    cast (NULL as TEXT) as instrument_name,
    text 'phys.reflectance;em.wl;arith.ratio' as measurement_type,
    text 'http://voparis-srv-paris.obspm.fr/titan/to_vot_m4ast.py?fileurl='||text 'http://cardamine.imcce.fr/m4ast/Spectre/'|| filename as access_url,
    text 'application/x-votable+xml' access_format,
    access_estsize as access_estsize,
    reference::text as bib_reference,
    integer '3' as processing_level,
    text 'M4AST' AS service_title,
    CASE WHEN date_publi = '' then
        cast (NULL as timestamp) 
    else 
        cast(to_timestamp(date_publi, 'YYYY') as timestamp) 
    end as release_date,
    CAST(time_obs_iso AS timestamp)  AS creation_date,
    CAST(time_obs_iso AS timestamp)  AS modification_date,
    text 'http://voparis-srv.obspm.fr/vo/planeto/m4ast/'|| granule_uid::text ||text'.png' as thumbnail_url,
    alternate_name::text as alt_target_name,
    CAST(NULL AS spoly) AS s_region

    from m4ast.data
    UNION ALL
    SELECT
    granule_uid::text || text '_nativ' AS granule_uid,
    text 'native' as granule_gid,
    granule_uid::text || text '_obs' AS obs_id,
    text 'sp' as dataproduct_type,
    target_name::text as target_name,
    case target_name
        when 'Ceres' then text 'dwarf_planet'
        when 'Pluto' then text 'dwarf_planet'
        when 'Eris' then text 'dwarf_planet'
        when 'Haumea' then text 'dwarf_planet'
        when 'Makemake' then text 'dwarf_planet'
        ELSE 'asteroid'::text
        END AS target_class,
    time_obs_jd as time_min,
    time_obs_jd 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,
    spectral_range_max as spectral_range_min,
    spectral_range_min as spectral_range_max,
    spectral_sampling_step_min as spectral_sampling_step_min,
    spectral_sampling_step_max 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 c2min,
    cast (NULL as double precision) as c3min,
    cast (NULL as double precision) as c1max,
    cast (NULL as double precision) as c2max,
    cast (NULL as double precision) as c3max,
    cast (NULL as double precision)  as c1_resol_min,
    cast (NULL as double precision) as c2_resol_min,
    cast (NULL as double precision) as c3_resol_min,
    cast (NULL as double precision) as c1_resol_max,
    cast (NULL as double precision) as c2_resol_max,
    cast (NULL as double precision) as c3_resol_max,
    cast (NULL as TEXT) 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,
    instrument_host_name::text  as instrument_host_name,
    cast (NULL as TEXT) as instrument_name,
    text 'phys.reflectance;em.wl;arith.ratio' as measurement_type,
    text 'http://cardamine.imcce.fr/m4ast/Spectre/'|| filename as access_url,
    text 'text/plain' as access_format,
    access_estsize as access_estsize,
    reference::text as bib_reference,
    integer '3' as processing_level,
    text 'M4AST' AS service_title,
     CASE WHEN date_publi = '' then
          cast (NULL as timestamp) 
     else 
          cast(to_timestamp(date_publi, 'YYYY') as timestamp) 
     end as release_date,
    CAST(time_obs_iso AS timestamp)  AS creation_date,
    CAST(time_obs_iso AS timestamp)  AS modification_date,
    text 'http://voparis-srv.obspm.fr/vo/planeto/m4ast/'|| granule_uid::text ||text'.png' as thumbnail_url,
    alternate_name::text as alt_target_name,
    CAST(NULL AS spoly) AS s_region

    from m4ast.data ;

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;
