<?php

declare(strict_types=1);

namespace App\Controllers;

use App\Core\Controller;

/**
 * Practice Set Controller (Frontend)
 *
 * Handles the student-facing practice test pages:
 * - Listing available practice sets (with attempt tracking)
 * - Taking a practice test (with timer, bilingual support, user name display)
 * - Submitting answers and viewing results
 * - Test history for logged-in users
 *
 * Supports both guest access and logged-in student tracking.
 * Attempt counting is done by EMAIL as the primary identifier,
 * with user_id backfill for data consistency.
 */
class PracticeSetController extends Controller
{
    /**
     * Display all published practice sets with user attempt info.
     */
    public function index(): void
    {
        $this->ensureUserIdColumn();

        $practiceModel = $this->model('PracticeSet');
        $sets = $practiceModel->getPublished();

        $stmt = $this->db->query('SELECT `id`, `name`, `category` FROM `courses` WHERE `active` = 1 ORDER BY `name` ASC');
        $courses = $stmt->fetchAll() ?: [];

        $user = getCurrentUser();
        $userId = null;
        $studentId = null;
        $userEmail = '';
        $userName = '';

        if ($user !== null) {
            $userId = (int) $user['id'];
            $studentModel = $this->model('Student');
            $student = $studentModel->getByUserId($userId);
            if ($student !== null) {
                $studentId = (int) $student['id'];
            }
            $userEmail = $user['email'] ?? '';
            $userName = $user['name'] ?? '';
            $userPhone = preg_replace('/[^0-9]/', '', (string) ($user['phone'] ?? ''));

            // Backfill user_id on old attempts matched by email
            $this->backfillUserIds($userId, $userEmail);
        } else {
            $userPhone = '';
        }

        $attemptModel = $this->model('PracticeAttempt');
        foreach ($sets as &$set) {
            $summary = $attemptModel->getUserAttemptSummary(
                (int) $set['id'],
                $studentId,
                $userEmail,
                $userId,
                $userPhone
            );
            $set['user_attempt_summary'] = $summary;

            $maxAttempts = (int) ($set['max_attempts'] ?? 0);
            $usedAttempts = $summary['count'];
            $set['remaining_attempts'] = ($maxAttempts > 0) ? max(0, $maxAttempts - $usedAttempts) : -1;
        }
        unset($set);

        $this->view('practice/index', [
            'title'      => 'Practice Tests - Gmind Institute',
            'sets'       => $sets,
            'courses'    => $courses,
            'currentNav' => 'practice',
            'userName'   => $userName,
            'isLoggedIn' => $user !== null,
            'userId'     => $userId,
        ]);
    }

    /**
     * Display a practice test for the student to take.
     * Shows user name if logged in, checks attempt limits.
     *
     * @param int $id Practice set ID
     */
    public function take(int $id): void
    {
        $this->ensureUserIdColumn();

        $practiceModel = $this->model('PracticeSet');
        $set = $practiceModel->getWithQuestions($id);

        if (empty($set) || !(bool) $set['is_published']) {
            flash('error', 'Practice test not found or not available.');
            redirect(BASE_PATH . '/practice');
        }

        if (empty($set['questions'])) {
            flash('error', 'This practice test has no questions yet.');
            redirect(BASE_PATH . '/practice');
        }

        $user = getCurrentUser();
        $userId = null;
        $studentId = null;
        $userEmail = '';
        $userName = '';
        $userAlreadyAttempted = false;
        $attemptCount = 0;
        $maxAttempts = (int) ($set['max_attempts'] ?? 0);
        $remainingAttempts = -1;

        if ($user !== null) {
            $userId = (int) $user['id'];
            $studentModel = $this->model('Student');
            $student = $studentModel->getByUserId($userId);
            if ($student !== null) {
                $studentId = (int) $student['id'];
            }
            $userEmail = $user['email'] ?? '';
            $userName = $user['name'] ?? '';
            $userPhone = (string) ($user['phone'] ?? '');

            // Backfill user_id on old attempts matched by email
            $this->backfillUserIds($userId, $userEmail);

            // Count existing attempts — email OR phone is the primary identifier
            $attemptModel = $this->model('PracticeAttempt');
            $attemptCount = $attemptModel->countBySetAndUser($id, $userId, $studentId, $userEmail, $userPhone);

            if ($maxAttempts > 0) {
                $remainingAttempts = max(0, $maxAttempts - $attemptCount);
                if ($attemptCount >= $maxAttempts) {
                    $userAlreadyAttempted = true;
                }
            }
        } else {
            $userPhone = '';
        }

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

        $this->view('practice/take', [
            'title'                => ($set['title_hindi'] ?? $set['title']) . ' - Practice Test',
            'set'                  => $set,
            'currentNav'           => 'practice',
            'userName'             => $userName,
            'userEmail'            => $userEmail,
            'userPhone'            => $userPhone,
            'isLoggedIn'           => $user !== null,
            'userAlreadyAttempted' => $userAlreadyAttempted,
            'attemptCount'         => $attemptCount,
            'maxAttempts'          => $maxAttempts,
            'remainingAttempts'    => $remainingAttempts,
        ]);
    }

