Reem-Notas/app/services/simpli_route/planilha_carga.rb

# app/services/simpli_route/planilha_carga.rb
#
# Monta as linhas da planilha de carga do SimpliRoute a partir de UMA operação
# vigente (tabela gade_entregas_*). Preenche Latitude/Longitude reaproveitando a
# geocodificação do MÊS ANTERIOR da mesma operação: casa nome + endereço +
# supervisão e puxa a lat/long do rastreio (db_reem_simplerout_2026) pelo NF antigo.
#
# Tabelas de operação e rastreio são SOMENTE LEITURA (apenas SELECT).
module SimpliRoute
  class PlanilhaCarga
    # Colunas da tabela de operação usadas (nem toda operação tem todas — as que
    # faltam viram NULL, no mesmo padrão do OperacaoMetricas#selects_gade).
    COLUNAS_OP = %w[
      nota_fiscal nome_completo endereco_sem_complemento complemento supervisao telefones
    ].freeze

    # Colunas que formam a chave de match com o histórico.
    COLUNAS_CHAVE = %w[nome_completo endereco_sem_complemento supervisao].freeze

    # Mês (número) -> grafias aceitas nos nomes de tabela (abreviação e extenso).
    MESES_GRAFIAS = {
      1 => %w[jan janeiro], 2 => %w[fev fevereiro], 3 => %w[mar marco março],
      4 => %w[abr abril], 5 => %w[mai maio], 6 => %w[jun junho], 7 => %w[jul julho],
      8 => %w[ago agosto], 9 => %w[set setembro], 10 => %w[out outubro],
      11 => %w[nov novembro], 12 => %w[dez dezembro]
    }.freeze
    GRAFIA_PARA_NUM = MESES_GRAFIAS.each_with_object({}) do |(num, grafias), h|
      grafias.each { |g| h[g] = num }
    end.freeze

    attr_reader :tabela

    def initialize(tabela)
      @tabela = tabela.to_s
      raise ArgumentError, "Operação inválida: #{@tabela}" unless Operacao.valida?(@tabela)
    end

    # Nome amigável da operação (para o nome do arquivo).
    def label
      Operacao.label(@tabela)
    end

    # A tabela do mês anterior foi encontrada? (para avisar quando não houver histórico)
    def mes_anterior_encontrado?
      tabela_anterior.present?
    end

    # Array de hashes no formato das colunas da planilha (A..N).
    def linhas
      geo = lookup_geo
      registros_operacao.map do |r|
        lat, lng = geo[chave(r)] || [nil, nil]
        {
          titulo:    "NF #{r['nota_fiscal']} - #{r['nome_completo']}".strip,
          endereco:  r['endereco_sem_complemento'],
          carga:     1,
          janela_ini: '08:00',
          janela_fim: '18:00',
          tempo:     10,
          anotacoes: r['complemento'],
          latitude:  lat,
          longitude: lng,
          referencia: r['nota_fiscal'],
          contato:   r['supervisao'],
          telefone:  r['telefones']
        }
      end
    end

    private

    def conn
      ActiveRecord::Base.connection
    end

    # Linhas cruas da operação vigente.
    def registros_operacao
      sql = "SELECT #{selects(@tabela, COLUNAS_OP)} " \
            "FROM #{conn.quote_table_name(@tabela)} g " \
            "WHERE g.nota_fiscal IS NOT NULL"
      conn.select_all(sql).to_a
    end

    # SELECT com as colunas existentes; as ausentes viram NULL com o mesmo alias.
    # As chaves de COLUNAS_* são uma whitelist fixa (sem entrada do usuário no SQL).
    def selects(tabela, colunas)
      existentes = conn.columns(tabela).map(&:name)
      colunas.map do |c|
        existentes.include?(c) ? "g.#{c} AS #{c}" : "CAST(NULL AS text) AS #{c}"
      end.join(', ')
    end

    # Hash { chave_normalizada => [lat, lng] } vindo do mês anterior.
    def lookup_geo
      prev = tabela_anterior
      return {} if prev.nil?

      # Sem as colunas-chave não dá pra casar com segurança.
      cols_prev = conn.columns(prev).map(&:name)
      return {} unless COLUNAS_CHAVE.all? { |c| cols_prev.include?(c) }

      rastreio = conn.quote_table_name(Entrega.table_name)
      sql = <<~SQL
        WITH ultimo AS (
          SELECT reference_id, latitude, longitude,
                 ROW_NUMBER() OVER (PARTITION BY reference_id ORDER BY checkout DESC NULLS LAST) AS rn
          FROM #{rastreio}
          WHERE reference_id IS NOT NULL
        )
        SELECT #{selects(prev, COLUNAS_CHAVE)}, r.latitude AS lat, r.longitude AS lng
        FROM #{conn.quote_table_name(prev)} g
        INNER JOIN ultimo r ON r.reference_id::text = g.nota_fiscal AND r.rn = 1
        WHERE r.latitude IS NOT NULL AND r.longitude IS NOT NULL
      SQL

      conn.select_all(sql).to_a.each_with_object({}) do |r, memo|
        lat = to_coord(r['lat'])
        lng = to_coord(r['lng'])
        next if lat.nil? || lng.nil? || (lat.zero? && lng.zero?)

        memo[chave(r)] = [lat, lng]  # o INNER JOIN já ordena pelo checkout mais recente
      end
    end

    # Chave de match: nome | endereço | supervisão, normalizados.
    def chave(row)
      COLUNAS_CHAVE.map { |c| normaliza(row[c]) }.join('|')
    end

    def normaliza(valor)
      valor.to_s.strip.gsub(/\s+/, ' ').upcase
    end

    # Coordenada como Float (com PONTO). O rastreio pode trazer texto com vírgula
    # decimal ("-23,62"); coordenadas não usam separador de milhar, então trocar
    # a vírgula por ponto é seguro e garante a saída numérica (ponto) no .xlsx.
    def to_coord(valor)
      Float(valor.to_s.strip.tr(',', '.'))
    rescue ArgumentError, TypeError
      nil
    end

    # Nome da tabela do mês imediatamente anterior, da MESMA operação, se existir.
    def tabela_anterior
      return @tabela_anterior if defined?(@tabela_anterior)

      @tabela_anterior = calcular_tabela_anterior
    end

    def calcular_tabela_anterior
      tokens = @tabela.delete_prefix(Operacao::PREFIXO).split('_')
      idx = tokens.index { |t| GRAFIA_PARA_NUM.key?(t.downcase) }
      return nil if idx.nil?

      num = GRAFIA_PARA_NUM[tokens[idx].downcase]
      tem_ano = tokens[idx + 1].to_s.match?(/\A\d{4}\z/)
      ano = tem_ano ? tokens[idx + 1].to_i : nil

      prev_num = num == 1 ? 12 : num - 1
      prev_ano = ano && num == 1 ? ano - 1 : ano

      validas = Operacao.nomes_validos
      MESES_GRAFIAS[prev_num].each do |grafia|
        novo = tokens.dup
        novo[idx] = grafia
        novo[idx + 1] = prev_ano.to_s if tem_ano
        candidato = Operacao::PREFIXO + novo.join('_')
        return candidato if validas.include?(candidato)
      end
      nil
    end
  end
end