-- ========================================
-- CORRECCIÓN: SP_LISTAR_TRAMITE_AREA (Trámites Recibidos)
-- ========================================
-- PROBLEMA: Muestra trámites que ya fueron derivados/finalizados desde el área actual
-- SOLUCIÓN: Solo mostrar el ÚLTIMO movimiento de cada documento que tenga estado PENDIENTE

DROP PROCEDURE IF EXISTS SP_LISTAR_TRAMITE_AREA;

DELIMITER $$

CREATE PROCEDURE SP_LISTAR_TRAMITE_AREA(
    IN IDUSUARIO INT,
    IN TIPO_RE VARCHAR(20)
)
BEGIN
    DECLARE IDAREA INT;

    -- Obtener el área del usuario
    SET @IDAREA := (SELECT area_id FROM usuario WHERE usu_id = IDUSUARIO);

    -- Listar SOLO los trámites donde el ÚLTIMO movimiento está PENDIENTE en mi área
    SELECT
        SUBSTRING_INDEX(documento.documento_id, '-', 1) AS expe_num,
        documento.documento_id,
        documento.doc_nrodocumento,
        documento.doc_dniremitente,
        documento.doc_nombreremitente,
        documento.doc_apepatremitente,
        documento.doc_apematremitente,
        documento.doc_emailremitente,
        documento.doc_celularremitente,
        documento.doc_direccionremitente,
        documento.doc_representacion,
        documento.doc_ruc,
        documento.doc_empresa,
        documento.doc_folio,
        documento.doc_archivo,
        documento.doc_asunto,
        documento.doc_fecharegistro,
        documento.tipodocumento_id,
        documento.area_origen,
        documento.area_destino,
        documento.doc_estatus,
        tipo_documento.tipodocumento_id AS tipodoc_id,
        tipo_documento.tipodoc_descripcion,
        tupa.tupa_id,
        tupa.tupa_descripcion,
        tupa.tupa_requisitos,
        movimiento.movimiento_id,
        movimiento.area_origen_id,
        movimiento.area_destino_id,
        movimiento.mov_estatus,
        movimiento.mov_descripcion,
        movimiento.mov_archivo,
        movimiento.mov_fecharegistro,
        areaorigen.area_nombre AS origen,
        areadestino.area_nombre AS destino,
        documento.grupo_area_destino_id,
        grupo.area_nombre AS grupo,
        CONCAT_WS(' ',
            documento.doc_nombreremitente,
            documento.doc_apepatremitente,
            documento.doc_apematremitente
        ) AS REMITENTE
    FROM movimiento
    INNER JOIN documento ON movimiento.documento_id = documento.documento_id
    LEFT JOIN tipo_documento ON documento.tipodocumento_id = tipo_documento.tipodocumento_id
    LEFT JOIN tupa ON documento.tupa_id = tupa.tupa_id
    LEFT JOIN area AS areaorigen ON movimiento.area_origen_id = areaorigen.area_cod
    LEFT JOIN area AS areadestino ON movimiento.area_destino_id = areadestino.area_cod
    LEFT JOIN area AS grupo ON documento.grupo_area_destino_id = grupo.area_cod
    WHERE
        -- El área destino es MI área
        movimiento.area_destino_id = @IDAREA
        -- El estado es PENDIENTE (no lo he procesado aún)
        AND movimiento.mov_estatus = 'PENDIENTE'
        -- Este es el ÚLTIMO movimiento de este documento
        AND movimiento.movimiento_id = (
            SELECT MAX(m2.movimiento_id)
            FROM movimiento m2
            WHERE m2.documento_id = movimiento.documento_id
        )
        -- Filtro de representación si se proporciona
        AND (TIPO_RE = '' OR documento.doc_representacion = TIPO_RE)
    ORDER BY movimiento.mov_fecharegistro DESC;
END$$

DELIMITER ;

SELECT 'SP_LISTAR_TRAMITE_AREA corregido exitosamente' AS Resultado;

-- ========================================
-- CORRECCIÓN: SP_LISTAR_TRAMITE_AREA_DERIVADO (Trámites Derivados)
-- ========================================
-- CONCEPTO: Mostrar TODOS los trámites que YO derivé desde MI área
-- No importa si fui el origen inicial del documento o no
-- Lo importante es que ME LLEGÓ y YO lo derivé a otra área
-- Diferencia con "Trámites Enviados": Enviados = yo soy el origen INICIAL del documento

DROP PROCEDURE IF EXISTS SP_LISTAR_TRAMITE_AREA_DERIVADO;

DELIMITER $$