    /**
     * AJAX endpoint: Check attempt count for a given email or phone on a practice set.
     * Used by the start modal for guest users to show attempt info
     * right after they enter their email or phone.
     */
    public function checkAttempts(int $id): void
    {
        $this->ensureUserIdColumn();

        $email = trim($_GET['email'] ?? '');
        $phone = trim($_GET['phone'] ?? '');
        // Normalise phone to digits only
        $phone = preg_replace('/[^0-9]/', '', $phone);
        $practiceModel = $this->model('PracticeSet');
        $set = $practiceModel->find($id);

        if (empty($set)) {
            $this->json(['error' => 'Practice set not found'], 404);
            return;
        }

        $maxAttempts = (int) ($set['max_attempts'] ?? 0);
        $attemptCount = 0;
        $userId = null;
        $studentId = null;

        // If user is logged in, also check by user_id
        $user = getCurrentUser();
        if ($user !== null) {
            $userId = (int) $user['id'];
            $studentModel = $this->model('Student');
            $student = $studentModel->getByUserId($userId);
            if ($student !== null) {
                $studentId = (int) $student['id'];
            }
            if (empty($email)) {
                $email = $user['email'] ?? '';
            }
            if (empty($phone)) {
                $phone = preg_replace('/[^0-9]/', '', (string) ($user['phone'] ?? ''));
            }
        }

        if (!empty($email) || !empty($phone)) {
            $attemptModel = $this->model('PracticeAttempt');
            $attemptCount = $attemptModel->countBySetAndUser($id, $userId, $studentId, $email, $phone);
        }

        $remaining = ($maxAttempts > 0) ? max(0, $maxAttempts - $attemptCount) : -1;
        $nextAttempt = $attemptCount + 1;
        $blocked = ($maxAttempts > 0 && $attemptCount >= $maxAttempts);

        $this->json([
            'attemptCount'    => $attemptCount,
            'maxAttempts'     => $maxAttempts,
            'remaining'       => $remaining,
            'nextAttempt'     => $nextAttempt,
            'blocked'         => $blocked,
            'nextAttemptLabel'=> $nextAttempt . ($nextAttempt === 1 ? 'st' : ($nextAttempt === 2 ? 'nd' : ($nextAttempt === 3 ? 'rd' : 'th'))) . ' Attempt',
        ]);
    }

