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

$PAGE_TITLE = 'My Results';
$ACTIVE = 'results';

require_once __DIR__ . '/../includes/student_auth.php';
require_once __DIR__ . '/../includes/elms_settings.php';

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

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

$mysqli->set_charset('utf8mb4');

/* ---------------- Helpers ---------------- */

if (!function_exists('h')) {
    function h($value): string {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('sr_table_exists')) {
    function sr_table_exists(mysqli $db, string $table): bool {
        $safe = $db->real_escape_string($table);
        $res = $db->query("SHOW TABLES LIKE '{$safe}'");
        $ok = $res && $res->num_rows > 0;
        if ($res) $res->free();
        return $ok;
    }
}

if (!function_exists('sr_col_exists')) {
    function sr_col_exists(mysqli $db, string $table, string $col): bool {
        $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        $safeCol = $db->real_escape_string($col);
        $res = $db->query("SHOW COLUMNS FROM `{$safeTable}` LIKE '{$safeCol}'");
        $ok = $res && $res->num_rows > 0;
        if ($res) $res->free();
        return $ok;
    }
}

if (!function_exists('sr_first_col')) {
    function sr_first_col(mysqli $db, string $table, array $cols): ?string {
        foreach ($cols as $col) {
            if (sr_col_exists($db, $table, $col)) {
                return $col;
            }
        }
        return null;
    }
}

if (!function_exists('nfmt')) {
    function nfmt($n): string {
        if ($n === null || $n === '') return '—';

        $f = (float)$n;
        return (abs($f - (int)$f) < 0.00001) ? (string)(int)$f : number_format($f, 2);
    }
}

if (!function_exists('grade_from_percent')) {
    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';
    }
}

if (!function_exists('fmt_dt')) {
    function fmt_dt($date): string {
        $date = trim((string)$date);

        if ($date === '' || $date === '0000-00-00' || $date === '0000-00-00 00:00:00') {
            return '—';
        }

        $time = strtotime($date);
        return $time ? date('Y-m-d H:i', $time) : $date;
    }
}

/* ---------------- Student check ---------------- */

$student_id = (int)($GLOBALS['STUDENT_ID'] ?? ($_SESSION['student_id'] ?? 0));

if ($student_id <= 0) {
    session_destroy();
    header('Location: /elms/student/login.php');
    exit;
}

if (!sr_table_exists($mysqli, 'studentss')) {
    http_response_code(500);
    exit('studentss table not found');
}

$studentCols = "id, full_name, academic_email, center_reg_no, reference_no";

if (sr_col_exists($mysqli, 'studentss', 'lms_status')) {
    $studentCols .= ", lms_status";
}

$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'] ?? '');

$GLOBALS['STUDENT_NAME'] = $_SESSION['student_name'];
$GLOBALS['STUDENT_EMAIL'] = $_SESSION['academic_email'];
$GLOBALS['STUDENT_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;
}

/* ---------------- Query setup ---------------- */

$rows = [];
$total = 0;
$pages = 1;

$q = trim((string)($_GET['q'] ?? ''));
$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;

$hasMainSubmissions = sr_table_exists($mysqli, 'lms_submissions');
$hasAssessments = sr_table_exists($mysqli, 'lms_assessments');

