<?php
session_start();
include '_serveur.php';

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
mysqli_set_charset($link, "utf8mb4");

$cycle = $_REQUEST['cycle'] ?? '';
$classeid = $_GET['classe'] ?? ($_POST['classe'] ?? '');

if (!$link) die("Erreur connexion base: " . mysqli_connect_error());

if ($classeid == '') {
    echo "<form method='get' class='w3-container w3-padding'>
        <label>Choisir une classe : </label>
        <select name='classe' class='w3-input w3-border w3-margin-bottom'>";
    $res = mysqli_query($link, "SELECT * FROM classes ORDER BY name");
    while ($row = mysqli_fetch_assoc($res)) {
        echo "<option value='" . $row['id'] . "'>" . strtoupper(htmlspecialchars($row['name'])) . "</option>";
    }
    echo "</select>
        <input type='hidden' name='cycle' value='" . htmlspecialchars($cycle) . "' />
        <button type='submit' class='w3-button w3-blue'>Afficher</button>
    </form>";
    exit;
}
$className = '';
$resClass = mysqli_query($link, "SELECT name FROM classes WHERE id = '$classeid'");
if ($row = mysqli_fetch_assoc($resClass)) {
    $className = strtoupper(htmlspecialchars($row['name']));
}
$today = date("d/m/Y");
// Fetch subSubjects
$subSubjectsQuery = "
    SELECT 
        ss.id AS subid,
        ss.name AS subname,
        s.id AS subjectid,
        s.name AS subjectname,
        s.ordering AS subjectordering,
        gs.coeficient AS maxgrade
    FROM subSubjects ss
    INNER JOIN subjects s ON ss.subjectID = s.id
    INNER JOIN gradesSubjects gs ON gs.subSubjectID = ss.id AND gs.classID = '$classeid'
    ORDER BY s.ordering, ss.id
";
$subResult = mysqli_query($link, $subSubjectsQuery);
if (!$subResult) die("Erreur requête sous-matières: " . mysqli_error($link));

$subSubjects = [];
$subjectsOrder = [];

while ($row = mysqli_fetch_assoc($subResult)) {
    $subid = $row['subid'];
    $subjectid = $row['subjectid'];
    $subSubjects[$subid] = [
        'name' => $row['subname'],
        'subjectid' => $subjectid,
        'subjectname' => $row['subjectname'],
        'maxgrade' => floatval($row['maxgrade'] ?? 20),
        'subjectordering' => $row['subjectordering']
    ];
    if (!isset($subjectsOrder[$subjectid])) {
        $subjectsOrder[$subjectid] = [
            'name' => $row['subjectname'],
            'ordering' => $row['subjectordering']
        ];
    }
}
uasort($subjectsOrder, fn($a, $b) => $a['ordering'] <=> $b['ordering']);

