<?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';
require_once dirname(__DIR__, 2) . '/modules/notifications/helpers.php';
require_once dirname(__DIR__) . '/players/helpers.php';
require_once dirname(__DIR__) . '/table/helpers.php';
require_once dirname(__DIR__) . '/fixtures/helpers.php';

function match_news_player_name(array $event): string
{
    $fullName = trim((string) ($event['player_first_name'] ?? '') . ' ' . (string) ($event['player_last_name'] ?? ''));

    return $fullName !== '' ? $fullName : (string) ($event['club_name'] ?? 'Unknown player');
}

function match_news_minute_label(array $event): string
{
    $minute = (int) ($event['minute'] ?? 0);
    $extraMinute = (int) ($event['extra_minute'] ?? 0);

    return $extraMinute > 0 ? sprintf('%d+%d\'', $minute, $extraMinute) : sprintf('%d\'', $minute);
}

function match_news_goal_events(array $events): array
{
    return array_values(array_filter($events, static function (array $event): bool {
        return in_array((string) ($event['event_type'] ?? ''), ['goal', 'own_goal', 'penalty_goal'], true);
    }));
}

function match_news_red_card_events(array $events): array
{
    return array_values(array_filter($events, static function (array $event): bool {
        return (string) ($event['event_type'] ?? '') === 'red_card';
    }));
}

function match_news_goal_beneficiary(array $fixture, array $event): string
{
    $clubId = (int) ($event['club_id'] ?? 0);
    $eventType = (string) ($event['event_type'] ?? '');

    if ($eventType === 'own_goal') {
        return $clubId === (int) $fixture['home_club_id'] ? 'away' : 'home';
    }

    return $clubId === (int) $fixture['home_club_id'] ? 'home' : 'away';
}

function match_news_scoreline_timeline(array $fixture, array $goalEvents): array
{
    $homeScore = 0;
    $awayScore = 0;
    $timeline = [];

    foreach ($goalEvents as $event) {
        if (match_news_goal_beneficiary($fixture, $event) === 'home') {
            $homeScore++;
        } else {
            $awayScore++;
        }

        $timeline[] = [
            'event' => $event,
            'home_score' => $homeScore,
            'away_score' => $awayScore,
        ];
    }

    return $timeline;
}

function match_news_detect_comeback(array $fixture, array $goalEvents): bool
{
    $homeFinal = (int) ($fixture['home_score'] ?? 0);
    $awayFinal = (int) ($fixture['away_score'] ?? 0);

    if ($homeFinal === $awayFinal) {
        return false;
    }

    $winner = $homeFinal > $awayFinal ? 'home' : 'away';
    foreach (match_news_scoreline_timeline($fixture, $goalEvents) as $snapshot) {
        if ($winner === 'home' && $snapshot['home_score'] < $snapshot['away_score']) {
            return true;
        }

        if ($winner === 'away' && $snapshot['away_score'] < $snapshot['home_score']) {
            return true;
        }
    }

    return false;
}

function match_news_late_winner(array $fixture, array $goalEvents): bool
{
    $homeFinal = (int) ($fixture['home_score'] ?? 0);
    $awayFinal = (int) ($fixture['away_score'] ?? 0);

    if ($homeFinal === $awayFinal || $goalEvents === []) {
        return false;
    }

    $winner = $homeFinal > $awayFinal ? 'home' : 'away';
    $lastGoal = end($goalEvents);
    if (!is_array($lastGoal)) {
        return false;
    }

    $minute = (int) ($lastGoal['minute'] ?? 0);

    return $minute >= 85 && match_news_goal_beneficiary($fixture, $lastGoal) === $winner;
}

function match_news_player_of_match(array $fixture, array $events): ?string
{
    $winnerClubId = null;
    if ((int) ($fixture['home_score'] ?? 0) > (int) ($fixture['away_score'] ?? 0)) {
        $winnerClubId = (int) $fixture['home_club_id'];
    } elseif ((int) ($fixture['away_score'] ?? 0) > (int) ($fixture['home_score'] ?? 0)) {
        $winnerClubId = (int) $fixture['away_club_id'];
    }

    $contributions = [];
    foreach ($events as $event) {
        if (!in_array((string) ($event['event_type'] ?? ''), ['goal', 'penalty_goal', 'assist'], true)) {
            continue;
        }

        if ($winnerClubId !== null && (int) ($event['club_id'] ?? 0) !== $winnerClubId) {
            continue;
        }

        $name = match_news_player_name($event);
        $contributions[$name] = ($contributions[$name] ?? 0) + 1;
    }

    if ($contributions === []) {
        return null;
    }

    arsort($contributions);

    return array_key_first($contributions) ?: null;
}

function match_news_goal_summary(array $fixture, array $goalEvents): string
{
    if ($goalEvents === []) {
        return 'There were no recorded goalscorer details in the match log, but the final scoreline was still confirmed in full.';
    }

    $fragments = [];
    foreach (array_slice($goalEvents, 0, 4) as $event) {
        $player = match_news_player_name($event);
        $minute = match_news_minute_label($event);
        $eventType = (string) ($event['event_type'] ?? 'goal');

        if ($eventType === 'own_goal') {
            $fragments[] = sprintf('%s turned the ball into his own net at %s', $player, $minute);
        } elseif ($eventType === 'penalty_goal') {
            $fragments[] = sprintf('%s converted from the spot at %s', $player, $minute);
        } else {
            $fragments[] = sprintf('%s struck at %s', $player, $minute);
        }
    }

    return count($fragments) === 1 ? $fragments[0] . '.' : implode('; ', $fragments) . '.';
}

