IF OBJECT_ID('dbo.P_ENVIO_RETORNO_PAGINADO') IS NOT NULL
    DROP PROCEDURE dbo.P_ENVIO_RETORNO_PAGINADO;
GO

CREATE PROCEDURE dbo.P_ENVIO_RETORNO_PAGINADO
(
    @DtIni DATE,
    @DtFim DATE,
    @Status VARCHAR(10) = NULL, -- 'OK' | 'PENDENTE'
    @Pagina INT = 1,
    @TamPagina INT = 50
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Inicio INT = ((@Pagina - 1) * @TamPagina) + 1;
    DECLARE @Fim    INT =  @Pagina * @TamPagina;

    ------------------------------------------------------------
    -- TABELA TEMP
    ------------------------------------------------------------
    CREATE TABLE #DADOS
    (
        RN INT NOT NULL,
        DT_CADASTRO DATETIME,
        NM_USU VARCHAR(200),
        ID_TRANSACAO VARCHAR(100),
        DS_EVENTO VARCHAR(10),
        TP_SANEAMENTO VARCHAR(20),
        DS_STATUS VARCHAR(50),
        CD_CTR INT
    );

    ------------------------------------------------------------
    -- CARGA UNICA
    ------------------------------------------------------------
    INSERT INTO #DADOS
    SELECT
        ROW_NUMBER() OVER (
            ORDER BY ENVIO.DTHR_CAD,
                     RET.ID_TRANSACAO,
                     DS_EVENTO
        ) AS RN,

        ENVIO.DTHR_CAD,

        CONVERT(VARCHAR, USU.CD_USU) + ' - ' + USU.NM_USU AS NM_USU,

        RET.ID_TRANSACAO,

        EVT.DS_EVENTO,

        'MANUAL' AS TP_SANEAMENTO,

        ISNULL(RET.MENSAGEM, 'Com Erro') AS DS_STATUS,

        ENVIO.CD_CTR
    FROM GT_ENVIO_EMITENTE ENVIO
    INNER JOIN GLB_USU USU
        ON ISNULL(ENVIO.CD_USU, 1) = USU.CD_USU

    LEFT JOIN GT_ENVIO_EMITENTE_GT_RETORNO_CABECALHO LNK
        ON LNK.CD_CTR_ENVIO = ENVIO.CD_CTR
    LEFT JOIN GT_RETORNO_CABECALHO RET
        ON RET.CD_CTR = LNK.CD_CTR_RETORNO

    CROSS APPLY (
        SELECT 'ENVIO'
        WHERE ENVIO.JSON_ENVIADO IS NOT NULL
        UNION ALL
        SELECT 'RETORNO'
        WHERE ENVIO.JSON_RETORNO IS NOT NULL
    ) EVT(DS_EVENTO)

    WHERE CONVERT(DATE, ENVIO.DTHR_CAD) BETWEEN @DtIni AND @DtFim
      AND (
            @Status IS NULL
            OR (@Status = 'OK' AND ISNULL(RET.MENSAGEM,'') = 'OK')
            OR (@Status = 'PENDENTE' AND ISNULL(RET.MENSAGEM,'') <> 'OK')
          );

    CREATE CLUSTERED INDEX IX_DADOS_RN ON #DADOS (RN);

    ------------------------------------------------------------
    -- 1 RECORDSET - TOTAL
    ------------------------------------------------------------
    SELECT COUNT(1) AS TOTAL
    FROM #DADOS;

    ------------------------------------------------------------
    -- 2 RECORDSET - PÁGINA
    ------------------------------------------------------------
    SELECT
        DT_CADASTRO,
        NM_USU,
        ID_TRANSACAO,
        DS_EVENTO,
        TP_SANEAMENTO,
        DS_STATUS,
        CD_CTR
    FROM #DADOS
    WHERE RN BETWEEN @Inicio AND @Fim
    ORDER BY RN;
END;
GO
