<!DOCTYPE HTML>
<html lang="de">
<head>
    <meta charset="utf-8">
    <title>Länderdaten nach Regionen</title>
    <?php
    include_once("db.inc.php");
    ?>
</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="post">
    <?php
    // Select-Auswahl für Regionen
    $sql = "SELECT region_id, name FROM regions ORDER BY name";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    
    echo "Region <select name='region' id='region'>";
    echo "<option value=''>Region wählen</option>";
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $region) {
        $selected = (isset($_POST['region']) && $_POST['region'] == $region['region_id']) ? 'selected' : '';
        echo "<option value='" . htmlspecialchars($region['region_id']) . "' $selected>" . 
             htmlspecialchars($region['name']) . "</option>";
    }
    echo "</select>";
    
    // Ausgabe der Daten
    if(isset($_POST["region"]) && !empty($_POST["region"])){
        try {

            $auswahl = "SELECT countries.name AS Land, countries.area AS 'Fläche', AVG(country_stats.gdp) AS 'AVG BIP', AVG(country_stats.population) AS 'AVG Bevölkerung', continents.name AS Kontinent from regions 
                    JOIN continents on continents.continent_id = regions.continent_id 
                    JOIN countries on regions.region_id = countries.region_id
                    JOIN country_stats on countries.country_id = country_stats.country_id 
                    WHERE regions.region_id = :region_id GROUP BY countries.country_id, countries.name, countries.area, continents.name";
            $stmt = $pdo->prepare($auswahl);
            $stmt->bindParam(':region_id', $_POST['region'], PDO::PARAM_INT);
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            if(count($results) > 0) {
                echo "<br> <br>";
                echo "<table border='3'>";
                echo "<tr><th>Land</th><th>Fläche</th><th>AVG BIP</th><th>AVG Bevölkerung</th><th>Kontinent</th></tr>";
                
                foreach($results as $row) {
                    echo "<tr>";
                    echo "<td>" . htmlspecialchars($row['Land']) . "</td>";
                    echo "<td>" . htmlspecialchars($row['Fläche'], 0, ',', '.') . "</td>";
                    echo "<td>" . htmlspecialchars($row['AVG BIP'], 2, ',', '.') . "</td>";
                    echo "<td>" . htmlspecialchars($row['AVG Bevölkerung'], 0, ',', '.') . "</td>";
                    echo "<td>" . htmlspecialchars($row['Kontinent']) . "</td>";
                    echo "</tr>";
                }
                echo "</table>";
            } else {
                echo "<p>Keine Daten.</p>";
            }
        } catch(PDOException $e) {
            echo "<p>Fehler bei der Datenbankabfrage: " . htmlspecialchars($e->getMessage()) . "</p>";
        }
    }
    ?>
    <br>
    <input type="submit" value="anzeigen">
</form>   
</body>
</html>