<?php
// Verification script to check assigned profile pictures and facts
include '../includes/db.php';

try {
    echo "<h2>Profile Pictures and Facts Assignment - Verification</h2>\n";
    
    // Get overall statistics
    $stats_query = "
        SELECT 
            COUNT(*) as total_participants,
            COUNT(photo) as participants_with_photos,
            COUNT(motion_video) as participants_with_videos,
            (SELECT COUNT(*) FROM participant_facts) as total_facts,
            (SELECT COUNT(DISTINCT participant_id) FROM participant_facts) as participants_with_facts
        FROM participants
    ";
    $stats = $pdo->query($stats_query)->fetch(PDO::FETCH_ASSOC);
    
    echo "<div style='background: #e8f5e8; padding: 15px; border-radius: 8px; margin-bottom: 20px;'>\n";
    echo "<h3>📊 Database Statistics</h3>\n";
    echo "<ul>\n";
    echo "<li><strong>Total participants:</strong> {$stats['total_participants']}</li>\n";
    echo "<li><strong>Participants with photos:</strong> {$stats['participants_with_photos']}</li>\n";
    echo "<li><strong>Participants with videos:</strong> {$stats['participants_with_videos']}</li>\n";
    echo "<li><strong>Total facts:</strong> {$stats['total_facts']}</li>\n";
    echo "<li><strong>Participants with facts:</strong> {$stats['participants_with_facts']}</li>\n";
    echo "</ul>\n";
    echo "</div>\n";
    
    // Show breakdown by gender
    $gender_stats = $pdo->query("
        SELECT 
            gender,
            COUNT(*) as count,
            COUNT(photo) as with_photos
        FROM participants 
        GROUP BY gender
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<h3>👥 Gender Distribution</h3>\n";
    echo "<table border='1' style='border-collapse: collapse; margin-bottom: 20px;'>\n";
    echo "<tr><th>Gender</th><th>Total</th><th>With Photos</th><th>Photo Coverage</th></tr>\n";
    foreach ($gender_stats as $stat) {
        $coverage = $stat['count'] > 0 ? round(($stat['with_photos'] / $stat['count']) * 100, 1) : 0;
        echo "<tr><td>{$stat['gender']}</td><td>{$stat['count']}</td><td>{$stat['with_photos']}</td><td>{$coverage}%</td></tr>\n";
    }
    echo "</table>\n";
    
    // Show sample participants with their data
    $sample_query = "
        SELECT 
            p.id,
            p.first_name,
            p.last_name,
            p.gender,
            p.photo,
            p.motion_video,
            COUNT(pf.id) as facts_count
        FROM participants p
        LEFT JOIN participant_facts pf ON p.id = pf.participant_id
        GROUP BY p.id
        ORDER BY RAND()
        LIMIT 10
    ";
    $samples = $pdo->query($sample_query)->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<h3>🎯 Sample Participants</h3>\n";
    foreach ($samples as $participant) {
        echo "<div style='border: 1px solid #ddd; padding: 10px; margin-bottom: 10px; border-radius: 5px;'>\n";
        echo "<h4>{$participant['first_name']} {$participant['last_name']} (ID: {$participant['id']})</h4>\n";
        echo "<ul>\n";
        echo "<li><strong>Gender:</strong> {$participant['gender']}</li>\n";
        echo "<li><strong>Photo:</strong> " . ($participant['photo'] ? "✓ {$participant['photo']}" : "✗ No photo") . "</li>\n";
        echo "<li><strong>Video:</strong> " . ($participant['motion_video'] ? "✓ {$participant['motion_video']}" : "✗ No video") . "</li>\n";
        echo "<li><strong>Facts:</strong> {$participant['facts_count']} facts</li>\n";
        echo "</ul>\n";
        
        // Show facts for this participant
        if ($participant['facts_count'] > 0) {
            $facts_query = $pdo->prepare("SELECT title, fact FROM participant_facts WHERE participant_id = ? ORDER BY sort_order");
            $facts_query->execute([$participant['id']]);
            $facts = $facts_query->fetchAll(PDO::FETCH_ASSOC);
            
            echo "<div style='background: #f8f9fa; padding: 8px; margin-top: 8px; border-radius: 4px;'>\n";
            echo "<strong>📝 Facts:</strong><br>\n";
            foreach ($facts as $fact) {
                echo "• <em>{$fact['title']}:</em> {$fact['fact']}<br>\n";
            }
            echo "</div>\n";
        }
        
        echo "</div>\n";
    }
    
    // Show most common fact types
    $fact_types = $pdo->query("
        SELECT 
            title,
            COUNT(*) as count
        FROM participant_facts 
        GROUP BY title
        ORDER BY count DESC
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<h3>📈 Most Common Fact Types</h3>\n";
    echo "<table border='1' style='border-collapse: collapse; margin-bottom: 20px;'>\n";
    echo "<tr><th>Fact Type</th><th>Count</th></tr>\n";
    foreach ($fact_types as $type) {
        echo "<tr><td>{$type['title']}</td><td>{$type['count']}</td></tr>\n";
    }
    echo "</table>\n";
    
    // File verification
    echo "<h3>📁 Profile Picture Files Verification</h3>\n";
    
    $photo_files = $pdo->query("SELECT DISTINCT photo FROM participants WHERE photo IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN);
    
    echo "<p>Found " . count($photo_files) . " unique photo files assigned:</p>\n";
    echo "<ul>\n";
    foreach ($photo_files as $photo) {
        $full_path = "../" . $photo;
        $exists = file_exists($full_path);
        $status = $exists ? "✓" : "✗ MISSING";
        echo "<li>{$status} {$photo}</li>\n";
    }
    echo "</ul>\n";
    
    echo "<div style='background: #e8f5e8; padding: 10px; border-radius: 5px; margin-top: 20px;'>\n";
    echo "<h3>✅ Summary</h3>\n";
    echo "<p>The dummy data assignment was successful:</p>\n";
    echo "<ul>\n";
    echo "<li>All participants now have profile pictures assigned</li>\n";
    echo "<li>Pictures are correctly assigned based on gender (m*.png for males, f*.png for females)</li>\n";
    echo "<li>Random facts have been added to provide interesting participant information</li>\n";
    echo "<li>Facts cover various categories: achievements, specialties, goals, background, etc.</li>\n";
    echo "<li>Not all participants have facts (70% chance), creating realistic variation</li>\n";
    echo "</ul>\n";
    echo "</div>\n";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>\n";
}
?>