if ($hasMainSubmissions && $hasAssessments) {
    $assessmentTitleCol = sr_first_col($mysqli, 'lms_assessments', ['title', 'name']) ?? 'id';
    $assessmentMaxCol = sr_first_col($mysqli, 'lms_assessments', ['max_score', 'max_mark', 'max_marks', 'total_marks', 'marks']);
    $assessmentDueCol = sr_first_col($mysqli, 'lms_assessments', ['due_at', 'due_date', 'deadline', 'available_to', 'close_at']);

    $hasWorkflow = sr_col_exists($mysqli, 'lms_submissions', 'workflow_status');
    $hasFinalScore = sr_col_exists($mysqli, 'lms_submissions', 'final_score');
    $hasMarks = sr_col_exists($mysqli, 'lms_submissions', 'marks');
    $hasScore = sr_col_exists($mysqli, 'lms_submissions', 'score');
    $hasFeedback = sr_col_exists($mysqli, 'lms_submissions', 'feedback');
    $hasLecturerFeedback = sr_col_exists($mysqli, 'lms_submissions', 'lecturer_feedback');
    $hasSubmittedAt = sr_col_exists($mysqli, 'lms_submissions', 'submitted_at');
    $hasUpdatedAt = sr_col_exists($mysqli, 'lms_submissions', 'updated_at');
    $hasReleasedAt = sr_col_exists($mysqli, 'lms_submissions', 'released_at');

    $scoreExpr = $hasFinalScore ? "s.final_score"
        : ($hasMarks ? "s.marks"
        : ($hasScore ? "s.score" : "NULL"));

    $feedbackExpr = $hasLecturerFeedback ? "s.lecturer_feedback"
        : ($hasFeedback ? "s.feedback" : "''");

    $submittedExpr = $hasSubmittedAt ? "s.submitted_at"
        : ($hasUpdatedAt ? "s.updated_at" : "NULL");

    $releasedExpr = $hasReleasedAt ? "s.released_at"
        : ($hasUpdatedAt ? "s.updated_at" : $submittedExpr);

    $maxExpr = $assessmentMaxCol ? "COALESCE(a.`{$assessmentMaxCol}`, 100)" : "100";
    $dueExpr = $assessmentDueCol ? "a.`{$assessmentDueCol}`" : "NULL";

    $where = [
        "s.student_id = ?"
    ];

    $types = "i";
    $params = [$student_id];

    if ($hasWorkflow) {
    $resultVisibility = elms_setting($mysqli, 'result_visibility', 'released_only');

    if ($resultVisibility === 'all') {
        // show all results
    } else {
        $where[] = "LOWER(COALESCE(s.workflow_status,'')) IN ('released','published')";
    }
} else {
    $where[] = "{$scoreExpr} IS NOT NULL";
}

    if ($q !== '') {
        $where[] = "(a.`{$assessmentTitleCol}` LIKE ? OR {$feedbackExpr} LIKE ?)";
        $like = "%{$q}%";
        $params[] = $like;
        $params[] = $like;
        $types .= "ss";
    }

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

    $sqlCount = "
        SELECT COUNT(*) AS n
        FROM lms_submissions s
        JOIN lms_assessments a ON a.id = s.assessment_id
        {$whereSql}
    ";

    $st = $mysqli->prepare($sqlCount);
    if ($st) {
        $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));
    }

    $select = "
        s.id AS submission_id,
        s.assessment_id,
        s.student_id,
        s.batch_id,
        {$scoreExpr} AS marks,
        {$feedbackExpr} AS feedback,
        {$submittedExpr} AS submitted_at,
        {$releasedExpr} AS released_at,
        a.`{$assessmentTitleCol}` AS assessment_title,
        {$maxExpr} AS max_marks,
        {$dueExpr} AS due_at
    ";

    if (isset($_GET['export']) && $_GET['export'] === 'csv') {
        $sqlAll = "
            SELECT {$select}
            FROM lms_submissions s
            JOIN lms_assessments a ON a.id = s.assessment_id
            {$whereSql}
            ORDER BY {$releasedExpr} 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_released_results.csv');

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

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

            $grade = ($percent !== '') ? grade_from_percent((float)$percent) : '';

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

        fclose($out);
        exit;
    }

    $sql = "
        SELECT {$select}
        FROM lms_submissions s
        JOIN lms_assessments a ON a.id = s.assessment_id
        {$whereSql}
        ORDER BY {$releasedExpr} DESC, s.id DESC
        LIMIT {$per} OFFSET {$offset}
    ";

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

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

        $st->close();
    }
}

/* ---------------- Stats ---------------- */

$releasedCount = count($rows);
$totalMarks = 0.0;
$totalOutOf = 0.0;

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

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

require_once __DIR__ . '/../includes/student_layout_top.php';
?>

<style>
    .results-summary-grid {
        display: grid;
        grid-template-columns: repeat(3, 1fr);
        gap: 16px;
        margin-bottom: 22px;
    }

    .results-summary-card {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 18px;
        padding: 18px;
        box-shadow: 0 8px 22px rgba(15,23,42,.045);
    }

    .results-summary-icon {
        width: 40px;
        height: 40px;
        border-radius: 13px;
        background: #eff6ff;
        color: #034ea2;
        display: grid;
        place-items: center;
        font-size: 1.15rem;
        margin-bottom: 12px;
    }

    .results-summary-value {
        font-size: 1.65rem;
        font-weight: 950;
        color: #111827;
        line-height: 1;
    }

    .results-summary-label {
        margin-top: 7px;
        color: #7b8190;
        font-size: .78rem;
        font-weight: 850;
    }

    .results-filter-card {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 18px;
        padding: 16px;
        box-shadow: 0 8px 22px rgba(15,23,42,.04);
        margin-bottom: 22px;
    }

    .results-shell {
    background: #fff;
    border: 1px solid #e5e7eb;
    border-radius: 14px;
    box-shadow: none;
    overflow: hidden;
}

