<?php
namespace BranchAdmin\Controllers;

use BranchAdmin\Controllers\BranchAdminController;

class BranchStudentController extends BranchAdminController
{
    public function index(): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();

        $search = sanitize($_GET['search'] ?? '');
        $courseFilter = sanitize($_GET['course_id'] ?? '');

        $sql = "SELECT s.*, u.name, u.email, u.phone, u.photo, c.name as course_name 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.branch_id = ?";
        $params = [$branchId];

        if ($search !== '') {
            $sql .= " AND (u.name LIKE ? OR s.enrollment_no LIKE ? OR u.email LIKE ?)";
            $params[] = "%{$search}%";
            $params[] = "%{$search}%";
            $params[] = "%{$search}%";
        }
        if ($courseFilter !== '') {
            $sql .= " AND s.course_id = ?";
            $params[] = $courseFilter;
        }
        $sql .= " ORDER BY s.created_at DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $students = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        // ✅ FIX: Get ALL active courses for filter dropdown
        $courses = $this->db->query("SELECT id, name FROM courses WHERE active=1 ORDER BY name")->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        // ✅ FIX: Fetch fee details for each student
        foreach ($students as &$s) {
            $courseFee = 0;
            // Check branch-specific fee
            try {
                $stmtFee = $this->db->prepare("SELECT course_fee FROM branch_course_settings WHERE branch_id = :bid AND course_id = :cid LIMIT 1");
                $stmtFee->execute([':bid' => $branchId, ':cid' => $s['course_id'] ?? 0]);
                $bFee = $stmtFee->fetchColumn();
                if ($bFee !== false && $bFee !== null && (float)$bFee > 0) {
                    $courseFee = (float)$bFee;
                }
            } catch (\Throwable $e) {}

            // Fallback to global fee
            if ($courseFee == 0) {
                try {
                    $stmtFee = $this->db->prepare("SELECT fee FROM courses WHERE id = :cid LIMIT 1");
                    $stmtFee->execute([':cid' => $s['course_id'] ?? 0]);
                    $gFee = $stmtFee->fetchColumn();
                    if ($gFee !== false) $courseFee = (float)$gFee;
                } catch (\Throwable $e) {}
            }
            $s['course_fee'] = $courseFee;

            // Get total paid
            $totalPaid = 0;
            try {
                $stmtPaid = $this->db->prepare("SELECT COALESCE(SUM(amount), 0) as total_paid FROM fee_payments WHERE student_id = :sid AND payment_mode != 'pending'");
                $stmtPaid->execute([':sid' => $s['id']]);
                $totalPaid = (float)$stmtPaid->fetchColumn();
            } catch (\Throwable $e) {}
            $s['fee_paid'] = $totalPaid;
            $s['fee_due'] = max(0, $courseFee - $totalPaid);
        }
        unset($s);

