<?php
// ═══════════════════════════════════════════════
// DEBUG FILE — relever_debug.php
// Upload this, visit it, and share the output
// ═══════════════════════════════════════════════
error_reporting(E_ALL);
ini_set('display_errors', 1);

session_start();
include '_serveur.php';
mysqli_set_charset($link, "utf8");

$cycle    = $_GET['cycle'] ?? $_POST['cycle'] ?? $_SESSION['cycle'] ?? '';
$sortMode = $_GET['sort']  ?? 'name';

$classeId   = $_POST['classeId']   ?? $_GET['classeId']   ?? '';
$classeName = $_POST['classeName'] ?? $_GET['classeName'] ?? '';
$semId      = $_POST['semId']      ?? $_GET['semId']      ?? '';
$semName    = $_POST['semName']    ?? $_GET['semName']     ?? '';
$submitted  = ($classeId !== '' && $semId !== '');

if (isset($_POST['chercher'])) {
    $cl1        = explode(";", $_POST['cla'] ?? '', 2);
    $se1        = explode(";", $_POST['ctr'] ?? '', 2);
    $classeId   = $cl1[0];
    $classeName = $cl1[1] ?? '';
    $semId      = $se1[0];
    $semName    = $se1[1] ?? '';
    $submitted  = ($classeId !== '' && $semId !== '');
    if ($cycle) $_SESSION['cycle'] = $cycle;
}

