DROP VIEW v_consumption_energy_last_update;

DROP VIEW v_consumption_energy_missing_devices_last_update;

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) =
                CASE
                    WHEN d.addr::text !~~ '/%'::text THEN ('/'::text || d.addr::text)::character varying
                    ELSE d.addr
                END::text
             JOIN consumption_energy_buildings b ON d.building_id = b.id
          WHERE b.building_address_code::text !~~ '10.%'::text
          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 energetics.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 replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) AS 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 (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) IN ( SELECT
                        CASE
                            WHEN consumption_energy_devices.addr::text !~~ '/%'::text THEN ('/'::text || consumption_energy_devices.addr::text)::character varying
                            ELSE consumption_energy_devices.addr
                        END AS addr
                   FROM consumption_energy_devices))
          GROUP BY (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text)), consumption_energy_consumption.addr
        ), missing_devices_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 AND NOT (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) IN ( SELECT
                        CASE
                            WHEN consumption_energy_devices.addr::text !~~ '/%'::text THEN ('/'::text || consumption_energy_devices.addr::text)::character varying
                            ELSE consumption_energy_devices.addr
                        END AS addr
                   FROM consumption_energy_devices))
          GROUP BY consumption_energy_consumption.addr, (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text))
        ), buildings_mapping AS (
         SELECT DISTINCT
                CASE
                    WHEN d.addr::text !~~ '/%'::text THEN '/'::text || "left"(d.addr::text, "position"("right"(d.addr::text, length(d.addr::text) - 1), '/'::text))
                    ELSE "left"(d.addr::text, "position"("right"(d.addr::text, length(d.addr::text) - 1), '/'::text))
                END AS building_code,
            b.building_name
           FROM consumption_energy_devices d
             JOIN consumption_energy_buildings b ON d.building_id = b.id
          WHERE b.building_address_code::text !~~ '10.%'::text
        )
 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 =
        CASE
            WHEN bm.building_code !~~ '/%'::text THEN '/'::text || bm.building_code
            ELSE bm.building_code
        END
     LEFT JOIN missing_devices_last_not_zero lnz ON lu.addr = lnz.addr;

CREATE OR REPLACE VIEW v_consumption_energy_last_update_prague_10
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) =
                CASE
                    WHEN d.addr::text !~~ '/%'::text THEN ('/'::text || d.addr::text)::character varying
                    ELSE d.addr
                END::text
             JOIN consumption_energy_buildings b ON d.building_id = b.id
          WHERE b.building_address_code::text ~~ '10.%'::text
          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_prague_10
AS WITH missing_devices_last_update AS (
         SELECT replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) AS 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 (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) IN ( SELECT
                        CASE
                            WHEN consumption_energy_devices.addr::text !~~ '/%'::text THEN ('/'::text || consumption_energy_devices.addr::text)::character varying
                            ELSE consumption_energy_devices.addr
                        END AS addr
                   FROM consumption_energy_devices))
          GROUP BY (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text)), consumption_energy_consumption.addr
        ), missing_devices_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 AND NOT (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text) IN ( SELECT
                        CASE
                            WHEN consumption_energy_devices.addr::text !~~ '/%'::text THEN ('/'::text || consumption_energy_devices.addr::text)::character varying
                            ELSE consumption_energy_devices.addr
                        END AS addr
                   FROM consumption_energy_devices))
          GROUP BY consumption_energy_consumption.addr, (replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text))
        ), buildings_mapping AS (
         SELECT DISTINCT
                CASE
                    WHEN d.addr::text !~~ '/%'::text THEN '/'::text || "left"(d.addr::text, "position"("right"(d.addr::text, length(d.addr::text) - 1), '/'::text))
                    ELSE "left"(d.addr::text, "position"("right"(d.addr::text, length(d.addr::text) - 1), '/'::text))
                END AS building_code,
            b.building_name
           FROM consumption_energy_devices d
             JOIN consumption_energy_buildings b ON d.building_id = b.id
          WHERE b.building_address_code::text ~~ '10.%'::text
        )
 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 =
        CASE
            WHEN bm.building_code !~~ '/%'::text THEN '/'::text || bm.building_code
            ELSE bm.building_code
        END
     LEFT JOIN missing_devices_last_not_zero lnz ON lu.addr = lnz.addr;



