<?php
// /elms/admin/reports/quiz_report.php
declare(strict_types=1);

define('APP_ROOT', dirname(__DIR__, 2));

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

require_once APP_ROOT . '/includes/admin_auth.php';
require_once APP_ROOT . '/includes/db.php';

$mysqli = $mysqli ?? ($conn ?? null);
if (!$mysqli instanceof mysqli) {
    http_response_code(500);
    exit('Database connection not found.');
}
$mysqli->set_charset('utf8mb4');

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

function qr_table_exists(mysqli $db, string $table): bool {
    $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1");
    if (!$st) return false;
    $st->bind_param('s', $table);
    $st->execute();
    return (bool)$st->get_result()->fetch_row();
}

function qr_col_exists(mysqli $db, string $table, string $col): bool {
    if (!qr_table_exists($db, $table)) return false;
    $st = $db->prepare("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1");
    if (!$st) return false;
    $st->bind_param('ss', $table, $col);
    $st->execute();
    return (bool)$st->get_result()->fetch_row();
}

function qr_table(mysqli $db, array $tables): string {
    foreach ($tables as $t) {
        if (qr_table_exists($db, $t)) return $t;
    }
    return '';
}

function qr_first_col(mysqli $db, string $table, array $cols): string {
    foreach ($cols as $c) {
        if ($table !== '' && qr_col_exists($db, $table, $c)) return $c;
    }
    return '';
}

function qr_num($v): float {
    if ($v === null || $v === '') return 0.0;
    return is_numeric($v) ? (float)$v : 0.0;
}

$studentTable  = qr_table($mysqli, ['studentss', 'students']);
$attemptTable  = qr_table($mysqli, ['lms_quiz_attempts', 'quiz_attempts']);
$quizTable     = qr_table($mysqli, ['lms_quizzes', 'quizzes']);
$questionTable = qr_table($mysqli, ['lms_quiz_questions', 'quiz_questions']);
$courseTable   = qr_table($mysqli, ['courses']);

$courseId  = (int)($_GET['course_id'] ?? 0);
$quizId    = (int)($_GET['quiz_id'] ?? 0);
$finalOnly = isset($_GET['final_only']) ? (int)$_GET['final_only'] : 0;
$q         = trim((string)($_GET['q'] ?? ''));

$rows = [];
$courses = [];
$quizzes = [];

$courseLabel = $courseTable ? qr_first_col($mysqli, $courseTable, ['course_name', 'name', 'title', 'code']) : '';
$quizTitle   = $quizTable ? qr_first_col($mysqli, $quizTable, ['title', 'quiz_title', 'name']) : '';
$quizCourse  = $quizTable ? qr_first_col($mysqli, $quizTable, ['course_id']) : '';

if ($courseTable && $courseLabel) {
    $res = $mysqli->query("SELECT id, `$courseLabel` AS label FROM `$courseTable` ORDER BY `$courseLabel` ASC");
    if ($res) {
        while ($r = $res->fetch_assoc()) {
            $courses[] = $r;
        }
    }
}

if ($quizTable && $quizTitle) {
    $whereQuiz = [];
    $typesQuiz = '';
    $paramsQuiz = [];

    if ($courseId > 0 && $quizCourse) {
        $whereQuiz[] = "`$quizCourse` = ?";
        $typesQuiz .= 'i';
        $paramsQuiz[] = $courseId;
    }

    if ($finalOnly === 1) {
        $whereQuiz[] = "LOWER(`$quizTitle`) LIKE '%final%'";
    }

    $whereQuizSql = $whereQuiz ? 'WHERE ' . implode(' AND ', $whereQuiz) : '';
    $sqlQuiz = "SELECT id, `$quizTitle` AS title FROM `$quizTable` $whereQuizSql ORDER BY `$quizTitle` ASC LIMIT 1000";

    $stq = $mysqli->prepare($sqlQuiz);
    if ($stq) {
        if ($typesQuiz !== '') $stq->bind_param($typesQuiz, ...$paramsQuiz);
        $stq->execute();
        $quizzes = $stq->get_result()->fetch_all(MYSQLI_ASSOC);
    }
}

