<?php
/**
 * admin/export.php
 * Menu de navigation mis a jour (ajout Import .ntm, Edition de site).
 */
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();

const TECH_RADIO_LABEL_EXPORT = ['2G' => 'GSM', '3G' => 'UMTS', '4G' => 'LTE', '5G' => 'NR', 'CDMA' => 'CDMA'];

function composeLocationForExport(string $tech, ?string $enb, ?string $band, ?string $frequency, $azimuth, ?string $bandwidth, ?string $address): string
{
    $techLabel = TECH_RADIO_LABEL_EXPORT[$tech] ?? $tech;
    $segments = [];

    if ($enb !== null && $enb !== '' && (int)$enb !== 0) {
        $segments[] = "eNB ID $enb";
    }

    $radioLine = trim($techLabel . ' ' . ($band ?? '') . ' ' . ($frequency ?? ''));
    if ($radioLine !== $techLabel && $radioLine !== '') {
        $segments[] = $radioLine;
    }

    if ($azimuth !== null && $azimuth !== '') {
        $segments[] = $azimuth . '°';
    }

    if ($bandwidth !== null && $bandwidth !== '') {
        $segments[] = $bandwidth;
    }

    if ($address !== null && $address !== '') {
        $segments[] = $address;
    }

    return implode(' - ', $segments);
}

function sanitizeFilterValue(string $value, string $pattern): string
{
    return preg_match($pattern, $value) ? $value : '';
}

if (isset($_GET['download'])) {
    $format = $_GET['download'] === 'csv' ? 'csv' : 'ntm';
    $countryFilter = sanitizeFilterValue($_GET['country'] ?? '', '/^[A-Z]{2}$/');
    $operatorFilter = sanitizeFilterValue($_GET['operator'] ?? '', '/^\d+$/');
    $techFilter = sanitizeFilterValue($_GET['tech'] ?? '', '/^(2G|3G|4G|5G|CDMA)$/');

    $sql = '
        SELECT cl.tech, cl.mcc, cl.mnc, cl.cell_id, cl.area_code, cl.sub_id, cl.sub_id2,
               cl.band, cl.frequency, cl.azimuth, cl.bandwidth, cl.arfcn,
               s.lat, s.lon, s.address_desc, o.brand AS operator_brand, c.name AS country_name, c.iso2
        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
        WHERE 1=1
    ';
    $params = [];
    if ($countryFilter !== '') { $sql .= ' AND c.iso2 = ?'; $params[] = $countryFilter; }
    if ($operatorFilter !== '') { $sql .= ' AND o.id = ?'; $params[] = $operatorFilter; }
    if ($techFilter !== '') { $sql .= ' AND cl.tech = ?'; $params[] = $techFilter; }
    $sql .= ' ORDER BY c.name, o.brand, cl.tech, cl.cell_id';

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

    $labelParts = [];
    if ($countryFilter !== '') $labelParts[] = strtolower($countryFilter);
    if ($operatorFilter !== '') $labelParts[] = 'op' . $operatorFilter;
    if ($techFilter !== '') $labelParts[] = strtolower($techFilter);
    $suffix = $labelParts ? '_' . implode('_', $labelParts) : '_all';
    $filename = 'netmonster_export' . $suffix . '_' . date('Ymd_His');

    if ($format === 'csv') {
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '.csv"');

        $out = fopen('php://output', 'w');
        fputcsv($out, [
            'tech', 'mcc', 'mnc', 'operator', 'country', 'cell_id', 'area_code', 'sub_id', 'enb_rnc',
            'band', 'frequency', 'azimuth', 'bandwidth', 'arfcn', 'lat', 'lon', 'address_desc', 'location_desc_recompose',
        ], ';');
        foreach ($rows as $r) {
            $composed = composeLocationForExport($r['tech'], (string)($r['sub_id2'] ?? ''), $r['band'], $r['frequency'], $r['azimuth'], $r['bandwidth'], $r['address_desc']);
            fputcsv($out, [
                $r['tech'], $r['mcc'], $r['mnc'], $r['operator_brand'], $r['country_name'],
                $r['cell_id'], $r['area_code'], $r['sub_id'], $r['sub_id2'],
                $r['band'], $r['frequency'], $r['azimuth'], $r['bandwidth'], $r['arfcn'],
                $r['lat'], $r['lon'], $r['address_desc'], $composed,
            ], ';');
        }
        fclose($out);
        exit;
    }

    header('Content-Type: text/plain; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '.ntm"');

    $tagMap = ['2G' => '2G', '3G' => '3G', '4G' => '4G', '5G' => '5G', 'CDMA' => 'CD2'];

    foreach ($rows as $r) {
        $tag = $tagMap[$r['tech']] ?? $r['tech'];
        $lat = $r['lat'] ?? 0;
        $lon = $r['lon'] ?? 0;
        $arfcn = $r['arfcn'] ?? 0;

        $location = composeLocationForExport(
            $r['tech'],
            $r['tech'] === '4G' ? (string)($r['sub_id2'] ?? '') : null,
            $r['band'], $r['frequency'], $r['azimuth'], $r['bandwidth'], $r['address_desc']
        );

        if (in_array($r['tech'], ['3G', '4G'], true)) {
            echo implode(';', [$tag, $r['mcc'], $r['mnc'], $r['cell_id'], $r['area_code'], $r['sub_id2'] ?? 0, $r['sub_id'] ?? 0, $lat, $lon, $location, $arfcn]) . "\r\n";
        } elseif ($r['tech'] === 'CDMA') {
            echo implode(';', [$tag, $r['mcc'], $r['mnc'], $r['cell_id'], $r['area_code'], 0, $r['sub_id'] ?? 0, $lat, $lon, $location, '']) . "\r\n";
        } else {
            echo implode(';', [$tag, $r['mcc'], $r['mnc'], $r['cell_id'], $r['area_code'], 0, $r['sub_id'] ?? 0, $lat, $lon, $location, $arfcn]) . "\r\n";
        }
    }
    exit;
}

