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

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

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

$grouped_data = [];
if ($selected_event) {
    $sql = "
        SELECT
            p.category,
            p.gender,
            p.bib,
            p.first_name,
            p.last_name,
            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 = ?
        " . ($filter_gender !== 'all' ? "AND p.gender = " . $pdo->quote($filter_gender) : "") . "
        GROUP BY p.id, ep.heat_number, r.run_number
        ORDER BY p.category, ep.heat_number, r.run_number
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$selected_event]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($rows as $row) {
        $cat = $row['category'];
        $key = "{$row['bib']} - {$row['first_name']} {$row['last_name']} ({$row['gender']})";
        $grouped_data[$cat][$key][$row['heat_number']][$row['run_number']] = number_format($row['avg_score'], 2);
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>View 2 - Heat & Run Breakdown</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
<!-- style skin holder -->
</head>
<body class="body-bg-aurora-bright">
<div class="p-4">
    <h2>View 2: Heat & Run Breakdown</h2>
    <form method="get" class="row g-3 mb-4">
        <div class="col-md-4">
            <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-4">
            <label class="form-label">Filter by Gender</label>
            <select name="gender" class="form-select" onchange="this.form.submit()">
                <option value="all">All</option>
                <option value="M" <?= ($filter_gender == 'M') ? 'selected' : '' ?>>Male</option>
                <option value="F" <?= ($filter_gender == 'F') ? 'selected' : '' ?>>Female</option>
                <option value="OPEN" <?= ($filter_gender == 'OPEN') ? 'selected' : '' ?>>Open</option>
            </select>
        </div>
        <div class="col-md-4">
            <label>Filter by Category</label>
            <select name="category" class="form-select" onchange="this.form.submit()">
                <option value="all">All Categories</option>
                <?php foreach ($categories as $cat): ?>
                    <option value="<?= $cat ?>" <?= $filter_category == $cat ? 'selected' : '' ?>><?= $cat ?></option>
                <?php endforeach; ?>
            </select>
        </div>
    </form>

    <?php foreach ($grouped_data as $category => $participants): ?>
        <?php if ($filter_category === 'all' || $filter_category === $category): ?>
            <h4 class="mt-4"><?= htmlspecialchars($category) ?></h4>
            <table class="table table-bordered bg-white">
                <thead>
                    <tr><th>Participant</th><th>Heat</th><th>Run</th><th>Avg Score</th></tr>
                </thead>
                <tbody>
                <?php foreach ($participants as $name => $heats): ?>
                    <?php foreach ($heats as $heat => $runs): ?>
                        <?php foreach ($runs as $run => $score): ?>
                        <tr>
                            <td><?= $name ?></td>
                            <td><?= $heat ?></td>
                            <td><?= $run ?></td>
                            <td><?= $score ?></td>
                        </tr>
                        <?php endforeach; ?>
                    <?php endforeach; ?>
                <?php endforeach; ?>
                </tbody>
            </table>
        <?php endif; ?>
    <?php endforeach; ?>
</div>
</body>
</html>
