<?php
/**
 * lib/sport.php — Batched auto-importer (timeout-safe).
 *
 * Each call to sport_events_window() only imports a SMALL BATCH of leagues
 * (SPORT_BATCH_SIZE), then remembers the offset. Over successive page loads
 * every league gets refreshed without the request timing out.
 *
 * Priority inside each league:
 *   1. eventsnextleague (fast — F1/MLB/NFL/etc.)
 *   2. eventsseason JSON if still thin
 *   3. HTML season scrape only if still thin (soccer mainly)
 */

declare(strict_types=1);

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/settings.php';
require_once __DIR__ . '/helpers.php';

const SPORT_REFRESH_MINUTES = 10;
const SPORT_BATCH_SIZE      = 6;   // leagues per request — safe on shared hosting
const SPORT_DISPLAY_DAYS    = 90;  // display window

const SPORT_KNOWN_LEAGUE_IDS = [
    'english premier league' => '4328', 'english championship' => '4396',
    'english league one' => '4397', 'english league two' => '4398',
    'english fa cup' => '4329', 'english league cup' => '4570',
    'scottish premiership' => '4330', 'scottish premier league' => '4330',
    'german bundesliga' => '4331', 'german 2. bundesliga' => '4399',
    'italian serie a' => '4332', 'italian serie b' => '4394',
    'french ligue 1' => '4334', 'french ligue 2' => '4401',
    'spanish la liga' => '4335', 'spanish segunda división' => '4400',
    'spanish segunda division' => '4400', 'spanish liga acb' => '4408',
    'uefa champions league' => '4480', 'uefa europa league' => '4481',
    'uefa conference league' => '4822', 'major league soccer' => '4346', 'mls' => '4346',
    'brazilian série a' => '4351', 'brazilian serie a' => '4351',
    'argentine primera división' => '4406', 'argentine primera division' => '4406',
    'dutch eredivisie' => '4337', 'portuguese primeira liga' => '4344',
    'belgian pro league' => '4338', 'turkish süper lig' => '4339', 'turkish super lig' => '4339',
    'nba' => '4387', 'euroleague' => '4388', 'nfl' => '4391', 'mlb' => '4424',
    'nhl' => '4380', 'khl' => '4920', 'shl' => '4419', 'cfl' => '4405',
    'formula 1' => '4370', 'formula1' => '4370', 'f1' => '4370',
    'motogp' => '4407', 'nascar cup series' => '4393', 'nascar' => '4393',
    'formula e' => '4371', 'indycar series' => '4373', 'indycar' => '4373',
    'wrc' => '4409', 'wec' => '4413', 'ufc' => '4443', 'boxing' => '4445',
    'bellator' => '4467', 'six nations' => '4714', 'super rugby' => '4551',
    'premiership rugby' => '4414', 'ipl' => '4460', 'the ashes' => '4844',
    't20 world cup' => '5103', 'pga tour' => '4425', 'dp world tour' => '4426',
    'fivb volleyball' => '5083', 'cev champions league' => '5616',
    'atp tour' => '4464', 'wta tour' => '4517', 'npb' => '4591',
    'afc champions league' => '4422', 'uefa european championship' => '4502',
];

function sport_block(): array
{
    return [
        'enabled'    => (bool) (int) settings_get('sport_enabled', '1'),
        'header'     => (string) settings_get('sport_header', 'Live & Upcoming'),
        'border'     => (string) settings_get('sport_border', '#7c3aed'),
        'background' => (string) settings_get('sport_background', '#0b0716'),
        'text'       => (string) settings_get('sport_text', '#ece9f8'),
        'days'       => max(1, min(365, (int) settings_get('sport_days', (string) SPORT_DISPLAY_DAYS))),
    ];
}

