DO $$
DECLARE
 version_new_ CONSTANT integer := 21;-- new version number --
 ver_ integer;
BEGIN
  LOCK TABLE variables;
  SELECT version_is('Ls') INTO ver_;
  IF ver_ < version_new_ THEN
    -- begin update --

ALTER TABLE device ADD COLUMN rssi integer DEFAULT -100;

-- Table: radio_stats

-- DROP TABLE radio_stats;

CREATE TABLE radio_stats
(
  _id bigserial,
  ts timestamp with time zone,
  period integer,
  msg integer,
  umsg integer,
  rssi integer,
  drssi integer,
  CONSTRAINT radio_stats_pkey PRIMARY KEY (_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE radio_stats
  OWNER TO su;


-- Function: device_insert_or_update(bigint, integer, integer, boolean)

DROP FUNCTION device_insert_or_update(bigint, integer, boolean);

CREATE OR REPLACE FUNCTION device_insert_or_update(
    device_id_ bigint,
    frequency_ integer,
    rssi_ integer,
    is_ack_ boolean)
  RETURNS integer AS
$BODY$
DECLARE
  drssi_ integer;
BEGIN
IF is_ack_ IS TRUE THEN
  BEGIN
    INSERT INTO device(device_id, frequency, rssi, online, busy, ack)
      VALUES (device_id_, frequency_, rssi_, now(), now(), now());
    drssi_ := 0;
  EXCEPTION WHEN unique_violation THEN
    UPDATE device SET frequency = frequency_, online=now(), busy=now(), ack=now(), 
      rssi = CASE WHEN ABS(rssi - rssi_) <= 2 THEN rssi_
                  WHEN rssi < rssi_ THEN rssi + 2
                  ELSE rssi - 2
             END
      WHERE device_id = device_id_ RETURNING rssi_ - rssi INTO drssi_;
  END;
ELSE
  BEGIN
    INSERT INTO device(device_id, frequency, rssi, online, busy)
      VALUES (device_id_, frequency_, rssi_, now(), now());
    drssi_ := 0;
  EXCEPTION WHEN unique_violation THEN
    UPDATE device SET frequency = frequency_, online=now(), busy=now(), 
      rssi = CASE WHEN ABS(rssi - rssi_) <= 2 THEN rssi_
                  WHEN rssi < rssi_ THEN rssi + 2
                  ELSE rssi - 2
             END
      WHERE device_id = device_id_ RETURNING rssi_ - rssi INTO drssi_;
  END;
END IF;
RETURN drssi_;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION device_insert_or_update(bigint, integer, integer, boolean)
  OWNER TO su;


INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value)
                         VALUES (2, 'StatPeriod', 'Statistics period', 'Statistics period', 'time_period', '1 m', '30 m');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value)
                         VALUES (2, 'MinWorkTime', 'Minimum restart', 'Minimum work before restart', 'time_period', '1 h', '24 h');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value)
                         VALUES (2, 'RestartMsg', 'Restart msg', 'Restart if message statistics X times lower medium value (or zero)', 'real', '2', '1000');

PERFORM objects_add_setting(2, 'StatPeriod', '300000');
PERFORM objects_add_setting(2, 'MinWorkTime', '28800000');
PERFORM objects_add_setting(2, 'RestartMsg', '8');

    -- end update --
    PERFORM version_set('Ls', version_new_);
  END IF;
END$$;