<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Models\ClassSectionModel;
use App\Models\ConfigurationModel;
use App\Models\ExamDraftModel;
use App\Models\StudentClassModel;
use App\Models\TeacherClassModel;
use App\Models\UserModel;
use CodeIgniter\HTTP\Files\UploadedFile;
use Config\Database;

class ExamDraftController extends BaseController
{
    protected ExamDraftModel $examDraftModel;
    protected TeacherClassModel $teacherClassModel;
    protected ClassSectionModel $classSectionModel;
    protected StudentClassModel $studentClassModel;
    protected UserModel $userModel;
    protected ConfigurationModel $configModel;

    protected string $schoolYear;
    protected string $semester;
    protected bool $hasFinalPdfColumn = false;
    protected bool $hasIsLegacyColumn = false;
    protected bool $hasAcceptanceTypeColumn = false;
    protected bool $hasAdminIdColumn = false;
    protected bool $hasReviewRevisionColumn = false;
    protected bool $hasReviewerCommentColumn = false;
    protected bool $hasReviewerCommentsColumn = false;
    protected bool $hasAdminCommentsColumn = false;
    protected string $authorIdColumn = 'teacher_id';
    protected string $authorFileColumn = 'teacher_file';
    protected string $authorFilenameColumn = 'teacher_filename';
    protected string $reviewerIdColumn = 'admin_id';
    protected string $reviewerCommentColumn = 'reviewer_comment';

    // DB status: submitted, accepted, review needed, rejected, canceled, under review, legacy

    protected const TEACHER_UPLOAD_DIR = 'exams/drafts';
    protected const FINAL_UPLOAD_DIR = 'exams/finals';
    protected const MAX_UPLOAD_BYTES = 12 * 1024 * 1024;
    protected const ALLOWED_EXTENSIONS = ['doc', 'docx'];
    protected const ADMIN_ALLOWED_EXTENSIONS = ['doc', 'docx', 'pdf'];

    protected array $examTypes = [
        'Final Exam',
        'Midterm Exam',
        'Quiz',
        'Study Guide',
        'Practice Exam',
        'Other',
    ];

    public function __construct()
    {
        $this->examDraftModel = new ExamDraftModel();
        $this->teacherClassModel = new TeacherClassModel();
        $this->classSectionModel = new ClassSectionModel();
        $this->studentClassModel = new StudentClassModel();
        $this->userModel = new UserModel();
        $this->configModel = new ConfigurationModel();
        $this->db = Database::connect();

        $this->schoolYear = (string) ($this->configModel->getConfig('school_year') ?? '');
        $this->semester = (string) ($this->configModel->getConfig('semester') ?? '');
        $this->hasFinalPdfColumn = $this->schemaHasColumn('exam_drafts', 'final_pdf_file');
        $this->hasIsLegacyColumn = $this->schemaHasColumn('exam_drafts', 'is_legacy');
        $this->hasAcceptanceTypeColumn = $this->schemaHasColumn('exam_drafts', 'acceptance_type');
        $this->hasAdminIdColumn = $this->schemaHasColumn('exam_drafts', 'admin_id');
        $this->hasReviewRevisionColumn = $this->schemaHasColumn('exam_drafts', 'review_revision');
        $this->hasReviewerCommentColumn = $this->schemaHasColumn('exam_drafts', 'reviewer_comment');
        $this->hasReviewerCommentsColumn = $this->schemaHasColumn('exam_drafts', 'reviewer_comments');
        $this->hasAdminCommentsColumn = $this->schemaHasColumn('exam_drafts', 'admin_comments');
        $this->authorIdColumn = $this->schemaHasColumn('exam_drafts', 'teacher_id') ? 'teacher_id' : 'author_id';
        $this->authorFileColumn = $this->schemaHasColumn('exam_drafts', 'teacher_file') ? 'teacher_file' : 'author_file';
        $this->authorFilenameColumn = $this->schemaHasColumn('exam_drafts', 'teacher_filename') ? 'teacher_filename' : 'author_filename';
        $this->reviewerIdColumn = $this->hasAdminIdColumn
            ? 'admin_id'
            : ($this->schemaHasColumn('exam_drafts', 'reviewer_id') ? 'reviewer_id' : '');
        $this->reviewerCommentColumn = $this->schemaHasColumn('exam_drafts', 'reviewer_comment')
            ? 'reviewer_comment'
            : ($this->schemaHasColumn('exam_drafts', 'reviewer_comments')
                ? 'reviewer_comments'
                : ($this->schemaHasColumn('exam_drafts', 'admin_comments') ? 'admin_comments' : ''));

        helper(['form', 'url', 'date']);
    }

    public function teacherIndex()
    {
        $this->syncAcademicContext();
        $context = $this->resolveSchoolYearContext();

        $teacherId = (int) (session()->get('user_id') ?? 0);
        if ($teacherId <= 0) {
            return redirect()->to('/login');
        }

        $assignments = $this->teacherClassModel->getClassAssignmentsByUserId($teacherId, $this->schoolYear);
        $selectedClass = $this->resolveSelectedClassSection($assignments);
        if ($selectedClass > 0) {
            session()->set('class_section_id', $selectedClass);
        }

        $classSectionIds = array_map(static fn($a) => (int) $a['class_section_id'], $assignments);

        $allDrafts = $this->visibleTeacherDraftsQuery($teacherId, $classSectionIds)
            ->orderBy('created_at', 'DESC')
            ->findAll();

        foreach ($allDrafts as &$row) {
            if (empty($row['final_pdf_file'])) {
                $pdf = $this->ensurePdfExists($row['final_file'] ?? '', pathinfo($row['final_file'] ?? '', PATHINFO_EXTENSION));
                if ($pdf !== null) {
                    $row['final_pdf_file'] = $pdf;
                }
            }
            $row = $this->attachTeacherDraftContext($row, $teacherId);
        }
        unset($row);

        $legacyExams = [];
        // Guard against missing schema column in older databases
        if ($this->hasIsLegacyColumn) {
            // Keep all submissions visible; legacy ones are also surfaced in a separate tab
            $drafts = $allDrafts;

            $legacyQuery = $this->examDraftModel
                ->select($this->draftSelectColumns())
                ->select('cs.class_section_name, u.firstname AS teacher_first, u.lastname AS teacher_last')
                ->join('classSection cs', 'cs.class_section_id = exam_drafts.class_section_id', 'left')
                ->join('users u', 'u.id = exam_drafts.' . $this->authorIdColumn, 'left')
                ->where('exam_drafts.is_legacy', 1)
                ->where('exam_drafts.status', 'legacy')
                ->where('exam_drafts.final_file IS NOT NULL', null, false);

            if (!empty($classSectionIds)) {
                $legacyQuery = $legacyQuery->whereIn('exam_drafts.class_section_id', $classSectionIds);
            }

            $legacyExams = $legacyQuery
                ->orderBy('cs.class_section_name', 'ASC')
                ->orderBy('exam_drafts.created_at', 'DESC')
                ->findAll();

            foreach ($legacyExams as &$row) {
                $row = $this->attachTeacherDraftContext($row, $teacherId);
            }
            unset($row);
        } else {
            // Legacy column absent, show all drafts and skip legacy tab query
            $drafts = $allDrafts;
        }

        $printablePool = array_merge($drafts, $legacyExams);
        $printableIds = $this->printableDraftIds($printablePool);
        if ($this->hasReviewRevisionColumn) {
            $drafts = $this->groupReviewRevisions($drafts);
            $legacyExams = $this->groupReviewRevisions($legacyExams);
        }
        $drafts = $this->attachPrintableFlags($drafts, $printableIds);
        $legacyExams = $this->attachPrintableFlags($legacyExams, $printableIds);

        $validation = session()->getFlashdata('validation') ?? $this->validator;

        return view('teacher/drafts', [
            'assignments' => $assignments,
            'selectedClassSection' => $selectedClass,
            'drafts' => $drafts,
            'legacyExams' => $legacyExams,
            'examTypes' => $this->examTypes,
            'statusBadges' => $this->statusBadgeMap(),
            'schoolYear' => $this->schoolYear,
            'semester' => $this->semester,
            'maxUploadBytes' => self::MAX_UPLOAD_BYTES,
            'validation' => $validation,
            'printableDraftIds' => $printableIds,
            'isSchoolYearReadonly' => $context->isReadonly(),
        ]);
    }