        $this->branchAdminView('students/index', [
            'title' => 'Manage Students', 'currentPage' => 'students',
            'students' => $students, 'courses' => $courses,
            'search' => $search, 'courseFilter' => $courseFilter,
        ]);
    }

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

        // ✅ FIX: Fetch ALL active courses (not just those with branch_course_fees mapping)
        $courses = [];
        try {
            $courses = $this->db->query("SELECT id, name, duration, fee FROM courses WHERE active=1 ORDER BY name")->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {}

        // ✅ Fetch branch-specific course fees
        $branchFees = [];
        try {
            $stmt = $this->db->prepare("SELECT course_id, course_fee FROM branch_course_settings WHERE branch_id = :bid");
            $stmt->execute([':bid' => $branchId]);
            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $bf) {
                $branchFees[(int)$bf['course_id']] = (float)$bf['course_fee'];
            }
        } catch (\Throwable $e) {}

        // ✅ Fetch branch batches (for batch dropdown)
        $batches = [];
        try {
            $stmt = $this->db->prepare("SELECT id, batch_name, start_date, end_date, start_time, end_time, status FROM batches WHERE branch_id = :bid AND status IN ('upcoming','running') ORDER BY batch_name");
            $stmt->execute([':bid' => $branchId]);
            $batches = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {}

        // Merge: use branch fee if set, else global course fee
        foreach ($courses as &$c) {
            $cid = (int)($c['id'] ?? 0);
            if (isset($branchFees[$cid])) {
                $c['fee_amount'] = $branchFees[$cid];
            } else {
                $c['fee_amount'] = (float)($c['fee'] ?? 0);
            }
        }
        unset($c);

        $this->branchAdminView('students/create', [
            'title'       => 'Add Student',
            'currentPage' => 'students',
            'courses'     => $courses,
            'batches'     => $batches,
            'csrfToken'   => $this->csrfToken ?? '',
        ]);
    }

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

        $name          = sanitize($_POST['name'] ?? '');
        $email         = sanitize($_POST['email'] ?? '');
        $phone         = sanitize($_POST['phone'] ?? '');
        // ✅ AUTO-GENERATE password (no password field in form) — last 6 digits of phone
        $phoneDigits   = preg_replace('/\D/', '', $phone);
        $password      = strlen($phoneDigits) >= 6 ? substr($phoneDigits, -6) : str_pad((string)rand(100000, 999999), 6, '0', STR_PAD_LEFT);
        $courseId      = sanitize($_POST['course_id'] ?? '');
        $batchId       = (int)($_POST['batch_id'] ?? 0);
        $dob           = sanitize($_POST['dob'] ?? '') ?: null;
        $fatherName    = sanitize($_POST['father_name'] ?? '') ?: null;
        $motherName    = sanitize($_POST['mother_name'] ?? '') ?: null;
        $address       = sanitize($_POST['address'] ?? '');
        $city          = sanitize($_POST['city'] ?? '');
        $state         = sanitize($_POST['state'] ?? '');
        $pinCode       = sanitize($_POST['pin_code'] ?? '');
        $guardianName  = sanitize($_POST['guardian_name'] ?? '');
        $guardianPhone = sanitize($_POST['guardian_phone'] ?? '');
        $admissionDate = sanitize($_POST['admission_date'] ?? date('Y-m-d'));

        if (empty($name) || empty($email) || empty($phone) || empty($courseId)) {
            flash('error', 'Please fill in all required fields (Name, Email, Phone, Course).');
            redirect(BASE_PATH . '/branch/students/create');
            return;
        }

        // Check if email already exists
        $stmt = $this->db->prepare("SELECT id FROM users WHERE email = ?");
        $stmt->execute([$email]);
        if ($stmt->fetch()) {
            flash('error', 'A user with this email already exists.');
            redirect(BASE_PATH . '/branch/students/create');
            return;
        }

        // ✅ Get fee: branch-specific first, then global course fee
        $courseFee = 0;
        try {
            $stmt = $this->db->prepare("SELECT course_fee FROM branch_course_settings WHERE branch_id = :bid AND course_id = :cid LIMIT 1");
            $stmt->execute([':bid' => $branchId, ':cid' => $courseId]);
            $bFee = $stmt->fetchColumn();
            if ($bFee !== false && $bFee !== null && (float)$bFee > 0) {
                $courseFee = (float)$bFee;
            }
        } catch (\Throwable $e) {}

        if ($courseFee == 0) {
            try {
                $stmt = $this->db->prepare("SELECT fee FROM courses WHERE id = :cid LIMIT 1");
                $stmt->execute([':cid' => $courseId]);
                $gFee = $stmt->fetchColumn();
                if ($gFee !== false) $courseFee = (float)$gFee;
            } catch (\Throwable $e) {}
        }

        // Generate enrollment number
        $year = date('Y');
        $stmt = $this->db->prepare("SELECT enrollment_no FROM students WHERE branch_id = ? AND enrollment_no LIKE ? ORDER BY id DESC LIMIT 1");
        $stmt->execute([$branchId, "BR{$branchCode}{$year}%"]);
        $lastEnrollment = $stmt->fetch(\PDO::FETCH_ASSOC);
        $sequential = 1;
        if ($lastEnrollment && $lastEnrollment['enrollment_no']) {
            $lastNum = (int) substr($lastEnrollment['enrollment_no'], -4);
            $sequential = $lastNum + 1;
        }
        $enrollmentNo = 'BR' . $branchCode . $year . str_pad($sequential, 4, '0', STR_PAD_LEFT);

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

        // Ensure students table has required columns
        $this->ensureStudentColumns();

        try {
            $this->db->beginTransaction();

            $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
            $userCols = $this->getTableColumns('users');
            $userData = [
                'name'        => $name,
                'email'       => $email,
                'phone'       => $phone,
                'password'    => $hashedPassword,
                'role'        => 'student',
                'branch_id'   => $branchId,
                'branch_code' => $branchCode,
                'is_active'   => 1,
                'status'      => 'active',
                'created_at'  => date('Y-m-d H:i:s'),
            ];
            if (in_array('photo', $userCols, true) && $photoPath) $userData['photo'] = $photoPath;

            $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;
            $this->db->prepare($uSql)->execute($uParams);
            $userId = (int)$this->db->lastInsertId();

            $studentCols = $this->getTableColumns('students');
            $studentData = [
                'user_id'        => $userId,
                'enrollment_no'  => $enrollmentNo,
                'course_id'      => $courseId,
                'branch_id'      => $branchId,
                'status'         => 'active',
                'admission_date' => $admissionDate,
                'created_at'     => date('Y-m-d H:i:s'),
                'updated_at'     => date('Y-m-d H:i:s'),
            ];
            if (in_array('dob', $studentCols, true))            $studentData['dob']            = $dob;
            if (in_array('father_name', $studentCols, true))    $studentData['father_name']    = $fatherName;
            if (in_array('mother_name', $studentCols, true))    $studentData['mother_name']    = $motherName;
            if (in_array('address', $studentCols, true))        $studentData['address']        = $address;
            if (in_array('city', $studentCols, true))           $studentData['city']           = $city;
            if (in_array('state', $studentCols, true))          $studentData['state']          = $state;
            if (in_array('pin_code', $studentCols, true))       $studentData['pin_code']       = $pinCode;
            if (in_array('guardian_name', $studentCols, true))  $studentData['guardian_name']  = $guardianName;
            if (in_array('guardian_phone', $studentCols, true)) $studentData['guardian_phone'] = $guardianPhone;
            if (in_array('batch_id', $studentCols, true) && $batchId > 0) $studentData['batch_id'] = $batchId;
            if (in_array('fee_amount', $studentCols, true))     $studentData['fee_amount']     = $courseFee;
            if (in_array('fee_paid', $studentCols, true))       $studentData['fee_paid']       = 0;
            if (in_array('fee_due', $studentCols, true))        $studentData['fee_due']        = $courseFee;

            $sCols = array_keys($studentData);
            $sColList = implode(', ', array_map(fn($c) => "`{$c}`", $sCols));
            $sPhList  = implode(', ', array_map(fn($c) => ":{$c}", $sCols));
            $sSql = "INSERT INTO students ({$sColList}) VALUES ({$sPhList})";
            $sParams = [];
            foreach ($studentData as $k => $v) $sParams[":{$k}"] = $v;
            $this->db->prepare($sSql)->execute($sParams);
            $studentId = (int)$this->db->lastInsertId();

            // ✅ Auto-generate initial fee record (with full fee as pending)
            try {
                $fpCols = $this->getTableColumns('fee_payments');
                $fpData = [
                    'student_id'   => $studentId,
                    'branch_id'    => $branchId,
                    'amount'       => 0,  // Pending — no payment yet
                    'payment_mode' => 'pending',
                    'payment_date' => $admissionDate,
                    'remarks'      => "Initial fee record on admission. Total course fee: ₹{$courseFee}",
                    'created_at'   => date('Y-m-d H:i:s'),
                ];
                $fpInsert = [];
                foreach ($fpData as $k => $v) {
                    if (in_array($k, $fpCols, true)) $fpInsert[$k] = $v;
                }
                if (!empty($fpInsert)) {
                    $fpKeys = array_keys($fpInsert);
                    $fpColList = implode(', ', array_map(fn($c) => "`{$c}`", $fpKeys));
                    $fpPhList  = implode(', ', array_map(fn($c) => ":{$c}", $fpKeys));
                    $fpSql = "INSERT INTO fee_payments ({$fpColList}) VALUES ({$fpPhList})";
                    $fpParams = [];
                    foreach ($fpInsert as $k => $v) $fpParams[":{$k}"] = $v;
                    $this->db->prepare($fpSql)->execute($fpParams);
                }
            } catch (\Throwable $e) {
                error_log('Auto fee_payments insert failed (non-critical): ' . $e->getMessage());
            }

            $this->db->commit();

            // ═══════════════════════════════════════════════════════════════════
            // ✅ SEND ADMISSION CONFIRMATION EMAIL TO STUDENT
            // Non-blocking: admission succeeds even if email fails.
            // Reads SMTP settings directly from DB (more reliable than Mail class's own DB connection).
            // ═══════════════════════════════════════════════════════════════════
            $emailSent = false;
            $emailError = '';

            try {
                // Step 1: Read SMTP settings directly from settings table
                $smtpSettings = $this->readSmtpSettingsDirect();

                if (empty($smtpSettings['smtp_host']) || empty($smtpSettings['smtp_from_email'])) {
                    $emailError = 'SMTP not configured — set Host and From Email in Admin → SMTP Settings';
                    error_log('Admission email: ' . $emailError);
                } else {
                    // Step 2: Get branch name
                    $branchName = 'GMIND INSTITUTE';
                    try {
                        $stmtB = $this->db->prepare("SELECT name FROM branches WHERE id = :bid LIMIT 1");
                        $stmtB->execute([':bid' => $branchId]);
                        $branchName = $stmtB->fetchColumn() ?: $branchName;
                    } catch (\Throwable $e) {}

                    // Step 3: Load PHPMailer
                    $phpMailerPath = $this->findPHPMailerPath();
                    if (!$phpMailerPath) {
                        $emailError = 'PHPMailer library not found';
                        error_log('Admission email: ' . $emailError);
                    } else {
                        require_once $phpMailerPath . '/PHPMailer.php';
                        require_once $phpMailerPath . '/SMTP.php';
                        require_once $phpMailerPath . '/Exception.php';

                        // Step 4: Build & send email
                        $mail = new \PHPMailer\PHPMailer\PHPMailer(true);
                        $mail->isSMTP();
                        $mail->Host       = $smtpSettings['smtp_host'];
                        $mail->Port       = (int)($smtpSettings['smtp_port'] ?? 465);
                        $mail->SMTPAuth   = !empty($smtpSettings['smtp_username']);
                        $mail->Username   = $smtpSettings['smtp_username'] ?? '';
                        $mail->Password   = $smtpSettings['smtp_password'] ?? '';

                        // Encryption
                        $enc = $smtpSettings['smtp_encryption'] ?? 'ssl';
                        if ($enc === 'ssl') {
                            $mail->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_SMTPS;
                        } elseif ($enc === 'tls') {
                            $mail->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
                        } else {
                            $mail->SMTPSecure = '';
                            $mail->SMTPAutoTLS = false;
                        }

                        $mail->CharSet = 'UTF-8';
                        $mail->Timeout = 25;

                        // SSL bypass for cPanel self-signed certs
                        $mail->SMTPOptions = [
                            'ssl' => [
                                'verify_peer'       => false,
                                'verify_peer_name'  => false,
                                'allow_self_signed' => true,
                            ],
                        ];

                        // From
                        $mail->setFrom($smtpSettings['smtp_from_email'], $smtpSettings['smtp_from_name'] ?? 'Gmind Institute');

                        // To (student)
                        $mail->addAddress($email, $name);

                        // Subject + HTML body
                        $emailSubject = "Admission Confirmed — {$branchName}";
                        $emailBody = $this->buildAdmissionEmailHtml([
                            'student_name'   => $name,
                            'enrollment_no'  => $enrollmentNo,
                            'course_name'    => $this->getCourseName($courseId),
                            'branch_name'    => $branchName,
                            'branch_code'    => $branchCode,
                            'password'       => $password,
                            'email'          => $email,
                            'phone'          => $phone,
                            'admission_date' => $admissionDate,
                            'course_fee'     => $courseFee,
                            'login_url'      => (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off' ? 'https' : 'http') . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost') . (defined('BASE_PATH') ? BASE_PATH : '/gmind') . '/login',
                        ]);

                        $mail->isHTML(true);
                        $mail->Subject = $emailSubject;
                        $mail->Body    = $emailBody;
                        $mail->AltBody = strip_tags(str_replace(['<br>', '<br/>', '</p>'], "\n", $emailBody));

                        $mail->send();
                        $emailSent = true;
                        error_log('Admission email sent successfully to ' . $email);
                    }
                }
            } catch (\PHPMailer\PHPMailer\Exception $e) {
                $emailError = $e->getMessage();
                error_log('Admission email PHPMailer error: ' . $e->getMessage());
            } catch (\Throwable $e) {
                $emailError = $e->getMessage();
                error_log('Admission email exception: ' . $e->getMessage());
            }

            // Build success message
            $successMsg = "✅ Student admitted successfully! Enrollment No: {$enrollmentNo}. Auto-generated login password: {$password} (last 6 digits of phone).";
            if ($emailSent) {
                $successMsg .= " 📧 Admission confirmation email sent to {$email}.";
            } else {
                $successMsg .= " ⚠️ Email not sent ({$emailError}). Please share login details manually.";
            }
            flash('success', $successMsg);

            // ✅ Redirect to admission preview page (with PDF download link)
            redirect(BASE_PATH . '/branch/students/' . $studentId . '/admission-preview');
        } catch (\Exception $e) {
            try { $this->db->rollBack(); } catch (\Throwable $e2) {}
            flash('error', 'Failed to add student. ' . $e->getMessage());
        }
        redirect(BASE_PATH . '/branch/students');
    }

    /**
     * Admission Preview Page — shows filled form preview with PDF download button.
     */
    public function admissionPreview(array $params): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();
        $id = (int)($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            "SELECT s.*, u.name, u.email, u.phone, u.photo, c.name as course_name, c.duration, c.fee,
                    b.name as branch_name, b.branch_code,
                    bt.batch_name, bt.start_time, bt.end_time
             FROM students s
             INNER JOIN users u ON s.user_id = u.id
             LEFT JOIN courses c ON s.course_id = c.id
             LEFT JOIN branches b ON s.branch_id = b.id
             LEFT JOIN batches bt ON s.batch_id = bt.id
             WHERE s.id = :sid AND s.branch_id = :bid LIMIT 1"
        );
        $stmt->execute([':sid' => $id, ':bid' => $branchId]);
        $student = $stmt->fetch(\PDO::FETCH_ASSOC) ?: null;

        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        // Get course fee (branch-specific or global)
        $courseFee = 0;
        try {
            $stmt = $this->db->prepare("SELECT course_fee FROM branch_course_settings WHERE branch_id = :bid AND course_id = :cid LIMIT 1");
            $stmt->execute([':bid' => $branchId, ':cid' => $student['course_id'] ?? 0]);
            $bFee = $stmt->fetchColumn();
            if ($bFee !== false && $bFee !== null && (float)$bFee > 0) {
                $courseFee = (float)$bFee;
            }
        } catch (\Throwable $e) {}
        if ($courseFee == 0) $courseFee = (float)($student['fee'] ?? $student['fee_amount'] ?? 0);

        $this->branchAdminView('students/admission_preview', [
            'title'       => 'Admission Confirmation',
            'currentPage' => 'students',
            'student'     => $student,
            'courseFee'   => $courseFee,
            'studentId'   => $id,
        ]);
    }

    /**
     * Generate and download admission form as PDF.
     */
    public function admissionPdf(array $params): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();
        $id = (int)($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            "SELECT s.*, u.name, u.email, u.phone, u.photo, c.name as course_name, c.duration, c.fee,
                    b.*
             FROM students s
             INNER JOIN users u ON s.user_id = u.id
             LEFT JOIN courses c ON s.course_id = c.id
             LEFT JOIN branches b ON s.branch_id = b.id
             WHERE s.id = :sid AND s.branch_id = :bid LIMIT 1"
        );
        $stmt->execute([':sid' => $id, ':bid' => $branchId]);
        $student = $stmt->fetch(\PDO::FETCH_ASSOC) ?: null;

        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        $courseFee = 0;
        try {
            $stmt = $this->db->prepare("SELECT course_fee FROM branch_course_settings WHERE branch_id = :bid AND course_id = :cid LIMIT 1");
            $stmt->execute([':bid' => $branchId, ':cid' => $student['course_id'] ?? 0]);
            $bFee = $stmt->fetchColumn();
            if ($bFee !== false && $bFee !== null && (float)$bFee > 0) {
                $courseFee = (float)$bFee;
            }
        } catch (\Throwable $e) {}
        if ($courseFee == 0) $courseFee = (float)($student['fee'] ?? $student['fee_amount'] ?? 0);

        $this->generateAdmissionPdf([
            'student'    => $student,
            'courseFee'  => $courseFee,
        ]);
    }

    /**
     * Read SMTP settings directly from settings table (both column pairs).
     * Returns: ['smtp_host' => ..., 'smtp_port' => ..., 'smtp_username' => ..., ...]
     */
    private function readSmtpSettingsDirect(): array
    {
        $keys = ['smtp_host', 'smtp_port', 'smtp_username', 'smtp_password', 'smtp_encryption', 'smtp_from_email', 'smtp_from_name'];
        $result = [];

        // Method 1: setting_key / setting_value
        try {
            $placeholders = implode(',', array_fill(0, count($keys), '?'));
            $stmt = $this->db->prepare("SELECT `setting_key`, `setting_value` FROM `settings` WHERE `setting_key` IN ({$placeholders})");
            $stmt->execute($keys);
            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
                $k = $row['setting_key'] ?? '';
                if (in_array($k, $keys, true)) {
                    $result[$k] = $row['setting_value'] ?? '';
                }
            }
        } catch (\Throwable $e) {
            error_log('readSmtpSettingsDirect (method 1) failed: ' . $e->getMessage());
        }

        // Method 2: key_name / value (fill missing or empty)
        try {
            $placeholders = implode(',', array_fill(0, count($keys), '?'));
            $stmt = $this->db->prepare("SELECT `key_name`, `value` FROM `settings` WHERE `key_name` IN ({$placeholders})");
            $stmt->execute($keys);
            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $row) {
                $k = $row['key_name'] ?? '';
                $v = $row['value'] ?? '';
                if (in_array($k, $keys, true) && (!isset($result[$k]) || $result[$k] === '')) {
                    $result[$k] = $v;
                }
            }
        } catch (\Throwable $e) {
            error_log('readSmtpSettingsDirect (method 2) failed: ' . $e->getMessage());
        }

        return $result;
    }

    /**
     * Find PHPMailer library path (tries multiple locations).
     */
    private function findPHPMailerPath(): ?string
    {
        $basePath = defined('BASE_PATH') ? BASE_PATH : '';
        $docRoot  = rtrim($_SERVER['DOCUMENT_ROOT'] ?? '', '/');
        $relRoot  = dirname(__DIR__, 3); // 3 levels up from branch/Controllers/

        $paths = [
            $docRoot . $basePath . '/phpmailer/src',
            $docRoot . '/phpmailer/src',
            $relRoot . '/phpmailer/src',
            $docRoot . $basePath . '/PHPMailer/src',
            $docRoot . '/PHPMailer/src',
            $relRoot . '/PHPMailer/src',
            '/home/advaysin/public_html/gmind/phpmailer/src',
            '/home/advaysin/public_html/phpmailer/src',
        ];

        foreach ($paths as $path) {
            $realPath = realpath($path);
            if ($realPath && is_dir($realPath) && file_exists($realPath . '/PHPMailer.php')) {
                return $realPath;
            }
        }
        return null;
    }

    /**
     * Build HTML email body for admission confirmation.
     */
    private function buildAdmissionEmailHtml(array $data): string
    {
        $studentName  = htmlspecialchars($data['student_name'] ?? '', ENT_QUOTES);
        $enrollmentNo = htmlspecialchars($data['enrollment_no'] ?? '', ENT_QUOTES);
        $courseName   = htmlspecialchars($data['course_name'] ?? '', ENT_QUOTES);
        $branchName   = htmlspecialchars($data['branch_name'] ?? 'GMIND INSTITUTE', ENT_QUOTES);
        $branchCode   = htmlspecialchars($data['branch_code'] ?? '', ENT_QUOTES);
        $password     = htmlspecialchars($data['password'] ?? '', ENT_QUOTES);
        $email        = htmlspecialchars($data['email'] ?? '', ENT_QUOTES);
        $phone        = htmlspecialchars($data['phone'] ?? '', ENT_QUOTES);
        $admissionDate= htmlspecialchars($data['admission_date'] ?? date('Y-m-d'), ENT_QUOTES);
        $courseFee    = htmlspecialchars(number_format((float)($data['course_fee'] ?? 0), 2), ENT_QUOTES);
        $loginUrl     = htmlspecialchars($data['login_url'] ?? '', ENT_QUOTES);

        return <<<HTML
<!DOCTYPE html>
<html><head><meta charset="UTF-8"></head>
<body style="margin:0;padding:0;font-family:'Segoe UI',Arial,sans-serif;background:#f3f4f6;">
  <table width="100%" cellpadding="0" cellspacing="0" style="background:#f3f4f6;padding:20px;">
    <tr><td align="center">
      <table width="600" cellpadding="0" cellspacing="0" style="background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 8px rgba(0,0,0,0.06);">
        <tr><td style="background:linear-gradient(135deg,#16a085,#117a65);padding:24px 32px;text-align:center;">
          <h1 style="margin:0;color:#fff;font-size:1.6rem;">GMIND INSTITUTE</h1>
          <p style="margin:6px 0 0;color:rgba(255,255,255,0.9);font-size:0.88rem;">{$branchName} (Branch: {$branchCode})</p>
        </td></tr>
        <tr><td style="padding:32px;">
          <h2 style="margin:0 0 16px;color:#2c1e4a;font-size:1.3rem;">🎉 Admission Confirmed!</h2>
          <p style="margin:0 0 16px;color:#4b5563;font-size:0.95rem;line-height:1.6;">
            Dear <strong>{$studentName}</strong>,<br><br>
            Welcome to <strong>{$branchName}</strong>! Your admission has been successfully confirmed. Below are your enrollment details:
          </p>
          <table width="100%" cellpadding="12" cellspacing="0" style="border:1px solid #e5e7eb;border-radius:8px;margin:16px 0;">
            <tr style="background:#f8f4ff;"><td style="color:#6c3fe8;font-weight:600;font-size:0.85rem;">Enrollment No</td><td style="color:#2c1e4a;font-weight:700;">{$enrollmentNo}</td></tr>
            <tr><td style="color:#6c3fe8;font-weight:600;font-size:0.85rem;border-top:1px solid #e5e7eb;">Course</td><td style="color:#2c1e4a;border-top:1px solid #e5e7eb;">{$courseName}</td></tr>
            <tr><td style="color:#6c3fe8;font-weight:600;font-size:0.85rem;border-top:1px solid #e5e7eb;">Admission Date</td><td style="color:#2c1e4a;border-top:1px solid #e5e7eb;">{$admissionDate}</td></tr>
            <tr><td style="color:#6c3fe8;font-weight:600;font-size:0.85rem;border-top:1px solid #e5e7eb;">Total Course Fee</td><td style="color:#2c1e4a;border-top:1px solid #e5e7eb;">&#8377; {$courseFee}</td></tr>
          </table>
          <div style="background:#fef3c7;border-left:4px solid #d97706;padding:14px 18px;border-radius:6px;margin:20px 0;">
            <p style="margin:0 0 8px;color:#b45309;font-weight:700;font-size:0.9rem;">🔐 Your Login Credentials</p>
            <p style="margin:0;color:#92400e;font-size:0.88rem;">
              <strong>Login URL:</strong> <a href="{$loginUrl}" style="color:#2563eb;">{$loginUrl}</a><br>
              <strong>Email:</strong> {$email}<br>
              <strong>Password:</strong> {$password} <em style="color:#6b7280;font-size:0.8rem;">(last 6 digits of your phone)</em>
            </p>
          </div>
          <p style="margin:16px 0;color:#4b5563;font-size:0.88rem;line-height:1.6;">
            Please keep these credentials safe. You can use them to login to the student portal and access your course materials, exam results, and certificates.
          </p>
          <div style="background:#fee2e2;border-left:4px solid #dc2626;padding:14px 18px;border-radius:6px;margin:20px 0;">
            <p style="margin:0;color:#b91c1c;font-size:0.85rem;"><strong>📌 Important Terms:</strong></p>
            <ul style="margin:6px 0 0;padding-left:20px;color:#b91c1c;font-size:0.82rem;">
              <li>Fee once paid will NOT be refundable.</li>
              <li>Certificate will be issued only after full fee payment and course completion.</li>
              <li>Please maintain 75% attendance to be eligible for examination.</li>
            </ul>
          </div>
          <p style="margin:24px 0 0;color:#4b5563;font-size:0.88rem;line-height:1.6;">
            If you have any questions, please contact your branch office.<br><br>
            Best regards,<br>
            <strong>{$branchName}</strong><br>
            <span style="color:#6b7280;font-size:0.82rem;">GMIND Institute Management System</span>
          </p>
        </td></tr>
        <tr><td style="background:#f8f9fa;padding:16px 32px;text-align:center;border-top:1px solid #e5e7eb;">
          <p style="margin:0;color:#9ca3af;font-size:0.75rem;">This is an automated email. Please do not reply. Generated on {$admissionDate}</p>
        </td></tr>
      </table>
    </td></tr>
  </table>
</body></html>
HTML;
    }

    /**
     * Helper: get course name by ID.
     */
    private function getCourseName($courseId): string
    {
        try {
            $stmt = $this->db->prepare("SELECT name FROM courses WHERE id = :cid LIMIT 1");
            $stmt->execute([':cid' => $courseId]);
            return $stmt->fetchColumn() ?: '';
        } catch (\Throwable $e) {
            return '';
        }
    }

    /**
     * Generate admission form PDF using FPDF — attractive professional design.
     */
    private function generateAdmissionPdf(array $data): void
    {
        // Load FPDF
        if (!class_exists('FPDF')) {
            $candidates = [
                dirname(dirname(dirname(__DIR__))) . '/fpdf/fpdf.php',
                dirname(dirname(dirname(dirname(__DIR__)))) . '/fpdf/fpdf.php',
                $_SERVER['DOCUMENT_ROOT'] . '/fpdf/fpdf.php',
                $_SERVER['DOCUMENT_ROOT'] . '/gmind/fpdf/fpdf.php',
                '/home/advaysin/public_html/gmind/fpdf/fpdf.php',
                '/home/advaysin/public_html/fpdf/fpdf.php',
            ];
            foreach ($candidates as $path) {
                if (file_exists($path)) {
                    require_once $path;
                    break;
                }
            }
        }

        if (!class_exists('FPDF')) {
            flash('error', 'FPDF library not found at /gmind/fpdf/fpdf.php');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        $student = $data['student'];
        $courseFee = $data['courseFee'];

        // ✅ Safe column access — branches table schema varies
        $branchName    = $student['name'] ?? $student['branch_name'] ?? 'GMIND INSTITUTE';
        // Note: 'name' collides with student name (u.name). Use 'branch_name' alias if available; fallback.
        // Since SELECT b.* gives 'name' for branch, but u.name (student) overrides — we need to re-fetch branch name.
        if (empty($student['branch_name'])) {
            try {
                $stmtB = $this->db->prepare("SELECT name FROM branches WHERE id = :bid LIMIT 1");
                $stmtB->execute([':bid' => $student['branch_id'] ?? 0]);
                $branchName = $stmtB->fetchColumn() ?: 'GMIND INSTITUTE';
            } catch (\Throwable $e) {}
        }
        $branchCode    = $student['branch_code'] ?? '—';
        $branchAddress = $student['address'] ?? '';
        $branchCity    = $student['city'] ?? '';
        $branchState   = $student['state'] ?? '';
        $branchPin     = $student['pin_code'] ?? $student['pin'] ?? '';
        $branchPhone   = $student['phone'] ?? '';  // ⚠️ This is student phone because u.phone wins. Will override below.
        $branchEmail   = $student['email'] ?? ''; // Same issue.

        // Re-fetch branch contact info separately (since b.* is overridden by u.*)
        try {
            $stmtB = $this->db->prepare("SELECT phone, email, address, city, state, pin_code FROM branches WHERE id = :bid LIMIT 1");
            $stmtB->execute([':bid' => $student['branch_id'] ?? 0]);
            $branchRow = $stmtB->fetch(\PDO::FETCH_ASSOC);
            if ($branchRow) {
                $branchAddress = $branchRow['address'] ?? $branchAddress;
                $branchCity    = $branchRow['city']    ?? $branchCity;
                $branchState   = $branchRow['state']   ?? $branchState;
                $branchPin     = $branchRow['pin_code'] ?? $branchPin;
                $branchPhone   = $branchRow['phone']   ?? '';
                $branchEmail   = $branchRow['email']   ?? '';
            }
        } catch (\Throwable $e) {}

        // Student details (use u.* aliases that won via SELECT s.*, u.name)
        $studentName   = $student['name'] ?? '—';  // This is student name (u.name)
        $studentEmail  = $student['email'] ?? '—';
        $studentPhone  = $student['phone'] ?? '—';

        $pdf = new \FPDF('P', 'mm', 'A4');
        $pdf->AddPage();
        $pdf->SetMargins(0, 0, 0);
        $pdf->SetAutoPageBreak(false);

        // ═══════════════════════════════════════════════════════════════════
        // HEADER — Green gradient bar with institute name
        // ═══════════════════════════════════════════════════════════════════
        $pdf->SetFillColor(13, 59, 59);  // Dark teal
        $pdf->Rect(0, 0, 210, 38, 'F');

        $pdf->SetFillColor(22, 160, 133);  // Green accent stripe
        $pdf->Rect(0, 38, 210, 2, 'F');

        // Institute name (white, centered)
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFont('Arial', 'B', 22);
        $pdf->SetXY(0, 6);
        $pdf->Cell(210, 10, 'GMIND INSTITUTE', 0, 1, 'C');

        // Branch name
        $pdf->SetFont('Arial', '', 11);
        $pdf->SetTextColor(180, 230, 220);
        $pdf->SetXY(0, 18);
        $pdf->Cell(210, 5, strtoupper($branchName), 0, 1, 'C');

        // Branch code badge
        if (!empty($branchCode) && $branchCode !== '—') {
            $pdf->SetFont('Arial', 'B', 8);
            $pdf->SetTextColor(255, 255, 255);
            $pdf->SetXY(0, 25);
            $pdf->Cell(210, 5, 'Branch Code: ' . $branchCode, 0, 1, 'C');
        }

        // ═══════════════════════════════════════════════════════════════════
        // TITLE BAR — Purple
        // ═══════════════════════════════════════════════════════════════════
        $pdf->SetFillColor(108, 63, 232);
        $pdf->Rect(0, 44, 210, 12, 'F');
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFont('Arial', 'B', 14);
        $pdf->SetXY(0, 47);
        $pdf->Cell(210, 7, 'ADMISSION FORM', 0, 0, 'C');

        // ═══════════════════════════════════════════════════════════════════
        // BODY — White area with content
        // ═══════════════════════════════════════════════════════════════════
        $pdf->SetMargins(15, 60, 15);
        $pdf->SetY(62);

        // ─── Photo Box (right-aligned) ─────────────────────────────────────
        $photoPath = '';
        if (!empty($student['photo'])) {
            $candidates = [
                $student['photo'],
                $_SERVER['DOCUMENT_ROOT'] . '/' . ltrim($student['photo'], '/'),
                $_SERVER['DOCUMENT_ROOT'] . '/gmind/' . ltrim($student['photo'], '/'),
                '/home/advaysin/public_html/' . ltrim($student['photo'], '/'),
                '/home/advaysin/public_html/gmind/' . ltrim($student['photo'], '/'),
            ];
            foreach ($candidates as $c) {
                if (file_exists($c)) {
                    $photoPath = $c;
                    break;
                }
            }
        }
        if ($photoPath) {
            try {
                // Photo border
                $pdf->SetDrawColor(22, 160, 133);
                $pdf->SetLineWidth(0.8);
                $pdf->Rect(165, 62, 30, 38, 'D');
                $pdf->Image($photoPath, 166, 63, 28, 36);
                // Label below
                $pdf->SetTextColor(150, 150, 150);
                $pdf->SetFont('Arial', '', 7);
                $pdf->SetXY(160, 101);
                $pdf->Cell(40, 4, 'Student Photo', 0, 0, 'C');
            } catch (\Throwable $e) {}
        }

        // ─── Branch Address (under header) ────────────────────────────────
        $pdf->SetTextColor(100, 116, 139);
        $pdf->SetFont('Arial', '', 8);
        $addrParts = array_filter([$branchAddress, $branchCity, $branchState, $branchPin]);
        $addrLine = implode(', ', $addrParts);
        if (!empty($addrLine)) {
            $pdf->SetX(15);
            $pdf->MultiCell(145, 4, $addrLine, 0, 'L');
        }
        if (!empty($branchPhone) || !empty($branchEmail)) {
            $contactLine = '';
            if (!empty($branchPhone)) $contactLine .= 'Phone: ' . $branchPhone;
            if (!empty($branchEmail)) $contactLine .= (!empty($branchPhone) ? '  |  ' : '') . 'Email: ' . $branchEmail;
            $pdf->SetX(15);
            $pdf->Cell(145, 4, $contactLine, 0, 1, 'L');
        }
        $pdf->Ln(3);

        // ─── Enrollment Highlight Banner ──────────────────────────────────
        $pdf->SetFillColor(220, 252, 231);  // Light green
        $pdf->Rect(15, $pdf->GetY(), 145, 14, 'F');
        $pdf->SetDrawColor(22, 163, 74);
        $pdf->SetLineWidth(0.3);
        $pdf->Rect(15, $pdf->GetY(), 145, 14, 'D');
        $pdf->SetFont('Arial', 'B', 9);
        $pdf->SetTextColor(22, 101, 52);
        $pdf->SetXY(18, $pdf->GetY() + 2);
        $pdf->Cell(60, 5, 'ENROLLMENT NO:', 0, 0);
        $pdf->SetFont('Arial', 'B', 12);
        $pdf->SetTextColor(22, 160, 133);
        $pdf->Cell(80, 5, $student['enrollment_no'] ?? '—', 0, 0);
        $pdf->SetFont('Arial', '', 8);
        $pdf->SetTextColor(22, 101, 52);
        $pdf->SetXY(18, $pdf->GetY() + 6);
        $pdf->Cell(140, 5, 'Admission Date: ' . (!empty($student['admission_date']) ? date('d M Y', strtotime($student['admission_date'])) : date('d M Y')), 0, 0);
        $pdf->Ln(20);

        // ─── Personal Information Section ─────────────────────────────────
        $pdf->SetFillColor(108, 63, 232);
        $pdf->Rect(15, $pdf->GetY(), 180, 7, 'F');
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFont('Arial', 'B', 10);
        $pdf->SetX(18);
        $pdf->Cell(174, 6, 'PERSONAL INFORMATION', 0, 0, 'L');
        $pdf->Ln(8);

        // Two-column detail grid
        $this->pdfDetailRow($pdf, 'Student Name', $studentName, "Father's Name", $student['father_name'] ?? '—');
        $this->pdfDetailRow($pdf, 'Email', $studentEmail, 'Phone', $studentPhone);
        $this->pdfDetailRow($pdf, 'Date of Birth', !empty($student['dob']) ? date('d M Y', strtotime($student['dob'])) : '—', 'Guardian Name', $student['guardian_name'] ?? '—');
        $this->pdfDetailRow($pdf, 'Guardian Phone', $student['guardian_phone'] ?? '—', 'Address', trim(($student['address'] ?? '') . ', ' . ($student['city'] ?? '') . ', ' . ($student['state'] ?? '') . ' - ' . ($student['pin_code'] ?? '')));
        $pdf->Ln(3);

        // ─── Course & Fee Section ─────────────────────────────────────────
        $pdf->SetFillColor(22, 160, 133);
        $pdf->Rect(15, $pdf->GetY(), 180, 7, 'F');
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFont('Arial', 'B', 10);
        $pdf->SetX(18);
        $pdf->Cell(174, 6, 'COURSE & FEE DETAILS', 0, 0, 'L');
        $pdf->Ln(8);

        $this->pdfDetailRow($pdf, 'Course', $student['course_name'] ?? '—', 'Duration', $student['duration'] ?? '—');
        $this->pdfDetailRow($pdf, 'Batch', $student['batch_name'] ?? '—', 'Total Course Fee', 'Rs. ' . number_format($courseFee, 2));
        $pdf->Ln(5);

        // ─── Terms & Conditions Box (Amber) ───────────────────────────────
        $termsStartY = $pdf->GetY();
        $pdf->SetFillColor(254, 247, 205);
        $pdf->Rect(15, $termsStartY, 180, 48, 'F');
        $pdf->SetDrawColor(217, 119, 6);
        $pdf->SetLineWidth(0.5);
        $pdf->Rect(15, $termsStartY, 180, 48, 'D');
        $pdf->SetXY(18, $termsStartY + 3);
        $pdf->SetFont('Arial', 'B', 10);
        $pdf->SetTextColor(146, 64, 14);
        $pdf->Cell(174, 6, 'TERMS & CONDITIONS', 0, 1);

        $pdf->SetFont('Arial', '', 8);
        $pdf->SetTextColor(120, 53, 15);
        $terms = [
            '1. Fee once paid will NOT be refundable under any circumstances.',
            '2. Student must maintain 75% attendance to be eligible for examination.',
            '3. Certificate/Marksheet will be issued only after full fee payment.',
            '4. The information provided above is true and correct to the best of my knowledge.',
            '5. Institute reserves the right to cancel admission in case of false information.',
        ];
        foreach ($terms as $t) {
            $pdf->SetX(18);
            $pdf->MultiCell(174, 5, $t, 0, 'L');
        }
        $pdf->Ln(12);

        // ─── Signature Row ────────────────────────────────────────────────
        $sigY = $pdf->GetY() + 8;
        $pdf->SetDrawColor(44, 30, 74);
        $pdf->SetLineWidth(0.3);
        $pdf->Line(25, $sigY, 85, $sigY);
        $pdf->Line(125, $sigY, 185, $sigY);
        $pdf->SetTextColor(100, 116, 139);
        $pdf->SetFont('Arial', '', 9);
        $pdf->SetXY(20, $sigY + 2);
        $pdf->Cell(70, 5, 'Student / Guardian Signature', 0, 0, 'C');
        $pdf->SetXY(120, $sigY + 2);
        $pdf->Cell(70, 5, 'Authorized Signatory', 0, 0, 'C');
        $pdf->Ln(20);

        // ─── Footer ───────────────────────────────────────────────────────
        $pdf->SetFillColor(248, 250, 252);
        $pdf->Rect(0, 285, 210, 12, 'F');
        $pdf->SetDrawColor(22, 160, 133);
        $pdf->SetLineWidth(0.5);
        $pdf->Line(0, 285, 210, 285);
        $pdf->SetTextColor(150, 150, 150);
        $pdf->SetFont('Arial', 'I', 8);
        $pdf->SetXY(0, 288);
        $pdf->Cell(210, 5, 'Generated on ' . date('d M Y, h:i A') . '  |  GMIND Institute Management System  |  This is a computer-generated document.', 0, 0, 'C');

        // Output
        $studentNameFile = preg_replace('/[^a-zA-Z0-9]/', '_', $studentName);
        $downloadName = 'Admission_Form_' . $studentNameFile . '_' . ($student['enrollment_no'] ?? $student['id']) . '.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('D', $downloadName);
        exit;
    }

    /**
     * Helper: render a 2-column detail row in the PDF.
     */
    private function pdfDetailRow(\FPDF $pdf, string $label1, $value1, string $label2, $value2): void
    {
        $y = $pdf->GetY();
        // Label 1
        $pdf->SetXY(15, $y);
        $pdf->SetFont('Arial', 'B', 8);
        $pdf->SetTextColor(108, 63, 232);
        $pdf->Cell(45, 5, strtoupper($label1), 0, 0);
        // Value 1
        $pdf->SetFont('Arial', '', 9);
        $pdf->SetTextColor(44, 30, 74);
        $pdf->Cell(45, 5, $value1, 0, 0);
        // Label 2
        $pdf->SetFont('Arial', 'B', 8);
        $pdf->SetTextColor(108, 63, 232);
        $pdf->Cell(45, 5, strtoupper($label2), 0, 0);
        // Value 2
        $pdf->SetFont('Arial', '', 9);
        $pdf->SetTextColor(44, 30, 74);
        $pdf->Cell(45, 5, $value2, 0, 1);
        $pdf->Ln(2);
    }

    public function show(array $params): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();
        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare(
            "SELECT s.*, u.name, u.email, u.phone, u.photo, c.name as course_name, c.duration,
                    b.batch_name, b.start_time, b.end_time
             FROM students s
             LEFT JOIN users u ON s.user_id = u.id
             LEFT JOIN courses c ON s.course_id = c.id
             LEFT JOIN batches b ON s.batch_id = b.id
             WHERE s.id = ? AND s.branch_id = ?"
        );
        $stmt->execute([$id, $branchId]);
        $student = $stmt->fetch(\PDO::FETCH_ASSOC) ?: null;

        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        // ✅ Compute branch-specific fee
        $courseFee = 0;
        try {
            $stmt = $this->db->prepare("SELECT course_fee FROM branch_course_settings WHERE branch_id = :bid AND course_id = :cid LIMIT 1");
            $stmt->execute([':bid' => $branchId, ':cid' => $student['course_id'] ?? 0]);
            $bFee = $stmt->fetchColumn();
            if ($bFee !== false && $bFee !== null && (float)$bFee > 0) {
                $courseFee = (float)$bFee;
            }
        } catch (\Throwable $e) {}
        if ($courseFee == 0) {
            $courseFee = (float)($student['fee_amount'] ?? 0);
        }

        // ✅ Get total paid from fee_payments
        $totalPaid = 0;
        try {
            $stmt = $this->db->prepare("SELECT COALESCE(SUM(amount), 0) FROM fee_payments WHERE student_id = :sid AND payment_mode != 'pending'");
            $stmt->execute([':sid' => $id]);
            $totalPaid = (float)$stmt->fetchColumn();
        } catch (\Throwable $e) {
            $totalPaid = (float)($student['fee_paid'] ?? 0);
        }

        $student['course_fee'] = $courseFee;
        $student['fee_paid']   = $totalPaid;
        $student['fee_due']    = max(0, $courseFee - $totalPaid);

        $stmt = $this->db->prepare("SELECT er.*, e.title as exam_title, e.total_marks FROM exam_results er LEFT JOIN exams e ON er.exam_id = e.id WHERE er.student_id = ? ORDER BY er.created_at DESC");
        $stmt->execute([$id]);
        $examResults = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $stmt = $this->db->prepare("SELECT * FROM fee_payments WHERE student_id = ? ORDER BY payment_date DESC, id DESC");
        $stmt->execute([$id]);
        $feePayments = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $this->branchAdminView('students/show', [
            'title' => 'Student Details', 'currentPage' => 'students',
            'student' => $student, 'examResults' => $examResults, 'feePayments' => $feePayments,
            'csrfToken' => $this->csrfToken ?? '',
        ]);
    }

    public function edit(array $params): void
    {
        requireBranchAdmin();
        $branchId = $this->getBranchId();
        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare("SELECT s.*, u.name, u.email, u.phone, c.name as course_name 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 = ? AND s.branch_id = ?");
        $stmt->execute([$id, $branchId]);
        $student = $stmt->fetch(\PDO::FETCH_ASSOC) ?: null;

        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        $stmt = $this->db->prepare("SELECT c.*, bcf.fee_amount FROM courses c INNER JOIN branch_course_fees bcf ON c.id = bcf.course_id WHERE bcf.branch_id = ? AND bcf.is_active = 1 ORDER BY c.name");
        $stmt->execute([$branchId]);
        $courses = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $this->branchAdminView('students/edit', [
            'title' => 'Edit Student', 'currentPage' => 'students', 'student' => $student, 'courses' => $courses,
        ]);
    }

    public function update(array $params): void
    {
        requireBranchAdmin();
        $this->validateCsrf();
        $branchId = $this->getBranchId();
        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare("SELECT * FROM students WHERE id = ? AND branch_id = ?");
        $stmt->execute([$id, $branchId]);
        $student = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        $name = sanitize($_POST['name'] ?? '');
        $email = sanitize($_POST['email'] ?? '');
        $phone = sanitize($_POST['phone'] ?? '');
        $courseId = sanitize($_POST['course_id'] ?? '');
        $dob = sanitize($_POST['dob'] ?? '') ?: null;
        $address = sanitize($_POST['address'] ?? '');
        $guardianName = sanitize($_POST['guardian_name'] ?? '');
        $guardianPhone = sanitize($_POST['guardian_phone'] ?? '');
        $status = sanitize($_POST['status'] ?? $student['status']);
        $admissionDate = sanitize($_POST['admission_date'] ?? $student['admission_date']);

        if (empty($name) || empty($email)) {
            flash('error', 'Name and email are required.');
            redirect(BASE_PATH . '/branch/students/' . $id . '/edit');
            return;
        }

        try {
            $this->db->beginTransaction();

            $stmt = $this->db->prepare("UPDATE users SET name = ?, email = ?, phone = ?, updated_at = NOW() WHERE id = ?");
            $stmt->execute([$name, $email, $phone, $student['user_id']]);

            $password = $_POST['password'] ?? '';
            if (!empty($password)) {
                $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
                $this->db->prepare("UPDATE users SET password = ? WHERE id = ?")->execute([$hashedPassword, $student['user_id']]);
            }

            $stmt = $this->db->prepare(
                "UPDATE students SET course_id = ?, dob = ?, address = ?, guardian_name = ?, guardian_phone = ?, status = ?, admission_date = ?, updated_at = NOW() WHERE id = ? AND branch_id = ?"
            );
            $stmt->execute([$courseId, $dob, $address, $guardianName, $guardianPhone, $status, $admissionDate, $id, $branchId]);

            $this->db->commit();
            flash('success', 'Student updated successfully.');
        } catch (\Exception $e) {
            $this->db->rollBack();
            flash('error', 'Failed to update student. ' . $e->getMessage());
        }
        redirect(BASE_PATH . '/branch/students');
    }

    public function delete(array $params): void
    {
        requireBranchAdmin();
        $this->validateCsrf();
        $branchId = $this->getBranchId();
        $id = (int) ($params['id'] ?? 0);

        $stmt = $this->db->prepare("SELECT * FROM students WHERE id = ? AND branch_id = ?");
        $stmt->execute([$id, $branchId]);
        $student = $stmt->fetch(\PDO::FETCH_ASSOC);
        if (!$student) {
            flash('error', 'Student not found.');
            redirect(BASE_PATH . '/branch/students');
            return;
        }

        try {
            $this->db->beginTransaction();
            $this->db->prepare("DELETE FROM students WHERE id = ? AND branch_id = ?")->execute([$id, $branchId]);
            $this->db->prepare("DELETE FROM users WHERE id = ?")->execute([$student['user_id']]);
            $this->db->commit();
            flash('success', 'Student deleted successfully.');
        } catch (\Exception $e) {
            $this->db->rollBack();
            flash('error', 'Failed to delete student. ' . $e->getMessage());
        }
        redirect(BASE_PATH . '/branch/students');
    }

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

    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 []; }
    }

    /**
     * Ensure students table has all required columns.
     */
    private function ensureStudentColumns(): void
    {
        $existing = $this->getTableColumns('students');
        $needed = [
            '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",
            '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",
            'guardian_name'  => "ADD COLUMN `guardian_name` VARCHAR(150) NULL DEFAULT NULL",
            'guardian_phone' => "ADD COLUMN `guardian_phone` VARCHAR(20) NULL DEFAULT NULL",
            'batch_id'       => "ADD COLUMN `batch_id` INT UNSIGNED NULL DEFAULT NULL",
            'fee_amount'     => "ADD COLUMN `fee_amount` DECIMAL(10,2) NOT NULL DEFAULT 0",
            'fee_paid'       => "ADD COLUMN `fee_paid` DECIMAL(10,2) NOT NULL DEFAULT 0",
            'fee_due'        => "ADD COLUMN `fee_due` DECIMAL(10,2) NOT NULL DEFAULT 0",
            'status'         => "ADD COLUMN `status` VARCHAR(20) NOT NULL DEFAULT 'active'",
            'created_at'     => "ADD COLUMN `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP",
            'updated_at'     => "ADD COLUMN `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
        ];
        foreach ($needed as $col => $ddl) {
            if (!in_array($col, $existing, true)) {
                try { $this->db->exec("ALTER TABLE `students` {$ddl}"); } catch (\Throwable $e) {}
            }
        }
    }
}
