<?php
// /elms/student/results.php
declare(strict_types=1);

if (session_status() === PHP_SESSION_NONE) session_start();

require_once __DIR__ . '/../includes/db.php';

$mysqli = $mysqli ?? ($conn ?? null);

if (!$mysqli instanceof mysqli) {
  http_response_code(500);
  exit('DB connection failed');
}

$mysqli->set_charset('utf8mb4');

if (empty($_SESSION['student_id'])) {
  header('Location: /elms/student/login.php?next=' . rawurlencode($_SERVER['REQUEST_URI'] ?? '/elms/student/results.php'));
  exit;
}

function h($s) {
  return htmlspecialchars((string)$s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function table_exists(mysqli $db, string $table): bool {
  $table = $db->real_escape_string($table);
  $q = $db->query("SHOW TABLES LIKE '{$table}'");
  return $q && $q->num_rows > 0;
}

function col_exists(mysqli $db, string $table, string $col): bool {
  $table = $db->real_escape_string($table);
  $col = $db->real_escape_string($col);
  $q = $db->query("SHOW COLUMNS FROM `{$table}` LIKE '{$col}'");
  return $q && $q->num_rows > 0;
}

function nfmt($n): string {
  if ($n === null || $n === '') return 'Pending';
  $f = (float)$n;
  return (abs($f - (int)$f) < 0.00001) ? (string)(int)$f : number_format($f, 2);
}

function grade_from_percent(float $p): string {
  if ($p >= 75) return 'A';
  if ($p >= 65) return 'B';
  if ($p >= 55) return 'C';
  if ($p >= 40) return 'S';
  return 'F';
}

$student_id = (int)$_SESSION['student_id'];

/* Student */
$studentCols = "id, full_name, academic_email, center_reg_no, reference_no, lms_status";

if (table_exists($mysqli, 'studentss') && col_exists($mysqli, 'studentss', 'course_id')) {
  $studentCols .= ", course_id";
}

$st = $mysqli->prepare("SELECT $studentCols FROM studentss WHERE id=? LIMIT 1");
$st->bind_param('i', $student_id);
$st->execute();
$student = $st->get_result()->fetch_assoc();
$st->close();

if (!$student) {
  session_destroy();
  header('Location: /elms/student/login.php');
  exit;
}

$_SESSION['student_name']   = $student['full_name'] ?? ($_SESSION['student_name'] ?? 'Student');
$_SESSION['academic_email'] = $student['academic_email'] ?? ($_SESSION['academic_email'] ?? '');
$_SESSION['center_reg_no']  = $student['center_reg_no'] ?? ($_SESSION['center_reg_no'] ?? '');

$lms_status = strtolower(trim((string)($student['lms_status'] ?? 'active')));

if (in_array($lms_status, ['inactive', 'suspended'], true)) {
  session_destroy();
  header('Location: /elms/student/login.php');
  exit;
}

/* Ensure submissions table exists */
$mysqli->query("
CREATE TABLE IF NOT EXISTS `lms_assessment_submissions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `assessment_id` INT UNSIGNED NOT NULL,
  `student_id` INT UNSIGNED NOT NULL,
  `file_path` VARCHAR(500) NULL,
  `original_name` VARCHAR(255) NULL,
  `student_note` TEXT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'submitted',
  `marks` DECIMAL(10,2) NULL,
  `feedback` TEXT NULL,
  `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_assessment_student` (`assessment_id`, `student_id`),
  KEY `idx_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
");

/* Detect assessment table */
$assessmentTable = null;

foreach (['lms_assessments', 'assessments', 'assignments', 'course_assessments'] as $tbl) {
  if (table_exists($mysqli, $tbl)) {
    $assessmentTable = $tbl;
    break;
  }
}

$rows = [];

$q = trim((string)($_GET['q'] ?? ''));
$status = $_GET['status'] ?? 'all';
if (!in_array($status, ['all', 'marked', 'pending'], true)) {
  $status = 'all';
}

$per = (int)($_GET['per'] ?? 50);
if ($per <= 0) $per = 50;
if ($per > 200) $per = 200;

$page = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page - 1) * $per;

if ($assessmentTable) {
  $aIdCol = col_exists($mysqli, $assessmentTable, 'id') ? 'id' : 'assessment_id';
  $aTitleCol = col_exists($mysqli, $assessmentTable, 'title') ? 'title' : (col_exists($mysqli, $assessmentTable, 'name') ? 'name' : $aIdCol);

  $aMaxCol = null;
  foreach (['max_mark', 'max_marks', 'total_marks', 'max_score', 'marks'] as $tryCol) {
    if (col_exists($mysqli, $assessmentTable, $tryCol)) {
      $aMaxCol = $tryCol;
      break;
    }
  }

  $aDueCol = null;
  foreach (['due_at', 'due_date', 'deadline', 'available_to', 'close_at'] as $tryCol) {
    if (col_exists($mysqli, $assessmentTable, $tryCol)) {
      $aDueCol = $tryCol;
      break;
    }
  }

  $aCourseCol = col_exists($mysqli, $assessmentTable, 'course_id') ? 'course_id' : null;
  $aModuleCol = col_exists($mysqli, $assessmentTable, 'module_id') ? 'module_id' : null;

  $select = "
    s.id AS submission_id,
    s.assessment_id,
    s.student_id,
    s.file_path,
    s.original_name,
    s.status,
    s.marks,
    s.feedback,
    s.submitted_at,
    a.`$aTitleCol` AS assessment_title
  ";

  $select .= $aMaxCol ? ", COALESCE(a.`$aMaxCol`,100) AS max_marks" : ", 100 AS max_marks";
  $select .= $aDueCol ? ", a.`$aDueCol` AS due_at" : ", NULL AS due_at";
  $select .= $aCourseCol ? ", a.`$aCourseCol` AS course_id" : ", 0 AS course_id";
  $select .= $aModuleCol ? ", a.`$aModuleCol` AS module_id" : ", 0 AS module_id";

  $where = ["s.student_id = ?"];
  $types = "i";
  $params = [$student_id];

  if ($status === 'marked') {
    $where[] = "s.marks IS NOT NULL";
  } elseif ($status === 'pending') {
    $where[] = "s.marks IS NULL";
  }

  if ($q !== '') {
    $where[] = "(a.`$aTitleCol` LIKE ? OR s.status LIKE ? OR s.feedback LIKE ?)";
    $types .= "sss";
    $like = '%' . $q . '%';
    $params[] = $like;
    $params[] = $like;
    $params[] = $like;
  }

  $whereSql = "WHERE " . implode(" AND ", $where);

  /* Count */
  $sqlCount = "
    SELECT COUNT(*) AS n
    FROM lms_assessment_submissions s
    LEFT JOIN `$assessmentTable` a ON a.`$aIdCol` = s.assessment_id
    $whereSql
  ";

  $st = $mysqli->prepare($sqlCount);
  $st->bind_param($types, ...$params);
  $st->execute();
  $totalRow = $st->get_result()->fetch_assoc();
  $st->close();

  $total = (int)($totalRow['n'] ?? 0);
  $pages = max(1, (int)ceil($total / $per));

  /* Export CSV */
  if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    $sqlAll = "
      SELECT $select
      FROM lms_assessment_submissions s
      LEFT JOIN `$assessmentTable` a ON a.`$aIdCol` = s.assessment_id
      $whereSql
      ORDER BY s.submitted_at DESC, s.id DESC
    ";

    $st = $mysqli->prepare($sqlAll);
    $st->bind_param($types, ...$params);
    $st->execute();
    $rs = $st->get_result();

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=my_results.csv');

    $out = fopen('php://output', 'w');
    fputcsv($out, ['Assessment', 'Marks', 'Out Of', 'Percentage', 'Grade', 'Status', 'Submitted At', 'Feedback']);

    while ($r = $rs->fetch_assoc()) {
      $marks = $r['marks'];
      $outOf = (float)($r['max_marks'] ?? 100);
      $percent = ($marks !== null && $outOf > 0) ? round(((float)$marks / $outOf) * 100, 2) : '';
      $grade = ($marks !== null && $outOf > 0) ? grade_from_percent((float)$percent) : 'Pending';

      fputcsv($out, [
        $r['assessment_title'] ?? ('Assessment #' . $r['assessment_id']),
        $marks !== null ? nfmt($marks) : '',
        nfmt($outOf),
        $percent,
        $grade,
        $marks !== null ? 'Marked' : 'Pending',
        $r['submitted_at'] ?? '',
        $r['feedback'] ?? '',
      ]);
    }

    fclose($out);
    exit;
  }

  /* Main rows */
  $sql = "
    SELECT $select
    FROM lms_assessment_submissions s
    LEFT JOIN `$assessmentTable` a ON a.`$aIdCol` = s.assessment_id
    $whereSql
    ORDER BY s.submitted_at DESC, s.id DESC
    LIMIT $per OFFSET $offset
  ";

  $st = $mysqli->prepare($sql);
  $st->bind_param($types, ...$params);
  $st->execute();
  $rs = $st->get_result();

  while ($r = $rs->fetch_assoc()) {
    $rows[] = $r;
  }

  $st->close();

} else {
  $total = 0;
  $pages = 1;
}

/* Summary */
$markedCount = 0;
$pendingCount = 0;
$totalMarks = 0.0;
$totalOutOf = 0.0;

foreach ($rows as $r) {
  if ($r['marks'] !== null && $r['marks'] !== '') {
    $markedCount++;
    $totalMarks += (float)$r['marks'];
    $totalOutOf += (float)($r['max_marks'] ?? 100);
  } else {
    $pendingCount++;
  }
}

$avgPercent = ($totalOutOf > 0) ? round(($totalMarks / $totalOutOf) * 100, 2) : 0;

$PAGE_TITLE = 'My Results';
$ACTIVE_MENU = 'results';
require_once __DIR__ . '/studentlayout_top.php';
?>

<div class="row g-4 mb-4">

  <div class="col-md-4">
    <div class="card stat-card">
      <div class="card-body d-flex align-items-center gap-3">
        <div class="stat-icon">
          <i class="bi bi-award"></i>
        </div>
        <div>
          <div class="stat-label">Marked Results</div>
          <div class="stat-value"><?= (int)$markedCount ?></div>
        </div>
      </div>
    </div>
  </div>

  <div class="col-md-4">
    <div class="card stat-card">
      <div class="card-body d-flex align-items-center gap-3">
        <div class="stat-icon">
          <i class="bi bi-hourglass-split"></i>
        </div>
        <div>
          <div class="stat-label">Pending</div>
          <div class="stat-value"><?= (int)$pendingCount ?></div>
        </div>
      </div>
    </div>
  </div>

  <div class="col-md-4">
    <div class="card stat-card">
      <div class="card-body d-flex align-items-center gap-3">
        <div class="stat-icon">
          <i class="bi bi-graph-up"></i>
        </div>
        <div>
          <div class="stat-label">Average</div>
          <div class="stat-value"><?= $avgPercent > 0 ? h($avgPercent . '%') : '—' ?></div>
        </div>
      </div>
    </div>
  </div>

</div>

<div class="card soft-card">
  <div class="card-body p-4">

    <div class="d-flex flex-column flex-xl-row justify-content-between align-items-xl-center gap-3 mb-4">
      <div>
        <h2 class="section-title mb-1">Results History</h2>
        <div class="text-muted small">Only your submitted assessments are shown here.</div>
      </div>

      <form method="get" class="d-flex flex-wrap gap-2">
        <input type="text" name="q" value="<?= h($q) ?>" class="form-control form-control-sm" style="width:240px;" placeholder="Search assessment...">

        <select name="status" class="form-select form-select-sm" style="width:150px;">
          <option value="all" <?= $status === 'all' ? 'selected' : '' ?>>All</option>
          <option value="marked" <?= $status === 'marked' ? 'selected' : '' ?>>Marked</option>
          <option value="pending" <?= $status === 'pending' ? 'selected' : '' ?>>Pending</option>
        </select>

        <select name="per" class="form-select form-select-sm" style="width:120px;">
          <?php foreach ([20, 50, 100, 200] as $opt): ?>
            <option value="<?= $opt ?>" <?= $per === $opt ? 'selected' : '' ?>><?= $opt ?>/page</option>
          <?php endforeach; ?>
        </select>

        <button class="btn btn-primary btn-sm">Apply</button>
        <a href="/elms/student/results.php" class="btn btn-outline-secondary btn-sm">Reset</a>

        <a class="btn btn-outline-success btn-sm"
           href="?<?= h(http_build_query(['q'=>$q, 'status'=>$status, 'per'=>$per, 'export'=>'csv'])) ?>">
          Export CSV
        </a>
      </form>
    </div>

    <?php if (!$assessmentTable): ?>

      <div class="text-center py-5">
        <i class="bi bi-exclamation-triangle fs-1 text-danger d-block mb-3"></i>
        <h3 class="h5 fw-bold">Assessment table not found</h3>
        <p class="text-muted mb-0">Need lms_assessments / assessments table.</p>
      </div>

    <?php elseif (!$rows): ?>

      <div class="text-center py-5">
        <i class="bi bi-inbox fs-1 text-muted d-block mb-3"></i>
        <h3 class="h5 fw-bold">No results yet</h3>
        <p class="text-muted mb-0">
          Results will appear here after you submit an assessment and admin/lecturer enters marks.
        </p>
      </div>

    <?php else: ?>

      <div class="table-responsive">
        <table class="table align-middle results-table">
          <thead>
            <tr>
              <th>Assessment</th>
              <th>Submitted</th>
              <th class="text-end">Marks</th>
              <th class="text-end">Out Of</th>
              <th class="text-end">Percentage</th>
              <th>Grade</th>
              <th>Status</th>
              <th>Feedback</th>
              <th class="text-end">Action</th>
            </tr>
          </thead>

          <tbody>
            <?php foreach ($rows as $r): ?>
              <?php
                $marks = $r['marks'];
                $outOf = (float)($r['max_marks'] ?? 100);
                $isMarked = ($marks !== null && $marks !== '');

                $percent = null;
                $grade = 'Pending';

                if ($isMarked && $outOf > 0) {
                  $percent = round(((float)$marks / $outOf) * 100, 2);
                  $grade = grade_from_percent((float)$percent);
                }

                $badgeClass = $isMarked ? 'badge-soft-green' : 'badge-soft-yellow';
                $statusText = $isMarked ? 'Marked' : 'Pending';
              ?>

              <tr>
                <td>
                  <div class="fw-bold">
                    <?= h($r['assessment_title'] ?? ('Assessment #' . $r['assessment_id'])) ?>
                  </div>
                  <div class="small text-muted">
                    Assessment ID: <?= (int)$r['assessment_id'] ?>
                    <?php if (!empty($r['due_at'])): ?>
                      · Due <?= h(date('Y-m-d H:i', strtotime((string)$r['due_at']))) ?>
                    <?php endif; ?>
                  </div>
                </td>

                <td>
                  <?= !empty($r['submitted_at']) ? h(date('Y-m-d H:i', strtotime((string)$r['submitted_at']))) : '-' ?>
                </td>

                <td class="text-end fw-bold">
                  <?= $isMarked ? h(nfmt($marks)) : '—' ?>
                </td>

                <td class="text-end">
                  <?= h(nfmt($outOf)) ?>
                </td>

                <td class="text-end">
                  <?= $percent !== null ? h($percent . '%') : '—' ?>
                </td>

                <td>
                  <?php if ($isMarked): ?>
                    <span class="grade-pill"><?= h($grade) ?></span>
                  <?php else: ?>
                    <span class="text-muted">—</span>
                  <?php endif; ?>
                </td>

                <td>
                  <span class="badge <?= h($badgeClass) ?> rounded-pill">
                    <?= h($statusText) ?>
                  </span>
                </td>

                <td style="min-width:220px;">
                  <?php if (!empty($r['feedback'])): ?>
                    <div class="feedback-preview">
                      <?= h(mb_strimwidth(strip_tags((string)$r['feedback']), 0, 120, '...')) ?>
                    </div>
                  <?php else: ?>
                    <span class="text-muted">No feedback yet</span>
                  <?php endif; ?>
                </td>

                <td class="text-end">
                  <a href="/elms/student/assessments/submit.php?assessment_id=<?= (int)$r['assessment_id'] ?>"
                     class="btn btn-outline-primary btn-sm rounded-pill">
                    View
                  </a>
                </td>
              </tr>

            <?php endforeach; ?>
          </tbody>
        </table>
      </div>

      <form method="get" class="d-flex justify-content-end align-items-center gap-2 mt-3">
        <input type="hidden" name="q" value="<?= h($q) ?>">
        <input type="hidden" name="status" value="<?= h($status) ?>">
        <input type="hidden" name="per" value="<?= (int)$per ?>">

        <button class="btn btn-outline-secondary btn-sm" name="page" value="1" <?= $page <= 1 ? 'disabled' : '' ?>>« First</button>
        <button class="btn btn-outline-secondary btn-sm" name="page" value="<?= max(1, $page - 1) ?>" <?= $page <= 1 ? 'disabled' : '' ?>>‹ Prev</button>

        <span class="small text-muted px-2">Page <?= (int)$page ?> / <?= (int)$pages ?></span>

        <button class="btn btn-outline-secondary btn-sm" name="page" value="<?= min($pages, $page + 1) ?>" <?= $page >= $pages ? 'disabled' : '' ?>>Next ›</button>
        <button class="btn btn-outline-secondary btn-sm" name="page" value="<?= (int)$pages ?>" <?= $page >= $pages ? 'disabled' : '' ?>>Last »</button>
      </form>

    <?php endif; ?>

  </div>
</div>

<style>
  .results-table thead th{
    color:#64748b;
    font-size:12px;
    text-transform:uppercase;
    letter-spacing:.04em;
    border-bottom:1px solid rgba(15,23,42,.08);
  }

  .results-table tbody td{
    padding-top:16px;
    padding-bottom:16px;
  }

  .grade-pill{
    display:inline-flex;
    align-items:center;
    justify-content:center;
    min-width:34px;
    height:28px;
    border-radius:999px;
    background:#eef4ff;
    color:#1d4ed8;
    font-weight:900;
  }

  .feedback-preview{
    background:#f8fafc;
    border:1px solid rgba(15,23,42,.06);
    border-radius:12px;
    padding:10px;
    font-size:13px;
    color:#334155;
  }
</style>

<?php require_once __DIR__ . '/studentlayout_bottom.php'; ?>