-- =============================================
-- NEXUS IT — Schema Completo (Fase 1 + Fase 2)
-- =============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Usuarios
CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre_completo VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    rol ENUM('admin','tecnico','usuario') NOT NULL DEFAULT 'usuario',
    departamento VARCHAR(100) NULL,
    cargo VARCHAR(100) NULL,
    telefono VARCHAR(20) NULL,
    avatar_url VARCHAR(255) NULL,
    activo TINYINT(1) DEFAULT 1,
    ultimo_login DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_rol (rol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Categorías
CREATE TABLE IF NOT EXISTS categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT NULL,
    color VARCHAR(7) DEFAULT '#6366f1',
    icono VARCHAR(10) DEFAULT '📁',
    activa TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activos
CREATE TABLE IF NOT EXISTS activos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo_activo VARCHAR(20) NOT NULL UNIQUE,
    tipo ENUM('laptop','desktop','servidor','impresora','red','telefono','tablet','monitor','otro') NOT NULL,
    marca VARCHAR(100) NOT NULL,
    modelo VARCHAR(100) NOT NULL,
    numero_serie VARCHAR(100) NULL UNIQUE,
    estado ENUM('activo','mantenimiento','baja','bodega') DEFAULT 'activo',
    ubicacion VARCHAR(200) NULL,
    fecha_compra DATE NULL,
    valor_compra DECIMAL(12,2) NULL,
    proveedor VARCHAR(150) NULL,
    notas TEXT NULL,
    asignado_a_id INT NULL,
    responsable_id INT NULL,
    ip_address VARCHAR(45) NULL,
    mac_address VARCHAR(17) NULL,
    so VARCHAR(100) NULL,
    procesador VARCHAR(150) NULL,
    ram_gb INT NULL,
    disco_gb INT NULL,
    ultima_conexion DATETIME NULL,
    garantia_hasta DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (asignado_a_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (responsable_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_tipo (tipo),
    INDEX idx_estado (estado),
    INDEX idx_asignado (asignado_a_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tickets
CREATE TABLE IF NOT EXISTS tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_ticket VARCHAR(20) NOT NULL UNIQUE,
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    solicitante_id INT NOT NULL,
    categoria_id INT NULL,
    activo_id INT NULL,
    prioridad ENUM('baja','media','alta','critica') DEFAULT 'media',
    estado ENUM('abierto','en_progreso','pendiente','resuelto','cerrado') DEFAULT 'abierto',
    tecnico_asignado_id INT NULL,
    fecha_resolucion DATETIME NULL,
    fecha_cierre DATETIME NULL,
    sla_horas INT DEFAULT 24,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (solicitante_id) REFERENCES usuarios(id),
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE SET NULL,
    FOREIGN KEY (tecnico_asignado_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_estado (estado),
    INDEX idx_prioridad (prioridad),
    INDEX idx_solicitante (solicitante_id),
    INDEX idx_tecnico (tecnico_asignado_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Comentarios de tickets
CREATE TABLE IF NOT EXISTS ticket_comentarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    usuario_id INT NOT NULL,
    comentario TEXT NOT NULL,
    es_interno TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Adjuntos de tickets
CREATE TABLE IF NOT EXISTS ticket_adjuntos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    usuario_id INT NOT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    nombre_servidor VARCHAR(255) NOT NULL,
    ruta VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100) NULL,
    tamano INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historial de activos
CREATE TABLE IF NOT EXISTS activo_historial (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activo_id INT NOT NULL,
    usuario_id INT NULL,
    accion VARCHAR(100) NOT NULL,
    detalle TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Software instalado
CREATE TABLE IF NOT EXISTS activo_software (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activo_id INT NOT NULL,
    nombre VARCHAR(200) NOT NULL,
    version VARCHAR(50) NULL,
    publisher VARCHAR(200) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Telemetría
CREATE TABLE IF NOT EXISTS telemetria (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activo_id INT NOT NULL,
    cpu_uso DECIMAL(5,2) NULL,
    ram_uso DECIMAL(5,2) NULL,
    disco_uso DECIMAL(5,2) NULL,
    temperatura DECIMAL(5,2) NULL,
    red_in BIGINT NULL,
    red_out BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE CASCADE,
    INDEX idx_activo_fecha (activo_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notificaciones
CREATE TABLE IF NOT EXISTS notificaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    mensaje TEXT NULL,
    leida TINYINT(1) DEFAULT 0,
    url VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    INDEX idx_usuario_leida (usuario_id, leida)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- FASE 2: Mantenimientos
-- =============================================
CREATE TABLE IF NOT EXISTS mantenimientos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activo_id INT NOT NULL,
    tecnico_id INT NULL,
    tipo ENUM('preventivo','correctivo') NOT NULL DEFAULT 'preventivo',
    titulo VARCHAR(255) NOT NULL,
    descripcion TEXT NULL,
    estado ENUM('programado','en_curso','completado','cancelado') NOT NULL DEFAULT 'programado',
    fecha_programada DATE NOT NULL,
    fecha_ejecucion DATETIME NULL,
    fecha_completado DATETIME NULL,
    notas_tecnico TEXT NULL,
    costo DECIMAL(12,2) DEFAULT 0,
    recurrencia ENUM('unica','semanal','mensual','trimestral','semestral','anual') DEFAULT 'unica',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE CASCADE,
    FOREIGN KEY (tecnico_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_estado (estado),
    INDEX idx_fecha (fecha_programada)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FASE 2: Alertas
CREATE TABLE IF NOT EXISTS alertas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activo_id INT NOT NULL,
    tipo_alerta ENUM('disco_lleno','ram_alta','temp_alta','cpu_alta','offline','garantia_vence') NOT NULL,
    mensaje VARCHAR(500) NOT NULL,
    severidad ENUM('info','warning','critical') NOT NULL DEFAULT 'warning',
    resuelta TINYINT(1) DEFAULT 0,
    resuelta_por_id INT NULL,
    resuelta_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE CASCADE,
    FOREIGN KEY (resuelta_por_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_resuelta (resuelta),
    INDEX idx_tipo (tipo_alerta)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FASE 2: Base de Conocimientos
CREATE TABLE IF NOT EXISTS articulos_kb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    autor_id INT NULL,
    categoria_id INT NULL,
    titulo VARCHAR(255) NOT NULL,
    contenido LONGTEXT NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    tags VARCHAR(500) NULL,
    vistas INT DEFAULT 0,
    util_si INT DEFAULT 0,
    util_no INT DEFAULT 0,
    publicado TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (autor_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL,
    INDEX idx_publicado (publicado),
    FULLTEXT INDEX ft_kb (titulo, contenido, tags)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- FASE 2: Depreciación
CREATE TABLE IF NOT EXISTS depreciacion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activo_id INT NOT NULL UNIQUE,
    metodo ENUM('linea_recta','acelerada') DEFAULT 'linea_recta',
    vida_util_meses INT DEFAULT 36,
    valor_residual DECIMAL(12,2) DEFAULT 0,
    depreciacion_mensual DECIMAL(12,2) DEFAULT 0,
    valor_actual DECIMAL(12,2) DEFAULT 0,
    ultimo_calculo DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (activo_id) REFERENCES activos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
