<?php
// Comprehensive test for bulk download with all attachments
require_once 'config/config.php';

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

echo "<style>
    body { font-family: Arial, sans-serif; margin: 20px; background: #f8f9fa; }
    .container { max-width: 1200px; background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
    table { width: 100%; border-collapse: collapse; margin: 10px 0; }
    th, td { padding: 8px; border: 1px solid #dee2e6; text-align: left; }
    th { background: #f8f9fa; font-weight: 600; }
    .btn { padding: 8px 15px; background: #007bff; color: white; text-decoration: none; border-radius: 4px; margin: 5px; }
    .btn:hover { background: #0056b3; }
    .success { color: #28a745; } .warning { color: #ffc107; } .danger { color: #dc3545; }
</style>";

echo "<div class='container'>";
echo "<h1>Comprehensive Bulk Download Test</h1>";

// Test 1: Check all attachment tables
echo "<h2>1. Database Tables Check</h2>";
$attachment_tables = [
    'resumes' => 'Main resumes table',
    'certificates' => 'Certificates table',  
    'candidate_certificates' => 'Alternative certificates table',
    'candidate_documents' => 'General documents table'
];

echo "<table>";
echo "<tr><th>Table</th><th>Exists</th><th>Record Count</th><th>Sample Fields</th></tr>";

foreach ($attachment_tables as $table => $description) {
    try {
        $check_stmt = $db->query("SHOW TABLES LIKE '{$table}'");
        $exists = $check_stmt->rowCount() > 0;
        
        if ($exists) {
            $count_stmt = $db->query("SELECT COUNT(*) as count FROM {$table}");
            $count = $count_stmt->fetch()['count'];
            
            $desc_stmt = $db->query("DESCRIBE {$table}");
            $columns = $desc_stmt->fetchAll(PDO::FETCH_COLUMN);
            $sample_fields = implode(', ', array_slice($columns, 0, 4));
            
            echo "<tr><td>{$table}</td><td class='success'>✓ Yes</td><td>{$count}</td><td>{$sample_fields}</td></tr>";
        } else {
            echo "<tr><td>{$table}</td><td class='danger'>✗ No</td><td>-</td><td>-</td></tr>";
        }
    } catch (PDOException $e) {
        echo "<tr><td>{$table}</td><td class='danger'>Error</td><td>-</td><td>{$e->getMessage()}</td></tr>";
    }
}
echo "</table>";

// Test 2: Check upload directories
echo "<h2>2. Upload Directories Check</h2>";
$upload_dirs = [
    'resumes' => UPLOAD_PATH . 'resumes/',
    'profiles' => UPLOAD_PATH . 'profiles/',
    'certificates' => UPLOAD_PATH . 'certificates/',
    'documents' => UPLOAD_PATH . 'documents/'
];

echo "<table>";
echo "<tr><th>Directory</th><th>Path</th><th>Exists</th><th>File Count</th><th>Sample Files</th></tr>";

foreach ($upload_dirs as $type => $dir) {
    $exists = file_exists($dir);
    $file_count = 0;
    $sample_files = '-';
    
    if ($exists) {
        $files = glob($dir . '*');
        $file_count = count($files);
        if ($file_count > 0) {
            $sample_names = array_map('basename', array_slice($files, 0, 3));
            $sample_files = implode(', ', $sample_names);
            if ($file_count > 3) $sample_files .= '...';
        }
    }
    
    $status = $exists ? 'success' : 'danger';
    $icon = $exists ? '✓' : '✗';
    
    echo "<tr><td>{$type}</td><td>{$dir}</td><td class='{$status}'>{$icon}</td><td>{$file_count}</td><td>{$sample_files}</td></tr>";
}
echo "</table>";

// Test 3: Test attachment queries
echo "<h2>3. Attachment Query Tests</h2>";

// Get some candidates for testing
$stmt = $db->prepare("SELECT id, first_name, last_name FROM candidates LIMIT 5");
$stmt->execute();
$test_candidates = $stmt->fetchAll();

if (!empty($test_candidates)) {
    $candidate_ids = array_column($test_candidates, 'id');
    $candidate_placeholders = str_repeat('?,', count($candidate_ids) - 1) . '?';
    
    echo "<h3>Testing attachment queries for candidates: " . implode(', ', $candidate_ids) . "</h3>";
    
    $attachment_queries = [
        'Certificates (certificates)' => "SELECT candidate_id, title as name, file_path as path, issue_date as date, 'certificate' as type FROM certificates WHERE candidate_id IN ($candidate_placeholders)",
        'Certificates (candidate_certificates)' => "SELECT candidate_id, certificate_name as name, file_path as path, issue_date as date, 'certificate' as type FROM candidate_certificates WHERE candidate_id IN ($candidate_placeholders)",
        'Documents (candidate_documents)' => "SELECT candidate_id, title as name, file_path as path, document_type as type, created_at as date FROM candidate_documents WHERE candidate_id IN ($candidate_placeholders)",
        'Additional Resumes' => "SELECT candidate_id, title as name, file_path as path, created_at as date, 'resume' as type FROM resumes WHERE candidate_id IN ($candidate_placeholders) AND file_path IS NOT NULL"
    ];
    
    echo "<table>";
    echo "<tr><th>Query Type</th><th>Status</th><th>Records Found</th><th>Sample Data</th></tr>";
    
    foreach ($attachment_queries as $query_name => $query) {
        try {
            $stmt = $db->prepare($query);
            $stmt->execute($candidate_ids);
            $results = $stmt->fetchAll();
            
            $count = count($results);
            $sample_data = '';
            
            if ($count > 0) {
                $first_result = $results[0];
                $sample_data = "Candidate {$first_result['candidate_id']}: {$first_result['name']} ({$first_result['type']})";
            }
            
            echo "<tr><td>{$query_name}</td><td class='success'>✓ Works</td><td>{$count}</td><td>{$sample_data}</td></tr>";
        } catch (PDOException $e) {
            echo "<tr><td>{$query_name}</td><td class='danger'>✗ Error</td><td>0</td><td>{$e->getMessage()}</td></tr>";
        }
    }
    echo "</table>";
} else {
    echo "<p class='warning'>No candidates found to test attachment queries.</p>";
}

// Test 4: Test application with attachments
echo "<h2>4. Applications with Attachments Test</h2>";

$app_stmt = $db->prepare("
    SELECT ja.id, ja.candidate_id, 
           CONCAT(c.first_name, ' ', c.last_name) as candidate_name,
           j.title as job_title
    FROM job_applications ja
    JOIN jobs j ON ja.job_id = j.id  
    JOIN candidates c ON ja.candidate_id = c.id
    LIMIT 5
");
$app_stmt->execute();
$test_applications = $app_stmt->fetchAll();

if (!empty($test_applications)) {
    echo "<table>";
    echo "<tr><th>App ID</th><th>Candidate</th><th>Job</th><th>Potential Attachments</th></tr>";
    
    foreach ($test_applications as $app) {
        $attachment_count = 0;
        $attachment_types = [];
        
        // Check for attachments
        foreach ($attachment_queries as $query_name => $query) {
            try {
                $stmt = $db->prepare(str_replace($candidate_placeholders, '?', $query));
                $stmt->execute([$app['candidate_id']]);
                $results = $stmt->fetchAll();
                
                if (count($results) > 0) {
                    $attachment_count += count($results);
                    $attachment_types[] = str_replace(['Certificates (', ')', 'candidate_'], '', $query_name) . ': ' . count($results);
                }
            } catch (PDOException $e) {
                // Ignore errors for this test
            }
        }
        
        $attachment_info = !empty($attachment_types) ? implode(', ', $attachment_types) : 'None found';
        
        echo "<tr><td>#{$app['id']}</td><td>{$app['candidate_name']}</td><td>{$app['job_title']}</td><td>{$attachment_info}</td></tr>";
    }
    echo "</table>";
} else {
    echo "<p class='warning'>No applications found to test.</p>";
}

// Test 5: ZIP functionality
echo "<h2>5. ZIP Creation Test</h2>";
if (class_exists('ZipArchive')) {
    echo "<p class='success'>✓ ZipArchive class available</p>";
    
    $temp_dir = sys_get_temp_dir();
    if (is_writable($temp_dir)) {
        echo "<p class='success'>✓ Temporary directory writable: {$temp_dir}</p>";
    } else {
        echo "<p class='danger'>✗ Temporary directory not writable: {$temp_dir}</p>";
    }
} else {
    echo "<p class='danger'>✗ ZipArchive class not available</p>";
}

// Summary and recommendations
echo "<h2>6. Summary & Recommendations</h2>";
echo "<div style='background: #e7f3ff; padding: 15px; border-radius: 5px; border-left: 4px solid #007bff;'>";
echo "<h4>System Status:</h4>";
echo "<ul>";
echo "<li><strong>Core Functionality:</strong> The bulk download system can now handle multiple attachment types</li>";
echo "<li><strong>Supported Types:</strong> Resumes, Certificates, Documents, Cover Letters, Transcripts, Others</li>";
echo "<li><strong>File Organization:</strong> Each application gets organized subfolders by attachment type</li>";
echo "<li><strong>Backward Compatibility:</strong> Still works with basic resume and certificate systems</li>";
echo "</ul>";

echo "<h4>Ready to Test:</h4>";
echo "<p><a href='company/applications.php' class='btn'>🔗 Test Bulk Download</a>";
echo "<a href='bulk_download_info.html' class='btn' style='background: #28a745;'>📖 View Documentation</a></p>";
echo "</div>";

echo "</div>";
?>