if ($studentTable && $attemptTable && $quizTable) {
    $sidCol  = qr_first_col($mysqli, $studentTable, ['id', 'student_id']);
    $nameCol = qr_first_col($mysqli, $studentTable, ['full_name', 'student_name', 'name', 'first_name']);
    $regCol  = qr_first_col($mysqli, $studentTable, ['center_reg_no', 'reg_no', 'registration_no', 'student_no', 'course_reg_no']);
    $refCol  = qr_first_col($mysqli, $studentTable, ['reference_no', 'ref_no', 'nec_no', 'student_ref', 'ref']);
    $nicCol  = qr_first_col($mysqli, $studentTable, ['nic_passport', 'nic', 'nic_no', 'nic_number', 'national_id', 'identity_no', 'id_no', 'passport_no']);

    $aId      = qr_first_col($mysqli, $attemptTable, ['id', 'attempt_id']);
    $aStudent = qr_first_col($mysqli, $attemptTable, ['student_id', 'user_id']);
    $aQuiz    = qr_first_col($mysqli, $attemptTable, ['quiz_id', 'lms_quiz_id']);
    $aAttempt = qr_first_col($mysqli, $attemptTable, ['attempt_no', 'attempt_number']);
    $aScore   = qr_first_col($mysqli, $attemptTable, ['score', 'marks', 'obtained_marks', 'correct_count', 'points']);
    $aTotal   = qr_first_col($mysqli, $attemptTable, ['total', 'total_marks', 'max_score', 'max_marks', 'out_of', 'question_count']);
    $aPercent = qr_first_col($mysqli, $attemptTable, ['percentage', 'percent']);
    $aPassed  = qr_first_col($mysqli, $attemptTable, ['passed', 'status', 'result', 'pass_status']);
    $aStarted = qr_first_col($mysqli, $attemptTable, ['started_at', 'start_time', 'created_at']);
    $aFinished= qr_first_col($mysqli, $attemptTable, ['finished_at', 'submitted_at', 'attempted_at', 'updated_at', 'created_at', 'date_taken']);

    $qPassScore = qr_first_col($mysqli, $quizTable, ['pass_score', 'pass_mark', 'passing_score']);

    if ($sidCol && $aStudent && $aQuiz && $quizTitle) {
        $where = [];
        $types = '';
        $params = [];

        if ($quizId > 0) {
            $where[] = "a.`$aQuiz` = ?";
            $types .= 'i';
            $params[] = $quizId;
        }

        if ($courseId > 0 && $quizCourse) {
            $where[] = "q.`$quizCourse` = ?";
            $types .= 'i';
            $params[] = $courseId;
        }

        if ($finalOnly === 1) {
            $where[] = "LOWER(q.`$quizTitle`) LIKE '%final%'";
        }

        if ($q !== '') {
            $search = [];
            foreach ([$nameCol, $regCol, $refCol, $nicCol] as $c) {
                if ($c) {
                    $search[] = "s.`$c` LIKE ?";
                    $types .= 's';
                    $params[] = '%' . $q . '%';
                }
            }
            if ($search) {
                $where[] = '(' . implode(' OR ', $search) . ')';
            }
        }

        $whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
        $dateExpr = $aFinished ? "COALESCE(a.`$aFinished`, a.`$aId`)" : "a.`$aId`";

        $select = "
            a.*,
            s.`$sidCol` AS student_id_real,
            " . ($nameCol ? "s.`$nameCol`" : "''") . " AS student_name,
            " . ($regCol ? "s.`$regCol`" : "''") . " AS reg_no,
            " . ($refCol ? "s.`$refCol`" : "''") . " AS reference_no,
            " . ($nicCol ? "s.`$nicCol`" : "''") . " AS nic_no,
            q.`$quizTitle` AS quiz_title
            " . ($qPassScore ? ", q.`$qPassScore` AS quiz_pass_score" : ", NULL AS quiz_pass_score") . "
        ";

        $sql = "
            SELECT $select
            FROM `$attemptTable` a
            JOIN `$studentTable` s ON s.`$sidCol` = a.`$aStudent`
            JOIN `$quizTable` q ON q.id = a.`$aQuiz`
            $whereSql
            ORDER BY a.`$aQuiz` ASC, a.`$aStudent` ASC, $dateExpr DESC, a.`$aId` DESC
            LIMIT 5000
        ";

        $st = $mysqli->prepare($sql);
        if ($st) {
            if ($types !== '') $st->bind_param($types, ...$params);
            $st->execute();
            $all = $st->get_result()->fetch_all(MYSQLI_ASSOC);

            $seen = [];
            foreach ($all as $r) {
                $key = (int)($r[$aQuiz] ?? 0) . '_' . (int)($r[$aStudent] ?? 0);
                if (isset($seen[$key])) continue;
                $seen[$key] = true;

                $qid = (int)($r[$aQuiz] ?? 0);
                $score = $aScore ? qr_num($r[$aScore] ?? 0) : 0;
                $total = $aTotal ? qr_num($r[$aTotal] ?? 0) : 0;

                if ($total <= 0 && $questionTable && $qid > 0) {
                    $qQuizCol  = qr_first_col($mysqli, $questionTable, ['quiz_id', 'lms_quiz_id']);
                    $qPointCol = qr_first_col($mysqli, $questionTable, ['points', 'mark', 'marks', 'score']);

                    if ($qQuizCol) {
                        if ($qPointCol) {
                            $st2 = $mysqli->prepare("SELECT COALESCE(SUM(`$qPointCol`),0) AS total_points FROM `$questionTable` WHERE `$qQuizCol` = ?");
                        } else {
                            $st2 = $mysqli->prepare("SELECT COUNT(*) AS total_points FROM `$questionTable` WHERE `$qQuizCol` = ?");
                        }

                        if ($st2) {
                            $st2->bind_param('i', $qid);
                            $st2->execute();
                            $x = $st2->get_result()->fetch_assoc();
                            $total = qr_num($x['total_points'] ?? 0);
                        }
                    }
                }

                $percent = 0;
                if ($aPercent && isset($r[$aPercent]) && is_numeric($r[$aPercent])) {
                    $percent = round((float)$r[$aPercent], 2);
                } elseif ($total > 0) {
                    $percent = round(($score / $total) * 100, 2);
                }

                $rawPass = $aPassed ? strtolower((string)($r[$aPassed] ?? '')) : '';
                $passScore = isset($r['quiz_pass_score']) && is_numeric($r['quiz_pass_score']) ? (float)$r['quiz_pass_score'] : null;

                if ($rawPass === '1' || $rawPass === 'pass' || $rawPass === 'passed' || $rawPass === 'yes') {
                    $passed = 'PASS';
                } elseif ($rawPass === '0' || str_contains($rawPass, 'fail') || $rawPass === 'no') {
                    $passed = 'FAIL';
                } elseif ($passScore !== null) {
                    $passed = $score >= $passScore ? 'PASS' : 'FAIL';
                } else {
                    $passed = $percent >= 50 ? 'PASS' : 'FAIL';
                }

                $rows[] = [
                    'attempt_id'   => (int)($r[$aId] ?? 0),
                    'student_id'   => (int)($r['student_id_real'] ?? 0),
                    'student'      => $r['student_name'] ?: ('Student #' . ($r['student_id_real'] ?? '')),
                    'reg_no'       => $r['reg_no'] ?? '',
                    'reference_no' => $r['reference_no'] ?? '',
                    'nic_no'       => $r['nic_no'] ?? '',
                    'quiz_id'      => $qid,
                    'quiz'         => $r['quiz_title'] ?? ('Quiz #' . $qid),
                    'attempt_no'   => $aAttempt ? ($r[$aAttempt] ?? '') : '',
                    'score'        => $score,
                    'total'        => $total,
                    'percent'      => $percent,
                    'passed'       => $passed,
                    'started_at'   => $aStarted ? ($r[$aStarted] ?? '') : '',
                    'finished_at'  => $aFinished ? ($r[$aFinished] ?? '') : '',
                ];
            }
        }
    }
}

