CREATE TABLE vpalac_meter_mapping (
	location_code int4 NULL,
	met_nazev varchar(100) NULL,
	sublocation_id int4 NULL,
	me_extid varchar(100) NULL,
	location_name varchar(100) NULL,
	var_id int4 NULL
);

CREATE TABLE consumption_energy_buildings (
	id int4 NOT NULL,
	building_name varchar(255) NOT NULL,
	description varchar(255) NOT NULL,
	building_address_code varchar(255) NOT NULL,
	building_label varchar(255) NOT NULL,
	current_note varchar(255) NOT NULL,
	main_use varchar(255) NOT NULL,
	secondary_use varchar(255) NOT NULL,
	year_of_construction varchar(255) NOT NULL,
	method_of_protection varchar(255) NOT NULL,
	built_up_area varchar(255) NOT NULL,
	heated_bulding_volume varchar(255) NOT NULL,
	students_count varchar(255) NOT NULL,
	employees_count varchar(255) NOT NULL,
	classrooms_count varchar(255) NOT NULL,
	beds_count varchar(255) NOT NULL,
	eno_id varchar(255) NOT NULL,
	csu_code varchar(255) NOT NULL,
	ku_code varchar(255) NOT NULL,
	allotment_number varchar(255) NOT NULL,
	registration_unit varchar(255) NOT NULL,
	gas_consumption_normatives varchar(255) NOT NULL,
	heat_consumption_normatives varchar(255) NOT NULL,
	water_consumption_normatives varchar(255) NOT NULL,
	electricity_consumption_normatives_per_person varchar(255) NOT NULL,
	electricity_consumption_normatives varchar(255) NOT NULL,
	energetic_management varchar(255) NOT NULL,
	opening_hours varchar(255) NOT NULL,
	weekend_opening_hours varchar(255) NOT NULL,
	latitude varchar(255) NOT NULL,
	longitude varchar(255) NOT NULL,
	address_street varchar(255) NOT NULL,
	address_house_number varchar(255) NOT NULL,
	address_city varchar(255) NOT NULL,
	address_country varchar(255) NOT NULL,
	address_mail varchar(255) NOT NULL,
	address_phone varchar(255) NOT NULL,
	address_web_address varchar(255) NOT NULL,
	penb_penbnumber varchar(255) NOT NULL,
	penb_issue_date varchar(255) NOT NULL,
	penb_total_building_envelope_area varchar(255) NOT NULL,
	penb_volume_factor_of_avshape varchar(255) NOT NULL,
	penb_total_energy_reference_area varchar(255) NOT NULL,
	penb_total_provided_energy varchar(255) NOT NULL,
	penb_total_provided_energy_category varchar(255) NOT NULL,
	penb_primary_non_renewable_energy varchar(255) NOT NULL,
	penb_primary_non_renewable_energy_category varchar(255) NOT NULL,
	penb_building_envelope varchar(255) NOT NULL,
	penb_building_envelope_category varchar(255) NOT NULL,
	penb_heating varchar(255) NOT NULL,
	penb_heating_category varchar(255) NOT NULL,
	penb_cooling varchar(255) NOT NULL,
	penb_cooling_category varchar(255) NOT NULL,
	penb_ventilation varchar(255) NOT NULL,
	penb_ventilation_category varchar(255) NOT NULL,
	penb_humidity_adjustment varchar(255) NOT NULL,
	penb_humidity_adjustment_category varchar(255) NOT NULL,
	penb_warm_water varchar(255) NOT NULL,
	penb_warm_water_category varchar(255) NOT NULL,
	penb_lighting varchar(255) NOT NULL,
	penb_lighting_category varchar(255) NOT NULL,
	energy_audits_energy_audit varchar(255) NOT NULL,
	energy_audits_earegistration_number varchar(255) NOT NULL,
	energy_audits_created_at varchar(255) NOT NULL,
	building_envelope_side_wall_prevailing_construction varchar(255) NOT NULL,
	building_envelope_side_wall_area varchar(255) NOT NULL,
	building_envelope_side_wall_heat_insulation varchar(255) NOT NULL,
	building_envelope_side_wall_year_of_adjustment varchar(255) NOT NULL,
	building_envelope_side_wall_technical_condition varchar(255) NOT NULL,
	building_envelope_filling_of_hole_construction varchar(255) NOT NULL,
	building_envelope_filling_of_hole_area varchar(255) NOT NULL,
	building_envelope_filling_of_hole_year_of_adjustment varchar(255) NOT NULL,
	building_envelope_filling_of_hole_technical_condition varchar(255) NOT NULL,
	building_envelope_roof_construction varchar(255) NOT NULL,
	building_envelope_roof_area varchar(255) NOT NULL,
	building_envelope_roof_thermal_insulation varchar(255) NOT NULL,
	building_envelope_roof_year_of_adjustment varchar(255) NOT NULL,
	building_envelope_roof_technical_condition varchar(255) NOT NULL,
	building_envelope_floor_of_the_lowest_heated_floor_construction varchar(255) NOT NULL,
	building_envelope_floor_of_the_lowest_heated_floor_area varchar(255) NOT NULL,
	floor_of_the_lowest_heated_floor_thermal_insulation varchar(255) NOT NULL,
	building_envelope_floor_of_the_lowest_heated_floor_year_of_adju varchar(255) NOT NULL,
	floor_of_the_lowest_heated_floor_technical_condition varchar(255) NOT NULL,
	fuel_and_energy_coal varchar(255) NOT NULL,
	fuel_and_energy_gas varchar(255) NOT NULL,
	fuel_and_energy_electricity varchar(255) NOT NULL,
	fuel_and_energy_czt varchar(255) NOT NULL,
	fuel_and_energy_oze varchar(255) NOT NULL,
	fuel_and_energy_other varchar(255) NOT NULL,
	technical_equipment_heating_main_source_of_heat varchar(255) NOT NULL,
	technical_equipment_heating_heat_percentage varchar(255) NOT NULL,
	technical_equipment_heating_secondary_source_of_heat varchar(255) NOT NULL,
	technical_equipment_heating_heating_system varchar(255) NOT NULL,
	technical_equipment_heating_year varchar(255) NOT NULL,
	technical_equipment_heating_technical_condition varchar(255) NOT NULL,
	technical_equipment_cooling_cooling_system varchar(255) NOT NULL,
	technical_equipment_cooling_cooling_area_percentage varchar(255) NOT NULL,
	technical_equipment_cooling_year varchar(255) NOT NULL,
	technical_equipment_cooling_technical_condition varchar(255) NOT NULL,
	technical_equipment_ventilation_ventilation varchar(255) NOT NULL,
	technical_equipment_ventilation_year varchar(255) NOT NULL,
	technical_equipment_ventilation_technical_condition varchar(255) NOT NULL,
	technical_equipment_humidity_adjustment_humidity_adjustment varchar(255) NOT NULL,
	technical_equipment_humidity_adjustment_year varchar(255) NOT NULL,
	technical_equipment_humidity_adjustment_technical_condition varchar(255) NOT NULL,
	technical_equipment_hot_water_predominant_way_of_heating_tv varchar(255) NOT NULL,
	technical_equipment_hot_water_hot_water_source varchar(255) NOT NULL,
	technical_equipment_hot_water_year varchar(255) NOT NULL,
	technical_equipment_hot_water_technical_condition varchar(255) NOT NULL,
	technical_equipment_lighting_lighting varchar(255) NOT NULL,
	technical_equipment_lighting_year varchar(255) NOT NULL,
	technical_equipment_lighting_technical_condition varchar(255) NOT NULL,
	technical_equipment_lighting_other_technological_elements varchar(255) NOT NULL,
	technical_equipment_lighting_measurement_method varchar(255) NOT NULL,
	oze_energy_production_solar_energy_photovoltaic varchar(255) NOT NULL,
	oze_energy_production_solar_energy_photothermal varchar(255) NOT NULL,
	oze_energy_production_integrated_turbines_wind_energy varchar(255) NOT NULL,
	oze_energy_production_heat_pump varchar(255) NOT NULL,
	waste_and_emissions_solid_waste_production varchar(255) NOT NULL,
	waste_and_emissions_tied_co2_emissions varchar(255) NOT NULL,
	waste_and_emissions_sox_emissions varchar(255) NOT NULL,
	waste_and_emissions_operating_co_2emissions varchar(255) NOT NULL,
	link varchar(255) NOT NULL,
	create_batch_id int8 NULL,
	created_at timestamp NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamp NULL,
	updated_by varchar(150) NULL,
	CONSTRAINT consumption_energy_buildings_pkey PRIMARY KEY (id)
);
CREATE INDEX consumption_energy_buildings_create_batch ON consumption_energy_buildings USING btree (create_batch_id);
CREATE INDEX consumption_energy_buildings_update_batch ON consumption_energy_buildings USING btree (update_batch_id);


CREATE TABLE consumption_energy_consumption (
	time_utc timestamp NOT NULL,
	value numeric(30, 15) NOT NULL,
	addr varchar(255) NOT NULL,
	var varchar(255) NOT NULL,
	"type" varchar(255) NULL,
	commodity varchar(255) NULL,
	unit varchar(255) NULL,
	meter varchar(255) NOT NULL,
	create_batch_id int8 NULL,
	created_at timestamp NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamp NULL,
	updated_by varchar(150) NULL,
	CONSTRAINT consumption_energy_consumption_pkey PRIMARY KEY (time_utc, addr, var, meter)
);
CREATE INDEX consumption_energy_consumption_create_batch ON consumption_energy_consumption USING btree (create_batch_id);
CREATE INDEX consumption_energy_consumption_update_batch ON consumption_energy_consumption USING btree (update_batch_id);


CREATE TABLE consumption_energy_devices (
	id int4 NOT NULL,
	addr varchar(255) NOT NULL,
	description varchar(255) NOT NULL,
	meter_number varchar(255) NOT NULL,
	meter_index varchar(255) NOT NULL,
	location_number varchar(255) NOT NULL,
	location_description varchar(255) NOT NULL,
	include_in_evaluation varchar(255) NOT NULL,
	meter_type varchar(255) NOT NULL,
	category varchar(255) NOT NULL,
	unit varchar(255) NOT NULL,
	replaced_meter_id varchar(255) NOT NULL,
	deleted varchar(255) NOT NULL,
	building_id int4 NOT NULL,
	create_batch_id int8 NULL,
	created_at timestamp NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamp NULL,
	updated_by varchar(150) NULL,
	CONSTRAINT consumption_energy_devices_pkey PRIMARY KEY (id)
);
CREATE INDEX consumption_energy_devices_create_batch ON consumption_energy_devices USING btree (create_batch_id);
CREATE INDEX consumption_energy_devices_update_batch ON consumption_energy_devices USING btree (update_batch_id);

CREATE TABLE vpalac_measurement (
	var_id int4 NOT NULL,
	time_measurement int8 NOT NULL,
	value numeric NULL,
	created_at timestamptz NULL DEFAULT now(),
	updated_at timestamptz NULL,
	CONSTRAINT vpalac_measurement_pk PRIMARY KEY (var_id, time_measurement)
);

CREATE TABLE vpalac_measuring_equipment (
	me_id int4 NOT NULL,
	me_fakt bool NULL,
	umisteni varchar(250) NULL,
	mis_nazev varchar(255) NULL,
	me_serial varchar(50) NULL,
	me_od timestamptz NULL,
	me_do timestamptz NULL,
	pot_id int4 NOT NULL,
	poc_typode int4 NULL,
	var_id int4 NULL,
	mis_id int4 NULL,
	met_id int4 NULL,
	me_extid varchar(50) NULL,
	me_zapoc bool NULL,
	me_plom varchar(250) NULL,
	created_at timestamptz NULL DEFAULT now(),
	updated_at timestamptz NULL,
	CONSTRAINT vpalac_measuring_equipment_pkey PRIMARY KEY (me_id, pot_id)
);

CREATE TABLE vpalac_meter_type (
	met_id int4 NOT NULL,
	met_druh int4 NULL,
	met_nazev varchar(150) NULL,
	met_kod varchar(50) NULL,
	met_ziv numeric NULL,
	vyr_zkr varchar(50) NULL,
	fir_id int4 NULL,
	medium int4 NULL,
	created_at timestamptz NULL DEFAULT now(),
	updated_at timestamptz NULL,
	CONSTRAINT vpalac_meter_type_pkey PRIMARY KEY (met_id)
);

