<?php
/*
Example Usage:
Heat-Specific Categories (Your Use Case):
This will automatically detect that Heat 2 has categories ["10","9"] (Female Junior, Male Junior) and return results for both categories combined.
GET /api/summary_table_api.php?event_id=3&category=all&heat_run_filter={"2":[1,2]}

Explicit Category:
This will return results for only "female junior" category in Heat 2.
GET /api/summary_table_api.php?event_id=3&category=female%20junior&heat_run_filter={"2":[1,2]}

All Categories:
This will return results for all categories across all heats.
GET /api/summary_table_api.php?event_id=3&category=all


*/
header('Content-Type: application/json');
include '../includes/db.php';

// Get parameters
$selected_event = $_GET['event_id'] ?? 0;
$filter_category = $_GET['category'] ?? 'all';
$filter_gender = $_GET['gender'] ?? 'all';
$sort_by = $_GET['sort'] ?? 'none';

// New enhanced column filtering parameters
$heat_run_filter = $_GET['heat_run_filter'] ?? '{}'; // JSON object: {heat: [runs]}
$show_runs = $_GET['show_runs'] ?? 'true';
$show_judges = $_GET['show_judges'] ?? 'true';
$show_control_points = $_GET['show_control_points'] ?? 'true';
$show_heat_best = $_GET['show_heat_best'] ?? 'true';
$show_heat_average = $_GET['show_heat_average'] ?? 'true';
$show_overall_best = $_GET['show_overall_best'] ?? 'false';
$show_highest_average = $_GET['show_highest_average'] ?? 'false';

// Function to parse heat/run filter
function parseHeatRunFilter($filterJson) {
    $decoded = json_decode($filterJson, true);
    if (!is_array($decoded)) {
        return [];
    }
    
    // Convert string keys to integers and ensure run arrays are integers
    $result = [];
    foreach ($decoded as $heat => $runs) {
        $heatNum = (int)$heat;
        if (is_array($runs)) {
            $result[$heatNum] = array_map('intval', $runs);
        }
    }
    
    return $result;
}

// Function to check if column should be included
function shouldIncludeColumn($header, $filters) {
    // Basic columns are always included
    if (in_array($header, ['Rank', 'BIB', 'Participant', 'Category', 'Club', 'Gender'])) {
        return true;
    }
    
    // Parse heat and run from header
    if (preg_match('/^H(\d+)R(\d+)(_control_points|_judges)?$/', $header, $matches)) {
        $heat = (int)$matches[1];
        $run = (int)$matches[2];
        $type = $matches[3] ?? '';
        
        // Check if this heat/run combination is selected
        if (!empty($filters['heat_run_filter'])) {
            if (!isset($filters['heat_run_filter'][$heat])) {
                return false; // Heat not selected
            }
            
            // NEW: If heat is selected but runs array is empty or contains 'all', show all runs for this heat
            $selected_runs = $filters['heat_run_filter'][$heat];
            if (empty($selected_runs) || in_array('all', $selected_runs)) {
                // Heat is selected but no specific runs - include all runs for this heat
                // This will be handled by the heat settings to determine max runs
            } else {
                // Specific runs are selected - check if this run is included
                if (!in_array($run, $selected_runs)) {
                    return false; // Run not selected for this heat
                }
            }
        }
        
        // Check type filters
        if ($type === '_control_points' && !$filters['show_control_points']) {
            return false;
        }
        if ($type === '_judges' && !$filters['show_judges']) {
            return false;
        }
        if ($type === '' && !$filters['show_runs']) {
            return false;
        }
        
        return true;
    }
    
    // Heat best/average columns - show if heat is selected (regardless of specific runs)
    if (preg_match('/^H(\d+)(Best|Average)$/', $header, $matches)) {
        $heat = (int)$matches[1];
        $type = $matches[2];
        
        // Check if this heat is selected (has any runs or is marked for inclusion)
        if (!empty($filters['heat_run_filter'])) {
            if (!isset($filters['heat_run_filter'][$heat])) {
                return false; // Heat not selected
            }
            // NEW: If heat exists in filter (even with empty runs array), include heat columns
            // This allows showing heat averages/bests when only heat number is specified
        }
        
        // Check type filters
        if ($type === 'Best' && !$filters['show_heat_best']) {
            return false;
        }
        if ($type === 'Average' && !$filters['show_heat_average']) {
            return false;
        }
        
        return true;
    }
    
    // Overall columns
    if ($header === 'OverallAverage' && !$filters['show_overall_best']) {
        return false;
    }
    if ($header === 'HighestAverage' && !$filters['show_highest_average']) {
        return false;
    }
    
    return true;
}

// Function to get column CSS class
function getColClass($header) {
    if (preg_match('/^H\d+Best$/', $header)) return 'col-best table-primary';
    if (preg_match('/^H\d+Average$/', $header)) return 'col-average table-secondary';
    if ($header === 'OverallAverage') return 'col-overall-average table-success'; // Changed from OverallBest
    if ($header === 'HighestAverage') return 'col-highest-average table-info';
    if (preg_match('/^H\d+R\d+_control_points$/', $header)) return 'col-control-points table-warning';
    if (preg_match('/^H\d+R\d+_judges$/', $header)) return 'col-judges table-light';
    return '';
}

// Function to generate available column configuration
function getAvailableColumns($heats_total, $runs_per_heat, $heat_settings = []) {
    $columns = [
        'basic' => ['Rank', 'BIB', 'Participant', 'Category', 'Club', 'Gender'],
        'heats' => [],
        'heat_info' => [], // Enhanced heat information
        'runs' => [],
        'judges' => [],
        'control_points' => [],
        'heat_best' => [],
        'heat_average' => [],
        'overall' => ['OverallAverage', 'HighestAverage'] // Changed from OverallBest
    ];
    
    for ($h = 1; $h <= $heats_total; $h++) {
        $columns['heats'][] = $h;
        
        // Get heat-specific settings
        $heat_name = $heat_settings[$h]['name'] ?? "Heat {$h}";
        $scoring_method = $heat_settings[$h]['runs_scoring_method'] ?? 'average';
        $is_active = $heat_settings[$h]['is_active'] ?? 1;
        $runs_count = $heat_settings[$h]['runs_count'] ?? $runs_per_heat; // Use runs_count as the primary value
        $runs_total = $heat_settings[$h]['runs_total'] ?? $runs_count; // Keep runs_total for compatibility
        $categories = $heat_settings[$h]['categories'] ?? [];
        
        // Enhanced heat information with all settings
        $columns['heat_info'][$h] = [
            'number' => $h,
            'name' => $heat_name,
            'runs_scoring_method' => $scoring_method,
            'is_active' => $is_active,
            'runs_count' => $runs_count,      // Primary run count value
            'runs_total' => $runs_total,      // Compatibility
            'categories' => $categories,
            'created_at' => $heat_settings[$h]['created_at'] ?? null,
            'updated_at' => $heat_settings[$h]['updated_at'] ?? null
        ];
        
        $columns['heat_best'][] = "H{$h}Best";
        $columns['heat_average'][] = "H{$h}Average";
        
        // Use runs_count for generating columns
        for ($r = 1; $r <= $runs_count; $r++) {
            $columns['runs'][] = "H{$h}R{$r}";
            $columns['judges'][] = "H{$h}R{$r}_judges";
            $columns['control_points'][] = "H{$h}R{$r}_control_points";
        }
    }
    
    return $columns;
}

