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

function fixture_module_boot(): void
{
    require_login();
    require_permission('manage_fixtures');
}

function can_generate_fixtures(): bool
{
    return can('generate_fixtures');
}

function can_manage_match_preparation(): bool
{
    return can('manage_match_preparation');
}

function can_assign_fixture_referees(): bool
{
    return can('assign_referees') || can('manage_referees');
}

function can_confirm_fixture_kits(): bool
{
    return !is_super_admin() && (can_manage_match_preparation() || can('manage_fixtures') || can_assign_fixture_referees() || can('verify_results'));
}

function fixture_preparation_boot(): void
{
    require_login();

    if (!can_manage_match_preparation() && !can_confirm_fixture_kits() && !is_super_admin()) {
        abort_request(403, 'Access denied', 'Your account does not have permission to access match preparation.');
    }

    if (is_super_admin()) {
        return;
    }

    if (user_club_id() === null && !can_confirm_fixture_kits()) {
        abort_request(403, 'Club assignment required', 'Match preparation access requires a user account linked to a club.');
    }
}

function can_edit_fixture_preparation(): bool
{
    return !is_super_admin() && user_club_id() !== null;
}

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

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

    try {
        $tableReady = (int) db_fetch_value(
            'SELECT COUNT(*)
             FROM information_schema.tables
             WHERE table_schema = DATABASE()
               AND table_name = "fixture_kits"',
            [],
            0
        ) === 1;

        $categoryType = (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',
            [],
            ''
        );

        $fixtureIdColumn = (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
        ) === 1;

        $ready = $tableReady && $fixtureIdColumn && str_contains($categoryType, 'fixture_kit_confirmation');
    } catch (Throwable) {
        $ready = false;
    }

    return $ready;
}

function fixture_kit_color_fields(): array
{
    return [
        'shirt_color' => 'Shirt color',
        'shorts_color' => 'Shorts color',
        'socks_color' => 'Socks color',
        'goalkeeper_shirt_color' => 'Goalkeeper shirt color',
        'goalkeeper_shorts_color' => 'Goalkeeper shorts color',
    ];
}

function validate_fixture_kit_payload(array $data, array &$errors): array
{
    validate_required($data, fixture_kit_color_fields(), $errors);

    $normalized = [];
    foreach (array_keys(fixture_kit_color_fields()) as $field) {
        $normalized[$field] = trim((string) ($data[$field] ?? ''));

        if ($normalized[$field] !== '' && mb_strlen($normalized[$field]) > 60) {
            $errors[$field] = fixture_kit_color_fields()[$field] . ' may not be longer than 60 characters.';
        }
    }

    return $normalized;
}

function fixture_kit_record_for_club(int $fixtureId, int $clubId): ?array
{
    if (!fixture_kit_schema_ready()) {
        return null;
    }

    return db_fetch_one(
        'SELECT fk.*, submitter.full_name AS submitted_by_name, confirmer.full_name AS confirmed_by_name
         FROM fixture_kits fk
         LEFT JOIN users submitter ON submitter.id = fk.submitted_by
         LEFT JOIN users confirmer ON confirmer.id = fk.confirmed_by
         WHERE fk.fixture_id = :fixture_id AND fk.club_id = :club_id
         LIMIT 1',
        ['fixture_id' => $fixtureId, 'club_id' => $clubId]
    );
}

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

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

function fixture_mark_kit_confirmation_news_pending(int $fixtureId): void
{
    if (!fixture_kit_schema_ready()) {
        return;
    }

    $existing = fixture_kit_news_post($fixtureId);
    if ($existing === null) {
        return;
    }

    db_query(
        'UPDATE news_posts
         SET status = "draft",
             published_at = NULL
         WHERE id = :id',
        ['id' => (int) $existing['id']]
    );
}

function save_fixture_kit(int $fixtureId, int $clubId, int $submittedBy, array $payload): void
{
    if (!fixture_kit_schema_ready()) {
        throw new RuntimeException('Fixture kit workflow is not available yet.');
    }

    db_query(
        'INSERT INTO fixture_kits (
            fixture_id, club_id, shirt_color, shorts_color, socks_color,
            goalkeeper_shirt_color, goalkeeper_shorts_color,
            submitted_by, submitted_at, confirmed_by, confirmed_at, created_at, updated_at
         ) VALUES (
            :fixture_id, :club_id, :shirt_color, :shorts_color, :socks_color,
            :goalkeeper_shirt_color, :goalkeeper_shorts_color,
            :submitted_by, NOW(), NULL, NULL, NOW(), NOW()
         )
         ON DUPLICATE KEY UPDATE
            shirt_color = VALUES(shirt_color),
            shorts_color = VALUES(shorts_color),
            socks_color = VALUES(socks_color),
            goalkeeper_shirt_color = VALUES(goalkeeper_shirt_color),
            goalkeeper_shorts_color = VALUES(goalkeeper_shorts_color),
            submitted_by = VALUES(submitted_by),
            submitted_at = NOW(),
            confirmed_by = NULL,
            confirmed_at = NULL,
            updated_at = NOW()',
        [
            'fixture_id' => $fixtureId,
            'club_id' => $clubId,
            'shirt_color' => $payload['shirt_color'],
            'shorts_color' => $payload['shorts_color'],
            'socks_color' => $payload['socks_color'],
            'goalkeeper_shirt_color' => $payload['goalkeeper_shirt_color'],
            'goalkeeper_shorts_color' => $payload['goalkeeper_shorts_color'],
            'submitted_by' => $submittedBy,
        ]
    );

    fixture_mark_kit_confirmation_news_pending($fixtureId);
}

function confirm_fixture_kit(int $fixtureId, int $clubId, int $confirmedBy): void
{
    if (!fixture_kit_schema_ready()) {
        throw new RuntimeException('Fixture kit workflow is not available yet.');
    }

    db_query(
        'UPDATE fixture_kits
         SET confirmed_by = :confirmed_by,
             confirmed_at = NOW(),
             updated_at = NOW()
         WHERE fixture_id = :fixture_id
           AND club_id = :club_id',
        [
            'confirmed_by' => $confirmedBy,
            'fixture_id' => $fixtureId,
            'club_id' => $clubId,
        ]
    );
}

function fixture_kit_summary_line(array $kit): string
{
    return sprintf(
        'Shirt: %s, Shorts: %s, Socks: %s, Goalkeeper: %s / %s',
        $kit['shirt_color'] ?? 'TBC',
        $kit['shorts_color'] ?? 'TBC',
        $kit['socks_color'] ?? 'TBC',
        $kit['goalkeeper_shirt_color'] ?? 'TBC',
        $kit['goalkeeper_shorts_color'] ?? 'TBC'
    );
}

function upsert_fixture_kit_confirmation_news(array $fixture): ?int
{
    if (!fixture_kit_schema_ready()) {
        return null;
    }

    $homeKit = fixture_kit_record_for_club((int) $fixture['id'], (int) $fixture['home_club_id']);
    $awayKit = fixture_kit_record_for_club((int) $fixture['id'], (int) $fixture['away_club_id']);

    if ($homeKit === null || $awayKit === null || empty($homeKit['confirmed_at']) || empty($awayKit['confirmed_at'])) {
        return null;
    }

    $headline = sprintf('Matchday kits confirmed: %s vs %s', $fixture['home_club_name'], $fixture['away_club_name']);
    $body = implode("\n\n", [
        sprintf(
            'Matchday colors have been confirmed for %s vs %s in %s. The fixture is scheduled for %s at %s.',
            $fixture['home_club_name'],
            $fixture['away_club_name'],
            $fixture['competition_name'],
            $fixture['match_date'] ?: 'Date TBC',
            $fixture['match_time'] ?: 'Time TBC'
        ),
        sprintf('%s: %s.', $fixture['home_club_name'], fixture_kit_summary_line($homeKit)),
        sprintf('%s: %s.', $fixture['away_club_name'], fixture_kit_summary_line($awayKit)),
        'Both sets have now been confirmed by the match official for this fixture.',
    ]);

    $existing = fixture_kit_news_post((int) $fixture['id']);
    if ($existing !== null) {
        db_query(
            'UPDATE news_posts
             SET title = :title,
                 slug = :slug,
                 category = :category,
                 fixture_id = :fixture_id,
                 body = :body,
                 status = "published",
                 published_at = NOW()
             WHERE id = :id',
            [
                'title' => $headline,
                'slug' => fixture_unique_news_slug($headline, (int) $existing['id']),
                'category' => 'fixture_kit_confirmation',
                'fixture_id' => (int) $fixture['id'],
                'body' => $body,
                'id' => (int) $existing['id'],
            ]
        );

        web_push_send_news_post((int) $existing['id']);

        return (int) $existing['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, "published", :created_by, NOW(), NOW()
         )',
        [
            'title' => $headline,
            'slug' => fixture_unique_news_slug($headline),
            'category' => 'fixture_kit_confirmation',
            'fixture_id' => (int) $fixture['id'],
            'body' => $body,
            'created_by' => (int) ($_SESSION['user_id'] ?? 0) ?: null,
        ]
    );

    $postId = (int) db()->lastInsertId();
    web_push_send_news_post($postId);

    return $postId;
}

function fixture_status_options(): array
{
    return ['scheduled', 'postponed', 'live', 'completed', 'cancelled'];
}

function fixture_competition_options(bool $generationOnly = false): array
{
    $sql = 'SELECT c.id, c.name, c.type, c.status, c.number_of_teams, c.season_id, s.name AS season_name
            FROM competitions c
            INNER JOIN seasons s ON s.id = c.season_id';

    if ($generationOnly) {
        $sql .= ' WHERE c.type IN ("league", "knockout", "group_stage", "mixed") AND c.status IN ("draft", "open", "active")';
    }

    $sql .= ' ORDER BY FIELD(c.status, "active", "paused", "open", "draft", "completed", "cancelled"), s.start_date DESC, c.name ASC';

    return db_fetch_all($sql);
}

function fixture_venue_options(): array
{
    return db_fetch_all('SELECT id, name, location, status FROM venues WHERE status = "active" ORDER BY name ASC');
}

function fixture_referee_options(): array
{
    return db_fetch_all('SELECT id, full_name, level, status FROM referees WHERE status = "active" ORDER BY full_name ASC');
}

function fixture_match_commissioner_options(): array
{
    return db_fetch_all(
        'SELECT u.id, u.full_name
         FROM users u
         INNER JOIN roles r ON r.id = u.role_id
         WHERE u.status = "active"
           AND r.name = "Match Commissioner"
         ORDER BY u.full_name ASC'
    );
}

function fixture_assignment_slot_key(?string $matchDate, ?string $matchTime): string
{
    return trim((string) $matchDate) . '|' . trim((string) $matchTime);
}

function fixture_auto_assignment_state(): array
{
    return [
        'referees' => fixture_referee_options(),
        'commissioners' => fixture_match_commissioner_options(),
        'referee_cursor' => 0,
        'commissioner_cursor' => 0,
        'slots' => [],
    ];
}