.results-shell-head {
    padding: 14px 18px;
    border-bottom: 1px solid #e5e7eb;
    background: #fff;
    display: flex;
    align-items: center;
    justify-content: space-between;
    gap: 14px;
}

.results-shell-title {
    margin: 0;
    color: #111827;
    font-size: 1rem;
    font-weight: 900;
}

.results-shell-sub {
    color: #7b8190;
    font-size: .8rem;
    font-weight: 650;
    margin-top: 3px;
}

.results-table {
    margin: 0;
}

.results-table thead th {
    background: #f8fafc;
    color: #475569;
    font-size: 12px;
    font-weight: 900;
    text-transform: uppercase;
    letter-spacing: .05em;
    border-bottom: 1px solid #e5e7eb;
    padding: 12px 14px;
    white-space: nowrap;
}

.results-table tbody td {
    padding: 16px 14px;
    vertical-align: middle;
    border-bottom: 1px solid #eef2f7;
    color: #111827;
    font-size: .9rem;
}

.results-table tbody tr:last-child td {
    border-bottom: 0;
}

.result-assessment-title {
    font-weight: 900;
    color: #000;
    margin-bottom: 4px;
}

.result-assessment-meta {
    color: #64748b;
    font-size: .78rem;
}

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

.result-status-released {
    display: inline-flex;
    align-items: center;
    justify-content: center;
    background: #dcfce7;
    color: #15803d;
    border: 1px solid #bbf7d0;
    border-radius: 999px;
    font-size: .72rem;
    font-weight: 850;
    padding: 5px 10px;
}

.result-feedback-box {
    background: #f8fafc;
    border: 1px solid #e5e7eb;
    border-radius: 14px;
    padding: 10px 14px;
    min-width: 180px;
    color: #334155;
}

.result-view-btn {
    border: 1px solid #2563eb;
    color: #2563eb;
    background: #fff;
    border-radius: 999px;
    font-weight: 850;
    padding: 7px 16px;
    text-decoration: none;
}

.result-view-btn:hover {
    background: #2563eb;
    color: #fff;
}

.results-pagination {
    border-top: 1px solid #eef2f7;
    padding: 14px 16px;
    background: #fff;
}

@media (max-width: 1200px) {
    .results-table {
        min-width: 1180px;
    }
}

    @media (max-width: 1250px) {
        .result-row-clean {
            grid-template-columns: minmax(0, 1fr) 120px 120px 100px 70px 80px 42px;
        }

        .result-status-wrap {
            display: none;
        }
    }

    @media (max-width: 900px) {
        .results-summary-grid {
            grid-template-columns: 1fr;
        }

        .result-row-clean {
            grid-template-columns: 1fr;
            gap: 8px;
            padding: 16px 0;
        }

        .result-open-btn {
            width: 100%;
            border-radius: 12px;
            height: 38px;
        }

        .results-shell-head {
            display: block;
        }
    }
</style>

<div class="student-page-tabs">
    <a href="/elms/student/results.php" class="active">Results</a>
</div>

<div class="results-summary-grid">
    <div class="results-summary-card">
        <div class="results-summary-icon">
            <i class="bi bi-award"></i>
        </div>
        <div class="results-summary-value"><?= (int)$total ?></div>
        <div class="results-summary-label">Released Results</div>
    </div>

    <div class="results-summary-card">
        <div class="results-summary-icon">
            <i class="bi bi-graph-up-arrow"></i>
        </div>
        <div class="results-summary-value"><?= $avgPercent > 0 ? h($avgPercent . '%') : '—' ?></div>
        <div class="results-summary-label">Average Percentage</div>
    </div>

    <div class="results-summary-card">
        <div class="results-summary-icon">
            <i class="bi bi-shield-check"></i>
        </div>
        <div class="results-summary-value">Released</div>
        <div class="results-summary-label">Result Status</div>
    </div>
</div>

