<?php
/**
 * Summary Table API
 * 
 * Generates scoring summary tables with flexible filtering options
 * 
 * Example Usage:
 * - Heat-specific: /api/summary_table_api.php?event_id=3&category=all&heat_run_filter={"2":[1,2]}
 * - Single category: /api/summary_table_api.php?event_id=3&category=female%20junior&heat_run_filter={"2":[1,2]}
 * - All categories: /api/summary_table_api.php?event_id=3&category=all
 */
require_once '../includes/dompdf/autoload.inc.php';
use Dompdf\Dompdf;
use Dompdf\Options;
header('Content-Type: application/json; charset=utf-8');
include '../includes/db.php';
include '../includes/participant_colors.php';

// Set UTF-8 encoding for multibyte string functions
mb_internal_encoding('UTF-8');

/**
 * Convert mixed array items to safe strings for display, handling deeply nested arrays
 */
function convertArrayItemsToString($items) {
    if (!is_array($items)) {
        return [(string)$items];
    }
    
    $result = [];
    foreach ($items as $item) {
        if (is_array($item)) {
            // Recursively handle nested arrays
            $result[] = convertNestedArrayToString($item);
        } else {
            $result[] = (string)$item;
        }
    }
    return $result;
}

/**
 * Recursively convert nested arrays to a safe string representation
 */
function convertNestedArrayToString($array, $delimiter = '|') {
    if (!is_array($array)) {
        return (string)$array;
    }
    
    $result = [];
    foreach ($array as $item) {
        if (is_array($item)) {
            // Handle deeper nesting
            $result[] = convertNestedArrayToString($item, ',');
        } else {
            $result[] = (string)$item;
        }
    }
    return implode($delimiter, $result);
}

/**
 * Format figures data for display with proper category mapping
 */
function formatFiguresDisplay($figures_data) {
    $figures_display = [];
    $figures_found = false;
    
    if (!is_array($figures_data)) {
        return ['display' => $figures_display, 'found' => $figures_found];
    }
    
    foreach ($figures_data as $category => $items) {
        // Skip validation data - only show figures
        if ($category === 'validation') {
            continue;
        }
        
        // If this is a figures object, iterate through its properties
        if ($category === 'figures' && is_array($items)) {
            foreach ($items as $figure_category => $figure_items) {
                if (is_array($figure_items) && !empty($figure_items)) {
                    // Map figure categories to display labels
                    $category_map = [
                        'Rotation' => 'Rot',
                        'Direction' => 'Dir', 
                        'Axis' => 'Axi',
                        'Grab' => 'Gra',
                        'Feature' => 'Fea',
                        'Rail' => 'Rai'
                    ];
                    
                    $display_category = $category_map[$figure_category] ?? $figure_category;
                    
                    $figures_display[] = '<div class="figure-category" style="display: inline-block;">';
                    $figures_display[] = '<strong>' . htmlspecialchars($display_category) . ':</strong>';
                    
                    // Convert items to strings, handling nested arrays
                    $string_items = convertArrayItemsToString($figure_items);
                    
                    // Additional safety check - filter out any remaining arrays
                    $safe_items = [];
                    foreach ($string_items as $item) {
                        if (is_array($item)) {
                            $safe_items[] = convertNestedArrayToString($item);
                        } else {
                            $safe_items[] = (string)$item;
                        }
                    }
                    
                    $figures_display[] = htmlspecialchars(implode(', ', $safe_items));
                    $figures_display[] = ', </div>';
                    $figures_found = true;
                }
            }
            continue;
        }

        if (is_array($items) && !empty($items)) {
            // Handle legacy format or other categories
            $category = @trim((string)$category);
            $figures_display[] = '<div class="figure-category" style="display: inline-block;">';
            $figures_display[] = '<strong>' . htmlspecialchars($category) . ':</strong>';
            
            // Convert items to strings, handling nested arrays
            $string_items = convertArrayItemsToString($items);
            
            // Additional safety check - filter out any remaining arrays
            $safe_items = [];
            foreach ($string_items as $item) {
                if (is_array($item)) {
                    $safe_items[] = convertNestedArrayToString($item);
                } else {
                    $safe_items[] = (string)$item;
                }
            }
            
            $figures_display[] = htmlspecialchars(implode(', ', $safe_items));
            $figures_display[] = ', </div>';
            $figures_found = true;
        }
    }
    
    return ['display' => $figures_display, 'found' => $figures_found];
}

/**
 * Get event format settings for display
 */
