<?php

declare(strict_types=1);

namespace SuperAdmin\Controllers;

use App\Helpers\DocumentGenerator;

/**
 * Super Admin Result Creation Controller
 *
 * Lets Super Admin create student + result in ONE single form (no need for
 * the multi-step branch workflow). Submission AUTO-APPROVES the request and
 * immediately generates both PDFs (certificate + marksheet) using the
 * Pattern System.
 *
 * Key Differences vs Branch CertGen:
 *   - Single-page form (student + result + payment all in one)
 *   - Status set directly to 'generated' (NO approval needed)
 *   - PDF generated immediately after submit
 *   - Institute Name: hardcoded to "GMIND INSTITUTE SULTANPUR"
 *   - Branch Code: hardcoded to "GMBR01"
 *   - branch_id: 0 (or NULL) — super admin has no branch
 *
 * URL prefix: /super-admin/cert-gen/*
 */
class SuperAdminCertGenController extends SuperAdminBaseController
{
    /** ✅ Hardcoded institute identity for Super Admin generated certificates */
    private const DEFAULT_INSTITUTE_NAME = 'GMIND INSTITUTE SULTANPUR';
    private const DEFAULT_BRANCH_CODE    = 'GMBR01';

    /**
     * List all Super-Admin-generated results.
     */
    public function index(): void
    {
        requireSuperAdmin();

        $statusFilter = trim($_GET['status'] ?? '');

        $sql = "SELECT c.*,
                       COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                       COALESCE(c.course_name, cs.name, '—') AS course_name,
                       s.enrollment_no,
                       t.name AS template_name, t.type AS doc_type,
                       b.name AS branch_name, b.branch_code
                FROM certificates c
                LEFT JOIN students s ON c.student_id = s.id
                LEFT JOIN users u ON s.user_id = u.id
                LEFT JOIN courses cs ON c.course_id = cs.id
                LEFT JOIN certificate_templates t ON c.template_id = t.id
                LEFT JOIN branches b ON c.branch_id = b.id
                WHERE (c.branch_id = 0 OR c.branch_id IS NULL)";

        $params = [];
        if ($statusFilter && in_array($statusFilter, ['pending_approval','generated','rejected','cancelled'], true)) {
            $sql .= " AND c.status = :status";
            $params[':status'] = $statusFilter;
        }
        $sql .= " ORDER BY c.created_at DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $documents = $stmt->fetchAll() ?: [];

        $stats = ['total' => count($documents), 'pending' => 0, 'generated' => 0, 'rejected' => 0];
        foreach ($documents as $d) {
            $status = $d['status'] ?? '';
            if ($status === 'pending_approval') $stats['pending']++;
            elseif ($status === 'generated')    $stats['generated']++;
            elseif ($status === 'rejected')     $stats['rejected']++;
        }

        $this->superAdminView('cert_gen/index', [
            'title'        => 'Results (Super Admin)',
            'currentPage'  => 'sa_cert_gen',
            'documents'    => $documents,
            'stats'        => $stats,
            'statusFilter' => $statusFilter,
        ]);
    }