function fixture_auto_assignment_slot(?string $matchDate, ?string $matchTime, array &$state): array
{
    $slotKey = fixture_assignment_slot_key($matchDate, $matchTime);
    if (isset($state['slots'][$slotKey])) {
        return $state['slots'][$slotKey];
    }

    $assignedRefereeIds = [];
    $assignedCommissioners = [];

    if ($matchDate !== null && trim($matchDate) !== '' && $matchTime !== null && trim($matchTime) !== '') {
        $assignedRefereeIds = array_map(
            'intval',
            array_filter(
                array_map(
                    static fn(array $row): mixed => $row['referee_id'] ?? null,
                    db_fetch_all(
                        'SELECT referee_id
                         FROM fixtures
                         WHERE match_date = :match_date
                           AND match_time = :match_time
                           AND referee_id IS NOT NULL',
                        ['match_date' => $matchDate, 'match_time' => $matchTime]
                    )
                ),
                static fn(mixed $value): bool => $value !== null
            )
        );

        $assignedCommissioners = array_values(array_filter(
            array_map(
                static fn(array $row): string => trim((string) ($row['match_commissioner'] ?? '')),
                db_fetch_all(
                    'SELECT match_commissioner
                     FROM fixtures
                     WHERE match_date = :match_date
                       AND match_time = :match_time
                       AND match_commissioner IS NOT NULL
                       AND match_commissioner <> ""',
                    ['match_date' => $matchDate, 'match_time' => $matchTime]
                )
            ),
            static fn(string $value): bool => $value !== ''
        ));
    }

    $state['slots'][$slotKey] = [
        'assigned_referee_ids' => $assignedRefereeIds,
        'assigned_commissioners' => $assignedCommissioners,
    ];

    return $state['slots'][$slotKey];
}

function fixture_next_auto_referee_id(?string $matchDate, ?string $matchTime, array &$state): ?int
{
    $referees = $state['referees'] ?? [];
    if ($referees === []) {
        return null;
    }

    $slotKey = fixture_assignment_slot_key($matchDate, $matchTime);
    fixture_auto_assignment_slot($matchDate, $matchTime, $state);

    $poolSize = count($referees);
    for ($attempt = 0; $attempt < $poolSize; $attempt++) {
        $index = ((int) $state['referee_cursor'] + $attempt) % $poolSize;
        $refereeId = (int) ($referees[$index]['id'] ?? 0);
        if ($refereeId <= 0) {
            continue;
        }

        if (!in_array($refereeId, $state['slots'][$slotKey]['assigned_referee_ids'], true)) {
            $state['referee_cursor'] = $index + 1;
            $state['slots'][$slotKey]['assigned_referee_ids'][] = $refereeId;
            return $refereeId;
        }
    }

    return null;
}

function fixture_next_auto_match_commissioner(?string $matchDate, ?string $matchTime, array &$state): string
{
    $commissioners = $state['commissioners'] ?? [];
    if ($commissioners === []) {
        return '';
    }

    $slotKey = fixture_assignment_slot_key($matchDate, $matchTime);
    fixture_auto_assignment_slot($matchDate, $matchTime, $state);

    $poolSize = count($commissioners);
    for ($attempt = 0; $attempt < $poolSize; $attempt++) {
        $index = ((int) $state['commissioner_cursor'] + $attempt) % $poolSize;
        $commissionerName = trim((string) ($commissioners[$index]['full_name'] ?? ''));
        if ($commissionerName === '') {
            continue;
        }

        if (!in_array($commissionerName, $state['slots'][$slotKey]['assigned_commissioners'], true)) {
            $state['commissioner_cursor'] = $index + 1;
            $state['slots'][$slotKey]['assigned_commissioners'][] = $commissionerName;
            return $commissionerName;
        }
    }

    return '';
}

function fixture_apply_auto_official_assignments(array $payload, ?array &$state = null): array
{
    $assignmentState = $state ?? fixture_auto_assignment_state();

    if (($payload['referee_id'] ?? null) === null) {
        $payload['referee_id'] = fixture_next_auto_referee_id($payload['match_date'] ?? null, $payload['match_time'] ?? null, $assignmentState);
    }

    if (trim((string) ($payload['match_commissioner'] ?? '')) === '') {
        $payload['match_commissioner'] = fixture_next_auto_match_commissioner($payload['match_date'] ?? null, $payload['match_time'] ?? null, $assignmentState);
    }

    $state = $assignmentState;

    return $payload;
}

function fixture_list(): array
{
    return db_fetch_all(
        'SELECT f.*, c.name AS competition_name, c.status AS competition_status, s.name AS season_name,
                s.status AS season_status,
                hc.name AS home_club_name, hc.short_name AS home_club_short_name,
                ac.name AS away_club_name, ac.short_name AS away_club_short_name,
                v.name AS venue_name, r.full_name AS referee_name,
                mr.home_score, mr.away_score, mr.result_status
         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 venues v ON v.id = f.venue_id
         LEFT JOIN referees r ON r.id = f.referee_id
         LEFT JOIN match_results mr ON mr.fixture_id = f.id
         ORDER BY COALESCE(f.match_date, "9999-12-31") ASC, f.match_time ASC, f.matchday ASC, f.id DESC'
    );
}

function get_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, s.status AS season_status,
                hc.name AS home_club_name, hc.short_name AS home_club_short_name,
                ac.name AS away_club_name, ac.short_name AS away_club_short_name,
                v.name AS venue_name, v.location AS venue_location,
                r.full_name AS referee_name, r.level AS referee_level,
                mr.id AS result_id, mr.home_score, mr.away_score, mr.result_status
         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 venues v ON v.id = f.venue_id
         LEFT JOIN referees r ON r.id = f.referee_id
         LEFT JOIN match_results mr ON mr.fixture_id = f.id
         WHERE f.id = :id
         LIMIT 1',
        ['id' => $fixtureId]
    );
}

function find_fixture_or_abort(int $fixtureId): array
{
    $fixture = get_fixture_by_id($fixtureId);

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

    return $fixture;
}

function fixture_preparation_window_state(?string $matchDate, ?string $matchTime, string $fixtureStatus = 'scheduled', ?string $seasonStatus = null, ?string $competitionStatus = null, ?int $referenceTimestamp = null): array
{
    $state = [
        'available' => false,
        'seconds_to_kickoff' => null,
        'window_opens_in' => null,
        'message' => 'Formation window opens two hours before kickoff.',
    ];

    if ($fixtureStatus !== 'scheduled') {
        $state['message'] = 'Formation can only be submitted for scheduled fixtures.';
        return $state;
    }

    if ($seasonStatus !== null && $seasonStatus !== 'active') {
        $state['message'] = 'The season is not active right now.';
        return $state;
    }

    if ($competitionStatus !== null && $competitionStatus !== 'active') {
        $state['message'] = 'The competition is not active right now.';
        return $state;
    }

    if (trim((string) $matchDate) === '' || trim((string) $matchTime) === '') {
        $state['message'] = 'Match date and kickoff time must be scheduled before formation can open.';
        return $state;
    }

    $kickoffTimestamp = strtotime(trim((string) $matchDate . ' ' . (string) $matchTime));
    if ($kickoffTimestamp === false) {
        $state['message'] = 'Kickoff time is invalid.';
        return $state;
    }

    $referenceTimestamp ??= time();
    $secondsToKickoff = $kickoffTimestamp - $referenceTimestamp;
    $windowOpensIn = $secondsToKickoff - 7200;

    $state['seconds_to_kickoff'] = $secondsToKickoff;
    $state['window_opens_in'] = $windowOpensIn;

    if ($secondsToKickoff < 0) {
        $state['message'] = 'Kickoff time has passed for this fixture.';
        return $state;
    }

    if ($secondsToKickoff > 7200) {
        $state['message'] = 'Formation window opens two hours before kickoff.';
        return $state;
    }

    $state['available'] = true;
    $state['message'] = 'Formation window is live until kickoff.';

    return $state;
}

function fixture_preparation_list_for_club(int $clubId, int $limit = 12): array
{
    $fixtures = db_fetch_all(
        'SELECT f.id, f.matchday, f.match_date, f.match_time, f.status, f.group_label, f.knockout_round, f.stage_type,
                c.name AS competition_name, c.status AS competition_status,
                s.name AS season_name, s.status AS season_status,
                hc.id AS home_club_id, hc.name AS home_club_name, hc.short_name AS home_club_short_name, hc.logo AS home_club_logo,
                ac.id AS away_club_id, ac.name AS away_club_name, ac.short_name AS away_club_short_name, ac.logo AS away_club_logo,
                ff.formation, ff.tactical_notes, ff.submitted_at,
                fk.shirt_color, fk.shorts_color, fk.socks_color,
                fk.goalkeeper_shirt_color, fk.goalkeeper_shorts_color,
                fk.confirmed_at AS kit_confirmed_at
         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 fixture_formations ff ON ff.fixture_id = f.id AND ff.club_id = :club_id
         LEFT JOIN fixture_kits fk ON fk.fixture_id = f.id AND fk.club_id = :club_id
         WHERE (f.home_club_id = :home_club_scope OR f.away_club_id = :away_club_scope)
         ORDER BY COALESCE(f.match_date, "9999-12-31") ASC, COALESCE(f.match_time, "23:59:59") ASC, f.id ASC
         LIMIT ' . (int) $limit,
        ['club_id' => $clubId, 'home_club_scope' => $clubId, 'away_club_scope' => $clubId]
    );

    foreach ($fixtures as $index => $fixture) {
        $fixtures[$index]['window_state'] = fixture_preparation_window_state(
            $fixture['match_date'] ?? null,
            $fixture['match_time'] ?? null,
            (string) ($fixture['status'] ?? 'scheduled'),
            $fixture['season_status'] ?? null,
            $fixture['competition_status'] ?? null
        );
        $fixtures[$index]['is_home_club'] = (int) $fixture['home_club_id'] === $clubId;
    }

    return $fixtures;
}

function fixture_preparation_notifications(int $clubId, int $limit = 4): array
{
    $notifications = [];

    foreach (fixture_preparation_list_for_club($clubId, 20) as $fixture) {
        $windowState = $fixture['window_state'] ?? [];
        $secondsToKickoff = $windowState['seconds_to_kickoff'] ?? null;
        $windowOpensIn = $windowState['window_opens_in'] ?? null;

        $shouldInclude = ($windowState['available'] ?? false)
            || ($windowOpensIn !== null && $windowOpensIn >= 0 && $windowOpensIn <= 86400);

        if (!$shouldInclude) {
            continue;
        }

        $notifications[] = $fixture;
        if (count($notifications) >= $limit) {
            break;
        }
    }

    return $notifications;
}

