Recorrência - Vendedores LP v2

/**
 * @OnlyCurrentDoc
 */

// --- CONFIGURAÇÕES ---
const ABA_CONSOLIDADA = "Base_Consolidada_Vendedores";
const COLUNA_CICLO = 1;     // Coluna A
const COLUNA_NOME = 2;      // Coluna B
const COLUNA_ID_UNICO = 3;  // Coluna C (CPF)
const COLUNA_EMAIL = 4;     // Coluna D
// --------------------

/**
 * Cria um menu personalizado na interface da planilha.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('📈 Análise de Vendedores')
    .addItem('Gerar Relatório de Recorrência', 'gerarRelatorioRecorrencia')
    .addToUi();
}

/**
 * Função principal que gera o relatório de recorrência (VERSÃO CORRIGIDA)
 */
function gerarRelatorioRecorrencia() {
  const ui = SpreadsheetApp.getUi();
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // 1. LER OS DADOS CONSOLIDADOS
  const abaConsolidada = spreadsheet.getSheetByName(ABA_CONSOLIDADA);
  if (!abaConsolidada) {
    ui.alert(`Erro: A aba "${ABA_CONSOLIDADA}" não foi encontrada! Verifique o nome da aba.`);
    return;
  }

  const dadosRange = abaConsolidada.getRange(2, 1, abaConsolidada.getLastRow() - 1, abaConsolidada.getLastColumn());
  // Usar getDisplayValues() para pegar as datas como texto (ex: "3/2024"), não como números de série
  const dados = dadosRange.getDisplayValues();

  // 2. PROCESSAR E AGRUPAR OS DADOS
  const vendedores = {};
  let todosOsCiclos = new Set();

  dados.forEach(linha => {
    const idLimpo = String(linha[COLUNA_ID_UNICO - 1]).replace(/\D/g, '');
    if (!idLimpo) return;

    const nome = linha[COLUNA_NOME - 1];
    const email = linha[COLUNA_EMAIL - 1];

    // Pega o valor da coluna ciclo (já como texto "3/2024" graças ao getDisplayValues())
    const ciclo = linha[COLUNA_CICLO - 1];

    // Se o ciclo for um texto válido, adiciona ao nosso conjunto
    if (ciclo && ciclo.includes('/')) {
        todosOsCiclos.add(ciclo);
    } else {
        return; // Pula linhas onde a coluna de ciclo não está no formato esperado
    }

    if (!vendedores[idLimpo]) {
      vendedores[idLimpo] = {
        nome: nome,
        email: email,
        ciclos: new Set(),
      };
    }
    vendedores[idLimpo].ciclos.add(ciclo);
  });

  // Ordena os ciclos de forma cronológica
  const ciclosOrdenados = Array.from(todosOsCiclos).sort((a, b) => {
      const [mesA, anoA] = a.split('/');
      const [mesB, anoB] = b.split('/');
      if (anoA !== anoB) return parseInt(anoA) - parseInt(anoB);
      return parseInt(mesA) - parseInt(mesB);
  });

  const totalDeCiclosPossiveis = ciclosOrdenados.length;

  if (totalDeCiclosPossiveis === 0) {
      ui.alert("Atenção!", "Nenhum ciclo no formato 'mês/ano' foi encontrado na coluna A. Verifique sua base de dados.", ui.ButtonSet.OK);
      return;
  }

  // 3. PREPARAR OS DADOS PARA O RELATÓRIO (o resto do código permanece o mesmo)
  const dadosDoRelatorio = [];
  const cabecalhos = ["CPF (ID)", "Nome do Vendedor", "E-mail", "Nº de Participações", "Status de Recorrência"];
  ciclosOrdenados.forEach(ciclo => cabecalhos.push(String(ciclo)));
  dadosDoRelatorio.push(cabecalhos);

  for (const id in vendedores) {
    const vendedor = vendedores[id];
    const numParticipacoes = vendedor.ciclos.size;

    let status = `${numParticipacoes} de ${totalDeCiclosPossiveis} ciclos`;
    if (numParticipacoes === totalDeCiclosPossiveis) {
      status = "✅ Engajamento Máximo";
    } else if (numParticipacoes === 1) {
      status = "⚠️ Participação Única";
    }

    const linhaRelatorio = [id, vendedor.nome, vendedor.email, numParticipacoes, status];

    ciclosOrdenados.forEach(ciclo => {
      linhaRelatorio.push(vendedor.ciclos.has(ciclo) ? "Sim" : "");
    });

    dadosDoRelatorio.push(linhaRelatorio);
  }

  dadosDoRelatorio.sort((a, b) => {
    if (typeof a[3] === 'string') return -1;
    if (typeof b[3] === 'string') return 1;
    return b[3] - a[3];
  });

  // 4. ESCREVER O RELATÓRIO NA PLANILHA
  let abaRelatorio = spreadsheet.getSheetByName("Relatorio_Recorrencia");
  if (abaRelatorio) {
    abaRelatorio.clear();
  } else {
    abaRelatorio = spreadsheet.insertSheet("Relatorio_Recorrencia");
  }

  abaRelatorio.getRange(1, 1, dadosDoRelatorio.length, dadosDoRelatorio[0].length).setValues(dadosDoRelatorio);

  // 5. FORMATAR O RELATÓRIO
  formatarRelatorio(abaRelatorio); // Reutilizando a função que já temos

  ui.alert("Sucesso!", `O Relatório de Recorrência foi gerado para ${totalDeCiclosPossiveis} ciclo(s) único(s) encontrado(s).`, ui.ButtonSet.OK);
}

