<?php
// =============================================================================
// sports/sync.php — pull leagues + events from the active provider, upsert
// into sports_leagues / sports_events, log the run.
//
// Callable from:
//   - CLI cron: `php /www/wwwroot/<panel>/includes/sports/sync.php`
//   - HTTP "Pull now" button: api/sports/sync.php proxies to this.
//
// Respects admin overrides: any field in sports_events.override_json beats
// the feed value when the sync runs.
// =============================================================================

// Load only the DB layer — NOT bootstrap.php — so the shutdown-triggered
// background sync (called after the WebView response has flushed headers)
// doesn't try to re-start the session and emit "headers already sent" warnings.
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../helpers.php';
require_once __DIR__ . '/../error_log.php';
require_once __DIR__ . '/provider_interface.php';
require_once __DIR__ . '/provider_thesportsdb.php';
require_once __DIR__ . '/provider_sportsdb_paid.php';

// ---------------------------------------------------------------------------
// Settings helpers (live in webview_settings table)
// ---------------------------------------------------------------------------
function wv_setting(string $key, string $default = ''): string
{
    $row = db_one('SELECT value FROM webview_settings WHERE key=?', [$key]);
    return $row ? (string)$row['value'] : $default;
}
function wv_setting_set(string $key, string $value): void
{
    db_exec('INSERT INTO webview_settings (key, value, updated_at) VALUES (?, ?, ?)
             ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at',
            [$key, $value, time()]);
}

// ---------------------------------------------------------------------------
// Provider picker: webview_settings.sports_provider is 'free' (default) or 'paid'.
// ---------------------------------------------------------------------------
function sports_active_provider(): SportsProvider
{
    $which = wv_setting('sports_provider', 'free');
    if ($which === 'paid') {
        $key = wv_setting('paid_sportsdb_key', '');
        if ($key !== '') return new SportsDbPaidProvider($key);
    }
    return new TheSportsDbProvider();
}

// ---------------------------------------------------------------------------
// Upsert helpers — `source` + `source_id` is the natural key so a re-sync
// updates the row in place instead of inserting duplicates.
// ---------------------------------------------------------------------------
function sports_upsert_league(string $source, array $league): int
{
    $now = time();
    $row = db_one('SELECT id FROM sports_leagues WHERE source=? AND source_league_id=?',
                  [$source, $league['source_league_id']]);
    if ($row) {
        db_exec('UPDATE sports_leagues SET name=?, country=?, logo_url=?, updated_at=? WHERE id=?',
                [$league['name'], $league['country'], $league['logo_url'], $now, (int)$row['id']]);
        return (int)$row['id'];
    }
    return db_insert(
        'INSERT INTO sports_leagues (source, source_league_id, name, country, logo_url, sort, enabled, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        [$source, $league['source_league_id'], $league['name'],
         $league['country'], $league['logo_url'], 0, 0, $now]
    );
}