CREATE TABLE vpalac_type_measuring_equipment (
	cik_nazev varchar(150) NULL,
	lt_key varchar(50) NOT NULL,
	cik_fk int4 NULL,
	cik_char varchar(50) NULL,
	cik_cislo int4 NULL,
	cik_double numeric NULL,
	cik_pzn varchar(50) NULL,
	cik_cislo2 int4 NULL,
	cik_zprac varchar(50) NULL,
	cik_akt bool NULL,
	created_at timestamptz NULL DEFAULT now(),
	updated_at timestamptz NULL,
	CONSTRAINT vpalac_type_measuring_equipment_pkey PRIMARY KEY (lt_key)
);

CREATE TABLE vpalac_units (
	pot_id int4 NULL,
	lt_key varchar(50) NOT NULL,
	jed_nazev varchar(150) NULL,
	jed_zkr varchar(50) NULL,
	pot_defcolor varchar(20) NULL,
	pot_type int4 NULL,
	jed_id int4 NULL,
	ptv_id int4 NULL,
	created_at timestamptz NULL DEFAULT now(),
	updated_at timestamptz NULL,
	CONSTRAINT vpalac_units_pkey PRIMARY KEY (lt_key)
);

-- pohledy

CREATE OR REPLACE VIEW v_consumption_energy_avg_consumption_min
AS SELECT za_minutu.addr,
    za_minutu.var,
    za_minutu.meter,
    avg(za_minutu.spotreba_za_minutu) AS consumption_min
   FROM ( SELECT consumption_energy_consumption.time_utc,
            consumption_energy_consumption.addr,
            consumption_energy_consumption.var,
            consumption_energy_consumption.commodity,
            consumption_energy_consumption.unit,
            consumption_energy_consumption.value,
            consumption_energy_consumption.meter,
                CASE
                    WHEN ((consumption_energy_consumption.value - lag(consumption_energy_consumption.value) OVER (PARTITION BY consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.meter ORDER BY consumption_energy_consumption.time_utc))::double precision / date_part('epoch'::text, consumption_energy_consumption.time_utc - lag(consumption_energy_consumption.time_utc) OVER (PARTITION BY consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.meter ORDER BY consumption_energy_consumption.time_utc)) / 60::double precision) < 0::double precision THEN 0::double precision
                    ELSE (consumption_energy_consumption.value - lag(consumption_energy_consumption.value) OVER (PARTITION BY consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.meter ORDER BY consumption_energy_consumption.time_utc))::double precision / (date_part('epoch'::text, consumption_energy_consumption.time_utc - lag(consumption_energy_consumption.time_utc) OVER (PARTITION BY consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.meter ORDER BY consumption_energy_consumption.time_utc)) / 60::double precision)
                END AS spotreba_za_minutu
           FROM consumption_energy_consumption
          WHERE consumption_energy_consumption.var::text = 'core'::text AND (consumption_energy_consumption.addr::text ~~ '%F%'::text OR consumption_energy_consumption.addr::text = '/2.1/VP2'::text) AND consumption_energy_consumption.value > 0::numeric) za_minutu
  GROUP BY za_minutu.addr, za_minutu.var, za_minutu.meter;


CREATE OR REPLACE VIEW v_consumption_energy_alerts
AS WITH vymena_s AS (
         SELECT es.time_utc,
            es.addr,
            es.var,
            es.commodity,
            es.unit,
            es.value,
            es.meter,
            lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_value,
            lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_timeutc,
            es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS delta_value,
            date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision AS minut,
            min.consumption_min,
            (es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc))::double precision / (date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision) AS za_minutu_ted,
                CASE
                    WHEN (es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) < 0::numeric THEN 'pokles'::text
                    WHEN min.consumption_min > 0::double precision AND ((es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc))::double precision / (date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision)) > (1000::double precision * min.consumption_min) THEN 'narust'::text
                    ELSE NULL::text
                END AS vymena
           FROM consumption_energy_consumption es
             JOIN v_consumption_energy_avg_consumption_min min ON es.addr::text = min.addr::text AND es.var::text = min.var::text AND es.meter::text = min.meter::text
          WHERE es.var::text = 'core'::text AND es.addr::text ~~ '%F%'::text AND es.value > 0::numeric
        )
 SELECT vymena_s.time_utc,
    vymena_s.addr,
    vymena_s.var,
    vymena_s.commodity,
    vymena_s.unit,
    vymena_s.value,
    vymena_s.meter,
    vymena_s.last_value,
    vymena_s.last_timeutc,
    vymena_s.delta_value,
    vymena_s.minut,
    vymena_s.consumption_min,
    vymena_s.za_minutu_ted,
    vymena_s.vymena
   FROM vymena_s
  WHERE vymena_s.vymena IS NOT NULL;

CREATE OR REPLACE VIEW v_consumption_energy_change
AS SELECT a.time_utc,
    a.addr,
    a.var,
    a.commodity,
    a.unit,
    a.value,
    a.meter,
    a.last_value,
    a.last_timeutc,
    a.delta_value,
    a.minut,
    a.consumption_min,
    a.za_minutu_ted,
    a.vymena
   FROM ( SELECT es.time_utc,
            es.addr,
            es.var,
            es.commodity,
            es.unit,
            es.value,
            es.meter,
            lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_value,
            lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_timeutc,
            es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS delta_value,
            date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision AS minut,
            min.consumption_min,
            (es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc))::double precision / (date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision) AS za_minutu_ted,
                CASE
                    WHEN (es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) < 0::numeric THEN 'pokles'::text
                    WHEN min.consumption_min > 0::double precision AND ((es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc))::double precision / (date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision)) > (1000::double precision * min.consumption_min) THEN 'narust'::text
                    ELSE NULL::text
                END AS vymena
           FROM consumption_energy_consumption es
             JOIN v_consumption_energy_avg_consumption_min min ON es.addr::text = min.addr::text AND es.var::text = min.var::text AND es.meter::text = min.meter::text
          WHERE es.var::text = 'core'::text AND es.addr::text ~~ '%F%'::text AND es.value > 0::numeric) a
  WHERE a.vymena IS NOT NULL;

CREATE OR REPLACE VIEW v_consumption_energy_delta
AS WITH delta AS (
         SELECT es.time_utc,
            es.addr,
            es.var,
            es.commodity,
            es.unit,
            es.value,
            es.meter,
            lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_value,
            lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS last_timeutc,
            es.value - lag(es.value) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc) AS delta_value,
            min.consumption_min AS prumerna_spotreba,
            date_part('epoch'::text, es.time_utc - lag(es.time_utc) OVER (PARTITION BY es.addr, es.var, es.meter ORDER BY es.time_utc)) / 60::double precision AS minut_mereni
           FROM consumption_energy_consumption es
             JOIN v_consumption_energy_avg_consumption_min min ON es.addr::text = min.addr::text AND es.var::text = min.var::text AND es.meter::text = min.meter::text
          WHERE es.var::text = 'core'::text AND (es.addr::text ~~ '%F%'::text OR es.addr::text = '/2.1/VP2'::text) AND es.value > 0::numeric
        )
 SELECT delta.time_utc,
    delta.addr,
    delta.var,
    delta.commodity,
    delta.unit,
    delta.value,
    delta.meter,
    delta.last_value,
    delta.last_timeutc,
        CASE
            WHEN abs(delta.delta_value::double precision / 60::double precision) > abs(1000::double precision * delta.prumerna_spotreba) THEN 0::numeric
            ELSE delta.delta_value
        END AS delta_value,
    delta.minut_mereni
   FROM delta;

CREATE OR REPLACE VIEW v_consumption_energy_deduct_month_ptv
AS SELECT date_part('year'::text, consumption_energy_consumption.time_utc) AS rok,
    date_part('month'::text, consumption_energy_consumption.time_utc) AS mesic,
    consumption_energy_consumption.addr,
    consumption_energy_consumption.var,
    consumption_energy_consumption.commodity,
    consumption_energy_consumption.unit,
    sum(consumption_energy_consumption.delta_value) AS value,
    max(consumption_energy_consumption.time_utc::date) AS data_do,
    count(*) AS count
   FROM v_consumption_energy_delta consumption_energy_consumption
  WHERE consumption_energy_consumption.meter::text <> '300066393'::text OR consumption_energy_consumption.time_utc::date <= '2021-04-01'::date
  GROUP BY (date_part('year'::text, consumption_energy_consumption.time_utc)), (date_part('month'::text, consumption_energy_consumption.time_utc)), consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.commodity, consumption_energy_consumption.unit;


CREATE OR REPLACE VIEW v_consumption_energy_consumption_month_ptv
AS SELECT v_consumption_energy_deduct_month_ptv.rok,
    v_consumption_energy_deduct_month_ptv.mesic,
    v_consumption_energy_deduct_month_ptv.addr,
    v_consumption_energy_deduct_month_ptv.var,
    v_consumption_energy_deduct_month_ptv.commodity,
    v_consumption_energy_deduct_month_ptv.unit,
    v_consumption_energy_deduct_month_ptv.value,
    v_consumption_energy_deduct_month_ptv.data_do,
    v_consumption_energy_deduct_month_ptv.count,
    NULL::numeric AS previous_month_value,
    v_consumption_energy_deduct_month_ptv.value AS mesicni_spotreba
   FROM v_consumption_energy_deduct_month_ptv
  WHERE v_consumption_energy_deduct_month_ptv.addr::text <> ALL (ARRAY['/2.1/TVF1'::character varying::text, '/2.1/VF1'::character varying::text, '/2.1/VP2'::character varying::text])
UNION
 SELECT tvf1.rok,
    tvf1.mesic,
    tvf1.addr,
    tvf1.var,
    tvf1.commodity,
    tvf1.unit,
    vf1.value - vp2.value + tvf1.value AS value,
    tvf1.data_do,
    tvf1.count,
    NULL::numeric AS previous_month_value,
    vf1.value - vp2.value + tvf1.value AS mesicni_spotreba
   FROM v_consumption_energy_deduct_month_ptv tvf1
     LEFT JOIN v_consumption_energy_deduct_month_ptv vf1 ON vf1.addr::text = '/2.1/VF1'::text AND vf1.rok = tvf1.rok AND vf1.mesic = tvf1.mesic
     LEFT JOIN v_consumption_energy_deduct_month_ptv vp2 ON vp2.addr::text = '/2.1/VP2'::text AND vp2.rok = tvf1.rok AND vp2.mesic = tvf1.mesic
  WHERE tvf1.addr::text = '/2.1/TVF1'::text;

CREATE OR REPLACE VIEW v_consumption_energy_consumption_month_t_f
AS SELECT v_consumption_energy_consumption_month_ptv.rok,
    v_consumption_energy_consumption_month_ptv.mesic,
    v_consumption_energy_consumption_month_ptv.addr,
    v_consumption_energy_consumption_month_ptv.var,
    v_consumption_energy_consumption_month_ptv.commodity,
    v_consumption_energy_consumption_month_ptv.unit,
    v_consumption_energy_consumption_month_ptv.value,
    v_consumption_energy_consumption_month_ptv.data_do,
    v_consumption_energy_consumption_month_ptv.count,
    v_consumption_energy_consumption_month_ptv.previous_month_value,
    v_consumption_energy_consumption_month_ptv.mesicni_spotreba
   FROM v_consumption_energy_consumption_month_ptv
  WHERE v_consumption_energy_consumption_month_ptv.addr::text ~~ '%/TF%'::text AND v_consumption_energy_consumption_month_ptv.rok > 2018::double precision;