/**
 * Função auxiliar para formatar a aba de relatório.
 */
function formatarRelatorio(sheet) {
  sheet.autoResizeColumns(1, sheet.getLastColumn());
  sheet.setFrozenRows(1);
  sheet.getRange("1:1").setFontWeight("bold").setHorizontalAlignment("center");
  sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).applyRowBanding();
  sheet.getRange("D:Z").setHorizontalAlignment("center"); // Centraliza colunas de números e "Sim"
}

/**
 * ==================================================================
 *  NOVO RELATÓRIO: ANÁLISE DE DESEMPENHO E INTENSIDADE
 * ==================================================================
 */

/**
 * Adiciona o novo relatório ao menu.
 * (Esta função deve ser mesclada com sua onOpen existente se você já tiver uma)
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('📈 Análise de Vendedores')
    .addItem('Gerar Relatório de Recorrência (Sim/Não)', 'gerarRelatorioRecorrencia')
    .addSeparator()
    .addItem('Gerar Relatório de Desempenho (Contagem)', 'gerarRelatorioDesempenho')
    .addToUi();
}

/**
 * Função principal que gera o Relatório de Desempenho.
 */
function gerarRelatorioDesempenho() {
  const ui = SpreadsheetApp.getUi();
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  const abaConsolidada = spreadsheet.getSheetByName(ABA_CONSOLIDADA);
  if (!abaConsolidada) {
    ui.alert(`Erro: A aba "${ABA_CONSOLIDADA}" não foi encontrada!`);
    return;
  }

  const dados = abaConsolidada.getRange(2, 1, abaConsolidada.getLastRow() - 1, abaConsolidada.getLastColumn()).getDisplayValues();

  // Objeto para armazenar dados detalhados dos vendedores
  const vendedores = {};
  let todosOsCiclos = new Set();

  dados.forEach(linha => {
    const idLimpo = String(linha[COLUNA_ID_UNICO - 1]).replace(/\D/g, '');
    if (!idLimpo) return;

    const nome = linha[COLUNA_NOME - 1];
    const email = linha[COLUNA_EMAIL - 1];
    const ciclo = linha[COLUNA_CICLO - 1];

    if (!ciclo || !ciclo.includes('/')) return;
    todosOsCiclos.add(ciclo);

    // Se o vendedor é novo, inicializa sua estrutura de dados
    if (!vendedores[idLimpo]) {
      vendedores[idLimpo] = {
        nome: nome,
        email: email,
        participacoesPorCiclo: {} // Objeto para contar participações por ciclo
      };
    }

    // Inicializa o contador para o ciclo se for a primeira vez
    if (!vendedores[idLimpo].participacoesPorCiclo[ciclo]) {
      vendedores[idLimpo].participacoesPorCiclo[ciclo] = 0;
    }
    // Incrementa a contagem de participações para aquele vendedor naquele ciclo
    vendedores[idLimpo].participacoesPorCiclo[ciclo]++;
  });

  const ciclosOrdenados = Array.from(todosOsCiclos).sort((a, b) => {
      const [mesA, anoA] = a.split('/'); const [mesB, anoB] = b.split('/');
      if (anoA !== anoB) return parseInt(anoA) - parseInt(anoB);
      return parseInt(mesA) - parseInt(mesB);
  });
  const totalDeCiclosPossiveis = ciclosOrdenados.length;

  const dadosDoRelatorio = [];
  const cabecalhos = [
    "CPF (ID)", "Nome do Vendedor", "E-mail", "Total de Prêmios",
    "Ciclos Distintos", "Status de Recorrência"
  ];
  ciclosOrdenados.forEach(ciclo => cabecalhos.push(`Prêmios em ${ciclo}`));
  dadosDoRelatorio.push(cabecalhos);

  for (const id in vendedores) {
    const vendedor = vendedores[id];

    const numCiclosDistintos = Object.keys(vendedor.participacoesPorCiclo).length;
    const totalPremios = Object.values(vendedor.participacoesPorCiclo).reduce((sum, count) => sum + count, 0);

    let status = `${numCiclosDistintos} de ${totalDeCiclosPossiveis} ciclos`;
    if (numCiclosDistintos === totalDeCiclosPossiveis) {
      status = "✅ Recorrência Máxima";
    }

    const linhaRelatorio = [id, vendedor.nome, vendedor.email, totalPremios, numCiclosDistintos, status];

    ciclosOrdenados.forEach(ciclo => {
      linhaRelatorio.push(vendedor.participacoesPorCiclo[ciclo] || 0);
    });

    dadosDoRelatorio.push(linhaRelatorio);
  }

  dadosDoRelatorio.sort((a, b) => {
    if (typeof a[3] === 'string') return -1;
    if (typeof b[3] === 'string') return 1;
    return b[3] - a[3]; // Ordena pelo Total de Prêmios
  });

  let abaRelatorio = spreadsheet.getSheetByName("Relatorio_Desempenho");
  if (abaRelatorio) {
    abaRelatorio.clear();
  } else {
    abaRelatorio = spreadsheet.insertSheet("Relatorio_Desempenho");
  }

  abaRelatorio.getRange(1, 1, dadosDoRelatorio.length, dadosDoRelatorio[0].length).setValues(dadosDoRelatorio);

  formatarRelatorio(abaRelatorio);

  ui.alert("Sucesso!", "O Relatório de Desempenho foi gerado na aba 'Relatorio_Desempenho'.", ui.ButtonSet.OK);
}