-- SELECTION DES PROFILS RMQS DE TYPE COMPOSITES --
WITH select_profils_composite_rmqs AS
(	
SELECT DISTINCT id_profil, type_profil_rmqs
FROM data_rmqs.l_profil_intervention
WHERE type_profil_rmqs IN ('C','M','N','O','B')
ORDER BY id_profil, type_profil_rmqs 	
) 
-- FILTRE DES PROJETS A NE PAS INTEGRER DANS LA TABLE analyses_rmqs_composites_hz --
,filtre_projets_analyses AS 
(
	WITH filtre_projets_intervention AS
	(
	-- Filtre sur les projets liés aux interventions
	SELECT DISTINCT l_pr_i.id_projet, l_p_i.id_profil, a.id_prelevement, a.id_analyse
	FROM data_rmqs.l_projet_intervention AS l_pr_i
	INNER JOIN data_rmqs.intervention AS i
	ON l_pr_i.id_intervention = i.id_intervention
	INNER JOIN data_rmqs.l_profil_intervention AS l_p_i
	ON i.id_intervention = l_p_i.id_intervention
	INNER JOIN data.analyse AS a
	ON l_p_i.id_profil = a.id_profil
	WHERE l_pr_i.id_projet IN (12,48) -- Projets Répétabilité et Réanalyse RMQS
	)
	, filtre_projets_prelevement AS
	(
	-- Filtre sur les projets liés aux prelevement
	SELECT l_p_pr.id_projet, a.id_profil, a.id_prelevement, a.id_analyse 
	FROM data_rmqs.l_projet_prelevement AS l_p_pr
	INNER JOIN data.analyse AS a
	ON l_p_pr.id_prelevement = a.id_prelevement
	WHERE id_projet IN (12,48) -- Projets Répétabilité et Réanalyse RMQS
	)
	-- Union de toutes les filtres sur les analyses
	SELECT * FROM filtre_projets_intervention
	UNION
	SELECT * FROM filtre_projets_prelevement
)
-- SELECTION DE L'ENSEMBLE DES ANALYSES CHIMIQUES POUR LES COMPOSITES RMQS --
,select_analyses_chimiques AS
(
	WITH select_toutes_analyses_d3 AS
	(
	-- Selection de toutes les analyses chimiques présentes dans la table resultat_analyse de DoneSol3ns
	SELECT p.id_profil, hz.no_horizon, pr.id_prelevement, a.id_analyse, m_a.no_methode, r_a.valeur, r_a.unite, 
	CASE WHEN r_a.determination IN 
	(SELECT DISTINCT determination FROM data.resultat_analyse WHERE ASCII(SUBSTRING(determination,1,1)) BETWEEN 48 AND 57) 
	THEN 'diox_'||determination 
	ELSE determination 
	END AS determination -- Pour gérer les nom de champs commençant par un chiffre
	FROM data.profil AS p
	INNER JOIN data.horizon AS hz
	ON p.id_profil=hz.id_profil 
	LEFT JOIN data.prelevement AS pr --On passe par la table prélevement pour les analyses du RMQS
	ON hz.id_profil=pr.id_profil AND hz.no_horizon=pr.no_horizon 
	LEFT JOIN data.analyse AS a
	ON pr.id_prelevement = a.id_prelevement
	LEFT JOIN data.resultat_analyse AS r_a
	ON a.id_analyse = r_a.id_analyse
	INNER JOIN meta.methode_analyse AS m_a
	ON r_a.id_methode = m_a.id_methode 
	WHERE valeur IS NOT NULL
	)
	,select_analyses_composites AS
	(
	-- Jointure pour ne garder que les analyses des profils de type composites 
	SELECT s_p_comp.*, s_t_a_d3.no_horizon, s_t_a_d3.id_prelevement, s_t_a_d3.id_analyse, s_t_a_d3.determination, s_t_a_d3.no_methode,
	(s_t_a_d3.determination||'_'||replace(s_t_a_d3.no_methode::text,'.','_')) AS det_meth,  s_t_a_d3.valeur, s_t_a_d3.unite
	FROM select_toutes_analyses_d3 AS s_t_a_d3
	INNER JOIN select_profils_composite_rmqs AS s_p_comp
	ON s_t_a_d3.id_profil=s_p_comp.id_profil
	ORDER BY id_profil, no_horizon, type_profil_rmqs
	)
	,filtre_projets_analyses_composites AS
	(
	-- Jointure pour ne garder que les analyses correspondantes aux filtres sur les projets 
	SELECT s_a_comp.*
	FROM select_analyses_composites AS s_a_comp
	LEFT JOIN filtre_projets_analyses AS f_pr_a
	ON s_a_comp.id_analyse = f_pr_a.id_analyse
	WHERE f_pr_a.id_analyse IS NULL
	)
	, select_analyses_repete_hz AS
	(
	-- Selection des analyses possedant des répétitions sur un même horizon --
	SELECT id_profil, type_profil_rmqs, no_horizon, det_meth, array_agg(id_analyse) AS id_analyse,
	(id_profil||'_'||no_horizon) AS id_prof_hz, array_agg(valeur) AS list_values, round(avg(valeur),3) AS valeur, 
	array_length(array_agg(valeur),1) AS nb_repet, coalesce(unite) AS unite
	FROM filtre_projets_analyses_composites
	GROUP BY id_profil, type_profil_rmqs, no_horizon, det_meth, unite
	HAVING array_length(array_agg(valeur),1)>1
	ORDER BY id_profil, no_horizon, det_meth, unite
	)
	, select_analyses_uniques_hz AS
	(
	-- Selection des analyses uniques sur un même horizon --
	SELECT id_profil, type_profil_rmqs, no_horizon, det_meth, array_agg(id_analyse) AS id_analyse,
	(id_profil||'_'||no_horizon) AS id_prof_hz, array_agg(valeur) AS list_values, valeur, 
	array_length(array_agg(valeur),1) AS nb_repet, coalesce(unite) AS unite
	FROM filtre_projets_analyses_composites
	GROUP BY id_profil, type_profil_rmqs, no_horizon, id_prelevement, det_meth, valeur, unite
	HAVING array_length(array_agg(valeur),1)=1
	ORDER BY id_profil, no_horizon, det_meth, unite
	)
	-- Union pour récupérer l'ensemble des résultats d'analyse agrégés sur l'horizon --
	SELECT * FROM select_analyses_repete_hz
	UNION
	SELECT * FROM select_analyses_uniques_hz
)
SELECT * 
FROM select_analyses_chimiques
WHERE nb_repet>2 and det_meth like '%166_1'
ORDER BY id_analyse DESC