-- =====================================================
-- HELPDESK ITSM - Schema Completo
-- MySQL 8+ / MariaDB 10.4+
-- =====================================================
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─── USUARIOS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin','agent','client') NOT NULL DEFAULT 'client',
  `department` VARCHAR(100) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `active` TINYINT(1) DEFAULT 1,
  `last_login` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_role` (`role`),
  INDEX `idx_users_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── CATEGORÍAS (parametrizables, con subcategorías) ─
CREATE TABLE IF NOT EXISTS `ticket_categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `parent_id` INT DEFAULT NULL,
  `icon` VARCHAR(50) DEFAULT 'fa-tag',
  `color` VARCHAR(7) DEFAULT '#6B7280',
  `active` TINYINT(1) DEFAULT 1,
  `sort_order` INT DEFAULT 0,
  FOREIGN KEY (`parent_id`) REFERENCES `ticket_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── ESTADOS (configurables) ─────────────────────────
CREATE TABLE IF NOT EXISTS `ticket_statuses` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `color` VARCHAR(7) DEFAULT '#6B7280',
  `icon` VARCHAR(50) DEFAULT 'fa-circle',
  `sort_order` INT DEFAULT 0,
  `is_default` TINYINT(1) DEFAULT 0,
  `is_closed` TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── SLA ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sla_policies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `priority` ENUM('low','medium','high','critical') NOT NULL,
  `response_hours` INT NOT NULL DEFAULT 4,
  `resolution_hours` INT NOT NULL DEFAULT 24,
  `is_default` TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── ACTIVOS (CMDB) ─────────────────────────────────
CREATE TABLE IF NOT EXISTS `assets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `type` ENUM('desktop','laptop','server','printer','network','phone','software','other') DEFAULT 'other',
  `asset_tag` VARCHAR(50) UNIQUE,
  `serial_number` VARCHAR(100) DEFAULT NULL,
  `brand` VARCHAR(100) DEFAULT NULL,
  `model` VARCHAR(100) DEFAULT NULL,
  `status` ENUM('active','maintenance','retired','lost') DEFAULT 'active',
  `location` VARCHAR(200) DEFAULT NULL,
  `assigned_to` INT DEFAULT NULL,
  `purchase_date` DATE DEFAULT NULL,
  `warranty_until` DATE DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_assets_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── HISTORIAL MANTENIMIENTO ─────────────────────────
CREATE TABLE IF NOT EXISTS `asset_maintenance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `asset_id` INT NOT NULL,
  `type` ENUM('maintenance','repair','upgrade','inspection','relocation') DEFAULT 'maintenance',
  `description` TEXT NOT NULL,
  `cost` DECIMAL(10,2) DEFAULT 0.00,
  `performed_by` INT DEFAULT NULL,
  `performed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `next_maintenance` DATE DEFAULT NULL,
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`performed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── TICKETS ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `subject` VARCHAR(300) NOT NULL,
  `description` TEXT NOT NULL,
  `priority` ENUM('low','medium','high','critical') DEFAULT 'medium',
  `status_id` INT NOT NULL,
  `category_id` INT DEFAULT NULL,
  `subcategory_id` INT DEFAULT NULL,
  `assigned_to` INT DEFAULT NULL,
  `created_by` INT NOT NULL,
  `asset_id` INT DEFAULT NULL,
  `sla_policy_id` INT DEFAULT NULL,
  `sla_response_due` DATETIME DEFAULT NULL,
  `sla_resolution_due` DATETIME DEFAULT NULL,
  `first_response_at` DATETIME DEFAULT NULL,
  `resolved_at` DATETIME DEFAULT NULL,
  `closed_at` DATETIME DEFAULT NULL,
  `satisfaction_rating` TINYINT DEFAULT NULL,
  `satisfaction_comment` TEXT DEFAULT NULL,
  `ai_summary` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`status_id`) REFERENCES `ticket_statuses`(`id`),
  FOREIGN KEY (`category_id`) REFERENCES `ticket_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`subcategory_id`) REFERENCES `ticket_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`),
  FOREIGN KEY (`asset_id`) REFERENCES `assets`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`sla_policy_id`) REFERENCES `sla_policies`(`id`) ON DELETE SET NULL,
  INDEX `idx_tickets_status` (`status_id`),
  INDEX `idx_tickets_priority` (`priority`),
  INDEX `idx_tickets_assigned` (`assigned_to`),
  INDEX `idx_tickets_sla` (`sla_resolution_due`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── COMENTARIOS (conversación) ──────────────────────
CREATE TABLE IF NOT EXISTS `ticket_comments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `comment` TEXT NOT NULL,
  `is_internal` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
  INDEX `idx_comments_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── ADJUNTOS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ticket_attachments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `file_size` INT DEFAULT 0,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `uploaded_by` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── BASE DE CONOCIMIENTO ────────────────────────────
CREATE TABLE IF NOT EXISTS `kb_categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `icon` VARCHAR(50) DEFAULT 'fa-folder',
  `sort_order` INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `kb_articles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(300) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `category_id` INT DEFAULT NULL,
  `status` ENUM('draft','published','archived') DEFAULT 'draft',
  `is_pinned` TINYINT(1) DEFAULT 0,
  `views` INT DEFAULT 0,
  `helpful_yes` INT DEFAULT 0,
  `helpful_no` INT DEFAULT 0,
  `created_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `kb_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FULLTEXT INDEX `idx_kb_search` (`title`, `content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── AUDITORÍA (trazabilidad completa) ───────────────
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `user_name` VARCHAR(100) DEFAULT NULL,
  `action` VARCHAR(100) NOT NULL,
  `entity_type` VARCHAR(50) NOT NULL,
  `entity_id` INT DEFAULT NULL,
  `old_values` JSON DEFAULT NULL,
  `new_values` JSON DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_audit_entity` (`entity_type`, `entity_id`),
  INDEX `idx_audit_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── NOTIFICACIONES ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `message` TEXT NOT NULL,
  `link` VARCHAR(255) DEFAULT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_notif_user` (`user_id`, `is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── CONFIGURACIÓN ───────────────────────────────────