$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);

$totalCells = (int)$db->query('SELECT COUNT(*) FROM cells')->fetchColumn();
$totalSites = (int)$db->query('SELECT COUNT(*) FROM sites')->fetchColumn();
$lastValidated = $db->query('SELECT MAX(validated_at) FROM cells')->fetchColumn();

$byCountryStats = $db->query('
    SELECT c.name AS country_name, c.iso2, COUNT(DISTINCT s.id) AS site_count, COUNT(cl.id) AS cell_count
    FROM countries c
    LEFT JOIN sites s ON s.country_id = c.id
    LEFT JOIN cells cl ON cl.site_id = s.id
    GROUP BY c.id
    ORDER BY c.name
')->fetchAll(PDO::FETCH_ASSOC);

$user = current_admin_username();
?>
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8"><title>NetMonster Admin - Export</title>
<link rel="stylesheet" href="assets/theme.css">
<style>
.export-options { display: grid; grid-template-columns: 1fr 1fr; gap: 1rem; margin-top: 1rem; }
.export-card { border: 1px solid var(--border); border-radius: var(--radius-sm); padding: 1rem; background: var(--surface-alt); }
.export-card h3 { margin: 0 0 .4rem; font-size: .95rem; }
.export-card p { margin: 0 0 .8rem; font-size: .82rem; color: var(--text-muted); }
</style>
</head>
<body>
<?php render_admin_nav('export.php', $user); ?>

<div class="page-wrap">
    <h1>Export de la base pour mise en production</h1>
    <p class="page-subtitle">Telechargez la base validee (sites + cellules) pour l'envoyer sur votre serveur de diffusion.</p>

    <div class="stats-bar">
        <div class="stat-chip"><strong><?= $totalSites ?></strong>site(s) valides</div>
        <div class="stat-chip"><strong><?= $totalCells ?></strong>cellule(s) validees</div>
        <div class="stat-chip"><strong><?= $lastValidated ? htmlspecialchars(date('d/m/Y H:i', strtotime($lastValidated))) : '—' ?></strong>derniere validation</div>
    </div>

    <div class="card">
        <h2>Repartition par pays</h2>
        <table class="data-table">
            <tr><th>Pays</th><th>Sites</th><th>Cellules</th></tr>
            <?php foreach ($byCountryStats as $s): ?>
            <tr>
                <td><?= htmlspecialchars($s['country_name']) ?></td>
                <td><?= (int)$s['site_count'] ?></td>
                <td><?= (int)$s['cell_count'] ?></td>
            </tr>
            <?php endforeach; ?>
        </table>
    </div>

    <div class="card">
        <h2>Filtrer avant export</h2>
        <p class="page-subtitle" style="margin-bottom:0.8rem;">Laissez sur "Tous" pour exporter l'integralite de la base validee.</p>
        <form class="filters" method="get" id="exportFilters">
            <select name="country">
                <option value="">Tous les pays</option>
                <?php foreach ($countries as $c): ?>
                    <option value="<?= htmlspecialchars($c['iso2']) ?>"><?= htmlspecialchars($c['name']) ?></option>
                <?php endforeach; ?>
            </select>
            <select name="operator">
                <option value="">Tous les operateurs</option>
                <?php foreach ($operators as $o): ?>
                    <option value="<?= $o['id'] ?>"><?= htmlspecialchars($o['brand']) ?> (<?= htmlspecialchars($o['mcc'] . '-' . $o['mnc']) ?>)</option>
                <?php endforeach; ?>
            </select>
            <select name="tech">
                <option value="">Toutes technos</option>
                <?php foreach (['2G', '3G', '4G', '5G', 'CDMA'] as $t): ?>
                    <option value="<?= $t ?>"><?= $t ?></option>
                <?php endforeach; ?>
            </select>
        </form>

        <div class="export-options">
            <div class="export-card">
                <h3>Format .ntm</h3>
                <p>Reconstruit le format d'origine NetMonster (CSV ";"), Location recomposee depuis les champs structures, pret a etre republie tel quel.</p>
                <button type="button" class="primary" onclick="downloadExport('ntm')">Telecharger en .ntm</button>
            </div>
            <div class="export-card">
                <h3>Format .csv</h3>
                <p>Format tabulaire avec colonnes separees (bande, frequence, azimut, largeur de bande), pratique pour verification dans un tableur.</p>
                <button type="button" class="primary" onclick="downloadExport('csv')">Telecharger en .csv</button>
            </div>
        </div>
    </div>
</div>

<script>
function downloadExport(format) {
    const form = document.getElementById('exportFilters');
    const params = new URLSearchParams(new FormData(form));
    params.set('download', format);
    window.location.href = 'export.php?' + params.toString();
}
</script>
</body>
</html>