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

$database = new Database();
$db = $database->getConnection();

// Check the actual structure first
echo "Checking candidates table:\n";
$stmt = $db->query("DESCRIBE candidates");
while($row = $stmt->fetch()) {
    echo $row['Field'] . " - " . $row['Type'] . "\n";
}

echo "\nChecking job_applications table structure:\n";
$stmt = $db->query("DESCRIBE job_applications");
while($row = $stmt->fetch()) {
    echo $row['Field'] . " - " . $row['Type'] . "\n";
}

echo "\nChecking Business Development applications and their documents:\n";
// Get application data with resume info
$stmt = $db->query("SELECT ja.id, ja.candidate_id, ja.resume_id, c.first_name, c.last_name, j.title,
                           r.file_path as resume_path, c.profile_picture
                    FROM job_applications ja 
                    JOIN candidates c ON ja.candidate_id = c.id 
                    JOIN jobs j ON ja.job_id = j.id
                    LEFT JOIN resumes r ON ja.resume_id = r.id
                    WHERE c.first_name IN ('Rammy', 'Sisanda') 
                    AND j.title LIKE '%Business Development%'");
while($row = $stmt->fetch()) {
    echo "App ID: " . $row['id'] . " - " . $row['first_name'] . " " . $row['last_name'] . "\n";
    echo "  Resume ID: " . ($row['resume_id'] ?? 'NONE') . " - Path: " . ($row['resume_path'] ?? 'NONE') . "\n";
    echo "  Profile: " . ($row['profile_picture'] ?? 'NONE') . "\n";
    
    // Check file existence
    if ($row['resume_path']) {
        $resume_full_path = UPLOAD_PATH . 'resumes/' . $row['resume_path'];
        echo "  Resume exists: " . (file_exists($resume_full_path) ? 'YES' : 'NO') . " at " . $resume_full_path . "\n";
    }
    if ($row['profile_picture']) {
        $profile_full_path = UPLOAD_PATH . 'profiles/' . $row['profile_picture'];
        echo "  Profile exists: " . (file_exists($profile_full_path) ? 'YES' : 'NO') . " at " . $profile_full_path . "\n";
    }
    echo "\n";
}

echo "\nChecking resumes table:\n";
$stmt = $db->query("DESCRIBE resumes");
while($row = $stmt->fetch()) {
    echo $row['Field'] . " - " . $row['Type'] . "\n";
}

echo "\nChecking specific resume records:\n";
$stmt = $db->query("SELECT * FROM resumes WHERE id IN (3)");
while($row = $stmt->fetch()) {
    echo "Resume ID: " . $row['id'] . " - File Path: " . ($row['file_path'] ?? 'NONE') . "\n";
    print_r($row);
}
?>