-- ========================================
-- SOLUCIÓN COMPLETA: TRÁMITES DERIVADOS
-- ========================================

-- PROBLEMA RAÍZ:
-- Cuando Mesa de Partes REGISTRA un trámite interno, crea un movimiento con estado VACÍO
-- Cuando otra área DERIVA ese trámite, el SP busca mov_estatus='PENDIENTE' y no lo encuentra
-- Por eso no actualiza el movimiento anterior a 'DERIVADO'

-- ========================================
-- PASO 1: Corregir movimientos existentes con estado VACÍO
-- ========================================

UPDATE movimiento
SET mov_estatus = 'PENDIENTE'
WHERE (mov_estatus = '' OR mov_estatus IS NULL)
AND area_destino_id IS NOT NULL
AND movimiento_id IN (
    -- Solo actualizar los que son el ÚLTIMO movimiento de su documento
    SELECT * FROM (
        SELECT MAX(m2.movimiento_id)
        FROM movimiento m2
        GROUP BY m2.documento_id
    ) AS ultimos
);

SELECT '✅ Movimientos existentes con estado vacío actualizados a PENDIENTE' AS Resultado;

-- También actualizar los que NO son el último (deben ser DERIVADO)
UPDATE movimiento
SET mov_estatus = 'DERIVADO'
WHERE (mov_estatus = '' OR mov_estatus IS NULL)
AND area_destino_id IS NOT NULL
AND movimiento_id NOT IN (
    -- Excluir los que son el ÚLTIMO movimiento
    SELECT * FROM (
        SELECT MAX(m2.movimiento_id)
        FROM movimiento m2
        GROUP BY m2.documento_id
    ) AS ultimos
);

SELECT '✅ Movimientos anteriores actualizados a DERIVADO' AS Resultado;

-- ========================================
-- PASO 2: Corregir SP_REGISTRAR_TRAMITE_DERIVAR
-- ========================================

DROP PROCEDURE IF EXISTS SP_REGISTRAR_TRAMITE_DERIVAR;

DELIMITER $$

CREATE PROCEDURE SP_REGISTRAR_TRAMITE_DERIVAR(
    IN IDDO VARCHAR(50),
    IN ORIG INT,
    IN DEST INT,
    IN DESCRIP VARCHAR(255),
    IN IDUSU INT,
    IN RUTA VARCHAR(255),
    IN ACCION VARCHAR(20),
    IN VISIBLE_PUBLICO TINYINT
)
BEGIN
    DECLARE ESTATUS VARCHAR(20);

    -- Determinar el estatus del NUEVO movimiento
    IF ACCION = 'DERIVAR' THEN
        SET ESTATUS = 'PENDIENTE';
    ELSE
        SET ESTATUS = 'FINALIZADO';
    END IF;

    -- PASO 1: Actualizar el movimiento ANTERIOR
    -- IMPORTANTE: Buscar por estado PENDIENTE O VACÍO
    UPDATE movimiento
    SET mov_estatus = ACCION
    WHERE documento_id = IDDO
    AND area_destino_id = ORIG
    AND (mov_estatus = 'PENDIENTE' OR mov_estatus = '' OR mov_estatus IS NULL)
    ORDER BY movimiento_id DESC
    LIMIT 1;

    -- PASO 2: Insertar el NUEVO movimiento
    INSERT INTO movimiento(
        documento_id,
        area_origen_id,
        area_destino_id,
        mov_descripcion,
        mov_estatus,
        mov_fecharegistro,
        usuario_id,
        mov_archivo,
        mov_visible_publico
    ) VALUES (
        IDDO,
        ORIG,
        IF(DEST = 0 OR DEST IS NULL, NULL, DEST),
        DESCRIP,
        ESTATUS,
        NOW(),
        IDUSU,
        RUTA,
        VISIBLE_PUBLICO
    );

    -- PASO 3: Actualizar el documento
    UPDATE documento SET
        doc_estatus = IF(ACCION = 'FINALIZAR', 'FINALIZADO', doc_estatus)
    WHERE documento_id = IDDO;
END$$

DELIMITER ;

SELECT '✅ SP_REGISTRAR_TRAMITE_DERIVAR corregido' AS Resultado;

-- ========================================
-- PASO 3: SP_LISTAR_TRAMITE_AREA (Trámites Recibidos)
-- ========================================

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;
    SET @IDAREA := (SELECT area_id FROM usuario WHERE usu_id = IDUSUARIO);

    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
        movimiento.area_destino_id = @IDAREA
        AND movimiento.mov_estatus = 'PENDIENTE'
        AND movimiento.movimiento_id = (
            SELECT MAX(m2.movimiento_id)
            FROM movimiento m2
            WHERE m2.documento_id = movimiento.documento_id
        )
        AND (TIPO_RE = '' OR documento.doc_representacion = TIPO_RE)
    ORDER BY movimiento.mov_fecharegistro DESC;
END$$

DELIMITER ;

SELECT '✅ SP_LISTAR_TRAMITE_AREA corregido' AS Resultado;

-- ========================================
-- PASO 4: SP_LISTAR_TRAMITE_AREA_DERIVADO (Trámites Derivados)
-- ========================================

DROP PROCEDURE IF EXISTS SP_LISTAR_TRAMITE_AREA_DERIVADO;

DELIMITER $$

CREATE PROCEDURE SP_LISTAR_TRAMITE_AREA_DERIVADO(
    IN IDUSUARIO INT
)
BEGIN
    DECLARE IDAREA INT;
    SET @IDAREA := (SELECT area_id FROM usuario WHERE usu_id = IDUSUARIO);

    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
        movimiento.area_origen_id = @IDAREA
        AND movimiento.mov_estatus = 'DERIVADO'
    ORDER BY movimiento.mov_fecharegistro DESC;
END$$

DELIMITER ;

SELECT '✅ SP_LISTAR_TRAMITE_AREA_DERIVADO corregido' AS Resultado;

-- ========================================
-- VERIFICACIÓN FINAL
-- ========================================

SELECT '📊 Estado final de movimientos del documento 001-2025:' AS Info;

SELECT
    movimiento_id,
    area_origen_id,
    area_destino_id,
    mov_estatus,
    mov_fecharegistro,
    CASE
        WHEN movimiento_id = (
            SELECT MAX(m2.movimiento_id)
            FROM movimiento m2
            WHERE m2.documento_id = movimiento.documento_id
        ) THEN '✅ ÚLTIMO'
        ELSE '⬜ ANTERIOR'
    END AS posicion
FROM movimiento
WHERE documento_id = '001-2025'
ORDER BY movimiento_id;

SELECT '✅ ¡SOLUCIÓN COMPLETA APLICADA!' AS Resultado;
SELECT 'Ahora prueba derivar un nuevo trámite y verifica que aparece en Trámites Derivados' AS Instruccion;
