text: ------------------------------------
  -- mqtt,kotogep,status logok törlése
  ------------------------------------
  -- delete from kotode_mqtt_log ;
  -- alter sequence kotode_mqtt_log_id_seq RESTART WITH 1 ;
  
  delete from kotode_kotogep_log ;
  alter sequence kotode_kotogep_log_id_seq RESTART WITH 1 ;
  
  delete from kotode_status_log ;
  alter sequence kotode_status_log_id_seq RESTART WITH 1 ;
  
  ---------------------------
  -- kotode_kotogep_log írása
  ---------------------------
  WITH
  last_id AS (
    SELECT COALESCE(MAX(mqtt_log_id), 0) AS lastid FROM kotode_kotogep_log
  ),
  log1 AS (
    SELECT id, create_date AS datum, extract(hour FROM write_date) AS ora, split_part(topic, '/', 2) AS gepazonosito, split_part(topic, '/', 3) AS source, split_part(topic, '/', 4) AS sensor, payload
    FROM kotode_mqtt_log JOIN last_id ON lastid < id
  ),
  log AS (
    SELECT log1.*, gep.id AS kotogep_id, gep.name AS gepnev, gep.uzem,
    CASE WHEN ora < 6 THEN '3'
         WHEN ora < 14 THEN '1'
         WHEN ora < 22 THEN '2'
    ELSE '3'
    END AS muszak,
    CASE WHEN ora < 6 THEN '3/2'
         WHEN ora >= 22 THEN '3/1'
    END AS muszak3,
    CASE source
      WHEN 'status' THEN
        CASE payload
          WHEN 'offline' THEN 'ki'
        END
      WHEN 'sensor' THEN
        CASE sensor
          WHEN 'machine' THEN payload
        END
    END AS jelzes
    FROM log1
    JOIN kotode_kotogep AS gep ON gep.azonosito = log1.gepazonosito
  )
  INSERT INTO kotode_kotogep_log (jelzes, datum, uzem, kotogep_id, gepazonosito, gep, muszak, muszak3, mqtt_log_id)
  SELECT jelzes, datum, uzem, kotogep_id, gepazonosito, gepnev, muszak, muszak3, id FROM log WHERE jelzes IS NOT NULL order by id
  ;
  
  WITH
  ido AS (
    SELECT t1.id, extract('epoch' from t2.datum - t1.datum) AS sec FROM kotode_kotogep_log AS t1
    JOIN kotode_kotogep_log AS t2 ON t2.id = (SELECT MIN(id) FROM kotode_kotogep_log WHERE id > t1.id AND kotogep_id = t1.kotogep_id)
    WHERE t1.idotartam IS NULL
  )
  UPDATE kotode_kotogep_log AS log SET idotartam = ido.sec, idotartam_perc = ido.sec / 60,  idotartam_ora = ido.sec / 3600
  FROM ido WHERE ido.id = log.id
  ;
  
  --------------------------
  -- kotode_status_log írása
  --------------------------
  WITH
  last_id AS (
    SELECT COALESCE(MAX(mqtt_log_id), 0) AS lastid FROM kotode_status_log
  ),
  log1 AS (
    SELECT id, create_date AS datum, extract(hour FROM write_date) AS ora, split_part(topic, '/', 2) AS gepazonosito, split_part(topic, '/', 3) AS source, split_part(topic, '/', 4) AS sensor, payload
    FROM kotode_mqtt_log JOIN last_id ON lastid < id
  ),
  log AS (
    SELECT log1.*, gep.id AS kotogep_id, gep.name AS gepnev, gep.uzem,
    CASE WHEN ora < 6 THEN '3'
         WHEN ora < 14 THEN '1'
         WHEN ora < 22 THEN '2'
    ELSE '3'
    END AS muszak,
    CASE WHEN ora < 6 THEN '3/2'
         WHEN ora >= 22 THEN '3/1'
    END AS muszak3,
    CASE source
      WHEN 'status' THEN payload
    END AS jelzes
    FROM log1
    JOIN kotode_kotogep AS gep ON gep.azonosito = log1.gepazonosito
  )
  INSERT INTO kotode_status_log (jelzes, datum, uzem, kotogep_id, gepazonosito, gep, muszak, muszak3, mqtt_log_id)
  SELECT jelzes, datum, uzem, kotogep_id, gepazonosito, gepnev, muszak, muszak3, id FROM log WHERE jelzes IS NOT NULL order by id
  ;
  
  WITH
  ido AS (
    SELECT t1.id, extract('epoch' from t2.datum - t1.datum) AS sec FROM kotode_status_log AS t1
    JOIN kotode_status_log AS t2 ON t2.id = (SELECT MIN(id) FROM kotode_status_log WHERE id > t1.id AND kotogep_id = t1.kotogep_id)
    WHERE t1.idotartam IS NULL
  )
  UPDATE kotode_status_log AS log SET idotartam = ido.sec, idotartam_perc = ido.sec / 60,  idotartam_ora = ido.sec / 3600
  FROM ido WHERE ido.id = log.id
  ;
  