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

try {
    // Check table structure
    $stmt = $pdo->query("DESCRIBE expenses");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "Expenses table columns:\n";
    foreach ($columns as $column) {
        echo "- " . $column['Field'] . " (" . $column['Type'] . ")\n";
    }
    
    // Check if created_by column exists
    $has_created_by = false;
    foreach ($columns as $column) {
        if ($column['Field'] === 'created_by') {
            $has_created_by = true;
            break;
        }
    }
    
    if (!$has_created_by) {
        echo "\nAdding missing 'created_by' column...\n";
        $pdo->exec("ALTER TABLE expenses ADD COLUMN created_by INT NULL AFTER receipt_no");
        echo "Column 'created_by' added successfully!\n";
    } else {
        echo "\n'created_by' column already exists.\n";
    }
    
    // Test the original query
    echo "\nTesting query...\n";
    $stmt = $pdo->query("
        SELECT e.*, ec.name as category_name, u.username as created_by_name 
        FROM expenses e 
        LEFT JOIN expense_categories ec ON e.category_id = ec.id 
        LEFT JOIN users u ON e.created_by = u.id 
        ORDER BY e.expense_date DESC, e.id DESC
        LIMIT 5
    ");
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "Query executed successfully! Found " . count($results) . " records.\n";
    if (!empty($results)) {
        echo "Sample data:\n";
        foreach ($results as $row) {
            echo "- " . $row['title'] . " (" . ($row['category_name'] ?? 'No Category') . ")\n";
        }
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>