    /**
     * Process practice test submission.
     * Calculates score, stores attempt, checks attempt limits, and redirects to result.
     *
     * @param int $id Practice set ID
     */
    public function submit(int $id): void
    {
        $this->validateCsrf();
        $this->ensureUserIdColumn();

        $practiceModel = $this->model('PracticeSet');
        $set = $practiceModel->find($id);

        if (empty($set) || !(bool) $set['is_published']) {
            flash('error', 'Practice test not found or not available.');
            redirect(BASE_PATH . '/practice');
        }

        // Collect info from form
        $studentName  = sanitize($_POST['student_name'] ?? '');
        $studentEmail = sanitize($_POST['student_email'] ?? '');
        $studentPhone = sanitize($_POST['student_phone'] ?? '');

        // Basic phone validation — strip everything except digits and +, then
        // require at least 10 digits. Empty phone is allowed for logged-in users
        // (their phone may already be on file via the users table).
        $phoneDigits = preg_replace('/[^0-9]/', '', $studentPhone);
        if (strlen($phoneDigits) > 0 && strlen($phoneDigits) < 10) {
            flash('error', 'Please enter a valid mobile number (at least 10 digits).');
            redirect(BASE_PATH . '/practice/' . $id);
        }
        // Normalise phone to digits-only for consistent tracking
        if (!empty($phoneDigits)) {
            $studentPhone = $phoneDigits;
        }

        // Check if logged-in user
        $user = getCurrentUser();
        $userId = null;
        $studentId = null;
        if ($user !== null) {
            $userId = (int) $user['id'];
            $studentModel = $this->model('Student');
            $student = $studentModel->getByUserId($userId);
            if ($student !== null) {
                $studentId = (int) $student['id'];
            }
            // Always use user's name/email for logged-in users
            if (empty($studentName)) {
                $studentName = $user['name'] ?? '';
            }
            if (empty($studentEmail)) {
                $studentEmail = $user['email'] ?? '';
            }
            if (empty($studentPhone)) {
                // Try to pull from user record (phone column on users table)
                $studentPhone = (string) ($user['phone'] ?? '');
            }

            // Backfill user_id on old attempts matched by email OR phone
            $this->backfillUserIds($userId, $studentEmail);
        }

        // Check attempt limits — EMAIL or PHONE is the primary identifier
        $maxAttempts = (int) ($set['max_attempts'] ?? 0);
        if ($maxAttempts > 0 && (!empty($studentEmail) || !empty($studentPhone))) {
            $attemptModel = $this->model('PracticeAttempt');
            $existingCount = $attemptModel->countBySetAndUser($id, $userId, $studentId, $studentEmail, $studentPhone);

            if ($existingCount >= $maxAttempts) {
                flash('error', 'You have already reached the maximum number of attempts (' . $maxAttempts . ') for this test.');
                redirect(BASE_PATH . '/practice');
            }
        }

        // Collect answers
        $rawAnswers = $_POST['answers'] ?? [];
        $answers = [];

        foreach ($rawAnswers as $questionId => $answer) {
            $qId = (int) $questionId;
            $ans = sanitize((string) $answer);
            if (in_array($ans, ['a', 'b', 'c', 'd'], true)) {
                $answers[$qId] = $ans;
            }
        }

        // Calculate score
        $attemptModel = $this->model('PracticeAttempt');
        $score = $attemptModel->calculateScore($id, $answers);

        // Store the attempt
        try {
            $answersJson = json_encode($score['details'], JSON_UNESCAPED_UNICODE);

            $stmt = $this->db->prepare(
                'INSERT INTO `practice_attempts`
                    (`practice_set_id`, `student_name`, `student_email`, `student_phone`, `student_id`, `user_id`,
                     `total_obtained`, `total_marks`, `percentage`,
                     `correct_count`, `wrong_count`, `unanswered_count`,
                     `status`, `answers_json`, `started_at`, `completed_at`)
                 VALUES
                    (:practice_set_id, :student_name, :student_email, :student_phone, :student_id, :user_id,
                     :total_obtained, :total_marks, :percentage,
                     :correct_count, :wrong_count, :unanswered_count,
                     :status, :answers_json, :started_at, :completed_at)'
            );

            $stmt->execute([
                ':practice_set_id'  => $id,
                ':student_name'     => $studentName ?: null,
                ':student_email'    => $studentEmail ?: null,
                ':student_phone'    => $studentPhone ?: null,
                ':student_id'       => $studentId,
                ':user_id'          => $userId,
                ':total_obtained'   => $score['obtained'],
                ':total_marks'      => $score['total'],
                ':percentage'       => $score['percentage'],
                ':correct_count'    => $score['correct_count'],
                ':wrong_count'      => $score['wrong_count'],
                ':unanswered_count' => $score['unanswered_count'],
                ':status'           => $score['status'],
                ':answers_json'     => $answersJson,
                ':started_at'       => $_POST['started_at'] ?? date('Y-m-d H:i:s'),
                ':completed_at'     => date('Y-m-d H:i:s'),
            ]);

            $attemptId = (int) $this->db->lastInsertId();

            if ((bool) $set['result_published']) {
                flash('success', $score['status'] === 'pass'
                    ? 'Congratulations! You passed with ' . $score['percentage'] . '%.'
                    : 'You scored ' . $score['percentage'] . '%. Keep practicing!');
            } else {
                flash('success', 'Your practice test has been submitted successfully! Results will be announced later.');
            }

            redirect(BASE_PATH . '/practice/' . $id . '/result/' . $attemptId);
        } catch (\Throwable $e) {
            error_log('Practice set submission error: ' . $e->getMessage());
            flash('error', 'Something went wrong submitting your test. Please try again.');
            redirect(BASE_PATH . '/practice/' . $id);
        }
    }