function getEventFormatSettings($pdo, $event_id, $show_format_name = true, $show_judge_info = true, $show_scale_info = true, $show_drop_rule = true, $show_mode_config = true, $show_heat_info = true) {
    try {
        // First get the event to find the scoring_format
        $event_stmt = $pdo->prepare("SELECT name, scoring_format, heats_total, runs_per_heat FROM events WHERE id = ?");
        $event_stmt->execute([$event_id]);
        $event_data = $event_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$event_data || empty($event_data['scoring_format'])) {
            return [];
        }
        
        $settings = [];
        
        // Load format details with judge configuration (same as event_dashboard.php)
        $format_stmt = $pdo->prepare("
            SELECT sf.*, 
                   sfj.min_judges, sfj.max_judges, sfj.scale_min, sfj.scale_max, 
                   sfj.scale_type, sfj.scale_custom, sfj.precision_decimal, sfj.drop_rule
            FROM scoring_formats sf
            LEFT JOIN scoring_format_judges sfj ON sf.format_id = sfj.format_id
            WHERE sf.format_id = ?
        ");
        $format_stmt->execute([$event_data['scoring_format']]);
        $formatSettings = $format_stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($formatSettings) {
            // Format name and description
            if ($show_format_name && $formatSettings['name']) {
                $format_display = $formatSettings['name'];
                if ($formatSettings['mode']) {
                    $format_display .= ' (' . ucfirst($formatSettings['mode']) . ')';
                }
                $settings[] = ['label' => 'Format', 'value' => $format_display];
            }
            
            // Judge configuration
            if ($show_judge_info && $formatSettings['min_judges']) {
                $judge_info = $formatSettings['min_judges'];
                if ($formatSettings['max_judges'] && $formatSettings['max_judges'] != $formatSettings['min_judges']) {
                    $judge_info .= '-' . $formatSettings['max_judges'];
                }
                $judge_info .= ' Judges';
                $settings[] = ['label' => 'Judges', 'value' => $judge_info];
            }
            
            // Scoring scale
            if ($show_scale_info && $formatSettings['scale_type']) {
                if ($formatSettings['scale_type'] === 'numeric') {
                    $scale_info = $formatSettings['scale_min'] . '-' . $formatSettings['scale_max'];
                    if ($formatSettings['precision_decimal'] > 0) {
                        $scale_info .= ' (' . $formatSettings['precision_decimal'] . ' decimal)';
                    }
                } else {
                    $scale_info = $formatSettings['scale_custom'] ?? 'Custom';
                }
                $settings[] = ['label' => 'Scale', 'value' => $scale_info];
            }
            
            // Drop rule (using same function logic as event_dashboard.php)
            if ($show_drop_rule && $formatSettings['drop_rule']) {
                $dropRuleNames = [
                    'none' => 'None',
                    'highest' => 'Drop Highest',
                    'lowest' => 'Drop Lowest', 
                    'highest_and_lowest' => 'Drop High/Low',
                    'median' => 'Median Score'
                ];
                $drop_rule_name = $dropRuleNames[$formatSettings['drop_rule']] ?? ucfirst(str_replace('_', ' ', $formatSettings['drop_rule']));
                $settings[] = ['label' => 'Drop Rule', 'value' => $drop_rule_name];
            }
            
            // Load mode configuration (same as event_dashboard.php)
            $mode_stmt = $pdo->prepare("
                SELECT config_type, config_key, config_value
                FROM scoring_format_mode_config
                WHERE format_id = ?
                ORDER BY config_type, config_key
            ");
            $mode_stmt->execute([$event_data['scoring_format']]);
            $modeConfigs = [];
            foreach ($mode_stmt->fetchAll(PDO::FETCH_ASSOC) as $config) {
                $modeConfigs[$config['config_type']][$config['config_key']] = $config['config_value'];
            }
            
            // Add key mode configurations to settings
            if ($show_mode_config) {
                foreach ($modeConfigs as $configType => $configs) {
                    $config_items = [];
                    foreach ($configs as $key => $value) {
                        $config_items[] = str_replace('_', ' ', $key) . ': ' . $value;
                    }
                    if (!empty($config_items)) {
                        $settings[] = ['label' => str_replace('_', ' ', ucwords($configType)), 'value' => implode(', ', $config_items)];
                    }
                }
            }
        }
        
        // Heat and run configuration
        if ($show_heat_info) {
            $settings[] = ['label' => 'Heats', 'value' => $event_data['heats_total'] ?? '2'];
            $settings[] = ['label' => 'Runs per Heat', 'value' => $event_data['runs_per_heat'] ?? '2'];
        }
        
        // If we have no other settings, add basic event info
        if (count($settings) <= 2) {
            $settings[] = ['label' => 'Event', 'value' => $event_data['name'] ?? 'Event #' . $event_id];
        }
        
        return $settings;
        
    } catch (Exception $e) {
        return [];
    }
}

/**
 * Extract clean participant data from HTML table for TV overlay
 */
function extractCleanParticipantData($tableBodyHtml) {
    $participants = [];
    
    // Create DOMDocument to parse HTML with proper UTF-8 encoding
    $dom = new DOMDocument();
    $dom->encoding = 'UTF-8';
    
    // Load HTML with UTF-8 encoding and proper flags
    libxml_use_internal_errors(true);
    $dom->loadHTML('<?xml encoding="UTF-8">' . '<table>' . $tableBodyHtml . '</table>', 
                   LIBXML_HTML_NOIMPLIED | LIBXML_HTML_NODEFDTD | LIBXML_NOCDATA);
    libxml_clear_errors();
    
    $xpath = new DOMXPath($dom);
    
    // Find participant rows (both grouped and single category formats)
    $rows = $xpath->query('//tr[@data-approved_at] | //tr[contains(@class, "group-participant-row")]');
    
    foreach ($rows as $index => $row) {
        $cells = $xpath->query('.//td', $row);
        
        if ($cells->length >= 5) {
            // Extract rank
            $rankNode = $xpath->query('.//span[contains(@class, "group-rank")]', $cells->item(0))->item(0);
            $rank = $rankNode ? (int)trim($rankNode->textContent) : $index + 1;
            
            // Extract BIB
            $bibNode = $xpath->query('.//span[contains(@class, "badge")]', $cells->item(1))->item(0);
            $bib = $bibNode ? trim($bibNode->textContent) : '';
            
            // Extract name with proper UTF-8 handling
            $nameNode = $xpath->query('.//strong', $cells->item(2))->item(0);
            $name = $nameNode ? mb_convert_encoding(trim($nameNode->textContent), 'UTF-8', 'UTF-8') : 'Unknown';
            
            // Extract club with proper UTF-8 handling
            $clubNode = $xpath->query('.//span[contains(@class, "badge")]', $cells->item(3))->item(0);
            $club = $clubNode ? mb_convert_encoding(trim($clubNode->textContent), 'UTF-8', 'UTF-8') : '';
            
            // Extract score (last cell, usually the best score)
            $lastCell = $cells->item($cells->length - 1);
            $scoreNode = $xpath->query('.//span', $lastCell)->item(0);
            $scoreText = $scoreNode ? trim($scoreNode->textContent) : '0.00';
            $score = is_numeric($scoreText) ? (float)$scoreText : 0.00;
            
            $participants[] = [
                'rank' => $rank,
                'bib' => $bib,
                'name' => $name,
                'club' => $club,
                'score' => $score,
                'formatted_score' => number_format($score, 2)
            ];
        }
    }
    
    return $participants;
}

// Configuration - Easy to change settings
$config = [
    'debug_mode' => true, // Set to true for detailed debug output
    'default_runs_per_heat' => 2,
    'default_heats_total' => 2,
    'basic_columns' => ['Rank', 'BIB', 'Participant', 'Category', 'Club', 'Gender', 'fis_code', 'licence_code', 'country'],
    'table_classes' => [
        'heat_best' => 'col-best table-primary',
        'heat_average' => 'col-average table-secondary', 
        'overall_average' => 'col-overall-average table-success',
        'highest_average' => 'col-highest-average table-info',
        'control_points' => 'col-control-points table-warning',
        'judges' => 'col-judges table-light',
        'figures' => 'col-figures table-light'
    ]
    ];

// Get and validate parameters
$selected_event = (int)($_GET['event_id'] ?? 0);
$filter_category = $_GET['category'] ?? 'all';
$filter_category_id = isset($_GET['category_id']) ? (int)$_GET['category_id'] : null;
$filter_gender = $_GET['gender'] ?? 'all';
$sort_by = $_GET['sort'] ?? 'OverallAverage';
$sort_direction = $_GET['sort_direction'] ?? 'desc'; // Add sort direction
$heat_run_filter = $_GET['heat_run_filter'] ?? '{}';
$summary_title = '';
// If category_id is provided, look up the category name
if ($filter_category_id && $filter_category === 'all') {
    $cat_stmt = $pdo->prepare("SELECT category_name FROM event_categories WHERE id = ? AND event_id = ? LIMIT 1");
    $cat_stmt->execute([$filter_category_id, $selected_event]);
    $category_name = $cat_stmt->fetchColumn();
    
    if ($category_name) {
        $filter_category = $category_name;
    }
}

// Column visibility flags
$show_runs = ($_GET['show_runs'] ?? 'true') === 'true';
$show_judges = ($_GET['show_judges'] ?? 'true') === 'true';
$show_control_points = ($_GET['show_control_points'] ?? 'true') === 'true';
$show_figures = ($_GET['show_figures'] ?? 'false') === 'true';
$show_heat_best = ($_GET['show_heat_best'] ?? 'true') === 'true';
$show_heat_average = ($_GET['show_heat_average'] ?? 'true') === 'true';
$show_overall_best = ($_GET['show_overall_best'] ?? 'false') === 'true';
$show_highest_average = ($_GET['show_highest_average'] ?? 'false') === 'true';
$show_event_settings = ($_GET['show_event_settings'] ?? 'false') === 'true';

// Event settings detail visibility
$show_format_name = ($_GET['show_format_name'] ?? 'true') === 'true';
$show_judge_info = ($_GET['show_judge_info'] ?? 'true') === 'true';
$show_scale_info = ($_GET['show_scale_info'] ?? 'true') === 'true';
$show_drop_rule = ($_GET['show_drop_rule'] ?? 'true') === 'true';
$show_mode_config = ($_GET['show_mode_config'] ?? 'true') === 'true';
$show_heat_info = ($_GET['show_heat_info'] ?? 'true') === 'true';

// NEW: Basic column visibility flags (all default to true except specialized codes)
$show_rank = ($_GET['show_rank'] ?? 'true') === 'true';
$show_bib = ($_GET['show_bib'] ?? 'true') === 'true';
$show_participant = ($_GET['show_participant'] ?? 'true') === 'true';
$show_category = ($_GET['show_category'] ?? 'true') === 'true';
$show_club = ($_GET['show_club'] ?? 'true') === 'true';
$show_gender = ($_GET['show_gender'] ?? 'true') === 'true';
$show_fis_code = ($_GET['show_fis_code'] ?? 'false') === 'true';
$show_licence_code = ($_GET['show_licence_code'] ?? 'false') === 'true';
$show_country = ($_GET['show_country'] ?? 'false') === 'true';

// Column rename parameters
$column_renames = [];
$rename_keys = [
    'rename_rank', 'rename_bib', 'rename_participant', 'rename_category', 'rename_club',
    'rename_gender', 'rename_fis_code', 'rename_licence_code', 'rename_country',
    'rename_runs', 'rename_judges', 'rename_control_points', 'rename_figures',
    'rename_heat_best', 'rename_heat_average', 'rename_overall_best', 'rename_highest_average'
];

foreach ($rename_keys as $key) {
    if (isset($_GET[$key]) && !empty($_GET[$key])) {
        $column_key = str_replace('rename_', '', $key);
        $column_renames[$column_key] = $_GET[$key];
    }
}

if (isset($_GET['action']) && $_GET['action'] === 'get_participants_to_promote') {
    $event_id = (int)($_GET['event_id'] ?? 0);
    $target_heat_number = (int)($_GET['target_heat_number'] ?? 0);
    $result = getParticipantsToPromote($pdo, $event_id, $target_heat_number);
    //header('Content-Type: application/json');
    echo json_encode($result, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}
if (isset($_GET['action']) && $_GET['action'] === 'promote') {
    $event_id = (int)($_GET['event_id'] ?? 0);
    $target_heat = (int)($_GET['target_heat'] ?? 0);

    // 1. Get participants to promote
    $promote_result = getParticipantsToPromote($pdo, $event_id, $target_heat);

    if (empty($promote_result['success']) || empty($promote_result['participants'])) {
        echo json_encode([
            'status' => 'error',
            'message' => $promote_result['message'] ?? 'No participants to promote.'
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        exit;
    }

    // 2. Prepare participant IDs and sort_order for copy logic
    $ep_ids = [];
    foreach ($promote_result['participants'] as $ep_row) {
        if (isset($ep_row['id'])) {
            // Store as associative array with both id and sort_order
            $ep_ids[] = [
                'id' => $ep_row['id'],
                'sort_order' => $ep_row['sort_order'] ?? null
            ];
        }
    }

    if (empty($ep_ids)) {
        echo json_encode([
            'status' => 'error',
            'message' => 'No valid event_participant IDs found for promotion.'
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        exit;
    }

    // 3. Run the "copy" logic from ajax_move_to_heat.php (inline here for API use)
    try {
        $pdo->beginTransaction();

        // Extract just the IDs for validation and selection
        $id_list = array_column($ep_ids, 'id');
        $placeholders = implode(',', array_fill(0, count($id_list), '?'));

        // Validate that all event_participant IDs belong to the specified event
        $validate_stmt = $pdo->prepare("
            SELECT COUNT(*) 
            FROM event_participants 
            WHERE id IN ($placeholders) AND event_id = ?
        ");
        $validate_stmt->execute(array_merge($id_list, [$event_id]));
        
        if ($validate_stmt->fetchColumn() != count($id_list)) {
            throw new Exception('Some participants do not belong to this event.');
        }

        // Copy: Create new records for the target heat
        $select_stmt = $pdo->prepare("
            SELECT id, participant_id, bib_number, category_id, category, matching_category_ids
            FROM event_participants 
            WHERE id IN ($placeholders) AND event_id = ?
        ");
        $select_stmt->execute(array_merge($id_list, [$event_id]));
        $participants_data = $select_stmt->fetchAll(PDO::FETCH_ASSOC);

        $insert_stmt = $pdo->prepare("
                INSERT INTO event_participants (event_id, participant_id, heat_number, bib_number, category_id, category, matching_category_ids, sort_order)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ");

        $copied_count = 0;
        // Build a map of id => sort_order for quick lookup
        $id_to_sort_order = [];
        foreach ($ep_ids as $row) {
            $id_to_sort_order[$row['id']] = $row['sort_order'] ?? null;
        }

        foreach ($participants_data as $participant) {
            $sort_order = $id_to_sort_order[$participant['id']] ?? null;

            // Check if participant already exists in target heat
            $exists_stmt = $pdo->prepare("
                SELECT COUNT(*) FROM event_participants 
                WHERE event_id = ? AND participant_id = ? AND heat_number = ?
            ");
            $exists_stmt->execute([$event_id, $participant['participant_id'], $target_heat]);

            if ($exists_stmt->fetchColumn() == 0) {
                $insert_stmt->execute([
                    $event_id,
                    $participant['participant_id'],
                    $target_heat,
                    $participant['bib_number'],
                    $participant['category_id'],
                    $participant['category'],
                    $participant['matching_category_ids'],
                    $sort_order // Use the rank as sort_order
                ]);
                $copied_count++;
            }
        }

        $pdo->commit();

        $message = "Successfully promoted {$copied_count} participants to Heat {$target_heat}.";
        if ($copied_count < count($participants_data)) {
            $message .= " " . (count($participants_data) - $copied_count) . " participants were already in the target heat.";
        }

        echo json_encode([
            'status' => 'success', 
            'message' => $message
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    } catch (Exception $e) {
        $pdo->rollBack();
        echo json_encode(['status' => 'error', 'message' => $e->getMessage()], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    }
    exit;
}
// Function to load configuration from result_configurations
    function loadConfigurationById($pdo, $config_id) {
        $stmt = $pdo->prepare("SELECT id, event_id, name, category, heat_number, configuration FROM result_configurations WHERE id = ? LIMIT 1");
        $stmt->execute([$config_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$row) {
            return null;
        }
        $summary_title = $row['name'];
        // Parse configuration JSON
        $config = [];
        if (!empty($row['configuration'])) {
            $decoded = json_decode($row['configuration'], true);
            if (is_array($decoded)) {
                $config = $decoded;
            }
        }
        
        return [
            'id' => $row['id'],
            'event_id' => $row['event_id'],
            'category' => $row['category'],
            'heat_number' => $row['heat_number'],
            //'summary_title' => $row['name'],
            'settings' => $config
        ];
    }

    // Apply configuration from config_id if provided
    $applied_config_id = 0;
    if (!empty($_GET['config_id'])) {
        $config_id = (int)$_GET['config_id'];
        $config_data = loadConfigurationById($pdo, $config_id);
        //print_r($config_data);
        if ($config_data) {
            $applied_config_id = $config_id;
            $config_settings = $config_data['settings'];
            
            // Override event_id and category from configuration
            if (!empty($config_data['event_id'])) {
                $selected_event = (int)$config_data['event_id'];
            }
            
            if (isset($config_data['category']) && $config_data['category'] !== '') {
                $filter_category = $config_data['category'] !== 'all' ? $config_data['category'] : 'all';
            }
            
            //$summary_title = $config_settings['summaryTitle'] ?? '';
            // Apply column visibility settings
            $show_runs = $config_settings['showRuns'] ?? $show_runs;
            $show_judges = $config_settings['showJudges'] ?? $show_judges;
            $show_control_points = $config_settings['showControlPoints'] ?? $show_control_points;
            $show_figures = $config_settings['showFigures'] ?? $show_figures;
            $show_event_settings = $config_settings['showEventSettings'] ?? $show_event_settings;
            $show_heat_best = $config_settings['showHeatBest'] ?? $show_heat_best;
            $show_heat_average = $config_settings['showHeatAverage'] ?? $show_heat_average;
            $show_overall_best = $config_settings['showOverallBest'] ?? $show_overall_best;
            $show_highest_average = $config_settings['showOverallBest'] ?? $show_highest_average;
            
            // NEW: Apply basic column visibility settings
            $show_rank = $config_settings['showRank'] ?? $show_rank;
            $show_bib = $config_settings['showBIB'] ?? $show_bib;
            $show_participant = $config_settings['showParticipant'] ?? $show_participant;
            $show_category = $config_settings['showCategory'] ?? $show_category;
            $show_club = $config_settings['showClub'] ?? $show_club;
            $show_gender = $config_settings['showGender'] ?? $show_gender;
            $show_fis_code = $config_settings['showFisCode'] ?? $show_fis_code;
            $show_licence_code = $config_settings['showLicenceCode'] ?? $show_licence_code;
            $show_country = $config_settings['showCountryCode'] ?? $show_country;
            
            // Apply sorting settings
            $sort_by = $config_settings['sortBy'] ?? $sort_by;
            $sort_direction = $config_settings['sortDirection'] ?? $sort_direction;
            
            // Apply gender filter
            $filter_gender = $config_settings['genderFilter'] ?? $filter_gender;
            
            // Apply event settings detail visibility
            $show_format_name = $config_settings['showFormatName'] ?? $show_format_name;
            $show_judge_info = $config_settings['showJudgeInfo'] ?? $show_judge_info;
            $show_scale_info = $config_settings['showScaleInfo'] ?? $show_scale_info;
            $show_drop_rule = $config_settings['showDropRule'] ?? $show_drop_rule;
            $show_mode_config = $config_settings['showModeConfig'] ?? $show_mode_config;
            $show_heat_info = $config_settings['showHeatInfo'] ?? $show_heat_info;
            
            // Apply column renames from configuration
            if (isset($config_settings['columnRenames']) && is_array($config_settings['columnRenames'])) {
                $column_renames = array_merge($column_renames, $config_settings['columnRenames']);
            }
            
            // Apply heat/run filter if heat_number is specified
            if (!empty($config_data['heat_number'])) {
                $heat_number = (int)$config_data['heat_number'];
                
                // Get runs_count for this heat
                $runs_stmt = $pdo->prepare("SELECT runs_count FROM event_heat_settings WHERE event_id = ? AND heat_number = ?");
                $runs_stmt->execute([$config_data['event_id'], $heat_number]);
                $runs_count = (int)$runs_stmt->fetchColumn();
                
                if ($runs_count <= 0) {
                    // Fallback to event's runs_per_heat
                    $event_stmt = $pdo->prepare("SELECT runs_per_heat FROM events WHERE id = ?");
                    $event_stmt->execute([$config_data['event_id']]);
                    $runs_count = (int)$event_stmt->fetchColumn() ?: 3;
                }
                
                // Build heat_run_filter with all runs for this heat
                $heat_run_filter = json_encode([
                    $heat_number => range(1, $runs_count)
                ]);
            }
        }
    }

// 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) {
    global $config;
    
    // Basic columns are conditionally included based on settings
    if (in_array($header, $config['basic_columns'])) {
        switch ($header) {
            case 'Rank':
                return $filters['show_rank'] ?? true;
            case 'BIB':
                return $filters['show_bib'] ?? true;
            case 'Participant':
                return $filters['show_participant'] ?? true;
            case 'Category':
                return $filters['show_category'] ?? true;
            case 'Club':
                return $filters['show_club'] ?? true;
            case 'Gender':
                return $filters['show_gender'] ?? true;
            case 'fis_code':
                return $filters['show_fis_code'] ?? false;
            case 'licence_code':
                return $filters['show_licence_code'] ?? false;
            case 'country':
                return $filters['show_country'] ?? false;
            default:
                return true;
        }
    }
    
    // Parse heat and run from header
    if (preg_match('/^H(\d+)R(\d+)(_control_points|_judges|_figures)?$/', $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 === '_figures' && !($filters['show_figures'] ?? false)) {
            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 === 'OverallBest' && !$filters['show_highest_average']) {
        return false;
    }
    
    return true;
}

// Function to get column CSS class
function getColClass($header) {
    global $config;
    
    // Create field-based CSS class
    $field_class = 'css-' . strtolower(str_replace(['_', ' '], '-', $header));
    
    $base_class = '';
    if (preg_match('/^H\d+Best$/', $header)) $base_class = $config['table_classes']['heat_best'];
    elseif (preg_match('/^H\d+Average$/', $header)) $base_class = $config['table_classes']['heat_average'];
    elseif ($header === 'OverallAverage') $base_class = $config['table_classes']['overall_average'];
    elseif ($header === 'OverallBest') $base_class = $config['table_classes']['highest_average'];
    elseif (preg_match('/^H\d+R\d+_control_points$/', $header)) $base_class = $config['table_classes']['control_points'];
    elseif (preg_match('/^H\d+R\d+_judges$/', $header)) $base_class = $config['table_classes']['judges'];
    elseif (preg_match('/^H\d+R\d+_figures$/', $header)) $base_class = $config['table_classes']['figures'];
    
    // Combine field class with existing base class
    return trim($field_class . ' ' . $base_class);
}

// Function to apply column renames to a header
function applyColumnRename($header, $column_renames) {
    // Define mapping from column keys to possible header matches
    $mapping = [
        'rank' => ['Rank'],
        'bib' => ['BIB'],
        'participant' => ['Participant'],
        'category' => ['Category'],
        'club' => ['Club'],
        'gender' => ['Gender'],
        'fis_code' => ['fis_code'],
        'licence_code' => ['licence_code'],
        'country' => ['country'],
        'runs' => [], // Will be filled with pattern matches
        'judges' => [], // Will be filled with pattern matches
        'control_points' => [], // Will be filled with pattern matches
        'figures' => [], // Will be filled with pattern matches
        'heat_best' => [], // Will be filled with pattern matches
        'heat_average' => [], // Will be filled with pattern matches
        'overall_best' => ['OverallAverage'],
        'highest_average' => ['OverallBest']
    ];
    
    // Direct mapping for basic columns
    foreach ($mapping as $key => $headers) {
        if (isset($column_renames[$key]) && in_array($header, $headers)) {
            return $column_renames[$key];
        }
    }
    
    // Pattern matching for dynamic columns
    if (isset($column_renames['runs']) && preg_match('/^H\d+R\d+$/', $header)) {
        return $column_renames['runs'];
    }
    
    if (isset($column_renames['judges']) && preg_match('/^H\d+R\d+_judges$/', $header)) {
        return $column_renames['judges'];
    }
    
    if (isset($column_renames['control_points']) && preg_match('/^H\d+R\d+_control_points$/', $header)) {
        return $column_renames['control_points'];
    }
    
    if (isset($column_renames['figures']) && preg_match('/^H\d+R\d+_figures$/', $header)) {
        return $column_renames['figures'];
    }
    
    if (isset($column_renames['heat_best']) && preg_match('/^H\d+Best$/', $header)) {
        return $column_renames['heat_best'];
    }
    
    if (isset($column_renames['heat_average']) && preg_match('/^H\d+Average$/', $header)) {
        return $column_renames['heat_average'];
    }
    
    // Return original header if no rename found
    return $header;
}

// Function to generate available column configuration (simplified)
function getAvailableColumns($heats_total, $runs_per_heat, $heat_settings = []) {
    $columns = [
        'basic' => ['Rank', 'BIB', 'Participant', 'Category', 'Club', 'Gender'],
        'heats' => range(1, $heats_total),
        'overall' => ['OverallAverage', 'OverallBest']
    ];
    
    // Add heat-specific columns
    for ($h = 1; $h <= $heats_total; $h++) {
        $runs_count = $heat_settings[$h]['runs_count'] ?? $runs_per_heat;
        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";
        }
        $columns['heat_best'][] = "H{$h}Best";
        $columns['heat_average'][] = "H{$h}Average";
    }
    
    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;
}

// First, update the determineCategoriesToProcess function to look at matching_category_ids:

function determineCategoriesToProcess($pdo, $selected_event, $filter_category, $filter_category_id, $heat_run_filter) {
    // If a specific category ID or name is requested, use it
    if ($filter_category_id) {
        $cat_stmt = $pdo->prepare("SELECT category_name FROM event_categories WHERE id = ? AND event_id = ? LIMIT 1");
        $cat_stmt->execute([$filter_category_id, $selected_event]);
        $category_name = $cat_stmt->fetchColumn();
         if ($filter_category_id) {
        // Return the category ID directly
        return [$filter_category_id];
    }
        if ($category_name) {
            return [$category_name];
        }
    }
    
    // If a specific category name 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: Get only main categories (is_main_category = 1) for 'all' category selection
    $main_categories = [];
    
    // Get main categories from event_categories table
    $categories_stmt = $pdo->prepare("
        SELECT id, category_name 
        FROM event_categories 
        WHERE event_id = ? AND is_main_category = 1
        ORDER BY category_name
    ");
    $categories_stmt->execute([$selected_event]);
    $main_category_rows = $categories_stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($main_category_rows as $row) {
        $main_categories[] = $row['id'];
    }
    
    // If no main categories found, fall back to all categories to avoid empty result
    if (empty($main_categories)) {
        // Get all categories from event_categories table
        $categories_stmt = $pdo->prepare("
            SELECT id, category_name 
            FROM event_categories 
            WHERE event_id = ?
            ORDER BY category_name
        ");
        $categories_stmt->execute([$selected_event]);
        $event_category_rows = $categories_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($event_category_rows as $row) {
            $main_categories[] = $row['id'];
        }
        
        // If still empty, get distinct categories from event_participants
        if (empty($main_categories)) {
            $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]);
            $main_categories = $categories_stmt->fetchAll(PDO::FETCH_COLUMN);
        }
    }
    
    // Clean and deduplicate categories
    $main_categories = array_unique(array_map('trim', $main_categories));
    $main_categories = array_filter($main_categories, function($cat) {
        return !empty($cat);
    });
    sort($main_categories);
    
    return $main_categories;
}

// Modified generateSummaryTableData function to handle heat-specific categories
function generateSummaryTableData($pdo, $selected_event, $filter_category, $filter_gender, $sort_by, $column_filters) {
    // Load scoring format configuration for this event
    $scoring_format = loadScoringFormatConfig($pdo, $selected_event);
    
    // Determine which categories to process
    $categories_to_process = determineCategoriesToProcess(
        $pdo, 
        $selected_event, 
        $filter_category, 
        $filter_category_id ?? null,
        $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) {
            // First, get the category ID from event_categories if we're filtering by category name
           //print_r($cat);
           // print_r($filter_category);
            // Get category ID for the current category name
            $cat_id = null;
            if ($cat !== 'all') {
                $cat_id_stmt = $pdo->prepare("SELECT id FROM event_categories WHERE event_id = ? AND category_name = ? LIMIT 1");
                $cat_id_stmt->execute([$selected_event, $cat]);
                $cat_id = $cat_id_stmt->fetchColumn();
            }

            // Enhanced SQL to get individual judge scores with control point grouping
            // Modified to check both category column and matching_category_ids JSON array
        

            if($filter_category !== 'all')
    {
        // Check if scores table has figures_json column
        try {
            $hasColumnsQuery = "SHOW COLUMNS FROM scores LIKE 'figures_json'";
            $hasColumnsStmt = $pdo->query($hasColumnsQuery);
            $hasFiguresColumn = $hasColumnsStmt->rowCount() > 0;
        } catch (Exception $e) {
            $hasFiguresColumn = false;
        }
        
        // With this version that uses proper JSON string formatting:
            $sql = "
                SELECT
                    ep.id as event_participant_id,
                    ep.category,
                    ep.category_id,
                    ep.matching_category_ids,
                    ep.bib_number as bib,
                    p.first_name,
                    p.last_name,
                    p.gender,
                    p.club,
                    p.fis_code,
                    p.licence_code,
                    p.country,
                    ep.heat_number,
                    r.run_number,
                    s.score_value," .
                    ($hasFiguresColumn ? "s.figures_json," : "") . "
                    s.judge_id,
                    s.approved_at,
                    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  AND s.is_approved = 1
                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_id = ?
                
                ORDER BY ep.heat_number, r.run_number, ep.bib_number, ecp.sort_order, s.judge_id
            ";

            $stmt = $pdo->prepare($sql);
            // Pass category ID as a simple value, not wrapped in JSON
            $stmt->execute([$selected_event, $cat]);
                        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }else
    {
        // Check if scores table has figures_json column (if not already checked)
        if (!isset($hasFiguresColumn)) {
            try {
                $hasColumnsQuery = "SHOW COLUMNS FROM scores LIKE 'figures_json'";
                $hasColumnsStmt = $pdo->query($hasColumnsQuery);
                $hasFiguresColumn = $hasColumnsStmt->rowCount() > 0;
            } catch (Exception $e) {
                $hasFiguresColumn = false;
            }
        }
        
        // With this version that uses proper JSON string formatting:
            $sql = "
                SELECT
                    ep.id as event_participant_id,
                    ep.category,
                    ep.category_id,
                    ep.matching_category_ids,
                    ep.bib_number as bib,
                    p.first_name,
                    p.last_name,
                    p.gender,
                    p.club,
                    p.fis_code,
                    p.licence_code,
                    p.country,
                    ep.heat_number,
                    r.run_number,
                    s.score_value," .
                    ($hasFiguresColumn ? "s.figures_json," : "") . "
                    s.judge_id,
                    s.approved_at,
                    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  AND s.is_approved = 1
                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 = ? 
                    OR (ep.category_id = ?) 
                    OR (ep.matching_category_ids IS NOT NULL AND JSON_CONTAINS(ep.matching_category_ids, CONCAT('[',?,']')))
                )
                ORDER BY ep.heat_number, r.run_number, ep.bib_number, ecp.sort_order, s.judge_id
            ";

            $stmt = $pdo->prepare($sql);
            // Pass category ID as a simple value, not wrapped in JSON
            $stmt->execute([$selected_event, $cat, $cat_id, $cat_id]);
                        $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_last_name . ' ' . $participant_first_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);
                $figures_json = isset($row['figures_json']) ? $row['figures_json'] : null;
                $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;
                $approved_at = $row['approved_at'] ?? null;
                
                // 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]['fis_code'] = $row['fis_code'];
                    $summary_data[$cat][$key]['licence_code'] = $row['licence_code'];
                    $summary_data[$cat][$key]['country'] = $row['country'];
                    $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'] = [];
                    $summary_data[$cat][$key]['latest_approved_at'] = null; // Initialize latest approval timestamp
                }
                
                // Track the latest approved_at timestamp
    if ($approved_at && (!$summary_data[$cat][$key]['latest_approved_at'] || $approved_at > $summary_data[$cat][$key]['latest_approved_at'])) {
        $summary_data[$cat][$key]['latest_approved_at'] = $approved_at;
    }    

                // 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,
                            'figures_json' => $figures_json,
                            '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 using scoring format
                    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 = calculateRunScoreByFormat($run_scores, $scoring_format);
                            // Use minimum 2 decimal places for calculation display, regardless of input precision
                            $display_precision = max(2, $scoring_format['precision_decimal']);
                            $p[$code] = number_format($run_average, $display_precision);
                            
                            // 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, $scoring_format);
                        
                        $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, $scoring_format);
                        
                        $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 best scores, not heat averages
            if (!empty($column_filters['heat_run_filter'])) {
                // Filter is applied - calculate from heat best scores of selected heats
                $filtered_heat_best_for_overall = []; // For both OverallAverage and OverallBest calculation
                
                // Collect best 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}Best"]) && $p["H{$h}Best"] !== '-') {
                        $filtered_heat_best_for_overall[] = floatval($p["H{$h}Best"]);
                    }
                }
                
                // Calculate OverallAverage using scoring format aggregation
                if (!empty($filtered_heat_best_for_overall)) {
                    $overall_average = calculateFinalScoreByFormat($filtered_heat_best_for_overall, $scoring_format);
                    // Use minimum 2 decimal places for calculation display
                    $display_precision = max(2, $scoring_format['precision_decimal']);
                    $p["OverallAverage"] = number_format($overall_average, $display_precision);
                } else {
                    $p["OverallAverage"] = '-';
                }
                
                if (!empty($filtered_heat_best_for_overall)) {
                    $p["OverallBest"] = number_format(max($filtered_heat_best_for_overall), 2);
                } else {
                    $p["OverallBest"] = '-';
                }
            } else {
                // No filter applied - use all heat best scores
                $all_heat_best_for_overall = [];
                
                for ($h = 1; $h <= $heats_total; $h++) {
                    if (isset($p["H{$h}Best"]) && $p["H{$h}Best"] !== '-') {
                        $all_heat_best_for_overall[] = floatval($p["H{$h}Best"]);
                    }
                }
                
                // Calculate OverallAverage using scoring format aggregation  
                if (!empty($all_heat_best_for_overall)) {
                    $overall_average = calculateFinalScoreByFormat($all_heat_best_for_overall, $scoring_format);
                    // Use minimum 2 decimal places for calculation display
                    $display_precision = max(2, $scoring_format['precision_decimal']);
                    $p["OverallAverage"] = number_format($overall_average, $display_precision);
                } else {
                    $p["OverallAverage"] = '-';
                }
                
                if (!empty($all_heat_best_for_overall)) {
                    $p["OverallBest"] = number_format(max($all_heat_best_for_overall), 2);
                } else {
                    $p["OverallBest"] = '-';
                }
            }
        }
    }

    // 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}_figures";
            $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[] = 'OverallBest';
    
    // 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),
        'scoring_format' => $scoring_format
    ];
}

// Function to load scoring format configuration for calculations
function loadScoringFormatConfig($pdo, $event_id) {
    try {
        // First, get the scoring format ID from event settings (use scoring_format column)
        $event_stmt = $pdo->prepare("SELECT scoring_format FROM events WHERE id = ? LIMIT 1");
        $event_stmt->execute([$event_id]);
        $scoring_format_id = $event_stmt->fetchColumn();
        
        if (!$scoring_format_id) {
            return [
                'format_id' => null,
                'aggregation_method' => 'average',
                'scale_type' => 'numeric',
                'precision_decimal' => 2,
                'drop_rule' => 'none',
                'rounds' => [],
                'criteria' => []
            ];
        }
        
        // Load format details with judge settings
        $format_stmt = $pdo->prepare("
            SELECT sf.*, 
                   sfj.min_judges, sfj.max_judges, sfj.scale_min, sfj.scale_max, 
                   sfj.scale_type, sfj.scale_custom, sfj.precision_decimal, sfj.drop_rule
            FROM scoring_formats sf
            LEFT JOIN scoring_format_judges sfj ON sf.format_id = sfj.format_id
            WHERE sf.format_id = ?
        ");
        $format_stmt->execute([$scoring_format_id]);
        $format = $format_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$format) {
            return [
                'format_id' => null,
                'aggregation_method' => 'average',
                'scale_type' => 'numeric',
                'precision_decimal' => 2,
                'drop_rule' => 'none',
                'rounds' => [],
                'criteria' => []
            ];
        }
        
        // Load rounds configuration
        $rounds_stmt = $pdo->prepare("
            SELECT round_name, runs, aggregation
            FROM scoring_format_rounds
            WHERE format_id = ?
            ORDER BY round_name
        ");
        $rounds_stmt->execute([$scoring_format_id]);
        $rounds = $rounds_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Load criteria configuration
        $criteria_stmt = $pdo->prepare("
            SELECT criteria_type, criteria_name, sort_order
            FROM scoring_format_criteria
            WHERE format_id = ?
            ORDER BY criteria_type, sort_order
        ");
        $criteria_stmt->execute([$scoring_format_id]);
        $criteria = $criteria_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Load mode configuration
        $mode_config_stmt = $pdo->prepare("
            SELECT config_type, config_key, config_value
            FROM scoring_format_mode_config
            WHERE format_id = ?
            ORDER BY config_type, config_key
        ");
        $mode_config_stmt->execute([$scoring_format_id]);
        $mode_config = [];
        foreach ($mode_config_stmt->fetchAll(PDO::FETCH_ASSOC) as $config) {
            $mode_config[$config['config_type']][$config['config_key']] = $config['config_value'];
        }
        
        // Determine primary aggregation method
        $aggregation_methods = array_unique(array_column($rounds, 'aggregation'));
        $primary_aggregation = $aggregation_methods[0] ?? 'average';
        
        return [
            'format_id' => $scoring_format_id,
            'name' => $format['name'],
            'sport' => $format['sport'],
            'mode' => $format['mode'],
            'aggregation_method' => $primary_aggregation,
            'scale_type' => $format['scale_type'] ?? 'numeric',
            'precision_decimal' => (int)($format['precision_decimal'] ?? 2),
            'drop_rule' => $format['drop_rule'] ?? 'none',
            'scale_min' => $format['scale_min'],
            'scale_max' => $format['scale_max'],
            'min_judges' => $format['min_judges'],
            'max_judges' => $format['max_judges'],
            'rounds' => $rounds,
            'criteria' => $criteria,
            'mode_config' => $mode_config
        ];
        
    } catch (Exception $e) {
        error_log("Error loading scoring format config: " . $e->getMessage());
        return [
            'format_id' => null,
            'aggregation_method' => 'average',
            'scale_type' => 'numeric',
            'precision_decimal' => 2,
            'drop_rule' => 'none',
            'rounds' => [],
            'criteria' => []
        ];
    }
}

// Function to calculate individual run score based on format rules
function calculateRunScoreByFormat($judge_scores, $scoring_format) {
    if ($scoring_format['scale_type'] !== 'numeric') {
        return 'Custom';
    }
    
    if (empty($judge_scores)) {
        return 0;
    }
    
    $working_scores = array_map('floatval', $judge_scores);
    
    // Apply drop rule from scoring format
    switch ($scoring_format['drop_rule']) {
        case 'highest':
            if (count($working_scores) > 1) {
                rsort($working_scores);
                array_shift($working_scores); // Remove highest
            }
            break;
        case 'lowest':
            if (count($working_scores) > 1) {
                sort($working_scores);
                array_shift($working_scores); // Remove lowest
            }
            break;
        case 'highest_lowest':
        case 'highest_and_lowest': // Handle "Drop High/Low" rule
            if (count($working_scores) > 2) {
                sort($working_scores);
                array_shift($working_scores); // Remove lowest
                array_pop($working_scores);   // Remove highest
            }
            break;
        case 'none':
        default:
            // No drops
            break;
    }
    
    // Calculate average and apply precision (minimum 2 decimal places for calculations)
    $average = count($working_scores) > 0 ? array_sum($working_scores) / count($working_scores) : 0;
    $calculation_precision = max(2, $scoring_format['precision_decimal']);
    return round($average, $calculation_precision);
}

// Function to format judge scores with drop rule indicators
function formatJudgeScoresWithDrops($judge_scores, $scoring_format) {
    if (empty($judge_scores) || $scoring_format['scale_type'] !== 'numeric') {
        return [];
    }
    
    // Extract scores and keep track of original positions
    $scores_with_positions = [];
    foreach ($judge_scores as $index => $judge_data) {
        $scores_with_positions[] = [
            'score' => floatval($judge_data['score']),
            'index' => $index,
            'judge_data' => $judge_data
        ];
    }
    
    // Determine which scores to drop based on drop rule
    $dropped_indices = [];
    $score_values = array_column($scores_with_positions, 'score');
    $drop_rule = $scoring_format['drop_rule'] ?? 'none';
    
    // Debug logging - can remove this later
    if (count($score_values) > 0) {
        error_log("Drop rule: " . $drop_rule . ", Scores: " . implode(',', $score_values));
    }
    
    switch ($drop_rule) {
        case 'highest':
            if (count($score_values) > 1) {
                $max_score = max($score_values);
                // Find first occurrence of highest score
                foreach ($scores_with_positions as $item) {
                    if ($item['score'] == $max_score) {
                        $dropped_indices[] = $item['index'];
                        break;
                    }
                }
            }
            break;
        case 'lowest':
            if (count($score_values) > 1) {
                $min_score = min($score_values);
                // Find first occurrence of lowest score
                foreach ($scores_with_positions as $item) {
                    if ($item['score'] == $min_score) {
                        $dropped_indices[] = $item['index'];
                        break;
                    }
                }
            }
            break;
        case 'highest_lowest':
        case 'highest_and_lowest': // Handle "Drop High/Low" rule
            if (count($score_values) > 2) {
                $max_score = max($score_values);
                $min_score = min($score_values);
                
                // Find first occurrence of highest and lowest scores
                $found_highest = false;
                $found_lowest = false;
                foreach ($scores_with_positions as $item) {
                    if (!$found_highest && $item['score'] == $max_score) {
                        $dropped_indices[] = $item['index'];
                        $found_highest = true;
                    } else if (!$found_lowest && $item['score'] == $min_score) {
                        $dropped_indices[] = $item['index'];
                        $found_lowest = true;
                    }
                    if ($found_highest && $found_lowest) break;
                }
            }
            break;
        case 'none':
        default:
            // No drops
            break;
    }
    
    // Debug logging for dropped indices
    if (!empty($dropped_indices)) {
        error_log("Dropped indices: " . implode(',', $dropped_indices));
    }
    
    // Format the display with brackets and parentheses
    $formatted_scores = [];
    foreach ($judge_scores as $index => $judge_data) {
        $score = number_format($judge_data['score'], 0);
        if (in_array($index, $dropped_indices)) {
            // Dropped score: [score] becomes ([score])
            $formatted_scores[] = '<div class="judge_score dropped-score" style="text-decoration: line-through;">[' . $score . ']</div>';
        } else {
            // Normal score: [score]
            $formatted_scores[] = '<div class="judge_score normal-score">[' . $score . ']</div>';
        }
    }
    
    return $formatted_scores;
}

// Function to calculate final score based on format aggregation rules
function calculateFinalScoreByFormat($run_scores, $scoring_format) {
    if ($scoring_format['scale_type'] !== 'numeric' || empty($run_scores)) {
        return 0;
    }
    
    // Remove any non-numeric values
    $clean_scores = array_filter(array_map('floatval', $run_scores), function($val) {
        return $val > 0;
    });
    
    if (empty($clean_scores)) {
        return 0;
    }
    
    $final_score = 0;
    
    switch ($scoring_format['aggregation_method']) {
        case 'sum':
        case 'total':
            $final_score = array_sum($clean_scores);
            break;
            
        case 'best':
        case 'max':
        case 'highest':
            $final_score = max($clean_scores);
            break;
            
        case 'best_2':
            rsort($clean_scores);
            $final_score = array_sum(array_slice($clean_scores, 0, 2));
            break;
            
        case 'best_3':
            rsort($clean_scores);
            $final_score = array_sum(array_slice($clean_scores, 0, 3));
            break;
            
        case 'average':
        default:
            $final_score = array_sum($clean_scores) / count($clean_scores);
            break;
    }
    
    // Use minimum 2 decimal places for calculation results
    $calculation_precision = max(2, $scoring_format['precision_decimal']);
    return round($final_score, $calculation_precision);
}

// Function to calculate heat score based on scoring method and format
function calculateHeatScore($run_averages, $scoring_method, $scoring_format = null) {
    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;
    }
    
    // If we have a scoring format, use it to determine the calculation method
    if ($scoring_format && isset($scoring_format['aggregation_method'])) {
        $result = calculateFinalScoreByFormat($clean_averages, $scoring_format);
        return $result;
    }
    
    // Parse the scoring method (legacy support)
    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':
        case 'total':
            return array_sum($clean_averages);
        case 'best_2':
            rsort($clean_averages);
            return array_sum(array_slice($clean_averages, 0, 2));
        case 'best_3':
            rsort($clean_averages);
            return array_sum(array_slice($clean_averages, 0, 3));
        case 'average':
        default:
            return array_sum($clean_averages) / count($clean_averages);
    }
}
function validateScoringMethodByFormat($method, $runs_count, $scoring_format = null) {
    // If we have a scoring format, use its aggregation method
    if ($scoring_format && isset($scoring_format['aggregation_method'])) {
        $format_method = $scoring_format['aggregation_method'];
        
        // Handle format-specific methods
        switch ($format_method) {
            case 'best_2':
            case 'best_3':
                return $format_method;
            case 'sum':
            case 'total':
            case 'best':
            case 'max':
            case 'highest':
            case 'average':
                return $format_method;
            default:
                // Fall through to standard validation
                break;
        }
    }
    
    // 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', 'total', 'best_2', 'best_3'];
    if (in_array($method, $legacy_methods)) {
        return $method;
    }
    
    // Default fallback
    return $scoring_format['aggregation_method'] ?? 'average';
}

// 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';
}

// Replace the hardcoded sort_by parameter
$sort_by = $_GET['sort'] ?? 'OverallAverage';
$sort_direction = $_GET['sort_direction'] ?? 'desc'; // Add sort direction

// Add validation for sort column
function isValidSortColumn($column, $available_headers) {
    // Allow basic columns and generated headers
    $basic_columns = ['bib', 'participant_name', 'category', 'club', 'gender'];
    return in_array($column, $basic_columns) || in_array($column, $available_headers);
}

try {
    // Add format detection at the top, after header
$format = $_GET['format'] ?? 'json';

    if (!$selected_event) {
        if ($format === 'csv') {
            header('Content-Type: text/csv; charset=utf-8');
            echo "\xEF\xBB\xBF"; // UTF-8 BOM for Excel compatibility
            echo "Error,No event selected\n";
        } elseif ($format === 'html') {
            header('Content-Type: text/html; charset=utf-8');
            echo '<link href="/v2/assets/css/bootstrap.min.css" rel="stylesheet">';
            echo '<link id="" href="/v2/assets/bootswatch/zephyr/bootstrap.min.css" rel="stylesheet">';
            echo '<link href="/v2/assets/css/global.css" rel="stylesheet">';
            echo '<!DOCTYPE html><html><head><meta charset="UTF-8"><title>Error</title></head><body>';
            echo "<div class='alert alert-danger'>No event selected</div>";
            echo '</body></html>';
        } else {
            echo json_encode([
                'success' => false,
                'message' => 'No event selected'
            ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        }
        exit;
    }

    // Parse column filters into a structured array
    $column_filters = [
        'heat_run_filter' => parseHeatRunFilter($heat_run_filter),
        'heat_run_filter_json' => $heat_run_filter,
        'show_runs' => $show_runs,
        'show_judges' => $show_judges,
        'show_control_points' => $show_control_points,
        'show_figures' => $show_figures,
        'show_heat_best' => $show_heat_best,
        'show_heat_average' => $show_heat_average,
        'show_overall_best' => $show_overall_best,
        'show_highest_average' => $show_highest_average,
        'show_event_settings' => $show_event_settings,
        // NEW: Basic column visibility
        'show_rank' => $show_rank,
        'show_bib' => $show_bib,
        'show_participant' => $show_participant,
        'show_category' => $show_category,
        'show_club' => $show_club,
        'show_gender' => $show_gender,
        'show_fis_code' => $show_fis_code,
        'show_licence_code' => $show_licence_code,
        'show_country' => $show_country
    ];

    // Generate the main data
    $data = generateSummaryTableData($pdo, $selected_event, $filter_category, $filter_gender, $sort_by, $column_filters);
    
    
    // Process data based on category filter
    if ($filter_category === 'all' && !empty($data['summary_data'])) {
        // Multiple categories - group by category
        $heat_category_groups = [];
        $combined_participants = [];
        $processed_participants = [];
        $debug_info = [];
        $category_name_tracking = [];
        
        foreach ($data['summary_data'] as $original_category => $participants) {
            $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
            ];
            
            $clean_category = $original_category;
            $category_name_tracking[$original_category]['step_by_step'][] = "clean_category set to: '{$clean_category}'";
            
            if (!isset($heat_category_groups[$clean_category])) {
                $heat_category_groups[$clean_category] = [
                    'group_name' => $clean_category,
                    'heat_number' => null,
                    'heat_name' => 'All Heats',
                    'category' => $clean_category,
                    'participants' => []
                ];
                $category_name_tracking[$original_category]['step_by_step'][] = "Group created with name: '{$clean_category}'";
            } else {
                $debug_info[$original_category]['duplicate_category_detected'] = true;
                $category_name_tracking[$original_category]['step_by_step'][] = "DUPLICATE DETECTED - skipping";
                continue;
            }
            
            foreach ($participants as $name => $participant_data) {
                $participant_bib = $participant_data['bib'];
                $participant_category = $participant_data['category'];
                
                if ($participant_category !== $clean_category) {
                    $category_name_tracking[$original_category]['step_by_step'][] = "Participant category mismatch: participant='{$participant_category}', group='{$clean_category}'";
                }
                
                $participant_unique_id = $participant_bib . '_' . $participant_category;
                
                if (isset($processed_participants[$participant_unique_id])) {
                    $debug_info[$original_category]['skipped_duplicates']++;
                    continue;
                }
                
                $processed_participants[$participant_unique_id] = true;
                $debug_info[$original_category]['processed_count']++;
                
                $heat_category_groups[$clean_category]['participants'][$name] = $participant_data;
                
                $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 and clean up groups
        $sorted_heat_category_groups = [];
        foreach ($heat_category_groups as $clean_category => $group_data) {
            $new_group_data = [
                'group_name' => $clean_category,
                'heat_number' => null,
                'heat_name' => 'All Heats',
                'category' => $clean_category,
                'participants' => []
            ];
            
            // Remove 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'];
                
                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);
            
            // Apply sorting if specified
            if ($sort_by !== 'none') {
                uasort($new_group_data['participants'], function($a, $b) use ($sort_by, $sort_direction) {
                    return compareSortValues($a, $b, $sort_by, $sort_direction);
                });
            }
            
            $sorted_heat_category_groups[$clean_category] = $new_group_data;
        }
        
        $heat_category_groups = array_filter($sorted_heat_category_groups, function($group) {
            return !empty($group['participants']);
        });
        
        ksort($heat_category_groups);
        
        $participants = $combined_participants;
        $participant_count = count($participants);
        $categories_processed = array_keys($data['summary_data']);
        $grouped_by_heat_category = true;
        
        $grouping_debug = [
            'original_categories_count' => count($data['summary_data']),
            'final_groups_count' => count($heat_category_groups),
            'category_details' => $debug_info,
            'category_name_tracking' => $category_name_tracking
        ];
        
    } elseif ($filter_category !== 'all' && isset($data['summary_data'][$filter_category])) {
        // Single category processing
        $participants = $data['summary_data'][$filter_category];
        
        if ($sort_by !== 'none') {
            uasort($participants, function($a, $b) use ($sort_by, $sort_direction) {
                return compareSortValues($a, $b, $sort_by, $sort_direction);
            });
        }
        
        $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']
            ]
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        exit;
    }

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

    // Build the list of visible basic columns
$visible_basic_columns = [];
if ($column_filters['show_rank']) $visible_basic_columns[] = 'Rank';
if ($column_filters['show_bib']) $visible_basic_columns[] = 'BIB';
if ($column_filters['show_participant']) $visible_basic_columns[] = 'Participant';
if ($column_filters['show_category']) $visible_basic_columns[] = 'Category';
if ($column_filters['show_club']) $visible_basic_columns[] = 'Club';
if ($column_filters['show_gender']) $visible_basic_columns[] = 'Gender';
if ($column_filters['show_fis_code']) $visible_basic_columns[] = 'fis_code';
if ($column_filters['show_licence_code']) $visible_basic_columns[] = 'licence_code';
if ($column_filters['show_country']) $visible_basic_columns[] = 'country';

    // Generate table header
    $table_head .= '<tr>';
    if (in_array('Rank', $visible_basic_columns)) {
        $display_name = applyColumnRename('Rank', $column_renames);
        $table_head .= '<th scope="col"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('BIB', $visible_basic_columns)) {
        $display_name = applyColumnRename('BIB', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="bib"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('Participant', $visible_basic_columns)) {
        $display_name = applyColumnRename('Participant', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="participant_name"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('Category', $visible_basic_columns)) {
        $display_name = applyColumnRename('Category', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="category"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('Club', $visible_basic_columns)) {
        $display_name = applyColumnRename('Club', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="club"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('Gender', $visible_basic_columns)) {
        $display_name = applyColumnRename('Gender', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="gender"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('fis_code', $visible_basic_columns)) {
        $display_name = applyColumnRename('fis_code', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="fis_code"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('licence_code', $visible_basic_columns)) {
        $display_name = applyColumnRename('licence_code', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="licence_code"><span>' . htmlspecialchars($display_name) . '</span></th>';
    }
    if (in_array('country', $visible_basic_columns)) {
        $display_name = applyColumnRename('country', $column_renames);
        $table_head .= '<th scope="col" data-sortable="true" data-sort="country"><span>' . htmlspecialchars($display_name) . '</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) {
        // Ensure $h is a string to prevent array to string conversion warnings
        $h = (string)$h;
        $header_text = $h;
        $tooltip_text = '';
        
        // Apply column rename first
        $renamed_header = applyColumnRename($h, $column_renames);
        
        // Add filter indication to overall columns
        if ($has_heat_run_filter) {
            if ($h === 'OverallAverage') { // Changed from OverallBest
                $header_text = $renamed_header . '*';
                $tooltip_text = 'Average of heat averages - calculated based on selected heats/runs only';
            } elseif ($h === 'OverallBest') {
                $header_text = $renamed_header . '*';
                $tooltip_text = 'Highest heat average - calculated based on selected heats/runs only';
            } else {
                $header_text = $renamed_header;
            }
        } else {
            $header_text = $renamed_header;
        }
        
        $table_head .= '<th scope="col" class="angle-th ' . getColClass($h) . '" data-sortable="true" data-sort="' . htmlspecialchars($h) . '"' . 
                  ($tooltip_text ? ' title="' . htmlspecialchars($tooltip_text) . '"' : '') . 
                  '><span>' . htmlspecialchars($header_text) . '</span></th>';
    }
    $table_head .= '</tr>';

    // Collect participant initials for color generation
    $participant_colors = [];
    $collected_participants = [];
    
    // Function to collect participant data from both grouped and non-grouped scenarios
    if (isset($grouped_by_heat_category) && $grouped_by_heat_category) {
        // Collect all participants from grouped data
        $all_grouped_participants = [];
        foreach ($heat_category_groups as $category => $group_data) {
            foreach ($group_data['participants'] as $participant_key => $runs) {
                $all_grouped_participants[$participant_key] = $runs;
            }
        }
        $participant_colors = collectParticipantColors($all_grouped_participants, ['mode' => 'light', 'saturation' => 0.65]);
    } else {
        $participant_colors = collectParticipantColors($participants, ['mode' => 'light', 'saturation' => 0.65]);
    }

    // Generate table body
    $rank = 1;
    $current_group = null;

    // Helper function to check if participant has any valid scores
function hasValidScores($participant, $headers) {
    foreach ($headers as $header) {
        // Skip judge and control point breakdown columns
        if (strpos($header, '_judges') !== false || strpos($header, '_control_points') !== false) {
            continue;
        }
        
        // Check if participant has any numeric value in a score column
        if (isset($participant[$header]) && $participant[$header] !== '-' && is_numeric($participant[$header])) {
            return true;
        }
    }
    return false;
}

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 = count($visible_basic_columns) + 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;
            
            // Check if participant has any valid scores
            $has_scores = hasValidScores($runs, $data['final_headers']);
            
            // 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');
            }
            
            // Start row with conditional class
            $table_body .= '<tr class="group-participant-row' . (!$has_scores ? ' no_points' : '') . '" data-group="' . 
    htmlspecialchars($category) . '" data-approved_at="' . 
    htmlspecialchars($runs['latest_approved_at'] ?? '') . '">';
            // Only include columns that are in visible_basic_columns
            if (in_array('Rank', $visible_basic_columns)) {
                $table_body .= '<td class="css-rank" scope="row"><span class="group-rank">' . 
                    ($has_scores ? $group_rank++ : '-') . '</span></td>';
            }
            if (in_array('BIB', $visible_basic_columns)) {
                $table_body .= '<td class="css-bib"><span class="badge bg-secondary">' . htmlspecialchars($runs['bib'] ?? '') . '</span></td>';
            }
            if (in_array('Participant', $visible_basic_columns)) {
                $initials_badge = '';
                if (!empty($runs['last_name']) && !empty($runs['first_name'])) {
                    $initials = strtoupper(mb_substr($runs['last_name'], 0, 1, 'UTF-8') . mb_substr($runs['first_name'], 0, 1, 'UTF-8'));
                    $bib = $runs['bib'] ?? '';
                    $event_participant_id = $runs['event_participant_id'] ?? '';
                    $initials_badge = '<span class="badge badge_p badge_p_' . htmlspecialchars($bib) . '_' . htmlspecialchars($initials) . ' badge_ep_' . htmlspecialchars($event_participant_id) . '">' . htmlspecialchars($initials) . '</span> ';
                }
                $table_body .= '<td class="css-participant"><span class="p_full_cell">' . $initials_badge . '<span class="p_name_full"><strong>' . htmlspecialchars($participant_name) . '</strong></span></span></td>';
            }
            if (in_array('Category', $visible_basic_columns)) {
                $table_body .= '<td class="css-category"><small><span class="badge bg-info">' . htmlspecialchars($runs['category'] ?? '') . '</span></small></td>';
            }
            if (in_array('Club', $visible_basic_columns)) {
                $table_body .= '<td class="css-club"><small><span class="badge bg-secondary">' . htmlspecialchars($runs['club'] ?? '-') . '</span></small></td>';
            }
            if (in_array('Gender', $visible_basic_columns)) {
                $table_body .= '<td class="css-gender"><span>' . htmlspecialchars($runs['gender'] ?? '-') . '</span></td>';
            }
            if (in_array('fis_code', $visible_basic_columns)) {
                $table_body .= '<td class="css-fis-code"><span>' . htmlspecialchars($runs['fis_code'] ?? '-') . '</span></td>';
            }
            if (in_array('licence_code', $visible_basic_columns)) {
                $table_body .= '<td class="css-licence-code"><span>' . htmlspecialchars($runs['licence_code'] ?? '-') . '</span></td>';
            }
            if (in_array('country', $visible_basic_columns)) {
                $table_body .= '<td class="css-country"><span>' . htmlspecialchars($runs['country'] ?? '-') . '</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'], 0) . '</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+_figures$/', $h)) {
                        // Extract heat and run numbers from header
                        preg_match('/^H(\d+)R(\d+)_figures$/', $h, $matches);
                        $heat_num = $matches[1];
                        $run_num = $matches[2];
                        $run_code = "H{$heat_num}R{$run_num}";
                        
                        $table_body .= '<td class="' . getColClass($h) . '">';
                        
                        // Get figures data from judge scores for this specific run
                        $figures_found = false;
                        $figures_display = [];
                        
                        if (isset($runs['judge_scores'][$run_code])) {
                            foreach ($runs['judge_scores'][$run_code] as $judge_data) {
                                if (!empty($judge_data['figures_json'])) {
                                    $figures_data = json_decode($judge_data['figures_json'], true);
                                    
                                    if (is_array($figures_data)) {
                                        foreach ($figures_data as $category => $items) {
                                            // Skip validation data - only show figures
                                            if ($category === 'validation') {
                                                continue;
                                            }
                                            
                                            // If this is a figures object, iterate through its properties
                                            if ($category === 'figures' && is_array($items)) {
                                                foreach ($items as $figure_category => $figure_items) {
                                                    if (is_array($figure_items) && !empty($figure_items)) {
                                                        // Map figure categories to display labels
                                                        $category_map = [
                                                            'Rotation' => 'Rot',
                                                            'Direction' => 'Dir', 
                                                            'Axis' => 'Axi',
                                                            'Grab' => 'Gra',
                                                            'Feature' => 'Fea',
                                                            'Rail' => 'Rai'
                                                        ];
                                                        
                                                        $display_category = $category_map[$figure_category] ?? $figure_category;
                                                        
                                                        $figures_display[] = '<div class="figure-category" style="display: inline-block;">';
                                                        $figures_display[] = '<strong>' . htmlspecialchars($display_category) . ':</strong> ';
                                                        
                                                        // Convert items to strings, handling nested arrays
                                                        $string_items = convertArrayItemsToString($figure_items);
                                                        
                                                        // Additional safety check - filter out any remaining arrays
                                                        $safe_items = [];
                                                        foreach ($string_items as $item) {
                                                            if (is_array($item)) {
                                                                $safe_items[] = convertNestedArrayToString($item);
                                                            } else {
                                                                $safe_items[] = (string)$item;
                                                            }
                                                        }
                                                        
                                                        $figures_display[] = htmlspecialchars(implode(', ', $safe_items));
                                                        $figures_display[] = ', </div>';
                                                        $figures_found = true;
                                                    }
                                                }
                                                continue;
                                            }

                                            if (is_array($items) && !empty($items)) {
                                                // Handle legacy format or other categories
                                                $category = @trim((string)$category);
                                                $figures_display[] = '<div class="figure-category" style="display: inline-block;">';
                                                $figures_display[] = '<strong>' . htmlspecialchars($category) . ':</strong> ';
                                                // Convert items to strings, handling nested arrays
                                                $string_items = convertArrayItemsToString($items);
                                                
                                                // Additional safety check - filter out any remaining arrays
                                                $safe_items = [];
                                                foreach ($string_items as $item) {
                                                    if (is_array($item)) {
                                                        $safe_items[] = convertNestedArrayToString($item);
                                                    } else {
                                                        $safe_items[] = (string)$item;
                                                    }
                                                }
                                                
                                                $figures_display[] = htmlspecialchars(implode(', ', $safe_items));
                                                $figures_display[] = ', </div>';
                                                $figures_found = true;
                                            }
                                        }
                                    }
                                    break; // Use figures from first judge that has them
                                }
                            }
                        }
                        
                        if ($figures_found) {
                            $table_body .= '<div class="figures-breakdown">' . implode('', $figures_display) . '</div>';
                        } else {
                            $table_body .= '<span class="text-muted">-</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)) {
                            // Get scoring format for drop rule formatting
                            $scoring_format = loadScoringFormatConfig($pdo, $selected_event);
                            
                            // Temporary debug output
                            error_log("Event ID: $selected_event, Drop rule: " . ($scoring_format['drop_rule'] ?? 'not set'));
                            error_log("Judge scores count: " . count($judge_scores));
                            
                            $formatted_scores = formatJudgeScoresWithDrops($judge_scores, $scoring_format);
                            
                            $table_body .= '<div class="judge-breakdown">';
                            if (!empty($formatted_scores)) {
                                // Use the formatted scores with drop rule indicators
                                $table_body .= implode('', $formatted_scores);
                            } else {
                                // Fallback to original format if function fails
                                foreach ($judge_scores as $judge_data) {
                                    $table_body .= '[' . number_format($judge_data['score'], 0) . ']';
                                }
                            }
                            $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;
            
            // Check if participant has any valid scores
            $has_scores = hasValidScores($runs, $data['final_headers']);
            
            // 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');
            }
            
            // Start row with conditional class
            $table_body .= '<tr' . (!$has_scores ? ' class="no_points"' : '') . ' data-approved_at="' . 
    htmlspecialchars($runs['latest_approved_at'] ?? '') . '">'; 
            // Only include columns that are in visible_basic_columns
            if (in_array('Rank', $visible_basic_columns)) {
                $table_body .= '<td class="css-rank" scope="row"><span>' . 
                    ($has_scores ? $rank++ : '-') . '</span></td>';
            }
            if (in_array('BIB', $visible_basic_columns)) {
                $table_body .= '<td class="css-bib"><span class="badge bg-secondary">' . htmlspecialchars($runs['bib'] ?? '') . '</span></td>';
            }
            if (in_array('Participant', $visible_basic_columns)) {
                $initials_badge = '';
                if (!empty($runs['last_name']) && !empty($runs['first_name'])) {
                    $initials = strtoupper(mb_substr($runs['last_name'], 0, 1, 'UTF-8') . mb_substr($runs['first_name'], 0, 1, 'UTF-8'));
                    $bib = $runs['bib'] ?? '';
                    $event_participant_id = $runs['event_participant_id'] ?? '';
                    $initials_badge = '<span class="badge badge_p badge_p_' . htmlspecialchars($bib) . '_' . htmlspecialchars($initials) . ' badge_ep_' . htmlspecialchars($event_participant_id) . '">' . htmlspecialchars($initials) . '</span> ';
                }
                $table_body .= '<td class="css-participant"><span class="p_full_cell">' . $initials_badge . '<span class="p_name_full"><strong>' . htmlspecialchars($participant_name) . '</strong></span></span></td>';
            }
            if (in_array('Category', $visible_basic_columns)) {
                $table_body .= '<td class="css-category"><small><span class="badge bg-info">' . htmlspecialchars($runs['category'] ?? '') . '</span></small></td>';
            }
            if (in_array('Club', $visible_basic_columns)) {
                $table_body .= '<td class="css-club"><small><span class="badge bg-secondary">' . htmlspecialchars($runs['club'] ?? '-') . '</span></small></td>';
            }
            if (in_array('Gender', $visible_basic_columns)) {
                $table_body .= '<td class="css-gender"><span>' . htmlspecialchars($runs['gender'] ?? '-') . '</span></td>';
            }
            if (in_array('fis_code', $visible_basic_columns)) {
                $table_body .= '<td class="css-fis-code"><span>' . htmlspecialchars($runs['fis_code'] ?? '-') . '</span></td>';
            }
            if (in_array('licence_code', $visible_basic_columns)) {
                $table_body .= '<td class="css-licence-code"><span>' . htmlspecialchars($runs['licence_code'] ?? '-') . '</span></td>';
            }
            if (in_array('country', $visible_basic_columns)) {
                $table_body .= '<td class="css-country"><span>' . htmlspecialchars($runs['country'] ?? '-') . '</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'], 0) . ']</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+_figures$/', $h)) {
                    // Extract heat and run numbers from header
                    preg_match('/^H(\d+)R(\d+)_figures$/', $h, $matches);
                    $heat_num = $matches[1];
                    $run_num = $matches[2];
                    $run_code = "H{$heat_num}R{$run_num}";
                    
                    $table_body .= '<td class="' . getColClass($h) . '">';
                    
                    // Get figures data from judge scores for this specific run
                    $figures_found = false;
                    $figures_display = [];
                    
                    if (isset($runs['judge_scores'][$run_code])) {
                        foreach ($runs['judge_scores'][$run_code] as $judge_data) {
                            if (!empty($judge_data['figures_json'])) {
                                $figures_data = json_decode($judge_data['figures_json'], true);
                                if (is_array($figures_data)) {
                                    foreach ($figures_data as $category => $items) {
                                        // Skip validation data - only show figures
                                        if ($category === 'validation') {
                                            continue;
                                        }
                                        
                                        // If this is a figures object, iterate through its properties
                                        if ($category === 'figures' && is_array($items)) {
                                            foreach ($items as $figure_category => $figure_items) {
                                                if (is_array($figure_items) && !empty($figure_items)) {
                                                    // Map figure categories to display labels
                                                    $category_map = [
                                                        'Rotation' => 'Rot',
                                                        'Direction' => 'Dir', 
                                                        'Axis' => 'Axi',
                                                        'Grab' => 'Gra',
                                                        'Feature' => 'Fea',
                                                        'Rail' => 'Rai'
                                                    ];
                                                    
                                                    $display_category = $category_map[$figure_category] ?? $figure_category;
                                                    
                                                    $figures_display[] = '<div class="figure-category" style="display: inline-block;">';
                                                    $figures_display[] = '<strong>' . htmlspecialchars($display_category) . ':</strong> ';
                                                    
                                                    // Convert items to strings, handling nested arrays
                                                    $string_items = convertArrayItemsToString($figure_items);
                                                    
                                                    // Additional safety check - filter out any remaining arrays
                                                    $safe_items = [];
                                                    foreach ($string_items as $item) {
                                                        if (is_array($item)) {
                                                            $safe_items[] = convertNestedArrayToString($item);
                                                        } else {
                                                            $safe_items[] = (string)$item;
                                                        }
                                                    }
                                                    
                                                    $figures_display[] = htmlspecialchars(implode(', ', $safe_items));
                                                    $figures_display[] = ', </div>';
                                                    $figures_found = true;
                                                }
                                            }
                                            continue;
                                        }

                                        if (is_array($items) && !empty($items)) {
                                            // Handle legacy format or other categories
                                            $category = @trim((string)$category);
                                            $figures_display[] = '<div class="figure-category" style="display: inline-block;">';
                                            $figures_display[] = '<strong>' . htmlspecialchars($category) . ':</strong> ';
                                            // Convert items to strings, handling nested arrays
                                            $string_items = convertArrayItemsToString($items);
                                            
                                            // Additional safety check - filter out any remaining arrays
                                            $safe_items = [];
                                            foreach ($string_items as $item) {
                                                if (is_array($item)) {
                                                    $safe_items[] = convertNestedArrayToString($item);
                                                } else {
                                                    $safe_items[] = (string)$item;
                                                }
                                            }
                                            
                                            $figures_display[] = htmlspecialchars(implode(', ', $safe_items));
                                            $figures_display[] = ', </div>';
                                            $figures_found = true;
                                        }
                                    }
                                }
                                break; // Use figures from first judge that has them
                            }
                        }
                    }
                    
                    if ($figures_found) {
                        $table_body .= '<div class="figures-breakdown">' . implode('', $figures_display) . '</div>';
                    } else {
                        $table_body .= '<span class="text-muted">-</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)) {
                        // Get scoring format for drop rule formatting
                        $scoring_format = loadScoringFormatConfig($pdo, $selected_event);
                        $formatted_scores = formatJudgeScoresWithDrops($judge_scores, $scoring_format);
                        
                        $table_body .= '<div class="judge-breakdown">';
                        if (!empty($formatted_scores)) {
                            // Use the formatted scores with drop rule indicators
                            $table_body .= implode('', $formatted_scores);
                        } else {
                            // Fallback to original format if function fails
                            foreach ($judge_scores as $judge_data) {
                                $table_body .= '[' . number_format($judge_data['score'], 0) . ']';
                            }
                        }
                        $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++;
                    // Ensure $runs is an array before imploding
                    $runs_array = is_array($runs) ? $runs : [$runs];
                    $selected_combinations[] = "Heat {$heat} (Runs: " . implode(',', array_map('strval', $runs_array)) . ")";
                }
            }
            $filter_summary = "Filtered calculations using {$total_selected_heats} heat(s): " . implode(' | ', $selected_combinations);
                } else {
                    $filter_summary = "All available heats and runs included in calculations";
                }
                // Build visible basic columns list with renames
                $visible_basic_columns_renamed = [];
                if ($column_filters['show_rank']) $visible_basic_columns_renamed[] = applyColumnRename('Rank', $column_renames);
                if ($column_filters['show_bib']) $visible_basic_columns_renamed[] = applyColumnRename('BIB', $column_renames);
                if ($column_filters['show_participant']) $visible_basic_columns_renamed[] = applyColumnRename('Participant', $column_renames);
                if ($column_filters['show_category']) $visible_basic_columns_renamed[] = applyColumnRename('Category', $column_renames);
                if ($column_filters['show_club']) $visible_basic_columns_renamed[] = applyColumnRename('Club', $column_renames);
                if ($column_filters['show_gender']) $visible_basic_columns_renamed[] = applyColumnRename('Gender', $column_renames);
                if ($column_filters['show_fis_code']) $visible_basic_columns_renamed[] = applyColumnRename('fis_code', $column_renames);
                if ($column_filters['show_licence_code']) $visible_basic_columns_renamed[] = applyColumnRename('licence_code', $column_renames);
                if ($column_filters['show_country']) $visible_basic_columns_renamed[] = applyColumnRename('country', $column_renames);

                // Build renamed headers array for CSV output
                $renamed_headers = array_merge($visible_basic_columns_renamed, array_map(function($h) use ($column_renames) {
                    return applyColumnRename($h, $column_renames);
                }, $data['final_headers']));

                // Add event settings row if enabled
                if ($column_filters['show_event_settings']) {
                    $event_settings = getEventFormatSettings($pdo, $selected_event, $show_format_name, $show_judge_info, $show_scale_info, $show_drop_rule, $show_mode_config, $show_heat_info);
                    if (!empty($event_settings)) {
                        // Calculate colspan for the settings row
                        $settings_colspan = count($visible_basic_columns_renamed) + count($data['final_headers']);
                        
                        $table_body .= '<tr class="event-settings-row table-info">';
                        $table_body .= '<td colspan="' . $settings_colspan . '" class="event-settings-cell">';
                        $table_body .= '<div class="event-settings-content">';
                        $table_body .= '<strong><i class="fas fa-cog me-2"></i>Event Format Settings:</strong> ';
                        
                        $settings_display = [];
                        foreach ($event_settings as $setting) {
                            $settings_display[] = '<span class="setting-item"><strong>' . htmlspecialchars($setting['label']) . ':</strong> ' . htmlspecialchars($setting['value']) . '</span>';
                        }
                        
                        $table_body .= implode(' | ', $settings_display);
                        $table_body .= '</div>';
                        $table_body .= '</td>';
                        $table_body .= '</tr>';
                    } else {
                        // Add debug row if no settings found
                        $settings_colspan = count($visible_basic_columns_renamed) + count($data['final_headers']);
                        $table_body .= '<tr class="event-settings-row table-warning">';
                        $table_body .= '<td colspan="' . $settings_colspan . '" class="event-settings-cell">';
                        $table_body .= '<div class="event-settings-content">';
                        $table_body .= '<strong><i class="fas fa-exclamation-triangle me-2"></i>Event Settings:</strong> No format settings found for event ID: ' . $selected_event;
                        $table_body .= '</div>';
                        $table_body .= '</td>';
                        $table_body .= '</tr>';
                    }
                }

                // Generate CSS styles for participant badges and append to table body
                $table_css = generateParticipantCSS($participant_colors);
                
                // Keep empty array for JSON compatibility
                $badge_css_classes = [];

                // Build response
                $response = [
                    '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'],
                    'renamed_headers' => $renamed_headers,  // Add renamed headers for CSV
                    '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'],
                    'is_grouped' => isset($grouped_by_heat_category) && $grouped_by_heat_category,
                    'group_count' => isset($heat_category_groups) ? count($heat_category_groups) : 0,
                    'scoring_format' => $data['scoring_format'] ?? null,
                    'table_badge_colors' => htmlspecialchars($table_css)
                    //'badge_css_classes' => htmlspecialchars($participant_colors)
                ];

                // Add clean participant data for TV overlay (when tv_overlay=1 parameter is present)
                if (isset($_GET['tv_overlay']) && $_GET['tv_overlay'] == '1') {
                    $response['participants'] = extractCleanParticipantData($table_body);
                }

                // Add debug information if debug mode is enabled
                if ($config['debug_mode']) {
                    $response['debug'] = [
                        'config_applied' => $applied_config_id > 0,
                        'all_headers' => $data['all_headers'],
                        'available_columns' => $data['available_columns'],
                        'column_renames_received' => $column_renames,
                        'column_renames_applied_count' => count($column_renames),
                        '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
                        ],
                        'grouping_debug' => $grouping_debug ?? [],
                        'raw_summary_data_categories' => array_keys($data['summary_data'] ?? [])
                    ];
                    
                    if (isset($heat_category_groups)) {
                        $response['debug']['heat_category_groups'] = array_map(function($group, $key) {
                            return [
                                'group_name' => $group['group_name'],
                                'category' => $group['category'],
                                'participant_count' => count($group['participants'])
                            ];
                        }, $heat_category_groups, array_keys($heat_category_groups));
                    }
                }

                    // After building $response, handle formats:
                    if ($format === 'csv') {
                        header('Content-Type: text/csv; charset=utf-8');
                        // Add UTF-8 BOM for better Excel compatibility
                        echo "\xEF\xBB\xBF";
                        $csv = '';
                        // Output renamed headers
                        $csv .= implode(',', array_map('strval', $response['renamed_headers'])) . "\n";
                        // Output rows (strip HTML tags)
                        foreach (explode('</tr>', $response['table_body']) as $row) {
                            $row = trim(strip_tags(str_replace(['<td', '<th'], '<td', $row)));
                            if ($row) {
                                $row = preg_replace('/<[^>]+>/', '', $row); // Remove tags
                                $row = str_replace(["\n", "\r"], '', $row);
                                $csv .= $row . "\n";
                            }
                        }
                        echo $csv;
                        exit;
                    } elseif ($format === 'html' || $format === 'pdf') {

                    
                        $styling_mode = $_GET['styling'] ?? 'full';
                    
                        $event_id = $selected_event ?? ($_GET['event_id'] ?? null);
                        $config_id = $_GET['config_id'] ?? null;

                        // Prepare API call to results_styling.php
                        $styling_api_url = dirname(__FILE__) . '/../admin/results_styling.php';
                        $api_endpoint = $_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['HTTP_HOST'] . '/v2/admin/results_styling.php';
                        $post_fields = http_build_query([
                            'action' => 'get_styled_header_footer',
                            'event_id' => $selected_event,
                            'config_id' => null
                        ]);

                        // Use cURL to call the API
                        $ch = curl_init($api_endpoint);
                        curl_setopt($ch, CURLOPT_POST, true);
                        curl_setopt($ch, CURLOPT_POSTFIELDS, $post_fields);
                        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                        $styling_json = curl_exec($ch);
                        curl_close($ch);

                        $styling_data = json_decode($styling_json, true);

                        if ($format === 'html') {
                            header('Content-Type: text/html; charset=utf-8');
                            echo '<!DOCTYPE html><html><head><meta charset="UTF-8"><title>Summary Table</title></head><body class="ss_results format_pdf pdf_body">';

                             echo "$summary_title";
                            echo '<style>' . $response['table_badge_colors'] . '</style>';
                            echo $styling_data['header_html'] ?? '';
                            echo "<table class=\"table table-striped table-hover data-table\"><thead>{$response['table_head']}</thead>";
                            echo "<tbody>{$response['table_body']}</tbody></table>";
                            echo $styling_data['footer_html'] ?? '';
                            echo '</body></html>';
                            exit;
                        }
                    if ($format === 'pdf') {
                    
                                    // Get HTML content (reuse your HTML rendering logic)
                                
                                    // Use your styled HTML output block here:
                                    $styling_mode = $_GET['styling'] ?? '';
                                
                                            //echo "$summary_title";
                                            //echo $styling_data['header_html'] ?? '';
                                            //echo "<thead>{$response['table_head']}</thead>";
                                            //echo "<tbody>{$response['table_body']}</tbody>";
                                            //echo $styling_data['footer_html'] ?? '';   

                                    //$html = ob_get_clean();
                                    $html = '<html>
                                <style>' . $response['table_badge_colors'] . '</style>
                                <body class="pdf-body">
                                

                                    
                                    

                                    ' . $styling_data['header_html'] .' <table class="table table-striped table-hover data-table"><thead class=table-dark>'. $response['table_head'] . '</thead><tbody>' . $response['table_body'] . '</tbody></table>

                                </body>
                                </html>';
                                    // Setup dompdf
                                    $options = new Options();
                                    $options->set('isHtml5ParserEnabled', true);
                                    $options->set('isRemoteEnabled', true); // For images/logos
                                    //print_r($options); 
                                    $dompdf = new Dompdf($options);
                                    //$dompdf->loadHtml($response['table_body']);
                                    $dompdf->loadHtml($html);
                                    $pdo->exec("SET NAMES 'utf8mb4'");
                                    $dompdf->setPaper('A4', 'portrait');
                                    $dompdf->render();
                                        // Add page numbers
                                    $canvas = $dompdf->getCanvas();
                                    $font = $dompdf->getFontMetrics()->get_font("HelveticaCustom", "normal");
                                    $canvas->page_text(530, 822, "Page {PAGE_NUM} of {PAGE_COUNT}", $font, 6, [0,0,0]);
                                    // Serve PDF
                                    header('Content-Type: application/pdf');
                                    header('Content-Disposition: inline; filename="summary_table.pdf"');
                                    echo $dompdf->output();
                                    exit;
                }

        } else {
                // Default: JSON with proper UTF-8 encoding
                header('Content-Type: application/json; charset=utf-8');
                echo json_encode($response, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            }
} catch (Exception $e) {
    if ($format === 'csv') {
        header('Content-Type: text/csv; charset=utf-8');
        echo "\xEF\xBB\xBF"; // UTF-8 BOM for Excel compatibility
        echo "Error,Database error: {$e->getMessage()}\n";
    } elseif ($format === 'html') {
        header('Content-Type: text/html; charset=utf-8');
        
        echo '<link id="" href="/v2/assets/bootswatch/zephyr/bootstrap.min.css" rel="stylesheet">';
        echo '<link href="/v2/assets/css/global.css" rel="stylesheet">';
        echo '<!DOCTYPE html><html><head><meta charset="UTF-8"><title>Error</title></head><body>';
        echo "<div class='alert alert-danger'>Database error: {$e->getMessage()}</div>";
        echo '</body></html>';
    } else {
        header('Content-Type: application/json; charset=utf-8');
        echo json_encode([
            'success' => false,
            'message' => 'Database error: ' . $e->getMessage(),
            'error_details' => [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
                'trace' => $e->getTraceAsString()
            ]
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    }
}

function compareSortValues($a, $b, $sort_by, $sort_direction = 'desc') {
    // Get values to compare
    $a_val = getSortValue($a, $sort_by);
    $b_val = getSortValue($b, $sort_by);
    
    // Handle numeric vs string comparison
    if (is_numeric($a_val) && is_numeric($b_val)) {
        $result = floatval($b_val) <=> floatval($a_val); // Default desc for numbers
    } else {
        $result = strcasecmp($a_val, $b_val); // Case-insensitive string comparison
    }
    
    // Apply sort direction
    return $sort_direction === 'asc' ? -$result : $result;
}

function getSortValue($participant, $sort_by) {
    // Handle special cases
    switch ($sort_by) {
        case 'bib':
            return $participant['bib'] ?? 0;
        case 'participant_name':
            return $participant['participant_name'] ?? $participant['first_name'] . ' ' . $participant['last_name'] ?? '';
        case 'category':
            return $participant['category'] ?? '';
        case 'club':
            return $participant['club'] ?? '';
        case 'gender':
            return $participant['gender'] ?? '';
        default:
            // For score columns, handle '-' values
            $value = $participant[$sort_by] ?? '-';
            return $value === '-' ? -999999 : $value; // Put '-' values at the bottom
    }
}

// After getting the data but before processing:

if (!isValidSortColumn($sort_by, $data['final_headers'])) {
    $sort_by = 'OverallAverage'; // fallback to default
}

if (!in_array($sort_direction, ['asc', 'desc'])) {
    $sort_direction = 'desc'; // fallback to default
}

// Inside loadConfigurationById function

// Add after checking category
if (isset($config_data['settings']['categoryId']) && $config_data['settings']['categoryId'] > 0) {
    $filter_category_id = (int)$config_data['settings']['categoryId'];
    // Always set category_id and look up the name for display only
    $cat_stmt = $pdo->prepare("SELECT category_name FROM event_categories WHERE id = ? AND event_id = ? LIMIT 1");
    $cat_stmt->execute([$filter_category_id, $config_data['event_id']]);
    $category_name = $cat_stmt->fetchColumn();
    if ($category_name) {
        $filter_category = $category_name;
    }
}


// Function to get participants to promote from a heat based on heat flow settings.
/**
 * Get participants to promote from a heat based on heat flow settings.
 * @param PDO $pdo
 * @param int $event_id
 * @param int $target_heat_number
 * @return array List of participants to promote (with bib, participant_id, category, etc)
 */
function getParticipantsToPromote($pdo, $event_id, $target_heat_number) {
    // 1. Get heat settings for the target heat
    $stmt = $pdo->prepare("SELECT flow_source_heat, flow_participants_per_category, categories FROM event_heat_settings WHERE event_id = ? AND heat_number = ?");
    $stmt->execute([$event_id, $target_heat_number]);
    $heat = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$heat || !$heat['flow_source_heat'] || !$heat['flow_participants_per_category']) {
        return ['success' => false, 'message' => 'Heat flow settings not configured for this heat.'];
    }

    $source_heat = (int)$heat['flow_source_heat'];
    $promote_count = (int)$heat['flow_participants_per_category'];

    // 2. Get categories for this heat
    $categories = [];
    if (!empty($heat['categories'])) {
        $categories = json_decode($heat['categories'], true);
        if (!is_array($categories)) $categories = [];
    }

    // 3. For each category, get top N participants from the source heat using summary_table_api logic
    $participants_to_promote = [];
    $count_all = 0;
    foreach ($categories as $category_id) {
        // Get category name
        $cat_stmt = $pdo->prepare("SELECT id FROM event_categories WHERE id = ? AND event_id = ?");
        $cat_stmt->execute([$category_id, $event_id]);
        $category_name = $cat_stmt->fetchColumn();

        // Build heat_run_filter for the source heat (all runs)
        $runs_stmt = $pdo->prepare("SELECT runs_count FROM event_heat_settings WHERE event_id = ? AND heat_number = ?");
        $runs_stmt->execute([$event_id, $source_heat]);
        $runs_count = (int)$runs_stmt->fetchColumn() ?: 1;
        $heat_run_filter = json_encode([$source_heat => range(1, $runs_count)]);

        // Call this API recursively (internal call) to get summary data for the source heat/category
        $_GET_backup = $_GET;
        $_GET['event_id'] = $event_id;
        $_GET['category_id'] = $category_id;
        $_GET['heat_run_filter'] = $heat_run_filter;
        $_GET['format'] = 'json';

        // Generate summary data for this category and source heat
$column_filters = [
    'heat_run_filter' => parseHeatRunFilter($heat_run_filter),
    'heat_run_filter_json' => $heat_run_filter,
    'show_runs' => false,
    'show_judges' => false,
    'show_control_points' => false,
    'show_heat_best' => true,
    'show_heat_average' => true,
    'show_overall_best' => true,
    'show_highest_average' => true,
    'show_rank' => true,
    'show_bib' => true,
    'show_participant' => true,
    'show_category' => true,
    'show_club' => true,
    'show_gender' => true,
    'show_fis_code' => false,
    'show_licence_code' => false,
    'show_country' => false
];
        $data = generateSummaryTableData($pdo, $event_id, $category_name, 'all', 'OverallAverage', $column_filters);

        // Get participants for this category, sorted by OverallAverage desc
        $participants = $data['summary_data'][$category_name] ?? [];
        uasort($participants, function($a, $b) {
            $a_val = is_numeric($a['OverallAverage'] ?? null) ? floatval($a['OverallAverage']) : -999999;
            $b_val = is_numeric($b['OverallAverage'] ?? null) ? floatval($b['OverallAverage']) : -999999;
            return $b_val <=> $a_val;
        });

        $count = 0;
        foreach ($participants as $p) {
    if ($count++ >= $promote_count) break;
    // Get full event_participants row for this participant in the source heat using bib_number
    $ep_stmt = $pdo->prepare("
        SELECT * FROM event_participants
        WHERE event_id = ? AND heat_number = ? AND bib_number = ?
        LIMIT 1
    ");
    $ep_stmt->execute([$event_id, $source_heat, $p['bib']]);
    $ep_row = $ep_stmt->fetch(PDO::FETCH_ASSOC);

    if ($ep_row) {
        $ep_row['sort_order'] = $count_all;
        $participants_to_promote[] = $ep_row;
    } else {
        // fallback to summary data if not found
        $participants_to_promote[] = [
            'bib' => $p['bib'],
            'participant_id' => $p['participant_id'] ?? null,
            'category' => $p['category'],
            'category_id' => $category_id,
            'first_name' => $p['first_name'] ?? '',
            'last_name' => $p['last_name'] ?? '',
            'club' => $p['club'] ?? '',
            'gender' => $p['gender'] ?? '',
            'matching_category_ids' => $p['matching_category_ids'] ?? null,
            'overall_average' => $p['OverallAverage'] ?? null
        ];
    }
    $count_all++;
}
        $_GET = $_GET_backup; // Restore original GET
        
    }

    return [
        'success' => true,
        'participants' => $participants_to_promote,
        'source_heat' => $source_heat,
        'promote_count' => $promote_count
    ];
}