-- MySQL Script generated by MySQL Workbench
-- Thu Nov 14 21:02:32 2024
-- 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 vetapp
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `vetapp`.`regiones`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`regiones` (
  `id_region` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_region`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`comunas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`comunas` (
  `id_comuna` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `id_region` INT NOT NULL,
  PRIMARY KEY (`id_comuna`),
  INDEX `fk_comunas_regiones_idx` (`id_region` ASC) VISIBLE,
  CONSTRAINT `fk_comunas_regiones`
    FOREIGN KEY (`id_region`)
    REFERENCES `vetapp`.`regiones` (`id_region`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`clinicas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`clinicas` (
  `id_clinica` INT NOT NULL,
  `nombre` VARCHAR(150) NOT NULL,
  `rut` VARCHAR(8) NULL,
  `dv` CHAR(1) NULL,
  `telefono_contacto` VARCHAR(12) NOT NULL,
  `mail_contacto` VARCHAR(100) NOT NULL,
  `direccion` VARCHAR(150) NULL,
  `id_region` INT NULL,
  `id_comuna` INT NULL,
  PRIMARY KEY (`id_clinica`),
  INDEX `fk_clinicas_regiones_idx` (`id_region` ASC) VISIBLE,
  INDEX `fk_clinicas_comunas_idx` (`id_comuna` ASC) VISIBLE,
  CONSTRAINT `fk_clinicas_regiones`
    FOREIGN KEY (`id_region`)
    REFERENCES `vetapp`.`regiones` (`id_region`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_clinicas_comunas`
    FOREIGN KEY (`id_comuna`)
    REFERENCES `vetapp`.`comunas` (`id_comuna`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`roles` (
  `id_rol` INT NOT NULL,
  `nombre` VARCHAR(45) NULL,
  PRIMARY KEY (`id_rol`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`usuarios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`usuarios` (
  `id_usuario` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `apellidos` VARCHAR(100) NULL,
  `rut` VARCHAR(8) NULL,
  `dv` CHAR(1) NULL,
  `mail` VARCHAR(100) NOT NULL,
  `pass` VARCHAR(45) NOT NULL,
  `token` VARCHAR(45) NULL,
  `confirmado` VARCHAR(45) NOT NULL DEFAULT 0,
  `id_rol` INT NULL,
  `fecha_alta` DATETIME NULL,
  `activo` INT NOT NULL DEFAULT 1,
  `id_clinica` INT NULL,
  `telefono` VARCHAR(12) NULL,
  PRIMARY KEY (`id_usuario`),
  INDEX `fk_usuarios_roles_idx` (`id_rol` ASC) VISIBLE,
  INDEX `fk_usuarios_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  CONSTRAINT `fk_usuarios_roles`
    FOREIGN KEY (`id_rol`)
    REFERENCES `vetapp`.`roles` (`id_rol`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_usuarios_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`propietarios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`propietarios` (
  `id_propietario` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `apellidos` VARCHAR(100) NOT NULL,
  `rut` VARCHAR(8) NULL,
  `dv` CHAR(1) NULL,
  `telefono` VARCHAR(12) NOT NULL,
  `telefono_bis` VARCHAR(12) NULL,
  `mail` VARCHAR(100) NULL,
  `direccion` VARCHAR(150) NULL,
  `id_region` INT NULL,
  `id_comuna` INT NULL,
  `id_usuario_alta` INT NULL,
  `fecha_alta` DATETIME NULL,
  PRIMARY KEY (`id_propietario`),
  INDEX `fk_propietarios_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_propietarios_regiones_idx` (`id_region` ASC) VISIBLE,
  INDEX `fk_propietarios_comunas_idx` (`id_comuna` ASC) VISIBLE,
  INDEX `fk_propietarios_usuarios_idx` (`id_usuario_alta` ASC) VISIBLE,
  CONSTRAINT `fk_propietarios_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_propietarios_regiones`
    FOREIGN KEY (`id_region`)
    REFERENCES `vetapp`.`regiones` (`id_region`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_propietarios_comunas`
    FOREIGN KEY (`id_comuna`)
    REFERENCES `vetapp`.`comunas` (`id_comuna`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_propietarios_usuarios`
    FOREIGN KEY (`id_usuario_alta`)
    REFERENCES `vetapp`.`usuarios` (`id_usuario`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`especies`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`especies` (
  `id_especie` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `animal_mayor` INT NOT NULL DEFAULT 0,
  `id_clinica` INT NOT NULL,
  `id_usuario_alta` INT NOT NULL,
  PRIMARY KEY (`id_especie`),
  INDEX `fk_especies_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_especies_usuarios_idx` (`id_usuario_alta` ASC) VISIBLE,
  CONSTRAINT `fk_especies_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_especies_usuarios`
    FOREIGN KEY (`id_usuario_alta`)
    REFERENCES `vetapp`.`usuarios` (`id_usuario`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`razas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`razas` (
  `id_raza` INT NOT NULL,
  `nombre` VARCHAR(150) NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_especie` INT NOT NULL,
  `id_usuario_alta` INT NOT NULL,
  PRIMARY KEY (`id_raza`),
  INDEX `fk_razas_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_razas_especies_idx` (`id_especie` ASC) VISIBLE,
  INDEX `fk_razas_usuarios_idx` (`id_usuario_alta` ASC) VISIBLE,
  CONSTRAINT `fk_razas_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_razas_especies`
    FOREIGN KEY (`id_especie`)
    REFERENCES `vetapp`.`especies` (`id_especie`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_razas_usuarios`
    FOREIGN KEY (`id_usuario_alta`)
    REFERENCES `vetapp`.`usuarios` (`id_usuario`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`mascotas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`mascotas` (
  `id_mascota` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_propietario` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  `num_chip` VARCHAR(50) NOT NULL,
  `id_especie` INT NOT NULL,
  `id_raza` INT NOT NULL,
  `fecha_nacimiento` DATETIME NULL,
  `fecha_defuncion` BLOB NULL,
  `sexo` INT NULL,
  `Color` VARCHAR(150) NULL,
  `patron` VARCHAR(250) NULL,
  `tamano` VARCHAR(45) NULL,
  `peso` VARCHAR(45) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_mascota`),
  UNIQUE INDEX `num_chip_UNIQUE` (`num_chip` ASC) VISIBLE,
  INDEX `fk_mascotas_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_mascotas_propietarios_idx` (`id_propietario` ASC) VISIBLE,
  INDEX `fk_mascotas_especies_idx` (`id_especie` ASC) VISIBLE,
  INDEX `fk_mascotas_razas_idx` (`id_raza` ASC) VISIBLE,
  CONSTRAINT `fk_mascotas_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_mascotas_propietarios`
    FOREIGN KEY (`id_propietario`)
    REFERENCES `vetapp`.`propietarios` (`id_propietario`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_mascotas_especies`
    FOREIGN KEY (`id_especie`)
    REFERENCES `vetapp`.`especies` (`id_especie`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_mascotas_razas`
    FOREIGN KEY (`id_raza`)
    REFERENCES `vetapp`.`razas` (`id_raza`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`tipo_citas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`tipo_citas` (
  `id_tipo_cita` INT NOT NULL,
  `nombre` VARCHAR(45) NULL,
  PRIMARY KEY (`id_tipo_cita`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`citas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`citas` (
  `id_cita` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_propietario` INT NOT NULL,
  `fecha` DATE NOT NULL,
  `id_tipo_cita` INT NOT NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_cita`),
  INDEX `fk_citas_tipo_cita_idx` (`id_tipo_cita` ASC) VISIBLE,
  CONSTRAINT `fk_citas_tipo_cita`
    FOREIGN KEY (`id_tipo_cita`)
    REFERENCES `vetapp`.`tipo_citas` (`id_tipo_cita`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`horarios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`horarios` (
  `id_horario` INT NOT NULL,
  `id_clinica` INT NULL,
  `tramo` TIME NULL,
  PRIMARY KEY (`id_horario`),
  INDEX `fk_horarios_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  CONSTRAINT `fk_horarios_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`citas_horario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`citas_horario` (
  `id_cita_horario` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_cita` INT NOT NULL,
  `id_horario` INT NOT NULL,
  PRIMARY KEY (`id_cita_horario`),
  INDEX `fk_horario_clinica_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_horarios_citas_idx` (`id_cita` ASC) VISIBLE,
  INDEX `fk_cita_horario_idx` (`id_horario` ASC) VISIBLE,
  CONSTRAINT `fk_horarios_clinica`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_horarios_citas`
    FOREIGN KEY (`id_cita`)
    REFERENCES `vetapp`.`citas` (`id_cita`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_cita_horario`
    FOREIGN KEY (`id_horario`)
    REFERENCES `vetapp`.`horarios` (`id_horario`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`tratamientos_periodicos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`tratamientos_periodicos` (
  `id_tratamiento` INT NOT NULL,
  `nombre` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`id_tratamiento`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`historial_paciente`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`historial_paciente` (
  `id_historial` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_tratamiento` INT NOT NULL, -- DEFAULT Vacunas/Desparacitaciones,
  `fecha` DATE NOT NULL,
  `tipo` VARCHAR(45) NOT NULL,
  `producto` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`id_historial`),
  INDEX `fk_historial_clinica_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_historial_mascota_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_historial_tratamiento_idx` (`id_tratamiento` ASC) VISIBLE,
  CONSTRAINT `fk_historial_clinica`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_historial_mascota`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_historial_tratamiento`
    FOREIGN KEY (`id_tratamiento`)
    REFERENCES `vetapp`.`tratamientos_periodicos` (`id_tratamiento`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`consultas_animal_menor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`consultas_animal_menor` (
  `id_consulta_am` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_usuario` INT NOT NULL,
  `id_cita` INT NULL,
  `temperatural` INT NULL,
  `frec_cariaca` DECIMAL(5,2) NULL,
  `frec_respiratoria` DECIMAL(5,2) NULL,
  `hidratacion` DECIMAL(5,2) NULL,
  `presion_sistolica` INT NULL,
  `palp_tiroides` VARCHAR(45) NULL,
  `diagnostico` TEXT NULL,
  `tratamiento` TEXT NULL,
  `comentario_examenes` TEXT NULL,
  `conclusiones` TEXT NULL,
  PRIMARY KEY (`id_consulta_am`),
  INDEX `fk_consultas_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_consultas_mascota_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_consultas_usuarios_idx` (`id_usuario` ASC) VISIBLE,
  INDEX `fk_consultas_cita_idx` (`id_cita` ASC) VISIBLE,
  CONSTRAINT `fk_consultas_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_consultas_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_consultas_usuarios`
    FOREIGN KEY (`id_usuario`)
    REFERENCES `vetapp`.`usuarios` (`id_usuario`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_consultas_cita`
    FOREIGN KEY (`id_cita`)
    REFERENCES `vetapp`.`citas` (`id_cita`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`examenes_complementarios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`examenes_complementarios` (
  `id_exam_comp` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_exam_comp`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`examenes_imagen`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`examenes_imagen` (
  `id_exam_imagen` INT NOT NULL,
  `nombre` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_exam_imagen`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`examenes_comp_solicitados`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`examenes_comp_solicitados` (
  `id_examen_solic` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta_am` INT NOT NULL,
  `id_exam_comp` INT NOT NULL,
  PRIMARY KEY (`id_examen_solic`),
  INDEX `fk_exam_comp_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_exam_comp_mascota_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_exam_comp_consultas_idx` (`id_consulta_am` ASC) VISIBLE,
  INDEX `fx_exam_comp_exam_idx` (`id_exam_comp` ASC) VISIBLE,
  CONSTRAINT `fk_exam_comp_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_exam_comp_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_exam_comp_consultas`
    FOREIGN KEY (`id_consulta_am`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fx_exam_comp_exam`
    FOREIGN KEY (`id_exam_comp`)
    REFERENCES `vetapp`.`examenes_complementarios` (`id_exam_comp`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`examenes_imagen_solicitados`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`examenes_imagen_solicitados` (
  `id_examen_solic` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta_am` INT NOT NULL,
  `id_exam_imagen` INT NOT NULL,
  PRIMARY KEY (`id_examen_solic`),
  INDEX `fk_exam_imag_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_exam_imag_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_exam_imag_consulta_idx` (`id_consulta_am` ASC) VISIBLE,
  INDEX `fk_exam_imag_exam_idx` (`id_exam_imagen` ASC) VISIBLE,
  CONSTRAINT `fk_exam_imag_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_exam_imag_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_exam_imag_consulta`
    FOREIGN KEY (`id_consulta_am`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_exam_imag_exam`
    FOREIGN KEY (`id_exam_imagen`)
    REFERENCES `vetapp`.`examenes_imagen` (`id_exam_imagen`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`recetas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`recetas` (
  `id_recetas` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_usuario` INT NOT NULL,
  `farmaco` VARCHAR(100) NOT NULL,
  `nombre_comercial` VARCHAR(100) NULL,
  `dosis` VARCHAR(45) NULL,
  `horario` VARCHAR(45) NULL,
  `dias` VARCHAR(45) NULL,
  `comentario` TEXT NULL,
  PRIMARY KEY (`id_recetas`),
  INDEX `fk_recetas_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_recetas_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_recetas_usuarios_idx` (`id_usuario` ASC) VISIBLE,
  CONSTRAINT `fk_recetas_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_recetas_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_recetas_usuarios`
    FOREIGN KEY (`id_usuario`)
    REFERENCES `vetapp`.`usuarios` (`id_usuario`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_cardiovacular`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_cardiovacular` (
  `id_cardio` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `ausc_card` INT NULL,
  `ausc_card_comen` VARCHAR(500) NULL,
  `pulso_fem` INT NULL,
  `pulso_fem_comen` VARCHAR(500) NULL,
  `tllc` INT NULL,
  `tllc_comen` VARCHAR(500) NULL,
  `color_muc` INT NULL,
  `color_muc_comen` VARCHAR(500) NULL,
  `palpacion` INT NULL,
  `palpacion_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_cardio`),
  INDEX `fk_cardio_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_cardio_macotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_cardio_consultas_idx` (`id_consulta` ASC) VISIBLE,
  CONSTRAINT `fk_cardio_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_cardio_macotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_cardio_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_respiratorio`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_respiratorio` (
  `id_respiratorio` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `aerea_superior` INT NULL,
  `aerea_superior_comen` VARCHAR(500) NULL,
  `patron_resp` INT NULL,
  `patron_resp_comen` VARCHAR(500) NULL,
  `reflejo_tus` INT NULL,
  `reflejo_tus_comen` VARCHAR(500) NULL,
  `auscult` INT NULL,
  `auscult_comen` VARCHAR(500) NULL,
  `percusion` INT NULL,
  `percusion_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_respiratorio`),
  INDEX `fk_respiratorio_consultas_idx` (`id_consulta` ASC) VISIBLE,
  INDEX `fk_respiratorio_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_respiratorio_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  CONSTRAINT `fk_respiratorio_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_respiratorio_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_respiratorio_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_digestivo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_digestivo` (
  `id_digestivo` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `oral` INT,
  `oral_comen` VARCHAR(500),
  `esofagica` INT,
  `esofagica_comen` VARCHAR(500),
  `peri_abdominal` INT,
  `peri_abd_comen` VARCHAR(500),
  `palp_abdominal` INT,
  `palp_abd_comen` VARCHAR(500),
  `borborigmo` INT,
  `borborigmo_comen` VARCHAR(500),
  `palp_rectal` INT,
  `palp_rectal_comen` VARCHAR(500),
  `deposiciones` INT,
  `deposiciones_comen` VARCHAR(500),
  `comentarios` TEXT,
  PRIMARY KEY (`id_digestivo`),
  INDEX `fk_digestivo_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_digestivo_mascota_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_digestivo_consultas_idx` (`id_consulta` ASC) VISIBLE,
  CONSTRAINT `fk_digestivo_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_digestivo_mascota`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_digestivo_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_urogenital`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_urogenital` (
  `id_urogenital` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `palp_vesical` INT NULL,
  `palp_vesical_comen` VARCHAR(500) NULL,
  `palp_renal` INT NULL,
  `palp_renal_comen` VARCHAR(500) NULL,
  `ex_gen_vulva` INT NULL,
  `ex_gen_vulva_comen` VARCHAR(500) NULL,
  `ex_gen_test` INT NULL,
  `ex_gen_test_comen` VARCHAR(500) NULL,
  `palp_prost` INT NULL,
  `palp_prost_comen` VARCHAR(500) NULL,
  `ex_gl_mam` INT NULL,
  `ex_gl_mam_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_urogenital`),
  INDEX `fk_uro_consultas_idx` (`id_consulta` ASC) VISIBLE,
  INDEX `fk_uro_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_uro_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  CONSTRAINT `fk_uro_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_uro_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_uro_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_tegumentario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_tegumentario` (
  `id_tegumentario` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `piel` INT NULL,
  `piel_comen` VARCHAR(500) NULL,
  `pliegue` INT NULL,
  `pliegue_comen` VARCHAR(4500) NULL,
  `fanerios` INT NULL,
  `fanerios_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_tegumentario`),
  INDEX `fk_teg_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_teg_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_teg_consultas_idx` (`id_consulta` ASC) VISIBLE,
  CONSTRAINT `fk_teg_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_teg_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_teg_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_musculo_esq`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_musculo_esq` (
  `id_musculo` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `est_muscular` INT NULL,
  `est_muscular_comen` VARCHAR(500) NULL,
  `cond_corp` INT NULL,
  `cond_corp_comen` VARCHAR(500) NULL,
  `deambulacion` INT NULL,
  `deambulacion_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_musculo`),
  INDEX `fk_musculo_consultas_idx` (`id_consulta` ASC) VISIBLE,
  INDEX `fk_musculo_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_musculo_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  CONSTRAINT `fk_musculo_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_musculo_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_musculo_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_neurologico`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_neurologico` (
  `id_neurologico` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `estado_conciencia` INT NULL,
  `estado_conciencia_comen` VARCHAR(500) NULL,
  `reflejo_pupilar` INT NULL,
  `reflejo_pupilar_comen` VARCHAR(500) NULL,
  `propiocepcion` INT NULL,
  `propiocepcion_comen` VARCHAR(500) NULL,
  `reflejo_panicular` INT NULL,
  `reflejo_panicular_comen` VARCHAR(500) NULL,
  `ev_dolor` INT NULL,
  `ev_dolor_comen` VARCHAR(500) NULL,
  `reflejo_anal` INT NULL,
  `reflejo_anal_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_neurologico`),
  INDEX `fk_neuro_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_neuro_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_neuro_consultas_idx` (`id_consulta` ASC) VISIBLE,
  CONSTRAINT `fk_neuro_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_neuro_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_neuro_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_ocular`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_ocular` (
  `id_ocular` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `globos_oc` INT NULL,
  `globos_oc_comen` VARCHAR(500) NULL,
  `pupilas` INT NULL,
  `pupilas_comen` VARCHAR(500) NULL,
  `iris` INT NULL,
  `iris_comen` VARCHAR(500) NULL,
  `esclerotica` INT NULL,
  `esclerotica_comen` VARCHAR(500) NULL,
  `camaras` INT NULL,
  `camara_comen` VARCHAR(500) NULL,
  `parpados` INT NULL,
  `parpados_comen` VARCHAR(500) NULL,
  `secreciones` INT NULL,
  `secreciones_comen` VARCHAR(500) NULL,
  `comentarios` TEXT NULL,
  PRIMARY KEY (`id_ocular`),
  INDEX `fk_ocular_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_ocular_mascotas_idx` (`id_mascota` ASC) VISIBLE,
  INDEX `fk_ocular_consultas_idx` (`id_consulta` ASC) VISIBLE,
  CONSTRAINT `fk_ocular_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ocular_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_ocular_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`am_sis_auditivo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`am_sis_auditivo` (
  `id_auditivo` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `id_mascota` INT NOT NULL,
  `id_consulta` INT NOT NULL,
  `pabellones` INT NULL,
  `pabellones_comen` VARCHAR(500) NULL,
  `deambulacion` INT NULL,
  `deambulacion_comen` VARCHAR(500) NULL,
  `secreciones` INT NULL,
  `secreciones_comen` VARCHAR(500) NULL,
  `dolor` INT NULL,
  `dolor_comen` VARCHAR(500) NULL,
  `coloracion` INT NULL,
  `coloracion_comen` VARCHAR(500) NULL,
  `olor` INT NULL,
  `olor_comen` VARCHAR(500) NULL,
  `comentario` TEXT NULL,
  PRIMARY KEY (`id_auditivo`),
  INDEX `fk_auditivo_consultas_idx` (`id_consulta` ASC) VISIBLE,
  INDEX `fk_auditivo_clinicas_idx` (`id_clinica` ASC) VISIBLE,
  CONSTRAINT `fk_auditivo_clinicas`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_auditivo_mascotas`
    FOREIGN KEY (`id_mascota`)
    REFERENCES `vetapp`.`mascotas` (`id_mascota`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_auditivo_consultas`
    FOREIGN KEY (`id_consulta`)
    REFERENCES `vetapp`.`consultas_animal_menor` (`id_consulta_am`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`tipo_producto`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`tipo_producto` (
  `id_tipo` INT NOT NULL,
  `nombre` VARCHAR(150) NOT NULL,
  PRIMARY KEY (`id_tipo`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vetapp`.`productos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vetapp`.`productos` (
  `id_producto` INT NOT NULL,
  `id_clinica` INT NOT NULL,
  `nombre` VARCHAR(150) NULL,
  `descripcion` TEXT NULL,
  `cantidad` INT NULL,
  `min` INT NULL,
  `max` INT NULL,
  `id_tipo` INT NOT NULL,
  PRIMARY KEY (`id_producto`),
  INDEX `fk_productos_clinica_idx` (`id_clinica` ASC) VISIBLE,
  INDEX `fk_productos_tipos_idx` (`id_tipo` ASC) VISIBLE,
  CONSTRAINT `fk_productos_clinica`
    FOREIGN KEY (`id_clinica`)
    REFERENCES `vetapp`.`clinicas` (`id_clinica`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_productos_tipos`
    FOREIGN KEY (`id_tipo`)
    REFERENCES `vetapp`.`tipo_producto` (`id_tipo`)
    ON DELETE RESTRICT
    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;
