<?php
/**
 * Estreka Ratsaklubi - Admin Advanced Statistics Panel
 * Põhjalik statistika ja analüüsi süsteem administraatoritele
 */

require_once '../config/config.php';
session_start();
require_once '../config/database.php';
require_once '../includes/functions.php';

// Nõua admin õigusi
requireAdmin();

$current_year = date('Y');
$error = '';

// Filtrid
$selected_year = getGet('year', $current_year);
$selected_discipline = getGet('discipline', 'all');
$selected_age_group = getGet('age_group', 'all');
$selected_athlete = getGet('athlete', 'all');
$date_from = getGet('date_from', $selected_year . '-01-01');
$date_to = getGet('date_to', $selected_year . '-12-31');
$competition_type = getGet('competition_type', 'all');

// PÕHI STATISTIKA LAADIMINE
try {
    // 1. ÜLDSTATISTIKA
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(DISTINCT u.id) as total_athletes,
            SUM(CASE WHEN u.isActive = 1 AND r.id IS NOT NULL THEN 1 ELSE 0 END) as active_athletes,
            COUNT(DISTINCT c.id) as total_competitions,
            COUNT(r.id) as total_results,
            COUNT(DISTINCT r.userId) as athletes_with_results,
            AVG(r.points) as avg_points,
            MAX(r.points) as max_points,
            MIN(r.points) as min_points,
            SUM(r.points) as total_points
        FROM users u
        LEFT JOIN results r ON u.id = r.userId
        LEFT JOIN competitions c ON r.competitionId = c.id
        WHERE u.role = 'athlete' 
        AND (c.date IS NULL OR c.date BETWEEN ? AND ?)
    ");
    $stmt->execute([$date_from, $date_to]);
    $overview_stats = $stmt->fetch(PDO::FETCH_ASSOC);

    // 2. DISTSIPLIINIDE DETAILSTATISTIKA
    $stmt = $pdo->prepare("
        SELECT 
            r.discipline,
            COUNT(r.id) as total_results,
            COUNT(DISTINCT r.userId) as unique_athletes,
            COUNT(DISTINCT r.competitionId) as competitions_count,
            AVG(r.points) as avg_points,
            MAX(r.points) as max_points,
            MIN(r.points) as min_points,
            SUM(CASE WHEN r.finished = 1 THEN 1 ELSE 0 END) as finished_count,
            AVG(CASE WHEN r.finished = 1 THEN r.points END) as avg_finished_points,
            
            -- Koolisõit spetsiifilised
            AVG(CASE WHEN r.discipline = 'koolisoit' AND r.percentage IS NOT NULL THEN r.percentage END) as avg_percentage,
            MAX(CASE WHEN r.discipline = 'koolisoit' AND r.percentage IS NOT NULL THEN r.percentage END) as max_percentage,
            
            -- Takistussõit spetsiifilised  
            AVG(CASE WHEN r.discipline = 'takistussoit' AND r.faultPoints IS NOT NULL THEN r.faultPoints END) as avg_fault_points,
            MIN(CASE WHEN r.discipline = 'takistussoit' AND r.faultPoints IS NOT NULL THEN r.faultPoints END) as min_fault_points,
            
            -- Kestvusratsutamine spetsiifilised
            AVG(CASE WHEN r.discipline = 'kestvusratsutamine' AND r.distance IS NOT NULL THEN r.distance END) as avg_distance,
            MAX(CASE WHEN r.discipline = 'kestvusratsutamine' AND r.distance IS NOT NULL THEN r.distance END) as max_distance,
            SUM(CASE WHEN r.discipline = 'kestvusratsutamine' AND r.distance IS NOT NULL THEN r.distance END) as total_distance
            
        FROM results r
        JOIN competitions c ON r.competitionId = c.id
        WHERE c.date BETWEEN ? AND ?
        " . ($selected_discipline !== 'all' ? "AND r.discipline = ?" : "") . "
        GROUP BY r.discipline
        ORDER BY total_results DESC
    ");
    
    $params = [$date_from, $date_to];
    if ($selected_discipline !== 'all') {
        $params[] = $selected_discipline;
    }
    $stmt->execute($params);
    $discipline_detailed_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 3. VANUSEGRUPPIDE ANALÜÜS
    $stmt = $pdo->prepare("
        SELECT 
            CASE 
                WHEN ? - strftime('%Y', u.birthDate) <= 18 THEN 'noored'
                ELSE 'täiskasvanud'
            END as age_group,
            COUNT(DISTINCT u.id) as athletes_count,
            COUNT(r.id) as results_count,
            AVG(r.points) as avg_points,
            MAX(r.points) as max_points,
            COUNT(DISTINCT r.competitionId) as competitions_participated
        FROM users u
        LEFT JOIN results r ON u.id = r.userId
        LEFT JOIN competitions c ON r.competitionId = c.id
        WHERE u.role = 'athlete' AND u.isActive = 1
        AND (c.date IS NULL OR c.date BETWEEN ? AND ?)
        GROUP BY age_group
    ");
    $stmt->execute([$selected_year, $date_from, $date_to]);
    $age_group_stats = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 4. KUINE AKTIIVSUS JA TRENDID  
    $stmt = $pdo->prepare("
        SELECT 
            strftime('%Y-%m', c.date) as year_month,
            strftime('%m', c.date) as month,
            COUNT(DISTINCT c.id) as competitions_count,
            COUNT(r.id) as results_count,
            COUNT(DISTINCT r.userId) as active_athletes,
            AVG(r.points) as avg_points,
            MAX(r.points) as max_points,
            SUM(CASE WHEN r.finished = 1 THEN 1 ELSE 0 END) as finished_count,
            ROUND(AVG(CASE WHEN r.discipline = 'koolisoit' AND r.percentage IS NOT NULL THEN r.percentage END), 2) as avg_dressage_percentage,
            ROUND(AVG(CASE WHEN r.discipline = 'takistussoit' AND r.faultPoints IS NOT NULL THEN r.faultPoints END), 2) as avg_jumping_faults,
            ROUND(AVG(CASE WHEN r.discipline = 'kestvusratsutamine' AND r.distance IS NOT NULL THEN r.distance END), 2) as avg_endurance_distance
        FROM competitions c
        LEFT JOIN results r ON c.id = r.competitionId
        WHERE c.date BETWEEN ? AND ?
        GROUP BY strftime('%Y-%m', c.date)
        ORDER BY year_month
    ");
    $stmt->execute([$date_from, $date_to]);
    $monthly_trends = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 5. TOP SPORTLASED (eraldi distsipliinide kaupa)
    $top_athletes = [];
    $disciplines = ['koolisoit', 'takistussoit', 'kestvusratsutamine'];
    
    foreach ($disciplines as $discipline) {
        $stmt = $pdo->prepare("
            SELECT 
                u.id, u.firstName, u.lastName, u.birthDate,
                COUNT(r.id) as results_count,
                AVG(r.points) as avg_points,
                SUM(r.points) as total_points,
                MAX(r.points) as best_result,
                SUM(CASE WHEN r.finished = 1 THEN 1 ELSE 0 END) as finished_count,
                
                -- Distsipliini spetsiifilised parimad tulemused
                " . ($discipline === 'koolisoit' ? "MAX(r.percentage) as best_percentage," : "") . "
                " . ($discipline === 'takistussoit' ? "MIN(r.faultPoints) as best_fault_points," : "") . "
                " . ($discipline === 'kestvusratsutamine' ? "MAX(r.distance) as longest_distance," : "") . "
                
                -- Hooaja arvestuse punktid (simulatsioon)
                CASE 
                    WHEN COUNT(r.id) >= (SELECT max_starts FROM (
                        SELECT 6 as max_starts WHERE ? = 'koolisoit'
                        UNION SELECT 10 WHERE ? = 'takistussoit'  
                        UNION SELECT 4 WHERE ? = 'kestvusratsutamine'
                    )) THEN (
                        SELECT SUM(points) FROM (
                            SELECT points FROM results r2 
                            WHERE r2.userId = u.id AND r2.discipline = ?
                            AND EXISTS(SELECT 1 FROM competitions c2 WHERE c2.id = r2.competitionId AND c2.date BETWEEN ? AND ?)
                            ORDER BY points DESC 
                            LIMIT (SELECT max_starts FROM (
                                SELECT 6 as max_starts WHERE ? = 'koolisoit'
                                UNION SELECT 10 WHERE ? = 'takistussoit'
                                UNION SELECT 4 WHERE ? = 'kestvusratsutamine'
                            ))
                        )
                    )
                    ELSE SUM(r.points)
                END as season_points
                
            FROM users u
            JOIN results r ON u.id = r.userId
            JOIN competitions c ON r.competitionId = c.id
            WHERE u.role = 'athlete' AND u.isActive = 1
            AND r.discipline = ?
            AND c.date BETWEEN ? AND ?
            GROUP BY u.id
            HAVING results_count > 0
            ORDER BY season_points DESC, avg_points DESC
            LIMIT 10
        ");
        
        $params = [
    $discipline, $discipline, $discipline, // Esimene UNION (3)
    $discipline,                           // r2.discipline = ? (1)
    $date_from,                            // r2 kuupäevad (2)
    $date_to,
    $discipline, $discipline, $discipline, // Teine UNION (3)
    $discipline,                           // r.discipline = ? (1)
    $date_from,                            // c.date kuupäevad (2)
    $date_to
];

$stmt->execute($params);
        $top_athletes[$discipline] = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // 6. VÕISTLUSTE ANALÜÜS
    $stmt = $pdo->prepare("
        SELECT 
            c.*,
            COUNT(r.id) as participants_count,
            COUNT(DISTINCT r.userId) as unique_participants,
            AVG(r.points) as avg_points,
            MAX(r.points) as best_result,
            SUM(CASE WHEN r.finished = 1 THEN 1 ELSE 0 END) as finished_count,
            ROUND((SUM(CASE WHEN r.finished = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(r.id)), 2) as completion_rate,
            
            -- Distsipliini spetsiifilised
            GROUP_CONCAT(DISTINCT r.discipline) as disciplines_held,
            
            COUNT(CASE WHEN r.discipline = 'koolisoit' THEN 1 END) as dressage_count,
            COUNT(CASE WHEN r.discipline = 'takistussoit' THEN 1 END) as jumping_count, 
            COUNT(CASE WHEN r.discipline = 'kestvusratsutamine' THEN 1 END) as endurance_count
            
        FROM competitions c
        LEFT JOIN results r ON c.id = r.competitionId
        WHERE c.date BETWEEN ? AND ?
        GROUP BY c.id
        ORDER BY c.date DESC
    ");
    $stmt->execute([$date_from, $date_to]);
    $competitions_analysis = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 7. PUNKTIDE JAOTUS JA SAAVUTUSED
    $stmt = $pdo->prepare("
        SELECT 
            CASE 
                WHEN r.points >= 15 THEN '15+ (Tipptulemused)'
                WHEN r.points >= 12 THEN '12-14.9 (Väga head)'
                WHEN r.points >= 10 THEN '10-11.9 (Head)'
                WHEN r.points >= 8 THEN '8-9.9 (Keskmised)'
                WHEN r.points >= 5 THEN '5-7.9 (Nõrgad)'
                WHEN r.points >= 1 THEN '1-4.9 (Algajad)'
                ELSE '0-0.9 (Katkestused)'
            END as points_range,
            COUNT(*) as count,
            COUNT(DISTINCT r.userId) as unique_athletes,
            r.discipline
        FROM results r
        JOIN competitions c ON r.competitionId = c.id
        WHERE c.date BETWEEN ? AND ?
        GROUP BY points_range, r.discipline
        ORDER BY MIN(r.points) DESC
    ");
    $stmt->execute([$date_from, $date_to]);
    $points_distribution = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 8. PROGRESSI ANALÜÜS (võrdlus eelmise aastaga)
    if ($selected_year > 2020) {
        $prev_year = $selected_year - 1;
        $stmt = $pdo->prepare("
            SELECT 
                'current' as period,
                COUNT(DISTINCT u.id) as athletes,
                COUNT(DISTINCT c.id) as competitions,
                COUNT(r.id) as results,
                AVG(r.points) as avg_points
            FROM users u
            LEFT JOIN results r ON u.id = r.userId  
            LEFT JOIN competitions c ON r.competitionId = c.id
            WHERE u.role = 'athlete' 
            AND c.date BETWEEN ? AND ?
            
            UNION ALL
            
            SELECT 
                'previous' as period,
                COUNT(DISTINCT u.id) as athletes,
                COUNT(DISTINCT c.id) as competitions,
                COUNT(r.id) as results,
                AVG(r.points) as avg_points
            FROM users u
            LEFT JOIN results r ON u.id = r.userId
            LEFT JOIN competitions c ON r.competitionId = c.id  
            WHERE u.role = 'athlete'
            AND c.date BETWEEN ? AND ?
        ");
        $stmt->execute([
            $date_from, $date_to,
            $prev_year . '-01-01', $prev_year . '-12-31'
        ]);
        $period_comparison = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $period_comparison = [];
    }

    // 9. REKORDID JA SAAVUTUSED - PARANDATUD VERSIOON
    // Teeme eraldi päringud igale rekordile, et vältida UNION keerukust
    $records_achievements = [];

    // 1. Kõrgeim punktisumma
    $stmt = $pdo->prepare("
        SELECT 
            'Kõrgeim punktisumma' as record_type,
            r.points as value,
            u.firstName || ' ' || u.lastName as athlete_name,
            c.name as competition_name,
            c.date as achievement_date,
            r.discipline
        FROM results r
        JOIN users u ON r.userId = u.id
        JOIN competitions c ON r.competitionId = c.id
        WHERE c.date BETWEEN ? AND ?
        ORDER BY r.points DESC
        LIMIT 1
    ");
    $stmt->execute([$date_from, $date_to]);
    $highest_points = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($highest_points) {
        $records_achievements[] = $highest_points;
    }

    // 2. Parim koolisõidu protsent
    $stmt = $pdo->prepare("
        SELECT 
            'Parim koolisõidu protsent' as record_type,
            r.percentage as value,
            u.firstName || ' ' || u.lastName as athlete_name,
            c.name as competition_name,
            c.date as achievement_date,
            'koolisoit' as discipline
        FROM results r
        JOIN users u ON r.userId = u.id
        JOIN competitions c ON r.competitionId = c.id
        WHERE r.discipline = 'koolisoit' 
        AND r.percentage IS NOT NULL
        AND c.date BETWEEN ? AND ?
        ORDER BY r.percentage DESC
        LIMIT 1
    ");
    $stmt->execute([$date_from, $date_to]);
    $best_dressage = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($best_dressage) {
        $records_achievements[] = $best_dressage;
    }

    // 3. Vähim takistussõidu karistuspunkte
    $stmt = $pdo->prepare("
        SELECT 
            'Vähim takistussõidu vigu' as record_type,
            r.faultPoints as value,
            u.firstName || ' ' || u.lastName as athlete_name,
            c.name as competition_name,
            c.date as achievement_date,
            'takistussoit' as discipline
        FROM results r
        JOIN users u ON r.userId = u.id
        JOIN competitions c ON r.competitionId = c.id
        WHERE r.discipline = 'takistussoit' 
        AND r.faultPoints IS NOT NULL
        AND c.date BETWEEN ? AND ?
        ORDER BY r.faultPoints ASC, r.points DESC
        LIMIT 1
    ");
    $stmt->execute([$date_from, $date_to]);
    $best_jumping = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($best_jumping) {
        $records_achievements[] = $best_jumping;
    }

    // 4. Pikim kestvusratsutamise distants
    $stmt = $pdo->prepare("
        SELECT 
            'Pikim kestvusratsutamise distants' as record_type,
            r.distance as value,
            u.firstName || ' ' || u.lastName as athlete_name,
            c.name as competition_name,
            c.date as achievement_date,
            'kestvusratsutamine' as discipline
        FROM results r
        JOIN users u ON r.userId = u.id
        JOIN competitions c ON r.competitionId = c.id
        WHERE r.discipline = 'kestvusratsutamine' 
        AND r.distance IS NOT NULL
        AND c.date BETWEEN ? AND ?
        ORDER BY r.distance DESC
        LIMIT 1
    ");
    $stmt->execute([$date_from, $date_to]);
    $longest_endurance = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($longest_endurance) {
        $records_achievements[] = $longest_endurance;
    }

} catch(PDOException $e) {
    $error = 'Statistika laadimine ebaõnnestus: ' . $e->getMessage();
    error_log('Advanced stats error: ' . $e->getMessage());
}

// Lae vajalikud andmed filtriteks
try {
    // Aastad
    $stmt = $pdo->prepare("SELECT DISTINCT strftime('%Y', date) as year FROM competitions ORDER BY year DESC");
    $stmt->execute();
    $available_years = $stmt->fetchAll(PDO::FETCH_COLUMN);

    // Sportlased
    $stmt = $pdo->prepare("SELECT id, firstName, lastName FROM users WHERE role = 'athlete' AND isActive = 1 ORDER BY lastName, firstName");
    $stmt->execute();
    $athletes_for_filter = $stmt->fetchAll(PDO::FETCH_ASSOC);

} catch(PDOException $e) {
    error_log('Filter data load error: ' . $e->getMessage());
    $available_years = [$current_year];
    $athletes_for_filter = [];
}

$page_title = 'Admin - Täiustatud Statistika';
$page_description = 'Põhjalik statistika ja analüüsi paneel administraatoritele.';

// LISA SEE RIDA SIIA:
$page_css = 'admin-advanced-stats.css';
// LISA FUNKTSIOON SIIA: ⬇️
function explainPointsRange($points_range, $discipline, $count, $total_count) {
    $percentage = $total_count > 0 ? round(($count / $total_count) * 100, 1) : 0;
    
    // Ekstrakti punktide vahemik
    if (preg_match('/(\d+)-(\d+\.?\d*)/', $points_range, $matches)) {
        $min = (float)$matches[1];
        $max = (float)$matches[2];
    } elseif (preg_match('/(\d+)\+/', $points_range, $matches)) {
        $min = (float)$matches[1];
        $max = 20;
    } else {
        return "<div class=\"range-title\">{$points_range}</div>
                <div class=\"range-stats\">{$count} tulemust ({$percentage}%)</div>";
    }
    
    $explanation = '';
    
    switch ($discipline) {
        case 'takistussoit':
            if ($min >= 12) {
                $explanation = "Puhas sõit (0 karistuspunkti)";
            } elseif ($min >= 10) {
                $explanation = "1-4 karistuspunkti";
            } elseif ($min >= 8) {
                $explanation = "5-8 karistuspunkti";
            } elseif ($min >= 6) {
                $explanation = "9-12 karistuspunkti";
            } elseif ($min >= 4) {
                $explanation = "13-16 karistuspunkti";
            } elseif ($min >= 2) {
                $explanation = "17-20 karistuspunkti";
            } elseif ($min >= 1) {
                $explanation = "20+ karistuspunkti";
            } else {
                $explanation = "Katkestus";
            }
            break;
            
        case 'koolisoit':
            if ($min >= 15) {
                $explanation = "70%+ ja kõrge skeem";
            } elseif ($min >= 12) {
                $explanation = "65-70% vahemik";
            } elseif ($min >= 10) {
                $explanation = "60-65% vahemik";
            } elseif ($min >= 8) {
                $explanation = "55-60% vahemik";
            } elseif ($min >= 6) {
                $explanation = "50-55% vahemik";
            } elseif ($min >= 3) {
                $explanation = "Alla 50%";
            } else {
                $explanation = "Katkestus või väga madal";
            }
            break;
            
        case 'kestvusratsutamine':
            if ($min >= 18) {
                $explanation = "100km+ ja esikoht";
            } elseif ($min >= 15) {
                $explanation = "80km+ ja hea koht";
            } elseif ($min >= 12) {
                $explanation = "40km+ ja keskmine koht";
            } elseif ($min >= 10) {
                $explanation = "Lõpetas distantsi";
            } elseif ($min >= 8) {
                $explanation = "Lühike distants";
            } elseif ($min >= 1) {
                $explanation = "Osales";
            } else {
                $explanation = "Katkestus";
            }
            break;
            
        default:
            $explanation = "Punktid: {$min}-{$max}";
    }
    
    return "<div class=\"range-title\">{$min}-{$max} punkti</div>
            <div class=\"range-explanation\">{$explanation}</div>
            <div class=\"range-stats\">{$count} tulemust ({$percentage}%)</div>";
}
include '../includes/header.php';
?>

<main class="admin-advanced-stats">
    <div class="container">
        <!-- Header -->
        <div class="page-header">
            <div class="page-header-content">
                <h1 class="page-title">
                    <i class="icon-bar-chart"></i>
                    Täiustatud Statistika
                </h1>
                <p class="page-subtitle">
                    Põhjalik analüüs ja andmed hooaja kohta
                </p>
            </div>
            <div class="page-actions">
                <button onclick="exportStatistics()" class="btn btn-primary">
                    <i class="icon-download"></i>
                    <span>Ekspordi</span>
                </button>
                <button onclick="printReport()" class="btn btn-secondary">
                    <i class="icon-printer"></i>
                    <span>Prindi</span>
                </button>
            </div>
        </div>

        <!-- Alerts -->
        <?php if ($error): ?>
            <div class="alert alert-error">
                <i class="icon-alert-circle"></i>
                <span><?php echo htmlspecialchars($error); ?></span>
            </div>
        <?php endif; ?>

        <!-- TÄIUSTATUD FILTRID -->
        <div class="advanced-filters">
            <div class="filters-header">
                <h3>
                    <i class="icon-filter"></i>
                    Täpsed filtrid ja analüüsi valikud
                </h3>
                <button onclick="toggleFilters()" class="btn-toggle" id="filtersToggle">
                    <i class="icon-chevron-down"></i>
                </button>
            </div>
            
            <form method="GET" class="filters-form" id="advancedFilters">
                <div class="filters-grid">
                    <div class="filter-group">
                        <label>Aasta</label>
                        <select name="year" class="filter-select">
                            <?php foreach ($available_years as $year): ?>
                                <option value="<?php echo $year; ?>" <?php echo $selected_year == $year ? 'selected' : ''; ?>>
                                    <?php echo $year; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>

                    <div class="filter-group">
                        <label>Kuupäevavahemik</label>
                        <div class="date-range">
                            <input type="date" name="date_from" value="<?php echo $date_from; ?>" class="filter-input">
                            <span class="date-separator">—</span>
                            <input type="date" name="date_to" value="<?php echo $date_to; ?>" class="filter-input">
                        </div>
                    </div>

                    <div class="filter-group">
                        <label>Distsipliin</label>
                        <select name="discipline" class="filter-select">
                            <option value="all" <?php echo $selected_discipline === 'all' ? 'selected' : ''; ?>>Kõik distsipliinid</option>
                            <option value="koolisoit" <?php echo $selected_discipline === 'koolisoit' ? 'selected' : ''; ?>>Koolisõit</option>
                            <option value="takistussoit" <?php echo $selected_discipline === 'takistussoit' ? 'selected' : ''; ?>>Takistussõit</option>
                            <option value="kestvusratsutamine" <?php echo $selected_discipline === 'kestvusratsutamine' ? 'selected' : ''; ?>>Kestvusratsutamine</option>
                        </select>
                    </div>

                    <div class="filter-group">
                        <label>Vanusegrupp</label>
                        <select name="age_group" class="filter-select">
                            <option value="all" <?php echo $selected_age_group === 'all' ? 'selected' : ''; ?>>Kõik vanused</option>
                            <option value="noored" <?php echo $selected_age_group === 'noored' ? 'selected' : ''; ?>>Noored (kuni 18)</option>
                            <option value="täiskasvanud" <?php echo $selected_age_group === 'täiskasvanud' ? 'selected' : ''; ?>>Täiskasvanud (üle 18)</option>
                        </select>
                    </div>

                    <div class="filter-group">
                        <label>Sportlane</label>
                        <select name="athlete" class="filter-select">
                            <option value="all" <?php echo $selected_athlete === 'all' ? 'selected' : ''; ?>>Kõik sportlased</option>
                            <?php foreach ($athletes_for_filter as $athlete): ?>
                                <option value="<?php echo $athlete['id']; ?>" <?php echo $selected_athlete == $athlete['id'] ? 'selected' : ''; ?>>
                                    <?php echo htmlspecialchars($athlete['firstName'] . ' ' . $athlete['lastName']); ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>

                    <div class="filter-group">
                        <label>Võistluste tüüp</label>
                        <select name="competition_type" class="filter-select">
                            <option value="all" <?php echo $competition_type === 'all' ? 'selected' : ''; ?>>Kõik võistlused</option>
                            <option value="single_day" <?php echo $competition_type === 'single_day' ? 'selected' : ''; ?>>Ühepäevased</option>
                            <option value="multi_day" <?php echo $competition_type === 'multi_day' ? 'selected' : ''; ?>>Mitme päeva</option>
                        </select>
                    </div>
                </div>

                <div class="filters-actions">
                    <button type="submit" class="btn btn-primary">
                        <i class="icon-search"></i>
                        Analüüsi
                    </button>
                    <a href="?year=<?php echo $current_year; ?>" class="btn btn-secondary">
                        <i class="icon-refresh-cw"></i>
                        Lähtesta
                    </a>
                </div>
            </form>
        </div>

        <!-- ÜLDSTATISTIKA DASHBOARD -->
        <div class="overview-dashboard">
            <div class="stats-cards-grid">
                <div class="stat-card primary">
                    <div class="stat-icon">
                        <i class="icon-users"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-number"><?php echo $overview_stats['total_athletes'] ?? 0; ?></div>
                        <div class="stat-label">Sportlast kokku</div>
                        <div class="stat-sublabel"><?php echo $overview_stats['active_athletes'] ?? 0; ?> starti</div>
                    </div>
                    <div class="stat-trend">
                        <?php 
                        $participation_rate = ($overview_stats['total_athletes'] > 0) 
                            ? round(($overview_stats['athletes_with_results'] / $overview_stats['total_athletes']) * 100, 1) 
                            : 0;
                        ?>
                        <span class="trend-value"><?php echo $participation_rate; ?>%</span>
                        <span class="trend-label">osaleb võistlustel</span>
                    </div>
                </div>

                <div class="stat-card success">
                    <div class="stat-icon">
                        <i class="icon-calendar"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-number"><?php echo $overview_stats['total_competitions'] ?? 0; ?></div>
                        <div class="stat-label">Võistlust</div>
                        <div class="stat-sublabel">Valitud perioodil</div>
                    </div>
                    <div class="stat-trend">
                        <?php 
                        $avg_participants = ($overview_stats['total_competitions'] > 0) 
                            ? round($overview_stats['total_results'] / $overview_stats['total_competitions'], 1) 
                            : 0;
                        ?>
                        <span class="trend-value"><?php echo $avg_participants; ?></span>
                        <span class="trend-label">keskm. osalejat</span>
                    </div>
                </div>

                <div class="stat-card warning">
                    <div class="stat-icon">
                        <i class="icon-trophy"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-number"><?php echo $overview_stats['total_results'] ?? 0; ?></div>
                        <div class="stat-label">Tulemust</div>
                        <div class="stat-sublabel">Sisestatud</div>
                    </div>
                    <div class="stat-trend">
                        <span class="trend-value"><?php echo formatPoints($overview_stats['avg_points'] ?? 0); ?></span>
                        <span class="trend-label">keskmine punktid</span>
                    </div>
                </div>

                <div class="stat-card info">
                    <div class="stat-icon">
                        <i class="icon-award"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-number"><?php echo formatPoints($overview_stats['max_points'] ?? 0); ?></div>
                        <div class="stat-label">Parim tulemus</div>
                        <div class="stat-sublabel">Hooaja rekord</div>
                    </div>
                    <div class="stat-trend">
                        <span class="trend-value"><?php echo formatPoints($overview_stats['total_points'] ?? 0); ?></span>
                        <span class="trend-label">punkte kokku</span>
                    </div>
                </div>
            </div>
        </div>

        <!-- DISTSIPLIINIDE PÕHJALIK ANALÜÜS -->
        <div class="analysis-grid">
            <div class="analysis-card discipline-analysis">
                <div class="card-header">
                    <h3>
                        <i class="icon-pie-chart"></i>
                        Distsipliinide detailanalüüs
                    </h3>
                    <div class="header-actions">
                        <button onclick="toggleView('disciplineView')" class="btn-toggle">
                            <i class="icon-grid"></i>
                        </button>
                    </div>
                </div>
                <div class="card-content">
                    <?php if (empty($discipline_detailed_stats)): ?>
                        <div class="no-data">
                            <i class="icon-info"></i>
                            <p>Valitud perioodil ei ole tulemusi</p>
                        </div>
                    <?php else: ?>
                        <div class="disciplines-detailed" id="disciplineView">
                            <?php foreach ($discipline_detailed_stats as $stat): ?>
                                <div class="discipline-detailed-card <?php echo $stat['discipline']; ?>">
                                    <div class="discipline-header">
                                        <div class="discipline-icon">
                                            <i class="icon-<?php echo $stat['discipline'] === 'koolisoit' ? 'target' : ($stat['discipline'] === 'takistussoit' ? 'zap' : 'map'); ?>"></i>
                                        </div>
                                        <div class="discipline-title">
                                            <h4><?php echo getDisciplineName($stat['discipline']); ?></h4>
                                            <div class="discipline-subtitle"><?php echo $stat['total_results']; ?> tulemust</div>
                                        </div>
                                    </div>

                                    <div class="discipline-metrics">
                                        <div class="metric-row">
                                            <div class="metric">
                                                <div class="metric-value"><?php echo $stat['unique_athletes']; ?></div>
                                                <div class="metric-label">Sportlast</div>
                                            </div>
                                            <div class="metric">
                                                <div class="metric-value"><?php echo $stat['competitions_count']; ?></div>
                                                <div class="metric-label">Võistlust</div>
                                            </div>
                                            <div class="metric">
                                                <div class="metric-value"><?php echo formatPoints($stat['avg_points']); ?></div>
                                                <div class="metric-label">Keskmine</div>
                                            </div>
                                            <div class="metric">
                                                <div class="metric-value"><?php echo formatPoints($stat['max_points']); ?></div>
                                                <div class="metric-label">Parim</div>
                                            </div>
                                        </div>

                                        <div class="metric-row">
                                            <div class="metric">
                                                <div class="metric-value"><?php echo $stat['finished_count']; ?></div>
                                                <div class="metric-label">Lõpetas</div>
                                            </div>
                                            <div class="metric">
                                                <div class="metric-value">
                                                    <?php echo $stat['total_results'] > 0 ? round(($stat['finished_count'] / $stat['total_results']) * 100, 1) : 0; ?>%
                                                </div>
                                                <div class="metric-label">Lõpetamise %</div>
                                            </div>
                                            <div class="metric">
                                                <div class="metric-value"><?php echo formatPoints($stat['avg_finished_points']); ?></div>
                                                <div class="metric-label">Keskm. lõpetanud</div>
                                            </div>
                                        </div>

                                        <!-- Distsipliini spetsiifilised meetrikud -->
                                        <?php if ($stat['discipline'] === 'koolisoit'): ?>
                                            <div class="metric-row special">
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo round($stat['avg_percentage'], 1); ?>%</div>
                                                    <div class="metric-label">Keskmine %</div>
                                                </div>
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo round($stat['max_percentage'], 1); ?>%</div>
                                                    <div class="metric-label">Parim %</div>
                                                </div>
                                            </div>
                                        <?php elseif ($stat['discipline'] === 'takistussoit'): ?>
                                            <div class="metric-row special">
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo round($stat['avg_fault_points'], 1); ?></div>
                                                    <div class="metric-label">Keskm. vead</div>
                                                </div>
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo $stat['min_fault_points']; ?></div>
                                                    <div class="metric-label">Vähim vigu</div>
                                                </div>
                                            </div>
                                        <?php elseif ($stat['discipline'] === 'kestvusratsutamine'): ?>
                                            <div class="metric-row special">
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo round($stat['avg_distance'], 1); ?> km</div>
                                                    <div class="metric-label">Keskm. distants</div>
                                                </div>
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo $stat['max_distance']; ?> km</div>
                                                    <div class="metric-label">Pikim distants</div>
                                                </div>
                                                <div class="metric">
                                                    <div class="metric-value"><?php echo round($stat['total_distance'], 1); ?> km</div>
                                                    <div class="metric-label">Kokku läbitud</div>
                                                </div>
                                            </div>
                                        <?php endif; ?>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>

            <!-- VANUSEGRUPPIDE ANALÜÜS -->
            <div class="analysis-card age-analysis">
                <div class="card-header">
                    <h3>
                        <i class="icon-users"></i>
                        Vanusegruppide analüüs
                    </h3>
                </div>
                <div class="card-content">
                    <?php if (empty($age_group_stats)): ?>
                        <div class="no-data">
                            <i class="icon-info"></i>
                            <p>Vanusegrupi andmed puuduvad</p>
                        </div>
                    <?php else: ?>
                        <div class="age-groups-analysis">
                            <?php foreach ($age_group_stats as $group): ?>
                                <div class="age-group-detailed">
                                    <div class="age-group-header">
                                        <h4><?php echo getAgeGroupName($group['age_group']); ?></h4>
                                        <div class="age-group-count"><?php echo $group['athletes_count']; ?> sportlast</div>
                                    </div>
                                    
                                    <div class="age-metrics">
                                        <div class="age-metric">
                                            <div class="metric-value"><?php echo $group['results_count']; ?></div>
                                            <div class="metric-label">Tulemust</div>
                                        </div>
                                        <div class="age-metric">
                                            <div class="metric-value"><?php echo formatPoints($group['avg_points']); ?></div>
                                            <div class="metric-label">Keskmine</div>
                                        </div>
                                        <div class="age-metric">
                                            <div class="metric-value"><?php echo formatPoints($group['max_points']); ?></div>
                                            <div class="metric-label">Parim</div>
                                        </div>
                                        <div class="age-metric">
                                            <div class="metric-value">
                                                <?php echo $group['athletes_count'] > 0 ? round($group['results_count'] / $group['athletes_count'], 1) : 0; ?>
                                            </div>
                                            <div class="metric-label">Tulemusi/sportlane</div>
                                        </div>
                                    </div>

                                    <div class="age-progress-bar">
                                        <div class="progress-fill" style="width: <?php echo ($group['athletes_count'] / array_sum(array_column($age_group_stats, 'athletes_count'))) * 100; ?>%"></div>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- KUINE TRENDID JA GRAAFIKUD -->
        <div class="trends-section">
            <div class="analysis-card">
                <div class="card-header">
                    <h3>
                        <i class="icon-trending-up"></i>
                        Hooaja trendid ja aktiivsus
                    </h3>
                    <div class="header-actions">
                        <select id="trendMetric" onchange="updateTrendChart()" class="metric-selector">
                            <option value="results_count">Tulemuste arv</option>
                            <option value="active_athletes">Aktiivsed sportlased</option>
                            <option value="avg_points">Keskmine punktid</option>
                            <option value="competitions_count">Võistluste arv</option>
                        </select>
                    </div>
                </div>
                <div class="card-content">
                    <?php if (empty($monthly_trends)): ?>
                        <div class="no-data">
                            <i class="icon-info"></i>
                            <p>Trendide andmed puuduvad</p>
                        </div>
                    <?php else: ?>
                        <div class="trends-chart-container">
                            <div class="trends-chart" id="trendsChart">
                                <?php 
                                $months = ['01' => 'Jan', '02' => 'Veeb', '03' => 'Märts', '04' => 'Apr', 
                                          '05' => 'Mai', '06' => 'Juuni', '07' => 'Juuli', '08' => 'Aug',
                                          '09' => 'Sept', '10' => 'Okt', '11' => 'Nov', '12' => 'Dets'];
                                
                                $max_results = max(array_column($monthly_trends, 'results_count'));
                                $max_athletes = max(array_column($monthly_trends, 'active_athletes'));
                                $max_points = max(array_filter(array_column($monthly_trends, 'avg_points')));
                                
                                foreach ($monthly_trends as $month): 
                                ?>
                                    <div class="month-trend-bar" data-month="<?php echo $month['month']; ?>">
                                        <div class="trend-bars">
                                            <div class="trend-bar results" 
                                                 style="height: <?php echo $max_results > 0 ? ($month['results_count'] / $max_results) * 100 : 0; ?>%"
                                                 data-value="<?php echo $month['results_count']; ?>"
                                                 title="<?php echo $month['results_count']; ?> tulemust">
                                            </div>
                                            <div class="trend-bar athletes" 
                                                 style="height: <?php echo $max_athletes > 0 ? ($month['active_athletes'] / $max_athletes) * 100 : 0; ?>%"
                                                 data-value="<?php echo $month['active_athletes']; ?>"
                                                 title="<?php echo $month['active_athletes']; ?> aktiivset">
                                            </div>
                                            <div class="trend-bar points" 
                                                 style="height: <?php echo $max_points > 0 ? ($month['avg_points'] / $max_points) * 100 : 0; ?>%"
                                                 data-value="<?php echo formatPoints($month['avg_points']); ?>"
                                                 title="Keskmine: <?php echo formatPoints($month['avg_points']); ?>p">
                                            </div>
                                        </div>
                                        <div class="month-label">
                                            <?php echo $months[$month['month']]; ?>
                                        </div>
                                        <div class="month-stats">
                                            <div class="month-stat"><?php echo $month['competitions_count']; ?> võistlust</div>
                                            <div class="month-stat"><?php echo $month['finished_count']; ?> lõpetas</div>
                                        </div>
                                    </div>
                                <?php endforeach; ?>
                            </div>
                            
                            <div class="chart-legend">
                                <div class="legend-item">
                                    <div class="legend-color results"></div>
                                    <span>Tulemused</span>
                                </div>
                                <div class="legend-item">
                                    <div class="legend-color athletes"></div>
                                    <span>Aktiivsed sportlased</span>
                                </div>
                                <div class="legend-item">
                                    <div class="legend-color points"></div>
                                    <span>Keskmine punktid</span>
                                </div>
                            </div>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- TOP SPORTLASED DISTSIPLIINIDE KAUPA -->
        <div class="top-athletes-section">
            <div class="section-header">
                <h2>
                    <i class="icon-crown"></i>
                    Parimad sportlased distsipliinide kaupa
                </h2>
            </div>
            
            <div class="top-athletes-grid">
                <?php foreach ($disciplines as $discipline): ?>
                    <?php if (!empty($top_athletes[$discipline])): ?>
                        <div class="top-athletes-card">
                            <div class="card-header">
                                <h3>
                                    <i class="icon-<?php echo $discipline === 'koolisoit' ? 'target' : ($discipline === 'takistussoit' ? 'zap' : 'map'); ?>"></i>
                                    <?php echo getDisciplineName($discipline); ?>
                                </h3>
                                <div class="discipline-badge <?php echo $discipline; ?>">
                                    TOP <?php echo count($top_athletes[$discipline]); ?>
                                </div>
                            </div>
                            <div class="card-content">
                                <div class="athletes-ranking">
                                    <?php foreach ($top_athletes[$discipline] as $index => $athlete): ?>
                                        <div class="athlete-rank-item <?php echo $index < 3 ? 'podium' : ''; ?>">
                                            <div class="rank-position">
                                                <?php if ($index === 0): ?>
                                                    <i class="icon-crown gold"></i>
                                                <?php elseif ($index === 1): ?>
                                                    <i class="icon-award silver"></i>
                                                <?php elseif ($index === 2): ?>
                                                    <i class="icon-award bronze"></i>
                                                <?php else: ?>
                                                    <span class="rank-number"><?php echo $index + 1; ?></span>
                                                <?php endif; ?>
                                            </div>
                                            
                                            <div class="athlete-info">
                                                <div class="athlete-name">
                                                    <?php echo htmlspecialchars($athlete['firstName'] . ' ' . $athlete['lastName']); ?>
                                                </div>
                                                <div class="athlete-stats">
                                                    <span class="stat">
                                                        <strong><?php echo formatPoints($athlete['season_points']); ?>p</strong>
                                                        hooaja arvestus
                                                    </span>
                                                    <span class="stat">
                                                        <?php echo $athlete['results_count']; ?> start
                                                        (<?php echo $athlete['finished_count']; ?> lõpetas)
                                                    </span>
                                                </div>
                                            </div>
                                            
                                            <div class="athlete-best">
                                                <div class="best-result">
                                                    <?php echo formatPoints($athlete['best_result']); ?>p
                                                </div>
                                                <div class="best-label">parim</div>
                                                
                                                <?php if ($discipline === 'koolisoit' && $athlete['best_percentage']): ?>
                                                    <div class="special-metric">
                                                        <?php echo $athlete['best_percentage']; ?>%
                                                    </div>
                                                <?php elseif ($discipline === 'takistussoit' && isset($athlete['best_fault_points'])): ?>
                                                    <div class="special-metric">
                                                        <?php echo $athlete['best_fault_points']; ?> viga
                                                    </div>
                                                <?php elseif ($discipline === 'kestvusratsutamine' && $athlete['longest_distance']): ?>
                                                    <div class="special-metric">
                                                        <?php echo $athlete['longest_distance']; ?> km
                                                    </div>
                                                <?php endif; ?>
                                            </div>
                                        </div>
                                    <?php endforeach; ?>
                                </div>
                            </div>
                        </div>
                    <?php endif; ?>
                <?php endforeach; ?>
            </div>
        </div>

        <!-- VÕISTLUSTE ANALÜÜS -->
        <div class="competitions-analysis">
            <div class="analysis-card">
                <div class="card-header">
                    <h3>
                        <i class="icon-calendar"></i>
                        Võistluste detailanalüüs
                    </h3>
                    <div class="header-actions">
                        <select id="competitionSort" onchange="sortCompetitions()" class="sort-selector">
                            <option value="date">Kuupäev</option>
                            <option value="participants">Osalejate arv</option>
                            <option value="completion_rate">Lõpetamise %</option>
                            <option value="avg_points">Keskmine punktid</option>
                        </select>
                    </div>
                </div>
                <div class="card-content">
                    <?php if (empty($competitions_analysis)): ?>
                        <div class="no-data">
                            <i class="icon-info"></i>
                            <p>Võistluste andmed puuduvad</p>
                        </div>
                    <?php else: ?>
                        <div class="competitions-table-container">
                            <table class="competitions-analysis-table" id="competitionsTable">
                                <thead>
                                    <tr>
                                        <th>Võistlus</th>
                                        <th>Kuupäev</th>
                                        <th>Distsipliinid</th>
                                        <th>Osalejad</th>
                                        <th>Lõpetas</th>
                                        <th>Keskmine</th>
                                        <th>Parim</th>
                                        <th>Efektiivsus</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($competitions_analysis as $comp): ?>
                                        <tr>
                                            <td class="competition-name">
                                                <div class="comp-title"><?php echo htmlspecialchars($comp['name']); ?></div>
                                                <?php if ($comp['location']): ?>
                                                    <div class="comp-location"><?php echo htmlspecialchars($comp['location']); ?></div>
                                                <?php endif; ?>
                                            </td>
                                            <td class="competition-date">
                                                <?php echo formatCompetitionDates($comp); ?>
                                            </td>
                                            <td class="competition-disciplines">
                                                <?php 
                                                $held_disciplines = explode(',', $comp['disciplines_held']);
                                                foreach ($held_disciplines as $disc): 
                                                ?>
                                                    <span class="discipline-tag <?php echo $disc; ?>">
                                                        <?php echo getDisciplineName($disc); ?>
                                                    </span>
                                                <?php endforeach; ?>
                                            </td>
                                            <td class="participants-count">
                                                <div class="count-main"><?php echo $comp['participants_count']; ?></div>
                                                <div class="count-unique"><?php echo $comp['unique_participants']; ?> unikaalset</div>
                                            </td>
                                            <td class="finished-count">
                                                <div class="count-main"><?php echo $comp['finished_count']; ?></div>
                                                <div class="count-percentage"><?php echo $comp['completion_rate']; ?>%</div>
                                            </td>
                                            <td class="avg-points">
                                                <?php echo formatPoints($comp['avg_points']); ?>
                                            </td>
                                            <td class="best-result">
                                                <?php echo formatPoints($comp['best_result']); ?>
                                            </td>
                                            <td class="efficiency">
                                                <div class="efficiency-bar">
                                                    <div class="efficiency-fill" style="width: <?php echo $comp['completion_rate']; ?>%"></div>
                                                </div>
                                                <div class="efficiency-text"><?php echo $comp['completion_rate']; ?>%</div>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                            </table>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- PUNKTIDE JAOTUS JA STATISTIKA -->
        <div class="points-distribution-section">
            <div class="analysis-card">
                <div class="card-header">
                    <h3>
                        <i class="icon-bar-chart-2"></i>
                        Punktide jaotus og saavutuste analüüs
                    </h3>
                </div>
                <div class="card-content">
                    <?php if (empty($points_distribution)): ?>
                        <div class="no-data">
                            <i class="icon-info"></i>
                            <p>Punktide jaotuse andmed puuduvad</p>
                        </div>
                    <?php else: ?>
                        <div class="points-distribution-grid">
                            <?php 
                            // Grupeeri distsipliini järgi
                            $points_by_discipline = [];
                            foreach ($points_distribution as $point) {
                                $points_by_discipline[$point['discipline']][] = $point;
                            }
                            
                            foreach ($points_by_discipline as $discipline => $ranges): 
                            ?>
                                <div class="distribution-card">
                                    <div class="distribution-header">
                                        <h4><?php echo getDisciplineName($discipline); ?></h4>
                                        <div class="total-results">
                                            <?php echo array_sum(array_column($ranges, 'count')); ?> tulemust
                                        </div>
                                    </div>
                                    
                                    <div class="distribution-chart">
                                        <?php 
                                        $total_count = array_sum(array_column($ranges, 'count'));
                                        foreach ($ranges as $range): 
                                            $percentage = $total_count > 0 ? ($range['count'] / $total_count) * 100 : 0;
                                        ?>
                                            <div class="distribution-bar">
                                                <div class="bar-info">
                                                    <?php echo explainPointsRange($range['points_range'], $discipline, $range['count'], $total_count); ?>
                                                </div>
                                                <div class="bar-container">
                                                    <div class="bar-fill" style="width: <?php echo $percentage; ?>%"></div>
                                                </div>
                                                <div class="unique-athletes">
                                                    <?php echo $range['unique_athletes']; ?> sportlast
                                                </div>
                                            </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- REKORDID JA SAAVUTUSED -->
        <div class="records-section">
            <div class="analysis-card">
                <div class="card-header">
                    <h3>
                        <i class="icon-star"></i>
                        Hooaja rekordid ja saavutused
                    </h3>
                </div>
                <div class="card-content">
                    <?php if (empty($records_achievements)): ?>
                        <div class="no-data">
                            <i class="icon-info"></i>
                            <p>Rekordite andmed puuduvad</p>
                        </div>
                    <?php else: ?>
                        <div class="records-grid">
                            <?php foreach ($records_achievements as $record): ?>
                                <div class="record-card">
                                    <div class="record-icon">
                                        <i class="icon-star"></i>
                                    </div>
                                    <div class="record-content">
                                        <div class="record-title"><?php echo htmlspecialchars($record['record_type']); ?></div>
                                        <div class="record-value">
                                            <?php 
                                                if (strpos($record['record_type'], 'protsent') !== false) {
                                                    echo round($record['value'], 1) . '%';
                                                } elseif (strpos($record['record_type'], 'distants') !== false) {
                                                    echo $record['value'] . ' km';
                                                } elseif (strpos($record['record_type'], 'vigu') !== false) {
                                                    echo $record['value'] . ' viga';
                                                } else {
                                                    echo formatPoints($record['value']);
                                                }
                                                ?>
                                        </div>
                                        <div class="record-athlete"><?php echo htmlspecialchars($record['athlete_name']); ?></div>
                                        <div class="record-details">
                                            <div class="record-competition"><?php echo htmlspecialchars($record['competition_name']); ?></div>
                                            <div class="record-date"><?php echo formatDate($record['achievement_date']); ?></div>
                                        </div>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>

        <!-- VÕRDLUS EELMISE AASTAGA -->
        <?php if (!empty($period_comparison)): ?>
            <div class="comparison-section">
                <div class="analysis-card">
                    <div class="card-header">
                        <h3>
                            <i class="icon-trending-up"></i>
                            Võrdlus eelmise aastaga
                        </h3>
                    </div>
                    <div class="card-content">
                        <div class="comparison-grid">
                            <?php 
                            $current = array_values(array_filter($period_comparison, fn($p) => $p['period'] === 'current'))[0] ?? [];
                            $previous = array_values(array_filter($period_comparison, fn($p) => $p['period'] === 'previous'))[0] ?? [];
                            
                            $metrics = [
                                'athletes' => 'Sportlased',
                                'competitions' => 'Võistlused', 
                                'results' => 'Tulemused',
                                'avg_points' => 'Keskmine punktid'
                            ];
                            
                            foreach ($metrics as $key => $label):
                                $current_val = $current[$key] ?? 0;
                                $previous_val = $previous[$key] ?? 0;
                                $change = $previous_val > 0 ? (($current_val - $previous_val) / $previous_val) * 100 : 0;
                            ?>
                                <div class="comparison-metric">
                                    <div class="metric-label"><?php echo $label; ?></div>
                                    <div class="metric-comparison">
                                        <div class="current-value">
                                            <?php echo $key === 'avg_points' ? formatPoints($current_val) : $current_val; ?>
                                        </div>
                                        <div class="change-indicator <?php echo $change >= 0 ? 'positive' : 'negative'; ?>">
                                            <i class="icon-<?php echo $change >= 0 ? 'trending-up' : 'trending-down'; ?>"></i>
                                            <?php echo abs(round($change, 1)); ?>%
                                        </div>
                                    </div>
                                    <div class="previous-value">
                                        Eelmine aasta: <?php echo $key === 'avg_points' ? formatPoints($previous_val) : $previous_val; ?>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    </div>
                </div>
            </div>
        <?php endif; ?>

        <!-- KOKKUVÕTE JA JÄRELDUSED -->
        <div class="summary-section">
            <div class="analysis-card">
                <div class="card-header">
                    <h3>
                        <i class="icon-clipboard"></i>
                        Hooaja kokkuvõte ja järeldused
                    </h3>
                </div>
                <div class="card-content">
                    <div class="summary-grid">
                        <div class="summary-highlights">
                            <h4>Põhilised saavutused</h4>
                            <ul class="highlights-list">
                                <li>Hooajal osales kokku <strong><?php echo $overview_stats['total_athletes']; ?></strong> sportlast</li>
                                <li>Osaleti <strong><?php echo $overview_stats['total_competitions']; ?></strong> võistlusel</li>
                                <li>Tehti <strong><?php echo $overview_stats['total_results']; ?></strong> starti</li>
                                <li>Parim tulemus oli <strong><?php echo formatPoints($overview_stats['max_points']); ?></strong> punkti</li>
                                <li>Keskmine tulemus <strong><?php echo formatPoints($overview_stats['avg_points']); ?></strong> punkti</li>
                            </ul>
                        </div>
                        
                        <div class="summary-insights">
                            <h4>Huvitavad faktid</h4>
                            <ul class="insights-list">
                                <?php if (!empty($discipline_detailed_stats)): ?>
                                    <?php 
                                    $most_popular = array_reduce($discipline_detailed_stats, fn($a, $b) => ($a['total_results'] ?? 0) > ($b['total_results'] ?? 0) ? $a : $b);
                                    ?>
                                    <li>Populaarseim distsipliin: <strong><?php echo getDisciplineName($most_popular['discipline']); ?></strong> (<?php echo $most_popular['total_results']; ?> tulemust)</li>
                                <?php endif; ?>
                                
                                <?php if (!empty($age_group_stats)): ?>
                                    <?php 
                                    $most_active_age = array_reduce($age_group_stats, fn($a, $b) => ($a['results_count'] ?? 0) > ($b['results_count'] ?? 0) ? $a : $b);
                                    ?>
                                    <li>Aktiivseim vanusegrupp: <strong><?php echo getAgeGroupName($most_active_age['age_group']); ?></strong></li>
                                <?php endif; ?>
                                
                                <?php if (!empty($monthly_trends)): ?>
                                    <?php 
                                    $busiest_month = array_reduce($monthly_trends, fn($a, $b) => ($a['results_count'] ?? 0) > ($b['results_count'] ?? 0) ? $a : $b);
                                    $months = ['01' => 'jaanuar', '02' => 'veebruar', '03' => 'märts', '04' => 'aprill', 
                                              '05' => 'mai', '06' => 'juuni', '07' => 'juuli', '08' => 'august',
                                              '09' => 'september', '10' => 'oktoober', '11' => 'november', '12' => 'detsember'];
                                    ?>
                                    <li>Aktiivseim kuu: <strong><?php echo $months[$busiest_month['month']]; ?></strong> (<?php echo $busiest_month['results_count']; ?> tulemust)</li>
                                <?php endif; ?>
                                
                                <li>Osaluse määr: <strong><?php echo round(($overview_stats['athletes_with_results'] / $overview_stats['total_athletes']) * 100, 1); ?>%</strong> sportlastest osales võistlustel</li>
                                
                                <?php if (!empty($competitions_analysis)): ?>
                                    <?php 
                                    $avg_completion = array_sum(array_column($competitions_analysis, 'completion_rate')) / count($competitions_analysis);
                                    ?>
                                    <li>Keskmine lõpetamise määr: <strong><?php echo round($avg_completion, 1); ?>%</strong></li>
                                <?php endif; ?>
                            </ul>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</main>

<script>
// ADVANCED STATISTICS JAVASCRIPT
document.addEventListener('DOMContentLoaded', function() {
    console.log('🔬 Advanced Statistics Panel loaded');
    initializeCharts();
    initializeFilters();
    initializeInteractions();
});

// Filter management
function toggleFilters() {
    const filters = document.getElementById('advancedFilters');
    const toggle = document.getElementById('filtersToggle');
    
    if (filters.classList.contains('expanded')) {
        filters.classList.remove('expanded');
        toggle.innerHTML = '<i class="icon-chevron-down"></i>';
    } else {
        filters.classList.add('expanded');
        toggle.innerHTML = '<i class="icon-chevron-up"></i>';
    }
}

function initializeFilters() {
    // Auto-submit filters on change
    const filterSelects = document.querySelectorAll('.filter-select, .filter-input');
    filterSelects.forEach(select => {
        select.addEventListener('change', function() {
            // Add loading state
            this.style.opacity = '0.7';
            setTimeout(() => {
                this.closest('form').submit();
            }, 300);
        });
    });
}

// Chart interactions
function updateTrendChart() {
    const metric = document.getElementById('trendMetric').value;
    const chart = document.getElementById('trendsChart');
    const bars = chart.querySelectorAll('.trend-bar');
    
    // Hide all bars first
    bars.forEach(bar => bar.style.opacity = '0.3');
    
    // Show selected metric bars
    bars.forEach(bar => {
        if (bar.classList.contains(metric.replace('_count', '').replace('avg_', ''))) {
            bar.style.opacity = '1';
        }
    });
}

function initializeCharts() {
    // Add hover effects to trend bars
    const trendBars = document.querySelectorAll('.trend-bar');
    trendBars.forEach(bar => {
        bar.addEventListener('mouseenter', function() {
            this.style.transform = 'scaleY(1.1)';
            
            // Show tooltip
            const tooltip = document.createElement('div');
            tooltip.className = 'chart-tooltip';
            tooltip.textContent = this.getAttribute('title');
            tooltip.style.cssText = `
                position: absolute;
                background: rgba(0,0,0,0.8);
                color: white;
                padding: 8px 12px;
                border-radius: 6px;
                font-size: 12px;
                pointer-events: none;
                z-index: 1000;
                top: -40px;
                left: 50%;
                transform: translateX(-50%);
                white-space: nowrap;
            `;
            this.style.position = 'relative';
            this.appendChild(tooltip);
        });
        
        bar.addEventListener('mouseleave', function() {
            this.style.transform = 'scaleY(1)';
            const tooltip = this.querySelector('.chart-tooltip');
            if (tooltip) {
                tooltip.remove();
            }
        });
    });
}

function initializeInteractions() {
    // Discipline cards hover effect
    const disciplineCards = document.querySelectorAll('.discipline-detailed-card');
    disciplineCards.forEach(card => {
        card.addEventListener('mouseenter', function() {
            this.style.transform = 'translateY(-4px) scale(1.02)';
            this.style.boxShadow = '0 8px 25px rgba(0,0,0,0.15)';
        });
        
        card.addEventListener('mouseleave', function() {
            this.style.transform = 'translateY(0) scale(1)';
            this.style.boxShadow = '';
        });
    });
    
    // Competition table sorting
   
    
    // Record cards animation
    const recordCards = document.querySelectorAll('.record-card');
    recordCards.forEach((card, index) => {
        setTimeout(() => {
            card.style.opacity = '1';
            card.style.transform = 'translateY(0)';
        }, index * 100);
    });
}

// Competition table sorting
function sortCompetitions() {
    const sortBy = document.getElementById('competitionSort').value;
    const table = document.getElementById('competitionsTable');
    const tbody = table.querySelector('tbody');
    const rows = Array.from(tbody.querySelectorAll('tr'));
    
    rows.sort((a, b) => {
        let aVal, bVal;
        
        switch(sortBy) {
            case 'date':
                aVal = new Date(a.querySelector('.competition-date').textContent);
                bVal = new Date(b.querySelector('.competition-date').textContent);
                return bVal - aVal; // Newest first
                
            case 'participants':
                aVal = parseInt(a.querySelector('.participants-count .count-main').textContent);
                bVal = parseInt(b.querySelector('.participants-count .count-main').textContent);
                return bVal - aVal; // Highest first
                
            case 'completion_rate':
                aVal = parseFloat(a.querySelector('.efficiency-text').textContent);
                bVal = parseFloat(b.querySelector('.efficiency-text').textContent);
                return bVal - aVal; // Highest first
                
            case 'avg_points':
                aVal = parseFloat(a.querySelector('.avg-points').textContent);
                bVal = parseFloat(b.querySelector('.avg-points').textContent);
                return bVal - aVal; // Highest first
                
            default:
                return 0;
        }
    });
    
    // Remove existing rows and add sorted ones
    rows.forEach(row => tbody.removeChild(row));
    rows.forEach(row => tbody.appendChild(row));
    
    // Add visual feedback
    tbody.style.opacity = '0.5';
    setTimeout(() => {
        tbody.style.opacity = '1';
    }, 300);
}

// Export functionality
function exportStatistics() {
    const exportData = {
        period: document.querySelector('select[name="year"]').value,
        filters: {
            discipline: document.querySelector('select[name="discipline"]').value,
            age_group: document.querySelector('select[name="age_group"]').value,
            date_from: document.querySelector('input[name="date_from"]').value,
            date_to: document.querySelector('input[name="date_to"]').value
        },
        timestamp: new Date().toISOString()
    };
    
    // Create CSV content
    let csvContent = "data:text/csv;charset=utf-8,";
    csvContent += "Estreka Ratsaklubi - Hooaja statistika\\n";
    csvContent += "Eksportitud: " + new Date().toLocaleString('et-EE') + "\\n\\n";
    
    // Add overview stats
    const overviewStats = document.querySelectorAll('.stat-card');
    csvContent += "ÜLEVAADE\\n";
    overviewStats.forEach(card => {
        const label = card.querySelector('.stat-label').textContent;
        const number = card.querySelector('.stat-number').textContent;
        csvContent += `${label},${number}\\n`;
    });
    
    csvContent += "\\n";
    
    // Add discipline stats
    csvContent += "DISTSIPLIINID\\n";
    csvContent += "Distsipliin,Sportlasi,Võistlusi,Tulemusi,Keskmine,Parim\\n";
    
    const disciplineCards = document.querySelectorAll('.discipline-detailed-card');
    disciplineCards.forEach(card => {
        const title = card.querySelector('.discipline-title h4').textContent;
        const metrics = card.querySelectorAll('.metric-value');
        const row = [title];
        metrics.forEach(metric => row.push(metric.textContent));
        csvContent += row.join(',') + "\\n";
    });
    
    // Download file
    const encodedUri = encodeURI(csvContent);
    const link = document.createElement("a");
    link.setAttribute("href", encodedUri);
    link.setAttribute("download", `estreka_statistika_${exportData.period}.csv`);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    
    console.log('📊 Statistics exported', exportData);
}

// Print functionality
function printReport() {
    // Hide unnecessary elements for printing
    const elementsToHide = [
        '.page-actions',
        '.filters-section',
        '.btn',
        '.header-actions'
    ];
    
    elementsToHide.forEach(selector => {
        const elements = document.querySelectorAll(selector);
        elements.forEach(el => el.style.display = 'none');
    });
    
    // Print
    window.print();
    
    // Restore elements after printing
    setTimeout(() => {
        elementsToHide.forEach(selector => {
            const elements = document.querySelectorAll(selector);
            elements.forEach(el => el.style.display = '');
        });
    }, 1000);
}

// View toggles
function toggleView(viewId) {
    const view = document.getElementById(viewId);
    if (view.classList.contains('grid-view')) {
        view.classList.remove('grid-view');
        view.classList.add('list-view');
    } else {
        view.classList.add('grid-view');
        view.classList.remove('list-view');
    }
}

// Utility functions
function formatNumber(num, decimals = 0) {
    return new Intl.NumberFormat('et-EE', {
        minimumFractionDigits: decimals,
        maximumFractionDigits: decimals
    }).format(num);
}

function animateCountUp(element, target, duration = 2000) {
    const start = 0;
    const range = target - start;
    const increment = target > start ? 1 : -1;
    const stepTime = Math.abs(Math.floor(duration / range));
    let current = start;
    
    const timer = setInterval(() => {
        current += increment;
        element.textContent = current;
        
        if (current === target) {
            clearInterval(timer);
        }
    }, stepTime);
}

// Initialize count-up animations on load
document.addEventListener('DOMContentLoaded', function() {
    const statNumbers = document.querySelectorAll('.stat-number');
    statNumbers.forEach(element => {
        const target = parseInt(element.textContent);
        if (!isNaN(target)) {
            element.textContent = '0';
            setTimeout(() => {
                animateCountUp(element, target, 1500);
            }, 300);
        }
    });
});

console.log('📊 Advanced Statistics Panel JavaScript loaded');
</script>

<?php include '../includes/footer.php'; ?>