<?php
/**
 * admin/review.php
 *
 * Corrige suite a l'audit du 20/09/2026 :
 *  - Le flux WMS cadastre est encode en JSON avec JSON_HEX_* au lieu
 *    d'addslashes() (memes raisons que edit_site.php).
 *  - Le catch silencieux de validate_group loggue desormais l'erreur
 *    et affiche un message a l'administrateur au lieu de rediriger
 *    silencieusement comme si tout s'etait bien passe.
 *  - La popup de la carte (groupes en attente) est reconstruite en DOM
 *    (textContent) plutot qu'en template literal HTML.
 */
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();
$user = current_admin_username();

function deriveSiteKey(array $row): string
{
    $mcc = $row['mcc'];
    $mnc = $row['mnc'];

    if ($row['tech'] === '4G' && !empty($row['sub_id2'])) {
        return "enb:{$mcc}:{$mnc}:{$row['area_code']}:{$row['sub_id2']}";
    }

    $latR = $row['lat'] !== null ? round((float)$row['lat'], 4) : 'na';
    $lonR = $row['lon'] !== null ? round((float)$row['lon'], 4) : 'na';
    return "geo:{$mcc}:{$mnc}:{$row['area_code']}:{$latR}:{$lonR}";
}

$flashError = null;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    require_csrf_token();
    $action = $_POST['action'] ?? '';

    if ($action === 'validate_group') {
        $stagingIds = array_map('intval', $_POST['staging_ids'] ?? []);
        $lat = (float)($_POST['lat'] ?? 0); $lon = (float)($_POST['lon'] ?? 0);
        $locationDesc = $_POST['location_desc'] ?? ''; $operatorId = (int)($_POST['operator_id'] ?? 0);

        // Verifie que l'operateur declare existe reellement avant toute ecriture.
        $opCheck = $db->prepare('SELECT country_id FROM operators WHERE id = ?');
        $opCheck->execute([$operatorId]);
        $countryId = $opCheck->fetchColumn();

        if ($stagingIds && $operatorId && $countryId !== false) {
            $db->beginTransaction();
            try {
                $near = $db->prepare('SELECT id FROM sites WHERE operator_id = ? AND ABS(lat - ?) < 0.0003 AND ABS(lon - ?) < 0.0003 LIMIT 1');
                $near->execute([$operatorId, $lat, $lon]);
                $siteId = $near->fetchColumn();

                if (!$siteId) {
                    $addrStmt = $db->prepare('SELECT address_desc FROM staging_cells WHERE id = ? LIMIT 1');
                    $addrStmt->execute([$stagingIds[0]]);
                    $addressDesc = $addrStmt->fetchColumn() ?: null;

                    $insertSite = $db->prepare('INSERT INTO sites (country_id, operator_id, lat, lon, location_desc, address_desc, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)');
                    $insertSite->execute([$countryId, $operatorId, $lat, $lon, $locationDesc, $addressDesc, $user]);
                    $siteId = (int)$db->lastInsertId();
                }

                $fetchStaging = $db->prepare('SELECT * FROM staging_cells WHERE id = ? AND status = "pending"');
                $insertCell = $db->prepare('
                    INSERT INTO cells (site_id, tech, mcc, mnc, cell_id, area_code, sub_id, sub_id2, band, frequency, azimuth, bandwidth, arfcn, validated_by)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE
                        site_id = VALUES(site_id), band = VALUES(band), frequency = VALUES(frequency),
                        azimuth = VALUES(azimuth), bandwidth = VALUES(bandwidth),
                        validated_by = VALUES(validated_by), validated_at = NOW()
                ');
                $markValidated = $db->prepare('UPDATE staging_cells SET status = "validated", reviewed_at = NOW(), reviewed_by = ? WHERE id = ?');

                foreach ($stagingIds as $sid) {
                    $fetchStaging->execute([$sid]);
                    $cell = $fetchStaging->fetch(PDO::FETCH_ASSOC);
                    if (!$cell) continue;
                    $insertCell->execute([
                        $siteId, $cell['tech'], $cell['mcc'], $cell['mnc'], $cell['cell_id'], $cell['area_code'],
                        $cell['sub_id'], $cell['sub_id2'], $cell['band'], $cell['frequency'], $cell['azimuth'], $cell['bandwidth'],
                        $cell['arfcn'], $user,
                    ]);
                    $markValidated->execute([$user, $sid]);
                }
                $db->commit();
            } catch (Throwable $e) {
                $db->rollBack();
                // Corrige : l'echec n'est plus silencieux. On loggue le
                // detail technique cote serveur et on informe
                // l'administrateur qu'aucune validation n'a ete
                // appliquee, plutot que de rediriger comme si de rien
                // n'etait alors que la transaction a echoue.
                error_log('[review] validate_group: ' . $e->getMessage());
                $flashError = "La validation du site a echoue et a ete annulee (aucune donnee ecrite). Consultez les journaux du serveur si le probleme persiste.";
            }
        } else {
            $flashError = "Validation refusee : operateur invalide ou selection de cellules vide.";
        }
    }

    if ($action === 'reject') {
        $stagingId = (int)($_POST['staging_id'] ?? 0);
        try {
            $db->prepare('UPDATE staging_cells SET status = "rejected", reviewed_at = NOW(), reviewed_by = ?, reject_reason = ? WHERE id = ? AND status = "pending"')
               ->execute([$user, $_POST['reject_reason'] ?? null, $stagingId]);
        } catch (Throwable $e) {
            error_log('[review] reject: ' . $e->getMessage());
            $flashError = 'Le rejet de cette cellule a echoue.';
        }
    }

    if ($action === 'add_missing_cell') {
        // Verifie que l'operateur/pays declares existent reellement (coherence
        // MCC/MNC) avant d'inserer une nouvelle ligne staging manuelle.
        $operatorId = (int)($_POST['operator_id'] ?? 0);
        $opCheck = $db->prepare('SELECT mcc, mnc FROM operators WHERE id = ?');
        $opCheck->execute([$operatorId]);
        $op = $opCheck->fetch(PDO::FETCH_ASSOC);

        if ($op && in_array($_POST['tech'] ?? '', ['2G', '3G', '4G', '5G', 'CDMA'], true)) {
            try {
                $db->prepare('
                    INSERT INTO staging_cells (tech, mcc, mnc, operator_guess, cell_id, area_code, sub_id, sub_id2, band, frequency, azimuth, bandwidth, lat, lon, location_desc, arfcn, source, status)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "manual_form", "pending")
                ')->execute([
                    $_POST['tech'], $op['mcc'], $op['mnc'], $operatorId,
                    $_POST['cell_id'], $_POST['area_code'],
                    $_POST['sub_id'] !== '' ? $_POST['sub_id'] : null, $_POST['sub_id2'] !== '' ? $_POST['sub_id2'] : null,
                    $_POST['band'] !== '' ? $_POST['band'] : null, $_POST['frequency'] !== '' ? $_POST['frequency'] : null,
                    $_POST['azimuth'] !== '' ? (int)$_POST['azimuth'] : null, $_POST['bandwidth'] !== '' ? $_POST['bandwidth'] : null,
                    $_POST['lat'], $_POST['lon'], $_POST['location_desc'], $_POST['arfcn'] !== '' ? $_POST['arfcn'] : null,
                ]);
            } catch (Throwable $e) {
                error_log('[review] add_missing_cell: ' . $e->getMessage());
                $flashError = "L'ajout de la cellule manquante a echoue.";
            }
        } else {
            $flashError = "Ajout refuse : operateur ou technologie invalide.";
        }
    }

    if ($flashError !== null) {
        $_SESSION['review_flash_error'] = $flashError;
    }

    header('Location: review.php' . (isset($_GET['country']) ? '?country=' . urlencode($_GET['country']) : ''));
    exit;
}

