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

--- Milur gate --- (7, 7)
INSERT INTO object_type (_id, name, descr) VALUES (7, 'mi', 'Milur gate');
INSERT INTO object(_id, _otype, guid, name, descr, revision, uri, status) VALUES (7, 7, 'mi', 'Milur gate', 'Milur gate default', 0, '', 0);
INSERT INTO object_connection(_omaster, _oslave, type) VALUES (0, 7, 0);

--- Milur --- (7, 7)
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) VALUES (7, 'ConnectList', 'Connect list', 'List of connections to Milur servers and local servers', 'text', '', '');
INSERT INTO object_settings(_object, key, value) VALUES (7, 'ConnectList', '');

--- LPWAN Portal --- (6, 6)
INSERT INTO object_type (_id, name, descr) VALUES (6, 'www', 'LPWAN Portal');
INSERT INTO object(_id, _otype, guid, name, descr, revision, uri, status) VALUES (6, 6, 'www', 'LPWAN Portal', 'LPWAN Portal default', 0, '', 0);
INSERT INTO object_connection(_omaster, _oslave, type) VALUES (0, 6, 0);

--- LPWAN Portal --- (6, 6)
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) VALUES (6, 'Port', 'Port', 'Web server port', 'int', '1', '60000');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) VALUES (6, 'Name', 'Name', 'Web server name', 'text', '', '');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) VALUES (6, 'Cert', 'Certificate', 'Certificate path for web server', 'text', '', '');
INSERT INTO object_settings_type(_otype, key, name, descr, type, min_value, max_value) VALUES (6, 'CertPass', 'Certificate pass', 'Certificate passphrase', 'text', '', '');
INSERT INTO object_settings(_object, key, value) VALUES (6, 'Port', '8081');
INSERT INTO object_settings(_object, key, value) VALUES (6, 'Name', 'LPWAN portal');
INSERT INTO object_settings(_object, key, value) VALUES (6, 'Cert', '');
INSERT INTO object_settings(_object, key, value) VALUES (6, 'CertPass', '');

-- Table: meter_type

-- DROP TABLE meter_type CASCADE;

CREATE TABLE meter_type
(
  _id serial NOT NULL,
  name text NOT NULL,
  descr text,
  uuid text NOT NULL,
  vendor text,
  CONSTRAINT meter_type_pkey PRIMARY KEY (_id),
  CONSTRAINT meter_type_name_key UNIQUE (name),
  CONSTRAINT meter_type_uuid_key UNIQUE (uuid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE meter_type
  OWNER TO su;

-- Index: meter_type_name_idx

-- DROP INDEX meter_type_name_idx;

CREATE INDEX meter_type_name_idx
  ON meter_type
  USING btree
  (name COLLATE pg_catalog."default");


-- Table: meter

-- DROP TABLE meter CASCADE;

CREATE TABLE meter
(
  _id bigserial NOT NULL,
  _mtype integer NOT NULL,
  uuid text NOT NULL,
  name text,
  descr text,
  access_key text,
  status integer DEFAULT 0,
  last_message timestamp with time zone,
  next_check timestamp with time zone,
  CONSTRAINT meter_pkey PRIMARY KEY (_id),
  CONSTRAINT meter__mtype_fkey FOREIGN KEY (_mtype)
      REFERENCES meter_type (_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT meter__mtype_uuid_key UNIQUE (_mtype, uuid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE meter
  OWNER TO su;

-- Index: meter__mtype_uuid_idx

-- DROP INDEX meter__mtype_uuid_idx;

CREATE INDEX meter__mtype_uuid_idx
  ON meter
  USING btree
  (_mtype, uuid);

-- Table: meter_channel

-- DROP TABLE meter_channel CASCADE;

CREATE TABLE meter_channel
(
  _id bigserial NOT NULL,
  _meter integer NOT NULL,
  name text,
  descr text,
  uuid text,
  measure text,
  CONSTRAINT meter_channel_pkey PRIMARY KEY (_id),
  CONSTRAINT meter_channel__meter_fkey FOREIGN KEY (_meter)
      REFERENCES meter (_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT meter_channel__meter_uuid_key UNIQUE (_meter, uuid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE meter_channel
  OWNER TO su;

-- Index: meter__meter_uuid_idx

-- DROP INDEX meter__meter_uuid_idx;

CREATE INDEX meter__meter_uuid_idx
  ON meter_channel
  USING btree
  (_meter, uuid);


-- Table: meter_log

-- DROP TABLE meter_log CASCADE;

CREATE TABLE meter_log
(
  _id bigserial NOT NULL,
  _mchannel integer NOT NULL,
  mtimestamp timestamp with time zone NOT NULL,
  mvalue real NOT NULL,
  CONSTRAINT meter_log_pkey PRIMARY KEY (_id),
  CONSTRAINT meter_log__mchannel_fkey FOREIGN KEY (_mchannel)
      REFERENCES meter_channel (_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE meter_log
  OWNER TO su;

-- Index: meter_log__mchannel_mtimestamp_idx

-- DROP INDEX meter_log__mchannel_mtimestamp_idx;

CREATE INDEX meter_log__mchannel_mtimestamp_idx
  ON meter_log
  USING btree
  (_mchannel, mtimestamp);


INSERT INTO meter_type(_id, name, descr, uuid, vendor)
  VALUES (1, 'milur', 'Милур 304-307', 1000001, 'milandr');

-- View: meter_view

-- DROP VIEW meter_view;

CREATE OR REPLACE VIEW meter_view AS 
 SELECT m._id,m.uuid,m.name,m.descr,m.access_key,m.status,m.last_message,m.next_check,
    mt.name AS type_name,mt.descr AS type_descr,mt.uuid AS type_uuid,mt.vendor
   FROM meter m
     JOIN meter_type mt ON m._mtype = mt._id;

ALTER TABLE meter_view
  OWNER TO su;


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