if (isset($_GET['export']) && $_GET['export'] === 'csv') {
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="quiz_report.csv"');

    $out = fopen('php://output', 'w');
    fputcsv($out, ['Attempt ID', 'Student ID', 'Student', 'Reg No', 'Reference No', 'NIC', 'Quiz ID', 'Quiz', 'Attempt #', 'Score', 'Total', 'Percent', 'Result', 'Started', 'Finished']);

    foreach ($rows as $r) {
        fputcsv($out, [
            $r['attempt_id'],
            $r['student_id'],
            $r['student'],
            $r['reg_no'],
            $r['reference_no'],
            $r['nic_no'],
            $r['quiz_id'],
            $r['quiz'],
            $r['attempt_no'],
            $r['score'],
            $r['total'],
            $r['percent'],
            $r['passed'],
            $r['started_at'],
            $r['finished_at'],
        ]);
    }

    fclose($out);
    exit;
}

$totalAttempts = count($rows);
$avgPercent = $totalAttempts ? round(array_sum(array_column($rows, 'percent')) / $totalAttempts, 2) : 0;
$passCount = 0;
$failCount = 0;

foreach ($rows as $r) {
    $r['passed'] === 'PASS' ? $passCount++ : $failCount++;
}

$PAGE_TITLE = 'Quiz Report';
$ACTIVE = 'reports';

