create_db.sql

-- MySQL Script generated by MySQL Workbench
-- Tue Jan  7 20:28:02 2020
-- Model: New Model    Version: 1.0
-- 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 mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema fiscalize
-- -----------------------------------------------------
drop SCHEMA IF EXISTS `fiscalize` ;

-- -----------------------------------------------------
-- Schema fiscalize
-- -----------------------------------------------------
create SCHEMA IF NOT EXISTS `fiscalize` DEFAULT CHARACTER SET utf8 ;
USE `fiscalize` ;

-- -----------------------------------------------------
-- Table `fiscalize`.`cota`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`cota` (
  `cotaId` INT(11) NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`cotaId`))
ENGINE = InnoDB
AUTO_INCREMENT = 18
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `fiscalize`.`partido`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`partido` (
  `partidoId` INT(11) NOT NULL AUTO_INCREMENT,
  `sigla` VARCHAR(50) NOT NULL,
  `nome` VARCHAR(200) NULL DEFAULT NULL,
  PRIMARY KEY (`partidoId`))
ENGINE = InnoDB
AUTO_INCREMENT = 31
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `fiscalize`.`parlamentar`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`parlamentar` (
  `parlamentarId` INT(11) NOT NULL AUTO_INCREMENT,
  `partidoId` INT(11) NOT NULL,
  `nome` VARCHAR(200) NOT NULL,
  `nomeCivil` VARCHAR(200) NULL DEFAULT NULL,
  `email` VARCHAR(500) NULL DEFAULT NULL,
  `profissao` VARCHAR(200) NULL DEFAULT NULL,
  `ideCadastro` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`parlamentarId`),
  CONSTRAINT `fk_Parlamentar_Partido1`
    FOREIGN KEY (`partidoId`)
    REFERENCES `fiscalize`.`partido` (`partidoId`)
    ON delete NO ACTION
    ON update NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 786
DEFAULT CHARACTER SET = utf8;

create INDEX `fk_Parlamentar_Partido1_idx` ON `fiscalize`.`parlamentar` (`partidoId` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `fiscalize`.`uf`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`uf` (
  `ufId` INT(11) NOT NULL AUTO_INCREMENT,
  `sigla` VARCHAR(2) NOT NULL,
  `nome` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`ufId`))
ENGINE = InnoDB
AUTO_INCREMENT = 29
DEFAULT CHARACTER SET = utf8;