function sports_upsert_event(string $source, array $event, int $league_db_id): void
{
    $now = time();
    $row = db_one('SELECT id, override_json FROM sports_events WHERE source=? AND source_event_id=?',
                  [$source, $event['source_event_id']]);

    // Admin override: any field in override_json wins.
    $override = [];
    if ($row && !empty($row['override_json'])) {
        $decoded = json_decode($row['override_json'], true);
        if (is_array($decoded)) $override = $decoded;
    }
    $merge = function ($key) use ($event, $override) {
        return array_key_exists($key, $override) ? $override[$key] : ($event[$key] ?? null);
    };

    if ($row) {
        db_exec('UPDATE sports_events SET
                    league_id=?, home_team=?, away_team=?, home_badge_url=?, away_badge_url=?,
                    kickoff_ts=?, status=?, home_score=?, away_score=?, channels_csv=?, thumb_url=?, updated_at=?
                 WHERE id=?',
                [$league_db_id, $merge('home_team'), $merge('away_team'),
                 $merge('home_badge_url'), $merge('away_badge_url'),
                 $merge('kickoff_ts'), $merge('status'),
                 $merge('home_score'), $merge('away_score'),
                 $merge('channels_csv'),
                 array_key_exists('thumb_url', $override) ? $override['thumb_url'] : ($event['thumb_url'] ?? ''),
                 $now, (int)$row['id']]);
        return;
    }
    db_exec('INSERT INTO sports_events
                (source, source_event_id, league_id, home_team, away_team,
                 home_badge_url, away_badge_url, kickoff_ts, status,
                 home_score, away_score, channels_csv, thumb_url, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            [$source, $event['source_event_id'], $league_db_id,
             $event['home_team'], $event['away_team'],
             $event['home_badge_url'], $event['away_badge_url'],
             $event['kickoff_ts'], $event['status'],
             $event['home_score'], $event['away_score'],
             $event['channels_csv'], $event['thumb_url'] ?? '', $now]);
}

// ---------------------------------------------------------------------------
// Curated league bootstrap — if zero leagues are enabled the first time the
// admin hits "Pull now", auto-enable the football top-flights so the UI
// shows real content without requiring the admin to hunt through 600 rows.
// Match by case-insensitive name fragment so it works across providers.
// ---------------------------------------------------------------------------
function sports_default_league_patterns(): array {
    return [
        'english premier league',
        'spanish la liga',
        'la liga',
        'italian serie a',
        'serie a',
        'german bundesliga',
        'bundesliga',
        'french ligue 1',
        'ligue 1',
        'uefa champions league',
        'champions league',
        'uefa europa league',
        'europa league',
        'mls',
        'major league soccer',
        'world cup',
    ];
}

function sports_auto_enable_defaults(string $source): int {
    $patterns = sports_default_league_patterns();
    $rows = db_all('SELECT id, name FROM sports_leagues WHERE source=?', [$source]);
    $enabled = 0;
    foreach ($rows as $r) {
        $name = strtolower((string)$r['name']);
        foreach ($patterns as $p) {
            if (strpos($name, $p) !== false) {
                db_exec('UPDATE sports_leagues SET enabled=1 WHERE id=?', [(int)$r['id']]);
                $enabled++;
                break;
            }
        }
    }
    return $enabled;
}

// ---------------------------------------------------------------------------
// Main sync routine
// ---------------------------------------------------------------------------
function sports_sync_once(): array
{
    $provider = sports_active_provider();
    $source   = $provider->name();
    $log_id   = db_insert(
        'INSERT INTO sports_sync_log (provider, started_at) VALUES (?, ?)',
        [$source, time()]
    );

    try {
        // Leagues
        $leagues = $provider->fetch_leagues();
        $league_db_id_by_source = [];
        foreach ($leagues as $L) {
            $id = sports_upsert_league($source, $L);
            $league_db_id_by_source[(string)$L['source_league_id']] = $id;
        }
        $leagues_count = count($leagues);

        // First-run bootstrap: if no leagues are enabled yet, auto-enable
        // a curated default set so the admin sees real events immediately.
        $enabled_total = (int)db_one('SELECT COUNT(*) c FROM sports_leagues WHERE source=? AND enabled=1', [$source])['c'];
        $auto_enabled = 0;
        if ($enabled_total === 0 && $leagues_count > 0) {
            $auto_enabled = sports_auto_enable_defaults($source);
        }

        // Events — only for enabled leagues
        $enabled_source_ids = array_column(
            db_all('SELECT source_league_id FROM sports_leagues WHERE source=? AND enabled=1', [$source]),
            'source_league_id'
        );
        $from_ts = time() - 30 * 24 * 3600;     // 30d back (captures recent FT)
        $to_ts   = time() + 30 * 24 * 3600;     // 30d forward
        $events  = $enabled_source_ids
            ? $provider->fetch_events($enabled_source_ids, $from_ts, $to_ts)
            : [];
        $events_count = 0;
        foreach ($events as $E) {
            $lid = $league_db_id_by_source[(string)$E['source_league_id']] ?? null;
            if ($lid === null) continue;
            sports_upsert_event($source, $E, $lid);
            $events_count++;
        }

        db_exec('UPDATE sports_sync_log
                 SET finished_at=?, leagues_upserted=?, events_upserted=?, ok=1
                 WHERE id=?',
                [time(), $leagues_count, $events_count, $log_id]);
        return [
            'ok'           => true,
            'leagues'      => $leagues_count,
            'events'       => $events_count,
            'auto_enabled' => $auto_enabled,
            'enabled_now'  => count($enabled_source_ids),
            'provider'     => $source,
        ];
    } catch (Throwable $e) {
        db_exec('UPDATE sports_sync_log SET finished_at=?, error=? WHERE id=?',
                [time(), $e->getMessage(), $log_id]);
        return ['ok' => false, 'error' => $e->getMessage(), 'provider' => $source];
    }
}

// CLI entry point
if (PHP_SAPI === 'cli') {
    $r = sports_sync_once();
    echo json_encode($r, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT), PHP_EOL;
    exit($r['ok'] ? 0 : 1);
}