CREATE OR REPLACE VIEW v_consumption_gas_reserve
AS WITH kalendar AS (
         SELECT den.den::date AS den
           FROM generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, now() - '1 day'::interval, '1 day'::interval) den(den)
        ), meraky AS (
         SELECT DISTINCT consumption_energy_devices.addr
           FROM consumption_energy_devices
          WHERE consumption_energy_devices.meter_type::text = 'Plynoměr'::text
        ), daily_delta_core AS (
         SELECT date_trunc('day'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval)::date AS measured_from,
            v_consumption_energy_delta.addr,
            v_consumption_energy_delta.var,
            sum(v_consumption_energy_delta.delta_value) AS delta_value
           FROM v_consumption_energy_delta
          WHERE v_consumption_energy_delta.commodity::text = 'gas'::text AND date_part('year'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval) >= 2019::double precision
          GROUP BY (date_trunc('day'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval)::date), v_consumption_energy_delta.addr, v_consumption_energy_delta.var
        ), daily_delta_core2 AS (
         SELECT date_trunc('day'::text, consumption_energy_consumption.time_utc - '06:00:00'::interval)::date AS measured_from,
            consumption_energy_consumption.addr,
            consumption_energy_consumption.var,
            sum(consumption_energy_consumption.value) AS delta_value
           FROM consumption_energy_consumption
          WHERE consumption_energy_consumption.var::text = 'core2'::text AND consumption_energy_consumption.time_utc::date > '2018-12-31'::date
          GROUP BY (date_trunc('day'::text, consumption_energy_consumption.time_utc - '06:00:00'::interval)::date), consumption_energy_consumption.addr, consumption_energy_consumption.var
        ), delta_dates AS (
         SELECT daily_delta_core.measured_from,
            daily_delta_core.addr,
            daily_delta_core.var,
            daily_delta_core.delta_value,
            lag(daily_delta_core.measured_from, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS measured_before,
            lead(daily_delta_core.measured_from, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS measured_after,
            lag(daily_delta_core.delta_value, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS delta_before,
            lead(daily_delta_core.delta_value, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS delta_after
           FROM daily_delta_core
        ), delta_date_diff AS (
         SELECT delta_dates.measured_from,
            delta_dates.addr,
            delta_dates.var,
            delta_dates.delta_value,
            delta_dates.measured_before,
            delta_dates.measured_after,
            delta_dates.delta_before,
            delta_dates.delta_after,
                CASE
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 AND (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN 'mid'::text
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 THEN 'begin'::text
                    WHEN (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN 'end'::text
                    ELSE 'ok'::text
                END AS value_type,
                CASE
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 AND (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN (delta_dates.delta_value / 2::numeric + delta_dates.delta_after) / (delta_dates.measured_after - delta_dates.measured_from + 1)::numeric
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 THEN (delta_dates.delta_value + delta_dates.delta_after) / (delta_dates.measured_after - delta_dates.measured_from + 1)::numeric
                    ELSE NULL::numeric
                END AS fix
           FROM delta_dates
        ), joined_table AS (
         SELECT kalendar.den AS measured_from,
            meraky.addr,
            diff.delta_value,
            diff.value_type,
            diff.fix,
            core2.delta_value AS core2_delta,
                CASE
                    WHEN diff.value_type = 'ok'::text THEN COALESCE(core2.delta_value, diff.delta_value)
                    ELSE diff.fix
                END AS final_fix,
                CASE
                    WHEN diff.value_type = 'ok'::text THEN COALESCE(core2.var, diff.var)
                    ELSE 'core'::character varying
                END AS final_var
           FROM kalendar
             LEFT JOIN meraky ON true
             LEFT JOIN delta_date_diff diff ON diff.measured_from = kalendar.den AND diff.addr::text = meraky.addr::text
             LEFT JOIN daily_delta_core2 core2 ON core2.measured_from = kalendar.den AND core2.addr::text = meraky.addr::text
          ORDER BY meraky.addr, kalendar.den
        ), fixed_table AS (
         SELECT q.measured_from,
            q.addr,
            q.delta_value,
            q.value_type,
            q.fix,
            q.core2_delta,
            q.final_fix,
            q.final_var,
            q.value_partition,
            first_value(q.final_fix) OVER w AS value
           FROM ( SELECT joined_table.measured_from,
                    joined_table.addr,
                    joined_table.delta_value,
                    joined_table.value_type,
                    joined_table.fix,
                    joined_table.core2_delta,
                    joined_table.final_fix,
                    joined_table.final_var,
                    sum(
                        CASE
                            WHEN joined_table.final_fix IS NULL THEN 0
                            ELSE 1
                        END) OVER (PARTITION BY joined_table.addr ORDER BY joined_table.measured_from) AS value_partition
                   FROM joined_table) q
          WINDOW w AS (PARTITION BY q.addr, q.value_partition ORDER BY q.measured_from)
        )
 SELECT fixed_table.measured_from,
    fixed_table.addr,
    fixed_table.value AS delta_value,
    fixed_table.final_var,
    row_number() OVER (PARTITION BY fixed_table.addr, (date_trunc('month'::text, fixed_table.measured_from::timestamp with time zone)) ORDER BY fixed_table.value DESC) AS ran
   FROM fixed_table
  WHERE fixed_table.value IS NOT NULL;


CREATE OR REPLACE VIEW v_consumption_energy_buildings_real
AS SELECT consumption_energy_buildings.id,
    consumption_energy_buildings.building_name,
    consumption_energy_buildings.description,
    consumption_energy_buildings.building_address_code,
    consumption_energy_buildings.building_label,
    consumption_energy_buildings.current_note,
    consumption_energy_buildings.main_use,
    consumption_energy_buildings.secondary_use,
    consumption_energy_buildings.year_of_construction,
    consumption_energy_buildings.method_of_protection,
    consumption_energy_buildings.built_up_area,
    consumption_energy_buildings.heated_bulding_volume,
    consumption_energy_buildings.students_count,
    consumption_energy_buildings.employees_count,
    consumption_energy_buildings.classrooms_count,
    consumption_energy_buildings.beds_count,
    consumption_energy_buildings.eno_id,
    consumption_energy_buildings.csu_code,
    consumption_energy_buildings.ku_code,
    consumption_energy_buildings.allotment_number,
    consumption_energy_buildings.registration_unit,
    consumption_energy_buildings.gas_consumption_normatives,
    consumption_energy_buildings.heat_consumption_normatives,
    consumption_energy_buildings.water_consumption_normatives,
    consumption_energy_buildings.electricity_consumption_normatives_per_person,
    consumption_energy_buildings.electricity_consumption_normatives,
    consumption_energy_buildings.energetic_management,
    consumption_energy_buildings.opening_hours,
    consumption_energy_buildings.weekend_opening_hours,
    consumption_energy_buildings.latitude,
    consumption_energy_buildings.longitude,
    consumption_energy_buildings.address_street,
    consumption_energy_buildings.address_house_number,
    consumption_energy_buildings.address_city,
    consumption_energy_buildings.address_country,
    consumption_energy_buildings.address_mail,
    consumption_energy_buildings.address_phone,
    consumption_energy_buildings.address_web_address,
    consumption_energy_buildings.penb_penbnumber,
    consumption_energy_buildings.penb_issue_date,
    consumption_energy_buildings.penb_total_building_envelope_area,
    consumption_energy_buildings.penb_volume_factor_of_avshape,
    consumption_energy_buildings.penb_total_energy_reference_area,
    consumption_energy_buildings.penb_total_provided_energy,
    consumption_energy_buildings.penb_total_provided_energy_category,
    consumption_energy_buildings.penb_primary_non_renewable_energy,
    consumption_energy_buildings.penb_primary_non_renewable_energy_category,
    consumption_energy_buildings.penb_building_envelope,
    consumption_energy_buildings.penb_building_envelope_category,
    consumption_energy_buildings.penb_heating,
    consumption_energy_buildings.penb_heating_category,
    consumption_energy_buildings.penb_cooling,
    consumption_energy_buildings.penb_cooling_category,
    consumption_energy_buildings.penb_ventilation,
    consumption_energy_buildings.penb_ventilation_category,
    consumption_energy_buildings.penb_humidity_adjustment,
    consumption_energy_buildings.penb_humidity_adjustment_category,
    consumption_energy_buildings.penb_warm_water,
    consumption_energy_buildings.penb_warm_water_category,
    consumption_energy_buildings.penb_lighting,
    consumption_energy_buildings.penb_lighting_category,
    consumption_energy_buildings.energy_audits_energy_audit,
    consumption_energy_buildings.energy_audits_earegistration_number,
    consumption_energy_buildings.energy_audits_created_at,
    consumption_energy_buildings.building_envelope_side_wall_prevailing_construction,
    consumption_energy_buildings.building_envelope_side_wall_area,
    consumption_energy_buildings.building_envelope_side_wall_heat_insulation,
    consumption_energy_buildings.building_envelope_side_wall_year_of_adjustment,
    consumption_energy_buildings.building_envelope_side_wall_technical_condition,
    consumption_energy_buildings.building_envelope_filling_of_hole_construction,
    consumption_energy_buildings.building_envelope_filling_of_hole_area,
    consumption_energy_buildings.building_envelope_filling_of_hole_year_of_adjustment,
    consumption_energy_buildings.building_envelope_filling_of_hole_technical_condition,
    consumption_energy_buildings.building_envelope_roof_construction,
    consumption_energy_buildings.building_envelope_roof_area,
    consumption_energy_buildings.building_envelope_roof_thermal_insulation,
    consumption_energy_buildings.building_envelope_roof_year_of_adjustment,
    consumption_energy_buildings.building_envelope_roof_technical_condition,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_construction,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_area,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_thermal_insulation,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_technical_condition,
    consumption_energy_buildings.fuel_and_energy_coal,
    consumption_energy_buildings.fuel_and_energy_gas,
    consumption_energy_buildings.fuel_and_energy_electricity,
    consumption_energy_buildings.fuel_and_energy_czt,
    consumption_energy_buildings.fuel_and_energy_oze,
    consumption_energy_buildings.fuel_and_energy_other,
    consumption_energy_buildings.technical_equipment_heating_main_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heat_percentage,
    consumption_energy_buildings.technical_equipment_heating_secondary_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heating_system,
    consumption_energy_buildings.technical_equipment_heating_year,
    consumption_energy_buildings.technical_equipment_heating_technical_condition,
    consumption_energy_buildings.technical_equipment_cooling_cooling_system,
    consumption_energy_buildings.technical_equipment_cooling_cooling_area_percentage,
    consumption_energy_buildings.technical_equipment_cooling_year,
    consumption_energy_buildings.technical_equipment_cooling_technical_condition,
    consumption_energy_buildings.technical_equipment_ventilation_ventilation,
    consumption_energy_buildings.technical_equipment_ventilation_year,
    consumption_energy_buildings.technical_equipment_ventilation_technical_condition,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_humidity_adjustment,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_year,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_technical_condition,
    consumption_energy_buildings.technical_equipment_hot_water_predominant_way_of_heating_tv,
    consumption_energy_buildings.technical_equipment_hot_water_hot_water_source,
    consumption_energy_buildings.technical_equipment_hot_water_year,
    consumption_energy_buildings.technical_equipment_hot_water_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_lighting,
    consumption_energy_buildings.technical_equipment_lighting_year,
    consumption_energy_buildings.technical_equipment_lighting_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_other_technological_elements,
    consumption_energy_buildings.technical_equipment_lighting_measurement_method,
    consumption_energy_buildings.oze_energy_production_solar_energy_photovoltaic,
    consumption_energy_buildings.oze_energy_production_solar_energy_photothermal,
    consumption_energy_buildings.oze_energy_production_integrated_turbines_wind_energy,
    consumption_energy_buildings.oze_energy_production_heat_pump,
    consumption_energy_buildings.waste_and_emissions_solid_waste_production,
    consumption_energy_buildings.waste_and_emissions_tied_co2_emissions,
    consumption_energy_buildings.waste_and_emissions_sox_emissions,
    consumption_energy_buildings.waste_and_emissions_operating_co_2emissions,
    consumption_energy_buildings.link
   FROM consumption_energy_buildings
  WHERE consumption_energy_buildings.building_label::text <> ALL (ARRAY['ZKB 1'::character varying::text, 'ZKB 2'::character varying::text, 'TB1'::character varying::text, 'TB2'::character varying::text]);


CREATE OR REPLACE VIEW v_consumption_energy_buildings_real_mc
AS SELECT consumption_energy_buildings.id,
    consumption_energy_buildings.building_name,
    consumption_energy_buildings.description,
    consumption_energy_buildings.building_address_code,
    consumption_energy_buildings.building_label,
    consumption_energy_buildings.current_note,
    consumption_energy_buildings.main_use,
    consumption_energy_buildings.secondary_use,
    consumption_energy_buildings.year_of_construction,
    consumption_energy_buildings.method_of_protection,
    consumption_energy_buildings.built_up_area,
    consumption_energy_buildings.heated_bulding_volume,
    consumption_energy_buildings.students_count,
    consumption_energy_buildings.employees_count,
    consumption_energy_buildings.classrooms_count,
    consumption_energy_buildings.beds_count,
    consumption_energy_buildings.eno_id,
    consumption_energy_buildings.csu_code,
    consumption_energy_buildings.ku_code,
    consumption_energy_buildings.allotment_number,
    consumption_energy_buildings.registration_unit,
    consumption_energy_buildings.gas_consumption_normatives,
    consumption_energy_buildings.heat_consumption_normatives,
    consumption_energy_buildings.water_consumption_normatives,
    consumption_energy_buildings.electricity_consumption_normatives_per_person,
    consumption_energy_buildings.electricity_consumption_normatives,
    consumption_energy_buildings.energetic_management,
    consumption_energy_buildings.opening_hours,
    consumption_energy_buildings.weekend_opening_hours,
    consumption_energy_buildings.latitude,
    consumption_energy_buildings.longitude,
    public.st_setsrid(public.st_point(consumption_energy_buildings.longitude::double precision, consumption_energy_buildings.latitude::double precision), 4326) AS geom,
    consumption_energy_buildings.address_street,
    consumption_energy_buildings.address_house_number,
    consumption_energy_buildings.address_city,
    citydistricts.district_name,
    citydistricts.geom AS district_geom,
    consumption_energy_buildings.address_country,
    consumption_energy_buildings.address_mail,
    consumption_energy_buildings.address_phone,
    consumption_energy_buildings.address_web_address,
    consumption_energy_buildings.penb_penbnumber,
    consumption_energy_buildings.penb_issue_date,
    consumption_energy_buildings.penb_total_building_envelope_area,
    consumption_energy_buildings.penb_volume_factor_of_avshape,
    consumption_energy_buildings.penb_total_energy_reference_area,
    consumption_energy_buildings.penb_total_provided_energy,
    consumption_energy_buildings.penb_total_provided_energy_category,
    consumption_energy_buildings.penb_primary_non_renewable_energy,
    consumption_energy_buildings.penb_primary_non_renewable_energy_category,
    consumption_energy_buildings.penb_building_envelope,
    consumption_energy_buildings.penb_building_envelope_category,
    consumption_energy_buildings.penb_heating,
    consumption_energy_buildings.penb_heating_category,
    consumption_energy_buildings.penb_cooling,
    consumption_energy_buildings.penb_cooling_category,
    consumption_energy_buildings.penb_ventilation,
    consumption_energy_buildings.penb_ventilation_category,
    consumption_energy_buildings.penb_humidity_adjustment,
    consumption_energy_buildings.penb_humidity_adjustment_category,
    consumption_energy_buildings.penb_warm_water,
    consumption_energy_buildings.penb_warm_water_category,
    consumption_energy_buildings.penb_lighting,
    consumption_energy_buildings.penb_lighting_category,
    consumption_energy_buildings.energy_audits_energy_audit,
    consumption_energy_buildings.energy_audits_earegistration_number,
    consumption_energy_buildings.energy_audits_created_at,
    consumption_energy_buildings.building_envelope_side_wall_prevailing_construction,
    consumption_energy_buildings.building_envelope_side_wall_area,
    consumption_energy_buildings.building_envelope_side_wall_heat_insulation,
    consumption_energy_buildings.building_envelope_side_wall_year_of_adjustment,
    consumption_energy_buildings.building_envelope_side_wall_technical_condition,
    consumption_energy_buildings.building_envelope_filling_of_hole_construction,
    consumption_energy_buildings.building_envelope_filling_of_hole_area,
    consumption_energy_buildings.building_envelope_filling_of_hole_year_of_adjustment,
    consumption_energy_buildings.building_envelope_filling_of_hole_technical_condition,
    consumption_energy_buildings.building_envelope_roof_construction,
    consumption_energy_buildings.building_envelope_roof_area,
    consumption_energy_buildings.building_envelope_roof_thermal_insulation,
    consumption_energy_buildings.building_envelope_roof_year_of_adjustment,
    consumption_energy_buildings.building_envelope_roof_technical_condition,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_construction,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_area,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_thermal_insulation,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_technical_condition,
    consumption_energy_buildings.fuel_and_energy_coal,
    consumption_energy_buildings.fuel_and_energy_gas,
    consumption_energy_buildings.fuel_and_energy_electricity,
    consumption_energy_buildings.fuel_and_energy_czt,
    consumption_energy_buildings.fuel_and_energy_oze,
    consumption_energy_buildings.fuel_and_energy_other,
    consumption_energy_buildings.technical_equipment_heating_main_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heat_percentage,
    consumption_energy_buildings.technical_equipment_heating_secondary_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heating_system,
    consumption_energy_buildings.technical_equipment_heating_year,
    consumption_energy_buildings.technical_equipment_heating_technical_condition,
    consumption_energy_buildings.technical_equipment_cooling_cooling_system,
    consumption_energy_buildings.technical_equipment_cooling_cooling_area_percentage,
    consumption_energy_buildings.technical_equipment_cooling_year,
    consumption_energy_buildings.technical_equipment_cooling_technical_condition,
    consumption_energy_buildings.technical_equipment_ventilation_ventilation,
    consumption_energy_buildings.technical_equipment_ventilation_year,
    consumption_energy_buildings.technical_equipment_ventilation_technical_condition,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_humidity_adjustment,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_year,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_technical_condition,
    consumption_energy_buildings.technical_equipment_hot_water_predominant_way_of_heating_tv,
    consumption_energy_buildings.technical_equipment_hot_water_hot_water_source,
    consumption_energy_buildings.technical_equipment_hot_water_year,
    consumption_energy_buildings.technical_equipment_hot_water_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_lighting,
    consumption_energy_buildings.technical_equipment_lighting_year,
    consumption_energy_buildings.technical_equipment_lighting_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_other_technological_elements,
    consumption_energy_buildings.technical_equipment_lighting_measurement_method,
    consumption_energy_buildings.oze_energy_production_solar_energy_photovoltaic,
    consumption_energy_buildings.oze_energy_production_solar_energy_photothermal,
    consumption_energy_buildings.oze_energy_production_integrated_turbines_wind_energy,
    consumption_energy_buildings.oze_energy_production_heat_pump,
    consumption_energy_buildings.waste_and_emissions_solid_waste_production,
    consumption_energy_buildings.waste_and_emissions_tied_co2_emissions,
    consumption_energy_buildings.waste_and_emissions_sox_emissions,
    consumption_energy_buildings.waste_and_emissions_operating_co_2emissions,
    consumption_energy_buildings.link
   FROM consumption_energy_buildings
     JOIN common.citydistricts ON public.st_within(public.st_setsrid(public.st_point(consumption_energy_buildings.longitude::double precision, consumption_energy_buildings.latitude::double precision), 4326), citydistricts.geom)
  WHERE (consumption_energy_buildings.building_label::text <> ALL (ARRAY['ZKB 1'::character varying::text, 'ZKB 2'::character varying::text, 'TB1'::character varying::text, 'TB2'::character varying::text])) AND consumption_energy_buildings.longitude::text <> ''::text AND consumption_energy_buildings.latitude::text <> ''::text;

CREATE OR REPLACE VIEW v_consumption_energy_buildings_mimo_prahu
AS SELECT v_consumption_energy_buildings_real.id,
    v_consumption_energy_buildings_real.building_name,
    v_consumption_energy_buildings_real.description,
    v_consumption_energy_buildings_real.building_address_code,
    v_consumption_energy_buildings_real.building_label,
    v_consumption_energy_buildings_real.current_note,
    v_consumption_energy_buildings_real.main_use,
    v_consumption_energy_buildings_real.secondary_use,
    v_consumption_energy_buildings_real.year_of_construction,
    v_consumption_energy_buildings_real.method_of_protection,
    v_consumption_energy_buildings_real.built_up_area,
    v_consumption_energy_buildings_real.heated_bulding_volume,
    v_consumption_energy_buildings_real.students_count,
    v_consumption_energy_buildings_real.employees_count,
    v_consumption_energy_buildings_real.classrooms_count,
    v_consumption_energy_buildings_real.beds_count,
    v_consumption_energy_buildings_real.eno_id,
    v_consumption_energy_buildings_real.csu_code,
    v_consumption_energy_buildings_real.ku_code,
    v_consumption_energy_buildings_real.allotment_number,
    v_consumption_energy_buildings_real.registration_unit,
    v_consumption_energy_buildings_real.gas_consumption_normatives,
    v_consumption_energy_buildings_real.heat_consumption_normatives,
    v_consumption_energy_buildings_real.water_consumption_normatives,
    v_consumption_energy_buildings_real.electricity_consumption_normatives_per_person,
    v_consumption_energy_buildings_real.electricity_consumption_normatives,
    v_consumption_energy_buildings_real.energetic_management,
    v_consumption_energy_buildings_real.opening_hours,
    v_consumption_energy_buildings_real.weekend_opening_hours,
    v_consumption_energy_buildings_real.latitude,
    v_consumption_energy_buildings_real.longitude,
    v_consumption_energy_buildings_real.address_street,
    v_consumption_energy_buildings_real.address_house_number,
    v_consumption_energy_buildings_real.address_city,
    v_consumption_energy_buildings_real.address_country,
    v_consumption_energy_buildings_real.address_mail,
    v_consumption_energy_buildings_real.address_phone,
    v_consumption_energy_buildings_real.address_web_address,
    v_consumption_energy_buildings_real.penb_penbnumber,
    v_consumption_energy_buildings_real.penb_issue_date,
    v_consumption_energy_buildings_real.penb_total_building_envelope_area,
    v_consumption_energy_buildings_real.penb_volume_factor_of_avshape,
    v_consumption_energy_buildings_real.penb_total_energy_reference_area,
    v_consumption_energy_buildings_real.penb_total_provided_energy,
    v_consumption_energy_buildings_real.penb_total_provided_energy_category,
    v_consumption_energy_buildings_real.penb_primary_non_renewable_energy,
    v_consumption_energy_buildings_real.penb_primary_non_renewable_energy_category,
    v_consumption_energy_buildings_real.penb_building_envelope,
    v_consumption_energy_buildings_real.penb_building_envelope_category,
    v_consumption_energy_buildings_real.penb_heating,
    v_consumption_energy_buildings_real.penb_heating_category,
    v_consumption_energy_buildings_real.penb_cooling,
    v_consumption_energy_buildings_real.penb_cooling_category,
    v_consumption_energy_buildings_real.penb_ventilation,
    v_consumption_energy_buildings_real.penb_ventilation_category,
    v_consumption_energy_buildings_real.penb_humidity_adjustment,
    v_consumption_energy_buildings_real.penb_humidity_adjustment_category,
    v_consumption_energy_buildings_real.penb_warm_water,
    v_consumption_energy_buildings_real.penb_warm_water_category,
    v_consumption_energy_buildings_real.penb_lighting,
    v_consumption_energy_buildings_real.penb_lighting_category,
    v_consumption_energy_buildings_real.energy_audits_energy_audit,
    v_consumption_energy_buildings_real.energy_audits_earegistration_number,
    v_consumption_energy_buildings_real.energy_audits_created_at,
    v_consumption_energy_buildings_real.building_envelope_side_wall_prevailing_construction,
    v_consumption_energy_buildings_real.building_envelope_side_wall_area,
    v_consumption_energy_buildings_real.building_envelope_side_wall_heat_insulation,
    v_consumption_energy_buildings_real.building_envelope_side_wall_year_of_adjustment,
    v_consumption_energy_buildings_real.building_envelope_side_wall_technical_condition,
    v_consumption_energy_buildings_real.building_envelope_filling_of_hole_construction,
    v_consumption_energy_buildings_real.building_envelope_filling_of_hole_area,
    v_consumption_energy_buildings_real.building_envelope_filling_of_hole_year_of_adjustment,
    v_consumption_energy_buildings_real.building_envelope_filling_of_hole_technical_condition,
    v_consumption_energy_buildings_real.building_envelope_roof_construction,
    v_consumption_energy_buildings_real.building_envelope_roof_area,
    v_consumption_energy_buildings_real.building_envelope_roof_thermal_insulation,
    v_consumption_energy_buildings_real.building_envelope_roof_year_of_adjustment,
    v_consumption_energy_buildings_real.building_envelope_roof_technical_condition,
    v_consumption_energy_buildings_real.building_envelope_floor_of_the_lowest_heated_floor_construction,
    v_consumption_energy_buildings_real.building_envelope_floor_of_the_lowest_heated_floor_area,
    v_consumption_energy_buildings_real.floor_of_the_lowest_heated_floor_thermal_insulation,
    v_consumption_energy_buildings_real.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    v_consumption_energy_buildings_real.floor_of_the_lowest_heated_floor_technical_condition,
    v_consumption_energy_buildings_real.fuel_and_energy_coal,
    v_consumption_energy_buildings_real.fuel_and_energy_gas,
    v_consumption_energy_buildings_real.fuel_and_energy_electricity,
    v_consumption_energy_buildings_real.fuel_and_energy_czt,
    v_consumption_energy_buildings_real.fuel_and_energy_oze,
    v_consumption_energy_buildings_real.fuel_and_energy_other,
    v_consumption_energy_buildings_real.technical_equipment_heating_main_source_of_heat,
    v_consumption_energy_buildings_real.technical_equipment_heating_heat_percentage,
    v_consumption_energy_buildings_real.technical_equipment_heating_secondary_source_of_heat,
    v_consumption_energy_buildings_real.technical_equipment_heating_heating_system,
    v_consumption_energy_buildings_real.technical_equipment_heating_year,
    v_consumption_energy_buildings_real.technical_equipment_heating_technical_condition,
    v_consumption_energy_buildings_real.technical_equipment_cooling_cooling_system,
    v_consumption_energy_buildings_real.technical_equipment_cooling_cooling_area_percentage,
    v_consumption_energy_buildings_real.technical_equipment_cooling_year,
    v_consumption_energy_buildings_real.technical_equipment_cooling_technical_condition,
    v_consumption_energy_buildings_real.technical_equipment_ventilation_ventilation,
    v_consumption_energy_buildings_real.technical_equipment_ventilation_year,
    v_consumption_energy_buildings_real.technical_equipment_ventilation_technical_condition,
    v_consumption_energy_buildings_real.technical_equipment_humidity_adjustment_humidity_adjustment,
    v_consumption_energy_buildings_real.technical_equipment_humidity_adjustment_year,
    v_consumption_energy_buildings_real.technical_equipment_humidity_adjustment_technical_condition,
    v_consumption_energy_buildings_real.technical_equipment_hot_water_predominant_way_of_heating_tv,
    v_consumption_energy_buildings_real.technical_equipment_hot_water_hot_water_source,
    v_consumption_energy_buildings_real.technical_equipment_hot_water_year,
    v_consumption_energy_buildings_real.technical_equipment_hot_water_technical_condition,
    v_consumption_energy_buildings_real.technical_equipment_lighting_lighting,
    v_consumption_energy_buildings_real.technical_equipment_lighting_year,
    v_consumption_energy_buildings_real.technical_equipment_lighting_technical_condition,
    v_consumption_energy_buildings_real.technical_equipment_lighting_other_technological_elements,
    v_consumption_energy_buildings_real.technical_equipment_lighting_measurement_method,
    v_consumption_energy_buildings_real.oze_energy_production_solar_energy_photovoltaic,
    v_consumption_energy_buildings_real.oze_energy_production_solar_energy_photothermal,
    v_consumption_energy_buildings_real.oze_energy_production_integrated_turbines_wind_energy,
    v_consumption_energy_buildings_real.oze_energy_production_heat_pump,
    v_consumption_energy_buildings_real.waste_and_emissions_solid_waste_production,
    v_consumption_energy_buildings_real.waste_and_emissions_tied_co2_emissions,
    v_consumption_energy_buildings_real.waste_and_emissions_sox_emissions,
    v_consumption_energy_buildings_real.waste_and_emissions_operating_co_2emissions,
    v_consumption_energy_buildings_real.link
   FROM v_consumption_energy_buildings_real
  WHERE NOT (v_consumption_energy_buildings_real.building_name::text IN ( SELECT v_consumption_energy_buildings_real_mc.building_name
           FROM v_consumption_energy_buildings_real_mc));

CREATE OR REPLACE VIEW v_consumption_energy_buildings_real_mc_nogeom
AS SELECT v_consumption_energy_buildings_real_mc.id,
    v_consumption_energy_buildings_real_mc.building_name,
    v_consumption_energy_buildings_real_mc.description,
    v_consumption_energy_buildings_real_mc.building_address_code,
    v_consumption_energy_buildings_real_mc.building_label,
    v_consumption_energy_buildings_real_mc.current_note,
    v_consumption_energy_buildings_real_mc.main_use,
    v_consumption_energy_buildings_real_mc.secondary_use,
    v_consumption_energy_buildings_real_mc.year_of_construction,
    v_consumption_energy_buildings_real_mc.method_of_protection,
    v_consumption_energy_buildings_real_mc.built_up_area,
    v_consumption_energy_buildings_real_mc.heated_bulding_volume,
    v_consumption_energy_buildings_real_mc.students_count,
    v_consumption_energy_buildings_real_mc.employees_count,
    v_consumption_energy_buildings_real_mc.classrooms_count,
    v_consumption_energy_buildings_real_mc.beds_count,
    v_consumption_energy_buildings_real_mc.eno_id,
    v_consumption_energy_buildings_real_mc.csu_code,
    v_consumption_energy_buildings_real_mc.ku_code,
    v_consumption_energy_buildings_real_mc.allotment_number,
    v_consumption_energy_buildings_real_mc.registration_unit,
    v_consumption_energy_buildings_real_mc.gas_consumption_normatives,
    v_consumption_energy_buildings_real_mc.heat_consumption_normatives,
    v_consumption_energy_buildings_real_mc.water_consumption_normatives,
    v_consumption_energy_buildings_real_mc.electricity_consumption_normatives_per_person,
    v_consumption_energy_buildings_real_mc.electricity_consumption_normatives,
    v_consumption_energy_buildings_real_mc.energetic_management,
    v_consumption_energy_buildings_real_mc.opening_hours,
    v_consumption_energy_buildings_real_mc.weekend_opening_hours,
    v_consumption_energy_buildings_real_mc.latitude,
    v_consumption_energy_buildings_real_mc.longitude,
    v_consumption_energy_buildings_real_mc.address_street,
    v_consumption_energy_buildings_real_mc.address_house_number,
    v_consumption_energy_buildings_real_mc.address_city,
    v_consumption_energy_buildings_real_mc.district_name,
    v_consumption_energy_buildings_real_mc.address_country,
    v_consumption_energy_buildings_real_mc.address_mail,
    v_consumption_energy_buildings_real_mc.address_phone,
    v_consumption_energy_buildings_real_mc.address_web_address,
    v_consumption_energy_buildings_real_mc.penb_penbnumber,
    v_consumption_energy_buildings_real_mc.penb_issue_date,
    v_consumption_energy_buildings_real_mc.penb_total_building_envelope_area,
    v_consumption_energy_buildings_real_mc.penb_volume_factor_of_avshape,
    v_consumption_energy_buildings_real_mc.penb_total_energy_reference_area,
    v_consumption_energy_buildings_real_mc.penb_total_provided_energy,
    v_consumption_energy_buildings_real_mc.penb_total_provided_energy_category,
    v_consumption_energy_buildings_real_mc.penb_primary_non_renewable_energy,
    v_consumption_energy_buildings_real_mc.penb_primary_non_renewable_energy_category,
    v_consumption_energy_buildings_real_mc.penb_building_envelope,
    v_consumption_energy_buildings_real_mc.penb_building_envelope_category,
    v_consumption_energy_buildings_real_mc.penb_heating,
    v_consumption_energy_buildings_real_mc.penb_heating_category,
    v_consumption_energy_buildings_real_mc.penb_cooling,
    v_consumption_energy_buildings_real_mc.penb_cooling_category,
    v_consumption_energy_buildings_real_mc.penb_ventilation,
    v_consumption_energy_buildings_real_mc.penb_ventilation_category,
    v_consumption_energy_buildings_real_mc.penb_humidity_adjustment,
    v_consumption_energy_buildings_real_mc.penb_humidity_adjustment_category,
    v_consumption_energy_buildings_real_mc.penb_warm_water,
    v_consumption_energy_buildings_real_mc.penb_warm_water_category,
    v_consumption_energy_buildings_real_mc.penb_lighting,
    v_consumption_energy_buildings_real_mc.penb_lighting_category,
    v_consumption_energy_buildings_real_mc.energy_audits_energy_audit,
    v_consumption_energy_buildings_real_mc.energy_audits_earegistration_number,
    v_consumption_energy_buildings_real_mc.energy_audits_created_at,
    v_consumption_energy_buildings_real_mc.building_envelope_side_wall_prevailing_construction,
    v_consumption_energy_buildings_real_mc.building_envelope_side_wall_area,
    v_consumption_energy_buildings_real_mc.building_envelope_side_wall_heat_insulation,
    v_consumption_energy_buildings_real_mc.building_envelope_side_wall_year_of_adjustment,
    v_consumption_energy_buildings_real_mc.building_envelope_side_wall_technical_condition,
    v_consumption_energy_buildings_real_mc.building_envelope_filling_of_hole_construction,
    v_consumption_energy_buildings_real_mc.building_envelope_filling_of_hole_area,
    v_consumption_energy_buildings_real_mc.building_envelope_filling_of_hole_year_of_adjustment,
    v_consumption_energy_buildings_real_mc.building_envelope_filling_of_hole_technical_condition,
    v_consumption_energy_buildings_real_mc.building_envelope_roof_construction,
    v_consumption_energy_buildings_real_mc.building_envelope_roof_area,
    v_consumption_energy_buildings_real_mc.building_envelope_roof_thermal_insulation,
    v_consumption_energy_buildings_real_mc.building_envelope_roof_year_of_adjustment,
    v_consumption_energy_buildings_real_mc.building_envelope_roof_technical_condition,
    v_consumption_energy_buildings_real_mc.building_envelope_floor_of_the_lowest_heated_floor_construction,
    v_consumption_energy_buildings_real_mc.building_envelope_floor_of_the_lowest_heated_floor_area,
    v_consumption_energy_buildings_real_mc.floor_of_the_lowest_heated_floor_thermal_insulation,
    v_consumption_energy_buildings_real_mc.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    v_consumption_energy_buildings_real_mc.floor_of_the_lowest_heated_floor_technical_condition,
    v_consumption_energy_buildings_real_mc.fuel_and_energy_coal,
    v_consumption_energy_buildings_real_mc.fuel_and_energy_gas,
    v_consumption_energy_buildings_real_mc.fuel_and_energy_electricity,
    v_consumption_energy_buildings_real_mc.fuel_and_energy_czt,
    v_consumption_energy_buildings_real_mc.fuel_and_energy_oze,
    v_consumption_energy_buildings_real_mc.fuel_and_energy_other,
    v_consumption_energy_buildings_real_mc.technical_equipment_heating_main_source_of_heat,
    v_consumption_energy_buildings_real_mc.technical_equipment_heating_heat_percentage,
    v_consumption_energy_buildings_real_mc.technical_equipment_heating_secondary_source_of_heat,
    v_consumption_energy_buildings_real_mc.technical_equipment_heating_heating_system,
    v_consumption_energy_buildings_real_mc.technical_equipment_heating_year,
    v_consumption_energy_buildings_real_mc.technical_equipment_heating_technical_condition,
    v_consumption_energy_buildings_real_mc.technical_equipment_cooling_cooling_system,
    v_consumption_energy_buildings_real_mc.technical_equipment_cooling_cooling_area_percentage,
    v_consumption_energy_buildings_real_mc.technical_equipment_cooling_year,
    v_consumption_energy_buildings_real_mc.technical_equipment_cooling_technical_condition,
    v_consumption_energy_buildings_real_mc.technical_equipment_ventilation_ventilation,
    v_consumption_energy_buildings_real_mc.technical_equipment_ventilation_year,
    v_consumption_energy_buildings_real_mc.technical_equipment_ventilation_technical_condition,
    v_consumption_energy_buildings_real_mc.technical_equipment_humidity_adjustment_humidity_adjustment,
    v_consumption_energy_buildings_real_mc.technical_equipment_humidity_adjustment_year,
    v_consumption_energy_buildings_real_mc.technical_equipment_humidity_adjustment_technical_condition,
    v_consumption_energy_buildings_real_mc.technical_equipment_hot_water_predominant_way_of_heating_tv,
    v_consumption_energy_buildings_real_mc.technical_equipment_hot_water_hot_water_source,
    v_consumption_energy_buildings_real_mc.technical_equipment_hot_water_year,
    v_consumption_energy_buildings_real_mc.technical_equipment_hot_water_technical_condition,
    v_consumption_energy_buildings_real_mc.technical_equipment_lighting_lighting,
    v_consumption_energy_buildings_real_mc.technical_equipment_lighting_year,
    v_consumption_energy_buildings_real_mc.technical_equipment_lighting_technical_condition,
    v_consumption_energy_buildings_real_mc.technical_equipment_lighting_other_technological_elements,
    v_consumption_energy_buildings_real_mc.technical_equipment_lighting_measurement_method,
    v_consumption_energy_buildings_real_mc.oze_energy_production_solar_energy_photovoltaic,
    v_consumption_energy_buildings_real_mc.oze_energy_production_solar_energy_photothermal,
    v_consumption_energy_buildings_real_mc.oze_energy_production_integrated_turbines_wind_energy,
    v_consumption_energy_buildings_real_mc.oze_energy_production_heat_pump,
    v_consumption_energy_buildings_real_mc.waste_and_emissions_solid_waste_production,
    v_consumption_energy_buildings_real_mc.waste_and_emissions_tied_co2_emissions,
    v_consumption_energy_buildings_real_mc.waste_and_emissions_sox_emissions,
    v_consumption_energy_buildings_real_mc.waste_and_emissions_operating_co_2emissions,
    v_consumption_energy_buildings_real_mc.link
   FROM v_consumption_energy_buildings_real_mc;

CREATE OR REPLACE VIEW v_consumption_energy_consumption
AS SELECT "substring"(consumption_energy_consumption.addr::text, 2, "position"(btrim(consumption_energy_consumption.addr::text, '/'::text), '/'::text) - 1)::character varying(255) AS building_address_code,
    "right"(consumption_energy_consumption.addr::text, char_length(consumption_energy_consumption.addr::text) - "position"(btrim(consumption_energy_consumption.addr::text, '/'::text), '/'::text) - 1)::character varying(255) AS devicetype,
    consumption_energy_consumption.time_utc,
    consumption_energy_consumption.value,
    consumption_energy_consumption.addr,
    consumption_energy_consumption.var,
    consumption_energy_consumption.type,
    consumption_energy_consumption.commodity,
    consumption_energy_consumption.unit,
    consumption_energy_consumption.meter
   FROM consumption_energy_consumption
UNION ALL
 SELECT ((( SELECT btrim(consumption_energy_buildings.building_address_code::text) AS btrim
           FROM consumption_energy_buildings
          WHERE consumption_energy_buildings.building_name::text = 'Vrtbovský palác'::text)))::character varying(255) AS building_address_code,
    mt.met_nazev::character varying(255) AS devicetype,
    to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone AS time_utc,
    m.value::numeric(30,15) AS value,
    NULL::character varying(255) AS addr,
    me.var_id::character varying(255) AS var,
    NULL::character varying(255) AS type,
    NULL::character varying(255) AS commodity,
    NULL::character varying(255) AS unit,
    NULL::character varying(255) AS meter
   FROM vpalac_measurement m
     JOIN vpalac_measuring_equipment me ON me.var_id = m.var_id
     JOIN vpalac_meter_type mt ON mt.met_id = me.met_id;


CREATE OR REPLACE VIEW v_consumption_energy_buildings_w_data
AS SELECT consumption_energy_buildings.id,
    consumption_energy_buildings.building_name,
    consumption_energy_buildings.description,
    consumption_energy_buildings.building_address_code,
    consumption_energy_buildings.building_label,
    consumption_energy_buildings.current_note,
    consumption_energy_buildings.main_use,
    consumption_energy_buildings.secondary_use,
    consumption_energy_buildings.year_of_construction,
    consumption_energy_buildings.method_of_protection,
    consumption_energy_buildings.built_up_area,
    consumption_energy_buildings.heated_bulding_volume,
    consumption_energy_buildings.students_count,
    consumption_energy_buildings.employees_count,
    consumption_energy_buildings.classrooms_count,
    consumption_energy_buildings.beds_count,
    consumption_energy_buildings.eno_id,
    consumption_energy_buildings.csu_code,
    consumption_energy_buildings.ku_code,
    consumption_energy_buildings.allotment_number,
    consumption_energy_buildings.registration_unit,
    consumption_energy_buildings.gas_consumption_normatives,
    consumption_energy_buildings.heat_consumption_normatives,
    consumption_energy_buildings.water_consumption_normatives,
    consumption_energy_buildings.electricity_consumption_normatives_per_person,
    consumption_energy_buildings.electricity_consumption_normatives,
    consumption_energy_buildings.energetic_management,
    consumption_energy_buildings.opening_hours,
    consumption_energy_buildings.weekend_opening_hours,
    consumption_energy_buildings.latitude,
    consumption_energy_buildings.longitude,
    consumption_energy_buildings.address_street,
    consumption_energy_buildings.address_house_number,
    consumption_energy_buildings.address_city,
    consumption_energy_buildings.address_country,
    consumption_energy_buildings.address_mail,
    consumption_energy_buildings.address_phone,
    consumption_energy_buildings.address_web_address,
    consumption_energy_buildings.penb_penbnumber,
    consumption_energy_buildings.penb_issue_date,
    consumption_energy_buildings.penb_total_building_envelope_area,
    consumption_energy_buildings.penb_volume_factor_of_avshape,
    consumption_energy_buildings.penb_total_energy_reference_area,
    consumption_energy_buildings.penb_total_provided_energy,
    consumption_energy_buildings.penb_total_provided_energy_category,
    consumption_energy_buildings.penb_primary_non_renewable_energy,
    consumption_energy_buildings.penb_primary_non_renewable_energy_category,
    consumption_energy_buildings.penb_building_envelope,
    consumption_energy_buildings.penb_building_envelope_category,
    consumption_energy_buildings.penb_heating,
    consumption_energy_buildings.penb_heating_category,
    consumption_energy_buildings.penb_cooling,
    consumption_energy_buildings.penb_cooling_category,
    consumption_energy_buildings.penb_ventilation,
    consumption_energy_buildings.penb_ventilation_category,
    consumption_energy_buildings.penb_humidity_adjustment,
    consumption_energy_buildings.penb_humidity_adjustment_category,
    consumption_energy_buildings.penb_warm_water,
    consumption_energy_buildings.penb_warm_water_category,
    consumption_energy_buildings.penb_lighting,
    consumption_energy_buildings.penb_lighting_category,
    consumption_energy_buildings.energy_audits_energy_audit,
    consumption_energy_buildings.energy_audits_earegistration_number,
    consumption_energy_buildings.energy_audits_created_at,
    consumption_energy_buildings.building_envelope_side_wall_prevailing_construction,
    consumption_energy_buildings.building_envelope_side_wall_area,
    consumption_energy_buildings.building_envelope_side_wall_heat_insulation,
    consumption_energy_buildings.building_envelope_side_wall_year_of_adjustment,
    consumption_energy_buildings.building_envelope_side_wall_technical_condition,
    consumption_energy_buildings.building_envelope_filling_of_hole_construction,
    consumption_energy_buildings.building_envelope_filling_of_hole_area,
    consumption_energy_buildings.building_envelope_filling_of_hole_year_of_adjustment,
    consumption_energy_buildings.building_envelope_filling_of_hole_technical_condition,
    consumption_energy_buildings.building_envelope_roof_construction,
    consumption_energy_buildings.building_envelope_roof_area,
    consumption_energy_buildings.building_envelope_roof_thermal_insulation,
    consumption_energy_buildings.building_envelope_roof_year_of_adjustment,
    consumption_energy_buildings.building_envelope_roof_technical_condition,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_construction,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_area,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_thermal_insulation,
    consumption_energy_buildings.building_envelope_floor_of_the_lowest_heated_floor_year_of_adju,
    consumption_energy_buildings.floor_of_the_lowest_heated_floor_technical_condition,
    consumption_energy_buildings.fuel_and_energy_coal,
    consumption_energy_buildings.fuel_and_energy_gas,
    consumption_energy_buildings.fuel_and_energy_electricity,
    consumption_energy_buildings.fuel_and_energy_czt,
    consumption_energy_buildings.fuel_and_energy_oze,
    consumption_energy_buildings.fuel_and_energy_other,
    consumption_energy_buildings.technical_equipment_heating_main_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heat_percentage,
    consumption_energy_buildings.technical_equipment_heating_secondary_source_of_heat,
    consumption_energy_buildings.technical_equipment_heating_heating_system,
    consumption_energy_buildings.technical_equipment_heating_year,
    consumption_energy_buildings.technical_equipment_heating_technical_condition,
    consumption_energy_buildings.technical_equipment_cooling_cooling_system,
    consumption_energy_buildings.technical_equipment_cooling_cooling_area_percentage,
    consumption_energy_buildings.technical_equipment_cooling_year,
    consumption_energy_buildings.technical_equipment_cooling_technical_condition,
    consumption_energy_buildings.technical_equipment_ventilation_ventilation,
    consumption_energy_buildings.technical_equipment_ventilation_year,
    consumption_energy_buildings.technical_equipment_ventilation_technical_condition,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_humidity_adjustment,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_year,
    consumption_energy_buildings.technical_equipment_humidity_adjustment_technical_condition,
    consumption_energy_buildings.technical_equipment_hot_water_predominant_way_of_heating_tv,
    consumption_energy_buildings.technical_equipment_hot_water_hot_water_source,
    consumption_energy_buildings.technical_equipment_hot_water_year,
    consumption_energy_buildings.technical_equipment_hot_water_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_lighting,
    consumption_energy_buildings.technical_equipment_lighting_year,
    consumption_energy_buildings.technical_equipment_lighting_technical_condition,
    consumption_energy_buildings.technical_equipment_lighting_other_technological_elements,
    consumption_energy_buildings.technical_equipment_lighting_measurement_method,
    consumption_energy_buildings.oze_energy_production_solar_energy_photovoltaic,
    consumption_energy_buildings.oze_energy_production_solar_energy_photothermal,
    consumption_energy_buildings.oze_energy_production_integrated_turbines_wind_energy,
    consumption_energy_buildings.oze_energy_production_heat_pump,
    consumption_energy_buildings.waste_and_emissions_solid_waste_production,
    consumption_energy_buildings.waste_and_emissions_tied_co2_emissions,
    consumption_energy_buildings.waste_and_emissions_sox_emissions,
    consumption_energy_buildings.waste_and_emissions_operating_co_2emissions,
    consumption_energy_buildings.link
   FROM consumption_energy_buildings
  WHERE (btrim(consumption_energy_buildings.building_address_code::text) IN ( SELECT DISTINCT v_consumption_energy_consumption.building_address_code
           FROM v_consumption_energy_consumption));

CREATE OR REPLACE VIEW v_consumption_vrtbovsky_palac
AS WITH daily_status AS (
         SELECT date_part('year'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) AS measure_year,
            date_part('month'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) AS measure_month,
            me.me_id::text AS addr,
            m.var_id AS var,
                CASE
                    WHEN mt.met_nazev::text = 'Senzor'::text THEN 'heat'::text
                    WHEN mt.met_nazev::text = 'Elektroměr'::text THEN 'electricity'::text
                    WHEN mt.met_nazev::text = 'Plynoměr'::text THEN 'gas'::text
                    WHEN mt.met_nazev::text = 'Vodoměr'::text THEN 'water'::text
                    ELSE NULL::text
                END AS commodity,
            NULL::text AS unit,
                CASE
                    WHEN mt.met_nazev::text = 'Senzor'::text AND date_part('month'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) = 12::double precision THEN 0::numeric
                    ELSE min(m.value)
                END AS start_value,
            me.mis_nazev,
            date_trunc('month'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) AS data_do
           FROM vpalac_measuring_equipment me
             JOIN vpalac_measurement m ON me.var_id = m.var_id
             JOIN vpalac_meter_type mt ON me.met_id = mt.met_id
          GROUP BY me.me_id, (date_part('year'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone)), (date_part('month'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone)), mt.met_nazev, m.var_id, me.mis_nazev, me.umisteni, (date_trunc('month'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone))
        )
 SELECT daily_status.measure_year,
    daily_status.measure_month,
    daily_status.addr,
    daily_status.var,
    daily_status.commodity,
    daily_status.unit,
    lead(daily_status.start_value) OVER (PARTITION BY daily_status.var ORDER BY daily_status.data_do) - daily_status.start_value AS consumption,
    daily_status.data_do
   FROM daily_status;


CREATE OR REPLACE VIEW v_consumption_energy_consumption_month_p_f
AS SELECT v_consumption_energy_consumption_month_ptv.rok,
    v_consumption_energy_consumption_month_ptv.mesic,
    v_consumption_energy_consumption_month_ptv.addr,
    v_consumption_energy_consumption_month_ptv.var,
    v_consumption_energy_consumption_month_ptv.commodity,
    v_consumption_energy_consumption_month_ptv.unit,
    v_consumption_energy_consumption_month_ptv.value,
    v_consumption_energy_consumption_month_ptv.data_do,
    v_consumption_energy_consumption_month_ptv.count,
    v_consumption_energy_consumption_month_ptv.previous_month_value,
    v_consumption_energy_consumption_month_ptv.mesicni_spotreba
   FROM v_consumption_energy_consumption_month_ptv
  WHERE v_consumption_energy_consumption_month_ptv.addr::text ~~ '%/PF%'::text AND v_consumption_energy_consumption_month_ptv.rok > 2018::double precision
UNION
 SELECT v_consumption_vrtbovsky_palac.measure_year AS rok,
    v_consumption_vrtbovsky_palac.measure_month AS mesic,
    v_consumption_vrtbovsky_palac.addr::character varying(255) AS addr,
    v_consumption_vrtbovsky_palac.var::character varying(255) AS var,
    v_consumption_vrtbovsky_palac.commodity::character varying(255) AS commodity,
    v_consumption_vrtbovsky_palac.unit::character varying(255) AS unit,
    sum(v_consumption_vrtbovsky_palac.consumption) AS value,
    v_consumption_vrtbovsky_palac.data_do::date AS data_do,
    count(*) AS count,
    NULL::numeric AS previous_month_value,
    sum(v_consumption_vrtbovsky_palac.consumption) AS mesicni_spotreba
   FROM v_consumption_vrtbovsky_palac
  WHERE v_consumption_vrtbovsky_palac.commodity = 'gas'::text
  GROUP BY v_consumption_vrtbovsky_palac.measure_year, v_consumption_vrtbovsky_palac.measure_month, v_consumption_vrtbovsky_palac.data_do, v_consumption_vrtbovsky_palac.addr, v_consumption_vrtbovsky_palac.var, v_consumption_vrtbovsky_palac.commodity, v_consumption_vrtbovsky_palac.unit;


CREATE OR REPLACE VIEW v_consumption_energy_consumption_month_el_f
AS SELECT date_part('year'::text, consumption_energy_consumption.time_utc) AS rok,
    date_part('month'::text, consumption_energy_consumption.time_utc) AS mesic,
    consumption_energy_consumption.addr,
    consumption_energy_consumption.var,
    consumption_energy_consumption.commodity,
    consumption_energy_consumption.unit,
    sum(consumption_energy_consumption.value::numeric(10,2)) / 4::numeric AS value,
    max(consumption_energy_consumption.time_utc::date) AS data_do,
    count(*) AS count
   FROM consumption_energy_consumption
  WHERE (consumption_energy_consumption.var::text = ANY (ARRAY['EFwActi'::character varying::text, 'EFwActiNT'::character varying::text, 'EFwActi'::character varying::text])) AND consumption_energy_consumption.addr::text ~~ '%/EF%'::text
  GROUP BY (date_part('year'::text, consumption_energy_consumption.time_utc)), (date_part('month'::text, consumption_energy_consumption.time_utc)), consumption_energy_consumption.addr, consumption_energy_consumption.var, consumption_energy_consumption.commodity, consumption_energy_consumption.unit
UNION
 SELECT v_consumption_vrtbovsky_palac.measure_year AS rok,
    v_consumption_vrtbovsky_palac.measure_month AS mesic,
    v_consumption_vrtbovsky_palac.addr::character varying(255) AS addr,
    v_consumption_vrtbovsky_palac.var::character varying(255) AS var,
    v_consumption_vrtbovsky_palac.commodity::character varying(255) AS commodity,
    v_consumption_vrtbovsky_palac.unit::character varying(255) AS unit,
    sum(v_consumption_vrtbovsky_palac.consumption) AS value,
    v_consumption_vrtbovsky_palac.data_do::date AS data_do,
    count(*) AS count
   FROM v_consumption_vrtbovsky_palac
  WHERE v_consumption_vrtbovsky_palac.commodity = 'electricity'::text
  GROUP BY v_consumption_vrtbovsky_palac.measure_year, v_consumption_vrtbovsky_palac.measure_month, v_consumption_vrtbovsky_palac.data_do, v_consumption_vrtbovsky_palac.addr, v_consumption_vrtbovsky_palac.var, v_consumption_vrtbovsky_palac.commodity, v_consumption_vrtbovsky_palac.unit;


CREATE OR REPLACE VIEW v_consumption_energy_consumption_month_v_f
AS SELECT v_consumption_energy_consumption_month_ptv.rok,
    v_consumption_energy_consumption_month_ptv.mesic,
    v_consumption_energy_consumption_month_ptv.addr,
    v_consumption_energy_consumption_month_ptv.var,
    v_consumption_energy_consumption_month_ptv.commodity,
    v_consumption_energy_consumption_month_ptv.unit,
    v_consumption_energy_consumption_month_ptv.value,
    v_consumption_energy_consumption_month_ptv.data_do,
    v_consumption_energy_consumption_month_ptv.count,
    v_consumption_energy_consumption_month_ptv.previous_month_value,
    v_consumption_energy_consumption_month_ptv.mesicni_spotreba
   FROM v_consumption_energy_consumption_month_ptv
  WHERE v_consumption_energy_consumption_month_ptv.addr::text ~~ '%VF%'::text AND v_consumption_energy_consumption_month_ptv.rok > 2018::double precision
UNION
 SELECT v_consumption_vrtbovsky_palac.measure_year AS rok,
    v_consumption_vrtbovsky_palac.measure_month AS mesic,
    v_consumption_vrtbovsky_palac.addr::character varying(255) AS addr,
    v_consumption_vrtbovsky_palac.var::character varying(255) AS var,
    v_consumption_vrtbovsky_palac.commodity::character varying(255) AS commodity,
    v_consumption_vrtbovsky_palac.unit::character varying(255) AS unit,
    sum(v_consumption_vrtbovsky_palac.consumption) AS value,
    v_consumption_vrtbovsky_palac.data_do::date AS data_do,
    count(*) AS count,
    NULL::numeric AS previous_month_value,
    sum(v_consumption_vrtbovsky_palac.consumption) AS mesicni_spotreba
   FROM v_consumption_vrtbovsky_palac
  WHERE v_consumption_vrtbovsky_palac.commodity = 'water'::text
  GROUP BY v_consumption_vrtbovsky_palac.measure_year, v_consumption_vrtbovsky_palac.measure_month, v_consumption_vrtbovsky_palac.data_do, v_consumption_vrtbovsky_palac.addr, v_consumption_vrtbovsky_palac.var, v_consumption_vrtbovsky_palac.commodity, v_consumption_vrtbovsky_palac.unit;

CREATE OR REPLACE VIEW v_consumption_energy_devices_active
AS SELECT consumption_energy_devices.id,
    consumption_energy_devices.addr,
    consumption_energy_devices.description,
    consumption_energy_devices.meter_number,
    consumption_energy_devices.meter_index,
    consumption_energy_devices.location_number,
    consumption_energy_devices.location_description,
    consumption_energy_devices.include_in_evaluation,
    consumption_energy_devices.meter_type,
    consumption_energy_devices.category,
    consumption_energy_devices.unit,
    consumption_energy_devices.replaced_meter_id,
    consumption_energy_devices.deleted,
    consumption_energy_devices.building_id
   FROM consumption_energy_devices
  WHERE NOT (consumption_energy_devices.id IN ( SELECT consumption_energy_devices_1.replaced_meter_id::integer AS replaced_meter_id
           FROM consumption_energy_devices consumption_energy_devices_1
          WHERE consumption_energy_devices_1.replaced_meter_id::text <> ''::text)) AND (consumption_energy_devices.addr::text <> ALL (ARRAY['aaa'::character varying::text, 'VF1'::character varying::text, 'TF1'::character varying::text, 'EF1'::character varying::text, 'PF1'::character varying::text, '01/VF1'::character varying::text, 'Návštěvnost'::character varying::text])) AND consumption_energy_devices.deleted::text <> '1'::text
UNION
 SELECT me.me_id AS id,
    me.me_id::character varying(255) AS addr,
    NULL::character varying(255) AS description,
    NULL::character varying(255) AS meter_number,
    NULL::character varying(255) AS meter_index,
    NULL::character varying(255) AS location_number,
    NULL::character varying(255) AS location_description,
    NULL::character varying(255) AS include_in_evaluation,
    NULL::character varying(255) AS meter_type,
    NULL::character varying(255) AS category,
    NULL::character varying(255) AS unit,
    NULL::character varying(255) AS replaced_meter_id,
    NULL::character varying(255) AS deleted,
    111 AS building_id
   FROM vpalac_measuring_equipment me;


CREATE OR REPLACE VIEW v_consumption_energy_last_update
AS WITH last_update AS (
         SELECT b.building_name,
            c.addr,
            max(c.time_utc) AS last_update
           FROM consumption_energy_consumption c
             JOIN consumption_energy_devices d ON c.addr::text = d.addr::text
             JOIN consumption_energy_buildings b ON d.building_id = b.id
          GROUP BY b.building_name, c.addr
        ), last_not_zero AS (
         SELECT consumption_energy_consumption.addr,
            max(consumption_energy_consumption.time_utc) AS last_not_zero_value
           FROM consumption_energy_consumption
          WHERE consumption_energy_consumption.value > 0::numeric
          GROUP BY consumption_energy_consumption.addr
        )
 SELECT lu.building_name,
    lu.last_update,
    lu.addr,
        CASE
            WHEN lnz.last_not_zero_value = lu.last_update THEN NULL::timestamp without time zone
            ELSE lnz.last_not_zero_value
        END AS zero_values_after
   FROM last_update lu
     LEFT JOIN last_not_zero lnz ON lu.addr::text = lnz.addr::text;


CREATE OR REPLACE VIEW v_consumption_energy_missing_devices_last_update
AS WITH missing_devices_last_update AS (
         SELECT consumption_energy_consumption.addr,
            "left"(consumption_energy_consumption.addr::text, "position"("right"(consumption_energy_consumption.addr::text, length(consumption_energy_consumption.addr::text) - 1), '/'::text)) AS building_code,
            max(consumption_energy_consumption.time_utc) AS last_update
           FROM consumption_energy_consumption
          WHERE NOT (consumption_energy_consumption.addr::text IN ( SELECT consumption_energy_devices.addr
                   FROM consumption_energy_devices))
          GROUP BY consumption_energy_consumption.addr
        ), missing_devices_last_not_zero AS (
         SELECT consumption_energy_consumption.addr,
            max(consumption_energy_consumption.time_utc) AS last_not_zero_value
           FROM consumption_energy_consumption
          WHERE consumption_energy_consumption.value > 0::numeric AND NOT (consumption_energy_consumption.addr::text IN ( SELECT consumption_energy_devices.addr
                   FROM consumption_energy_devices))
          GROUP BY consumption_energy_consumption.addr
        ), buildings_mapping AS (
         SELECT "left"(d.addr::text, "position"("right"(d.addr::text, length(d.addr::text) - 1), '/'::text)) AS building_code,
            b.building_name
           FROM consumption_energy_devices d
             JOIN consumption_energy_buildings b ON d.building_id = b.id
        )
 SELECT bm.building_name,
    lu.last_update,
    lu.addr,
        CASE
            WHEN lnz.last_not_zero_value = lu.last_update THEN NULL::timestamp without time zone
            ELSE lnz.last_not_zero_value
        END AS zero_values_after
   FROM missing_devices_last_update lu
     JOIN buildings_mapping bm ON lu.building_code = bm.building_code
     LEFT JOIN missing_devices_last_not_zero lnz ON lu.addr::text = lnz.addr::text;

CREATE OR REPLACE VIEW v_consumption_energy_outside_temperature
AS SELECT c.time_utc,
    c.value,
    c.addr,
    b.longitude,
    b.latitude,
    b.building_name
   FROM consumption_energy_consumption c
     JOIN ( SELECT max(consumption_energy_consumption.time_utc) AS last_measure_date,
            consumption_energy_consumption.addr
           FROM consumption_energy_consumption
          GROUP BY consumption_energy_consumption.addr) last_measure ON last_measure.addr::text = c.addr::text AND last_measure.last_measure_date = c.time_utc
     JOIN ( SELECT DISTINCT "left"(d.addr::text, "position"("right"(d.addr::text, length(d.addr::text) - 1), '/'::text)) AS building_code,
            b_1.latitude,
            b_1.longitude,
            b_1.building_name
           FROM consumption_energy_devices d
             JOIN consumption_energy_buildings b_1 ON d.building_id = b_1.id
          WHERE b_1.address_city::text ~~ 'Praha%'::text) b ON b.building_code = "left"(c.addr::text, "position"("right"(c.addr::text, length(c.addr::text) - 1), '/'::text))
  WHERE c.addr::text ~~ '%Text%'::text AND c.commodity::text = 'temperature'::text AND c.time_utc >= (now() - '1 year'::interval) AND (c.addr::text <> ALL (ARRAY['/2.8/Text'::text, '/2.5/Text'::text]));


CREATE OR REPLACE VIEW v_consumption_energy_vrtbovsky_palac_last_update
AS WITH last_update AS (
         SELECT m.var_id,
            me.umisteni,
            me.me_serial,
            max(to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) AS last_update
           FROM vpalac_measurement m
             JOIN vpalac_measuring_equipment me ON m.var_id = me.var_id
          GROUP BY m.var_id, me.umisteni, me.me_serial
        ), last_not_zero_value AS (
         SELECT vpalac_measurement.var_id,
            max(to_timestamp((vpalac_measurement.time_measurement / 1000)::double precision)::timestamp without time zone) AS last_not_zero_value
           FROM vpalac_measurement
          WHERE vpalac_measurement.value > 0::numeric
          GROUP BY vpalac_measurement.var_id
        )
 SELECT lu.var_id,
    lu.me_serial,
    lu.umisteni,
    lu.last_update,
    lnz.last_not_zero_value,
        CASE
            WHEN lnz.last_not_zero_value = lu.last_update THEN NULL::timestamp without time zone
            ELSE lnz.last_not_zero_value
        END AS zero_values_after
   FROM last_update lu
     LEFT JOIN last_not_zero_value lnz ON lu.var_id = lnz.var_id;

CREATE OR REPLACE VIEW v_consumption_gas_reserve
AS WITH kalendar AS (
         SELECT den.den::date AS den
           FROM generate_series('2019-01-01 00:00:00+01'::timestamp with time zone, now() - '1 day'::interval, '1 day'::interval) den(den)
        ), meraky AS (
         SELECT DISTINCT consumption_energy_devices.addr
           FROM consumption_energy_devices
          WHERE consumption_energy_devices.meter_type::text = 'Plynoměr'::text
        ), daily_delta_core AS (
         SELECT date_trunc('day'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval)::date AS measured_from,
            v_consumption_energy_delta.addr,
            v_consumption_energy_delta.var,
            sum(v_consumption_energy_delta.delta_value) AS delta_value
           FROM v_consumption_energy_delta
          WHERE v_consumption_energy_delta.commodity::text = 'gas'::text AND date_part('year'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval) >= 2019::double precision
          GROUP BY (date_trunc('day'::text, v_consumption_energy_delta.time_utc - '06:00:00'::interval)::date), v_consumption_energy_delta.addr, v_consumption_energy_delta.var
        ), daily_delta_core2 AS (
         SELECT date_trunc('day'::text, consumption_energy_consumption.time_utc - '06:00:00'::interval)::date AS measured_from,
            consumption_energy_consumption.addr,
            consumption_energy_consumption.var,
            sum(consumption_energy_consumption.value) AS delta_value
           FROM consumption_energy_consumption
          WHERE consumption_energy_consumption.var::text = 'core2'::text AND consumption_energy_consumption.time_utc::date > '2018-12-31'::date
          GROUP BY (date_trunc('day'::text, consumption_energy_consumption.time_utc - '06:00:00'::interval)::date), consumption_energy_consumption.addr, consumption_energy_consumption.var
        ), delta_dates AS (
         SELECT daily_delta_core.measured_from,
            daily_delta_core.addr,
            daily_delta_core.var,
            daily_delta_core.delta_value,
            lag(daily_delta_core.measured_from, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS measured_before,
            lead(daily_delta_core.measured_from, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS measured_after,
            lag(daily_delta_core.delta_value, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS delta_before,
            lead(daily_delta_core.delta_value, 1) OVER (PARTITION BY daily_delta_core.addr ORDER BY daily_delta_core.measured_from) AS delta_after
           FROM daily_delta_core
        ), delta_date_diff AS (
         SELECT delta_dates.measured_from,
            delta_dates.addr,
            delta_dates.var,
            delta_dates.delta_value,
            delta_dates.measured_before,
            delta_dates.measured_after,
            delta_dates.delta_before,
            delta_dates.delta_after,
                CASE
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 AND (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN 'mid'::text
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 THEN 'begin'::text
                    WHEN (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN 'end'::text
                    ELSE 'ok'::text
                END AS value_type,
                CASE
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 AND (delta_dates.measured_from - delta_dates.measured_before) > 1 THEN (delta_dates.delta_value / 2::numeric + delta_dates.delta_after) / (delta_dates.measured_after - delta_dates.measured_from + 1)::numeric
                    WHEN (delta_dates.measured_after - delta_dates.measured_from) > 1 THEN (delta_dates.delta_value + delta_dates.delta_after) / (delta_dates.measured_after - delta_dates.measured_from + 1)::numeric
                    ELSE NULL::numeric
                END AS fix
           FROM delta_dates
        ), joined_table AS (
         SELECT kalendar.den AS measured_from,
            meraky.addr,
            diff.delta_value,
            diff.value_type,
            diff.fix,
            core2.delta_value AS core2_delta,
                CASE
                    WHEN diff.value_type = 'ok'::text THEN COALESCE(core2.delta_value, diff.delta_value)
                    ELSE diff.fix
                END AS final_fix,
                CASE
                    WHEN diff.value_type = 'ok'::text THEN COALESCE(core2.var, diff.var)
                    ELSE 'core'::character varying
                END AS final_var
           FROM kalendar
             LEFT JOIN meraky ON true
             LEFT JOIN delta_date_diff diff ON diff.measured_from = kalendar.den AND diff.addr::text = meraky.addr::text
             LEFT JOIN daily_delta_core2 core2 ON core2.measured_from = kalendar.den AND core2.addr::text = meraky.addr::text
          ORDER BY meraky.addr, kalendar.den
        ), fixed_table AS (
         SELECT q.measured_from,
            q.addr,
            q.delta_value,
            q.value_type,
            q.fix,
            q.core2_delta,
            q.final_fix,
            q.final_var,
            q.value_partition,
            first_value(q.final_fix) OVER w AS value
           FROM ( SELECT joined_table.measured_from,
                    joined_table.addr,
                    joined_table.delta_value,
                    joined_table.value_type,
                    joined_table.fix,
                    joined_table.core2_delta,
                    joined_table.final_fix,
                    joined_table.final_var,
                    sum(
                        CASE
                            WHEN joined_table.final_fix IS NULL THEN 0
                            ELSE 1
                        END) OVER (PARTITION BY joined_table.addr ORDER BY joined_table.measured_from) AS value_partition
                   FROM joined_table) q
          WINDOW w AS (PARTITION BY q.addr, q.value_partition ORDER BY q.measured_from)
        )
 SELECT fixed_table.measured_from,
    fixed_table.addr,
    fixed_table.value AS delta_value,
    fixed_table.final_var,
    row_number() OVER (PARTITION BY fixed_table.addr, (date_trunc('month'::text, fixed_table.measured_from::timestamp with time zone)) ORDER BY fixed_table.value DESC) AS ran
   FROM fixed_table
  WHERE fixed_table.value IS NOT NULL;


CREATE OR REPLACE VIEW v_vpalac_apartments_daily_consumption
AS WITH last_measurement AS (
         SELECT date_part('year'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) AS measure_year,
            date_trunc('day'::text, max(to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone)) AS last_measurement,
            m.var_id
           FROM vpalac_measurement m
          GROUP BY (date_part('year'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone)), m.var_id
        ), daily_status AS (
         SELECT
                CASE
                    WHEN me.me_serial::text = '5640201'::text THEN 'Prostor 109,111'::text
                    WHEN mm.location_name IS NOT NULL THEN mm.location_name::text
                    WHEN me.umisteni::text ~~ '%Byt%'::text THEN "right"(me.umisteni::text, length(me.umisteni::text) - "position"(me.umisteni::text, 'Byt'::text) + 1)
                    ELSE "right"(me.umisteni::text, length(me.umisteni::text) - "position"(me.umisteni::text, 'Prostor'::text) + 1)
                END AS apartment,
                CASE
                    WHEN mt.met_nazev::text = 'Sensor'::text THEN 'Teplo'::character varying
                    ELSE mt.met_nazev
                END AS met_nazev,
            me.mis_nazev,
            me.var_id,
            u.jed_nazev,
            date_trunc('hour'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone) AS measure_date,
            min(m.value) AS start_value
           FROM vpalac_measuring_equipment me
             JOIN vpalac_measurement m ON me.var_id = m.var_id
             LEFT JOIN vpalac_meter_type mt ON me.met_id = mt.met_id
             LEFT JOIN vpalac_units u ON me.pot_id = u.pot_id
             LEFT JOIN vpalac_meter_mapping mm ON mm.var_id = me.var_id
          WHERE to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone >= (now() - '3 years'::interval)
          GROUP BY mm.location_name, me.umisteni, me.me_serial, (date_part('month'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone)), me.var_id, ("right"(me.umisteni::text, 6)), mt.met_nazev, me.mis_nazev, (date_trunc('hour'::text, to_timestamp((m.time_measurement / 1000)::double precision)::timestamp without time zone)), u.jed_nazev
        )
 SELECT ds.apartment,
    ds.met_nazev AS measure_type,
    ds.mis_nazev AS measure_detail,
    ds.jed_nazev,
    ds.measure_date,
    lm.last_measurement,
        CASE
            WHEN ds.met_nazev::text = 'Teplo'::text AND ds.measure_date = lm.last_measurement THEN lead(ds.start_value) OVER (PARTITION BY ds.var_id ORDER BY ds.measure_date)
            ELSE ds.start_value - lag(ds.start_value) OVER (PARTITION BY ds.var_id ORDER BY ds.measure_date)
        END AS consumption,
    ds.var_id
   FROM daily_status ds
     JOIN last_measurement lm ON ds.var_id = lm.var_id AND date_part('year'::text, ds.measure_date) = lm.measure_year;