function get_fixture_preparation_by_id(int $fixtureId, int $clubId): ?array
{
    $fixture = db_fetch_one(
        'SELECT f.id, f.matchday, f.match_date, f.match_time, f.status, f.group_label, f.knockout_round, f.stage_type,
                c.name AS competition_name, c.status AS competition_status,
                s.name AS season_name, s.status AS season_status,
                hc.id AS home_club_id, hc.name AS home_club_name, hc.short_name AS home_club_short_name, hc.logo AS home_club_logo,
                ac.id AS away_club_id, ac.name AS away_club_name, ac.short_name AS away_club_short_name, ac.logo AS away_club_logo,
                v.name AS venue_name,
                ff.id AS formation_id, ff.formation, ff.tactical_notes, ff.submitted_at,
                fk.shirt_color, fk.shorts_color, fk.socks_color,
                fk.goalkeeper_shirt_color, fk.goalkeeper_shorts_color,
                fk.submitted_at AS kit_submitted_at,
                fk.confirmed_at AS kit_confirmed_at,
                fk.confirmed_by AS kit_confirmed_by
         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 venues v ON v.id = f.venue_id
         LEFT JOIN fixture_formations ff ON ff.fixture_id = f.id AND ff.club_id = :club_id
         LEFT JOIN fixture_kits fk ON fk.fixture_id = f.id AND fk.club_id = :club_id
         WHERE f.id = :fixture_id
                     AND (f.home_club_id = :home_club_scope OR f.away_club_id = :away_club_scope)
         LIMIT 1',
        ['fixture_id' => $fixtureId, 'club_id' => $clubId, 'home_club_scope' => $clubId, 'away_club_scope' => $clubId]
    );

    if ($fixture === null) {
        return null;
    }

    $fixture['window_state'] = fixture_preparation_window_state(
        $fixture['match_date'] ?? null,
        $fixture['match_time'] ?? null,
        (string) ($fixture['status'] ?? 'scheduled'),
        $fixture['season_status'] ?? null,
        $fixture['competition_status'] ?? null
    );
    $fixture['is_home_club'] = (int) $fixture['home_club_id'] === $clubId;

    return $fixture;
}

function fixture_preparation_list_for_admin(int $limit = 24): array
{
    $fixtures = db_fetch_all(
        'SELECT f.id, f.matchday, f.match_date, f.match_time, f.status, f.group_label, f.knockout_round, f.stage_type,
                c.name AS competition_name, c.status AS competition_status,
                s.name AS season_name, s.status AS season_status,
                hc.id AS home_club_id, hc.name AS home_club_name, hc.short_name AS home_club_short_name, hc.logo AS home_club_logo,
                ac.id AS away_club_id, ac.name AS away_club_name, ac.short_name AS away_club_short_name, ac.logo AS away_club_logo,
                home_ff.formation AS home_formation, home_ff.tactical_notes AS home_tactical_notes, home_ff.submitted_at AS home_submitted_at,
                away_ff.formation AS away_formation, away_ff.tactical_notes AS away_tactical_notes, away_ff.submitted_at AS away_submitted_at,
                home_fk.shirt_color AS home_shirt_color, home_fk.shorts_color AS home_shorts_color, home_fk.socks_color AS home_socks_color,
                home_fk.goalkeeper_shirt_color AS home_goalkeeper_shirt_color, home_fk.goalkeeper_shorts_color AS home_goalkeeper_shorts_color,
                home_fk.submitted_at AS home_kit_submitted_at, home_fk.confirmed_at AS home_kit_confirmed_at,
                away_fk.shirt_color AS away_shirt_color, away_fk.shorts_color AS away_shorts_color, away_fk.socks_color AS away_socks_color,
                away_fk.goalkeeper_shirt_color AS away_goalkeeper_shirt_color, away_fk.goalkeeper_shorts_color AS away_goalkeeper_shorts_color,
                away_fk.submitted_at AS away_kit_submitted_at, away_fk.confirmed_at AS away_kit_confirmed_at
         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 fixture_formations home_ff ON home_ff.fixture_id = f.id AND home_ff.club_id = f.home_club_id
         LEFT JOIN fixture_formations away_ff ON away_ff.fixture_id = f.id AND away_ff.club_id = f.away_club_id
         LEFT JOIN fixture_kits home_fk ON home_fk.fixture_id = f.id AND home_fk.club_id = f.home_club_id
         LEFT JOIN fixture_kits away_fk ON away_fk.fixture_id = f.id AND away_fk.club_id = f.away_club_id
         ORDER BY COALESCE(f.match_date, "9999-12-31") ASC, COALESCE(f.match_time, "23:59:59") ASC, f.id ASC
         LIMIT ' . (int) $limit
    );

    foreach ($fixtures as $index => $fixture) {
        $fixtures[$index]['window_state'] = fixture_preparation_window_state(
            $fixture['match_date'] ?? null,
            $fixture['match_time'] ?? null,
            (string) ($fixture['status'] ?? 'scheduled'),
            $fixture['season_status'] ?? null,
            $fixture['competition_status'] ?? null
        );
    }

    return $fixtures;
}

function get_fixture_preparation_by_id_for_admin(int $fixtureId): ?array
{
    $fixture = db_fetch_one(
        'SELECT f.id, f.matchday, f.match_date, f.match_time, f.status, f.group_label, f.knockout_round, f.stage_type,
                c.name AS competition_name, c.status AS competition_status,
                s.name AS season_name, s.status AS season_status,
                hc.id AS home_club_id, hc.name AS home_club_name, hc.short_name AS home_club_short_name, hc.logo AS home_club_logo,
                ac.id AS away_club_id, ac.name AS away_club_name, ac.short_name AS away_club_short_name, ac.logo AS away_club_logo,
                v.name AS venue_name,
                home_ff.formation AS home_formation, home_ff.tactical_notes AS home_tactical_notes, home_ff.submitted_at AS home_submitted_at,
                away_ff.formation AS away_formation, away_ff.tactical_notes AS away_tactical_notes, away_ff.submitted_at AS away_submitted_at,
                home_fk.shirt_color AS home_shirt_color, home_fk.shorts_color AS home_shorts_color, home_fk.socks_color AS home_socks_color,
                home_fk.goalkeeper_shirt_color AS home_goalkeeper_shirt_color, home_fk.goalkeeper_shorts_color AS home_goalkeeper_shorts_color,
                home_fk.submitted_at AS home_kit_submitted_at, home_fk.confirmed_at AS home_kit_confirmed_at,
                away_fk.shirt_color AS away_shirt_color, away_fk.shorts_color AS away_shorts_color, away_fk.socks_color AS away_socks_color,
                away_fk.goalkeeper_shirt_color AS away_goalkeeper_shirt_color, away_fk.goalkeeper_shorts_color AS away_goalkeeper_shorts_color,
                away_fk.submitted_at AS away_kit_submitted_at, away_fk.confirmed_at AS away_kit_confirmed_at
         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 venues v ON v.id = f.venue_id
         LEFT JOIN fixture_formations home_ff ON home_ff.fixture_id = f.id AND home_ff.club_id = f.home_club_id
         LEFT JOIN fixture_formations away_ff ON away_ff.fixture_id = f.id AND away_ff.club_id = f.away_club_id
         LEFT JOIN fixture_kits home_fk ON home_fk.fixture_id = f.id AND home_fk.club_id = f.home_club_id
         LEFT JOIN fixture_kits away_fk ON away_fk.fixture_id = f.id AND away_fk.club_id = f.away_club_id
         WHERE f.id = :fixture_id
         LIMIT 1',
        ['fixture_id' => $fixtureId]
    );

    if ($fixture === null) {
        return null;
    }

    $fixture['window_state'] = fixture_preparation_window_state(
        $fixture['match_date'] ?? null,
        $fixture['match_time'] ?? null,
        (string) ($fixture['status'] ?? 'scheduled'),
        $fixture['season_status'] ?? null,
        $fixture['competition_status'] ?? null
    );

    return $fixture;
}

function find_fixture_preparation_or_abort_for_admin(int $fixtureId): array
{
    $fixture = get_fixture_preparation_by_id_for_admin($fixtureId);

    if ($fixture === null) {
        abort_request(404, 'Fixture not found', 'No fixture preparation record was found for this match.');
    }

    return $fixture;
}

function published_fixture_formations(int $fixtureId, ?int $referenceTimestamp = null): array
{
    $fixture = db_fetch_one(
        'SELECT f.id, f.match_date, f.match_time, f.status,
                c.status AS competition_status,
                s.status AS season_status
         FROM fixtures f
         INNER JOIN competitions c ON c.id = f.competition_id
         INNER JOIN seasons s ON s.id = f.season_id
         WHERE f.id = :fixture_id
         LIMIT 1',
        ['fixture_id' => $fixtureId]
    );

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

    $windowState = fixture_preparation_window_state(
        $fixture['match_date'] ?? null,
        $fixture['match_time'] ?? null,
        (string) ($fixture['status'] ?? 'scheduled'),
        $fixture['season_status'] ?? null,
        $fixture['competition_status'] ?? null,
        $referenceTimestamp
    );

    $secondsToKickoff = $windowState['seconds_to_kickoff'] ?? null;
    if ($secondsToKickoff === null || $secondsToKickoff > 3600) {
        return [];
    }

    $formations = db_fetch_all(
        'SELECT ff.club_id, ff.formation, ff.tactical_notes, ff.submitted_at,
                c.name AS club_name, c.short_name AS club_short_name, c.logo AS club_logo
         FROM fixture_formations ff
         INNER JOIN clubs c ON c.id = ff.club_id
         WHERE ff.fixture_id = :fixture_id
         ORDER BY ff.submitted_at DESC, ff.id DESC',
        ['fixture_id' => $fixtureId]
    );

    foreach ($formations as &$formation) {
        $formation['lineup'] = fixture_formation_lineup_for_club($fixtureId, (int) $formation['club_id']);
    }
    unset($formation);

    return $formations;
}

function fixture_preparation_player_pool(int $clubId): array
{
    return db_fetch_all(
        'SELECT p.id, p.first_name, p.last_name, p.jersey_number, p.position
         FROM players p
         WHERE p.club_id = :club_id
           AND p.status = "active"
           AND p.transfer_status = "active"
         ORDER BY COALESCE(p.jersey_number, 999) ASC, p.last_name ASC, p.first_name ASC, p.id ASC',
        ['club_id' => $clubId]
    );
}

function fixture_lineup_player_label(array $player): string
{
    $jersey = $player['jersey_number'] !== null && $player['jersey_number'] !== ''
        ? '#' . (string) $player['jersey_number'] . ' '
        : '';

    return trim($jersey . (string) $player['first_name'] . ' ' . (string) $player['last_name']);
}

