<?php
require_once 'config/database.php';

echo "Testing multiple students for payment history issues...\n";

// Get a few students with fees
$stmt = $pdo->query("SELECT s.id as student_id, s.student_id as student_code, s.user_id, s.first_name, s.last_name, COUNT(f.id) as fee_count FROM students s JOIN fees f ON s.id = f.student_id GROUP BY s.id, s.student_id, s.user_id, s.first_name, s.last_name HAVING COUNT(f.id) > 0 ORDER BY s.id LIMIT 5");
$students = $stmt->fetchAll();

echo "Found " . count($students) . " students with fees:\n\n";

foreach ($students as $student) {
    echo "Testing student: " . $student['first_name'] . " " . $student['last_name'] . " (ID: " . $student['student_code'] . ", DB ID: " . $student['student_id'] . ", User ID: " . $student['user_id'] . ")\n";
    
    // Test the payment query
    try {
        $stmt = $pdo->prepare(
            "SELECT 
                fp.id,
                fp.amount,
                fp.payment_method,
                fp.transaction_id,
                COALESCE(fp.payment_date, fp.created_at) AS pay_date,
                f.fee_type,
                f.month
             FROM fee_payments fp
             JOIN fees f ON f.id = fp.fee_id
             LEFT JOIN bkash_transactions bt ON fp.transaction_id = bt.transaction_id COLLATE utf8mb4_unicode_ci
             WHERE f.student_id = ?
             AND (bt.status != 'rejected' OR bt.status IS NULL OR fp.payment_method != 'bkash')
             ORDER BY pay_date DESC, fp.id DESC"
        );
        $stmt->execute([$student['student_id']]);
        $payments = $stmt->fetchAll();
        
        echo "  Payments with bkash join: " . count($payments) . "\n";
    } catch (Exception $e) {
        echo "  ERROR with bkash join: " . $e->getMessage() . "\n";
        
        // Try fallback
        try {
            $stmt = $pdo->prepare(
                "SELECT 
                    fp.id,
                    fp.amount,
                    fp.payment_method,
                    fp.transaction_id,
                    COALESCE(fp.payment_date, fp.created_at) AS pay_date,
                    f.fee_type,
                    f.month
                 FROM fee_payments fp
                 JOIN fees f ON f.id = fp.fee_id
                 WHERE f.student_id = ?
                 ORDER BY pay_date DESC, fp.id DESC"
            );
            $stmt->execute([$student['student_id']]);
            $payments = $stmt->fetchAll();
            
            echo "  Payments with fallback query: " . count($payments) . "\n";
        } catch (Exception $e2) {
            echo "  ERROR with fallback: " . $e2->getMessage() . "\n";
        }
    }
    
    echo "\n";
}

echo "Test completed.\n";
?>