<?php

declare(strict_types=1);

namespace Admin\Controllers;

/**
 * Admin Practice Set Controller
 *
 * Full CRUD for practice sets, question management,
 * Excel/CSV import, and attempt viewing.
 */
class AdminPracticeSetController extends AdminController
{
    /**
     * Full CREATE TABLE SQL for auto-creating tables if missing.
     */
    private const CREATE_TABLES_SQL = "
        CREATE TABLE IF NOT EXISTS `practice_sets` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `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,
            PRIMARY KEY (`id`),
            KEY `idx_course_id` (`course_id`),
            KEY `idx_is_published` (`is_published`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

        CREATE TABLE IF NOT EXISTS `practice_questions` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `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,
            PRIMARY KEY (`id`),
            KEY `idx_practice_set_id` (`practice_set_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

        CREATE TABLE IF NOT EXISTS `practice_attempts` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `practice_set_id` INT UNSIGNED NOT NULL,
            `student_name` VARCHAR(255) DEFAULT NULL,
            `student_email` VARCHAR(255) 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,
            PRIMARY KEY (`id`),
            KEY `idx_practice_set_id` (`practice_set_id`),
            KEY `idx_student_id` (`student_id`),
            KEY `idx_user_id` (`user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";

    /**
     * Ensure all practice tables exist.
     */
    private function ensureTablesExist(): void
    {
        try {
            $this->db->query('SELECT 1 FROM `practice_sets` LIMIT 1');
        } catch (\Throwable $e) {
            // Run all CREATE TABLE statements
            foreach (explode(';', self::CREATE_TABLES_SQL) as $sql) {
                $sql = trim($sql);
                if (!empty($sql)) {
                    $this->db->exec($sql);
                }
            }
            error_log('Admin Practice: Auto-created practice tables');
        }

        // Auto-migrate: Add user_id column to practice_attempts if missing
        try {
            $stmt = $this->db->query("SHOW COLUMNS FROM `practice_attempts` LIKE 'user_id'");
            if ($stmt->fetch() === false) {
                $this->db->exec('ALTER TABLE `practice_attempts` ADD COLUMN `user_id` INT UNSIGNED DEFAULT NULL AFTER `student_id`');
                $this->db->exec('ALTER TABLE `practice_attempts` ADD KEY `idx_user_id` (`user_id`)');
                error_log('Admin Practice: Added user_id column to practice_attempts');
            }
        } catch (\Throwable $e) {
            error_log('Admin Practice: Migration check error: ' . $e->getMessage());
        }

        // Ensure all expected columns exist 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.
        $this->ensurePracticeSetsColumns();
        $this->ensurePracticeQuestionsColumns();
    }

    /**
     * Ensure practice_sets table has every column the controller writes to.
     * Each missing column is added with ALTER TABLE — guarded so it's safe
     * to call on every request.
     */
    private function ensurePracticeSetsColumns(): void
    {
        $expected = [
            '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",
        ];

        foreach ($expected as $col => $def) {
            try {
                $check = $this->db->prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_sets' AND COLUMN_NAME = :col"
                );
                $check->execute([':col' => $col]);
                if ((int) $check->fetchColumn() === 0) {
                    $this->db->exec("ALTER TABLE `practice_sets` ADD COLUMN `{$col}` {$def}");
                    error_log("Admin Practice: Auto-added `{$col}` column to practice_sets");
                }
            } catch (\Throwable $e) {
                error_log("Admin Practice: practice_sets.{$col} check failed: " . $e->getMessage());
            }
        }
    }

    /**
     * Ensure practice_questions table has every column the controller writes to.
     * Each missing column is added with ALTER TABLE — guarded so it's safe
     * to call on every request.
     *
     * This is the safety net for errors like:
     *   "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'marks' in 'field list'"
     *   "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'title_hindi' in 'field list'"
     */
    private function ensurePracticeQuestionsColumns(): void
    {
        $expected = [
            '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",
        ];

        foreach ($expected as $col => $def) {
            try {
                $check = $this->db->prepare(
                    "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'practice_questions' AND COLUMN_NAME = :col"
                );
                $check->execute([':col' => $col]);
                if ((int) $check->fetchColumn() === 0) {
                    $this->db->exec("ALTER TABLE `practice_questions` ADD COLUMN `{$col}` {$def}");
                    error_log("Admin Practice: Auto-added `{$col}` column to practice_questions");
                }
            } catch (\Throwable $e) {
                error_log("Admin Practice: practice_questions.{$col} check failed: " . $e->getMessage());
            }
        }
    }

