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

$selected_event = $_GET['event_id'] ?? 0;
$selected_gender = $_GET['gender'] ?? '';

$events = $pdo->query("SELECT id, name FROM events ORDER BY date DESC")->fetchAll(PDO::FETCH_ASSOC);

if ($selected_event) {
    // Get distinct heat/run numbers
    $heat_runs_stmt = $pdo->prepare("
        SELECT ep.heat_number, r.run_number
        FROM runs r
        JOIN event_participants ep ON r.event_participant_id = ep.id
        WHERE ep.event_id = ?
        GROUP BY ep.heat_number, r.run_number
        ORDER BY ep.heat_number, r.run_number
    ");
    $heat_runs_stmt->execute([$selected_event]);
    $heat_run_data = $heat_runs_stmt->fetchAll(PDO::FETCH_ASSOC);

    $heat_run_map = [];
    foreach ($heat_run_data as $hr) {
        $heat_run_map[$hr['heat_number']][] = $hr['run_number'];
    }

    // Get participant scores by heat and run with optional gender filter
    $gender_clause = $selected_gender ? "AND p.gender = ?" : "";
    $scores_stmt = $pdo->prepare("
        SELECT
            ep.heat_number,
            ep.participant_id,
            p.bib,
            p.first_name,
            p.last_name,
            p.club,
            p.country,
            p.gender,
            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 s.run_id = r.id
        WHERE ep.event_id = ? $gender_clause
        GROUP BY ep.heat_number, ep.participant_id, r.run_number
        ORDER BY ep.heat_number, p.last_name, r.run_number
    ");
    $params = $selected_gender ? [$selected_event, $selected_gender] : [$selected_event];
    $scores_stmt->execute($params);
    $raw_scores = $scores_stmt->fetchAll(PDO::FETCH_ASSOC);

    // Group scores by heat and participant
    $grouped = [];
    foreach ($raw_scores as $row) {
        $heat = $row['heat_number'];
        $pid = $row['participant_id'];

        if (!isset($grouped[$heat][$pid])) {
            $grouped[$heat][$pid] = [
                'bib' => $row['bib'],
                'name' => $row['first_name'] . ' ' . $row['last_name'],
                'club' => $row['club'],
                'country' => $row['country'],
                'gender' => $row['gender'],
                'runs' => []
            ];
        }

        $grouped[$heat][$pid]['runs'][$row['run_number']] = number_format((float)$row['avg_score'], 2);
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Live Scoreboard (Runs x Heats)</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
<!-- style skin holder -->
    <style>
        body { background: #f4f6f8; }
        .heat-table { margin-bottom: 60px; padding: 20px; background: #fff; border-radius: 12px; box-shadow: 0 0 10px rgba(0,0,0,0.05); }
        h3.heat-header { color: #234a6e; margin-bottom: 20px; }
        .table th { background: #eaf1f8; }
    </style>
</head>
<body class="body-bg-aurora-bright">
<div class="p-4">
    <h2 class="mb-4">Live Scoreboard - By Heat & Run</h2>

    <form method="get" class="row g-3 mb-4">
        <div class="col-md-6">
            <label class="form-label">Select Event</label>
            <select name="event_id" class="form-select" onchange="this.form.submit()">
                <option value="">-- Choose Event --</option>
                <?php foreach ($events as $ev): ?>
                    <option value="<?= $ev['id'] ?>" <?= $selected_event == $ev['id'] ? 'selected' : '' ?>>
                        <?= htmlspecialchars($ev['name']) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="col-md-6">
            <label class="form-label">Filter by Gender</label>
            <select name="gender" class="form-select" onchange="this.form.submit()">
                <option value="">All</option>
                <option value="M" <?= $selected_gender == 'M' ? 'selected' : '' ?>>M</option>
                <option value="F" <?= $selected_gender == 'F' ? 'selected' : '' ?>>F</option>
                <option value="OPEN" <?= $selected_gender == 'OPEN' ? 'selected' : '' ?>>OPEN</option>
            </select>
        </div>
    </form>

    <?php if (!empty($grouped)): ?>
        <?php foreach ($grouped as $heat_number => $participants): ?>
            <div class="heat-table">
                <h3 class="heat-header">Heat <?= $heat_number ?></h3>
                <table class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>BIB</th>
                            <th>Name</th>
                            <th>Gender</th>
                            <th>Club</th>
                            <th>Country</th>
                            <?php foreach ($heat_run_map[$heat_number] as $run_no): ?>
                                <th>Run <?= $run_no ?></th>
                            <?php endforeach; ?>
                        </tr>
                    </thead>
                    <tbody>
                        <?php $i = 1; foreach ($participants as $pid => $info): ?>
                            <tr>
                                <td><?= $i++ ?></td>
                                <td><?= $info['bib'] ?></td>
                                <td><?= $info['name'] ?></td>
                                <td><?= $info['gender'] ?></td>
                                <td><?= $info['club'] ?></td>
                                <td><?= $info['country'] ?></td>
                                <?php foreach ($heat_run_map[$heat_number] as $run_no): ?>
                                    <td><?= $info['runs'][$run_no] ?? '-' ?></td>
                                <?php endforeach; ?>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        <?php endforeach; ?>
    <?php elseif ($selected_event): ?>
        <div class="alert alert-warning">No scores available for this event.</div>
    <?php endif; ?>
</div>
</body>
</html>