$flashError = $_SESSION['review_flash_error'] ?? null;
unset($_SESSION['review_flash_error']);

$countryFilter = $_GET['country'] ?? ''; $operatorFilter = $_GET['operator'] ?? '';

$sql = '
    SELECT sc.*, o.brand AS operator_brand, c.name AS country_name
    FROM staging_cells sc
    LEFT JOIN operators o ON o.id = sc.operator_guess
    LEFT JOIN countries c ON c.mcc = sc.mcc
    WHERE sc.status = "pending"
';
$params = [];
if ($countryFilter !== '') { $sql .= ' AND c.iso2 = ?'; $params[] = $countryFilter; }
if ($operatorFilter !== '') { $sql .= ' AND sc.operator_guess = ?'; $params[] = $operatorFilter; }
$sql .= ' ORDER BY sc.submitted_at DESC LIMIT 500';

$stmt = $db->prepare($sql); $stmt->execute($params);
$allPending = $stmt->fetchAll(PDO::FETCH_ASSOC);

$groups = [];
foreach ($allPending as $row) {
    $key = deriveSiteKey($row);
    $groups[$key]['cells'][] = $row;
    $groups[$key]['operator_brand'] = $row['operator_brand'];
    $groups[$key]['operator_id'] = $row['operator_guess'];
    $groups[$key]['mcc'] = $row['mcc']; $groups[$key]['mnc'] = $row['mnc'];
    $groups[$key]['lat'] = $groups[$key]['lat'] ?? $row['lat'];
    $groups[$key]['lon'] = $groups[$key]['lon'] ?? $row['lon'];
    $groups[$key]['location_desc'] = $groups[$key]['location_desc'] ?? $row['location_desc'];
    $groups[$key]['country_name'] = $row['country_name'];
    $groups[$key]['has_dup'] = ($groups[$key]['has_dup'] ?? false) || (bool)$row['possible_dup_of'];
}

