-- MySQL Script generated by MySQL Workbench
-- Sun Mar 13 12:15:01 2022
-- 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 exophp
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `exophp`.`userAccount`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `exophp`.`userAccount` (
  `userId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(128) NOT NULL,
  `password` VARCHAR(128) NOT NULL,
  `tokenAutoLogin` VARCHAR(128) NULL DEFAULT NULL,
  `inscriptionDate` TIMESTAMP NOT NULL,
  `isClient` TINYINT(1) UNSIGNED NOT NULL,
  `isPro` TINYINT(1) UNSIGNED NOT NULL,
  `isAdmin` TINYINT(1) UNSIGNED NOT NULL,
  PRIMARY KEY (`userId`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `exophp`.`jobCategrory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `exophp`.`jobCategrory` (
  `jobCategoryId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `jobCategoryNameEn` VARCHAR(128) NOT NULL,
  `jobCategoryNameFr` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`jobCategoryId`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `exophp`.`mission`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `exophp`.`mission` (
  `missionId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` TIMESTAMP NOT NULL,
  `subject` VARCHAR(128) NOT NULL,
  `note` TINYINT(1) UNSIGNED NULL DEFAULT NULL,
  `review` TEXT NULL DEFAULT NULL,
  `acceptedByPro` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `validatedByClient` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `validatedByPro` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `clientId` INT UNSIGNED NOT NULL,
  `proId` INT UNSIGNED NOT NULL,
  `jobCategroryId` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`missionId`),
  INDEX `fk_mission_clientId_idx` (`clientId` ASC) VISIBLE,
  INDEX `fk_mission_proId_idx` (`proId` ASC) VISIBLE,
  INDEX `fk_mission_jobCategroryId_idx` (`jobCategroryId` ASC) VISIBLE,
  CONSTRAINT `fk_mission_userId`
    FOREIGN KEY (`clientId`)
    REFERENCES `exophp`.`userAccount` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_mission_consultantId`
    FOREIGN KEY (`proId`)
    REFERENCES `exophp`.`userAccount` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_mission_jobcategrory1`
    FOREIGN KEY (`jobCategroryId`)
    REFERENCES `exophp`.`jobCategrory` (`jobCategoryId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `exophp`.`userInfo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `exophp`.`userInfo` (
  `lastName` VARCHAR(128) NOT NULL,
  `firstName` VARCHAR(128) NOT NULL,
  `phoneNumber` VARCHAR(10) NOT NULL,
  `adress` VARCHAR(128) NOT NULL,
  `zipCode` VARCHAR(6) NOT NULL,
  `city` VARCHAR(128) NOT NULL,
  `degree` VARCHAR(128) NULL DEFAULT NULL,
  `capability` TEXT NULL DEFAULT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `userId` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`userId`),
  INDEX `fk_userinfo_userId_idx` (`userId` ASC) VISIBLE,
  CONSTRAINT `fk_userinfo_userId`
    FOREIGN KEY (`userId`)
    REFERENCES `exophp`.`userAccount` (`userId`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `exophp`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `exophp`.`category` (
  `category_id` INT NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`category_id`));


-- -----------------------------------------------------
-- Table `exophp`.`userJob`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `exophp`.`userJob` (
  `jobCategroryId` INT UNSIGNED NOT NULL,
  `userId` INT UNSIGNED NOT NULL,
  INDEX `fk_userJob_jobcategrory1_idx` (`jobCategroryId` ASC) VISIBLE,
  INDEX `fk_userJob_userinfo1_idx` (`userId` ASC) VISIBLE,
  CONSTRAINT `fk_userJob_jobCategroryId`
    FOREIGN KEY (`jobCategroryId`)
    REFERENCES `exophp`.`jobCategrory` (`jobCategoryId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_userJob_userId`
    FOREIGN KEY (`userId`)
    REFERENCES `exophp`.`userInfo` (`userId`)
    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;