<?php
// StyleScore Database Structure Inspector
try {
    require_once '../includes/db.php';
    echo "<h2>🎯 StyleScore Database Structure</h2>";
    
    // Show all tables
    $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    echo "<h3>📊 Database Tables (" . count($tables) . " total):</h3>";
    
    $relevant_tables = ['events', 'participants', 'event_participants', 'scores', 'judges'];
    
    foreach ($relevant_tables as $table) {
        if (in_array($table, $tables)) {
            echo "<div style='border: 1px solid #ddd; margin: 10px 0; padding: 15px; border-radius: 8px;'>";
            echo "<h4 style='color: #2196F3; margin-top: 0;'>✅ $table</h4>";
            
            // Show table structure
            $columns = $pdo->query("DESCRIBE `$table`")->fetchAll(PDO::FETCH_ASSOC);
            echo "<table border='1' style='border-collapse: collapse; width: 100%; margin-bottom: 10px;'>";
            echo "<tr style='background: #f5f5f5;'><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
            foreach ($columns as $col) {
                echo "<tr>";
                echo "<td><strong>{$col['Field']}</strong></td>";
                echo "<td>{$col['Type']}</td>";
                echo "<td>{$col['Null']}</td>";
                echo "<td>{$col['Key']}</td>";
                echo "<td>{$col['Default']}</td>";
                echo "</tr>";
            }
            echo "</table>";
            
            // Show row count and sample data
            try {
                $count = $pdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
                echo "<p><strong>Records:</strong> $count</p>";
                
                if ($count > 0 && $count < 50) {
                    $sample = $pdo->query("SELECT * FROM `$table` LIMIT 3")->fetchAll(PDO::FETCH_ASSOC);
                    if ($sample) {
                        echo "<h5>Sample Data:</h5>";
                        echo "<table border='1' style='border-collapse: collapse; width: 100%; font-size: 12px;'>";
                        echo "<tr style='background: #f9f9f9;'>";
                        foreach (array_keys($sample[0]) as $header) {
                            echo "<th>$header</th>";
                        }
                        echo "</tr>";
                        foreach ($sample as $row) {
                            echo "<tr>";
                            foreach ($row as $value) {
                                echo "<td>" . htmlspecialchars($value ?? 'NULL') . "</td>";
                            }
                            echo "</tr>";
                        }
                        echo "</table>";
                    }
                }
            } catch (Exception $e) {
                echo "<p style='color: orange;'>⚠️ Could not read data: " . $e->getMessage() . "</p>";
            }
            
            echo "</div>";
        } else {
            echo "<p style='color: red;'>❌ <strong>$table</strong> - Table not found</p>";
        }
    }
    
    // Show all other tables
    $other_tables = array_diff($tables, $relevant_tables);
    if (!empty($other_tables)) {
        echo "<h3>📋 Other Tables:</h3>";
        echo "<ul>";
        foreach ($other_tables as $table) {
            try {
                $count = $pdo->query("SELECT COUNT(*) FROM `$table`")->fetchColumn();
                echo "<li><strong>$table</strong> ($count records)</li>";
            } catch (Exception $e) {
                echo "<li><strong>$table</strong> (unable to count)</li>";
            }
        }
        echo "</ul>";
    }
    
    echo "<hr><a href='index.php' style='background: #39FF14; color: black; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>→ Back to TV Overlay Control Panel</a>";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>
