<?php
// Direct database test for figures
try {
    // Database connection (same as API)
    $host = 'localhost';
    $db = 'freestyle_db';
    $user = 'root';
    $pass = '';
    $dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";
    
    $pdo = new PDO($dsn, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "<h3>Direct Database Test for Event 3</h3>\n";
    
    // Get event info
    $event_stmt = $pdo->prepare("SELECT * FROM events WHERE id = 3");
    $event_stmt->execute();
    $event = $event_stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($event) {
        echo "<p><strong>Event found:</strong></p>\n";
        echo "<ul>\n";
        echo "<li>Name: " . htmlspecialchars($event['name'] ?? 'N/A') . "</li>\n";
        echo "<li>Scoring Format ID: " . htmlspecialchars($event['scoring_format'] ?? 'N/A') . "</li>\n";
        echo "</ul>\n";
        
        // Get sport from scoring format
        if ($event['scoring_format']) {
            echo "<p><strong>Looking up scoring_format ID:</strong> " . htmlspecialchars($event['scoring_format']) . "</p>\n";
            
            $sport_stmt = $pdo->prepare("SELECT id, sport FROM scoring_formats WHERE id = ?");
            $sport_stmt->execute([$event['scoring_format']]);
            $format_data = $sport_stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($format_data) {
                echo "<p><strong>Scoring format found:</strong></p>\n";
                echo "<ul>\n";
                echo "<li>ID: " . htmlspecialchars($format_data['id']) . "</li>\n";
                echo "<li>Sport: " . htmlspecialchars($format_data['sport']) . "</li>\n";
                echo "</ul>\n";
                
                $sport = $format_data['sport'];
                
                // Test base sport extraction
                $base_sport = $sport;
                if (strpos($sport, 'snowboard') === 0) {
                    $base_sport = 'snowboard';
                } elseif (strpos($sport, 'skiing') === 0) {
                    $base_sport = 'skiing';
                } elseif (strpos($sport, 'freestyle') === 0) {
                    $base_sport = 'freestyle';
                } else {
                    // Try first word before underscore
                    $parts = explode('_', $sport);
                    $base_sport = $parts[0];
                }
                
                echo "<p><strong>Original sport:</strong> " . htmlspecialchars($sport) . "</p>\n";
                echo "<p><strong>Base sport extracted:</strong> " . htmlspecialchars($base_sport) . "</p>\n";
                
                // Test figure query - try original sport first
                $figCheck = $pdo->prepare("SELECT COUNT(*) FROM figure_categories fc JOIN figure_items fi ON fi.sport_name = fc.sport_name AND fi.category_name = fc.category_name WHERE fc.sport_name = ? AND fc.is_active = 1 AND fi.is_active = 1");
                $figCheck->execute([$sport]);
                $count_original = $figCheck->fetchColumn();
                
                // Test with base sport
                $figCheck->execute([$base_sport]);
                $count_base = $figCheck->fetchColumn();
                
                echo "<p><strong>Figures found for original sport '$sport':</strong> $count_original</p>\n";
                echo "<p><strong>Figures found for base sport '$base_sport':</strong> $count_base</p>\n";
                
                $sport_to_use = $count_original > 0 ? $sport : ($count_base > 0 ? $base_sport : null);
                
                if ($sport_to_use) {
                    echo "<p><strong>Using sport:</strong> " . htmlspecialchars($sport_to_use) . "</p>\n";
                    
                    // Get categories
                    $catStmt = $pdo->prepare("SELECT category_name FROM figure_categories WHERE sport_name = ? AND is_active = 1 ORDER BY sort_order, category_name");
                    $catStmt->execute([$sport_to_use]);
                    $categories = $catStmt->fetchAll(PDO::FETCH_COLUMN);
                    
                    echo "<p><strong>Categories:</strong></p>\n<ul>\n";
                    foreach ($categories as $cat) {
                        echo "<li>" . htmlspecialchars($cat) . "</li>\n";
                    }
                    echo "</ul>\n";
                    
                    // Get items
                    $itemsStmt = $pdo->prepare("SELECT category_name, item_name FROM figure_items WHERE sport_name = ? AND is_active = 1 ORDER BY category_name, sort_order, item_name LIMIT 20");
                    $itemsStmt->execute([$sport_to_use]);
                    $items = $itemsStmt->fetchAll(PDO::FETCH_ASSOC);
                    
                    echo "<p><strong>Sample items (first 20):</strong></p>\n<ul>\n";
                    foreach ($items as $item) {
                        echo "<li>" . htmlspecialchars($item['category_name']) . " -> " . htmlspecialchars($item['item_name']) . "</li>\n";
                    }
                    echo "</ul>\n";
                } else {
                    echo "<p><strong>No figures found for either sport name!</strong></p>\n";
                }
            } else {
                echo "<p><strong>Scoring format not found!</strong></p>\n";
            }
        } else {
            echo "<p><strong>No scoring format ID found in event!</strong></p>\n";
        }
    } else {
        echo "<p><strong>Event 3 not found!</strong></p>\n";
    }
    
} catch (Exception $e) {
    echo "<p><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</p>\n";
}
?>
