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

echo "<h2>Database Structure Analysis</h2>";

$event_id = 3;

function getTableStructure($pdo, $table_name) {
    try {
        $stmt = $pdo->prepare("DESCRIBE $table_name");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        return null;
    }
}

function tableExists($pdo, $table_name) {
    try {
        $stmt = $pdo->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table_name]);
        return $stmt->rowCount() > 0;
    } catch (Exception $e) {
        return false;
    }
}

// Check key tables
$key_tables = ['events', 'participants', 'event_participants', 'scores', 'event_heat_settings', 'categories', 'judges'];

echo "<h3>Table Structures</h3>";

foreach ($key_tables as $table) {
    if (tableExists($pdo, $table)) {
        echo "<h4>$table</h4>";
        $structure = getTableStructure($pdo, $table);
        if ($structure) {
            echo "<table border='1'>";
            echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
            foreach ($structure as $column) {
                echo "<tr>";
                echo "<td>" . $column['Field'] . "</td>";
                echo "<td>" . $column['Type'] . "</td>";
                echo "<td>" . $column['Null'] . "</td>";
                echo "<td>" . $column['Key'] . "</td>";
                echo "<td>" . $column['Default'] . "</td>";
                echo "</tr>";
            }
            echo "</table><br>";
        }
    } else {
        echo "<p style='color: red;'>Table '$table' does not exist</p>";
    }
}

// Now test queries with correct columns
echo "<h3>Data Tests with Correct Columns</h3>";

// Test events table
if (tableExists($pdo, 'events')) {
    try {
        $stmt = $pdo->prepare("SELECT * FROM events WHERE id = ?");
        $stmt->execute([$event_id]);
        $event = $stmt->fetch(PDO::FETCH_ASSOC);
        
        echo "<h4>Event $event_id:</h4>";
        if ($event) {
            echo "<pre>" . print_r($event, true) . "</pre>";
        } else {
            echo "<p>Event $event_id not found</p>";
        }
    } catch (Exception $e) {
        echo "<p style='color: red;'>Error querying events: " . $e->getMessage() . "</p>";
    }
}

// Test participants for this event
if (tableExists($pdo, 'participants') && tableExists($pdo, 'event_participants')) {
    try {
        $stmt = $pdo->prepare("
            SELECT p.*, ep.bib_number, ep.heat_number, ep.category_id
            FROM participants p
            INNER JOIN event_participants ep ON p.id = ep.participant_id
            WHERE ep.event_id = ?
            LIMIT 5
        ");
        $stmt->execute([$event_id]);
        $participants = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<h4>Participants in Event $event_id (" . count($participants) . " shown):</h4>";
        if (!empty($participants)) {
            echo "<table border='1'>";
            echo "<tr><th>ID</th><th>Name</th><th>Photo</th><th>Bib</th><th>Heat</th><th>Category ID</th></tr>";
            foreach ($participants as $p) {
                echo "<tr>";
                echo "<td>" . $p['id'] . "</td>";
                echo "<td>" . htmlspecialchars($p['name'] ?? ($p['first_name'] . ' ' . $p['last_name'])) . "</td>";
                echo "<td>" . htmlspecialchars($p['photo'] ?? 'none') . "</td>";
                echo "<td>" . $p['bib_number'] . "</td>";
                echo "<td>" . $p['heat_number'] . "</td>";
                echo "<td>" . $p['category_id'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        }
    } catch (Exception $e) {
        echo "<p style='color: red;'>Error querying participants: " . $e->getMessage() . "</p>";
    }
}

// Test scores - find correct relationship
if (tableExists($pdo, 'scores')) {
    echo "<h4>Scores Table Sample:</h4>";
    try {
        $stmt = $pdo->prepare("SELECT * FROM scores LIMIT 3");
        $stmt->execute();
        $scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (!empty($scores)) {
            echo "<pre>" . print_r($scores[0], true) . "</pre>";
            
            // Now try to find the relationship
            echo "<h4>Scores for Event $event_id:</h4>";
            
            // Try different possible relationships
            $possible_queries = [
                "SELECT s.* FROM scores s WHERE s.event_id = ?",
                "SELECT s.* FROM scores s INNER JOIN event_participants ep ON s.participant_id = ep.participant_id WHERE ep.event_id = ?",
                "SELECT s.* FROM scores s INNER JOIN event_participants ep ON s.event_participant_id = ep.id WHERE ep.event_id = ?",
                "SELECT s.* FROM scores s WHERE s.event_participant_id IN (SELECT id FROM event_participants WHERE event_id = ?)"
            ];
            
            foreach ($possible_queries as $i => $query) {
                try {
                    $stmt = $pdo->prepare($query);
                    $stmt->execute([$event_id]);
                    $event_scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    echo "<p>Query " . ($i + 1) . ": Found " . count($event_scores) . " scores</p>";
                    if (count($event_scores) > 0) {
                        echo "<p style='color: green;'>✓ This query works!</p>";
                        echo "<pre>" . print_r($event_scores[0], true) . "</pre>";
                        break;
                    }
                } catch (Exception $e) {
                    echo "<p>Query " . ($i + 1) . ": " . $e->getMessage() . "</p>";
                }
            }
        }
    } catch (Exception $e) {
        echo "<p style='color: red;'>Error querying scores: " . $e->getMessage() . "</p>";
    }
}

// Test photo files
echo "<h3>Photo Files Check</h3>";

$photo_dirs = [
    __DIR__ . '/../uploads/',
    __DIR__ . '/../uploads/participants/',
    __DIR__ . '/../uploads/profile_stills/'
];

foreach ($photo_dirs as $dir) {
    echo "<h4>Directory: $dir</h4>";
    if (is_dir($dir)) {
        $files = scandir($dir);
        $photo_files = array_filter($files, function($file) {
            return in_array(strtolower(pathinfo($file, PATHINFO_EXTENSION)), ['jpg', 'jpeg', 'png', 'gif']);
        });
        
        echo "<p>Found " . count($photo_files) . " photo files:</p>";
        foreach (array_slice($photo_files, 0, 10) as $file) {
            echo "- $file<br>";
        }
    } else {
        echo "<p style='color: red;'>Directory does not exist</p>";
    }
}
?>