// Function to get categories assigned to a specific heat
function getHeatCategories($pdo, $event_id, $heat_number) {
    $heat_categories = [];
    
    // Get categories from event_heat_settings for the specific heat
    $heat_settings_stmt = $pdo->prepare("
        SELECT categories 
        FROM event_heat_settings 
        WHERE event_id = ? AND heat_number = ?
    ");
    $heat_settings_stmt->execute([$event_id, $heat_number]);
    $heat_setting = $heat_settings_stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($heat_setting && !empty($heat_setting['categories'])) {
        $category_ids = json_decode($heat_setting['categories'], true);
        
        if (is_array($category_ids) && !empty($category_ids)) {
            // Get category names from category IDs
            $placeholders = str_repeat('?,', count($category_ids) - 1) . '?';
            $categories_stmt = $pdo->prepare("
                SELECT category_name 
                FROM event_categories 
                WHERE event_id = ? AND id IN ({$placeholders})
                ORDER BY category_name
            ");
            $categories_stmt->execute(array_merge([$event_id], $category_ids));
            $heat_categories = $categories_stmt->fetchAll(PDO::FETCH_COLUMN);
        }
    }
    
    return $heat_categories;
}

// Function to get category names from category IDs
function getHeatCategoryNames($pdo, $event_id, $category_ids) {
    if (empty($category_ids)) {
        return [];
    }
    
    $placeholders = str_repeat('?,', count($category_ids) - 1) . '?';
    $categories_stmt = $pdo->prepare("
        SELECT category_name 
        FROM event_categories 
        WHERE event_id = ? AND id IN ({$placeholders})
        ORDER BY category_name
    ");
    $categories_stmt->execute(array_merge([$event_id], $category_ids));
    return $categories_stmt->fetchAll(PDO::FETCH_COLUMN);
}

// Function to determine which categories to process based on filters and heat settings
function determineCategoriesToProcess($pdo, $selected_event, $filter_category, $heat_run_filter) {
    // Get all available categories for the event with deduplication
    $all_categories = [];
    if ($selected_event) {
        $categories_stmt = $pdo->prepare("
            SELECT DISTINCT category 
            FROM event_participants 
            WHERE event_id = ? 
              AND category IS NOT NULL 
              AND category != '' 
              AND TRIM(category) != ''
            ORDER BY category
        ");
        $categories_stmt->execute([$selected_event]);
        $all_categories = $categories_stmt->fetchAll(PDO::FETCH_COLUMN);
        
        // Additional cleanup - remove any potential whitespace issues
        $all_categories = array_unique(array_map('trim', $all_categories));
        $all_categories = array_filter($all_categories, function($cat) {
            return !empty($cat);
        });
        sort($all_categories);
    }
    
    // If a specific category is requested, use it
    if ($filter_category !== 'all') {
        return [$filter_category];
    }
    
    // If heat_run_filter is specified and contains only one heat, get categories for that heat
    if (!empty($heat_run_filter)) {
        $heat_filter = json_decode($heat_run_filter, true);
        if (is_array($heat_filter) && count($heat_filter) === 1) {
            $heat_number = array_keys($heat_filter)[0];
            $heat_categories = getHeatCategories($pdo, $selected_event, $heat_number);
            
            if (!empty($heat_categories)) {
                // Clean and deduplicate heat categories too
                $heat_categories = array_unique(array_map('trim', $heat_categories));
                $heat_categories = array_filter($heat_categories, function($cat) {
                    return !empty($cat);
                });
                sort($heat_categories);
                return $heat_categories;
            }
        }
    }
    
    // Default to all categories
    return $all_categories;
}

// Modified generateSummaryTableData function to handle heat-specific categories
function generateSummaryTableData($pdo, $selected_event, $filter_category, $filter_gender, $sort_by, $column_filters) {
    // Determine which categories to process
    $categories_to_process = determineCategoriesToProcess($pdo, $selected_event, $filter_category, $column_filters['heat_run_filter_json'] ?? '{}');
    
    // Holders for the summary data
    $summary_data = [];
    $heat_numbers = [];
    $final_headers = [];

    // Fetch event settings and heat configurations
    $heats_total = 2;
    $runs_per_heat = 2;
    $heat_settings = [];
    
    if ($selected_event) {
        // Get basic event settings
        $event_settings_stmt = $pdo->prepare("SELECT heats_total, runs_per_heat FROM events WHERE id = ?");
        $event_settings_stmt->execute([$selected_event]);
        $event_settings = $event_settings_stmt->fetch(PDO::FETCH_ASSOC);
        if ($event_settings) {
            $heats_total = intval($event_settings['heats_total']);
            $runs_per_heat = intval($event_settings['runs_per_heat']); // Default from events table
        }
        
        // Get ALL heat-specific settings including names, scoring methods, and run counts
        $heat_settings_stmt = $pdo->prepare("
            SELECT 
                heat_number,
                heat_name,
                runs_scoring_method,
                is_active,
                categories,
                runs_count,
                created_at,
                updated_at
            FROM event_heat_settings 
            WHERE event_id = ? 
            ORDER BY heat_number
        ");
        $heat_settings_stmt->execute([$selected_event]);
        $heat_settings_results = $heat_settings_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Process heat settings into associative array with enhanced scoring method parsing
        foreach ($heat_settings_results as $setting) {
            $heat_num = intval($setting['heat_number']);
            
            // Use runs_count if available, otherwise fall back to default
            $heat_runs_count = intval($setting['runs_count'] ?? $runs_per_heat);
            
            // Parse and validate scoring method
            $raw_scoring_method = $setting['runs_scoring_method'] ?? 'average';
            $validated_scoring_method = validateScoringMethod($raw_scoring_method, $heat_runs_count);
            
            $heat_settings[$heat_num] = [
                'name' => $setting['heat_name'] ?? "Heat {$heat_num}",
                'runs_scoring_method' => $validated_scoring_method,
                'runs_scoring_method_raw' => $raw_scoring_method, // Keep original for debugging
                'is_active' => intval($setting['is_active'] ?? 1),
                'categories' => json_decode($setting['categories'] ?? '[]', true),
                'runs_count' => $heat_runs_count,
                'runs_total' => $heat_runs_count,
                'created_at' => $setting['created_at'],
                'updated_at' => $setting['updated_at']
            ];
        }
        
        // Fill in any missing heat settings with defaults
        for ($h = 1; $h <= $heats_total; $h++) {
            if (!isset($heat_settings[$h])) {
                $heat_settings[$h] = [
                    'name' => "Heat {$h}",
                    'runs_scoring_method' => 'average',
                    'is_active' => 1,
                    'categories' => [],
                    'runs_count' => $runs_per_heat,
                    'created_at' => null,
                    'updated_at' => null
                ];
            }
        }
        
        // If no heat_run_filter is provided, use heat-specific run counts from settings
        if (empty($column_filters['heat_run_filter'])) {
            $default_heat_run_filter = [];
            for ($h = 1; $h <= $heats_total; $h++) {
                if ($heat_settings[$h]['is_active']) {
                   
                }
                 // Use runs_count from heat settings (this is the key change)
                    $heat_runs_count = $heat_settings[$h]['runs_count'];
                    $runs_array = range(1, $heat_runs_count); // Create array [1, 2, 3, ...] up to runs_count
                    $default_heat_run_filter[$h] = $runs_array;
            }
            // Update the column filters with the default from heat settings
            $column_filters['heat_run_filter'] = $default_heat_run_filter;
        }
    }

    if ($selected_event && !empty($categories_to_process)) {
        foreach ($categories_to_process as $cat) {
            // Enhanced SQL to get individual judge scores with control point grouping
            $sql = "
                SELECT
                    ep.category,
                    ep.bib_number as bib,
                    p.first_name,
                    p.last_name,
                    p.gender,
                    p.club,
                    ep.heat_number,
                    r.run_number,
                    s.score_value,
                    s.judge_id,
                    u.username as judge_name,
                    ja.control_point_id,
                    ecp.control_point_name as control_point_name,
                    ecp.description as control_point_description,
                    ecp.sort_order as control_point_order
                FROM event_participants ep
                JOIN participants p ON ep.participant_id = p.id
                LEFT JOIN runs r ON r.event_participant_id = ep.id
                LEFT JOIN scores s ON r.id = s.run_id
                LEFT JOIN users u ON s.judge_id = u.id
                LEFT JOIN judge_assignments ja ON (s.judge_id = ja.judge_id AND ja.event_id = ep.event_id)
                LEFT JOIN event_control_points ecp ON (ja.control_point_id = ecp.id AND ecp.event_id = ep.event_id)
                WHERE ep.event_id = ?
                  AND ep.category = ?
                ORDER BY ep.heat_number, r.run_number, ep.bib_number, ecp.sort_order, s.judge_id
            ";
            
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$selected_event, $cat]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($rows as $row) {
                if ($filter_gender !== 'all' && $row['gender'] !== $filter_gender) {
                    continue;
                }

                // Use BIB from event_participants and create participant key
                $participant_first_name = trim($row['first_name'] ?? '');
                $participant_last_name = trim($row['last_name'] ?? '');
                $full_name = trim($participant_first_name . ' ' . $participant_last_name);
                
                // Create a consistent key for the participant that includes name
                $key = $full_name;
                if (empty($key) || $key === ' ') {
                    $key = "BIB " . $row['bib'];
                }
                
                $score = floatval($row['score_value'] ?? 0);
                $heat = $row['heat_number'];
                $run = $row['run_number'];
                $judge_id = $row['judge_id'];
                $control_point_id = $row['control_point_id'];
                $control_point_name = $row['control_point_name'] ?? 'General';
                $control_point_order = $row['control_point_order'] ?? 999;
                
                // Initialize participant data if not exists
                if (!isset($summary_data[$cat][$key])) {
                    $summary_data[$cat][$key]['club'] = $row['club'] ?? '-';
                    $summary_data[$cat][$key]['gender'] = $row['gender'];
                    $summary_data[$cat][$key]['category'] = $row['category'];
                    $summary_data[$cat][$key]['bib'] = $row['bib'];
                    $summary_data[$cat][$key]['first_name'] = $participant_first_name;
                    $summary_data[$cat][$key]['last_name'] = $participant_last_name;
                    $summary_data[$cat][$key]['participant_name'] = $full_name; // Store the full name
                    $summary_data[$cat][$key]['all_scores'] = [];
                    $summary_data[$cat][$key]['judge_scores'] = [];
                    $summary_data[$cat][$key]['control_point_scores'] = [];
                }
                
                // Only process if we have run data
                if ($run && $heat) {
                    $code = "H{$heat}R{$run}";
                    $heat_numbers[$code] = true;
                    
                    // Judge name formatting
                    $judge_name = $row['judge_name'] ?? '';
                    
                    // Store individual judge scores
                    if ($score > 0 && $judge_id) {
                        // Store by control point for grouping
                        $cp_key = $control_point_id ?: 'general';
                        
                        $summary_data[$cat][$key]['judge_scores'][$code][$judge_id] = [
                            'score' => $score,
                            'judge_name' => $judge_name ?: "Judge {$judge_id}",
                            'judge_id' => $judge_id,
                            'control_point_id' => $control_point_id,
                            'control_point_name' => $control_point_name,
                            'control_point_order' => $control_point_order
                        ];
                        
                        // Group by control point for display
                        if (!isset($summary_data[$cat][$key]['control_point_scores'][$code][$cp_key])) {
                            $summary_data[$cat][$key]['control_point_scores'][$code][$cp_key] = [
                                'name' => $control_point_name,
                                'order' => $control_point_order,
                                'judges' => []
                            ];
                        }
                        
                        $summary_data[$cat][$key]['control_point_scores'][$code][$cp_key]['judges'][$judge_id] = [
                            'score' => $score,
                            'judge_name' => $judge_name ?: "Judge {$judge_id}",
                            'judge_id' => $judge_id
                        ];
                        
                        $summary_data[$cat][$key]['all_scores'][] = $score;
                    }
                }
            }
        }
    }

    // Compute averages, best scores, and judge breakdown
    foreach ($summary_data as $cat => &$participants) {
        foreach ($participants as &$p) {
            // Track filtered scores for overall calculations
            $filtered_all_scores = [];
            $filtered_heat_averages = [];
            
            for ($h = 1; $h <= $heats_total; $h++) {
                $heat_scores = [];
                $heat_selected_scores = []; // Only scores from selected runs in this heat
                $heat_has_selected_runs = false;
                
                // Get the actual number of runs for this heat from settings (use runs_count)
                $heat_runs_count = $heat_settings[$h]['runs_count'] ?? $runs_per_heat;
                
                for ($r = 1; $r <= $heat_runs_count; $r++) { // Use heat-specific run count from runs_count
                    $code = "H{$h}R{$r}";
                    
                    // Check if this heat/run is selected in the filter
                    $is_run_selected = true;
                    if (!empty($column_filters['heat_run_filter'])) {
                        $is_run_selected = isset($column_filters['heat_run_filter'][$h]) && 
                                         in_array($r, $column_filters['heat_run_filter'][$h]);
                    }
                    
                    // Calculate average for this run
                    if (isset($p['judge_scores'][$code]) && !empty($p['judge_scores'][$code])) {
                        $run_scores = array_column($p['judge_scores'][$code], 'score');
                        if (!empty($run_scores)) {
                            $run_average = array_sum($run_scores) / count($run_scores);
                            $p[$code] = number_format($run_average, 2);
                            
                            // Always collect for heat calculations (for display)
                            $heat_scores = array_merge($heat_scores, $run_scores);
                            
                            // Only add to filtered scores if this run is selected
                            if ($is_run_selected) {
                                $heat_selected_scores = array_merge($heat_selected_scores, $run_scores);
                                $filtered_all_scores = array_merge($filtered_all_scores, $run_scores);
                                $heat_has_selected_runs = true;
                            }
                            
                            // Store grouped judge scores for display
                            $p["{$code}_judges"] = $p['judge_scores'][$code];
                            $p["{$code}_control_points"] = $p['control_point_scores'][$code] ?? [];
                            
                            // Sort control points by their order
                            if (isset($p["{$code}_control_points"])) {
                                uasort($p["{$code}_control_points"], function($a, $b) {
                                    return ($a['order'] ?? 999) <=> ($b['order'] ?? 999);
                                });
                            }
                        } else {
                            $p[$code] = '-';
                            $p["{$code}_judges"] = [];
                            $p["{$code}_control_points"] = [];
                        }
                    } else {
                        $p[$code] = '-';
                        $p["{$code}_judges"] = [];
                        $p["{$code}_control_points"] = [];
                    }
                }
                
                // Calculate heat best and average based on filter selection and scoring method
                $heat_scoring_method = $heat_settings[$h]['runs_scoring_method'] ?? 'average';
                $heat_run_averages = []; // Store the calculated run averages for this heat
                $heat_selected_run_averages = []; // Store only selected run averages
                
                // Collect the already calculated run averages for this heat
                for ($r = 1; $r <= $heat_runs_count; $r++) {
                    $code = "H{$h}R{$r}";
                    
                    // Check if this heat/run is selected in the filter
                    $is_run_selected = true;
                    if (!empty($column_filters['heat_run_filter'])) {
                        $is_run_selected = isset($column_filters['heat_run_filter'][$h]) && 
                                         in_array($r, $column_filters['heat_run_filter'][$h]);
                    }
                    
                    // Get the already calculated run average (not individual judge scores)
                    if (isset($p[$code]) && $p[$code] !== '-' && is_numeric($p[$code])) {
                        $run_average = floatval($p[$code]);
                        $heat_run_averages[] = $run_average;
                        
                        // Only add to selected averages if this run is selected
                        if ($is_run_selected) {
                            $heat_selected_run_averages[] = $run_average;
                        }
                    }
                }
                
                // Calculate heat scores based on scoring method using run averages (not individual judge scores)
                if (!empty($column_filters['heat_run_filter'])) {
                    // Use only selected runs for heat calculations when filter is applied
                    if (!empty($heat_selected_run_averages)) {
                        $p["H{$h}Best"] = number_format(max($heat_selected_run_averages), 2);
                        
                        // Apply heat-specific scoring method to run averages
                        $heat_calculated_score = calculateHeatScore($heat_selected_run_averages, $heat_scoring_method);
                        
                        $p["H{$h}Average"] = number_format($heat_calculated_score, 2);
                        $filtered_heat_averages[] = $heat_calculated_score;
                    } else {
                        // No selected runs in this heat
                        $p["H{$h}Best"] = '-';
                        $p["H{$h}Average"] = '-';
                    }
                } else {
                    // No filter applied, use all run averages for this heat
                    if (!empty($heat_run_averages)) {
                        $p["H{$h}Best"] = number_format(max($heat_run_averages), 2);
                        
                        // Apply heat-specific scoring method to run averages
                        $heat_calculated_score = calculateHeatScore($heat_run_averages, $heat_scoring_method);
                        
                        $p["H{$h}Average"] = number_format($heat_calculated_score, 2);
                        $filtered_heat_averages[] = $heat_calculated_score;
                    } else {
                        $p["H{$h}Best"] = '-';
                        $p["H{$h}Average"] = '-';
                    }
                }
            }
            
            // Overall calculations - use heat averages, not individual scores
            if (!empty($column_filters['heat_run_filter'])) {
                // Filter is applied - calculate from heat averages of selected heats
                $filtered_heat_averages_for_overall = []; // For OverallAverage calculation
                
                // Collect average scores from selected heats only
                for ($h = 1; $h <= $heats_total; $h++) {
                    // Check if this heat has any selected runs
                    $heat_has_selected_runs = false;
                    if (isset($column_filters['heat_run_filter'][$h]) && !empty($column_filters['heat_run_filter'][$h])) {
                        $heat_has_selected_runs = true;
                    }
                    
                    if ($heat_has_selected_runs && isset($p["H{$h}Average"]) && $p["H{$h}Average"] !== '-') {
                        $filtered_heat_averages_for_overall[] = floatval($p["H{$h}Average"]);
                    }
                }
                
                // Calculate OverallAverage as the average of heat averages
                if (!empty($filtered_heat_averages_for_overall)) {
                    $overall_average = array_sum($filtered_heat_averages_for_overall) / count($filtered_heat_averages_for_overall);
                    $p["OverallAverage"] = number_format($overall_average, 2);
                } else {
                    $p["OverallAverage"] = '-';
                }
                
                if (!empty($filtered_heat_averages)) {
                    $p["HighestAverage"] = number_format(max($filtered_heat_averages), 2);
                } else {
                    $p["HighestAverage"] = '-';
                }
            } else {
                // No filter applied - use all heat averages
                $all_heat_averages_for_overall = [];
                
                for ($h = 1; $h <= $heats_total; $h++) {
                    if (isset($p["H{$h}Average"]) && $p["H{$h}Average"] !== '-') {
                        $all_heat_averages_for_overall[] = floatval($p["H{$h}Average"]);
                    }
                }
                
                // Calculate OverallAverage as the average of heat averages
                if (!empty($all_heat_averages_for_overall)) {
                    $overall_average = array_sum($all_heat_averages_for_overall) / count($all_heat_averages_for_overall);
                    $p["OverallAverage"] = number_format($overall_average, 2);
                } else {
                    $p["OverallAverage"] = '-';
                }
                
                if (!empty($filtered_heat_averages)) {
                    $p["HighestAverage"] = number_format(max($filtered_heat_averages), 2);
                } else {
                    $p["HighestAverage"] = '-';
                }
            }
        }
    }

    // Build header list based on filters and heat-specific run counts
    $all_headers = [];
    for ($h = 1; $h <= $heats_total; $h++) {
        // Get the actual number of runs for this heat from runs_count
        $heat_runs_count = $heat_settings[$h]['runs_count'] ?? $runs_per_heat;
        
        for ($r = 1; $r <= $heat_runs_count; $r++) { // Use heat-specific run count
            $all_headers[] = "H{$h}R{$r}";
            $all_headers[] = "H{$h}R{$r}_judges";
            $all_headers[] = "H{$h}R{$r}_control_points";
        }
        $all_headers[] = "H{$h}Average";
        $all_headers[] = "H{$h}Best";
    }
    $all_headers[] = 'OverallAverage'; // Changed from OverallBest
    $all_headers[] = 'HighestAverage';
    
    // Filter headers based on column filters
    $final_headers = array_filter($all_headers, function($header) use ($column_filters) {
        return shouldIncludeColumn($header, $column_filters);
    });


    return [
        'summary_data' => $summary_data,
        'final_headers' => array_values($final_headers), // Re-index array
        'all_headers' => $all_headers,
        'categories' => $categories_to_process, // Return the categories that were actually processed
        'heats_total' => $heats_total,
        'runs_per_heat' => $runs_per_heat,
        'heat_settings' => $heat_settings,
        'available_columns' => getAvailableColumns($heats_total, $runs_per_heat, $heat_settings)
    ];
}

// Function to calculate heat score based on scoring method
function calculateHeatScore($run_averages, $scoring_method) {
    if (empty($run_averages)) {
        return 0;
    }
    
    // Remove any non-numeric values and convert to floats
    $clean_averages = array_filter(array_map('floatval', $run_averages), function($val) {
        return $val > 0;
    });
    
    if (empty($clean_averages)) {
        return 0;
    }
    
    // Parse the scoring method
    if (preg_match('/^(best|average)_from_(\d+)$/', $scoring_method, $matches)) {
        $method = $matches[1]; // 'best' or 'average'
        $count = intval($matches[2]); // number of runs to consider
        
        // Sort run averages in descending order to get the highest scores first
        rsort($clean_averages);
        
        // Take only the specified number of highest run averages
        $selected_averages = array_slice($clean_averages, 0, $count);
        
        if ($method === 'best') {
            // Return the highest run average from the selected runs
            return max($selected_averages);
        } elseif ($method === 'average') {
            // Return the average of the selected run averages
            return array_sum($selected_averages) / count($selected_averages);
        }
    }
    
    // Handle legacy scoring methods and fallbacks
    switch ($scoring_method) {
        case 'best':
            return max($clean_averages);
        case 'sum':
            return array_sum($clean_averages);
        case 'average':
        default:
            return array_sum($clean_averages) / count($clean_averages);
    }
}

// Function to validate and normalize scoring method
function validateScoringMethod($method, $runs_count) {
    // Handle new format: best_from_N, average_from_N
    if (preg_match('/^(best|average)_from_(\d+)$/', $method, $matches)) {
        $type = $matches[1];
        $count = intval($matches[2]);
        
        // Ensure count doesn't exceed available runs
        if ($count > $runs_count) {
            $count = $runs_count;
        }
        
        return "{$type}_from_{$count}";
    }
    
    // Handle legacy methods
    $legacy_methods = ['best', 'average', 'sum'];
    if (in_array($method, $legacy_methods)) {
        return $method;
    }
    
    // Default fallback
    return 'average';
}

try {
    if (!$selected_event) {
        echo json_encode([
            'success' => false,
            'message' => 'No event selected'
        ]);
        exit;
    }

    // Parse column filters
    $column_filters = [
        'heat_run_filter' => parseHeatRunFilter($heat_run_filter),
        'heat_run_filter_json' => $heat_run_filter, // Add the raw JSON for heat detection
        'show_runs' => $show_runs === 'true',
        'show_judges' => $show_judges === 'true',
        'show_control_points' => $show_control_points === 'true',
        'show_heat_best' => $show_heat_best === 'true',
        'show_heat_average' => $show_heat_average === 'true',
        'show_overall_best' => $show_overall_best === 'true',
        'show_highest_average' => $show_highest_average === 'true'
    ];

    $data = generateSummaryTableData($pdo, $selected_event, $filter_category, $filter_gender, $sort_by, $column_filters);
    
    // NEW: Debug the raw data categories before any processing
    $raw_data_debug = [
        'categories_from_db' => array_keys($data['summary_data']),
        'sample_participant_categories' => []
    ];
    
    // Check the actual category values stored in participant data
    foreach ($data['summary_data'] as $category => $participants) {
        $sample_participant = reset($participants); // Get first participant
        if ($sample_participant) {
            $raw_data_debug['sample_participant_categories'][$category] = $sample_participant['category'];
        }
    }
    
    // Handle multiple categories when no specific category is requested
    if ($filter_category === 'all' && !empty($data['summary_data'])) {
        // Group by CATEGORY ONLY (not heat-category combinations)
        $heat_category_groups = [];
        $combined_participants = [];
        $processed_participants = []; // Track processed participants to prevent duplicates
        $debug_info = []; // Add debugging information
        $category_name_tracking = []; // NEW: Track category name changes
        
        foreach ($data['summary_data'] as $original_category => $participants) {
            // Track the original category name
            $category_name_tracking[$original_category] = [
                'original' => $original_category,
                'step_by_step' => []
            ];
            
            $debug_info[$original_category] = [
                'original_count' => count($participants),
                'processed_count' => 0,
                'skipped_duplicates' => 0
            ];
            
            // Use the EXACT original category name - no modifications
            $clean_category = $original_category; // Don't trim or modify
            $category_name_tracking[$original_category]['step_by_step'][] = "clean_category set to: '{$clean_category}'";
            
            // Initialize group for this category if it doesn't exist
            if (!isset($heat_category_groups[$clean_category])) {
                // Create a NEW array for this group (avoid reference issues)
                $heat_category_groups[$clean_category] = [
                    'group_name' => $clean_category,  // Use the exact original category name
                    'heat_number' => null, // Not tied to specific heat
                    'heat_name' => 'All Heats',
                    'category' => $clean_category,    // Use the exact original category name
                    'participants' => []
                ];
                $category_name_tracking[$original_category]['step_by_step'][] = "Group created with name: '{$clean_category}'";
            } else {
                // This should not happen - category already exists!
                $debug_info[$original_category]['duplicate_category_detected'] = true;
                $category_name_tracking[$original_category]['step_by_step'][] = "DUPLICATE DETECTED - skipping";
                continue; // Skip this duplicate category
            }
            
            foreach ($participants as $name => $participant_data) {
                $participant_bib = $participant_data['bib'];
                $participant_category = $participant_data['category'];
                
                // Track if participant category differs from the group category
                if ($participant_category !== $clean_category) {
                    $category_name_tracking[$original_category]['step_by_step'][] = "Participant category mismatch: participant='{$participant_category}', group='{$clean_category}'";
                }
                
                // Create a unique participant identifier to prevent duplicates
                $participant_unique_id = $participant_bib . '_' . $participant_category;
                
                // Skip if this participant has already been processed
                if (isset($processed_participants[$participant_unique_id])) {
                    $debug_info[$original_category]['skipped_duplicates']++;
                    continue;
                }
                
                // Mark this participant as processed
                $processed_participants[$participant_unique_id] = true;
                $debug_info[$original_category]['processed_count']++;
                
                // Add participant to their category group using the EXACT original category key
                $heat_category_groups[$clean_category]['participants'][$name] = $participant_data;
                
                // Also add to combined list with group information
                $combined_participants[$participant_unique_id] = array_merge($participant_data, [
                    'heat_group' => $clean_category,
                    'group_name' => $clean_category,
                    'original_key' => $name
                ]);
            }
            
            $category_name_tracking[$original_category]['step_by_step'][] = "Final group key: '{$clean_category}'";
            $category_name_tracking[$original_category]['final_key'] = $clean_category;
        }
        
        // Sort each group individually and ensure no duplicates within groups
        // IMPORTANT: Create a new array to avoid reference issues
        $sorted_heat_category_groups = [];
        foreach ($heat_category_groups as $clean_category => $group_data) {
            // Create a completely new array for this group to avoid reference issues
            $new_group_data = [
                'group_name' => $clean_category,  // Explicitly set to the key
                'heat_number' => null,
                'heat_name' => 'All Heats',
                'category' => $clean_category,    // Explicitly set to the key
                'participants' => []
            ];
            
            // Remove any potential duplicates within the group based on BIB
            $unique_participants = [];
            $seen_bibs = [];
            $duplicate_bibs_removed = 0;
            
            foreach ($group_data['participants'] as $name => $participant_data) {
                $bib = $participant_data['bib'];
                $unique_key = $bib . '_' . $participant_data['category']; // Use BIB + category as unique key
                
                if (!isset($seen_bibs[$unique_key])) {
                    $unique_participants[$name] = $participant_data;
                    $seen_bibs[$unique_key] = true;
                } else {
                    $duplicate_bibs_removed++;
                }
            }
            
            $new_group_data['participants'] = $unique_participants;
            $debug_info[$clean_category]['duplicate_bibs_removed'] = $duplicate_bibs_removed;
            $debug_info[$clean_category]['final_count'] = count($unique_participants);
            
            // Sort the group
            if ($sort_by !== 'none') {
                uasort($new_group_data['participants'], function($a, $b) use ($sort_by) {
                    $a_val = is_numeric($a[$sort_by] ?? 0) ? floatval($a[$sort_by]) : 0;
                    $b_val = is_numeric($b[$sort_by] ?? 0) ? floatval($b[$sort_by]) : 0;
                    return $b_val <=> $a_val;
                });
            }
            
            // Add to the new sorted array
            $sorted_heat_category_groups[$clean_category] = $new_group_data;
        }
        
        // Replace the original array with the sorted one
        $heat_category_groups = $sorted_heat_category_groups;
        
        // Remove empty groups
        $heat_category_groups_before_filter = count($heat_category_groups);
        $heat_category_groups = array_filter($heat_category_groups, function($group) {
            return !empty($group['participants']);
        });
        $heat_category_groups_after_filter = count($heat_category_groups);
        
        // Sort groups alphabetically by category name
        ksort($heat_category_groups);
        
        $participants = $combined_participants;
        $participant_count = count($participants);
        $categories_processed = array_keys($data['summary_data']);
        $grouped_by_heat_category = true;
        
        // Add debug information to response with enhanced category tracking
        $grouping_debug = [
            'original_categories_count' => count($data['summary_data']),
            'original_categories' => array_keys($data['summary_data']),
            'categories_processed' => array_keys($data['summary_data']),
            'final_groups_count' => count($heat_category_groups),
            'final_group_names' => array_keys($heat_category_groups),
            'final_group_details' => array_map(function($group) {
                return [
                    'group_name' => $group['group_name'],
                    'category' => $group['category'],
                    'participant_count' => count($group['participants'])
                ];
            }, $heat_category_groups),
            'groups_before_filter' => $heat_category_groups_before_filter,
            'groups_after_filter' => $heat_category_groups_after_filter,
            'category_details' => $debug_info,
            'category_name_tracking' => $category_name_tracking // NEW: detailed tracking
        ];
        
    } elseif ($filter_category !== 'all' && isset($data['summary_data'][$filter_category])) {
        // Single category processing
        $participants = $data['summary_data'][$filter_category];
        
        // Apply sorting
        if ($sort_by !== 'none') {
            uasort($participants, function($a, $b) use ($sort_by) {
                $a_val = is_numeric($a[$sort_by] ?? 0) ? floatval($a[$sort_by]) : 0;
                $b_val = is_numeric($b[$sort_by] ?? 0) ? floatval($b[$sort_by]) : 0;
                return $b_val <=> $a_val;
            });
        }
        
        $participant_count = count($participants);
        $categories_processed = [$filter_category];
        $grouped_by_heat_category = false;
        $grouping_debug = ['single_category_mode' => true];
        
    } else {
        echo json_encode([
            'success' => false,
            'message' => 'No data found for the specified criteria',
            'categories' => $data['categories'],
            'available_columns' => $data['available_columns'],
            'heat_settings' => $data['heat_settings'],
            'debug_info' => [
                'filter_category' => $filter_category,
                'categories_found' => array_keys($data['summary_data'] ?? []),
                'heat_run_filter' => $column_filters['heat_run_filter']
            ]
        ]);
        exit;
    }

    // Generate table HTML
    $table_head = '';
    $table_body = '';

    // Generate table header
    $table_head .= '<tr>';
    $table_head .= '<th scope="col"><span>Rank</span></th>';
    $table_head .= '<th scope="col"><span>BIB</span></th>';
    $table_head .= '<th scope="col"><span>Participant</span></th>';
    $table_head .= '<th scope="col"><span>Category</span></th>';
    $table_head .= '<th scope="col"><span>Club</span></th>';
    $table_head .= '<th scope="col"><span>Gender</span></th>';
    
    // Check if filters are applied for overall columns
    $has_heat_run_filter = !empty($column_filters['heat_run_filter']);
    
    foreach ($data['final_headers'] as $h) {
        $header_text = $h;
        $tooltip_text = '';
        
        
        
// Add filter indication to overall columns
        if ($has_heat_run_filter) {
            if ($h === 'OverallAverage') { // Changed from OverallBest
                $header_text = 'OverallAverage*';
                $tooltip_text = 'Average of heat averages - calculated based on selected heats/runs only';
            } elseif ($h === 'HighestAverage') {
                $header_text = 'HighestAverage*';
                $tooltip_text = 'Highest heat average - calculated based on selected heats/runs only';
            }
        }
        
        $table_head .= '<th scope="col" class="angle-th ' . getColClass($h) . '"' . 
                      ($tooltip_text ? ' title="' . htmlspecialchars($tooltip_text) . '"' : '') . 
                      '><span>' . htmlspecialchars($header_text) . '</span></th>';
    }
    $table_head .= '</tr>';

    // Generate table body
    $rank = 1;
    $current_group = null;
    
    if (isset($grouped_by_heat_category) && $grouped_by_heat_category) {
        // Generate grouped table with headers - group by category only
        $processed_categories = []; // Track which categories we've already output
        
        foreach ($heat_category_groups as $category => $group_data) {
            // Skip if we've already processed this category
            if (isset($processed_categories[$category])) {
                continue;
            }
            $processed_categories[$category] = true;
            
            // Add group header row
            $colspan = 6 + count($data['final_headers']);
            $table_body .= '<tr class="group-header-row">';
            $table_body .= '<td colspan="' . $colspan . '" class="group-header">';
            $table_body .= '<div class="group-header-content">';
            $table_body .= '<i class="fas fa-layer-group me-2"></i>';
            $table_body .= '<strong>' . htmlspecialchars($group_data['group_name']) . '</strong>';
            $table_body .= '<span class="participant-count ms-2">(' . count($group_data['participants']) . ' participants)</span>';
            $table_body .= '</div>';
            $table_body .= '</td>';
            $table_body .= '</tr>';
            
            // Add participants in this group (each participant appears only once per category)
            $group_rank = 1;
            $processed_participants_in_group = []; // Track participants within this group
            
            foreach ($group_data['participants'] as $participant_key => $runs) {
                // Create unique identifier for this participant
                $participant_unique_id = $runs['bib'] . '_' . $runs['category'];
                
                // Skip if we've already processed this participant in this group
                if (isset($processed_participants_in_group[$participant_unique_id])) {
                    continue;
                }
                $processed_participants_in_group[$participant_unique_id] = true;
                
                // Get the real participant name from the stored data
                $participant_name = '';
                if (isset($runs['participant_name']) && !empty($runs['participant_name'])) {
                    $participant_name = $runs['participant_name'];
                } elseif (isset($runs['first_name']) && isset($runs['last_name'])) {
                    $participant_name = trim($runs['first_name'] . ' ' . $runs['last_name']);
                } else {
                    // Extract name from the key if needed
                    $participant_name = $participant_key;
                }
                
                // If still empty, use BIB as fallback
                if (empty($participant_name) || $participant_name === ' ') {
                    $participant_name = "BIB " . ($runs['bib'] ?? 'Unknown');
                }
                
                $table_body .= '<tr class="group-participant-row" data-group="' . htmlspecialchars($category) . '">';
                $table_body .= '<td scope="row"><span class="group-rank">' . $group_rank++ . '</span></td>';
                $table_body .= '<td><span>' . htmlspecialchars($runs['bib'] ?? '') . '</span></td>';
                $table_body .= '<td><span>' . htmlspecialchars($participant_name) . '</span></td>';
                $table_body .= '<td><span>' . htmlspecialchars($runs['category'] ?? '') . '</span></td>';
                $table_body .= '<td><span>' . htmlspecialchars($runs['club'] ?? '-') . '</span></td>';
                $table_body .= '<td><span>' . htmlspecialchars($runs['gender'] ?? '-') . '</span></td>';
                
                foreach ($data['final_headers'] as $h) {
                    if (preg_match('/^H\d+R\d+_control_points$/', $h)) {
                        $control_points = $runs[$h] ?? [];
                        $table_body .= '<td class="' . getColClass($h) . '">';
                        
                        if (!empty($control_points)) {
                            $table_body .= '<div class="control-point-breakdown">';
                            foreach ($control_points as $cp_key => $cp_data) {
                                $table_body .= '<div class="control-point-group" title="' . htmlspecialchars($cp_data['name'] ?? 'General') . '">';
                                $table_body .= '<div class="control-point-header">';
                                $table_body .= '<span class="control-point-name">' . htmlspecialchars(substr($cp_data['name'] ?? 'General', 0, 10)) . '</span>';
                                $table_body .= '</div>';
                                $table_body .= '<div class="judges-in-control-point">';
                                
                                foreach ($cp_data['judges'] ?? [] as $judge_data) {
                                    $table_body .= '<div class="judge-score-item" title="' . htmlspecialchars($judge_data['judge_name'] ?? 'Judge ' . $judge_data['judge_id']) . '">';
                                    $table_body .= '<span class="judge-name">' . htmlspecialchars(substr($judge_data['judge_name'] ?? 'J' . $judge_data['judge_id'], 0, 6)) . '</span>:';
                                    $table_body .= '<span class="judge-score">' . number_format($judge_data['score'], 2) . '</span>';
                                    $table_body .= '</div>';
                                }
                                
                                $table_body .= '</div>';
                                $table_body .= '</div>';
                            }
                            $table_body .= '</div>';
                        } else {
                            $table_body .= '<span>-</span>';
                        }
                        
                        $table_body .= '</td>';
                    } elseif (preg_match('/^H\d+R\d+_judges$/', $h)) {
                        $judge_scores = $runs[$h] ?? [];
                        $table_body .= '<td class="' . getColClass($h) . '">';
                        
                        if (!empty($judge_scores)) {
                            $table_body .= '<div class="judge-breakdown">';
                            foreach ($judge_scores as $judge_data) {
                                $table_body .= '<div class="judge-score-item" title="' . htmlspecialchars($judge_data['judge_name'] ?? 'Judge ' . $judge_data['judge_id']) . '">';
                                $table_body .= '<span class="judge-name">' . htmlspecialchars(substr($judge_data['judge_name'] ?? 'J' . $judge_data['judge_id'], 0, 8)) . '</span>:';
                                $table_body .= '<span class="judge-score">' . number_format($judge_data['score'], 2) . '</span>';
                                $table_body .= '</div>';
                            }
                            $table_body .= '</div>';
                        } else {
                            $table_body .= '<span>-</span>';
                        }
                        
                        $table_body .= '</td>';
                    } else {
                        $table_body .= '<td class="' . getColClass($h) . '"><span>' . ($runs[$h] ?? '-') . '</span></td>';
                    }
                }
                $table_body .= '</tr>';
            }
        }
    } else {
        // Generate regular table without grouping
        $processed_participants = []; // Track processed participants for non-grouped mode
        
        foreach ($participants as $name => $runs) {
            // Create unique identifier for this participant
            $participant_unique_id = $runs['bib'] . '_' . $runs['category'];
            
            // Skip if we've already processed this participant
            if (isset($processed_participants[$participant_unique_id])) {
                continue;
            }
            $processed_participants[$participant_unique_id] = true;
            
            // Get the real participant name from the stored data
            $participant_name = '';
            if (isset($runs['first_name']) && isset($runs['last_name'])) {
                $participant_name = trim($runs['first_name'] . ' ' . $runs['last_name']);
            } elseif (isset($runs['participant_name'])) {
                $participant_name = $runs['participant_name'];
            } else {
                // Fallback to the key name if no specific name fields are available
                $participant_name = $name;
            }
            
            // If still empty, use BIB as fallback
            if (empty($participant_name) || $participant_name === ' ') {
                $participant_name = "BIB " . ($runs['bib'] ?? 'Unknown');
            }
            
            $table_body .= '<tr>';
            $table_body .= '<td scope="row"><span>' . $rank++ . '</span></td>';
            $table_body .= '<td><span>' . htmlspecialchars($runs['bib'] ?? '') . '</span></td>';
            $table_body .= '<td><span>' . htmlspecialchars($participant_name) . '</span></td>';
            $table_body .= '<td><span>' . htmlspecialchars($runs['category'] ?? '') . '</span></td>';
            $table_body .= '<td><span>' . htmlspecialchars($runs['club'] ?? '-') . '</span></td>';
            $table_body .= '<td><span>' . htmlspecialchars($runs['gender'] ?? '-') . '</span></td>';
            
            foreach ($data['final_headers'] as $h) {
                if (preg_match('/^H\d+R\d+_control_points$/', $h)) {
                    $control_points = $runs[$h] ?? [];
                    $table_body .= '<td class="' . getColClass($h) . '">';
                    
                    if (!empty($control_points)) {
                        $table_body .= '<div class="control-point-breakdown">';
                        foreach ($control_points as $cp_key => $cp_data) {
                            $table_body .= '<div class="control-point-group" title="' . htmlspecialchars($cp_data['name'] ?? 'General') . '">';
                            $table_body .= '<div class="control-point-header">';
                            $table_body .= '<span class="control-point-name">' . htmlspecialchars(substr($cp_data['name'] ?? 'General', 0, 10)) . '</span>';
                            $table_body .= '</div>';
                            $table_body .= '<div class="judges-in-control-point">';
                            
                            foreach ($cp_data['judges'] ?? [] as $judge_data) {
                                $table_body .= '<div class="judge-score-item" title="' . htmlspecialchars($judge_data['judge_name'] ?? 'Judge ' . $judge_data['judge_id']) . '">';
                                $table_body .= '<span class="judge-name">' . htmlspecialchars(substr($judge_data['judge_name'] ?? 'J' . $judge_data['judge_id'], 0, 6)) . '</span>:';
                                $table_body .= '<span class="judge-score">' . number_format($judge_data['score'], 2) . '</span>';
                                $table_body .= '</div>';
                            }
                            
                            $table_body .= '</div>';
                            $table_body .= '</div>';
                        }
                        $table_body .= '</div>';
                    } else {
                        $table_body .= '<span>-</span>';
                    }
                    
                    $table_body .= '</td>';
                } elseif (preg_match('/^H\d+R\d+_judges$/', $h)) {
                    $judge_scores = $runs[$h] ?? [];
                    $table_body .= '<td class="' . getColClass($h) . '">';
                    
                    if (!empty($judge_scores)) {
                        $table_body .= '<div class="judge-breakdown">';
                        foreach ($judge_scores as $judge_data) {
                            $table_body .= '<div class="judge-score-item" title="' . htmlspecialchars($judge_data['judge_name'] ?? 'Judge ' . $judge_data['judge_id']) . '">';
                            $table_body .= '<span class="judge-name">' . htmlspecialchars(substr($judge_data['judge_name'] ?? 'J' . $judge_data['judge_id'], 0, 8)) . '</span>:';
                            $table_body .= '<span class="judge-score">' . number_format($judge_data['score'], 2) . '</span>';
                            $table_body .= '</div>';
                        }
                        $table_body .= '</div>';
                    } else {
                        $table_body .= '<span>-</span>';
                    }
                    
                    $table_body .= '</td>';
                } else {
                    $table_body .= '<td class="' . getColClass($h) . '"><span>' . ($runs[$h] ?? '-') . '</span></td>';
                }
            }
            $table_body .= '</tr>';
        }
    }
// Determine if filtered calculations are being used
    $using_filtered_calculations = !empty($column_filters['heat_run_filter']);
    
// Generate filter summary for display
$filter_summary = '';
$total_selected_heats = 0;
if ($using_filtered_calculations) {
    $selected_combinations = [];
    foreach ($column_filters['heat_run_filter'] as $heat => $runs) {
        if (!empty($runs)) {
            $total_selected_heats++;
            $selected_combinations[] = "Heat {$heat} (Runs: " . implode(',', $runs) . ")";
        }
    }
    $filter_summary = "Filtered calculations using {$total_selected_heats} heat(s): " . implode(' | ', $selected_combinations);
} else {
    $filter_summary = "All available heats and runs included in calculations";
}
echo json_encode([ 
    'success' => true,
    'message' => $filter_category === 'all' ? 
        (isset($grouped_by_heat_category) && $grouped_by_heat_category ? 
            'Results grouped by categories' : 
            'Combined results from heat-assigned categories') : 
        'Single category results',
    'table_head' => $table_head,
    'table_body' => $table_body,
    'category' => $filter_category,
    'categories_processed' => $categories_processed ?? [$filter_category],
    'participant_count' => $participant_count,
    'headers' => $data['final_headers'],
    'all_headers' => $data['all_headers'],
    'available_columns' => $data['available_columns'],
    'heat_settings' => $data['heat_settings'],
    'applied_filters' => $column_filters,
    'using_filtered_calculations' => $using_filtered_calculations,
    'filter_summary' => $filter_summary,
    'total_heats_available' => $data['heats_total'],
    'total_heats_selected' => $total_selected_heats ?? $data['heats_total'],
    'calculation_note' => $using_filtered_calculations ? 
        'Heat Best, Heat Average, Overall Average, and Highest Average are calculated using only selected heats/runs' : 
        'All calculations include all available heats and runs',
    'heat_category_detection' => [
        'method' => $filter_category === 'all' ? 'heat_settings_based' : 'explicit_category',
        'heat_detected' => $filter_category === 'all' && !empty($column_filters['heat_run_filter']) ? array_keys($column_filters['heat_run_filter'])[0] ?? null : null
    ],
    
    // Enhanced grouping information with debugging
    'is_grouped' => isset($grouped_by_heat_category) && $grouped_by_heat_category,
    'heat_category_groups' => isset($heat_category_groups) ? array_map(function($group, $key) {
        $verification_status = 'OK';
        if ($group['group_name'] !== $key) {
            $verification_status = 'MISMATCH_GROUP_NAME';
        }
        if ($group['category'] !== $key) {
            $verification_status = 'MISMATCH_CATEGORY';
        }
        
        return [
            'group_name' => $group['group_name'],
            'heat_number' => $group['heat_number'],
            'heat_name' => $group['heat_name'],
            'category' => $group['category'],
            'participant_count' => count($group['participants']),
            'verification_status' => $verification_status,
            'original_key' => $key
        ];
    }, $heat_category_groups, array_keys($heat_category_groups)) : [],
    'group_count' => isset($heat_category_groups) ? count($heat_category_groups) : 0,
    
    // Enhanced debug information
    'grouping_debug' => $grouping_debug ?? [],
    'raw_summary_data_categories' => array_keys($data['summary_data'] ?? []),
    'raw_summary_data_counts' => array_map(function($participants) {
        return count($participants);
    }, $data['summary_data'] ?? []),
    
    // NEW: Raw data debugging
    'raw_data_debug' => $raw_data_debug ?? [],
    
    // NEW: Enhanced heat settings debug
    'heat_settings_debug' => [
        'default_runs_per_heat' => $data['runs_per_heat'],
        'heat_specific_runs' => array_map(function($setting) {
            $scoring_method = $setting['runs_scoring_method'];
            $parsed_method = [];
            
            // Parse scoring method for debugging
            if (preg_match('/^(best|average)_from_(\d+)$/', $scoring_method, $matches)) {
                $parsed_method = [
                    'type' => $matches[1],
                    'count' => intval($matches[2]),
                    'description' => "Take {$matches[1]} score from top {$matches[2]} runs"
                ];
            } else {
                $parsed_method = [
                    'type' => $scoring_method,
                    'count' => 'all',
                    'description' => "Legacy method: {$scoring_method}"
                ];
            }
            
            return [
                'name' => $setting['name'],
                'runs_count' => $setting['runs_count'],
                'runs_total' => $setting['runs_total'] ?? 'not_set',
                'is_active' => $setting['is_active'],
                'scoring_method_raw' => $scoring_method,
                'scoring_method_parsed' => $parsed_method
            ];
        }, $data['heat_settings']),
        'filter_applied' => !empty($_GET['heat_run_filter']),
        'auto_generated_filter' => empty($_GET['heat_run_filter']) ? $column_filters['heat_run_filter'] : null,
        'runs_count_source' => 'event_heat_settings.runs_count'
    ],
    
    // NEW: Table generation debug info
    'table_generation_debug' => [
        'heat_category_groups_keys' => isset($heat_category_groups) ? array_keys($heat_category_groups) : [],
        'heat_category_groups_count' => isset($heat_category_groups) ? count($heat_category_groups) : 0,
        'table_body_contains_duplicate_check' => substr_count($table_body, 'testowa, senior female') > 1 ? 'DUPLICATE_DETECTED' : 'NO_DUPLICATES',
        'table_groups_generated' => substr_count($table_body, 'group-header-row'),
    ],
    
    // NEW: Category name verification
    'category_verification' => isset($heat_category_groups) ? array_map(function($group, $key) {
        return [
            'key' => $key,
            'group_name' => $group['group_name'],
            'category' => $group['category'],
            'names_match' => $group['group_name'] === $key && $group['category'] === $key
        ];
    }, $heat_category_groups, array_keys($heat_category_groups)) : [],
    
    // NEW: Enhanced heat scoring debug
    'heat_scoring_debug' => isset($heat_category_groups) ? array_map(function($group) use ($data) {
        $sample_participant = reset($group['participants']);
        if (!$sample_participant) return [];
        
        $scoring_breakdown = [];
        foreach ($data['heat_settings'] as $heat_num => $heat_setting) {
            $heat_scoring = [
                'heat_number' => $heat_num,
                'heat_name' => $heat_setting['name'],
                'scoring_method' => $heat_setting['runs_scoring_method'],
                'runs_count' => $heat_setting['runs_count'],
                'run_averages_found' => [],
                'heat_best' => $sample_participant["H{$heat_num} Best"] ?? 'not_calculated',
                'heat_average' => $sample_participant["H{$heat_num} Average"] ?? 'not_calculated'
            ];
            
            // Show which run averages were found for this heat
            for ($r = 1; $r <= $heat_setting['runs_count']; $r++) {
                $code = "H{$heat_num}R{$r}";
                if (isset($sample_participant[$code]) && $sample_participant[$code] !== '-') {
                    $heat_scoring['run_averages_found'][$code] = $sample_participant[$code];
                }
            }
            
            $scoring_breakdown[$heat_num] = $heat_scoring;
        }
        
        return [
            'group_name' => $group['group_name'],
            'sample_participant_bib' => $sample_participant['bib'] ?? 'unknown',
            'scoring_breakdown' => $scoring_breakdown
        ];
    }, $heat_category_groups) : [],
]);} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage(),
        'error_details' => [
            'file' => $e->getFile(),
            'line' => $e->getLine(),
            'trace' => $e->getTraceAsString()
        ]
    ]);
}
?>