    /**
     * Display the result for a practice test attempt.
     *
     * @param int $id Practice set ID
     * @param int $attemptId Attempt ID
     */
    public function result(int $id, int $attemptId): void
    {
        $this->ensureUserIdColumn();

        $attemptModel = $this->model('PracticeAttempt');
        $attempt = $attemptModel->findWithSet($attemptId);

        if ($attempt === null || (int) $attempt['practice_set_id'] !== $id) {
            flash('error', 'Result not found.');
            redirect(BASE_PATH . '/practice');
        }

        // If result is NOT published, show only "submitted" message
        if (!(bool) ($attempt['result_published'] ?? 1)) {
            $this->view('practice/submitted', [
                'title'      => 'Test Submitted - Gmind Institute',
                'attempt'    => $attempt,
                'currentNav' => 'practice',
            ]);
            return;
        }

        // Result is published — show full result
        $practiceModel = $this->model('PracticeSet');
        $set = $practiceModel->getWithQuestions($id);

        // Decode answers JSON for detailed review
        $answerDetails = json_decode($attempt['answers_json'] ?? '[]', true) ?: [];

        // Map questions with answer details for review
        $reviewQuestions = [];
        if (!empty($set['questions']) && (bool) ($attempt['show_correct_answers'] ?? 1)) {
            $answerMap = [];
            foreach ($answerDetails as $detail) {
                $answerMap[(int) $detail['question_id']] = $detail;
            }
            foreach ($set['questions'] as $q) {
                $qId = (int) $q['id'];
                if (isset($answerMap[$qId])) {
                    $q['selected'] = $answerMap[$qId]['selected'];
                    $q['is_correct'] = $answerMap[$qId]['is_correct'];
                    $q['earned'] = $answerMap[$qId]['earned'];
                } else {
                    $q['selected'] = '';
                    $q['is_correct'] = false;
                    $q['earned'] = 0;
                }
                $reviewQuestions[] = $q;
            }
        }

        // Get user info — use the attempt's stored email AND phone as primary identifiers
        $user = getCurrentUser();
        $userId = null;
        $studentId = null;
        $userEmail = $attempt['student_email'] ?? '';
        $userPhone = (string) ($attempt['student_phone'] ?? '');

        if ($user !== null) {
            $userId = (int) $user['id'];
            $studentModel = $this->model('Student');
            $student = $studentModel->getByUserId($userId);
            if ($student !== null) {
                $studentId = (int) $student['id'];
            }
            // Also check logged-in user's email
            if (empty($userEmail)) {
                $userEmail = $user['email'] ?? '';
            }
            if (empty($userPhone)) {
                $userPhone = preg_replace('/[^0-9]/', '', (string) ($user['phone'] ?? ''));
            }

            // Backfill user_id on old attempts matched by email
            $this->backfillUserIds($userId, $userEmail);
        }

        $userName = $user['name'] ?? $attempt['student_name'] ?? '';

        // Calculate attempt number (1st, 2nd, 3rd attempt) using email OR phone as key
        $attemptNumber = $attemptModel->getAttemptNumber($attemptId, $id, $userId, $studentId, $userEmail, $userPhone);

        // Calculate total attempts for this user on this set — using email OR phone
        $maxAttempts = (int) ($attempt['max_attempts'] ?? $set['max_attempts'] ?? 0);
        $totalAttempts = $attemptModel->countBySetAndUser($id, $userId, $studentId, $userEmail, $userPhone);
        $remainingAttempts = ($maxAttempts > 0) ? max(0, $maxAttempts - $totalAttempts) : -1;

        $this->view('practice/result', [
            'title'              => 'Test Result - Gmind Institute',
            'attempt'            => $attempt,
            'set'                => $set,
            'reviewQuestions'    => $reviewQuestions,
            'currentNav'         => 'practice',
            'userName'           => $userName,
            'attemptNumber'      => $attemptNumber,
            'maxAttempts'        => $maxAttempts,
            'totalAttempts'      => $totalAttempts,
            'remainingAttempts'  => $remainingAttempts,
        ]);
    }

