CREATE TABLE measurements (
    id BIGSERIAL NOT NULL,
    time TIMESTAMPTZ NOT NULL DEFAULT now(),

    org TEXT,
    name TEXT,
    location TEXT,
    area TEXT,
    building TEXT,
    floor TEXT,
    room TEXT,
    "group" TEXT,
    device TEXT,

    measurement TEXT NOT NULL,
    field TEXT NOT NULL,

    value_bool BOOLEAN,
    value_int INTEGER,
    value_bigint BIGINT,
    value_double DOUBLE PRECISION,
    value_text TEXT,
    unit TEXT,
    tags JSONB,
    PRIMARY KEY (id, time)
);

CREATE INDEX measurements_home_idx ON measurements (name, location, building, floor, room, device, measurement, field, time DESC);
CREATE INDEX measurements_industrial_idx ON measurements (org, location, building, area, "group", room, device, measurement, field, time DESC);
CREATE INDEX measurements_tags_gin_idx ON measurements USING GIN (tags);

SELECT create_hypertable('measurements', 'time');