-- ============================================================
-- SISTEMA DE CONTROL DE GESTIÓN DE DOCUMENTACIÓN
-- Oficios Jurídicos Institucionales
-- Versión: 1.0 | Fecha: 2026-04-22
-- ============================================================

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

CREATE DATABASE IF NOT EXISTS `gestion_documentos`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `gestion_documentos`;

-- -----------------------------------------------------------
-- TABLA: roles
-- -----------------------------------------------------------
CREATE TABLE `roles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(50) NOT NULL,
  `descripcion` VARCHAR(255) DEFAULT NULL,
  `permisos` JSON DEFAULT NULL COMMENT 'Permisos en formato JSON para RBAC',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rol_nombre` (`nombre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`nombre`, `descripcion`, `permisos`) VALUES
('administrador', 'Acceso total al sistema', '{"oficios":"CRUD","usuarios":"CRUD","reportes":true,"respaldo":true,"papelera_fisica":true,"config_alertas":true}'),
('supervisor', 'Supervisa usuarios y recibe escalaciones', '{"oficios":"CRUD","usuarios":"read","reportes":true,"respaldo":false,"papelera_fisica":false,"config_alertas":false}'),
('estandar', 'Gestión de oficios propios', '{"oficios":"CRUD_own","usuarios":"none","reportes":"own","respaldo":false,"papelera_fisica":false,"config_alertas":false}');

-- -----------------------------------------------------------
-- TABLA: usuarios
-- -----------------------------------------------------------
CREATE TABLE `usuarios` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rol_id` INT UNSIGNED NOT NULL DEFAULT 3,
  `nombre` VARCHAR(100) NOT NULL,
  `apellido` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `username` VARCHAR(60) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `cargo` VARCHAR(100) DEFAULT NULL,
  `area` VARCHAR(100) DEFAULT NULL,
  `supervisor_id` INT UNSIGNED DEFAULT NULL COMMENT 'Usuario supervisor para escalaciones',
  `token_recuperacion` VARCHAR(100) DEFAULT NULL,
  `token_expira` DATETIME DEFAULT NULL,
  `ultimo_login` DATETIME DEFAULT NULL,
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `deleted_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `fk_usuario_rol` (`rol_id`),
  KEY `fk_usuario_supervisor` (`supervisor_id`),
  CONSTRAINT `fk_usuario_rol` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_usuario_supervisor` FOREIGN KEY (`supervisor_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Usuario administrador por defecto: admin / Admin@2026
INSERT INTO `usuarios` (`rol_id`, `nombre`, `apellido`, `email`, `username`, `password_hash`, `cargo`, `area`, `activo`) VALUES
(1, 'Administrador', 'Sistema', 'admin@sistema.local', 'admin', '$2y$12$hF3W4J5K9L2M7N8P1Q6R4OQKvVbFa.Np/GzDzFH8fRv1sXkLmEpWu', 'Administrador TI', 'Sistemas', 1);

-- -----------------------------------------------------------
-- TABLA: etiquetas
-- -----------------------------------------------------------
CREATE TABLE `etiquetas` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(80) NOT NULL,
  `color` VARCHAR(7) NOT NULL DEFAULT '#6c757d' COMMENT 'Hex color',
  `icono` VARCHAR(50) DEFAULT 'fa-tag',
  `creado_por` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_etiqueta_nombre` (`nombre`),
  KEY `fk_etiqueta_usuario` (`creado_por`),
  CONSTRAINT `fk_etiqueta_usuario` FOREIGN KEY (`creado_por`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `etiquetas` (`nombre`, `color`, `icono`) VALUES
('Contrato', '#0d6efd', 'fa-file-contract'),
('Demanda', '#dc3545', 'fa-gavel'),
('Recurso', '#fd7e14', 'fa-folder-open'),
('Solicitud', '#198754', 'fa-envelope-open'),
('Circular', '#6f42c1', 'fa-circle-info'),
('Convenio', '#20c997', 'fa-handshake'),
('Resolución', '#0dcaf0', 'fa-stamp'),
('Decreto', '#e91e63', 'fa-scroll');

-- -----------------------------------------------------------
-- TABLA: plantillas_respuesta
-- -----------------------------------------------------------
CREATE TABLE `plantillas_respuesta` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `titulo` VARCHAR(150) NOT NULL,
  `contenido` TEXT NOT NULL,
  `tipo` ENUM('recibido','enviado','general') NOT NULL DEFAULT 'general',
  `creado_por` INT UNSIGNED DEFAULT NULL,
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_plantilla_usuario` (`creado_por`),
  CONSTRAINT `fk_plantilla_usuario` FOREIGN KEY (`creado_por`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `plantillas_respuesta` (`titulo`, `contenido`, `tipo`) VALUES
('Acuse de recibo', 'En respuesta a su oficio N° {numero_oficio} de fecha {fecha_recepcion}, le informamos que hemos recibido correctamente su comunicación y procederemos a darle el trámite correspondiente.', 'recibido'),
('Solicitud de información adicional', 'En atención a su oficio N° {numero_oficio}, nos permitimos solicitarle amablemente información complementaria sobre el asunto referido, a fin de darle una respuesta adecuada y oportuna.', 'recibido'),
('Respuesta favorable', 'En atención a lo solicitado en el oficio N° {numero_oficio}, nos complace comunicarle que su petición ha sido evaluada favorablemente por esta instancia.', 'enviado'),
('Respuesta negativa', 'Lamentamos comunicarle que, tras la revisión del oficio N° {numero_oficio}, esta instancia no puede dar respuesta favorable a lo solicitado por las razones que se detallan a continuación.', 'enviado');

-- -----------------------------------------------------------
-- TABLA: oficios
-- -----------------------------------------------------------
CREATE TABLE `oficios` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `numero_oficio` VARCHAR(50) NOT NULL,
  `tipo` ENUM('recibido','enviado') NOT NULL DEFAULT 'recibido',
  `remitente` VARCHAR(200) NOT NULL,
  `destinatario` VARCHAR(200) NOT NULL,
  `asunto` TEXT NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `fecha_recepcion` DATE NOT NULL,
  `fecha_vencimiento` DATE DEFAULT NULL,
  `prioridad` ENUM('alta','media','baja') NOT NULL DEFAULT 'media',
  `estado` ENUM('recibido','en_proceso','respondido','vencido','archivado') NOT NULL DEFAULT 'recibido',
  `responsable_id` INT UNSIGNED DEFAULT NULL,
  `creado_por` INT UNSIGNED DEFAULT NULL,
  `derivado_de_id` INT UNSIGNED DEFAULT NULL COMMENT 'Oficio del que fue derivado',
  `derivado_a_id` INT UNSIGNED DEFAULT NULL COMMENT 'Usuario al que se derivó',
  `comentario_derivacion` TEXT DEFAULT NULL,
  `es_confidencial` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_numero_oficio` (`numero_oficio`),
  KEY `idx_tipo` (`tipo`),
  KEY `idx_estado` (`estado`),
  KEY `idx_prioridad` (`prioridad`),
  KEY `idx_fecha_vencimiento` (`fecha_vencimiento`),
  KEY `idx_deleted_at` (`deleted_at`),
  KEY `fk_oficio_responsable` (`responsable_id`),
  KEY `fk_oficio_creador` (`creado_por`),
  KEY `fk_oficio_derivado_a` (`derivado_a_id`),
  FULLTEXT KEY `ft_busqueda` (`numero_oficio`, `remitente`, `destinatario`, `asunto`),
  CONSTRAINT `fk_oficio_responsable` FOREIGN KEY (`responsable_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_oficio_creador` FOREIGN KEY (`creado_por`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_oficio_derivado_a` FOREIGN KEY (`derivado_a_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: oficio_etiquetas (N:M)
-- -----------------------------------------------------------
CREATE TABLE `oficio_etiquetas` (
  `oficio_id` INT UNSIGNED NOT NULL,
  `etiqueta_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`oficio_id`, `etiqueta_id`),
  KEY `fk_oe_etiqueta` (`etiqueta_id`),
  CONSTRAINT `fk_oe_oficio` FOREIGN KEY (`oficio_id`) REFERENCES `oficios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_oe_etiqueta` FOREIGN KEY (`etiqueta_id`) REFERENCES `etiquetas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: documentos_adjuntos
-- -----------------------------------------------------------
CREATE TABLE `documentos_adjuntos` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `oficio_id` INT UNSIGNED NOT NULL,
  `nombre_original` VARCHAR(255) NOT NULL,
  `nombre_archivo` VARCHAR(255) NOT NULL COMMENT 'Nombre hasheado en servidor',
  `ruta` VARCHAR(500) NOT NULL,
  `tipo_mime` VARCHAR(100) NOT NULL,
  `tamanio` INT UNSIGNED NOT NULL COMMENT 'Tamaño en bytes',
  `subido_por` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_doc_oficio` (`oficio_id`),
  KEY `fk_doc_usuario` (`subido_por`),
  CONSTRAINT `fk_doc_oficio` FOREIGN KEY (`oficio_id`) REFERENCES `oficios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_doc_usuario` FOREIGN KEY (`subido_por`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: tareas
-- -----------------------------------------------------------
CREATE TABLE `tareas` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `oficio_id` INT UNSIGNED NOT NULL,
  `asignado_a` INT UNSIGNED DEFAULT NULL,
  `creado_por` INT UNSIGNED DEFAULT NULL,
  `titulo` VARCHAR(200) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `fecha_limite` DATE DEFAULT NULL,
  `estado` ENUM('pendiente','en_proceso','completada','cancelada') NOT NULL DEFAULT 'pendiente',
  `prioridad` ENUM('alta','media','baja') NOT NULL DEFAULT 'media',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_tarea_oficio` (`oficio_id`),
  KEY `fk_tarea_asignado` (`asignado_a`),
  CONSTRAINT `fk_tarea_oficio` FOREIGN KEY (`oficio_id`) REFERENCES `oficios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_tarea_asignado` FOREIGN KEY (`asignado_a`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: historial_cambios (Auditoría)
-- -----------------------------------------------------------
CREATE TABLE `historial_cambios` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tabla` VARCHAR(60) NOT NULL,
  `registro_id` INT UNSIGNED NOT NULL,
  `campo_modificado` VARCHAR(100) DEFAULT NULL,
  `valor_anterior` TEXT DEFAULT NULL,
  `valor_nuevo` TEXT DEFAULT NULL,
  `accion` ENUM('crear','editar','eliminar','restaurar','derivar','escalacion') NOT NULL,
  `usuario_id` INT UNSIGNED DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `detalle` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_historial_tabla_registro` (`tabla`, `registro_id`),
  KEY `fk_historial_usuario` (`usuario_id`),
  CONSTRAINT `fk_historial_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: alertas_config
-- -----------------------------------------------------------
CREATE TABLE `alertas_config` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100) NOT NULL,
  `dias_antes` INT NOT NULL DEFAULT 3 COMMENT 'Días antes del vencimiento para alertar',
  `enviar_email` TINYINT(1) NOT NULL DEFAULT 1,
  `enviar_notificacion` TINYINT(1) NOT NULL DEFAULT 1,
  `activo` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `alertas_config` (`nombre`, `dias_antes`, `enviar_email`, `enviar_notificacion`, `activo`) VALUES
('Alerta 3 días antes', 3, 1, 1, 1),
('Alerta 1 día antes', 1, 1, 1, 1),
('Alerta día de vencimiento', 0, 1, 1, 1),
('Alerta día después (vencido)', -1, 1, 1, 1);

-- -----------------------------------------------------------
-- TABLA: notificaciones
-- -----------------------------------------------------------
CREATE TABLE `notificaciones` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` INT UNSIGNED NOT NULL,
  `oficio_id` INT UNSIGNED DEFAULT NULL,
  `tipo` ENUM('vencimiento','derivacion','escalacion','sistema','tarea') NOT NULL DEFAULT 'sistema',
  `titulo` VARCHAR(200) NOT NULL,
  `mensaje` TEXT NOT NULL,
  `leida` TINYINT(1) NOT NULL DEFAULT 0,
  `leida_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_usuario_leida` (`usuario_id`, `leida`),
  KEY `fk_notif_oficio` (`oficio_id`),
  CONSTRAINT `fk_notif_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_notif_oficio` FOREIGN KEY (`oficio_id`) REFERENCES `oficios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: comentarios
-- -----------------------------------------------------------
CREATE TABLE `comentarios` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `oficio_id` INT UNSIGNED NOT NULL,
  `usuario_id` INT UNSIGNED NOT NULL,
  `comentario` TEXT NOT NULL,
  `es_privado` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=solo admins y supervisores',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_coment_oficio` (`oficio_id`),
  KEY `fk_coment_usuario` (`usuario_id`),
  CONSTRAINT `fk_coment_oficio` FOREIGN KEY (`oficio_id`) REFERENCES `oficios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_coment_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TABLA: log_actividad
-- -----------------------------------------------------------
CREATE TABLE `log_actividad` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` INT UNSIGNED DEFAULT NULL,
  `accion` VARCHAR(100) NOT NULL,
  `modulo` VARCHAR(60) NOT NULL,
  `descripcion` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_log_usuario` (`usuario_id`),
  KEY `idx_log_modulo` (`modulo`),
  KEY `idx_log_created` (`created_at`),
  CONSTRAINT `fk_log_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- VISTA: v_oficios_completo (para reportes y listados)
-- ============================================================
CREATE OR REPLACE VIEW `v_oficios_completo` AS
SELECT
  o.id,
  o.numero_oficio,
  o.tipo,
  o.remitente,
  o.destinatario,
  o.asunto,
  o.fecha_recepcion,
  o.fecha_vencimiento,
  o.prioridad,
  o.estado,
  o.es_confidencial,
  o.deleted_at,
  o.created_at,
  o.updated_at,
  CONCAT(u_resp.nombre, ' ', u_resp.apellido) AS responsable_nombre,
  u_resp.email AS responsable_email,
  CONCAT(u_crea.nombre, ' ', u_crea.apellido) AS creado_por_nombre,
  DATEDIFF(o.fecha_vencimiento, CURDATE()) AS dias_para_vencer,
  CASE
    WHEN o.estado = 'respondido' OR o.estado = 'archivado' THEN 'completado'
    WHEN o.fecha_vencimiento IS NULL THEN 'sin_vencimiento'
    WHEN DATEDIFF(o.fecha_vencimiento, CURDATE()) < 0 THEN 'vencido'
    WHEN DATEDIFF(o.fecha_vencimiento, CURDATE()) <= 3 THEN 'proximo'
    ELSE 'vigente'
  END AS semaforo,
  (SELECT COUNT(*) FROM documentos_adjuntos da WHERE da.oficio_id = o.id) AS total_adjuntos,
  (SELECT COUNT(*) FROM comentarios c WHERE c.oficio_id = o.id) AS total_comentarios,
  (SELECT GROUP_CONCAT(e.nombre ORDER BY e.nombre SEPARATOR ', ')
   FROM oficio_etiquetas oe
   JOIN etiquetas e ON e.id = oe.etiqueta_id
   WHERE oe.oficio_id = o.id) AS etiquetas
FROM oficios o
LEFT JOIN usuarios u_resp ON u_resp.id = o.responsable_id
LEFT JOIN usuarios u_crea ON u_crea.id = o.creado_por
WHERE o.deleted_at IS NULL;

-- ============================================================
-- VISTA: v_dashboard_kpis
-- ============================================================
CREATE OR REPLACE VIEW `v_dashboard_kpis` AS
SELECT
  (SELECT COUNT(*) FROM oficios WHERE deleted_at IS NULL) AS total_oficios,
  (SELECT COUNT(*) FROM oficios WHERE deleted_at IS NULL AND estado NOT IN ('respondido','archivado') AND fecha_vencimiento < CURDATE()) AS total_vencidos,
  (SELECT COUNT(*) FROM oficios WHERE deleted_at IS NULL AND estado NOT IN ('respondido','archivado') AND fecha_vencimiento BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)) AS total_por_vencer,
  (SELECT COUNT(*) FROM oficios WHERE deleted_at IS NULL AND estado IN ('respondido','archivado') AND MONTH(updated_at) = MONTH(CURDATE()) AND YEAR(updated_at) = YEAR(CURDATE())) AS completados_este_mes,
  (SELECT COUNT(*) FROM oficios WHERE deleted_at IS NULL AND estado = 'en_proceso') AS en_proceso,
  (SELECT COUNT(*) FROM usuarios WHERE activo = 1 AND deleted_at IS NULL) AS total_usuarios;