/*
 Navicat Premium Dump SQL

 Source Server         : MySQL
 Source Server Type    : MySQL
 Source Server Version : 80300 (8.3.0)
 Source Host           : localhost:3306
 Source Schema         : app_enxu

 Target Server Type    : MySQL
 Target Server Version : 80300 (8.3.0)
 File Encoding         : 65001

 Date: 25/02/2026 23:31:53
*/

DROP DATABASE IF EXISTS app_enxu;
CREATE DATABASE app_enxu
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE app_enxu;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ciudades
-- ----------------------------
DROP TABLE IF EXISTS `ciudades`;
CREATE TABLE `ciudades`  (
  `ciu_id` int NOT NULL AUTO_INCREMENT,
  `dep_id` int NOT NULL,
  `ciu_nombre` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ciu_latitud` decimal(10, 8) NULL DEFAULT NULL,
  `ciu_longitud` decimal(11, 8) NULL DEFAULT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ciu_id`) USING BTREE,
  UNIQUE INDEX `dep_id`(`dep_id` ASC, `ciu_nombre` ASC) USING BTREE,
  INDEX `creado_por`(`creado_por` ASC) USING BTREE,
  INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
  CONSTRAINT `ciudades_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `departamentos` (`dep_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ciudades_ibfk_2` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ciudades_ibfk_3` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for conexiones