function fixture_formation_lineup_for_club(int $fixtureId, int $clubId): array
{
    try {
        $players = db_fetch_all(
            'SELECT ffp.squad_role, ffp.display_order,
                    p.id AS player_id, p.first_name, p.last_name, p.jersey_number, p.position
             FROM fixture_formations ff
             INNER JOIN fixture_formation_players ffp ON ffp.formation_id = ff.id
             INNER JOIN players p ON p.id = ffp.player_id
             WHERE ff.fixture_id = :fixture_id
               AND ff.club_id = :club_id
             ORDER BY FIELD(ffp.squad_role, "starter", "substitute"), ffp.display_order ASC, p.last_name ASC, p.first_name ASC',
            ['fixture_id' => $fixtureId, 'club_id' => $clubId]
        );
    } catch (Throwable) {
        return ['starters' => [], 'substitutes' => []];
    }

    $lineup = ['starters' => [], 'substitutes' => []];

    foreach ($players as $player) {
        $bucket = ($player['squad_role'] ?? 'starter') === 'substitute' ? 'substitutes' : 'starters';
        $lineup[$bucket][] = $player;
    }

    return $lineup;
}

function fixture_normalize_player_selection(mixed $value): array
{
    if (!is_array($value)) {
        return [];
    }

    $normalized = [];
    foreach ($value as $playerId) {
        $playerId = (int) $playerId;
        if ($playerId > 0 && !in_array($playerId, $normalized, true)) {
            $normalized[] = $playerId;
        }
    }

    return $normalized;
}

function find_fixture_preparation_or_abort(int $fixtureId, int $clubId): array
{
    $fixture = get_fixture_preparation_by_id($fixtureId, $clubId);

    if ($fixture === null) {
        abort_request(404, 'Fixture not found', 'No club-scoped fixture preparation record was found for this match.');
    }

    return $fixture;
}

function validate_fixture_formation_payload(array $data, array &$errors): array
{
    validate_required($data, ['formation' => 'Formation'], $errors);

    $normalized = [
        'formation' => preg_replace('/\s+/', '', trim((string) ($data['formation'] ?? ''))),
        'tactical_notes' => trim((string) ($data['tactical_notes'] ?? '')),
        'starter_player_ids' => fixture_normalize_player_selection($data['starter_player_ids'] ?? []),
        'substitute_player_ids' => fixture_normalize_player_selection($data['substitute_player_ids'] ?? []),
    ];

    if ($normalized['formation'] !== '' && preg_match('/^[1-9](?:-[0-9]{1,2}){2,5}$/', $normalized['formation']) !== 1) {
        $errors['formation'] = 'Formation must look like 4-3-3, 4-2-3-1, or 3-5-2.';
    }

    if ($normalized['tactical_notes'] !== '' && mb_strlen($normalized['tactical_notes']) > 2000) {
        $errors['tactical_notes'] = 'Tactical notes may not be longer than 2000 characters.';
    }

    $clubId = (int) user_club_id();
    $availablePlayers = fixture_preparation_player_pool($clubId);
    $availablePlayerIds = array_map(
        static fn(array $player): int => (int) $player['id'],
        $availablePlayers
    );

    if (count($normalized['starter_player_ids']) !== 11) {
        $errors['starter_player_ids'] = 'Select exactly 11 starters.';
    }

    if (count($normalized['substitute_player_ids']) !== 5) {
        $errors['substitute_player_ids'] = 'Select exactly 5 substitutes.';
    }

    $selectedPlayerIds = array_merge($normalized['starter_player_ids'], $normalized['substitute_player_ids']);
    if (count($selectedPlayerIds) !== count(array_unique($selectedPlayerIds))) {
        $errors['substitute_player_ids'] = 'A player cannot be selected in both starters and substitutes.';
    }

    foreach ($selectedPlayerIds as $playerId) {
        if (!in_array($playerId, $availablePlayerIds, true)) {
            $errors['starter_player_ids'] = 'One or more selected players are invalid for this club.';
            break;
        }
    }

    return $normalized;
}

function save_fixture_formation(int $fixtureId, int $clubId, int $submittedBy, array $payload): void
{
    db_query(
        'INSERT INTO fixture_formations (fixture_id, club_id, formation, tactical_notes, submitted_by, submitted_at, created_at, updated_at)
         VALUES (:fixture_id, :club_id, :formation, :tactical_notes, :submitted_by, NOW(), NOW(), NOW())
         ON DUPLICATE KEY UPDATE
             formation = VALUES(formation),
             tactical_notes = VALUES(tactical_notes),
             submitted_by = VALUES(submitted_by),
             submitted_at = NOW(),
             updated_at = NOW(),
             id = LAST_INSERT_ID(id)',
        [
            'fixture_id' => $fixtureId,
            'club_id' => $clubId,
            'formation' => $payload['formation'],
            'tactical_notes' => $payload['tactical_notes'] !== '' ? $payload['tactical_notes'] : null,
            'submitted_by' => $submittedBy,
        ]
    );

    $formationId = (int) db()->lastInsertId();

    try {
        db_query('DELETE FROM fixture_formation_players WHERE formation_id = :formation_id', ['formation_id' => $formationId]);

        foreach ($payload['starter_player_ids'] as $index => $playerId) {
            db_query(
                'INSERT INTO fixture_formation_players (formation_id, player_id, squad_role, display_order, created_at, updated_at)
                 VALUES (:formation_id, :player_id, :squad_role, :display_order, NOW(), NOW())',
                [
                    'formation_id' => $formationId,
                    'player_id' => $playerId,
                    'squad_role' => 'starter',
                    'display_order' => $index + 1,
                ]
            );
        }

        foreach ($payload['substitute_player_ids'] as $index => $playerId) {
            db_query(
                'INSERT INTO fixture_formation_players (formation_id, player_id, squad_role, display_order, created_at, updated_at)
                 VALUES (:formation_id, :player_id, :squad_role, :display_order, NOW(), NOW())',
                [
                    'formation_id' => $formationId,
                    'player_id' => $playerId,
                    'squad_role' => 'substitute',
                    'display_order' => $index + 1,
                ]
            );
        }
    } catch (Throwable) {
    }
}

function fixture_has_dependencies(int $fixtureId): bool
{
    return (int) db_fetch_value(
        'SELECT (
            (SELECT COUNT(*) FROM match_results WHERE fixture_id = :results_fixture_id) +
            (SELECT COUNT(*) FROM match_events WHERE fixture_id = :events_fixture_id)
        )',
        [
            'results_fixture_id' => $fixtureId,
            'events_fixture_id' => $fixtureId,
        ],
        0
    ) > 0;
}

function fixture_competition_by_id(int $competitionId): ?array
{
    return db_fetch_one(
        'SELECT c.*, s.name AS season_name, s.status AS season_status
         FROM competitions c
         INNER JOIN seasons s ON s.id = c.season_id
         WHERE c.id = :id
         LIMIT 1',
        ['id' => $competitionId]
    );
}

function fixture_club_options_for_competition(?int $competitionId = null): array
{
    return db_fetch_all(
        'SELECT id, name, short_name, status, license_status
         FROM clubs
         WHERE status = "active" AND license_status = "approved"
         ORDER BY name ASC'
    );
}

function fixture_time_is_valid(string $time): bool
{
    if ($time === '') {
        return true;
    }

    return preg_match('/^(?:[01]\d|2[0-3]):[0-5]\d$/', $time) === 1;
}

function fixture_weekday_options(): array
{
    return [
        '1' => 'Monday',
        '2' => 'Tuesday',
        '3' => 'Wednesday',
        '4' => 'Thursday',
        '5' => 'Friday',
        '6' => 'Saturday',
        '7' => 'Sunday',
    ];
}

function fixture_normalize_weekday_selection(mixed $value): array
{
    $allowedWeekdays = array_keys(fixture_weekday_options());
    $selectedWeekdays = is_array($value) ? $value : (($value === null || $value === '') ? [] : [$value]);
    $normalized = [];

    foreach ($selectedWeekdays as $weekday) {
        $weekdayValue = (string) $weekday;
        if (in_array($weekdayValue, $allowedWeekdays, true) && !in_array($weekdayValue, $normalized, true)) {
            $normalized[] = $weekdayValue;
        }
    }

    sort($normalized);

    return $normalized;
}

function fixture_matchday_dates(?DateTimeImmutable $baseDate, int $matchdayCount, int $daysBetweenMatchdays, array $selectedWeekdays = []): array
{
    if ($baseDate === null || $matchdayCount <= 0) {
        return [];
    }

    if ($selectedWeekdays === []) {
        $dates = [];
        for ($matchdayIndex = 0; $matchdayIndex < $matchdayCount; $matchdayIndex++) {
            $dates[] = $baseDate->modify('+' . ($matchdayIndex * $daysBetweenMatchdays) . ' days')->format('Y-m-d');
        }

        return $dates;
    }

    $dates = [];
    $cursor = $baseDate;
    $selectedLookup = array_flip($selectedWeekdays);

    while (count($dates) < $matchdayCount) {
        while (!isset($selectedLookup[$cursor->format('N')])) {
            $cursor = $cursor->modify('+1 day');
        }

        $dates[] = $cursor->format('Y-m-d');
        $cursor = $cursor->modify('+1 day');
    }

    return $dates;
}

function fixture_generation_clubs(array $competition): array
{
    $clubs = fixture_club_options_for_competition((int) $competition['id']);
    $configuredTeams = (int) ($competition['number_of_teams'] ?? 0);

    if ($configuredTeams > 0 && count($clubs) !== $configuredTeams) {
        return [];
    }

    return $clubs;
}

