<?php

declare(strict_types=1);

namespace BranchAdmin\Controllers;

use App\Helpers\DocumentGenerator;

/**
 * Branch Certificate & Marksheet Generation Controller (PATTERN SYSTEM FIXED)
 *
 * Complete 4-step workflow:
 *   1. Student Registration Form (12 fields)
 *   2. Preview + Submit / Create Result
 *   3. Result entry (completion month, subjects, marks, grade auto-fill)
 *   4. PDF generation after Super Admin approval
 *
 * URL prefix: /branch/cert-gen/*
 *
 * ✅ FIXES APPLIED:
 *   - storeResult() now saves pattern_id from course's cert_pattern_id/marksheet_pattern_id
 *   - download() now uses multi-path resolution (8+ paths tried)
 */
class BranchCertGenController extends BranchBaseController
{
    public function index(): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();

        $stmt = $this->db->prepare(
            "SELECT c.*,
                    COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                    s.enrollment_no, u.photo,
                    COALESCE(c.course_name, cs.name, '—') AS course_name,
                    t.type AS doc_type, t.name AS template_name
             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.branch_id = :bid
             ORDER BY c.created_at DESC"
        );
        $stmt->execute([':bid' => $branchId]);
        $documents = $stmt->fetchAll() ?: [];

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

