WITH contrat_agri_valide AS
(
SELECT id_contrat
FROM usage.contrat
JOIN usage.mode_exploit ON mode_exploit.id_mode_exploit=contrat.mode_exploitation_id
WHERE contrat.ref_statut=2 AND mode_exploit.ref_usage=3
),

contrat_brce_valide AS
(
SELECT id_contrat
FROM usage.contrat
WHERE contrat.ref_statut=2 AND type_contrat_id=7
),

contrat_cmdsafer_valide AS
(
SELECT id_contrat
FROM usage.contrat
WHERE contrat.ref_statut=2 AND type_contrat_id=8
),

geo_in_agri_valide AS
(
SELECT DISTINCT(ref_id_geo) ref_id_geo
FROM usage.rel_contrat_geo	
JOIN contrat_agri_valide ON rel_contrat_geo.ref_contrat=contrat_agri_valide.id_contrat
),

geo_in_brce_valide AS
(
SELECT DISTINCT(ref_id_geo) ref_id_geo
FROM usage.rel_contrat_geo	
JOIN contrat_brce_valide ON rel_contrat_geo.ref_contrat=contrat_brce_valide.id_contrat
),

geo_in_cmdsafer_valide AS
(
SELECT DISTINCT(ref_id_geo) ref_id_geo
FROM usage.rel_contrat_geo	
JOIN contrat_cmdsafer_valide ON rel_contrat_geo.ref_contrat=contrat_cmdsafer_valide.id_contrat
),

surf_contrat_agri AS
(
SELECT sum(surface) as surface
FROM (
	SELECT surface
	FROM geo_in_agri_valide
	JOIN mf.parcelle ON geo_in_agri_valide.ref_id_geo=parcelle.cleparc
	UNION
	SELECT surf_sig_ha AS surface
	FROM geo_in_agri_valide
	JOIN spatial.usage_maison ON geo_in_agri_valide.ref_id_geo=usage_maison.cleusage) req
),
surf_contrat_brce AS
(
SELECT sum(surface) as surface
FROM (
	SELECT surface
	FROM geo_in_brce_valide
	JOIN mf.parcelle ON geo_in_brce_valide.ref_id_geo=parcelle.cleparc
	UNION
	SELECT surf_sig_ha AS surface
	FROM geo_in_brce_valide
	JOIN spatial.usage_maison ON geo_in_brce_valide.ref_id_geo=usage_maison.cleusage) req
),
surf_contrat_cmdsafer AS
(
SELECT sum(surface) as surface
FROM (
	SELECT surface
	FROM geo_in_cmdsafer_valide
	JOIN mf.parcelle ON geo_in_cmdsafer_valide.ref_id_geo=parcelle.cleparc
	UNION
	SELECT surf_sig_ha AS surface
	FROM geo_in_cmdsafer_valide
	JOIN spatial.usage_maison ON geo_in_cmdsafer_valide.ref_id_geo=usage_maison.cleusage) req
),

nb_sites_agri AS
(
SELECT count (DISTINCT (ref_site)) as nb_sites
FROM (
	SELECT ref_site
	FROM contrat_agri_valide
	JOIN usage.rel_contrat_site ON ref_contrat=id_contrat) req
),
nb_agri AS
(
SELECT  count (DISTINCT (ref_usager_id)) AS nb_agri
FROM contrat_agri_valide
JOIN usage.contrat ON contrat_agri_valide.id_contrat=contrat.id_contrat
)

SELECT information, nombre, surface_ha
FROM (
SELECT 1 AS ordre,
	'Agriculteurs sous contrat' AS information,
	(SELECT * FROM nb_agri) AS Nombre,
	ROUND (surface::NUMERIC,3) AS surface_ha FROM surf_contrat_agri
UNION
SELECT 2 AS ordre,
	'Sites concernés' AS information,
	(SELECT * FROM nb_sites_agri) AS Nombre,
	ROUND (surface::NUMERIC,3) AS surface_ha FROM surf_contrat_agri
UNION
SELECT 3 AS ordre,
	'Bail rural à clauses environnementales' AS information,
	(SELECT count (DISTINCT (id_contrat)) FROM contrat_brce_valide) AS Nombre,
	ROUND (surface::NUMERIC,3) AS surface_ha FROM surf_contrat_brce
UNION
SELECT 4 as ordre,
	'CMD SAFER' AS information,
	(SELECT count (DISTINCT (id_contrat)) FROM contrat_cmdsafer_valide) AS Nombre,
	(SELECT ROUND (surface::NUMERIC,3) FROM surf_contrat_cmdsafer) AS surface_ha) req
ORDER BY ordre

	