drop table soil_sample;
drop table soil_monitor;

create table sm_device(
    id              integer         not null primary key autoincrement,
    bt_device       integer         not null references bt_device(id),
    useful          boolean
);

create table sm_sensor(
    id              integer         not null primary key autoincrement,
    sm_device       integer         not null references soil_device(id),
    idx             integer         not null,
    constraint uq_sensor_idx unique(sm_device, idx)
);

create table soil_sample(
    id              integer         not null primary key autoincrement,
    sm_sensor       integer         not null references sm_sensor(id),
    timestamp       timestamp,
    value           integer
);