-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema Sensor
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema Sensor
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Sensor` DEFAULT CHARACTER SET utf8;
USE `Sensor`;

-- -----------------------------------------------------
-- Table `Sensor`.`backenduser`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`backenduser` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(20) NULL,
  `lastname` VARCHAR(20) NULL,
  `username` VARCHAR(30) NOT NULL,
  `password` VARCHAR(30) NOT NULL,
  `authKey` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Sensor`.`Device`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`Device` (
  `idDevice` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `description` VARCHAR(255) NULL,
  `additionDate` DATETIME NULL,
  `lastActualization` DATETIME NULL,
  PRIMARY KEY (`idDevice`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Sensor`.`Sensortype`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`Sensortype` (
  `idSensor_Type` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(63) NOT NULL,
  `DataSize` INT NOT NULL,
  PRIMARY KEY (`idSensor_Type`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Sensor`.`Sensor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`Sensor` (
  `idSensor` INT NOT NULL AUTO_INCREMENT,
  `idDevice` INT NOT NULL,
  `idSensor_Type` INT NOT NULL,
  `name` VARCHAR(100) NULL,
  PRIMARY KEY (`idSensor`),
  CONSTRAINT `fk_Sensor_Device`
    FOREIGN KEY (`idDevice`)
    REFERENCES `Sensor`.`Device` (`idDevice`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Sensor_Sensor_Type1`
    FOREIGN KEY (`idSensor_Type`)
    REFERENCES `Sensor`.`Sensortype` (`idSensor_Type`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_Sensor_Device_idx` ON `Sensor`.`Sensor` (`idDevice` ASC) VISIBLE;

CREATE INDEX `fk_Sensor_Sensor_Type1_idx` ON `Sensor`.`Sensor` (`idSensor_Type` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `Sensor`.`Session`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`Session` (
  `idSession` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `start` DATETIME NOT NULL,
  `samples` INT NOT NULL,
  `tp` FLOAT NOT NULL,
  PRIMARY KEY (`idSession`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Sensor`.`Sample`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`Sample` (
  `idSample` INT NOT NULL AUTO_INCREMENT,
  `idSensor` INT NOT NULL,
  `value_1` FLOAT NULL,
  `value_2` FLOAT NULL,
  `value_3` FLOAT NULL,
  `timestamp` DATETIME(6) NOT NULL,
  PRIMARY KEY (`idSample`),
  CONSTRAINT `fk_Sample_Sensor1`
    FOREIGN KEY (`idSensor`)
    REFERENCES `Sensor`.`Sensor` (`idSensor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_Sample_Sensor1_idx` ON `Sensor`.`Sample` (`idSensor` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `Sensor`.`Sessionhassensor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Sensor`.`Sessionhassensor` (
  `idSession` INT NOT NULL,
  `idSensor` INT NOT NULL,
  PRIMARY KEY (`idSession`, `idSensor`),
  CONSTRAINT `fk_Session_has_Sensor_Session1`
    FOREIGN KEY (`idSession`)
    REFERENCES `Sensor`.`Session` (`idSession`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Session_has_Sensor_Sensor1`
    FOREIGN KEY (`idSensor`)
    REFERENCES `Sensor`.`Sensor` (`idSensor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_Session_has_Sensor_Sensor1_idx` ON `Sensor`.`Sessionhassensor` (`idSensor` ASC) VISIBLE;

CREATE INDEX `fk_Session_has_Sensor_Session1_idx` ON `Sensor`.`Sessionhassensor` (`idSession` ASC) VISIBLE;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `Sensor`.`Device`
-- -----------------------------------------------------
START TRANSACTION;
USE `Sensor`;
INSERT INTO `Sensor`.`backenduser` (`id`, `firstname`, `lastname`, `username`, `password`, `authKey`) VALUES (1, 'Jan', 'Kowalski', 'Student', '1234', '12345');

COMMIT;

-- -----------------------------------------------------
-- Data for table `Sensor`.`Device`
-- -----------------------------------------------------
START TRANSACTION;
USE `Sensor`;
INSERT INTO `Sensor`.`Device` (`idDevice`, `name`, `description`, `additionDate`, `lastActualization`) VALUES (1, 'LG G7', 'Telefon prywatny', '2020-07-29 12:18:29.000000', '2020-07-29 12:18:29.000000');
INSERT INTO `Sensor`.`Device` (`idDevice`, `name`, `description`, `additionDate`, `lastActualization`) VALUES (2, 'Xiaomi Mi 10', 'Telefon służbowy', '2018-03-15 13:23:21.000000', '2018-03-15 13:23:21.000000');

COMMIT;


-- -----------------------------------------------------
-- Data for table `Sensor`.`Sensortype`
-- -----------------------------------------------------
START TRANSACTION;
USE `Sensor`;
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Proximity', 1);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Light', 1);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Accelerometer', 3);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Gyroscope', 3);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Barometer', 1);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Magnetic', 3);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Orientation', 3);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Gravity', 3);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Linear Acceleration', 3);
INSERT INTO `Sensor`.`Sensortype` (`idSensor_Type`, `name`, `DataSize`) VALUES (DEFAULT, 'Rotation Vector', 3);

COMMIT;


-- -----------------------------------------------------
-- Data for table `Sensor`.`Sensor`
-- -----------------------------------------------------
START TRANSACTION;
USE `Sensor`;
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 1, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 2, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 3, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 4, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 5, 'Ciśnienie');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 6, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 7, 'Kompas');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 8, 'Poziomica');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 9, 'Krokomierz');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 1, 10, 'Obrót ekranu');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 1, 'Wyłącz ekran');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 2, 'Oświetlenie 1');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 2, 'Oświetlenie 2');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 3, 'Przyspieszenie całość');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 4, 'Prędkość kątowa');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 5, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 6, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 7, 'Reserved for future release');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 8, 'Przyspiedzenie grawitacja');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 9, 'Przyspieszenie liniowe');
INSERT INTO `Sensor`.`Sensor` (`idSensor`, `idDevice`, `idSensor_Type`, `name`) VALUES (DEFAULT, 2, 10, 'Całka z żyroskopu');

COMMIT;