feat: gerar planilha de carga do SimpliRoute (download .xlsx)

Nova tela ADM/gerente (Administração > Planilha SimpliRoute) que gera a
planilha de importação (.xlsx) de uma operação vigente, no formato do
pt_br_example.xlsx. Preenche Latitude/Longitude reaproveitando a
geocodificação do mês anterior da mesma operação (casa nome + endereço +
supervisão; geo vem do rastreio db_reem_simplerout_2026). Pacientes novos
saem sem lat/long.

- SimpliRoute::PlanilhaCarga: monta linhas + resolve match do mês anterior
- SimpliRoute::PlanilhaCargaXlsx: builder caxlsx com cabeçalho verbatim
- Admin::PlanilhasSimpliRouteController (show/baixar) + policy admin/gerente
- Gem caxlsx (rebuild do container atualiza o Gemfile.lock)

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

app/controllers/admin/planilhas_simpli_route_controller.rb

@@ -0,0 +1,43 @@
+# app/controllers/admin/planilhas_simpliroute_controller.rb
+#
+# Gera a planilha de carga do SimpliRoute (.xlsx) a partir de UMA operação
+# vigente. Lat/long são preenchidas com o histórico do mês anterior
+# (SimpliRoute::PlanilhaCarga). Somente leitura das bases.
+class Admin::PlanilhasSimpliRouteController < ApplicationController
+  XLSX_MIME = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'.freeze
+
+  def show
+    authorize :planilha_simpli_route
+    @operacoes_agrupadas = Operacao.agrupadas_por_mes
+  end
+
+  # GET /admin/planilha_simpli_route/baixar?tabela=gade_entregas_...
+  def baixar
+    authorize :planilha_simpli_route
+
+    tabela = params[:tabela].to_s
+    unless Operacao.valida?(tabela)
+      return redirect_to(admin_planilha_simpli_route_path, alert: 'Selecione uma operação válida.')
+    end
+
+    carga  = SimpliRoute::PlanilhaCarga.new(tabela)
+    linhas = carga.linhas
+
+    if linhas.empty?
+      return redirect_to(admin_planilha_simpli_route_path,
+                         alert: "A operação #{carga.label} não tem registros.")
+    end
+
+    binario = SimpliRoute::PlanilhaCargaXlsx.new(linhas).gerar
+    send_data binario,
+              filename: "carga_simpliroute_#{nome_arquivo(carga.label)}.xlsx",
+              type: XLSX_MIME,
+              disposition: 'attachment'
+  end
+
+  private
+
+  def nome_arquivo(label)
+    label.to_s.parameterize(separator: '_').presence || 'operacao'
+  end
+end

app/policies/planilha_simpli_route_policy.rb

@@ -0,0 +1,9 @@
+# app/policies/planilha_simpliroute_policy.rb
+#
+# Geração da planilha de carga do SimpliRoute — tarefa de preparação de operação.
+# Liberada para admin e gerente (quem prepara as cargas). Policy "headless"
+# (autorizada com o símbolo :planilha_simpliroute).
+class PlanilhaSimpliRoutePolicy < ApplicationPolicy
+  def show?   = admin_ou_gerente?
+  def baixar? = admin_ou_gerente?
+end

app/services/simpli_route/planilha_carga.rb

@@ -0,0 +1,170 @@
+# 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
+
+    def to_coord(valor)
+      Float(valor)
+    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

app/services/simpli_route/planilha_carga_xlsx.rb