    public function teacherStore()
    {
        $this->syncAcademicContext();
        $this->assertSchoolYearWritable($this->resolveSchoolYearContext());

        $teacherId = (int) (session()->get('user_id') ?? 0);
        if ($teacherId <= 0) {
            return redirect()->to('/login');
        }

        $classSectionId = (int) ($this->request->getPost('class_section_id') ?? session()->get('class_section_id') ?? 0);
        $examType = $this->normalizeExamType($this->request->getPost('exam_type'));
        $authorComment = trim((string) $this->request->getPost('author_comment'));

        if ($classSectionId <= 0) {
            return redirect()->back()->withInput()->with('error', 'Select a class section before submitting.');
        }

        $assignmentQuery = $this->teacherClassModel
            ->where('teacher_id', $teacherId)
            ->where('class_section_id', $classSectionId);
        if ($this->schoolYear !== '') {
            $assignmentQuery->where('school_year', $this->schoolYear);
        }
        $assignment = $assignmentQuery->first();

        if (empty($assignment)) {
            return redirect()->back()->withInput()->with('error', 'You are not assigned to the selected class section.');
        }

        session()->set('class_section_id', $classSectionId);

        $saveAsDraft = $this->request->getPost('action') === 'draft'
            || !empty($this->request->getPost('save_as_draft'));

        $file = $this->request->getFile('draft_file');
        $teacherFile = null;
        $teacherFilename = null;
        if ($file && $file->isValid() && !$file->hasMoved()) {
            $stored = $this->storeUploadedFile($file, self::TEACHER_UPLOAD_DIR);
            if ($stored === null) {
                return redirect()->back()->withInput()->with('error', 'Failed to store the uploaded file.');
            }
            $teacherFile = $stored;
            $teacherFilename = $file->getClientName();
        } elseif ($file && $file->getError() !== UPLOAD_ERR_NO_FILE) {
            return redirect()->back()->withInput()->with('error', 'Upload failed. Please try again.');
        }

        $existingDraft = $this->examDraftModel
            ->select($this->draftSelectColumns())
            ->where($this->authorIdColumn, $teacherId)
            ->where('class_section_id', $classSectionId)
            ->where('semester', $this->semester)
            ->where('school_year', $this->schoolYear)
            ->orderBy('version', 'DESC')
            ->first();

        if ($examType === '') {
            return redirect()->back()->withInput()->with('error', 'Select an exam type before submitting.');
        }

        $title = $examType;

        if ($saveAsDraft) {
            if ($teacherFile === null && (empty($existingDraft) || empty($this->draftTeacherFile($existingDraft)))) {
                return redirect()->back()->withInput()->with('error', 'Upload a file to save a draft.');
            }
            if (!empty($existingDraft) && strtolower((string) ($existingDraft['status'] ?? '')) === 'draft') {
                $draftRowId = (int) ($existingDraft['id'] ?? 0);
                $updateDraft = [
                    'exam_type' => $examType,
                    'draft_title' => $title,
                    'author_comment' => $authorComment === '' ? null : $authorComment,
                    'status' => 'draft',
                ];
                $this->applyAuthorFilePayload($updateDraft, $teacherFile, $teacherFilename);
                if ($this->examDraftModel->update($draftRowId, $updateDraft)) {
                    $this->notifyExamDraftEvent($this->examDraftModel->find($draftRowId), 'draft_saved');
                    return redirect()->to('/teacher/exam-drafts')->with('success', 'Draft saved.');
                }
                return redirect()->back()->withInput()->with('error', 'Unable to update the draft.');
            }

            $nextVersion = 1;
            $previousId = null;
            if (!empty($existingDraft)) {
                $currentVersion = (int) ($existingDraft['version'] ?? 0);
                if ($currentVersion <= 0) {
                    $currentVersion = 1;
                }
                $nextVersion = $currentVersion + 1;
                $previousId = (int) ($existingDraft['id'] ?? 0) ?: null;
            }
            $payload = [
                $this->authorIdColumn => $teacherId,
                'class_section_id' => $classSectionId,
                'semester' => $this->semester,
                'school_year' => $this->schoolYear,
                'exam_type' => $examType,
                'draft_title' => $title,
                'author_comment' => $authorComment === '' ? null : $authorComment,
                'status' => 'draft',
                'version' => $nextVersion,
                'previous_draft_id' => $previousId,
            ];
            $this->applyAuthorFilePayload($payload, $teacherFile, $teacherFilename);
            if ($this->examDraftModel->insert($payload)) {
                $newDraftId = (int) $this->examDraftModel->getInsertID();
                $this->notifyExamDraftEvent($newDraftId > 0 ? $this->examDraftModel->find($newDraftId) : null, 'draft_saved');
                return redirect()->to('/teacher/exam-drafts')->with('success', 'Draft saved.');
            }
            return redirect()->back()->withInput()->with('error', 'Unable to save the draft.');
        }

        $existingStatus = strtolower((string) ($existingDraft['status'] ?? ''));
        if ($teacherFile === null) {
            return redirect()->back()->withInput()->with('error', 'Upload a DOC/DOCX file to submit the exam draft.');
        }

        // Submit for review: overwrite only if latest is already submitted; otherwise create a new version.
            if (!empty($existingDraft) && $existingStatus === 'submitted') {
                $draftRowId = (int) ($existingDraft['id'] ?? 0);
                $updateSubmit = [
                    'exam_type' => $examType,
                    'draft_title' => $title,
                    'author_comment' => $authorComment === '' ? null : $authorComment,
                    'status' => 'submitted',
                ];
                $this->applyAuthorFilePayload($updateSubmit, $teacherFile, $teacherFilename);
            if ($this->examDraftModel->update($draftRowId, $updateSubmit)) {
                $saved = $this->ensureDraftStatus($draftRowId, 'submitted');
                $this->notifyPrincipalExamDraftSubmitted($saved, $teacherId, $classSectionId);
                $this->notifyExamDraftEvent($saved, 'submitted');
                return redirect()->to('/teacher/exam-drafts')->with('success', 'Exam draft submitted for review.');
            }
                return redirect()->back()->withInput()->with('error', 'Unable to submit the exam draft.');
            }

        $nextVersion = 1;
        $previousId = null;
        if (!empty($existingDraft)) {
            $currentVersion = (int) ($existingDraft['version'] ?? 0);
            if ($currentVersion <= 0) {
                $currentVersion = 1;
            }
            $nextVersion = $currentVersion + 1;
            $previousId = (int) ($existingDraft['id'] ?? 0) ?: null;
        }

        $payload = [
            $this->authorIdColumn => $teacherId,
            'class_section_id' => $classSectionId,
            'semester' => $this->semester,
            'school_year' => $this->schoolYear,
            'exam_type' => $examType,
            'draft_title' => $title,
            'author_comment' => $authorComment === '' ? null : $authorComment,
            'status' => 'submitted',
            'version' => $nextVersion,
            'previous_draft_id' => $previousId,
        ];
        $this->applyAuthorFilePayload($payload, $teacherFile, $teacherFilename);

        if ($this->examDraftModel->insert($payload)) {
            $newId = (int) $this->examDraftModel->getInsertID();
            $saved = $newId > 0 ? $this->ensureDraftStatus($newId, 'submitted') : null;
            $this->notifyPrincipalExamDraftSubmitted($saved, $teacherId, $classSectionId);
            $this->notifyExamDraftEvent($saved, 'submitted');
            return redirect()->to('/teacher/exam-drafts')->with('success', 'Exam draft submitted for review.');
        }

        return redirect()->back()->withInput()->with('error', 'Unable to save the exam draft.');
    }

