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, surf_sig_ha AS surface
	FROM geo_in_valide
	JOIN spatial.usage_maison ON geo_in_valide.ref_id_geo=usage_maison.cleusage) req
GROUP BY ref_contrat
),
site_par_me AS
(
SELECT intitule AS mode_exploitation, 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 usage.mode_exploit ON req.mode_exploitation_id=mode_exploit.id_mode_exploit
GROUP BY mode_exploitation
),
surf_par_me AS
(
SELECT intitule AS mode_exploitation, 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 usage.mode_exploit ON req.mode_exploitation_id=mode_exploit.id_mode_exploit
GROUP BY mode_exploitation
),
nbc_par_me AS
(
SELECT intitule AS mode_exploitation, count (DISTINCT (id_contrat)) AS nb_contrats
FROM usage.contrat
JOIN usage.mode_exploit ON contrat.mode_exploitation_id=mode_exploit.id_mode_exploit
WHERE ref_statut=2
GROUP BY mode_exploitation
)
SELECT req1.*,surface_ha
FROM 
	(SELECT nbc_par_me.*,nb_sites
	FROM nbc_par_me
	LEFT JOIN site_par_me ON nbc_par_me.mode_exploitation=site_par_me.mode_exploitation) req1
LEFT JOIN surf_par_me ON req1.mode_exploitation=surf_par_me.mode_exploitation
