-- ========================================
-- SOLUCIÓN ÓPTIMA FINAL
-- ========================================

-- ESTRATEGIA:
-- 1. Agregar campo mov_recibido (0 = pendiente, 1 = procesado/derivado)
-- 2. Cuando se deriva, se marca mov_recibido=1 y se crea nuevo movimiento con mov_recibido=0
-- 3. Trámites Recibidos: mov_estatus='DERIVADO' AND mov_recibido=0
-- 4. Trámites Derivados: mov_estatus='DERIVADO' AND mov_recibido=1

-- ========================================
-- PASO 1: Agregar columna mov_recibido
-- ========================================

ALTER TABLE movimiento
ADD COLUMN mov_recibido TINYINT(1) NOT NULL DEFAULT 0
COMMENT '0=Pendiente de recibir, 1=Ya procesado/derivado';

SELECT '✅ Columna mov_recibido agregada' AS Resultado;

-- Actualizar datos existentes
-- Movimientos PENDIENTES antiguos: mov_recibido = 0 (aún no procesados)
UPDATE movimiento SET mov_recibido = 0
WHERE mov_estatus = 'PENDIENTE' OR mov_estatus = '' OR mov_estatus IS NULL;

-- Movimientos DERIVADO/FINALIZADO: mov_recibido = 1 (ya procesados)
UPDATE movimiento SET mov_recibido = 1
WHERE mov_estatus IN ('DERIVADO', 'FINALIZADO');

SELECT '✅ Datos existentes actualizados' 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,           -- Área del usuario que está derivando
    IN DEST INT,           -- Área de destino
    IN DESCRIP VARCHAR(255),
    IN IDUSU INT,
    IN RUTA VARCHAR(255),
    IN ACCION VARCHAR(20), -- 'DERIVAR' o 'FINALIZAR'
    IN VISIBLE_PUBLICO TINYINT
)
BEGIN
    -- PASO 1: Actualizar el movimiento anterior
    -- Cambiar de PENDIENTE a DERIVADO y marcar como procesado (mov_recibido=1)
    UPDATE movimiento
    SET
        mov_estatus = 'DERIVADO',
        mov_recibido = 1
    WHERE documento_id = IDDO
    AND area_destino_id = ORIG
    AND mov_estatus = 'PENDIENTE'
    AND mov_recibido = 0
    ORDER BY movimiento_id DESC
    LIMIT 1;

    -- PASO 2: Insertar el NUEVO movimiento
    IF ACCION = 'DERIVAR' THEN
        INSERT INTO movimiento(
            documento_id,
            area_origen_id,
            area_destino_id,
            mov_descripcion,
            mov_estatus,
            mov_fecharegistro,
            usuario_id,
            mov_archivo,
            mov_visible_publico,
            mov_recibido        -- NUEVO CAMPO
        ) VALUES (
            IDDO,
            ORIG,               -- Quien deriva
            DEST,               -- A quién deriva
            DESCRIP,
            'PENDIENTE',        -- Estado PENDIENTE (para que aparezca en Recibidos)
            NOW(),
            IDUSU,
            RUTA,
            VISIBLE_PUBLICO,
            0                   -- mov_recibido=0 (pendiente de recibir)
        );
    ELSE
        -- FINALIZAR
        INSERT INTO movimiento(
            documento_id,
            area_origen_id,
            area_destino_id,
            mov_descripcion,
            mov_estatus,
            mov_fecharegistro,
            usuario_id,
            mov_archivo,
            mov_visible_publico,
            mov_recibido
        ) VALUES (
            IDDO,
            ORIG,
            NULL,               -- Sin destino
            DESCRIP,
            'FINALIZADO',
            NOW(),
            IDUSU,
            RUTA,
            VISIBLE_PUBLICO,
            1                   -- mov_recibido=1 (ya está finalizado)
        );

        -- Actualizar documento
        UPDATE documento SET doc_estatus = 'FINALIZADO' WHERE documento_id = IDDO;
    END IF;
END$$

DELIMITER ;

SELECT '✅ SP_REGISTRAR_TRAMITE_DERIVAR corregido' AS Resultado;

-- ========================================
-- PASO 3: SP_LISTAR_TRAMITE_AREA (Trámites Recibidos)
-- ========================================
-- Mostrar trámites con mov_estatus='PENDIENTE' y mov_recibido=0

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'     -- Estado PENDIENTE
        AND movimiento.mov_recibido = 0              -- Pendiente de recibir
        AND movimiento.movimiento_id = (
            SELECT MAX(m2.movimiento_id)
            FROM movimiento m2
            WHERE m2.documento_id = movimiento.documento_id
            AND m2.area_destino_id = @IDAREA
            AND m2.mov_estatus = 'PENDIENTE'
            AND m2.mov_recibido = 0
        )
        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)
-- ========================================
-- Mostrar trámites donde mov_recibido=1 (ya procesados/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'
        AND movimiento.mov_recibido = 1             -- CLAVE: Ya fue procesado
    ORDER BY movimiento.mov_fecharegistro DESC;
END$$

DELIMITER ;

SELECT '✅ SP_LISTAR_TRAMITE_AREA_DERIVADO corregido' AS Resultado;

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

SELECT '✅ ¡SOLUCIÓN ÓPTIMA APLICADA!' AS Resultado;
SELECT 'Ventajas:' AS Info;
SELECT '1. UN SOLO movimiento por derivación (no duplica datos)' AS Ventaja1;
SELECT '2. mov_recibido=0 → Aparece en Recibidos' AS Ventaja2;
SELECT '3. mov_recibido=1 → Aparece en Derivados' AS Ventaja3;
SELECT '4. Permite derivar a la misma área múltiples veces' AS Ventaja4;