function validate_fixture_payload(array $data, array &$errors, ?int $fixtureId = null): array
{
    validate_required($data, [
        'competition_id' => 'Competition',
        'matchday' => 'Matchday',
        'home_club_id' => 'Home club',
        'away_club_id' => 'Away club',
        'status' => 'Status',
    ], $errors);
    validate_numeric_min($data, 'matchday', 'Matchday', 1, $errors);
    validate_date_field($data, 'match_date', 'Match date', $errors);
    validate_in_array_field($data, 'status', fixture_status_options(), 'Status', $errors);

    $normalized = [
        'competition_id' => (int) ($data['competition_id'] ?? 0),
        'season_id' => 0,
        'matchday' => (int) ($data['matchday'] === '' ? 0 : ($data['matchday'] ?? 0)),
        'home_club_id' => (int) ($data['home_club_id'] ?? 0),
        'away_club_id' => (int) ($data['away_club_id'] ?? 0),
        'venue_id' => ($data['venue_id'] ?? '') === '' ? null : (int) $data['venue_id'],
        'match_date' => trim((string) ($data['match_date'] ?? '')),
        'match_time' => trim((string) ($data['match_time'] ?? '')),
        'status' => (string) ($data['status'] ?? 'scheduled'),
        'referee_id' => ($data['referee_id'] ?? '') === '' ? null : (int) $data['referee_id'],
        'assistant_referee_1' => trim((string) ($data['assistant_referee_1'] ?? '')),
        'assistant_referee_2' => trim((string) ($data['assistant_referee_2'] ?? '')),
        'fourth_official' => trim((string) ($data['fourth_official'] ?? '')),
        'match_commissioner' => trim((string) ($data['match_commissioner'] ?? '')),
    ];

    $competition = fixture_competition_by_id($normalized['competition_id']);
    if ($competition === null) {
        $errors['competition_id'] = 'Selected competition is invalid.';
    } else {
        $normalized['season_id'] = (int) $competition['season_id'];

        if (in_array($competition['status'], ['completed', 'cancelled'], true)) {
            $errors['competition_id'] = 'Fixtures cannot be scheduled for completed or cancelled competitions.';
        }
    }

    if ($normalized['home_club_id'] > 0 || $normalized['away_club_id'] > 0) {
        $eligibleIds = array_map(
            static fn(array $club): int => (int) $club['id'],
            fixture_club_options_for_competition($normalized['competition_id'])
        );

        if ($normalized['home_club_id'] <= 0 || !in_array($normalized['home_club_id'], $eligibleIds, true)) {
            $errors['home_club_id'] = 'Selected home club is invalid or not eligible for scheduling.';
        }

        if ($normalized['away_club_id'] <= 0 || !in_array($normalized['away_club_id'], $eligibleIds, true)) {
            $errors['away_club_id'] = 'Selected away club is invalid or not eligible for scheduling.';
        }
    }

    if ($normalized['home_club_id'] > 0 && $normalized['home_club_id'] === $normalized['away_club_id']) {
        $errors['away_club_id'] = 'Away club must be different from the home club.';
    }

    if (!fixture_time_is_valid($normalized['match_time'])) {
        $errors['match_time'] = 'Match time must be a valid 24-hour time in HH:MM format.';
    }

    if ($normalized['venue_id'] !== null) {
        $venue = db_fetch_one('SELECT id FROM venues WHERE id = :id AND status = "active" LIMIT 1', ['id' => $normalized['venue_id']]);
        if ($venue === null) {
            $errors['venue_id'] = 'Selected venue is invalid or inactive.';
        }
    }

    if ($normalized['referee_id'] !== null) {
        $referee = db_fetch_one('SELECT id FROM referees WHERE id = :id AND status = "active" LIMIT 1', ['id' => $normalized['referee_id']]);
        if ($referee === null) {
            $errors['referee_id'] = 'Selected referee is invalid or inactive.';
        }
    }

    if ($errors === [] && $normalized['competition_id'] > 0 && $normalized['matchday'] > 0) {
        $sameClubQuery = 'SELECT COUNT(*)
                          FROM fixtures
                          WHERE competition_id = :competition_id
                            AND matchday = :matchday
                            AND (
                                home_club_id = :home_club_id_primary
                                OR home_club_id = :away_club_id_primary
                                OR away_club_id = :home_club_id_secondary
                                OR away_club_id = :away_club_id_secondary
                            )';
        $sameClubParams = [
            'competition_id' => $normalized['competition_id'],
            'matchday' => $normalized['matchday'],
            'home_club_id_primary' => $normalized['home_club_id'],
            'away_club_id_primary' => $normalized['away_club_id'],
            'home_club_id_secondary' => $normalized['home_club_id'],
            'away_club_id_secondary' => $normalized['away_club_id'],
        ];

        if ($fixtureId !== null) {
            $sameClubQuery .= ' AND id <> :id';
            $sameClubParams['id'] = $fixtureId;
        }

        if ((int) db_fetch_value($sameClubQuery, $sameClubParams, 0) > 0) {
            $errors['matchday'] = 'One or both selected clubs already have a fixture in this competition for the chosen matchday.';
        }
    }

    if ($errors === [] && $normalized['venue_id'] !== null && $normalized['match_date'] !== '' && $normalized['match_time'] !== '') {
        $venueQuery = 'SELECT COUNT(*) FROM fixtures WHERE venue_id = :venue_id AND match_date = :match_date AND match_time = :match_time';
        $venueParams = [
            'venue_id' => $normalized['venue_id'],
            'match_date' => $normalized['match_date'],
            'match_time' => $normalized['match_time'],
        ];

        if ($fixtureId !== null) {
            $venueQuery .= ' AND id <> :id';
            $venueParams['id'] = $fixtureId;
        }

        if ((int) db_fetch_value($venueQuery, $venueParams, 0) > 0) {
            $errors['venue_id'] = 'Selected venue already has a fixture at the same date and time.';
        }
    }

    if ($errors === [] && $normalized['referee_id'] !== null && $normalized['match_date'] !== '' && $normalized['match_time'] !== '') {
        $refereeQuery = 'SELECT COUNT(*) FROM fixtures WHERE referee_id = :referee_id AND match_date = :match_date AND match_time = :match_time';
        $refereeParams = [
            'referee_id' => $normalized['referee_id'],
            'match_date' => $normalized['match_date'],
            'match_time' => $normalized['match_time'],
        ];

        if ($fixtureId !== null) {
            $refereeQuery .= ' AND id <> :id';
            $refereeParams['id'] = $fixtureId;
        }

        if ((int) db_fetch_value($refereeQuery, $refereeParams, 0) > 0) {
            $errors['referee_id'] = 'Selected referee is already assigned to another fixture at the same date and time.';
        }
    }

    return $normalized;
}

function validate_fixture_generation_payload(array $data, array &$errors): array
{
    $selectedWeekdays = fixture_normalize_weekday_selection($data['matchdays_of_week'] ?? []);

    validate_required($data, [
        'competition_id' => 'Competition',
    ], $errors);
    validate_date_field($data, 'start_date', 'Start date', $errors);

    if ($selectedWeekdays === []) {
        validate_numeric_min($data, 'days_between_matchdays', 'Days between matchdays', 1, $errors);
    }

    $normalized = [
        'competition_id' => (int) ($data['competition_id'] ?? 0),
        'start_date' => trim((string) ($data['start_date'] ?? '')),
        'match_time' => trim((string) ($data['match_time'] ?? '')),
        'days_between_matchdays' => $selectedWeekdays === []
            ? (int) (($data['days_between_matchdays'] ?? '') === '' ? 7 : $data['days_between_matchdays'])
            : 0,
        'matchdays_of_week' => $selectedWeekdays,
        'double_round' => ($data['double_round'] ?? '0') === '1',
    ];

    if (!fixture_time_is_valid($normalized['match_time'])) {
        $errors['match_time'] = 'Kickoff time must be a valid 24-hour time in HH:MM format.';
    }

    $competition = fixture_competition_by_id($normalized['competition_id']);
    if ($competition === null) {
        $errors['competition_id'] = 'Selected competition is invalid.';
        return $normalized;
    }

    if (!in_array($competition['type'], ['league', 'knockout', 'group_stage', 'mixed'], true)) {
        $errors['competition_id'] = 'Automatic fixture generation is only available for league, knockout, group-stage, or mixed competitions.';
    }

    if (in_array($competition['status'], ['completed', 'cancelled'], true)) {
        $errors['competition_id'] = 'Fixtures cannot be generated for completed or cancelled competitions.';
    }

    if (($competition['season_status'] ?? '') === 'paused') {
        $errors['competition_id'] = 'Fixture generation is unavailable while the parent season is paused.';
    }

    if ((int) db_fetch_value('SELECT COUNT(*) FROM fixtures WHERE competition_id = :competition_id', ['competition_id' => $normalized['competition_id']], 0) > 0) {
        $errors['competition_id'] = 'This competition already has fixtures. Clear them before generating a new schedule.';
    }

    $eligibleClubCount = (int) db_fetch_value(
        'SELECT COUNT(*) FROM clubs WHERE status = "active" AND license_status = "approved"',
        [],
        0
    );
    $clubs = fixture_generation_clubs($competition);
    if ((int) ($competition['number_of_teams'] ?? 0) > 0 && $eligibleClubCount !== (int) $competition['number_of_teams']) {
        $errors['competition_id'] = sprintf(
            'This competition is configured for %d teams, but only %d active approved clubs are currently available.',
            (int) $competition['number_of_teams'],
            $eligibleClubCount
        );
    } elseif (count($clubs) < 2) {
        $errors['competition_id'] = 'At least two active, approved clubs are required to generate fixtures.';
    }

    if ($errors === [] && ($competition['type'] ?? '') === 'knockout') {
        $teamCount = count($clubs);
        if (($teamCount & ($teamCount - 1)) !== 0) {
            $errors['competition_id'] = 'Knockout fixture generation requires a power-of-two team count such as 2, 4, 8, 16, or 32.';
        }
    }

    return $normalized;
}

function fixture_group_labels(int $groupCount): array
{
    $labels = [];

    for ($index = 0; $index < $groupCount; $index++) {
        $labels[] = 'Group ' . chr(65 + $index);
    }

    return $labels;
}

function fixture_group_chunks(array $clubs, int $groupCount): array
{
    $groupSize = (int) (count($clubs) / max(1, $groupCount));
    $labels = fixture_group_labels($groupCount);
    $groups = [];

    foreach ($labels as $index => $label) {
        $groups[$label] = array_slice($clubs, $index * $groupSize, $groupSize);
    }

    return $groups;
}

function fixture_knockout_round_label(int $teamCount): string
{
    return match ($teamCount) {
        2 => 'Final',
        4 => 'Semi-finals',
        8 => 'Quarter-finals',
        16 => 'Round of 16',
        default => 'Knockout round',
    };
}

function fixture_next_stage_matchday(int $competitionId): int
{
    return ((int) db_fetch_value('SELECT COALESCE(MAX(matchday), 0) FROM fixtures WHERE competition_id = :competition_id', ['competition_id' => $competitionId], 0)) + 1;
}

function fixture_next_stage_date(int $competitionId): ?string
{
    $latestDate = db_fetch_value('SELECT MAX(match_date) FROM fixtures WHERE competition_id = :competition_id AND match_date IS NOT NULL', ['competition_id' => $competitionId], null);
    if ($latestDate === null || trim((string) $latestDate) === '') {
        return null;
    }

    $date = new DateTimeImmutable((string) $latestDate);

    return $date->modify('+7 days')->format('Y-m-d');
}

function fixture_insert_generated_match(array $competition, int $matchday, int $homeClubId, int $awayClubId, ?string $matchDate, ?string $matchTime, string $stageType, ?string $groupLabel = null, ?string $knockoutRound = null, ?int $refereeId = null, string $matchCommissioner = ''): void
{
    db_query(
        'INSERT INTO fixtures (
            competition_id, season_id, matchday, stage_type, group_label, knockout_round,
            home_club_id, away_club_id, venue_id, match_date, match_time, status,
            referee_id, assistant_referee_1, assistant_referee_2, fourth_official,
            match_commissioner, created_at, updated_at
         ) VALUES (
            :competition_id, :season_id, :matchday, :stage_type, :group_label, :knockout_round,
            :home_club_id, :away_club_id, NULL, :match_date, :match_time, "scheduled",
                :referee_id, NULL, NULL, NULL,
                :match_commissioner, NOW(), NOW()
         )',
        [
            'competition_id' => (int) $competition['id'],
            'season_id' => (int) $competition['season_id'],
            'matchday' => $matchday,
            'stage_type' => $stageType,
            'group_label' => $groupLabel,
            'knockout_round' => $knockoutRound,
            'home_club_id' => $homeClubId,
            'away_club_id' => $awayClubId,
            'match_date' => $matchDate,
            'match_time' => $matchTime,
            'referee_id' => $refereeId,
            'match_commissioner' => $matchCommissioner,
        ]
    );
}

