-- https://www.phpmyadmin.net/
--
-- Servidor: 127.0.0.1
-- Tiempo de generación: 11-02-2026 a las 13:15:41
-- Versión del servidor: 10.4.32-MariaDB
-- Versión de PHP: 8.0.30

START TRANSACTION;



--
-- Base de datos: `db_diplomatic`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_event_catalog`
--

CREATE TABLE `tbl_event_catalog` (
  `id` int(10) UNSIGNED NOT NULL,
  `event_code` varchar(30) NOT NULL,
  `event_type` enum('SECURITY','ACADEMIC','FINANCIAL','SYSTEM','DOCUMENT') NOT NULL,
  `severity` enum('LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'LOW',
  `title` varchar(120) NOT NULL,
  `message_template` varchar(255) NOT NULL,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_event_logs`
--

CREATE TABLE `tbl_event_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `event_catalog_id` int(10) UNSIGNED NOT NULL,
  `event_code` varchar(30) NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `role_key` varchar(60) DEFAULT NULL,
  `session_id` int(10) UNSIGNED DEFAULT NULL,
  `module_key` varchar(60) DEFAULT NULL,
  `action_key` varchar(60) DEFAULT NULL,
  `result` enum('SUCCESS','FAIL') NOT NULL DEFAULT 'SUCCESS',
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `entity_table` varchar(80) DEFAULT NULL,
  `entity_id` bigint(20) UNSIGNED DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `occurred_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_permissions`
--

CREATE TABLE `tbl_permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `perm_key` varchar(80) NOT NULL,
  `module_key` varchar(60) NOT NULL,
  `action_key` varchar(60) NOT NULL,
  `description` varchar(255) NOT NULL,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_roles`
--

CREATE TABLE `tbl_roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `role_key` varchar(60) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `level` int(11) NOT NULL DEFAULT 1,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_role_permissions`
--

CREATE TABLE `tbl_role_permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `role_id` int(10) UNSIGNED NOT NULL,
  `permission_id` int(10) UNSIGNED NOT NULL,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_sessions`
--

CREATE TABLE `tbl_sessions` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `started_at` datetime NOT NULL DEFAULT current_timestamp(),
  `last_activity_at` datetime DEFAULT NULL,
  `ended_at` datetime DEFAULT NULL,
  `end_reason` enum('LOGOUT','TIMEOUT','FORCED','UNKNOWN') DEFAULT NULL,
  `status` enum('ACTIVE','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_users`
--

CREATE TABLE `tbl_users` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_type` enum('INTERNAL','PARTICIPANT') NOT NULL DEFAULT 'INTERNAL',
  `status` enum('ACTIVE','SUSPENDED','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(190) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `document_id` varchar(30) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `provenance` varchar(150) DEFAULT NULL,
  `undergraduate_degree` varchar(150) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT 'default_avatar.png',
  `password_hash` varchar(255) NOT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `role` varchar(255) DEFAULT 'user'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Volcado de datos para la tabla `tbl_users`
--

INSERT INTO `tbl_users` (`id`, `user_type`, `status`, `first_name`, `last_name`, `email`, `phone`, `document_id`, `address`, `provenance`, `undergraduate_degree`, `avatar`, `password_hash`, `last_login_at`, `created_at`, `updated_at`, `role`) VALUES
(1, 'INTERNAL', 'ACTIVE', 'ADMIN', 'GENERAL', 'admin@diplomatic.local', NULL, NULL, NULL, NULL, NULL, 'default_avatar.png', '$2y$10$00VC/qpQDbIDycCmDk0.Au4AEP7EfpgQ8J451Zs9YOvzdH4Vpty5K', NULL, '2026-02-07 17:08:04', '2026-02-08 01:48:48', 'ADMIN'),
(2, 'INTERNAL', 'ACTIVE', 'JORGE', 'PEREIRA', 'raizamileva@gmail.com', '', '14979523', 'Urb El Valle, Calle Principal, Casa Nro 23, Cabudare, Jose Gregorio Bastidas, Palavecino', 'Cabudare', 'Ingenieria', 'usr_1770810835.png', '$2y$10$Pa.672SkyV7tzG8yfpeI5uInUoOt7T/YKWdtVa.dfqqsKjjLFn8pG', NULL, '2026-02-08 11:35:40', '2026-02-11 07:54:16', 'ADMIN'),
(3, 'INTERNAL', 'ACTIVE', 'Luis', 'Olivares', 'jorgeluispereiraolivares@gmail.com', '', '149795231', '10773 NW 58th ST, Doral FL 33178', 'Barquisimeto', 'Ingenieria', 'usr_1770810903.png', '$2y$10$HI6YEnRVg21tKiaoj/1uaeFZaS4ruNvjM1SYcXfnbItRhpBMZXXtm', NULL, '2026-02-11 07:54:54', '2026-02-11 08:04:39', 'ADMIN'),
(4, 'INTERNAL', 'ACTIVE', 'Homer', 'Pereira', 'ddrgomes@gmail.com', '', '13567117', 'Urb Club Hipico Las Trinitarias, Res La Trinitaria Piso 4 Apartament 44', 'Barquisimeto', 'Ingenieria Quimica', 'usr_1770811774.png', '$2y$10$3BwNQJ7Ym4CqChkOZ1bTe.NfrBfO89YNFsjtdE5Q.EAqN3ab027p6', NULL, '2026-02-11 08:05:52', '2026-02-11 08:09:34', 'ADMIN');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `tbl_user_roles`
--

CREATE TABLE `tbl_user_roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `role_id` int(10) UNSIGNED NOT NULL,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `assigned_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `tbl_event_catalog`
--
ALTER TABLE `tbl_event_catalog`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_event_catalog_code` (`event_code`),
  ADD KEY `idx_event_catalog_type` (`event_type`),
  ADD KEY `idx_event_catalog_severity` (`severity`);