    public function adminIndex()
    {
        return $this->reviewIndex();
    }

    public function principalIndex()
    {
        return $this->reviewIndex();
    }

    public function reviewIndex()
    {
        if ($this->reviewerIdColumn !== '') {
            $allDrafts = $this->examDraftModel
                ->select($this->draftSelectColumns())
                ->select('cs.class_section_name, u.firstname AS teacher_first, u.lastname AS teacher_last, a.firstname AS admin_first, a.lastname AS admin_last')
                ->join('classSection cs', 'cs.class_section_id = exam_drafts.class_section_id', 'left')
                ->join('users u', 'u.id = exam_drafts.' . $this->authorIdColumn, 'left')
                ->join('users a', 'a.id = exam_drafts.' . $this->reviewerIdColumn, 'left')
                ->orderBy('exam_drafts.created_at', 'DESC')
                ->findAll();
        } else {
            $allDrafts = $this->examDraftModel
                ->select($this->draftSelectColumns())
                ->select('cs.class_section_name, u.firstname AS teacher_first, u.lastname AS teacher_last, NULL AS admin_first, NULL AS admin_last', false)
                ->join('classSection cs', 'cs.class_section_id = exam_drafts.class_section_id', 'left')
                ->join('users u', 'u.id = exam_drafts.' . $this->authorIdColumn, 'left')
                ->orderBy('exam_drafts.created_at', 'DESC')
                ->findAll();
        }

        if ($this->hasReviewRevisionColumn) {
            $allDrafts = $this->groupReviewRevisions($allDrafts);
        }

        foreach ($allDrafts as &$row) {
            if (empty($row['final_pdf_file'])) {
                $pdf = $this->ensurePdfExists($row['final_file'] ?? '', pathinfo($row['final_file'] ?? '', PATHINFO_EXTENSION));
                if ($pdf !== null) {
                    $row['final_pdf_file'] = $pdf;
                }
            }
        }
        unset($row);

        $classSections = $this->classSectionModel
            ->select('class_section_id, class_section_name')
            ->orderBy('class_section_name', 'ASC')
            ->findAll();

        // Group legacy uploads (admin-uploaded accepted exams) by class_section for separate tab
        $legacyByClass = [];
        if ($this->hasIsLegacyColumn) {
            // Keep legacy items out of the main submissions list; show them in the legacy tab only.
            $drafts = [];
            foreach ($allDrafts as $d) {
                $isLegacy = !empty($d['is_legacy']);
                if ($isLegacy) {
                    $cid = (int)($d['class_section_id'] ?? 0);
                    if (!isset($legacyByClass[$cid])) {
                        $legacyByClass[$cid] = [
                            'class_section_id' => $cid,
                            'class_section_name' => $d['class_section_name'] ?? 'Class ' . $cid,
                            'items' => [],
                        ];
                    }
                    $legacyByClass[$cid]['items'][] = $d;
                    continue;
                }
                $drafts[] = $d;
            }
        } else {
            // Column missing: keep behavior simple and avoid legacy tab
            $drafts = $allDrafts;
        }

        $legacyFlat = [];
        foreach ($legacyByClass as $group) {
            foreach ($group['items'] ?? [] as $item) {
                $legacyFlat[] = $item;
            }
        }
        $printablePool = array_merge($drafts, $legacyFlat);
        $printableIds = $this->printableDraftIds($printablePool);
        $drafts = $this->attachPrintableFlags($drafts, $printableIds);
        foreach ($legacyByClass as &$group) {
            $group['items'] = $this->attachPrintableFlags($group['items'] ?? [], $printableIds);
        }
        unset($group);

        $classSectionsById = [];
        foreach ($classSections as $cs) {
            $csId = (int) ($cs['class_section_id'] ?? 0);
            if ($csId <= 0) {
                continue;
            }
            $classSectionsById[$csId] = $cs;
        }

        $studentCounts = $this->studentClassModel->getStudentCountsBySection($this->schoolYear);
        $visibleClasses = [];
        foreach ($studentCounts as $csId => $count) {
            $csId = (int) $csId;
            if ($csId <= 0 || $count <= 0) {
                continue;
            }
            $classData = $classSectionsById[$csId] ?? $this->classSectionModel->where('class_section_id', $csId)->first();
            if ($classData === null) {
                $classData = [
                    'class_section_id' => $csId,
                    'class_section_name' => 'Class ' . $csId,
                ];
            }
            $classData['student_count'] = $count;
            $visibleClasses[$csId] = $classData;
        }
        uasort($visibleClasses, static fn ($a, $b): int => strcasecmp($a['class_section_name'] ?? '', $b['class_section_name'] ?? ''));

        $classDraftGroups = [];
        foreach ($drafts as $draft) {
            $cid = (int) ($draft['class_section_id'] ?? 0);
            if ($cid <= 0) {
                continue;
            }
            $classDraftGroups[$cid][] = $draft;
        }
        $newSubmissionClasses = [];
        foreach ($classDraftGroups as $cid => $group) {
            foreach ($group as $draft) {
                if (strtolower((string) ($draft['status'] ?? '')) === 'submitted') {
                    $newSubmissionClasses[$cid] = true;
                    break;
                }
            }
        }

        return view('administrator/exam_drafts', [
            'drafts' => $drafts,
            'statusBadges' => $this->statusBadgeMap(),
            'schoolYear' => $this->schoolYear,
            'semester' => $this->semester,
            'allowedExtensions' => self::ADMIN_ALLOWED_EXTENSIONS,
            'maxUploadBytes' => self::MAX_UPLOAD_BYTES,
            'examTypes' => $this->examTypes,
            'classSections' => $classSections,
            'legacyByClass' => $legacyByClass,
            'printableDraftIds' => $printableIds,
            'visibleClasses' => $visibleClasses,
            'classDraftGroups' => $classDraftGroups,
            'newSubmissionClasses' => $newSubmissionClasses,
            'reviewActionUrl' => base_url($this->reviewRoutePrefix() . '/exam-drafts/review'),
            'legacyUploadUrl' => base_url($this->reviewRoutePrefix() . '/exam-drafts/upload-legacy'),
            'reviewPortalLabel' => $this->reviewPortalLabel(),
        ]);
    }

    public function adminUploadLegacy()
    {
        return $this->reviewUploadLegacy();
    }

    public function principalUploadLegacy()
    {
        return $this->reviewUploadLegacy();
    }

