-- drop dependant views
DROP VIEW IF EXISTS v_consumption_energy_buildings_w_data;
DROP VIEW IF EXISTS v_consumption_energy_consumption;

DROP VIEW IF EXISTS v_consumption_energy_consumption_month_p_f;
DROP VIEW IF EXISTS v_consumption_energy_consumption_month_el_f;
DROP VIEW IF EXISTS v_consumption_energy_consumption_month_v_f;
DROP VIEW IF EXISTS v_consumption_vrtbovsky_palac;

DROP VIEW IF EXISTS v_consumption_energy_vrtbovsky_palac_last_update;
DROP VIEW IF EXISTS v_vpalac_apartments_daily_consumption;


-- alter timestamp
ALTER TABLE vpalac_measurement ALTER COLUMN time_measurement TYPE bigint USING extract(epoch FROM time_measurement) * 1000;


-- rebuild dependant views

-- v_consumption_energy_consumption source

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;


-- v_consumption_energy_buildings_w_data source

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)) AND consumption_energy_buildings.building_address_code::text !~~ '10.%'::text;


-- v_consumption_vrtbovsky_palac source

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;


-- v_consumption_energy_consumption_month_p_f source

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;


-- v_consumption_energy_consumption_month_el_f source

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,
    replace(consumption_energy_consumption.addr::text, concat('/', consumption_energy_consumption.var), ''::text)::character varying(255) AS 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 AND consumption_energy_consumption.addr::text !~~ '/10.%'::text AND consumption_energy_consumption.addr::text !~~ '10.%'::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;


-- v_consumption_energy_consumption_month_v_f source

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;


-- v_consumption_energy_vrtbovsky_palac_last_update source

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;


-- v_vpalac_apartments_daily_consumption source

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,
            m.measure_date,
            m.start_value
           FROM vpalac_measuring_equipment me
             JOIN ( SELECT m_1.var_id,
                    date_trunc('hour'::text, to_timestamp((m_1.time_measurement / 1000)::double precision)::timestamp without time zone) AS measure_date,
                    min(m_1.value) AS start_value
                   FROM vpalac_measurement m_1
                  WHERE to_timestamp((m_1.time_measurement / 1000)::double precision)::timestamp without time zone >= (now() - '3 years'::interval)
                  GROUP BY m_1.var_id, (date_trunc('hour'::text, to_timestamp((m_1.time_measurement / 1000)::double precision)::timestamp without time zone))) 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
        )
 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;
