<?php
// Include database connection
require_once '../includes/db.php';

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

try {
    // Test database connection
    $stmt = $pdo->query("SELECT DATABASE() as db_name");
    $db_info = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "<p><strong>Connected to database:</strong> " . $db_info['db_name'] . "</p>";
    
    // Get all tables
    $stmt = $pdo->query("SHOW TABLES");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    echo "<h2>Available Tables (" . count($tables) . ")</h2>";
    echo "<ul>";
    foreach ($tables as $table) {
        echo "<li>$table</li>";
    }
    echo "</ul>";
    
    // Check for scores table structure
    if (in_array('scores', $tables)) {
        echo "<h2>Scores Table Structure</h2>";
        $stmt = $pdo->query("DESCRIBE scores");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<table border='1'>";
        echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
        foreach ($columns as $col) {
            echo "<tr>";
            echo "<td>" . $col['Field'] . "</td>";
            echo "<td>" . $col['Type'] . "</td>";
            echo "<td>" . $col['Null'] . "</td>";
            echo "<td>" . $col['Key'] . "</td>";
            echo "<td>" . $col['Default'] . "</td>";
            echo "<td>" . $col['Extra'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        // Get sample data
        echo "<h3>Sample Scores Data</h3>";
        $stmt = $pdo->query("SELECT * FROM scores LIMIT 3");
        $sample_scores = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (!empty($sample_scores)) {
            echo "<pre>" . print_r($sample_scores, true) . "</pre>";
        } else {
            echo "<p>No scores data found.</p>";
        }
    }
    
    // Check for event_participants table structure
    if (in_array('event_participants', $tables)) {
        echo "<h2>Event Participants Table Structure</h2>";
        $stmt = $pdo->query("DESCRIBE event_participants");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<table border='1'>";
        echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
        foreach ($columns as $col) {
            echo "<tr>";
            echo "<td>" . $col['Field'] . "</td>";
            echo "<td>" . $col['Type'] . "</td>";
            echo "<td>" . $col['Null'] . "</td>";
            echo "<td>" . $col['Key'] . "</td>";
            echo "<td>" . $col['Default'] . "</td>";
            echo "<td>" . $col['Extra'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    // Check for runs table structure
    if (in_array('runs', $tables)) {
        echo "<h2>Runs Table Structure</h2>";
        $stmt = $pdo->query("DESCRIBE runs");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<table border='1'>";
        echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
        foreach ($columns as $col) {
            echo "<tr>";
            echo "<td>" . $col['Field'] . "</td>";
            echo "<td>" . $col['Type'] . "</td>";
            echo "<td>" . $col['Null'] . "</td>";
            echo "<td>" . $col['Key'] . "</td>";
            echo "<td>" . $col['Default'] . "</td>";
            echo "<td>" . $col['Extra'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    // Test if we have any events to work with
    if (in_array('events', $tables)) {
        echo "<h2>Available Events</h2>";
        $stmt = $pdo->query("SELECT id, name, created_at FROM events ORDER BY id DESC LIMIT 5");
        $events = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (!empty($events)) {
            echo "<table border='1'>";
            echo "<tr><th>ID</th><th>Name</th><th>Created</th></tr>";
            foreach ($events as $event) {
                echo "<tr>";
                echo "<td>" . $event['id'] . "</td>";
                echo "<td>" . htmlspecialchars($event['name']) . "</td>";
                echo "<td>" . $event['created_at'] . "</td>";
                echo "</tr>";
            }
            echo "</table>";
        } else {
            echo "<p>No events found.</p>";
        }
    }
    
} catch (Exception $e) {
    echo "<p style='color: red;'>Error: " . $e->getMessage() . "</p>";
}
?>
