CREATE DATABASE IF NOT EXISTS `nfeprodutordb2` DEFAULT CHARACTER SET utf8 ;
USE `nfeprodutordb2` ;

-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`access_levels`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`access_levels` (
  `idaccess_levels` INT NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(20) NOT NULL,
  `code` INT NOT NULL,
  PRIMARY KEY (`idaccess_levels`),
  UNIQUE INDEX `idaccess_levels_UNIQUE` (`idaccess_levels` ASC),
  UNIQUE INDEX `valor_UNIQUE` (`code` ASC),
  UNIQUE INDEX `nivel_UNIQUE` (`description` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NULL,
  `email` VARCHAR(50) NULL,
  `login` VARCHAR(20) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NULL,
  `access_levels` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idusuario_UNIQUE` (`id` ASC),
  UNIQUE INDEX `login_UNIQUE` (`login` ASC),
  UNIQUE INDEX `token_UNIQUE` (`token` ASC),
  INDEX `fk_usuario_access_levels1_idx` (`access_levels` ASC),
  CONSTRAINT `fk_usuario_access_levels1`
    FOREIGN KEY (`access_levels`)
    REFERENCES `nfeprodutordb2`.`access_levels` (`idaccess_levels`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`producer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`producer` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `document` VARCHAR(14) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `fantasy` VARCHAR(255) NULL,
  `ie` VARCHAR(20) NULL,
  `im` VARCHAR(20) NULL,
  `email` VARCHAR(50) NOT NULL,
  `fone` VARCHAR(11) NOT NULL,
  `number` VARCHAR(10) NULL,
  `address` VARCHAR(20) NOT NULL,
  `neighborhood` VARCHAR(20) NULL,
  `complement` VARCHAR(30) NULL,
  `uf` VARCHAR(2) NOT NULL,
  `city` VARCHAR(30) NOT NULL,
  `cep` VARCHAR(8) NOT NULL,
  `certd` VARCHAR(20) NULL,
  `codnota` VARCHAR(20) NULL,
  `user` INT NOT NULL,
  UNIQUE INDEX `idcomprador_UNIQUE` (`id` ASC),
  UNIQUE INDEX `cpfcnpj_UNIQUE` (`document` ASC),
  PRIMARY KEY (`id`),
  INDEX `fk_usuario_idx` (`user` ASC),
  CONSTRAINT `fk_usuario`
    FOREIGN KEY (`user`)
    REFERENCES `nfeprodutordb2`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`buyer`
-- 8aaqyZcLu3ZuGQbd <<< senha -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`buyer` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `document` VARCHAR(14) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `fantasy` VARCHAR(30) NULL,
  `ie` VARCHAR(20) NULL,
  `im` VARCHAR(20) NULL,
  `email` VARCHAR(50) NOT NULL,
  `fone` VARCHAR(11) NOT NULL,
  `number` VARCHAR(10) NULL,
  `address` VARCHAR(20) NOT NULL,
  `neighborhood` VARCHAR(20) NULL,
  `complement` VARCHAR(30) NULL,
  `uf` VARCHAR(2) NOT NULL,
  `city` VARCHAR(30) NOT NULL,
  `cep` VARCHAR(8) NOT NULL,
  UNIQUE INDEX `idcomprador_UNIQUE` (`id` ASC),
  UNIQUE INDEX `cpfcnpj_UNIQUE` (`document` ASC),
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`producer_buyer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`producer_buyer` (
  `produtor` INT NOT NULL,
  `comprador` INT NOT NULL,
  PRIMARY KEY (`produtor`, `comprador`),
  INDEX `comprador_idx` (`comprador` ASC),
  CONSTRAINT `producer`
    FOREIGN KEY (`produtor`)
    REFERENCES `nfeprodutordb2`.`producer` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `buyer`
    FOREIGN KEY (`comprador`)
    REFERENCES `nfeprodutordb2`.`buyer` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`conveyor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`conveyor` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `document` VARCHAR(14) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `fantasy` VARCHAR(30) NULL,
  `ie` VARCHAR(20) NULL,
  `fone` VARCHAR(11) NOT NULL,
  `number` VARCHAR(10) NULL,
  `address` VARCHAR(20) NOT NULL,
  `neighborhood` VARCHAR(20) NULL,
  `complement` VARCHAR(30) NULL,
  `uf` VARCHAR(2) NOT NULL,
  `City` VARCHAR(30) NOT NULL,
  `cep` VARCHAR(8) NOT NULL,
  `board` VARCHAR(7) NOT NULL,
  `boarduf` VARCHAR(2) NOT NULL,
  UNIQUE INDEX `idcomprador_UNIQUE` (`id` ASC),
  UNIQUE INDEX `cpfcnpj_UNIQUE` (`document` ASC),
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nfeprodutordb2`.`producer_conveyor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nfeprodutordb2`.`producer_conveyor` (
  `producer` INT NOT NULL,
  `conveyor` INT NOT NULL,
  PRIMARY KEY (`producer`, `conveyor`),
  INDEX `transportador_idx` (`conveyor` ASC),
  CONSTRAINT `produtor`
    FOREIGN KEY (`producer`)
    REFERENCES `nfeprodutordb2`.`producer` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `transportador`
    FOREIGN KEY (`conveyor`)
    REFERENCES `nfeprodutordb2`.`conveyor` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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