-- phpMyAdmin SQL Dump
-- version 5.2.1deb3
-- https://www.phpmyadmin.net/
--
-- Servidor: localhost:3306
-- Tiempo de generación: 11-03-2026 a las 06:11:27
-- Versión del servidor: 10.11.14-MariaDB-0ubuntu0.24.04.1-log
-- Versión de PHP: 8.3.6

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de datos: `intranet`
--

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

--
-- Estructura de tabla para la tabla `ad_unlock_dept_log`
--

CREATE TABLE `ad_unlock_dept_log` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `unlocked_at` datetime NOT NULL DEFAULT current_timestamp(),
  `dept_cn` varchar(128) NOT NULL,
  `target_username` varchar(128) NOT NULL,
  `target_dn` varchar(512) DEFAULT NULL,
  `unlocked_by` varchar(128) NOT NULL,
  `unlocked_by_name` varchar(128) DEFAULT NULL,
  `ip` varchar(64) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `result` enum('success','error') NOT NULL,
  `error_msg` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `apps_user_year`
--

CREATE TABLE `apps_user_year` (
  `year` int(11) NOT NULL,
  `app_id` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `last_click_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `apps_year`
--

CREATE TABLE `apps_year` (
  `year` int(11) NOT NULL,
  `app_id` varchar(100) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `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 `compras_acquisitions`
--

CREATE TABLE `compras_acquisitions` (
  `id` int(11) NOT NULL,
  `request_date` date NOT NULL,
  `sector_id` int(11) NOT NULL,
  `subsector` varchar(100) DEFAULT NULL,
  `justification` text NOT NULL,
  `sector_manager_name` varchar(255) NOT NULL,
  `it_manager_name` varchar(255) DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `approximate_amount` decimal(10,2) NOT NULL,
  `articles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`articles`)),
  `signed_doc_filename` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `compras_document_sequences`
--

CREATE TABLE `compras_document_sequences` (
  `date_key` date NOT NULL,
  `sequence_number` int(11) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `entregas_afiliados`
--

CREATE TABLE `entregas_afiliados` (
  `DNI` varchar(20) NOT NULL,
  `Nombre` varchar(100) NOT NULL,
  `FechaAlta` date NOT NULL,
  `Importe` varchar(5) NOT NULL,
  `PagoEspecial` varchar(20) NOT NULL,
  `Empresa` varchar(150) NOT NULL,
  `Usuario` varchar(25) NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `entregas_entregas`
--

CREATE TABLE `entregas_entregas` (
  `ID` int(11) NOT NULL,
  `DNI` varchar(20) NOT NULL,
  `Objeto` varchar(100) NOT NULL,
  `FechaEntrega` date NOT NULL,
  `PersonaEntrega` varchar(50) NOT NULL,
  `Observaciones` text DEFAULT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura Stand-in para la vista `entregas_entregas_recientes`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `entregas_entregas_recientes` (
`ID` int(11)
,`DNI` varchar(20)
,`Objeto` varchar(100)
,`FechaEntrega` date
,`PersonaEntrega` varchar(50)
,`Observaciones` text
,`Timestamp` timestamp
);

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

--
-- Estructura Stand-in para la vista `entregas_entrgeasExt`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `entregas_entrgeasExt` (
`dni` varchar(20)
,`nombre` varchar(100)
,`objeto` varchar(100)
,`fechaEntrega` date
,`PersonaEntrega` varchar(50)
,`observaciones` text
);

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

--
-- Estructura Stand-in para la vista `entregas_estadisticas`
-- (Véase abajo para la vista actual)
--
CREATE TABLE `entregas_estadisticas` (
`objeto` varchar(100)
,`fechaentrega` date
,`count(*)` bigint(21)
);

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

--
-- Estructura de tabla para la tabla `entregas_objetos`
--

CREATE TABLE `entregas_objetos` (
  `Objeto` varchar(100) NOT NULL,
  `FechaAlta` date NOT NULL,
  `FechaBaja` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `flota_documentos`
--

CREATE TABLE `flota_documentos` (
  `id` int(11) NOT NULL,
  `vehiculo_id` int(11) NOT NULL,
  `nombre_archivo` varchar(255) NOT NULL,
  `categoria` enum('Seguro','ITV','Ficha Técnica','Permiso Circulación','Otros') NOT NULL,
  `ruta_path` varchar(500) NOT NULL,
  `fecha_vencimiento` date DEFAULT NULL,
  `fecha_subida` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Estructura de tabla para la tabla `flota_reservas`
--

CREATE TABLE `flota_reservas` (
  `id` int(11) NOT NULL,
  `vehiculo_id` int(11) NOT NULL,
  `sector_id` int(11) NOT NULL,
  `fecha` date NOT NULL,
  `conductor` varchar(100) DEFAULT NULL,
  `destino` varchar(255) DEFAULT NULL,
  `km_salida` int(11) DEFAULT NULL,
  `km_llegada` int(11) DEFAULT NULL,
  `observaciones` text DEFAULT NULL,
  `estado` enum('reservado','liberado','cancelado') NOT NULL DEFAULT 'reservado',
  `ubicacion_inicio` varchar(255) DEFAULT NULL,
  `ubicacion_fin` varchar(255) DEFAULT NULL,
  `fecha_liberacion` datetime DEFAULT NULL,
  `motivo_cancelacion` varchar(255) DEFAULT NULL,
  `fecha_cancelacion` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Estructura de tabla para la tabla `flota_reserva_documentos`
--

CREATE TABLE `flota_reserva_documentos` (
  `id` int(11) NOT NULL,
  `prestamo_id` int(11) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `file_size` int(11) DEFAULT NULL,
  `sha256` char(64) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `flota_vehiculos`
--

CREATE TABLE `flota_vehiculos` (
  `id` int(11) NOT NULL,
  `matricula` varchar(15) NOT NULL,
  `alias` varchar(50) DEFAULT NULL,
  `tipo` enum('coche','moto') NOT NULL,
  `marca` varchar(50) DEFAULT NULL,
  `modelo` varchar(50) DEFAULT NULL,
  `bastidor` varchar(50) DEFAULT NULL,
  `combustible` enum('Diesel','Gasolina','Hibrido','Electrico','Otros') DEFAULT 'Diesel',
  `km_actual` int(11) DEFAULT 0,
  `ubicacion_actual` varchar(255) DEFAULT NULL,
  `km_proxima_revision` int(11) DEFAULT 15000,
  `fecha_matriculacion` date DEFAULT NULL,
  `propietario` varchar(100) DEFAULT NULL,
  `observaciones` text DEFAULT NULL,
  `estado` enum('activo','taller','baja') DEFAULT 'activo',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Estructura de tabla para la tabla `inc_attachments`
--

CREATE TABLE `inc_attachments` (
  `id` int(11) NOT NULL,
  `incident_id` int(11) NOT NULL,
  `uploader` varchar(64) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `size_bytes` bigint(20) NOT NULL,
  `sha256` char(64) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `inc_categories`
--

CREATE TABLE `inc_categories` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 100,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `inc_comments`
--

CREATE TABLE `inc_comments` (
  `id` int(11) NOT NULL,
  `incident_id` int(11) NOT NULL,
  `author` varchar(64) NOT NULL,
  `body` mediumtext NOT NULL,
  `is_internal` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `inc_department_emails`
--

CREATE TABLE `inc_department_emails` (
  `id` int(11) NOT NULL,
  `department` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `name` varchar(120) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `inc_history`
--

CREATE TABLE `inc_history` (
  `id` int(11) NOT NULL,
  `incident_id` int(11) NOT NULL,
  `actor` varchar(64) NOT NULL,
  `action` varchar(50) NOT NULL,
  `from_value` varchar(255) DEFAULT NULL,
  `to_value` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `inc_incidents`
--

CREATE TABLE `inc_incidents` (
  `id` int(11) NOT NULL,
  `code` varchar(30) NOT NULL,
  `department` varchar(100) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `description` mediumtext NOT NULL,
  `status` enum('abierta','en_progreso','resuelta','cerrada') NOT NULL DEFAULT 'abierta',
  `priority` enum('baja','media','alta','critica') NOT NULL DEFAULT 'media',
  `created_by` varchar(64) NOT NULL,
  `created_email` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `assigned_to` varchar(64) DEFAULT NULL,
  `last_activity_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `inc_notifications`
--

CREATE TABLE `inc_notifications` (
  `id` int(11) NOT NULL,
  `recipient` varchar(64) NOT NULL,
  `incident_id` int(11) NOT NULL,
  `kind` enum('admin_update','admin_edit','admin_comment') NOT NULL DEFAULT 'admin_update',
  `message` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `read_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `intranet_apps`
--

CREATE TABLE `intranet_apps` (
  `id` int(11) NOT NULL,
  `app_id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `url` varchar(500) NOT NULL,
  `group_name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `icon` varchar(1024) DEFAULT NULL,
  `image` varchar(500) DEFAULT NULL,
  `new_tab` tinyint(1) NOT NULL DEFAULT 0,
  `roles_json` text NOT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Estructura de tabla para la tabla `intranet_catalog_groups`
--

CREATE TABLE `intranet_catalog_groups` (
  `id` int(11) NOT NULL,
  `group_type` varchar(32) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  `icon_svg` mediumtext DEFAULT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Estructura de tabla para la tabla `intranet_user_preferences`
--

CREATE TABLE `intranet_user_preferences` (
  `username` varchar(100) NOT NULL,
  `theme_mode` varchar(10) NOT NULL DEFAULT 'light',
  `menu_layout` varchar(20) NOT NULL DEFAULT 'top',
  `session_timeout_minutes` int unsigned NOT NULL DEFAULT 30,
  `home_frequent_limit` int unsigned NOT NULL DEFAULT 10,
  `home_frequent_app_bg` varchar(7) NOT NULL DEFAULT '#eaf8f0',
  `home_frequent_link_bg` varchar(7) NOT NULL DEFAULT '#eef5ff',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

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

--
-- Estructura de tabla para la tabla `intranet_links`
--

CREATE TABLE `intranet_links` (
  `id` int(11) NOT NULL,
  `link_type` enum('links','nntt','novedades') NOT NULL,
  `grupo` varchar(255) NOT NULL DEFAULT 'General',
  `descripcion` varchar(500) NOT NULL,
  `destino` varchar(800) NOT NULL,
  `imagen` varchar(500) NOT NULL DEFAULT '/images/default.png',
  `icon_svg` mediumtext DEFAULT NULL,
  `aviso` varchar(500) DEFAULT NULL,
  `position` int(11) NOT NULL DEFAULT 0,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `intranet_plugins`
--

CREATE TABLE `intranet_plugins` (
  `id` varchar(64) NOT NULL,
  `name` varchar(128) NOT NULL,
  `version` varchar(32) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `installed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  `source_url` varchar(255) DEFAULT NULL,
  `sha256` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `intranet_roles`
--

CREATE TABLE `intranet_roles` (
  `role_key` varchar(100) NOT NULL,
  `label` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `position` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`role_key`),
  KEY `idx_intranet_roles_enabled_position` (`is_enabled`, `position`, `role_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_spanish_ci;

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

--
-- Estructura de tabla para la tabla `mail_signatures`
--

CREATE TABLE `mail_signatures` (
  `id` int(11) NOT NULL,
  `title` varchar(160) NOT NULL,
  `valid_from` date NOT NULL,
  `html_signature` longtext NOT NULL,
  `text_signature` longtext DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `plugin_builder_apps`
--

CREATE TABLE `plugin_builder_apps` (
  `id` int(11) NOT NULL,
  `source_app` varchar(128) NOT NULL,
  `plugin_id` varchar(128) NOT NULL,
  `app_folder` varchar(128) NOT NULL,
  `name` varchar(255) NOT NULL,
  `version` varchar(64) NOT NULL,
  `description` text DEFAULT NULL,
  `icon` varchar(2048) DEFAULT NULL,
  `roles_json` text DEFAULT NULL,
  `new_tab` tinyint(1) NOT NULL DEFAULT 0,
  `default_group_name` varchar(128) DEFAULT NULL,
  `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 `sectores`
--

CREATE TABLE `sectores` (
  `id` int(11) NOT NULL,
  `nombre` varchar(150) NOT NULL,
  `color_hex` char(7) DEFAULT NULL,
  `activo` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `sectores_alias`
--

CREATE TABLE `sectores_alias` (
  `alias` varchar(150) NOT NULL,
  `sector_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `solicitudes_delegados`
--

CREATE TABLE `solicitudes_delegados` (
  `id` int(11) NOT NULL,
  `nombre_delegado` varchar(100) NOT NULL,
  `dni` varchar(9) NOT NULL,
  `telefono` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `sector_id` int(11) DEFAULT NULL,
  `observaciones` text DEFAULT NULL,
  `solicitado_por` varchar(50) DEFAULT NULL,
  `fecha_solicitud` timestamp NULL DEFAULT current_timestamp(),
  `estado` enum('Pendiente','Tramitado','Rechazado') DEFAULT 'Pendiente',
  `resuelto_por` varchar(50) DEFAULT NULL,
  `fecha_resolucion` datetime DEFAULT NULL,
  `bitacora` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `stats_apps_user_year`
--

CREATE TABLE `stats_apps_user_year` (
  `year` int(11) NOT NULL,
  `app_id` varchar(100) NOT NULL,
  `username` varchar(190) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `last_click_at` datetime DEFAULT NULL,
  `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 `stats_apps_year`
--

CREATE TABLE `stats_apps_year` (
  `year` int(11) NOT NULL,
  `app_id` varchar(100) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `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 `stats_clicks`
--

CREATE TABLE `stats_clicks` (
  `id` bigint(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `username` varchar(190) NOT NULL,
  `item_kind` enum('app','link') NOT NULL,
  `item_key` varchar(100) NOT NULL,
  `link_type` enum('links','nntt','novedades') DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `destino` varchar(800) DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `referer` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `stats_links_user_year`
--

CREATE TABLE `stats_links_user_year` (
  `year` int(11) NOT NULL,
  `link_type` enum('links','nntt','novedades') NOT NULL,
  `link_id` int(11) NOT NULL,
  `username` varchar(190) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `last_click_at` datetime DEFAULT NULL,
  `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 `stats_links_year`
--

CREATE TABLE `stats_links_year` (
  `year` int(11) NOT NULL,
  `link_type` enum('links','nntt','novedades') NOT NULL,
  `link_id` int(11) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `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 `stats_user_year`
--

CREATE TABLE `stats_user_year` (
  `year` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `last_visit_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura de tabla para la tabla `stats_year`
--

CREATE TABLE `stats_year` (
  `year` int(11) NOT NULL,
  `total` bigint(20) NOT NULL DEFAULT 0,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Estructura para la vista `entregas_entregas_recientes`
--
DROP TABLE IF EXISTS `entregas_entregas_recientes`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `entregas_entregas_recientes`  AS SELECT `entregas_entregas`.`ID` AS `ID`, `entregas_entregas`.`DNI` AS `DNI`, `entregas_entregas`.`Objeto` AS `Objeto`, `entregas_entregas`.`FechaEntrega` AS `FechaEntrega`, `entregas_entregas`.`PersonaEntrega` AS `PersonaEntrega`, `entregas_entregas`.`Observaciones` AS `Observaciones`, `entregas_entregas`.`Timestamp` AS `Timestamp` FROM `entregas_entregas` ORDER BY `entregas_entregas`.`Timestamp` DESC LIMIT 0, 10 ;

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

--
-- Estructura para la vista `entregas_entrgeasExt`
--
DROP TABLE IF EXISTS `entregas_entrgeasExt`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `entregas_entrgeasExt`  AS SELECT `e`.`DNI` AS `dni`, `a`.`Nombre` AS `nombre`, `e`.`Objeto` AS `objeto`, `e`.`FechaEntrega` AS `fechaEntrega`, `e`.`PersonaEntrega` AS `PersonaEntrega`, `e`.`Observaciones` AS `observaciones` FROM (`entregas_entregas` `e` join `entregas_afiliados` `a` on(`e`.`DNI` = `a`.`DNI`)) ;

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

--
-- Estructura para la vista `entregas_estadisticas`
--
DROP TABLE IF EXISTS `entregas_estadisticas`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `entregas_estadisticas`  AS SELECT `entregas_entrgeasExt`.`objeto` AS `objeto`, `entregas_entrgeasExt`.`fechaEntrega` AS `fechaentrega`, count(0) AS `count(*)` FROM `entregas_entrgeasExt` GROUP BY `entregas_entrgeasExt`.`objeto`, `entregas_entrgeasExt`.`fechaEntrega` ;

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `ad_unlock_dept_log`
--
ALTER TABLE `ad_unlock_dept_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_target` (`target_username`),
  ADD KEY `idx_by` (`unlocked_by`),
  ADD KEY `idx_dept` (`dept_cn`),
  ADD KEY `idx_when` (`unlocked_at`);

--
-- Indices de la tabla `apps_user_year`
--
ALTER TABLE `apps_user_year`
  ADD PRIMARY KEY (`year`,`app_id`,`username`),
  ADD KEY `idx_user` (`username`),
  ADD KEY `idx_app` (`app_id`);

--
-- Indices de la tabla `apps_year`
--
ALTER TABLE `apps_year`
  ADD PRIMARY KEY (`year`,`app_id`),
  ADD KEY `idx_app` (`app_id`);

--
-- Indices de la tabla `compras_acquisitions`
--
ALTER TABLE `compras_acquisitions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_compras_sector_id` (`sector_id`);

--
-- Indices de la tabla `compras_document_sequences`
--
ALTER TABLE `compras_document_sequences`
  ADD PRIMARY KEY (`date_key`);

--
-- Indices de la tabla `entregas_afiliados`
--
ALTER TABLE `entregas_afiliados`
  ADD PRIMARY KEY (`DNI`);

--
-- Indices de la tabla `entregas_entregas`
--
ALTER TABLE `entregas_entregas`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `Objeto` (`Objeto`),
  ADD KEY `idx_fecha` (`FechaEntrega`),
  ADD KEY `idx_persona_fecha` (`PersonaEntrega`,`FechaEntrega`),
  ADD KEY `DNI` (`DNI`,`Objeto`) USING BTREE;

--
-- Indices de la tabla `entregas_objetos`
--
ALTER TABLE `entregas_objetos`
  ADD PRIMARY KEY (`Objeto`);

--
-- Indices de la tabla `flota_documentos`
--
ALTER TABLE `flota_documentos`
  ADD PRIMARY KEY (`id`),
  ADD KEY `vehiculo_id` (`vehiculo_id`);

--
-- Indices de la tabla `flota_reservas`
--
ALTER TABLE `flota_reservas`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_reservas_vehiculo` (`vehiculo_id`),
  ADD KEY `idx_reservas_sector` (`sector_id`),
  ADD KEY `idx_fecha` (`fecha`),
  ADD KEY `idx_vehiculo_fecha` (`vehiculo_id`,`fecha`),
  ADD KEY `idx_sector_fecha` (`sector_id`,`fecha`),
  ADD KEY `idx_estado` (`estado`),
  ADD KEY `idx_flota_sector_id` (`sector_id`);

--
-- Indices de la tabla `flota_reserva_documentos`
--
ALTER TABLE `flota_reserva_documentos`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_prestamo_id` (`prestamo_id`);

--
-- Indices de la tabla `flota_vehiculos`
--
ALTER TABLE `flota_vehiculos`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `matricula` (`matricula`),
  ADD UNIQUE KEY `uq_matricula` (`matricula`),
  ADD KEY `idx_tipo_estado` (`tipo`,`estado`);

--
-- Indices de la tabla `inc_attachments`
--
ALTER TABLE `inc_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_incident` (`incident_id`);

--
-- Indices de la tabla `inc_categories`
--
ALTER TABLE `inc_categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_name` (`name`);

--
-- Indices de la tabla `inc_comments`
--
ALTER TABLE `inc_comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_incident` (`incident_id`);

--
-- Indices de la tabla `inc_department_emails`
--
ALTER TABLE `inc_department_emails`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_department` (`department`);

--
-- Indices de la tabla `inc_history`
--
ALTER TABLE `inc_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_incident` (`incident_id`);

--
-- Indices de la tabla `inc_incidents`
--
ALTER TABLE `inc_incidents`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_code` (`code`),
  ADD KEY `idx_dept_status` (`department`,`status`),
  ADD KEY `idx_created_by` (`created_by`),
  ADD KEY `idx_last_activity` (`last_activity_at`),
  ADD KEY `fk_inc_category` (`category_id`);

--
-- Indices de la tabla `inc_notifications`
--
ALTER TABLE `inc_notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_recipient_read` (`recipient`,`read_at`),
  ADD KEY `idx_incident` (`incident_id`);

--
-- Indices de la tabla `intranet_apps`
--
ALTER TABLE `intranet_apps`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `app_id` (`app_id`),
  ADD KEY `idx_group_pos` (`group_name`,`position`);

--
-- Indices de la tabla `intranet_catalog_groups`
--
ALTER TABLE `intranet_catalog_groups`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_group_type_name` (`group_type`,`group_name`),
  ADD KEY `idx_group_type_position` (`group_type`,`position`);

--
-- Indices de la tabla `intranet_links`
--
ALTER TABLE `intranet_links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_type_pos` (`link_type`,`position`),
  ADD KEY `idx_type_group_pos` (`link_type`,`grupo`,`position`);

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

--
-- Indices de la tabla `mail_signatures`
--
ALTER TABLE `mail_signatures`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_valid_from` (`valid_from`),
  ADD KEY `idx_enabled_valid` (`is_enabled`,`valid_from`);

--
-- Indices de la tabla `plugin_builder_apps`
--
ALTER TABLE `plugin_builder_apps`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `source_app` (`source_app`);

--
-- Indices de la tabla `sectores`
--
ALTER TABLE `sectores`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_nombre` (`nombre`),
  ADD KEY `idx_activo` (`activo`);

--
-- Indices de la tabla `sectores_alias`
--
ALTER TABLE `sectores_alias`
  ADD PRIMARY KEY (`alias`),
  ADD KEY `idx_sector_id` (`sector_id`);

--
-- Indices de la tabla `solicitudes_delegados`
--
ALTER TABLE `solicitudes_delegados`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_delegados_sector_id` (`sector_id`);

--
-- Indices de la tabla `stats_apps_user_year`
--
ALTER TABLE `stats_apps_user_year`
  ADD PRIMARY KEY (`year`,`app_id`,`username`);

--
-- Indices de la tabla `stats_apps_year`
--
ALTER TABLE `stats_apps_year`
  ADD PRIMARY KEY (`year`,`app_id`);

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

--
-- Indices de la tabla `stats_links_user_year`
--
ALTER TABLE `stats_links_user_year`
  ADD PRIMARY KEY (`year`,`link_type`,`link_id`,`username`);

--
-- Indices de la tabla `stats_links_year`
--
ALTER TABLE `stats_links_year`
  ADD PRIMARY KEY (`year`,`link_type`,`link_id`);

--
-- Indices de la tabla `stats_user_year`
--
ALTER TABLE `stats_user_year`
  ADD PRIMARY KEY (`year`,`username`),
  ADD KEY `idx_username` (`username`);

--
-- Indices de la tabla `stats_year`
--
ALTER TABLE `stats_year`
  ADD PRIMARY KEY (`year`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

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

--
-- AUTO_INCREMENT de la tabla `compras_acquisitions`
--
ALTER TABLE `compras_acquisitions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `entregas_entregas`
--
ALTER TABLE `entregas_entregas`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `flota_documentos`
--
ALTER TABLE `flota_documentos`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `flota_reservas`
--
ALTER TABLE `flota_reservas`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `flota_reserva_documentos`
--
ALTER TABLE `flota_reserva_documentos`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `flota_vehiculos`
--
ALTER TABLE `flota_vehiculos`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_attachments`
--
ALTER TABLE `inc_attachments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_categories`
--
ALTER TABLE `inc_categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_comments`
--
ALTER TABLE `inc_comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_department_emails`
--
ALTER TABLE `inc_department_emails`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_history`
--
ALTER TABLE `inc_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_incidents`
--
ALTER TABLE `inc_incidents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `inc_notifications`
--
ALTER TABLE `inc_notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `intranet_apps`
--
ALTER TABLE `intranet_apps`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `intranet_catalog_groups`
--
ALTER TABLE `intranet_catalog_groups`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `intranet_links`
--
ALTER TABLE `intranet_links`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `mail_signatures`
--
ALTER TABLE `mail_signatures`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `plugin_builder_apps`
--
ALTER TABLE `plugin_builder_apps`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT de la tabla `solicitudes_delegados`
--
ALTER TABLE `solicitudes_delegados`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `compras_acquisitions`
--
ALTER TABLE `compras_acquisitions`
  ADD CONSTRAINT `fk_compras_sector` FOREIGN KEY (`sector_id`) REFERENCES `sectores` (`id`) ON UPDATE CASCADE;

--
-- Filtros para la tabla `flota_documentos`
--
ALTER TABLE `flota_documentos`
  ADD CONSTRAINT `documentos_ibfk_1` FOREIGN KEY (`vehiculo_id`) REFERENCES `flota_vehiculos` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `flota_reservas`
--
ALTER TABLE `flota_reservas`
  ADD CONSTRAINT `fk_flota_sector` FOREIGN KEY (`sector_id`) REFERENCES `sectores` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `reservas_ibfk_1` FOREIGN KEY (`vehiculo_id`) REFERENCES `flota_vehiculos` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `reservas_ibfk_2` FOREIGN KEY (`sector_id`) REFERENCES `sectores` (`id`);

--
-- Filtros para la tabla `flota_reserva_documentos`
--
ALTER TABLE `flota_reserva_documentos`
  ADD CONSTRAINT `fk_reserva_documentos_prestamo` FOREIGN KEY (`prestamo_id`) REFERENCES `flota_reservas` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `inc_attachments`
--
ALTER TABLE `inc_attachments`
  ADD CONSTRAINT `fk_attach_incident` FOREIGN KEY (`incident_id`) REFERENCES `inc_incidents` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `inc_comments`
--
ALTER TABLE `inc_comments`
  ADD CONSTRAINT `fk_comment_incident` FOREIGN KEY (`incident_id`) REFERENCES `inc_incidents` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `inc_history`
--
ALTER TABLE `inc_history`
  ADD CONSTRAINT `fk_hist_incident` FOREIGN KEY (`incident_id`) REFERENCES `inc_incidents` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `inc_incidents`
--
ALTER TABLE `inc_incidents`
  ADD CONSTRAINT `fk_inc_category` FOREIGN KEY (`category_id`) REFERENCES `inc_categories` (`id`) ON DELETE SET NULL;

--
-- Filtros para la tabla `inc_notifications`
--
ALTER TABLE `inc_notifications`
  ADD CONSTRAINT `fk_notif_incident` FOREIGN KEY (`incident_id`) REFERENCES `inc_incidents` (`id`) ON DELETE CASCADE;

--
-- Filtros para la tabla `solicitudes_delegados`
--
ALTER TABLE `solicitudes_delegados`
  ADD CONSTRAINT `fk_delegados_sector` FOREIGN KEY (`sector_id`) REFERENCES `sectores` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
