-- MySQL Script generated by MySQL Workbench
-- Wed Mar 29 06:19:59 2017
-- 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 maf
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `maf` ;

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

-- -----------------------------------------------------
-- Table `maf`.`roles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`roles` ;

CREATE TABLE IF NOT EXISTS `maf`.`roles` (
  `id` INT UNSIGNED NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`admins`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`admins` ;

CREATE TABLE IF NOT EXISTS `maf`.`admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mail` VARCHAR(255) NULL,
  `password` VARCHAR(255) NULL,
  `name` VARCHAR(12) NULL,
  `second_name` VARCHAR(12) NULL,
  `phone` VARCHAR(255) NULL,
  `id_role` INT(2) UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `admins_id_power_powers_id_idx` (`id_role` ASC),
  CONSTRAINT `admins_id_role_roles_id`
    FOREIGN KEY (`id_role`)
    REFERENCES `maf`.`roles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`organizers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`organizers` ;

CREATE TABLE IF NOT EXISTS `maf`.`organizers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `mail` VARCHAR(255) NULL,
  `name` VARCHAR(255) NULL,
  `second_name` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  `address` VARCHAR(255) NULL,
  `status` VARCHAR(45) NULL,
  `img` VARCHAR(255) NULL,
  `city` VARCHAR(255) NULL,
  `id_role` INT UNSIGNED NOT NULL,
  `password` VARCHAR(255) NULL,
  `department` INT(2) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`news`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`news` ;

CREATE TABLE IF NOT EXISTS `maf`.`news` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `content` TEXT NULL,
  `title` VARCHAR(255) NULL,
  `exerpt` VARCHAR(255) NULL,
  `img` VARCHAR(255) NULL,
  `published_at` DATE NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`mafs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`mafs` ;

CREATE TABLE IF NOT EXISTS `maf`.`mafs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `second_name` VARCHAR(255) NULL,
  `job` VARCHAR(255) NULL,
  `department` INT(2) NULL,
  `medal` VARCHAR(255) NULL,
  `year` DATE NULL,
  `adress` VARCHAR(255) NULL,
  `mail` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  `img` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`staffs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`staffs` ;

CREATE TABLE IF NOT EXISTS `maf`.`staffs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `second_name` VARCHAR(255) NULL,
  `img` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`partners`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`partners` ;

CREATE TABLE IF NOT EXISTS `maf`.`partners` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `img` VARCHAR(255) NULL,
  `name` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`documents`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`documents` ;

CREATE TABLE IF NOT EXISTS `maf`.`documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `url` VARCHAR(255) NULL,
  `type` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`jobs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`jobs` ;

CREATE TABLE IF NOT EXISTS `maf`.`jobs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `img` VARCHAR(255) NULL,
  `id_document` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `job_id_document_documents_id_idx` (`id_document` ASC),
  CONSTRAINT `job_id_document_documents_id`
    FOREIGN KEY (`id_document`)
    REFERENCES `maf`.`documents` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`contracts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`contracts` ;

CREATE TABLE IF NOT EXISTS `maf`.`contracts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `mail` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  `employer` VARCHAR(255) NULL,
  `address` VARCHAR(255) NULL,
  `city` VARCHAR(255) NULL,
  `postal_code` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`etablishments`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`etablishments` ;

CREATE TABLE IF NOT EXISTS `maf`.`etablishments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `address` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  `mail` VARCHAR(255) NULL,
  `city` VARCHAR(255) NULL,
  `former` VARCHAR(255) NULL,
  `name` VARCHAR(255) NULL,
  `postal_code` VARCHAR(255) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`candidates`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`candidates` ;

CREATE TABLE IF NOT EXISTS `maf`.`candidates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `second_name` VARCHAR(255) NULL,
  `born_date` DATE NULL,
  `mail` VARCHAR(255) NULL,
  `department` INT(2) NULL,
  `city` VARCHAR(255) NULL,
  `status` VARCHAR(255) NULL,
  `job` VARCHAR(255) NULL,
  `identity_card` VARCHAR(255) NULL,
  `id_etablishment` INT UNSIGNED NULL,
  `id_contract` INT UNSIGNED NULL,
  `school_certificate` VARCHAR(255) NULL,
  `address` VARCHAR(255) NULL,
  `postal_code` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  INDEX `subscribers_id_contract_contracts_id_idx` (`id_contract` ASC),
  INDEX `fk_subscribers_etablissements1_idx` (`id_etablishment` ASC),
  CONSTRAINT `subscribers_id_contract_contracts_id`
    FOREIGN KEY (`id_contract`)
    REFERENCES `maf`.`contracts` (`id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION,
  CONSTRAINT `subscribers_id_etablissement_etablissements_id`
    FOREIGN KEY (`id_etablishment`)
    REFERENCES `maf`.`etablishments` (`id`)
    ON DELETE SET NULL
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `maf`.`departments`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `maf`.`departments` ;

CREATE TABLE IF NOT EXISTS `maf`.`departments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_organizer` INT UNSIGNED NULL,
  `departement_number` INT(2) UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `departement_id_organizer_organizers_id_idx` (`id_organizer` ASC),
  CONSTRAINT `departement_id_organizer_organizers_id`
    FOREIGN KEY (`id_organizer`)
    REFERENCES `maf`.`organizers` (`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;