function leagues_list(): array
{
    try {
        $rows = db()->query(
            "SELECT name, league_id, COALESCE(sport_type, 'soccer') AS sport_type
             FROM leagues ORDER BY name ASC, id ASC"
        )->fetchAll();
    } catch (Throwable $e) {
        try {
            $rows = db()->query('SELECT name, league_id FROM leagues ORDER BY name ASC, id ASC')->fetchAll();
            foreach ($rows as &$r) { $r['sport_type'] = 'soccer'; }
            unset($r);
        } catch (Throwable $e2) {
            error_log('[sport] leagues_list: ' . $e2->getMessage());
            return [];
        }
    }
    $out = [];
    foreach ($rows as $r) {
        $out[] = [
            'name'       => (string) $r['name'],
            'league_id'  => (string) ($r['league_id'] ?? ''),
            'sport_type' => (string) ($r['sport_type'] ?? 'soccer'),
        ];
    }
    return $out;
}

function sport_normalize_time(string $time): string
{
    $time = trim($time);
    if ($time === '' || $time === '00:00' || $time === '00:00:00') return '';
    if (preg_match('/^(\d{1,2}):(\d{2})(?::\d{2})?$/', $time, $m)) {
        return sprintf('%02d:%02d', (int) $m[1], (int) $m[2]);
    }
    return '';
}

function sport_events_window(int $days = 7, ?string $sportType = null): array
{
    sport_auto_refresh_if_stale();

    $days  = max(1, min(365, $days));
    $today = gmdate('Y-m-d');
    $until = gmdate('Y-m-d', time() + ($days - 1) * 86400);

    try {
        $have = array_column(db()->query('PRAGMA table_info(sport_events)')->fetchAll(), 'name');
        $opt = static function (string $c) use ($have) {
            return in_array($c, $have, true) ? "se.$c" : "'' AS $c";
        };
        $badgeCols = implode(', ', array_map($opt, ['home_badge', 'away_badge', 'league_badge', 'status', 'score']));
        $hasCategory = in_array('category', $have, true);
        $hasSort = in_array('sort', $have, true);
        $catExpr = $hasCategory
            ? "COALESCE(NULLIF(se.category, ''), lg.sport_type, 'soccer')"
            : "COALESCE(lg.sport_type, 'soccer')";
        $sortCol = $hasSort ? 'se.sort' : '0';

        $sql = "SELECT se.league, se.home, se.away, se.event_date, se.event_time,
                       se.channels, $badgeCols, $catExpr AS sport_type
                  FROM sport_events se
             LEFT JOIN leagues lg ON lg.name = se.league
                 WHERE se.event_date >= ? AND se.event_date <= ?";
        $params = [$today, $until];
        if ($sportType !== null && $sportType !== '') {
            $sql .= " AND $catExpr = ?";
            $params[] = $sportType;
        }
        $sql .= " ORDER BY se.event_date ASC, se.event_time ASC, $sortCol ASC, se.id ASC";
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
    } catch (Throwable $e) {
        try {
            $stmt = db()->prepare(
                'SELECT league, home, away, event_date, event_time, channels
                 FROM sport_events WHERE event_date >= ? AND event_date <= ?
                 ORDER BY event_date ASC, event_time ASC, id ASC'
            );
            $stmt->execute([$today, $until]);
            $rows = $stmt->fetchAll();
            foreach ($rows as &$r) {
                $r['sport_type'] = 'soccer';
                $r['home_badge'] = $r['away_badge'] = $r['league_badge'] = $r['status'] = $r['score'] = '';
            }
            unset($r);
            if ($sportType !== null && $sportType !== '' && $sportType !== 'soccer') {
                $rows = [];
            }
        } catch (Throwable $e2) {
            error_log('[sport] sport_events_window: ' . $e2->getMessage());
            return [];
        }
    }

    $out = [];
    foreach ($rows as $r) {
        $channels = [];
        $raw = trim((string) ($r['channels'] ?? ''));
        if ($raw !== '') {
            foreach (preg_split('/[,;|]+/', $raw) as $c) {
                $c = trim($c);
                if ($c !== '') $channels[] = $c;
            }
        }
        $cat = (string) ($r['sport_type'] ?? 'soccer');
        if ($cat === '') $cat = 'soccer';
        $out[] = [
            'category'     => $cat,
            'league'       => (string) ($r['league'] ?? ''),
            'league_badge' => (string) ($r['league_badge'] ?? ''),
            'home'         => (string) ($r['home'] ?? ''),
            'away'         => (string) ($r['away'] ?? ''),
            'home_badge'   => (string) ($r['home_badge'] ?? ''),
            'away_badge'   => (string) ($r['away_badge'] ?? ''),
            'date'         => (string) ($r['event_date'] ?? ''),
            'time'         => sport_normalize_time((string) ($r['event_time'] ?? '')),
            'channels'     => $channels,
            'status'       => (string) ($r['status'] ?? ''),
            'score'        => (string) ($r['score'] ?? ''),
        ];
    }
    return $out;
}