    public function reviewUploadLegacy()
    {
        $adminId = (int) (session()->get('user_id') ?? 0);
        if ($adminId <= 0) {
            return redirect()->to('/login');
        }

        $classSectionIds = $this->request->getPost('class_section_ids');
        if (!is_array($classSectionIds)) {
            $classSectionIds = [$classSectionIds];
        }
        $classSectionIds = array_values(array_filter(array_map('intval', $classSectionIds)));
        $schoolYear = trim((string) ($this->request->getPost('school_year') ?? $this->schoolYear));
        $semester = trim((string) ($this->request->getPost('semester') ?? $this->semester));
        $examType = $this->normalizeExamType($this->request->getPost('exam_type'));

        if (empty($classSectionIds)) {
            return redirect()->back()->withInput()->with('error', 'Select at least one class section.');
        }
        if ($schoolYear === '') {
            return redirect()->back()->withInput()->with('error', 'School year is required.');
        }
        if ($semester === '') {
            return redirect()->back()->withInput()->with('error', 'Semester is required.');
        }
        if ($examType === '') {
            return redirect()->back()->withInput()->with('error', 'Exam type is required.');
        }

        $file = $this->request->getFile('old_exam_file');
        if (!$file || !$file->isValid()) {
            return redirect()->back()->withInput()->with('error', 'A valid file is required.');
        }

        $stored = $this->storeUploadedFile($file, self::FINAL_UPLOAD_DIR, self::ADMIN_ALLOWED_EXTENSIONS);
        if ($stored === null) {
            return redirect()->back()->withInput()->with('error', 'File type not allowed or upload failed.');
        }

        $pdfName = null;
        if (strtolower($file->getClientExtension()) === 'pdf') {
            $pdfName = $stored;
        } else {
            $pdfName = $this->convertDocToPdf(
                $this->fullUploadPath(self::FINAL_UPLOAD_DIR, $stored),
                self::FINAL_UPLOAD_DIR
            );
        }
        $basePayload = [
            $this->authorIdColumn => $adminId, // store under admin user since legacy uploads are admin-only
            'semester' => ucfirst(strtolower($semester)),
            'school_year' => $schoolYear,
            'exam_type' => $examType,
            'draft_title' => $examType,
            'author_comment' => null,
            'final_file' => $stored,
            'final_filename' => $file->getClientName(),
            'status' => 'legacy',
            'reviewed_at' => utc_now(),
            'version' => 1,
        ];
        if ($this->reviewerIdColumn !== '') {
            $basePayload[$this->reviewerIdColumn] = $adminId;
        }
        if ($this->hasIsLegacyColumn) {
            $basePayload['is_legacy'] = 1;
        }
        if ($pdfName !== null && $this->hasFinalPdfColumn) {
            $basePayload['final_pdf_file'] = $pdfName;
        }

        $saved = 0;
        foreach ($classSectionIds as $classSectionId) {
            $payload = $basePayload;
            $payload['class_section_id'] = $classSectionId;
            if ($this->examDraftModel->insert($payload)) {
                $saved++;
            }
        }

        if ($saved > 0) {
            return redirect()->to($this->reviewDashboardPath())->with('success', 'Old exam uploaded successfully.');
        }

        return redirect()->back()->withInput()->with('error', 'Unable to save the old exam.');
    }

    public function adminReview()
    {
        return $this->reviewSubmission();
    }

    public function principalReview()
    {
        return $this->reviewSubmission();
    }

    public function reviewSubmission()
    {
        $draftId = (int) ($this->request->getPost('draft_id') ?? 0);
        if ($draftId <= 0) {
            return redirect()->back()->with('error', 'Invalid submission selected.');
        }

        $draft = $this->findDraft($draftId);
        if (empty($draft)) {
            return redirect()->back()->with('error', 'Submission not found.');
        }

        $reviewerComment = trim((string) ($this->request->getPost('reviewer_comment') ?? $this->request->getPost('admin_comments')));
        $statusInput = trim((string) $this->request->getPost('review_status'));
        $currentStatus = (string) ($draft['status'] ?? 'draft');
        $status = $this->normalizeStatus(
            $statusInput !== '' ? $statusInput : $currentStatus,
            $currentStatus
        );
        $status = strtolower($status);
        if (!in_array($status, $this->statusOptions(), true)) {
            $status = 'under review';
        }

        $acceptanceType = null;
        if ($status === 'accepted') {
            $rawAccept = strtolower(trim((string) $this->request->getPost('acceptance_type')));
            $acceptanceType = in_array($rawAccept, ['as_is', 'minor_edits'], true) ? $rawAccept : 'as_is';
        }

        $finalFile = null;
        $finalFilename = null;

        $file = $this->request->getFile('final_file');
        if ($file && $file->isValid() && !$file->hasMoved()) {
            $stored = $this->storeUploadedFile($file, self::FINAL_UPLOAD_DIR, self::ADMIN_ALLOWED_EXTENSIONS);
            if ($stored === null) {
                return redirect()->back()->with('error', 'Unable to store the final draft.')->withInput();
            }
            $finalFile = $stored;
            $finalFilename = $file->getClientName();
            if ($status === 'accepted') {
                $status = 'accepted';
            }
        } elseif ($file && $file->getError() !== UPLOAD_ERR_NO_FILE) {
            return redirect()->back()->with('error', 'Final file upload failed.');
        }

        $update = [
            'status' => $status,
            'reviewed_at' => utc_now(),
        ];
        $existingComment = (string) ($draft['reviewer_comment'] ?? $draft['reviewer_comments'] ?? $draft['admin_comments'] ?? '');
        $commentValue = $reviewerComment === '' ? null : $this->appendReviewerComment($existingComment, $reviewerComment);
        if ($this->hasReviewerCommentColumn) {
            $update['reviewer_comment'] = $commentValue;
        }
        if ($this->hasReviewerCommentsColumn) {
            $update['reviewer_comments'] = $commentValue;
        }
        if ($this->hasAdminCommentsColumn) {
            $update['admin_comments'] = $commentValue;
        }
        if ($this->reviewerIdColumn !== '') {
            $update[$this->reviewerIdColumn] = (int) (session()->get('user_id') ?? 0);
        }

        if ($this->hasAcceptanceTypeColumn) {
            $update['acceptance_type'] = $status === 'accepted' ? $acceptanceType : null;
        }

        if ($finalFile !== null) {
            $baseVersion = (int) ($draft['version'] ?? 1);
            if ($baseVersion <= 0) {
                $baseVersion = 1;
            }
            $reviewRevision = $this->nextReviewRevision($draft, $baseVersion);
            $reviewExamType = $this->normalizeExamType($draft['exam_type'] ?? $draft['draft_title'] ?? '');
            if ($reviewExamType === '') {
                return redirect()->back()->with('error', 'This draft is missing an exam type. Update the exam type before reviewing.');
            }

            $newRow = [
                $this->authorIdColumn => $this->draftTeacherId($draft),
                'class_section_id' => (int) ($draft['class_section_id'] ?? 0),
                'semester' => (string) ($draft['semester'] ?? ''),
                'school_year' => (string) ($draft['school_year'] ?? ''),
                'exam_type' => $reviewExamType,
                'draft_title' => $draft['draft_title'] ?? $reviewExamType,
                'author_comment' => $draft['author_comment'] ?? null,
                'status' => $status,
                'reviewed_at' => $update['reviewed_at'],
                'version' => $baseVersion,
                'previous_draft_id' => (int) ($draft['id'] ?? 0) ?: null,
            ];
            if ($this->hasReviewRevisionColumn) {
                $newRow['review_revision'] = $reviewRevision;
            }
            if ($this->hasReviewerCommentColumn) {
                $newRow['reviewer_comment'] = $commentValue;
            }
            if ($this->hasReviewerCommentsColumn) {
                $newRow['reviewer_comments'] = $commentValue;
            }
            if ($this->hasAdminCommentsColumn) {
                $newRow['admin_comments'] = $commentValue;
            }
            if ($this->reviewerIdColumn !== '') {
                $newRow[$this->reviewerIdColumn] = $update[$this->reviewerIdColumn] ?? null;
            }
            if ($this->hasAcceptanceTypeColumn) {
                $newRow['acceptance_type'] = $status === 'accepted' ? $acceptanceType : null;
            }
            if ($this->hasIsLegacyColumn && !empty($draft['is_legacy'])) {
                $newRow['is_legacy'] = 1;
            }
            $newRow['final_file'] = $finalFile;
            $newRow['final_filename'] = $finalFilename;
            $pdfName = $this->ensurePdfExists($finalFile, $file ? $file->getClientExtension() : null);
            if ($pdfName !== null && $this->hasFinalPdfColumn) {
                $newRow['final_pdf_file'] = $pdfName;
            }
            if ($this->examDraftModel->insert($newRow)) {
                $newId = (int) $this->examDraftModel->getInsertID();
                $after = $newId > 0 ? $this->examDraftModel->find($newId) : null;
                $this->notifyExamDraftEvent($after, 'review_' . $status);
                return redirect()->back()->with('success', 'Review saved successfully.');
            }
            return redirect()->back()->with('error', 'Unable to save the review.');
        }
        if ($status === 'legacy') {
            $this->prepareLegacyPdfVersion($update, $draft);
        }
        if ($this->hasIsLegacyColumn) {
            $update['is_legacy'] = strtolower($status) === 'legacy' ? 1 : 0;
        }

        $updated = $this->examDraftModel
            ->set($update)
            ->where('id', $draftId)
            ->update();

        if ($updated) {
            $after = $this->examDraftModel->find($draftId);
            $this->notifyExamDraftEvent($after, 'review_' . $status);
            return redirect()->back()->with('success', 'Review saved successfully.');
        }

        return redirect()->back()->with('error', 'Unable to save the review.');
    }