@@ -0,0 +1,93 @@
+# app/services/simpli_route/planilha_carga_xlsx.rb
+#
+# Gera o binário .xlsx da carga do SimpliRoute a partir das linhas montadas pelo
+# PlanilhaCarga, replicando EXATAMENTE o cabeçalho do arquivo de referência
+# (Imagens para implantação/pt_br_example.xlsx) — 26 colunas A..Z.
+require 'caxlsx'
+
+module SimpliRoute
+  class PlanilhaCargaXlsx
+    # Cabeçalho verbatim do exemplo (inclui os espaços duplos em A e B).
+    CABECALHO = [
+      "Titulo*    Solicitado",
+      "endereço completo*    Solicitado",
+      "Carga",
+      "Janela de horário inicial",
+      "Janela de horário final",
+      "Tempo de serviço",
+      "Anotações",
+      "Latitude",
+      "Longitude",
+      "Identificação de referência",
+      "Habilidade necessária",
+      "Habilidade opcional",
+      "Pessoa de contato",
+      "Telefone de contato",
+      "Janela de horário inicial 2",
+      "Janela de horário final 2",
+      "Capacidade 2",
+      "Capacidade 3",
+      "Prioridade",
+      "SMS",
+      "Correio eletrônico de contato",
+      "Carga pick",
+      "Carga pick 2",
+      "Carga pick 3",
+      "Data agendada",
+      "Tipo de visita"
+    ].freeze
+
+    # Colunas O..Z (14ª em diante) ficam vazias — 12 células.
+    VAZIAS_FINAIS = Array.new(CABECALHO.size - 14, '').freeze
+
+    def initialize(linhas)
+      @linhas = linhas
+    end
+
+    # String binária do .xlsx.
+    def gerar
+      pkg = Axlsx::Package.new
+      pkg.workbook.add_worksheet(name: 'Carga') do |sheet|
+        sheet.add_row(CABECALHO)
+        @linhas.each do |l|
+          valores, tipos = linha(l)
+          sheet.add_row(valores, types: tipos)
+        end
+      end
+      pkg.to_stream.read
+    end
+
+    private
+
+    # Monta [valores, tipos] de uma linha (A..Z). Lat/long numéricos quando há
+    # match; D/E como texto ("08:00"/"18:00"); NF/ref como texto (igual ao exemplo).
+    def linha(l)
+      lat = l[:latitude]
+      lng = l[:longitude]
+      valores = [
+        l[:titulo].to_s,                 # A
+        l[:endereco].to_s,               # B
+        l[:carga],                       # C
+        l[:janela_ini],                  # D
+        l[:janela_fim],                  # E
+        l[:tempo],                       # F
+        l[:anotacoes].to_s,              # G
+        (lat.nil? ? '' : lat),           # H
+        (lng.nil? ? '' : lng),           # I
+        l[:referencia].to_s,             # J
+        '', '',                          # K, L
+        l[:contato].to_s,                # M
+        l[:telefone].to_s,               # N
+        *VAZIAS_FINAIS                   # O..Z
+      ]
+      tipos = [
+        :string, :string, :integer, :string, :string, :integer, :string,
+        (lat.nil? ? :string : :float),   # H
+        (lng.nil? ? :string : :float),   # I
+        :string, :string, :string, :string, :string,
+        *Array.new(VAZIAS_FINAIS.size, :string)
+      ]
+      [valores, tipos]
+    end
+  end
+end

app/views/admin/planilhas_simpli_route/show.html.erb

@@ -0,0 +1,51 @@
+<%# app/views/admin/planilhas_simpliroute/show.html.erb %>
+<div class="space-y-6">
+
+  <%# Header %>
+  <div>
+    <h1 class="text-2xl font-bold text-white">Planilha SimpliRoute</h1>
+    <p class="text-gray-400 text-sm mt-0.5">Gera a planilha de carga (.xlsx) de uma operação vigente, já com lat/long do mês anterior</p>
+  </div>
+
+  <%# Flash %>
+  <%= render 'shared/flash' %>
+
+  <%# Explicação %>
+  <div class="p-4 bg-blue-900/20 border border-blue-500/40 rounded-xl text-blue-200/90 text-sm flex items-start gap-2">
+    <span class="text-base leading-none">ℹ️</span>
+    <p>A <strong>Latitude/Longitude</strong> é reaproveitada do <strong>mês anterior</strong> da mesma
+       operação (casando nome + endereço + supervisão). Pacientes <strong>novos</strong> ou sem
+       correspondência saem <strong>sem</strong> lat/long — o SimpliRoute geocodifica na importação.
+       Se a operação não tiver mês anterior, a planilha sai inteira sem lat/long.</p>
+  </div>
+
+  <%# Seleção + download %>
+  <div class="bg-[#111] border border-white/5 rounded-2xl p-5">
+    <% if @operacoes_agrupadas.blank? %>
+      <p class="text-gray-400 text-sm">Nenhuma operação encontrada.</p>
+    <% else %>
+      <%= form_with url: baixar_admin_planilha_simpli_route_path, method: :get, data: { turbo: false },
+                    class: 'flex flex-col sm:flex-row sm:items-end gap-3' do %>
+        <div class="flex-1">
+          <label class="block text-sm font-medium text-gray-300 mb-1.5">Operação vigente</label>
+          <select name="tabela" required
+                  class="w-full bg-[#1a1a1a] border border-white/10 rounded-xl px-3 py-3 text-white
+                         focus:outline-none focus:border-orange-500 min-h-[48px]">
+            <% @operacoes_agrupadas.each do |titulo, ops| %>
+              <optgroup label="<%= titulo %>">
+                <% ops.each do |op| %>
+                  <option value="<%= op[:tabela] %>"><%= op[:label] %></option>
+                <% end %>
+              </optgroup>
+            <% end %>
+          </select>
+        </div>
+        <button type="submit"
+                class="px-6 py-3 bg-[#f97316] hover:bg-orange-500 text-white font-semibold rounded-xl
+                       transition-colors min-h-[48px] whitespace-nowrap">
+          📥 Baixar planilha
+        </button>
+      <% end %>
+    <% end %>
+  </div>
+</div>

app/views/layouts/_navbar.html.erb

@@ -58,6 +58,7 @@
         <%= nav_link_to '👥 Usuários', admin_usuarios_path %>
         <%= nav_link_to '⚙️ Configurações', admin_configuracoes_path %>
         <%= nav_link_to '🔍 Auditoria', admin_auditoria_logs_path %>
+        <%= nav_link_to '📥 Planilha SimpliRoute', admin_planilha_simpli_route_path %>
         <% if current_user.admin? %>
           <%= nav_link_to '✏️ Editar Lançamento', admin_edicao_lancamento_path %>
         <% end %>