    /**
     * Display test history for a logged-in user.
     */
    public function history(): void
    {
        $this->ensureUserIdColumn();

        $user = getCurrentUser();
        $userId = null;
        $studentId = null;
        $userEmail = '';
        $userName = '';

        if ($user !== null) {
            $userId = (int) $user['id'];
            $studentModel = $this->model('Student');
            $student = $studentModel->getByUserId($userId);
            if ($student !== null) {
                $studentId = (int) $student['id'];
            }
            $userEmail = $user['email'] ?? '';
            $userName = $user['name'] ?? '';

            // Backfill user_id on old attempts matched by email
            $this->backfillUserIds($userId, $userEmail);
        }

        $attemptModel = $this->model('PracticeAttempt');
        $attempts = $attemptModel->getByUserId($userId, $studentId, $userEmail);

        $totalAttempts = count($attempts);
        $passCount = 0;
        $failCount = 0;
        $totalPercentage = 0;

        foreach ($attempts as $attempt) {
            if (($attempt['status'] ?? '') === 'pass') {
                $passCount++;
            } else {
                $failCount++;
            }
            $totalPercentage += (float) ($attempt['percentage'] ?? 0);
        }

        $avgPercentage = $totalAttempts > 0 ? round($totalPercentage / $totalAttempts, 1) : 0;

        $this->view('practice/history', [
            'title'         => 'My Test History - Gmind Institute',
            'currentNav'    => 'practice',
            'attempts'      => $attempts,
            'userName'      => $userName,
            'totalAttempts' => $totalAttempts,
            'passCount'     => $passCount,
            'failCount'     => $failCount,
            'avgPercentage' => $avgPercentage,
            'isLoggedIn'    => $user !== null,
        ]);
    }