$operators = $db->query('SELECT id, mcc, mnc, brand FROM operators ORDER BY brand')->fetchAll(PDO::FETCH_ASSOC);
$countries = $db->query('SELECT iso2, name, mcc, default_lat, default_lon, default_zoom, cadastre_wms_url, cadastre_wms_layer, cadastre_attribution_html, cadastre_srs FROM countries ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);

$activeCountry = null;
if ($countryFilter !== '') {
    foreach ($countries as $c) { if ($c['iso2'] === $countryFilter) { $activeCountry = $c; break; } }
} elseif (!empty($groups)) {
    $firstMcc = reset($groups)['mcc'];
    foreach ($countries as $c) { if ($c['mcc'] === $firstMcc) { $activeCountry = $c; break; } }
}
if (!$activeCountry) {
    $activeCountry = ['default_lat' => 20, 'default_lon' => 0, 'default_zoom' => 2, 'cadastre_wms_url' => null, 'cadastre_wms_layer' => null, 'cadastre_attribution_html' => null, 'cadastre_srs' => 'EPSG:3857'];
}

$mapPoints = [];
foreach ($groups as $key => $g) {
    if ($g['lat'] && $g['lon']) {
        $mapPoints[] = ['key' => $key, 'lat' => (float)$g['lat'], 'lon' => (float)$g['lon'], 'operator' => $g['operator_brand'], 'count' => count($g['cells']), 'has_dup' => $g['has_dup']];
    }
}
$hasCadastre = !empty($activeCountry['cadastre_wms_url']) && !empty($activeCountry['cadastre_wms_layer']);
$totalPending = count($allPending);
$totalDup = count(array_filter($allPending, fn($r) => (bool)$r['possible_dup_of']));
?>
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8"><title>NetMonster Admin - Validation</title>
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css" crossorigin="" />
<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js" crossorigin=""></script>
<link rel="stylesheet" href="assets/theme.css">
</head>
<body>
<?php render_admin_nav('review.php', $user); ?>

<div class="page-wrap">
    <h1>Validation groupee par site</h1>
    <p class="page-subtitle">Verifiez chaque site avant qu'il ne rejoigne la base finale.</p>

    <?php if ($flashError): ?><p class="msg-err"><?= htmlspecialchars($flashError, ENT_QUOTES, 'UTF-8') ?></p><?php endif; ?>

    <div class="stats-bar">
        <div class="stat-chip"><strong><?= count($groups) ?></strong>site(s) en attente</div>
        <div class="stat-chip"><strong><?= $totalPending ?></strong>ligne(s) au total</div>
        <div class="stat-chip"><strong><?= $totalDup ?></strong>doublon(s) potentiel(s)</div>
    </div>

    <div class="map-controls <?= $hasCadastre ? '' : 'disabled' ?>">
        <label><input type="checkbox" id="toggleCadastre" <?= $hasCadastre ? 'checked' : 'disabled' ?>>
        Afficher le cadastre<?= $hasCadastre ? '' : ' (non configure pour ce pays)' ?></label>
    </div>
    <div id="map"></div>

    <form class="filters" method="get">
        <select name="country" onchange="this.form.submit()">
            <option value="">Tous les pays</option>
            <?php foreach ($countries as $c): ?>
                <option value="<?= htmlspecialchars($c['iso2'], ENT_QUOTES, 'UTF-8') ?>" <?= $countryFilter === $c['iso2'] ? 'selected' : '' ?>><?= htmlspecialchars($c['name'], ENT_QUOTES, 'UTF-8') ?></option>
            <?php endforeach; ?>
        </select>
        <select name="operator" onchange="this.form.submit()">
            <option value="">Tous les operateurs</option>
            <?php foreach ($operators as $o): ?>
                <option value="<?= $o['id'] ?>" <?= (string)$operatorFilter === (string)$o['id'] ? 'selected' : '' ?>><?= htmlspecialchars($o['brand'], ENT_QUOTES, 'UTF-8') ?> (<?= htmlspecialchars($o['mcc'].'-'.$o['mnc'], ENT_QUOTES, 'UTF-8') ?>)</option>
            <?php endforeach; ?>
        </select>
    </form>

    <?php foreach ($groups as $key => $group): ?>
    <div class="group-card" id="group-<?= htmlspecialchars($key, ENT_QUOTES, 'UTF-8') ?>">
        <div class="group-header">
            <h3><?= htmlspecialchars($group['operator_brand'] ?? 'Operateur inconnu', ENT_QUOTES, 'UTF-8') ?> &middot; <?= htmlspecialchars($group['country_name'] ?? '?', ENT_QUOTES, 'UTF-8') ?> &middot; <?= htmlspecialchars((string)$group['lat'], ENT_QUOTES, 'UTF-8') ?>, <?= htmlspecialchars((string)$group['lon'], ENT_QUOTES, 'UTF-8') ?></h3>
            <div>
                <span class="badge badge-info"><?= count($group['cells']) ?> cellule<?= count($group['cells']) > 1 ? 's' : '' ?></span>
                <?php if ($group['has_dup']): ?><span class="badge badge-dup">Doublons potentiels</span><?php endif; ?>
            </div>
        </div>
        <table class="cells-table">
            <tr><th>Techno</th><th>Cell ID</th><th>Area code</th><th>Sub ID</th><th>eNB/RNC</th><th>Bande</th><th>Frequence</th><th>Azimut</th><th>Largeur</th><th>ARFCN</th><th>Dup.</th><th></th></tr>
            <?php foreach ($group['cells'] as $c): ?>
            <tr>
                <td><?= htmlspecialchars((string)$c['tech'], ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)$c['cell_id'], ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)$c['area_code'], ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)($c['sub_id'] ?? ''), ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)($c['sub_id2'] ?? ''), ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)($c['band'] ?? ''), ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)($c['frequency'] ?? ''), ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= $c['azimuth'] !== null ? htmlspecialchars((string)$c['azimuth'], ENT_QUOTES, 'UTF-8') . '°' : '' ?></td>
                <td><?= htmlspecialchars((string)($c['bandwidth'] ?? ''), ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= htmlspecialchars((string)($c['arfcn'] ?? ''), ENT_QUOTES, 'UTF-8') ?></td>
                <td><?= $c['possible_dup_of'] ? '#' . htmlspecialchars((string)$c['possible_dup_of'], ENT_QUOTES, 'UTF-8') : '&mdash;' ?></td>
                <td>
                    <form method="post" style="display:inline">
                        <?= csrf_field() ?>
                        <input type="hidden" name="action" value="reject">
                        <input type="hidden" name="staging_id" value="<?= htmlspecialchars((string)$c['id'], ENT_QUOTES, 'UTF-8') ?>">
                        <button type="submit" class="reject-small" onclick="return confirm('Rejeter ?')">Rejeter</button>
                    </form>
                </td>
            </tr>
            <?php endforeach; ?>
        </table>
        <div class="actions-row">
            <form method="post">
                <?= csrf_field() ?>
                <?php foreach ($group['cells'] as $c): ?><input type="hidden" name="staging_ids[]" value="<?= htmlspecialchars((string)$c['id'], ENT_QUOTES, 'UTF-8') ?>"><?php endforeach; ?>
                <input type="hidden" name="action" value="validate_group">
                <input type="hidden" name="lat" value="<?= htmlspecialchars((string)$group['lat'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="lon" value="<?= htmlspecialchars((string)$group['lon'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="location_desc" value="<?= htmlspecialchars((string)$group['location_desc'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="operator_id" value="<?= htmlspecialchars((string)$group['operator_id'], ENT_QUOTES, 'UTF-8') ?>">
                <button type="submit" class="validate">Valider le site complet</button>
            </form>
            <button type="button" class="add-missing" onclick="toggleMissingForm('<?= htmlspecialchars($key, ENT_QUOTES, 'UTF-8') ?>')">+ Ajouter un secteur / une bande manquante</button>
        </div>
        <div class="missing-form" id="missing-<?= htmlspecialchars($key, ENT_QUOTES, 'UTF-8') ?>">
            <form method="post">
                <?= csrf_field() ?>
                <input type="hidden" name="action" value="add_missing_cell">
                <input type="hidden" name="mcc" value="<?= htmlspecialchars($group['mcc'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="mnc" value="<?= htmlspecialchars($group['mnc'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="operator_id" value="<?= htmlspecialchars((string)$group['operator_id'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="lat" value="<?= htmlspecialchars((string)$group['lat'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="lon" value="<?= htmlspecialchars((string)$group['lon'], ENT_QUOTES, 'UTF-8') ?>">
                <input type="hidden" name="location_desc" value="<?= htmlspecialchars((string)$group['location_desc'], ENT_QUOTES, 'UTF-8') ?>">
                <div class="missing-grid">
                    <label>Techno<select name="tech" required><option value="2G">2G</option><option value="3G">3G</option><option value="4G" selected>4G</option><option value="5G">5G</option><option value="CDMA">CDMA</option></select></label>
                    <label>Cell ID<input type="text" name="cell_id" required></label>
                    <label>Area code<input type="text" name="area_code" required></label>
                    <label>Sub ID (BSIC/PSC/PCI)<input type="text" name="sub_id"></label>
                    <label>eNB/RNC<input type="text" name="sub_id2"></label>
                    <label>Bande<input type="text" name="band" placeholder="ex: B7"></label>
                    <label>Frequence<input type="text" name="frequency" placeholder="ex: 700MHz"></label>
                    <label>Azimut<input type="number" name="azimuth" min="0" max="359" placeholder="ex: 280"></label>
                    <label>Largeur de bande<input type="text" name="bandwidth" placeholder="ex: 10MHz"></label>
                    <label>ARFCN<input type="text" name="arfcn"></label>
                </div>
                <button type="submit" class="validate" style="margin-top:0.8rem;">Ajouter cette cellule au site</button>
            </form>
        </div>
    </div>
    <?php endforeach; ?>
</div>

<script>
function toggleMissingForm(key){const el=document.getElementById('missing-'+key);el.style.display=el.style.display==='block'?'none':'block';}
const map = L.map('map').setView([<?= (float)$activeCountry['default_lat'] ?>, <?= (float)$activeCountry['default_lon'] ?>], <?= (int)$activeCountry['default_zoom'] ?>);
L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', {maxZoom:19, attribution:'&copy; <a href="https://www.openstreetmap.org/copyright" target="_blank">OpenStreetMap</a> contributors'}).addTo(map);
<?php if ($hasCadastre): ?>
const cadastreLayer = L.tileLayer.wms(
    <?= json_encode($activeCountry['cadastre_wms_url'], JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT) ?>,
    {
        layers: <?= json_encode($activeCountry['cadastre_wms_layer'], JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT) ?>,
        format: 'image/png',
        transparent: true,
        opacity: 0.6,
        attribution: <?= json_encode($activeCountry['cadastre_attribution_html'] ?? '', JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT) ?>
    }
).addTo(map);
document.getElementById('toggleCadastre').addEventListener('change', function(){ if(this.checked){map.addLayer(cadastreLayer);}else{map.removeLayer(cadastreLayer);} });
<?php endif; ?>
const groupPoints = <?= json_encode($mapPoints, JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS | JSON_HEX_QUOT) ?>;

function buildGroupPopup(point) {
    const container = document.createElement('div');
    const title = document.createElement('strong');
    title.textContent = point.operator ?? '?';
    container.appendChild(title);
    container.appendChild(document.createElement('br'));
    const count = document.createElement('span');
    count.textContent = `${point.count} cellule(s)`;
    container.appendChild(count);
    if (point.has_dup) {
        container.appendChild(document.createElement('br'));
        const dup = document.createElement('span');
        dup.textContent = 'doublon potentiel';
        container.appendChild(dup);
    }
    return container;
}

groupPoints.forEach(g => {
  const color = g.has_dup ? '#e0392f' : '#2f5fe0';
  L.circleMarker([g.lat, g.lon], {radius:9, color, fillColor:color, fillOpacity:0.7, weight:2}).addTo(map)
    .bindPopup(buildGroupPopup(g))
    .on('click', () => document.getElementById('group-' + g.key)?.scrollIntoView({behavior:'smooth'}));
});
if (groupPoints.length > 0) map.fitBounds(L.latLngBounds(groupPoints.map(g => [g.lat, g.lon])), {padding:[30,30]});
</script>
</body>
</html>