    /**
     * List all practice sets with course info and question counts.
     */
    public function index(array $params = []): void
    {
        requireAdmin();
        $this->ensureTablesExist();

        // Check if user_id column exists (safe for legacy tables)
        $hasUserIdCol = false;
        try {
            $colCheck = $this->db->query("SHOW COLUMNS FROM `practice_attempts` LIKE 'user_id'");
            $hasUserIdCol = ($colCheck !== false && $colCheck->fetch() !== false);
        } catch (\Throwable $e) {
            // Ignore
        }

        if ($hasUserIdCol) {
            $uniqueStudentSub = '(SELECT COUNT(DISTINCT CASE WHEN pa2.user_id IS NOT NULL THEN pa2.user_id ELSE pa2.student_id END) '
                              . 'FROM `practice_attempts` pa2 WHERE pa2.practice_set_id = ps.id AND (pa2.user_id IS NOT NULL OR pa2.student_id IS NOT NULL)) AS unique_student_count';
        } else {
            $uniqueStudentSub = '(SELECT COUNT(DISTINCT pa2.student_id) '
                              . 'FROM `practice_attempts` pa2 WHERE pa2.practice_set_id = ps.id AND pa2.student_id IS NOT NULL) AS unique_student_count';
        }

        $sql = 'SELECT ps.*, c.name AS course_name,
                    (SELECT COUNT(*) FROM `practice_questions` pq WHERE pq.practice_set_id = ps.id) AS question_count,
                    (SELECT COUNT(*) FROM `practice_attempts` pa WHERE pa.practice_set_id = ps.id) AS attempt_count,
                    ' . $uniqueStudentSub . '
             FROM `practice_sets` ps
             LEFT JOIN `courses` c ON ps.course_id = c.id
             ORDER BY ps.created_at DESC';

        $stmt = $this->db->query($sql);
        $sets = $stmt->fetchAll() ?: [];

        $this->adminView('practice_sets', [
            'title'       => 'Manage Practice Sets',
            'currentPage' => 'practice-sets',
            'sets'        => $sets,
            'editSet'     => null,
            'questions'   => [],
            'showForm'    => false,
            'showQuestions'=> false,
            'showAttempts'=> false,
        ]);
    }

    /**
     * Show the create practice set form.
     */
    public function create(array $params = []): void
    {
        requireAdmin();
        $this->ensureTablesExist();

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

        $this->adminView('practice_sets', [
            'title'        => 'Add New Practice Set',
            'currentPage'  => 'practice-sets',
            'sets'         => [],
            'courses'      => $courses,
            'editSet'      => null,
            'questions'    => [],
            'showForm'     => true,
            'showQuestions'=> false,
            'showAttempts' => false,
        ]);
    }