    public function teacherPrint(int $id)
    {
        $teacherId = (int) (session()->get('user_id') ?? 0);
        if ($teacherId <= 0) {
            return redirect()->to('/login');
        }
        $draft = $this->findDraft($id);
        if (empty($draft) || $this->draftTeacherId($draft) !== $teacherId) {
            return redirect()->to('/teacher/exam-drafts')->with('error', 'Submission not found.');
        }
        $all = $this->examDraftModel
            ->select($this->draftSelectColumns())
            ->where($this->authorIdColumn, $teacherId)
            ->findAll();
        $printable = $this->printableDraftIds($all);
        if (!in_array($id, $printable, true)) {
            return redirect()->to('/teacher/exam-drafts')->with('error', 'Only the latest accepted revision can be printed.');
        }

        return $this->streamFinalPdf($draft);
    }

    public function teacherStatusFeed()
    {
        $this->syncAcademicContext();

        $teacherId = (int) (session()->get('user_id') ?? 0);
        if ($teacherId <= 0) {
            return $this->response->setStatusCode(401);
        }

        $assignments = $this->teacherClassModel->getClassAssignmentsByUserId($teacherId, $this->schoolYear);
        $classSectionIds = array_map(static fn($a) => (int) $a['class_section_id'], $assignments);

        $drafts = $this->visibleTeacherDraftsQuery($teacherId, $classSectionIds)
            ->select('exam_drafts.id, exam_drafts.status, exam_drafts.acceptance_type, exam_drafts.updated_at, exam_drafts.reviewed_at')
            ->orderBy('updated_at', 'DESC')
            ->findAll();

        $payload = [
            'drafts' => array_map(static fn(array $row): array => [
                'id' => (int) ($row['id'] ?? 0),
                'status' => strtolower((string) ($row['status'] ?? '')),
                'acceptance_type' => (string) ($row['acceptance_type'] ?? ''),
                'updated_at' => (string) ($row['updated_at'] ?? ''),
                'reviewed_at' => (string) ($row['reviewed_at'] ?? ''),
            ], $drafts),
        ];

        return $this->response->setJSON($payload);
    }

    public function adminPrint(int $id)
    {
        $adminId = (int) (session()->get('user_id') ?? 0);
        if ($adminId <= 0) {
            return redirect()->to('/login');
        }
        $draft = $this->findDraft($id);
        if (empty($draft)) {
            return redirect()->to('/administrator/exam-drafts')->with('error', 'Submission not found.');
        }
        $all = $this->examDraftModel->select($this->draftSelectColumns())->findAll();
        $printable = $this->printableDraftIds($all);
        if (!in_array($id, $printable, true)) {
            return redirect()->to('/administrator/exam-drafts')->with('error', 'Only the latest accepted revision can be printed.');
        }

        return $this->streamFinalPdf($draft);
    }

    private function resolveSelectedClassSection(array $assignments): int
    {
        $candidate = (int) ($this->request->getGet('class_section_id') ?? session()->get('class_section_id') ?? 0);
        $validIds = array_map('intval', array_column($assignments, 'class_section_id'));
        if ($candidate > 0 && in_array($candidate, $validIds, true)) {
            return $candidate;
        }
        return $validIds[0] ?? 0;
    }

    private function visibleTeacherDraftsQuery(int $teacherId, array $classSectionIds)
    {
        $query = $this->examDraftModel
            ->select($this->draftSelectColumns())
            ->select('cs.class_section_name, u.firstname AS teacher_first, u.lastname AS teacher_last')
            ->join('classSection cs', 'cs.class_section_id = exam_drafts.class_section_id', 'left')
            ->join('users u', 'u.id = exam_drafts.' . $this->authorIdColumn, 'left');

        $this->applyExamDraftYearScope($query);

        if (empty($classSectionIds)) {
            return $query->where('exam_drafts.' . $this->authorIdColumn, $teacherId);
        }

        $query->groupStart()
            ->where('exam_drafts.' . $this->authorIdColumn, $teacherId)
            ->orGroupStart()
                ->whereIn('exam_drafts.class_section_id', $classSectionIds)
                ->where('exam_drafts.' . $this->authorIdColumn . ' !=', $teacherId)
                ->where('exam_drafts.status !=', 'draft');

        $query->groupEnd()
            ->groupEnd();

        return $query;
    }

    private function syncAcademicContext(): void
    {
        $this->schoolYear = $this->currentSchoolYearName((string) ($this->configModel->getConfig('school_year') ?? ''));
        $this->semester = (string) ($this->configModel->getConfig('semester') ?? '');
    }

    private function applyExamDraftYearScope($query): void
    {
        if ($this->schoolYear !== '') {
            $query->where('exam_drafts.school_year', $this->schoolYear);
        }
    }

    private function attachTeacherDraftContext(array $row, int $viewerId): array
    {
        $isOwn = $this->draftTeacherId($row) === $viewerId;
        $row['is_own_submission'] = $isOwn;
        $row['teacher_display_name'] = $isOwn ? 'You' : $this->draftTeacherName($row);

        return $row;
    }

    private function draftTeacherName(array $draft): string
    {
        $name = trim(((string) ($draft['teacher_first'] ?? '')) . ' ' . ((string) ($draft['teacher_last'] ?? '')));
        if ($name !== '') {
            return $name;
        }

        $teacherId = $this->draftTeacherId($draft);
        return $teacherId > 0 ? 'Teacher #' . $teacherId : 'Teacher';
    }

    private function statusBadgeMap(): array
    {
        return [
            'submitted' => [
                'label' => 'Submitted',
                'class' => 'bg-info text-dark',
            ],
            'under review' => [
                'label' => 'Under review',
                'class' => 'bg-warning text-dark',
            ],
            'review needed' => [
                'label' => 'Review needed',
                'class' => 'text-dark',
                'style' => 'background-color:#fd7e14;',
            ],
            'accepted' => [
                'label' => 'Accepted',
                'class' => 'bg-success text-white',
            ],
            'legacy' => [
                'label' => 'Legacy',
                'class' => 'bg-secondary text-white',
            ],
            'rejected' => [
                'label' => 'Rejected',
                'class' => 'bg-danger text-white',
            ],
            'canceled' => [
                'label' => 'Canceled',
                'class' => 'bg-danger text-white',
            ],
        ];
    }

