-- 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 P1-LockCypher
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `P1-LockCypher`.`Role`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `P1-LockCypher`.`Role` (
  `idRole` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`idRole`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `P1-LockCypher`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `P1-LockCypher`.`User` (
  `idUser` INT NOT NULL AUTO_INCREMENT,
  `FName` VARCHAR(45) NULL,
  `LName` VARCHAR(45) NULL,
  `Email` VARCHAR(45) NULL,
  `Tel` VARCHAR(45) NULL,
  `Username` VARCHAR(45) NULL,
  `Password` VARCHAR(45) NULL,
  `CreatedAt` TIMESTAMP NULL,
  `idRole` INT NOT NULL,
  PRIMARY KEY (`idUser`),
  UNIQUE INDEX `Username_UNIQUE` (`Username` ASC),
  UNIQUE INDEX `Email_UNIQUE` (`Email` ASC),
  UNIQUE INDEX `Tel_UNIQUE` (`Tel` ASC),
  INDEX `fk_User_Role1_idx` (`idRole` ASC),
  CONSTRAINT `fk_User_Role1`
    FOREIGN KEY (`idRole`)
    REFERENCES `P1-LockCypher`.`Role` (`idRole`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `P1-LockCypher`.`Scan`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `P1-LockCypher`.`Scan` (
  `idScan` INT NOT NULL AUTO_INCREMENT,
  `ScanDate` TIMESTAMP NULL,
  `ACTIE` VARCHAR(45) NULL,
  PRIMARY KEY (`idScan`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `P1-LockCypher`.`Sensor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `P1-LockCypher`.`Sensor` (
  `idSensor` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`idSensor`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `P1-LockCypher`.`Code`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `P1-LockCypher`.`Code` (
  `idCode` INT NOT NULL AUTO_INCREMENT,
  `Key` VARCHAR(45) NOT NULL,
  `CreatedAt` TIMESTAMP NOT NULL,
  `idScan` INT NOT NULL,
  `idSensor` INT NOT NULL,
  PRIMARY KEY (`idCode`),
  INDEX `fk_Barcode_Scan1_idx` (`idScan` ASC),
  INDEX `fk_Code_Sensor1_idx` (`idSensor` ASC),
  UNIQUE INDEX `Key_UNIQUE` (`Key` ASC),
  CONSTRAINT `fk_Barcode_Scan1`
    FOREIGN KEY (`idScan`)
    REFERENCES `P1-LockCypher`.`Scan` (`idScan`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Code_Sensor1`
    FOREIGN KEY (`idSensor`)
    REFERENCES `P1-LockCypher`.`Sensor` (`idSensor`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `P1-LockCypher`.`User_has_Code`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `P1-LockCypher`.`User_has_Code` (
  `User_idUser` INT NOT NULL,
  `Code_idCode` INT NOT NULL,
  PRIMARY KEY (`User_idUser`, `Code_idCode`),
  INDEX `fk_User_has_Code_Code1_idx` (`Code_idCode` ASC),
  INDEX `fk_User_has_Code_User1_idx` (`User_idUser` ASC),
  CONSTRAINT `fk_User_has_Code_User1`
    FOREIGN KEY (`User_idUser`)
    REFERENCES `P1-LockCypher`.`User` (`idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_User_has_Code_Code1`
    FOREIGN KEY (`Code_idCode`)
    REFERENCES `P1-LockCypher`.`Code` (`idCode`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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