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 energetics.consumption_energy_devices
          WHERE consumption_energy_devices.meter_type::text = 'Plynoměr'::text AND consumption_energy_devices.addr::text !~~ '10.%'::text AND consumption_energy_devices.addr::text !~~ '/10.%'::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 energetics.v_consumption_energy_delta
          WHERE (v_consumption_energy_delta.addr::text ~~ '%/PF%'::text OR v_consumption_energy_delta.addr::text ~~ '%/PP%'::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,
            replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text)::character varying(255) AS addr,
            consumption_energy_consumption.var,
            sum(consumption_energy_consumption.value) AS delta_value
           FROM energetics.consumption_energy_consumption
          WHERE consumption_energy_consumption.var::text = 'core2'::text AND consumption_energy_consumption.time_utc::date > '2018-12-31'::date AND consumption_energy_consumption.addr::text !~~ '10.%'::text AND consumption_energy_consumption.addr::text !~~ '/10.%'::text
          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;
  
