admin-netmonster-db/admin/statistics.php

<?php
/**
 * admin/statistics.php
 *
 * Statistiques par pays, operateur, technologie et bande. Page en
 * lecture seule : aucune ecriture, aucune action sensible. Uniquement
 * des agregations SQL via requetes preparees.
 *
 * CORRIGE (20/09/2026, suite a erreur 500 en usage reel) : cette page
 * utilisait des noms de colonnes inexistants dans le schema reel
 * (o.name, c.technology), ecrits avant que le schema definitif
 * (install_full_schema.sql) ne soit connu. Alignee maintenant sur les
 * vraies colonnes :
 *   - operators.brand (pas "name")
 *   - cells.tech       (pas "technology")
 * Ajout egalement du require_once _nav.php qui manquait, provoquant
 * une erreur fatale "Call to undefined function render_admin_nav()".
 */
declare(strict_types=1);

require_once __DIR__ . '/admin_auth.php';
require_once __DIR__ . '/../lib/db.php';
require_once __DIR__ . '/_nav.php';

require_admin_auth();

$db = pdo();

$countries = $db->query('SELECT id, name FROM countries ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);
$countryId = filter_input(INPUT_GET, 'country_id', FILTER_VALIDATE_INT) ?: null;

$whereCountry = '';
$params = [];
if ($countryId) {
    $whereCountry = 'WHERE s.country_id = ?';
    $params[] = $countryId;
}

$totalSites = 0;
$totalCells = 0;
try {
    $stmt = $db->prepare("SELECT COUNT(*) FROM sites s $whereCountry");
    $stmt->execute($params);
    $totalSites = (int)$stmt->fetchColumn();

    $stmt = $db->prepare(
        "SELECT COUNT(*) FROM cells c INNER JOIN sites s ON s.id = c.site_id $whereCountry"
    );
    $stmt->execute($params);
    $totalCells = (int)$stmt->fetchColumn();
} catch (Throwable $e) {
    error_log('[statistics] compteurs generaux: ' . $e->getMessage());
}

$byOperator = $db->prepare(
    "SELECT o.brand AS operator_name, COUNT(DISTINCT s.id) AS site_count, COUNT(c.id) AS cell_count
     FROM operators o
     LEFT JOIN sites s ON s.operator_id = o.id " . ($countryId ? 'AND s.country_id = ?' : '') . "
     LEFT JOIN cells c ON c.site_id = s.id
     GROUP BY o.id, o.brand
     ORDER BY cell_count DESC"
);
$byOperator->execute($countryId ? [$countryId] : []);
$byOperator = $byOperator->fetchAll(PDO::FETCH_ASSOC);

$byTechnology = $db->prepare(
    "SELECT c.tech, COUNT(*) AS total
     FROM cells c
     INNER JOIN sites s ON s.id = c.site_id
     $whereCountry
     GROUP BY c.tech
     ORDER BY total DESC"
);
$byTechnology->execute($params);
$byTechnology = $byTechnology->fetchAll(PDO::FETCH_ASSOC);

$byBand = $db->prepare(
    "SELECT c.band, COUNT(*) AS total
     FROM cells c
     INNER JOIN sites s ON s.id = c.site_id
     $whereCountry
     GROUP BY c.band
     ORDER BY total DESC
     LIMIT 30"
);
$byBand->execute($params);
$byBand = $byBand->fetchAll(PDO::FETCH_ASSOC);

$user = current_admin_username();
?>
<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8">
    <title>Statistiques — NetMonster Admin</title>
    <link rel="stylesheet" href="assets/theme.css">
</head>
<body>
<?php render_admin_nav('statistics.php', $user); ?>

<main class="page-wrap">
    <h1>Statistiques</h1>
    <p class="page-subtitle">Vue de consultation agregee, sans action d'ecriture possible.</p>

    <section class="card">
        <form method="get" class="filters">
            <label>Pays
                <select name="country_id" onchange="this.form.submit()">
                    <option value="">Tous les pays</option>
                    <?php foreach ($countries as $c): ?>
                        <option value="<?= (int)$c['id'] ?>" <?= $countryId === (int)$c['id'] ? 'selected' : '' ?>>
                            <?= htmlspecialchars((string)$c['name'], ENT_QUOTES, 'UTF-8') ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </label>
        </form>
    </section>

    <section class="card">
        <h2>Vue d'ensemble</h2>
        <div class="stats-bar">
            <div class="stat-chip"><strong><?= number_format($totalSites, 0, ',', ' ') ?></strong>Sites</div>
            <div class="stat-chip"><strong><?= number_format($totalCells, 0, ',', ' ') ?></strong>Cellules</div>
        </div>
    </section>

    <section class="card">
        <h2>Par operateur</h2>
        <table class="data-table">
            <tr><th>Operateur</th><th>Sites</th><th>Cellules</th></tr>
            <?php foreach ($byOperator as $row): ?>
                <tr>
                    <td><?= htmlspecialchars((string)$row['operator_name'], ENT_QUOTES, 'UTF-8') ?></td>
                    <td><?= (int)$row['site_count'] ?></td>
                    <td><?= (int)$row['cell_count'] ?></td>
                </tr>
            <?php endforeach; ?>
            <?php if (empty($byOperator)): ?>
                <tr><td colspan="3" class="no-cadastre">Aucune donnee.</td></tr>
            <?php endif; ?>
        </table>
    </section>

    <section class="card">
        <h2>Par technologie</h2>
        <table class="data-table">
            <tr><th>Technologie</th><th>Cellules</th></tr>
            <?php foreach ($byTechnology as $row): ?>
                <tr>
                    <td><?= htmlspecialchars((string)$row['tech'], ENT_QUOTES, 'UTF-8') ?></td>
                    <td><?= (int)$row['total'] ?></td>
                </tr>
            <?php endforeach; ?>
            <?php if (empty($byTechnology)): ?>
                <tr><td colspan="2" class="no-cadastre">Aucune donnee.</td></tr>
            <?php endif; ?>
        </table>
    </section>

    <section class="card">
        <h2>Par bande (top 30)</h2>
        <table class="data-table">
            <tr><th>Bande</th><th>Cellules</th></tr>
            <?php foreach ($byBand as $row): ?>
                <tr>
                    <td><?= htmlspecialchars((string)$row['band'], ENT_QUOTES, 'UTF-8') ?></td>
                    <td><?= (int)$row['total'] ?></td>
                </tr>
            <?php endforeach; ?>
            <?php if (empty($byBand)): ?>
                <tr><td colspan="2" class="no-cadastre">Aucune donnee.</td></tr>
            <?php endif; ?>
        </table>
    </section>
</main>
</body>
</html>