<?php declare(strict_types=1);
/* One-page Simple Report — latest Quiz marks + latest Assessment/Project submissions
 * Path: /elms/admin/reports/simple_report.php
 */
define('APP_ROOT', dirname(__DIR__, 2));
if (session_status() === PHP_SESSION_NONE) session_start();

require_once APP_ROOT . '/includes/auth.php';
require_role(['super_admin','admin','branch_admin','course_coordinator','coordinator','lecturer']);
require_once APP_ROOT . '/includes/db.php';

function esc($s){ return htmlspecialchars((string)$s, ENT_QUOTES|ENT_SUBSTITUTE,'UTF-8'); }
if (!function_exists('url_for')) {
  function url_for(string $p):string{
    $b = rtrim(dirname($_SERVER['SCRIPT_NAME']), '/');
    $b = preg_replace('#/admin/reports$#','',$b);
    if ($p==='' || $p[0] !== '/') $p='/'.$p;
    return ($b===''?'/':$b).$p;
  }
}
function c_exists(mysqli $db,string $t,string $c):bool{
  $st=$db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1");
  $st->bind_param('ss',$t,$c); $st->execute();
  $ok=(bool)$st->get_result()->fetch_row(); $st->close();
  return $ok;
}
function label_col(mysqli $db,string $t,array $cands):string{
  $cols=[]; if($rs=$db->query("SHOW COLUMNS FROM `$t`")) while($r=$rs->fetch_assoc()) $cols[]=$r['Field'];
  foreach($cands as $c) if(in_array($c,$cols,true)) return $c; return 'id';
}

/* -------- inputs -------- */
$course_id     = (int)($_GET['course_id'] ?? 0);
$batch_id      = (int)($_GET['batch_id'] ?? 0);
$quiz_id       = (int)($_GET['quiz_id'] ?? 0);
$assessment_id = (int)($_GET['assessment_id'] ?? 0);
$student_id    = (int)($_GET['student_id'] ?? 0);
$export        = (string)($_GET['export'] ?? ''); // 'quiz'|'ass'

/* -------- common labels -------- */
$studLabel  = label_col($mysqli,'studentss',['full_name','name','title']);
$assTitle   = label_col($mysqli,'lms_assessments',['title','name']);
$batchLbl   = label_col($mysqli,'batches',['batch_name','name','title']);
$quizTitle  = label_col($mysqli,'lms_quizzes',['title','name','label']);
$hasCourseQ = c_exists($mysqli,'lms_quizzes','course_id');
$hasCourseA = c_exists($mysqli,'lms_assessments','course_id');

/* consistent student name */
$studNameExpr = "st.$studLabel";
if ($studLabel==='id' && c_exists($mysqli,'studentss','first_name') && c_exists($mysqli,'studentss','last_name')) {
  $studNameExpr = "CONCAT_WS(' ', st.first_name, st.last_name)";
}

/* -------- QUIZ: detect columns -------- */
$qaTimeCands = array_values(array_filter([
  c_exists($mysqli,'lms_quiz_attempts','finished_at') ? 'finished_at' : null,
  c_exists($mysqli,'lms_quiz_attempts','ended_at')    ? 'ended_at'    : null,
  c_exists($mysqli,'lms_quiz_attempts','completed_at')? 'completed_at': null,
  c_exists($mysqli,'lms_quiz_attempts','created_at')  ? 'created_at'  : null,
  c_exists($mysqli,'lms_quiz_attempts','updated_at')  ? 'updated_at'  : null,
  c_exists($mysqli,'lms_quiz_attempts','started_at')  ? 'started_at'  : null,
]));
if (!$qaTimeCands) $qaTimeCands=['created_at']; // harmless fallback

$qaScore = c_exists($mysqli,'lms_quiz_attempts','score') ? 'score'
         : (c_exists($mysqli,'lms_quiz_attempts','marks') ? 'marks' : null);

$qaTimeExpr  = function(string $alias) use ($qaTimeCands){ return 'COALESCE('.implode(',', array_map(fn($c)=>"$alias.$c",$qaTimeCands)).')'; };

/* filters -> WHERE for quiz */
$qwhere=[]; $qt=''; $qp=[];
if ($quiz_id>0){ $qwhere[]='qa.quiz_id=?'; $qt.='i'; $qp[]=$quiz_id; }
if ($student_id>0){ $qwhere[]='qa.student_id=?'; $qt.='i'; $qp[]=$student_id; }
if ($course_id>0 && $hasCourseQ){ $qwhere[]='q.course_id=?'; $qt.='i'; $qp[]=$course_id; }
$QWHERE = $qwhere? ('WHERE '.implode(' AND ',$qwhere)) : '';

$timeQA = $qaTimeExpr('qa');
$timeQA1= $qaTimeExpr('qa1');

