<?php
// Test script to verify the fix works
require_once 'config/database.php';

echo "<h2>Testing Income Report Fix</h2>";

// Test the corrected query
$start_date = date('Y-m-01') . ' 00:00:00';
$end_date = date('Y-m-t') . ' 23:59:59';

$sql = "SELECT fp.*, 
               f.fee_type as fee_title, 
               f.student_id, 
               CONCAT(COALESCE(s.first_name, ''), ' ', COALESCE(s.last_name, '')) as student_name, 
               c.name as class, 
               s.roll_number, 
               COALESCE(u.username, 'Unknown User') as collected_by_name 
        FROM fee_payments fp 
        LEFT JOIN fees f ON fp.fee_id = f.id 
        LEFT JOIN students s ON f.student_id = s.id 
        LEFT JOIN classes c ON s.class_id = c.id
        LEFT JOIN users u ON fp.created_by = u.id 
        WHERE fp.payment_date BETWEEN ? AND ?
        ORDER BY fp.payment_date DESC, fp.id DESC
        LIMIT 5";

try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$start_date, $end_date]);
    $results = $stmt->fetchAll();
    
    echo "<p style='color: green;'>✅ Query executed successfully!</p>";
    echo "<p>Found " . count($results) . " records</p>";
    
    if (!empty($results)) {
        echo "<h3>Sample Results:</h3>";
        echo "<table border='1' cellpadding='5'>";
        echo "<tr><th>ID</th><th>Amount</th><th>Date</th><th>Method</th><th>Collected By</th></tr>";
        foreach ($results as $row) {
            echo "<tr>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>৳" . number_format($row['amount'], 2) . "</td>";
            echo "<td>" . $row['payment_date'] . "</td>";
            echo "<td>" . $row['payment_method'] . "</td>";
            echo "<td>" . ($row['collected_by_name'] ?? 'N/A') . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Query failed: " . $e->getMessage() . "</p>";
    echo "<p>Error Code: " . $e->getCode() . "</p>";
}

echo "<hr>";
echo "<p><a href='fees/income_report.php' target='_blank'>Try accessing the actual income report page</a></p>";
?>