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

/* ══════════════════════════════════════════════════════════════
   _relever1.php  –  Relevé des Notes (version améliorée)
   • Tri par ordre croissant (A→Z) ou décroissant (moy ↓ → ↑)
   • Rang avec médailles
   • Notes en rouge si < 10
   • Export Excel
   • Sécurité : prepared statements
   ══════════════════════════════════════════════════════════════ */

$cycle    = $_REQUEST['cycle'] ?? '';
$sortMode = $_GET['sort'] ?? 'name';   // 'name' | 'desc' | 'asc'

// ── Paramètres POST (soumission formulaire) ───────────────────
$classeId   = '';
$classeName = '';
$semId      = '';
$semName    = '';
$submitted  = false;

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

// ── Export Excel ──────────────────────────────────────────────
$export = isset($_GET['export']) && $_GET['export'] === 'xls' && $submitted;
if ($export) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="releve_' . date('Ymd_Hi') . '.xls"');
    header('Cache-Control: max-age=0');
}
?>
<!DOCTYPE html>
<html lang="fr">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Relevé des Notes</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">
<style>
/* ── RESET & BASE ── */
*{margin:0;padding:0;box-sizing:border-box;}
body{font-family:"Segoe UI",Arial,sans-serif;background:#f0f4ff;font-size:13px;color:#1f2937;}

/* ── TOPBAR ── */
.topbar{
  background:#1e3a8a;color:white;
  padding:12px 20px;
  display:flex;align-items:center;justify-content:space-between;
  box-shadow:0 2px 8px rgba(0,0,0,.25);
}
.topbar h1{font-size:18px;font-weight:700;display:flex;align-items:center;gap:10px;}
.topbar-actions{display:flex;gap:8px;}

/* ── FORM BAR ── */
.formbar{
  background:white;padding:14px 20px;
  display:flex;flex-wrap:wrap;gap:10px;align-items:flex-end;
  border-bottom:2px solid #e5e7eb;
  box-shadow:0 2px 6px rgba(0,0,0,.06);
}
.formbar label{font-size:11px;font-weight:700;color:#374151;display:block;margin-bottom:3px;text-transform:uppercase;letter-spacing:.5px;}
.formbar select{
  padding:8px 12px;border:1.5px solid #d1d5db;border-radius:8px;
  font-size:13px;min-width:160px;background:white;cursor:pointer;
  transition:border-color .2s;
}
.formbar select:focus{outline:none;border-color:#1e3a8a;box-shadow:0 0 0 3px rgba(30,58,138,.12);}

/* ── BOUTONS ── */
.btn{
  padding:9px 18px;border:none;border-radius:8px;cursor:pointer;
  font-size:13px;font-weight:600;transition:.2s;
  display:inline-flex;align-items:center;gap:6px;text-decoration:none;
  white-space:nowrap;
}
.btn-primary{background:#1e3a8a;color:white;}
.btn-primary:hover{background:#2563eb;}
.btn-success{background:#059669;color:white;}
.btn-success:hover{background:#047857;}
.btn-white{background:white;color:#1e3a8a;border:2px solid white;}
.btn-white:hover{background:#dbeafe;color:#1e3a8a;border-color:#dbeafe;}
.btn-sort{background:#7c3aed;color:white;}
.btn-sort:hover{background:#6d28d9;}
.btn-sort.active{background:#f59e0b;color:#1f2937;}

/* ── INFO ── */
.infobar{
  background:#eff6ff;border-left:4px solid #1e3a8a;
  padding:10px 18px;margin:12px 16px 0;border-radius:4px;
  font-size:13px;display:flex;flex-wrap:wrap;gap:16px;align-items:center;
}
.infobar strong{color:#1e3a8a;}
.badge{
  background:#1e3a8a;color:white;
  padding:3px 10px;border-radius:20px;font-size:11px;font-weight:700;
}
.badge-purple{background:#7c3aed;}
.badge-amber{background:#d97706;}

/* ── TABLE WRAPPER ── */
.table-wrap{overflow-x:auto;padding:12px 16px 24px;}

/* ── TABLE ── */
table{
  border-collapse:collapse;white-space:nowrap;
  background:white;
  box-shadow:0 4px 24px rgba(0,0,0,.10);
  border-radius:10px;overflow:hidden;
  font-size:12px;
}
th,td{border:1px solid #cbd5e1;padding:4px 7px;text-align:center;vertical-align:middle;}

/* Headers */
.th-main   {background:#1e3a8a;color:white;font-weight:700;}
.th-sub    {background:#1d4ed8;color:white;font-weight:600;}
.th-coef   {background:#2563eb;color:white;font-size:11px;}
.th-stotal {background:#0369a1;color:white;}
.th-final  {background:#0f172a;color:white;}

/* Rotation */
.th-rot{height:190px;vertical-align:bottom;padding:4px 3px;}
.rot-inner{
  display:inline-block;
  writing-mode:vertical-rl;
  transform:rotate(180deg);
  max-height:175px;overflow:hidden;
  font-size:11px;font-weight:600;
  text-overflow:ellipsis;
}

/* Data cells */
tbody tr:nth-child(even){background:#f8fafc;}
tbody tr:hover{background:#eff6ff;}
.td-name{text-align:left;padding-left:10px;font-weight:600;min-width:160px;}
.td-rank{font-weight:800;min-width:36px;}
.td-stot{background:#e0f2fe;font-weight:700;}
.td-smoy{background:#bae6fd;font-weight:700;}
.td-ftot{background:#dbeafe;font-weight:700;}
.td-fmoy{background:#bfdbfe;font-weight:800;font-size:13px;}
.note-bad{color:#dc2626;font-weight:800;}
.note-ok {color:#059669;}
.moy-bad {color:#dc2626;font-weight:800;font-size:13px;}
.moy-ok  {color:#059669;font-weight:800;font-size:13px;}

/* Rang médailles */
.r1{color:#d97706;}
.r2{color:#6b7280;}
.r3{color:#92400e;}
.rn{color:#374151;}

/* ── PRINT ── */
@media print{
  .noprint{display:none!important;}
  body{background:white;}
  .topbar{background:#1e3a8a!important;-webkit-print-color-adjust:exact;print-color-adjust:exact;}
  @page{size:A4 landscape;margin:6mm;}
  table{font-size:10px;}
  .rot-inner{font-size:9px;}
}
</style>
</head>
<body>

<!-- ══ TOPBAR ══ -->
<div class="topbar noprint">
  <h1><i class="fa fa-table-list"></i> Relevé des Notes</h1>
  <div class="topbar-actions">
    <button onclick="window.print()" class="btn btn-white">
      <i class="fa fa-print"></i> Imprimer
    </button>
    <?php if ($submitted): ?>
    <a href="?<?= http_build_query(['cycle'=>$cycle,'sort'=>$sortMode,'export'=>'xls']) ?>"
       onclick="this.closest('form')&&this.closest('form').submit()"
       class="btn btn-success">
      <i class="fa fa-file-excel"></i> Excel
    </a>
    <?php endif; ?>
  </div>
</div>

<!-- ══ FORM ══ -->
<div class="formbar noprint">
  <form method="post" action="_relever1.php?cycle=<?= urlencode($cycle) ?>&sort=<?= urlencode($sortMode) ?>"
        style="display:flex;flex-wrap:wrap;gap:10px;align-items:flex-end;">

    <input name="cycle" type="hidden" value="<?= htmlspecialchars($cycle) ?>">

    <?php
    /* Classes */
    $stmt = $link->prepare("SELECT id, name FROM classes WHERE cycleID=? ORDER BY id ASC");
    $stmt->bind_param("s", $cycle);
    $stmt->execute();
    $resC = $stmt->get_result();
    ?>
    <div>
      <label><i class="fa fa-users"></i> Classe</label>
      <select name="cla">
        <?php while ($r = $resC->fetch_assoc()):
          $n = strtoupper($r['name']); ?>
          <option value="<?= $r['id'].';'.$n ?>"
            <?= ($classeId==$r['id'])?'selected':'' ?>><?= htmlspecialchars($n) ?></option>
        <?php endwhile; ?>
      </select>
    </div>

    <?php
    /* Semestres */
    $resS = $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"
    );
    ?>
    <div>
      <label><i class="fa fa-calendar-check"></i> Contrôle / Semestre</label>
      <select name="ctr">
        <?php while ($r = $resS->fetch_assoc()): ?>
          <option value="<?= $r['id'].';'.htmlspecialchars($r['name']) ?>"
            <?= ($semId==$r['id'])?'selected':'' ?>><?= htmlspecialchars($r['name']) ?></option>
        <?php endwhile; ?>
      </select>
    </div>

    <button type="submit" name="chercher" value="1" class="btn btn-primary">
      <i class="fa fa-magnifying-glass"></i> Afficher
    </button>

    <?php if ($submitted): ?>
    <!-- Boutons de tri -->
    <?php
    $baseQ = ['cycle'=>$cycle,'sort'=>''];
    $qName = http_build_query(array_merge($baseQ,['sort'=>'name']));
    $qAsc  = http_build_query(array_merge($baseQ,['sort'=>'asc']));
    $qDesc = http_build_query(array_merge($baseQ,['sort'=>'desc']));
    ?>
    <a href="?<?=$qName?>" class="btn btn-sort <?=$sortMode==='name'?'active':''?>">
      <i class="fa fa-arrow-down-a-z"></i> A → Z
    </a>
    <a href="?<?=$qAsc?>" class="btn btn-sort <?=$sortMode==='asc'?'active':''?>">
      <i class="fa fa-arrow-up-1-9"></i> Moy ↑
    </a>
    <a href="?<?=$qDesc?>" class="btn btn-sort <?=$sortMode==='desc'?'active':''?>">
      <i class="fa fa-arrow-down-9-1"></i> Moy ↓
    </a>
    <?php endif; ?>
  </form>
</div>

<?php if ($submitted && $classeId && $semId): ?>
<?php
/* ══════════════════════════════════════════════════════════════
   ÉTAPE 1 : Colonnes (sous-matières) pour cette classe
   ══════════════════════════════════════════════════════════════ */
$stmt = $link->prepare(
  "SELECT gs.coefficient, ss.id AS ssid, ss.name AS ssname,
          s.id AS sid, s.name AS sname, s.ordering
   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"
);
$stmt->bind_param("s", $classeId);
$stmt->execute();
$resCols = $stmt->get_result();

$columns  = [];   // [{ssid, ssname, sid, sname, coef}]
$subjects = [];   // [sid => {name, cols:[ssid]}]

while ($r = $resCols->fetch_assoc()) {
  $columns[] = ['ssid'=>$r['ssid'],'ssname'=>$r['ssname'],
                'sid'=>$r['sid'],'sname'=>$r['sname'],'coef'=>(float)$r['coefficient']];
  if (!isset($subjects[$r['sid']])) $subjects[$r['sid']] = ['name'=>$r['sname'],'cols'=>[]];
  $subjects[$r['sid']]['cols'][] = $r['ssid'];
}

if (empty($columns)) {
  echo "<div class='infobar'><span style='color:#dc2626;font-weight:700;'>
        <i class='fa fa-triangle-exclamation'></i>
        Aucune matière trouvée pour cette classe.</span></div>";
} else:

/* ══════════════════════════════════════════════════════════════
   ÉTAPE 2 : Notes de tous les élèves
   ══════════════════════════════════════════════════════════════ */
$stmt = $link->prepare(
  "SELECT st.id AS stid,
          COALESCE(NULLIF(TRIM(st.firstName),''), u.firstName, '?') AS prenom,
          COALESCE(NULLIF(TRIM(st.lastName),''),  u.lastName,  '')  AS famille,
          g.subSubjectID AS ssid,
          g.grade
   FROM   classes cl
   LEFT JOIN students        st ON st.classID  = cl.id
   LEFT JOIN users            u ON u.id        = st.userID
   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 = ?
   ORDER BY st.firstName, st.lastName, u.firstName, g.subSubjectID"
);
$stmt->bind_param("ss", $classeId, $semId);
$stmt->execute();
$resG = $stmt->get_result();

$stMap  = [];  // [stid => {name, grades:[ssid=>grade]}]
$stOrder= [];  // ordre de découverte

while ($r = $resG->fetch_assoc()) {
  $stid = $r['stid'];
  if (!$stid) continue;
  if (!isset($stMap[$stid])) {
    $stMap[$stid]  = ['name' => trim($r['prenom'].' '.$r['famille']), 'grades'=>[]];
    $stOrder[]     = $stid;
  }
  if ($r['ssid'] !== null) $stMap[$stid]['grades'][$r['ssid']] = $r['grade'];
}

/* ══════════════════════════════════════════════════════════════
   ÉTAPE 3 : Calcul des moyennes & totaux
   ══════════════════════════════════════════════════════════════ */
$rows = [];

foreach ($stOrder as $stid) {
  $st     = $stMap[$stid];
  $grades = $st['grades'];

  $subjTotals = [];   // [sid => {wsum, coef}]
  $totalWsum  = 0;
  $totalCoef  = 0;

  foreach ($columns as $col) {
    $ssid = $col['ssid'];
    $sid  = $col['sid'];
    $coef = $col['coef'];
    $note = $grades[$ssid] ?? null;

    if (!isset($subjTotals[$sid])) $subjTotals[$sid] = ['wsum'=>0,'coef'=>0];

    if ($note !== null && $note != -1) {
      $subjTotals[$sid]['wsum'] += $note * $coef;
      $subjTotals[$sid]['coef'] += $coef;
      $totalWsum                += $note * $coef;
      $totalCoef                += $coef;
    }
  }

  $moyFinal = $totalCoef > 0 ? round($totalWsum / $totalCoef, 2) : 0;

  $rows[] = [
    'stid'       => $stid,
    'name'       => $st['name'],
    'grades'     => $grades,
    'subjTotals' => $subjTotals,
    'totalWsum'  => $totalWsum,
    'totalCoef'  => $totalCoef,
    'moyFinal'   => $moyFinal,
  ];
}

/* ══════════════════════════════════════════════════════════════
   ÉTAPE 4 : Tri
   ══════════════════════════════════════════════════════════════ */
switch ($sortMode) {
  case 'desc':
    usort($rows, fn($a,$b) => $b['moyFinal'] <=> $a['moyFinal']);
    break;
  case 'asc':
    usort($rows, fn($a,$b) => $a['moyFinal'] <=> $b['moyFinal']);
    break;
  default: // 'name' → ordre alphabétique (déjà trié par la SQL)
    break;
}

/* ══════════════════════════════════════════════════════════════
   ÉTAPE 5 : Affichage
   ══════════════════════════════════════════════════════════════ */
$sortLabel = match($sortMode) {
  'desc' => '📊 Tri : Meilleure moyenne en premier',
  'asc'  => '📊 Tri : Moins bonne moyenne en premier',
  default=> '📊 Tri : Ordre alphabétique',
};
?>

<!-- ── Info bar ── -->
<div class="infobar noprint">
  <span><strong>Classe :</strong> <?= htmlspecialchars($classeName) ?></span>
  <span><strong>Contrôle :</strong> <?= htmlspecialchars($semName) ?></span>
  <span><strong>Date :</strong> <?= date('d/m/Y') ?></span>
  <span class="badge"><?= count($rows) ?> élèves</span>
  <span class="badge badge-<?= $sortMode==='name'?'amber':'purple' ?>"><?= $sortLabel ?></span>
</div>

<!-- ── Tableau ── -->
<div class="table-wrap">
<table id="tbl_export">
<thead>

  <!-- LIGNE 1 : Matières (groupées) -->
  <tr>
    <th class="th-main" rowspan="3" style="min-width:34px;">#</th>
    <th class="th-main" rowspan="3" style="min-width:170px;">Prénom &amp; Nom</th>
    <?php foreach ($subjects as $sid => $subj):
      $nbCols = count($subj['cols']); ?>
      <th class="th-main" colspan="<?= $nbCols + 2 ?>">
        <?= htmlspecialchars($subj['name']) ?>
      </th>
    <?php endforeach; ?>
    <th class="th-final" colspan="3">Résultat Final</th>
  </tr>

  <!-- LIGNE 2 : Coefficients -->
  <tr>
    <?php foreach ($subjects as $sid => $subj):
      foreach ($subj['cols'] as $ssid):
        $coef = 0;
        foreach ($columns as $c) { if ($c['ssid']==$ssid){$coef=$c['coef'];break;} }
      ?>
        <th class="th-coef"><?= $coef ?></th>
      <?php endforeach; ?>
      <th class="th-stotal" colspan="2" style="font-size:10px;">Σ&nbsp;/&nbsp;20</th>
    <?php endforeach; ?>
    <th class="th-final" style="font-size:10px;">Σ Pts</th>
    <th class="th-final" style="font-size:10px;">Coef</th>
    <th class="th-final" style="font-size:10px;">Moy</th>
  </tr>

  <!-- LIGNE 3 : Noms sous-matières (pivotés) -->
  <tr>
    <?php foreach ($subjects as $sid => $subj):
      foreach ($subj['cols'] as $ssid):
        $ssname = '';
        foreach ($columns as $c){if($c['ssid']==$ssid){$ssname=$c['ssname'];break;}}
      ?>
        <th class="th-sub th-rot">
          <span class="rot-inner"><?= htmlspecialchars($ssname) ?></span>
        </th>
      <?php endforeach; ?>
      <th class="th-stotal th-rot"><span class="rot-inner">Total <?= htmlspecialchars($subj['name']) ?></span></th>
      <th class="th-stotal th-rot"><span class="rot-inner">Moy /20 <?= htmlspecialchars($subj['name']) ?></span></th>
    <?php endforeach; ?>
    <th class="th-final th-rot"><span class="rot-inner">Total Final</span></th>
    <th class="th-final th-rot"><span class="rot-inner">Coef Total</span></th>
    <th class="th-final th-rot"><span class="rot-inner">Moyenne Finale</span></th>
  </tr>

</thead>
<tbody>
<?php
$rang = 0;
$prevMoy = null;
$sameRang = 0;

foreach ($rows as $idx => $st):
  // Calcul du rang (gestion ex-aequo pour tri moy)
  if ($sortMode !== 'name') {
    if ($st['moyFinal'] !== $prevMoy) {
      $rang    = $idx + 1;
      $prevMoy = $st['moyFinal'];
    }
    $sameRang = $rang;
  } else {
    $sameRang = $idx + 1;
  }

  // Icône rang
  $rangHtml = match($sameRang) {
    1 => "<span class='r1'>🥇</span>",
    2 => "<span class='r2'>🥈</span>",
    3 => "<span class='r3'>🥉</span>",
    default => "<span class='rn'>".$sameRang."</span>"
  };

  // Couleur moyenne finale
  $moyClass = $st['moyFinal'] >= 10 ? 'moy-ok' : 'moy-bad';
?>
<tr>
  <td class="td-rank"><?= $rangHtml ?></td>
  <td class="td-name"><?= htmlspecialchars($st['name']) ?></td>

  <?php foreach ($subjects as $sid => $subj):
    foreach ($subj['cols'] as $ssid):
      $note    = $st['grades'][$ssid] ?? null;
      $display = ($note !== null && $note != -1) ? $note : '–';
      $nc      = ($note !== null && $note != -1 && $note < 10) ? 'note-bad' : (($note >= 10) ? 'note-ok' : '');
    ?>
      <td class="<?= $nc ?>"><?= $display ?></td>
    <?php endforeach;

    $scoef = $st['subjTotals'][$sid]['coef'] ?? 0;
    $swsum = $st['subjTotals'][$sid]['wsum'] ?? 0;
    $smoy  = $scoef > 0 ? round($swsum / $scoef, 2) : '–';
    $smoyC = (is_numeric($smoy) && $smoy < 10) ? 'note-bad' : 'note-ok';
  ?>
    <td class="td-stot"><?= round($swsum, 2) ?></td>
    <td class="td-smoy <?= $smoyC ?>"><?= $smoy ?></td>
  <?php endforeach; ?>

  <td class="td-ftot"><?= round($st['totalWsum'], 2) ?></td>
  <td class="td-ftot"><?= $st['totalCoef'] ?></td>
  <td class="td-fmoy <?= $moyClass ?>"><?= $st['moyFinal'] ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>

<?php endif; // end if columns ?>
<?php endif; // end if submitted ?>

<?php mysqli_close($link); ?>
</body>
</html>
