<?php
session_start();
require_once '../config/database.php';

// Only admins can access this page
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] !== 'admin') {
    header('Location: ../login.php');
    exit();
}

$error_message = '';
$success_message = '';

// Handle payment verification
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['verify_payment'])) {
    try {
        // Get payment data from POST
        $transaction_id = (int)($_POST['transaction_id'] ?? 0);
        $action = $_POST['verify_payment']; // 'approve' or 'reject' - get from button value
        $admin_notes = trim($_POST['admin_notes'] ?? '');
        
        if ($transaction_id <= 0) {
            throw new Exception("Invalid transaction data");
        }
        
        // Get transaction details
        $stmt = $pdo->prepare("
            SELECT bt.*, s.first_name, s.last_name, s.student_id as student_code, ba.bank_name, ba.branch_name
            FROM bank_transactions bt
            JOIN students s ON bt.student_id = s.id
            JOIN bank_accounts ba ON bt.bank_account_id = ba.id
            WHERE bt.id = ? AND bt.status = 'pending'
        ");
        $stmt->execute([$transaction_id]);
        $transaction = $stmt->fetch();
        
        if (!$transaction) {
            throw new Exception("Transaction not found or already processed");
        }
        
        // Check if this transaction ID has already been verified for any student
        if ($action === 'approve') {
            $stmt = $pdo->prepare("
                SELECT bt.id, s.first_name, s.last_name, s.student_id as student_code
                FROM bank_transactions bt
                JOIN students s ON bt.student_id = s.id
                WHERE bt.transaction_id = ? AND bt.status = 'verified'
            ");
            $stmt->execute([$transaction['transaction_id']]);
            $existing_verified = $stmt->fetch();
            
            if ($existing_verified) {
                throw new Exception("This transaction ID has already been verified for student " . 
                    $existing_verified['student_code'] . " (" . 
                    $existing_verified['first_name'] . " " . 
                    $existing_verified['last_name'] . "). " .
                    "The same transaction cannot be accepted multiple times.");
            }
        }
        
        if ($action === 'approve') {
            // Update fee records (distribute payment across due fees)
            $pdo->beginTransaction();
            
            try {
                // Get due fees for this student
                $stmt = $pdo->prepare("
                    SELECT id, amount, paid_amount 
                    FROM fees 
                    WHERE student_id = ? AND status IN ('pending', 'partial', 'overdue') 
                    ORDER BY due_date ASC
                ");
                $stmt->execute([$transaction['student_id']]);
                $due_fees = $stmt->fetchAll();
                
                $remaining_amount = (float)$transaction['amount'];
                $updated_fees = [];
                
                foreach ($due_fees as $fee) {
                    if ($remaining_amount <= 0) break;
                    
                    $due_amount = $fee['amount'] - $fee['paid_amount'];
                    $payment_amount = min($remaining_amount, $due_amount);
                    
                    $new_paid_amount = $fee['paid_amount'] + $payment_amount;
                    $new_status = ($new_paid_amount >= $fee['amount']) ? 'paid' : 'partial';
                    
                    $stmt = $pdo->prepare("
                        UPDATE fees 
                        SET paid_amount = ?, status = ?, paid_date = IF(paid_date IS NULL, CURDATE(), paid_date)
                        WHERE id = ?
                    ");
                    $stmt->execute([$new_paid_amount, $new_status, $fee['id']]);
                    
                    // Insert payment record for this specific fee
                    $ins = $pdo->prepare("
                        INSERT INTO fee_payments (fee_id, amount, payment_date, payment_method, transaction_id, notes, created_by) 
                        VALUES (?, ?, NOW(), ?, ?, ?, ?)
                    ");
                    $ins->execute([
                        $fee['id'], 
                        $payment_amount, 
                        'bank', 
                        $transaction['transaction_id'], 
                        'Bank payment to ' . $transaction['bank_name'] . ' - ' . $transaction['branch_name'] . '. Verified by admin. Notes: ' . $admin_notes, 
                        $_SESSION['user_id'] ?? null
                    ]);
                    
                    $remaining_amount -= $payment_amount;
                    $updated_fees[] = [
                        'fee_id' => $fee['id'],
                        'paid_amount' => $payment_amount
                    ];
                }
                
                // Update transaction status
                $stmt = $pdo->prepare("
                    UPDATE bank_transactions 
                    SET status = 'verified', verified_at = NOW(), verified_by = ?, notes = ?
                    WHERE id = ?
                ");
                $stmt->execute([$_SESSION['user_id'], $admin_notes, $transaction_id]);
                
                $pdo->commit();
                
                $success_message = "Payment verified and applied successfully!";
                
                // Send SMS notification to student
                try {
                    $smsHelperPath = __DIR__ . '/../includes/sms_helper.php';
                    if (file_exists($smsHelperPath)) {
                        require_once $smsHelperPath;
                        
                        // Check if non-musking SMS service is enabled
                        $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = 'non_musking_enabled'");
                        $stmt->execute();
                        $result = $stmt->fetch();
                        $nonMuskingEnabled = $result && $result['setting_value'] === '1';
                        
                        // Check if bank transaction notifications are enabled
                        $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = 'sms_send_bank_transaction_notifications'");
                        $stmt->execute();
                        $result = $stmt->fetch();
                        $bankNotificationsEnabled = $result && $result['setting_value'] === '1';
                        
                        if ($nonMuskingEnabled && $bankNotificationsEnabled) {
                            $cfg = sms_config();
                            $numbers = [];
                            // Fetch student's phone numbers
                            $pStmt = $pdo->prepare('SELECT phone, guardian_phone, first_name, last_name FROM students WHERE id=?');
                            $pStmt->execute([$transaction['student_id']]);
                            $p = $pStmt->fetch();
                            if (!empty($p['phone'])) $numbers[] = $p['phone'];
                            if (($cfg['send_to_guardian'] ?? true) && !empty($p['guardian_phone'])) $numbers[] = $p['guardian_phone'];

                            if (!empty($numbers)) {
                                // Calculate total due after payment
                                $dueStmt = $pdo->prepare('SELECT COALESCE(SUM(amount-COALESCE(paid_amount,0)),0) AS due FROM fees WHERE student_id=?');
                                $dueStmt->execute([$transaction['student_id']]);
                                $due = (float)($dueStmt->fetch()['due'] ?? 0);
                                $vars = [
                                    'name' => trim(($p['first_name'] ?? '').' '.($p['last_name'] ?? '')),
                                    'paid_amount' => number_format($transaction['amount'], 2),
                                    'due_amount' => number_format($due, 2),
                                ];
                                
                                // Try to use non-musking template for bank transaction, fallback to hardcoded message
                                $result = non_musking_send_template($pdo, 'bank_transaction', $vars, $numbers);
                                
                                // If template doesn't exist, use hardcoded Bengali message
                                if (!$result['success'] && strpos($result['message'], 'Template not found') !== false) {
                                    $bankMessage = "\xE0\xA6\xAB\xE0\xA7\x87\xE0\xA6\xB8 \xE0\xA6\xAA\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xA6\xE0\xA6\xBE\xE0\xA6\xA8 \xE0\xA6\x95\xE0\xA6\xB0\xE0\xA7\x87\xE0\xA6\xB0 \xE0\xA6\x86\xE0\xA6\xAA\xE0\xA6\xA8\xE0\xA6\xBE\xE0\xA6\xB0 \xE0\xA6\xAC\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xBE\xE0\xA6\x82\xE0\xA6\x95 \xE0\xA6\x9F\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBE\xE0\xA6\xA8\xE0\xA6\x9C\xE0\xA7\x87\xE0\xA6\x95\xE0\xA6\xB6\xE0\xA6\xA8 \xE0\xA7\xBB\xE0\xA6\x93" . number_format($transaction['amount'], 2) . " \xE0\xA6\x9F\xE0\xA6\xBE\xE0\xA6\x95\xE0\xA6\xBE \xE0\xA6\x97\xE0\xA7\x83\xE0\xA6\xB9\xE0\xA7\x80\xE0\xA6\xA4 \xE0\xA6\xB9\xE0\xA6\AF\xE0\xA6\xBC\xE0\xA7\x87\xE0\xA6\xB0\xE0\xA7\x87\xE0\xA6\x9B\xE0\xA7\x87\xE0\xA5\xA4 \xE0\xA6\xAC\xE0\xA6\xB0\xE0\xA7\x8D\xE0\xA6\xA4\xE0\xA6\xAE\xE0\xA6\xBE\xE0\xA6\xA8 \xE0\xA6\xAC\xE0\xA6\x95\xE0\xA7\x87\xE0\xA6\xAF\xE0\xA6\xBC\xE0\xA6\xBE: " . number_format($due, 2) . " \xE0\xA6\x9F\xE0\xA6\xBE\xE0\xA6\x95\xE0\xA6\xBE\xE0\xA5\xA4 \xE0\xA6\xA7\xE0\xA6\xA8\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xAC\xE0\xA6\xBE\xE0\xA6\xA6\xE0\xA5\xA4";
                                    non_musking_send($pdo, $numbers, $bankMessage, ['template'=>'bank_transaction']);
                                }
                            } else {
                                // Log that no phone numbers were found for the student
                                if (function_exists('sms_log')) sms_log('No phone numbers found for student ID: ' . $transaction['student_id']);
                            }
                        } else {
                            // Show why notifications were not sent
                            $reasons = [];
                            if (!$nonMuskingEnabled) {
                                $reasons[] = '\xE0\xA6\xA8\xE0\xA6\xA8-\xE0\xA6\xAE\xE0\xA6\xBE\xE0\xA6\xB8\xE0\xA7\x8D\xE0\xA6\x95\xE0\xA6\xBF\xE0\xA6\x82 \xE0\xA6\x8F\xE0\xA6\xB8\xE0\xA6\x8F\xE0\xA6\xAE\xE0\xA6\x8F\xE0\xA6\xB8 \xE0\xA6\xB8\xE0\xA6\xBE\xE0\xA6\xB0\xE0\xA7\x8D\xE0\xA6\xAD\xE0\xA6\xBF\xE0\xA6\xB8 \xE0\xA6\xA8\xE0\xA6\xBF\xE0\xA6\xB7\xE0\xA7\x8D\xE0\xA6\x95\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBF\xE0\xA6\xAF\xE0\xA6\xBC';
                            }
                            if (!$bankNotificationsEnabled) {
                                $reasons[] = '\xE0\xA6\xAC\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xBE\xE0\xA6\x82\xE0\xA6\x95 \xE0\xA6\x9F\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBE\xE0\xA6\xA8\xE0\xA6\x9C\xE0\xA7\x87\xE0\xA6\x95\xE0\xA6\xB6\xE0\xA6\xA8 \xE0\xA6\xA8\xE0\xA7\x8B\xE0\xA6\x9F\xE0\xA6\xBF\xE0\xA6\xAB\xE0\xA6\xBF\xE0\xA6\x95\xE0\xA7\x87\xE0\xA6\B6\xE0\xA6\xA8 \xE0\xA6\xA8\xE0\xA6\xBF\xE0\xA6\xB7\xE0\xA7\x8D\xE0\xA6\x95\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBF\xE0\xA6\xAF\xE0\xA6\xBC';
                            }
                            if (function_exists('sms_log')) sms_log('BANK TRANSACTION SMS not sent to student ID: ' . $transaction['student_id'] . '. Reasons: ' . implode(', ', $reasons));
                        }
                    }
                } catch (Throwable $e) {
                    if (function_exists('sms_log')) sms_log('BANK TRANSACTION SMS error: '.$e->getMessage());
                }
            } catch (Exception $e) {
                $pdo->rollBack();
                throw new Exception("Failed to update fee records: " . $e->getMessage());
            }
        } else if ($action === 'reject') {
            // Reject the transaction
            $stmt = $pdo->prepare("UPDATE bank_transactions SET status = 'rejected', verified_at = NOW(), verified_by = ?, notes = ? WHERE id = ?");
            $stmt->execute([$_SESSION['user_id'], $admin_notes, $transaction_id]);
                        
            $success_message = "Payment rejected successfully!";
                        
                $smsHelperPath = __DIR__ . '/../includes/sms_helper.php';
                if (file_exists($smsHelperPath)) {
                    require_once $smsHelperPath;
                                
                    // Check if non-musking SMS service is enabled
                    $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = 'non_musking_enabled'");
                    $stmt->execute();
                    $result = $stmt->fetch();
                    $nonMuskingEnabled = $result && $result['setting_value'] === '1';
                                
                    // Check if bank transaction notifications are enabled
                    $stmt = $pdo->prepare("SELECT setting_value FROM settings WHERE setting_key = 'sms_send_bank_transaction_notifications'");
                    $stmt->execute();
                    $result = $stmt->fetch();
                    $bankNotificationsEnabled = $result && $result['setting_value'] === '1';
                                
                    if ($nonMuskingEnabled && $bankNotificationsEnabled) {
                        $cfg = sms_config();
                        $numbers = [];
                        // Fetch student's phone numbers
                        $pStmt = $pdo->prepare('SELECT phone, guardian_phone, first_name, last_name FROM students WHERE id=?');
                        $pStmt->execute([$transaction['student_id']]);
                        $p = $pStmt->fetch();
                        if (!empty($p['phone'])) $numbers[] = $p['phone'];
                        if (($cfg['send_to_guardian'] ?? true) && !empty($p['guardian_phone'])) $numbers[] = $p['guardian_phone'];
            
                        if (!empty($numbers)) {
                            $vars = [
                                'name' => trim(($p['first_name'] ?? '').' '.($p['last_name'] ?? '')),
                                'amount' => number_format($transaction['amount'], 2),
                                'transaction_id' => $transaction['transaction_id'],
                                'notes' => $admin_notes,
                            ];
                                        
                            // Try to use non-musking template for bank transaction rejection, fallback to hardcoded message
                            $result = non_musking_send_template($pdo, 'bank_transaction_rejection', $vars, $numbers);
                                        
                            // If template doesn't exist, use hardcoded Bengali message
                            if (!$result['success'] && strpos($result['message'], 'Template not found') !== false) {
                                $rejectionMessage = "\xE0\xA6\x86\xE0\xA6\xAA\xE0\xA6\xA8\xE0\xA6\xBE\xE0\xA6\xB0 \xE0\xA6\xAC\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xBE\xE0\xA6\x82\xE0\xA6\x95 \xE0\xA6\x9F\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBE\xE0\xA6\xA8\xE0\xA6\x9C\xE0\xA7\x87\xE0\xA6\x95\xE0\xA6\xB6\xE0\xA6\xA8 \xE0\xA7\xBB\xE0\xA6\x93" . number_format($transaction['amount'], 2) . " \xE0\xA6\x9F\xE0\xA6\xBE\xE0\xA6\x95\xE0\xA6\xBE \xE0\xA6\xAA\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xA4\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xBE\xE0\xA6\x96\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xBE\xE0\xA6\A8 \xE0\xA6\xB9\xE0\xA6\oy\xE0\xA6\xBC\xE0\xA7\x87\xE0\xA6\x9B\xE0\xA7\x87\xE0\xA5\xA4 \xE0\xA6\x9F\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBE\xE0\xA6\xA8\xE0\xA6\x9C\xE0\xA7\x87\xE0\xA6\x95\xE0\xA6\xB6\xE0\xA6\xA8 \xE0\xA6\x86\xE0\xA6\xBF\xE0\xA6\x9C\xE0\xA7\x87\xE0\xA6\xB0 \xE0\xA6\x95\xE0\xA6\xBE\xE0\xA6\xB0\xE0\xA6\xA3\xE0\xA7\x87: " . $admin_notes . " \xE0\xA5\xA4 \xE0\xA6\x85\xE0\xA6\xA8\xE0\xA7\x81\xE0\xA6\x97\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xB9 \xE0\xA6\xAA\xE0\xA7\x81\xE0\xA6\xA8\xE0\xA6\xB0\xE0\xA7\x8D\xE0\xA6\x86\xE0\xA6\xAF\xE0\xA6\xBC \xE0\xA6\xAA\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xA6\xE0\xA6\xBE\xE0\xA6\xA8 \xE0\xA6\x95\xE0\xA6\xB0\xE0\xA7\x81\xE0\xA6\A8\xE0\xA5\xA4";
                                non_musking_send($pdo, $numbers, $rejectionMessage, ['template'=>'bank_transaction_rejection']);
                            }
                        } else {
                            // Log that no phone numbers were found for the student
                            if (function_exists('sms_log')) sms_log('No phone numbers found for student ID: ' . $transaction['student_id']);
                        }
                    } else {
                        // Show why notifications were not sent
                        $reasons = [];
                        if (!$nonMuskingEnabled) {
                            $reasons[] = '\xE0\xA6\xA8\xE0\xA6\xA8-\xE0\xA6\xAE\xE0\xA6\xBE\xE0\xA6\xB8\xE0\xA7\x8D\xE0\xA6\x95\xE0\xA6\xBF\xE0\xA6\x82 \xE0\xA6\x8F\xE0\xA6\xB8\xE0\xA6\x8F\xE0\xA6\xAE\xE0\xA6\x8F\xE0\xA6\xB8 \xE0\xA6\xB8\xE0\xA6\xBE\xE0\xA6\xB0\xE0\xA7\x8D\xE0\xA6\xAD\xE0\xA6\xBF\xE0\xA6\xB8 \xE0\xA6\xA8\xE0\xA6\xBF\xE0\xA6\xB7\xE0\xA7\x8D\xE0\xA6\x95\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBF\xE0\xA6\xAF\xE0\xA6\xBC';
                        }
                        if (!$bankNotificationsEnabled) {
                            $reasons[] = '\xE0\xA6\xAC\xE0\xA7\x8D\xE0\xA6\xAF\xE0\xA6\xBE\xE0\xA6\x82\xE0\xA6\x95 \xE0\xA6\x9F\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBE\xE0\xA6\xA8\xE0\xA6\x9C\xE0\xA7\x87\xE0\xA6\x95\xE0\xA6\xB6\xE0\xA6\xA8 \xE0\xA6\xA8\xE0\xA7\x8B\xE0\xA6\x9F\xE0\xA6\xBF\xE0\xA6\xAB\xE0\xA6\xBF\xE0\xA6\x95\xE0\xA7\x87\xE0\xA6\B6\xE0\xA6\xA8 \xE0\xA6\xA8\xE0\xA6\xBF\xE0\xA6\xB7\xE0\xA7\x8D\xE0\xA6\x95\xE0\xA7\x8D\xE0\xA6\xB0\xE0\xA6\xBF\xE0\xA6\xAF\xE0\xA6\xBC';
                        }
                        if (function_exists('sms_log')) sms_log('BANK TRANSACTION REJECTION SMS not sent to student ID: ' . $transaction['student_id'] . '. Reasons: ' . implode(', ', $reasons));
                    }
                }
            } catch (Throwable $e) {
                if (function_exists('sms_log')) sms_log('BANK TRANSACTION REJECTION SMS error: '.$e->getMessage());
            }
        }
        
    } catch (Exception $e) {
        $error_message = $e->getMessage();
    }
}

// Handle AJAX search requests
if (isset($_POST['ajax']) && $_POST['ajax'] == 'search_transactions') {
    header('Content-Type: application/json');
    
    $status = $_POST['status'] ?? '';
    $search_term = trim($_POST['search_term'] ?? '');
    
    if (!in_array($status, ['pending', 'verified', 'rejected'])) {
        echo json_encode(['success' => false, 'message' => 'Invalid status']);
        exit();
    }
    
    try {
        $where_clause = "bt.status = ?";
        $params = [$status];
        
        if (!empty($search_term)) {
            $where_clause .= " AND (bt.transaction_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR s.student_id LIKE ? OR ba.bank_name LIKE ? OR ba.branch_name LIKE ? OR bt.amount LIKE ?";
            if ($status !== 'pending') { // verified and rejected have notes and verified_by fields
                $where_clause .= " OR u.username LIKE ? OR bt.notes LIKE ?";
                $search_param = "%{$search_term}%";
                $params = array_merge($params, array_fill(0, 9, $search_param));
            } else { // pending status
                $search_param = "%{$search_term}%";
                $params = array_merge($params, array_fill(0, 7, $search_param));
            }
            $where_clause .= ")";
        }
        
        $sql = "SELECT bt.*, s.first_name, s.last_name, s.student_id as student_code, ba.bank_name, ba.branch_name";
        
        if ($status !== 'pending') {
            $sql .= ", u.username as verified_by_name, bt.notes";
        }
        
        $sql .= "
               FROM bank_transactions bt
               JOIN students s ON bt.student_id = s.id
               JOIN bank_accounts ba ON bt.bank_account_id = ba.id";
        
        if ($status !== 'pending') {
            $sql .= "
               LEFT JOIN users u ON bt.verified_by = u.id";
        }
        
        $sql .= "
               WHERE {$where_clause}";
        
        if ($status === 'pending') {
            $sql .= "
               ORDER BY bt.submitted_at DESC";
        } else {
            $sql .= "
               ORDER BY bt.verified_at DESC";
        }
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $transactions = $stmt->fetchAll();
        
        echo json_encode(['success' => true, 'transactions' => $transactions]);
        exit();
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        exit();
    }
}

// Handle transaction editing
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['edit_transaction'])) {
    try {
        $transaction_id = (int)($_POST['transaction_id'] ?? 0);
        $transaction_id_value = trim($_POST['edit_transaction_id'] ?? '');
        $amount = (float)($_POST['edit_amount'] ?? 0);
        $status = trim($_POST['edit_status'] ?? 'pending');
        
        if ($transaction_id <= 0 || empty($transaction_id_value) || $amount <= 0) {
            throw new Exception("All fields are required");
        }
        
        // Validate status
        if (!in_array($status, ['pending', 'verified', 'rejected'])) {
            throw new Exception("Invalid status");
        }
        
        // If changing to verified, check if this transaction ID has already been verified
        if ($status === 'verified') {
            // Get the current transaction to get its transaction_id
            $stmt = $pdo->prepare("SELECT transaction_id FROM bank_transactions WHERE id = ?");
            $stmt->execute([$transaction_id]);
            $current_transaction = $stmt->fetch();
            
            if ($current_transaction) {
                $stmt = $pdo->prepare("
                    SELECT bt.id, s.first_name, s.last_name, s.student_id as student_code
                    FROM bank_transactions bt
                    JOIN students s ON bt.student_id = s.id
                    WHERE bt.transaction_id = ? AND bt.status = 'verified' AND bt.id != ?
                ");
                $stmt->execute([$current_transaction['transaction_id'], $transaction_id]);
                $existing_verified = $stmt->fetch();
                
                if ($existing_verified) {
                    throw new Exception("This transaction ID has already been verified for student " . 
                        $existing_verified['student_code'] . " (" . 
                        $existing_verified['first_name'] . " " . 
                        $existing_verified['last_name'] . "). " .
                        "The same transaction cannot be accepted multiple times.");
                }
            }
        }
        
        // Update the transaction
        $stmt = $pdo->prepare("
            UPDATE bank_transactions 
            SET transaction_id = ?, amount = ?, status = ?
            WHERE id = ?
        ");
        $stmt->execute([$transaction_id_value, $amount, $status, $transaction_id]);
        
        $success_message = 'à¦Ÿà§à¦°à¦¾à¦¨à¦œà§‡à¦•à¦¶à¦¨ à¦¸à¦«à¦²à¦­à¦¾à¦¬à§‡ à¦†à¦ªà¦¡à§‡à¦Ÿ à¦•à¦°à¦¾ à¦¹à¦¯à¦¼à§‡à¦›à§‡';
        
    } catch (Exception $e) {
        $error_message = $e->getMessage();
    }
}

// Get search parameters
$search_pending = isset($_GET['search_pending']) ? trim($_GET['search_pending']) : '';
$search_verified = isset($_GET['search_verified']) ? trim($_GET['search_verified']) : '';
$search_rejected = isset($_GET['search_rejected']) ? trim($_GET['search_rejected']) : '';

// Get all pending bank transactions with search filters
$where_clause = "bt.status = 'pending'";
$params = [];

if (!empty($search_pending)) {
    $where_clause .= " AND (bt.transaction_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR s.student_id LIKE ? OR ba.bank_name LIKE ? OR ba.branch_name LIKE ? OR bt.amount LIKE ?)";
    $search_param = "%{$search_pending}%";
    $params = array_fill(0, 7, $search_param);
}

$sql = "SELECT bt.*, s.first_name, s.last_name, s.student_id as student_code, ba.bank_name, ba.branch_name
        FROM bank_transactions bt
        JOIN students s ON bt.student_id = s.id
        JOIN bank_accounts ba ON bt.bank_account_id = ba.id
        WHERE {$where_clause}
        ORDER BY bt.submitted_at DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$pending_transactions = $stmt->fetchAll();

// Get all verified bank transactions with search filters
$where_clause_v = "bt.status = 'verified'";
$params_v = [];

if (!empty($search_verified)) {
    $where_clause_v .= " AND (bt.transaction_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR s.student_id LIKE ? OR ba.bank_name LIKE ? OR ba.branch_name LIKE ? OR bt.amount LIKE ? OR u.username LIKE ?)";
    $search_param_v = "%{$search_verified}%";
    $params_v = array_fill(0, 8, $search_param_v);
}

$sql_v = "SELECT bt.*, s.first_name, s.last_name, s.student_id as student_code, ba.bank_name, ba.branch_name, u.username as verified_by_name, bt.notes
        FROM bank_transactions bt
        JOIN students s ON bt.student_id = s.id
        JOIN bank_accounts ba ON bt.bank_account_id = ba.id
        LEFT JOIN users u ON bt.verified_by = u.id
        WHERE {$where_clause_v}
        ORDER BY bt.verified_at DESC";

$stmt = $pdo->prepare($sql_v);
$stmt->execute($params_v);
$verified_transactions = $stmt->fetchAll();

// Get all rejected bank transactions with search filters
$where_clause_r = "bt.status = 'rejected'";
$params_r = [];

if (!empty($search_rejected)) {
    $where_clause_r .= " AND (bt.transaction_id LIKE ? OR s.first_name LIKE ? OR s.last_name LIKE ? OR s.student_id LIKE ? OR ba.bank_name LIKE ? OR ba.branch_name LIKE ? OR bt.amount LIKE ? OR u.username LIKE ? OR bt.notes LIKE ?)";
    $search_param_r = "%{$search_rejected}%";
    $params_r = array_fill(0, 9, $search_param_r);
}

$sql_r = "SELECT bt.*, s.first_name, s.last_name, s.student_id as student_code, ba.bank_name, ba.branch_name, u.username as verified_by_name, bt.notes
        FROM bank_transactions bt
        JOIN students s ON bt.student_id = s.id
        JOIN bank_accounts ba ON bt.bank_account_id = ba.id
        LEFT JOIN users u ON bt.verified_by = u.id
        WHERE {$where_clause_r}
        ORDER BY bt.verified_at DESC";

$stmt = $pdo->prepare($sql_r);
$stmt->execute($params_r);
$rejected_transactions = $stmt->fetchAll();

// Check for messages from session
if (isset($_SESSION['success_message'])) {
    $success_message = $_SESSION['success_message'];
    unset($_SESSION['success_message']);
}

if (isset($_SESSION['error_message'])) {
    $error_message = $_SESSION['error_message'];
    unset($_SESSION['error_message']);
}
?>