create UNIQUE INDEX `sigla_UNIQUE` ON `fiscalize`.`uf` (`sigla` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `fiscalize`.`notafiscal`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`notafiscal` (
  `notaFiscalId` INT(11) NOT NULL AUTO_INCREMENT,
  `parlamentarId` INT(11) NOT NULL,
  `cotaId` INT(11) NOT NULL,
  `ufId` INT(11) NOT NULL,
  `dataEmissao` DATETIME NULL DEFAULT NULL,
  `descricao` VARCHAR(500) NULL DEFAULT NULL,
  `descricaoSubCota` VARCHAR(500) NULL DEFAULT NULL,
  `beneficiario` VARCHAR(500) NULL DEFAULT NULL,
  `cpfCnpj` VARCHAR(45) NULL DEFAULT NULL,
  `ano` INT(11) NOT NULL,
  `mes` INT(11) NOT NULL,
  `numeroDocumento` VARCHAR(45) NULL DEFAULT NULL,
  `parcela` INT(11) NULL DEFAULT NULL,
  `tipoDocumentoFiscal` INT(11) NOT NULL COMMENT 'Nota Fiscal - 0\nRecibo - 1\nDespesa Exterior - 2\n',
  `nomePassageiro` VARCHAR(500) NULL DEFAULT NULL,
  `trechoViagem` VARCHAR(500) NULL DEFAULT NULL,
  `valor` DECIMAL(10,2) NOT NULL,
  `valorGlosa` DECIMAL(10,2) NULL DEFAULT NULL,
  `valorLiquido` DECIMAL(10,2) NULL DEFAULT NULL,
  `dataInclusao` DATETIME NOT NULL,
  PRIMARY KEY (`notaFiscalId`),
  CONSTRAINT `fk_NotaFiscal_Cota1`
    FOREIGN KEY (`cotaId`)
    REFERENCES `fiscalize`.`cota` (`cotaId`)
    ON delete NO ACTION
    ON update NO ACTION,
  CONSTRAINT `fk_NotaFiscal_Parlamentar1`
    FOREIGN KEY (`parlamentarId`)
    REFERENCES `fiscalize`.`parlamentar` (`parlamentarId`)
    ON delete NO ACTION
    ON update NO ACTION,
  CONSTRAINT `fk_notafiscal_Uf1`
    FOREIGN KEY (`ufId`)
    REFERENCES `fiscalize`.`uf` (`ufId`)
    ON delete NO ACTION
    ON update NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 79368
DEFAULT CHARACTER SET = utf8;

create INDEX `fk_NotaFiscal_Parlamentar1_idx` ON `fiscalize`.`notafiscal` (`parlamentarId` ASC) VISIBLE;

create INDEX `fk_NotaFiscal_Cota1_idx` ON `fiscalize`.`notafiscal` (`cotaId` ASC) VISIBLE;

create INDEX `fk_notafiscal_Uf1_idx` ON `fiscalize`.`notafiscal` (`ufId` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `fiscalize`.`usuario`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`usuario` (
  `usuarioId` INT(11) NOT NULL AUTO_INCREMENT,
  `usuario` VARCHAR(45) NULL DEFAULT NULL,
  `senha` VARCHAR(45) NULL DEFAULT NULL,
  `facebookId` VARCHAR(45) NULL DEFAULT NULL,
  `tokenId` VARCHAR(100) NULL DEFAULT NULL,
  `nome` VARCHAR(100) NULL DEFAULT NULL,
  `email` VARCHAR(200) NULL DEFAULT NULL,
  `experiente` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`usuarioId`))
ENGINE = InnoDB
AUTO_INCREMENT = 17932
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `fiscalize`.`analise`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`analise` (
  `analiseId` INT(11) NOT NULL AUTO_INCREMENT,
  `notaFiscalId` INT(11) NOT NULL,
  `responsavelUsuarioId` INT(11) NOT NULL,
  `concluida` TINYINT(1) NOT NULL DEFAULT '0',
  `comentarios` VARCHAR(2000) NULL DEFAULT NULL,
  PRIMARY KEY (`analiseId`),
  CONSTRAINT `fk_analise_notafiscal1`
    FOREIGN KEY (`notaFiscalId`)
    REFERENCES `fiscalize`.`notafiscal` (`notaFiscalId`)
    ON delete NO ACTION
    ON update NO ACTION,
  CONSTRAINT `fk_analise_usuario1`
    FOREIGN KEY (`responsavelUsuarioId`)
    REFERENCES `fiscalize`.`usuario` (`usuarioId`)
    ON delete NO ACTION
    ON update NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

create INDEX `fk_analise_notafiscal1_idx` ON `fiscalize`.`analise` (`notaFiscalId` ASC) VISIBLE;

create INDEX `fk_analise_usuario1_idx` ON `fiscalize`.`analise` (`responsavelUsuarioId` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `fiscalize`.`despesa`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`despesa` (
  `despesaId` INT(11) NOT NULL AUTO_INCREMENT,
  `codigoLegislatura` VARCHAR(10) NULL DEFAULT NULL,
  `dataEmissao` VARCHAR(30) NULL DEFAULT NULL,
  `idDeputado` VARCHAR(10) NULL DEFAULT NULL,
  `tipoDocumento` VARCHAR(10) NULL DEFAULT NULL,
  `numeroCarteiraParlamentar` VARCHAR(10) NULL DEFAULT NULL,
  `legislatura` VARCHAR(10) NULL DEFAULT NULL,
  `ano` VARCHAR(10) NULL DEFAULT NULL,
  `numeroEspecificacaoSubCota` VARCHAR(500) NULL DEFAULT NULL,
  `lote` VARCHAR(20) NULL DEFAULT NULL,
  `mes` VARCHAR(10) NULL DEFAULT NULL,
  `parcela` VARCHAR(10) NULL DEFAULT NULL,
  `ressarcimento` VARCHAR(10) NULL DEFAULT NULL,
  `numeroSubCota` VARCHAR(500) NULL DEFAULT NULL,
  `siglaPartido` VARCHAR(50) NULL DEFAULT NULL,
  `siglaUF` VARCHAR(50) NULL DEFAULT NULL,
  `nomeParlamentar` VARCHAR(500) NULL DEFAULT NULL,
  `cnpjCPF` VARCHAR(20) NULL DEFAULT NULL,
  `descricao` VARCHAR(500) NULL DEFAULT NULL,
  `descricaoEspecificacao` VARCHAR(500) NULL DEFAULT NULL,
  `fornecedor` VARCHAR(500) NULL DEFAULT NULL,
  `numero` VARCHAR(50) NULL DEFAULT NULL,
  `passageiro` VARCHAR(200) NULL DEFAULT NULL,
  `trecho` VARCHAR(500) NULL DEFAULT NULL,
  `valorDocumento` VARCHAR(20) NULL DEFAULT NULL,
  `valorGlosa` VARCHAR(20) NULL DEFAULT NULL,
  `valorLiquido` VARCHAR(20) NULL DEFAULT NULL,
  `dataInclusao` DATETIME NOT NULL,
  PRIMARY KEY (`despesaId`))
ENGINE = InnoDB
AUTO_INCREMENT = 90766
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `fiscalize`.`suspeita`
-- -----------------------------------------------------
create TABLE IF NOT EXISTS `fiscalize`.`suspeita` (
  `suspeitaId` INT(11) NOT NULL AUTO_INCREMENT,
  `notaFiscalId` INT(11) NOT NULL,
  `usuarioId` INT(11) NOT NULL,
  `suspeita` TINYINT(1) NOT NULL DEFAULT '0',
  `suspeitaValor` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Valor sob suspeita?',
  `suspeitaObjeto` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Objeto da compra/contratação sob suspeita?',
  `suspeitaBeneficiario` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Fornecedor sob suspeita?',
  `comentarios` VARCHAR(2000) NULL DEFAULT NULL,
  `dataInclusao` DATETIME NOT NULL,
  PRIMARY KEY (`suspeitaId`),
  CONSTRAINT `fk_Suspeita_Usuario1`
    FOREIGN KEY (`usuarioId`)
    REFERENCES `fiscalize`.`usuario` (`usuarioId`)
    ON delete NO ACTION
    ON update NO ACTION,
  CONSTRAINT `fk_Suspeita_notafiscal1`
    FOREIGN KEY (`notaFiscalId`)
    REFERENCES `fiscalize`.`notafiscal` (`notaFiscalId`)
    ON delete NO ACTION
    ON update NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1371766
DEFAULT CHARACTER SET = utf8;

create INDEX `fk_Suspeita_notafiscal1_idx` ON `fiscalize`.`suspeita` (`notaFiscalId` ASC) VISIBLE;

create INDEX `fk_Suspeita_Usuario1_idx` ON `fiscalize`.`suspeita` (`usuarioId` ASC) VISIBLE;


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