--
-- Sequences
--
CREATE SEQUENCE seq_measured_value MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE SEQUENCE seq_sensor MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE SEQUENCE seq_alert MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE SEQUENCE seq_alert_log MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE SEQUENCE seq_relay MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE SEQUENCE seq_relay_state MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;

  
-- 
-- SENSOR_TYPES
--
CREATE TABLE iot.sensor_type (
  id                 NUMBER(8) NOT NULL,
  name               NVARCHAR2(30) NOT NULL,
  unit               NVARCHAR2(15) NOT NULL,
  description        NVARCHAR2(160) NULL
)
/

ALTER TABLE iot.sensor_type ADD CONSTRAINT pk_sensor_type_id PRIMARY KEY (id)
/


-- 
-- SENSOR_OWNERS
--
CREATE TABLE iot.person (
  id                 NUMBER(8) NOT NULL,
  first_name         VARCHAR2(20) NOT NULL,
  last_name          VARCHAR2(20) NOT NULL,
  mail_recipient   	 VARCHAR2(30) NULL,
  description        NVARCHAR2(80) NULL
)
/

ALTER TABLE iot.person ADD CONSTRAINT pk_person_id PRIMARY KEY (id)
/


-- 
-- SENSORS
--
CREATE TABLE iot.sensor (
  id                 NUMBER(8) NOT NULL,
  id_type            NUMBER(8) NOT NULL,
  id_owner           NUMBER(8) NOT NULL,
  code               VARCHAR2(20) NOT NULL,
  name               VARCHAR2(50) NOT NULL,
  description        NVARCHAR2(80) NULL,
  date_created       DATE DEFAULT SYSDATE NOT NULL,
  date_terminated    DATE DEFAULT NULL NULL
)
/

ALTER TABLE iot.sensor ADD CONSTRAINT pk_sensor_id PRIMARY KEY (id)
/

ALTER TABLE iot.sensor ADD CONSTRAINT fk_sensor__type FOREIGN KEY (id_type) REFERENCES iot.sensor_type(id)
/

ALTER TABLE iot.sensor ADD CONSTRAINT fk_sensor__owner FOREIGN KEY (id_owner) REFERENCES iot.person(id)
/

ALTER TABLE iot.sensor ADD CONSTRAINT uq_sensor_code UNIQUE (code) USING INDEX
/


-- 
-- MEASURED_VALUES
--
CREATE TABLE iot.measured_value (
  id                   NUMBER(8) NOT NULL,
  id_sensor			   NUMBER(8) NOT NULL,
  date_measured        DATE NOT NULL,
  the_value            FLOAT NOT NULL
)
/

ALTER TABLE iot.measured_value ADD CONSTRAINT pk_measured_value_id PRIMARY KEY (id)
/

ALTER TABLE iot.measured_value ADD CONSTRAINT fk_meas_value__sensor FOREIGN KEY (id_sensor) REFERENCES iot.sensor(id)
/

ALTER TABLE iot.measured_value ADD CONSTRAINT uq_meas_value__date UNIQUE (id_sensor, date_measured) USING INDEX
/


-- 
-- ALERTS
--
CREATE TABLE iot.alert (
  id                   NUMBER(8) NOT NULL,
  id_sensor			   NUMBER(8) NOT NULL,
  threshold            FLOAT NOT NULL,
  active			   NUMBER(1) DEFAULT 1 NOT NULL, CONSTRAINT chk_alert_active CHECK (active IN (0, 1)),
  alert_mode		   CHAR(1) NOT NULL, CONSTRAINT chk_alert_mode CHECK (alert_mode IN ('H', 'L')), 	-- H for checking high value, L for low value
  mail_recipient   	   VARCHAR2(30) NULL, 	-- Alert mail or null for owners mail use
  mail_subject         NVARCHAR2(50) NOT NULL
)
/

ALTER TABLE iot.alert ADD CONSTRAINT pk_alert_id PRIMARY KEY (id)
/

ALTER TABLE iot.alert ADD CONSTRAINT fk_alert__sensor FOREIGN KEY (id_sensor) REFERENCES iot.sensor(id) 
/


-- 
-- ALERT_LOGS
--
CREATE TABLE iot.alert_log (
  id                   NUMBER(8) NOT NULL,
  id_meas_value 	   NUMBER(8) NOT NULL,
  id_alert			   NUMBER(8) NOT NULL
)
/

ALTER TABLE iot.alert_log ADD CONSTRAINT pk_alert_log_id PRIMARY KEY (id)
/

ALTER TABLE iot.alert_log ADD CONSTRAINT fk_alert_logs__meas_value FOREIGN KEY (id_meas_value) REFERENCES iot.measured_value(id) ON DELETE CASCADE
/


ALTER TABLE iot.alert_log ADD CONSTRAINT fk_alert_logs__alert FOREIGN KEY (id_alert) REFERENCES iot.alert(id)
/

-- 
-- RELAYS
--
CREATE TABLE iot.relay (
  id                 NUMBER(8) NOT NULL,
  id_owner           NUMBER(8) NOT NULL,
  code               VARCHAR2(20) NOT NULL,
  name               VARCHAR2(50) NOT NULL,
  description        NVARCHAR2(80) NULL,
  date_created       DATE DEFAULT SYSDATE NOT NULL,
  date_terminated    DATE DEFAULT NULL NULL
)
/

ALTER TABLE iot.relay ADD CONSTRAINT pk_relay_id PRIMARY KEY (id)
/

ALTER TABLE iot.relay ADD CONSTRAINT fk_relay__owner FOREIGN KEY (id_owner) REFERENCES iot.person(id)
/

ALTER TABLE iot.relay ADD CONSTRAINT uq_relay_code UNIQUE (code) USING INDEX
/

-- 
-- RELAY_STATES
--
CREATE TABLE iot.relay_state (
  id                   NUMBER(8) NOT NULL,
  id_relay			   NUMBER(8) NOT NULL,
  date_change          DATE DEFAULT SYSDATE NOT NULL,
  state			       NUMBER(1) DEFAULT 0 NOT NULL, CONSTRAINT chk_relay_state CHECK (state IN (0, 1))
)
/

ALTER TABLE iot.relay_state ADD CONSTRAINT pk_relay_state_id PRIMARY KEY (id)
/

ALTER TABLE iot.relay_state ADD CONSTRAINT fk_relay_state__relay FOREIGN KEY (id_relay) REFERENCES iot.relay(id)
/

ALTER TABLE iot.relay_state ADD CONSTRAINT uq_relay_state__date UNIQUE (id_relay, date_change) USING INDEX
/