-- MySQL Script generated by MySQL Workbench
-- Tue Mar 18 17:50:13 2025
-- 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 mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`_CatPlans`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`_CatPlans` (
  `plan_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `limit_roles` INT NOT NULL DEFAULT 1,
  `status` TINYINT NOT NULL DEFAULT 0,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`plan_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Companies`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Companies` (
  `company_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `legal_name` VARCHAR(150) NOT NULL COMMENT 'Razón social completa',
  `trade_name` VARCHAR(150) NOT NULL COMMENT 'nombre comercial',
  `mail` VARCHAR(254) NOT NULL,
  `street` VARCHAR(100) NOT NULL COMMENT 'calle',
  `zip_code` CHAR(5) NOT NULL COMMENT 'Código Postal México',
  `external_number` VARCHAR(10) NOT NULL COMMENT 'numero exterior',
  `internal_number` VARCHAR(10) NULL DEFAULT NULL COMMENT 'numero interior',
  `neighborhood` VARCHAR(100) NOT NULL COMMENT 'colonia',
  `municipality` VARCHAR(100) NOT NULL,
  `state` ENUM('AGU', 'BCN', 'BCS', 'CAM', 'CHH', 'CHP', 'CMX', 'COA', 'COL', 'DUR', 'GRO', 'GUA', 'HID', 'JAL', 'MEX', 'MIC', 'MOR', 'NAY', 'NLE', 'OAX', 'PUE', 'QUE', 'ROO', 'SIN', 'SLP', 'SON', 'TAB', 'TAM', 'TLA', 'VER', 'YUC', 'ZAC') NOT NULL COMMENT 'Clave de entidad federativa',
  `country` VARCHAR(50) NOT NULL DEFAULT 'México',
  `web` VARCHAR(253) NULL COMMENT 'Dominio completo',
  `phone` VARCHAR(20) NOT NULL COMMENT 'Formato internacional',
  `tax_regime_code` CHAR(3) NOT NULL COMMENT 'Clave SAT del régimen fiscal',
  `logo` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Ruta relativa del archivo del logo',
  `incremental_vacation` TINYINT NOT NULL DEFAULT 0 COMMENT 'Vacaciones incrementales por antigüedad',
  `plan` INT UNSIGNED NOT NULL COMMENT 'Plan contratado',
  `is_active` TINYINT NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`company_id`),
  INDEX `plan_catplans_idx` (`plan` ASC) VISIBLE,
  CONSTRAINT `plan_catplans`
    FOREIGN KEY (`plan`)
    REFERENCES `mydb`.`_CatPlans` (`plan_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mydb`.`Roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Roles` (
  `role_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `folio_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE INDEX `rol_company_unique_index` (`role_id` ASC, `company_id` ASC) INVISIBLE,
  PRIMARY KEY (`role_id`),
  INDEX `roles_company_idx` (`company_id` ASC) VISIBLE,
  CONSTRAINT `roles_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Users` (
  `user_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `folio_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `first_name` VARCHAR(100) NOT NULL,
  `last_name` VARCHAR(100) NOT NULL COMMENT 'last name',
  `mail` VARCHAR(254) NOT NULL,
  `password` CHAR(100) NOT NULL COMMENT 'Hash bcrypt',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '0=Inactivo, 1=Activo',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE INDEX `usr_mail_UNIQUE` (`mail` ASC) VISIBLE,
  PRIMARY KEY (`user_id`),
  INDEX `user_company_idx` (`company_id` ASC) VISIBLE,
  INDEX `user_roles_idx` (`role_id` ASC) VISIBLE,
  CONSTRAINT `user_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `user_roles`
    FOREIGN KEY (`role_id`)
    REFERENCES `mydb`.`Roles` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mydb`.`EmploymentData`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`EmploymentData` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `department_id` BIGINT UNSIGNED NOT NULL COMMENT 'departamento',
  `position_id` BIGINT UNSIGNED NOT NULL COMMENT 'puesto de trabajo',
  `hire_date` DATE NOT NULL DEFAULT CURRENT_DATE COMMENT 'fecha de ingreso',
  `start_date` DATE NULL DEFAULT NULL COMMENT 'inicio de contrato',
  `end_date` DATE NULL DEFAULT NULL COMMENT 'Finalizacion de contrato\n',
  `annual_vacation` SMALLINT UNSIGNED NOT NULL COMMENT 'vacaciones anuales',
  `pay_frequency` ENUM('SEMANAL', 'CATORCENAL', 'QUINCENAL', 'MENSUAL') NOT NULL,
  `daily_salary` DECIMAL(10,2) UNSIGNED NOT NULL,
  `contract_type` VARCHAR(50) NULL COMMENT 'Texto libre, segun lo acordado',
  `company_email` VARCHAR(254) NULL,
  `company_phone` VARCHAR(20) NULL,
  `termination_date` DATE NULL COMMENT 'Fecha de baja',
  `termination_reason` VARCHAR(150) NULL COMMENT 'razón de baja',
  `termination_details` TEXT NULL COMMENT 'detalles de baja',
  `termination_notes` TEXT NULL COMMENT 'notas de baja',
  `rehire_eligible` TINYINT NULL DEFAULT 0 COMMENT 'recontratacion',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `employmentData_User_idx` (`user_id` ASC) VISIBLE,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) VISIBLE,
  CONSTRAINT `employmentData_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mydb`.`MedicalData`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`MedicalData` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `ssn` VARCHAR(11) NOT NULL COMMENT 'Numero de seguro social (11 digitos)',
  `emergency_contact` VARCHAR(100) NOT NULL,
  `emergency_phone` VARCHAR(45) NOT NULL,
  `blood_type` ENUM('A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-') NOT NULL COMMENT 'Tipo de sangre',
  `insurance_start_date` DATE NULL COMMENT 'Fecha de alta en el imss',
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `medicalData_User_idx` (`user_id` ASC) VISIBLE,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) VISIBLE,
  CONSTRAINT `medicalData_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mydb`.`FinancialData`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`FinancialData` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `account_number` VARCHAR(20) NOT NULL COMMENT 'Numero de cuenta bancaria',
  `interbank_key` CHAR(18) NOT NULL COMMENT 'Clave interbancaria (18 digitos)',
  `bank_name` VARCHAR(50) NOT NULL COMMENT 'Nombre completo del banco',
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `finacialData_User_idx` (`user_id` ASC) VISIBLE,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) VISIBLE,
  CONSTRAINT `finacialData_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mydb`.`PersonalData`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PersonalData` (
  `user_id` BIGINT UNSIGNED NOT NULL,
  `gender` ENUM('Hombre', 'Mujer', 'Prefiero no decirlo') NOT NULL DEFAULT 'Prefiero no decirlo',
  `phone` VARCHAR(20) NOT NULL COMMENT 'formato internacional',
  `birth_date` DATE NOT NULL,
  `nationality` VARCHAR(50) NULL DEFAULT 'Mexicana',
  `education_level` ENUM('Primary', 'Secondary', 'High School', 'Bachelor', 'Master', 'PhD') NOT NULL COMMENT 'Campo de estudio',
  `field_of_study` VARCHAR(100) NULL DEFAULT NULL,
  `street` VARCHAR(100) NOT NULL,
  `external_number` VARCHAR(10) NOT NULL,
  `internal_number` VARCHAR(10) NULL DEFAULT NULL,
  `zip_code` CHAR(5) NOT NULL COMMENT 'Codigo postal en mexico',
  `neighborhood` VARCHAR(100) NOT NULL COMMENT 'colonia',
  `state` VARCHAR(50) NOT NULL,
  `country` VARCHAR(50) NOT NULL DEFAULT 'Mexico',
  `municipality` VARCHAR(50) NOT NULL,
  `curp` VARCHAR(18) NOT NULL COMMENT ' Clave Única de Registro de Población',
  `rfc` VARCHAR(13) NOT NULL COMMENT 'Registro Federal de Contribuyentes',
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `personalData_User_idx` (`user_id` ASC) VISIBLE,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) VISIBLE,
  CONSTRAINT `personalData_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `mydb`.`_CatPermissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`_CatPermissions` (
  `permission_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT NULL,
  `icono` VARCHAR(45) NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`permission_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`RolePermissions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`RolePermissions` (
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission` INT UNSIGNED NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL,
  UNIQUE INDEX `role_permission_unique_index` (`role_id` ASC, `permission` ASC) VISIBLE,
  INDEX `rolPermissions_permissions_idx` (`permission` ASC) INVISIBLE,
  CONSTRAINT `rolePermissions_roles`
    FOREIGN KEY (`role_id`)
    REFERENCES `mydb`.`Roles` (`role_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `rolPermissions_permissions`
    FOREIGN KEY (`permission`)
    REFERENCES `mydb`.`_CatPermissions` (`permission_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Corporations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Corporations` (
  `corporation_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`corporation_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CorporationsCompanies`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`CorporationsCompanies` (
  `corporation_id` INT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `CorporationsCompany_Company_idx` (`company_id` ASC) INVISIBLE,
  INDEX `CorporationsCompany_Corporation_idx` (`corporation_id` ASC) VISIBLE,
  UNIQUE INDEX `CorporationCompany_unique_index` (`corporation_id` ASC, `company_id` ASC) VISIBLE,
  CONSTRAINT `CorporationsCompany_Company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `CorporationsCompany_Corporation`
    FOREIGN KEY (`corporation_id`)
    REFERENCES `mydb`.`Corporations` (`corporation_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`_CatTables`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`_CatTables` (
  `table_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`table_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`UserHistory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`UserHistory` (
  `log_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `description` TEXT NOT NULL,
  `relationship` INT UNSIGNED NOT NULL DEFAULT 0,
  `id_identify` BIGINT NULL DEFAULT NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`log_id`),
  INDEX `history_user_idx` (`user_id` ASC) VISIBLE,
  INDEX `history_company_idx` (`company_id` ASC) VISIBLE,
  INDEX `history_tables_idx` (`relationship` ASC) VISIBLE,
  CONSTRAINT `history_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `history_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `history_tables`
    FOREIGN KEY (`relationship`)
    REFERENCES `mydb`.`_CatTables` (`table_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Releases`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Releases` (
  `release.id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `content` TEXT NOT NULL,
  `date_start` DATE NULL,
  `date_end` DATE NULL,
  `user_create` BIGINT UNSIGNED NOT NULL,
  `user_update` BIGINT UNSIGNED NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`release.id`),
  INDEX `release_company_idx` (`company_id` ASC) VISIBLE,
  INDEX `release_user_create_idx` (`user_create` ASC) VISIBLE,
  INDEX `release_user_update_idx` (`user_update` ASC) VISIBLE,
  CONSTRAINT `release_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `release_user_create`
    FOREIGN KEY (`user_create`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `release_user_update`
    FOREIGN KEY (`user_update`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ReleasesImg`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ReleasesImg` (
  `img_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `path` VARCHAR(100) NOT NULL,
  `release` BIGINT UNSIGNED NOT NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`img_id`),
  INDEX `img_release_idx` (`release` ASC) VISIBLE,
  CONSTRAINT `img_release`
    FOREIGN KEY (`release`)
    REFERENCES `mydb`.`Releases` (`release.id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`ReleasesDoc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ReleasesDoc` (
  `doc_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `path` VARCHAR(100) NOT NULL,
  `release` BIGINT UNSIGNED NOT NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`doc_id`),
  INDEX `doc_releases_idx` (`release` ASC) VISIBLE,
  CONSTRAINT `doc_releases`
    FOREIGN KEY (`release`)
    REFERENCES `mydb`.`Releases` (`release.id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CatAbsences`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`CatAbsences` (
  `absence_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `name` TINYINT UNSIGNED NOT NULL,
  `justification` TINYINT UNSIGNED NOT NULL COMMENT 'requiere justificante?',
  `check` TINYINT UNSIGNED NOT NULL COMMENT 'requiere aprovación?',
  `working` TINYINT UNSIGNED NOT NULL COMMENT 'es laborable?',
  `format` TINYINT UNSIGNED NOT NULL COMMENT 'genera formato propio?',
  `paid` TINYINT UNSIGNED NOT NULL COMMENT 'se le paga sueldo?',
  `status` TINYINT UNSIGNED NOT NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`absence_id`),
  INDEX `CatAbsencess_company_idx` (`company_id` ASC) VISIBLE,
  CONSTRAINT `CatAbsencess_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Absences`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Absences` (
  `absence_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `date_start` DATETIME NOT NULL,
  `date_end` DATETIME NOT NULL,
  `motive` VARCHAR(150) NULL,
  `type` BIGINT UNSIGNED NOT NULL COMMENT 'tipo de ausencia',
  `justification_path` VARCHAR(150) NULL DEFAULT NULL COMMENT 'dirección relativa del documento\n',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = Espera, 1 = Aprobada, 2 = Rechazada, 3 = Devuelta (falta de datos)',
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`absence_id`),
  INDEX `absences_user_idx` (`user_id` ASC) VISIBLE,
  INDEX `type_absences_idx` (`type` ASC) VISIBLE,
  CONSTRAINT `absences_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `type_absences`
    FOREIGN KEY (`type`)
    REFERENCES `mydb`.`CatAbsences` (`absence_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Vacations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Vacations` (
  `vacation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `days` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `absence_id` BIGINT UNSIGNED NOT NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`vacation_id`),
  INDEX `Vacations_Absences_idx` (`absence_id` ASC) VISIBLE,
  INDEX `Vacations_User_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `Vacations_Absences`
    FOREIGN KEY (`absence_id`)
    REFERENCES `mydb`.`Absences` (`absence_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `Vacations_User`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`Users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CatBranches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`CatBranches` (
  `branch_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `street` VARCHAR(100) NOT NULL,
  `zip_code` CHAR(5) NOT NULL,
  `external_number` VARCHAR(10) NOT NULL,
  `internal_number` VARCHAR(10) NULL,
  `neighborhood` VARCHAR(100) NOT NULL,
  `municipality` VARCHAR(100) NOT NULL,
  `state` ENUM('AGU', 'BCN', 'BCS', 'CAM', 'CHH', 'CHP', 'CMX', 'COA', 'COL', 'DUR', 'GRO', 'GUA', 'HID', 'JAL', 'MEX', 'MIC', 'MOR', 'NAY', 'NLE', 'OAX', 'PUE', 'QUE', 'ROO', 'SIN', 'SLP', 'SON', 'TAB', 'TAM', 'TLA', 'VER', 'YUC', 'ZAC') NOT NULL,
  `country` VARCHAR(50) NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`branch_id`),
  INDEX `Branche_Company_idx` (`company_id` ASC) VISIBLE,
  CONSTRAINT `Branche_Company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CatDepartments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`CatDepartments` (
  `department_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `branch_id` BIGINT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `status` TINYINT NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`department_id`),
  INDEX `catDepartamentos_company_idx` (`company_id` ASC) VISIBLE,
  INDEX `catDepartaments_Branches_idx` (`branch_id` ASC) VISIBLE,
  CONSTRAINT `catDepartamentos_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `catDepartaments_Branches`
    FOREIGN KEY (`branch_id`)
    REFERENCES `mydb`.`CatBranches` (`branch_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`_CatAbsences`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`_CatAbsences` (
  `absence_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` TINYINT UNSIGNED NOT NULL,
  `justification` TINYINT UNSIGNED NOT NULL COMMENT 'requiere justificante?',
  `check` TINYINT UNSIGNED NOT NULL COMMENT 'requiere aprovación?',
  `working` TINYINT UNSIGNED NOT NULL COMMENT 'es laborable?',
  `format` TINYINT UNSIGNED NOT NULL COMMENT 'genera formato propio?',
  `paid` TINYINT UNSIGNED NOT NULL COMMENT 'se le paga sueldo?',
  `status` TINYINT UNSIGNED NOT NULL,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`absence_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Workstations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Workstations` (
  `workstation_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `departament_id` BIGINT UNSIGNED NOT NULL,
  `company_id` INT UNSIGNED NOT NULL,
  `boss` BIGINT UNSIGNED NOT NULL,
  `depends` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`workstation_id`),
  INDEX `Workstations_Departament_idx` (`departament_id` ASC) VISIBLE,
  INDEX `Workstations_Company_idx` (`company_id` ASC) VISIBLE,
  INDEX `Workstations_depends_idx` (`depends` ASC) VISIBLE,
  CONSTRAINT `Workstations_Departament`
    FOREIGN KEY (`departament_id`)
    REFERENCES `mydb`.`CatDepartments` (`department_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `Workstations_Company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `Workstations_depends`
    FOREIGN KEY (`depends`)
    REFERENCES `mydb`.`Workstations` (`workstation_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`_CatDocuments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`_CatDocuments` (
  `document_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `validity` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'documento con vigencia?',
  `portfolio` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`document_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CatDocuments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`CatDocuments` (
  `document_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `validity` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `portfolio` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `create_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`document_id`),
  INDEX `catDocuments_company_idx` (`company_id` ASC) VISIBLE,
  CONSTRAINT `catDocuments_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `mydb`.`Companies` (`company_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`DocumentsWorkstations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`DocumentsWorkstations` (
  `doc_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` BIGINT UNSIGNED NOT NULL,
  `workstation` BIGINT UNSIGNED NOT NULL,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`doc_id`),
  INDEX `DocumentsWorks_Workstations_idx` (`workstation` ASC) VISIBLE,
  INDEX `DocumentsWorks_Documents_idx` (`type` ASC) VISIBLE,
  CONSTRAINT `DocumentsWorks_Workstations`
    FOREIGN KEY (`workstation`)
    REFERENCES `mydb`.`Workstations` (`workstation_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `DocumentsWorks_Documents`
    FOREIGN KEY (`type`)
    REFERENCES `mydb`.`CatDocuments` (`document_id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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