WITH geo_in_valide AS
(
SELECT ref_contrat, ref_id_geo
FROM usage.rel_contrat_geo	
JOIN usage.contrat ON rel_contrat_geo.ref_contrat=contrat.id_contrat
WHERE ref_statut=2
),
surf_contrat_valide AS
(
SELECT ref_contrat, ROUND (sum(surface)::NUMERIC,3) AS surface_ha
FROM (
	SELECT ref_contrat, surface
	FROM geo_in_valide
	JOIN mf.parcelle ON geo_in_valide.ref_id_geo=parcelle.cleparc
	UNION
	SELECT ref_contrat, ROUND (surf_sig_ha::NUMERIC,3) AS surface_ha
	FROM geo_in_valide
	JOIN spatial.usage_maison ON geo_in_valide.ref_id_geo=usage_maison.cleusage) req
GROUP BY ref_contrat
),
id_modEx_usage AS
(
SELECT id_mode_exploit AS id_me, intitule_usage as usage
FROM usage.mode_exploit
JOIN usage.type_usage ON mode_exploit.ref_usage=type_usage.id_typ_usage
),
site_par_usage AS
(
SELECT usage, count (DISTINCT (ref_site)) as nb_sites
FROM (
	SELECT mode_exploitation_id, ref_site
	FROM usage.contrat
	JOIN usage.rel_contrat_site ON ref_contrat=id_contrat
	WHERE ref_statut=2) req
JOIN id_modEx_usage ON req.mode_exploitation_id=id_modEx_usage.id_me
GROUP BY usage
),
surf_par_usage AS
(
SELECT usage, ROUND (sum(surface_ha)::NUMERIC,3) AS surface_ha
FROM 
	(SELECT mode_exploitation_id, surface_ha
	FROM usage.contrat
	JOIN surf_contrat_valide ON contrat.id_contrat=surf_contrat_valide.ref_contrat
	WHERE ref_statut=2) req
JOIN id_modEx_usage ON req.mode_exploitation_id=id_modEx_usage.id_me
GROUP BY usage
),
nbc_par_usage AS
(
SELECT usage, count (DISTINCT (id_contrat)) AS nb_contrats
FROM usage.contrat
JOIN id_modEx_usage ON contrat.mode_exploitation_id=id_modEx_usage.id_me
WHERE ref_statut=2
GROUP BY usage
)
SELECT req1.*,surface_ha
FROM 
	(SELECT nbc_par_usage.*,nb_sites
	FROM nbc_par_usage
	LEFT JOIN site_par_usage ON nbc_par_usage.usage=site_par_usage.usage) req1
LEFT JOIN surf_par_usage ON req1.usage=surf_par_usage.usage