function sport_normalize_league_name(string $s): string
{
    return trim(preg_replace('/\s+/', ' ', mb_strtolower($s)));
}

function sport_fetch_json(string $url): array
{
    $ch = curl_init();
    curl_setopt_array($ch, [
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_TIMEOUT => 12,
        CURLOPT_CONNECTTIMEOUT => 5,
        CURLOPT_USERAGENT => 'Mozilla/5.0 (compatible; SportImportBot/1.3)',
        CURLOPT_HTTPHEADER => ['Accept: application/json'],
    ]);
    $body = curl_exec($ch);
    $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    return ['http_code' => $code, 'raw' => $body === false ? '' : $body];
}

function sport_fetch_html(string $url): array
{
    $ch = curl_init();
    curl_setopt_array($ch, [
        CURLOPT_URL => $url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_TIMEOUT => 15,
        CURLOPT_USERAGENT => 'Mozilla/5.0 (Linux; Android 10) AppleWebKit/537.36 Chrome/120.0.0.0 Safari/537.36',
    ]);
    $body = curl_exec($ch);
    $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    return ['http_code' => $code, 'raw' => $body === false ? '' : $body];
}

function sport_slugify(string $s): string
{
    $s = mb_strtolower($s);
    $s = preg_replace('/[^a-z0-9]+/', '-', $s) ?? '';
    return trim($s, '-');
}

function sport_fetch_league_meta(string $leagueId): ?array
{
    $res = sport_fetch_json("https://www.thesportsdb.com/api/v1/json/123/lookupleague.php?id={$leagueId}");
    if ($res['http_code'] !== 200 || trim($res['raw']) === '') return null;
    $data = json_decode($res['raw'], true);
    $row = $data['leagues'][0] ?? null;
    if ($row === null) return null;
    $season = (string) ($row['strCurrentSeason'] ?? '');
    $name   = (string) ($row['strLeague'] ?? '');
    $badge  = (string) ($row['strBadge'] ?? '');
    if ($season === '' || $name === '') return null;
    return ['season' => $season, 'slug' => sport_slugify($name), 'badge' => $badge, 'name' => $name];
}


/**
 * Fetch all team badges for a league (one API call).
 * Returns map: lowercase team name => badge URL
 */
function sport_fetch_team_badges(string $leagueId): array
{
    $res = sport_fetch_json("https://www.thesportsdb.com/api/v1/json/123/lookup_all_teams.php?id={$leagueId}");
    if ($res['http_code'] !== 200 || trim($res['raw']) === '') {
        return [];
    }
    $data = json_decode($res['raw'], true);
    $map = [];
    foreach (($data['teams'] ?? []) as $team) {
        $name = trim((string) ($team['strTeam'] ?? ''));
        $badge = trim((string) ($team['strBadge'] ?? ''));
        if ($name === '' || $badge === '') {
            continue;
        }
        $map[mb_strtolower($name)] = $badge;
        // also short name if present
        $short = trim((string) ($team['strTeamShort'] ?? ''));
        if ($short !== '') {
            $map[mb_strtolower($short)] = $badge;
        }
    }
    return $map;
}