function fixture_group_stage_standings(int $competitionId): array
{
    $competition = fixture_competition_by_id($competitionId);
    if ($competition === null) {
        return [];
    }

    $fixtures = db_fetch_all(
        'SELECT f.id, f.group_label, f.home_club_id, f.away_club_id,
                mr.home_score, mr.away_score
         FROM fixtures f
         INNER JOIN match_results mr ON mr.fixture_id = f.id
         WHERE f.competition_id = :competition_id
           AND f.stage_type = "group_stage"
           AND mr.result_status = "verified"
         ORDER BY f.group_label ASC, f.matchday ASC, f.id ASC',
        ['competition_id' => $competitionId]
    );

    $clubs = db_fetch_all(
        'SELECT DISTINCT f.group_label, c.id AS club_id, c.name AS club_name, c.short_name AS club_short_name
         FROM fixtures f
         INNER JOIN clubs c ON c.id = f.home_club_id OR c.id = f.away_club_id
         WHERE f.competition_id = :competition_id
           AND f.stage_type = "group_stage"
         ORDER BY f.group_label ASC, c.name ASC',
        ['competition_id' => $competitionId]
    );

    $standings = [];
    foreach ($clubs as $club) {
        $groupLabel = (string) $club['group_label'];
        $clubId = (int) $club['club_id'];
        $standings[$groupLabel][$clubId] = [
            'group_label' => $groupLabel,
            'club_id' => $clubId,
            'club_name' => $club['club_name'],
            'club_short_name' => $club['club_short_name'],
            'played' => 0,
            'won' => 0,
            'drawn' => 0,
            'lost' => 0,
            'goals_for' => 0,
            'goals_against' => 0,
            'goal_difference' => 0,
            'points' => 0,
            'position' => 0,
        ];
    }

    $pointsWin = (int) ($competition['points_win'] ?? site_setting('points_win', 3));
    $pointsDraw = (int) ($competition['points_draw'] ?? site_setting('points_draw', 1));
    $pointsLoss = (int) ($competition['points_loss'] ?? site_setting('points_loss', 0));

    foreach ($fixtures as $fixture) {
        $groupLabel = (string) $fixture['group_label'];
        $homeClubId = (int) $fixture['home_club_id'];
        $awayClubId = (int) $fixture['away_club_id'];
        if (!isset($standings[$groupLabel][$homeClubId], $standings[$groupLabel][$awayClubId])) {
            continue;
        }

        $homeScore = (int) $fixture['home_score'];
        $awayScore = (int) $fixture['away_score'];
        $standings[$groupLabel][$homeClubId]['played']++;
        $standings[$groupLabel][$awayClubId]['played']++;
        $standings[$groupLabel][$homeClubId]['goals_for'] += $homeScore;
        $standings[$groupLabel][$homeClubId]['goals_against'] += $awayScore;
        $standings[$groupLabel][$awayClubId]['goals_for'] += $awayScore;
        $standings[$groupLabel][$awayClubId]['goals_against'] += $homeScore;

        if ($homeScore > $awayScore) {
            $standings[$groupLabel][$homeClubId]['won']++;
            $standings[$groupLabel][$awayClubId]['lost']++;
            $standings[$groupLabel][$homeClubId]['points'] += $pointsWin;
            $standings[$groupLabel][$awayClubId]['points'] += $pointsLoss;
        } elseif ($homeScore < $awayScore) {
            $standings[$groupLabel][$awayClubId]['won']++;
            $standings[$groupLabel][$homeClubId]['lost']++;
            $standings[$groupLabel][$awayClubId]['points'] += $pointsWin;
            $standings[$groupLabel][$homeClubId]['points'] += $pointsLoss;
        } else {
            $standings[$groupLabel][$homeClubId]['drawn']++;
            $standings[$groupLabel][$awayClubId]['drawn']++;
            $standings[$groupLabel][$homeClubId]['points'] += $pointsDraw;
            $standings[$groupLabel][$awayClubId]['points'] += $pointsDraw;
        }
    }

    foreach ($standings as $groupLabel => $rows) {
        foreach ($rows as &$row) {
            $row['goal_difference'] = $row['goals_for'] - $row['goals_against'];
        }
        unset($row);

        uasort(
            $rows,
            static fn(array $left, array $right): int => [$right['points'], $right['goal_difference'], $right['goals_for'], $left['club_name']]
                <=> [$left['points'], $left['goal_difference'], $left['goals_for'], $right['club_name']]
        );

        $position = 1;
        foreach ($rows as &$row) {
            $row['position'] = $position++;
        }
        unset($row);

        $standings[$groupLabel] = array_values($rows);
    }

    ksort($standings);

    return $standings;
}

function fixture_group_stage_is_complete(int $competitionId): bool
{
    $fixtureCount = (int) db_fetch_value('SELECT COUNT(*) FROM fixtures WHERE competition_id = :competition_id AND stage_type = "group_stage"', ['competition_id' => $competitionId], 0);
    if ($fixtureCount === 0) {
        return false;
    }

    $verifiedCount = (int) db_fetch_value(
        'SELECT COUNT(*)
         FROM fixtures f
         INNER JOIN match_results mr ON mr.fixture_id = f.id
         WHERE f.competition_id = :competition_id
           AND f.stage_type = "group_stage"
           AND mr.result_status = "verified"',
        ['competition_id' => $competitionId],
        0
    );

    return $fixtureCount === $verifiedCount;
}

function fixture_knockout_round_rows(int $competitionId): array
{
    return db_fetch_all(
        'SELECT f.*, mr.home_score, mr.away_score, mr.result_status,
                hc.name AS home_club_name, ac.name AS away_club_name
         FROM fixtures f
         LEFT JOIN match_results mr ON mr.fixture_id = f.id
         INNER JOIN clubs hc ON hc.id = f.home_club_id
         INNER JOIN clubs ac ON ac.id = f.away_club_id
         WHERE f.competition_id = :competition_id
           AND f.stage_type = "knockout"
         ORDER BY f.matchday ASC, f.id ASC',
        ['competition_id' => $competitionId]
    );
}

function fixture_generate_group_stage_schedule(array $competition, array $payload): int
{
    $clubs = fixture_generation_clubs($competition);
    $groups = fixture_group_chunks($clubs, (int) $competition['group_count']);
    $baseDate = $payload['start_date'] !== '' ? new DateTimeImmutable($payload['start_date']) : null;
    $created = 0;
    $maxRounds = 0;
    $assignmentState = fixture_auto_assignment_state();

    $groupSchedules = [];
    foreach ($groups as $groupLabel => $groupClubs) {
        $clubIds = array_map(static fn(array $club): int => (int) $club['id'], $groupClubs);
        $groupSchedules[$groupLabel] = fixture_round_robin_pairs($clubIds, (bool) $payload['double_round']);
        $maxRounds = max($maxRounds, count($groupSchedules[$groupLabel]));
    }

    $matchdayDates = fixture_matchday_dates(
        $baseDate,
        $maxRounds,
        (int) $payload['days_between_matchdays'],
        $payload['matchdays_of_week'] ?? []
    );

    for ($roundIndex = 0; $roundIndex < $maxRounds; $roundIndex++) {
        $matchDate = $matchdayDates[$roundIndex] ?? null;

        foreach ($groupSchedules as $groupLabel => $schedule) {
            foreach (($schedule[$roundIndex] ?? []) as $fixture) {
                $assignment = fixture_apply_auto_official_assignments([
                    'match_date' => $matchDate,
                    'match_time' => $payload['match_time'] !== '' ? $payload['match_time'] : null,
                    'referee_id' => null,
                    'match_commissioner' => '',
                ], $assignmentState);
                fixture_insert_generated_match(
                    $competition,
                    $roundIndex + 1,
                    (int) $fixture['home_club_id'],
                    (int) $fixture['away_club_id'],
                    $matchDate,
                    $payload['match_time'] !== '' ? $payload['match_time'] : null,
                    'group_stage',
                    $groupLabel,
                    null,
                    $assignment['referee_id'],
                    (string) $assignment['match_commissioner']
                );
                $created++;
            }
        }
    }

    return $created;
}

function fixture_knockout_pairs_from_group_standings(array $standings, int $qualifiersPerGroup): array
{
    $groupLabels = array_keys($standings);
    $pairs = [];

    if ($qualifiersPerGroup === 1) {
        $winners = [];
        foreach ($groupLabels as $groupLabel) {
            if (isset($standings[$groupLabel][0])) {
                $winners[] = $standings[$groupLabel][0];
            }
        }

        for ($index = 0; $index < (int) floor(count($winners) / 2); $index++) {
            $pairs[] = [
                'home_club_id' => (int) $winners[$index]['club_id'],
                'away_club_id' => (int) $winners[count($winners) - 1 - $index]['club_id'],
            ];
        }

        return $pairs;
    }

    for ($index = 0; $index < count($groupLabels); $index += 2) {
        $leftGroup = $groupLabels[$index] ?? null;
        $rightGroup = $groupLabels[$index + 1] ?? null;
        if ($leftGroup === null || $rightGroup === null) {
            continue;
        }

        if (isset($standings[$leftGroup][0], $standings[$rightGroup][1])) {
            $pairs[] = [
                'home_club_id' => (int) $standings[$leftGroup][0]['club_id'],
                'away_club_id' => (int) $standings[$rightGroup][1]['club_id'],
            ];
        }

        if (isset($standings[$rightGroup][0], $standings[$leftGroup][1])) {
            $pairs[] = [
                'home_club_id' => (int) $standings[$rightGroup][0]['club_id'],
                'away_club_id' => (int) $standings[$leftGroup][1]['club_id'],
            ];
        }
    }

    return $pairs;
}

function fixture_knockout_winner_club_ids(array $fixtures): array
{
    $winners = [];

    foreach ($fixtures as $fixture) {
        if (($fixture['result_status'] ?? '') !== 'verified') {
            return [];
        }

        $homeScore = (int) ($fixture['home_score'] ?? 0);
        $awayScore = (int) ($fixture['away_score'] ?? 0);
        if ($homeScore === $awayScore) {
            throw new RuntimeException('Knockout fixtures must produce a winner before progression can continue.');
        }

        $winners[] = $homeScore > $awayScore ? (int) $fixture['home_club_id'] : (int) $fixture['away_club_id'];
    }

    return $winners;
}

