<?php
session_start();
include 'includes/db.php';

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

// Holders for the summary data
$summary_data = [];
$heat_numbers = [];

// Fetch event settings
$heats_total = 2;
$runs_per_heat = 2;
if ($selected_event) {
    $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']);
    }
}

$categories = $pdo
    ->query("SELECT DISTINCT category FROM participants ORDER BY category")
    ->fetchAll(PDO::FETCH_COLUMN);

if ($selected_event) {
    $categories_to_use = $filter_category === 'all'
        ? $categories
        : [ $filter_category ];

    foreach ($categories_to_use as $cat) {
        $sql = "
            SELECT
                p.category,
                ep.bib_number as bib,
                p.first_name,
                p.last_name,
                p.gender,
                p.club,
                ep.heat_number,
                r.run_number,
                AVG(s.score_value) AS avg_score
            FROM runs r
            JOIN event_participants ep
              ON r.event_participant_id = ep.id
            JOIN participants p
              ON ep.participant_id = p.id
            LEFT JOIN scores s
              ON r.id = s.run_id
            WHERE ep.event_id = ?
              AND p.category = ?
            GROUP BY p.id, ep.heat_number, r.run_number
            ORDER BY ep.heat_number, r.run_number
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([ $selected_event, $cat ]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

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

            $key   = "{$row['bib']} - {$row['first_name']} {$row['last_name']}";
            $score = floatval($row['avg_score']);
            $heat  = $row['heat_number'];
            $run   = $row['run_number'];
            $code  = "H{$heat}R{$run}";

            $summary_data[$cat][$key]['club']       = $row['club']   ?? '-';
            $summary_data[$cat][$key]['gender']     = $row['gender'];
            $summary_data[$cat][$key][$code]        = number_format($score, 2);
            $summary_data[$cat][$key]['raw'][$code] = $score;
            $summary_data[$cat][$key]['all_scores'][] = $score;

            $heat_numbers[$code] = true;
        }
    }

    // Compute best and average
    foreach ($summary_data as $cat => &$participants) {
        foreach ($participants as &$p) {
            for ($h = 1; $h <= $heats_total; $h++) {
                $scores = [];
                for ($r = 1; $r <= $runs_per_heat; $r++) {
                    $score = $p['raw']["H{$h}R{$r}"] ?? null;
                    if ($score !== null) $scores[] = $score;
                }
                if (!empty($scores)) {
                    $p["H{$h}Best"]    = number_format(max($scores), 2);
                    $p["H{$h}Average"] = number_format(array_sum($scores)/count($scores), 2);
                }
            }
            $p["OverallBest"] = number_format(max($p['all_scores'] ?? [0]), 2);

            // Collect all heat averages for HighestAverage
            $heat_avgs = [];
            for ($h = 1; $h <= $heats_total; $h++) {
                $heat_avgs[] = floatval($p["H{$h}Average"] ?? 0);
            }
            $heat_avgs = array_filter($heat_avgs);
            $p["HighestAverage"] = $heat_avgs
                ? number_format(max($heat_avgs), 2)
                : '-';
        }
    }

    // Build header list and insert derived columns
    $final_headers = [];
    for ($h = 1; $h <= $heats_total; $h++) {
        for ($r = 1; $r <= $runs_per_heat; $r++) {
            $final_headers[] = "H{$h}R{$r}";
        }
        $final_headers[] = "H{$h}Best";
        $final_headers[] = "H{$h}Average";
    }
    $final_headers[] = 'OverallBest';
    $final_headers[] = 'HighestAverage';
}

function getColClass($header) {
    if (preg_match('/^H\d+Best$/', $header)) return 'col-best table-primary';
    if (preg_match('/^H\d+Average$/', $header)) return 'col-average table-secondary';
    if ($header === 'OverallBest') return 'col-overall-best table-success';
    if ($header === 'HighestAverage') return 'col-highest-average table-info';
    return '';
}

// Return JSON response
if ($selected_event && $filter_category !== 'all' && isset($summary_data[$filter_category])) {
    $participants = $summary_data[$filter_category];
    if ($sort_by !== 'none') {
        uasort($participants, function($a, $b) use ($sort_by) {
            return floatval($b[$sort_by] ?? 0) <=> floatval($a[$sort_by] ?? 0);
        });
    }

    ob_start();
    $rank = 1;
    foreach ($participants as $name => $runs): ?>
        <tr>
            <td scope="row"><span><?= $rank++ ?></span></td>
            <td><span><?= htmlspecialchars($name) ?></span></td>
            <td><span><?= htmlspecialchars($runs['club'] ?? '-') ?></span></td>
            <td><span><?= htmlspecialchars($runs['gender'] ?? '-') ?></span></td>
            <?php foreach ($final_headers as $h): ?>
                <td class="<?= getColClass($h) ?>"><span><?= $runs[$h] ?? '-' ?></span></td>
            <?php endforeach; ?>
        </tr>
    <?php endforeach;
    
    $table_body = ob_get_clean();
    
    // Also generate headers for consistency
    ob_start(); ?>
    <tr>
        <th scope="col"><span>Rank</span></th>
        <th scope="col"><span>Participant</span></th>
        <th scope="col"><span>Club</span></th>
        <th scope="col"><span>Gender</span></th>
        <?php foreach ($final_headers as $h): ?>
            <th scope="col" class="angle-th <?= getColClass($h) ?>"><span><?= $h ?></span></th>
        <?php endforeach; ?>
    </tr>
    <?php $table_head = ob_get_clean();

    echo json_encode([
        'success' => true,
        'table_head' => $table_head,
        'table_body' => $table_body,
        'category' => $filter_category
    ]);
} else {
    echo json_encode([
        'success' => false,
        'message' => 'No data available'
    ]);
}
?>