    /**
     * Ensure the user_id column exists in the practice_attempts table.
     * Also ensures every column the controller reads exists on practice_sets
     * and practice_questions — this is a safety net against old-backup
     * schemas that may be missing columns like `title_hindi`, `marks`,
     * `sort_order`, etc.
     */
    private function ensureUserIdColumn(): void
    {
        static $migrated = false;

        if ($migrated) {
            return;
        }

        try {
            // 1. Ensure practice_attempts.user_id exists
            $stmt = $this->db->query("SHOW COLUMNS FROM `practice_attempts` LIKE 'user_id'");
            if ($stmt !== false && $stmt->fetch() === false) {
                $this->db->exec('ALTER TABLE `practice_attempts` ADD COLUMN `user_id` INT UNSIGNED DEFAULT NULL AFTER `student_id`');
                // Add index only if it doesn't already exist
                $idxCheck = $this->db->query("SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_attempts' AND INDEX_NAME = 'idx_user_id'");
                if ($idxCheck !== false && $idxCheck->fetch() === false) {
                    $this->db->exec('ALTER TABLE `practice_attempts` ADD KEY `idx_user_id` (`user_id`)');
                }
                error_log('PracticeSetController: Added user_id column to practice_attempts');
            }

            // 2. Ensure every expected column exists on practice_sets
            $this->ensureColumnsExist('practice_sets', [
                'title'                 => "VARCHAR(255) NOT NULL",
                'title_hindi'           => "VARCHAR(255) DEFAULT NULL",
                'course_id'             => "INT UNSIGNED DEFAULT NULL",
                'description'           => "TEXT DEFAULT NULL",
                'description_hindi'     => "TEXT DEFAULT NULL",
                'duration_minutes'      => "INT NOT NULL DEFAULT 60",
                'total_marks'           => "INT NOT NULL DEFAULT 100",
                'pass_marks'            => "INT NOT NULL DEFAULT 40",
                'negative_marks'        => "DECIMAL(3,1) NOT NULL DEFAULT 0.0",
                'is_published'          => "TINYINT(1) NOT NULL DEFAULT 0",
                'result_published'      => "TINYINT(1) NOT NULL DEFAULT 1",
                'language_mode'         => "ENUM('both','hindi','english') NOT NULL DEFAULT 'both'",
                'max_attempts'          => "INT NOT NULL DEFAULT 0",
                'show_correct_answers'  => "TINYINT(1) NOT NULL DEFAULT 1",
                'created_at'            => "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
                'updated_at'            => "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
            ]);

            // 3. Ensure every expected column exists on practice_questions
            $this->ensureColumnsExist('practice_questions', [
                'practice_set_id'       => "INT UNSIGNED NOT NULL",
                'question_text'         => "TEXT NOT NULL",
                'question_text_hindi'   => "TEXT DEFAULT NULL",
                'option_a'              => "VARCHAR(500) NOT NULL",
                'option_a_hindi'        => "VARCHAR(500) DEFAULT NULL",
                'option_b'              => "VARCHAR(500) NOT NULL",
                'option_b_hindi'        => "VARCHAR(500) DEFAULT NULL",
                'option_c'              => "VARCHAR(500) DEFAULT NULL",
                'option_c_hindi'        => "VARCHAR(500) DEFAULT NULL",
                'option_d'              => "VARCHAR(500) DEFAULT NULL",
                'option_d_hindi'        => "VARCHAR(500) DEFAULT NULL",
                'correct_answer'        => "ENUM('a','b','c','d') NOT NULL DEFAULT 'a'",
                'marks'                 => "INT NOT NULL DEFAULT 1",
                'sort_order'            => "INT NOT NULL DEFAULT 0",
                'created_at'            => "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ]);

            // 4. Ensure every expected column exists on practice_attempts
            //    (including the new student_phone column)
            $this->ensureColumnsExist('practice_attempts', [
                'practice_set_id'   => "INT UNSIGNED NOT NULL",
                'student_name'      => "VARCHAR(255) DEFAULT NULL",
                'student_email'     => "VARCHAR(255) DEFAULT NULL",
                'student_phone'     => "VARCHAR(20) DEFAULT NULL",
                'student_id'        => "INT UNSIGNED DEFAULT NULL",
                'user_id'           => "INT UNSIGNED DEFAULT NULL",
                'total_obtained'    => "DECIMAL(7,2) NOT NULL DEFAULT 0.00",
                'total_marks'       => "INT NOT NULL DEFAULT 0",
                'percentage'        => "DECIMAL(5,2) NOT NULL DEFAULT 0.00",
                'correct_count'     => "INT NOT NULL DEFAULT 0",
                'wrong_count'       => "INT NOT NULL DEFAULT 0",
                'unanswered_count'  => "INT NOT NULL DEFAULT 0",
                'status'            => "ENUM('pass','fail') NOT NULL DEFAULT 'fail'",
                'answers_json'      => "JSON DEFAULT NULL",
                'started_at'        => "TIMESTAMP NULL DEFAULT NULL",
                'completed_at'      => "TIMESTAMP NULL DEFAULT NULL",
                'created_at'        => "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
            ]);

            $migrated = true;
        } catch (\Throwable $e) {
            error_log('PracticeSetController: Migration check error: ' . $e->getMessage());
            $migrated = true; // Prevent retry loop
        }
    }

    /**
     * Ensure all listed columns exist on the given table.
     * Each missing column is added with ALTER TABLE.
     */
    private function ensureColumnsExist(string $table, array $columns): void
    {
        foreach ($columns as $col => $def) {
            try {
                $check = $this->db->prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table AND COLUMN_NAME = :col"
                );
                $check->execute([':table' => $table, ':col' => $col]);
                if ((int) $check->fetchColumn() === 0) {
                    $this->db->exec("ALTER TABLE `{$table}` ADD COLUMN `{$col}` {$def}");
                    error_log("PracticeSetController: Auto-added `{$col}` column to `{$table}`");
                }
            } catch (\Throwable $e) {
                error_log("PracticeSetController: {$table}.{$col} check failed: " . $e->getMessage());
            }
        }
    }

    /**
     * Backfill user_id on old attempts that match by email but have NULL user_id.
     * This ensures attempt counting works correctly when the user is logged in,
     * because old attempts only stored student_email without user_id.
     *
     * @param int|null $userId The logged-in user's ID
     * @param string $email The user's email address
     */
    private function backfillUserIds(?int $userId, string $email): void
    {
        if ($userId === null || $userId <= 0 || empty($email)) {
            return;
        }

        // Check if user_id column exists first
        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM `practice_attempts` LIKE 'user_id'");
            if ($stmt === false || $stmt->fetch() === false) {
                return; // Column doesn't exist yet
            }
        } catch (\Throwable $e) {
            return;
        }

        try {
            // Update all attempts with this email that don't have user_id set
            $updateStmt = $this->db->prepare(
                "UPDATE `practice_attempts` SET `user_id` = :user_id 
                 WHERE `student_email` = :email AND (`user_id` IS NULL OR `user_id` = 0)"
            );
            $updateStmt->execute([
                ':user_id' => $userId,
                ':email'   => $email,
            ]);

            $rowsUpdated = $updateStmt->rowCount();
            if ($rowsUpdated > 0) {
                error_log("PracticeSetController: Backfilled user_id={$userId} on {$rowsUpdated} attempts for email={$email}");
            }
        } catch (\Throwable $e) {
            error_log('PracticeSetController: Backfill user_id error: ' . $e->getMessage());
        }
    }
}
