-- ========================================
-- CORREGIR: SP_LISTAR_TRAMITE_AREA_DERIVADO
-- ========================================
-- SIN usar la tabla expediente (que no existe)
-- Basado en la estructura real de las tablas

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 trámites DERIVADOS desde el área del usuario
    SELECT
        -- Número de expediente (extraído del documento_id)
        SUBSTRING_INDEX(documento.documento_id, '-', 1) AS expe_num,

        -- Información del documento
        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 de documento
        tipo_documento.tipodocumento_id AS tipodoc_id,
        tipo_documento.tipodoc_descripcion,

        -- TUPA
        tupa.tupa_id,
        tupa.tupa_descripcion,
        tupa.tupa_requisitos,

        -- Información del movimiento
        movimiento.movimiento_id,
        movimiento.area_origen_id,
        movimiento.area_destino_id,
        movimiento.mov_estatus,
        movimiento.mov_descripcion,
        movimiento.mov_archivo,
        movimiento.mov_fecharegistro,

        -- Áreas (origen y destino)
        areaorigen.area_nombre AS origen,
        areadestino.area_nombre AS destino,

        -- Grupo (si existe)
        documento.grupo_area_destino_id,
        grupo.area_nombre AS grupo,

        -- Remitente completo
        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 movimiento.area_origen_id = @IDAREA
    AND movimiento.mov_estatus = 'DERIVADO'

    ORDER BY movimiento.mov_fecharegistro DESC;
END$$

DELIMITER ;

-- Verificar creación
SELECT 'SP_LISTAR_TRAMITE_AREA_DERIVADO corregido y creado exitosamente' AS Resultado;

-- Test
-- CALL SP_LISTAR_TRAMITE_AREA_DERIVADO(62);
