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

try {
    // Test the specific query used in student_fees.php with actual data
    $where_conditions = ["s.status = 'active'"];
    $params = [];
    
    // Use actual data from the database
    $class_id = 6; // Six class
    $session = '2025-2026'; // Actual session
    
    if (!empty($class_id)) {
        $where_conditions[] = "s.class_id = ?";
        $params[] = $class_id;
    }
    
    if (!empty($session)) {
        $where_conditions[] = "s.session = ?";
        $params[] = $session;
    }
    
    $where_clause = implode(' AND ', $where_conditions);
    
    $sql = "
        SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name, s.scholarship, c.name as class_name, c.section as class_section,
               COALESCE(SUM(f.amount - f.paid_amount), 0) as total_due
        FROM students s
        JOIN classes c ON s.class_id = c.id
        LEFT JOIN fees f ON s.id = f.student_id
        WHERE $where_clause
        GROUP BY s.id, s.student_id, s.first_name, s.last_name, s.roll_number, s.session, s.group_name, s.scholarship, c.name, c.section
        ORDER BY s.roll_number
    ";
    
    echo "Executing query: " . $sql . "\n";
    echo "With parameters: " . json_encode($params) . "\n";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $students = $stmt->fetchAll();
    
    echo "Query executed successfully!\n";
    echo "Number of students found: " . count($students) . "\n";
    
    if (count($students) > 0) {
        echo "First student data:\n";
        print_r($students[0]);
    } else {
        echo "No students found with these criteria.\n";
    }
    
} catch (Exception $e) {
    echo "Query execution error: " . $e->getMessage() . "\n";
    echo "Error code: " . $e->getCode() . "\n";
}
?>