<?php
/**
 * admin/data_quality.php
 *
 * Tableau de controle qualite en lecture seule.
 * Aucun INSERT, UPDATE ou DELETE n'est execute par cette page.
 */
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 h(mixed $value): string
{
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function addIssue(array &$issues, string $type, string $severity, array $data): void
{
    $data['type'] = $type;
    $data['severity'] = $severity;
    $issues[] = $data;
}

function normalizeCode(mixed $value): string
{
    return trim((string)$value);
}

function technologyNeedsNode(string $tech): bool
{
    return in_array(strtoupper(trim($tech)), ['3G', '4G', '5G'], true);
}

$countryFilter = trim((string)($_GET['country'] ?? ''));
$operatorFilter = trim((string)($_GET['operator'] ?? ''));
$typeFilter = trim((string)($_GET['type'] ?? ''));

$countries = $db->query('SELECT id, iso2, name FROM countries ORDER BY name')->fetchAll(PDO::FETCH_ASSOC);
$operators = $db->query('SELECT id, mcc, mnc, brand FROM operators ORDER BY brand')->fetchAll(PDO::FETCH_ASSOC);

$siteWhere = ' WHERE 1=1 ';
$siteParams = [];
if ($countryFilter !== '') {
    $siteWhere .= ' AND c.iso2 = ?';
    $siteParams[] = $countryFilter;
}
if ($operatorFilter !== '') {
    $siteWhere .= ' AND o.id = ?';
    $siteParams[] = (int)$operatorFilter;
}

$siteSql = '
    SELECT s.id AS site_id, s.lat, s.lon, s.location_desc, s.address_desc,
           o.brand AS operator_brand, o.mcc AS operator_mcc, o.mnc AS operator_mnc,
           c.name AS country_name, COUNT(cl.id) AS cell_count
    FROM sites s
    JOIN operators o ON o.id = s.operator_id
    JOIN countries c ON c.id = s.country_id
    LEFT JOIN cells cl ON cl.site_id = s.id
' . $siteWhere . '
    GROUP BY s.id, s.lat, s.lon, s.location_desc, s.address_desc,
             o.brand, o.mcc, o.mnc, c.name
    ORDER BY s.id';

$siteStmt = $db->prepare($siteSql);
$siteStmt->execute($siteParams);
$siteRows = $siteStmt->fetchAll(PDO::FETCH_ASSOC);

$cellWhere = ' WHERE 1=1 ';
$cellParams = [];
if ($countryFilter !== '') {
    $cellWhere .= ' AND c.iso2 = ?';
    $cellParams[] = $countryFilter;
}
if ($operatorFilter !== '') {
    $cellWhere .= ' AND o.id = ?';
    $cellParams[] = (int)$operatorFilter;
}

$cellSql = '
    SELECT cl.id AS cell_pk, cl.site_id, cl.tech,
           cl.mcc AS cell_mcc, cl.mnc AS cell_mnc,
           cl.cell_id, cl.area_code, cl.sub_id, cl.sub_id2,
           cl.band, cl.frequency, cl.azimuth, cl.bandwidth, cl.arfcn,
           o.brand AS operator_brand, o.mcc AS operator_mcc, o.mnc AS operator_mnc,
           c.name AS country_name
    FROM cells cl
    JOIN sites s ON s.id = cl.site_id
    JOIN operators o ON o.id = s.operator_id
    JOIN countries c ON c.id = s.country_id
' . $cellWhere . '
    ORDER BY cl.site_id, cl.tech, cl.sub_id2, cl.cell_id';

$cellStmt = $db->prepare($cellSql);
$cellStmt->execute($cellParams);
$cellRows = $cellStmt->fetchAll(PDO::FETCH_ASSOC);

$issues = [];

foreach ($siteRows as $site) {
    $siteId = (int)$site['site_id'];
    $lat = (float)$site['lat'];
    $lon = (float)$site['lon'];
    $cellCount = (int)$site['cell_count'];
    $location = $site['address_desc'] ?: $site['location_desc'];

    if ($cellCount === 0) {
        addIssue($issues, 'site_without_cells', 'warning', [
            'site_id' => $siteId, 'cell_pk' => null,
            'operator_brand' => $site['operator_brand'],
            'country_name' => $site['country_name'],
            'tech' => null, 'cell_id' => null, 'sub_id2' => null,
            'band' => null, 'frequency' => null, 'azimuth' => null,
            'message' => 'Le site ne contient aucune cellule.',
            'location' => $location,
        ]);
    }

    if ($lat === 0.0 || $lon === 0.0) {
        addIssue($issues, 'invalid_coordinates', 'error', [
            'site_id' => $siteId, 'cell_pk' => null,
            'operator_brand' => $site['operator_brand'],
            'country_name' => $site['country_name'],
            'tech' => null, 'cell_id' => null, 'sub_id2' => null,
            'band' => null, 'frequency' => null, 'azimuth' => null,
            'message' => 'Latitude ou longitude egale a 0.',
            'location' => $location,
        ]);
    }

    if ($lat < -90 || $lat > 90 || $lon < -180 || $lon > 180) {
        addIssue($issues, 'invalid_coordinates', 'error', [
            'site_id' => $siteId, 'cell_pk' => null,
            'operator_brand' => $site['operator_brand'],
            'country_name' => $site['country_name'],
            'tech' => null, 'cell_id' => null, 'sub_id2' => null,
            'band' => null, 'frequency' => null, 'azimuth' => null,
            'message' => 'Coordonnees hors des plages GPS valides.',
            'location' => $location,
        ]);
    }
}

foreach ($cellRows as $cell) {
    $base = [
        'site_id' => (int)$cell['site_id'],
        'cell_pk' => (int)$cell['cell_pk'],
        'operator_brand' => $cell['operator_brand'],
        'country_name' => $cell['country_name'],
        'tech' => $cell['tech'],
        'cell_id' => $cell['cell_id'],
        'sub_id2' => $cell['sub_id2'],
        'band' => $cell['band'],
        'frequency' => $cell['frequency'],
        'azimuth' => $cell['azimuth'],
        'location' => null,
    ];

    if ($cell['azimuth'] !== null && $cell['azimuth'] !== '') {
        $azimuth = (int)$cell['azimuth'];
        if ($azimuth < 0 || $azimuth > 359) {
            addIssue($issues, 'invalid_azimuth', 'error', $base + [
                'message' => 'Azimut hors de la plage 0-359 degres.',
            ]);
        }
    }

    if (
        normalizeCode($cell['cell_mcc']) !== normalizeCode($cell['operator_mcc'])
        || normalizeCode($cell['cell_mnc']) !== normalizeCode($cell['operator_mnc'])
    ) {
        addIssue($issues, 'mcc_mnc_mismatch', 'error', $base + [
            'message' => 'Le MCC/MNC de la cellule ne correspond pas a son operateur.',
        ]);
    }

    if (
        technologyNeedsNode((string)$cell['tech'])
        && ($cell['sub_id2'] === null || trim((string)$cell['sub_id2']) === '')
    ) {
        addIssue($issues, 'missing_enb', 'warning', $base + [
            'message' => 'eNB/RNC absent pour cette technologie.',
        ]);
    }

    if ($cell['band'] === null || trim((string)$cell['band']) === '') {
        addIssue($issues, 'missing_band', 'warning', $base + [
            'message' => 'Bande radio absente.',
        ]);
    }

    if ($cell['frequency'] === null || trim((string)$cell['frequency']) === '') {
        addIssue($issues, 'missing_frequency', 'warning', $base + [
            'message' => 'Frequence absente.',
        ]);
    }

    if ($cell['bandwidth'] === null || trim((string)$cell['bandwidth']) === '') {
        addIssue($issues, 'missing_bandwidth', 'info', $base + [
            'message' => 'Largeur de bande absente.',
        ]);
    }
}

$issueTypes = [
    'site_without_cells' => 'Site sans cellule',
    'invalid_coordinates' => 'Coordonnees invalides',
    'invalid_azimuth' => 'Azimut invalide',
    'mcc_mnc_mismatch' => 'MCC/MNC incoherent',
    'missing_enb' => 'eNB/RNC absent',
    'missing_band' => 'Bande absente',
    'missing_frequency' => 'Frequence absente',
    'missing_bandwidth' => 'Largeur de bande absente',
];

if ($typeFilter !== '') {
    $issues = array_values(array_filter($issues, static fn(array $issue): bool => $issue['type'] === $typeFilter));
}

$errors = count(array_filter($issues, static fn(array $issue): bool => $issue['severity'] === 'error'));
$warnings = count(array_filter($issues, static fn(array $issue): bool => $issue['severity'] === 'warning'));
$infos = count(array_filter($issues, static fn(array $issue): bool => $issue['severity'] === 'info'));

$countSiteSql = 'SELECT COUNT(*) FROM sites s JOIN operators o ON o.id = s.operator_id JOIN countries c ON c.id = s.country_id' . $siteWhere;
$countCellSql = 'SELECT COUNT(*) FROM cells cl JOIN sites s ON s.id = cl.site_id JOIN operators o ON o.id = s.operator_id JOIN countries c ON c.id = s.country_id' . $cellWhere;
$countSiteStmt = $db->prepare($countSiteSql);
$countSiteStmt->execute($siteParams);
$siteCount = (int)$countSiteStmt->fetchColumn();
$countCellStmt = $db->prepare($countCellSql);
$countCellStmt->execute($cellParams);
$cellCount = (int)$countCellStmt->fetchColumn();
?>
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>NetMonster Admin - Controle qualite</title>
<link rel="stylesheet" href="assets/theme.css">
<style>
.quality-summary{display:grid;grid-template-columns:repeat(4,minmax(130px,1fr));gap:.8rem;margin-bottom:1rem}.quality-card{border-radius:8px;padding:1rem;background:#fff;border:1px solid #dce2ea}.quality-card.error{border-left:4px solid #dc2626}.quality-card.warning{border-left:4px solid #d97706}.quality-card.info{border-left:4px solid #2563eb}.quality-number{font-size:1.65rem;font-weight:700;margin-top:.25rem}.quality-label{color:#64748b;font-size:.8rem}.filters{display:grid;grid-template-columns:1fr 1fr 1fr auto;gap:.6rem;align-items:end}.filters label{display:block;font-size:.78rem;color:#64748b;margin-bottom:.25rem}.filters input,.filters select{width:100%;box-sizing:border-box}.issue-table{margin-top:1rem;overflow:auto}.issue-table table{min-width:1050px}.issue-type{font-size:.75rem;padding:.2rem .45rem;border-radius:999px;white-space:nowrap}.issue-type.error{color:#991b1b;background:#fee2e2}.issue-type.warning{color:#92400e;background:#fef3c7}.issue-type.info{color:#1e40af;background:#dbeafe}.action-links{display:flex;gap:.35rem;flex-wrap:wrap}.action-links a{font-size:.78rem}.empty-state{padding:1.5rem;text-align:center;color:#64748b}@media(max-width:800px){.quality-summary{grid-template-columns:repeat(2,1fr)}.filters{grid-template-columns:1fr}}
</style>
</head>
<body>
<?php render_admin_nav('data_quality.php', $user); ?>
<div class="page-wrap"><h1>Controle qualite des donnees</h1><p class="page-subtitle">Cette page est en lecture seule : elle signale les anomalies sans modifier la base.</p>
<div class="quality-summary"><div class="quality-card"><div class="quality-label">Anomalies affichees</div><div class="quality-number"><?= count($issues) ?></div></div><div class="quality-card error"><div class="quality-label">Erreurs</div><div class="quality-number"><?= $errors ?></div></div><div class="quality-card warning"><div class="quality-label">Avertissements</div><div class="quality-number"><?= $warnings ?></div></div><div class="quality-card info"><div class="quality-label">Informations</div><div class="quality-number"><?= $infos ?></div></div></div>
<div class="card"><form class="filters" method="get"><div><label for="country">Pays</label><select id="country" name="country"><option value="">Tous les pays</option><?php foreach($countries as $country): ?><option value="<?= h($country['iso2']) ?>" <?= $countryFilter === $country['iso2'] ? 'selected' : '' ?>><?= h($country['name']) ?></option><?php endforeach; ?></select></div><div><label for="operator">Operateur</label><select id="operator" name="operator"><option value="">Tous les operateurs</option><?php foreach($operators as $operator): ?><option value="<?= (int)$operator['id'] ?>" <?= $operatorFilter === (string)$operator['id'] ? 'selected' : '' ?>><?= h($operator['brand']) ?></option><?php endforeach; ?></select></div><div><label for="type">Type d'anomalie</label><select id="type" name="type"><option value="">Tous les types</option><?php foreach($issueTypes as $key=>$label): ?><option value="<?= h($key) ?>" <?= $typeFilter === $key ? 'selected' : '' ?>><?= h($label) ?></option><?php endforeach; ?></select></div><button type="submit" class="primary">Filtrer</button></form></div>
<div class="card" style="margin-top:1rem"><p class="page-subtitle" style="margin:0">Base filtree controlee : <?= $siteCount ?> site(s), <?= $cellCount ?> cellule(s).</p></div>
<div class="card issue-table"><h2>Anomalies</h2><?php if(empty($issues)): ?><div class="empty-state">Aucune anomalie detectee avec ces filtres.</div><?php else: ?><table class="data-table"><tr><th>Gravite</th><th>Type</th><th>Operateur</th><th>Site</th><th>Technologie</th><th>eNB/RNC</th><th>Cell ID</th><th>Bande</th><th>Frequence</th><th>Azimut</th><th>Message</th><th>Actions</th></tr><?php foreach($issues as $issue): ?><tr><td><span class="issue-type <?= h($issue['severity']) ?>"><?= h(ucfirst($issue['severity'])) ?></span></td><td><?= h($issueTypes[$issue['type']] ?? $issue['type']) ?></td><td><?= h($issue['operator_brand'] ?? '') ?></td><td><?= (int)$issue['site_id'] ?></td><td><?= h($issue['tech'] ?? '') ?></td><td><?= h($issue['sub_id2'] ?? '') ?></td><td><?= h($issue['cell_id'] ?? '') ?></td><td><?= h($issue['band'] ?? '') ?></td><td><?= h($issue['frequency'] ?? '') ?></td><td><?= $issue['azimuth'] === null || $issue['azimuth'] === '' ? '' : h($issue['azimuth']) . '°' ?></td><td><?= h($issue['message']) ?></td><td><div class="action-links"><a href="edit_site.php?site_id=<?= (int)$issue['site_id'] ?>">Editer</a><?php if(!empty($issue['sub_id2'])): ?><a href="search_site.php?q=<?= rawurlencode((string)$issue['sub_id2']) ?>">Rechercher eNB</a><?php endif; ?></div></td></tr><?php endforeach; ?></table><?php endif; ?></div>
</div></body></html>