    /**
     * Show the single-page result creation form (student + result + payment).
     */
    public function create(): void
    {
        requireSuperAdmin();

        // Fetch all active courses
        $courses = [];
        try {
            $stmt = $this->db->query("SELECT id, name, duration, subjects FROM courses WHERE active=1 ORDER BY name");
            $courses = $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {}

        // Build months dropdown (last 24 months)
        $months = [];
        $current = strtotime('first day of this month');
        for ($i = 0; $i < 24; $i++) {
            $ts = strtotime("-{$i} months", $current);
            $value = date('Y-m', $ts);
            $label = date('F Y', $ts);
            $months[$value] = $label;
        }

        // Fetch stats for the quick-stats panel
        $stats = ['total' => 0, 'pending' => 0, 'generated' => 0, 'rejected' => 0];
        try {
            $stmt = $this->db->query("SELECT status, COUNT(*) AS c FROM certificates WHERE branch_id IS NULL OR branch_id = 0 GROUP BY status");
            foreach ($stmt->fetchAll() ?: [] as $r) {
                $stats['total'] += (int)$r['c'];
                if ($r['status'] === 'pending_approval') $stats['pending']   = (int)$r['c'];
                elseif ($r['status'] === 'generated')    $stats['generated'] = (int)$r['c'];
                elseif ($r['status'] === 'rejected')     $stats['rejected']  = (int)$r['c'];
            }
        } catch (\Throwable $e) {}

        $this->superAdminView('cert_gen/create', [
            'title'       => 'Create Result (Auto-Approve)',
            'currentPage' => 'sa_cert_gen',
            'courses'     => $courses,
            'months'      => $months,
            'stats'       => $stats,
        ]);
    }

    /**
     * Store the result + AUTO-APPROVE + generate PDF immediately.
     *
     * This is a SINGLE method that does:
     *   1. Validate form
     *   2. Create user + student records (if email doesn't exist)
     *   3. Create certificate + marksheet records with status='generated'
     *   4. Generate PDF for both using Pattern System
     *   5. Save pdf_path
     */
    public function store(): void
    {
        requireSuperAdmin();
        $this->validateCsrf();
        $this->ensurePaymentColumns();
        $this->ensureStudentColumns();

        // ─── 1. Collect form data ─────────────────────────────────────────
        $name           = trim($_POST['name'] ?? '');
        $fatherName     = trim($_POST['father_name'] ?? '');
        $motherName     = trim($_POST['mother_name'] ?? '');
        $dob            = trim($_POST['dob'] ?? '');
        $address        = trim($_POST['address'] ?? '');
        $city           = trim($_POST['city'] ?? '');
        $state          = trim($_POST['state'] ?? '');
        $pinCode        = trim($_POST['pin_code'] ?? '');
        $email          = trim($_POST['email'] ?? '');
        $phone          = trim($_POST['phone'] ?? '');
        $courseId       = (int)($_POST['course_id'] ?? 0);
        $completionMonth = trim($_POST['completion_month'] ?? '');
        $subjectNames   = $_POST['subject_name'] ?? [];
        $maxMarks       = $_POST['max_marks'] ?? [];
        $obtMarks       = $_POST['obtained_marks'] ?? [];
        $issueDate      = trim($_POST['issue_date'] ?? date('Y-m-d'));
        $paymentMode    = trim($_POST['payment_mode'] ?? 'offline');
        $transactionId  = trim($_POST['transaction_id'] ?? '');
        $paymentDate    = trim($_POST['payment_date'] ?? date('Y-m-d'));
        $totalAmount    = (float)($_POST['total_amount'] ?? 200);

        // ─── 2. Validate ──────────────────────────────────────────────────
        $errors = [];
        if (empty($name))            $errors[] = 'Student Name';
        if (empty($fatherName))      $errors[] = 'Father Name';
        if (empty($dob))             $errors[] = 'Date of Birth';
        if (empty($courseId))        $errors[] = 'Course';
        if (empty($email))           $errors[] = 'Email';
        if (empty($phone))           $errors[] = 'Mobile Number';
        if (empty($completionMonth)) $errors[] = 'Completion Month';

        if (!empty($errors)) {
            flash('error', 'Please fill all required fields: ' . implode(', ', $errors));
            redirect(BASE_PATH . '/super-admin/cert-gen/new');
        }

        // ─── 3. Fetch course details ──────────────────────────────────────
        $stmt = $this->db->prepare("SELECT name, duration, subjects, cert_pattern_id, marksheet_pattern_id FROM courses WHERE id = :cid LIMIT 1");
        $stmt->execute([':cid' => $courseId]);
        $course = $stmt->fetch() ?: null;
        if (!$course) {
            flash('error', 'Selected course not found.');
            redirect(BASE_PATH . '/super-admin/cert-gen/new');
        }
        $courseName     = $course['name'];
        $courseDuration = $course['duration'] ?? '6 Months';

        // ─── 4. Calculate marks / grade / division ────────────────────────
        $totalMax = 0; $totalObt = 0;
        $subjectsData = [];
        for ($i = 0; $i < count($subjectNames); $i++) {
            $m = (float)($maxMarks[$i] ?? 100);
            $o = (float)($obtMarks[$i] ?? 0);
            if ($o > $m) $o = $m;
            $totalMax += $m;
            $totalObt += $o;
            $subjectsData[] = ['name' => $subjectNames[$i], 'max' => $m, 'obtained' => $o];
        }
        $percentage = $totalMax > 0 ? round(($totalObt / $totalMax) * 100, 2) : 0;
        $grade      = $this->calculateGrade($percentage);
        $division   = $this->calculateDivision($percentage);

        // ─── 5. Calculate session labels (inclusive) ─────────────────────
        $durationMonths    = $this->parseDurationMonths($courseDuration);
        $startMonthOffset  = max(0, $durationMonths - 1);
        $toDateLabel       = '';
        $fromDateLabel     = '';
        $completionDt      = \DateTime::createFromFormat('Y-m', $completionMonth);
        if ($completionDt) {
            $toDateLabel = strtoupper($completionDt->format('M Y'));
            $fromDt = clone $completionDt;
            $fromDt->modify("-{$startMonthOffset} months");
            $fromDateLabel = strtoupper($fromDt->format('M Y'));
        }

        // ─── 6. Handle photo upload (BEFORE creating student so it can be saved on the user record) ──
        $photoPath = null;
        if (!empty($_FILES['photo']['name']) && $_FILES['photo']['error'] === UPLOAD_ERR_OK) {
            $photoPath = $this->handleImageUpload('photo', 'students');
            if ($photoPath === null) {
                flash('error', '❌ Photo upload failed. Please try a different image (JPG/PNG, max 5MB).');
                redirect(BASE_PATH . '/super-admin/cert-gen/new');
            }
        }

        // ─── 7. Find or create the student ───────────────────────────────
        $studentId = $this->findOrCreateStudent([
            'name'        => $name,
            'father_name' => $fatherName,
            'mother_name' => $motherName,
            'dob'         => $dob,
            'address'     => $address,
            'city'        => $city,
            'state'       => $state,
            'pin_code'    => $pinCode,
            'email'       => $email,
            'phone'       => $phone,
            'course_id'   => $courseId,
            'photo'       => $photoPath,  // ✅ Pass uploaded photo path
        ]);

        if ($studentId <= 0) {
            $errMsg = $this->lastStudentError ?: 'Unknown error';
            flash('error', '❌ Failed to create student record. ' . $errMsg . '. Please try again or check error_log for details.');
            redirect(BASE_PATH . '/super-admin/cert-gen/new');
        }

        // ─── 8. If photo wasn't uploaded this submission, try to fetch existing photo from student record ──
        if (empty($photoPath)) {
            $stmt2 = $this->db->prepare("SELECT u.photo FROM students s LEFT JOIN users u ON s.user_id = u.id WHERE s.id = :sid LIMIT 1");
            $stmt2->execute([':sid' => $studentId]);
            $photoRow = $stmt2->fetch();
            if ($photoRow && !empty($photoRow['photo'])) {
                $photoPath = $photoRow['photo'];
            }
        }

        $addrLine = '';
        $stmt3 = $this->db->prepare("SELECT address, city, state, pin_code FROM students WHERE id = :sid LIMIT 1");
        $stmt3->execute([':sid' => $studentId]);
        $addrRow = $stmt3->fetch();
        if ($addrRow) {
            $parts = array_filter([$addrRow['address'] ?? '', $addrRow['city'] ?? '', $addrRow['state'] ?? '', $addrRow['pin_code'] ?? '']);
            $addrLine = implode(', ', $parts);
        }

        // ─── 8. Fetch certificate + marksheet templates ──────────────────
        $stmt = $this->db->prepare(
            "SELECT * FROM certificate_templates
             WHERE course_id = :cid AND is_active = 1 AND type IN ('certificate','marksheet')"
        );
        $stmt->execute([':cid' => $courseId]);
        $templates = $stmt->fetchAll() ?: [];

        if (empty($templates)) {
            flash('error', 'No certificate/marksheet templates mapped for this course. Please create templates first via Old Mapping or Course Subjects page.');
            redirect(BASE_PATH . '/super-admin/cert-gen/new');
        }

        // ─── 9. Find default locked pattern (fallback) ───────────────────
        $defaultPatternId = null;
        try {
            $stmt = $this->db->query("SELECT id FROM certificate_patterns WHERE is_default = 1 AND status = 'locked' LIMIT 1");
            $defaultPatternId = $stmt->fetchColumn() ?: null;
        } catch (\Throwable $e) {}

        // ─── 10. INSERT cert + marksheet rows + AUTO-GENERATE PDFs ───────
        try {
            $this->db->beginTransaction();
            $generatedDocIds   = [];
            $generatedDocNums  = [];
            $generatedPdfPaths = [];
            $generatedSuccess  = [];   // ✅ Track which PDFs generated OK
            $generatedFailed   = [];   // ✅ Track which PDFs failed + reason

            foreach ($templates as $template) {
                $docType = $template['type'];
                $serialPrefix = ($docType === 'marksheet') ? 'MS' : 'GMIND';
                $stmt = $this->db->prepare("SELECT COUNT(*) AS c FROM certificates WHERE certificate_no LIKE :pfx");
                $stmt->execute([':pfx' => $serialPrefix . '-%']);
                $lastNum = (int)($stmt->fetch()['c'] ?? 0) + 1;
                $documentNumber = $serialPrefix . '-' . date('Y') . '-' . str_pad((string)$lastNum, 5, '0', STR_PAD_LEFT);

                $verificationHash = DocumentGenerator::generateVerificationHash($studentId, $documentNumber);
                $verificationUrl  = DocumentGenerator::buildVerificationUrl($documentNumber, $verificationHash);

                // ✅ Pattern ID — prefer course's per-type pattern, fallback to default
                $patternId = ($docType === 'certificate')
                    ? ($course['cert_pattern_id'] ?? null)
                    : ($course['marksheet_pattern_id'] ?? null);
                if (!$patternId) $patternId = $defaultPatternId;

                $availableCols = $this->getTableColumns('certificates');
                $insertData = [
                    'certificate_no'   => $documentNumber,
                    'student_id'       => $studentId,
                    // ✅ FIX: Use NULL for branch_id (FK constraint may reject 0)
                    'branch_id'        => null,
                    'course_id'        => $courseId,
                    'student_name'     => $name,
                    'course_name'      => $courseName,
                    'grade'            => $grade,
                    'percentage'       => $percentage,
                    'division'         => $division,
                    'issue_date'       => $issueDate,
                    'status'           => 'generated', // ✅ AUTO-APPROVED
                    'template_id'      => $template['id'],
                    'created_by'       => $_SESSION['user_id'] ?? null,
                    'qr_hash'          => $verificationHash,
                    'verification_url' => $verificationUrl,
                    'payment_mode'     => $paymentMode,
                    'transaction_id'   => $transactionId ?: null,
                    'payment_date'     => $paymentDate ?: null,
                    'amount_paid'      => $totalAmount,
                    'subjects_json'    => json_encode([
                        'subjects'       => $subjectNames,
                        'max_marks'      => $maxMarks,
                        'obtained_marks' => $obtMarks,
                        'from_label'     => $fromDateLabel,
                        'to_label'       => $toDateLabel,
                    ]),
                ];

                if (in_array('pattern_id', $availableCols, true) && $patternId) {
                    $insertData['pattern_id'] = $patternId;
                }
                if (in_array('session_from', $availableCols, true)) $insertData['session_from'] = $completionMonth . '-01';
                if (in_array('session_to',   $availableCols, true)) $insertData['session_to']   = $completionMonth . '-01';
                if (in_array('created_at',   $availableCols, true)) $insertData['created_at']   = date('Y-m-d H:i:s');
                if (in_array('updated_at',   $availableCols, true)) $insertData['updated_at']   = date('Y-m-d H:i:s');

                $cols = array_keys($insertData);
                $colList = implode(', ', array_map(fn($c) => "`{$c}`", $cols));
                $phList  = implode(', ', array_map(fn($c) => ":{$c}", $cols));
                $sql = "INSERT INTO certificates ({$colList}) VALUES ({$phList})";
                $params = [];
                foreach ($insertData as $k => $v) $params[":{$k}"] = $v;
                $this->db->prepare($sql)->execute($params);

                $certId = (int)$this->db->lastInsertId();
                $generatedDocIds[]  = $certId;
                $generatedDocNums[] = $documentNumber . ' (' . ucfirst($docType) . ')';

                // ─── Timeline: auto-approved entry ──────────────────────
                $this->db->prepare(
                    "INSERT INTO certificate_timeline (certificate_id, event, description, created_at)
                     VALUES (:cid, 'approved', :desc, NOW())"
                )->execute([
                    ':cid'  => $certId,
                    ':desc' => "Auto-approved by Super Admin. Institute: " . self::DEFAULT_INSTITUTE_NAME . ", Branch Code: " . self::DEFAULT_BRANCH_CODE . ". Grade: {$grade} ({$percentage}%), Division: {$division}.",
                ]);

                // ─── Generate PDF immediately ───────────────────────────
                $pdfPath = $this->generatePdfForCertificate($certId, $docType, $patternId, [
                    'student_name'    => $name,
                    'father_name'     => $fatherName,
                    'mother_name'     => $motherName,
                    'enrollment_no'   => $this->getEnrollmentNo($studentId),
                    'certificate_no'  => $documentNumber,
                    'percentage'      => $percentage . '%',
                    'grade'           => $grade,
                    'division'        => $division,
                    'branch_name'     => self::DEFAULT_INSTITUTE_NAME,   // ✅ Shows "GMIND INSTITUTE SULTANPUR"
                    'branch_code'     => self::DEFAULT_BRANCH_CODE,       // ✅ Shows "GMBR01"
                    'course'          => $courseName,
                    'duration'        => $courseDuration,
                    'date_of_issue'   => !empty($issueDate) ? date('d/m/Y', strtotime($issueDate)) : date('d/m/Y'),
                    'from_date'       => $fromDateLabel,
                    'to_date'         => $toDateLabel,
                    'verification_url'=> $verificationUrl,
                    'qr_code'         => $verificationUrl,
                    'qr_data'         => $verificationUrl,
                    'institute_name'  => self::DEFAULT_INSTITUTE_NAME,
                    'photo'           => $photoPath,
                    'address'         => $addrLine,
                    'subjects'        => $subjectsData,
                ]);

                if ($pdfPath) {
                    $generatedPdfPaths[$certId] = $pdfPath;
                    $generatedSuccess[] = $documentNumber . ' (' . ucfirst($docType) . ')';

                    // Update cert record with pdf_path + pattern_id
                    $this->db->prepare(
                        "UPDATE certificates
                         SET pdf_path = :path,
                             pattern_id = :pid,
                             updated_at = NOW()
                         WHERE id = :id"
                    )->execute([
                        ':path' => $pdfPath,
                        ':pid'  => $patternId,
                        ':id'   => $certId,
                    ]);

                    // Timeline: PDF generated
                    $this->db->prepare(
                        "INSERT INTO certificate_timeline (certificate_id, event, description, created_at)
                         VALUES (:cid, 'pdf_generated', :desc, NOW())"
                    )->execute([
                        ':cid'  => $certId,
                        ':desc' => "PDF auto-generated. Path: {$pdfPath}",
                    ]);
                } else {
                    // ✅ PDF generation failed — capture reason and surface to user
                    $failReason = $this->lastPdfError ?: 'Unknown reason';
                    $generatedFailed[] = $documentNumber . ' (' . ucfirst($docType) . ') — ' . $failReason;

                    // Timeline: PDF generation FAILED
                    $this->db->prepare(
                        "INSERT INTO certificate_timeline (certificate_id, event, description, created_at)
                         VALUES (:cid, 'pdf_failed', :desc, NOW())"
                    )->execute([
                        ':cid'  => $certId,
                        ':desc' => "PDF generation FAILED: " . $failReason,
                    ]);
                }
            }

            $this->db->commit();

            // ✅ Build accurate flash message based on PDF success/failure
            $docList = implode(', ', $generatedDocNums);
            $successCount = count($generatedSuccess);
            $failedCount  = count($generatedFailed);

            if ($failedCount === 0) {
                // All PDFs generated
                flash('success',
                    "✅ Result created & auto-approved! Documents: {$docList}. Grade: {$grade} ({$percentage}%), Division: {$division}. " .
                    "Both PDFs (certificate + marksheet) have been generated. Institute: " . self::DEFAULT_INSTITUTE_NAME . ", Branch Code: " . self::DEFAULT_BRANCH_CODE . "."
                );
            } elseif ($successCount === 0) {
                // All PDFs failed
                $failedList = implode('; ', $generatedFailed);
                flash('error',
                    "⚠️ Result created & auto-approved (status=generated), but PDF generation FAILED for all documents. " .
                    "Reason: " . $failedList . ". " .
                    "Please ensure the course has a LOCKED pattern assigned with background images uploaded. " .
                    "Go to 'Assign to Courses' page to fix, then click 'Regenerate PDF' on the result list."
                );
            } else {
                // Partial success
                $successList = implode(', ', $generatedSuccess);
                $failedList  = implode('; ', $generatedFailed);
                flash('warning',
                    "⚠️ Result created & auto-approved! Some PDFs generated, some failed. " .
                    "SUCCESS: " . $successList . ". " .
                    "FAILED: " . $failedList . ". " .
                    "Click 'Regenerate PDF' on the result list to retry failed PDFs."
                );
            }
        } catch (\Throwable $e) {
            try { $this->db->rollBack(); } catch (\Throwable $e2) {}
            error_log('SuperAdminCertGen store error: ' . $e->getMessage() . "\n" . $e->getTraceAsString());
            flash('error', 'Error creating result: ' . $e->getMessage());
        }

        redirect(BASE_PATH . '/super-admin/cert-gen');
    }

    /**
     * Regenerate PDF for a certificate (in case it failed during initial creation).
     *
     * Useful when the pattern wasn't assigned when the result was first created,
     * but the user has since assigned a pattern via "Assign to Courses" page.
     */
    public function regenerate(?int $id = null): void
    {
        requireSuperAdmin();

        if (!$id || $id <= 0) {
            flash('error', 'Invalid document ID.');
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        // Load certificate
        $stmt = $this->db->prepare(
            "SELECT c.*,
                    COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                    s.father_name, s.mother_name, s.enrollment_no,
                    s.dob, s.admission_date,
                    COALESCE(c.course_name, cs.name, '—') AS course_name,
                    cs.duration,
                    cs.cert_pattern_id, cs.marksheet_pattern_id,
                    t.type AS doc_type
             FROM certificates c
             LEFT JOIN students s ON c.student_id = s.id
             LEFT JOIN users u ON s.user_id = u.id
             LEFT JOIN courses cs ON c.course_id = cs.id
             LEFT JOIN certificate_templates t ON c.template_id = t.id
             WHERE c.id = :cid AND (c.branch_id = 0 OR c.branch_id IS NULL) LIMIT 1"
        );
        $stmt->execute([':cid' => $id]);
        $doc = $stmt->fetch() ?: null;

        if (!$doc) {
            flash('error', 'Document not found.');
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        $docType = $doc['doc_type'] ?? 'certificate';

        // Determine pattern ID
        $patternId = $doc['pattern_id'] ?? null;
        if (!$patternId) {
            $patternId = ($docType === 'certificate')
                ? ($doc['cert_pattern_id'] ?? null)
                : ($doc['marksheet_pattern_id'] ?? null);
        }
        if (!$patternId) {
            // Fallback to default locked pattern
            try {
                $stmt = $this->db->query("SELECT id FROM certificate_patterns WHERE is_default = 1 AND status = 'locked' LIMIT 1");
                $patternId = $stmt->fetchColumn() ?: null;
            } catch (\Throwable $e) {}
        }

        if (!$patternId) {
            flash('error', "No pattern assigned. Please go to 'Assign to Courses' page and assign a locked pattern to this course first.");
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        // Parse subjects_json
        $subjectsData = [];
        if (!empty($doc['subjects_json'])) {
            $subjectsData = json_decode($doc['subjects_json'], true) ?: [];
        }
        $subjects = [];
        if (!empty($subjectsData['subjects'])) {
            $subjList = $subjectsData['subjects'];
            $maxList  = $subjectsData['max_marks'] ?? [];
            $obtList  = $subjectsData['obtained_marks'] ?? [];
            for ($i = 0; $i < count($subjList); $i++) {
                $subjects[] = [
                    'name'     => $subjList[$i] ?? 'Subject ' . ($i + 1),
                    'max'      => (int)($maxList[$i] ?? 100),
                    'obtained' => (int)($obtList[$i] ?? 0),
                ];
            }
        }

        // Build student data array
        $division = $doc['division'] ?? '';
        if (empty($division)) {
            $pct = (float)($doc['percentage'] ?? 0);
            if ($pct >= 60) $division = 'First Division';
            elseif ($pct >= 45) $division = 'Second Division';
            elseif ($pct >= 33) $division = 'Third Division';
            else $division = 'Fail';
        }

        $studentData = [
            'student_name'    => $doc['student_name'] ?? '',
            'father_name'     => $doc['father_name'] ?? '',
            'mother_name'     => $doc['mother_name'] ?? '',
            'enrollment_no'   => $doc['enrollment_no'] ?? '',
            'certificate_no'  => $doc['certificate_no'] ?? '',
            'percentage'      => ($doc['percentage'] ?? 0) . '%',
            'grade'           => $doc['grade'] ?? '',
            'division'        => $division,
            'branch_name'     => self::DEFAULT_INSTITUTE_NAME,
            'branch_code'     => self::DEFAULT_BRANCH_CODE,
            'course'          => $doc['course_name'] ?? '',
            'duration'        => $doc['duration'] ?? '',
            'date_of_issue'   => !empty($doc['issue_date']) ? date('d/m/Y', strtotime($doc['issue_date'])) : date('d/m/Y'),
            'from_date'       => $subjectsData['from_label'] ?? '',
            'to_date'         => $subjectsData['to_label'] ?? '',
            'verification_url'=> $doc['verification_url'] ?? '',
            'qr_code'         => $doc['verification_url'] ?? '',
            'qr_data'         => $doc['verification_url'] ?? '',
            'institute_name'  => self::DEFAULT_INSTITUTE_NAME,
            'photo'           => '',
            'address'         => '',
            'subjects'        => $subjects,
        ];

        // Fetch photo
        $stmt = $this->db->prepare("SELECT u.photo FROM students s LEFT JOIN users u ON s.user_id = u.id WHERE s.id = :sid LIMIT 1");
        $stmt->execute([':sid' => $doc['student_id']]);
        $photoRow = $stmt->fetch();
        if ($photoRow && !empty($photoRow['photo'])) {
            $studentData['photo'] = $photoRow['photo'];
        }

        // Generate PDF
        $pdfPath = $this->generatePdfForCertificate($id, $docType, $patternId, $studentData);

        if ($pdfPath) {
            // Update cert record
            $this->db->prepare(
                "UPDATE certificates
                 SET pdf_path = :path,
                     pattern_id = :pid,
                     updated_at = NOW()
                 WHERE id = :id"
            )->execute([
                ':path' => $pdfPath,
                ':pid'  => $patternId,
                ':id'   => $id,
            ]);

            // Timeline
            $this->db->prepare(
                "INSERT INTO certificate_timeline (certificate_id, event, description, created_at)
                 VALUES (:cid, 'pdf_regenerated', :desc, NOW())"
            )->execute([
                ':cid'  => $id,
                ':desc' => "PDF regenerated by Super Admin. Path: {$pdfPath}",
            ]);

            flash('success', "✅ PDF regenerated successfully for {$doc['certificate_no']} ({$docType}). Path: {$pdfPath}");
        } else {
            $reason = $this->lastPdfError ?: 'Unknown reason';
            flash('error', "❌ PDF regeneration failed for {$doc['certificate_no']} ({$docType}). Reason: {$reason}");
        }

        redirect(BASE_PATH . '/super-admin/cert-gen');
    }

    /**
     * Download a generated PDF.
     *
     * NOTE: Param name MUST be `$id` (not `$certId`) because the route is
     * /super-admin/cert-gen/download/{id} and Router matches params by name.
     */
    public function download(?int $id = null): void
    {
        requireSuperAdmin();

        if (!$id || $id <= 0) {
            flash('error', 'Invalid document ID.');
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        $stmt = $this->db->prepare(
            "SELECT c.*,
                    COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                    t.name AS template_name, t.type AS doc_type
             FROM certificates c
             LEFT JOIN students s ON c.student_id = s.id
             LEFT JOIN users u ON s.user_id = u.id
             LEFT JOIN certificate_templates t ON c.template_id = t.id
             WHERE c.id = :cid AND (c.branch_id = 0 OR c.branch_id IS NULL) LIMIT 1"
        );
        $stmt->execute([':cid' => $id]);
        $doc = $stmt->fetch() ?: null;

        if (!$doc) {
            flash('error', 'Document not found.');
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }
        if ($doc['status'] !== 'generated') {
            flash('error', 'Document not yet generated.');
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        $pdfPath = $doc['pdf_path'] ?? '';

        // ✅ If pdf_path is empty, try to regenerate the PDF now
        if (empty($pdfPath)) {
            flash('error', 'PDF was not generated when the result was created. This usually means the course has no pattern assigned, or the pattern has no background image. Please assign a locked pattern with background images via "Assign to Courses" page, then edit and re-save the result.');
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        $filePath = $this->resolvePdfPath($pdfPath);

        if (!$filePath) {
            flash('error', 'PDF file not found on server. Stored path: ' . $pdfPath);
            redirect(BASE_PATH . '/super-admin/cert-gen');
        }

        $studentName = preg_replace('/[^a-zA-Z0-9]/', '_', $doc['student_name'] ?? 'student');
        $downloadName = ($doc['doc_type'] ?? 'document') . '_' . $studentName . '_' . ($doc['certificate_no'] ?? $id) . '.pdf';

        $mode = $_GET['mode'] ?? 'view';

        header('Content-Type: application/pdf');
        if ($mode === 'download') {
            header('Content-Disposition: attachment; filename="' . $downloadName . '"');
        } else {
            header('Content-Disposition: inline; filename="' . $downloadName . '"');
        }
        header('Content-Length: ' . filesize($filePath));
        header('Cache-Control: private, max-age=0, must-revalidate');

        while (ob_get_level()) ob_end_clean();
        readfile($filePath);
        exit;
    }

    // ════════════════════════════════════════════════════════════════════════
    // PRIVATE HELPERS
    // ════════════════════════════════════════════════════════════════════════

    /**
     * Find existing student by email, OR create a new user + student record.
     *
     * Returns int > 0 on success, or 0 on failure (with $this->lastStudentError set).
     */
    private string $lastStudentError = '';

    /**
     * Last PDF generation error message (set by generatePdfForCertificate on failure).
     */
    private string $lastPdfError = '';

    private function findOrCreateStudent(array $data): int
    {
        $this->lastStudentError = '';

        // ─── Check if user with this email already exists ────────────────
        $stmt = $this->db->prepare("SELECT id FROM users WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => $data['email']]);
        $existingUser = $stmt->fetch();

        if ($existingUser) {
            $userId = (int)$existingUser['id'];
            // Check if student record already exists for this user
            $stmt = $this->db->prepare("SELECT id FROM students WHERE user_id = :uid LIMIT 1");
            $stmt->execute([':uid' => $userId]);
            $existingStudent = $stmt->fetch();
            if ($existingStudent) {
                // ✅ Student already exists — but if a new photo was uploaded, update it on the user record
                if (!empty($data['photo'])) {
                    try {
                        $this->db->prepare("UPDATE users SET photo = :photo, updated_at = NOW() WHERE id = :uid")
                            ->execute([':photo' => $data['photo'], ':uid' => $userId]);
                    } catch (\Throwable $e) {
                        error_log('SuperAdmin findOrCreate: Failed to update photo on existing user: ' . $e->getMessage());
                    }
                }
                return (int)$existingStudent['id'];
            }
        } else {
            // ─── Create new user ──────────────────────────────────────────
            $password = '';
            $phoneDigits = preg_replace('/\D/', '', $data['phone'] ?? '');
            if (strlen($phoneDigits) >= 6) {
                $password = substr($phoneDigits, -6);
            } else {
                $password = str_pad((string)rand(100000, 999999), 6, '0', STR_PAD_LEFT);
            }
            $hashedPassword = password_hash($password, PASSWORD_DEFAULT);

            // Ensure role enum includes 'student'
            try {
                $this->db->exec("ALTER TABLE `users` MODIFY COLUMN `role` ENUM('super_admin','admin','branch_admin','staff','student') NOT NULL DEFAULT 'student'");
            } catch (\Throwable $e) {}

            $userCols = $this->getTableColumns('users');
            $userData = [];
            if (in_array('name', $userCols, true))        $userData['name']       = $data['name'];
            if (in_array('email', $userCols, true))       $userData['email']      = $data['email'];
            if (in_array('password', $userCols, true))    $userData['password']   = $hashedPassword;
            if (in_array('role', $userCols, true))        $userData['role']       = 'student';
            if (in_array('phone', $userCols, true))       $userData['phone']      = $data['phone'];
            // ✅ FIX: Use NULL for branch_id (FK constraint rejects 0)
            if (in_array('branch_id', $userCols, true))   $userData['branch_id']  = null;
            if (in_array('branch_code', $userCols, true)) $userData['branch_code']= self::DEFAULT_BRANCH_CODE;
            if (in_array('is_active', $userCols, true))   $userData['is_active']  = 1;
            if (in_array('status', $userCols, true))      $userData['status']     = 'active';
            if (in_array('created_at', $userCols, true))  $userData['created_at'] = date('Y-m-d H:i:s');
            if (in_array('updated_at', $userCols, true))  $userData['updated_at'] = date('Y-m-d H:i:s');
            // ✅ Save photo path on user record (so it's queryable via users.photo)
            if (in_array('photo', $userCols, true) && !empty($data['photo'])) {
                $userData['photo'] = $data['photo'];
            }

            $uCols = array_keys($userData);
            $uColList = implode(', ', array_map(fn($c) => "`{$c}`", $uCols));
            $uPhList  = implode(', ', array_map(fn($c) => ":{$c}", $uCols));
            $uSql = "INSERT INTO users ({$uColList}) VALUES ({$uPhList})";
            $uParams = [];
            foreach ($userData as $k => $v) $uParams[":{$k}"] = $v;

            try {
                $this->db->prepare($uSql)->execute($uParams);
            } catch (\Throwable $e) {
                $this->lastStudentError = 'User INSERT failed: ' . $e->getMessage() . ' | SQL: ' . $uSql;
                error_log('SuperAdmin findOrCreate ' . $this->lastStudentError);
                return 0;
            }
            $userId = (int)$this->db->lastInsertId();
        }

        if ($userId <= 0) {
            $this->lastStudentError = 'Failed to get valid user ID after insert.';
            return 0;
        }

        // ─── Generate enrollment no ──────────────────────────────────────
        $year = date('Y');
        $stmt = $this->db->prepare("SELECT enrollment_no FROM students WHERE enrollment_no LIKE :pattern ORDER BY id DESC LIMIT 1");
        $stmt->execute([':pattern' => "GM{$year}%"]);
        $last = $stmt->fetch();
        $seq = 1;
        if ($last && !empty($last['enrollment_no'])) {
            // Extract numeric part after 'GMYYYY' (e.g., GM2026008 → '008' → 8)
            $numPart = substr($last['enrollment_no'], 6);
            $seq = (int)$numPart + 1;
        }
        $enrollmentNo = 'GM' . $year . str_pad((string)$seq, 3, '0', STR_PAD_LEFT);

        // ─── Create student record ───────────────────────────────────────
        $availableCols = $this->getTableColumns('students');
        $insertData = [
            'user_id'        => $userId,
            'enrollment_no'  => $enrollmentNo,
            'course_id'      => $data['course_id'] ?: null,
            // ✅ FIX: Use NULL for branch_id (FK constraint may reject 0)
            'branch_id'      => null,
            'status'         => 'active',
        ];
        if (in_array('father_name', $availableCols, true))    $insertData['father_name']    = $data['father_name'] ?: null;
        if (in_array('mother_name', $availableCols, true))    $insertData['mother_name']    = $data['mother_name'] ?? null;
        if (in_array('dob', $availableCols, true))            $insertData['dob']            = $data['dob'] ?: null;
        if (in_array('admission_date', $availableCols, true)) $insertData['admission_date'] = date('Y-m-d');
        if (in_array('address', $availableCols, true))        $insertData['address']        = $data['address'] ?: null;
        if (in_array('city', $availableCols, true))           $insertData['city']           = $data['city'] ?: null;
        if (in_array('state', $availableCols, true))          $insertData['state']          = $data['state'] ?: null;
        if (in_array('pin_code', $availableCols, true))       $insertData['pin_code']       = $data['pin_code'] ?: null;
        if (in_array('created_at', $availableCols, true))     $insertData['created_at']     = date('Y-m-d H:i:s');
        if (in_array('updated_at', $availableCols, true))     $insertData['updated_at']     = date('Y-m-d H:i:s');

        $cols = array_keys($insertData);
        $colList = implode(', ', array_map(fn($c) => "`{$c}`", $cols));
        $phList  = implode(', ', array_map(fn($c) => ":{$c}", $cols));
        $sql = "INSERT INTO students ({$colList}) VALUES ({$phList})";
        $params = [];
        foreach ($insertData as $k => $v) $params[":{$k}"] = $v;

        try {
            $this->db->prepare($sql)->execute($params);
        } catch (\Throwable $e) {
            $this->lastStudentError = 'Student INSERT failed: ' . $e->getMessage() . ' | SQL: ' . $sql . ' | Params: ' . json_encode($params);
            error_log('SuperAdmin findOrCreate ' . $this->lastStudentError);
            return 0;
        }

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

        if ($studentId <= 0) {
            $this->lastStudentError = 'lastInsertId returned 0 after student INSERT.';
            return 0;
        }

        $this->auditLog('sa_student_created', 'students', $studentId, "Super Admin created student {$data['name']} ({$enrollmentNo}). Branch Code: " . self::DEFAULT_BRANCH_CODE);
        return $studentId;
    }

    /**
     * Generate PDF for a single certificate using Pattern System.
     *
     * Returns relative path on success, or NULL on failure.
     * On failure, sets $this->lastPdfError with a human-readable reason.
     */
    private function generatePdfForCertificate(int $certId, string $docType, ?int $patternId, array $studentData): ?string
    {
        $this->lastPdfError = '';

        if (!$patternId) {
            $this->lastPdfError = "No pattern assigned to this course. Please go to 'Assign to Courses' page and assign a LOCKED pattern.";
            error_log("SuperAdminCertGen generatePdf: {$this->lastPdfError} (cert #{$certId})");
            return null;
        }

        // Load pattern — must be LOCKED
        $stmt = $this->db->prepare("SELECT * FROM certificate_patterns WHERE id = :pid LIMIT 1");
        $stmt->execute([':pid' => $patternId]);
        $pattern = $stmt->fetch() ?: null;

        if (!$pattern) {
            $this->lastPdfError = "Pattern ID {$patternId} does not exist in database.";
            error_log("SuperAdminCertGen generatePdf: {$this->lastPdfError} (cert #{$certId})");
            return null;
        }

        if ($pattern['status'] !== 'locked') {
            $this->lastPdfError = "Pattern '{$pattern['name']}' is not LOCKED (current status: '{$pattern['status']}'). Please lock the pattern first.";
            error_log("SuperAdminCertGen generatePdf: {$this->lastPdfError} (cert #{$certId})");
            return null;
        }

        // Background path
        $bgPath = $docType === 'certificate'
            ? ($pattern['cert_background_path'] ?? '')
            : ($pattern['marksheet_background_path'] ?? '');

        if (empty($bgPath)) {
            $this->lastPdfError = "Pattern '{$pattern['name']}' has no background image for {$docType}. Please upload a JPG background in the pattern editor.";
            error_log("SuperAdminCertGen generatePdf: {$this->lastPdfError} (cert #{$certId})");
            return null;
        }

        // Pattern fields
        $stmt = $this->db->prepare(
            "SELECT * FROM certificate_pattern_fields
             WHERE pattern_id = :pid AND doc_type = :dt AND is_enabled = 1
             ORDER BY id ASC"
        );
        $stmt->execute([':pid' => $patternId, ':dt' => $docType]);
        $fields = $stmt->fetchAll() ?: [];

        // Resolve photo path (try multiple locations)
        if (!empty($studentData['photo'])) {
            $studentData['photo'] = $this->resolvePhotoPath($studentData['photo']);
        }

        try {
            $generator = new DocumentGenerator();
            $pdf = $generator->generateFromPattern([
                'pattern'    => $pattern,
                'fields'     => $fields,
                'student'    => $studentData,
                'doc_type'   => $docType,
                'bg_path'    => $bgPath,
                'is_preview' => false,
            ]);

            // Save PDF — try multiple directories
            $candidateDirs = [
                $_SERVER['DOCUMENT_ROOT'] . '/uploads/certificates/',
                '/home/advaysin/public_html/uploads/certificates/',
                dirname(dirname(dirname(dirname(__DIR__)))) . '/uploads/certificates/',
            ];

            $uploadDir = null;
            foreach ($candidateDirs as $dir) {
                if (!is_dir($dir)) @mkdir($dir, 0755, true);
                if (is_dir($dir) && is_writable($dir)) {
                    $uploadDir = $dir;
                    break;
                }
            }

            if (!$uploadDir) {
                $this->lastPdfError = "Cannot find writable PDF directory. Tried: " . implode(', ', $candidateDirs);
                error_log("SuperAdminCertGen generatePdf: {$this->lastPdfError}");
                return null;
            }

            $filename = $docType . '_' . $certId . '_' . time() . '.pdf';
            $filePath = $uploadDir . $filename;

            $pdf->Output('F', $filePath);

            if (!file_exists($filePath) || filesize($filePath) < 1000) {
                $this->lastPdfError = "PDF save failed or file too small at: " . $filePath;
                error_log("SuperAdminCertGen generatePdf: {$this->lastPdfError}");
                return null;
            }

            // Build relative path
            $pdfRelPath = '/uploads/certificates/' . $filename;
            $docRoot = $_SERVER['DOCUMENT_ROOT'];
            if (strpos($filePath, $docRoot) === 0) {
                $pdfRelPath = substr($filePath, strlen($docRoot));
            }

            error_log("SuperAdminCertGen: PDF generated OK for cert #{$certId}: {$filePath} -> stored as {$pdfRelPath}");
            return $pdfRelPath;
        } catch (\Throwable $e) {
            $this->lastPdfError = "PDF generation exception: " . $e->getMessage();
            error_log('SuperAdminCertGen generatePdf error: ' . $this->lastPdfError);
            return null;
        }
    }

    private function getEnrollmentNo(int $studentId): string
    {
        $stmt = $this->db->prepare("SELECT enrollment_no FROM students WHERE id = :sid LIMIT 1");
        $stmt->execute([':sid' => $studentId]);
        return $stmt->fetchColumn() ?: '';
    }

    private function resolvePhotoPath(string $photoPath): string
    {
        if (empty($photoPath)) return $photoPath;
        if ($photoPath[0] === '/' && file_exists($photoPath)) return realpath($photoPath);

        $docRoot = $_SERVER['DOCUMENT_ROOT'] ?? '/home/advaysin/public_html';
        $scriptDir = dirname(dirname(dirname(__DIR__)));

        $candidates = [];
        if ($photoPath[0] === '/') {
            $candidates[] = $docRoot . $photoPath;
            $candidates[] = $docRoot . '/gmind' . $photoPath;
            $candidates[] = '/home/advaysin/public_html' . $photoPath;
            $candidates[] = '/home/advaysin/public_html/gmind' . $photoPath;
            $candidates[] = $scriptDir . $photoPath;
        } else {
            $candidates[] = $docRoot . '/' . $photoPath;
            $candidates[] = $docRoot . '/gmind/' . $photoPath;
            $candidates[] = '/home/advaysin/public_html/' . $photoPath;
            $candidates[] = '/home/advaysin/public_html/gmind/' . $photoPath;
            $candidates[] = $scriptDir . '/' . $photoPath;
            $candidates[] = dirname(dirname(dirname(dirname(__DIR__)))) . '/' . $photoPath;
        }

        foreach ($candidates as $c) {
            if (file_exists($c)) return realpath($c);
        }
        if (file_exists($photoPath)) return realpath($photoPath);
        return $photoPath;
    }

    private function resolvePdfPath(string $pdfPath): ?string
    {
        if (empty($pdfPath)) return null;

        $candidates = [
            $pdfPath,
            $_SERVER['DOCUMENT_ROOT'] . $pdfPath,
            $_SERVER['DOCUMENT_ROOT'] . '/' . ltrim($pdfPath, '/'),
            rtrim($_SERVER['DOCUMENT_ROOT'], '/') . $pdfPath,
            $_SERVER['DOCUMENT_ROOT'] . '/gmind' . $pdfPath,
            '/home/advaysin/public_html' . $pdfPath,
            '/home/advaysin/public_html/gmind' . $pdfPath,
        ];

        if ($pdfPath[0] !== '/') {
            $candidates[] = $_SERVER['DOCUMENT_ROOT'] . '/' . $pdfPath;
            $candidates[] = '/home/advaysin/public_html/' . $pdfPath;
            $candidates[] = '/home/advaysin/public_html/gmind/' . $pdfPath;
        }

        if (defined('PUBLIC_ROOT')) {
            $candidates[] = PUBLIC_ROOT . '/' . ltrim($pdfPath, '/');
            $candidates[] = PUBLIC_ROOT . $pdfPath;
        }

        foreach ($candidates as $cand) {
            if (file_exists($cand) && is_readable($cand)) return $cand;
        }
        return null;
    }

    private function calculateGrade(float $pct): string
    {
        if ($pct >= 90) return 'A+';
        if ($pct >= 80) return 'A';
        if ($pct >= 70) return 'B+';
        if ($pct >= 60) return 'B';
        if ($pct >= 45) return 'C';
        if ($pct >= 33) return 'D';
        return 'F';
    }

    private function calculateDivision(float $pct): string
    {
        if ($pct >= 60) return 'First Division';
        if ($pct >= 45) return 'Second Division';
        if ($pct >= 33) return 'Third Division';
        return 'Fail';
    }

    private function parseDurationMonths(string $duration): int
    {
        $duration = trim($duration);
        if (preg_match('/^([\d.]+)\s*month/i', $duration, $m)) return (int)round((float)$m[1]);
        if (preg_match('/^([\d.]+)\s*year/i',  $duration, $m)) return (int)round((float)$m[1] * 12);
        if (preg_match('/^([\d.]+)\s*week/i',  $duration, $m)) return (int)round((float)$m[1] / 4);
        if (preg_match('/^([\d.]+)\s*day/i',   $duration, $m)) return (int)round((float)$m[1] / 30);
        return 6;
    }

    private function getTableColumns(string $table): array
    {
        try {
            $stmt = $this->db->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table");
            $stmt->execute([':table' => $table]);
            return array_map('strtolower', $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: []);
        } catch (\Throwable $e) {
            return [];
        }
    }

    private function ensureStudentColumns(): void
    {
        $existing = $this->getTableColumns('students');
        $needed = [
            'address'        => "ADD COLUMN `address` TEXT NULL DEFAULT NULL",
            'city'           => "ADD COLUMN `city` VARCHAR(100) NULL DEFAULT NULL",
            'state'          => "ADD COLUMN `state` VARCHAR(100) NULL DEFAULT NULL",
            'pin_code'       => "ADD COLUMN `pin_code` VARCHAR(20) NULL DEFAULT NULL",
            'father_name'    => "ADD COLUMN `father_name` VARCHAR(150) NULL DEFAULT NULL",
            'mother_name'    => "ADD COLUMN `mother_name` VARCHAR(150) NULL DEFAULT NULL",
            'dob'            => "ADD COLUMN `dob` DATE NULL DEFAULT NULL",
            'admission_date' => "ADD COLUMN `admission_date` DATE NULL DEFAULT NULL",
        ];
        foreach ($needed as $col => $ddl) {
            if (!in_array($col, $existing, true)) {
                try { $this->db->exec("ALTER TABLE `students` {$ddl}"); } catch (\Throwable $e) {}
            }
        }

        // ✅ FIX: Make branch_id nullable on `students` and `users` so Super Admin
        // can create records without a branch. Drop FK first (if exists), then modify column.
        $this->makeBranchIdNullable('students');
        $this->makeBranchIdNullable('users');
        $this->makeBranchIdNullable('certificates');
    }

    /**
     * Drop any foreign key constraint on {table}.branch_id, then alter the column
     * to allow NULL values (with default NULL). Safe to call multiple times.
     */
    private function makeBranchIdNullable(string $table): void
    {
        // Find FK constraint name(s) on this column
        $findFkSql = "SELECT CONSTRAINT_NAME
                      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                      WHERE TABLE_SCHEMA = DATABASE()
                        AND TABLE_NAME = :tbl
                        AND COLUMN_NAME = 'branch_id'
                        AND REFERENCED_TABLE_NAME IS NOT NULL";
        try {
            $stmt = $this->db->prepare($findFkSql);
            $stmt->execute([':tbl' => $table]);
            $fks = $stmt->fetchAll(\PDO::FETCH_COLUMN) ?: [];
            foreach ($fks as $fkName) {
                try {
                    $this->db->exec("ALTER TABLE `{$table}` DROP FOREIGN KEY `{$fkName}`");
                } catch (\Throwable $e) {
                    error_log("Drop FK {$fkName} on {$table} failed: " . $e->getMessage());
                }
            }
        } catch (\Throwable $e) {
            error_log("Find FK on {$table}.branch_id failed: " . $e->getMessage());
        }

        // Now alter the column to be NULL DEFAULT NULL
        // Get column type first
        try {
            $stmt = $this->db->prepare(
                "SELECT COLUMN_TYPE, IS_NULLABLE
                 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :tbl AND COLUMN_NAME = 'branch_id'"
            );
            $stmt->execute([':tbl' => $table]);
            $col = $stmt->fetch();
            if ($col) {
                if (($col['IS_NULLABLE'] ?? 'NO') === 'YES') {
                    return; // already nullable
                }
                $colType = $col['COLUMN_TYPE'] ?? 'BIGINT UNSIGNED';
                try {
                    $this->db->exec("ALTER TABLE `{$table}` MODIFY COLUMN `branch_id` {$colType} NULL DEFAULT NULL");
                } catch (\Throwable $e) {
                    error_log("Modify {$table}.branch_id to NULL failed: " . $e->getMessage());
                }
            }
        } catch (\Throwable $e) {
            error_log("Inspect {$table}.branch_id column failed: " . $e->getMessage());
        }
    }

    private function ensurePaymentColumns(): void
    {
        $existing = $this->getTableColumns('certificates');
        $needed = [
            'payment_mode'   => "ADD COLUMN `payment_mode` VARCHAR(20) NULL DEFAULT NULL",
            'transaction_id' => "ADD COLUMN `transaction_id` VARCHAR(100) NULL DEFAULT NULL",
            'payment_date'   => "ADD COLUMN `payment_date` DATE NULL DEFAULT NULL",
            'amount_paid'    => "ADD COLUMN `amount_paid` DECIMAL(10,2) NULL DEFAULT NULL",
            'division'       => "ADD COLUMN `division` VARCHAR(20) NULL DEFAULT NULL",
            'session_from'   => "ADD COLUMN `session_from` DATE NULL DEFAULT NULL",
            'session_to'     => "ADD COLUMN `session_to` DATE NULL DEFAULT NULL",
            'pdf_path'       => "ADD COLUMN `pdf_path` VARCHAR(500) NULL DEFAULT NULL",
            'subjects_json'  => "ADD COLUMN `subjects_json` TEXT NULL DEFAULT NULL",
            'pattern_id'     => "ADD COLUMN `pattern_id` INT NULL DEFAULT NULL",
        ];
        foreach ($needed as $col => $ddl) {
            if (!in_array($col, $existing, true)) {
                try { $this->db->exec("ALTER TABLE `certificates` {$ddl}"); } catch (\Throwable $e) {}
            }
        }
    }
}
