<?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");

$classeid   = $_REQUEST['classe']   ?? '';
$subjectid  = $_REQUEST['subject']  ?? '';

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

/* ──────────────────────────────────────────────────────────────
   STEP 1 – Show selection form if class or subject is missing
────────────────────────────────────────────────────────────── */
if ($classeid === '' || $subjectid === '') {
    $resClasses  = mysqli_query($link, "SELECT * FROM classes ORDER BY name");
    $resSubjects = mysqli_query($link, "SELECT * FROM subjects ORDER BY ordering, name");
    ?>
    <!DOCTYPE html>
    <html lang="fr">
    <head>
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="stylesheet" href="w3.css">
        <title>Relevé par Matière</title>
    </head>
    <body>
    <div class="w3-container w3-padding-large" style="max-width:500px;margin:40px auto;">
        <h3 class="w3-center">Relevé de Notes par Matière</h3>
        <form method="get">
            <label class="w3-text-blue"><b>Classe :</b></label>
            <select name="classe" class="w3-input w3-border w3-margin-bottom" required>
                <option value="">-- Choisir une classe --</option>
                <?php while ($row = mysqli_fetch_assoc($resClasses)): ?>
                    <option value="<?= $row['id'] ?>"
                        <?= ($classeid == $row['id']) ? 'selected' : '' ?>>
                        <?= strtoupper(htmlspecialchars($row['name'])) ?>
                    </option>
                <?php endwhile; ?>
            </select>

            <label class="w3-text-blue"><b>Matière :</b></label>
            <select name="subject" class="w3-input w3-border w3-margin-bottom" required>
                <option value="">-- Choisir une matière --</option>
                <?php while ($row = mysqli_fetch_assoc($resSubjects)): ?>
                    <option value="<?= $row['id'] ?>"
                        <?= ($subjectid == $row['id']) ? 'selected' : '' ?>>
                        <?= strtoupper(htmlspecialchars($row['name'])) ?>
                    </option>
                <?php endwhile; ?>
            </select>

            <button type="submit" class="w3-button w3-blue w3-block">Afficher</button>
        </form>
    </div>
    </body>
    </html>
    <?php
    exit;
}

/* ──────────────────────────────────────────────────────────────
   STEP 2 – Load class & subject names
────────────────────────────────────────────────────────────── */
$className   = '';
$subjectName = '';

$resClass = mysqli_query($link, "SELECT name FROM classes WHERE id = '" . mysqli_real_escape_string($link, $classeid) . "'");
if ($row = mysqli_fetch_assoc($resClass)) $className = strtoupper(htmlspecialchars($row['name']));

$resSubject = mysqli_query($link, "SELECT name FROM subjects WHERE id = '" . mysqli_real_escape_string($link, $subjectid) . "'");
if ($row = mysqli_fetch_assoc($resSubject)) $subjectName = strtoupper(htmlspecialchars($row['name']));

$today = date("d/m/Y");

/* ──────────────────────────────────────────────────────────────
   STEP 3 – Fetch sub-subjects for the chosen subject & class
────────────────────────────────────────────────────────────── */
$subSubjectsQuery = "
    SELECT
        ss.id   AS subid,
        ss.name AS subname,
        gs.coeficient AS maxgrade
    FROM subSubjects ss
    INNER JOIN gradesSubjects gs ON gs.subSubjectID = ss.id AND gs.classID = '" . mysqli_real_escape_string($link, $classeid) . "'
    WHERE ss.subjectID = '" . mysqli_real_escape_string($link, $subjectid) . "'
    ORDER BY ss.id
";
$subResult = mysqli_query($link, $subSubjectsQuery);
if (!$subResult) die("Erreur requête sous-matières: " . mysqli_error($link));

$subSubjects = [];
while ($row = mysqli_fetch_assoc($subResult)) {
    $subSubjects[$row['subid']] = [
        'name'     => $row['subname'],
        'maxgrade' => floatval($row['maxgrade'] ?? 20),
    ];
}

if (empty($subSubjects)) die("Aucune sous-matière trouvée pour cette matière / classe.");