        $this->branchAdminView('cert_gen/track', [
            'title'       => 'Certificate & Marksheet Status',
            'currentPage' => 'cert_gen',
            'documents'   => $documents,
            'stats'       => $stats,
            'csrfToken'   => $this->csrfToken ?? ($_SESSION['csrf_token'] ?? ''),
        ]);
    }

    public function createStudent(): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();

        $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) {}

        $batches = [];
        try {
            $stmt = $this->db->prepare("SELECT id, batch_name FROM batches WHERE branch_id=:bid AND status IN ('upcoming','running') ORDER BY batch_name");
            $stmt->execute([':bid' => $branchId]);
            $batches = $stmt->fetchAll() ?: [];
        } catch (\Throwable $e) {}

        $this->branchAdminView('cert_gen/student_form', [
            'title'       => 'Student Registration',
            'currentPage' => 'cert_gen',
            'courses'     => $courses,
            'batches'     => $batches,
        ]);
    }

    public function previewStudent(): void
    {
        requireBranchAdmin();
        $this->validateCsrf();

        $studentData = [
            'name'         => trim($_POST['name'] ?? ''),
            'father_name'  => trim($_POST['father_name'] ?? ''),
            'dob'          => trim($_POST['dob'] ?? ''),
            'address'      => trim($_POST['address'] ?? ''),
            'city'         => trim($_POST['city'] ?? ''),
            'state'        => trim($_POST['state'] ?? ''),
            'pin_code'     => trim($_POST['pin_code'] ?? ''),
            'course_id'    => (int)($_POST['course_id'] ?? 0),
            'batch_id'     => (int)($_POST['batch_id'] ?? 0),
            'email'        => trim($_POST['email'] ?? ''),
            'phone'        => trim($_POST['phone'] ?? ''),
        ];

        $photoPath = null;
        if (!empty($_FILES['photo']['name']) && $_FILES['photo']['error'] === UPLOAD_ERR_OK) {
            $photoPath = $this->handleImageUpload('photo', 'students');
        }

        $errors = [];
        if (empty($studentData['name'])) $errors[] = 'Student Name';
        if (empty($studentData['father_name'])) $errors[] = 'Father Name';
        if (empty($studentData['dob'])) $errors[] = 'Date of Birth';
        if (empty($studentData['course_id'])) $errors[] = 'Course';
        if (empty($studentData['email'])) $errors[] = 'Email';
        if (empty($studentData['phone'])) $errors[] = 'Mobile Number';

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

        $courseName = '';
        $courseDuration = '';
        $courseSubjects = '[]';
        if ($studentData['course_id'] > 0) {
            $stmt = $this->db->prepare("SELECT name, duration, subjects FROM courses WHERE id=:cid LIMIT 1");
            $stmt->execute([':cid' => $studentData['course_id']]);
            $course = $stmt->fetch();
            if ($course) {
                $courseName = $course['name'];
                $courseDuration = $course['duration'];
                $courseSubjects = $course['subjects'] ?? '[]';
            }
        }

        $batchName = '';
        if ($studentData['batch_id'] > 0) {
            $stmt = $this->db->prepare("SELECT batch_name FROM batches WHERE id=:bid LIMIT 1");
            $stmt->execute([':bid' => $studentData['batch_id']]);
            $batchName = $stmt->fetchColumn() ?: '';
        }

        $_SESSION['cert_gen_student'] = $studentData;
        $_SESSION['cert_gen_photo']   = $photoPath;

        $this->branchAdminView('cert_gen/preview', [
            'title'           => 'Preview Student Details',
            'currentPage'     => 'cert_gen',
            'student'         => $studentData,
            'photoPath'       => $photoPath,
            'courseName'      => $courseName,
            'courseDuration'  => $courseDuration,
            'batchName'       => $batchName,
        ]);
    }

    public function storeStudent(): void
    {
        requireBranchAdmin();
        $this->validateCsrf();
        $branchId = $this->getBranchId();

        $studentData = $_SESSION['cert_gen_student'] ?? null;
        $photoPath   = $_SESSION['cert_gen_photo'] ?? null;

        if (!$studentData) {
            flash('error', 'Session expired. Please fill the form again.');
            redirect(BASE_PATH . '/branch/cert-gen/new');
        }

        $studentId = $this->saveStudentToDb($studentData, $photoPath, $branchId);
        unset($_SESSION['cert_gen_student'], $_SESSION['cert_gen_photo']);

        if ($studentId > 0) {
            flash('success', "✅ Student registered successfully! Enrollment No: " . ($studentData['enrollment_no'] ?? '—') . ". You can now create a result for this student.");
            redirect(BASE_PATH . '/branch/cert-gen/student/' . $studentId);
        } else {
            redirect(BASE_PATH . '/branch/cert-gen/new');
        }
    }

    public function createResult(?int $studentId = null): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();

        if (!$studentId || $studentId <= 0) {
            flash('error', 'Invalid student ID.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        $stmt = $this->db->prepare(
            "SELECT s.*, u.name, u.email, u.phone, u.photo, c.name AS course_name, c.duration, c.subjects
             FROM students s
             LEFT JOIN users u ON s.user_id = u.id
             LEFT JOIN courses c ON s.course_id = c.id
             WHERE s.id = :sid AND s.branch_id = :bid LIMIT 1"
        );
        $stmt->execute([':sid' => $studentId, ':bid' => $branchId]);
        $student = $stmt->fetch() ?: null;
        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        $subjects = [];
        
        // ✅ FIX: Check branch-specific subjects FIRST (override global)
        $stmt = $this->db->prepare("SELECT subject_1, subject_2, subject_3 FROM branch_course_settings WHERE branch_id = :bid AND course_id = :cid LIMIT 1");
        $stmt->execute([':bid' => $branchId, ':cid' => $student['course_id'] ?? 0]);
        $branchSubjects = $stmt->fetch() ?: null;
        
        if ($branchSubjects) {
            // Use branch-specific subjects (max 3)
            if (!empty($branchSubjects['subject_1'])) $subjects[] = ['name' => $branchSubjects['subject_1'], 'max_marks' => 100];
            if (!empty($branchSubjects['subject_2'])) $subjects[] = ['name' => $branchSubjects['subject_2'], 'max_marks' => 100];
            if (!empty($branchSubjects['subject_3'])) $subjects[] = ['name' => $branchSubjects['subject_3'], 'max_marks' => 100];
        }
        
        // Fallback to global course subjects if branch subjects not set
        if (empty($subjects) && !empty($student['subjects'])) {
            $decoded = json_decode($student['subjects'], true);
            if (is_array($decoded)) $subjects = $decoded;
        }
        
        // Ultimate fallback
        if (empty($subjects)) {
            $subjects = [
                ['name' => 'Theory', 'max_marks' => 100],
                ['name' => 'Practical', 'max_marks' => 100],
            ];
        }

        $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;
        }

        $this->branchAdminView('cert_gen/result_form', [
            'title'       => 'Create Result: ' . ($student['name'] ?? ''),
            'currentPage' => 'cert_gen',
            'student'     => $student,
            'subjects'    => $subjects,
            'months'      => $months,
        ]);
    }

    /**
     * Step 3: Store result + create cert/marksheet requests (status=pending_approval).
     * ✅ FIXED: Now saves pattern_id from course's cert_pattern_id/marksheet_pattern_id
     */
    public function storeResult(?int $studentId = null): void
    {
        requireBranchAdmin();
        $this->validateCsrf();
        $branchId = $this->getBranchId();
        $this->ensurePaymentColumns();

        if (!$studentId || $studentId <= 0) {
            flash('error', 'Invalid student ID.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        $stmt = $this->db->prepare(
            "SELECT s.*, u.name, u.photo, c.name AS course_name, c.duration,
                    c.cert_pattern_id, c.marksheet_pattern_id
             FROM students s
             LEFT JOIN users u ON s.user_id = u.id
             LEFT JOIN courses c ON s.course_id = c.id
             WHERE s.id = :sid AND s.branch_id = :bid LIMIT 1"
        );
        $stmt->execute([':sid' => $studentId, ':bid' => $branchId]);
        $student = $stmt->fetch() ?: null;
        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        $completionMonth = trim($_POST['completion_month'] ?? '');
        $fromMonth       = trim($_POST['from_month'] ?? '');  // Will be IGNORED — auto-calculated below
        $subjectNames    = $_POST['subject_name'] ?? [];
        $maxMarks        = $_POST['max_marks'] ?? [];
        $obtMarks        = $_POST['obtained_marks'] ?? [];
        $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);
        $issueDate       = trim($_POST['issue_date'] ?? date('Y-m-d'));

        if (empty($completionMonth)) {
            flash('error', 'Please select the completion month.');
            redirect(BASE_PATH . '/branch/cert-gen/student/' . $studentId);
        }

        $isOnline = in_array($paymentMode, ['online', 'upi', 'wallet'], true);
        if (!$isOnline && $transactionId === '') {
            flash('error', 'Please enter the Transaction ID for offline payment.');
            redirect(BASE_PATH . '/branch/cert-gen/student/' . $studentId);
        }

        // ✅ WALLET AUTO-APPROVE: If wallet payment, check balance and auto-approve
        $isWalletPayment = ($paymentMode === 'wallet' || $paymentMode === 'online');
        $walletBalance = 0;
        if ($isWalletPayment) {
            $stmt = $this->db->prepare("SELECT wallet_balance FROM branches WHERE id = :bid LIMIT 1");
            $stmt->execute([':bid' => $branchId]);
            $walletBalance = (float)($stmt->fetchColumn() ?: 0);

            if ($walletBalance < $totalAmount) {
                flash('error', "❌ Insufficient wallet balance! You need ₹{$totalAmount} but have only ₹{$walletBalance}. Please ask Super Admin to recharge your wallet.");
                redirect(BASE_PATH . '/branch/cert-gen/student/' . $studentId);
            }
        }

        $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);

        // ✅ AUTO-CALCULATE from_date based on completion_month + course duration
        // to_date = completion_month (user entered)
        // from_date = completion_month - (duration_months - 1) months (INCLUSIVE)
        $courseDuration = $student['duration'] ?? '6 Months';
        $durationMonths = $this->parseDurationMonths($courseDuration);
        // Subtract (duration - 1) months from completion to get start month (inclusive)
        $startMonthOffset = max(0, $durationMonths - 1);

        $toDateLabel = '';
        $fromDateLabel = '';
        $completionDt = \DateTime::createFromFormat('Y-m', $completionMonth);
        if ($completionDt) {
            $toDateLabel = strtoupper($completionDt->format('M Y'));

            // Calculate from_date = completion - (duration - 1) months
            $fromDt = clone $completionDt;
            $fromDt->modify("-{$startMonthOffset} months");
            $fromDateLabel = strtoupper($fromDt->format('M Y'));

            // Also override fromMonth for session_from storage
            $fromMonth = $fromDt->format('Y-m');
        }

        $stmt = $this->db->prepare("SELECT * FROM branches WHERE id = :bid LIMIT 1");
        $stmt->execute([':bid' => $branchId]);
        $branch = $stmt->fetch() ?: [];

        // Find cert + 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' => $student['course_id']]);
        $templates = $stmt->fetchAll() ?: [];

        if (empty($templates)) {
            flash('error', 'No certificate/marksheet templates mapped for this course.');
            redirect(BASE_PATH . '/branch/cert-gen/student/' . $studentId);
        }

        // ✅ Fetch default pattern ID (fallback if course doesn't have specific pattern)
        $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) {}

        try {
            $this->db->beginTransaction();
            $generatedDocNumbers = [];
            $generatedCertIds = [];

            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);

                // ✅ DETERMINE PATTERN ID for this doc type
                $patternId = null;
                if ($docType === 'certificate') {
                    $patternId = $student['cert_pattern_id'] ?? null;
                } else {
                    $patternId = $student['marksheet_pattern_id'] ?? null;
                }
                // Fallback to default pattern
                if (!$patternId) {
                    $patternId = $defaultPatternId;
                }

                $availableCols = $this->getTableColumns('certificates');
                $insertData = [
                    'certificate_no'   => $documentNumber,
                    'student_id'       => $studentId,
                    'branch_id'        => $branchId,
                    'course_id'        => $student['course_id'] ?? null,
                    'student_name'     => $student['name'] ?? '',
                    'course_name'      => $student['course_name'] ?? '',
                    'grade'            => $grade,
                    'percentage'       => $percentage,
                    'division'         => $division,
                    'issue_date'       => $issueDate,
                    'status'           => $isWalletPayment ? 'generated' : 'pending_approval',
                    '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,
                    ]),
                ];

                // ✅ ADD pattern_id if column exists
                if (in_array('pattern_id', $availableCols, true) && $patternId) {
                    $insertData['pattern_id'] = $patternId;
                }

                if (in_array('session_from', $availableCols, true)) $insertData['session_from'] = $fromMonth . '-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();
                $generatedCertIds[] = $certId;
                $generatedDocNumbers[] = $documentNumber . ' (' . ucfirst($docType) . ')';

                $this->db->prepare(
                    "INSERT INTO certificate_timeline (certificate_id, event, description, created_at)
                     VALUES (:cid, 'requested', :desc, NOW())"
                )->execute([
                    ':cid'  => $certId,
                    ':desc' => "Result submitted by branch. Completion: {$toDateLabel}, {$grade} ({$percentage}%). Payment: {$paymentMode}" . ($transactionId ? ", Txn: {$transactionId}" : ""),
                ]);
            }

            if ($isOnline && $totalAmount > 0) {
                $this->db->prepare("UPDATE branches SET wallet_balance = wallet_balance - :amt WHERE id = :bid")
                    ->execute([':amt' => $totalAmount, ':bid' => $branchId]);
                $this->db->prepare(
                    "INSERT INTO branch_wallet_transactions (branch_id, type, amount, description, category, reference_type, reference_id, created_by, created_at)
                     VALUES (:bid, 'debit', :amt, :desc, 'document', 'certificate', :ref, :uid, NOW())"
                )->execute([
                    ':bid'  => $branchId,
                    ':amt'  => $totalAmount,
                    ':desc' => "Cert+Marksheet combined: " . implode(', ', $generatedDocNumbers),
                    ':ref'  => $generatedCertIds[0] ?? 0,
                    ':uid'  => $_SESSION['user_id'] ?? null,
                ]);
            }

            $this->db->commit();

            $docList = implode(', ', $generatedDocNumbers);

            // ✅ AUTO-GENERATE PDFs if wallet payment (no approval needed)
            if ($isWalletPayment) {
                try {
                    // Load the certificates we just created (with full data for PDF generation)
                    $placeholders = implode(',', array_fill(0, count($generatedCertIds), '?'));
                    $stmt = $this->db->prepare(
                        "SELECT c.*,
                                COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                                s.enrollment_no, s.father_name, 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,
                                b.name AS branch_name, b.branch_code,
                                u.photo
                         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.id IN ($placeholders)"
                    );
                    $stmt->execute($generatedCertIds);
                    $certsForPdf = $stmt->fetchAll() ?: [];

                    // Generate PDFs using the bulk helper
                    foreach ($certsForPdf as $cert) {
                        $this->autoGeneratePdf($cert);
                    }

                    // Timeline entry for auto-approval
                    foreach ($generatedCertIds as $certId) {
                        $this->db->prepare(
                            "INSERT INTO certificate_timeline (certificate_id, event, description, created_at)
                             VALUES (:cid, 'auto_approved', :desc, NOW())"
                        )->execute([
                            ':cid'  => $certId,
                            ':desc' => "Auto-approved via wallet payment. ₹{$totalAmount} deducted from wallet. PDF generated.",
                        ]);
                    }

                    flash('success', "✅ Result submitted & AUTO-APPROVED! Documents: {$docList}. ₹{$totalAmount} deducted from wallet. PDFs generated and ready to download!");
                } catch (\Throwable $pdfEx) {
                    error_log('Auto-generate PDF error: ' . $pdfEx->getMessage());
                    flash('success', "✅ Result submitted & auto-approved! ₹{$totalAmount} deducted from wallet. PDFs will be available shortly. (PDF generation pending)");
                }
            } else {
                flash('success', "✅ Result submitted for approval! Documents: {$docList}. Completion: {$toDateLabel}. Grade: {$grade} ({$percentage}%). Total: ₹{$totalAmount} ({$paymentMode}). Super Admin will verify and approve — once approved, both PDFs will be generated and downloadable.");
            }
        } catch (\Throwable $e) {
            try { $this->db->rollBack(); } catch (\Throwable $e2) {}
            error_log('BranchCertGen storeResult error: ' . $e->getMessage());
            flash('error', 'Error submitting result: ' . $e->getMessage());
        }
        redirect(BASE_PATH . '/branch/cert-gen');
    }

    /**
     * ✅ BULK DOWNLOAD — Multiple certificates in one PDF
     *
     * Accepts POST with:
     *   - cert_ids: comma-separated string OR array of certificate IDs
     *   - include_both: if "1", for each student include BOTH cert+marksheet
     *   - mode: "download" (attachment) or "print" (inline for printing)
     */
    public function bulkDownload(): void
    {
        requireBranchAdmin();
        $this->validateCsrf();
        $branchId = $this->getBranchId();

        $certIdsRaw = $_POST['cert_ids'] ?? [];
        $includeBoth = ($_POST['include_both'] ?? '0') === '1';
        $mode = $_POST['mode'] ?? 'download';

        // ✅ FIX: Handle both comma-separated string AND array
        if (is_string($certIdsRaw)) {
            $certIds = explode(',', $certIdsRaw);
        } else {
            $certIds = (array) $certIdsRaw;
        }
        $certIds = array_map('intval', $certIds);
        $certIds = array_filter($certIds, fn($id) => $id > 0);

        if (empty($certIds)) {
            flash('error', 'No certificates selected.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        // If include_both, expand to include sibling (cert+marksheet) for each student
        if ($includeBoth) {
            $expandedIds = [];
            foreach ($certIds as $cid) {
                $expandedIds[] = $cid;
                // Find sibling
                $stmt = $this->db->prepare(
                    "SELECT id FROM certificates
                     WHERE student_id = (SELECT student_id FROM certificates WHERE id = ?)
                       AND course_id = (SELECT course_id FROM certificates WHERE id = ?)
                       AND id != ?
                       AND branch_id = ?
                       AND status = 'generated'
                     LIMIT 1"
                );
                $stmt->execute([$cid, $cid, $cid, $branchId]);
                $sibling = $stmt->fetchColumn();
                if ($sibling) {
                    $expandedIds[] = (int)$sibling;
                }
            }
            $certIds = array_unique($expandedIds);
        }

        // Load all selected certificates with full data
        $placeholders = implode(',', array_fill(0, count($certIds), '?'));
        $params = array_merge($certIds, [$branchId]);

        $stmt = $this->db->prepare(
            "SELECT c.*,
                    COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                    s.enrollment_no, s.father_name, 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,
                    b.name AS branch_name, b.branch_code,
                    u.photo
             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.id IN ($placeholders) AND c.branch_id = ? AND c.status = 'generated'
             ORDER BY c.student_name, c.id"
        );
        $stmt->execute($params);
        $certs = $stmt->fetchAll() ?: [];

        if (empty($certs)) {
            flash('error', 'No approved certificates found for selected items.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        // ✅ FIX: Use helper method to generate the PDF
        $pdf = $this->generateBulkPdf($certs);

        if (!$pdf) {
            flash('error', 'Failed to generate PDF. Check pattern assignments.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        $filename = 'Bulk_' . count($certs) . 'docs_' . date('Ymd_His') . '.pdf';

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

        while (ob_get_level()) ob_end_clean();
        $pdf->Output('I', $filename);
        exit;
    }

    /**
     * Download combined Certificate + Marksheet PDF (both pages in one file)
     * ✅ FIXED: Now generates fresh from pattern (no FPDI needed)
     */
    public function downloadCombined(?int $certId = null): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();

        if (!$certId || $certId <= 0) {
            flash('error', 'Invalid certificate ID.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        // Find BOTH cert + marksheet for this student+course
        $stmt = $this->db->prepare(
            "SELECT c.*,
                    COALESCE(c.student_name, u.name, 'Unknown') AS student_name,
                    s.enrollment_no, s.father_name, 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,
                    b.name AS branch_name, b.branch_code,
                    u.photo
             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.student_id = (SELECT student_id FROM certificates WHERE id = ?)
               AND c.course_id = (SELECT course_id FROM certificates WHERE id = ?)
               AND c.branch_id = ?
               AND c.status = 'generated'
             ORDER BY c.id"
        );
        $stmt->execute([$certId, $certId, $branchId]);
        $certs = $stmt->fetchAll() ?: [];

        if (empty($certs)) {
            flash('error', 'No approved documents found for this student.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        // ✅ FIX: Use helper method to generate the PDF
        $pdf = $this->generateBulkPdf($certs);

        if (!$pdf) {
            flash('error', 'Failed to generate combined PDF.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        $studentName = preg_replace('/[^a-zA-Z0-9]/', '_', $certs[0]['student_name'] ?? 'student');
        $downloadName = 'Combined_' . $studentName . '_' . ($certs[0]['certificate_no'] ?? $certId) . '.pdf';

        header('Content-Type: application/pdf');
        header('Content-Disposition: attachment; filename="' . $downloadName . '"');
        header('Cache-Control: private, max-age=0, must-revalidate');

        while (ob_get_level()) ob_end_clean();
        $pdf->Output('I', $downloadName);
        exit;
    }

    /**
     * ✅ AUTO-GENERATE PDF for a single certificate (used by wallet auto-approve)
     */
    private function autoGeneratePdf(array $cert): void
    {
        $docType = $cert['doc_type'] ?? 'certificate';

        // Determine pattern ID
        $patternId = $cert['pattern_id'] ?? null;
        if (!$patternId) {
            $patternId = $docType === 'marksheet'
                ? ($cert['marksheet_pattern_id'] ?? null)
                : ($cert['cert_pattern_id'] ?? null);
        }
        if (!$patternId) {
            $stmt = $this->db->query("SELECT id FROM certificate_patterns WHERE is_default = 1 AND status = 'locked' LIMIT 1");
            $patternId = $stmt->fetchColumn() ?: null;
        }
        if (!$patternId) return;

        // Load pattern
        $stmt = $this->db->prepare("SELECT * FROM certificate_patterns WHERE id = ? AND status = 'locked'");
        $stmt->execute([$patternId]);
        $pattern = $stmt->fetch() ?: null;
        if (!$pattern) return;

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

        $bgPath = $docType === 'certificate'
            ? ($pattern['cert_background_path'] ?? '')
            : ($pattern['marksheet_background_path'] ?? '');
        if (empty($bgPath)) return;

        // Parse subjects
        $subjectsData = [];
        if (!empty($cert['subjects_json'])) {
            $subjectsData = json_decode($cert['subjects_json'], true) ?: [];
        }
        $subjects = [];
        if ($docType === 'marksheet' && !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),
                ];
            }
        }

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

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

        // Save PDF to disk
        $uploadDir = $_SERVER['DOCUMENT_ROOT'] . '/uploads/certificates/';
        if (!is_dir($uploadDir)) {
            mkdir($uploadDir, 0755, true);
        }

        $filename = $docType . '_' . $cert['id'] . '_' . time() . '.pdf';
        $filePath = $uploadDir . $filename;
        $pdf->Output('F', $filePath);

        $pdfRelPath = '/uploads/certificates/' . $filename;

        // Update certificate with PDF path
        $this->db->prepare("UPDATE certificates SET pdf_path = :path, updated_at = NOW() WHERE id = :id")
            ->execute([':path' => $pdfRelPath, ':id' => $cert['id']]);
    }

    /**
     * ✅ HELPER: Generate a multi-page PDF from an array of certificate records
     * Used by both bulkDownload() and downloadCombined()
     */
    private function generateBulkPdf(array $certs): ?\FPDF
    {
        if (empty($certs)) return null;

        // Fetch default pattern ID
        $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) {}

        // ✅ FIX: DocumentGenerator already loads FPDF — no manual loading needed
        $generator = new DocumentGenerator();

        $patternCache = [];
        $fieldsCache = [];
        $pdf = null;
        $successCount = 0;

        foreach ($certs as $cert) {
            $docType = $cert['doc_type'] ?? 'certificate';

            // Determine pattern ID
            $patternId = $cert['pattern_id'] ?? null;
            if (!$patternId) {
                $patternId = $docType === 'marksheet'
                    ? ($cert['marksheet_pattern_id'] ?? null)
                    : ($cert['cert_pattern_id'] ?? null);
            }
            if (!$patternId) {
                $patternId = $defaultPatternId;
            }
            if (!$patternId) continue;

            // Load pattern (cache)
            if (!isset($patternCache[$patternId])) {
                $stmt = $this->db->prepare("SELECT * FROM certificate_patterns WHERE id = ? AND status = 'locked'");
                $stmt->execute([$patternId]);
                $patternCache[$patternId] = $stmt->fetch() ?: null;
            }
            $pattern = $patternCache[$patternId];
            if (!$pattern) continue;

            // Load fields (cache)
            $cacheKey = $patternId . '_' . $docType;
            if (!isset($fieldsCache[$cacheKey])) {
                $stmt = $this->db->prepare("SELECT * FROM certificate_pattern_fields WHERE pattern_id = ? AND doc_type = ? AND is_enabled = 1 ORDER BY id ASC");
                $stmt->execute([$patternId, $docType]);
                $fieldsCache[$cacheKey] = $stmt->fetchAll() ?: [];
            }
            $fields = $fieldsCache[$cacheKey];

            $bgPath = $docType === 'certificate'
                ? ($pattern['cert_background_path'] ?? '')
                : ($pattern['marksheet_background_path'] ?? '');
            if (empty($bgPath)) continue;

            // Parse subjects
            $subjectsData = [];
            if (!empty($cert['subjects_json'])) {
                $subjectsData = json_decode($cert['subjects_json'], true) ?: [];
            }
            $subjects = [];
            if ($docType === 'marksheet' && !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),
                    ];
                }
            }

            $studentData = [
                'student_name'   => $cert['student_name'] ?? '',
                'father_name'    => $cert['father_name'] ?? '',
                'enrollment_no'  => $cert['enrollment_no'] ?? '',
                'certificate_no' => $cert['certificate_no'] ?? '',
                'course'         => $cert['course_name'] ?? '',
                'branch_name'    => $cert['branch_name'] ?? '',
                'branch_code'    => $cert['branch_code'] ?? '',
                'duration'       => $cert['duration'] ?? '',
                'date_of_issue'  => !empty($cert['issue_date']) ? date('d/m/Y', strtotime($cert['issue_date'])) : date('d/m/Y'),
                'from_date'      => $subjectsData['from_label'] ?? '',
                'to_date'        => $subjectsData['to_label'] ?? '',
                'percentage'     => ($cert['percentage'] ?? 0) . '%',
                'grade'          => $cert['grade'] ?? '',
                'photo'          => $cert['photo'] ?? '',  // ✅ FIX: Added photo from DB
                'qr_data'        => $cert['verification_url'] ?? $cert['certificate_no'] ?? '',  // ✅ FIX: Use verification_url for QR
                'subjects'       => $subjects,
            ];

            try {
                if ($pdf === null) {
                    // First page — create new PDF
                    $pdf = $generator->generateFromPattern([
                        'pattern'    => $pattern,
                        'fields'     => $fields,
                        'student'    => $studentData,
                        'doc_type'   => $docType,
                        'bg_path'    => $bgPath,
                        'is_preview' => false,
                    ]);
                } else {
                    // Subsequent pages — add to existing
                    $generator->addPatternPage($pdf, [
                        'pattern'    => $pattern,
                        'fields'     => $fields,
                        'student'    => $studentData,
                        'doc_type'   => $docType,
                        'bg_path'    => $bgPath,
                        'is_preview' => false,
                    ]);
                }
                $successCount++;
            } catch (\Throwable $e) {
                error_log("PDF page error for cert {$cert['id']}: " . $e->getMessage());
            }
        }

        return $successCount > 0 ? $pdf : null;
    }

    /**
     * Step 4: Download generated PDF (after approval).
     * ✅ FIXED: Multi-path resolution (8+ paths tried)
     */
    public function download(?int $certId = null): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();

        if (!$certId || $certId <= 0) {
            flash('error', 'Invalid document ID.');
            redirect(BASE_PATH . '/branch/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 = :bid LIMIT 1"
        );
        $stmt->execute([':cid' => $certId, ':bid' => $branchId]);
        $doc = $stmt->fetch() ?: null;

        if (!$doc) {
            flash('error', 'Document not found.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }
        if ($doc['status'] !== 'generated') {
            flash('error', 'Document not yet approved. Current status: ' . ucfirst(str_replace('_', ' ', $doc['status'] ?? 'unknown')));
            redirect(BASE_PATH . '/branch/cert-gen');
        }

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

        if (empty($pdfPath)) {
            flash('error', 'PDF file not found. Please contact Super Admin to regenerate.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

        // ✅ MULTI-PATH RESOLUTION — try many paths
        $candidates = [
            $pdfPath,                                              // as-is
            $_SERVER['DOCUMENT_ROOT'] . $pdfPath,                 // + DOCUMENT_ROOT
            $_SERVER['DOCUMENT_ROOT'] . '/' . ltrim($pdfPath, '/'),
            rtrim($_SERVER['DOCUMENT_ROOT'], '/') . $pdfPath,
            $_SERVER['DOCUMENT_ROOT'] . '/gmind' . $pdfPath,      // + /gmind subfolder
            '/home/advaysin/public_html' . $pdfPath,              // hardcoded
            '/home/advaysin/public_html/gmind' . $pdfPath,
        ];

        // If path doesn't start with /, prepend it
        if ($pdfPath[0] !== '/') {
            $candidates[] = $_SERVER['DOCUMENT_ROOT'] . '/' . $pdfPath;
            $candidates[] = '/home/advaysin/public_html/' . $pdfPath;
            $candidates[] = '/home/advaysin/public_html/gmind/' . $pdfPath;
        }

        // If PUBLIC_ROOT is defined, try that too
        if (defined('PUBLIC_ROOT')) {
            $candidates[] = PUBLIC_ROOT . '/' . ltrim($pdfPath, '/');
            $candidates[] = PUBLIC_ROOT . $pdfPath;
        }

        $filePath = null;
        foreach ($candidates as $cand) {
            if (file_exists($cand) && is_readable($cand)) {
                $filePath = $cand;
                break;
            }
        }

        if (!$filePath) {
            error_log("PDF not found. Cert ID: {$certId}, stored path: {$pdfPath}. Tried: " . implode(' | ', $candidates));
            flash('error', 'PDF file not found on server. Please contact Super Admin.');
            redirect(BASE_PATH . '/branch/cert-gen');
        }

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

        // Send file to browser
        header('Content-Description: File Transfer');
        header('Content-Type: application/pdf');
        header('Content-Disposition: attachment; filename="' . $downloadName . '"');
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
        header('Content-Length: ' . filesize($filePath));

        // Clear any output buffer
        while (ob_get_level()) {
            ob_end_clean();
        }

        readfile($filePath);
        exit;
    }

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

    private function saveStudentToDb(array $data, ?string $photoPath, int $branchId): int
    {
        $this->ensureStudentColumns();

        $stmt = $this->db->prepare("SELECT id FROM users WHERE email = :email LIMIT 1");
        $stmt->execute([':email' => $data['email']]);
        if ($stmt->fetch()) {
            flash('error', "Email '{$data['email']}' already exists. Please use a different email.");
            return 0;
        }

        $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'])) {
            $seq = (int)substr($last['enrollment_no'], 6) + 1;
        }
        $enrollmentNo = 'GM' . $year . str_pad((string)$seq, 3, '0', STR_PAD_LEFT);

        $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);

        $stmt = $this->db->prepare("SELECT branch_code FROM branches WHERE id = :bid LIMIT 1");
        $stmt->execute([':bid' => $branchId]);
        $branchCode = $stmt->fetchColumn() ?: '';

        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'];
        if (in_array('photo', $userCols, true)) $userData['photo'] = $photoPath;
        if (in_array('branch_id', $userCols, true)) $userData['branch_id'] = $branchId;
        if (in_array('branch_code', $userCols, true)) $userData['branch_code'] = $branchCode;
        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');

        $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) {
            error_log('saveStudentToDb: users INSERT failed: ' . $e->getMessage() . ' | SQL: ' . $uSql);
            flash('error', '❌ Failed to create user account: ' . $e->getMessage() . '. SQL: ' . $uSql);
            return 0;
        }
        $userId = (int)$this->db->lastInsertId();

        if ($userId <= 0) {
            flash('error', '❌ Failed to get user ID after insert.');
            return 0;
        }

        $availableCols = $this->getTableColumns('students');
        $insertData = [
            'user_id'        => $userId,
            'enrollment_no'  => $enrollmentNo,
            'course_id'      => $data['course_id'] ?: null,
            'branch_id'      => $branchId,
            'status'         => 'active',
        ];
        if (in_array('dob', $availableCols, true))            $insertData['dob'] = $data['dob'] ?: null;
        if (in_array('father_name', $availableCols, true))    $insertData['father_name'] = $data['father_name'] ?: 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('batch_id', $availableCols, true))       $insertData['batch_id'] = ($data['batch_id'] > 0) ? $data['batch_id'] : 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) {
            error_log('saveStudentToDb: students INSERT failed: ' . $e->getMessage() . ' | SQL: ' . $sql);
            try { $this->db->prepare("DELETE FROM users WHERE id = :uid")->execute([':uid' => $userId]); } catch (\Throwable $e2) {}
            flash('error', '❌ Failed to create student record: ' . $e->getMessage() . '. SQL: ' . $sql);
            return 0;
        }
        $studentId = (int)$this->db->lastInsertId();

        $data['enrollment_no'] = $enrollmentNo;
        $_SESSION['cert_gen_student'] = $data;

        $this->auditLog('student_registered_via_cert_gen', 'students', $studentId, "Registered {$data['name']} ({$enrollmentNo}) via Cert Gen module. Login: {$data['email']} / {$password}");
        return $studentId;
    }

    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';
    }

    /**
     * Parse course duration string and return number of months.
     * Supports: "3 Months", "6 Months", "1 Year", "2 Years", "1.5 Years", "12 Months"
     *
     * Returns: number of months (e.g., "3 Months" → 3, "1 Year" → 12)
     */
    private function parseDurationMonths(string $duration): int
    {
        $duration = trim($duration);

        // Try to parse "X Months"
        if (preg_match('/^([\d.]+)\s*month/i', $duration, $m)) {
            return (int)round((float)$m[1]);
        }

        // Try to parse "X Years"
        if (preg_match('/^([\d.]+)\s*year/i', $duration, $m)) {
            return (int)round((float)$m[1] * 12);
        }

        // Try to parse "X Weeks" (4 weeks = 1 month approximation)
        if (preg_match('/^([\d.]+)\s*week/i', $duration, $m)) {
            return (int)round((float)$m[1] / 4);
        }

        // Try to parse "X Days"
        if (preg_match('/^([\d.]+)\s*day/i', $duration, $m)) {
            return (int)round((float)$m[1] / 30);
        }

        // Default — 6 months
        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",
            'batch_id'       => "ADD COLUMN `batch_id` BIGINT UNSIGNED NULL DEFAULT NULL",
            'father_name'    => "ADD COLUMN `father_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) {}
            }
        }
    }

    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",
            'certificate_pdf_path' => "ADD COLUMN `certificate_pdf_path` VARCHAR(500) NULL DEFAULT NULL",
            'marksheet_pdf_path'   => "ADD COLUMN `marksheet_pdf_path` VARCHAR(500) 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) {}
            }
        }
    }
}