function fixture_generate_knockout_round(array $competition, array $clubIds): int
{
    $teamCount = count($clubIds);
    if ($teamCount < 2) {
        return 0;
    }

    $roundLabel = fixture_knockout_round_label($teamCount);
    $matchday = fixture_next_stage_matchday((int) $competition['id']);
    $matchDate = fixture_next_stage_date((int) $competition['id']);
    $matchTime = db_fetch_value(
        'SELECT match_time FROM fixtures WHERE competition_id = :competition_id AND match_time IS NOT NULL ORDER BY matchday DESC, id DESC LIMIT 1',
        ['competition_id' => (int) $competition['id']],
        null
    );
    $created = 0;
    $assignmentState = fixture_auto_assignment_state();
    $normalizedMatchTime = $matchTime !== null && trim((string) $matchTime) !== '' ? (string) $matchTime : null;

    for ($index = 0; $index < count($clubIds); $index += 2) {
        if (!isset($clubIds[$index + 1])) {
            continue;
        }

        $assignment = fixture_apply_auto_official_assignments([
            'match_date' => $matchDate,
            'match_time' => $normalizedMatchTime,
            'referee_id' => null,
            'match_commissioner' => '',
        ], $assignmentState);

        fixture_insert_generated_match(
            $competition,
            $matchday,
            (int) $clubIds[$index],
            (int) $clubIds[$index + 1],
            $matchDate,
            $normalizedMatchTime,
            'knockout',
            null,
            $roundLabel,
            $assignment['referee_id'],
            (string) $assignment['match_commissioner']
        );
        $created++;
    }

    return $created;
}

function fixture_generate_initial_knockout_schedule(array $competition, array $payload): int
{
    $clubs = fixture_generation_clubs($competition);
    $clubIds = array_map(static fn(array $club): int => (int) $club['id'], $clubs);

    if ($clubIds === []) {
        return 0;
    }

    $baseDate = $payload['start_date'] !== '' ? new DateTimeImmutable($payload['start_date']) : null;
    $matchdayDates = fixture_matchday_dates(
        $baseDate,
        1,
        (int) $payload['days_between_matchdays'],
        $payload['matchdays_of_week'] ?? []
    );
    $matchDate = $matchdayDates[0] ?? null;
    $roundLabel = fixture_knockout_round_label(count($clubIds));
    $created = 0;
    $assignmentState = fixture_auto_assignment_state();

    for ($index = 0; $index < count($clubIds); $index += 2) {
        $assignment = fixture_apply_auto_official_assignments([
            'match_date' => $matchDate,
            'match_time' => $payload['match_time'] !== '' ? $payload['match_time'] : null,
            'referee_id' => null,
            'match_commissioner' => '',
        ], $assignmentState);
        fixture_insert_generated_match(
            $competition,
            1,
            (int) $clubIds[$index],
            (int) $clubIds[$index + 1],
            $matchDate,
            $payload['match_time'] !== '' ? $payload['match_time'] : null,
            'knockout',
            null,
            $roundLabel,
            $assignment['referee_id'],
            (string) $assignment['match_commissioner']
        );
        $created++;
    }

    return $created;
}

function fixture_progress_competition_stage(int $competitionId): int
{
    $competition = fixture_competition_by_id($competitionId);
    if ($competition === null || !in_array($competition['type'], ['knockout', 'group_stage', 'mixed'], true)) {
        return 0;
    }

    $knockoutFixtures = fixture_knockout_round_rows($competitionId);
    if ($knockoutFixtures === []) {
        if (!fixture_group_stage_is_complete($competitionId)) {
            return 0;
        }

        $standings = fixture_group_stage_standings($competitionId);
        $pairs = fixture_knockout_pairs_from_group_standings($standings, (int) $competition['qualifiers_per_group']);
        $qualifiedClubIds = [];
        foreach ($pairs as $pair) {
            $qualifiedClubIds[] = (int) $pair['home_club_id'];
            $qualifiedClubIds[] = (int) $pair['away_club_id'];
        }

        return fixture_generate_knockout_round($competition, $qualifiedClubIds);
    }

    $currentMatchday = max(array_map(static fn(array $fixture): int => (int) $fixture['matchday'], $knockoutFixtures));
    $currentRoundFixtures = array_values(array_filter(
        $knockoutFixtures,
        static fn(array $fixture): bool => (int) $fixture['matchday'] === $currentMatchday
    ));
    $winnerClubIds = fixture_knockout_winner_club_ids($currentRoundFixtures);
    if ($winnerClubIds === []) {
        return 0;
    }

    if (count($winnerClubIds) === 1) {
        competition_set_status((int) $competition['id'], 'completed');
        return 0;
    }

    $nextRoundCount = (int) db_fetch_value('SELECT COUNT(*) FROM fixtures WHERE competition_id = :competition_id AND stage_type = "knockout" AND matchday > :matchday', ['competition_id' => $competitionId, 'matchday' => $currentMatchday], 0);
    if ($nextRoundCount > 0) {
        return 0;
    }

    return fixture_generate_knockout_round($competition, $winnerClubIds);
}

function fixture_countdown(?string $matchDate, ?string $matchTime): ?array
{
    if ($matchDate === null || trim($matchDate) === '') {
        return null;
    }

    $deadline = trim($matchDate . ' ' . ($matchTime !== null && trim($matchTime) !== '' ? $matchTime : '23:59:59'));
    $timestamp = strtotime($deadline);
    if ($timestamp === false) {
        return null;
    }

    $secondsRemaining = max(0, $timestamp - time());

    return [
        'deadline_iso' => date('c', $timestamp),
        'seconds_remaining' => $secondsRemaining,
        'days_remaining' => (int) floor($secondsRemaining / 86400),
    ];
}

function fixture_news_slugify(string $value): string
{
    $value = strtolower(trim($value));
    $value = preg_replace('/[^a-z0-9]+/', '-', $value) ?? '';
    $value = trim($value, '-');

    return $value !== '' ? $value : 'fixture-news';
}

function fixture_unique_news_slug(string $headline, ?int $ignoreId = null): string
{
    $baseSlug = fixture_news_slugify($headline);
    $slug = $baseSlug;
    $counter = 2;

    while ((int) db_fetch_value(
        'SELECT COUNT(*) FROM news_posts WHERE slug = :slug' . ($ignoreId !== null && $ignoreId > 0 ? ' AND id <> :ignore_id' : ''),
        $ignoreId !== null && $ignoreId > 0 ? ['slug' => $slug, 'ignore_id' => $ignoreId] : ['slug' => $slug],
        0
    ) > 0) {
        $slug = $baseSlug . '-' . $counter;
        $counter++;
    }

    return $slug;
}

function fixture_publish_news(string $headline, string $body, string $category): int
{
    db_query(
        'INSERT INTO news_posts (
            title, slug, category, body, status, published_at, created_at, created_by
         ) VALUES (
            :title, :slug, :category, :body, "published", NOW(), NOW(), :created_by
         )',
        [
            'title' => $headline,
            'slug' => fixture_unique_news_slug($headline),
            'category' => $category,
            'body' => $body,
            'created_by' => (int) ($_SESSION['user_id'] ?? 0) ?: null,
        ]
    );

    $postId = (int) db()->lastInsertId();
    web_push_send_news_post($postId);

    return $postId;
}

function validate_fixture_postponement_payload(array $data, array &$errors): array
{
    validate_required($data, ['postponement_reason' => 'Postponement reason'], $errors);

    return [
        'postponement_reason' => trim((string) ($data['postponement_reason'] ?? '')),
    ];
}

function validate_fixture_reschedule_payload(array $data, array &$errors, array $fixture): array
{
    validate_required($data, [
        'match_date' => 'Rescheduled match date',
        'match_time' => 'Rescheduled kickoff time',
        'postponement_reason' => 'Reschedule note',
    ], $errors);
    validate_date_field($data, 'match_date', 'Rescheduled match date', $errors);

    $matchTime = trim((string) ($data['match_time'] ?? ''));
    if (!fixture_time_is_valid($matchTime)) {
        $errors['match_time'] = 'Kickoff time must be a valid 24-hour time in HH:MM format.';
    }

    $normalized = [
        'match_date' => trim((string) ($data['match_date'] ?? '')),
        'match_time' => $matchTime,
        'postponement_reason' => trim((string) ($data['postponement_reason'] ?? '')),
    ];

    if ($normalized['match_date'] !== '' && strtotime($normalized['match_date']) === false) {
        $errors['match_date'] = 'Rescheduled match date is invalid.';
    }

    if ($errors === [] && $fixture['venue_id'] !== null) {
        $conflict = (int) db_fetch_value(
            'SELECT COUNT(*) FROM fixtures WHERE venue_id = :venue_id AND match_date = :match_date AND match_time = :match_time AND id <> :id',
            [
                'venue_id' => (int) $fixture['venue_id'],
                'match_date' => $normalized['match_date'],
                'match_time' => $normalized['match_time'],
                'id' => (int) $fixture['id'],
            ],
            0
        );

        if ($conflict > 0) {
            $errors['match_date'] = 'The selected venue already has a fixture at the rescheduled date and time.';
        }
    }

    return $normalized;
}

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

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

    try {
        $ready = (int) db_fetch_value(
            'SELECT COUNT(*)
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = "fixtures"
               AND column_name IN ("postponement_reason", "postponed_at", "rescheduled_from_date", "rescheduled_from_time")',
            [],
            0
        ) === 4;
    } catch (Throwable) {
        $ready = false;
    }

    return $ready;
}

function fixture_postpone(array $fixture, string $reason): void
{
    $headline = sprintf('Match postponed: %s vs %s', $fixture['home_club_name'], $fixture['away_club_name']);
    $body = sprintf(
        '%s vs %s has been postponed. Original schedule: %s%s. Reason: %s',
        $fixture['home_club_name'],
        $fixture['away_club_name'],
        $fixture['match_date'] ?: 'Date TBC',
        !empty($fixture['match_time']) ? ' ' . $fixture['match_time'] : '',
        $reason
    );

    db()->beginTransaction();

    try {
        $params = [
            'id' => (int) $fixture['id'],
        ];

        if (fixture_postpone_metadata_columns_ready()) {
            $updateSql = 'UPDATE fixtures
                          SET status = "postponed",
                              postponement_reason = :postponement_reason,
                              postponed_at = NOW(),
                              updated_at = NOW()
                          WHERE id = :id';
            $params['postponement_reason'] = $reason;
        } else {
            $updateSql = 'UPDATE fixtures
                          SET status = "postponed",
                              updated_at = NOW()
                          WHERE id = :id';
        }

        db_query($updateSql, $params);

        $newsPostId = fixture_publish_news($headline, $body, 'fixture_postponement');
        audit_log('postpone_fixture', 'fixtures', (int) $fixture['id'], $fixture, ['postponement_reason' => $reason, 'news_post_id' => $newsPostId]);
        db()->commit();
    } catch (Throwable $exception) {
        if (db()->inTransaction()) {
            db()->rollBack();
        }

        throw $exception;
    }
}

