<?php

declare(strict_types=1);

require_once dirname(__DIR__, 2) . '/core/auth.php';
require_once dirname(__DIR__, 2) . '/core/csrf.php';
require_once dirname(__DIR__, 2) . '/core/validation.php';
require_once dirname(__DIR__, 2) . '/core/audit.php';

function statistics_module_boot(): void
{
    require_login();
    require_permission('manage_statistics');
}

function statistics_competition_options(): array
{
    return db_fetch_all(
        'SELECT c.id, c.name, c.type, c.status, s.name AS season_name
         FROM competitions c
         INNER JOIN seasons s ON s.id = c.season_id
         ORDER BY FIELD(c.status, "active", "open", "draft", "completed", "cancelled"), s.start_date DESC, c.name ASC'
    );
}

function player_statistics_rows(?int $competitionId = null): array
{
    $sql = 'SELECT ps.*, p.first_name, p.last_name, p.position,
                   c.name AS club_name, c.short_name,
                   comp.name AS competition_name, s.name AS season_name
            FROM player_statistics ps
            INNER JOIN players p ON p.id = ps.player_id
            INNER JOIN clubs c ON c.id = ps.club_id
            INNER JOIN competitions comp ON comp.id = ps.competition_id
            INNER JOIN seasons s ON s.id = ps.season_id';

    $params = [];
    if ($competitionId !== null && $competitionId > 0) {
        $sql .= ' WHERE ps.competition_id = :competition_id';
        $params['competition_id'] = $competitionId;
    }

    $sql .= ' ORDER BY ps.goals DESC, ps.assists DESC, ps.yellow_cards ASC, p.first_name ASC, p.last_name ASC';

    return db_fetch_all($sql, $params);
}

function statistics_leaders(string $metric, ?int $competitionId = null, int $limit = 5): array
{
    $allowedMetrics = ['goals', 'assists', 'yellow_cards', 'red_cards', 'appearances'];
    if (!in_array($metric, $allowedMetrics, true)) {
        return [];
    }

    $sql = sprintf(
        'SELECT ps.%1$s AS metric_value, p.first_name, p.last_name, c.name AS club_name
         FROM player_statistics ps
         INNER JOIN players p ON p.id = ps.player_id
         INNER JOIN clubs c ON c.id = ps.club_id',
        $metric
    );
    $params = [];

    if ($competitionId !== null && $competitionId > 0) {
        $sql .= ' WHERE ps.competition_id = :competition_id';
        $params['competition_id'] = $competitionId;
    }

    $sql .= sprintf(' ORDER BY ps.%1$s DESC, p.first_name ASC, p.last_name ASC LIMIT %2$d', $metric, $limit);

    return db_fetch_all($sql, $params);
}