    /**
     * Store a new practice set.
     */
    public function store(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();
        $this->ensureTablesExist();

        $title           = sanitize($_POST['title'] ?? '');
        $titleHindi      = $_POST['title_hindi'] ?? '';
        $courseId        = (int) ($_POST['course_id'] ?? 0);
        $description     = $_POST['description'] ?? '';
        $descriptionHindi= $_POST['description_hindi'] ?? '';
        $duration        = (int) ($_POST['duration_minutes'] ?? 60);
        $totalMarks      = (int) ($_POST['total_marks'] ?? 100);
        $passMarks       = (int) ($_POST['pass_marks'] ?? 40);
        $negativeMarks   = (float) ($_POST['negative_marks'] ?? 0);
        $isPublished     = isset($_POST['is_published']) ? 1 : 0;
        $resultPublished = isset($_POST['result_published']) ? 1 : 0;
        $languageMode    = sanitize($_POST['language_mode'] ?? 'both');
        $maxAttempts     = (int) ($_POST['max_attempts'] ?? 0);
        $showCorrect     = isset($_POST['show_correct_answers']) ? 1 : 0;

        if (empty($title)) {
            flash('error', 'Practice set title is required.');
            redirect(BASE_PATH . '/admin/practice-sets/create');
        }

        if (!in_array($languageMode, ['both', 'hindi', 'english'], true)) {
            $languageMode = 'both';
        }

        $stmt = $this->db->prepare(
            'INSERT INTO `practice_sets` (`title`, `title_hindi`, `course_id`, `description`, `description_hindi`,
                `duration_minutes`, `total_marks`, `pass_marks`, `negative_marks`,
                `is_published`, `result_published`, `language_mode`, `max_attempts`, `show_correct_answers`)
             VALUES (:title, :title_hindi, :course_id, :description, :description_hindi,
                :duration_minutes, :total_marks, :pass_marks, :negative_marks,
                :is_published, :result_published, :language_mode, :max_attempts, :show_correct_answers)'
        );

        $stmt->execute([
            ':title'              => $title,
            ':title_hindi'        => $titleHindi ?: null,
            ':course_id'          => $courseId > 0 ? $courseId : null,
            ':description'        => $description ?: null,
            ':description_hindi'  => $descriptionHindi ?: null,
            ':duration_minutes'   => $duration,
            ':total_marks'        => $totalMarks,
            ':pass_marks'         => $passMarks,
            ':negative_marks'     => $negativeMarks,
            ':is_published'       => $isPublished,
            ':result_published'   => $resultPublished,
            ':language_mode'      => $languageMode,
            ':max_attempts'       => $maxAttempts,
            ':show_correct_answers'=> $showCorrect,
        ]);

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