require_once APP_ROOT . '/includes/admin_layout_top.php';
?>

<style>
.qr-wrap{width:100%;max-width:none;margin:0;padding:0 18px 28px}
.qr-head{display:flex;align-items:flex-start;justify-content:space-between;gap:12px;flex-wrap:wrap;margin:0 0 14px}
.qr-title{font-size:24px;font-weight:900;color:#0f172a;margin:0}
.qr-sub{color:#64748b;margin:4px 0 0;font-size:13px}
.qr-card{background:#fff;border:1px solid #dbe3ef;border-radius:16px;box-shadow:0 8px 22px rgba(15,23,42,.035)}
.qr-filter{padding:16px;margin-bottom:14px}
.qr-filter .form-control,.qr-filter .form-select,.qr-filter .btn{height:42px;border-radius:8px}
.qr-grid{display:grid;grid-template-columns:repeat(4,1fr);gap:8px;margin:0 0 14px}
.qr-kpi{padding:16px 15px;border-radius:14px;background:#fff;border:1px solid #dbe3ef;box-shadow:0 8px 20px rgba(15,23,42,.035)}
.qr-kpi .num{font-size:24px;line-height:1;font-weight:950;color:#020617;margin-bottom:8px}
.qr-kpi .lbl{font-size:13px;color:#334155;font-weight:700}
.qr-table-card{padding:16px;margin-bottom:14px}
.qr-table-card .table{margin-bottom:0}
.qr-table-card .table thead th{font-size:11px;text-transform:uppercase;letter-spacing:.04em;color:#475569;background:#f8fafc;border-bottom:1px solid #dbe3ef;padding:10px 8px;white-space:nowrap}
.qr-table-card .table tbody td{padding:12px 8px;vertical-align:middle;border-bottom:1px solid #e5e7eb}
.badge-soft-success{background:#dcfce7;color:#166534;padding:5px 10px;border-radius:999px;font-size:11px;font-weight:800}
.badge-soft-danger{background:#fee2e2;color:#991b1b;padding:5px 10px;border-radius:999px;font-size:11px;font-weight:800}
@media(max-width:1100px){.qr-grid{grid-template-columns:repeat(2,1fr)}}
@media(max-width:650px){.qr-wrap{padding:0 10px 18px}.qr-grid{grid-template-columns:1fr}}
</style>

<div class="qr-wrap">
    <div class="qr-head">
        <div>
            <h1 class="qr-title">Quiz Report</h1>
            <p class="qr-sub">Latest quiz attempt per student with final-only filter, attempt details and CSV export.</p>
        </div>
        <a class="btn btn-success" href="?<?= h(http_build_query(array_merge($_GET, ['export' => 'csv']))) ?>">Export CSV</a>
    </div>

    <div class="qr-card qr-filter">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-lg-3">
                <label class="form-label fw-bold">Course</label>
                <select name="course_id" class="form-select" onchange="this.form.submit()">
                    <option value="0">All Courses</option>
                    <?php foreach ($courses as $c): ?>
                        <option value="<?= (int)$c['id'] ?>" <?= (int)$c['id'] === $courseId ? 'selected' : '' ?>>
                            <?= h($c['label']) ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-lg-3">
                <label class="form-label fw-bold">Quiz</label>
                <select name="quiz_id" class="form-select">
                    <option value="0">All Quizzes</option>
                    <?php foreach ($quizzes as $z): ?>
                        <option value="<?= (int)$z['id'] ?>" <?= (int)$z['id'] === $quizId ? 'selected' : '' ?>>
                            <?= h($z['title']) ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-lg-2">
                <label class="form-label fw-bold">Final Only?</label>
                <select name="final_only" class="form-select">
                    <option value="0" <?= $finalOnly === 0 ? 'selected' : '' ?>>No</option>
                    <option value="1" <?= $finalOnly === 1 ? 'selected' : '' ?>>Yes</option>
                </select>
            </div>

            <div class="col-lg-3">
                <label class="form-label fw-bold">Search Student</label>
                <input type="text" name="q" class="form-control" value="<?= h($q) ?>" placeholder="Name / NEC / Reference / NIC">
            </div>

            <div class="col-lg-1 d-grid">
                <button class="btn btn-primary">Filter</button>
            </div>
        </form>
    </div>

    <div class="qr-grid">
        <div class="qr-kpi"><div class="num"><?= (int)$totalAttempts ?></div><div class="lbl">Latest Attempts</div></div>
        <div class="qr-kpi"><div class="num"><?= h(number_format($avgPercent, 1)) ?>%</div><div class="lbl">Average %</div></div>
        <div class="qr-kpi"><div class="num"><?= (int)$passCount ?></div><div class="lbl">Pass Count</div></div>
        <div class="qr-kpi"><div class="num"><?= (int)$failCount ?></div><div class="lbl">Fail Count</div></div>
    </div>

    <div class="qr-card qr-table-card">
        <div class="table-responsive">
            <table class="table table-hover align-middle">
                <thead>
                    <tr>
                        <th>Student</th>
                        <th>Reg No</th>
                        <th>Reference No</th>
                        <th>NIC</th>
                        <th>Quiz</th>
                        <th class="text-center">Attempt #</th>
                        <th class="text-end">Score</th>
                        <th class="text-end">%</th>
                        <th>Result</th>
                        <th>Started</th>
                        <th>Finished</th>
                        
                    </tr>
                </thead>
                <tbody>
                <?php if (!$rows): ?>
                    <tr>
                        
                    </tr>
                <?php else: ?>
                    <?php foreach ($rows as $r): ?>
                        <tr>
                            <td class="fw-bold">
                                <?= h($r['student']) ?>
                                <span class="text-muted">#<?= (int)$r['student_id'] ?></span>
                            </td>
                            <td><?= h($r['reg_no'] ?: '-') ?></td>
                            <td><?= h($r['reference_no'] ?: '-') ?></td>
                            <td><?= h($r['nic_no'] ?: '-') ?></td>
                            <td>
                                <?= h($r['quiz']) ?>
                                <span class="text-muted">#<?= (int)$r['quiz_id'] ?></span>
                            </td>
                            <td class="text-center"><?= h($r['attempt_no'] !== '' ? $r['attempt_no'] : '-') ?></td>
                            <td class="text-end"><?= h($r['score']) ?> / <?= h($r['total']) ?></td>
                            <td class="text-end"><?= h(number_format((float)$r['percent'], 2)) ?>%</td>
                            <td>
                                <span class="badge <?= $r['passed'] === 'PASS' ? 'badge-soft-success' : 'badge-soft-danger' ?>">
                                    <?= h($r['passed']) ?>
                                </span>
                            </td>
                            <td class="text-muted small"><?= h($r['started_at'] ?: '-') ?></td>
                            <td class="text-muted small"><?= h($r['finished_at'] ?: '-') ?></td>
                            
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<?php require_once APP_ROOT . '/includes/admin_layout_bottom.php'; ?>