<?php

declare(strict_types=1);

namespace App\Controllers;

use App\Core\Controller;

/**
 * Branch Exam Portal Controller (public-facing student area)
 *
 * URL prefix: /branch-exam/*
 *
 * Flow:
 *   1. Student visits /branch-exam  →  login form (enrollment_no / email + password)
 *   2. Authenticated → /branch-exam/dashboard  →  list of active, published exams
 *      for the student's branch that are inside their validity window
 *   3. /branch-exam/take/{id}  →  exam questions (bilingual: EN / HI / both)
 *   4. /branch-exam/submit/{id}  →  grades submission
 *      - If exam.show_result = 1  →  redirect to result page with score
 *      - If exam.show_result = 0  →  show "Thank you, results will be published later"
 *   5. /branch-exam/result/{attemptId}  →  score page (only if allowed by show_result)
 *
 * A student is admitted to exactly ONE branch (students.branch_id). They can
 * only see exams created by their own branch (exams.branch_id = student.branch_id).
 *
 * Login uses the standard users table — only role='student' is accepted here.
 */
class BranchExamPortalController extends Controller
{
    /**
     * Show the login form. If already logged in as a student, go to dashboard.
     */
    public function loginForm(): void
    {
        if (isLoggedIn() && isStudent()) {
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Also list active branch exams publicly so visitors can see what's available
        $upcoming = $this->fetchPortalExams(false);

        $this->view('branch_exam/login', [
            'title'      => 'Branch Exam Portal - Gmind Institute',
            'currentNav' => 'branch-exam',
            'upcoming'   => $upcoming,
        ]);
    }

    /**
     * Process student login.
     * Accepts login_id = enrollment_no OR email OR admit_cards.login_username,
     * plus password.
     *
     * This method is FAULT-TOLERANT: if the admit_cards table doesn't have
     * the login_username column (e.g. the SQL patch hasn't been run yet),
     * it falls back to a simpler query without that subquery, so login still
     * works. Any SQL error is logged to error_log with the actual message
     * (instead of being silently swallowed), and the user sees the actual
     * error message in APP_DEBUG mode.
     */
    public function login(): void
    {
        $this->validateCsrf();

        $loginId  = trim($_POST['login_id'] ?? '');
        $password = $_POST['password'] ?? '';

        if ($loginId === '' || $password === '') {
            flash('error', 'Please enter your Enrollment No / Email and password.');
            redirect(BASE_PATH . '/branch-exam');
        }

        $isEmail = filter_var($loginId, FILTER_VALIDATE_EMAIL) !== false;

        // Check whether admit_cards table has the login_username column.
        // If not (e.g. SQL patch not run yet), we skip the custom-username
        // subquery and only match by enrollment_no.
        $hasLoginUsernameCol = $this->columnExists('admit_cards', 'login_username');

        try {
            if ($isEmail) {
                // Login by email
                $stmt = $this->db->prepare(
                    "SELECT u.*, s.id AS student_id, s.enrollment_no, s.branch_id AS student_branch_id,
                            s.status AS student_status, b.branch_code, b.name AS branch_name
                     FROM users u
                     INNER JOIN students s ON s.user_id = u.id
                     INNER JOIN branches b ON s.branch_id = b.id
                     WHERE u.email = :lid AND u.role = 'student'
                     LIMIT 1"
                );
                $stmt->execute([':lid' => $loginId]);
            } elseif ($hasLoginUsernameCol) {
                // Login by enrollment_no OR admit_cards.login_username (custom username set by branch admin)
                // NOTE: We use EXISTS instead of IN (SELECT ... LIMIT 1) because older MySQL versions
                // (MySQL 5.7 and MariaDB 10.4) don't support LIMIT inside IN/ALL/ANY/SOME subqueries.
                $stmt = $this->db->prepare(
                    "SELECT u.*, s.id AS student_id, s.enrollment_no, s.branch_id AS student_branch_id,
                            s.status AS student_status, b.branch_code, b.name AS branch_name
                     FROM users u
                     INNER JOIN students s ON s.user_id = u.id
                     INNER JOIN branches b ON s.branch_id = b.id
                     WHERE u.role = 'student' AND (
                         s.enrollment_no = :lid
                         OR EXISTS (SELECT 1 FROM admit_cards ac WHERE ac.student_id = s.id AND ac.login_username = :lid2)
                     )
                     LIMIT 1"
                );
                $stmt->execute([':lid' => $loginId, ':lid2' => $loginId]);
            } else {
                // Fallback: login by enrollment_no only (no admit_cards.login_username column)
                $stmt = $this->db->prepare(
                    "SELECT u.*, s.id AS student_id, s.enrollment_no, s.branch_id AS student_branch_id,
                            s.status AS student_status, b.branch_code, b.name AS branch_name
                     FROM users u
                     INNER JOIN students s ON s.user_id = u.id
                     INNER JOIN branches b ON s.branch_id = b.id
                     WHERE u.role = 'student' AND s.enrollment_no = :lid
                     LIMIT 1"
                );
                $stmt->execute([':lid' => $loginId]);
            }
            $user = $stmt->fetch() ?: null;
        } catch (\Throwable $e) {
            // Log the actual error so we can diagnose
            error_log('BranchExamPortal login failed: ' . $e->getMessage() . "\n" . $e->getTraceAsString());

            // In debug mode, show the actual error to help diagnose
            if (defined('APP_DEBUG') && APP_DEBUG) {
                flash('error', 'Login query failed: ' . $e->getMessage());
            } else {
                flash('error', 'Login is temporarily unavailable. Please contact your branch administrator.');
            }
            redirect(BASE_PATH . '/branch-exam');
        }

        if (!$user || !password_verify($password, $user['password'])) {
            flash('error', 'Invalid credentials. Please check your Enrollment No / Email and password.');
            redirect(BASE_PATH . '/branch-exam');
        }
        if ((int)($user['is_active'] ?? 1) !== 1 || ($user['status'] ?? 'active') !== 'active') {
            flash('error', 'Your account is not active. Please contact your branch.');
            redirect(BASE_PATH . '/branch-exam');
        }
        if (($user['student_status'] ?? '') === 'dropped') {
            flash('error', 'Your admission has been discontinued. Please contact your branch.');
            redirect(BASE_PATH . '/branch-exam');
        }
        if (empty($user['student_branch_id'])) {
            flash('error', 'You are not assigned to a branch. Please contact administration.');
            redirect(BASE_PATH . '/branch-exam');
        }

        // Log the student in
        \login([
            'id'          => $user['id'],
            'name'        => $user['name'],
            'email'       => $user['email'],
            'role'        => $user['role'],
            'avatar'      => $user['photo'] ?? null,
            'branch_id'   => $user['student_branch_id'],
            'branch_code' => $user['branch_code'] ?? null,
        ]);
        // Stash student_id in session for easy access
        $_SESSION['student_id'] = (int) $user['student_id'];
        $_SESSION['student_enrollment_no'] = $user['enrollment_no'];

        flash('success', 'Welcome, ' . htmlspecialchars($user['name'], ENT_QUOTES, 'UTF-8') . '! You are now logged in to the Branch Exam portal.');
        redirect(BASE_PATH . '/branch-exam/dashboard');
    }

    /**
     * Check if a column exists in a table (safety net for older DB schemas).
     * Borrowed from BranchBaseController — duplicated here so this controller
     * (which extends App\Core\Controller, not BranchBaseController) can use it.
     */
    private function columnExists(string $table, string $column): bool
    {
        try {
            $stmt = $this->db->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND COLUMN_NAME = :column"
            );
            $stmt->execute([':table' => $table, ':column' => $column]);
            return (int) $stmt->fetchColumn() > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Logout — clears session and returns to login page.
     */
    public function logout(): void
    {
        $_SESSION = [];
        if (ini_get('session.use_cookies')) {
            $p = session_get_cookie_params();
            setcookie(session_name(), '', time() - 42000, $p['path'], $p['domain'], $p['secure'], $p['httponly']);
        }
        session_destroy();
        redirect(BASE_PATH . '/branch-exam');
    }

    /**
     * Student dashboard — lists their branch's active, published exams that
     * are inside their validity window. Also shows upcoming and past exams.
     */
    public function dashboard(): void
    {
        $this->requireStudent();

        $studentId = (int)($_SESSION['student_id'] ?? 0);
        $branchId  = (int)($_SESSION['branch_id'] ?? 0);
        $user      = getCurrentUser();

        if ($studentId <= 0 || $branchId <= 0) {
            flash('error', 'Student profile not found.');
            redirect(BASE_PATH . '/branch-exam');
        }

        $exams = $this->fetchPortalExams(true, $branchId);

        // Categorize: live / upcoming / past
        $now = time();
        $live = []; $upcoming = []; $past = []; $alwaysAvailable = [];

        foreach ($exams as $e) {
            $attemptCount = $this->countAttempts((int)$e['id'], $studentId);
            $e['attempt_count'] = $attemptCount;
            $e['already_taken'] = $attemptCount > 0;

            $hasSchedule = !empty($e['start_time']) || !empty($e['end_time']);
            if (!$hasSchedule) {
                $alwaysAvailable[] = $e;
                continue;
            }
            $st = !empty($e['start_time']) ? strtotime($e['start_time']) : 0;
            $et = !empty($e['end_time'])   ? strtotime($e['end_time'])   : PHP_INT_MAX;

            if ($now < $st)        $upcoming[] = $e;
            elseif ($now > $et)    $past[] = $e;
            else                   $live[] = $e;
        }

        $this->view('branch_exam/dashboard', [
            'title'           => 'My Exams - Branch Exam Portal',
            'currentNav'      => 'branch-exam',
            'userName'        => $user['name'] ?? '',
            'enrollmentNo'    => $_SESSION['student_enrollment_no'] ?? '',
            'branchCode'      => $_SESSION['branch_code'] ?? '',
            'live'            => $live,
            'upcoming'        => $upcoming,
            'past'            => $past,
            'alwaysAvailable' => $alwaysAvailable,
        ]);
    }

    /**
     * Take an exam — display all questions (bilingual) for the student to answer.
     */
    public function take(int $id): void
    {
        $this->requireStudent();
        $studentId = (int)($_SESSION['student_id'] ?? 0);
        $branchId  = (int)($_SESSION['branch_id'] ?? 0);
        $user      = getCurrentUser();

        $exam = $this->fetchExamForPortal($id, $branchId);
        if (!$exam) {
            flash('error', 'Exam not found or not available.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Check validity window
        $now = time();
        $st = !empty($exam['start_time']) ? strtotime($exam['start_time']) : 0;
        $et = !empty($exam['end_time'])   ? strtotime($exam['end_time'])   : PHP_INT_MAX;
        $hasSchedule = !empty($exam['start_time']) || !empty($exam['end_time']);
        if ($hasSchedule && ($now < $st || $now > $et)) {
            flash('error', 'This exam is not available right now. Please check the schedule.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Check attempt limit (1 attempt per exam in branch portal)
        $attemptCount = $this->countAttempts($id, $studentId);
        if ($attemptCount > 0) {
            flash('error', 'You have already taken this exam. Multiple attempts are not allowed.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Fetch questions
        $stmt = $this->db->prepare("SELECT * FROM exam_questions WHERE exam_id = :eid ORDER BY id ASC");
        $stmt->execute([':eid' => $id]);
        $questions = $stmt->fetchAll() ?: [];

        if (empty($questions)) {
            flash('error', 'This exam has no questions yet. Please contact your branch.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Strip correct_answer before sending to the view
        $safeQuestions = array_map(function (array $q): array {
            unset($q['correct_answer']);
            return $q;
        }, $questions);

        $this->view('branch_exam/take', [
            'title'      => $exam['title'] . ' - Branch Exam',
            'currentNav' => 'branch-exam',
            'exam'       => $exam,
            'questions'  => $safeQuestions,
            'userName'   => $user['name'] ?? '',
            'csrfToken'  => generateCsrfToken(),
        ]);
    }

    /**
     * Process exam submission.
     * - Grade answers
     * - Store exam_result
     * - If show_result = 1 → redirect to /branch-exam/result/{attemptId}
     * - If show_result = 0 → render "Thank you" page (no score shown)
     */
    public function submit(int $id): void
    {
        $this->requireStudent();
        $this->validateCsrf();

        $studentId = (int)($_SESSION['student_id'] ?? 0);
        $branchId  = (int)($_SESSION['branch_id'] ?? 0);

        $exam = $this->fetchExamForPortal($id, $branchId);
        if (!$exam) {
            flash('error', 'Exam not found.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }
        if ($this->countAttempts($id, $studentId) > 0) {
            flash('error', 'You have already submitted this exam.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Fetch questions
        $stmt = $this->db->prepare("SELECT * FROM exam_questions WHERE exam_id = :eid ORDER BY id ASC");
        $stmt->execute([':eid' => $id]);
        $questions = $stmt->fetchAll() ?: [];

        // Grade
        $rawAnswers = $_POST['answers'] ?? [];
        $obtained = 0; $total = 0; $correct = 0; $wrong = 0;
        $answersJson = [];
        foreach ($questions as $q) {
            $qId   = (int)$q['id'];
            $total += (int)$q['marks'];
            $given = strtolower(trim((string)($rawAnswers[$qId] ?? '')));
            if (in_array($given, ['a','b','c','d'], true)) {
                $answersJson[$qId] = $given;
                if ($given === $q['correct_answer']) {
                    $obtained += (int)$q['marks'];
                    $correct++;
                } else {
                    $wrong++;
                }
            } else {
                $answersJson[$qId] = null;
                $wrong++;
            }
        }

        $percentage = $total > 0 ? round(($obtained / $total) * 100, 2) : 0;
        $passMarks = (int)($exam['pass_marks'] ?? 40);
        // Pass if either pass_marks OR 33% threshold is met
        $status = ($obtained >= $passMarks || $percentage >= 33) ? 'pass' : 'fail';

        // Auto-ensure critical columns exist (defensive — handles older DB schemas
        // where the table might be missing total_obtained, attempt_number, answers_json, etc.)
        $this->ensureExamResultColumns();

        // Build the INSERT dynamically based on which columns actually exist.
        // This avoids "Unknown column" errors on older/mismatched schemas.
        $candidateColumns = [
            'exam_id'         => $id,
            'student_id'      => $studentId,
            'attempt_number'  => 1,
            'total_obtained'  => $obtained,
            'total_marks'     => $total,
            'percentage'      => $percentage,
            'status'          => $status,
            'answers_json'    => json_encode($answersJson),
            'started_at'      => $_POST['started_at'] ?? date('Y-m-d H:i:s'),
            'completed_at'    => date('Y-m-d H:i:s'),
        ];

        // Filter to only columns that exist in the actual table
        $insertColumns = [];
        foreach ($candidateColumns as $col => $value) {
            if ($this->columnExists('exam_results', $col)) {
                $insertColumns[$col] = $value;
            }
        }

        // Safety: if none of the critical columns exist, the table is too broken to use
        if (empty($insertColumns) || !isset($insertColumns['exam_id']) || !isset($insertColumns['student_id'])) {
            error_log('BranchExamPortal submit: exam_results table is missing critical columns. Available: ' . implode(',', array_keys($insertColumns)));
            flash('error', 'Exam results table is misconfigured. Please contact your branch administrator to run the database migration.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        try {
            $colList = implode(', ', array_map(fn($c) => "`{$c}`", array_keys($insertColumns)));
            $phList  = implode(', ', array_map(fn($c) => ":{$c}", array_keys($insertColumns)));
            $sql = "INSERT INTO `exam_results` ({$colList}) VALUES ({$phList})";
            $stmt = $this->db->prepare($sql);

            // Build params with prefixed keys to avoid collisions
            $params = [];
            foreach ($insertColumns as $col => $value) {
                $params[":{$col}"] = $value;
            }
            $stmt->execute($params);
            $attemptId = (int)$this->db->lastInsertId();
        } catch (\Throwable $e) {
            error_log('BranchExamPortal submit failed: ' . $e->getMessage() . ' | SQL: ' . ($sql ?? 'N/A'));
            if (defined('APP_DEBUG') && APP_DEBUG) {
                flash('error', 'Could not save your exam submission: ' . $e->getMessage());
            } else {
                flash('error', 'Could not save your exam submission. Please try again or contact your branch.');
            }
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // Branch by show_result toggle
        if (!empty($exam['show_result'])) {
            flash('success', $status === 'pass'
                ? 'Congratulations! You passed with ' . $percentage . '%.'
                : 'You scored ' . $percentage . '%. Better luck next time!');
            redirect(BASE_PATH . '/branch-exam/result/' . $attemptId);
        } else {
            // Show "thank you" page — no score
            $this->view('branch_exam/thank_you', [
                'title'      => 'Submitted - Branch Exam',
                'currentNav' => 'branch-exam',
                'exam'       => $exam,
                'userName'   => getCurrentUser()['name'] ?? '',
            ]);
        }
    }

    /**
     * Show the result page for a specific attempt.
     * If the exam's show_result is OFF, the student is told results will be
     * published later — only the score is NOT shown here.
     */
    public function result(int $attemptId): void
    {
        $this->requireStudent();
        $studentId = (int)($_SESSION['student_id'] ?? 0);
        $branchId  = (int)($_SESSION['branch_id'] ?? 0);
        $user      = getCurrentUser();

        $stmt = $this->db->prepare(
            "SELECT er.*, e.title, e.title_hindi, e.language, e.show_result, e.pass_marks,
                    e.total_marks AS exam_total, e.duration_minutes, e.branch_id
             FROM exam_results er
             INNER JOIN exams e ON er.exam_id = e.id
             WHERE er.id = :aid AND er.student_id = :sid AND e.branch_id = :bid
             LIMIT 1"
        );
        $stmt->execute([':aid' => $attemptId, ':sid' => $studentId, ':bid' => $branchId]);
        $result = $stmt->fetch() ?: null;

        if (!$result) {
            flash('error', 'Result not found.');
            redirect(BASE_PATH . '/branch-exam/dashboard');
        }

        // If show_result is OFF, render the "later" page even if they hit this URL
        if (empty($result['show_result'])) {
            $this->view('branch_exam/thank_you', [
                'title'      => 'Results Pending - Branch Exam',
                'currentNav' => 'branch-exam',
                'exam'       => $result,
                'userName'   => $user['name'] ?? '',
            ]);
            return;
        }

        $this->view('branch_exam/result', [
            'title'      => 'Your Result - Branch Exam',
            'currentNav' => 'branch-exam',
            'result'     => $result,
            'userName'   => $user['name'] ?? '',
            'enrollmentNo' => $_SESSION['student_enrollment_no'] ?? '',
            'branchCode' => $_SESSION['branch_code'] ?? '',
        ]);
    }

    // ========================================================================
    // PRIVATE HELPERS
    // ========================================================================

    /**
     * Require a logged-in student; otherwise redirect to login.
     */
    private function requireStudent(): void
    {
        if (!isLoggedIn() || !isStudent()) {
            flash('error', 'Please log in to access the Branch Exam portal.');
            redirect(BASE_PATH . '/branch-exam');
        }
    }

    /**
     * Fetch the list of portal-eligible exams.
     * Portal-eligible = is_published=1 AND is_active=1 AND branch_id set.
     *
     * @param bool $onlyForStudentBranch  If true, filter by the student's branch.
     * @param int|null $branchId          Used when $onlyForStudentBranch=true.
     */
    private function fetchPortalExams(bool $onlyForStudentBranch, ?int $branchId = null): array
    {
        try {
            if ($onlyForStudentBranch && $branchId) {
                $stmt = $this->db->prepare(
                    "SELECT e.id, e.title, e.title_hindi, e.language, e.duration_minutes, e.total_marks,
                            e.pass_marks, e.start_time, e.end_time, e.instructions, e.instructions_hindi,
                            b.name AS branch_name, b.branch_code,
                            (SELECT COUNT(*) FROM exam_questions q WHERE q.exam_id = e.id) AS question_count
                     FROM exams e
                     INNER JOIN branches b ON e.branch_id = b.id
                     WHERE e.is_published = 1 AND e.is_active = 1 AND e.branch_id = :bid
                     ORDER BY e.start_time ASC"
                );
                $stmt->execute([':bid' => $branchId]);
            } else {
                $stmt = $this->db->query(
                    "SELECT e.id, e.title, e.title_hindi, e.language, e.duration_minutes, e.total_marks,
                            e.pass_marks, e.start_time, e.end_time,
                            b.name AS branch_name, b.branch_code,
                            (SELECT COUNT(*) FROM exam_questions q WHERE q.exam_id = e.id) AS question_count
                     FROM exams e
                     INNER JOIN branches b ON e.branch_id = b.id
                     WHERE e.is_published = 1 AND e.is_active = 1
                       AND e.branch_id IS NOT NULL
                     ORDER BY e.start_time DESC
                     LIMIT 10"
                );
                return $stmt->fetchAll() ?: [];
            }
            return $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Fetch a single exam (with its branch) for the portal.
     * Verifies published + active + branch ownership.
     */
    private function fetchExamForPortal(int $id, int $branchId): ?array
    {
        try {
            $stmt = $this->db->prepare(
                "SELECT e.*, b.name AS branch_name, b.branch_code
                 FROM exams e
                 INNER JOIN branches b ON e.branch_id = b.id
                 WHERE e.id = :id AND e.branch_id = :bid
                   AND e.is_published = 1 AND e.is_active = 1
                 LIMIT 1"
            );
            $stmt->execute([':id' => $id, ':bid' => $branchId]);
            return $stmt->fetch() ?: null;
        } catch (\Throwable $e) {
            return null;
        }
    }

    /**
     * Count attempts a student has made for an exam.
     */
    private function countAttempts(int $examId, int $studentId): int
    {
        try {
            $stmt = $this->db->prepare(
                "SELECT COUNT(*) AS c FROM exam_results WHERE exam_id = :eid AND student_id = :sid"
            );
            $stmt->execute([':eid' => $examId, ':sid' => $studentId]);
            return (int)($stmt->fetch()['c'] ?? 0);
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /**
     * Auto-migrate: ensure exam_results has all the columns we need.
     * Safety net so this controller works even if the SQL migration hasn't been run
     * OR if the user's table was created with an older/different schema.
     *
     * This is a HEAVY safety net — it checks for every column we might use and
     * adds it if missing. The columnExists() check makes this idempotent.
     */
    private function ensureExamResultColumns(): void
    {
        static $done = false;
        if ($done) return;

        $checks = [
            // Critical columns that should always exist
            'total_obtained'  => "ADD COLUMN `total_obtained` INT NOT NULL DEFAULT 0",
            'total_marks'     => "ADD COLUMN `total_marks` INT NOT NULL DEFAULT 0",
            'percentage'      => "ADD COLUMN `percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00",
            'status'          => "ADD COLUMN `status` ENUM('pass','fail') NOT NULL DEFAULT 'fail'",
            'started_at'      => "ADD COLUMN `started_at` DATETIME NULL DEFAULT NULL",
            'completed_at'    => "ADD COLUMN `completed_at` DATETIME NULL DEFAULT NULL",
            // New columns added by Phase 4
            'attempt_number'  => "ADD COLUMN `attempt_number` INT UNSIGNED NOT NULL DEFAULT 1 AFTER `student_id`",
            'answers_json'    => "ADD COLUMN `answers_json` TEXT NULL DEFAULT NULL",
            // Approval workflow columns
            'branch_approved'      => "ADD COLUMN `branch_approved` TINYINT(1) NOT NULL DEFAULT 0",
            'approved_at'          => "ADD COLUMN `approved_at` DATETIME NULL DEFAULT NULL",
            'approved_by'          => "ADD COLUMN `approved_by` BIGINT UNSIGNED NULL DEFAULT NULL",
            'admin_remarks'        => "ADD COLUMN `admin_remarks` TEXT NULL DEFAULT NULL",
            'marksheet_generated'  => "ADD COLUMN `marksheet_generated` TINYINT(1) NOT NULL DEFAULT 0",
            'marksheet_generated_at' => "ADD COLUMN `marksheet_generated_at` DATETIME NULL DEFAULT NULL",
        ];
        foreach ($checks as $col => $ddl) {
            try {
                if (!$this->columnExists('exam_results', $col)) {
                    $this->db->exec("ALTER TABLE `exam_results` {$ddl}");
                    error_log("BranchExamPortal: Auto-added column `{$col}` to exam_results table.");
                }
            } catch (\Throwable $e) {
                // Log but don't fail — the columnExists check above will skip
                // this column on the next attempt
                error_log("BranchExamPortal: Failed to add column `{$col}`: " . $e->getMessage());
            }
        }
        $done = true;
    }
}