function match_news_red_card_summary(array $redCards): string
{
    if ($redCards === []) {
        return '';
    }

    $firstCard = $redCards[0];

    return sprintf(
        '%s was shown a red card at %s, a flashpoint that sharpened the tension and altered the contest.',
        match_news_player_name($firstCard),
        match_news_minute_label($firstCard)
    );
}

function match_news_stage_text(array $fixture): string
{
    $stage = competition_stage_label($fixture, 'League');

    return $stage === 'League' ? 'league fixture' : strtolower($stage);
}

function match_news_visual_mood(array $fixture): string
{
    $margin = abs((int) ($fixture['home_score'] ?? 0) - (int) ($fixture['away_score'] ?? 0));

    if ($margin >= 3) {
        return 'fiery powerful atmosphere';
    }

    if ($margin === 1) {
        return 'dark cinematic tension';
    }

    return 'balanced dramatic stadium atmosphere';
}

function match_news_design_style(array $fixture): string
{
    return (string) ($fixture['knockout_round'] ?? '') !== '' ? 'UEFA broadcast style' : 'Sky Sports modern style';
}

function build_verified_match_news_payload(array $fixture, ?array $events = null): array
{
    $events ??= match_events_for_fixture((int) $fixture['id']);
    $goalEvents = match_news_goal_events($events);
    $redCards = match_news_red_card_events($events);
    $homeTeam = (string) $fixture['home_club_name'];
    $awayTeam = (string) $fixture['away_club_name'];
    $homeScore = (int) ($fixture['home_score'] ?? 0);
    $awayScore = (int) ($fixture['away_score'] ?? 0);
    $margin = abs($homeScore - $awayScore);
    $stageText = match_news_stage_text($fixture);
    $competitionName = (string) ($fixture['competition_name'] ?? 'Competition');
    $winner = $homeScore > $awayScore ? $homeTeam : ($awayScore > $homeScore ? $awayTeam : null);
    $loser = $winner === $homeTeam ? $awayTeam : ($winner === $awayTeam ? $homeTeam : null);
    $comeback = match_news_detect_comeback($fixture, $goalEvents);
    $lateWinner = match_news_late_winner($fixture, $goalEvents);
    $playerOfMatch = match_news_player_of_match($fixture, $events);
    $stageLabel = competition_stage_label($fixture, 'League');
    $goalSummary = match_news_goal_summary($fixture, $goalEvents);
    $redCardSummary = match_news_red_card_summary($redCards);
    $matchDate = format_match_date_time($fixture['match_date'] ?? null, $fixture['match_time'] ?? null, 'Date TBC', 'Time TBC');

    if ($winner === null) {
        $headline = sprintf('%s and %s share the points in tense %s clash', $homeTeam, $awayTeam, $competitionName);
        $subheadline = sprintf('Neither side found the separating goal as the %s fixture finished %d-%d.', $stageText, $homeScore, $awayScore);
        $summary = sprintf('%s and %s played out a %d-%d draw in the %s.', $homeTeam, $awayTeam, $homeScore, $awayScore, $competitionName);
        $outcomeSentence = sprintf('%s and %s had to settle for a %d-%d draw in the %s, a result that reflected long spells of tension and discipline rather than a decisive breakthrough.', $homeTeam, $awayTeam, $homeScore, $awayScore, $stageText);
        $closingSentence = sprintf('Both teams leave this %s encounter with lessons to carry forward, and the confirmed scoreline now stands as the official record of a closely fought night.', $competitionName);
    } elseif ($lateWinner) {
        $headline = sprintf('Late drama sends %s past %s', $winner, $loser);
        $subheadline = sprintf('%s struck when it mattered most to win %d-%d in the %s.', $winner, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $summary = sprintf('Late drama in the %s as %s beat %s %d-%d after a tense, narrow contest.', $competitionName, $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore));
        $outcomeSentence = sprintf('%s left it late but found the decisive moment to defeat %s %d-%d in the %s, turning a tight contest into one of the most emotionally charged finishes on the calendar.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $closingSentence = sprintf('For %s it is a result that carries momentum and belief, while %s are left with the kind of late heartbreak that lingers long after the final whistle.', $winner, $loser);
    } elseif ($margin >= 3) {
        $headline = sprintf('Ruthless %s sweep aside %s', $winner, $loser);
        $subheadline = sprintf('%s produced a statement %d-%d victory in the %s.', $winner, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $summary = sprintf('%s were too strong for %s, powering to a %d-%d win in the %s.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $competitionName);
        $outcomeSentence = sprintf('%s overwhelmed %s %d-%d in the %s, delivering a statement victory built on authority, incision, and a level of attacking conviction that never allowed the contest to settle.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $closingSentence = sprintf('%s move on with renewed confidence after a result that underlined their depth and ruthless edge, while %s were left to absorb a difficult night.', $winner, $loser);
    } elseif ($margin === 2) {
        $headline = sprintf('%s claim commanding win over %s', $winner, $loser);
        $subheadline = sprintf('%s controlled the key moments on the way to a %d-%d result in the %s.', $winner, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $summary = sprintf('%s beat %s %d-%d with control and authority in the %s.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $competitionName);
        $outcomeSentence = sprintf('%s defeated %s %d-%d in the %s with a performance full of control and authority, managing the rhythm of the game well enough to keep pressure on their opponents throughout.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $closingSentence = sprintf('The result keeps %s moving with confidence, while %s leave knowing the margin was shaped by a handful of decisive passages.', $winner, $loser);
    } else {
        $headline = sprintf('%s edge fierce battle against %s', $winner, $loser);
        $subheadline = sprintf('Fine margins proved decisive as %s secured a %d-%d win in the %s.', $winner, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $summary = sprintf('%s edged %s %d-%d after a disciplined and hard-fought %s contest.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $competitionName);
        $outcomeSentence = sprintf('%s defeated %s %d-%d in the %s, a contest in which fine margins proved decisive and one decisive blow settled the evening.', $winner, $loser, max($homeScore, $awayScore), min($homeScore, $awayScore), $stageText);
        $closingSentence = sprintf('%s take the official result and the momentum that comes with it, while %s are left to reflect on a narrow defeat in which football rarely forgives mistakes.', $winner, $loser);
    }

    $paragraphOne = $outcomeSentence . ' ' . sprintf('Played on %s, the fixture now enters the record books with the final scoreline fully verified.', $matchDate);

    $paragraphTwo = sprintf(
        'The flow of the match carried the feel of serious football from the outset, with %s and %s trading momentum in a way that demanded concentration rather than chaos. %s%s',
        $homeTeam,
        $awayTeam,
        $comeback ? sprintf('%s showed notable resilience after falling behind, refusing to let the game drift away from them. ', $winner ?? $homeTeam) : '',
        $margin <= 1 ? 'It was the kind of contest that rewarded tactical discipline, patience in possession, and composure in the biggest moments.' : 'As the scoreline widened, the winning side looked increasingly assured, pressing their advantage without losing shape.'
    );

    $paragraphThree = $goalSummary . ' ' . ($redCardSummary !== '' ? $redCardSummary . ' ' : '') . sprintf('Those moments defined the narrative of the night and gave the final %d-%d scoreline its proper context.', $homeScore, $awayScore);

    $playerLine = $playerOfMatch !== null
        ? sprintf('The standout individual contribution came from %s, who emerged as the game\'s most influential figure from the recorded events.', $playerOfMatch)
        : 'No official Player of the Match was recorded, but the winning side still had enough collective authority to decide the contest cleanly.';

    $paragraphFour = $playerLine . ' ' . $closingSentence . ' ' . sprintf('For %s, their campaign continues with growing belief; for %s, the focus now shifts to response and recovery.', $winner ?? $homeTeam, $loser ?? $awayTeam);

    return [
        'headline' => $headline,
        'subheadline' => $subheadline,
        'article_body' => implode("\n\n", [$subheadline, $paragraphOne, $paragraphTwo, $paragraphThree, $paragraphFour, 'Short summary: ' . $summary]),
        'short_summary' => $summary,
        'match_card' => [
            'title' => $headline,
            'subtitle' => $subheadline,
            'final_score' => sprintf('%d-%d', $homeScore, $awayScore),
            'penalty_score' => '',
            'competition' => $competitionName,
            'stage' => $stageLabel,
            'qualified_text' => $winner !== null && $stageLabel !== 'League' ? sprintf('%s advance', $winner) : 'Final score confirmed',
            'stadium' => '',
            'man_of_match' => $playerOfMatch ?? 'Not officially designated',
            'visual_mood' => match_news_visual_mood($fixture),
            'design_style' => match_news_design_style($fixture),
            'background_elements' => [$stageLabel, $winner !== null ? 'scoreline spotlight' : 'shared spoils emphasis'],
            'team_colors' => [
                'home' => '',
                'away' => '',
            ],
        ],
        'tags' => array_values(array_filter([
            strtolower(str_replace(' ', '-', $competitionName)),
            $winner !== null ? strtolower(str_replace(' ', '-', $winner)) : 'draw',
            strtolower(str_replace(' ', '-', $stageLabel)),
            $comeback ? 'comeback' : null,
            $lateWinner ? 'late-drama' : null,
        ])),
        'news_category' => 'match_report',
    ];
}

function publish_verified_match_report(array $fixture): int
{
    $payload = build_verified_match_news_payload($fixture);
    $publicationMode = (string) site_setting('verified_match_report_mode', 'published');
    $isPublished = $publicationMode !== 'draft';

    if (match_update_news_schema_ready()) {
        $existingDraft = match_update_existing_draft((int) $fixture['id']);
        if ($existingDraft !== null) {
            db_query(
                'UPDATE news_posts
                 SET title = :title,
                     slug = :slug,
                     category = :category,
                     fixture_id = :fixture_id,
                     body = :body,
                     status = :status,
                     published_at = :published_at
                 WHERE id = :id',
                [
                    'title' => $payload['headline'],
                    'slug' => fixture_unique_news_slug($payload['headline'], (int) $existingDraft['id']),
                    'category' => $payload['news_category'],
                    'fixture_id' => (int) $fixture['id'],
                    'body' => $payload['article_body'],
                    'status' => $isPublished ? 'published' : 'draft',
                    'published_at' => $isPublished ? date('Y-m-d H:i:s') : null,
                    'id' => (int) $existingDraft['id'],
                ]
            );

            if ($isPublished) {
                web_push_send_news_post((int) $existingDraft['id']);
            }

            return (int) $existingDraft['id'];
        }
    }

    db_query(
        'INSERT INTO news_posts (
            title, slug, category, fixture_id, body, status, published_at, created_at, created_by
         ) VALUES (
            :title, :slug, :category, :fixture_id, :body, :status, :published_at, NOW(), :created_by
         )',
        [
            'title' => $payload['headline'],
            'slug' => fixture_unique_news_slug($payload['headline']),
            'category' => $payload['news_category'],
            'fixture_id' => (int) $fixture['id'],
            'body' => $payload['article_body'],
            'status' => $isPublished ? 'published' : 'draft',
            'published_at' => $isPublished ? date('Y-m-d H:i:s') : null,
            'created_by' => (int) (current_user()['id'] ?? ($_SESSION['user_id'] ?? 0)) ?: null,
        ]
    );

    $postId = (int) db()->lastInsertId();
    if ($isPublished) {
        web_push_send_news_post($postId);
    }

    return $postId;
}

function match_update_news_schema_ready(): bool
{
    static $ready = null;

    if ($ready !== null) {
        return $ready;
    }

    try {
        $fixtureIdColumnCount = (int) db_fetch_value(
            'SELECT COUNT(*)
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = "news_posts"
               AND column_name = "fixture_id"',
            [],
            0
        );

        $categoryColumnType = (string) db_fetch_value(
            'SELECT COLUMN_TYPE
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = "news_posts"
               AND column_name = "category"
             LIMIT 1',
            [],
            ''
        );

        $ready = $fixtureIdColumnCount === 1 && str_contains($categoryColumnType, 'match_update');
    } catch (Throwable) {
        $ready = false;
    }

    return $ready;
}

function match_update_notable_event_types(): array
{
    return ['goal', 'own_goal', 'penalty_goal', 'penalty_miss', 'red_card'];
}

function match_update_is_notable_event_type(?string $eventType): bool
{
    return in_array((string) $eventType, match_update_notable_event_types(), true);
}

function match_update_existing_draft(int $fixtureId): ?array
{
    if (!match_update_news_schema_ready()) {
        return null;
    }

    return db_fetch_one(
        'SELECT *
         FROM news_posts
         WHERE fixture_id = :fixture_id
           AND category = :category
           AND status = "draft"
         ORDER BY id DESC
         LIMIT 1',
        [
            'fixture_id' => $fixtureId,
            'category' => 'match_update',
        ]
    );
}

function build_match_update_news_payload(array $fixture, string $changeLabel = 'Match log updated'): array
{
    $events = match_events_for_fixture((int) $fixture['id']);
    $goalEvents = match_news_goal_events($events);
    $redCards = match_news_red_card_events($events);
    $scoreline = isset($fixture['result_id']) && $fixture['result_id'] !== null
        ? sprintf('%d-%d', (int) ($fixture['home_score'] ?? 0), (int) ($fixture['away_score'] ?? 0))
        : 'Score pending';
    $headline = sprintf('Draft match update: %s vs %s', $fixture['home_club_name'], $fixture['away_club_name']);
    $subheadline = sprintf(
        '%s in %s. Current official score: %s.',
        $changeLabel,
        (string) ($fixture['competition_name'] ?? 'the competition'),
        $scoreline
    );

    $resultParagraph = isset($fixture['result_id']) && $fixture['result_id'] !== null
        ? sprintf(
            'A result has been submitted for %s vs %s, with the current working score at %d-%d. This update remains in draft until the match officials complete verification.',
            $fixture['home_club_name'],
            $fixture['away_club_name'],
            (int) ($fixture['home_score'] ?? 0),
            (int) ($fixture['away_score'] ?? 0)
        )
        : sprintf(
            'No official result has been submitted yet for %s vs %s, but the live match log has been updated and is being tracked in draft form pending confirmation.',
            $fixture['home_club_name'],
            $fixture['away_club_name']
        );

    $timelineParagraph = match_news_goal_summary($fixture, $goalEvents);
    if ($redCards !== []) {
        $timelineParagraph .= ' ' . match_news_red_card_summary($redCards);
    }

    $closingParagraph = 'This draft update is internal until further edits or final verification confirm the official story for publication.';

    return [
        'headline' => $headline,
        'body' => implode("\n\n", [$subheadline, $resultParagraph, $timelineParagraph, $closingParagraph]),
        'category' => 'match_update',
        'fixture_id' => (int) $fixture['id'],
    ];
}

function upsert_match_update_draft(array $fixture, string $changeLabel = 'Match log updated'): ?int
{
    if (
        !match_update_news_schema_ready()
        || (string) site_setting('enable_match_update_drafts', '1') !== '1'
        || (string) ($fixture['result_status'] ?? '') === 'verified'
    ) {
        return null;
    }

    $payload = build_match_update_news_payload($fixture, $changeLabel);
    $existingDraft = match_update_existing_draft((int) $fixture['id']);

    if ($existingDraft !== null) {
        db_query(
            'UPDATE news_posts
             SET title = :title,
                 body = :body,
                 category = :category,
                 fixture_id = :fixture_id,
                 status = "draft"
             WHERE id = :id',
            [
                'title' => $payload['headline'],
                'body' => $payload['body'],
                'category' => $payload['category'],
                'fixture_id' => $payload['fixture_id'],
                'id' => (int) $existingDraft['id'],
            ]
        );

        return (int) $existingDraft['id'];
    }

    db_query(
        'INSERT INTO news_posts (
            title, slug, category, fixture_id, body, status, created_by, published_at, created_at
         ) VALUES (
            :title, :slug, :category, :fixture_id, :body, "draft", :created_by, NULL, NOW()
         )',
        [
            'title' => $payload['headline'],
            'slug' => fixture_unique_news_slug($payload['headline']),
            'category' => $payload['category'],
            'fixture_id' => $payload['fixture_id'],
            'body' => $payload['body'],
            'created_by' => (int) (current_user()['id'] ?? ($_SESSION['user_id'] ?? 0)) ?: null,
        ]
    );

    return (int) db()->lastInsertId();
}

function match_module_boot(): void
{
    require_login();

    if (!can_submit_match_results() && !can_verify_match_results()) {
        abort_request(403, 'Access denied', 'You do not have permission to access match results management.');
    }
}

function can_submit_match_results(): bool
{
    return can('submit_results');
}

function can_verify_match_results(): bool
{
    return can('verify_results');
}

function can_manage_match_events(): bool
{
    return can_submit_match_results() || can('manage_statistics') || can('manage_discipline');
}

function match_result_status_badge_class(string $status): string
{
    return match ($status) {
        'verified' => 'badge-soft-success',
        'submitted' => 'bg-info-subtle text-info-emphasis',
        'disputed' => 'badge-soft-danger',
        'draft' => 'text-bg-light',
        default => 'text-bg-light',
    };
}

function match_event_type_options(): array
{
    return ['goal', 'assist', 'yellow_card', 'red_card', 'substitution', 'own_goal', 'penalty_goal', 'penalty_miss'];
}

function match_result_list(): array
{
    return db_fetch_all(
        'SELECT f.*, c.name AS competition_name, s.name AS season_name,
                hc.name AS home_club_name, ac.name AS away_club_name,
                mr.id AS result_id, mr.home_score, mr.away_score, mr.result_status,
                mr.submitted_at, mr.verified_at,
                submitter.full_name AS submitted_by_name,
                verifier.full_name AS verified_by_name
         FROM fixtures f
         INNER JOIN competitions c ON c.id = f.competition_id
         INNER JOIN seasons s ON s.id = f.season_id
         INNER JOIN clubs hc ON hc.id = f.home_club_id
         INNER JOIN clubs ac ON ac.id = f.away_club_id
         LEFT JOIN match_results mr ON mr.fixture_id = f.id
         LEFT JOIN users submitter ON submitter.id = mr.submitted_by
         LEFT JOIN users verifier ON verifier.id = mr.verified_by
         ORDER BY COALESCE(f.match_date, "9999-12-31") DESC, COALESCE(f.match_time, "23:59:59") DESC, f.id DESC'
    );
}

function get_match_fixture_by_id(int $fixtureId): ?array
{
    return db_fetch_one(
        'SELECT f.*, c.name AS competition_name, c.type AS competition_type, c.status AS competition_status,
                s.name AS season_name,
                hc.name AS home_club_name, ac.name AS away_club_name,
                mr.id AS result_id, mr.home_score, mr.away_score, mr.result_status,
                mr.submitted_by, mr.verified_by, mr.submitted_at, mr.verified_at,
                submitter.full_name AS submitted_by_name,
                verifier.full_name AS verified_by_name
         FROM fixtures f
         INNER JOIN competitions c ON c.id = f.competition_id
         INNER JOIN seasons s ON s.id = f.season_id
         INNER JOIN clubs hc ON hc.id = f.home_club_id
         INNER JOIN clubs ac ON ac.id = f.away_club_id
         LEFT JOIN match_results mr ON mr.fixture_id = f.id
         LEFT JOIN users submitter ON submitter.id = mr.submitted_by
         LEFT JOIN users verifier ON verifier.id = mr.verified_by
         WHERE f.id = :id
         LIMIT 1',
        ['id' => $fixtureId]
    );
}

function match_events_for_fixture(int $fixtureId): array
{
    return db_fetch_all(
        'SELECT me.*, c.name AS club_name,
                p.first_name AS player_first_name, p.last_name AS player_last_name,
                rp.first_name AS related_first_name, rp.last_name AS related_last_name
         FROM match_events me
         INNER JOIN clubs c ON c.id = me.club_id
         LEFT JOIN players p ON p.id = me.player_id
         LEFT JOIN players rp ON rp.id = me.related_player_id
         WHERE me.fixture_id = :fixture_id
         ORDER BY me.minute ASC, me.extra_minute ASC, me.id ASC',
        ['fixture_id' => $fixtureId]
    );
}

function get_match_event_by_id(int $eventId): ?array
{
    return db_fetch_one(
        'SELECT me.*, f.competition_id, f.season_id, f.home_club_id, f.away_club_id,
                mr.result_status
         FROM match_events me
         INNER JOIN fixtures f ON f.id = me.fixture_id
         LEFT JOIN match_results mr ON mr.fixture_id = f.id
         WHERE me.id = :id
         LIMIT 1',
        ['id' => $eventId]
    );
}

function find_match_event_or_abort(int $eventId): array
{
    $event = get_match_event_by_id($eventId);

    if ($event === null) {
        abort_request(404, 'Match event not found', 'The requested match event record does not exist.');
    }

    return $event;
}

function event_players_for_fixture(array $fixture): array
{
    return db_fetch_all(
        'SELECT p.id, p.club_id, p.first_name, p.last_name, p.position, c.name AS club_name
         FROM players p
         INNER JOIN clubs c ON c.id = p.club_id
         WHERE p.status = "active"
           AND p.eligibility_status IN ("eligible", "suspended")
           AND p.club_id IN (:home_club_id, :away_club_id)
         ORDER BY c.name ASC, p.first_name ASC, p.last_name ASC',
        [
            'home_club_id' => (int) $fixture['home_club_id'],
            'away_club_id' => (int) $fixture['away_club_id'],
        ]
    );
}

function validate_match_event_payload(array $data, array &$errors): array
{
    validate_required($data, [
        'fixture_id' => 'Fixture',
        'club_id' => 'Club',
        'event_type' => 'Event type',
        'minute' => 'Minute',
    ], $errors);
    validate_numeric_min($data, 'minute', 'Minute', 0, $errors);
    validate_numeric_min($data, 'extra_minute', 'Additional minute', 0, $errors);
    validate_in_array_field($data, 'event_type', match_event_type_options(), 'Event type', $errors);

    $normalized = [
        'fixture_id' => (int) ($data['fixture_id'] ?? 0),
        'club_id' => (int) ($data['club_id'] ?? 0),
        'player_id' => ($data['player_id'] ?? '') === '' ? null : (int) $data['player_id'],
        'event_type' => (string) ($data['event_type'] ?? ''),
        'minute' => (int) (($data['minute'] ?? '') === '' ? 0 : $data['minute']),
        'extra_minute' => ($data['extra_minute'] ?? '') === '' ? null : (int) $data['extra_minute'],
        'related_player_id' => ($data['related_player_id'] ?? '') === '' ? null : (int) $data['related_player_id'],
        'notes' => trim((string) ($data['notes'] ?? '')),
    ];

    $fixture = get_match_fixture_by_id($normalized['fixture_id']);
    if ($fixture === null) {
        $errors['fixture_id'] = 'Selected fixture is invalid.';
        return $normalized;
    }

    if (($fixture['result_id'] ?? null) === null) {
        $errors['fixture_id'] = 'Submit the match result before recording match events.';
    }

    $allowedClubIds = [(int) $fixture['home_club_id'], (int) $fixture['away_club_id']];
    if (!in_array($normalized['club_id'], $allowedClubIds, true)) {
        $errors['club_id'] = 'Selected club is not part of this fixture.';
    }

    $players = event_players_for_fixture($fixture);
    $playerMap = [];
    foreach ($players as $player) {
        $playerMap[(int) $player['id']] = (int) $player['club_id'];
    }

    $needsPrimaryPlayer = in_array($normalized['event_type'], ['goal', 'assist', 'yellow_card', 'red_card', 'substitution', 'own_goal', 'penalty_goal', 'penalty_miss'], true);
    if ($needsPrimaryPlayer && $normalized['player_id'] === null) {
        $errors['player_id'] = 'A player is required for the selected event type.';
    }

    if ($normalized['player_id'] !== null) {
        $playerClubId = $playerMap[$normalized['player_id']] ?? null;
        if ($playerClubId === null || $playerClubId !== $normalized['club_id']) {
            $errors['player_id'] = 'Selected player is invalid for the chosen club.';
        }
    }

    if ($normalized['related_player_id'] !== null) {
        $relatedClubId = $playerMap[$normalized['related_player_id']] ?? null;
        if ($relatedClubId === null || $relatedClubId !== $normalized['club_id']) {
            $errors['related_player_id'] = 'Related player is invalid for the chosen club.';
        }
    }

    if ($normalized['event_type'] === 'substitution' && $normalized['related_player_id'] === null) {
        $errors['related_player_id'] = 'A second player is required for substitution events.';
    }

    if ($normalized['player_id'] !== null && $normalized['player_id'] === $normalized['related_player_id']) {
        $errors['related_player_id'] = 'Related player must be different from the main player.';
    }

    return $normalized;
}

function rebuild_player_statistics_for_competition(int $competitionId, int $seasonId): int
{
    $events = db_fetch_all(
        'SELECT me.*, f.competition_id, f.season_id, f.home_club_id, f.away_club_id,
                mr.home_score, mr.away_score, mr.result_status,
                pp.club_id AS player_club_id,
                rp.club_id AS related_club_id
         FROM match_events me
         INNER JOIN fixtures f ON f.id = me.fixture_id
         INNER JOIN match_results mr ON mr.fixture_id = f.id
         LEFT JOIN players pp ON pp.id = me.player_id
         LEFT JOIN players rp ON rp.id = me.related_player_id
         WHERE f.competition_id = :competition_id
           AND mr.result_status = "verified"
         ORDER BY me.fixture_id ASC, me.id ASC',
        ['competition_id' => $competitionId]
    );

    $stats = [];
    $appearanceFixtures = [];

    foreach ($events as $event) {
        $clubId = (int) $event['club_id'];
        $playerId = $event['player_id'] !== null ? (int) $event['player_id'] : null;
        $relatedPlayerId = $event['related_player_id'] !== null ? (int) $event['related_player_id'] : null;
        $fixtureId = (int) $event['fixture_id'];

        $ensureRow = static function (int $playerId, int $clubId) use (&$stats, $competitionId, $seasonId): void {
            $key = $playerId . ':' . $clubId;
            if (!isset($stats[$key])) {
                $stats[$key] = [
                    'season_id' => $seasonId,
                    'competition_id' => $competitionId,
                    'player_id' => $playerId,
                    'club_id' => $clubId,
                    'appearances' => 0,
                    'goals' => 0,
                    'assists' => 0,
                    'yellow_cards' => 0,
                    'red_cards' => 0,
                    'clean_sheets' => 0,
                    'own_goals' => 0,
                ];
            }
        };

        if ($playerId !== null) {
            $playerClubId = (int) ($event['player_club_id'] ?? $clubId);
            $ensureRow($playerId, $playerClubId);
            $appearanceFixtures[$playerId . ':' . $playerClubId . ':' . $fixtureId] = [$playerId, $playerClubId];

            $key = $playerId . ':' . $playerClubId;
            switch ((string) $event['event_type']) {
                case 'goal':
                case 'penalty_goal':
                    $stats[$key]['goals']++;
                    break;
                case 'assist':
                    $stats[$key]['assists']++;
                    break;
                case 'yellow_card':
                    $stats[$key]['yellow_cards']++;
                    break;
                case 'red_card':
                    $stats[$key]['red_cards']++;
                    break;
                case 'own_goal':
                    $stats[$key]['own_goals']++;
                    break;
            }
        }

        if ($relatedPlayerId !== null) {
            $relatedClubId = (int) ($event['related_club_id'] ?? $clubId);
            $ensureRow($relatedPlayerId, $relatedClubId);
            $appearanceFixtures[$relatedPlayerId . ':' . $relatedClubId . ':' . $fixtureId] = [$relatedPlayerId, $relatedClubId];
        }
    }

    foreach ($appearanceFixtures as [$playerId, $clubId]) {
        $key = $playerId . ':' . $clubId;
        if (isset($stats[$key])) {
            $stats[$key]['appearances']++;
        }
    }

    $pdo = db();
    $pdo->beginTransaction();

    try {
        db_query('DELETE FROM player_statistics WHERE competition_id = :competition_id', ['competition_id' => $competitionId]);

        foreach ($stats as $row) {
            db_query(
                'INSERT INTO player_statistics (
                    season_id, competition_id, player_id, club_id, appearances, goals, assists,
                    yellow_cards, red_cards, clean_sheets, own_goals, updated_at
                 ) VALUES (
                    :season_id, :competition_id, :player_id, :club_id, :appearances, :goals, :assists,
                    :yellow_cards, :red_cards, :clean_sheets, :own_goals, NOW()
                 )',
                $row
            );
        }

        $pdo->commit();
    } catch (Throwable $throwable) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $throwable;
    }

    return count($stats);
}

function sync_discipline_records_for_competition(int $competitionId, int $seasonId): void
{
    $yellowThreshold = max(1, (int) site_setting('yellow_card_suspension_threshold', 5));
    $redCardSuspensionDefault = max(1, (int) site_setting('red_card_suspension_default', 1));

    $pdo = db();
    $pdo->beginTransaction();

    try {
        db_query(
            'DELETE s FROM suspensions s
             INNER JOIN fixtures f ON f.id = s.start_fixture_id
             WHERE f.competition_id = :competition_id',
            ['competition_id' => $competitionId]
        );

        db_query(
            'DELETE dc FROM disciplinary_cases dc
             INNER JOIN fixtures f ON f.id = dc.fixture_id
             WHERE f.competition_id = :competition_id',
            ['competition_id' => $competitionId]
        );

        $redCards = db_fetch_all(
            'SELECT me.*, f.id AS fixture_id
             FROM match_events me
             INNER JOIN fixtures f ON f.id = me.fixture_id
             INNER JOIN match_results mr ON mr.fixture_id = f.id
             WHERE f.competition_id = :competition_id
               AND me.event_type = "red_card"
               AND mr.result_status = "verified"',
            ['competition_id' => $competitionId]
        );

        foreach ($redCards as $event) {
            db_query(
                'INSERT INTO suspensions (
                    player_id, club_id, reason, start_fixture_id, number_of_matches,
                    matches_served, status, created_at
                 ) VALUES (
                    :player_id, :club_id, :reason, :start_fixture_id, :number_of_matches,
                    0, "active", NOW()
                 )',
                [
                    'player_id' => (int) $event['player_id'],
                    'club_id' => (int) $event['club_id'],
                    'reason' => 'Automatic suspension for verified red card',
                    'start_fixture_id' => (int) $event['fixture_id'],
                    'number_of_matches' => $redCardSuspensionDefault,
                ]
            );

            db_query(
                'INSERT INTO disciplinary_cases (
                    fixture_id, club_id, player_id, case_type, description, decision,
                    fine_amount, status, created_at
                 ) VALUES (
                    :fixture_id, :club_id, :player_id, :case_type, :description, NULL,
                    0.00, "pending", NOW()
                 )',
                [
                    'fixture_id' => (int) $event['fixture_id'],
                    'club_id' => (int) $event['club_id'],
                    'player_id' => (int) $event['player_id'],
                    'case_type' => 'red_card',
                    'description' => 'Automatic disciplinary review created from a verified red card event.',
                ]
            );
        }

        $yellowRows = db_fetch_all(
            'SELECT me.player_id, me.club_id, me.fixture_id,
                    ROW_NUMBER() OVER (PARTITION BY me.player_id ORDER BY COALESCE(f.match_date, "9999-12-31") ASC, COALESCE(f.match_time, "23:59:59") ASC, me.id ASC) AS yellow_sequence
             FROM match_events me
             INNER JOIN fixtures f ON f.id = me.fixture_id
             INNER JOIN match_results mr ON mr.fixture_id = f.id
             WHERE f.competition_id = :competition_id
               AND me.event_type = "yellow_card"
               AND mr.result_status = "verified"',
            ['competition_id' => $competitionId]
        );

        foreach ($yellowRows as $row) {
            $sequence = (int) $row['yellow_sequence'];
            if ($sequence % $yellowThreshold !== 0) {
                continue;
            }

            db_query(
                'INSERT INTO suspensions (
                    player_id, club_id, reason, start_fixture_id, number_of_matches,
                    matches_served, status, created_at
                 ) VALUES (
                    :player_id, :club_id, :reason, :start_fixture_id, 1,
                    0, "active", NOW()
                 )',
                [
                    'player_id' => (int) $row['player_id'],
                    'club_id' => (int) $row['club_id'],
                    'reason' => 'Automatic suspension for accumulated yellow cards',
                    'start_fixture_id' => (int) $row['fixture_id'],
                ]
            );
        }

        $pdo->commit();
    } catch (Throwable $throwable) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $throwable;
    }
}

function refresh_verified_fixture_side_effects(array $fixture): void
{
    if (($fixture['result_status'] ?? '') !== 'verified') {
        return;
    }

    rebuild_player_statistics_for_competition((int) $fixture['competition_id'], (int) $fixture['season_id']);
    sync_discipline_records_for_competition((int) $fixture['competition_id'], (int) $fixture['season_id']);

    if (in_array((string) ($fixture['competition_type'] ?? ''), ['knockout', 'group_stage', 'mixed'], true)) {
        fixture_progress_competition_stage((int) $fixture['competition_id']);
    }
}

function match_event_badge_class(string $eventType): string
{
    return match ($eventType) {
        'goal', 'penalty_goal' => 'badge-soft-success',
        'assist', 'substitution' => 'bg-info-subtle text-info-emphasis',
        'yellow_card' => 'bg-warning-subtle text-warning-emphasis',
        'red_card', 'own_goal', 'penalty_miss' => 'badge-soft-danger',
        default => 'text-bg-light',
    };
}

function find_match_fixture_or_abort(int $fixtureId): array
{
    $fixture = get_match_fixture_by_id($fixtureId);

    if ($fixture === null) {
        abort_request(404, 'Fixture not found', 'The requested match fixture does not exist.');
    }

    return $fixture;
}

function validate_match_result_payload(array $data, array &$errors): array
{
    validate_required($data, [
        'fixture_id' => 'Fixture',
        'home_score' => 'Home score',
        'away_score' => 'Away score',
    ], $errors);
    validate_numeric_min($data, 'home_score', 'Home score', 0, $errors);
    validate_numeric_min($data, 'away_score', 'Away score', 0, $errors);

    $normalized = [
        'fixture_id' => (int) ($data['fixture_id'] ?? 0),
        'home_score' => (int) (($data['home_score'] ?? '') === '' ? 0 : $data['home_score']),
        'away_score' => (int) (($data['away_score'] ?? '') === '' ? 0 : $data['away_score']),
    ];

    $fixture = get_match_fixture_by_id($normalized['fixture_id']);

    if ($fixture === null) {
        $errors['fixture_id'] = 'Selected fixture is invalid.';
        return $normalized;
    }

    if (($fixture['status'] ?? '') === 'cancelled') {
        $errors['fixture_id'] = 'Results cannot be submitted for cancelled fixtures.';
    }

    if (($fixture['result_status'] ?? '') === 'verified') {
        $errors['fixture_id'] = 'Verified results cannot be overwritten from the submission workflow.';
    }

    if (($fixture['stage_type'] ?? '') === 'knockout' && $normalized['home_score'] === $normalized['away_score']) {
        $errors['away_score'] = 'Knockout fixtures must produce a winner. Record a winning score before verification.';
    }

    return $normalized;
}

function upsert_match_result(array $fixture, array $payload, int $userId): int
{
    $existingResultId = isset($fixture['result_id']) ? (int) $fixture['result_id'] : 0;

    if ($existingResultId > 0) {
        db_query(
            'UPDATE match_results
             SET home_score = :home_score,
                 away_score = :away_score,
                 result_status = "submitted",
                 submitted_by = :submitted_by,
                 verified_by = NULL,
                 submitted_at = NOW(),
                 verified_at = NULL,
                 updated_at = NOW()
             WHERE fixture_id = :fixture_id',
            [
                'home_score' => $payload['home_score'],
                'away_score' => $payload['away_score'],
                'submitted_by' => $userId,
                'fixture_id' => $payload['fixture_id'],
            ]
        );

        return $existingResultId;
    }

    db_query(
        'INSERT INTO match_results (
            fixture_id, home_score, away_score, result_status,
            submitted_by, verified_by, submitted_at, verified_at, created_at, updated_at
         ) VALUES (
            :fixture_id, :home_score, :away_score, "submitted",
            :submitted_by, NULL, NOW(), NULL, NOW(), NOW()
         )',
        [
            'fixture_id' => $payload['fixture_id'],
            'home_score' => $payload['home_score'],
            'away_score' => $payload['away_score'],
            'submitted_by' => $userId,
        ]
    );

    return (int) db()->lastInsertId();
}

function verify_match_result(array $fixture, int $userId): void
{
    if (!isset($fixture['result_id']) || (int) $fixture['result_id'] <= 0) {
        throw new RuntimeException('A submitted result is required before verification.');
    }

    db_query(
        'UPDATE match_results
         SET result_status = "verified",
             verified_by = :verified_by,
             verified_at = NOW(),
             updated_at = NOW()
         WHERE fixture_id = :fixture_id',
        [
            'verified_by' => $userId,
            'fixture_id' => (int) $fixture['id'],
        ]
    );

    db_query(
        'UPDATE fixtures
         SET status = "completed", updated_at = NOW()
         WHERE id = :id',
        ['id' => (int) $fixture['id']]
    );

    if (($fixture['competition_type'] ?? '') === 'league') {
        recalculate_league_table((int) $fixture['competition_id']);
    }
}
