<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h2>Debug Query Script</h2>";
echo "Current File: " . __FILE__ . "\n";
echo "Current Dir: " . __DIR__ . "\n";

include 'includes/db.php';

echo "Connected to DB: " . $dbname . "\n";
echo "Host: " . $servername . "\n";
echo "User: " . $username . "\n";

// Check table existence
$tables = [];
$res = $conn->query("SHOW TABLES");
if ($res) {
    while ($row = $res->fetch_array()) {
        $tables[] = $row[0];
    }
    echo "Tables: " . implode(", ", $tables) . "\n";
} else {
    echo "Error listing tables: " . $conn->error . "\n";
}

// Check applications columns
echo "\nColumns in 'applications':\n";
$res = $conn->query("SHOW COLUMNS FROM applications");
if ($res) {
    while ($row = $res->fetch_assoc()) {
        echo $row['Field'] . "\n";
    }
} else {
    echo "Error describing applications: " . $conn->error . "\n";
}

// Try the exact failing query (mocking user_id)
$user_id = 1; // Assuming user_id 1 exists or doesn't matter for syntax check
echo "\nTesting Query:\n";
$sql = "SELECT a.id, p.program_name, a.status, a.submission_date, a.payment_status 
        FROM applications a 
        JOIN programs p ON a.program_id = p.id 
        WHERE a.user_id = $user_id ORDER BY a.id DESC";

echo "SQL: $sql\n";

try {
    $result = $conn->query($sql);
    if ($result) {
        echo "Query Successful! Rows: " . $result->num_rows . "\n";
    } else {
        echo "Query Failed: " . $conn->error . "\n";
    }
} catch (Exception $e) {
    echo "Exception: " . $e->getMessage() . "\n";
}
?>
