banco.sql

-- MySQL Script generated by MySQL Workbench
-- Thu Jan 18 12:42:13 2018
-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema pfc
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema pfc
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `pfc` DEFAULT CHARACTER SET utf8mb4 ;
USE `pfc` ;

-- -----------------------------------------------------
-- Table `pfc`.`member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`member` (
  `name` VARCHAR(150) NOT NULL,
  `personal_email` VARCHAR(150) NOT NULL,
  `professional_email` VARCHAR(150) NOT NULL,
  `rg` VARCHAR(14) NOT NULL,
  `cpf` VARCHAR(14) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `birthdate` VARCHAR(10) NOT NULL,
  `telephone` VARCHAR(14) NOT NULL,
  `marital_status` ENUM('single', 'married', 'divorced', 'widower') NOT NULL,
  `member_type` ENUM('director', 'member', 'trainee', 'admin') NOT NULL,
  `score` DECIMAL(10,2) NOT NULL,
  `path_profile_picture` VARCHAR(300) NOT NULL,
  PRIMARY KEY (`cpf`),
  UNIQUE INDEX `personal_email_UNIQUE` (`personal_email` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pfc`.`project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`project` (
  `project_id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(150) NOT NULL,
  `client_name` VARCHAR(150) NOT NULL,
  `duration` VARCHAR(50) NOT NULL,
  `payment_method` ENUM('in_money', 'in_parcel') NOT NULL,
  `price` DOUBLE NOT NULL,
  `status` ENUM('opened', 'closed') NOT NULL,
  PRIMARY KEY (`project_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pfc`.`members_in_project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`members_in_project` (
  `project_id` INT NOT NULL,
  `member_cpf` VARCHAR(14) NOT NULL,
  `role` ENUM('vendor', 'member') NOT NULL,
  PRIMARY KEY (`project_id`, `member_cpf`, `role`),
  INDEX `fk_members_in_project_member1_idx` (`member_cpf` ASC),
  INDEX `fk_members_in_project_project1_idx` (`project_id` ASC),
  CONSTRAINT `fk_members_in_project_member1`
    FOREIGN KEY (`member_cpf`)
    REFERENCES `pfc`.`member` (`cpf`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_members_in_project_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `pfc`.`project` (`project_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pfc`.`member_history`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`member_history` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `member_cpf` VARCHAR(14) NOT NULL,
  `reason` VARCHAR(200) NOT NULL,
  `date` DATE NOT NULL,
  `action` ENUM('gain', 'lose') NOT NULL,
  `value` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id`, `member_cpf`),
  INDEX `fk_member_history_member1_idx` (`member_cpf` ASC),
  CONSTRAINT `fk_member_history_member1`
    FOREIGN KEY (`member_cpf`)
    REFERENCES `pfc`.`member` (`cpf`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pfc`.`member_request`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`member_request` (
  `request_id` INT(11) NOT NULL AUTO_INCREMENT,
  `member_cpf` VARCHAR(14) NOT NULL,
  `status` ENUM('opened', 'accepted', 'rejected') NOT NULL,
  `reason` VARCHAR(300) NOT NULL,
  PRIMARY KEY (`request_id`, `member_cpf`),
  INDEX `fk_member_request_member1_idx` (`member_cpf` ASC),
  CONSTRAINT `fk_member_request_member1`
    FOREIGN KEY (`member_cpf`)
    REFERENCES `pfc`.`member` (`cpf`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pfc`.`request_files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`request_files` (
  `request_id` INT(11) NOT NULL,
  `filepath` VARCHAR(300) NOT NULL,
  INDEX `fk_request_files_member_request_idx` (`request_id` ASC),
  CONSTRAINT `fk_request_files_member_request`
    FOREIGN KEY (`request_id`)
    REFERENCES `pfc`.`member_request` (`request_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `pfc`.`payment_history`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pfc`.`payment_history` (
  `payment_id` INT NOT NULL AUTO_INCREMENT,
  `project_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `value` DOUBLE NOT NULL,
  `receptor_name` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`payment_id`, `project_id`),
  INDEX `fk_payment_history_project1_idx` (`project_id` ASC),
  CONSTRAINT `fk_payment_history_project1`
    FOREIGN KEY (`project_id`)
    REFERENCES `pfc`.`project` (`project_id`)
    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;

-- -----------------------------------------------------
-- Data for table `pfc`.`member`
-- -----------------------------------------------------
START TRANSACTION;
USE `pfc`;
INSERT INTO `pfc`.`member` (`name`, `personal_email`, `professional_email`, `rg`, `cpf`, `password`, `birthdate`, `telephone`, `marital_status`, `member_type`, `score`, `path_profile_picture`) VALUES ('Empresa', 'admin@admin.com', 'admin@admin.com', '00.000.000-00', '000.000.000-00', '$2y$10$xW0C87qP6Itk7Xw5OShBQujtpANYHsawZVAaV/Emk6S8ddhsR/Vse', '01/01/2000', '(75) 3161-8354', 'single', 'admin', 0, 'not_found');

COMMIT;