    private function draftSelectColumns(): string
    {
        $columns = ['exam_drafts.*'];
        if ($this->authorIdColumn !== 'teacher_id') {
            $columns[] = 'exam_drafts.' . $this->authorIdColumn . ' AS teacher_id';
        }
        if ($this->authorFileColumn !== 'teacher_file') {
            $columns[] = 'exam_drafts.' . $this->authorFileColumn . ' AS teacher_file';
        }
        if ($this->authorFilenameColumn !== 'teacher_filename') {
            $columns[] = 'exam_drafts.' . $this->authorFilenameColumn . ' AS teacher_filename';
        }
        return implode(', ', $columns);
    }

    private function applyAuthorFilePayload(array &$payload, ?string $file, ?string $filename): void
    {
        if ($file === null) {
            return;
        }
        $payload[$this->authorFileColumn] = $file;
        $payload[$this->authorFilenameColumn] = $filename;
    }

    private function draftTeacherId(array $draft): int
    {
        if (array_key_exists('teacher_id', $draft)) {
            return (int) $draft['teacher_id'];
        }
        return (int) ($draft[$this->authorIdColumn] ?? 0);
    }

    private function draftTeacherFile(array $draft): ?string
    {
        if (array_key_exists('teacher_file', $draft)) {
            return $draft['teacher_file'] ?? null;
        }
        return $draft[$this->authorFileColumn] ?? null;
    }

    private function draftTeacherFilename(array $draft): ?string
    {
        if (array_key_exists('teacher_filename', $draft)) {
            return $draft['teacher_filename'] ?? null;
        }
        return $draft[$this->authorFilenameColumn] ?? null;
    }

    private function findDraft(int $id): ?array
    {
        return $this->examDraftModel
            ->select($this->draftSelectColumns())
            ->where('id', $id)
            ->first();
    }

    private function ensureDraftStatus(int $id, string $status): ?array
    {
        $saved = $this->examDraftModel->find($id);
        if (empty($saved)) {
            return null;
        }
        $current = strtolower((string) ($saved['status'] ?? ''));
        if ($current !== $status) {
            $this->examDraftModel->update($id, ['status' => $status]);
            $saved = $this->examDraftModel->find($id);
        }
        return $saved;
    }

    private function notifyPrincipalExamDraftSubmitted(?array $draft, int $teacherId, int $classSectionId): void
    {
        $principalEmail = trim((string) (env('PRINCIPAL_EMAIL') ?: ''));
        if ($principalEmail === '' || !filter_var($principalEmail, FILTER_VALIDATE_EMAIL)) {
            return;
        }
        if (empty($draft)) {
            return;
        }

        try {
            $teacher = $this->userModel->find($teacherId) ?? [];
            $teacherName = trim(($teacher['firstname'] ?? '') . ' ' . ($teacher['lastname'] ?? ''));
            if ($teacherName === '') {
                $teacherName = 'Teacher #' . $teacherId;
            }

            $class = $this->classSectionModel->where('class_section_id', (int) $classSectionId)->first() ?? [];
            $className = $class['class_section_name'] ?? ('Class ' . $classSectionId);

            $subject = 'Exam draft submitted: ' . $className;
            $submittedAt = $draft['created_at'] ?? $draft['updated_at'] ?? '';
            $body = '<p>A teacher has submitted a new exam draft.</p>'
                . '<table style="border-collapse:collapse;">'
                . '<tr><td style="padding:4px 8px;"><strong>Teacher</strong></td><td style="padding:4px 8px;">' . esc($teacherName) . '</td></tr>'
                . '<tr><td style="padding:4px 8px;"><strong>Class</strong></td><td style="padding:4px 8px;">' . esc($className) . '</td></tr>'
                . '<tr><td style="padding:4px 8px;"><strong>Exam type</strong></td><td style="padding:4px 8px;">' . esc($draft['exam_type'] ?? 'N/A') . '</td></tr>'
                . '<tr><td style="padding:4px 8px;"><strong>Version</strong></td><td style="padding:4px 8px;">' . esc((string) ($draft['version'] ?? '')) . '</td></tr>'
                . '<tr><td style="padding:4px 8px;"><strong>Status</strong></td><td style="padding:4px 8px;">' . esc((string) ($draft['status'] ?? 'submitted')) . '</td></tr>'
                . '<tr><td style="padding:4px 8px;"><strong>Submitted at</strong></td><td style="padding:4px 8px;">' . esc((string) $submittedAt) . '</td></tr>'
                . '</table>'
                . '<p>Review drafts: <a href="' . esc(base_url($this->reviewRoutePrefix() . '/exam-drafts')) . '">' . esc($this->reviewPortalLabel()) . ' exam drafts</a></p>';

            $mailer = \Config\Services::emailService();
            $mailer->send($principalEmail, $subject, $body, 'notifications');
        } catch (\Throwable $e) {
            log_message('error', 'Failed to send exam draft notification: ' . $e->getMessage());
        }
    }

    private function reviewDashboardPath(): string
    {
        return '/' . $this->reviewRoutePrefix() . '/exam-drafts';
    }

    private function reviewRoutePrefix(): string
    {
        return $this->isPrincipalReviewer() ? 'principal' : 'administrator';
    }

    private function reviewPortalLabel(): string
    {
        return $this->isPrincipalReviewer() ? 'Principal' : 'Administrator';
    }

    private function isPrincipalReviewer(): bool
    {
        $roles = session()->get('roles');
        if (!is_array($roles)) {
            $roles = $roles === null ? [] : [$roles];
        }

        foreach ($roles as $role) {
            $normalized = strtolower(trim(str_replace([' ', '-'], '_', (string) $role)));
            if ($normalized === 'principal') {
                return true;
            }
        }

        return false;
    }

    private function nextDraftVersion(array $draft): int
    {
        $query = $this->examDraftModel
            ->select('version')
            ->where($this->authorIdColumn, $this->draftTeacherId($draft))
            ->where('class_section_id', (int) ($draft['class_section_id'] ?? 0))
            ->where('semester', (string) ($draft['semester'] ?? ''))
            ->where('school_year', (string) ($draft['school_year'] ?? ''));

        $examType = $draft['exam_type'] ?? null;
        if ($examType === null || $examType === '') {
            $query = $query->where('exam_type', null);
        } else {
            $query = $query->where('exam_type', $examType);
        }

        $latest = $query->orderBy('version', 'DESC')->first();
        $current = (int) ($latest['version'] ?? 0);
        if ($current <= 0) {
            $current = (int) ($draft['version'] ?? 0);
        }
        if ($current <= 0) {
            $current = 1;
        }
        return $current + 1;
    }

    private function nextReviewRevision(array $draft, int $baseVersion): int
    {
        if (!$this->hasReviewRevisionColumn) {
            return 1;
        }

        $query = $this->examDraftModel
            ->select('review_revision')
            ->where($this->authorIdColumn, $this->draftTeacherId($draft))
            ->where('class_section_id', (int) ($draft['class_section_id'] ?? 0))
            ->where('semester', (string) ($draft['semester'] ?? ''))
            ->where('school_year', (string) ($draft['school_year'] ?? ''))
            ->where('version', $baseVersion);

        $examType = $draft['exam_type'] ?? null;
        if ($examType === null || $examType === '') {
            $query = $query->where('exam_type', null);
        } else {
            $query = $query->where('exam_type', $examType);
        }

        $latest = $query->orderBy('review_revision', 'DESC')->first();
        $current = (int) ($latest['review_revision'] ?? 0);
        if ($current <= 0) {
            $current = (int) ($draft['review_revision'] ?? 0);
        }
        return $current + 1;
    }

