<?php
require_once '../includes/db.php';

echo "<h2>Database Photo Investigation</h2>";

echo "<h3>Participants Table Structure</h3>";
try {
    $stmt = $pdo->query("DESCRIBE participants");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<table border='1'>";
    echo "<tr><th>Column</th><th>Type</th><th>Null</th><th>Default</th></tr>";
    foreach ($columns as $col) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($col['Field']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Type']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Null']) . "</td>";
        echo "<td>" . htmlspecialchars($col['Default'] ?? 'NULL') . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}

echo "<h3>Photo Column Analysis</h3>";
try {
    // Check all participants with any photo data
    $stmt = $pdo->query("
        SELECT 
            COUNT(*) as total_participants,
            COUNT(CASE WHEN photo IS NOT NULL AND photo != '' THEN 1 END) as with_photos,
            COUNT(CASE WHEN photo IS NULL OR photo = '' THEN 1 END) as without_photos
        FROM participants
    ");
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    echo "<p><strong>Total participants:</strong> {$stats['total_participants']}</p>";
    echo "<p><strong>With photos:</strong> {$stats['with_photos']}</p>";
    echo "<p><strong>Without photos:</strong> {$stats['without_photos']}</p>";
    
    if ($stats['with_photos'] > 0) {
        echo "<h4>Sample Photo Filenames</h4>";
        $stmt = $pdo->query("
            SELECT id, first_name, last_name, photo 
            FROM participants 
            WHERE photo IS NOT NULL AND photo != '' 
            LIMIT 10
        ");
        $participants = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Name</th><th>Photo Filename</th></tr>";
        foreach ($participants as $p) {
            echo "<tr>";
            echo "<td>{$p['id']}</td>";
            echo "<td>{$p['first_name']} {$p['last_name']}</td>";
            echo "<td>" . htmlspecialchars($p['photo']) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        echo "<h4>Photo Filename Patterns</h4>";
        $stmt = $pdo->query("
            SELECT 
                SUBSTRING_INDEX(photo, '.', -1) as extension,
                COUNT(*) as count
            FROM participants 
            WHERE photo IS NOT NULL AND photo != ''
            GROUP BY SUBSTRING_INDEX(photo, '.', -1)
        ");
        $extensions = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<p>File extensions found:</p>";
        echo "<ul>";
        foreach ($extensions as $ext) {
            echo "<li>.{$ext['extension']}: {$ext['count']} files</li>";
        }
        echo "</ul>";
        
        // Check for common path patterns
        $stmt = $pdo->query("
            SELECT photo
            FROM participants 
            WHERE photo IS NOT NULL AND photo != ''
            LIMIT 5
        ");
        $sample_photos = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        echo "<h4>Sample Photo Paths Check</h4>";
        foreach ($sample_photos as $photo) {
            echo "<h5>Photo: " . htmlspecialchars($photo) . "</h5>";
            
            $possible_locations = [
                // Absolute paths from web root
                $_SERVER['DOCUMENT_ROOT'] . '/uploads/' . $photo,
                $_SERVER['DOCUMENT_ROOT'] . '/v2/uploads/' . $photo,
                $_SERVER['DOCUMENT_ROOT'] . '/stylescore2025/htdocs/v2/uploads/' . $photo,
                
                // Relative to current script location
                dirname(__DIR__) . '/uploads/' . $photo,
                dirname(dirname(__DIR__)) . '/uploads/' . $photo,
                dirname(dirname(dirname(__DIR__))) . '/uploads/' . $photo,
                
                // Common upload directories
                dirname(__DIR__) . '/uploads/participants/' . $photo,
                dirname(__DIR__) . '/uploads/profile_stills/' . $photo,
                dirname(__DIR__) . '/assets/img/participants/' . $photo,
            ];
            
            $found = false;
            foreach ($possible_locations as $location) {
                $real_path = realpath($location);
                if ($real_path && file_exists($real_path)) {
                    echo "<p style='color: green;'>✓ Found at: $location</p>";
                    echo "<p>Real path: $real_path</p>";
                    echo "<p>File size: " . filesize($real_path) . " bytes</p>";
                    $found = true;
                    break;
                }
            }
            
            if (!$found) {
                echo "<p style='color: red;'>✗ Not found in any checked location</p>";
                echo "<details><summary>Checked locations:</summary><ul>";
                foreach ($possible_locations as $location) {
                    echo "<li>$location</li>";
                }
                echo "</ul></details>";
            }
        }
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}

echo "<h3>Event Participants with Photos</h3>";
try {
    $events_stmt = $pdo->query("SELECT id, name FROM events ORDER BY id DESC LIMIT 5");
    $events = $events_stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($events as $event) {
        echo "<h4>Event: {$event['name']} (ID: {$event['id']})</h4>";
        
        $stmt = $pdo->prepare("
            SELECT 
                COUNT(*) as total_participants,
                COUNT(CASE WHEN p.photo IS NOT NULL AND p.photo != '' THEN 1 END) as with_photos
            FROM event_participants ep
            JOIN participants p ON ep.participant_id = p.id
            WHERE ep.event_id = ?
        ");
        $stmt->execute([$event['id']]);
        $event_stats = $stmt->fetch(PDO::FETCH_ASSOC);
        
        echo "<p>Total participants: {$event_stats['total_participants']}, With photos: {$event_stats['with_photos']}</p>";
        
        if ($event_stats['with_photos'] > 0) {
            $stmt = $pdo->prepare("
                SELECT p.id, p.first_name, p.last_name, p.photo, ep.bib_number
                FROM event_participants ep
                JOIN participants p ON ep.participant_id = p.id
                WHERE ep.event_id = ? AND p.photo IS NOT NULL AND p.photo != ''
                LIMIT 3
            ");
            $stmt->execute([$event['id']]);
            $participants = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            echo "<table border='1'>";
            echo "<tr><th>BIB</th><th>Name</th><th>Photo</th></tr>";
            foreach ($participants as $p) {
                echo "<tr>";
                echo "<td>{$p['bib_number']}</td>";
                echo "<td>{$p['first_name']} {$p['last_name']}</td>";
                echo "<td>" . htmlspecialchars($p['photo']) . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        }
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}

echo "<h3>File System Investigation</h3>";
echo "<p>Current script path: " . __FILE__ . "</p>";
echo "<p>Document root: " . $_SERVER['DOCUMENT_ROOT'] . "</p>";

// Look for uploads directories
$search_paths = [
    dirname(__DIR__),
    dirname(dirname(__DIR__)),
    dirname(dirname(dirname(__DIR__))),
    $_SERVER['DOCUMENT_ROOT']
];

foreach ($search_paths as $search_path) {
    echo "<h4>Searching in: $search_path</h4>";
    
    $upload_dirs = [
        $search_path . '/uploads',
        $search_path . '/v2/uploads',
        $search_path . '/assets',
        $search_path . '/images',
        $search_path . '/photos'
    ];
    
    foreach ($upload_dirs as $upload_dir) {
        if (is_dir($upload_dir)) {
            echo "<p style='color: green;'>✓ Found directory: $upload_dir</p>";
            
            // List subdirectories
            $subdirs = glob($upload_dir . '/*', GLOB_ONLYDIR);
            if (!empty($subdirs)) {
                echo "<ul>";
                foreach ($subdirs as $subdir) {
                    echo "<li>" . basename($subdir) . "/</li>";
                }
                echo "</ul>";
            }
            
            // Count files
            $files = glob($upload_dir . '/*.*');
            echo "<p>Files in directory: " . count($files) . "</p>";
        }
    }
}
?>
