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

$cycle    = $_GET['cycle'] ?? $_POST['cycle'] ?? $_SESSION['cycle'] ?? '';
if ($cycle) $_SESSION['cycle'] = $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 !== '');
}

function buildUrl(array $extra = []): string {
    global $cycle, $classeId, $classeName, $semId, $semName, $sortMode;
    $params = array_merge([
        'cycle'=>$cycle,'classeId'=>$classeId,'classeName'=>$classeName,
        'semId'=>$semId,'semName'=>$semName,'sort'=>$sortMode,
    ], $extra);
    return '?' . http_build_query($params);
}

if (isset($_GET['export']) && $_GET['export']==='xls' && $submitted) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="releve_'.date('Ymd').'.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>
*{margin:0;padding:0;box-sizing:border-box;}
body{font-family:"Segoe UI",Arial,sans-serif;background:#f0f4ff;font-size:13px;color:#1f2937;}
.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,.3);}
.topbar h1{font-size:17px;font-weight:700;display:flex;align-items:center;gap:8px;}
.topbar-right{display:flex;gap:8px;align-items:center;}
.formbar{background:white;padding:12px 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;}
.formbar select{padding:8px 10px;border:1.5px solid #d1d5db;border-radius:8px;font-size:13px;min-width:150px;background:white;cursor:pointer;}
.btn{padding:8px 16px;border:none;border-radius:8px;cursor:pointer;font-size:12px;font-weight:600;display:inline-flex;align-items:center;gap:5px;text-decoration:none;white-space:nowrap;transition:.15s;}
.btn-primary{background:#1e3a8a;color:white;}.btn-primary:hover{background:#2563eb;}
.btn-green{background:#059669;color:white;}.btn-green:hover{background:#047857;}
.btn-white{background:rgba(255,255,255,.15);color:white;border:1px solid rgba(255,255,255,.4);}.btn-white:hover{background:rgba(255,255,255,.25);}
.btn-sort{background:#6d28d9;color:white;}.btn-sort:hover{background:#5b21b6;}.btn-sort.on{background:#d97706;}
.infobar{background:#eff6ff;border-left:4px solid #1e3a8a;padding:9px 16px;margin:10px 14px 0;border-radius:4px;font-size:12px;display:flex;flex-wrap:wrap;gap:14px;align-items:center;}
.infobar b{color:#1e3a8a;}
.pill{display:inline-block;padding:2px 10px;border-radius:20px;font-size:11px;font-weight:700;color:white;}
.pill-blue{background:#1e3a8a;}.pill-purple{background:#6d28d9;}.pill-amber{background:#d97706;}
.twrap{overflow-x:auto;padding:10px 14px 22px;overflow-y:visible;}
table{border-collapse:collapse;white-space:nowrap;background:white;box-shadow:0 4px 20px rgba(0,0,0,.09);font-size:12px;margin:0 auto;}
th,td{border:1px solid #cbd5e1;padding:4px 6px;text-align:center;vertical-align:middle;}
.th-main{background:#1e3a8a;color:white;font-weight:700;}
.th-sub{background:#1d4ed8;color:white;min-width:22px;overflow:visible !important;}
.th-coef{background:#3b82f6;color:white;font-size:10px;}
.th-stot{background:#075985;color:white;}
.th-fin{background:#0f172a;color:white;}
.rot{height:200px;vertical-align:bottom;padding:3px 2px;word-wrap:break-word;}
.rot span{display:inline-block;writing-mode:vertical-rl;transform:rotate(180deg);max-height:190px;min-width:18px;overflow:visible;font-size:10px;font-weight:600;white-space:normal;}
tbody tr:nth-child(even){background:#f8fafc;}
tbody tr:hover{background:#eff6ff;}
.c-name{text-align:left;padding-left:8px;font-weight:600;min-width:155px;}
.c-rank{font-weight:800;min-width:32px;font-size:13px;}
.c-stot{background:#e0f2fe;font-weight:700;}
.c-smoy{background:#bae6fd;font-weight:700;}
.c-ftot{background:#dbeafe;font-weight:700;}
.c-fmoy{background:#bfdbfe;font-weight:800;font-size:13px;}
.bad{color:#dc2626;font-weight:800;}.ok{color:#059669;}
.th-abs{background:#7c3aed;color:white;}
.c-abs{background:#ede9fe;font-weight:700;color:#5b21b6;}
.c-abs-hi{background:#fce7f3;font-weight:800;color:#9d174d;}
.pill-green{background:#059669;}
.tr-moy-cls td{background:#0f172a;color:white;font-weight:700;text-align:center;border-top:3px solid #7c3aed;}
.tr-moy-cls td.lbl{text-align:left;padding-left:10px;background:#1e3a8a;font-size:11px;letter-spacing:.04em;}
.r1{color:#d97706;font-size:15px;}.r2{color:#9ca3af;font-size:15px;}.r3{color:#92400e;font-size:15px;}
.alert{background:#fef2f2;border-left:4px solid #dc2626;padding:12px 16px;margin:14px;border-radius:4px;color:#991b1b;font-weight:600;}
@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:5mm;}
  table{font-size:9px;}.rot span{font-size:8px;}
}
</style>
</head>
<body>

<div class="topbar noprint">
  <h1><i class="fa fa-table"></i> Relevé des Notes
    <?php if($classeName): ?><span style="font-weight:400;font-size:14px;opacity:.85;">— <?=htmlspecialchars($classeName)?></span><?php endif; ?>
  </h1>
  <div class="topbar-right">
    <button onclick="window.print()" class="btn btn-white"><i class="fa fa-print"></i> Imprimer</button>
    <?php if($submitted): ?>
    <a href="<?=buildUrl(['export'=>'xls'])?>" class="btn btn-green"><i class="fa fa-file-excel"></i> Excel</a>
    <?php endif; ?>
  </div>
</div>

<div class="formbar noprint">
  <form method="post" action="<?=htmlspecialchars($_SERVER['PHP_SELF'])?>?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><i class="fa fa-users"></i> 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><i class="fa fa-calendar-check"></i> Contrôle / Semestre</label>
      <select name="ctr">
        <?php
        $st_sem=$link->prepare("SELECT DISTINCT s.id,s.name FROM semesters s JOIN classesSemesters cs ON s.id=cs.semesterID JOIN classes cl ON cl.id=cs.classID JOIN grades g ON g.semesterID=s.id AND g.classID=cl.id WHERE cl.cycleID=? ORDER BY s.id ASC");
        $st_sem->bind_param("s",$cycle);$st_sem->execute();$rs=$st_sem->get_result();
        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 btn-primary"><i class="fa fa-search"></i> Afficher</button>
    <?php if($submitted): ?>
    <span style="margin-left:6px;color:#6b7280;font-size:11px;align-self:center;">Trier :</span>
    <a href="<?=buildUrl(['sort'=>'name'])?>" class="btn btn-sort <?=$sortMode==='name'?'on':''?>"><i class="fa fa-arrow-down-a-z"></i> A&#8594;Z</a>
    <a href="<?=buildUrl(['sort'=>'desc'])?>" class="btn btn-sort <?=$sortMode==='desc'?'on':''?>"><i class="fa fa-arrow-down-9-1"></i> 1er&#8594;Dernier</a>
    <a href="<?=buildUrl(['sort'=>'asc'])?>" class="btn btn-sort <?=$sortMode==='asc'?'on':''?>"><i class="fa fa-arrow-up-1-9"></i> Dernier&#8594;1er</a>
    <?php endif; ?>
  </form>
</div>

<?php
if(!$submitted){
  echo '<div class="infobar" style="margin-top:20px;"><i class="fa fa-circle-info" style="color:#1e3a8a;"></i>&nbsp;Sélectionnez une classe et un contrôle, puis cliquez <strong>Afficher</strong>.</div>';
} elseif(!$cycle){
  echo '<div class="alert"><i class="fa fa-triangle-exclamation"></i> Paramètre <code>cycle</code> manquant dans l\'URL.</div>';
} else {

// ÉTAPE 1: Colonnes
$st=$link->prepare("SELECT gs.coeficient,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");
$st->bind_param("s",$classeId);$st->execute();$rc=$st->get_result();
$columns=[];$subjects=[];
while($r=$rc->fetch_assoc()){
  $columns[]=['ssid'=>$r['ssid'],'ssname'=>$r['ssname'],'sid'=>$r['sid'],'sname'=>$r['sname'],'coef'=>(float)$r['coeficient']];
  if(!isset($subjects[$r['sid']])) $subjects[$r['sid']]=['name'=>$r['sname'],'cols'=>[]];
  $subjects[$r['sid']]['cols'][]=$r['ssid'];
}

// Build a quick lookup: ssid => coef (used for coloring)
$coefBySsid = [];
foreach($columns as $c){ $coefBySsid[$c['ssid']] = $c['coef']; }

if(empty($columns)){
  echo '<div class="alert"><i class="fa fa-triangle-exclamation"></i> Aucune matière trouvée pour classID='.htmlspecialchars($classeId).'</div>';
} else {

// ÉTAPE 2: Notes
$st=$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 AND g.semesterID=? WHERE cl.id=? ORDER BY st.firstName,st.lastName,u.firstName,g.subSubjectID");
$st->bind_param("ss",$semId,$classeId);$st->execute();$rg=$st->get_result();
$stMap=[];$stOrder=[];
while($r=$rg->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'];
}

if(empty($stOrder)){
  echo '<div class="alert"><i class="fa fa-triangle-exclamation"></i> Aucun élève trouvé. ClasseID='.htmlspecialchars($classeId).' | SemestreID='.htmlspecialchars($semId).'</div>';
} else {

// ÉTAPE 3: Calcul
$rows=[];
foreach($stOrder as $stid){
  $grades=$stMap[$stid]['grades'];
  $subjTotals=[];
  $globalCoefficient=0;
  $totalPtsSum=0;

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

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

    if($note!==null && $note!=-1){
      $subjTotals[$sid]['sum']+=$note;
      $subjTotals[$sid]['coef']+=$coef;
      $globalCoefficient+=$coef;
      $totalPtsSum+=$note;
    }
  }

  $moyFinal = $globalCoefficient > 0 ? round(($totalPtsSum*20)/$globalCoefficient, 2) : 0;

  $rows[]=[
    'stid'=>$stid,
    'name'=>$stMap[$stid]['name'],
    'grades'=>$grades,
    'subjTotals'=>$subjTotals,
    'totalPtsSum'=>$totalPtsSum,
    'globalCoef'=>$globalCoefficient,
    '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;
}

$sortLabel=['name'=>'A&#8594;Z','desc'=>'1er &#8594; Dernier','asc'=>'Dernier &#8594; 1er'];

// ÉTAPE 5: Absences par élève pour ce contrôle
$absMap = [];
$st_abs = $link->prepare("SELECT studentID, SUM(`count`) AS total_abs FROM absenceclasse WHERE classID=? AND semesterID=? GROUP BY studentID");
$st_abs->bind_param("ss", $classeId, $semId);
$st_abs->execute();
$ra = $st_abs->get_result();
while($r = $ra->fetch_assoc()){
  $absMap[(int)$r['studentID']] = (int)$r['total_abs'];
}

// ÉTAPE 6: Moyenne de la classe (élèves avec notes seulement)
$rowsAvec   = array_filter($rows, fn($r) => $r['globalCoef'] > 0 && $r['totalPtsSum'] > 0);
$nbEleves   = count($rowsAvec);
$moyClasse  = $nbEleves > 0 ? round(array_sum(array_column($rowsAvec,'moyFinal')) / $nbEleves, 2) : 0;
$mcCls      = $moyClasse >= 10 ? 'ok' : 'bad';

// Per-subject sums for footer (élèves avec notes seulement)
$clsSubjSum  = [];
$clsSubjCoef = [];
foreach($rowsAvec as $r2){
  foreach($subjects as $sid=>$subj){
    $clsSubjSum[$sid]  = ($clsSubjSum[$sid]  ?? 0) + ($r2['subjTotals'][$sid]['sum']  ?? 0);
    $clsSubjCoef[$sid] = ($clsSubjCoef[$sid] ?? 0) + ($r2['subjTotals'][$sid]['coef'] ?? 0);
  }
}
$clsTotalPts  = array_sum(array_column($rowsAvec,'totalPtsSum'));
$clsTotalCoef = array_sum(array_column($rowsAvec,'globalCoef'));
?>
<div class="infobar noprint">
  <span><b>Classe :</b> <?=htmlspecialchars($classeName)?></span>
  <span><b>Contrôle :</b> <?=htmlspecialchars($semName)?></span>
  <span><b>Date :</b> <?=date('d/m/Y')?></span>
  <span class="pill pill-blue"><?=count($rows)?> élèves</span>
  <span class="pill <?=$sortMode==='name'?'pill-amber':'pill-purple'?>">&#128202; <?=$sortLabel[$sortMode]??''?></span>
  <span><b>Moy. classe :</b> <span class="pill <?=$moyClasse>=10?'pill-green':'pill-amber'?>"><?=$moyClasse?>/20</span><?php if(count($rows)!=$nbEleves): ?> <span style="font-size:10px;color:#6b7280;">(sur <?=$nbEleves?> notés)</span><?php endif; ?></span>
</div>
<div class="twrap">
<table id="tbl_export">
<thead>
<tr>
  <th class="th-main" rowspan="3">#</th>
  <th class="th-main" rowspan="3" style="min-width:160px;">Prénom &amp; Nom</th>
  <?php foreach($subjects as $sid=>$subj): ?>
    <th class="th-main" colspan="<?=count($subj['cols'])+2?>"><?=htmlspecialchars($subj['name'])?></th>
  <?php endforeach; ?>
  <th class="th-fin" colspan="3">Résultat Final</th>
  <th class="th-abs" rowspan="3" style="min-width:46px;">Abs.<br><span style="font-size:9px;font-weight:400;opacity:.8;">(jours)</span></th>
</tr>
<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;}} echo '<th class="th-coef">'.$coef.'</th>';}
   $subjMaxCoef = 0;
    foreach($columns as $c){ if($c['sid']==$sid) $subjMaxCoef += $c['coef']; }
    echo '<th class="th-stot" colspan="2" style="font-size:10px;">&#931; '.$subjMaxCoef.' / 20</th>';
  endforeach; ?>
  <th class="th-fin" style="font-size:10px;">&#931; Pts</th>
  <th class="th-fin" style="font-size:10px;">Coef</th>
  <th class="th-fin" style="font-size:10px;">Moy/20</th>
</tr>
<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;}}
      echo '<th class="th-sub rot"><span>'.htmlspecialchars($ssname).'</span></th>';}
    echo '<th class="th-stot rot"><span>Total '.htmlspecialchars($subj['name']).'</span></th>';
    echo '<th class="th-stot rot"><span>Moy/20 '.htmlspecialchars($subj['name']).'</span></th>';
  endforeach; ?>
  <th class="th-fin rot"><span>Total Final</span></th>
  <th class="th-fin rot"><span>Coef Total</span></th>
  <th class="th-fin rot"><span>Moyenne Finale</span></th>
</tr>
</thead>
<tbody>
<?php
$prevMoy=null;$rangAff=0;
foreach($rows as $idx=>$st){
  if($sortMode!=='name'){if($st['moyFinal']!==$prevMoy){$rangAff=$idx+1;$prevMoy=$st['moyFinal'];}}
  else $rangAff=$idx+1;
  $medalSymbol=match($rangAff){1=>"&#127945;",2=>"&#127946;",3=>"&#127947;",default=>''};
  $medalDisplay=$medalSymbol ? "<span class='r".$rangAff."'>".$rangAff." ".$medalSymbol."</span>" : $rangAff;
  $mc=$st['moyFinal']>=10?'ok':'bad';
  echo '<tr><td class="c-rank">'.$medalDisplay.'</td><td class="c-name">'.htmlspecialchars($st['name']).'</td>';
  foreach($subjects as $sid=>$subj){
    foreach($subj['cols'] as $ssid){
      $note=$st['grades'][$ssid]??null;
      $disp=($note!==null&&$note!=-1)?$note:'&#8211;';
      // FIX: color based on coef/2 for each subsubject instead of hardcoded 10
      $coefCell = $coefBySsid[$ssid] ?? 0;
      $passCell = $coefCell / 2;
      $nc=($note!==null&&$note!=-1)?($note<$passCell?'bad':'ok'):'';
      echo '<td class="'.$nc.'">'.$disp.'</td>';
    }
    $scoef=$st['subjTotals'][$sid]['coef']??0;$ssum=$st['subjTotals'][$sid]['sum']??0;
    $smoy=$scoef>0?round(($ssum*20)/$scoef,2):'&#8211;';
    // Subject average is always out of 20, so threshold stays at 10
    $sc=is_numeric($smoy)?($smoy<10?'bad':'ok'):'';
    echo '<td class="c-stot">'.($ssum>0?round($ssum,2):'&#8211;').'</td><td class="c-smoy '.$sc.'">'.$smoy.'</td>';
  }
  echo '<td class="c-ftot">'.round($st['totalPtsSum'],2).'</td>';
  echo '<td class="c-ftot">'.round($st['globalCoef'],2).'</td>';
  echo '<td class="c-fmoy '.$mc.'">'.$st['moyFinal'].'</td>';
  $abs = $absMap[$st['stid']] ?? 0;
  $absCls = $abs >= 10 ? 'c-abs-hi' : ($abs > 0 ? 'c-abs' : '');
  echo '<td class="'.$absCls.'">'.($abs > 0 ? $abs : '&#8211;').'</td>';
  echo '</tr>';
}

// ── Ligne moyenne de la classe ──
if($nbEleves > 0){
  echo '<tr class="tr-moy-cls">';
  echo '<td class="lbl" colspan="2">&#8709;&nbsp; Moyenne de la classe &nbsp;('.$nbEleves.' notés / '.count($rows).' élèves)</td>';
  foreach($subjects as $sid=>$subj){
    foreach($subj['cols'] as $ssid) echo '<td style="background:#1a2744;"></td>';
    $sAvgSum  = $nbEleves > 0 ? round($clsSubjSum[$sid]  / $nbEleves, 2) : '&#8211;';
    $sMoy20   = $clsSubjCoef[$sid] > 0 ? round(($clsSubjSum[$sid]*20) / $clsSubjCoef[$sid], 2) : '&#8211;';
    $smoyClr  = is_numeric($sMoy20) && $sMoy20 < 10 ? '#dc2626' : '#86efac';
    echo '<td style="background:#1a2744;">'.$sAvgSum.'</td>';
    echo '<td style="background:#1a2744;color:'.$smoyClr.';font-weight:800;">'.$sMoy20.'</td>';
  }
  $avgTotalPts  = round($clsTotalPts  / $nbEleves, 2);
  $avgCoef      = round($clsTotalCoef / $nbEleves, 2);
  $moyClsColor  = $moyClasse >= 10 ? '#86efac' : '#fca5a5';
  echo '<td>'.$avgTotalPts.'</td>';
  echo '<td>'.$avgCoef.'</td>';
  echo '<td style="font-size:13px;color:'.$moyClsColor.';">'.$moyClasse.'</td>';
  echo '<td style="background:#4c1d95;color:white;">&#8211;</td>';
  echo '</tr>';
}
?>
</tbody>
</table>
</div>
<?php }}}
mysqli_close($link);?>
</body>
</html>