function sport_normalize_event(array $ev, string $fallbackLeagueBadge = ''): ?array
{
    $home = trim((string) ($ev['strHomeTeam'] ?? $ev['home'] ?? ''));
    $away = trim((string) ($ev['strAwayTeam'] ?? $ev['away'] ?? ''));
    $date = trim((string) ($ev['dateEvent'] ?? $ev['date'] ?? ''));
    $time = sport_normalize_time((string) ($ev['strTime'] ?? $ev['time'] ?? ''));
    $homeBadge   = trim((string) ($ev['strHomeTeamBadge'] ?? $ev['home_badge'] ?? ''));
    $awayBadge   = trim((string) ($ev['strAwayTeamBadge'] ?? $ev['away_badge'] ?? ''));
    $leagueBadge = trim((string) ($ev['strLeagueBadge'] ?? $ev['league_badge'] ?? $fallbackLeagueBadge));

    if ($home === '' && $away === '') {
        $eventName = trim((string) ($ev['strEvent'] ?? ''));
        if ($eventName === '') return null;
        $home = $eventName;
        $away = '';
    }
    if ($date === '') return null;

    return [
        'home' => $home, 'away' => $away, 'date' => $date, 'time' => $time,
        'home_badge' => $homeBadge, 'away_badge' => $awayBadge,
        'league_badge' => $leagueBadge,
        'status' => trim((string) ($ev['strStatus'] ?? '')),
        'score' => '',
    ];
}

function sport_scrape_season_table(string $leagueId, string $slug, string $season): array
{
    $url = "https://www.thesportsdb.com/season/{$leagueId}-{$slug}/" . rawurlencode($season) . '?all=1';
    $res = sport_fetch_html($url);
    if ($res['http_code'] !== 200 || trim($res['raw']) === '') return [];

    libxml_use_internal_errors(true);
    $dom = new DOMDocument();
    @$dom->loadHTML('<?xml encoding="utf-8" ?>' . $res['raw']);
    libxml_clear_errors();

    $cellText = static function (DOMElement $td): string {
        $as = $td->getElementsByTagName('a');
        if ($as->length > 0) return trim($as->item(0)->textContent);
        return trim($td->textContent);
    };

    $out = [];
    foreach ($dom->getElementsByTagName('tr') as $row) {
        $tds = $row->getElementsByTagName('td');
        if ($tds->length < 5) continue;
        $dateText = trim($tds->item(0)->textContent);
        if (!preg_match('/^\d{1,2}\s+[A-Za-z]{3}\s+\d{2}$/', $dateText)) continue;
        $dt = DateTime::createFromFormat('d M y', $dateText);
        if ($dt === false) continue;
        $home = $cellText($tds->item(2));
        $sep  = trim($tds->item(3)->textContent);
        $away = $cellText($tds->item(4));
        if ($home === '' && $away === '') continue;
        $time = '';
        if (preg_match('/^(\d{1,2}):(\d{2})/', $sep, $tm)) {
            $time = sprintf('%02d:%02d', (int) $tm[1], (int) $tm[2]);
        }
        $out[] = [
            'home' => $home, 'away' => $away, 'date' => $dt->format('Y-m-d'), 'time' => $time,
            'home_badge' => '', 'away_badge' => '', 'league_badge' => '', 'status' => '', 'score' => '',
        ];
    }
    return $out;
}

function sport_table_columns(): array
{
    static $cols = null;
    if ($cols !== null) return $cols;
    try {
        $cols = array_column(db()->query('PRAGMA table_info(sport_events)')->fetchAll(), 'name');
    } catch (Throwable $e) {
        $cols = [];
    }
    return $cols;
}

