-- MySQL Script generated by MySQL Workbench
-- Thu Dec  2 15:47:16 2021
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema nutrack
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `nutrack` ;

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

-- -----------------------------------------------------
-- Table `nutrack`.`corrales`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`corrales` (
  `c_id` VARCHAR(10) NOT NULL,
  `c_uuid` VARCHAR(36) NOT NULL,
  `c_desc` VARCHAR(50) NULL,
  `c_cabezas` INT NULL,
  `c_sec` INT NULL COMMENT 'Secuencia de Lectura',
  `c_estadia` DATETIME NULL,
  `c_lat_i` DECIMAL(9,7) NULL,
  `c_long_i` DECIMAL(10,7) NULL,
  `c_lat_f` DECIMAL(9,7) NULL,
  `c_long_f` DECIMAL(10,7) NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE INDEX `c_uuid_UNIQUE` (`c_uuid` ASC),
  PRIMARY KEY (`c_id`))
ENGINE = InnoDB
COMMENT = 'Producto/Lugar (ej Producto=MAIZ / Lugar=CORRAL 14)';


-- -----------------------------------------------------
-- Table `nutrack`.`formulas`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`formulas` (
  `f_id` INT NOT NULL,
  `f_uuid` VARCHAR(36) NOT NULL,
  `f_desc` VARCHAR(50) NULL,
  `f_mezclado` INT NULL,
  `f_kg_x_m3` INT NULL,
  `version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`f_id`),
  UNIQUE INDEX `f_uuid_UNIQUE` (`f_uuid` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`implementos`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`implementos` (
  `imp_id` INT NOT NULL AUTO_INCREMENT,
  `imp_uuid` VARCHAR(36) NOT NULL,
  `imp_desc` VARCHAR(50) NOT NULL,
  `imp_tipo` ENUM('M', 'R', 'MR', 'P', 'PL') NOT NULL,
  `imp_capacidad` INT NULL,
  `imp_wf_mac` VARCHAR(17) NULL,
  `imp_wf_ip` VARCHAR(17) NULL DEFAULT NULL,
  `imp_bt` VARCHAR(17) NULL,
  `imp_bt_bza` VARCHAR(17) NULL,
  `imp_start_formula` TINYINT(1) NOT NULL DEFAULT 0,
  `imp_fw_ingrediente` TINYINT(1) NOT NULL DEFAULT 0,
  `imp_do_ronda` TINYINT(1) NOT NULL DEFAULT 0,
  `imp_connect_server` TINYINT(1) NOT NULL DEFAULT 0,
  `imp_is_bt_server` TINYINT(1) NOT NULL DEFAULT 0,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`imp_id`),
  UNIQUE INDEX `imp_uuid_UNIQUE` (`imp_uuid` ASC))
ENGINE = InnoDB
PACK_KEYS = 0;


-- -----------------------------------------------------
-- Table `nutrack`.`prescripciones`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`prescripciones` (
  `pr_id` VARCHAR(30) NOT NULL,
  `pr_uuid` VARCHAR(36) NOT NULL,
  `pr_descripcion` VARCHAR(50) NULL,
  `campo_id_fk` VARCHAR(10) NULL,
  `sec_id_fk` VARCHAR(10) NULL,
  `f_id_fk` INT NULL,
  `imp_id_f_fk` INT NULL,
  `imp_id_r_fk` INT NULL,
  `pr_total` FLOAT NULL,
  `pr_fecha` DATETIME NOT NULL,
  `pr_turno` TINYINT(1) NOT NULL,
  `pr_estado` TINYINT(1) NOT NULL DEFAULT 0,
  `pr_bloqueada` TINYINT(1) NOT NULL DEFAULT 0,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`pr_id`),
  INDEX `fk_prescripciones_formulas1_idx` (`f_id_fk` ASC),
  INDEX `fk_prescripciones_implementos1_idx` (`imp_id_f_fk` ASC),
  INDEX `fk_prescripciones_implementos2_idx` (`imp_id_r_fk` ASC),
  CONSTRAINT `fk_prescripciones_formulas1`
    FOREIGN KEY (`f_id_fk`)
    REFERENCES `nutrack`.`formulas` (`f_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_prescripciones_implementos1`
    FOREIGN KEY (`imp_id_f_fk`)
    REFERENCES `nutrack`.`implementos` (`imp_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_prescripciones_implementos2`
    FOREIGN KEY (`imp_id_r_fk`)
    REFERENCES `nutrack`.`implementos` (`imp_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`ots`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`ots` (
  `ot_id` VARCHAR(30) NOT NULL,
  `ot_uuid` VARCHAR(36) NOT NULL,
  `campo_id_fk` VARCHAR(10) NULL,
  `sec_id_fk` VARCHAR(10) NULL,
  `pr_id_fk` VARCHAR(30) NULL,
  `f_id_fk` INT NULL,
  `imp_id_f_fk` INT NULL,
  `imp_id_r_fk` INT NULL,
  `ot_type` INT NOT NULL DEFAULT 0,
  `ot_total` FLOAT NOT NULL,
  `ot_nombre` VARCHAR(50) NULL,
  `ot_mezclado` INT NULL,
  `ot_fecha` DATETIME NULL,
  `ot_turno` TINYINT(1) NULL,
  `ot_estado` TINYINT(1) NOT NULL DEFAULT 0,
  `ot_bloqueada` TINYINT(1) NOT NULL DEFAULT 0,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`ot_id`),
  UNIQUE INDEX `ot_uuid_UNIQUE` (`ot_uuid` ASC),
  INDEX `fk_ots_formulas1_idx` (`f_id_fk` ASC),
  INDEX `fk_ots_prescripciones1_idx` (`pr_id_fk` ASC),
  INDEX `fk_ots_imp2` (`imp_id_r_fk` ASC),
  INDEX `fk_ots_imp1` (`imp_id_f_fk` ASC),
  CONSTRAINT `fk_ots_formulas1`
    FOREIGN KEY (`f_id_fk`)
    REFERENCES `nutrack`.`formulas` (`f_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ots_prescripciones1`
    FOREIGN KEY (`pr_id_fk`)
    REFERENCES `nutrack`.`prescripciones` (`pr_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ots_imp2`
    FOREIGN KEY (`imp_id_r_fk`)
    REFERENCES `nutrack`.`implementos` (`imp_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ots_imp1`
    FOREIGN KEY (`imp_id_f_fk`)
    REFERENCES `nutrack`.`implementos` (`imp_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Orden de Trabajo';


-- -----------------------------------------------------
-- Table `nutrack`.`insumos`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`insumos` (
  `ins_id` INT NOT NULL,
  `ins_uuid` VARCHAR(36) NOT NULL,
  `ins_desc` VARCHAR(50) NOT NULL,
  `ins_mseca` FLOAT NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ins_id`),
  UNIQUE INDEX `ins_id_UNIQUE` (`ins_uuid` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`ot_det_formula`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`ot_det_formula` (
  `otdf_uuid` VARCHAR(36) NOT NULL,
  `ot_id_fk` VARCHAR(30) NOT NULL,
  `ins_id_fk` INT NOT NULL,
  `u_id_fk` INT NULL,
  `imp_id_fk` INT NULL,
  `otdf_cant` FLOAT NOT NULL,
  `otdf_orden` INT NULL,
  `otdf_estado` TINYINT(1) NOT NULL DEFAULT 0,
  `otdf_dosificado` FLOAT NULL,
  `otdf_fecha` DATETIME NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`otdf_uuid`),
  INDEX `fk_ot_det_formula_insumos1_idx` (`ins_id_fk` ASC),
  INDEX `fk_ot_det_formula_usuarios1_idx` (`u_id_fk` ASC),
  INDEX `fk_ot_det_formula_implementos1_idx` (`imp_id_fk` ASC),
  INDEX `fk_ot_det_formula_ots1_idx` (`ot_id_fk` ASC),
  CONSTRAINT `fk_ot_det_formula_insumos1`
    FOREIGN KEY (`ins_id_fk`)
    REFERENCES `nutrack`.`insumos` (`ins_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_formula_usuarios1`
    FOREIGN KEY (`u_id_fk`)
    REFERENCES `nutrack`.`usuarios` (`u_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_formula_implementos1`
    FOREIGN KEY (`imp_id_fk`)
    REFERENCES `nutrack`.`implementos` (`imp_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_formula_ots1`
    FOREIGN KEY (`ot_id_fk`)
    REFERENCES `nutrack`.`ots` (`ot_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'TAREA a realizar/realizada';


-- -----------------------------------------------------
-- Table `nutrack`.`borrados`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`borrados` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(36) NOT NULL,
  `tabla` VARCHAR(20) NOT NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`formulas_det`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`formulas_det` (
  `fd_uuid` VARCHAR(36) NOT NULL,
  `f_id_fk` INT NOT NULL,
  `ins_id_fk` INT NOT NULL,
  `fd_orden` TINYINT(1) NOT NULL,
  `fd_cant` FLOAT NOT NULL DEFAULT 0,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`fd_uuid`),
  INDEX `fk_formulas_det_insumos1_idx` (`ins_id_fk` ASC),
  INDEX `fk_formulas_det_formulas1_idx` (`f_id_fk` ASC),
  CONSTRAINT `fk_formulas_det_insumos1`
    FOREIGN KEY (`ins_id_fk`)
    REFERENCES `nutrack`.`insumos` (`ins_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_formulas_det_formulas1`
    FOREIGN KEY (`f_id_fk`)
    REFERENCES `nutrack`.`formulas` (`f_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`com_estados`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`com_estados` (
  `ce_id` VARCHAR(5) NOT NULL,
  `ce_uuid` VARCHAR(36) NOT NULL,
  `ce_desc` VARCHAR(50) NOT NULL,
  `ce_nivel_urg` INT NOT NULL,
  `ce_coef_var` FLOAT NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ce_id`),
  UNIQUE INDEX `lc_id_UNIQUE` (`ce_uuid` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`prescripciones_det`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`prescripciones_det` (
  `prd_id` INT(11) NOT NULL AUTO_INCREMENT,
  `prd_uuid` VARCHAR(36) NOT NULL,
  `pr_id_fk` VARCHAR(30) NOT NULL,
  `c_id_fk` VARCHAR(10) NOT NULL,
  `prd_cant` FLOAT NOT NULL,
  `prd_orden` INT NULL,
  `prd_estado` TINYINT(1) NOT NULL DEFAULT 0,
  `prd_dosificado` FLOAT NULL,
  `prd_fecha` DATETIME NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`prd_id`),
  INDEX `fk_prescripciones_det_corrales1_idx` (`c_id_fk` ASC),
  INDEX `fk_prescripciones_det_prescripciones1_idx` (`pr_id_fk` ASC),
  UNIQUE INDEX `prd_uuid_UNIQUE` (`prd_uuid` ASC),
  CONSTRAINT `fk_prescripciones_det_corrales1`
    FOREIGN KEY (`c_id_fk`)
    REFERENCES `nutrack`.`corrales` (`c_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_prescripciones_det_prescripciones1`
    FOREIGN KEY (`pr_id_fk`)
    REFERENCES `nutrack`.`prescripciones` (`pr_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`ot_det_ronda`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`ot_det_ronda` (
  `otdr_uuid` VARCHAR(36) NOT NULL,
  `ot_id_fk` VARCHAR(30) NOT NULL,
  `prd_id_fk` INT(11) NULL,
  `c_id_fk` VARCHAR(10) NOT NULL,
  `u_id_fk` INT NULL,
  `imp_id_fk` INT NULL,
  `otdr_cant` FLOAT NOT NULL,
  `otdr_orden` INT NULL,
  `otdr_estado` TINYINT(1) NOT NULL DEFAULT 0,
  `otdr_dosificado` FLOAT NULL,
  `otdr_fecha` DATETIME NULL,
  `otdr_ubicacion` INT NULL,
  `otdr_lat_i` DECIMAL(9,7) NULL,
  `otdr_long_i` DECIMAL(10,7) NULL,
  `otdr_lat_f` DECIMAL(9,7) NULL,
  `otdr_long_f` DECIMAL(10,7) NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`otdr_uuid`),
  INDEX `fk_ot_det_ronda_corrales1_idx` (`c_id_fk` ASC),
  INDEX `fk_ot_det_ronda_usuarios1_idx` (`u_id_fk` ASC),
  INDEX `fk_ot_det_ronda_implementos1_idx` (`imp_id_fk` ASC),
  INDEX `fk_ot_det_ronda_ots1_idx` (`ot_id_fk` ASC),
  INDEX `fk_ot_det_ronda_prescripciones_det1_idx` (`prd_id_fk` ASC),
  CONSTRAINT `fk_ot_det_ronda_corrales1`
    FOREIGN KEY (`c_id_fk`)
    REFERENCES `nutrack`.`corrales` (`c_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_ronda_usuarios1`
    FOREIGN KEY (`u_id_fk`)
    REFERENCES `nutrack`.`usuarios` (`u_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_ronda_implementos1`
    FOREIGN KEY (`imp_id_fk`)
    REFERENCES `nutrack`.`implementos` (`imp_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_ronda_ots1`
    FOREIGN KEY (`ot_id_fk`)
    REFERENCES `nutrack`.`ots` (`ot_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ot_det_ronda_prescripciones_det1`
    FOREIGN KEY (`prd_id_fk`)
    REFERENCES `nutrack`.`prescripciones_det` (`prd_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'TAREA a realizar/realizada';


-- -----------------------------------------------------
-- Table `nutrack`.`com_acciones`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`com_acciones` (
  `ca_id` INT NOT NULL AUTO_INCREMENT,
  `ca_uuid` VARCHAR(36) NOT NULL,
  `ca_desc` VARCHAR(20) NOT NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ca_id`),
  UNIQUE INDEX `ca_uuid_UNIQUE` (`ca_uuid` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`com_lec`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`com_lec` (
  `cl_uuid` VARCHAR(36) NOT NULL,
  `c_id_fk` VARCHAR(10) NOT NULL,
  `ce_id_fk` VARCHAR(5) NOT NULL,
  `ca_id_fk` INT NULL,
  `cl_fecha` DATETIME NOT NULL,
  `cl_obs` VARCHAR(20) NULL,
  `version` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `fk_lec_com_corrales1_idx` (`c_id_fk` ASC),
  PRIMARY KEY (`cl_uuid`),
  INDEX `fk_com_lec_com_accion1_idx` (`ca_id_fk` ASC),
  CONSTRAINT `fk_lec_com_corrales1`
    FOREIGN KEY (`c_id_fk`)
    REFERENCES `nutrack`.`corrales` (`c_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_com_lec_com_accion1`
    FOREIGN KEY (`ca_id_fk`)
    REFERENCES `nutrack`.`com_acciones` (`ca_id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Producto/Lugar (ej Producto=MAIZ / Lugar=CORRAL 14)';


-- -----------------------------------------------------
-- Table `nutrack`.`aud_ot_det_formula`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`aud_ot_det_formula` (
  `aud_otdf_id` INT NOT NULL AUTO_INCREMENT,
  `ot_id_fk` VARCHAR(30) NULL,
  `ins_id_fk` INT NULL,
  `u_id_fk` INT NULL,
  `imp_id_fk` INT NULL,
  `otdf_cant` FLOAT NULL,
  `otdf_orden` INT NULL,
  `otdf_estado` TINYINT(1) NULL,
  `otdf_dosificado` FLOAT NULL,
  `otdf_fecha` DATETIME NULL,
  `aud_otdf_type` VARCHAR(45) NULL,
  `aud_otdf_version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`aud_otdf_id`))
ENGINE = InnoDB
COMMENT = 'TAREA a realizar/realizada';


-- -----------------------------------------------------
-- Table `nutrack`.`aud_ot_det_ronda`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`aud_ot_det_ronda` (
  `aud_otdf_id` INT NOT NULL AUTO_INCREMENT,
  `ot_id_fk` VARCHAR(30) NULL,
  `c_id_fk` VARCHAR(10) NULL,
  `u_id_fk` INT NULL,
  `imp_id_fk` INT NULL,
  `otdr_cant` FLOAT NULL,
  `otdr_orden` INT NULL,
  `otdr_estado` TINYINT(1) NULL,
  `otdr_dosificado` FLOAT NULL,
  `otdr_fecha` DATETIME NULL,
  `aud_otdr_type` VARCHAR(45) NULL,
  `aud_otdr_version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`aud_otdf_id`))
ENGINE = InnoDB
COMMENT = 'TAREA a realizar/realizada';


-- -----------------------------------------------------
-- Table `nutrack`.`aud_ots`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`aud_ots` (
  `aud_ot_id` INT NOT NULL AUTO_INCREMENT,
  `ot_id` VARCHAR(30) NULL,
  `campo_id_fk` VARCHAR(10) NULL,
  `sec_id_fk` VARCHAR(10) NULL,
  `pr_id_fk` VARCHAR(30) NULL,
  `f_id_fk` INT NULL,
  `imp_id_f_fk` INT NULL,
  `imp_id_r_fk` INT NULL,
  `ot_type` INT NULL,
  `ot_total` FLOAT NULL,
  `ot_nombre` VARCHAR(50) NULL,
  `ot_mezclado` INT NULL,
  `ot_fecha` DATETIME NULL,
  `ot_turno` TINYINT(1) NULL,
  `ot_estado` TINYINT(1) NULL,
  `ot_bloqueada` TINYINT(1) NULL,
  `aud_ot_type` VARCHAR(45) NULL,
  `aud_ot_version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`aud_ot_id`))
ENGINE = InnoDB
COMMENT = 'Orden de Trabajo';


-- -----------------------------------------------------
-- Table `nutrack`.`aud_prescripciones`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`aud_prescripciones` (
  `aud_pr_id` INT NOT NULL AUTO_INCREMENT,
  `pr_id` VARCHAR(30) NULL,
  `pr_descripcion` VARCHAR(50) NULL,
  `campo_id_fk` VARCHAR(10) NULL,
  `sec_id_fk` VARCHAR(10) NULL,
  `f_id_fk` INT NULL,
  `imp_id_f_fk` INT NULL,
  `imp_id_r_fk` INT NULL,
  `pr_total` FLOAT NULL,
  `pr_fecha` DATETIME NULL,
  `pr_turno` TINYINT(1) NULL,
  `pr_estado` TINYINT(1) NULL,
  `pr_bloqueada` TINYINT(1) NULL,
  `aud_pr_type` VARCHAR(45) NULL,
  `aud_pr_version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`aud_pr_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`sectores`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`sectores` (
  `sec_id` VARCHAR(10) NOT NULL,
  `sec_uuid` VARCHAR(36) NOT NULL,
  `sec_desc` VARCHAR(50) NULL,
  `version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sec_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`sectores_det`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`sectores_det` (
  `secd_uuid` VARCHAR(36) NOT NULL,
  `c_id_fk` VARCHAR(10) NOT NULL,
  `sec_id_fk` VARCHAR(10) NOT NULL,
  `secd_orden` INT NOT NULL,
  `version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`secd_uuid`),
  INDEX `fk_sectores_det_corrales1_idx` (`c_id_fk` ASC),
  INDEX `fk_sectores_det_sectores1_idx` (`sec_id_fk` ASC),
  CONSTRAINT `fk_sectores_det_corrales1`
    FOREIGN KEY (`c_id_fk`)
    REFERENCES `nutrack`.`corrales` (`c_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_sectores_det_sectores1`
    FOREIGN KEY (`sec_id_fk`)
    REFERENCES `nutrack`.`sectores` (`sec_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`aud_prescripciones_det`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`aud_prescripciones_det` (
  `prd_id` INT NOT NULL AUTO_INCREMENT,
  `pr_id_fk` VARCHAR(30) NULL,
  `c_id_fk` VARCHAR(10) NULL,
  `prd_cant` VARCHAR(45) NULL,
  `prd_orden` INT NULL,
  `prd_estado` TINYINT(1) NULL,
  `prd_dosificado` FLOAT NULL,
  `prd_fecha` DATETIME NULL,
  `aud_prd_type` VARCHAR(45) NULL,
  `aud_prd_version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `version_id` VARCHAR(20) NULL,
  PRIMARY KEY (`prd_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`campos`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`campos` (
  `campo_id` VARCHAR(10) NOT NULL,
  `campo_uuid` VARCHAR(36) NOT NULL,
  `campo_desc` TEXT NULL,
  `version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`campo_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nutrack`.`campos_det`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `nutrack`.`campos_det` (
  `cd_uuid` VARCHAR(36) NOT NULL,
  `sec_id_fk` VARCHAR(10) NOT NULL,
  `campo_id_fk` VARCHAR(10) NOT NULL,
  `version` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cd_uuid`),
  INDEX `fk_campo_det_sectores1_idx` (`sec_id_fk` ASC),
  INDEX `fk_campo_det_campos1_idx` (`campo_id_fk` ASC),
  CONSTRAINT `fk_campo_det_sectores1`
    FOREIGN KEY (`sec_id_fk`)
    REFERENCES `nutrack`.`sectores` (`sec_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_campo_det_campos1`
    FOREIGN KEY (`campo_id_fk`)
    REFERENCES `nutrack`.`campos` (`campo_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `nutrack`;

DELIMITER $$

USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`corrales_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`corrales_BEFORE_INSERT` BEFORE INSERT ON `corrales` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.c_uuid is null || trim(new.c_uuid) = '' then
		SET new.c_uuid = uuid();
	elseif length(new.c_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.c_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`corrales_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`corrales_AFTER_DELETE` AFTER DELETE ON `corrales` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.c_uuid,'corrales');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`formulas_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`formulas_BEFORE_INSERT` BEFORE INSERT ON `formulas` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.f_uuid is null || trim(new.f_uuid) = '' then
		SET new.f_uuid = uuid();
	elseif length(new.f_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.f_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`formulas_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`formulas_AFTER_DELETE` AFTER DELETE ON `formulas` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.f_uuid,'formulas');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`implementos_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`implementos_BEFORE_INSERT` BEFORE INSERT ON `implementos` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.imp_uuid is null || trim(new.imp_uuid) = '' then
		SET new.imp_uuid = uuid();
	elseif length(new.imp_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.imp_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`implementos_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`implementos_AFTER_DELETE` AFTER DELETE ON `implementos` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.imp_uuid,'implementos');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_BEFORE_INSERT` BEFORE INSERT ON `prescripciones` FOR EACH ROW
BEGIN
	declare msg varchar(128);
	if new.pr_uuid is null || trim(new.pr_uuid) = '' then
		SET new.pr_uuid = uuid();
	elseif length(new.pr_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.pr_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_AFTER_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_AFTER_INSERT` AFTER INSERT ON `prescripciones` FOR EACH ROW
BEGIN
	insert into aud_prescripciones(
			  pr_id, pr_descripcion, campo_id_fk, sec_id_fk, imp_id_f_fk, imp_id_r_fk, f_id_fk,
			  pr_total, pr_fecha, pr_turno, pr_estado, pr_bloqueada,
			  aud_pr_type, version_id
			) VALUES (
			  NEW.pr_id, NEW.pr_descripcion, NEW.campo_id_fk, NEW.sec_id_fk, NEW.imp_id_f_fk, NEW.imp_id_r_fk, NEW.f_id_fk, 
			  NEW.pr_total, NEW.pr_fecha, NEW.pr_turno, NEW.pr_estado, NEW.pr_bloqueada,
			  'INSERT', NEW.version_id
			);
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_AFTER_UPDATE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_AFTER_UPDATE` AFTER UPDATE ON `prescripciones` FOR EACH ROW
BEGIN
	insert into aud_prescripciones(
			  pr_id, pr_descripcion, campo_id_fk, sec_id_fk, imp_id_f_fk, imp_id_r_fk, f_id_fk, 
			  pr_total, pr_fecha, pr_turno, pr_estado, pr_bloqueada,
			  aud_pr_type, version_id
			) VALUES (
			  NEW.pr_id, NEW.pr_descripcion, NEW.campo_id_fk, NEW.sec_id_fk, NEW.imp_id_f_fk, NEW.imp_id_r_fk, NEW.f_id_fk, 
			  NEW.pr_total, NEW.pr_fecha, NEW.pr_turno, NEW.pr_estado, NEW.pr_bloqueada,
			  'UPDATE', NEW.version_id
			);
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_AFTER_DELETE` AFTER DELETE ON `prescripciones` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.pr_uuid,'prescripciones');
    
    insert into aud_prescripciones(
			  pr_id, pr_descripcion, campo_id_fk, sec_id_fk, imp_id_f_fk, imp_id_r_fk, f_id_fk, 
			  pr_total, pr_fecha, pr_turno, pr_estado, pr_bloqueada,
			  aud_pr_type, version_id
			) VALUES (
			  old.pr_id, old.pr_descripcion, old.campo_id_fk, old.sec_id_fk, old.imp_id_f_fk, old.imp_id_r_fk, old.f_id_fk, 
			  old.pr_total, old.pr_fecha, old.pr_turno, old.pr_estado, old.pr_bloqueada,
			  'DELETE', old.version_id
			);
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ots_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ots_BEFORE_INSERT` BEFORE INSERT ON `ots` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.ot_uuid is null || trim(new.ot_uuid) = '' then
		SET new.ot_uuid = uuid();
	elseif length(new.ot_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.ot_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ots_AFTER_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ots_AFTER_INSERT` AFTER INSERT ON `ots` FOR EACH ROW
BEGIN
	insert into aud_ots(
                  ot_id, campo_id_fk, sec_id_fk, pr_id_fk, f_id_fk, imp_id_f_fk, imp_id_r_fk,
                  ot_type, ot_total, ot_nombre, ot_mezclado, 
                  ot_fecha, ot_turno, ot_estado, ot_bloqueada, 
                  aud_ot_type, version_id
                ) VALUES (
				  NEW.ot_id, NEW.campo_id_fk, NEW.sec_id_fk, NEW.pr_id_fk, NEW.f_id_fk, NEW.imp_id_f_fk, NEW.imp_id_r_fk,
                  NEW.ot_type, NEW.ot_total, NEW.ot_nombre, NEW.ot_mezclado,
                  NEW.ot_fecha, NEW.ot_turno, NEW.ot_estado, NEW.ot_bloqueada,
                  'INSERT' , NEW.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ots_AFTER_UPDATE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ots_AFTER_UPDATE` AFTER UPDATE ON `ots` FOR EACH ROW
BEGIN
	insert into aud_ots(
				  ot_id, campo_id_fk, sec_id_fk, pr_id_fk, f_id_fk, imp_id_f_fk, imp_id_r_fk, 
                  ot_type, ot_total, ot_nombre, ot_mezclado, 
                  ot_fecha, ot_turno, ot_estado, ot_bloqueada, 
                  aud_ot_type, version_id
                ) VALUES (
				  NEW.ot_id, NEW.campo_id_fk, NEW.sec_id_fk, NEW.pr_id_fk, NEW.f_id_fk, NEW.imp_id_f_fk, NEW.imp_id_r_fk,
                  NEW.ot_type, NEW.ot_total, NEW.ot_nombre, NEW.ot_mezclado,
                  NEW.ot_fecha, NEW.ot_turno, NEW.ot_estado, NEW.ot_bloqueada,
                  'UPDATE' , NEW.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ots_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ots_AFTER_DELETE` AFTER DELETE ON `ots` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.ot_uuid,'ots');
    
    insert into aud_ots(
				  ot_id, campo_id_fk, sec_id_fk, pr_id_fk, f_id_fk, imp_id_f_fk, imp_id_r_fk,
                  ot_type, ot_total, ot_nombre, ot_mezclado, 
                  ot_fecha, ot_turno, ot_estado, ot_bloqueada, 
                  aud_ot_type, version_id
                ) VALUES (
				  old.ot_id, old.campo_id_fk, old.sec_id_fk, old.pr_id_fk, old.f_id_fk, old.imp_id_f_fk, old.imp_id_r_fk,
                  old.ot_type, old.ot_total, old.ot_nombre, old.ot_mezclado,
                  old.ot_fecha, old.ot_turno, old.ot_estado, old.ot_bloqueada,
                  'DELETE' , old.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`usuarios_BEFORE_INSERT` $$
USE `nutrack`$$

	if new.u_uuid is null || trim(new.u_uuid) = '' then
		SET new.u_uuid = uuid();
	elseif length(new.u_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.u_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`usuarios_AFTER_DELETE` $$
USE `nutrack`$$
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`insumos_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`insumos_BEFORE_INSERT` BEFORE INSERT ON `insumos` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.ins_uuid is null || trim(new.ins_uuid) = '' then
		SET new.ins_uuid = uuid();
	elseif length(new.ins_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.ins_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`insumos_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`insumos_AFTER_DELETE` AFTER DELETE ON `insumos` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.ins_uuid,'insumos');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_formula_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_formula_BEFORE_INSERT` BEFORE INSERT ON `ot_det_formula` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if  new.otdf_uuid is null || trim(new.otdf_uuid) = '' then
		SET new.otdf_uuid = uuid();
	elseif length(new.otdf_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.otdf_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_formula_AFTER_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_formula_AFTER_INSERT` AFTER INSERT ON `ot_det_formula` FOR EACH ROW
BEGIN
	insert into aud_ot_det_formula(
                  ot_id_fk, ins_id_fk, u_id_fk, imp_id_fk, 
                  otdf_cant, otdf_orden, otdf_estado, otdf_dosificado, otdf_fecha, 
                  aud_otdf_type, version_id
                ) VALUES (
				  NEW.ot_id_fk, NEW.ins_id_fk, NEW.u_id_fk, NEW.imp_id_fk, 
                  NEW.otdf_cant, NEW.otdf_orden, NEW.otdf_estado, NEW.otdf_dosificado, NEW.otdf_fecha, 
                  'INSERT', NEW.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_formula_AFTER_UPDATE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_formula_AFTER_UPDATE` AFTER UPDATE ON `ot_det_formula` FOR EACH ROW
BEGIN
	insert into aud_ot_det_formula(
                  ot_id_fk, ins_id_fk, u_id_fk, imp_id_fk, 
                  otdf_cant, otdf_orden, otdf_estado, otdf_dosificado, otdf_fecha,
                  aud_otdf_type, version_id
                ) VALUES (
				  NEW.ot_id_fk, NEW.ins_id_fk, NEW.u_id_fk, NEW.imp_id_fk, 
                  NEW.otdf_cant, NEW.otdf_orden, NEW.otdf_estado, NEW.otdf_dosificado, NEW.otdf_fecha, 
                  'UPDATE', NEW.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_formula_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_formula_AFTER_DELETE` AFTER DELETE ON `ot_det_formula` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.otdf_uuid,'ot_det_formula');
    
    insert into aud_ot_det_formula(
                  ot_id_fk, ins_id_fk, u_id_fk, imp_id_fk, 
                  otdf_cant, otdf_orden, otdf_estado, otdf_dosificado, otdf_fecha, 
                  aud_otdf_type, version_id
                ) VALUES (
				  old.ot_id_fk, old.ins_id_fk, old.u_id_fk, old.imp_id_fk, 
                  old.otdf_cant, old.otdf_orden, old.otdf_estado, old.otdf_dosificado, old.otdf_fecha,
				  'DELETE', old.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`formulas_det_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`formulas_det_BEFORE_INSERT` BEFORE INSERT ON `formulas_det` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.fd_uuid is null || trim(new.fd_uuid) = '' then
		SET new.fd_uuid = uuid();
	elseif length(new.fd_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.fd_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`formulas_det_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`formulas_det_AFTER_DELETE` AFTER DELETE ON `formulas_det` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.fd_uuid,'formulas_det');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`com_estados_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`com_estados_BEFORE_INSERT` BEFORE INSERT ON `com_estados` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.ce_uuid is null || trim(new.ce_uuid) = '' then
		SET new.ce_uuid = uuid();
	elseif length(new.ce_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.ce_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`com_estados_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`com_estados_AFTER_DELETE` AFTER DELETE ON `com_estados` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.ce_uuid,'com_estados');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_det_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_det_BEFORE_INSERT` BEFORE INSERT ON `prescripciones_det` FOR EACH ROW
BEGIN
	declare msg varchar(128);
	if new.prd_uuid is null || trim(new.prd_uuid) = '' then
		SET new.prd_uuid = uuid();
	elseif length(new.prd_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.prd_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_det_AFTER_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_det_AFTER_INSERT` AFTER INSERT ON `prescripciones_det` FOR EACH ROW
BEGIN
	insert into aud_prescripciones_det(
              pr_id_fk, c_id_fk, 
              prd_cant, prd_orden, prd_estado, prd_dosificado, prd_fecha,
			  aud_prd_type, version_id
			) VALUES (
			  NEW.pr_id_fk, NEW.c_id_fk, 
              NEW.prd_cant, NEW.prd_orden, NEW.prd_estado, NEW.prd_dosificado, NEW.prd_fecha,
			  'INSERT', NEW.version_id
			);
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_det_AFTER_UPDATE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_det_AFTER_UPDATE` AFTER UPDATE ON `prescripciones_det` FOR EACH ROW
BEGIN
			insert into aud_prescripciones_det(
              pr_id_fk, c_id_fk, 
              prd_cant, prd_orden, prd_estado, prd_dosificado, prd_fecha,
			  aud_prd_type, version_id
			) VALUES (
			  NEW.pr_id_fk, NEW.c_id_fk, 
              NEW.prd_cant, NEW.prd_orden, NEW.prd_estado, NEW.prd_dosificado, NEW.prd_fecha,
			  'UPDATE', NEW.version_id
			);
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`prescripciones_det_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`prescripciones_det_AFTER_DELETE` AFTER DELETE ON `prescripciones_det` FOR EACH ROW
BEGIN
			insert into borrados (uuid,tabla) values (old.prd_uuid,'prescripciones_det');
            
			insert into aud_prescripciones_det(
              pr_id_fk, c_id_fk, 
              prd_cant, prd_orden, prd_estado, prd_dosificado, prd_fecha,
			  aud_prd_type, version_id
			) VALUES (
			  old.pr_id_fk, old.c_id_fk, 
              old.prd_cant, old.prd_orden, old.prd_estado, old.prd_dosificado, old.prd_fecha,
			  'DELETE', old.version_id
			);
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_ronda_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_ronda_BEFORE_INSERT` BEFORE INSERT ON `ot_det_ronda` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.otdr_uuid is null || trim(new.otdr_uuid) = '' then
		SET new.otdr_uuid = uuid();
	elseif length(new.otdr_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.otdr_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_ronda_AFTER_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_ronda_AFTER_INSERT` AFTER INSERT ON `ot_det_ronda` FOR EACH ROW
BEGIN
	insert into aud_ot_det_ronda(
                  ot_id_fk, c_id_fk, u_id_fk, imp_id_fk, 
                  otdr_cant, otdr_orden, otdr_estado, otdr_dosificado, otdr_fecha,
				  aud_otdr_type, version_id
                ) VALUES (
				  NEW.ot_id_fk, NEW.c_id_fk, NEW.u_id_fk, NEW.imp_id_fk, 
                  NEW.otdr_cant, NEW.otdr_orden, NEW.otdr_estado, NEW.otdr_dosificado, NEW.otdr_fecha,
				  'INSERT', NEW.version_id
                );
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_ronda_AFTER_UPDATE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_ronda_AFTER_UPDATE` AFTER UPDATE ON `ot_det_ronda` FOR EACH ROW
BEGIN
	insert into aud_ot_det_ronda(
                  ot_id_fk, c_id_fk, u_id_fk, imp_id_fk,
				  otdr_cant, otdr_orden, otdr_estado, otdr_dosificado, otdr_fecha, 
                  aud_otdr_type, version_id
                ) VALUES (
				  NEW.ot_id_fk, NEW.c_id_fk, NEW.u_id_fk, NEW.imp_id_fk, 
                  NEW.otdr_cant, NEW.otdr_orden, NEW.otdr_estado, NEW.otdr_dosificado, NEW.otdr_fecha, 
                  'UPDATE', NEW.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`ot_det_ronda_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`ot_det_ronda_AFTER_DELETE` AFTER DELETE ON `ot_det_ronda` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.otdr_uuid,'ot_det_ronda');
    
    insert into aud_ot_det_ronda(
                  ot_id_fk, c_id_fk, u_id_fk, imp_id_fk, 
                  otdr_cant, otdr_orden, otdr_estado, otdr_dosificado, otdr_fecha, 
                  aud_otdr_type, version_id
                ) VALUES (
				  old.ot_id_fk, old.c_id_fk, old.u_id_fk, old.imp_id_fk, 
                  old.otdr_cant, old.otdr_orden, old.otdr_estado, old.otdr_dosificado, old.otdr_fecha, 
                  'DELETE', old.version_id
                ); 
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`com_acciones_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`com_acciones_BEFORE_INSERT` BEFORE INSERT ON `com_acciones` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.ca_uuid is null || trim(new.ca_uuid) = '' then
		SET new.ca_uuid = uuid();
	elseif length(new.ca_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.ca_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`com_acciones_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`com_acciones_AFTER_DELETE` AFTER DELETE ON `com_acciones` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.ca_uuid,'com_acciones');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`com_lec_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`com_lec_BEFORE_INSERT` BEFORE INSERT ON `com_lec` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.cl_uuid is null || trim(new.cl_uuid) = '' then
		SET new.cl_uuid = uuid();
	elseif length(new.cl_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.cl_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`com_lec_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`com_lec_AFTER_DELETE` AFTER DELETE ON `com_lec` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.cl_uuid,'com_lec');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`sectores_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`sectores_BEFORE_INSERT` BEFORE INSERT ON `sectores` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.sec_uuid is null || trim(new.sec_uuid) = '' then
		SET new.sec_uuid = uuid();
	elseif length(new.sec_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.sec_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`sectores_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`sectores_AFTER_DELETE` AFTER DELETE ON `sectores` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.sec_uuid,'sectores');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`sectores_det_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`sectores_det_BEFORE_INSERT` BEFORE INSERT ON `sectores_det` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.secd_uuid is null || trim(new.secd_uuid) = '' then
		SET new.secd_uuid = uuid();
	elseif length(new.secd_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.secd_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`sectores_det_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`sectores_det_AFTER_DELETE` AFTER DELETE ON `sectores_det` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.secd_uuid,'sectores_det');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`campos_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`campos_BEFORE_INSERT` BEFORE INSERT ON `campos` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.campo_uuid is null || trim(new.campo_uuid) = '' then
		SET new.campo_uuid = uuid();
	elseif length(new.campo_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.campo_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`campos_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`campos_AFTER_DELETE` AFTER DELETE ON `campos` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.campo_uuid,'campos');
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`campos_det_BEFORE_INSERT` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`campos_det_BEFORE_INSERT` BEFORE INSERT ON `campos_det` FOR EACH ROW
BEGIN
	declare msg varchar(128);

	if new.cd_uuid is null || trim(new.cd_uuid) = '' then
		SET new.cd_uuid = uuid();
	elseif length(new.cd_uuid) < 36 then
		set msg = concat('Trigger Error: UUID not valid: ', cast(new.cd_uuid as char));
        signal sqlstate '45000' set message_text = msg;
    end if;
END$$


USE `nutrack`$$
DROP TRIGGER IF EXISTS `nutrack`.`campos_det_AFTER_DELETE` $$
USE `nutrack`$$
CREATE DEFINER = CURRENT_USER TRIGGER `nutrack`.`campos_det_AFTER_DELETE` AFTER DELETE ON `campos_det` FOR EACH ROW
BEGIN
	insert into borrados (uuid,tabla) values (old.cd_uuid,'campos_det');
END$$


DELIMITER ;

