<?php

include_once("db.inc.php");

$selected_region = $_GET['region'] ?? 'Caribbean';

// Regionen für Dropdown aus Tabelle regions holen
$stmt_regions = $pdo->query("SELECT region_name FROM regions ORDER BY region_name");
$regions = $stmt_regions->fetchAll(PDO::FETCH_COLUMN);


$sql = "
    SELECT 
        country_name,
        area,
        AVG(gdp) as avg_gdp,
        AVG(population) as avg_population,
        continent
    FROM nation
    WHERE region = :region
    GROUP BY country_name, area, continent
    ORDER BY country_name
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['region' => $selected_region]);
$countries = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Länderdaten nach Regionen</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 40px; }
        h1 { color: #333; }
        h2 { color: #666; margin-top: 30px; }
        form { margin: 20px 0; padding: 20px; background: #f5f5f5; border-radius: 8px; }
        select, button { padding: 8px 12px; font-size: 16px; }
        table { border-collapse: collapse; width: 100%; margin-top: 20px; }
        th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
        th { background-color: #4CAF50; color: white; }
        tr:nth-child(even) { background-color: #f2f2f2; }
        .number { text-align: right; }
    </style>
</head>
<body>

<h1>Länderdaten nach Regionen</h1>
<p>Bitte wählen Sie die Region aus, deren Länder angezeigt werden sollen.</p>

<form method="GET" action="">
    <h2>Region | 
        <select name="region" onchange="this.form.submit()">
            <?php foreach ($regions as $region): ?>
                <option value="<?= htmlspecialchars($region) ?>" 
                    <?= $region == $selected_region ? 'selected' : '' ?>>
                    <?= htmlspecialchars($region) ?>
                </option>
            <?php endforeach; ?>
        </select>
    </h2>
    <button type="submit">anzeigen</button>
</form>

<?php if (!empty($countries)): ?>
    <table>
        <thead>
            <tr>
                <th>Land</th>
                <th class="number">Fläche</th>
                <th class="number">AVG BIP</th>
                <th class="number">AVG Bevälkerung</th>
                <th>Kontinent</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($countries as $country): ?>
                <tr>
                    <td><?= htmlspecialchars($country['country_name']) ?></td>
                    <td class="number"><?= number_format($country['area'], 2) ?></td>
                    <td class="number"><?= number_format($country['avg_gdp'], 4) ?></td>
                    <td class="number"><?= number_format($country['avg_population'], 4) ?></td>
                    <td><?= htmlspecialchars($country['continent']) ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
<?php elseif ($_SERVER['REQUEST_METHOD'] === 'GET'): ?>
    <p>Keine Daten für die ausgewählte Region gefunden.</p>
<?php endif; ?>

</body>
</html>