function sport_upsert_event(string $leagueName, string $sportType, array $ev): void
{
    $cols = sport_table_columns();
    $hasBadges = in_array('home_badge', $cols, true)
        && in_array('away_badge', $cols, true)
        && in_array('league_badge', $cols, true);

    $exists = db()->prepare(
        'SELECT id FROM sport_events WHERE league = ? AND home = ? AND away = ? AND event_date = ? LIMIT 1'
    );
    $exists->execute([$leagueName, $ev['home'], $ev['away'], $ev['date']]);
    $existingId = $exists->fetchColumn();

    if ($existingId) {
        if ($hasBadges) {
            db()->prepare(
                'UPDATE sport_events SET event_time=?, sport_type=?,
                 home_badge=?, away_badge=?, league_badge=?, status=? WHERE id=?'
            )->execute([
                $ev['time'], $sportType,
                $ev['home_badge'], $ev['away_badge'], $ev['league_badge'],
                $ev['status'], $existingId,
            ]);
        } else {
            db()->prepare('UPDATE sport_events SET event_time=?, sport_type=? WHERE id=?')
                ->execute([$ev['time'], $sportType, $existingId]);
        }
    } else {
        if ($hasBadges) {
            db()->prepare(
                'INSERT INTO sport_events
                 (league,home,away,event_date,event_time,channels,sort,sport_type,
                  home_badge,away_badge,league_badge,status,created_at)
                 VALUES (?,?,?,?,?,?,0,?,?,?,?,?,?)'
            )->execute([
                $leagueName, $ev['home'], $ev['away'], $ev['date'], $ev['time'],
                '', $sportType, $ev['home_badge'], $ev['away_badge'], $ev['league_badge'],
                $ev['status'], gmdate('Y-m-d H:i:s'),
            ]);
        } else {
            db()->prepare(
                'INSERT INTO sport_events
                 (league,home,away,event_date,event_time,channels,sort,sport_type,created_at)
                 VALUES (?,?,?,?,?,?,0,?,?)'
            )->execute([
                $leagueName, $ev['home'], $ev['away'], $ev['date'], $ev['time'],
                '', $sportType, gmdate('Y-m-d H:i:s'),
            ]);
        }
    }
}

/**
 * Import a single league. Fast path first.
 */
function sport_import_one_league(array $lg, string $base, string $cutoff): int
{
    $id = trim((string) $lg['league_id']);
    if ($id === '' || !ctype_digit($id)) return 0;

    $fixtures = [];
    $seen = [];
    $leagueBadge = '';
    $meta = sport_fetch_league_meta($id);
    if ($meta !== null) $leagueBadge = $meta['badge'] ?? '';

    // Team badge map for this league (fills gaps from HTML scrape)
    $teamBadges = [];
    $skipTeams = in_array($lg['sport_type'], ['motorsport', 'tennis', 'golf', 'fighting'], true);
    if (!$skipTeams) {
        $teamBadges = sport_fetch_team_badges($id);
    }

    $add = static function (array $norm) use (&$fixtures, &$seen): void {
        $key = $norm['date'] . '|' . $norm['home'] . '|' . $norm['away'];
        if (isset($seen[$key])) return;
        $seen[$key] = true;
        $fixtures[] = $norm;
    };

    // 1) Next events — always (fast, motorsport-safe)
    $res = sport_fetch_json($base . '/eventsnextleague.php?id=' . $id);
    if ($res['http_code'] === 200 && trim($res['raw']) !== '') {
        $data = json_decode($res['raw'], true);
        foreach (($data['events'] ?? []) as $ev) {
            $norm = sport_normalize_event($ev, $leagueBadge);
            if ($norm) $add($norm);
        }
    }

    // 2) Season JSON only if still thin
    if (count($fixtures) < 8 && $meta !== null) {
        $res = sport_fetch_json($base . '/eventsseason.php?id=' . $id . '&s=' . rawurlencode($meta['season']));
        if ($res['http_code'] === 200 && trim($res['raw']) !== '') {
            $data = json_decode($res['raw'], true);
            foreach (($data['events'] ?? []) as $ev) {
                $norm = sport_normalize_event($ev, $leagueBadge);
                if ($norm) $add($norm);
            }
        }
    }

    // 3) HTML scrape only if still thin (skip for known non-table sports)
    $skipScrape = in_array($lg['sport_type'], ['motorsport', 'tennis', 'golf', 'fighting', 'cricket'], true);
    if (count($fixtures) < 8 && $meta !== null && !$skipScrape) {
        foreach (sport_scrape_season_table($id, $meta['slug'], $meta['season']) as $ev) {
            if ($leagueBadge !== '' && $ev['league_badge'] === '') {
                $ev['league_badge'] = $leagueBadge;
            }
            $add($ev);
        }
    }

    $written = 0;
    foreach ($fixtures as $ev) {
        if ($ev['date'] < $cutoff) continue;
        // Fill missing team badges from league team map
        if ($ev['home_badge'] === '' && $ev['home'] !== '' && !empty($teamBadges)) {
            $key = mb_strtolower($ev['home']);
            if (isset($teamBadges[$key])) {
                $ev['home_badge'] = $teamBadges[$key];
            }
        }
        if ($ev['away_badge'] === '' && $ev['away'] !== '' && !empty($teamBadges)) {
            $key = mb_strtolower($ev['away']);
            if (isset($teamBadges[$key])) {
                $ev['away_badge'] = $teamBadges[$key];
            }
        }
        if ($ev['league_badge'] === '' && $leagueBadge !== '') {
            $ev['league_badge'] = $leagueBadge;
        }
        sport_upsert_event($lg['name'], $lg['sport_type'], $ev);
        $written++;
    }
    return $written;
}