<div class="results-filter-card">
    <form method="get" class="row g-3 align-items-end">
        <div class="col-lg-6">
            <label class="form-label fw-bold">Search assessment</label>
            <input
                type="text"
                name="q"
                value="<?= h($q) ?>"
                class="form-control"
                placeholder="Type assessment title or feedback..."
            >
        </div>

        <div class="col-lg-2">
            <label class="form-label fw-bold">Per page</label>
            <select name="per" class="form-select">
                <?php foreach ([20, 50, 100, 200] as $opt): ?>
                    <option value="<?= $opt ?>" <?= $per === $opt ? 'selected' : '' ?>>
                        <?= $opt ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="col-lg-4 d-flex flex-wrap gap-2">
            <button class="btn btn-primary rounded-pill fw-bold px-4">
                <i class="bi bi-funnel me-1"></i>
                Apply
            </button>

            <a href="/elms/student/results.php" class="btn btn-outline-secondary rounded-pill fw-bold px-4">
                Reset
            </a>

            <a class="btn btn-outline-success rounded-pill fw-bold px-4"
               href="?<?= h(http_build_query(['q' => $q, 'per' => $per, 'export' => 'csv'])) ?>">
                <i class="bi bi-download me-1"></i>
                CSV
            </a>
        </div>
    </form>
</div>

<?php if (!$hasMainSubmissions || !$hasAssessments): ?>

    <div class="result-empty">
        <i class="bi bi-exclamation-triangle"></i>
        <h5 class="fw-bold">Required tables not found</h5>
        <p class="mb-0">Need lms_submissions and lms_assessments tables.</p>
    </div>

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

    <div class="result-empty">
        <i class="bi bi-inbox"></i>
        <h5 class="fw-bold">No released results yet</h5>
        <p class="mb-0">
            Your results will appear here after lecturer marking and coordinator/admin release.
        </p>
    </div>

<?php else: ?>

    <div class="results-shell">
    <div class="results-shell-head">
        <div>
            <h2 class="results-shell-title">Results History</h2>
            <div class="results-shell-sub">
                Showing released results only.
            </div>
        </div>

        <span class="badge rounded-pill text-bg-light px-3 py-2">
            <?= number_format($total) ?> record(s)
        </span>
    </div>

    <div class="table-responsive">
        <table class="table align-middle results-table">
            <thead>
                <tr>
                    <th>Assessment</th>
                    <th>Submitted</th>
                    <th>Released</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);

                        $percent = null;
                        $grade = '—';

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

                        $feedback = trim((string)($r['feedback'] ?? ''));
                    ?>

                    <tr>
                        <td>
                            <div class="result-assessment-title">
                                <?= h($r['assessment_title'] ?? ('Assessment #' . $r['assessment_id'])) ?>
                            </div>

                            <div class="result-assessment-meta">
                                Assessment ID: <?= (int)$r['assessment_id'] ?>

                                <?php if (!empty($r['due_at'])): ?>
                                    <span class="mx-1">•</span>
                                    Due <?= h(fmt_dt($r['due_at'])) ?>
                                <?php endif; ?>
                            </div>
                        </td>

                        <td>
                            <?= h(fmt_dt($r['submitted_at'] ?? '')) ?>
                        </td>

                        <td>
                            <?= h(fmt_dt($r['released_at'] ?? '')) ?>
                        </td>

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

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

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

                        <td>
                            <?php if ($percent !== null): ?>
                                <span class="result-grade-badge">
                                    <?= h($grade) ?>
                                </span>
                            <?php else: ?>
                                <span class="text-muted">—</span>
                            <?php endif; ?>
                        </td>

                        <td>
                            <span class="result-status-released">
                                Released
                            </span>
                        </td>

                        <td>
                            <?php if ($feedback !== ''): ?>
                                <div class="result-feedback-box">
                                    <?= h(mb_strimwidth(strip_tags($feedback), 0, 120, '...')) ?>
                                </div>
                            <?php else: ?>
                                <span class="text-muted">No feedback</span>
                            <?php endif; ?>
                        </td>

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

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

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

        <button class="btn btn-outline-secondary btn-sm rounded-pill px-3"
                name="page"
                value="1"
                <?= $page <= 1 ? 'disabled' : '' ?>>
            First
        </button>

        <button class="btn btn-outline-secondary btn-sm rounded-pill px-3"
                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 rounded-pill px-3"
                name="page"
                value="<?= min($pages, $page + 1) ?>"
                <?= $page >= $pages ? 'disabled' : '' ?>>
            Next
        </button>

        <button class="btn btn-outline-secondary btn-sm rounded-pill px-3"
                name="page"
                value="<?= (int)$pages ?>"
                <?= $page >= $pages ? 'disabled' : '' ?>>
            Last
        </button>
    </form>
</div>

<?php endif; ?>

<?php require_once __DIR__ . '/../includes/student_layout_bottom.php'; ?>