    private function appendReviewerComment(string $existing, string $incoming): string
    {
        $existing = trim($existing);
        $incoming = trim($incoming);
        if ($incoming === '') {
            return $existing;
        }

        if ($existing !== '' && str_starts_with($incoming, $existing)) {
            $remainder = trim(substr($incoming, strlen($existing)));
            if ($remainder === '') {
                return $existing;
            }
            $incoming = $remainder;
        }

        $lines = $existing === '' ? [] : preg_split('/\R/', $existing);
        $lastLine = $lines ? trim((string) end($lines)) : '';
        if ($lastLine !== '') {
            $lastBody = preg_replace('/^\d+\-\s*/', '', $lastLine);
            if ($lastBody !== null && trim($lastBody) === $incoming) {
                return $existing;
            }
        }
        $nextIndex = count($lines) + 1;
        $incoming = trim(preg_replace('/\R+/', ' ', $incoming) ?? $incoming);
        $lines[] = $nextIndex . '- ' . $incoming;
        return implode("\n", $lines);
    }

    private function statusOptions(): array
    {
        return ['submitted', 'accepted', 'review needed', 'rejected', 'canceled', 'under review', 'legacy'];
    }

    private function normalizeStatus(string $input, string $default): string
    {
        $input = strtolower(trim($input));
        $aliases = [
            'submitted' => 'submitted',
            'under review' => 'under review',
            'under_review' => 'under review',
            'reviewed' => 'review needed',
            'review needed' => 'review needed',
            'review_needed' => 'review needed',
            'accepted' => 'accepted',
            'approved' => 'accepted',
            'final' => 'accepted',
            'legacy' => 'legacy',
            'archived' => 'legacy',
            'cancelled' => 'canceled',
            'cancel' => 'canceled',
            'rejected' => 'rejected',
            'draft' => 'submitted',
        ];
        if (isset($aliases[$input])) {
            $input = $aliases[$input];
        }
        return in_array($input, $this->statusOptions(), true) ? $input : $default;
    }