/* ──────────────────────────────────────────────────────────────
   STEP 4 – Fetch students
────────────────────────────────────────────────────────────── */
$eleveResult = mysqli_query($link, "
    SELECT id, UPPER(CONCAT(firstName, ' ', lastName)) AS fullname
    FROM students
    WHERE classID = '" . mysqli_real_escape_string($link, $classeid) . "'
    ORDER BY firstName, lastName
");
$eleveList = [];
while ($row = mysqli_fetch_assoc($eleveResult)) {
    $eleveList[$row['id']] = $row['fullname'];
}

if (empty($eleveList)) die("Aucun élève trouvé pour cette classe.");

/* ──────────────────────────────────────────────────────────────
   STEP 5 – Compute averages per student per sub-subject
────────────────────────────────────────────────────────────── */
$moyennes = [];
foreach ($eleveList as $eleveID => $_) {
    foreach ($subSubjects as $subid => $sub) {
        $sql = "SELECT grade FROM grades
                WHERE studentID = '" . mysqli_real_escape_string($link, $eleveID) . "'
                  AND subSubjectID = '" . mysqli_real_escape_string($link, $subid) . "'
                  AND classID = '" . mysqli_real_escape_string($link, $classeid) . "'";
        $res   = mysqli_query($link, $sql);
        $sum   = 0;
        $count = 0;
        $max   = $sub['maxgrade'] > 0 ? $sub['maxgrade'] : 20;
        while ($row = mysqli_fetch_assoc($res)) {
            $g = floatval($row['grade']);
            if ($g != -1) {
                $sum += ($g / $max) * 20;
                $count++;
            }
        }
        $moyennes[$eleveID][$subid] = ($count > 0) ? round($sum / $count, 1) : '';
    }
}

/* ──────────────────────────────────────────────────────────────
   STEP 6 – Class averages per sub-subject
────────────────────────────────────────────────────────────── */
$classAvg = [];
foreach ($subSubjects as $subid => $_) {
    $sum = $count = 0;
    foreach ($eleveList as $eleveID => $_e) {
        $v = $moyennes[$eleveID][$subid] ?? '';
        if ($v !== '') { $sum += $v; $count++; }
    }
    $classAvg[$subid] = ($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>Relevé – <?= $subjectName ?> – <?= $className ?></title>
    <style>
        body { font-family: "Times New Roman", Times, serif; font-size: 14px; }
        table { border-collapse: collapse; width: 100%; }
        th, td { border: 1px solid #000; padding: 5px; text-align: center; vertical-align: bottom; }
        th.sub-header { background-color: #ddd; }

        /* Rotated column headers */
        .rotate div {
            writing-mode: vertical-rl;
            height: 220px;
            font-size: 13px;
            overflow: hidden;
            word-wrap: break-word;
            line-height: 1;
            text-align: left;
            position: relative;
            left: 50%;
            transform: translateX(-90%);
        }

        /* Subject average column */
        .moyenne-col { background-color: #e0f7fa; font-weight: bold; }

        /* Rank column */
        .rank-col { background-color: #fff9c4; }

        @media print {
            .noprint { display: none; }
            @page { size: landscape; margin: 8mm; }
        }
    </style>
</head>
<body>

<!-- Toolbar (hidden on print) -->
<div class="noprint w3-bar w3-light-grey w3-padding">
    <button onclick="window.print()" class="w3-button w3-blue w3-margin-right">
        🖨️ Imprimer
    </button>
    <a href="?" class="w3-button w3-grey">← Changer de sélection</a>
</div>

<!-- Header info -->
<div style="margin:16px 0; font-size:15px; line-height:1.8;">
    <strong>Classe :</strong> <?= $className ?> &nbsp;|&nbsp;
    <strong>Matière :</strong> <?= $subjectName ?> &nbsp;|&nbsp;
    <strong>Date :</strong> <?= $today ?>
</div>

<h2 style="text-align:center; margin-bottom:14px;">
    Relevé de Notes — <?= $subjectName ?>
</h2>

<table>
<thead>
    <!-- Row 1: subject group header -->
    <tr>
        <th rowspan="2">#</th>
        <th rowspan="2" style="text-align:left; min-width:160px;">Élève</th>
        <th class="sub-header" colspan="<?= count($subSubjects) + 1 ?>">
            <?= $subjectName ?>
        </th>
        <th rowspan="2" class="rank-col">Rang</th>
    </tr>

    <!-- Row 2: sub-subject columns + subject average -->
    <tr>
        <?php foreach ($subSubjects as $subid => $sub): ?>
            <th height="200" class="sub-header">
                <span class="rotate"><div>
                    <?= htmlspecialchars($sub['name']) ?><br>
                    <small>(max <?= $sub['maxgrade'] ?>)</small>
                </div></span>
            </th>
        <?php endforeach; ?>
        <th class="moyenne-col" height="200">
            <span class="rotate"><div><b>Moyenne sur 20</b></div></span>
        </th>
    </tr>
</thead>

<tbody>
<?php
/* ── Build ranked list ── */
$rankData = [];
foreach ($eleveList as $eleveID => $eleveNom) {
    $weighted = 0; $coefSum = 0;
    foreach ($subSubjects as $subid => $sub) {
        $v = $moyennes[$eleveID][$subid] ?? '';
        if ($v !== '' && $sub['maxgrade'] > 0) {
            $weighted += $v * $sub['maxgrade'];
            $coefSum  += $sub['maxgrade'];
        }
    }
    $moy = ($coefSum > 0) ? round($weighted / $coefSum, 1) : null;
    $rankData[$eleveID] = ['name' => $eleveNom, 'moy' => $moy];
}

// Rank (dense ranking)
$sorted = $rankData;
uasort($sorted, fn($a, $b) => ($b['moy'] ?? -1) <=> ($a['moy'] ?? -1));
$ranks = []; $rank = 1;
$prev = null;
foreach ($sorted as $eid => $d) {
    if ($d['moy'] !== null) {
        if ($d['moy'] !== $prev) { $ranks[$eid] = $rank; $prev = $d['moy']; }
        else { $ranks[$eid] = $ranks[array_key_last($ranks)]; }
        $rank++;
    } else {
        $ranks[$eid] = '-';
    }
}

/* ── Print student rows ── */
$num = 1;
foreach ($eleveList as $eleveID => $eleveNom):
    $weighted = 0; $coefSum = 0;
?>
    <tr>
        <td><?= $num++ ?></td>
        <td style="text-align:left;"><?= htmlspecialchars($eleveNom) ?></td>

        <?php foreach ($subSubjects as $subid => $sub):
            $v   = $moyennes[$eleveID][$subid] ?? '';
            $max = $sub['maxgrade'];
            if ($v !== '' && $max > 0) { $weighted += $v * $max; $coefSum += $max; }
        ?>
            <td><?= $v !== '' ? $v : '-' ?></td>
        <?php endforeach; ?>

        <?php $moy = ($coefSum > 0) ? round($weighted / $coefSum, 1) : '-'; ?>
        <td class="moyenne-col"><b><?= $moy ?></b></td>
        <td class="rank-col"><b><?= $ranks[$eleveID] ?? '-' ?></b></td>
    </tr>
<?php endforeach; ?>

<!-- Class average row -->
<tr style="background:#f5f5f5;">
    <td colspan="2"><b>Moy. Classe</b></td>
    <?php foreach ($subSubjects as $subid => $_): ?>
        <td><small><?= $classAvg[$subid] ?></small></td>
    <?php endforeach; ?>
    <?php
        // Overall class average for the subject
        $cw = 0; $cc = 0;
        foreach ($subSubjects as $subid => $sub) {
            $v = $classAvg[$subid];
            if ($v !== '-' && $sub['maxgrade'] > 0) { $cw += floatval($v) * $sub['maxgrade']; $cc += $sub['maxgrade']; }
        }
        $classMoy = ($cc > 0) ? round($cw / $cc, 1) : '-';
    ?>
    <td class="moyenne-col"><b><?= $classMoy ?></b></td>
    <td class="rank-col">–</td>
</tr>
</tbody>
</table>

</body>
</html>
