WITH VendasFaturadas AS (
    SELECT 
        v.ID,
        v.DATA_EMISSAO,
        CAST(CURRENT_DATE - 180 AS DATE) AS DATA_INICIAL_REL,
        CAST(CURRENT_DATE AS DATE) AS DATA_FINAL_REL
    FROM VENDAS v
    WHERE v.STATUS = 'Venda faturada'
      AND v.DATA_EMISSAO >= CURRENT_DATE - 180
),               
RankingPrincipais AS (
    SELECT FIRST 100        
        vi.ID_PRODUTO,
        p.CODIGO AS COD_PRINCIPAL,
        p.DESCRICAO AS NOME_PRINCIPAL,
        COUNT(DISTINCT v.ID) AS PONTOS_PRINCIPAL,
        SUM(vi.QUANTIDADE) AS QTD_TOTAL_ITENS, -- Novo campo solicitado
        v.DATA_INICIAL_REL,
        v.DATA_FINAL_REL,
        CAST(SUM(vi.VALOR_TOTAL) AS NUMERIC(15,2)) AS VALOR_TOTAL_FAT,
        CAST(SUM(vi.VALOR_TOTAL - (p.PRECO_CUSTO * vi.QUANTIDADE)) AS NUMERIC(15,2)) AS LUCRO_TOTAL
    FROM VendasFaturadas v
    JOIN VENDAS_ITENS vi ON vi.ID_VENDA = v.ID
    JOIN PRODUTOS p ON p.ID = vi.ID_PRODUTO
    GROUP BY vi.ID_PRODUTO, p.CODIGO, p.DESCRICAO, v.DATA_INICIAL_REL, v.DATA_FINAL_REL
    ORDER BY 4 DESC
),
ContagemItensJuntos AS (
    SELECT 
        rp.COD_PRINCIPAL,
        rp.ID_PRODUTO,
        rp.NOME_PRINCIPAL,
        rp.PONTOS_PRINCIPAL,
        rp.QTD_TOTAL_ITENS, -- Transportando o campo
        rp.VALOR_TOTAL_FAT,
        rp.LUCRO_TOTAL,
        rp.DATA_INICIAL_REL,
        rp.DATA_FINAL_REL,
        p.CODIGO AS COD_SECUNDARIO,
        p.DESCRICAO AS NOME_SECUNDARIO,
        COUNT(DISTINCT vi.ID_VENDA) AS PONTOS_JUNTO,
        ROW_NUMBER() OVER(
            PARTITION BY rp.COD_PRINCIPAL 
            ORDER BY COUNT(DISTINCT vi.ID_VENDA) DESC, p.DESCRICAO ASC
        ) AS RANK_SUGESTAO
    FROM RankingPrincipais rp
    JOIN VENDAS_ITENS vi_p ON vi_p.ID_PRODUTO = rp.ID_PRODUTO
    JOIN VENDAS_ITENS vi ON vi.ID_VENDA = vi_p.ID_VENDA
    JOIN PRODUTOS p ON p.ID = vi.ID_PRODUTO
    JOIN VendasFaturadas v ON v.ID = vi.ID_VENDA
    WHERE vi.ID_PRODUTO <> rp.ID_PRODUTO 
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
)

