<?php declare(strict_types=1);
/* ============================================================================
 * Assessments / Projects Report (latest submission per student)
 * Path: /elms/admin/reports/assessments_report.php
 * ----------------------------------------------------------------------------
 * - Auto-detects timestamp/score/status/file columns across schema variants
 * - Filters: course_id (if available on lms_assessments), assessment_id, batch_id, q (student search)
 * - CSV export: ?export=csv plus the same filters
 * ========================================================================== */

define('APP_ROOT', dirname(__DIR__, 2)); // .../elms
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';

/* -------- helpers -------- */
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']), '/');      // /elms/admin/reports
    $b = preg_replace('#/admin/reports$#','',$b);           // -> /elms
    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);
$assessment_id = (int)($_GET['assessment_id'] ?? 0);
$batch_id      = (int)($_GET['batch_id'] ?? 0);
$q             = trim((string)($_GET['q'] ?? ''));
$exportCsv     = (isset($_GET['export']) && strtolower((string)$_GET['export'])==='csv');

/* -------- schema detection -------- */
$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']);

/* time columns to coalesce (order by preference) */
$timeCands = 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 (!$timeCands) {
  // absolute fallback so queries still work (but all times will be NULL)
  $timeCands = ['submitted_at']; // column may not exist; will just be NULL
}
$timeExpr = function(string $alias) use ($timeCands): string {
  $parts = [];
  foreach ($timeCands as $c) $parts[] = "$alias.$c";
  return 'COALESCE('.implode(',', $parts).')';
};

/* pick score column */
$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));

/* status/workflow column */
$statusCol = c_exists($mysqli,'lms_submissions','workflow_status') ? 'workflow_status'
           : (c_exists($mysqli,'lms_submissions','status') ? 'status' : null);

/* file column */
$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));

/* course column on assessments (optional) */
$assHasCourse = c_exists($mysqli,'lms_assessments','course_id');

/* student name expr */
$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)";
}

/* -------- build SQL (latest submission per student/assessment) -------- */
$where = [];
$params = []; $types='';

if ($assessment_id>0) { $where[] = 's.assessment_id=?'; $types.='i'; $params[]=$assessment_id; }
if ($batch_id>0)      { $where[] = 's.batch_id=?';      $types.='i'; $params[]=$batch_id; }
if ($course_id>0 && $assHasCourse) { $where[]='a.course_id=?'; $types.='i'; $params[]=$course_id; }
if ($q!=='') {
  $where[] = "($studNameExpr LIKE CONCAT('%',?,'%')
               OR COALESCE(st.reference_no,'') LIKE CONCAT('%',?,'%'))";
  $types.='ss'; $params[]=$q; $params[]=$q;
}
$WHERE = $where ? ('WHERE '.implode(' AND ', $where)) : '';

$timeExprS  = $timeExpr('s');
$timeExprS1 = $timeExpr('s1');

/* Build SELECT */
$sql = "
SELECT
  s.assessment_id,
  s.batch_id,
  s.student_id,
  $timeExprS   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($timeExprS1) AS 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 = $timeExprS
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
$WHERE
ORDER BY a.$assTitle, student_name
";

/* -------- run query -------- */
$rows=[];
$st=$mysqli->prepare($sql);
if ($types!=='') $st->bind_param($types, ...$params);
$st->execute(); $rows=$st->get_result()->fetch_all(MYSQLI_ASSOC); $st->close();

/* -------- CSV export -------- */
if ($exportCsv) {
  header('Content-Type: text/csv; charset=utf-8');
  header('Content-Disposition: attachment; filename="assessments_report.csv"');
  $out=fopen('php://output','w');
  fputcsv($out, ['Assessment','Batch','Student','Ref','Submitted At','Score','Status','File']);
  foreach($rows as $r){
    $file = $r['file_path'] ?? '';
    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'] ?? '',
      $file
    ]);
  }
  fclose($out); exit;
}

/* -------- layout -------- */
$PAGE_TITLE  = 'Assessments / Projects 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">Assessments / Projects Report</h1>
  <a class="btn btn-sm btn-outline-secondary ms-auto"
     href="<?= url_for('/admin/reports/index.php') ?>">← Back to Reports</a>
</div>

<div class="card mb-3">
  <div class="card-body">
    <form class="row g-2">
      <div class="col-md-2">
        <label class="form-label">Course ID</label>
        <input name="course_id" type="number" class="form-control" value="<?= (int)$course_id ?>">
      </div>
      <div class="col-md-2">
        <label class="form-label">Assessment ID</label>
        <input name="assessment_id" type="number" class="form-control" value="<?= (int)$assessment_id ?>">
      </div>
      <div class="col-md-2">
        <label class="form-label">Batch ID</label>
        <input name="batch_id" type="number" class="form-control" value="<?= (int)$batch_id ?>">
      </div>
      <div class="col-md-4">
        <label class="form-label">Student search</label>
        <input name="q" class="form-control" value="<?= esc($q) ?>" placeholder="name or reference no.">
      </div>
      <div class="col-md-2 d-grid align-items-end">
        <button class="btn btn-primary mt-4">Filter</button>
      </div>
    </form>

    <div class="mt-3 d-flex gap-2">
      <a class="btn btn-outline-secondary"
         href="<?= url_for('/admin/reports/assessments_report.php').'?'.http_build_query([
           'course_id'=>$course_id?:null,
           'assessment_id'=>$assessment_id?:null,
           'batch_id'=>$batch_id?:null,
           'q'=>$q?:null,
           'export'=>'csv'
         ]) ?>">Export CSV</a>
    </div>
  </div>
</div>

<div class="card">
  <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(!$rows): ?>
        <tr><td colspan="8" class="text-center text-muted">No results.</td></tr>
      <?php else: foreach($rows 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"
                 href="<?= esc($r['file_path']) ?>" target="_blank" rel="noopener">Open file</a>
            <?php else: ?>
              <span class="badge bg-secondary">No file</span>
            <?php endif; ?>
          </td>
        </tr>
      <?php endforeach; endif; ?>
      </tbody>
    </table>
  </div>
</div>

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