CREATE PROCEDURE SP_LISTAR_TRAMITE_AREA_DERIVADO(
    IN IDUSUARIO INT
)
BEGIN
    DECLARE IDAREA INT;

    -- Obtener el área del usuario
    SET @IDAREA := (SELECT area_id FROM usuario WHERE usu_id = IDUSUARIO);

    -- Listar TODOS los trámites que YO DERIVÉ desde MI área
    -- Sin importar si es el último movimiento o no
    -- Lo importante: YO fui el área_origen Y el estado es DERIVADO
    SELECT
        SUBSTRING_INDEX(documento.documento_id, '-', 1) AS expe_num,
        documento.documento_id,
        documento.doc_nrodocumento,
        documento.doc_dniremitente,
        documento.doc_nombreremitente,
        documento.doc_apepatremitente,
        documento.doc_apematremitente,
        documento.doc_emailremitente,
        documento.doc_celularremitente,
        documento.doc_direccionremitente,
        documento.doc_representacion,
        documento.doc_ruc,
        documento.doc_empresa,
        documento.doc_folio,
        documento.doc_archivo,
        documento.doc_asunto,
        documento.doc_fecharegistro,
        documento.tipodocumento_id,
        documento.area_origen,
        documento.area_destino,
        documento.doc_estatus,
        tipo_documento.tipodocumento_id AS tipodoc_id,
        tipo_documento.tipodoc_descripcion,
        tupa.tupa_id,
        tupa.tupa_descripcion,
        tupa.tupa_requisitos,
        movimiento.movimiento_id,
        movimiento.area_origen_id,
        movimiento.area_destino_id,
        movimiento.mov_estatus,
        movimiento.mov_descripcion,
        movimiento.mov_archivo,
        movimiento.mov_fecharegistro,
        areaorigen.area_nombre AS origen,
        areadestino.area_nombre AS destino,
        documento.grupo_area_destino_id,
        grupo.area_nombre AS grupo,
        CONCAT_WS(' ',
            documento.doc_nombreremitente,
            documento.doc_apepatremitente,
            documento.doc_apematremitente
        ) AS REMITENTE
    FROM movimiento
    INNER JOIN documento ON movimiento.documento_id = documento.documento_id
    LEFT JOIN tipo_documento ON documento.tipodocumento_id = tipo_documento.tipodocumento_id
    LEFT JOIN tupa ON documento.tupa_id = tupa.tupa_id
    LEFT JOIN area AS areaorigen ON movimiento.area_origen_id = areaorigen.area_cod
    LEFT JOIN area AS areadestino ON movimiento.area_destino_id = areadestino.area_cod
    LEFT JOIN area AS grupo ON documento.grupo_area_destino_id = grupo.area_cod
    WHERE
        -- YO (mi área) soy el origen de esta derivación
        movimiento.area_origen_id = @IDAREA
        -- El estado es DERIVADO
        AND movimiento.mov_estatus = 'DERIVADO'
        -- NO necesitamos verificar si es el último movimiento
        -- Porque queremos ver TODOS los que derivamos
    ORDER BY movimiento.mov_fecharegistro DESC;
END$$

DELIMITER ;

SELECT 'SP_LISTAR_TRAMITE_AREA_DERIVADO corregido exitosamente' AS Resultado;

-- ========================================
-- VERIFICACIONES
-- ========================================

-- Ver procedimientos creados
SHOW PROCEDURE STATUS WHERE Db = DATABASE()
    AND Name IN ('SP_LISTAR_TRAMITE_AREA', 'SP_LISTAR_TRAMITE_AREA_DERIVADO');

-- Ver últimos movimientos para verificar
SELECT
    m.movimiento_id,
    m.documento_id,
    m.area_origen_id,
    ao.area_nombre AS origen,
    m.area_destino_id,
    ad.area_nombre AS destino,
    m.mov_estatus,
    m.mov_fecharegistro,
    -- ¿Es el último movimiento de este documento?
    CASE
        WHEN m.movimiento_id = (
            SELECT MAX(m2.movimiento_id)
            FROM movimiento m2
            WHERE m2.documento_id = m.documento_id
        ) THEN 'SÍ - ÚLTIMO'
        ELSE 'NO'
    END AS es_ultimo
FROM movimiento m
LEFT JOIN area ao ON m.area_origen_id = ao.area_cod
LEFT JOIN area ad ON m.area_destino_id = ad.area_cod
ORDER BY m.documento_id, m.movimiento_id DESC
LIMIT 20;

-- Instrucciones:
-- 1. Ejecuta este script completo en phpMyAdmin
-- 2. Verifica que ambos SP se crearon correctamente
-- 3. Prueba derivando un trámite:
--    - Mesa de Partes → Gerencia: Debe aparecer en "Recibidos" de Gerencia
--    - Gerencia deriva → Informática: Ya NO debe aparecer en "Recibidos" de Gerencia
--    - Gerencia debe ver ese trámite en "Derivados"
--    - Informática debe verlo en "Recibidos"