    /**
     * One printable row per exam line: max(version) among accepted rows.
     *
     * @param list<array<string,mixed>> $rows
     * @return list<int>
     */
    private function printableDraftIds(array $rows): array
    {
        $best = [];
        foreach ($rows as $row) {
            if (strtolower((string) ($row['status'] ?? '')) !== 'accepted') {
                continue;
            }
            $key = $this->examDraftLineKey($row);
            $ver = (int) ($row['version'] ?? 0);
            $id = (int) ($row['id'] ?? 0);
            if ($id <= 0) {
                continue;
            }
            if (!isset($best[$key]) || $ver > $best[$key]['version']) {
                $best[$key] = ['version' => $ver, 'id' => $id];
            }
        }

        return array_values(array_map(static fn(array $g): int => $g['id'], $best));
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @param list<int> $printableIds
     * @return list<array<string,mixed>>
     */
    private function attachPrintableFlags(array $rows, array $printableIds): array
    {
        foreach ($rows as &$row) {
            $row['is_printable'] = in_array((int) ($row['id'] ?? 0), $printableIds, true);
        }
        unset($row);

        return $rows;
    }

    private function examDraftLineKey(array $row): string
    {
        return implode('|', [
            $this->draftTeacherId($row),
            (int) ($row['class_section_id'] ?? 0),
            (string) ($row['school_year'] ?? ''),
            strtolower(trim((string) ($row['semester'] ?? ''))),
            (string) ($row['exam_type'] ?? ''),
        ]);
    }

    private function examDraftVersionKey(array $row): string
    {
        return implode('|', [
            $this->draftTeacherId($row),
            (int) ($row['class_section_id'] ?? 0),
            (string) ($row['school_year'] ?? ''),
            strtolower(trim((string) ($row['semester'] ?? ''))),
            (string) ($row['exam_type'] ?? ''),
            (int) ($row['version'] ?? 0),
        ]);
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return list<array<string,mixed>>
     */
    private function groupReviewRevisions(array $rows): array
    {
        $grouped = [];

        foreach ($rows as $row) {
            $key = $this->examDraftVersionKey($row);
            $reviewRev = (int) ($row['review_revision'] ?? 0);

            if ($reviewRev > 0) {
            if (!isset($grouped[$key])) {
                $grouped[$key] = $row;
                $grouped[$key]['_is_review_row'] = true;
                $grouped[$key]['review_files'] = [];
                $grouped[$key]['final_version'] = null;
                $grouped[$key]['_latest_review_comment'] = null;
            }
            $grouped[$key]['review_files'][] = [
                'review_revision' => $reviewRev,
                'final_file' => $row['final_file'] ?? null,
                'final_filename' => $row['final_filename'] ?? null,
                'status' => $row['status'] ?? null,
            ];
            $reviewComment = $row['reviewer_comment'] ?? $row['admin_comments'] ?? null;
            if ($reviewComment !== null && $reviewComment !== '') {
                $latestComment = $grouped[$key]['_latest_review_comment'] ?? null;
                if ($latestComment === null || $reviewRev > (int) ($latestComment['review_revision'] ?? 0)) {
                    $grouped[$key]['_latest_review_comment'] = [
                        'review_revision' => $reviewRev,
                        'comment' => $reviewComment,
                    ];
                }
            }
            if (strtolower((string) ($row['status'] ?? '')) === 'accepted') {
                $currentFinal = $grouped[$key]['final_version'] ?? null;
                if ($currentFinal === null || $reviewRev > (int) ($currentFinal['review_revision'] ?? 0)) {
                    $grouped[$key]['final_version'] = [
                        'review_revision' => $reviewRev,
                        'final_file' => $row['final_file'] ?? null,
                        'final_filename' => $row['final_filename'] ?? null,
                    ];
                }
            }
            continue;
        }

            if (!isset($grouped[$key])) {
                $grouped[$key] = $row;
                $grouped[$key]['review_files'] = [];
                continue;
            }

            if (!empty($grouped[$key]['_is_review_row'])) {
                $reviewFiles = $grouped[$key]['review_files'] ?? [];
                $latestReviewComment = $grouped[$key]['_latest_review_comment'] ?? null;
                $grouped[$key] = $row;
                $grouped[$key]['review_files'] = $reviewFiles;
                if (!empty($latestReviewComment['comment'])) {
                    $grouped[$key]['reviewer_comment'] = $latestReviewComment['comment'];
                }
                unset($grouped[$key]['_is_review_row']);
            }
        }

        foreach ($grouped as &$row) {
            if (!empty($row['review_files']) && is_array($row['review_files'])) {
                usort($row['review_files'], static fn($a, $b) => ($a['review_revision'] ?? 0) <=> ($b['review_revision'] ?? 0));
            }
            if (!empty($row['final_version']) && is_array($row['final_version'])) {
                $row['final_file'] = $row['final_version']['final_file'] ?? $row['final_file'] ?? null;
                $row['final_filename'] = $row['final_version']['final_filename'] ?? $row['final_filename'] ?? null;
            }
            if (empty($row['reviewer_comment']) && !empty($row['_latest_review_comment']['comment'])) {
                $row['reviewer_comment'] = $row['_latest_review_comment']['comment'];
            }
            unset($row['_is_review_row']);
            unset($row['_latest_review_comment']);
        }
        unset($row);

        return array_values($grouped);
    }

    private function notifyExamDraftEvent(?array $draft, string $event): void
    {
        if (empty($draft)) {
            return;
        }
        $id = (int) ($draft['id'] ?? 0);
        $version = (int) ($draft['version'] ?? 1);
        log_message('info', "Exam draft notification [{$event}] id={$id} version={$version}");
    }

    private function streamFinalPdf(array $draft)
    {
        $storedName = !empty($draft['final_pdf_file'])
            ? (string) $draft['final_pdf_file']
            : (string) ($draft['final_file'] ?? '');
        if ($storedName === '') {
            return redirect()->back()->with('error', 'No final file is available for printing.');
        }
        $ext = strtolower(pathinfo($storedName, PATHINFO_EXTENSION));
        if ($ext !== 'pdf') {
            $pdfName = $this->ensurePdfExists($storedName, $ext);
            if ($pdfName !== null) {
                $storedName = $pdfName;
            }
        }
        $path = $this->fullUploadPath(self::FINAL_UPLOAD_DIR, $storedName);
        if (!is_file($path)) {
            return redirect()->back()->with('error', 'The file could not be found.');
        }
        $downloadName = $draft['final_filename'] ?? 'exam.pdf';
        if (strtolower(pathinfo($downloadName, PATHINFO_EXTENSION)) !== 'pdf') {
            $downloadName = pathinfo($downloadName, PATHINFO_FILENAME) . '.pdf';
        }

        $body = file_get_contents($path);
        if ($body === false) {
            return redirect()->back()->with('error', 'Unable to read the file.');
        }

        return $this->response
            ->setContentType('application/pdf')
            ->setHeader('Content-Disposition', 'inline; filename="' . basename($downloadName) . '"')
            ->setBody($body);
    }

    private function storeUploadedFile(UploadedFile $file, string $subdir, array $allowedExtensions = self::ALLOWED_EXTENSIONS): ?string
    {
        $ext = strtolower($file->getClientExtension());
        if (!in_array($ext, $allowedExtensions, true)) {
            return null;
        }
        if ($file->getSize() > self::MAX_UPLOAD_BYTES) {
            return null;
        }

        $destination = WRITEPATH . 'uploads/' . trim($subdir, '/');
        if (!is_dir($destination)) {
            mkdir($destination, 0755, true);
        }

        $filename = $file->getRandomName();
        try {
            $file->move($destination, $filename);
            return $filename;
        } catch (\Throwable $e) {
            log_message('error', 'ExamDraftController::storeUploadedFile error: ' . $e->getMessage());
            return null;
        }
    }

    private function convertDocToPdf(string $sourcePath, string $targetSubdir): ?string
    {
        // Only attempt conversion for doc/docx
        $ext = strtolower(pathinfo($sourcePath, PATHINFO_EXTENSION));
        if (!in_array($ext, ['doc', 'docx'], true)) {
            return null;
        }

        $targetDir = WRITEPATH . 'uploads/' . trim($targetSubdir, '/');
        if (!is_dir($targetDir)) {
            mkdir($targetDir, 0755, true);
        }

        $base = pathinfo($sourcePath, PATHINFO_FILENAME);
        $targetPath = $targetDir . '/' . $base . '.pdf';

        if (!function_exists('exec')) {
            log_message('warning', 'ExamDraftController::convertDocToPdf skipped because exec() is unavailable.');
            return is_file($targetPath) ? basename($targetPath) : null;
        }

        // Attempt conversion via LibreOffice if available
        $cmd = 'soffice --headless --convert-to pdf --outdir ' . escapeshellarg($targetDir) . ' ' . escapeshellarg($sourcePath) . ' 2>/dev/null';
        @\exec($cmd);

        return is_file($targetPath) ? basename($targetPath) : null;
    }

    private function schemaHasColumn(string $table, string $column): bool
    {
        try {
            $fields = $this->db->getFieldNames($table);
            return in_array($column, $fields, true);
        } catch (\Throwable $e) {
            log_message('error', "Schema check failed for {$table}.{$column}: " . $e->getMessage());
            return false;
        }
    }

    private function fullUploadPath(string $subdir, string $filename): string
    {
        return WRITEPATH . 'uploads/' . trim($subdir, '/') . '/' . $filename;
    }

    private function neighborPdfIfExists(?string $filename, string $subdir): ?string
    {
        if (empty($filename)) return null;
        $path = $this->fullUploadPath($subdir, $filename);
        $base = pathinfo($path, PATHINFO_FILENAME);
        $dir  = pathinfo($path, PATHINFO_DIRNAME);
        $pdfPath = $dir . '/' . $base . '.pdf';
        return is_file($pdfPath) ? basename($pdfPath) : null;
    }

    private function ensurePdfExists(string $finalFilename, ?string $originalExt): ?string
    {
        $pdfNeighbor = $this->neighborPdfIfExists($finalFilename, self::FINAL_UPLOAD_DIR);
        if ($pdfNeighbor !== null) {
            return $pdfNeighbor;
        }
        $ext = strtolower((string)$originalExt);
        if ($ext === 'pdf') {
            // final file itself is already pdf
            $path = $this->fullUploadPath(self::FINAL_UPLOAD_DIR, $finalFilename);
            return is_file($path) ? $finalFilename : null;
        }
        return $this->convertDocToPdf(
            $this->fullUploadPath(self::FINAL_UPLOAD_DIR, $finalFilename),
            self::FINAL_UPLOAD_DIR
        );
    }

    private function normalizeExamType($value): string
    {
        return trim((string) $value);
    }

    private function copyDraftToFinal(string $draftFilename): ?string
    {
        $source = $this->fullUploadPath(self::TEACHER_UPLOAD_DIR, $draftFilename);
        if (!is_file($source)) {
            return null;
        }
        $destinationDir = WRITEPATH . 'uploads/' . trim(self::FINAL_UPLOAD_DIR, '/');
        if (!is_dir($destinationDir)) {
            mkdir($destinationDir, 0755, true);
        }
        $ext = pathinfo($draftFilename, PATHINFO_EXTENSION);
        $destName = uniqid('final_', true) . '.' . $ext;
        $destPath = $destinationDir . '/' . $destName;
        if (!@copy($source, $destPath)) {
            return null;
        }
        return $destName;
    }

    private function prepareLegacyPdfVersion(array &$update, array $draft): void
    {
        $finalFile = $update['final_file'] ?? $draft['final_file'] ?? null;
        if (empty($finalFile)) {
            $teacherFile = $this->draftTeacherFile($draft);
            if (!empty($teacherFile)) {
                $copied = $this->copyDraftToFinal($teacherFile);
                if ($copied !== null) {
                    $finalFile = $copied;
                    $update['final_file'] = $copied;
                    $update['final_filename'] = $this->draftTeacherFilename($draft) ?? $teacherFile;
                }
            }
        }
        if (empty($finalFile)) {
            return;
        }
        $ext = strtolower(pathinfo($finalFile, PATHINFO_EXTENSION));
        $pdfName = $this->ensurePdfExists($finalFile, $ext);
        if ($pdfName === null) {
            return;
        }
        $filename = $update['final_filename'] ?? $draft['final_filename'] ?? '';
        $baseName = '';
        if ($filename !== '') {
            $baseName = pathinfo($filename, PATHINFO_FILENAME);
        }
        if ($baseName === '') {
            $baseName = pathinfo($pdfName, PATHINFO_FILENAME);
        }
        if ($baseName === '') {
            $baseName = 'exam';
        }
        $update['final_file'] = $pdfName;
        $update['final_filename'] = $baseName . '.pdf';
        if ($this->hasFinalPdfColumn) {
            $update['final_pdf_file'] = $pdfName;
        }
    }
}