// Fetch students
$eleveResult = mysqli_query($link, "
    SELECT id, UPPER(CONCAT(firstName, ' ', lastName)) AS fullname
    FROM students
    WHERE classID = '$classeid'
    ORDER BY firstName, lastName
");
$eleveList = [];
while ($row = mysqli_fetch_assoc($eleveResult)) {
    $eleveList[$row['id']] = $row['fullname'];
}
if (count($eleveList) == 0 || count($subSubjects) == 0) {
    die("Aucun élève ou sous-matière trouvée pour cette classe.");
}

// Calculate student averages
$moyennes = [];
foreach ($eleveList as $eleveID => $eleveNom) {
    foreach ($subSubjects as $subid => $sub) {
        $sql = "SELECT grade FROM grades WHERE studentID = '$eleveID' AND subSubjectID = '$subid' AND classID = '$classeid'";
        $res = mysqli_query($link, $sql);
        $sum = 0;
        $count = 0;
        $maxgrade = $sub['maxgrade'] > 0 ? $sub['maxgrade'] : 20;
        while ($row = mysqli_fetch_assoc($res)) {
            $grade = floatval($row['grade']);
            if ($grade != -1) {
                $normalized = ($grade / $maxgrade) * 20;
                $sum += $normalized;
                $count++;
            }
        }
        $moyennes[$eleveID][$subid] = ($count > 0) ? round($sum / $count, 1) : '';
    }
}

// Calculate subject-class averages
$classAverages = [];
foreach ($subjectsOrder as $subjectid => $subject) {
    $sum = 0;
    $count = 0;
    $coef = 0;
    foreach ($subSubjects as $subid => $sub) {
        if ($sub['subjectid'] == $subjectid) {
            $maxgrade = $sub['maxgrade'];
            $subjectSum = 0;
            $subjectCount = 0;
            foreach ($eleveList as $eleveID => $eleveNom) {
                $avg = $moyennes[$eleveID][$subid] ?? '';
                if ($avg !== '') {
                    $subjectSum += $avg;
                    $subjectCount++;
                    $sum += $avg * $maxgrade;
                    $count += $maxgrade;
                }
            }
        }
    }
    $classAverages[$subjectid] = ($count > 0) ? round($sum / $count, 1) : '-';
}
?>
<!DOCTYPE html>
<html lang="fr">
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="w3.css">
<title>Moyennes par Sous-Matières</title>
<style>
table { border-collapse: collapse; width: 100%; font-family: "Times New Roman", Times, serif; font-size: 14px; }
th, td { border: 1px solid #000; padding: 5px; text-align: center; vertical-align: bottom; }
th.subject-header { background-color: #ddd; }
.rotate div {
    writing-mode: vertical-rl;
    height: 250px;
    font-size: 13px;
    overflow: hidden;
    word-wrap: break-word;
    line-height: 0.99;
    text-align: left;
    left: 50%;
    transform: translateX(-90%);
    position: relative;
}
@media print {
    .noprint { display: none; }
    .page-break { display:block; page-break-after:always; }
    @page { size: landscape; margin: 5mm; }
}
.moyenne-col {
    background-color: #e0f7fa; /* Light cyan */
    font-weight: bold;
}
</style>
</head>
<body>
<div class='noprint'><button type="button" onclick="window.print()"><img src="imprimante.png" height="32" width="40" /></button></div>
<div style="margin: 20px 0; font-size: 16px;">
    <strong>Classe :</strong> <?= $className ?><br>
    <strong>Date :</strong> <?= $today ?>
</div>
<h2 class="w3-center">Tableau des moyennes par sous-matière groupées par matière</h2>

<table>
<thead>
<tr>
    <th rowspan="2">#</th>
    <th rowspan="2">Élève</th>
    <?php
    foreach ($subjectsOrder as $subjectid => $subject) {
        $count = count(array_filter($subSubjects, fn($s) => $s['subjectid'] == $subjectid));
        echo "<th class='subject-header' colspan='" . ($count + 1) . "'>" . htmlspecialchars($subject['name']) . "</th>";
    }
    ?>
    <th rowspan="2">Moy. Générale</th>
</tr>
<tr>
    <?php
    foreach ($subjectsOrder as $subjectid => $subject) {
        foreach ($subSubjects as $subid => $sub) {
            if ($sub['subjectid'] == $subjectid) {
                echo "<th height='200'><span class='rotate'><div>" . htmlspecialchars($sub['name']) . "<br><small>(max " . $sub['maxgrade'] . ")</small></div></span></th>";
            }
        }
        echo "<th class='moyenne-col' height='200'><span class='rotate'><div><b>Moyenne<br>sur 20</b></div></span></th>";
    }
    ?>
</tr>
</thead>
<tbody>
<?php
$num = 1;
foreach ($eleveList as $eleveID => $eleveNom) {
    echo "<tr><td>" . $num++ . "</td><td>" . htmlspecialchars($eleveNom) . "</td>";
    $totalWeighted = 0;
    $coefSum = 0;
    foreach ($subjectsOrder as $subjectid => $subject) {
        $subjectSum = 0;
        $subjectCoef = 0;
        foreach ($subSubjects as $subid => $sub) {
            if ($sub['subjectid'] == $subjectid) {
                $avg = $moyennes[$eleveID][$subid] ?? '';
                $maxgrade = $sub['maxgrade'];
                if ($avg !== '' && $maxgrade > 0) {
                    $subjectSum += $avg * $maxgrade;
                    $subjectCoef += $maxgrade;
                    $totalWeighted += $avg * $maxgrade;
                    $coefSum += $maxgrade;
                }
                echo "<td>" . ($avg !== '' ? $avg : '-') . "</td>";
            }
        }
        $moySubject = ($subjectCoef > 0) ? round($subjectSum / $subjectCoef, 1) : '-';
        echo "<td class='moyenne-col'><b>$moySubject</b></td>";
    }
    $moyg = ($coefSum > 0) ? round($totalWeighted / $coefSum, 1) : '-';
    echo "<td><b>$moyg</b></td></tr>";
}

// Class average row
echo "<tr><td colspan='2'><b>Moy. Classe</b></td>";
foreach ($subjectsOrder as $subjectid => $subject) {
    foreach ($subSubjects as $subid => $sub) {
        if ($sub['subjectid'] == $subjectid) {
            echo "<td></td>";
        }
    }
    $moyclass = $classAverages[$subjectid] ?? '-';
    echo "<td class='moyenne-col'><b>$moyclass</b></td>";
}
echo "<td></td></tr>";
?>
</tbody>
</table>
</body>
</html>