-- MySQL Script generated by MySQL Workbench
-- Sun Jul  9 18:20:19 2023
-- 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 recruitment_analytics
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `recruitment_analytics`.`application_state`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`application_state` (
  `application_state_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`application_state_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`origin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`origin` (
  `origin_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `iso_3166_2_code` VARCHAR(6) NOT NULL,
  PRIMARY KEY (`origin_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`candidate`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`candidate` (
  `candidate_id` VARCHAR(36) NOT NULL,
  `origin_id` VARCHAR(36) NOT NULL,
  `first_name` VARCHAR(200) NOT NULL,
  `last_name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`candidate_id`),
  INDEX `fk_candidate_origin1_idx` (`origin_id` ASC) VISIBLE,
  CONSTRAINT `fk_candidate_origin1`
    FOREIGN KEY (`origin_id`)
    REFERENCES `recruitment_analytics`.`origin` (`origin_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`location` (
  `location_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`location_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`employee` (
  `employee_id` VARCHAR(36) NOT NULL,
  `location_id` VARCHAR(36) NOT NULL,
  `abbreviation` VARCHAR(5) NOT NULL,
  `first_name` VARCHAR(200) NOT NULL,
  `last_name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`employee_id`),
  INDEX `fk_employee_location1_idx` (`location_id` ASC) VISIBLE,
  CONSTRAINT `fk_employee_location1`
    FOREIGN KEY (`location_id`)
    REFERENCES `recruitment_analytics`.`location` (`location_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`business_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`business_type` (
  `business_type_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`business_type_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`company`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`company` (
  `company_id` VARCHAR(36) NOT NULL,
  `business_type_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`company_id`),
  INDEX `fk_company_business_type1_idx` (`business_type_id` ASC) VISIBLE,
  CONSTRAINT `fk_company_business_type1`
    FOREIGN KEY (`business_type_id`)
    REFERENCES `recruitment_analytics`.`business_type` (`business_type_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`country`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`country` (
  `country_id` VARCHAR(36) NOT NULL,
  `iso_3166_1_code` VARCHAR(2) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`country_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`region`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`region` (
  `region_id` VARCHAR(36) NOT NULL,
  `country_id` VARCHAR(36) NOT NULL,
  `iso_3166_2_code` VARCHAR(6) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`region_id`),
  INDEX `fk_region_country1_idx` (`country_id` ASC) VISIBLE,
  CONSTRAINT `fk_region_country1`
    FOREIGN KEY (`country_id`)
    REFERENCES `recruitment_analytics`.`country` (`country_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`site`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`site` (
  `site_id` VARCHAR(36) NOT NULL,
  `company_id` VARCHAR(36) NOT NULL,
  `region_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`site_id`),
  INDEX `fk_site_company1_idx` (`company_id` ASC) VISIBLE,
  INDEX `fk_site_region1_idx` (`region_id` ASC) VISIBLE,
  CONSTRAINT `fk_site_company1`
    FOREIGN KEY (`company_id`)
    REFERENCES `recruitment_analytics`.`company` (`company_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_site_region1`
    FOREIGN KEY (`region_id`)
    REFERENCES `recruitment_analytics`.`region` (`region_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`job_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`job_group` (
  `job_group_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`job_group_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`source`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`source` (
  `source_id` VARCHAR(36) NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`source_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`education`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`education` (
  `education_id` VARCHAR(36) NOT NULL,
  `required_education_name` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`education_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`advertisement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`advertisement` (
  `advertisement_id` VARCHAR(36) NOT NULL,
  `job_group_id` VARCHAR(36) NOT NULL,
  `source_id` VARCHAR(36) NOT NULL,
  `education_id` VARCHAR(36) NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `creation_date` DATETIME NOT NULL,
  PRIMARY KEY (`advertisement_id`),
  INDEX `fk_advertisement_job_group1_idx` (`job_group_id` ASC) VISIBLE,
  INDEX `fk_advertisement_source1_idx` (`source_id` ASC) VISIBLE,
  INDEX `fk_advertisement_education1_idx` (`education_id` ASC) VISIBLE,
  CONSTRAINT `fk_advertisement_job_group1`
    FOREIGN KEY (`job_group_id`)
    REFERENCES `recruitment_analytics`.`job_group` (`job_group_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_advertisement_source1`
    FOREIGN KEY (`source_id`)
    REFERENCES `recruitment_analytics`.`source` (`source_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_advertisement_education1`
    FOREIGN KEY (`education_id`)
    REFERENCES `recruitment_analytics`.`education` (`education_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`salary`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`salary` (
  `salary_id` VARCHAR(36) NOT NULL,
  `min` INT NOT NULL,
  `max` INT NOT NULL,
  PRIMARY KEY (`salary_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`employment_fraction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`employment_fraction` (
  `employment_fraction_id` VARCHAR(36) NOT NULL,
  `min` INT NOT NULL,
  `max` INT NOT NULL,
  PRIMARY KEY (`employment_fraction_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`work_from_home_fraction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`work_from_home_fraction` (
  `work_from_home_fraction_id` VARCHAR(36) NOT NULL,
  `min` INT NOT NULL,
  `max` INT NOT NULL,
  PRIMARY KEY (`work_from_home_fraction_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`vacancy`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`vacancy` (
  `vacancy_id` VARCHAR(36) NOT NULL,
  `advertisement_id` VARCHAR(36) NOT NULL,
  `salary_salary_id` VARCHAR(36) NOT NULL,
  `employment_fraction_id` VARCHAR(36) NOT NULL,
  `work_from_home_fraction_id` VARCHAR(36) NOT NULL,
  PRIMARY KEY (`vacancy_id`),
  INDEX `fk_vacancy_advertisement1_idx` (`advertisement_id` ASC) VISIBLE,
  INDEX `fk_vacancy_salary1_idx` (`salary_salary_id` ASC) VISIBLE,
  INDEX `fk_vacancy_employment_fraction1_idx` (`employment_fraction_id` ASC) VISIBLE,
  INDEX `fk_vacancy_work_from_home_fraction1_idx` (`work_from_home_fraction_id` ASC) VISIBLE,
  CONSTRAINT `fk_vacancy_advertisement1`
    FOREIGN KEY (`advertisement_id`)
    REFERENCES `recruitment_analytics`.`advertisement` (`advertisement_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_vacancy_salary1`
    FOREIGN KEY (`salary_salary_id`)
    REFERENCES `recruitment_analytics`.`salary` (`salary_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_vacancy_employment_fraction1`
    FOREIGN KEY (`employment_fraction_id`)
    REFERENCES `recruitment_analytics`.`employment_fraction` (`employment_fraction_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_vacancy_work_from_home_fraction1`
    FOREIGN KEY (`work_from_home_fraction_id`)
    REFERENCES `recruitment_analytics`.`work_from_home_fraction` (`work_from_home_fraction_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `recruitment_analytics`.`application`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `recruitment_analytics`.`application` (
  `application_id` VARCHAR(36) NOT NULL,
  `application_state_id` VARCHAR(36) NOT NULL,
  `candidate_id` VARCHAR(36) NOT NULL,
  `employee_id` VARCHAR(36) NOT NULL,
  `site_id` VARCHAR(36) NOT NULL,
  `vacancy_id` VARCHAR(36) NOT NULL,
  PRIMARY KEY (`application_id`),
  INDEX `fk_application_application_state_idx` (`application_state_id` ASC) VISIBLE,
  INDEX `fk_application_candidate1_idx` (`candidate_id` ASC) VISIBLE,
  INDEX `fk_application_employee1_idx` (`employee_id` ASC) VISIBLE,
  INDEX `fk_application_site1_idx` (`site_id` ASC) VISIBLE,
  INDEX `fk_application_vacancy1_idx` (`vacancy_id` ASC) VISIBLE,
  CONSTRAINT `fk_application_application_state`
    FOREIGN KEY (`application_state_id`)
    REFERENCES `recruitment_analytics`.`application_state` (`application_state_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_application_candidate1`
    FOREIGN KEY (`candidate_id`)
    REFERENCES `recruitment_analytics`.`candidate` (`candidate_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_application_employee1`
    FOREIGN KEY (`employee_id`)
    REFERENCES `recruitment_analytics`.`employee` (`employee_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_application_site1`
    FOREIGN KEY (`site_id`)
    REFERENCES `recruitment_analytics`.`site` (`site_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_application_vacancy1`
    FOREIGN KEY (`vacancy_id`)
    REFERENCES `recruitment_analytics`.`vacancy` (`vacancy_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;
