WITH 
intersected as (
	SELECT 
    	type,
    	-- intersected geometries
    	CASE
    		-- Heidelberg  8.6,49.33,8.8,49.49
    		WHEN 
    			(
    			nat.the_geom && ST_Transform(ST_MakeEnvelope( {left0},{bottom0},{right0},{top0} ,4326),3857) -- a && b : a and b's bbox intersect
    			OR
    			nat.the_geom && ST_Transform(ST_MakeEnvelope( {left1},{bottom1},{right1},{top1} ,4326),3857) -- a && b : a and b's bbox intersect
    			)
    			AND
    			(
    			nat.the_geom @ ST_Transform(ST_MakeEnvelope( {left0},{bottom0},{right0},{top0},4326),3857) -- a @ b : a completly contained by b
    			OR
    			nat.the_geom @ ST_Transform(ST_MakeEnvelope( {left1},{bottom1},{right1},{top1},4326),3857) -- a @ b : a completly contained by b
    			)
    		THEN nat.the_geom
    		-- only do the real intersection work if polygons are not completely contained in bbox
			ELSE ST_Multi(
      				ST_Intersection(ST_MakeValid(nat.the_geom), 
      					ST_Collect(
      					ST_Transform(ST_MakeEnvelope( {left0},{bottom0},{right0},{top0} ,4326),3857),
      					ST_Transform(ST_MakeEnvelope( {left1},{bottom1},{right1},{top1} ,4326),3857)
      					)
      				)
      			 )
    	END as the_geom
    FROM public.naturals nat
    WHERE 
    	ST_Intersects(nat.the_geom, ST_Transform(ST_MakeEnvelope({left0},{bottom0},{right0},{top0} ,4326),3857))
    	OR
    	ST_Intersects(nat.the_geom, ST_Transform(ST_MakeEnvelope({left1},{bottom1},{right1},{top1} ,4326),3857))  
),
classes AS(
    SELECT 
        CASE 
            WHEN type IN ('water', 'riverbank', 'reservoir', 'basin', 'dock', 'canal', 'pond') THEN 'water_bodies'
            WHEN type IN ('greenhouse_horticulture', 'greenhouse', 'farmland', 'farm', 'farmyard') THEN 'arable_land'
			WHEN type IN ('camp_site', 'stadium', 'recreation_ground', 'golf_course', 'common', 'raceway', 'sports_center', 'playground', 'pitch', 'village_green', 'allotments', 'cemetery', 'park', 'zoo', 'track', 'garden') THEN 'artif_non_agri_vegetated'
			WHEN type IN ('forest', 'wood') THEN 'forests'
			WHEN type IN ('rest_area', 'apron', 'aerodrome', 'runway', 'parking', 'railway', 'lock', 'marina', 'industrial', 'commercial', 'retail', 'harbour', 'port') THEN 'ind_com'
			WHEN type IN ('quarry', 'construction', 'landfill', 'brownfield') THEN 'mine_dump_constr'
			WHEN type IN ('desert', 'bare_rock', 'cliff', 'fell', 'sand', 'scree', 'beach', 'mud', 'glacier', 'rock') THEN 'open_spaces_no_veg'
			WHEN type IN ('meadow') THEN 'pastures'
			WHEN type IN ('plant_nursery', 'vineyard', 'orchard') THEN 'permanent_crops'
			WHEN type IN ('logging', 'grass', 'greenfield', 'scrub', 'heath', 'grassland') THEN 'scrub_herbs'
			WHEN type IN ('pedestrian', 'garages', 'residential') THEN 'urban_fabric'
			WHEN type IN ('marsh', 'wetland') THEN 'wetlands'
			WHEN type IN ('salt_pond', 'tidal') THEN 'coastal_wetlands'		
            ELSE 'other' 
        END as class,
        ST_Area(ST_Transform(the_geom,4326)::geography) as area
        FROM intersected

        -- LIMIT 10
    ),
   
sum_classes AS (   
	SELECT class, SUM(area) as sum_area 
		FROM classes 
	    GROUP BY class
	    ORDER BY sum_area DESC
	),
sum_total AS (
	SELECT COALESCE( SUM(sum_area), 0 ) as total_area
		FROM sum_classes
)

SELECT * FROM sum_classes
UNION ALL
SELECT 'unmapped_area'::text as class, ((ST_Area(ST_MakeEnvelope({left0},{bottom0},{right0},{top0} ,4326)::geography) + ST_Area(ST_MakeEnvelope({left1},{bottom1},{right1},{top1} ,4326)::geography) ) - (SELECT * FROM sum_total)) as sum_area
;