/**
 * Batched refresh — never processes all leagues in one request.
 */
function sport_auto_refresh_if_stale(): void
{
    try {
        @set_time_limit(90);

        $last = (int) settings_get('sport_last_import', '0');
        // Allow a batch every few minutes; offset advances each time
        if (time() - $last < 120) {
            return; // at most one batch every 2 minutes
        }
        settings_set('sport_last_import', (string) time());

        // Self-heal league_ids once
        $leagues = leagues_list();
        foreach ($leagues as $lg) {
            $id = trim((string) $lg['league_id']);
            if ($id !== '' && ctype_digit($id)) continue;
            $key = sport_normalize_league_name($lg['name']);
            if (isset(SPORT_KNOWN_LEAGUE_IDS[$key])) {
                db()->prepare('UPDATE leagues SET league_id = ? WHERE name = ?')
                    ->execute([SPORT_KNOWN_LEAGUE_IDS[$key], $lg['name']]);
            }
        }
        $leagues = leagues_list();

        // Only leagues with valid numeric IDs
        $workable = array_values(array_filter(
            $leagues,
            static fn ($l) => $l['league_id'] !== '' && ctype_digit($l['league_id'])
        ));
        $total = count($workable);
        if ($total === 0) return;

        $base   = 'https://www.thesportsdb.com/api/v1/json/123';
        $cutoff = gmdate('Y-m-d', time() - 86400);
        $today  = gmdate('Y-m-d');

        // Count upcoming events per league — prioritize empty ones (F1, tennis, etc.)
        $counts = [];
        try {
            $rows = db()->query(
                "SELECT league, COUNT(*) AS c FROM sport_events
                  WHERE event_date >= '$today' GROUP BY league"
            )->fetchAll();
            foreach ($rows as $r) {
                $counts[(string) $r['league']] = (int) $r['c'];
            }
        } catch (Throwable $e) {
            $counts = [];
        }

        usort($workable, static function ($a, $b) use ($counts) {
            $ca = $counts[$a['name']] ?? 0;
            $cb = $counts[$b['name']] ?? 0;
            if ($ca === $cb) return strcasecmp($a['name'], $b['name']);
            return $ca <=> $cb; // fewest events first
        });

        $batch = array_slice($workable, 0, SPORT_BATCH_SIZE);

        foreach ($batch as $lg) {
            sport_import_one_league($lg, $base, $cutoff);
            usleep(200000);
        }

        // Keep display window sensible
        
        // Make sure the display window is wide enough
        $days = (int) settings_get('sport_days', '7');
        if ($days < 270) {
            settings_set('sport_days', '270');
        }
    } catch (Throwable $e) {
        error_log('[sport] auto_refresh: ' . $e->getMessage());
    }
}