DROP VIEW v_consumption_energy_missing_devices_last_update_prague_10;

DROP VIEW v_consumption_energy_last_update_prague_10;

CREATE OR REPLACE VIEW v_consumption_energy_last_update
AS WITH last_update AS (
         SELECT b.building_name,
            replace(c.addr::text, concat('/', c.var), ''::text) AS addr,
            max(c.time_utc) AS last_update
           FROM consumption_energy_consumption c
             JOIN consumption_energy_devices d ON replace(c.addr::text, concat('/', c.var), ''::text) = d.addr::text
             JOIN consumption_energy_buildings b ON d.building_id = b.id
          GROUP BY b.building_name, (replace(c.addr::text, concat('/', c.var), ''::text))
        ), last_not_zero AS (
         SELECT replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) AS 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 (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text))
        )
 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 = lnz.addr;

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 energetics.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::text,
        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 = lnz.addr;