        flash('success', 'Practice set created successfully. Now add questions.');
        redirect(BASE_PATH . '/admin/practice-sets/' . $setId . '/questions');
    }

    /**
     * Show the edit practice set form.
     */
    public function edit(array $params = []): void
    {
        requireAdmin();
        $this->ensureTablesExist();

        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            'SELECT ps.*, c.name AS course_name
             FROM `practice_sets` ps
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE ps.id = :id LIMIT 1'
        );
        $stmt->execute([':id' => $id]);
        $editSet = $stmt->fetch() ?: null;

        if ($editSet === null) {
            flash('error', 'Practice set not found.');
            redirect(BASE_PATH . '/admin/practice-sets');
        }

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

        $this->adminView('practice_sets', [
            'title'        => 'Edit Practice Set',
            'currentPage'  => 'practice-sets',
            'sets'         => [],
            'courses'      => $courses,
            'editSet'      => $editSet,
            'questions'    => [],
            'showForm'     => true,
            'showQuestions'=> false,
            'showAttempts' => false,
        ]);
    }

    /**
     * Update an existing practice set.
     */
    public function update(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $id = (int) ($params['id'] ?? 0);

        $title           = sanitize($_POST['title'] ?? '');
        $titleHindi      = $_POST['title_hindi'] ?? '';
        $courseId        = (int) ($_POST['course_id'] ?? 0);
        $description     = $_POST['description'] ?? '';
        $descriptionHindi= $_POST['description_hindi'] ?? '';
        $duration        = (int) ($_POST['duration_minutes'] ?? 60);
        $totalMarks      = (int) ($_POST['total_marks'] ?? 100);
        $passMarks       = (int) ($_POST['pass_marks'] ?? 40);
        $negativeMarks   = (float) ($_POST['negative_marks'] ?? 0);
        $isPublished     = isset($_POST['is_published']) ? 1 : 0;
        $resultPublished = isset($_POST['result_published']) ? 1 : 0;
        $languageMode    = sanitize($_POST['language_mode'] ?? 'both');
        $maxAttempts     = (int) ($_POST['max_attempts'] ?? 0);
        $showCorrect     = isset($_POST['show_correct_answers']) ? 1 : 0;

        if (empty($title)) {
            flash('error', 'Practice set title is required.');
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/edit');
        }

        if (!in_array($languageMode, ['both', 'hindi', 'english'], true)) {
            $languageMode = 'both';
        }

        $stmt = $this->db->prepare(
            'UPDATE `practice_sets` SET
                `title` = :title, `title_hindi` = :title_hindi,
                `course_id` = :course_id, `description` = :description,
                `description_hindi` = :description_hindi,
                `duration_minutes` = :duration_minutes, `total_marks` = :total_marks,
                `pass_marks` = :pass_marks, `negative_marks` = :negative_marks,
                `is_published` = :is_published, `result_published` = :result_published,
                `language_mode` = :language_mode, `max_attempts` = :max_attempts,
                `show_correct_answers` = :show_correct_answers
             WHERE `id` = :id'
        );

        $stmt->execute([
            ':title'              => $title,
            ':title_hindi'        => $titleHindi ?: null,
            ':course_id'          => $courseId > 0 ? $courseId : null,
            ':description'        => $description ?: null,
            ':description_hindi'  => $descriptionHindi ?: null,
            ':duration_minutes'   => $duration,
            ':total_marks'        => $totalMarks,
            ':pass_marks'         => $passMarks,
            ':negative_marks'     => $negativeMarks,
            ':is_published'       => $isPublished,
            ':result_published'   => $resultPublished,
            ':language_mode'      => $languageMode,
            ':max_attempts'       => $maxAttempts,
            ':show_correct_answers'=> $showCorrect,
            ':id'                 => $id,
        ]);

        flash('success', 'Practice set updated successfully.');
        redirect(BASE_PATH . '/admin/practice-sets');
    }

    /**
     * Delete a practice set (cascades to questions and attempts).
     */
    public function delete(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $id = (int) ($params['id'] ?? 0);

        // Delete attempts first (no FK constraint)
        $stmt = $this->db->prepare('DELETE FROM `practice_attempts` WHERE `practice_set_id` = :id');
        $stmt->execute([':id' => $id]);

        // Delete questions
        $stmt = $this->db->prepare('DELETE FROM `practice_questions` WHERE `practice_set_id` = :id');
        $stmt->execute([':id' => $id]);

        // Delete set
        $stmt = $this->db->prepare('DELETE FROM `practice_sets` WHERE `id` = :id');
        $stmt->execute([':id' => $id]);

        flash('success', 'Practice set and all its questions deleted successfully.');
        redirect(BASE_PATH . '/admin/practice-sets');
    }

    /**
     * Show questions for a practice set — allows adding and importing.
     */
    public function questions(array $params = []): void
    {
        requireAdmin();
        $this->ensureTablesExist();

        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            'SELECT ps.*, c.name AS course_name
             FROM `practice_sets` ps
             LEFT JOIN `courses` c ON ps.course_id = c.id
             WHERE ps.id = :id LIMIT 1'
        );
        $stmt->execute([':id' => $id]);
        $editSet = $stmt->fetch() ?: null;

        if ($editSet === null) {
            flash('error', 'Practice set not found.');
            redirect(BASE_PATH . '/admin/practice-sets');
        }

        $stmt = $this->db->prepare(
            'SELECT * FROM `practice_questions` WHERE `practice_set_id` = :set_id ORDER BY `sort_order` ASC'
        );
        $stmt->execute([':set_id' => $id]);
        $questions = $stmt->fetchAll() ?: [];

        $this->adminView('practice_sets', [
            'title'        => 'Manage Questions',
            'currentPage'  => 'practice-sets',
            'sets'         => [],
            'courses'      => [],
            'editSet'      => $editSet,
            'questions'    => $questions,
            'showForm'     => false,
            'showQuestions'=> true,
            'showAttempts' => false,
        ]);
    }

    /**
     * Add a single question to a practice set.
     */
    public function addQuestion(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();
        $this->ensureTablesExist(); // safety net: auto-adds missing columns

        $id = (int) ($params['id'] ?? 0);

        $questionText     = $_POST['question_text'] ?? '';
        $questionTextHindi= $_POST['question_text_hindi'] ?? '';
        $optionA          = sanitize($_POST['option_a'] ?? '');
        $optionAHindi     = $_POST['option_a_hindi'] ?? '';
        $optionB          = sanitize($_POST['option_b'] ?? '');
        $optionBHindi     = $_POST['option_b_hindi'] ?? '';
        $optionC          = sanitize($_POST['option_c'] ?? '');
        $optionCHindi     = $_POST['option_c_hindi'] ?? '';
        $optionD          = sanitize($_POST['option_d'] ?? '');
        $optionDHindi     = $_POST['option_d_hindi'] ?? '';
        $correctAnswer    = sanitize($_POST['correct_answer'] ?? 'a');
        $marks            = (int) ($_POST['marks'] ?? 1);
        $sortOrder        = (int) ($_POST['sort_order'] ?? 0);

        if (empty($questionText) || empty($optionA) || empty($optionB)) {
            flash('error', 'Question text and at least options A and B are required.');
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
        }

        if (!in_array($correctAnswer, ['a', 'b', 'c', 'd'], true)) {
            $correctAnswer = 'a';
        }

        $stmt = $this->db->prepare(
            'INSERT INTO `practice_questions` (`practice_set_id`, `question_text`, `question_text_hindi`,
                `option_a`, `option_a_hindi`, `option_b`, `option_b_hindi`,
                `option_c`, `option_c_hindi`, `option_d`, `option_d_hindi`,
                `correct_answer`, `marks`, `sort_order`)
             VALUES (:practice_set_id, :question_text, :question_text_hindi,
                :option_a, :option_a_hindi, :option_b, :option_b_hindi,
                :option_c, :option_c_hindi, :option_d, :option_d_hindi,
                :correct_answer, :marks, :sort_order)'
        );

        $stmt->execute([
            ':practice_set_id'    => $id,
            ':question_text'      => $questionText,
            ':question_text_hindi'=> $questionTextHindi ?: null,
            ':option_a'           => $optionA,
            ':option_a_hindi'     => $optionAHindi ?: null,
            ':option_b'           => $optionB,
            ':option_b_hindi'     => $optionBHindi ?: null,
            ':option_c'           => $optionC ?: null,
            ':option_c_hindi'     => $optionCHindi ?: null,
            ':option_d'           => $optionD ?: null,
            ':option_d_hindi'     => $optionDHindi ?: null,
            ':correct_answer'     => $correctAnswer,
            ':marks'              => $marks,
            ':sort_order'         => $sortOrder,
        ]);

        flash('success', 'Question added successfully.');
        redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
    }

    /**
     * Import questions from a CSV/Excel file upload.
     * Expected CSV columns: question_text, question_text_hindi,
     * option_a, option_a_hindi, option_b, option_b_hindi,
     * option_c, option_c_hindi, option_d, option_d_hindi,
     * correct_answer, marks
     */
    public function importQuestions(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();
        $this->ensureTablesExist(); // safety net: auto-adds missing columns (marks, sort_order, etc.)

        $id = (int) ($params['id'] ?? 0);

        if ($id <= 0) {
            flash('error', 'Invalid practice set ID.');
            redirect(BASE_PATH . '/admin/practice-sets');
        }

        // Verify practice set exists
        $checkStmt = $this->db->prepare('SELECT `id` FROM `practice_sets` WHERE `id` = :id LIMIT 1');
        $checkStmt->execute([':id' => $id]);
        if ($checkStmt->fetch() === false) {
            flash('error', 'Practice set not found.');
            redirect(BASE_PATH . '/admin/practice-sets');
        }

        if (empty($_FILES['import_file']['tmp_name'])) {
            flash('error', 'Please select a file to import.');
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
        }

        $file = $_FILES['import_file'];

        // Check for upload errors
        if ($file['error'] !== UPLOAD_ERR_OK) {
            $errMsg = match ($file['error']) {
                UPLOAD_ERR_INI_SIZE, UPLOAD_ERR_FORM_SIZE => 'File is too large. Maximum upload size exceeded.',
                UPLOAD_ERR_PARTIAL => 'File was only partially uploaded. Please try again.',
                UPLOAD_ERR_NO_FILE => 'No file was uploaded.',
                default => 'Upload error. Please try again.',
            };
            flash('error', $errMsg);
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
        }

        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));

        if (!in_array($ext, ['csv', 'xlsx', 'xls'], true)) {
            flash('error', 'Only CSV, XLS, and XLSX files are allowed.');
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
        }

        // Parse CSV file
        $questions = [];

        if ($ext === 'csv') {
            $handle = fopen($file['tmp_name'], 'r');
            if ($handle === false) {
                flash('error', 'Could not open the file.');
                redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
            }

            // Skip header row
            $headers = fgetcsv($handle);
            if ($headers === false) {
                fclose($handle);
                flash('error', 'Empty or invalid CSV file.');
                redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
            }

            // Normalize headers — trim, lowercase, remove BOM
            $headers = array_map(function ($h) {
                $h = trim($h);
                $h = strtolower($h);
                // Remove UTF-8 BOM if present
                $h = str_replace("\xEF\xBB\xBF", '', $h);
                return $h;
            }, $headers);

            $rowNum = 1; // header is row 1
            while (($row = fgetcsv($handle)) !== false) {
                $rowNum++;
                if (count($row) < 4) {
                    continue; // skip incomplete rows
                }

                // Pad or trim row to match header count
                $row = array_pad($row, count($headers), '');
                $row = array_slice($row, 0, count($headers));

                $data = array_combine($headers, $row);

                if ($data === false || empty($data['question_text'])) {
                    continue;
                }

                $correct = strtolower(trim($data['correct_answer'] ?? 'a'));
                if (!in_array($correct, ['a', 'b', 'c', 'd'], true)) {
                    $correct = 'a';
                }

                $questions[] = [
                    'question_text'       => $data['question_text'] ?? '',
                    'question_text_hindi' => $data['question_text_hindi'] ?? $data['question_hindi'] ?? '',
                    'option_a'            => $data['option_a'] ?? '',
                    'option_a_hindi'      => $data['option_a_hindi'] ?? '',
                    'option_b'            => $data['option_b'] ?? '',
                    'option_b_hindi'      => $data['option_b_hindi'] ?? '',
                    'option_c'            => $data['option_c'] ?? '',
                    'option_c_hindi'      => $data['option_c_hindi'] ?? '',
                    'option_d'            => $data['option_d'] ?? '',
                    'option_d_hindi'      => $data['option_d_hindi'] ?? '',
                    'correct_answer'      => $correct,
                    'marks'               => (int) ($data['marks'] ?? 1),
                    'sort_order'          => $rowNum - 1,
                ];
            }
            fclose($handle);
        } else {
            // For XLSX/XLS — require CSV format
            flash('error', 'Please convert your Excel file to CSV format first, then upload. CSV is natively supported.');
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
        }

        if (empty($questions)) {
            flash('error', 'No valid questions found in the file. Make sure the CSV has a header row with column names: question_text, option_a, option_b, option_c, option_d, correct_answer, marks');
            redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
        }

        // Bulk insert using model
        try {
            $questionModel = new \App\Models\PracticeQuestion($this->db);
            $count = $questionModel->createBulk($id, $questions);
            flash('success', $count . ' questions imported successfully.');
        } catch (\Throwable $e) {
            error_log('Practice question import error: ' . $e->getMessage());
            flash('error', 'Error importing questions: ' . $e->getMessage());
        }

        redirect(BASE_PATH . '/admin/practice-sets/' . $id . '/questions');
    }

    /**
     * Delete a single question.
     */
    public function deleteQuestion(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $setId      = (int) ($params['id'] ?? 0);
        $questionId = (int) ($params['question_id'] ?? 0);

        $stmt = $this->db->prepare(
            'DELETE FROM `practice_questions` WHERE `id` = :id AND `practice_set_id` = :set_id'
        );
        $stmt->execute([':id' => $questionId, ':set_id' => $setId]);

        if ($stmt->rowCount() > 0) {
            flash('success', 'Question deleted successfully.');
        } else {
            flash('warning', 'Question not found or already deleted.');
        }
        redirect(BASE_PATH . '/admin/practice-sets/' . $setId . '/questions');
    }

    /**
     * View attempts for a practice set.
     */
    public function attempts(array $params = []): void
    {
        requireAdmin();
        $this->ensureTablesExist();

        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            'SELECT * FROM `practice_sets` WHERE `id` = :id LIMIT 1'
        );
        $stmt->execute([':id' => $id]);
        $editSet = $stmt->fetch() ?: null;

        if ($editSet === null) {
            flash('error', 'Practice set not found.');
            redirect(BASE_PATH . '/admin/practice-sets');
        }

        $stmt = $this->db->prepare(
            'SELECT * FROM `practice_attempts` WHERE `practice_set_id` = :set_id ORDER BY `created_at` DESC'
        );
        $stmt->execute([':set_id' => $id]);
        $attempts = $stmt->fetchAll() ?: [];

        $this->adminView('practice_sets', [
            'title'        => 'View Attempts',
            'currentPage'  => 'practice-sets',
            'sets'         => [],
            'courses'      => [],
            'editSet'      => $editSet,
            'questions'    => [],
            'attempts'     => $attempts,
            'showForm'     => false,
            'showQuestions'=> false,
            'showAttempts' => true,
        ]);
    }

    /**
     * Toggle publish status of a practice set.
     */
    public function togglePublish(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            'UPDATE `practice_sets` SET `is_published` = NOT `is_published` WHERE `id` = :id'
        );
        $stmt->execute([':id' => $id]);

        flash('success', 'Publish status toggled successfully.');
        redirect(BASE_PATH . '/admin/practice-sets');
    }

    /**
     * Toggle result published status.
     */
    public function toggleResultPublish(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            'UPDATE `practice_sets` SET `result_published` = NOT `result_published` WHERE `id` = :id'
        );
        $stmt->execute([':id' => $id]);

        flash('success', 'Result publish status toggled successfully.');
        redirect(BASE_PATH . '/admin/practice-sets');
    }

    /**
     * Delete a practice attempt.
     */
    public function deleteAttempt(array $params = []): void
    {
        requireAdmin();
        $this->validateCsrf();

        $setId     = (int) ($params['id'] ?? 0);
        $attemptId = (int) ($params['attempt_id'] ?? 0);

        $stmt = $this->db->prepare('DELETE FROM `practice_attempts` WHERE `id` = :id');
        $stmt->execute([':id' => $attemptId]);

        flash('success', 'Attempt deleted successfully.');
        redirect(BASE_PATH . '/admin/practice-sets/' . $setId . '/attempts');
    }

    /**
     * Download CSV template for question import.
     */
    public function downloadTemplate(array $params = []): void
    {
        requireAdmin();

        $filename = 'practice_questions_template.csv';
        $headers = [
            'question_text',
            'question_text_hindi',
            'option_a',
            'option_a_hindi',
            'option_b',
            'option_b_hindi',
            'option_c',
            'option_c_hindi',
            'option_d',
            'option_d_hindi',
            'correct_answer',
            'marks',
        ];

        // Sample data rows
        $samples = [
            [
                'What is the full form of CPU?',
                'CPU का पूरा नाम क्या है?',
                'Central Processing Unit',
                'सेंट्रल प्रोसेसिंग यूनिट',
                'Computer Personal Unit',
                'कंप्यूटर पर्सनल यूनिट',
                'Central Program Utility',
                'सेंट्रल प्रोग्राम यूटिलिटी',
                'Central Processor Uniqueness',
                'सेंट्रल प्रोसेसर यूनीकनेस',
                'a',
                '1',
            ],
            [
                'Which key is used to refresh a webpage in a browser?',
                'ब्राउज़र में वेबपेज रिफ्रेश करने के लिए कौन सी की उपयोग होती है?',
                'F5',
                'F5',
                'F1',
                'F1',
                'F12',
                'F12',
                'Esc',
                'Esc',
                'a',
                '1',
            ],
        ];

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

        $output = fopen('php://output', 'w');
        // BOM for Excel UTF-8 support
        fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));

        fputcsv($output, $headers);
        foreach ($samples as $row) {
            fputcsv($output, $row);
        }

        fclose($output);
        exit;
    }
}
