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 devicedb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `devicedb` DEFAULT CHARACTER SET utf8 ;
USE `devicedb` ;

-- -----------------------------------------------------
-- Table `devicedb`.`device`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `devicedb`.`device` (
  `devID` INT(11) NOT NULL,
  `lastupdate` DATETIME NULL,
  `osname` VARCHAR(45) NULL,
  `osversion` VARCHAR(45) NULL,
  `permspace` INT(11) NULL,
  `ram` INT(11) NULL,
  `lastlogin` DATETIME NULL,
  PRIMARY KEY (`devID`))
ENGINE = InnoDB;

INSERT INTO `device` (`devID`,`lastupdate`,`osname`,`osversion`,`permspace`,`ram`,`lastlogin`) VALUES (1001,'2022-03-30 12:23:30','Windows','7',500,8,'2022-03-30 12:33:30');
INSERT INTO `device` (`devID`,`lastupdate`,`osname`,`osversion`,`permspace`,`ram`,`lastlogin`) VALUES (1002,'2022-03-30 10:23:30','Ubuntu','20.04',750,4,'2022-03-30 13:13:30');
INSERT INTO `device` (`devID`,`lastupdate`,`osname`,`osversion`,`permspace`,`ram`,`lastlogin`) VALUES (1003,'2021-02-10 08:23:30','Windows','11',500,4,'2021-11-15 09:03:45');
INSERT INTO `device` (`devID`,`lastupdate`,`osname`,`osversion`,`permspace`,`ram`,`lastlogin`) VALUES (1004,'2021-12-14 09:43:30','Ubuntu','18.04',250,2,'2021-12-14 09:13:10');

-- -----------------------------------------------------
-- Table `devicedb`.`deviceuser`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `devicedb`.`deviceuser` (
  `uid` INT(11) NOT NULL,
  `username` VARCHAR(45) NULL,
  `account` VARCHAR(45) NULL,
  `pwdHash` LONGTEXT NULL,
  `mail` VARCHAR(45) NULL,
  PRIMARY KEY (`uid`))
ENGINE = InnoDB;

INSERT INTO `deviceuser` (`uid`,`username`,`account`,`pwdHash`,`mail`) VALUES (401,'mschmitt','group_a','defc4ebee6ad8eaf08f3b32a423b309979d93121','mschmitt@companyweb.net');
INSERT INTO `deviceuser` (`uid`,`username`,`account`,`pwdHash`,`mail`) VALUES (412,'jahmad','group_b','0eae80126d086963ad8f4e4418920a078c663e6d','jahmad@companyweb.net');
INSERT INTO `deviceuser` (`uid`,`username`,`account`,`pwdHash`,`mail`) VALUES (423,'ocetin','group_a','7c40f842fe7a3025bcaa8a76966fafc9c410a33a','ocetin@companyweb.net');
INSERT INTO `deviceuser` (`uid`,`username`,`account`,`pwdHash`,`mail`) VALUES (445,'lmueller','group_a','81db3bf690965ece47e4170027776801dcb83511','lmueller@companyweb.net');
INSERT INTO `deviceuser` (`uid`,`username`,`account`,`pwdHash`,`mail`) VALUES (457,'ekwiatkowski','group_b','35a815ca543f4e90f354ecf708a2a73242f53052','ekwiatkowski@companyweb.net');

-- -----------------------------------------------------
-- Table `devicedb`.`deviceschedule`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `devicedb`.`deviceschedule` (
  `scheduleID` INT(11) NOT NULL,
  `checkout` DATETIME NULL,
  `checkin` DATETIME NULL,
  `device_devID` INT(11) NOT NULL,
  `deviceuser_uid` INT(11) NOT NULL,
  PRIMARY KEY (`scheduleID`),
  INDEX `fk_deviceschedule_device_idx` (`device_devID` ASC),
  INDEX `fk_deviceschedule_deviceuser1_idx` (`deviceuser_uid` ASC),
  CONSTRAINT `fk_deviceschedule_device`
    FOREIGN KEY (`device_devID`)
    REFERENCES `devicedb`.`device` (`devID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_deviceschedule_deviceuser1`
    FOREIGN KEY (`deviceuser_uid`)
    REFERENCES `devicedb`.`deviceuser` (`uid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8001,'2021-08-12 08:23:28','2021-08-12 15:23:28',1001,423);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8002,'2021-08-12 07:40:48','2021-08-12 15:40:28',1004,412);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8003,'2021-10-22 08:10:24','2021-10-22 16:40:44',1001,423);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8004,'2021-12-22 09:30:23','2021-12-22 17:21:44',1004,401);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8005,'2021-12-22 08:00:23','2021-12-22 18:10:35',1002,423);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8006,'2022-01-16 11:50:27','2022-01-16 18:20:22',1002,457);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8007,'2022-03-26 09:00:07','2022-03-26 19:23:01',1003,445);
INSERT INTO `deviceschedule` (`scheduleID`,`checkout`,`checkin`,`device_devID`,`deviceuser_uid`) VALUES (8008,'2022-03-29 08:10:35','2022-03-29 16:11:35',1001,457);

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