CREATE OR REPLACE VIEW gn_synthese.v_synthese_for_export
AS WITH taxon_att AS (
         SELECT a_1.cd_ref,
            json_object_agg(b.nom_attribut, a_1.valeur_attribut) AS attrib
           FROM taxonomie.cor_taxon_attribut a_1
             JOIN taxonomie.bib_attributs b ON a_1.id_attribut = b.id_attribut
          WHERE (a_1.id_attribut = ANY (ARRAY[50019, 1, 50050, 50016, 50009])) AND NOT (a_1.id_attribut = 1 AND a_1.valeur_attribut = 'non'::text)
          GROUP BY a_1.cd_ref
        )
 SELECT s.the_geom_local AS the_geom,
    st_asgeojson(s.the_geom_local) AS geojson_local,
    st_asgeojson(s.the_geom_4326) AS geojson_4326,
    s.the_geom_local,
    t.regne,
    t.group1_inpn,
    t.group2_inpn,
    t.classe,
    t.ordre,
    t.famille,
    t.id_rang AS rang,
    t.nom_valide,
    t.nom_vern,
    s.unique_id_sinp,
    s.unique_id_sinp_grp,
    s.nom_cite,
    s.date_min AS date_debut,
    s.date_max AS date_fin,
    life_stage.label_default AS stade_vie,
    sex.label_default AS sex,
    obs_meth.label_default AS meth_obs,
    comp.label_default AS comportement,
    n5.label_default AS biologique_statut,
    s.count_min AS nb_min,
    s.count_max AS nb_max,
    s.altitude_min AS alt_min,
    s.altitude_max AS alt_max,
    sens.label_default AS sensibilite,
    s.observers AS observateu,
    a.jname ->> 'COM'::text AS commune,
    COALESCE(a.jcode ->> 'ZC'::text, a.jcode ->> 'AA'::text, a.jcode ->> 'PEC'::text, 'Hors parc'::text) AS zone_pnc,
    a.jname ->> 'ZB'::text AS zone_bioge,
    valid_status.label_default AS st_validat,
    s.validator AS validateur,
    t.cd_nom,
    t.cd_ref,
    s.id_synthese AS "idSynthese",
    d.dataset_shortname AS jdd_nom,
    att.attrib ->> 'patrimonial'::text AS patrimonial,
    att.attrib ->> 'freq_estimee'::text AS freq_estimee,
    att.attrib ->> 'endemisme'::text AS endemisme,
    att.attrib ->> 'marcoeur_33'::text AS marcoeur_33,
    st.st_znieff,
    st.st_dir_europeenne,
    st.st_prot_national,
    st.st_prot_regional,
    st.st_uicn_max,
    st.list_status,
    d.id_dataset AS jdd_id,
    s.id_digitiser,
    s.id_synthese,
    s.comment_context AS comment_releve,
    s.comment_description AS comment_occurrence
   FROM gn_synthese.synthese s
     JOIN taxonomie.taxref t ON t.cd_nom = s.cd_nom
     JOIN gn_meta.t_datasets d ON d.id_dataset = s.id_dataset
     JOIN gn_synthese.t_sources sources ON sources.id_source = s.id_source
     JOIN taxonomie.v_taxref_bdc_statut_summary st ON t.cd_ref = st.cd_ref
     JOIN LATERAL ( SELECT d_1.id_synthese,
            json_object_agg(d_1.type_code, d_1.o_name) AS jname,
            json_object_agg(d_1.type_code, d_1.o_code) AS jcode
           FROM ( SELECT sa.id_synthese,
                    ta.type_code,
                    string_agg(DISTINCT a_1.area_name::text, ','::text) AS o_name,
                    string_agg(DISTINCT a_1.area_code::text, ','::text) AS o_code
                   FROM gn_synthese.cor_area_synthese sa
                     JOIN ref_geo.l_areas a_1 ON sa.id_area = a_1.id_area
                     JOIN ref_geo.bib_areas_types ta ON ta.id_type = a_1.id_type
                  WHERE sa.id_synthese = s.id_synthese
                  GROUP BY sa.id_synthese, ta.type_code) d_1
          GROUP BY d_1.id_synthese) a ON true
     LEFT JOIN ref_nomenclatures.t_nomenclatures obs_meth ON s.id_nomenclature_obs_technique = obs_meth.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures life_stage ON s.id_nomenclature_life_stage = life_stage.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures sex ON s.id_nomenclature_sex = sex.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures valid_status ON s.id_nomenclature_valid_status = valid_status.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures sens ON s.id_nomenclature_sensitivity = sens.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures comp ON s.id_nomenclature_behaviour = comp.id_nomenclature
     LEFT JOIN ref_nomenclatures.t_nomenclatures n5 ON s.id_nomenclature_bio_status = n5.id_nomenclature
     LEFT JOIN taxon_att att ON att.cd_ref = t.cd_ref;