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;
