<?php
// Comprehensive debugging for income_report.php
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h1>Income Report Debug - Step by Step</h1>";

// Step 1: Test session
echo "<h2>Step 1: Session Test</h2>";
session_start();
if (isset($_SESSION['user_id'])) {
    echo "<p>✅ Session OK - User ID: " . $_SESSION['user_id'] . "</p>";
} else {
    echo "<p>⚠️ No session - This is normal for testing</p>";
}

// Step 2: Test database connection
echo "<h2>Step 2: Database Connection</h2>";
try {
    require_once 'config/database.php';
    echo "<p>✅ Database connection successful</p>";
} catch (Exception $e) {
    echo "<p>❌ Database connection failed: " . $e->getMessage() . "</p>";
    exit;
}

// Step 3: Test table existence
echo "<h2>Step 3: Required Tables</h2>";
$tables_needed = ['fee_payments', 'fees', 'students', 'users'];
foreach ($tables_needed as $table) {
    try {
        $stmt = $pdo->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table]);
        $result = $stmt->fetchAll();
        if (count($result) > 0) {
            echo "<p>✅ Table '$table' exists</p>";
        } else {
            echo "<p>❌ Table '$table' NOT FOUND</p>";
        }
    } catch (Exception $e) {
        echo "<p>❌ Error checking table '$table': " . $e->getMessage() . "</p>";
    }
}

// Step 4: Test basic queries
echo "<h2>Step 4: Query Tests</h2>";

// Test 4a: Simple fee_payments query
echo "<h3>4a: Simple fee_payments query</h3>";
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM fee_payments");
    $result = $stmt->fetch();
    echo "<p>✅ fee_payments count: " . $result['count'] . "</p>";
} catch (Exception $e) {
    echo "<p>❌ fee_payments query failed: " . $e->getMessage() . "</p>";
}

// Test 4b: Simple fees query
echo "<h3>4b: Simple fees query</h3>";
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM fees");
    $result = $stmt->fetch();
    echo "<p>✅ fees count: " . $result['count'] . "</p>";
} catch (Exception $e) {
    echo "<p>❌ fees query failed: " . $e->getMessage() . "</p>";
}

// Test 4c: Simple students query
echo "<h3>4c: Simple students query</h3>";
try {
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM students LIMIT 1");
    $result = $stmt->fetch();
    echo "<p>✅ students table accessible</p>";
} catch (Exception $e) {
    echo "<p>❌ students query failed: " . $e->getMessage() . "</p>";
}

// Test 4d: Complex join query (the actual report query)
echo "<h3>4d: Complex join query (report query)</h3>";
try {
    $start_date = date('Y-m-01');
    $end_date = date('Y-m-t');
    
    $sql = "SELECT fp.*, f.title 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.collected_by = u.id 
            WHERE fp.payment_date BETWEEN ? AND ?
            ORDER BY fp.payment_date DESC, fp.id DESC
            LIMIT 3";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$start_date, $end_date]);
    $results = $stmt->fetchAll();
    
    echo "<p>✅ Complex query executed successfully</p>";
    echo "<p>Found " . count($results) . " records</p>";
    
    if (!empty($results)) {
        echo "<h4>Sample record structure:</h4>";
        echo "<pre>";
        print_r(array_keys($results[0]));
        echo "</pre>";
    }
    
} catch (Exception $e) {
    echo "<p>❌ Complex query failed: " . $e->getMessage() . "</p>";
    echo "<p>SQL: " . $sql . "</p>";
}

// Step 5: Test file includes
echo "<h2>Step 5: File Include Tests</h2>";
$files_to_test = [
    '../config/database.php',
    '../includes/sidebar_layout.php'
];

foreach ($files_to_test as $file) {
    if (file_exists($file)) {
        echo "<p>✅ File exists: $file</p>";
    } else {
        echo "<p>❌ File missing: $file</p>";
    }
}

// Step 6: Test the actual page components
echo "<h2>Step 6: Simulate Page Components</h2>";

// Test variable assignments
echo "<h3>6a: Variable assignments</h3>";
try {
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');
    $payment_method = $_GET['payment_method'] ?? '';
    
    echo "<p>✅ Variables assigned successfully</p>";
    echo "<p>Start date: $start_date</p>";
    echo "<p>End date: $end_date</p>";
    echo "<p>Payment method: '$payment_method'</p>";
} catch (Exception $e) {
    echo "<p>❌ Variable assignment failed: " . $e->getMessage() . "</p>";
}

// Test data processing
echo "<h3>6b: Data processing</h3>";
try {
    // Get the actual data
    $sql = "SELECT fp.*, f.title 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 ?";
    
    if ($payment_method) {
        $sql .= " AND fp.payment_method = ?";
        $params = [$start_date, $end_date, $payment_method];
    } else {
        $params = [$start_date, $end_date];
    }
    
    $sql .= " ORDER BY fp.payment_date DESC, fp.id DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $income_records = $stmt->fetchAll();
    
    // Calculate totals
    $total_income = 0;
    $payment_method_totals = [];
    foreach ($income_records as $record) {
        $total_income += $record['amount'];
        if (!isset($payment_method_totals[$record['payment_method']])) {
            $payment_method_totals[$record['payment_method']] = 0;
        }
        $payment_method_totals[$record['payment_method']] += $record['amount'];
    }
    
    echo "<p>✅ Data processing successful</p>";
    echo "<p>Total records: " . count($income_records) . "</p>";
    echo "<p>Total income: " . number_format($total_income, 2) . "</p>";
    echo "<p>Payment methods: " . count($payment_method_totals) . "</p>";
    
} catch (Exception $e) {
    echo "<p>❌ Data processing failed: " . $e->getMessage() . "</p>";
}

echo "<h2>Debug Complete</h2>";
echo "<p><a href='fees/income_report.php'>Try accessing the actual page</a></p>";
?>