CREATE TABLE IF NOT EXISTS `settings` (
  `skey` VARCHAR(100) PRIMARY KEY,
  `svalue` TEXT DEFAULT NULL,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── LOGIN ATTEMPTS ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ip_address` VARCHAR(45) NOT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_attempts_ip` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- DATOS INICIALES
-- =====================================================

INSERT INTO `ticket_statuses` (`name`, `color`, `icon`, `sort_order`, `is_default`, `is_closed`) VALUES
('Abierto',     '#3B82F6', 'fa-circle-dot',  1, 1, 0),
('En Progreso', '#F59E0B', 'fa-spinner',     2, 0, 0),
('En Espera',   '#8B5CF6', 'fa-clock',       3, 0, 0),
('Resuelto',    '#10B981', 'fa-check-circle', 4, 0, 1),
('Cerrado',     '#6B7280', 'fa-times-circle', 5, 0, 1);

INSERT INTO `ticket_categories` (`id`, `name`, `icon`, `color`, `sort_order`) VALUES
(1, 'Hardware',           'fa-desktop',  '#EF4444', 1),
(2, 'Software',           'fa-code',     '#3B82F6', 2),
(3, 'Red / Conectividad', 'fa-wifi',     '#8B5CF6', 3),
(4, 'Correo Electrónico', 'fa-envelope', '#F59E0B', 4),
(5, 'Accesos / Permisos', 'fa-key',      '#10B981', 5),
(6, 'Impresión',          'fa-print',    '#EC4899', 6),
(7, 'Telefonía',          'fa-phone',    '#06B6D4', 7),
(8, 'Otros',              'fa-ellipsis', '#6B7280', 8);

INSERT INTO `ticket_categories` (`name`, `parent_id`, `sort_order`) VALUES
('PC no enciende', 1, 1), ('Pantalla dañada', 1, 2), ('Teclado/Mouse', 1, 3),
('Instalación software', 2, 1), ('Error en aplicación', 2, 2), ('Actualización', 2, 3),
('Sin internet', 3, 1), ('VPN', 3, 2), ('WiFi lento', 3, 3),
('No envía correos', 4, 1), ('Buzón lleno', 4, 2),
('Resetear contraseña', 5, 1), ('Nuevo acceso', 5, 2), ('Permisos carpeta', 5, 3),
('Atasco papel', 6, 1), ('No imprime', 6, 2);

INSERT INTO `sla_policies` (`name`, `priority`, `response_hours`, `resolution_hours`, `is_default`) VALUES
('Crítico',  'critical', 1,  4,  0),
('Alto',     'high',     2,  8,  0),
('Medio',    'medium',   4,  24, 1),
('Bajo',     'low',      8,  48, 0);

INSERT INTO `kb_categories` (`name`, `icon`, `sort_order`) VALUES
('Guías de Usuario',      'fa-book',           1),
('Soluciones Frecuentes', 'fa-lightbulb',      2),
('Políticas TI',          'fa-shield-alt',     3),
('Tutoriales',            'fa-graduation-cap', 4);

INSERT INTO `kb_articles` (`title`, `content`, `category_id`, `status`, `is_pinned`, `created_by`) VALUES
('Cómo resetear tu contraseña', '<h3>Pasos</h3><ol><li>Ir a la página de login</li><li>Click en Olvidé mi contraseña</li><li>Ingresar correo corporativo</li><li>Seguir el enlace del correo</li></ol><p><strong>Nota:</strong> Revisa spam si no llega en 5 minutos.</p>', 1, 'published', 1, 1),
('Conectarse a la VPN corporativa', '<h3>Requisitos</h3><ul><li>Cliente VPN instalado</li><li>Credenciales activas</li></ul><h3>Pasos</h3><ol><li>Abrir cliente VPN</li><li>Servidor: vpn.empresa.com</li><li>Ingresar credenciales</li></ol>', 1, 'published', 0, 1),
('La impresora no responde', '<h3>Solución rápida</h3><ol><li>Verificar encendida y con papel</li><li>Revisar conexión USB o red</li><li>Reiniciar cola de impresión</li><li>Si persiste, reinstalar driver</li></ol>', 2, 'published', 0, 1),
('Política de uso de equipos', '<h3>Reglas</h3><ul><li>Equipos son propiedad de la empresa</li><li>No instalar software no autorizado</li><li>Reportar fallos inmediatamente</li><li>No compartir credenciales</li></ul>', 3, 'published', 0, 1);

INSERT INTO `settings` (`skey`, `svalue`) VALUES
('app_name', 'HelpDesk ITSM'),
('company_name', 'Mi Empresa'),
('primary_color', '#007A33'),
('gemini_api_key', ''),
('email_notifications', 'true');

SET FOREIGN_KEY_CHECKS = 1;