--
-- Indices de la tabla `tbl_event_logs`
--
ALTER TABLE `tbl_event_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_event_logs_code` (`event_code`),
  ADD KEY `idx_event_logs_user` (`user_id`),
  ADD KEY `idx_event_logs_session` (`session_id`),
  ADD KEY `idx_event_logs_occurred` (`occurred_at`),
  ADD KEY `idx_event_logs_entity` (`entity_table`,`entity_id`),
  ADD KEY `fk_event_logs_catalog` (`event_catalog_id`);

--
-- Indices de la tabla `tbl_permissions`
--
ALTER TABLE `tbl_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_permissions_key` (`perm_key`),
  ADD KEY `idx_permissions_module` (`module_key`),
  ADD KEY `idx_permissions_action` (`action_key`);

--
-- Indices de la tabla `tbl_roles`
--
ALTER TABLE `tbl_roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_roles_key` (`role_key`);

--
-- Indices de la tabla `tbl_role_permissions`
--
ALTER TABLE `tbl_role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_role_permission` (`role_id`,`permission_id`),
  ADD KEY `fk_role_permissions_permission` (`permission_id`);

--
-- Indices de la tabla `tbl_sessions`
--
ALTER TABLE `tbl_sessions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_sessions_token` (`session_token`),
  ADD KEY `idx_sessions_user` (`user_id`),
  ADD KEY `idx_sessions_status` (`status`);

--
-- Indices de la tabla `tbl_users`
--
ALTER TABLE `tbl_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_users_email` (`email`);

--
-- Indices de la tabla `tbl_user_roles`
--
ALTER TABLE `tbl_user_roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_user_role` (`user_id`,`role_id`),
  ADD KEY `fk_user_roles_role` (`role_id`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `tbl_event_catalog`
--
ALTER TABLE `tbl_event_catalog`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `tbl_event_logs`
--
ALTER TABLE `tbl_event_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `tbl_permissions`
--
ALTER TABLE `tbl_permissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `tbl_roles`
--
ALTER TABLE `tbl_roles`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `tbl_role_permissions`
--
ALTER TABLE `tbl_role_permissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `tbl_sessions`
--
ALTER TABLE `tbl_sessions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `tbl_users`
--
ALTER TABLE `tbl_users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT de la tabla `tbl_user_roles`
--
ALTER TABLE `tbl_user_roles`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `tbl_event_logs`
--
ALTER TABLE `tbl_event_logs`
  ADD CONSTRAINT `fk_event_logs_catalog` FOREIGN KEY (`event_catalog_id`) REFERENCES `tbl_event_catalog` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_event_logs_session` FOREIGN KEY (`session_id`) REFERENCES `tbl_sessions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_event_logs_user` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Filtros para la tabla `tbl_role_permissions`
--
ALTER TABLE `tbl_role_permissions`
  ADD CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `tbl_permissions` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `tbl_roles` (`id`) ON UPDATE CASCADE;

--
-- Filtros para la tabla `tbl_sessions`
--
ALTER TABLE `tbl_sessions`
  ADD CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON UPDATE CASCADE;

--
-- Filtros para la tabla `tbl_user_roles`
--
ALTER TABLE `tbl_user_roles`
  ADD CONSTRAINT `fk_user_roles_role` FOREIGN KEY (`role_id`) REFERENCES `tbl_roles` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_user_roles_user` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON UPDATE CASCADE;
COMMIT;