-- ----------------------------
-- DROP TABLE IF EXISTS `conexiones`;
-- CREATE TABLE `conexiones`  (
--   `con_id` int NOT NULL AUTO_INCREMENT,
--   `con_usuario_uno_id` int NOT NULL,
--   `con_usuario_dos_id` int NOT NULL,
--   `con_eliminado` tinyint(1) NULL DEFAULT 0,
--   `con_fecha_eliminacion` datetime NULL DEFAULT NULL,
--   `creado_por` int NULL DEFAULT NULL,
--   `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   `actualizado_por` int NULL DEFAULT NULL,
--   `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
--   PRIMARY KEY (`con_id`) USING BTREE,
--   UNIQUE INDEX `con_usuario_uno_id`(`con_usuario_uno_id` ASC, `con_usuario_dos_id` ASC) USING BTREE,
--   INDEX `creado_por`(`creado_por` ASC) USING BTREE,
--   INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
--   INDEX `con_usuario_uno_id_2`(`con_usuario_uno_id` ASC) USING BTREE,
--   INDEX `con_usuario_dos_id`(`con_usuario_dos_id` ASC) USING BTREE,
--   CONSTRAINT `conexiones_ibfk_1` FOREIGN KEY (`con_usuario_uno_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
--   CONSTRAINT `conexiones_ibfk_2` FOREIGN KEY (`con_usuario_dos_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
--   CONSTRAINT `conexiones_ibfk_3` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
--   CONSTRAINT `conexiones_ibfk_4` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
-- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for conversaciones
-- ----------------------------
DROP TABLE IF EXISTS `conversaciones`;
CREATE TABLE `conversaciones`  (
  `cov_id` int NOT NULL AUTO_INCREMENT,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cov_id`) USING BTREE,
  INDEX `creado_por`(`creado_por` ASC) USING BTREE,
  INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
  CONSTRAINT `conversaciones_ibfk_1` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `conversaciones_ibfk_2` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for departamentos
-- ----------------------------
DROP TABLE IF EXISTS `departamentos`;
CREATE TABLE `departamentos`  (
  `dep_id` int NOT NULL AUTO_INCREMENT,
  `pai_id` int NOT NULL,
  `dep_nombre` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`dep_id`) USING BTREE,
  UNIQUE INDEX `pai_id`(`pai_id` ASC, `dep_nombre` ASC) USING BTREE,
  INDEX `creado_por`(`creado_por` ASC) USING BTREE,
  INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
  CONSTRAINT `departamentos_ibfk_1` FOREIGN KEY (`pai_id`) REFERENCES `paises` (`pai_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `departamentos_ibfk_2` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `departamentos_ibfk_3` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for dispositivo
-- ----------------------------
DROP TABLE IF EXISTS `dispositivo`;
CREATE TABLE `dispositivo`  (
  `dis_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `dis_uuid` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dis_sistema` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `dis_modelo` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `dis_ultima_actividad` datetime NULL DEFAULT NULL,
  `dis_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`dis_id`) USING BTREE,
  UNIQUE INDEX `uq_usuario_dispositivo`(`usu_id` ASC, `dis_uuid` ASC) USING BTREE,
  CONSTRAINT `fk_dispositivo_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for habilidades
-- ----------------------------
DROP TABLE IF EXISTS `habilidades`;
CREATE TABLE `habilidades`  (
  `hab_id` int NOT NULL AUTO_INCREMENT,
  `hab_nombre` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `hab_categoria` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`hab_id`) USING BTREE,
  UNIQUE INDEX `hab_nombre`(`hab_nombre` ASC) USING BTREE,
  INDEX `creado_por`(`creado_por` ASC) USING BTREE,
  INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
  CONSTRAINT `habilidades_ibfk_1` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `habilidades_ibfk_2` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for historial_acceso
-- ----------------------------
DROP TABLE IF EXISTS `historial_acceso`;
CREATE TABLE `historial_acceso`  (
  `his_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `dis_id` int NULL DEFAULT NULL,
  `his_login` datetime NOT NULL,
  `his_logout` datetime NULL DEFAULT NULL,
  `his_ip` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `his_resultado` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`his_id`) USING BTREE,
  INDEX `fk_historial_usuario`(`usu_id` ASC) USING BTREE,
  INDEX `fk_historial_dispositivo`(`dis_id` ASC) USING BTREE,
  CONSTRAINT `fk_historial_dispositivo` FOREIGN KEY (`dis_id`) REFERENCES `dispositivo` (`dis_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_historial_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for rate_limit
-- ----------------------------
DROP TABLE IF EXISTS `rate_limit`;
CREATE TABLE `rate_limit`  (
  `rlm_id` int NOT NULL AUTO_INCREMENT,
  `rlm_identificador` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rlm_endpoint` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rlm_ventana_inicio` datetime NOT NULL,
  `rlm_conteo` int NOT NULL DEFAULT 1,
  `rlm_ventana_segundos` int NOT NULL,
  `rlm_actualizado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rlm_id`) USING BTREE,
  UNIQUE INDEX `uq_rate_limit`(`rlm_identificador` ASC, `rlm_endpoint` ASC, `rlm_ventana_inicio` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for login_social
-- ----------------------------
DROP TABLE IF EXISTS `login_social`;
CREATE TABLE `login_social`  (
  `soc_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `soc_proveedor` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `soc_id_externo` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `soc_email` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `soc_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`soc_id`) USING BTREE,
  UNIQUE INDEX `uq_social`(`soc_proveedor` ASC, `soc_id_externo` ASC) USING BTREE,
  INDEX `fk_social_usuario`(`usu_id` ASC) USING BTREE,
  CONSTRAINT `fk_social_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for mensajes
-- ----------------------------
DROP TABLE IF EXISTS `mensajes`;
CREATE TABLE `mensajes`  (
  `men_id` bigint NOT NULL AUTO_INCREMENT,
  `cov_id` int NOT NULL,
  `men_remitente_id` int NOT NULL,
  `men_contenido` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `men_leido` tinyint(1) NULL DEFAULT 0,
  `men_fecha_envio` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`men_id`) USING BTREE,
  INDEX `cov_id`(`cov_id` ASC) USING BTREE,
  INDEX `men_remitente_id`(`men_remitente_id` ASC) USING BTREE,
  INDEX `men_fecha_envio`(`men_fecha_envio` ASC) USING BTREE,
  CONSTRAINT `mensajes_ibfk_1` FOREIGN KEY (`cov_id`) REFERENCES `conversaciones` (`cov_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `mensajes_ibfk_2` FOREIGN KEY (`men_remitente_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for paises
-- ----------------------------
DROP TABLE IF EXISTS `paises`;
CREATE TABLE `paises`  (
  `pai_id` int NOT NULL AUTO_INCREMENT,
  `pai_nombre` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pai_id`) USING BTREE,
  UNIQUE INDEX `pai_nombre`(`pai_nombre` ASC) USING BTREE,
  INDEX `creado_por`(`creado_por` ASC) USING BTREE,
  INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
  CONSTRAINT `paises_ibfk_1` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `paises_ibfk_2` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for participantes_conversacion
-- ----------------------------
DROP TABLE IF EXISTS `participantes_conversacion`;
CREATE TABLE `participantes_conversacion`  (
  `pco_id` int NOT NULL AUTO_INCREMENT,
  `cov_id` int NOT NULL,
  `usu_id` int NOT NULL,
  PRIMARY KEY (`pco_id`) USING BTREE,
  UNIQUE INDEX `cov_id`(`cov_id` ASC, `usu_id` ASC) USING BTREE,
  INDEX `cov_id_2`(`cov_id` ASC) USING BTREE,
  INDEX `usu_id`(`usu_id` ASC) USING BTREE,
  CONSTRAINT `participantes_conversacion_ibfk_1` FOREIGN KEY (`cov_id`) REFERENCES `conversaciones` (`cov_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `participantes_conversacion_ibfk_2` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for perfil
-- ----------------------------
DROP TABLE IF EXISTS `perfil`;
CREATE TABLE `perfil`  (
  `per_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `per_nombres` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `per_ape_paterno` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `per_ape_materno` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `per_genero` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `per_empresa` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `ppf_id` int NULL DEFAULT NULL,
  `per_idioma` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'es',
  `per_tema` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'claro',
  `per_estado` tinyint(1) NOT NULL DEFAULT 1,
  `per_titulo` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `per_descripcion` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `per_remoto` tinyint(1) NULL DEFAULT NULL,
  `per_disponibilidad` tinyint(1) NULL DEFAULT NULL,
  `ciu_id` int NULL DEFAULT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`per_id`) USING BTREE,
  INDEX `fk_perfil_usuario`(`usu_id` ASC) USING BTREE,
  INDEX `fk_perfil_ciudad`(`ciu_id` ASC) USING BTREE,
  INDEX `fk_perfil_ppf`(`ppf_id` ASC) USING BTREE,
  CONSTRAINT `fk_perfil_ciudad` FOREIGN KEY (`ciu_id`) REFERENCES `ciudades` (`ciu_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_perfil_ppf` FOREIGN KEY (`ppf_id`) REFERENCES `perfil_profesional` (`ppf_id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `fk_perfil_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for perfil_habilidad
-- ----------------------------
DROP TABLE IF EXISTS `perfil_habilidad`;
CREATE TABLE `perfil_habilidad`  (
  `phb_id` int NOT NULL AUTO_INCREMENT,
  `ppf_id` int NOT NULL,
  `hab_id` int NOT NULL,
  `phb_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`phb_id`) USING BTREE,
  UNIQUE INDEX `uq_ppf_hab`(`ppf_id` ASC, `hab_id` ASC) USING BTREE,
  INDEX `fk_phb_hab`(`hab_id` ASC) USING BTREE,
  CONSTRAINT `fk_phb_hab` FOREIGN KEY (`hab_id`) REFERENCES `habilidades` (`hab_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_phb_ppf` FOREIGN KEY (`ppf_id`) REFERENCES `perfil_profesional` (`ppf_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for perfil_profesional
-- ----------------------------
DROP TABLE IF EXISTS `perfil_profesional`;
CREATE TABLE `perfil_profesional`  (
  `ppf_id` int NOT NULL AUTO_INCREMENT,
  `ppf_nombre` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ppf_descripcion` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `ppf_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ppf_id`) USING BTREE,
  UNIQUE INDEX `uq_ppf_nombre`(`ppf_nombre` ASC) USING BTREE,
  INDEX `fk_ppf_creado_por`(`creado_por` ASC) USING BTREE,
  INDEX `fk_ppf_actualizado_por`(`actualizado_por` ASC) USING BTREE,
  CONSTRAINT `fk_ppf_actualizado_por` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_ppf_creado_por` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for permiso
-- ----------------------------
DROP TABLE IF EXISTS `permiso`;
CREATE TABLE `permiso`  (
  `per_id` int NOT NULL AUTO_INCREMENT,
  `per_codigo` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `per_descripcion` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `per_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`per_id`) USING BTREE,
  UNIQUE INDEX `uq_permiso_codigo`(`per_codigo` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for proyecto
-- ----------------------------
DROP TABLE IF EXISTS `proyecto`;
CREATE TABLE `proyecto`  (
  `pro_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `pro_titulo` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pro_descripcion` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pro_ciudad_id` int NULL DEFAULT NULL,
  `pro_remoto` tinyint(1) NULL DEFAULT 0,
  `pro_estado` tinyint(1) NOT NULL DEFAULT 1,
  `pro_fecha_inicio` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `pro_fecha_cierre` datetime NULL DEFAULT NULL,
  `creado_por` bigint NULL DEFAULT NULL,
  `creado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` bigint NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pro_id`) USING BTREE,
  INDEX `usu_id`(`usu_id` ASC) USING BTREE,
  INDEX `pro_ciudad_id`(`pro_ciudad_id` ASC) USING BTREE,
  CONSTRAINT `fk_proyecto_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for proyecto_miembro
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_miembro`;
CREATE TABLE `proyecto_miembro`  (
  `pm_id` int NOT NULL AUTO_INCREMENT,
  `pro_id` int NOT NULL,
  `usu_id` int NOT NULL,
  `hab_id` int NOT NULL,
  `pm_fecha_ingreso` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `pm_estado` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`pm_id`) USING BTREE,
  UNIQUE INDEX `pro_id`(`pro_id` ASC, `usu_id` ASC) USING BTREE,
  INDEX `usu_id`(`usu_id` ASC) USING BTREE,
  INDEX `hab_id`(`hab_id` ASC) USING BTREE,
  CONSTRAINT `fk_proyecto_miembro_habilidad` FOREIGN KEY (`hab_id`) REFERENCES `habilidades` (`hab_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_proyecto_miembro_proyecto` FOREIGN KEY (`pro_id`) REFERENCES `proyecto` (`pro_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_proyecto_miembro_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for proyecto_perfil
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_perfil`;
CREATE TABLE `proyecto_perfil`  (
  `prp_id` int NOT NULL AUTO_INCREMENT,
  `pro_id` int NOT NULL,
  `ppf_id` int NOT NULL,
  `prp_cupos` int NOT NULL DEFAULT 1,
  `prp_cupos_ocupados` int NOT NULL DEFAULT 0,
  `prp_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` bigint NULL DEFAULT NULL,
  `creado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` bigint NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`prp_id`) USING BTREE,
  UNIQUE INDEX `uq_proyecto_perfil`(`pro_id` ASC, `ppf_id` ASC) USING BTREE,
  INDEX `fk_prp_ppf`(`ppf_id` ASC) USING BTREE,
  CONSTRAINT `fk_prp_ppf` FOREIGN KEY (`ppf_id`) REFERENCES `perfil_profesional` (`ppf_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_prp_proyecto` FOREIGN KEY (`pro_id`) REFERENCES `proyecto` (`pro_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for proyecto_perfil_habilidad
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_perfil_habilidad`;
CREATE TABLE `proyecto_perfil_habilidad`  (
  `pph_id` int NOT NULL AUTO_INCREMENT,
  `prp_id` int NOT NULL,
  `hab_id` int NOT NULL,
  `pph_estado` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`pph_id`) USING BTREE,
  UNIQUE INDEX `uq_prp_hab`(`prp_id` ASC, `hab_id` ASC) USING BTREE,
  INDEX `fk_pph_hab`(`hab_id` ASC) USING BTREE,
  CONSTRAINT `fk_pph_hab` FOREIGN KEY (`hab_id`) REFERENCES `habilidades` (`hab_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_pph_prp` FOREIGN KEY (`prp_id`) REFERENCES `proyecto_perfil` (`prp_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for proyecto_postulacion
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_postulacion`;
CREATE TABLE `proyecto_postulacion`  (
  `ppo_id` int NOT NULL AUTO_INCREMENT,
  `pro_id` int NOT NULL,
  `usu_id` int NOT NULL,
  `hab_id` int NOT NULL,
  `ppo_estado` tinyint(1) NOT NULL DEFAULT 1,
  `ppo_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `creado_por` bigint NULL DEFAULT NULL,
  `creado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` bigint NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ppo_id`) USING BTREE,
  UNIQUE INDEX `pro_id`(`pro_id` ASC, `usu_id` ASC, `hab_id` ASC) USING BTREE,
  INDEX `usu_id`(`usu_id` ASC) USING BTREE,
  INDEX `hab_id`(`hab_id` ASC) USING BTREE,
  CONSTRAINT `fk_proyecto_postulacion_habilidad` FOREIGN KEY (`hab_id`) REFERENCES `habilidades` (`hab_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_proyecto_postulacion_proyecto` FOREIGN KEY (`pro_id`) REFERENCES `proyecto` (`pro_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_proyecto_postulacion_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for proyecto_conversacion
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_conversacion`;
CREATE TABLE `proyecto_conversacion`  (
  `pc_id` int NOT NULL AUTO_INCREMENT,
  `pro_id` int NOT NULL,
  `creador_id` int NOT NULL,
  `socio_id` int NOT NULL,
  `pc_estado` tinyint(1) NOT NULL DEFAULT 1,
  `pc_ultimo_mensaje` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `pc_ultimo_mensaje_tipo` tinyint(1) NULL DEFAULT NULL,
  `pc_ultimo_mensaje_fecha` datetime NULL DEFAULT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pc_id`) USING BTREE,
  UNIQUE INDEX `uq_conv_proyecto_usuario`(`pro_id` ASC, `creador_id` ASC, `socio_id` ASC) USING BTREE,
  INDEX `idx_conv_socio`(`socio_id` ASC) USING BTREE,
  INDEX `idx_conv_creador`(`creador_id` ASC) USING BTREE,
  CONSTRAINT `fk_conv_proyecto` FOREIGN KEY (`pro_id`) REFERENCES `proyecto` (`pro_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_conv_creador` FOREIGN KEY (`creador_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_conv_socio` FOREIGN KEY (`socio_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for proyecto_mensaje
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_mensaje`;
CREATE TABLE `proyecto_mensaje`  (
  `pmj_id` int NOT NULL AUTO_INCREMENT,
  `pc_id` int NOT NULL,
  `remitente_id` int NOT NULL,
  `pmj_tipo` tinyint(1) NOT NULL DEFAULT 1,
  `pmj_contenido` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `pmj_metadata` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `pmj_estado` tinyint(1) NOT NULL DEFAULT 1,
  `pmj_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pmj_id`) USING BTREE,
  INDEX `fk_msg_conv`(`pc_id` ASC) USING BTREE,
  INDEX `fk_msg_remitente`(`remitente_id` ASC) USING BTREE,
  CONSTRAINT `fk_msg_conversacion` FOREIGN KEY (`pc_id`) REFERENCES `proyecto_conversacion` (`pc_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_msg_usuario` FOREIGN KEY (`remitente_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for proyecto_mensaje_adjunto
-- ----------------------------
DROP TABLE IF EXISTS `proyecto_mensaje_adjunto`;
CREATE TABLE `proyecto_mensaje_adjunto`  (
  `pma_id` int NOT NULL AUTO_INCREMENT,
  `pmj_id` int NOT NULL,
  `pma_ruta` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pma_nombre_original` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `pma_mime` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `pma_peso` int NULL DEFAULT NULL,
  `pma_ancho` int NULL DEFAULT NULL,
  `pma_alto` int NULL DEFAULT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`pma_id`) USING BTREE,
  INDEX `fk_adjunto_mensaje`(`pmj_id` ASC) USING BTREE,
  CONSTRAINT `fk_adjunto_mensaje` FOREIGN KEY (`pmj_id`) REFERENCES `proyecto_mensaje` (`pmj_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for recuperacion_password
-- ----------------------------
DROP TABLE IF EXISTS `recuperacion_password`;
CREATE TABLE `recuperacion_password`  (
  `rec_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `rec_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rec_expira` datetime NOT NULL,
  `rec_estado` tinyint(1) NULL DEFAULT 0,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`rec_id`) USING BTREE,
  INDEX `fk_rec_usuario`(`usu_id` ASC) USING BTREE,
  INDEX `idx_rec_token`(`rec_token` ASC) USING BTREE,
  CONSTRAINT `fk_rec_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for rol
-- ----------------------------
DROP TABLE IF EXISTS `rol`;
CREATE TABLE `rol`  (
  `rol_id` int NOT NULL AUTO_INCREMENT,
  `rol_nombre` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rol_descripcion` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `rol_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`rol_id`) USING BTREE,
  UNIQUE INDEX `uq_rol_nombre`(`rol_nombre` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for rol_permiso
-- ----------------------------
DROP TABLE IF EXISTS `rol_permiso`;
CREATE TABLE `rol_permiso`  (
  `rp_id` int NOT NULL AUTO_INCREMENT,
  `rol_id` int NOT NULL,
  `per_id` int NOT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`rp_id`) USING BTREE,
  UNIQUE INDEX `uq_rol_permiso`(`rol_id` ASC, `per_id` ASC) USING BTREE,
  INDEX `fk_rp_permiso`(`per_id` ASC) USING BTREE,
  CONSTRAINT `fk_rp_permiso` FOREIGN KEY (`per_id`) REFERENCES `permiso` (`per_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_rp_rol` FOREIGN KEY (`rol_id`) REFERENCES `rol` (`rol_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for solicitudes_conexion
-- ----------------------------
-- DROP TABLE IF EXISTS `solicitudes_conexion`;
-- CREATE TABLE `solicitudes_conexion`  (
--   `sol_id` int NOT NULL AUTO_INCREMENT,
--   `sol_remitente_id` int NOT NULL,
--   `sol_receptor_id` int NOT NULL,
--   `sol_estado` enum('pendiente','aceptada','rechazada','cancelada') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pendiente',
--   `sol_fecha_respuesta` datetime NULL DEFAULT NULL,
--   `sol_eliminado` tinyint(1) NULL DEFAULT 0,
--   `sol_fecha_eliminacion` datetime NULL DEFAULT NULL,
--   `creado_por` int NULL DEFAULT NULL,
--   `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   `actualizado_por` int NULL DEFAULT NULL,
--   `actualizado_fecha` datetime NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
--   PRIMARY KEY (`sol_id`) USING BTREE,
--   UNIQUE INDEX `sol_remitente_id`(`sol_remitente_id` ASC, `sol_receptor_id` ASC) USING BTREE,
--   INDEX `creado_por`(`creado_por` ASC) USING BTREE,
--   INDEX `actualizado_por`(`actualizado_por` ASC) USING BTREE,
--   INDEX `sol_remitente_id_2`(`sol_remitente_id` ASC) USING BTREE,
--   INDEX `sol_receptor_id`(`sol_receptor_id` ASC) USING BTREE,
--   INDEX `sol_estado`(`sol_estado` ASC) USING BTREE,
--   CONSTRAINT `solicitudes_conexion_ibfk_1` FOREIGN KEY (`sol_remitente_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
--   CONSTRAINT `solicitudes_conexion_ibfk_2` FOREIGN KEY (`sol_receptor_id`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
--   CONSTRAINT `solicitudes_conexion_ibfk_3` FOREIGN KEY (`creado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
--   CONSTRAINT `solicitudes_conexion_ibfk_4` FOREIGN KEY (`actualizado_por`) REFERENCES `usuario` (`usu_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
-- ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for token_sesion
-- ----------------------------
DROP TABLE IF EXISTS `token_sesion`;
CREATE TABLE `token_sesion`  (
  `tok_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `dis_id` int NOT NULL,
  `tok_valor` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tok_expira` datetime NOT NULL,
  `tok_estado` tinyint(1) NOT NULL DEFAULT 1,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`tok_id`) USING BTREE,
  INDEX `fk_token_usuario`(`usu_id` ASC) USING BTREE,
  INDEX `fk_token_dispositivo`(`dis_id` ASC) USING BTREE,
  INDEX `idx_token_valor`(`tok_valor` ASC) USING BTREE,
  CONSTRAINT `fk_token_dispositivo` FOREIGN KEY (`dis_id`) REFERENCES `dispositivo` (`dis_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_token_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for usuario
-- ----------------------------
DROP TABLE IF EXISTS `usuario`;
CREATE TABLE `usuario`  (
  `usu_id` int NOT NULL AUTO_INCREMENT,
  `usu_email` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `usu_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `usu_estado` tinyint(1) NOT NULL DEFAULT 1,
  `usu_verificado` tinyint(1) NOT NULL DEFAULT 0,
  `usu_metodo_verificacion` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `usu_ultimo_login` datetime NULL DEFAULT NULL,
  `creado_por` int NULL DEFAULT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `actualizado_por` int NULL DEFAULT NULL,
  `actualizado_fecha` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`usu_id`) USING BTREE,
  UNIQUE INDEX `uq_usuario_email`(`usu_email` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Table structure for usuario_rol
-- ----------------------------
DROP TABLE IF EXISTS `usuario_rol`;
CREATE TABLE `usuario_rol`  (
  `ur_id` int NOT NULL AUTO_INCREMENT,
  `usu_id` int NOT NULL,
  `rol_id` int NOT NULL,
  `creado_fecha` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ur_id`) USING BTREE,
  UNIQUE INDEX `uq_usuario_rol`(`usu_id` ASC, `rol_id` ASC) USING BTREE,
  INDEX `fk_ur_rol`(`rol_id` ASC) USING BTREE,
  CONSTRAINT `fk_ur_rol` FOREIGN KEY (`rol_id`) REFERENCES `rol` (`rol_id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_ur_usuario` FOREIGN KEY (`usu_id`) REFERENCES `usuario` (`usu_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;
