<?php
// Test script to verify bulk download fix
require_once 'config/config.php';

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

echo "<h2>Bulk Download Fix Test</h2>";

// Test 1: Check if candidate_certificates table exists
echo "<h3>Test 1: Check candidate_certificates table</h3>";
try {
    $check_table = $db->query("SHOW TABLES LIKE 'candidate_certificates'");
    $table_exists = $check_table->rowCount() > 0;
    
    if ($table_exists) {
        echo "✅ candidate_certificates table EXISTS<br>";
        
        // Count certificates
        $count_stmt = $db->query("SELECT COUNT(*) as count FROM candidate_certificates");
        $count = $count_stmt->fetch()['count'];
        echo "📊 Total certificates: {$count}<br>";
        
    } else {
        echo "❌ candidate_certificates table DOES NOT EXIST<br>";
        echo "🔧 Bulk download will work WITHOUT certificates<br>";
    }
} catch (PDOException $e) {
    echo "❌ Error checking table: " . $e->getMessage() . "<br>";
}

// Test 2: Check which columns exist in candidates table
echo "<h3>Test 2: Check candidates table columns</h3>";
try {
    $desc_stmt = $db->query("DESCRIBE candidates");
    $existing_columns = $desc_stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $columns_to_check = [
        'first_name', 'last_name', 'email', 'phone', 'address', 'profile_picture',
        'linkedin_url', 'portfolio_url', 'skills', 'experience_years'
    ];
    
    echo "<table border='1' style='border-collapse: collapse;'>";
    echo "<tr><th>Column</th><th>Exists</th></tr>";
    
    $available_columns = [];
    foreach ($columns_to_check as $col) {
        $exists = in_array($col, $existing_columns);
        if ($exists) $available_columns[] = "c.{$col}";
        $status = $exists ? '✅ Yes' : '❌ No';
        echo "<tr><td>{$col}</td><td>{$status}</td></tr>";
    }
    echo "</table>";
    
    echo "<p>Available candidate columns: " . implode(', ', $available_columns) . "</p>";
    
} catch (PDOException $e) {
    echo "❌ Error checking columns: " . $e->getMessage() . "<br>";
}

// Test 3: Check if we can get basic application data with dynamic columns
echo "<h3>Test 3: Check dynamic application query</h3>";
try {
    // Use the same logic as the fixed bulk_download.php
    $candidate_columns = [];
    $columns_to_check = [
        'first_name', 'last_name', 'email', 'phone', 'address', 'profile_picture',
        'linkedin_url', 'portfolio_url', 'skills', 'experience_years'
    ];

    $desc_stmt = $db->query("DESCRIBE candidates");
    $existing_columns = $desc_stmt->fetchAll(PDO::FETCH_COLUMN);
    
    foreach ($columns_to_check as $col) {
        if (in_array($col, $existing_columns)) {
            $candidate_columns[] = "c.{$col}";
        }
    }
    
    if (empty($candidate_columns)) {
        $candidate_columns = ['c.first_name', 'c.last_name', 'c.email'];
    }

    $candidate_select = implode(', ', $candidate_columns);
    
    $stmt = $db->prepare("
        SELECT ja.id, ja.candidate_id, ja.resume_id, ja.cover_letter, ja.applied_at, ja.status,
               COALESCE(ja.total_score, 0) as total_score,
               j.title as job_title, j.description as job_description,
               {$candidate_select},
               r.title as resume_title, r.file_path as resume_path
        FROM job_applications ja
        JOIN jobs j ON ja.job_id = j.id
        JOIN candidates c ON ja.candidate_id = c.id
        LEFT JOIN resumes r ON ja.resume_id = r.id
        LIMIT 3
    ");
    $stmt->execute();
    $test_applications = $stmt->fetchAll();
    
    echo "✅ Basic application query WORKS<br>";
    echo "📊 Found " . count($test_applications) . " test applications<br>";
    
    if (!empty($test_applications)) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>ID</th><th>Candidate</th><th>Job</th><th>Resume</th></tr>";
        foreach (array_slice($test_applications, 0, 3) as $app) {
            echo "<tr>";
            echo "<td>#{$app['id']}</td>";
            echo "<td>{$app['first_name']} {$app['last_name']}</td>";
            echo "<td>{$app['job_title']}</td>";
            echo "<td>" . ($app['resume_title'] ? $app['resume_title'] : 'No resume') . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
} catch (PDOException $e) {
    echo "❌ Error with dynamic query: " . $e->getMessage() . "<br>";
}

// Test 4: Check upload directories
echo "<h3>Test 4: Check upload directories</h3>";
$upload_dirs = [
    'resumes' => UPLOAD_PATH . 'resumes/',
    'profiles' => UPLOAD_PATH . 'profiles/',
    'certificates' => UPLOAD_PATH . 'certificates/'
];

foreach ($upload_dirs as $type => $dir) {
    if (file_exists($dir)) {
        echo "✅ {$type} directory exists: {$dir}<br>";
        $files = glob($dir . '*');
        echo "📁 Contains " . count($files) . " files<br>";
    } else {
        echo "❌ {$type} directory missing: {$dir}<br>";
    }
}

// Test 5: Test ZIP functionality
echo "<h3>Test 5: Check ZIP functionality</h3>";
if (class_exists('ZipArchive')) {
    echo "✅ ZipArchive class is available<br>";
    
    $temp_dir = sys_get_temp_dir();
    echo "📁 Temp directory: {$temp_dir}<br>";
    
    if (is_writable($temp_dir)) {
        echo "✅ Temp directory is writable<br>";
    } else {
        echo "❌ Temp directory is NOT writable<br>";
    }
} else {
    echo "❌ ZipArchive class is NOT available<br>";
}

echo "<h3>Summary</h3>";
echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px;'>";
echo "✅ The bulk download script has been fixed to handle missing candidate_certificates table<br>";
echo "🔧 Applications can be downloaded without certificates<br>";
echo "📦 ZIP functionality should work for resumes and profile photos<br>";
echo "🎯 Ready to test bulk download feature<br>";
echo "</div>";

echo "<p><a href='company/applications.php'>→ Test Bulk Download on Applications Page</a></p>";
?>