function buildUrl(array $extra = []): string {
    global $cycle, $classeId, $classeName, $semId, $semName, $sortMode;
    return '?' . http_build_query(array_merge([
        'cycle'=>$cycle,'classeId'=>$classeId,'classeName'=>$classeName,
        'semId'=>$semId,'semName'=>$semName,'sort'=>$sortMode,
    ], $extra));
}
?>
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>DEBUG — Relevé des Notes</title>
<style>
* { margin:0; padding:0; box-sizing:border-box; }
body { font-family: monospace; background:#1e1e2e; color:#cdd6f4; padding:20px; font-size:13px; }
h2 { color:#89b4fa; margin:16px 0 8px; font-size:15px; border-bottom:1px solid #313244; padding-bottom:4px; }
h3 { color:#a6e3a1; margin:12px 0 6px; font-size:13px; }
.box { background:#313244; border-radius:8px; padding:14px; margin-bottom:14px; }
.ok  { color:#a6e3a1; font-weight:700; }
.err { color:#f38ba8; font-weight:700; }
.warn{ color:#fab387; font-weight:700; }
.val { color:#f9e2af; }
table { border-collapse:collapse; width:100%; margin-top:8px; }
th { background:#45475a; color:#cdd6f4; padding:6px 10px; text-align:left; font-size:12px; }
td { padding:5px 10px; border-bottom:1px solid #313244; font-size:12px; }
tr:nth-child(even) td { background:#313244; }
.formbar { background:#313244; border-radius:8px; padding:14px; margin-bottom:16px; display:flex; flex-wrap:wrap; gap:10px; align-items:flex-end; }
.formbar label { font-size:11px; color:#89b4fa; display:block; margin-bottom:3px; font-family:sans-serif; }
.formbar select { padding:7px 10px; border-radius:6px; border:1px solid #45475a; background:#1e1e2e; color:#cdd6f4; font-size:13px; min-width:150px; }
.btn { padding:8px 16px; border:none; border-radius:6px; cursor:pointer; font-size:12px; font-weight:700; background:#89b4fa; color:#1e1e2e; font-family:monospace; }
.btn-sort { background:#cba6f7; }
</style>
</head>
<body>

<h2>🔍 DEBUG — Relevé des Notes</h2>

<!-- FORM -->
<div class="formbar">
  <form method="post" action="?cycle=<?= urlencode($cycle) ?>" style="display:flex;flex-wrap:wrap;gap:10px;align-items:flex-end;">
    <input type="hidden" name="cycle" value="<?= htmlspecialchars($cycle) ?>">
    <div>
      <label>Classe</label>
      <select name="cla">
        <?php
        if ($cycle) {
            $st = $link->prepare("SELECT id, name FROM classes WHERE cycleID=? ORDER BY id ASC");
            $st->bind_param("s", $cycle); $st->execute(); $rc = $st->get_result();
            while ($r = $rc->fetch_assoc()) {
                $n = strtoupper($r['name']);
                $sel = ($classeId == $r['id']) ? 'selected' : '';
                echo '<option value="'.$r['id'].';'.$n.'" '.$sel.'>'.htmlspecialchars($n).'</option>';
            }
        } else echo '<option>-- cycle manquant --</option>';
        ?>
      </select>
    </div>
    <div>
      <label>Contrôle / Semestre</label>
      <select name="ctr">
        <?php
        $rs = $link->query("SELECT DISTINCT s.id, s.name FROM semesters s JOIN classesSemesters cs ON s.id=cs.semesterID JOIN grades g ON g.semesterID=s.id ORDER BY s.id ASC");
        if ($rs) while ($r = $rs->fetch_assoc()) {
            $sel = ($semId == $r['id']) ? 'selected' : '';
            echo '<option value="'.$r['id'].';'.htmlspecialchars($r['name']).'" '.$sel.'>'.htmlspecialchars($r['name']).'</option>';
        }
        ?>
      </select>
    </div>
    <button type="submit" name="chercher" value="1" class="btn">🔍 Afficher</button>
    <?php if ($submitted): ?>
    <a href="<?= buildUrl(['sort'=>'name']) ?>" class="btn btn-sort">A→Z</a>
    <a href="<?= buildUrl(['sort'=>'desc']) ?>" class="btn btn-sort">1er→Dernier</a>
    <a href="<?= buildUrl(['sort'=>'asc'])  ?>" class="btn btn-sort">Dernier→1er</a>
    <?php endif; ?>
  </form>
</div>

<!-- ═══ BLOC DEBUG ═══ -->
<div class="box">
  <h2>📋 Variables reçues</h2>
  <table>
    <tr><th>Variable</th><th>Valeur</th><th>Statut</th></tr>
    <tr><td>cycle</td>      <td class="val"><?= htmlspecialchars($cycle ?: '(vide)') ?></td>      <td><?= $cycle      ? '<span class="ok">✅ OK</span>' : '<span class="err">❌ MANQUANT</span>' ?></td></tr>
    <tr><td>classeId</td>   <td class="val"><?= htmlspecialchars($classeId ?: '(vide)') ?></td>   <td><?= $classeId   ? '<span class="ok">✅ OK</span>' : '<span class="warn">⚠️ Pas encore sélectionné</span>' ?></td></tr>
    <tr><td>classeName</td> <td class="val"><?= htmlspecialchars($classeName ?: '(vide)') ?></td> <td></td></tr>
    <tr><td>semId</td>      <td class="val"><?= htmlspecialchars($semId ?: '(vide)') ?></td>      <td><?= $semId      ? '<span class="ok">✅ OK</span>' : '<span class="warn">⚠️ Pas encore sélectionné</span>' ?></td></tr>
    <tr><td>semName</td>    <td class="val"><?= htmlspecialchars($semName ?: '(vide)') ?></td>    <td></td></tr>
    <tr><td>sortMode</td>   <td class="val"><?= htmlspecialchars($sortMode) ?></td>               <td></td></tr>
    <tr><td>submitted</td>  <td class="val"><?= $submitted ? 'true' : 'false' ?></td>             <td><?= $submitted ? '<span class="ok">✅ Formulaire reçu</span>' : '<span class="warn">⚠️ Formulaire non soumis</span>' ?></td></tr>
    <tr><td>URL complète</td><td class="val" colspan="2"><?= htmlspecialchars($_SERVER['REQUEST_URI']) ?></td></tr>
  </table>
</div>

<?php if ($submitted): ?>

<!-- ═══ TEST 1 : Matières ═══ -->
<div class="box">
  <h2>📚 TEST 1 — Matières (gradesSubjects) pour classID=<?= htmlspecialchars($classeId) ?></h2>
  <?php
  $st = $link->prepare(
      "SELECT gs.coefficient, ss.id AS ssid, ss.name AS ssname,
              s.id AS sid, s.name AS sname
       FROM gradesSubjects gs
       INNER JOIN subSubjects ss ON gs.subSubjectID = ss.id
       INNER JOIN subjects    s  ON ss.subjectID    = s.id
       WHERE gs.classID = ?
       ORDER BY s.ordering, gs.ordering"
  );
  $st->bind_param("s", $classeId);
  $st->execute();
  $rc = $st->get_result();
  $nb = $rc->num_rows;
  echo $nb > 0
      ? "<p class='ok'>✅ $nb sous-matière(s) trouvée(s)</p>"
      : "<p class='err'>❌ 0 matières — Vérifiez que classID=$classeId existe dans gradesSubjects</p>";
  if ($nb > 0): ?>
  <table>
    <tr><th>ssid</th><th>Sous-matière</th><th>sid</th><th>Matière</th><th>Coef</th></tr>
    <?php while ($r = $rc->fetch_assoc()): ?>
    <tr>
      <td><?= $r['ssid'] ?></td>
      <td><?= htmlspecialchars($r['ssname']) ?></td>
      <td><?= $r['sid'] ?></td>
      <td><?= htmlspecialchars($r['sname']) ?></td>
      <td><?= $r['coefficient'] ?></td>
    </tr>
    <?php endwhile; ?>
  </table>
  <?php endif; ?>
</div>

<!-- ═══ TEST 2 : Élèves ═══ -->
<div class="box">
  <h2>👨‍🎓 TEST 2 — Élèves dans classID=<?= htmlspecialchars($classeId) ?></h2>
  <?php
  $st = $link->prepare("SELECT id, firstName, lastName, userID FROM students WHERE classID=? ORDER BY firstName, lastName");
  $st->bind_param("s", $classeId);
  $st->execute();
  $rc = $st->get_result();
  $nb = $rc->num_rows;
  echo $nb > 0
      ? "<p class='ok'>✅ $nb élève(s) dans cette classe</p>"
      : "<p class='err'>❌ 0 élève — La table students ne contient rien pour classID=$classeId</p>";
  if ($nb > 0): ?>
  <table>
    <tr><th>id</th><th>firstName</th><th>lastName</th><th>userID</th></tr>
    <?php while ($r = $rc->fetch_assoc()): ?>
    <tr><td><?=$r['id']?></td><td><?=htmlspecialchars($r['firstName'])?></td><td><?=htmlspecialchars($r['lastName'])?></td><td><?=$r['userID']?></td></tr>
    <?php endwhile; ?>
  </table>
  <?php endif; ?>
</div>

<!-- ═══ TEST 3 : Semestres disponibles ═══ -->
<div class="box">
  <h2>📅 TEST 3 — Semestres disponibles pour classID=<?= htmlspecialchars($classeId) ?></h2>
  <?php
  $st = $link->prepare(
      "SELECT DISTINCT s.id, s.name FROM semesters s
       JOIN classesSemesters cs ON s.id = cs.semesterID
       WHERE cs.classId = ?
       ORDER BY s.id"
  );
  $st->bind_param("s", $classeId);
  $st->execute();
  $rc = $st->get_result();
  $nb = $rc->num_rows;
  echo $nb > 0
      ? "<p class='ok'>✅ $nb semestre(s) liés à cette classe</p>"
      : "<p class='err'>❌ 0 semestre dans classesSemesters pour classID=$classeId — C'est probablement le problème !</p>";
  if ($nb > 0): ?>
  <table>
    <tr><th>id</th><th>Nom</th><th>Sélectionné ?</th></tr>
    <?php while ($r = $rc->fetch_assoc()): ?>
    <tr>
      <td><?= $r['id'] ?></td>
      <td><?= htmlspecialchars($r['name']) ?></td>
      <td><?= ($r['id'] == $semId) ? "<span class='ok'>✅ OUI (semId=$semId)</span>" : "<span class='warn'>Non (semId=$semId)</span>" ?></td>
    </tr>
    <?php endwhile; ?>
  </table>
  <?php endif; ?>
</div>

<!-- ═══ TEST 4 : Notes pour cette classe + semestre ═══ -->
<div class="box">
  <h2>📝 TEST 4 — Notes pour classID=<?= htmlspecialchars($classeId) ?> + semesterID=<?= htmlspecialchars($semId) ?></h2>
  <?php
  $st = $link->prepare(
      "SELECT COUNT(*) AS nb FROM grades g
       JOIN students st ON st.id = g.studentID
       WHERE st.classID = ? AND g.semesterID = ?"
  );
  $st->bind_param("ss", $classeId, $semId);
  $st->execute();
  $r = $st->get_result()->fetch_assoc();
  $nb = $r['nb'];
  echo $nb > 0
      ? "<p class='ok'>✅ $nb note(s) trouvée(s) pour cette combinaison classe+semestre</p>"
      : "<p class='err'>❌ 0 note — Aucune note dans la table grades pour classID=$classeId ET semesterID=$semId</p>";
  ?>

  <h3 style="margin-top:12px;">Vérification classesSemesters :</h3>
  <?php
  $st = $link->prepare("SELECT * FROM classesSemesters WHERE classId = ? AND semesterID = ?");
  $st->bind_param("ss", $classeId, $semId);
  $st->execute();
  $rc = $st->get_result();
  $nb2 = $rc->num_rows;
  echo $nb2 > 0
      ? "<p class='ok'>✅ Lien trouvé dans classesSemesters</p>"
      : "<p class='err'>❌ Aucun lien dans classesSemesters pour classId=$classeId + semesterID=$semId — C'est probablement la cause !</p>";
  ?>

  <h3 style="margin-top:12px;">Aperçu des notes (max 10) :</h3>
  <?php
  $st = $link->prepare(
      "SELECT st.id, st.firstName, st.lastName, g.subSubjectID, g.grade, g.semesterID
       FROM grades g
       JOIN students st ON st.id = g.studentID
       WHERE st.classID = ? AND g.semesterID = ?
       LIMIT 10"
  );
  $st->bind_param("ss", $classeId, $semId);
  $st->execute();
  $rc = $st->get_result();
  if ($rc->num_rows > 0): ?>
  <table>
    <tr><th>studentID</th><th>Prénom</th><th>Nom</th><th>subSubjectID</th><th>grade</th><th>semesterID</th></tr>
    <?php while ($r = $rc->fetch_assoc()): ?>
    <tr><td><?=$r['id']?></td><td><?=htmlspecialchars($r['firstName'])?></td><td><?=htmlspecialchars($r['lastName'])?></td><td><?=$r['subSubjectID']?></td><td><?=$r['grade']?></td><td><?=$r['semesterID']?></td></tr>
    <?php endwhile; ?>
  </table>
  <?php else: echo "<p class='warn'>Aucune donnée.</p>"; endif; ?>
</div>

<!-- ═══ TEST 5 : Requête principale complète ═══ -->
<div class="box">
  <h2>🔗 TEST 5 — Requête principale (JOIN complet)</h2>
  <?php
  $st = $link->prepare(
      "SELECT st.id AS stid, st.firstName, st.lastName,
              g.subSubjectID, g.grade, g.semesterID,
              cs.semesterID AS cs_semID, se.id AS se_id, se.name AS se_name
       FROM classes cl
       LEFT JOIN students        st ON st.classID   = cl.id
       LEFT JOIN grades           g ON g.studentID  = st.id AND g.classID = cl.id
       LEFT JOIN classesSemesters cs ON cs.classId  = cl.id
       LEFT JOIN semesters        se ON se.id        = cs.semesterID AND se.id = g.semesterID
       WHERE cl.id = ? AND se.id = ?
       LIMIT 10"
  );
  $st->bind_param("ss", $classeId, $semId);
  $st->execute();
  $rc = $st->get_result();
  $nb = $rc->num_rows;
  echo $nb > 0
      ? "<p class='ok'>✅ $nb ligne(s) — La requête principale fonctionne !</p>"
      : "<p class='err'>❌ 0 résultat — Le JOIN complet ne trouve rien. Voir tests 3 et 4 pour la cause.</p>";
  if ($nb > 0): ?>
  <table>
    <tr><th>stid</th><th>firstName</th><th>lastName</th><th>subSubjectID</th><th>grade</th><th>semesterID</th><th>cs_semID</th><th>se_id</th><th>se_name</th></tr>
    <?php $rc2 = clone $rc; while ($r = $rc->fetch_assoc()): ?>
    <tr>
      <td><?=$r['stid']?></td><td><?=htmlspecialchars($r['firstName'])?></td>
      <td><?=htmlspecialchars($r['lastName'])?></td><td><?=$r['subSubjectID']?></td>
      <td><?=$r['grade']?></td><td><?=$r['semesterID']?></td>
      <td><?=$r['cs_semID']?></td><td><?=$r['se_id']?></td>
      <td><?=htmlspecialchars($r['se_name']??'')?></td>
    </tr>
    <?php endwhile; ?>
  </table>
  <?php endif; ?>
</div>

<?php endif; // submitted

mysqli_close($link); ?>

</body>
</html>