/* latest quiz attempt per student/quiz */
$sqlQuiz = "
SELECT qa.quiz_id, qa.student_id,
       $timeQA AS attempted_at,
       ".($qaScore? "qa.$qaScore AS score," : "NULL AS score,")."
       q.$quizTitle AS quiz_title,
       $studNameExpr AS student_name,
       COALESCE(st.reference_no,'') AS student_ref
FROM lms_quiz_attempts qa
JOIN (
  SELECT qa1.quiz_id, qa1.student_id, MAX($timeQA1) last_time
  FROM lms_quiz_attempts qa1
  GROUP BY qa1.quiz_id, qa1.student_id
) t ON t.quiz_id=qa.quiz_id AND t.student_id=qa.student_id AND t.last_time=$timeQA
JOIN lms_quizzes q ON q.id=qa.quiz_id
JOIN studentss st ON st.id=qa.student_id
$QWHERE
ORDER BY q.$quizTitle, student_name
LIMIT 200
";
$quizRows=[];
$st=$mysqli->prepare($sqlQuiz);
if ($qt!=='') $st->bind_param($qt, ...$qp);
$st->execute(); $quizRows=$st->get_result()->fetch_all(MYSQLI_ASSOC); $st->close();

/* -------- ASSESSMENTS: detect columns -------- */
$subTimeCands = array_values(array_filter([
  c_exists($mysqli,'lms_submissions','submitted_at') ? 'submitted_at' : null,
  c_exists($mysqli,'lms_submissions','created_at')   ? 'created_at'   : null,
  c_exists($mysqli,'lms_submissions','updated_at')   ? 'updated_at'   : null,
  c_exists($mysqli,'lms_submissions','graded_at')    ? 'graded_at'    : null,
]));
if (!$subTimeCands) $subTimeCands=['submitted_at'];

$subTimeExpr=function(string $a) use($subTimeCands){ return 'COALESCE('.implode(',', array_map(fn($c)=>"$a.$c",$subTimeCands)).')'; };
$scoreCol = c_exists($mysqli,'lms_submissions','final_score') ? 'final_score'
          : (c_exists($mysqli,'lms_submissions','score') ? 'score'
          : (c_exists($mysqli,'lms_submissions','provisional_score') ? 'provisional_score' : null));
$statusCol= c_exists($mysqli,'lms_submissions','workflow_status') ? 'workflow_status'
          : (c_exists($mysqli,'lms_submissions','status') ? 'status' : null);
$fileCol  = c_exists($mysqli,'lms_submissions','file_path') ? 'file_path'
          : (c_exists($mysqli,'lms_submissions','file') ? 'file'
          : (c_exists($mysqli,'lms_submissions','attachment') ? 'attachment' : null));

$awhere=[]; $at=''; $ap=[];
if ($assessment_id>0){ $awhere[]='s.assessment_id=?'; $at.='i'; $ap[]=$assessment_id; }
if ($batch_id>0){ $awhere[]='s.batch_id=?'; $at.='i'; $ap[]=$batch_id; }
if ($student_id>0){ $awhere[]='s.student_id=?'; $at.='i'; $ap[]=$student_id; }
if ($course_id>0 && $hasCourseA){ $awhere[]='a.course_id=?'; $at.='i'; $ap[]=$course_id; }
$AWHERE=$awhere? ('WHERE '.implode(' AND ',$awhere)) : '';

$tS  = $subTimeExpr('s');
$tS1 = $subTimeExpr('s1');

$sqlAss = "
SELECT s.assessment_id, s.batch_id, s.student_id,
       $tS AS submitted_time,
       ".($scoreCol? "s.$scoreCol AS score," : "NULL AS score,")."
       ".($statusCol? "s.$statusCol AS wf_status," : "NULL AS wf_status,")."
       ".($fileCol? "s.$fileCol AS file_path," : "NULL AS file_path,")."
       a.$assTitle AS assessment_title,
       b.$batchLbl AS batch_label,
       $studNameExpr AS student_name,
       COALESCE(st.reference_no,'') AS student_ref
FROM lms_submissions s
JOIN (
  SELECT s1.assessment_id, s1.student_id, MAX($tS1) last_time
  FROM lms_submissions s1
  GROUP BY s1.assessment_id, s1.student_id
) t ON t.assessment_id=s.assessment_id AND t.student_id=s.student_id AND t.last_time=$tS
JOIN lms_assessments a ON a.id=s.assessment_id
JOIN batches b         ON b.id=s.batch_id
JOIN studentss st      ON st.id=s.student_id
$AWHERE
ORDER BY a.$assTitle, student_name
LIMIT 200
";
$assRows=[];
$st=$mysqli->prepare($sqlAss);
if ($at!=='') $st->bind_param($at, ...$ap);
$st->execute(); $assRows=$st->get_result()->fetch_all(MYSQLI_ASSOC); $st->close();

