\connect senslog1

create schema if not exists statistics; 

alter schema statistics OWNER TO senslog;

/* table with calculated values */
create table statistics.records (
	id SERIAL PRIMARY KEY NOT NULL,
	group_id bigint not null,
	value_attribute text not null,      -- [MIN, MAX, SUM, COUNT]
	record_value double precision not null,
	time_interval integer not null,     -- interval in seconds
	time_stamp timestamptz not null,
    created timestamptz DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (group_id, value_attribute, time_stamp)
);

alter table statistics.records OWNER TO senslog;

/* time interval to analyse sensor groups */
create table statistics.groups_interval (
    id SERIAL PRIMARY KEY NOT NULL,
	time_interval integer not null,     -- interval in seconds
    persistence boolean not null,
    aggregation_type text not null      -- ['DOUBLE']
);

alter table statistics.groups_interval OWNER TO senslog;

/* list of unit-sensor pair to run analyses */
create table statistics.sensors (
    id SERIAL PRIMARY KEY NOT NULL,
    unit_id bigint not null,
	sensor_id bigint not null,
	CONSTRAINT fk_unit_sensor FOREIGN KEY (unit_id, sensor_id) REFERENCES public.units_to_sensors(unit_id, sensor_id)
);

alter table statistics.sensors OWNER TO senslog;

/* thresholds to be checked */
create table statistics.thresholds (
    id SERIAL PRIMARY KEY NOT NULL,
	group_id integer not null,
	mode varchar(10) not null,      -- [le, lt, ge, gt, ne, eq]
	property varchar(10) not null,  -- [MIN, MAX, AVG, VAL]
	threshold_value double precision not null
);

alter table statistics.thresholds OWNER TO senslog;

create table statistics.sensor_to_group (
    id SERIAL PRIMARY KEY NOT NULL,
    sensor_id integer REFERENCES statistics.sensors(id),
    group_id integer REFERENCES statistics.groups_interval(id),
    created timestamptz DEFAULT CURRENT_TIMESTAMP
);

alter table statistics.sensor_to_group OWNER TO senslog;