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

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

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    
    // Test 1: Get event 3 scoring format
    echo "<h3>1. Event 3 Scoring Format</h3>";
    $stmt = $pdo->prepare("SELECT * FROM events WHERE id = 3");
    $stmt->execute();
    $event = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "<pre>Event 3: " . print_r($event, true) . "</pre>";
    
    if ($event && $event['scoring_format']) {
        echo "<h3>2. Scoring Format Details</h3>";
        $stmt = $pdo->prepare("SELECT * FROM scoring_formats WHERE id = ?");
        $stmt->execute([$event['scoring_format']]);
        $format = $stmt->fetch(PDO::FETCH_ASSOC);
        echo "<pre>Scoring Format: " . print_r($format, true) . "</pre>";
        
        if ($format && $format['sport']) {
            $sport = $format['sport'];
            $base_sport = explode('_', $sport)[0];
            
            echo "<h3>3. Figure Categories for sport '$sport' and base sport '$base_sport'</h3>";
            
            // Check figure_categories table structure
            echo "<h4>Figure Categories Table Structure:</h4>";
            $stmt = $pdo->query("DESCRIBE figure_categories");
            $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo "<pre>" . print_r($columns, true) . "</pre>";
            
            // Check for categories with exact sport name
            echo "<h4>Categories for exact sport name '$sport':</h4>";
            $stmt = $pdo->prepare("SELECT * FROM figure_categories WHERE sport_name = ?");
            $stmt->execute([$sport]);
            $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo "<pre>Count: " . count($categories) . "\n" . print_r($categories, true) . "</pre>";
            
            // Check for categories with base sport name
            echo "<h4>Categories for base sport name '$base_sport':</h4>";
            $stmt = $pdo->prepare("SELECT * FROM figure_categories WHERE sport_name = ?");
            $stmt->execute([$base_sport]);
            $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo "<pre>Count: " . count($categories) . "\n" . print_r($categories, true) . "</pre>";
            
            if (!empty($categories)) {
                echo "<h3>4. Figure Items for sport '$base_sport'</h3>";
                
                // Check figure_items table structure
                echo "<h4>Figure Items Table Structure:</h4>";
                $stmt = $pdo->query("DESCRIBE figure_items");
                $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
                echo "<pre>" . print_r($columns, true) . "</pre>";
                
                // Get sample items
                $stmt = $pdo->prepare("SELECT * FROM figure_items WHERE sport_name = ? LIMIT 10");
                $stmt->execute([$base_sport]);
                $items = $stmt->fetchAll(PDO::FETCH_ASSOC);
                echo "<pre>Sample Items Count: " . count($items) . "\n" . print_r($items, true) . "</pre>";
            }
            
            // Check if we have any sports at all in figure_categories
            echo "<h3>5. All Sports in Figure Categories</h3>";
            $stmt = $pdo->query("SELECT DISTINCT sport_name FROM figure_categories");
            $sports = $stmt->fetchAll(PDO::FETCH_COLUMN);
            echo "<pre>Available sports: " . print_r($sports, true) . "</pre>";
        }
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>