/* -------- CSV export (optional) -------- */
if ($export==='quiz') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="quiz_report_simple.csv"');
  $out=fopen('php://output','w');
  fputcsv($out,['Quiz','Student','Ref','Attempted At','Score']);
  foreach($quizRows as $r){
    fputcsv($out, [$r['quiz_title'],$r['student_name'],$r['student_ref'],$r['attempted_at'], ($r['score']===null?'':$r['score']) ]);
  }
  fclose($out); exit;
}
if ($export==='ass') {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="assessments_report_simple.csv"');
  $out=fopen('php://output','w');
  fputcsv($out,['Assessment/Project','Batch','Student','Ref','Submitted','Score','Status','File']);
  foreach($assRows as $r){
    fputcsv($out, [
      $r['assessment_title'],$r['batch_label'],$r['student_name'],$r['student_ref'],
      $r['submitted_time'], ($r['score']===null?'':$r['score']), ($r['wf_status']??''), ($r['file_path']??'')
    ]);
  }
  fclose($out); exit;
}

/* -------- layout -------- */
$PAGE_TITLE  = 'Simple Report';
$ACTIVE_MENU = 'reports';
require APP_ROOT . '/includes/admin_layout_top.php';
?>
<div class="d-flex align-items-center mb-3">
  <h1 class="h5 m-0">Simple Report</h1>
  <a class="btn btn-sm btn-outline-secondary ms-auto" href="<?= url_for('/admin/reports/index.php') ?>">← Filters</a>
</div>

<div class="row g-3">
  <div class="col-12">
    <div class="card">
      <div class="card-header d-flex align-items-center">
        <strong>Latest Quiz Marks</strong>
        <a class="btn btn-sm btn-outline-secondary ms-auto"
           href="<?= url_for('/admin/reports/simple_report.php').'?'.http_build_query(array_merge($_GET,['export'=>'quiz'])) ?>">Export CSV</a>
      </div>
      <div class="card-body table-responsive">
        <table class="table table-sm align-middle">
          <thead class="table-light"><tr><th>Quiz</th><th>Student</th><th>Ref</th><th>Attempted</th><th>Score</th></tr></thead>
          <tbody>
          <?php if(!$quizRows): ?>
            <tr><td colspan="5" class="text-center text-muted">No data.</td></tr>
          <?php else: foreach($quizRows as $r): ?>
            <tr>
              <td><?= esc($r['quiz_title']) ?></td>
              <td><?= esc($r['student_name']) ?></td>
              <td class="text-muted"><?= esc($r['student_ref']) ?></td>
              <td class="small"><?= esc($r['attempted_at'] ?? '') ?></td>
              <td><?= $r['score']===null ? '—' : esc((string)$r['score']) ?></td>
            </tr>
          <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>

  <div class="col-12">
    <div class="card">
      <div class="card-header d-flex align-items-center">
        <strong>Latest Assessment / Project Submissions</strong>
        <a class="btn btn-sm btn-outline-secondary ms-auto"
           href="<?= url_for('/admin/reports/simple_report.php').'?'.http_build_query(array_merge($_GET,['export'=>'ass'])) ?>">Export CSV</a>
      </div>
      <div class="card-body table-responsive">
        <table class="table table-sm align-middle">
          <thead class="table-light">
            <tr><th>Assessment</th><th>Batch</th><th>Student</th><th>Ref</th><th>Submitted</th><th>Score</th><th>Status</th><th>File</th></tr>
          </thead>
          <tbody>
          <?php if(!$assRows): ?>
            <tr><td colspan="8" class="text-center text-muted">No data.</td></tr>
          <?php else: foreach($assRows as $r): ?>
            <tr>
              <td><?= esc($r['assessment_title']) ?></td>
              <td><?= esc($r['batch_label']) ?></td>
              <td><?= esc($r['student_name']) ?></td>
              <td class="text-muted"><?= esc($r['student_ref']) ?></td>
              <td class="small"><?= esc($r['submitted_time'] ?? '') ?></td>
              <td><?= $r['score']===null ? '—' : esc((string)$r['score']) ?></td>
              <td><?= esc((string)($r['wf_status'] ?? '')) ?></td>
              <td>
                <?php if(!empty($r['file_path'])): ?>
                  <a class="btn btn-sm btn-outline-primary" target="_blank" href="<?= esc($r['file_path']) ?>">Open file</a>
                <?php else: ?>
                  <span class="badge bg-secondary">No file</span>
                <?php endif; ?>
              </td>
            </tr>
          <?php endforeach; endif; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<?php require APP_ROOT . '/includes/admin_layout_bottom.php';
