CREATE OR REPLACE FUNCTION monthly_report(address varchar(255),variable varchar(255),date_from timestamptz, date_to timestamptz, organization_ids varchar) 
RETURNS TABLE (
	value numeric(30,15),
	meter varchar(255),
	type varchar(255),
	addr varchar(255),
	var varchar(255)
)
SET search_path FROM CURRENT
AS $func$   
with previousMonth as (
	select
		max("value") as maxPrevious
	from
		"consumption_energy_measurements" as "consumption_energy_measurements"
	where
		("consumption_energy_measurements"."var" = $2
			and "consumption_energy_measurements"."addr" = $1
			and "consumption_energy_measurements"."timestamp" > ($3::timestamptz  - interval '1 month') and "consumption_energy_measurements"."timestamp" < $3	
    	) and 
		case -- limit access according to allowed organizations for the role
		  when $5 is null then 1 
		  when 
			"consumption_energy_measurements"."addr" in (
				select distinct 
					d.addr 
				from organizations_buildings b 
                inner join consumption_energy_devices d on	b.building_id = d.building_id 
                where b.organization_id = any(('{' || $5 || '}')::integer[])
            ) then 1
          else 0
        end = 1
), currentMonth as (
	select
		"meter",
		"type",
		"addr",
		"var",
		max("value") as maxValue		
	from
		"consumption_energy_measurements" as "consumption_energy_measurements"
	where
		("consumption_energy_measurements"."var" = $2
			and "consumption_energy_measurements"."addr" = $1
			and "consumption_energy_measurements"."timestamp" between $3 and $4			
	    ) and 
		case -- limit access according to allowed organizations for the role
		  when $5 is null then 1 
		  when 
			"consumption_energy_measurements"."addr" in (
				select distinct 
					d.addr 
				from organizations_buildings b 
                inner join consumption_energy_devices d on	b.building_id = d.building_id 
                where b.organization_id = any(('{' || $5 || '}')::integer[])
            ) then 1
          else 0
        end = 1
	group by
		"meter",
		"type",
		"addr",
		"var"
	) select
		case when var in ('EFwActi','EFwActiVT','EFwActiNT') then -- special handling for "činná energie"
			((maxValue/4) - (maxPrevious/4))
		else 
			maxValue - maxPrevious
		end as "value",
		"meter",
		"type",
		"addr",
		"var"
	from
		previousMonth, currentMonth;
$func$ LANGUAGE sql;