-- Base de datos: `nucleo_base`
-- Versión optimizada y limpia - Estructura central del sistema

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- ========================================================
-- TABLAS DE INTELIGENCIA ARTIFICIAL
-- ========================================================

CREATE TABLE `ai_categories` (
  `ai_cat_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la categoría AI',
  `ai_cat_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre de la categoría (support, sales, inventory, other)',
  `ai_cat_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_cat_state` int DEFAULT '0' COMMENT 'Estado: 0=activo, 1=archivado, 2=eliminado',
  `ai_cat_create_user_id` int DEFAULT NULL COMMENT 'Usuario que creó el registro',
  PRIMARY KEY (`ai_cat_id`) USING BTREE,
  UNIQUE KEY `ai_cat_name` (`ai_cat_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Categorías para prompts de AI' AUTO_INCREMENT=5;

INSERT INTO `ai_categories` (`ai_cat_id`, `ai_cat_name`, `ai_cat_ent_id`, `ai_cat_state`, `ai_cat_create_user_id`) VALUES
(1, 'support', 1, 0, NULL),
(2, 'sales', 1, 0, NULL),
(3, 'inventory', 1, 0, NULL),
(4, 'other', 1, 0, NULL);

CREATE TABLE `ai_keys` (
  `ai_key_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la API key',
  `ai_key_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre identificador de la key',
  `ai_key_value` varbinary(255) NOT NULL COMMENT 'Valor encriptado de la API key',
  `ai_key_provider` enum('OpenAI','Azure','Google','Other') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'OpenAI' COMMENT 'Proveedor del servicio AI',
  `ai_key_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `ai_key_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `ai_key_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_key_state` int DEFAULT '0' COMMENT 'Estado: 0=activo, 1=archivado, 2=eliminado',
  `ai_key_create_user_id` int DEFAULT NULL COMMENT 'Usuario que creó el registro',
  PRIMARY KEY (`ai_key_id`) USING BTREE,
  UNIQUE KEY `ai_key_name` (`ai_key_name`) USING BTREE,
  KEY `idx_provider` (`ai_key_provider`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='API Keys para servicios de AI';

CREATE TABLE `ai_logs` (
  `ai_log_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del log',
  `ai_log_user_id` int DEFAULT NULL COMMENT 'ID del usuario que ejecutó',
  `ai_log_prompt_id` int NOT NULL COMMENT 'ID del prompt utilizado',
  `ai_log_response_id` int NOT NULL COMMENT 'ID de la respuesta generada',
  `ai_log_execution_time` float DEFAULT NULL COMMENT 'Tiempo de ejecución en segundos',
  `ai_log_status` enum('success','error','pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'success' COMMENT 'Estado de la ejecución',
  `ai_log_error_details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Detalles de error si falló',
  `ai_log_request_metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT='Metadata de la solicitud (JSON)',
  `ai_log_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación del log',
  `ai_log_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_log_state` int DEFAULT '0' COMMENT 'Estado: 0=activo, 1=archivado, 2=eliminado',
  PRIMARY KEY (`ai_log_id`) USING BTREE,
  KEY `idx_fechas` (`ai_log_created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Logs de ejecuciones de AI';

CREATE TABLE `ai_module_relations` (
  `ai_mod_rel_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la relación',
  `ai_mod_rel_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre de la relación',
  `ai_mod_rel_feature` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Característica/feature asociada',
  `ai_mod_rel_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `ai_mod_rel_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `ai_mod_rel_mod_id` int NOT NULL COMMENT 'ID del módulo relacionado',
  `ai_mod_rel_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_mod_rel_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT='Configuración adicional (JSON)',
  `ai_mod_rel_state` int DEFAULT '0' COMMENT 'Estado: 0=activo, 1=archivado, 2=eliminado',
  `ai_mod_rel_create_user_id` int DEFAULT NULL COMMENT 'Usuario que creó el registro',
  PRIMARY KEY (`ai_mod_rel_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Relaciones entre AI y módulos';

CREATE TABLE `ai_prompts` (
  `ai_prt_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del prompt',
  `ai_prt_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Texto del prompt/template',
  `ai_prt_cat_id` int NOT NULL COMMENT 'ID de la categoría',
  `ai_prt_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `ai_prt_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `ai_prt_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_prt_state` int DEFAULT '0' COMMENT 'Estado: 0=activo, 1=archivado, 2=eliminado',
  `ai_prt_create_user_id` int DEFAULT NULL COMMENT 'Usuario que creó el registro',
  PRIMARY KEY (`ai_prt_id`) USING BTREE,
  KEY `idx_entidad` (`ai_prt_ent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Prompts/Templates para AI' AUTO_INCREMENT=3;

INSERT INTO `ai_prompts` (`ai_prt_id`, `ai_prt_text`, `ai_prt_cat_id`, `ai_prt_created_at`, `ai_prt_updated_at`, `ai_prt_ent_id`, `ai_prt_state`, `ai_prt_create_user_id`) VALUES
(1, 'Actúa como un escritor de un periódico líder en periodismo digital...', 0, '2025-03-20 00:13:51', '2025-03-20 21:06:16', 1, 1, 1),
(2, 'Actúa como un periodista de un medio digital de primer nivel...', 0, '2025-03-20 16:41:24', '2025-03-20 21:05:48', 1, 1, 1);

CREATE TABLE `ai_responses` (
  `ai_rsp_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la respuesta',
  `ai_rsp_prompt_id` int NOT NULL COMMENT 'ID del prompt utilizado',
  `ai_rsp_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Texto de la respuesta generada',
  `ai_rsp_model_used` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'GPT-4' COMMENT 'Modelo AI utilizado',
  `ai_rsp_confidence_score` decimal(5,2) DEFAULT NULL COMMENT 'Puntuación de confianza (0-100)',
  `ai_rsp_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `ai_rsp_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `ai_rsp_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_rsp_state` int DEFAULT '0' COMMENT 'Estado: 0=activo, 1=archivado, 2=eliminado',
  `ai_rsp_create_user_id` int DEFAULT NULL COMMENT 'Usuario que creó el registro',
  PRIMARY KEY (`ai_rsp_id`) USING BTREE,
  KEY `idx_prompt` (`ai_rsp_prompt_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Respuestas generadas por AI';

CREATE TABLE `ai_responses_history` (
  `ai_rsp_id` int NOT NULL COMMENT 'ID de la respuesta',
  `ai_rsp_prompt_id` int NOT NULL COMMENT 'ID del prompt',
  `ai_rsp_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Texto de la respuesta',
  `ai_rsp_model_used` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'GPT-4' COMMENT 'Modelo utilizado',
  `ai_rsp_confidence_score` decimal(5,2) DEFAULT NULL COMMENT 'Puntuación de confianza',
  `ai_rsp_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación original',
  `ai_rsp_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `ai_rsp_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `ai_rsp_state` int DEFAULT '0' COMMENT 'Estado',
  `operation_type` enum('INSERT','UPDATE','DELETE') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Tipo de operación',
  `operation_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de la operación',
  PRIMARY KEY (`ai_rsp_id`) USING BTREE,
  KEY `idx_prompt_id` (`ai_rsp_prompt_id`) USING BTREE,
  KEY `idx_ent_id` (`ai_rsp_ent_id`) USING BTREE,
  KEY `idx_operation_time` (`operation_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Historial de cambios en respuestas AI';

-- ========================================================
-- TABLAS DE ESTRUCTURA (Blocks, Categories, Contents)
-- ========================================================

CREATE TABLE `blocks` (
  `block_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del bloque',
  `block_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre del bloque',
  `block_class` varchar(44) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Clase CSS del bloque',
  `block_order` int DEFAULT NULL COMMENT 'Orden de visualización',
  `block_parent_id` int NOT NULL DEFAULT '0' COMMENT 'ID del bloque padre',
  `block_ent_id` int NOT NULL DEFAULT '0' COMMENT 'ID de la entidad',
  `block_state` int NOT NULL DEFAULT '0' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`block_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Bloques de estructura de página' AUTO_INCREMENT=10;

INSERT INTO `blocks` (`block_id`, `block_name`, `block_class`, `block_order`, `block_parent_id`, `block_ent_id`, `block_state`) VALUES
(1, 'Header', 'headerPage', 1, 0, 1, 1),
(2, 'Body', 'bodyPage', 2, 0, 1, 1),
(3, 'Footer', 'footerPage', 3, 0, 1, 1),
(4, 'Aside', 'asidePage', 2, 2, 1, 0),
(9, 'Aside Fluid', 'asideFluid', 1, 4, 1, 0);

CREATE TABLE `categorys` (
  `cat_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la categoría',
  `cat_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Nombre de la categoría',
  `cat_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Path/URL amigable',
  `cat_pathurl` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'URL completa',
  `cat_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Descripción de la categoría',
  `cat_order` int DEFAULT NULL COMMENT 'Orden de visualización',
  `cat_parent_id` int DEFAULT '0' COMMENT 'ID de la categoría padre',
  `cat_related` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Categorías relacionadas',
  `cat_icon` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Icono de la categoría',
  `cat_color` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Color de la categoría',
  `cat_img` int DEFAULT NULL COMMENT 'ID de la imagen',
  `cat_banner` int DEFAULT NULL COMMENT 'ID del banner',
  `cat_ent_id` int DEFAULT NULL COMMENT 'ID de la entidad',
  `cat_configpath` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Path de configuración',
  `cat_cls` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Clase CSS adicional',
  `cat_json` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Configuración JSON',
  `cat_state_collapse` int NOT NULL DEFAULT '1' COMMENT 'Estado colapsado: 0=no, 1=sí',
  `cat_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `cat_state` int NOT NULL DEFAULT '0' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`cat_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Categorías del sistema' AUTO_INCREMENT=8;

INSERT INTO `categorys` (`cat_id`, `cat_name`, `cat_path`, `cat_pathurl`, `cat_description`, `cat_order`, `cat_parent_id`, `cat_related`, `cat_icon`, `cat_color`, `cat_img`, `cat_banner`, `cat_ent_id`, `cat_configpath`, `cat_cls`, `cat_json`, `cat_state_collapse`, `cat_site_id`, `cat_state`) VALUES
(1, 'Home', 'home', 'home', '', 0, 0, '', '', '', 0, 0, 1, '', '', '', 1, 1, 1),
(2, 'Productos', 'productos', 'productos', '', 1, 0, '', '', '', 0, 0, 1, '', '', '', 1, 1, 1),
(3, 'Contact', 'contact', 'contact', 'Contact category', 4, 0, '', '', '', 0, 0, 1, '', '', '', 1, 1, 1);

CREATE TABLE `categorys_files` (
  `cat_file_file_id` int NOT NULL COMMENT 'ID del archivo',
  `cat_file_cat_id` int NOT NULL COMMENT 'ID de la categoría',
  `cat_file_order` int NOT NULL COMMENT 'Orden del archivo',
  PRIMARY KEY (`cat_file_file_id`,`cat_file_cat_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Relación categorías-archivos';

CREATE TABLE `contents` (
  `cont_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del contenido',
  `cont_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Título del contenido',
  `cont_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Descripción corta',
  `cont_text` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Texto/contenido completo',
  `cont_order` int DEFAULT NULL COMMENT 'Orden de visualización',
  `cont_cat_id` int DEFAULT NULL COMMENT 'ID de la categoría',
  `cont_ent_id` int DEFAULT NULL COMMENT 'ID de la entidad',
  `cont_state` int NOT NULL DEFAULT '0' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`cont_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Contenidos del sistema';

CREATE TABLE `files` (
  `file_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del archivo',
  `file_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre del archivo',
  `file_description` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Descripción del archivo',
  `file_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Path del archivo',
  `file_url` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'URL del archivo',
  `file_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Tipo MIME del archivo',
  `file_format` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Formato (jpg, pdf, etc)',
  `file_target` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '_self' COMMENT 'Target del enlace (_self, _blank)',
  `file_dimensions` varchar(145) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Dimensiones (para imágenes)',
  `file_size` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Tamaño del archivo',
  `file_hash` binary(255) DEFAULT NULL COMMENT 'Hash del archivo',
  `file_date` timestamp NULL DEFAULT NULL COMMENT 'Fecha del archivo',
  `file_cat_id` int DEFAULT NULL COMMENT 'ID de la categoría',
  `file_ent_id` int DEFAULT NULL COMMENT 'ID de la entidad',
  `file_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `file_state` int NOT NULL DEFAULT '1' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`file_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Archivos y documentos';

-- ========================================================
-- TABLAS DE FORMULARIOS
-- ========================================================

CREATE TABLE `forms` (
  `form_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del formulario',
  `form_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Título del formulario',
  `form_slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Slug/identificador URL',
  `form_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Descripción del formulario',
  `form_settings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Configuración JSON (confirmación, notificaciones, etc)',
  `form_notify_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Email para notificaciones',
  `form_user_id` bigint UNSIGNED DEFAULT NULL COMMENT 'ID del usuario creador',
  `form_ent_id` int UNSIGNED DEFAULT NULL COMMENT 'ID de la entidad',
  `form_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `form_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `form_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `form_status` int DEFAULT '1' COMMENT 'Estado: 1=activo, 0=inactivo',
  PRIMARY KEY (`form_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Definición de formularios (Maestro)' AUTO_INCREMENT=2;

INSERT INTO `forms` (`form_id`, `form_title`, `form_slug`, `form_description`, `form_settings`, `form_notify_email`, `form_user_id`, `form_ent_id`, `form_site_id`, `form_created_at`, `form_updated_at`, `form_status`) VALUES
(1, 'Formulario de contacto', 'contacto', 'Formulario de contacto básico', NULL, NULL, NULL, 1, 1, '2025-12-09 19:12:13', '2025-12-09 19:12:13', 1);

CREATE TABLE `forms_entries` (
  `form_entry_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del envío',
  `form_entry_form_id` bigint UNSIGNED NOT NULL COMMENT 'ID del formulario',
  `form_entry_ent_id` int UNSIGNED DEFAULT NULL COMMENT 'ID de la entidad',
  `form_entry_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `form_entry_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha del envío',
  `form_entry_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'IP del remitente',
  `form_entry_user_id` bigint UNSIGNED DEFAULT NULL COMMENT 'ID del usuario (si aplica)',
  `form_entry_meta` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Metadata adicional (JSON)',
  `form_entry_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `form_entry_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `form_entry_status` int DEFAULT '1' COMMENT 'Estado: 1=activo, 0=eliminado',
  PRIMARY KEY (`form_entry_id`) USING BTREE,
  KEY `idx_form_id` (`form_entry_form_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Entradas/envíos de formularios';

CREATE TABLE `forms_fields` (
  `form_field_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del campo',
  `form_field_form_id` bigint UNSIGNED NOT NULL COMMENT 'ID del formulario padre',
  `form_field_type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Tipo de campo (text, email, select, etc)',
  `form_field_label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Etiqueta del campo',
  `form_field_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre del campo (name attribute)',
  `form_field_placeholder` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Placeholder',
  `form_field_required` tinyint(1) DEFAULT '0' COMMENT '¿Es requerido? 0=no, 1=sí',
  `form_field_options` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Opciones para selects/radios (JSON)',
  `form_field_validation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Reglas de validación',
  `form_field_order` int DEFAULT '0' COMMENT 'Orden del campo',
  `form_field_status` int DEFAULT '1' COMMENT 'Estado: 1=activo, 0=inactivo',
  PRIMARY KEY (`form_field_id`) USING BTREE,
  KEY `idx_form_id` (`form_field_form_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Campos de formularios';

-- ========================================================
-- TABLAS DE USUARIOS Y ROLES
-- ========================================================

CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del usuario',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre completo',
  `user_nick` varchar(155) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nickname/apodo',
  `user_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Email del usuario',
  `user_password` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Contraseña hasheada',
  `user_img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Imagen de perfil',
  `user_type` int DEFAULT '0' COMMENT 'Tipo de usuario',
  `user_rol_id` int NOT NULL DEFAULT '0' COMMENT 'ID del rol principal',
  `user_ent_id` int NOT NULL DEFAULT '0' COMMENT 'ID de la entidad',
  `user_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `user_last_access` datetime DEFAULT NULL COMMENT 'Último acceso',
  `user_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `user_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  `user_state` int NOT NULL DEFAULT '0' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE KEY `user_email` (`user_email`) USING BTREE,
  KEY `idx_rol` (`user_rol_id`) USING BTREE,
  KEY `idx_entidad` (`user_ent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Usuarios del sistema';

CREATE TABLE `users_firms` (
  `user_firm_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único',
  `user_firm_user_id` int NOT NULL COMMENT 'ID del usuario',
  `user_firm_img` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Imagen de firma',
  `user_firm_reference` int DEFAULT NULL COMMENT 'Referencia',
  `user_firm_md5` int DEFAULT NULL COMMENT 'Hash MD5',
  `user_firm_state` int NOT NULL DEFAULT '0' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`user_firm_id`,`user_firm_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Firmas de usuarios';

CREATE TABLE `users_groups` (
  `user_group_user_id` int NOT NULL COMMENT 'ID del usuario',
  `user_group_group_id` int NOT NULL COMMENT 'ID del grupo',
  `user_group_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `user_group_order` int NOT NULL COMMENT 'Orden',
  PRIMARY KEY (`user_group_user_id`,`user_group_group_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Relación usuarios-grupos';

CREATE TABLE `users_roles` (
  `user_rol_user_id` int NOT NULL COMMENT 'ID del usuario',
  `user_rol_rol_id` int NOT NULL COMMENT 'ID del rol',
  `user_rol_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `user_rol_order` int NOT NULL COMMENT 'Orden',
  PRIMARY KEY (`user_rol_user_id`,`user_rol_rol_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Relación usuarios-roles (roles secundarios)';

INSERT INTO `users_roles` (`user_rol_user_id`, `user_rol_rol_id`, `user_rol_ent_id`, `user_rol_order`) VALUES
(1, 1, 1, 0),
(3, 10, 1, 1),
(4, 10, 1, 1);

CREATE TABLE `users_tokens` (
  `user_tk_user_id` bigint NOT NULL COMMENT 'ID del usuario',
  `user_tk_type` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Tipo de token (access_token, refresh_token)',
  `user_tk_token` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Token',
  `user_tk_expires_in` datetime DEFAULT NULL COMMENT 'Fecha de expiración',
  `user_tk_date` datetime DEFAULT NULL COMMENT 'Fecha de creación',
  `user_tk_dates_browser` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Info del navegador',
  PRIMARY KEY (`user_tk_type`,`user_tk_token`) USING BTREE,
  KEY `idx_user_id` (`user_tk_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Tokens de sesión de usuarios';

INSERT INTO `users_tokens` (`user_tk_user_id`, `user_tk_type`, `user_tk_token`, `user_tk_expires_in`, `user_tk_date`, `user_tk_dates_browser`) VALUES
(1, 'access_token', 'ZXlKaGJHY2lPaUpJVXpJMU5pSXNJblI1Y0NJNklrcFhWQ0o5LWdlZGVvbi4xLjEuMC40MjcyODAwMCAxNzYxMTYxNTA5NjhmOTMxMjU2ODUyMTkuMjE1MjM4NTY=', NULL, '2025-10-22 15:31:49', 'Google Chrome,141.0.0.0,Mac Os:Mozilla/5.0...'),
(1, 'access_token', 'ZXlKaGJHY2lPaUpJVXpJMU5pSXNJblI1Y0NJNklrcFhWQ0o5LWdlZGVvbi4xLjEuMC40MTg4MjQwMCAxNzMxNTk3MzU3NjczNjE0MmQ2NjQxMzkuNDc3NTQ1NzY=', NULL, '2024-11-14 11:15:57', 'Google Chrome,130.0.0.0,Windows:Mozilla/5.0...');

-- ========================================================
-- TABLAS DE ENTIDADES Y EMPRESAS
-- ========================================================

CREATE TABLE `entities` (
  `ent_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la entidad',
  `ent_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre de la entidad',
  `ent_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Descripción',
  `ent_nick` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Nickname/código corto',
  `ent_type` int DEFAULT '0' COMMENT 'Tipo de entidad',
  `ent_rfc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'RFC (México)',
  `ent_address` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Dirección',
  `ent_phone` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Teléfono',
  `ent_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Email principal',
  `ent_logo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Logo de la entidad',
  `ent_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `ent_state` int NOT NULL DEFAULT '1' COMMENT 'Estado: 0=inactivo, 1=activo',
  `ent_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `ent_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  PRIMARY KEY (`ent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Entidades/Empresas del sistema' AUTO_INCREMENT=2;

INSERT INTO `entities` (`ent_id`, `ent_name`, `ent_description`, `ent_nick`, `ent_type`, `ent_rfc`, `ent_address`, `ent_phone`, `ent_email`, `ent_logo`, `ent_site_id`, `ent_state`, `ent_created_at`, `ent_updated_at`) VALUES
(1, 'Empresa Demo', 'Entidad de prueba', 'demo', 1, 'XXXXXXXXXXXXX', 'Calle Demo 123', '555-555-5555', 'demo@empresa.com', NULL, 1, 1, '2024-01-01 00:00:00', '2024-01-01 00:00:00');

-- ========================================================
-- TABLAS DE PUBLICACIONES Y NOTICIAS
-- ========================================================

CREATE TABLE `posts` (
  `post_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del post',
  `post_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Título del post',
  `post_slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Slug URL-friendly',
  `post_extract` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Extracto/copete',
  `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Contenido completo',
  `post_cover` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Imagen de portada',
  `post_author_id` int DEFAULT NULL COMMENT 'ID del autor',
  `post_author_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Nombre del autor',
  `post_cat_id` int DEFAULT NULL COMMENT 'ID de la categoría',
  `post_ent_id` int DEFAULT NULL COMMENT 'ID de la entidad',
  `post_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `post_order` int DEFAULT '0' COMMENT 'Orden de visualización',
  `post_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'post' COMMENT 'Tipo: post, page, etc',
  `post_status` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'draft' COMMENT 'Estado: draft, published, archived',
  `post_publish_date` datetime DEFAULT NULL COMMENT 'Fecha de publicación',
  `post_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `post_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  PRIMARY KEY (`post_id`) USING BTREE,
  KEY `idx_slug` (`post_slug`) USING BTREE,
  KEY `idx_status` (`post_status`) USING BTREE,
  KEY `idx_cat` (`post_cat_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Publicaciones/Posts del sistema';

-- ========================================================
-- TABLAS DE LOGS Y NOTIFICACIONES
-- ========================================================

CREATE TABLE `logs` (
  `log_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del log',
  `log_user_id` int NOT NULL COMMENT 'ID del usuario',
  `log_ent_id` int NOT NULL COMMENT 'ID de la entidad',
  `log_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `log_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'info' COMMENT 'Tipo: info, warning, error, success',
  `log_action` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Acción realizada',
  `log_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Descripción detallada',
  `log_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'IP del usuario',
  `log_browser` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Navegador',
  `log_referer` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'URL referer',
  `log_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Datos adicionales (JSON)',
  `log_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha del evento',
  `log_state` int DEFAULT '1' COMMENT 'Estado: 1=activo, 0=eliminado',
  PRIMARY KEY (`log_id`) USING BTREE,
  KEY `idx_user` (`log_user_id`) USING BTREE,
  KEY `idx_type` (`log_type`) USING BTREE,
  KEY `idx_date` (`log_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Registro de actividad/logs del sistema';

CREATE TABLE `notifications` (
  `not_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único de la notificación',
  `not_user_id` int NOT NULL COMMENT 'ID del usuario destinatario',
  `not_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'info' COMMENT 'Tipo: info, warning, success, error',
  `not_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Título',
  `not_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Mensaje',
  `not_link` varchar(455) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'URL de acción',
  `not_icon` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Icono',
  `not_is_read` tinyint(1) DEFAULT '0' COMMENT '¿Leído? 0=no, 1=sí',
  `not_ent_id` int DEFAULT NULL COMMENT 'ID de la entidad',
  `not_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `not_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `not_read_at` datetime DEFAULT NULL COMMENT 'Fecha de lectura',
  PRIMARY KEY (`not_id`) USING BTREE,
  KEY `idx_user` (`not_user_id`) USING BTREE,
  KEY `idx_read` (`not_is_read`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Notificaciones del sistema';

-- ========================================================
-- TABLAS DE CONFIGURACIÓN Y SITIOS
-- ========================================================

CREATE TABLE `settings` (
  `set_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único',
  `set_name` varchar(145) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre de la configuración',
  `set_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Valor de la configuración',
  `set_type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'general' COMMENT 'Tipo/grupo de configuración',
  `set_ent_id` int DEFAULT NULL COMMENT 'ID de la entidad (NULL=global)',
  `set_site_id` int NOT NULL DEFAULT '1' COMMENT 'ID del sitio',
  `set_state` int DEFAULT '1' COMMENT 'Estado: 0=inactivo, 1=activo',
  PRIMARY KEY (`set_id`) USING BTREE,
  UNIQUE KEY `idx_name_ent` (`set_name`,`set_ent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Configuraciones del sistema';

CREATE TABLE `sites` (
  `site_id` int NOT NULL AUTO_INCREMENT COMMENT 'Identificador único del sitio',
  `site_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Nombre del sitio',
  `site_slug` varchar(155) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Slug identificador',
  `site_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 'Descripción',
  `site_domain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Dominio principal',
  `site_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'URL base',
  `site_logo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Logo del sitio',
  `site_favicon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Favicon',
  `site_theme` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'default' COMMENT 'Tema activo',
  `site_language` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'es' COMMENT 'Idioma por defecto',
  `site_timezone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'America/Mexico_City' COMMENT 'Zona horaria',
  `site_maintenance` tinyint(1) DEFAULT '0' COMMENT 'Modo mantenimiento: 0=no, 1=sí',
  `site_state` int DEFAULT '1' COMMENT 'Estado: 0=inactivo, 1=activo',
  `site_created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de creación',
  `site_updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Fecha de última actualización',
  PRIMARY KEY (`site_id`) USING BTREE,
  UNIQUE KEY `site_slug` (`site_slug`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci COMMENT='Sitios del sistema' AUTO_INCREMENT=2;

INSERT INTO `sites` (`site_id`, `site_name`, `site_slug`, `site_description`, `site_domain`, `site_url`, `site_logo`, `site_favicon`, `site_theme`, `site_language`, `site_timezone`, `site_maintenance`, `site_state`, `site_created_at`, `site_updated_at`) VALUES
(1, 'Sitio Principal', 'main', 'Sitio web principal del sistema', 'localhost', 'http://localhost', NULL, NULL, 'default', 'es', 'America/Mexico_City', 0, 1, '2024-01-01 00:00:00', '2024-01-01 00:00:00');