DROP TABLE IF EXISTS value;
DROP TABLE IF EXISTS device_property;
DROP TABLE IF EXISTS device;
DROP TABLE IF EXISTS message;

DROP SEQUENCE IF EXISTS id_seq;

CREATE SEQUENCE id_seq;

CREATE TABLE message (
  timestamp TIMESTAMPTZ   NOT NULL,
  topic     VARCHAR(1000) NOT NULL,
  message   BYTEA         NOT NULL
);

CREATE TABLE device (
  id                BIGINT        NOT NULL DEFAULT nextval('id_seq'),
  key               VARCHAR(1000) NOT NULL,
  created_timestamp TIMESTAMPTZ   NOT NULL,
  name              VARCHAR(1000),
  description       VARCHAR(1000),

  PRIMARY KEY (id),
  CONSTRAINT uq_device__key UNIQUE (key)
);

CREATE TABLE device_property (
  id                BIGINT        NOT NULL DEFAULT nextval('id_seq'),
  device            BIGINT        NOT NULL REFERENCES device,
  key               VARCHAR(1000) NOT NULL,
  created_timestamp TIMESTAMPTZ   NOT NULL,
  name              VARCHAR(1000),
  description       VARCHAR(1000),
  last_value        VARCHAR(1000),

  PRIMARY KEY (id),
  CONSTRAINT uq_device_property__key_name UNIQUE (id, name)
);

-- no constraints!
CREATE TABLE value (
  property  BIGINT      NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  value     VARCHAR(1000)
);