<?php
require_roles('super_admin');
$rows=db()->query("SELECT COALESCE(NULLIF(TRIM(city),''),'Não informado') city,COALESCE(NULLIF(TRIM(state),''),'--') state,COUNT(*) total,SUM(CASE WHEN role='client' THEN 1 ELSE 0 END) clients,SUM(CASE WHEN role='company' THEN 1 ELSE 0 END) companies,SUM(CASE WHEN role='service_provider' THEN 1 ELSE 0 END) providers,SUM(CASE WHEN role IN ('courier','motofreight','mototaxi','taxi','private_driver','moving_provider','shuttle_driver') THEN 1 ELSE 0 END) mobility,SUM(CASE WHEN home_lat IS NOT NULL AND home_lng IS NOT NULL THEN 1 ELSE 0 END) geolocated FROM users WHERE role NOT IN ('super_admin','sub_admin') GROUP BY COALESCE(NULLIF(TRIM(city),''),'Não informado'),COALESCE(NULLIF(TRIM(state),''),'--') ORDER BY total DESC,city")->fetchAll();
$total=(int)db()->query("SELECT COUNT(*) FROM users WHERE role NOT IN ('super_admin','sub_admin')")->fetchColumn();
$clients=(int)db()->query("SELECT COUNT(*) FROM users WHERE role='client'")->fetchColumn();
$cities=count(array_filter($rows,static fn($r)=>$r['city']!=='Não informado'));
$geo=(int)db()->query("SELECT COUNT(*) FROM users WHERE role NOT IN ('super_admin','sub_admin') AND home_lat IS NOT NULL AND home_lng IS NOT NULL")->fetchColumn();
?>
<section class="page-intro"><div><span class="mini-label">PERÍMETRO E COBERTURA</span><h2>Usuários por Cidade</h2><p>Veja em quais cidades os usuários estão se cadastrando com base na cidade de residência informada no perfil.</p></div></section>
<div class="stats-grid"><article class="stat-card"><div class="stat-top"><span>Usuários</span><span class="stat-icon">👥</span></div><strong><?= $total ?></strong><small>Sem contar administradores</small></article><article class="stat-card"><div class="stat-top"><span>Clientes</span><span class="stat-icon">👤</span></div><strong><?= $clients ?></strong><small>Perfis Cliente</small></article><article class="stat-card"><div class="stat-top"><span>Cidades</span><span class="stat-icon">🏙️</span></div><strong><?= $cities ?></strong><small>Com cadastros informados</small></article><article class="stat-card"><div class="stat-top"><span>Com coordenadas</span><span class="stat-icon">📍</span></div><strong><?= $geo ?></strong><small>Residência geolocalizada</small></article></div>
<section class="panel"><div class="panel-header"><div><h3>Distribuição por cidade</h3><p>Útil para acompanhar expansão e definir áreas de atendimento.</p></div></div><div class="table-wrap"><table><thead><tr><th>Cidade/UF</th><th>Total</th><th>Clientes</th><th>Empresas</th><th>Prestadores</th><th>Mobilidade/Entrega</th><th>Com localização</th></tr></thead><tbody><?php foreach($rows as $r):?><tr><td><strong><?= e($r['city']) ?></strong><small style="display:block"><?= e($r['state']) ?></small></td><td><?= (int)$r['total'] ?></td><td><?= (int)$r['clients'] ?></td><td><?= (int)$r['companies'] ?></td><td><?= (int)$r['providers'] ?></td><td><?= (int)$r['mobility'] ?></td><td><?= (int)$r['geolocated'] ?></td></tr><?php endforeach;?></tbody></table></div></section>