function fixture_reschedule(array $fixture, array $payload): void
{
    $headline = sprintf('Match rescheduled: %s vs %s', $fixture['home_club_name'], $fixture['away_club_name']);
    $body = sprintf(
        '%s vs %s has been rescheduled from %s%s to %s %s. Reason: %s',
        $fixture['home_club_name'],
        $fixture['away_club_name'],
        $fixture['match_date'] ?: 'Date TBC',
        !empty($fixture['match_time']) ? ' ' . $fixture['match_time'] : '',
        $payload['match_date'],
        $payload['match_time'],
        $payload['postponement_reason']
    );

    db()->beginTransaction();

    try {
        $params = [
            'match_date' => $payload['match_date'],
            'match_time' => $payload['match_time'],
            'id' => (int) $fixture['id'],
        ];

        if (fixture_postpone_metadata_columns_ready()) {
            $updateSql = 'UPDATE fixtures
                          SET status = "scheduled",
                              rescheduled_from_date = :rescheduled_from_date,
                              rescheduled_from_time = :rescheduled_from_time,
                              match_date = :match_date,
                              match_time = :match_time,
                              postponement_reason = :postponement_reason,
                              updated_at = NOW()
                          WHERE id = :id';
            $params['rescheduled_from_date'] = $fixture['match_date'];
            $params['rescheduled_from_time'] = $fixture['match_time'];
            $params['postponement_reason'] = $payload['postponement_reason'];
        } else {
            $updateSql = 'UPDATE fixtures
                          SET status = "scheduled",
                              match_date = :match_date,
                              match_time = :match_time,
                              updated_at = NOW()
                          WHERE id = :id';
        }

        db_query($updateSql, $params);

        $newsPostId = fixture_publish_news($headline, $body, 'fixture_reschedule');
        audit_log('reschedule_fixture', 'fixtures', (int) $fixture['id'], $fixture, $payload + ['news_post_id' => $newsPostId]);
        db()->commit();
    } catch (Throwable $exception) {
        if (db()->inTransaction()) {
            db()->rollBack();
        }

        throw $exception;
    }
}

function fixture_round_robin_pairs(array $clubIds, bool $doubleRound = false): array
{
    if (count($clubIds) % 2 !== 0) {
        $clubIds[] = null;
    }

    $rounds = count($clubIds) - 1;
    $halfSize = (int) (count($clubIds) / 2);
    $schedule = [];
    $rotation = $clubIds;

    for ($round = 0; $round < $rounds; $round++) {
        $matchdayFixtures = [];

        for ($index = 0; $index < $halfSize; $index++) {
            $home = $rotation[$index];
            $away = $rotation[count($rotation) - 1 - $index];

            if ($home === null || $away === null) {
                continue;
            }

            if ($round % 2 === 1) {
                [$home, $away] = [$away, $home];
            }

            $matchdayFixtures[] = ['home_club_id' => $home, 'away_club_id' => $away];
        }

        $schedule[] = $matchdayFixtures;

        $fixed = array_shift($rotation);
        $last = array_pop($rotation);
        array_unshift($rotation, $fixed);
        array_splice($rotation, 1, 0, [$last]);
    }

    if ($doubleRound) {
        $returnLegs = [];
        foreach ($schedule as $matchdayFixtures) {
            $returnLegs[] = array_map(
                static fn(array $fixture): array => [
                    'home_club_id' => $fixture['away_club_id'],
                    'away_club_id' => $fixture['home_club_id'],
                ],
                $matchdayFixtures
            );
        }
        $schedule = array_merge($schedule, $returnLegs);
    }

    return $schedule;
}

function generate_fixture_schedule(array $payload): int
{
    $competition = fixture_competition_by_id((int) $payload['competition_id']);

    if ($competition === null) {
        throw new RuntimeException('Competition record could not be loaded.');
    }

    if (in_array($competition['type'], ['group_stage', 'mixed'], true)) {
        return fixture_generate_group_stage_schedule($competition, $payload);
    }

    if (($competition['type'] ?? '') === 'knockout') {
        return fixture_generate_initial_knockout_schedule($competition, $payload);
    }

    $clubs = fixture_generation_clubs($competition);
    $clubIds = array_map(static fn(array $club): int => (int) $club['id'], $clubs);
    $schedule = fixture_round_robin_pairs($clubIds, (bool) $payload['double_round']);
    $baseDate = $payload['start_date'] !== '' ? new DateTimeImmutable($payload['start_date']) : null;
    $matchdayDates = fixture_matchday_dates(
        $baseDate,
        count($schedule),
        (int) $payload['days_between_matchdays'],
        $payload['matchdays_of_week'] ?? []
    );

    $created = 0;
    $assignmentState = fixture_auto_assignment_state();
    foreach ($schedule as $matchdayIndex => $fixtures) {
        $matchDate = $matchdayDates[$matchdayIndex] ?? null;

        foreach ($fixtures as $fixture) {
            $assignment = fixture_apply_auto_official_assignments([
                'match_date' => $matchDate,
                'match_time' => $payload['match_time'] !== '' ? $payload['match_time'] : null,
                'referee_id' => null,
                'match_commissioner' => '',
            ], $assignmentState);
            fixture_insert_generated_match(
                $competition,
                $matchdayIndex + 1,
                (int) $fixture['home_club_id'],
                (int) $fixture['away_club_id'],
                $matchDate,
                $payload['match_time'] !== '' ? $payload['match_time'] : null,
                'league',
                null,
                null,
                $assignment['referee_id'],
                (string) $assignment['match_commissioner']
            );
            $created++;
        }
    }

    return $created;
}

function fixture_badge_class(string $status): string
{
    return match ($status) {
        'scheduled' => 'badge-soft-success',
        'live' => 'bg-info-subtle text-info-emphasis',
        'completed' => 'bg-secondary-subtle text-secondary-emphasis',
        'postponed' => 'text-bg-light',
        'cancelled' => 'badge-soft-danger',
        default => 'text-bg-light',
    };
}

function fixture_export_rows(?int $competitionId = null): array
{
    $fixtures = fixture_list();

    if ($competitionId === null || $competitionId <= 0) {
        return $fixtures;
    }

    return array_values(array_filter(
        $fixtures,
        static fn(array $fixture): bool => (int) ($fixture['competition_id'] ?? 0) === $competitionId
    ));
}

function fixture_pdf_escape(string $value): string
{
    return str_replace(['\\', '(', ')'], ['\\\\', '\\(', '\\)'], $value);
}

function fixture_pdf_wrap_line(string $line, int $maxLength = 100): array
{
    $wrapped = wordwrap($line, $maxLength, "\n", true);
    return explode("\n", $wrapped);
}

function fixture_pdf_lines(array $fixtures, string $title): array
{
    $lines = [
        $title,
        'Generated: ' . date('Y-m-d H:i'),
        '',
    ];

    if ($fixtures === []) {
        $lines[] = 'No fixtures available for the selected filter.';
        return $lines;
    }

    foreach ($fixtures as $fixture) {
        $stage = (string) ($fixture['group_label'] ?: ($fixture['knockout_round'] ?: ucwords(str_replace('_', ' ', (string) ($fixture['stage_type'] ?? 'league')))));
        $schedule = trim((string) (($fixture['match_date'] ?: 'TBD') . ' ' . ($fixture['match_time'] ?: 'Time pending')));
        $line = sprintf(
            'MD %d | %s vs %s | %s | %s | %s | %s',
            (int) ($fixture['matchday'] ?? 0),
            (string) ($fixture['home_club_name'] ?? $fixture['home_club'] ?? 'Home club'),
            (string) ($fixture['away_club_name'] ?? $fixture['away_club'] ?? 'Away club'),
            (string) ($fixture['competition_name'] ?? 'Competition'),
            $stage,
            $schedule,
            (string) ($fixture['venue_name'] ?: 'TBC')
        );

        foreach (fixture_pdf_wrap_line($line) as $wrappedLine) {
            $lines[] = $wrappedLine;
        }

        $statusLine = 'Status: ' . ucfirst((string) ($fixture['status'] ?? 'scheduled'));
        if (($fixture['home_score'] ?? null) !== null && ($fixture['away_score'] ?? null) !== null) {
            $statusLine .= sprintf(
                ' | Result: %s - %s (%s)',
                (string) $fixture['home_score'],
                (string) $fixture['away_score'],
                ucfirst((string) ($fixture['result_status'] ?? 'draft'))
            );
        }

        foreach (fixture_pdf_wrap_line($statusLine) as $wrappedLine) {
            $lines[] = $wrappedLine;
        }

        $lines[] = '';
    }

    return $lines;
}

function fixture_render_pdf_document(string $title, array $fixtures): string
{
    $pageWidth = 595;
    $pageHeight = 842;
    $marginLeft = 50;
    $marginTop = 792;
    $lineHeight = 14;
    $linesPerPage = 50;
    $lines = fixture_pdf_lines($fixtures, $title);
    $pages = array_chunk($lines, $linesPerPage);

    $objects = [];
    $objects[1] = '<< /Type /Catalog /Pages 2 0 R >>';
    $objects[3] = '<< /Type /Font /Subtype /Type1 /BaseFont /Helvetica >>';

    $pageObjectNumbers = [];
    $nextObjectNumber = 4;

    foreach ($pages as $pageLines) {
        $pageObjectNumber = $nextObjectNumber++;
        $contentObjectNumber = $nextObjectNumber++;
        $pageObjectNumbers[] = $pageObjectNumber;

        $stream = "BT\n/F1 10 Tf\n" . $lineHeight . " TL\n" . $marginLeft . ' ' . $marginTop . " Td\n";
        foreach ($pageLines as $line) {
            $stream .= '(' . fixture_pdf_escape($line) . ") Tj\nT*\n";
        }
        $stream .= "ET";

        $objects[$contentObjectNumber] = "<< /Length " . strlen($stream) . " >>\nstream\n" . $stream . "\nendstream";
        $objects[$pageObjectNumber] = sprintf(
            '<< /Type /Page /Parent 2 0 R /MediaBox [0 0 %d %d] /Resources << /Font << /F1 3 0 R >> >> /Contents %d 0 R >>',
            $pageWidth,
            $pageHeight,
            $contentObjectNumber
        );
    }

    $kids = implode(' ', array_map(static fn(int $number): string => $number . ' 0 R', $pageObjectNumbers));
    $objects[2] = '<< /Type /Pages /Count ' . count($pageObjectNumbers) . ' /Kids [ ' . $kids . ' ] >>';

    ksort($objects);

    $pdf = "%PDF-1.4\n%\xE2\xE3\xCF\xD3\n";
    $offsets = [0 => 0];

    foreach ($objects as $number => $object) {
        $offsets[$number] = strlen($pdf);
        $pdf .= $number . " 0 obj\n" . $object . "\nendobj\n";
    }

    $xrefOffset = strlen($pdf);
    $pdf .= 'xref' . "\n";
    $pdf .= '0 ' . (count($objects) + 1) . "\n";
    $pdf .= "0000000000 65535 f \n";

    foreach (array_keys($objects) as $number) {
        $pdf .= sprintf('%010d 00000 n ', $offsets[$number]) . "\n";
    }

    $pdf .= 'trailer << /Size ' . (count($objects) + 1) . ' /Root 1 0 R >>' . "\n";
    $pdf .= 'startxref' . "\n" . $xrefOffset . "\n%%EOF";

    return $pdf;
}
