select
TAB3.Num_observation,
TAB3.observateur,
TAB3.etablissement,
TAB3.latitude,
TAB3.longitude,
TO_CHAR(MAX(TAB3.Date_observation), 'dd/mm/yyyy') as Date_observation,
extract (year from TAB3.Date_observation) as annee,
extract (month from TAB3.Date_observation) as mois,
extract (week from TAB3.Date_observation) as semaine,
TAB3.protocole,
TAB3.groupepk,
TAB3.effectifs,
TAB3.type_etablissement,
TAB3.date_create,
SUM(TAB3.Nombre_individus) as abondance_totale,
SUM(TAB3.calcul_diversite) as diversite_totale,
(case when SUM(TAB3.calcul_diversite)=0 then 'Aucune espece vue'
	  else string_agg (TAB3.Espece2 || ' (' || TAB3.Nombre_individus || ')', ', ') end ) as liste_espece,
string_agg (TAB3.photo, ' | ') as photos
from(
	--3 -> Ce niveau d'aggregation permet de creer une nouvelle colonne avec uniquement le nom des espece ou au moins 1 individus a ete vu
	select distinct
	TAB2.Num_observation,
	TAB2.observateur,
	TAB2.etablissement,
	TAB2.Date_observation,
	TAB2.protocole,
	TAB2.groupepk,
	TAB2.effectifs,
	TAB2.type_etablissement,
	TAB2.date_create,
	TAB2.latitude,
	TAB2.longitude,
	(case when TAB2.Nombre_individus>0 then TAB2.Espece
		  else null END) as Espece2,
	--TAB2.Espece as Espece,
	TAB2.Nombre_individus as Nombre_individus,
	TAB2.calcul_diversite,
	(case when TAB2.photo='Photo de  : ' then NULL
			  else TAB2.photo END) as Photo
		  	from (
		--2 -> Ce niveau d'aggregation permet d'avoir une ligne par espece avec le nombre d'individus (utile pour gerer les vers de terre)
		select distinct
		TAB.Num_observation,
		concat(UPPER(TAB.nom), ' ',initcap(TAB.prenom), ' | ', TAB.email) as observateur,
		concat(TAB.nom_etab, ' (', TAB.ville_etab,', ', left(TAB.zip_etab,5), ') | Structurepk : ', TAB.structurepk) as etablissement,
		TAB.Date_observation,
		TAB.protocole,
		TAB.groupepk,
		TAB.effectifs,
		TAB.type_etablissement,
		TAB.date_create,
		TAB.Espece as Espece,
		TAB.latitude,
		TAB.longitude,
		SUM(TAB.Nombre_individus) as Nombre_individus,
		TAB.calcul_diversite,
		concat('Photo de ', TAB.nom_espece, ' : ', TAB.url_photo) as photo
		from (
			--1 -> Cette premiere sous-requete permet d'avoir la liste des observations, de regrouper les vers adultes et juveniles, de gerer l'ancien protocole inventaire escargots et enfin d'ajouter une abondance =1 pour les plantes
			SELECT
			   observations.observationpk AS Num_observation,
			   observations.date_create,
			   users.nom as nom,
			   users.prenom as prenom,
			   users.email as email,
			   dico_structures.structurepk as structurepk,
			   dico_structures.type AS type_etablissement,
			   dico_structures."name" as nom_etab,
			   dico_structures.zipcode as zip_etab,
			   dico_structures.city as ville_etab,
			   observations.date AS Date_observation,
			   dico_protocoles.nom_courant as protocole,
			   groupes.groupepk,
			   groupes.effectifs,
			   z.latitude,
			   z.longitude,
			   (CASE observations_abondances.nom_espece WHEN 'Anecique tete noire (juvenile)' THEN 'Anecique tete noire'
								   					   WHEN 'Anecique tete rouge (juvenile)' THEN 'Anecique tete rouge'
								   					   WHEN 'Endoge (juvenile)' THEN 'Endoge'
								   					   WHEN 'Epiges (juvenile)' THEN 'Epige'
								   					   WHEN 'Epige (juvenile)' THEN 'Epige'
								   					   ELSE observations_abondances.nom_espece END) as Espece,
			   (CASE WHEN observations.protocolefk=5 and observations_abondances.nom_espece isnull THEN 0
			   		 WHEN observations.protocolefk=5 and observations_abondances.nom_espece notnull THEN 1
			   		 when observations_abondances.presence_individu=true then 1
			   		 when observations_abondances.abondance isnull then 0
			   		 else observations_abondances.abondance end) AS Nombre_individus,
			   (case when observations_abondances.abondance>0 then 1
			   		 when observations_abondances.presence_individu=true then 1
			   		 WHEN observations.protocolefk=5 and observations_abondances.nom_espece isnull THEN 0
			   		 WHEN observations.protocolefk=5 and observations_abondances.nom_espece notnull THEN 1
			   		 else 0 END) as calcul_diversite,
			   	observations_abondances.photo_taxon-> 0 ->>'url' AS url_photo,
			   	(case when observations.protocolefk=3 then observations_abondances.nom_espece
			   		  else (select dico_species.nom_espece from dico_species where dico_species.speciepk = cast(observations_abondances.photo_taxon-> 0 ->>'speciepk' as integer)) end) as nom_espece
					FROM observations
			LEFT JOIN observations_abondances on observations_abondances.observationfk = observations.observationpk
			left join observations_details_vdt on observations_details_vdt.observationfk = observations.observationpk
			LEFT JOIN observateurs ON observateurs.observateurpk = observations.observateurfk
			LEFT JOIN groupes ON groupes.groupepk = observateurs.groupefk
			LEFT JOIN users ON users.userpk = groupes.userfk
			left join dico_structures on dico_structures.structurepk = groupes.structurefk
			left join dico_protocoles on dico_protocoles.protocolepk = observations.protocolefk
			left join zones_changes zc on zc.zonechangepk  = observations.zonechangefk
			left join zones z on z.zonepk = zc.zonefk
			WHERE
			users.debug is not true
			--and left(dico_structures.zipcode, 2) in('95','75','78','91', '92', '93', '77', '94')
			--and observations.observationpk=105508
			--and observations.protocolefk = '14'
			order by observations.observationpk
			)TAB
		group by
		TAB.Num_observation,
		TAB.nom,
		TAB.prenom,
		TAB.email,
		TAB.nom_etab,
		TAB.zip_etab,
		TAB.ville_etab,
		TAB.structurepk,
		TAB.Date_observation,
		TAB.protocole,
		TAB.groupepk,
		TAB.effectifs,
		TAB.type_etablissement,
		TAB.date_create,
		TAB.Espece,
		TAB.calcul_diversite,
		TAB.nom_espece,
		TAB.url_photo,
		TAB.latitude,
		TAB.longitude
		order by TAB.Num_observation
		)TAB2
)TAB3
group by
TAB3.Num_observation,
TAB3.observateur,
TAB3.etablissement,
TAB3.Date_observation,
TAB3.protocole,
TAB3.groupepk,
TAB3.effectifs,
TAB3.type_etablissement,
TAB3.date_create,
TAB3.latitude ,
TAB3.longitude
order by TAB3.Num_observation
