-- https://www.phpmyadmin.net/
--
-- Servidor: 127.0.0.1
-- Tiempo de generación: 17-02-2026 a las 03:32:03
-- Versión del servidor: 10.4.32-MariaDB
-- Versión de PHP: 8.0.30

START TRANSACTION;



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

--
-- Estructura de tabla para la tabla `tbl_company_settings`
--

CREATE TABLE `tbl_company_settings` (
  `id` int(11) NOT NULL,
  `nombre_legal` varchar(255) DEFAULT NULL,
  `nombre_comercial` varchar(255) DEFAULT NULL,
  `id_fiscal` varchar(100) DEFAULT NULL,
  `direccion` text DEFAULT NULL,
  `telefono` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `sitio_web` varchar(255) DEFAULT NULL,
  `representante` varchar(255) DEFAULT NULL,
  `cargo_rep` varchar(150) DEFAULT NULL,
  `tel_rep` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `tbl_company_settings` (`id`, `nombre_legal`, `nombre_comercial`, `id_fiscal`, `direccion`, `telefono`, `email`, `sitio_web`, `representante`, `cargo_rep`, `tel_rep`, `created_at`, `updated_at`) VALUES
(1, 'Diplomados', 'Plataforma Diplomados', 'G1212121212', '10773 NW 58th ST, Doral FL 33178', '+584145365380', 'admin@plataformadiplomados.com', 'www.plataformadiplomados.com', 'JORGE PEREIRA', 'PROGRAMADOR', '+584145042125', '2026-02-12 15:54:22', '2026-02-12 15:58:10');

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

--
-- 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;

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

INSERT INTO `tbl_roles` (`id`, `role_key`, `name`, `description`, `level`, `status`, `created_at`, `updated_at`) VALUES
(1, 'ADMIN', 'Administrador', 'Control total, auditoría y configuración global del sistema', 10, 'ACTIVE', '2026-02-11 08:40:30', '2026-02-11 08:40:30'),
(2, 'FINANZAS', 'Finanzas', 'Gestión centralizada de cobros, validación de soportes y reportes financieros', 5, 'ACTIVE', '2026-02-11 08:40:30', '2026-02-11 08:40:30'),
(3, 'ACADEMIC', 'Académico', 'Administración de diplomados, cohortes, profesores y control de estudios', 5, 'ACTIVE', '2026-02-11 08:40:30', '2026-02-11 08:40:30'),
(4, 'OPERATOR', 'Operador', 'Administración de cartelera digital, noticias y soporte a inscripciones', 3, 'ACTIVE', '2026-02-11 08:40:30', '2026-02-11 08:40:30'),
(5, 'PARTICIPANT', 'Participante', 'Acceso a oferta académica, registro de pagos y estado de cuenta', 1, 'ACTIVE', '2026-02-11 08:40:30', '2026-02-11 08:40:30');

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

--
-- 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_user_remember_tokens`
--

CREATE TABLE `tbl_user_remember_tokens` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `token_hash` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `tbl_wa_manual_logs`
--

CREATE TABLE `tbl_wa_manual_logs` (
  `id` int(11) NOT NULL,
  `estudiante` varchar(255) DEFAULT NULL,
  `evento` varchar(50) DEFAULT NULL,
  `telefono` varchar(50) DEFAULT NULL,
  `fecha_envio` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `tbl_wa_manual_logs` (`id`, `estudiante`, `evento`, `telefono`, `fecha_envio`) VALUES
(1, 'Jorge Pereira', 'INSCRIPCION', '+584145365380', '2026-02-12 17:31:26'),
(2, 'Raiza Mileva', 'CONSTANCIA_ESTUDIO', '+584124571599', '2026-02-12 17:31:59'),
(3, 'Juan Pérez', 'CONSTANCIA_ESTUDIO', '+584145042125', '2026-02-13 16:39:33');

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

--
-- Estructura de tabla para la tabla `tbl_wa_manual_templates`
--

CREATE TABLE `tbl_wa_manual_templates` (
  `id` int(11) NOT NULL,
  `evento` varchar(50) NOT NULL,
  `mensaje` text DEFAULT NULL,
  `activo` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `tbl_wa_manual_templates` (`id`, `evento`, `mensaje`, `activo`) VALUES
(1, 'INSCRIPCION', 'Hola {NOMBRE}, tu inscripción al diplomado {DIPLOMADO} ha sido confirmada. ¡Nos vemos pronto! {URL_PORTAL}', 1),
(2, 'CONSTANCIA_ESTUDIO', 'Hola {NOMBRE}, tu constancia de estudio para {DIPLOMADO} ya está disponible aquí: {URL_PORTAL}', 1),
(3, 'CONSTANCIA_INSCRIPCION', 'Hola {NOMBRE}, adjuntamos tu constancia de inscripción al diplomado {DIPLOMADO}: {URL_PORTAL}', 1),
(4, 'PAGO', 'Hola {NOMBRE}, te recordamos que tienes un pago pendiente en el diplomado {DIPLOMADO}. Puedes gestionarlo aquí: {URL_PORTAL}', 1);

--
-- Indices de la tabla `tbl_company_settings`
--
ALTER TABLE `tbl_company_settings`
  ADD PRIMARY KEY (`id`);

--
-- 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_user_remember_tokens`
--
ALTER TABLE `tbl_user_remember_tokens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indices de la tabla `tbl_wa_manual_logs`
--
ALTER TABLE `tbl_wa_manual_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indices de la tabla `tbl_wa_manual_templates`
--
ALTER TABLE `tbl_wa_manual_templates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_evento_wa` (`evento`);

--
-- AUTO_INCREMENT de la tabla `tbl_company_settings`
--
ALTER TABLE `tbl_company_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- 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=6;

--
-- 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_user_remember_tokens`
--
ALTER TABLE `tbl_user_remember_tokens`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT de la tabla `tbl_wa_manual_logs`
--
ALTER TABLE `tbl_wa_manual_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT de la tabla `tbl_wa_manual_templates`
--
ALTER TABLE `tbl_wa_manual_templates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- 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_remember_tokens`
--
ALTER TABLE `tbl_user_remember_tokens`
  ADD CONSTRAINT `tbl_user_remember_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE;
COMMIT;