SELECT * FROM (
    -- LINHA DO PRODUTO PRINCIPAL
    SELECT 
        rp.PONTOS_PRINCIPAL AS ORDENACAO_GLOBAL,
        rp.COD_REF_ORDEM AS COD_REF,
        rp.NOME_PRINCIPAL AS NOME_REF,
        CAST(rp.PONTOS_PRINCIPAL AS VARCHAR(10)) AS TOTAL_VDS,
        rp.QTD_TOTAL_ITENS AS QTD_ITEM_PRINCIPAL, -- Exibindo no principal
        
        'R$ ' || 
        CASE 
            WHEN rp.VALOR_TOTAL_FAT >= 1000 THEN 
                REPLACE(CAST(TRUNC(rp.VALOR_TOTAL_FAT/1000) AS VARCHAR(20)), '.', ',') || '.' || 
                LPAD(REPLACE(CAST(MOD(ABS(rp.VALOR_TOTAL_FAT), 1000) AS NUMERIC(15,2)), '.', ','), 6, '0')
            ELSE 
                REPLACE(CAST(rp.VALOR_TOTAL_FAT AS VARCHAR(20)), '.', ',')
        END AS VALOR_CLI_FMT,

        'R$ ' || 
        CASE 
            WHEN rp.LUCRO_TOTAL >= 1000 THEN 
                REPLACE(CAST(TRUNC(rp.LUCRO_TOTAL/1000) AS VARCHAR(20)), '.', ',') || '.' || 
                LPAD(REPLACE(CAST(MOD(ABS(rp.LUCRO_TOTAL), 1000) AS NUMERIC(15,2)), '.', ','), 6, '0')
            ELSE 
                REPLACE(CAST(rp.LUCRO_TOTAL AS VARCHAR(20)), '.', ',')
        END AS LUCRO_CLI_FMT,

        999.99 AS PERCENTUAL_NUM,
        '' AS PERCENTUAL_FMT,
        '' AS COD_SUGESTAO,
        '' AS NOME_SUGESTAO,
        CAST(NULL AS VARCHAR(20)) AS PONTOS_SUGESTAO,
        rp.DATA_INICIAL_REL,
        rp.DATA_FINAL_REL,
        1 AS ORDEM,
        'PRINCIPAL' AS TIPO_LINHA
    FROM (SELECT r.*, r.COD_PRINCIPAL AS COD_REF_ORDEM FROM RankingPrincipais r) rp
    WHERE EXISTS (
        SELECT 1 FROM ContagemItensJuntos c 
        WHERE c.ID_PRODUTO = rp.ID_PRODUTO 
          AND c.RANK_SUGESTAO <= 5 
          AND (c.PONTOS_JUNTO * 100.0 / NULLIF(c.PONTOS_PRINCIPAL, 0)) >= 5
    )

    UNION ALL

    -- LINHAS DE SUGESTÕES
    SELECT 
        c.PONTOS_PRINCIPAL AS ORDENACAO_GLOBAL,
        c.COD_PRINCIPAL AS COD_REF,
        '' AS NOME_REF,
        '' AS TOTAL_VDS,
        NULL AS QTD_ITEM_PRINCIPAL, -- Vazio para sugestões
        NULL AS VALOR_CLI_FMT,
        NULL AS LUCRO_CLI_FMT,
        (c.PONTOS_JUNTO * 100.0 / NULLIF(c.PONTOS_PRINCIPAL, 0)) AS PERCENTUAL_NUM,
        REPLACE(CAST(ROUND(c.PONTOS_JUNTO * 100.0 / NULLIF(c.PONTOS_PRINCIPAL, 0), 1) AS VARCHAR(20)), '.', ',') || ' %' AS PERCENTUAL_FMT,
        c.COD_SECUNDARIO AS COD_SUGESTAO,
        c.NOME_SECUNDARIO AS NOME_SUGESTAO,
        CAST(c.PONTOS_JUNTO AS VARCHAR(10)) || ' / ' || CAST(c.PONTOS_PRINCIPAL AS VARCHAR(10)) AS PONTOS_SUGESTAO,
        c.DATA_INICIAL_REL,
        c.DATA_FINAL_REL,
        2 AS ORDEM,
        'SUGESTAO' AS TIPO_LINHA
    FROM ContagemItensJuntos c
    WHERE c.RANK_SUGESTAO <= 5 
      AND (c.PONTOS_JUNTO * 100.0 / NULLIF(c.PONTOS_PRINCIPAL, 0)) >= 5
)
ORDER BY ORDENACAO_GLOBAL DESC, COD_REF, ORDEM ASC, PERCENTUAL_NUM DESC;