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

echo "<h2>Income Report Diagnostic</h2>";

// Set session for testing
$_SESSION['user_id'] = 1;

// Test 1: Database connection
echo "<h3>1. Database Connection</h3>";
try {
    $pdo->query("SELECT 1");
    echo "<p style='color: green;'>✅ Database connected successfully</p>";
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Database connection failed: " . $e->getMessage() . "</p>";
    exit;
}

// Test 2: Required tables
echo "<h3>2. Required Tables</h3>";
$tables_needed = ['fee_payments', 'fees', 'students', 'users'];
foreach ($tables_needed as $table) {
    try {
        $stmt = $pdo->query("SHOW TABLES LIKE '$table'");
        $result = $stmt->fetchAll();
        if (count($result) > 0) {
            echo "<p style='color: green;'>✅ Table '$table' exists</p>";
        } else {
            echo "<p style='color: red;'>❌ Table '$table' does not exist</p>";
        }
    } catch (Exception $e) {
        echo "<p style='color: red;'>❌ Error checking table '$table': " . $e->getMessage() . "</p>";
    }
}

// Test 3: Table structures
echo "<h3>3. Table Structures</h3>";

// Check fee_payments structure
echo "<h4>fee_payments table:</h4>";
try {
    $stmt = $pdo->query("DESCRIBE fee_payments");
    $columns = $stmt->fetchAll();
    echo "<ul>";
    foreach ($columns as $col) {
        echo "<li>{$col['Field']} ({$col['Type']}) " . ($col['Null'] === 'YES' ? 'NULL' : 'NOT NULL') . "</li>";
    }
    echo "</ul>";
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}

// Check fees structure
echo "<h4>fees table:</h4>";
try {
    $stmt = $pdo->query("DESCRIBE fees");
    $columns = $stmt->fetchAll();
    echo "<ul>";
    foreach ($columns as $col) {
        echo "<li>{$col['Field']} ({$col['Type']}) " . ($col['Null'] === 'YES' ? 'NULL' : 'NOT NULL') . "</li>";
    }
    echo "</ul>";
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}

// Test 4: Execute the exact query from income_report.php
echo "<h3>4. Main Query Test</h3>";
try {
    $start_date = date('Y-m-01');
    $end_date = date('Y-m-t');
    
    echo "<p>Testing date range: $start_date to $end_date</p>";
    
    $sql = "SELECT fp.*, f.fee_type as fee_title, f.student_id, s.student_name, s.class, s.roll_number, u.username 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 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";
    
    echo "<p>SQL Query: " . htmlspecialchars($sql) . "</p>";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$start_date, $end_date]);
    $results = $stmt->fetchAll();
    
    echo "<p style='color: green;'>✅ Query executed successfully</p>";
    echo "<p>Records found: " . count($results) . "</p>";
    
    if (!empty($results)) {
        echo "<h4>Sample Record:</h4>";
        echo "<pre>";
        print_r($results[0]);
        echo "</pre>";
    } else {
        echo "<p>No records found for current month</p>";
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Query failed: " . $e->getMessage() . "</p>";
    echo "<p>SQL State: " . $e->getCode() . "</p>";
}

// Test 5: Check payment methods
echo "<h3>5. Payment Methods Analysis</h3>";
try {
    $stmt = $pdo->query("SELECT DISTINCT payment_method FROM fee_payments");
    $methods = $stmt->fetchAll();
    echo "<p>Available payment methods:</p>";
    echo "<ul>";
    foreach ($methods as $method) {
        echo "<li>'" . $method['payment_method'] . "'</li>";
    }
    echo "</ul>";
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}

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