DROP TABLE IF EXISTS soil_moisture_sample;
DROP TABLE IF EXISTS soil_moisture_sensor;
DROP TABLE IF EXISTS soil_moisture_device;

DROP SEQUENCE IF EXISTS sm_id;
CREATE SEQUENCE sm_id;

CREATE TABLE soil_moisture_device (
  id  BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('sm_id'),
  mac VARCHAR(100)
);

CREATE TABLE soil_moisture_sensor (
  id     BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('sm_id'),
  device BIGINT             NOT NULL REFERENCES soil_moisture_device,
  sensor BIGINT             NOT NULL
);

CREATE TABLE soil_moisture_sample (
  sensor    BIGINT NOT NULL REFERENCES soil_moisture_sensor,
  timestamp BIGINT NOT NULL,
  value     BIGINT NOT NULL,
  PRIMARY KEY (sensor, timestamp)
) WITHOUT OIDS;