<?php
// Suppress any PHP errors/warnings that could break JSON output
ini_set('display_errors', 0);
error_reporting(0);

header('Content-Type: application/json');

try {
    include '../includes/db.php';
} catch (Exception $e) {
    echo json_encode(['success' => false, 'message' => 'Database connection failed']);
    exit;
}

$action = $_GET['action'] ?? $_POST['action'] ?? null;

function respond($payload) {
    echo json_encode($payload);
    exit;
}

/**
 * Get category id=>name map for an event.
 *
 * @param PDO $pdo
 * @param int $event_id
 * @return array<int,string>
 */
function getCategoryMap(PDO $pdo, int $event_id): array {
    $stmt = $pdo->prepare("SELECT id, category_name FROM event_categories WHERE event_id = ?");
    $stmt->execute([$event_id]);
    $map = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $map[(int)$row['id']] = $row['category_name'];
    }
    return $map;
}

/**
 * Build reports list for an event, optionally filtered by heat.
 * Returns start list placeholders and summary table configurations.
 *
 * @param PDO $pdo
 * @param int $event_id
 * @param int|null $heat_number
 * @return array
 */
function getReportsForEvent(PDO $pdo, int $event_id, ?int $heat_number = null): array {
    // Heats basic info
    $heats_sql = "
        SELECT heat_number, heat_name, categories
        FROM event_heat_settings
        WHERE event_id = ?
        " . ($heat_number !== null ? "AND heat_number = ?" : "") . "
        ORDER BY heat_number
    ";
    $heats_stmt = $pdo->prepare($heats_sql);
    $heats_stmt->execute($heat_number !== null ? [$event_id, $heat_number] : [$event_id]);
    $heats = $heats_stmt->fetchAll(PDO::FETCH_ASSOC);

    // Active configurations (summary tables)
    $configs_stmt = $pdo->prepare("
        SELECT id, name, view_type, heat_number, category, display_order
        FROM result_configurations
        WHERE event_id = ? AND status = 'active'
        " . ($heat_number !== null ? "AND (heat_number = ? OR heat_number IS NULL)" : "") . "
        ORDER BY heat_number, display_order, name
    ");
    $configs_stmt->execute($heat_number !== null ? [$event_id, $heat_number] : [$event_id]);
    $configs = $configs_stmt->fetchAll(PDO::FETCH_ASSOC);

    // Group configurations by heat_number (NULL => event-level)
    $configsByHeat = [];
    foreach ($configs as $cfg) {
        $hn = isset($cfg['heat_number']) ? (string)$cfg['heat_number'] : 'event';
        if ($hn === '' || $hn === '0') $hn = 'event';
        if (!isset($configsByHeat[$hn])) $configsByHeat[$hn] = [];
        $configsByHeat[$hn][] = [
            'id' => (int)$cfg['id'],
            'name' => $cfg['name'],
            'view_type' => $cfg['view_type'],
            'heat_number' => $cfg['heat_number'] !== null ? (int)$cfg['heat_number'] : null,
            'category' => $cfg['category'],
            'display_order' => (int)$cfg['display_order'],
            // URL is template-based on client; provide params for convenience
            'params' => [
                'config_id' => (int)$cfg['id'],
                'event_id' => $event_id,
                'heat_number' => $cfg['heat_number'] !== null ? (int)$cfg['heat_number'] : null,
            ],
        ];
    }

    // Categories map (optional enrichment)
    $catMap = getCategoryMap($pdo, $event_id);

    // Build per-heat report set
    $heatsReports = [];
    foreach ($heats as $h) {
        $hn = (int)$h['heat_number'];
        $catNames = [];
        if (!empty($h['categories'])) {
            $ids = json_decode($h['categories'], true);
            if (is_array($ids)) {
                foreach ($ids as $cid) {
                    $catNames[] = $catMap[$cid] ?? (string)$cid;
                }
            }
        }
        $heatsReports[] = [
            'heat_number' => $hn,
            'heat_name' => $h['heat_name'],
            'categories' => $catNames,
            'start_list' => [
                'title' => 'Start List',
                'params' => [
                    'event_id' => $event_id,
                    'heat_number' => $hn,
                ],
                // Client can apply its own template: start_list.php?event_id={eventId}&heat_number={heatNumber}
                'url' => null,
            ],
            'summary_tables' => $configsByHeat[(string)$hn] ?? [],
        ];
    }

    // Event-level (no heat) configurations
    $eventLevelReports = $configsByHeat['event'] ?? [];

    // Provide URL templates as hints (client may override)
    $urlTemplates = [
        'start_list' => 'start_list.php?event_id={eventId}&heat_number={heatNumber}',
        'summary_table' => 'results_summary.php?config_id={configId}',
    ];

    return [
        'event_id' => $event_id,
        'url_templates' => $urlTemplates,
        'event_level' => $eventLevelReports,
        'heats' => $heatsReports,
    ];
}

try {
    if ($action === 'get_events_overview') {
        $events_stmt = $pdo->prepare("
            SELECT 
                e.id, e.name, e.date, e.heats_total, e.runs_per_heat, e.status,
                ehs.heat_number as current_heat, ehs.active_run as current_run,
                COUNT(DISTINCT ep.id) as total_participants,
                COUNT(DISTINCT ja.judge_id) as total_judges
            FROM events e
            LEFT JOIN event_participants ep ON e.id = ep.event_id
            LEFT JOIN judge_assignments ja ON e.id = ja.event_id
            LEFT JOIN event_heat_settings ehs ON e.id = ehs.event_id AND ehs.status = 'active'
            WHERE e.status IN ('active', 'upcoming', 'in_progress', 'live')
            GROUP BY e.id, e.name, e.date, e.heats_total, e.runs_per_heat, e.status, ehs.heat_number, ehs.active_run
            ORDER BY e.date DESC, e.name
        ");
        $events_stmt->execute();
        $events = $events_stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($events as &$event) {
            // Check if figures_json column exists first
            $hasFiguresColumn = false;
            try {
                $checkColumn = $pdo->query("SHOW COLUMNS FROM scores LIKE 'figures_json'");
                $hasFiguresColumn = $checkColumn->rowCount() > 0;
            } catch (Exception $e) {
                $hasFiguresColumn = false;
            }
            
            // latest score - build query based on column availability
            $figuresSelect = $hasFiguresColumn ? 's.figures_json,' : '';
            $latest_score_stmt = $pdo->prepare("
                SELECT s.score_value, s.created_at, s.run_id, {$figuresSelect}
                       p.first_name, p.last_name, p.club, p.country, p.photo,
                       ep.bib_number, ep.category_id,
                       ec.category_name as category,
                       r.run_number,
                       ehs.heat_name
                FROM scores s
                JOIN runs r ON s.run_id = r.id
                JOIN event_participants ep ON r.event_participant_id = ep.id
                JOIN participants p ON ep.participant_id = p.id
                LEFT JOIN event_categories ec ON ep.category_id = ec.id
                LEFT JOIN event_heat_settings ehs ON ep.event_id = ehs.event_id AND ehs.heat_number = ep.heat_number
                WHERE ep.event_id = ?
                ORDER BY s.created_at DESC
                LIMIT 1
            ");
            $latest_score_stmt->execute([$event['id']]);
            $latest_score = $latest_score_stmt->fetch(PDO::FETCH_ASSOC);
            
            // Calculate proper ranking based on summary table logic
            if ($latest_score) {
                // Get the participant's best score and rank using summary table logic
                $rank_stmt = $pdo->prepare("
                    SELECT 
                        ep.id as participant_id,
                        ep.bib_number,
                        p.first_name, 
                        p.last_name,
                        ep.category_id,
                        ec.category_name,
                        MAX(s.score_value) as best_score,
                        ROW_NUMBER() OVER (
                            PARTITION BY ep.category_id 
                            ORDER BY MAX(s.score_value) DESC
                        ) as category_rank,
                        ROW_NUMBER() OVER (
                            ORDER BY MAX(s.score_value) DESC
                        ) as overall_rank
                    FROM event_participants ep
                    JOIN participants p ON ep.participant_id = p.id
                    LEFT JOIN event_categories ec ON ep.category_id = ec.id
                    LEFT JOIN runs r ON ep.id = r.event_participant_id
                    LEFT JOIN scores s ON r.id = s.run_id
                    WHERE ep.event_id = ?
                    GROUP BY ep.id, ep.bib_number, p.first_name, p.last_name, ep.category_id, ec.category_name
                    HAVING MAX(s.score_value) IS NOT NULL
                ");
                $rank_stmt->execute([$event['id']]);
                $rankings = $rank_stmt->fetchAll(PDO::FETCH_ASSOC);
                
                // Find the current participant's ranking
                foreach ($rankings as $ranking) {
                    if ($ranking['bib_number'] == $latest_score['bib_number']) {
                        $latest_score['current_rank'] = $ranking['category_rank'];
                        $latest_score['overall_rank'] = $ranking['overall_rank'];
                        break;
                    }
                }
                
                // If no rank found, set to null
                if (!isset($latest_score['current_rank'])) {
                    $latest_score['current_rank'] = null;
                    $latest_score['overall_rank'] = null;
                }
            }
            
            // Process figure information if available
            if ($latest_score && $hasFiguresColumn && !empty($latest_score['figures_json'])) {
                $figures_data = json_decode($latest_score['figures_json'], true);
                if (is_array($figures_data) && !empty($figures_data)) {
                    // Handle different possible structures for figures
                    $figure_names = [];
                    foreach ($figures_data as $figure) {
                        if (is_string($figure)) {
                            $figure_names[] = $figure;
                        } elseif (is_array($figure) && isset($figure['name'])) {
                            $figure_names[] = $figure['name'];
                        } elseif (is_array($figure) && isset($figure['figure_name'])) {
                            $figure_names[] = $figure['figure_name'];
                        }
                    }
                    $latest_score['figure'] = !empty($figure_names) ? implode(', ', $figure_names) : null;
                } else {
                    $latest_score['figure'] = null;
                }
            } else {
                $latest_score['figure'] = null;
            }
            
            $event['latest_score'] = $latest_score ?: null;
            
            // Debug: Log the actual structure
            if ($latest_score) {
                error_log("Latest score data for event " . $event['id'] . ": " . print_r($latest_score, true));
            } else {
                error_log("No latest score data found for event " . $event['id']);
            }

            // judge count for next_bib calc
            $judge_count_stmt = $pdo->prepare("SELECT COUNT(DISTINCT judge_id) FROM judge_assignments WHERE event_id = ?");
            $judge_count_stmt->execute([$event['id']]);
            $judge_count = (int)$judge_count_stmt->fetchColumn();

            // next BIB for current heat/run
            $next_bib_stmt = $pdo->prepare("
                    SELECT ep.bib_number, ep.category_id,
                           p.first_name, p.last_name, p.club, p.country, p.photo,
                           ec.category_name as category,
                           ehs.heat_name
                    FROM event_participants ep
                    JOIN participants p ON ep.participant_id = p.id
                    LEFT JOIN event_categories ec ON ep.category_id = ec.id
                    LEFT JOIN event_heat_settings ehs ON ep.event_id = ehs.event_id AND ehs.heat_number = ep.heat_number
                    WHERE ep.event_id = ? AND ep.heat_number = ?
                    AND ep.id NOT IN (
                        SELECT DISTINCT r.event_participant_id
                        FROM runs r
                        JOIN scores s ON r.id = s.run_id
                        WHERE r.run_number = ?
                        GROUP BY r.event_participant_id
                        HAVING COUNT(s.id) >= ?
                    )
                    ORDER BY ep.bib_number
                    LIMIT 1
                ");
            $next_bib_stmt->execute([
                $event['id'],
                $event['current_heat'] ?? 1,
                $event['current_run'] ?? 1,
                $judge_count
            ]);
            $next_bip_data = $next_bib_stmt->fetch(PDO::FETCH_ASSOC);
            
            // Calculate proper ranking for next participant if they have scores
            if ($next_bip_data) {
                // Get the same ranking data we calculated above
                if (isset($rankings)) {
                    foreach ($rankings as $ranking) {
                        if ($ranking['bib_number'] == $next_bip_data['bib_number']) {
                            $next_bip_data['current_rank'] = $ranking['category_rank'];
                            $next_bip_data['overall_rank'] = $ranking['overall_rank'];
                            break;
                        }
                    }
                }
                
                // If no rank found, set to null
                if (!isset($next_bip_data['current_rank'])) {
                    $next_bip_data['current_rank'] = null;
                    $next_bip_data['overall_rank'] = null;
                }
                
                $next_bip_data['figure'] = null; // No figure info available for next participant
            }
            
            $event['next_bib'] = $next_bip_data ?: null;
            
            // Debug: Log the actual structure
            if ($next_bip_data) {
                error_log("Next bib data for event " . $event['id'] . ": " . print_r($next_bip_data, true));
            } else {
                error_log("No next bib data found for event " . $event['id']);
            }
        }

        respond(['success' => true, 'events' => $events]);
    }

    if ($action === 'get_event_heats') {
        $event_id = (int)($_GET['event_id'] ?? 0);
        
        if (!$event_id) {
            echo json_encode(['success' => false, 'message' => 'Invalid event ID']);
            exit;
        }
        
        // Get category names for all categories in this event
        $categories_stmt = $pdo->prepare("
            SELECT id, category_name FROM event_categories 
            WHERE event_id = ?
        ");
        $categories_stmt->execute([$event_id]);
        $category_map = [];
        while ($cat = $categories_stmt->fetch(PDO::FETCH_ASSOC)) {
            $category_map[$cat['id']] = $cat['category_name'];
        }
        
        // judge count
        $judges_count_stmt = $pdo->prepare("SELECT COUNT(*) FROM judge_assignments WHERE event_id = ?");
        $judges_count_stmt->execute([$event_id]);
        $judges_count = (int)$judges_count_stmt->fetchColumn();

        // heats
        $heats_stmt = $pdo->prepare("
    SELECT 
        ehs.id, ehs.event_id, ehs.heat_number, ehs.heat_name, 
        ehs.scoring_type, ehs.runs_count, ehs.categories, ehs.time_start,
        ehs.flow_type, ehs.flow_source_heat, ehs.flow_participants_per_category,
        ehs.is_active, ehs.active_run, ehs.bib_on_start, ehs.bib_latest_on_run,
        COUNT(DISTINCT ep.id) as participants,
        COUNT(DISTINCT CASE WHEN s.is_approved = 1 THEN s.id END) as total_scores,
        (COUNT(DISTINCT ep.id) * ehs.runs_count * ?) as expected_scores
    FROM event_heat_settings ehs
    LEFT JOIN event_participants ep ON ehs.event_id = ep.event_id AND ehs.heat_number = ep.heat_number
    LEFT JOIN runs r ON ep.id = r.event_participant_id
    LEFT JOIN scores s ON r.id = s.run_id
    WHERE ehs.event_id = ?
    GROUP BY ehs.id, ehs.event_id, ehs.heat_number, ehs.heat_name, 
             ehs.scoring_type, ehs.runs_count, ehs.runs_scoring_method,
             ehs.time_start, ehs.estimate_time_per_participant, ehs.categories,
             ehs.flow_type, ehs.flow_source_heat, ehs.flow_participants_per_category,
             ehs.is_active, ehs.active_run, ehs.bib_on_start, ehs.bib_latest_on_run
    ORDER BY ehs.heat_number
");
        $heats_stmt->execute([$judges_count, $event_id]);
        $heats = $heats_stmt->fetchAll(PDO::FETCH_ASSOC);

        // Add category names to each heat
        foreach ($heats as &$heat) {
            if (!empty($heat['categories'])) {
                try {
                    $category_ids = json_decode($heat['categories'], true);
                    if (is_array($category_ids)) {
                        // Map IDs to names using the category_map
                        $category_names = [];
                        foreach ($category_ids as $id) {
                            if (isset($category_map[$id])) {
                                $category_names[] = $category_map[$id];
                            } else {
                                $category_names[] = "ID: $id";
                            }
                        }
                        // Add category_names to the heat data
                        $heat['category_names'] = $category_names;
                    }
                } catch (Exception $e) {
                    // Keep original categories if there's an error
                }
            }
        }
        
        // configurations
        $configs_stmt = $pdo->prepare("
            SELECT id, name, view_type, heat_number, category, display_order
            FROM result_configurations
            WHERE event_id = ? AND status = 'active'
            ORDER BY heat_number, display_order, name
        ");
        $configs_stmt->execute([$event_id]);
        $configurations = $configs_stmt->fetchAll(PDO::FETCH_ASSOC);

        respond(['success' => true, 'heats' => $heats, 'configurations' => $configurations]);
    }

    if ($action === 'get_configurations') {
        $event_id = $_GET['event_id'] ?? $_POST['event_id'] ?? null;
        if (!$event_id) respond(['success' => false, 'message' => 'Missing event ID']);

        $configs_stmt = $pdo->prepare("
            SELECT id, name, view_type, heat_number, category, display_order
            FROM result_configurations
            WHERE event_id = ? AND status = 'active'
            ORDER BY display_order, name
        ");
        $configs_stmt->execute([$event_id]);
        $configurations = $configs_stmt->fetchAll(PDO::FETCH_ASSOC);

        respond(['success' => true, 'configurations' => $configurations]);
    }

    if ($action === 'get_active_heat_details') {
        $event_id = (int)($_GET['event_id'] ?? 0);
        
        if (!$event_id) {
            echo json_encode(['success' => false, 'message' => 'Invalid event ID']);
            exit;
        }
        
        // Get active heat details with participant names
        $heats_stmt = $pdo->prepare("
            SELECT 
                hs.heat_number, 
                hs.is_active, 
                hs.active_run, 
                hs.bib_on_start,
                CONCAT(p_start.first_name, ' ', p_start.last_name) as bib_start_name,
                hs.bib_latest_on_run,
                CONCAT(p_latest.first_name, ' ', p_latest.last_name) as bib_latest_name
            FROM event_heat_settings hs
            LEFT JOIN event_participants ep_start ON (hs.event_id = ep_start.event_id AND hs.heat_number = ep_start.heat_number AND ep_start.bib_number = hs.bib_on_start)
            LEFT JOIN participants p_start ON ep_start.participant_id = p_start.id
            LEFT JOIN event_participants ep_latest ON (hs.event_id = ep_latest.event_id AND hs.heat_number = ep_latest.heat_number AND ep_latest.bib_number = hs.bib_latest_on_run)
            LEFT JOIN participants p_latest ON ep_latest.participant_id = p_latest.id
            WHERE hs.event_id = ? AND hs.status = 'active'
        ");
        $heats_stmt->execute([$event_id]);
        $active_heats = $heats_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Format data as key-value by heat_number
        $heats_data = [];
        foreach ($active_heats as $heat) {
            $heats_data[$heat['heat_number']] = $heat;
        }
        
        echo json_encode([
            'success' => true,
            'heats' => $heats_data
        ]);
        exit;
    }

    if ($action === 'get_heat_rows') {
        $event_id = (int)($_GET['event_id'] ?? 0);
        
        if (!$event_id) {
            echo json_encode(['success' => false, 'message' => 'Invalid event ID']);
            exit;
        }
        
        // Get heat summary data for updating rows
        $heats_stmt = $pdo->prepare("
            SELECT 
                ehs.heat_number, ehs.heat_name, 
                ehs.scoring_type, ehs.runs_count,
                ehs.is_active, 
                COUNT(DISTINCT ep.id) as participants,
                COUNT(DISTINCT CASE WHEN s.is_approved = 1 THEN s.id END) as total_scores,
                (COUNT(DISTINCT ep.id) * ehs.runs_count * 
                    (SELECT COUNT(*) FROM judge_assignments ja WHERE ja.event_id = ehs.event_id)
                ) as expected_scores
            FROM event_heat_settings ehs
            LEFT JOIN event_participants ep ON ehs.event_id = ep.event_id AND ehs.heat_number = ep.heat_number
            LEFT JOIN runs r ON ep.id = r.event_participant_id
            LEFT JOIN scores s ON r.id = s.run_id
            WHERE ehs.event_id = ?
            GROUP BY ehs.heat_number, ehs.heat_name, ehs.scoring_type, ehs.runs_count, ehs.is_active
            ORDER BY ehs.heat_number
        ");
        $heats_stmt->execute([$event_id]);
        $heats = $heats_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo json_encode([
            'success' => true,
            'heats' => $heats
        ]);
        exit;
    }

    // New: unified reports listing for event/heat
    if ($action === 'get_reports') {
        $event_id = (int)($_GET['event_id'] ?? $_POST['event_id'] ?? 0);
        $heat_number = isset($_GET['heat_number']) || isset($_POST['heat_number'])
            ? (int)($_GET['heat_number'] ?? $_POST['heat_number'])
            : null;

        if (!$event_id) {
            respond(['success' => false, 'message' => 'Missing event ID']);
        }

        $reports = getReportsForEvent($pdo, $event_id, $heat_number);
        respond(['success' => true] + $reports);
    }

    // New: get latest updates for an event (latest score + next on start)
    if ($action === 'get_event_latest_updates') {
        $event_id = (int)($_GET['event_id'] ?? $_POST['event_id'] ?? 0);
        
        if (!$event_id) {
            respond(['success' => false, 'message' => 'Missing event ID']);
        }

        // Get performing now data from event_heat_settings with fallback for missing columns
        try {
            $performing_now_stmt = $pdo->prepare("
                SELECT 
                    ehs.bib_on_start,
                    ehs.bib_latest_on_run,
                    ehs.bib_performing,
                    ehs.heat_number,
                    ehs.heat_name,
                    ehs.is_active,
                    p_start.first_name as start_first_name,
                    p_start.last_name as start_last_name,
                    p_start.club as start_club,
                    p_start.country as start_country,
                    ec_start.category_name as start_category,
                    p_latest.first_name as latest_first_name,
                    p_latest.last_name as latest_last_name,
                    p_latest.club as latest_club,
                    p_latest.country as latest_country,
                    ec_latest.category_name as latest_category,
                    p_performing.first_name as performing_first_name,
                    p_performing.last_name as performing_last_name,
                    p_performing.club as performing_club,
                    p_performing.country as performing_country,
                    p_performing.photo as performing_photo,
                    ec_performing.category_name as performing_category
                FROM event_heat_settings ehs
                LEFT JOIN event_participants ep_start ON (ehs.event_id = ep_start.event_id AND ehs.heat_number = ep_start.heat_number AND ep_start.bib_number = ehs.bib_on_start)
                LEFT JOIN participants p_start ON ep_start.participant_id = p_start.id
                LEFT JOIN event_categories ec_start ON ep_start.category_id = ec_start.id
                LEFT JOIN event_participants ep_latest ON (ehs.event_id = ep_latest.event_id AND ehs.heat_number = ep_latest.heat_number AND ep_latest.bib_number = ehs.bib_latest_on_run)
                LEFT JOIN participants p_latest ON ep_latest.participant_id = p_latest.id
                LEFT JOIN event_categories ec_latest ON ep_latest.category_id = ec_latest.id
                LEFT JOIN event_participants ep_performing ON (ehs.event_id = ep_performing.event_id AND ehs.heat_number = ep_performing.heat_number AND ep_performing.bib_number = ehs.bib_performing)
                LEFT JOIN participants p_performing ON ep_performing.participant_id = p_performing.id
                LEFT JOIN event_categories ec_performing ON ep_performing.category_id = ec_performing.id
                WHERE ehs.event_id = ? AND ehs.status = 'active'
            ");
            $performing_now_stmt->execute([$event_id]);
        } catch (PDOException $e) {
            // Fallback if bib_performing column doesn't exist
            $performing_now_stmt = $pdo->prepare("
                SELECT 
                    ehs.bib_on_start,
                    ehs.bib_latest_on_run,
                    NULL as bib_performing,
                    ehs.heat_number,
                    ehs.heat_name,
                    ehs.is_active,
                    p_start.first_name as start_first_name,
                    p_start.last_name as start_last_name,
                    p_start.club as start_club,
                    p_start.country as start_country,
                    p_start.photo as start_photo,
                    ec_start.category_name as start_category,
                    p_latest.first_name as latest_first_name,
                    p_latest.last_name as latest_last_name,
                    p_latest.club as latest_club,
                    p_latest.country as latest_country,
                    p_latest.photo as latest_photo,
                    ec_latest.category_name as latest_category,
                    NULL as performing_first_name,
                    NULL as performing_last_name,
                    NULL as performing_club,
                    NULL as performing_country,
                    NULL as performing_photo,
                    NULL as performing_category
                FROM event_heat_settings ehs
                LEFT JOIN event_participants ep_start ON (ehs.event_id = ep_start.event_id AND ehs.heat_number = ep_start.heat_number AND ep_start.bib_number = ehs.bib_on_start)
                LEFT JOIN participants p_start ON ep_start.participant_id = p_start.id
                LEFT JOIN event_categories ec_start ON ep_start.category_id = ec_start.id
                LEFT JOIN event_participants ep_latest ON (ehs.event_id = ep_latest.event_id AND ehs.heat_number = ep_latest.heat_number AND ep_latest.bib_number = ehs.bib_latest_on_run)
                LEFT JOIN participants p_latest ON ep_latest.participant_id = p_latest.id
                LEFT JOIN event_categories ec_latest ON ep_latest.category_id = ec_latest.id
                WHERE ehs.event_id = ? AND ehs.status = 'active'
            ");
            $performing_now_stmt->execute([$event_id]);
        }
        
        $performing_data = $performing_now_stmt->fetch(PDO::FETCH_ASSOC);

        // Get latest score (most recent approved score)
        $latest_score_stmt = $pdo->prepare("
            SELECT 
                ep.bib_number, 
                p.first_name, 
                p.last_name,
                p.club, 
                p.country,
                p.photo,
                s.score_value, 
                s.created_at,
                ec.category_name as category,
                ehs.heat_name,
                ehs.heat_number,
                (
                    SELECT COUNT(*) + 1 
                    FROM scores s2 
                    JOIN runs r2 ON r2.id = s2.run_id 
                    JOIN event_participants ep2 ON ep2.id = r2.event_participant_id 
                    WHERE ep2.event_id = ep.event_id 
                    AND s2.is_approved = 1 
                    AND s2.score_value > s.score_value
                ) as current_rank
            FROM scores s
            JOIN runs r ON r.id = s.run_id
            JOIN event_participants ep ON ep.id = r.event_participant_id
            JOIN participants p ON p.id = ep.participant_id
            LEFT JOIN event_categories ec ON ec.id = ep.category_id
            LEFT JOIN event_heat_settings ehs ON ehs.event_id = ep.event_id AND ehs.heat_number = ep.heat_number
            WHERE ep.event_id = ? AND s.is_approved = 1
            ORDER BY s.created_at DESC
            LIMIT 1
        ");
        $latest_score_stmt->execute([$event_id]);
        $latest_score = $latest_score_stmt->fetch(PDO::FETCH_ASSOC);

        // Get next participant on start (first participant without scores in current active heat)
        $next_bib_stmt = $pdo->prepare("
            SELECT 
                ep.bib_number, 
                p.first_name, 
                p.last_name,
                p.club, 
                p.country,
                p.photo,
                ec.category_name as category,
                ehs.heat_name,
                ehs.heat_number,
                (
                    SELECT COUNT(*) + 1 
                    FROM scores s2 
                    JOIN runs r2 ON r2.id = s2.run_id 
                    JOIN event_participants ep2 ON ep2.id = r2.event_participant_id 
                    WHERE ep2.event_id = ep.event_id 
                    AND s2.is_approved = 1
                ) as current_rank
            FROM event_participants ep
            JOIN participants p ON p.id = ep.participant_id
            LEFT JOIN event_categories ec ON ec.id = ep.category_id
            LEFT JOIN event_heat_settings ehs ON ehs.event_id = ep.event_id AND ehs.heat_number = ep.heat_number
            LEFT JOIN runs r ON r.event_participant_id = ep.id
            LEFT JOIN scores s ON s.run_id = r.id AND s.is_approved = 1
            WHERE ep.event_id = ? 
            AND ehs.status = 'active'
            AND s.id IS NULL
            ORDER BY ep.sort_order ASC, ep.bib_number ASC
            LIMIT 1
        ");
        $next_bib_stmt->execute([$event_id]);
        $next_bib = $next_bib_stmt->fetch(PDO::FETCH_ASSOC);
        include_once '../includes/country_codes.php';
        // Structure performing now data
        $performing_now = null;
        if ($performing_data) {
            // If bib_performing exists, use it; otherwise use bib_latest_on_run for active heats
            $performing_bib = $performing_data['bib_performing'] ?: 
                              ($performing_data['is_active'] ? $performing_data['bib_latest_on_run'] : null);
            
            if ($performing_bib) {
                $performing_now = [
                    'bib_number' => $performing_bib,
                    'first_name' => $performing_data['performing_first_name'] ?: $performing_data['latest_first_name'],
                    'last_name' => $performing_data['performing_last_name'] ?: $performing_data['latest_last_name'],
                    'club' => $performing_data['performing_club'] ?: $performing_data['latest_club'],
                    'country' => getCountryEmoji($performing_data['performing_country'] ?: $performing_data['latest_country']),
                    'photo' => $performing_data['performing_photo'] ?: $performing_data['latest_photo'],
                    'category' => $performing_data['performing_category'] ?: $performing_data['latest_category'],
                    'heat_name' => $performing_data['heat_name'],
                    'heat_number' => $performing_data['heat_number']
                ];
            }
        }
        if ($latest_score) $latest_score['country'] = getCountryEmoji($latest_score['country']);
        if ($next_bib) $next_bib['country'] = getCountryEmoji($next_bib['country']);
        respond([
            'success' => true,
            'latest_score' => $latest_score ?: null,
            'next_bib' => $next_bib ?: null,
            'performing_now' => $performing_now,
            'heat_status' => $performing_data ? [
                'bib_on_start' => $performing_data['bib_on_start'],
                'bib_latest_on_run' => $performing_data['bib_latest_on_run'],
                'bib_performing' => $performing_data['bib_performing'],
                'heat_number' => $performing_data['heat_number'],
                'heat_name' => $performing_data['heat_name'],
                'is_active' => $performing_data['is_active']
            ] : null
        ]);
    }

    if ($action === 'get_participant_facts') {
        $participant_id = (int)($_GET['participant_id'] ?? 0);
        
        if (!$participant_id) {
            respond(['success' => false, 'message' => 'Invalid participant ID']);
        }
        
        try {
            $facts_stmt = $pdo->prepare("
                SELECT id, title, fact, sort_order
                FROM participant_facts
                WHERE participant_id = ?
                ORDER BY sort_order, id
            ");
            $facts_stmt->execute([$participant_id]);
            $facts = $facts_stmt->fetchAll(PDO::FETCH_ASSOC);
            
            respond(['success' => true, 'facts' => $facts]);
        } catch (Exception $e) {
            error_log("Error in get_participant_facts: " . $e->getMessage());
            respond(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
    }

    if ($action === 'get_participant_by_bib') {
        $event_id = (int)($_GET['event_id'] ?? 0);
        $bib_number = $_GET['bib_number'] ?? '';
        
        if (!$event_id || !$bib_number) {
            respond(['success' => false, 'message' => 'Missing event ID or BIB number']);
        }
        
        try {
            $participant_stmt = $pdo->prepare("
                SELECT p.id, p.first_name, p.last_name, p.photo, p.club, p.country,
                       ep.bib_number, ep.category_id,
                       ec.category_name as category
                FROM event_participants ep
                JOIN participants p ON ep.participant_id = p.id
                LEFT JOIN event_categories ec ON ep.category_id = ec.id
                WHERE ep.event_id = ? AND ep.bib_number = ?
            ");
            $participant_stmt->execute([$event_id, $bib_number]);
            $participant = $participant_stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$participant) {
                respond(['success' => false, 'message' => 'Participant not found']);
            }
            
            // Get participant facts
            $facts_stmt = $pdo->prepare("
                SELECT id, title, fact, sort_order
                FROM participant_facts
                WHERE participant_id = ?
                ORDER BY sort_order, id
            ");
            $facts_stmt->execute([$participant['id']]);
            $facts = $facts_stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $participant['facts'] = $facts;
            
            // Only process country if it exists
            if (function_exists('getCountryEmoji') && !empty($participant['country'])) {
                $participant['country'] = getCountryEmoji($participant['country']);
            }
            
            respond(['success' => true, 'participant' => $participant]);
        } catch (Exception $e) {
            error_log("Error in get_participant_by_bib: " . $e->getMessage());
            respond(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
        }
    }

    respond(['success' => false, 'message' => 'Invalid action']);
} catch (Exception $e) {
    // Log the actual error for debugging
    error_log("Public Dashboard API Error: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine());
    respond(['success' => false, 'message' => 'Database error: ' . $e->getMessage(), 'error_details' => [
        'file' => $e->getFile(),
        'line' => $e->getLine(),
        'trace' => $e->getTraceAsString()
    ]]);
}