<?php

declare(strict_types=1);

class CatalogueProcessor
{
    private const CRON_INTERVAL = 10;
    private const STALE_SECONDS = 300;
    private const MAX_TICK_SECONDS = 55;
    private const MAX_WORKERS = 3;
    private const WORKER_CLAIM_TTL = 30;

     
    private const AI_RETRY_DELAY = 5;
    private const AI_TOTAL_FAIL_THRESHOLD = 10;
    private const AI_MAX_FAILS = 30;
    private const AI_PAUSE_SECONDS = 60;
    private const AI_TRANSLATE_MAX_TRIES = 4;   
    private const AI_TRANSLATE_CHUNK_SIZE = 5;  
    private const AI_TRANSLATE_POOL_SIZE = 4;   

     
    private const DOWNLOAD_RETRY_MAX = 5;
    private const DOWNLOAD_RETRY_DELAY = 10;
    private const PARTIAL_MAX_AGE = 86400;

     
    private const CONSECUTIVE_NET_FAILS_PAUSE = 3;

    public static function registerCron(): void
    {
        CronManager::register('catalogue_processor', self::CRON_INTERVAL, [self::class, 'tick']);
        CronManager::register('catalogue_purchase_resume', 60, [self::class, 'resumeOrphanedPurchases']);
    }

    

    

    public static function filterMetaByIncludes(array $vm, array $inc): array
    {
        if (empty($inc['views']))       $vm['views'] = 0;
        if (empty($inc['channel']))     $vm['channel'] = null;
        if (empty($inc['categories']))  $vm['categories'] = null;
        if (empty($inc['tags']))        $vm['tags'] = null;
        if (empty($inc['performers']))  $vm['performers'] = null;
        if (empty($inc['description'])) $vm['description'] = null;
        if (empty($inc['preview']))     $vm['preview'] = null;
        return $vm;
    }

    public static function queueDownloads(array $purchased, array $meta, int $userId, string $batchId, ?array $aiSettings = null, string $selectedFormats = '[]'): int
    {
        $count = 0;
        foreach ($purchased as $item) {
            $remoteId = $item['remote_video_id'] ?? '';
            if ($remoteId === '') {
                continue;
            }

            $existing = Database::fetchOne(
                'SELECT id, video_id, status FROM catalogue_downloads WHERE remote_video_id = ?',
                [$remoteId]
            );
            if ($existing) {
                if ($existing['video_id'] !== null || in_array($existing['status'], ['pending', 'downloading'])) {
                    continue;
                }
                Database::query('DELETE FROM catalogue_downloads WHERE id = ?', [$existing['id']]);
            }

            $videoMeta = $meta[$remoteId] ?? [];

            $data = [
                'remote_video_id'      => $remoteId,
                'source_site'          => $videoMeta['source'] ?? '',
                'title'                => Sanitizer::decodeEntities($videoMeta['title'] ?? 'Untitled'),
                'description'          => Sanitizer::decodeEntities($videoMeta['description'] ?? null),
                'duration'             => (int) ($videoMeta['duration'] ?? 0),
                'thumbnail_url'        => $videoMeta['thumbnail'] ?? null,
                'thumbnail_small_url'  => $videoMeta['thumbnail_small'] ?? null,
                'preview_url'          => $videoMeta['preview'] ?? null,
                'embed_url'            => $videoMeta['embed_url'] ?? null,
                'categories'           => is_array($videoMeta['categories'] ?? null) ? json_encode($videoMeta['categories']) : ($videoMeta['categories'] ?? null),
                'tags'                 => is_array($videoMeta['tags'] ?? null) ? json_encode($videoMeta['tags']) : ($videoMeta['tags'] ?? null),
                'performers'           => is_array($videoMeta['performers'] ?? null) ? json_encode($videoMeta['performers']) : ($videoMeta['performers'] ?? null),
                'channel'              => $videoMeta['channel'] ?? null,
                'quality'              => $selectedFormats,
                'video_type'           => $videoMeta['video_type'] ?? Video::DEFAULT_TYPE,
                'views'                => (int) ($videoMeta['views'] ?? 0),
                'status'               => 'pending',
                'user_id'              => $userId,
                'batch_id'             => $batchId,
                'cost_credits'         => (float) ($item['cost'] ?? 0),
                'file_size'            => (int) ($videoMeta['file_size'] ?? 0),
                'ai_settings'          => $aiSettings !== null ? json_encode($aiSettings) : null,
                'ai_status'            => $aiSettings !== null ? 'pending' : 'none',
            ];

            if (!empty($item['download_token'])) {
                $data['download_token'] = $item['download_token'];
                $data['download_token_expires'] = $item['download_token_expires'] ?? null;
                $data['download_url'] = null;
                $data['download_url_expires'] = null;
            } else {
                $data['download_url'] = $item['download_url'] ?? null;
                $data['download_url_expires'] = !empty($item['expires']) ? $item['expires'] : null;
            }

            Database::insert('catalogue_downloads', $data);
            $count++;
        }

         
         
         
         
        if ($count > 0) {
            self::kickQueue();
        }

        return $count;
    }

    private static function asyncPing(): void
    {
        $url = rtrim(Setting::get('site_url', ''), '/') . '/';
        if ($url === '/') return;
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT_MS => 1500,
            CURLOPT_CONNECTTIMEOUT_MS => 1000,
            CURLOPT_SSL_VERIFYPEER => true,
            CURLOPT_USERAGENT => 'TubePress-Cron/1.0',
        ]);
        @curl_exec($ch);
        curl_close($ch);
    }

     

    

    public static function tick(): void
    {
        if (!self::checkServiceHealth()) {
            return;
        }

        self::recoverStalled();

        if (self::getPendingCount() === 0) {
            return;
        }

         
         
         
         
         
         
         
         
        $workerScript = APP_ROOT . '/bin/catalogue-worker.php';
        if (self::canUseCliWorkers() && is_file($workerScript)) {
            if (self::scheduleWorker() > 0 || self::liveWorkerCount() > 0) {
                return;
            }
            error_log('CatalogueProcessor: no CLI worker could be started, falling back to loopback workers');
        }

         
         
         
         
         
         
         
        self::fireLoopbackWorkers();
        if (Setting::get('catalogue_loopback_ok', '0') === '1') {
            return;  
        }

         
        $startTime = time();
        while ((time() - $startTime) < self::MAX_TICK_SECONDS) {
            $item = self::fetchNextItem();
            if (!$item) {
                return;  
            }
            $result = self::processOne($item, $startTime);
            if ($result === false || $result === 'paused') {
                break;
            }
        }
    }

    

    public static function workerLoop(int $maxSeconds = 600, ?string $leaseFile = null): string
    {
        $overallStart = time();

        while ((time() - $overallStart) < $maxSeconds) {
             
             
             
             
            if (!self::checkServiceHealth()) {
                return 'paused';
            }

            if ($leaseFile !== null) {
                @touch($leaseFile);  
            }

            $item = self::fetchNextItem();
            if (!$item) {
                return 'empty';
            }

             
            $itemStart = time();
            $result = self::processOne($item, $itemStart);
            if ($result === 'paused') {
                return 'paused';
            }
             
        }

        return 'timeout';
    }

    public static function getPendingCount(): int
    {
        $row = Database::fetchOne(
            "SELECT COUNT(*) as cnt FROM catalogue_downloads
             WHERE status IN ('pending', 'downloading')
               AND (ai_cooldown_until IS NULL OR ai_cooldown_until <= NOW())"
        );
        return (int) ($row['cnt'] ?? 0);
    }

    public static function fetchNextItem(): ?array
    {
        $pdo = Database::get();
        $pdo->beginTransaction();

        try {
             
             
             
             
             
             
             
            $stmt = $pdo->prepare(
                "SELECT * FROM catalogue_downloads
                 WHERE (
                        status = 'pending'
                        OR (status = 'downloading' AND updated_at < DATE_SUB(NOW(), INTERVAL " . self::STALE_SECONDS . " SECOND))
                       )
                   AND (ai_cooldown_until IS NULL OR ai_cooldown_until <= NOW())
                 ORDER BY
                    CASE status WHEN 'downloading' THEN 1 WHEN 'pending' THEN 2 END,
                    id ASC
                 LIMIT 1
                 FOR UPDATE SKIP LOCKED"
            );
            $stmt->execute();
            $item = $stmt->fetch(\PDO::FETCH_ASSOC);

            $pdo->commit();
            return $item ?: null;
        } catch (\Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    

    private static function scheduleWorker(): int
    {
        $phpBin = self::findPhpCli();
        $script = APP_ROOT . '/bin/catalogue-worker.php';
        if (!is_file($script) || !self::canUseCliWorkers() || $phpBin === '') {
            return 0;
        }

        $tmpDir = APP_ROOT . '/storage/tmp';
        if (!is_dir($tmpDir)) {
            @mkdir($tmpDir, 0755, true);
        }

        $pending = self::getPendingCount();
         
         
        $maxWorkers = max(1, min(self::MAX_WORKERS, (int) Setting::get('catalogue_max_workers', '3')));
        $desired = max(1, min($maxWorkers, $pending));

        $spawned = 0;

        for ($i = 0; $i < $desired; $i++) {
            $pidFile = $tmpDir . '/catalogue-worker-' . $i . '.pid';
            $unmanageable = false;

            if (file_exists($pidFile)) {
                $pid = (int) @file_get_contents($pidFile);
                if ($pid > 0 && self::workerAlive($pid)) {
                    continue;
                }
                if ($pid <= 0 && (time() - (int) @filemtime($pidFile)) < self::WORKER_CLAIM_TTL) {
                    continue;
                }
                if (!@unlink($pidFile) && file_exists($pidFile)) {
                    $unmanageable = true;
                }
            }

            if (!$unmanageable) {
                $claim = @fopen($pidFile, 'x');
                if ($claim === false) {
                    if (file_exists($pidFile)) {
                        continue;
                    }
                } else {
                    fwrite($claim, '0');
                    fclose($claim);
                }
            }

            @exec(escapeshellarg($phpBin) . ' ' . escapeshellarg($script) . ' --worker=' . $i . ' > /dev/null 2>&1 &');
            $spawned++;
        }

        return $spawned;
    }

    private static function workerAlive(int $pid): bool
    {
        if (!Env::processAlive($pid)) {
            return false;
        }

        $cmdline = @file_get_contents('/proc/' . $pid . '/cmdline');
        if ($cmdline === false || $cmdline === '') {
            return true;
        }

        return str_contains($cmdline, 'catalogue-worker.php');
    }

    public static function liveWorkerCount(): int
    {
        $tmpDir = APP_ROOT . '/storage/tmp';
        $live = 0;

        for ($i = 0; $i < self::MAX_WORKERS; $i++) {
            $pidFile = $tmpDir . '/catalogue-worker-' . $i . '.pid';
            if (!file_exists($pidFile)) {
                continue;
            }
            $pid = (int) @file_get_contents($pidFile);
            if ($pid > 0 && self::workerAlive($pid)) {
                $live++;
            }
        }

        return $live;
    }

    

    public static function ensureWorkersRunning(): void
    {
        if (!self::checkServiceHealth()) {
            return;
        }
        self::recoverStalled();
        if (self::getPendingCount() > 0) {
            if (self::canUseCliWorkers()) {
                if (self::scheduleWorker() === 0 && self::liveWorkerCount() === 0) {
                    error_log('CatalogueProcessor: no CLI worker could be started, falling back to loopback workers');
                    self::fireLoopbackWorkers();
                }
            } else {
                self::fireLoopbackWorkers();
            }
        }
    }

    

    private static function canUseCliWorkers(): bool
    {
        return Env::canSpawn() && Setting::get('catalogue_disable_cli_workers', '0') !== '1';
    }

     
     
     
     
     
     
     
     

    

    private static function isManuallyPaused(): bool
    {
        return Setting::getDirect('catalogue_service_paused', '0') === '1'
            && Setting::getDirect('catalogue_pause_reason', '') === 'manual';
    }

    private static function loopbackToken(): string
    {
        $tok = (string) Setting::get('catalogue_worker_token', '');
        if ($tok === '') {
            $tok = bin2hex(random_bytes(20));
            Setting::set('catalogue_worker_token', $tok);
        }
        return $tok;
    }

    private static function slotLease(int $slot): string
    {
        return APP_ROOT . '/storage/tmp/catalogue-loop-' . $slot . '.lease';
    }

    private static function leaseFresh(int $slot, int $maxAge = 90): bool
    {
        $f = self::slotLease($slot);
        clearstatcache(false, $f);
        return is_file($f) && (time() - (int) @filemtime($f)) < $maxAge;
    }

    

    public static function fireLoopbackWorkers(): void
    {
        $base = rtrim((string) Setting::get('site_url', ''), '/');
        if ($base === '') {
            return;
        }
        $pending = self::getPendingCount();
        if ($pending < 1 || self::isManuallyPaused()) {
            return;
        }
        $maxWorkers = max(1, min(self::MAX_WORKERS, (int) Setting::get('catalogue_max_workers', '3')));
        $desired = max(1, min($maxWorkers, $pending));
        $token = self::loopbackToken();

        $mh = curl_multi_init();
        $handles = [];
        for ($i = 0; $i < $desired; $i++) {
            if (self::leaseFresh($i)) {
                continue;  
            }
            $url = $base . '/cron/catalogue-worker?slot=' . $i . '&token=' . urlencode($token);
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_RETURNTRANSFER    => true,
                CURLOPT_TIMEOUT_MS        => 1200,
                CURLOPT_CONNECTTIMEOUT_MS => 1000,
                CURLOPT_SSL_VERIFYPEER    => true,
                CURLOPT_HTTPHEADER        => ['Accept: application/json'],
                CURLOPT_USERAGENT         => 'TubePress-QueueWorker/1.0',
            ]);
            curl_multi_add_handle($mh, $ch);
            $handles[] = $ch;
        }
        if ($handles) {
             
             
            $deadline = microtime(true) + 1.3;
            do {
                curl_multi_exec($mh, $running);
                if ($running) {
                    curl_multi_select($mh, 0.1);
                }
            } while ($running && microtime(true) < $deadline);
            foreach ($handles as $ch) {
                curl_multi_remove_handle($mh, $ch);
                curl_close($ch);
            }
        }
        curl_multi_close($mh);
    }

    

    public static function runLoopbackSlot(int $slot, string $token, bool $chain): void
    {
        $expected = (string) Setting::get('catalogue_worker_token', '');
        if ($expected === '' || !hash_equals($expected, $token)) {
            http_response_code(404);
            echo 'Not found';
            exit;
        }
        $maxWorkers = max(1, min(self::MAX_WORKERS, (int) Setting::get('catalogue_max_workers', '3')));
        if ($slot < 0 || $slot >= $maxWorkers) {
            http_response_code(204);
            exit;
        }
         
         
        if (!$chain && self::leaseFresh($slot)) {
            http_response_code(204);
            exit;
        }
        if (self::isManuallyPaused()) {
            http_response_code(204);
            exit;
        }

        $lease = self::slotLease($slot);
        $dir = dirname($lease);
        if (!is_dir($dir)) {
            @mkdir($dir, 0755, true);
        }
        @touch($lease);

         
         
        if (Setting::get('catalogue_loopback_ok', '0') !== '1') {
            Setting::set('catalogue_loopback_ok', '1');
        }

         
        ignore_user_abort(true);
        @set_time_limit(0);
        http_response_code(204);
        header('X-Robots-Tag: noindex');
        while (ob_get_level() > 0) {
            @ob_end_clean();
        }
        if (function_exists('fastcgi_finish_request')) {
            fastcgi_finish_request();
        } else {
            flush();
        }
        if (function_exists('session_write_close')) {
            @session_write_close();
        }

         
         
        $reason = self::workerLoop(45, $lease);

        if ($reason === 'timeout' && self::getPendingCount() > 0 && self::isServiceHealthy()) {
            @touch($lease);
            self::fireLoopbackChain($slot);  
        } else {
            @unlink($lease);  
        }
        exit;
    }

     
    private static function fireLoopbackChain(int $slot): void
    {
        $base = rtrim((string) Setting::get('site_url', ''), '/');
        if ($base === '') {
            @unlink(self::slotLease($slot));
            return;
        }
        $url = $base . '/cron/catalogue-worker?slot=' . $slot . '&token=' . urlencode(self::loopbackToken()) . '&chain=1';
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER    => true,
            CURLOPT_TIMEOUT_MS        => 1200,
            CURLOPT_CONNECTTIMEOUT_MS => 1000,
            CURLOPT_SSL_VERIFYPEER    => true,
            CURLOPT_HTTPHEADER        => ['Accept: application/json'],
            CURLOPT_USERAGENT         => 'TubePress-QueueWorker/1.0',
        ]);
        @curl_exec($ch);
        curl_close($ch);
    }

    

    public static function kickQueue(): void
    {
        try {
            if (self::canUseCliWorkers()) {
                self::ensureWorkersRunning();
            } else {
                self::fireLoopbackWorkers();
            }
        } catch (\Throwable $e) {
            error_log('CatalogueProcessor::kickQueue: ' . $e->getMessage());
        }
        self::asyncPing();
    }

    

    public static function runPurchaseJob(string $batchId, ?int $maxSeconds = null): bool
    {
        $tmpDir = APP_ROOT . '/storage/tmp';
        $jobFile = $tmpDir . '/purchase-job-' . $batchId . '.json';
        $progressFile = $tmpDir . '/purchase-progress-' . $batchId . '.json';
        if (!file_exists($jobFile)) return true;
        $job = json_decode((string) file_get_contents($jobFile), true);
        if (!$job) { @unlink($jobFile); return true; }

        $videoIds          = $job['video_ids'] ?? [];
        $aiSettings        = $job['ai_settings'] ?? null;
        $aiCreditsPerVideo = (float) ($job['ai_credits_per_video'] ?? 0);
        $userId            = (int) ($job['user_id'] ?? 0);
        $inc = $job['includes'] ?? ['views' => !empty($job['include_views']), 'channel' => true, 'categories' => true, 'tags' => true, 'performers' => true, 'description' => true, 'preview' => true];
        $selectedFormats   = $job['selected_formats'] ?? json_encode(array_keys(TranscodeManager::getFormats()));

        $chunksCompleted = 0;
        if (file_exists($progressFile)) {
            $p = json_decode((string) file_get_contents($progressFile), true);
            $chunksCompleted = (int) ($p['chunks_completed'] ?? 0);
        }

        $chunks = array_chunk($videoIds, 500);
        $totalChunks = count($chunks);
        $newBalance = null;
        $start = time();

        for ($i = $chunksCompleted; $i < $totalChunks; $i++) {
            if ($maxSeconds !== null && (time() - $start) >= $maxSeconds) {
                return false;  
            }
            $chunk = $chunks[$i];
            $result = CatalogueClient::purchase($chunk, $aiCreditsPerVideo, 120, $aiSettings);
            if ($result === null) { sleep(3); $result = CatalogueClient::purchase($chunk, $aiCreditsPerVideo, 120, $aiSettings); }

            if ($result === null || !empty($result['_error'])) {
                if (($result['_http_code'] ?? 0) === 402) break;  
                 
                file_put_contents($progressFile, json_encode(['chunks_completed' => $i, 'total_chunks' => $totalChunks]));
                return false;
            }

            $purchased = $result['purchased'] ?? [];
            if (!empty($purchased)) {
                $metaLookup = [];
                foreach (CatalogueClient::getByIds($chunk) as $vm) {
                    $rid = (string) ($vm['remote_video_id'] ?? '');
                    if ($rid !== '') {
                        $vm = self::filterMetaByIncludes($vm, $inc);
                        $metaLookup[$rid] = $vm;
                    }
                }
                self::queueDownloads($purchased, $metaLookup, $userId, $batchId, $aiSettings, $selectedFormats);
            }
            if (isset($result['new_balance'])) $newBalance = $result['new_balance'];
            file_put_contents($progressFile, json_encode(['chunks_completed' => $i + 1, 'total_chunks' => $totalChunks]));
        }

        if ($newBalance !== null) {
            Setting::set('catalogue_balance_cache', (string) $newBalance);
            Setting::set('catalogue_balance_updated', date('Y-m-d H:i:s'));
        }
        @unlink($jobFile);
        @unlink($progressFile);
        @unlink($tmpDir . '/purchase-pid-' . $batchId . '.pid');
        return true;
    }

    

    public static function resumeOrphanedPurchases(): void
    {
        $tmpDir = APP_ROOT . '/storage/tmp';
        if (!is_dir($tmpDir)) {
            return;
        }

        $jobFiles = glob($tmpDir . '/purchase-job-*.json');
        if (empty($jobFiles)) {
            return;
        }

        $script = APP_ROOT . '/bin/catalogue-purchase.php';
        $canSpawn = Env::canSpawn() && is_file($script);
        $phpBin = $canSpawn ? self::findPhpCli() : '';

        foreach ($jobFiles as $jobFile) {
             
            if (!preg_match('/purchase-job-([a-f0-9]+)\.json$/', $jobFile, $m)) {
                continue;
            }
            $batchId = $m[1];

             
            $pidFile = $tmpDir . '/purchase-pid-' . $batchId . '.pid';
            if (file_exists($pidFile)) {
                $pid = (int) @file_get_contents($pidFile);
                if (Env::processAlive($pid)) {
                    continue;  
                }
                @unlink($pidFile);
            }

             
            if (filemtime($jobFile) > time() - 30) {
                continue;
            }

            if ($canSpawn) {
                error_log("CatalogueProcessor: Resuming orphaned purchase job: $batchId");
                @exec(escapeshellarg($phpBin) . ' ' . escapeshellarg($script) . ' ' . escapeshellarg($batchId) . ' > /dev/null 2>&1 &');
            } else {
                 
                 
                error_log("CatalogueProcessor: Processing purchase job inline (no exec): $batchId");
                self::runPurchaseJob($batchId, self::MAX_TICK_SECONDS);
            }
            break;  
        }
    }

     

    

    private static function checkServiceHealth(): bool
    {
         
         
        $paused = Setting::getDirect('catalogue_service_paused', '0') === '1';

        if ($paused) {
            $reason = Setting::getDirect('catalogue_pause_reason', '');

             
            if ($reason === 'manual') {
                return false;
            }

             
            if ($reason === 'api_error') {
                $pauseUntil = Setting::getDirect('catalogue_pause_until', '');
                if ($pauseUntil !== '' && time() < strtotime($pauseUntil)) {
                    return false;  
                }
                Setting::set('catalogue_service_paused', '0');
                Setting::set('catalogue_pause_reason', '');
                Setting::set('catalogue_pause_until', '');
                error_log('CatalogueProcessor: API error cooldown expired (20 min), retrying');
                return true;
            }

             
            if (self::pingHealth()) {
                Setting::set('catalogue_service_paused', '0');
                Setting::set('catalogue_pause_reason', '');
                Setting::set('catalogue_net_fail_count', '0');
                error_log('CatalogueProcessor: Service health restored, resuming');
                return true;
            }
            return false;
        }
        return true;
    }

    

    private static function isServiceHealthy(): bool
    {
        return Setting::getDirect('catalogue_service_paused', '0') !== '1';
    }

    private static function pingHealth(): bool
    {
        return CatalogueClient::pingHealth();
    }

    private static function trackNetworkFailure(): void
    {
         
        $existing = Database::fetchOne("SELECT id FROM settings WHERE setting_key = 'catalogue_net_fail_count'");
        if ($existing) {
            Database::query(
                "UPDATE settings SET setting_value = CAST(setting_value AS UNSIGNED) + 1 WHERE setting_key = 'catalogue_net_fail_count'"
            );
        } else {
            try {
                Database::insert('settings', ['setting_key' => 'catalogue_net_fail_count', 'setting_value' => '1', 'autoload' => 0]);
            } catch (\PDOException) {
                Database::query(
                    "UPDATE settings SET setting_value = CAST(setting_value AS UNSIGNED) + 1 WHERE setting_key = 'catalogue_net_fail_count'"
                );
            }
        }
        Setting::clearCache();

        $count = (int) Setting::getDirect('catalogue_net_fail_count', '0');

        if ($count >= self::CONSECUTIVE_NET_FAILS_PAUSE) {
            if (!self::pingHealth()) {
                Setting::set('catalogue_service_paused', '1');
                Setting::set('catalogue_pause_reason', 'network');
                error_log("CatalogueProcessor: {$count} consecutive network failures, service paused");
            } else {
                self::resetNetworkFailures();
            }
        }
    }

    private static function pauseForApiError(string $error): void
    {
        $until = date('Y-m-d H:i:s', time() + 1200);  
        Setting::set('catalogue_service_paused', '1');
        Setting::set('catalogue_pause_reason', 'api_error');
        Setting::set('catalogue_pause_until', $until);
        error_log("CatalogueProcessor: API config error, pausing until {$until}: {$error}");
    }

    private static function resetNetworkFailures(): void
    {
        if ((int) Setting::getDirect('catalogue_net_fail_count', '0') > 0) {
            Setting::set('catalogue_net_fail_count', '0');
        }
    }

    private static function isNetworkError(string $error): bool
    {
        return (bool) preg_match('/timed?\s*out|CURLE_OPERATION_TIMEDOUT|DNS|resolve|getaddrinfo|cURL error|Connection refused|SSL|certificate/i', $error);
    }

     

    

    private static function processOne(array $item, int $startTime): bool|string
    {
        $id = (int) $item['id'];

         
        if (MediaStorage::isAllFull()) {
            Database::query("UPDATE catalogue_downloads SET status = 'pending', error_message = 'Storage full — waiting for space' WHERE id = ?", [$id]);
            return 'paused';
        }

        if (!self::acquireLock($id, $item['updated_at'])) {
            return true;
        }

         
        $externalId = 'cat_' . ($item['remote_video_id'] ?? '');
        if ($externalId !== 'cat_') {
            $existing = Database::fetchOne('SELECT id FROM videos WHERE external_id = ?', [$externalId]);
            if ($existing) {
                $busy = (int)(Database::fetchOne(
                    "SELECT COUNT(*) as c FROM transcode_jobs WHERE video_id = ? AND status IN ('pending','processing')",
                    [(int)$existing['id']]
                )['c'] ?? 0);
                if ($busy > 0) {
                    Database::query("UPDATE catalogue_downloads SET status = 'completed', video_id = ? WHERE id = ?", [(int)$existing['id'], $id]);
                    return true;
                }
            }
        }

        Database::query(
            "UPDATE catalogue_downloads SET status = 'downloading', started_at = COALESCE(started_at, NOW()) WHERE id = ?",
            [$id]
        );

         
        $folder = $item['video_folder'] ?? '';
        $downloadComplete = false;
        if ($folder !== '') {
            $videoDir = VideoAsset::dir($folder);
             
             
             
             
             
             
            $srcMaster = $videoDir . '/video.mp4';
            $path720 = $videoDir . '/video_720.mp4';
            $path480 = $videoDir . '/video_480.mp4';
            if (is_dir($videoDir) && (
                (file_exists($srcMaster) && filesize($srcMaster) > 10240) ||
                (file_exists($path720) && filesize($path720) > 1000) ||
                (file_exists($path480) && filesize($path480) > 1000)
            )) {
                $downloadComplete = true;
            }
        }

         
        $targetStorageId = MediaStorage::activeDefaultId();

        if (!$downloadComplete) {
             
            $folder = VideoAsset::createFolder();
            $videoDir = VideoAsset::dir($folder);
            if (!is_dir($videoDir)) {
                mkdir($videoDir, 0755, true);
            }

             
            $dlResult = self::downloadAllAssets($item, $folder, $id, $targetStorageId > 0 ? $targetStorageId : null);
            if ($dlResult === 'paused') {
                return 'paused';
            }
            if ($dlResult === false) {
                return true;  
            }

             
            Database::query("UPDATE catalogue_downloads SET video_folder = ? WHERE id = ?", [$folder, $id]);
        } else {
            $videoDir = VideoAsset::dir($folder);
        }

         
        $totalFileSize = 0;
        $srcMaster = $videoDir . '/video.mp4';
        if (file_exists($srcMaster)) {
            $totalFileSize += filesize($srcMaster);
        }
        $path720 = $videoDir . '/video_720.mp4';
        if (file_exists($path720)) {
            $totalFileSize += filesize($path720);
        }
        $path480 = $videoDir . '/video_480.mp4';
        if (file_exists($path480)) {
            $totalFileSize += filesize($path480);
        }

         
        $aiSettings = null;
        if (!empty($item['ai_settings'])) {
            $aiSettings = is_string($item['ai_settings']) ? json_decode($item['ai_settings'], true) : $item['ai_settings'];
            if (is_array($aiSettings) && !empty($aiSettings['enabled'])) {
                $aiCooldownUntil = $item['ai_cooldown_until'] ?? null;
                if ($aiCooldownUntil !== null && self::coolingDown($aiCooldownUntil)) {
                    Database::query("UPDATE catalogue_downloads SET status = 'pending' WHERE id = ?", [$id]);
                    return true;
                }

                $aiResult = self::runAiPipeline($item, $aiSettings, $id, $startTime);
                if ($aiResult === null) {
                    return true;  
                }
                if ($aiResult === false) {
                    return false;  
                }
                if ($aiResult === 'paused') {
                    return 'paused';  
                }
                $item = $aiResult;
            }
        }

         
        try {
            $isNewVideo = false;
            $videoId = self::createVideoRecord($item, $folder, true, $targetStorageId > 0 ? $targetStorageId : null, $isNewVideo);

            Database::query(
                "UPDATE catalogue_downloads SET status = 'completed', video_id = ?, completed_at = NOW(), file_size = ? WHERE id = ?",
                [$videoId, $totalFileSize, $id]
            );

            if ($aiSettings !== null && !empty($aiSettings['translate']) && !empty($item['_ai_translations'])) {
                self::saveTranslations($videoId, $item['_ai_translations']);
            }

            if ($aiSettings !== null && !empty($aiSettings['translate_description']) && !empty($item['_ai_desc_translations'])) {
                self::saveDescriptionTranslations($videoId, $item['_ai_desc_translations'], $item['title'] ?? '');
            }

            $transcodeQueued = 0;
            if (TranscodeManager::isEnabled()) {
                $formatTasks = json_decode($item['quality'] ?? 'null', true);
                 
                if (is_array($formatTasks)) {
                    if (!empty($formatTasks)) {
                        $transcodeQueued = TranscodeManager::queue($videoId, $formatTasks);
                        if ($transcodeQueued > 0) {
                            self::markProcessing($videoId, $isNewVideo);

                             
                             
                             
                             
                             
                             
                             
                            $allFormats = TranscodeManager::getFormats();
                            $maxKeptHeight = 0;
                            foreach ($formatTasks as $t) {
                                if (isset($allFormats[$t]['h'])) {
                                    $maxKeptHeight = max($maxKeptHeight, (int) $allFormats[$t]['h']);
                                }
                            }
                            $srcH = (int) (Database::fetchOne('SELECT source_height FROM videos WHERE id = ?', [$videoId])['source_height'] ?? 0);
                            if ($maxKeptHeight > 0 && $srcH > $maxKeptHeight) {
                                Database::query("UPDATE videos SET cleanup_source = 1 WHERE id = ?", [$videoId]);
                            }
                        }
                    }
                     
                } else {
                     
                    $transcodeQueued = TranscodeManager::queue($videoId);
                    if ($transcodeQueued > 0) {
                        self::markProcessing($videoId, $isNewVideo);
                    }
                }
            }

             
             
             
             
            if ($targetStorageId > 0 && MediaStorage::isRemote($targetStorageId) && $transcodeQueued === 0) {
                VideoAsset::purgeLocalFolder($folder);
            }
        } catch (\Throwable $e) {
            self::markFailed($id, $item, 'Video record creation failed: ' . $e->getMessage());
        }

        return true;
    }

     

    

    private static function downloadAllAssets(array $item, string $folder, int $id, ?int $storageId = null): bool|string
    {
        $videoDir = VideoAsset::dir($folder);
        $remoteId = $item['remote_video_id'] ?? '';

        $totalBytes = 0;

         
         
         
        $hbLast = time();
        $heartbeat = function () use ($id, &$hbLast) {
            $now = time();
            if ($now - $hbLast >= 15) {
                $hbLast = $now;
                try { Database::query("UPDATE catalogue_downloads SET updated_at = NOW() WHERE id = ?", [$id]); } catch (\Throwable $e) {}
            }
        };

         
        $r = self::downloadAssetWithRetry('video', $id, $item, function () use ($remoteId, $videoDir, $heartbeat) {
            $refreshed = CatalogueClient::refreshToken((int) $remoteId);
            if ($refreshed === null || empty($refreshed['download_token'])) {
                return ['success' => false, 'bytes' => 0, 'error' => 'Could not refresh download token'];
            }
             
             
             
            $part = $videoDir . '/video.mp4.part';
            $resumeFrom = 0;
            clearstatcache(true, $part);
            if (is_file($part)) {
                if (filemtime($part) > time() - self::PARTIAL_MAX_AGE) {
                    $resumeFrom = (int) filesize($part);
                } else {
                    @unlink($part);
                }
            }
            $res = CatalogueClient::downloadWithToken($refreshed['download_token'], $part, '', $heartbeat, $resumeFrom);
            if (!empty($res['success']) && is_file($part) && filesize($part) > 1000) {
                @rename($part, $videoDir . '/video.mp4');
            } elseif (empty($res['resumable'])) {
                @unlink($part);
            }
            return $res;
        });
        if ($r === 'paused') {
            return 'paused';
        }
        if (!$r) {
            return false;
        }

        $videoPath = $videoDir . '/video.mp4';
        if (!file_exists($videoPath) || filesize($videoPath) < 1000) {
            self::markFailed($id, $item, 'Downloaded video file is empty or too small');
            @unlink($videoPath);
            return false;
        }

        $totalBytes += filesize($videoPath);
        Database::query("UPDATE catalogue_downloads SET bytes_downloaded = ? WHERE id = ?", [$totalBytes, $id]);

        $sid = $storageId ?? MediaStorage::activeDefaultId();
        if ($sid > 0 && MediaStorage::isRemote($sid)) {
            $uploaded = MediaStorage::put($videoPath, 'uploads/videos/' . $folder . '/video.mp4', $sid);
            if (!$uploaded) {
                $why = MediaStorage::lastError();
                self::markFailed($id, $item, 'Failed to upload video.mp4 to storage server' . ($why !== null ? ' (' . $why . ')' : ''));
                return false;
            }
             
             
        }

         
        $freshMedia = null;
        if ($remoteId !== '') {
            $freshMedia = self::fetchFreshMediaUrls($remoteId);
        }

        $downloadThumbs = Setting::get('catalogue_download_thumbnails', '1') === '1';

         
        if ($downloadThumbs && !empty($item['thumbnail_url'])) {
            $r = self::downloadAssetWithRetry('thumb', $id, $item, function () use ($item, $folder, $freshMedia, $storageId) {
                $data = self::fetchMediaData($item['thumbnail_url']);
                if ($data === '' && $freshMedia && !empty($freshMedia['thumbnail'])) {
                    $data = self::fetchMediaData($freshMedia['thumbnail']);
                }
                if ($data !== '') {
                    VideoAsset::saveRaw($folder, 'thumb.jpg', $data, $storageId);
                    return ['success' => true, 'bytes' => strlen($data), 'error' => null];
                }
                return ['success' => false, 'bytes' => 0, 'error' => 'Thumbnail download failed'];
            });
            if ($r === 'paused') {
                return 'paused';
            }
            if (!$r) {
                return false;
            }
        }

         
        $smallUrl = $item['thumbnail_small_url'] ?? '';
        if ($smallUrl === '' && $freshMedia && !empty($freshMedia['thumbnail_small'])) {
            $smallUrl = $freshMedia['thumbnail_small'];
        }
        if ($downloadThumbs && $smallUrl !== '') {
            $r = self::downloadAssetWithRetry('thumb_small', $id, $item, function () use ($smallUrl, $folder, $freshMedia, $storageId) {
                $data = self::fetchMediaData($smallUrl);
                if ($data === '' && $freshMedia && !empty($freshMedia['thumbnail_small'])) {
                    $data = self::fetchMediaData($freshMedia['thumbnail_small']);
                }
                if ($data !== '') {
                    VideoAsset::saveRaw($folder, 'thumb_small.jpg', $data, $storageId);
                    return ['success' => true, 'bytes' => strlen($data), 'error' => null];
                }
                return ['success' => false, 'bytes' => 0, 'error' => 'Small thumbnail download failed'];
            });
            if ($r === 'paused') {
                return 'paused';
            }
            if (!$r) {
                return false;
            }
        }

         
        $previewUrl = $item['preview_url'] ?? '';
        if ($previewUrl === '' && $freshMedia && !empty($freshMedia['preview'])) {
            $previewUrl = $freshMedia['preview'];
        }
        if ($previewUrl !== '') {
            $r = self::downloadAssetWithRetry('preview', $id, $item, function () use ($previewUrl, $folder, $freshMedia, $storageId) {
                $data = self::fetchMediaData($previewUrl, 30, 1000);
                if ($data === '' && $freshMedia && !empty($freshMedia['preview'])) {
                    $data = self::fetchMediaData($freshMedia['preview'], 30, 1000);
                }
                if ($data !== '') {
                    VideoAsset::saveRaw($folder, 'preview.mp4', $data, $storageId);
                    return ['success' => true, 'bytes' => strlen($data), 'error' => null];
                }
                return ['success' => false, 'bytes' => 0, 'error' => 'Preview download failed'];
            });
            if ($r === 'paused') {
                return 'paused';
            }
            if (!$r) {
                return false;
            }
        }

        self::resetNetworkFailures();
        return true;
    }

    

    private static function downloadAssetWithRetry(string $assetName, int $id, array $item, callable $downloader): bool|string
    {
        for ($attempt = 1; $attempt <= self::DOWNLOAD_RETRY_MAX; $attempt++) {
            $result = $downloader();

            if ($result['success']) {
                return true;
            }

            $error = $result['error'] ?? 'Download failed';
            error_log("CatalogueProcessor: {$assetName} attempt #{$attempt} failed for #{$id}: {$error}");

            if (self::isNetworkError($error)) {
                self::trackNetworkFailure();
                if (!self::isServiceHealthy()) {
                    Database::query("UPDATE catalogue_downloads SET status = 'pending' WHERE id = ?", [$id]);
                    return 'paused';
                }
            }

            if ($attempt < self::DOWNLOAD_RETRY_MAX) {
                sleep(self::DOWNLOAD_RETRY_DELAY);
            }
        }

        self::markFailed($id, $item, "{$assetName} download failed after " . self::DOWNLOAD_RETRY_MAX . " retries");
        return false;
    }

     

    

    private static function runAiPipeline(array $item, array $aiSettings, int $id, int $startTime): array|null|false|string
    {
        $step = (int) ($item['ai_pipeline_step'] ?? 0);
        $totalFails = (int) ($item['ai_fail_count'] ?? 0);
        $cfg = self::loadAiConfig();
        $title = $item['title'] ?? '';
        $description = $item['description'] ?? '';

        if ($step === 0) {
             
            $item['_original_title'] = $title;
            $item['_original_description'] = $description;
            Database::query(
                "UPDATE catalogue_downloads SET original_title = ?, original_description = ? WHERE id = ?",
                [$title, $description, $id]
            );
        } else {
             
            $item['_original_title'] = $item['original_title'] ?? $title;
            $item['_original_description'] = $item['original_description'] ?? $description;
        }

         
         
         
        if (empty($item['_ai_translations']) && !empty($item['ai_title_translations'])) {
            $prev = json_decode((string) $item['ai_title_translations'], true);
            if (is_array($prev) && $prev !== []) {
                $item['_ai_translations'] = $prev;
            }
        }
        if (empty($item['_ai_desc_translations']) && !empty($item['ai_desc_translations'])) {
            $prev = json_decode((string) $item['ai_desc_translations'], true);
            if (is_array($prev) && $prev !== []) {
                $item['_ai_desc_translations'] = $prev;
            }
        }

        Database::query("UPDATE catalogue_downloads SET ai_status = 'processing' WHERE id = ?", [$id]);

         
        if ($step < 1 && !empty($aiSettings['rewrite_title']) && $title !== '') {
            $result = self::runAiStep(
                fn () => self::aiRewriteTitle($item, $aiSettings, $cfg),
                $id, $totalFails, $startTime
            );
            if ($result === 'failed') {
                return $item;
            }
            if ($result === null || $result === false || $result === 'paused') {
                return $result;
            }
            [$item, $totalFails] = $result;
            Database::query("UPDATE catalogue_downloads SET ai_pipeline_step = 1, title = ? WHERE id = ?", [$item['title'], $id]);
        }

         
         
         
        $needTranslate = ($step < 2 && !empty($aiSettings['translate']) && !empty($aiSettings['translate_languages']));
        $needDesc = ($step < 3 && !empty($aiSettings['rewrite_description']) && $description !== '');

         
        if ($step < 2 && $needTranslate) {
            $result = self::runAiStep(
                fn () => self::aiTranslateTitle($item, $aiSettings, $cfg),
                $id, $totalFails, $startTime
            );
            if ($result === 'failed') {
                return $item;
            }
            if ($result === null || $result === false || $result === 'paused') {
                return $result;
            }
            [$item, $totalFails] = $result;
            Database::query("UPDATE catalogue_downloads SET ai_pipeline_step = 2 WHERE id = ?", [$id]);
        }

         
        if ($step < 3 && $needDesc) {
            $result = self::runAiStep(
                fn () => self::aiRewriteDescription($item, $aiSettings, $cfg),
                $id, $totalFails, $startTime
            );
            if ($result === 'failed') {
                return $item;
            }
            if ($result === null || $result === false || $result === 'paused') {
                return $result;
            }
            [$item, $totalFails] = $result;
            Database::query("UPDATE catalogue_downloads SET ai_pipeline_step = 3 WHERE id = ?", [$id]);
        }
         
        $needTranslateDesc = ($step < 4 && !empty($aiSettings['translate_description'])
            && !empty($aiSettings['translate_languages']) && ($item['description'] ?? '') !== '');
        if ($needTranslateDesc) {
            $result = self::runAiStep(
                fn () => self::aiTranslateDescription($item, $aiSettings, $cfg),
                $id, $totalFails, $startTime
            );
            if ($result === 'failed') {
                return $item;
            }
            if ($result === null || $result === false || $result === 'paused') {
                return $result;
            }
            [$item, $totalFails] = $result;
            Database::query("UPDATE catalogue_downloads SET ai_pipeline_step = 4 WHERE id = ?", [$id]);
        }

         
        Database::query(
            "UPDATE catalogue_downloads SET ai_status = 'completed', ai_fail_count = 0, ai_cooldown_until = NULL, ai_pipeline_step = 4, title = ? WHERE id = ?",
            [$item['title'], $id]
        );

        return $item;
    }

    

    private static function runAiStep(callable $step, int $id, int $totalFails, int $startTime): array|null|false|string
    {
        while (true) {
            if ((time() - $startTime) >= self::MAX_TICK_SECONDS) {
                Database::query("UPDATE catalogue_downloads SET status = 'pending' WHERE id = ?", [$id]);
                return false;
            }

            try {
                $newItem = $step();
                self::resetNetworkFailures();
                return [$newItem, $totalFails];
            } catch (\Throwable $e) {
                $totalFails++;
                $msg = $e->getMessage();
                error_log("CatalogueProcessor: AI fail #{$totalFails} for #{$id}: {$msg}");
                Database::query(
                    "UPDATE catalogue_downloads SET ai_fail_count = ?, ai_error = ? WHERE id = ?",
                    [$totalFails, substr($msg, 0, 500), $id]
                );

                 
                if (str_contains($msg, 'API error HTTP 400') || str_contains($msg, 'API error HTTP 401')) {
                    self::pauseForApiError($msg);
                    Database::query("UPDATE catalogue_downloads SET status = 'pending' WHERE id = ?", [$id]);
                    return 'paused';
                }

                 
                if (str_contains($msg, 'returned null')) {
                    self::trackNetworkFailure();
                    if (!self::isServiceHealthy()) {
                        Database::query("UPDATE catalogue_downloads SET status = 'pending' WHERE id = ?", [$id]);
                        return 'paused';
                    }
                }

                if ($totalFails >= self::AI_MAX_FAILS) {
                    Database::query(
                        "UPDATE catalogue_downloads SET ai_status = 'failed', ai_error = ?, ai_cooldown_until = NULL WHERE id = ?",
                        [substr($msg, 0, 500), $id]
                    );
                    error_log("CatalogueProcessor: AI gave up for #{$id} after {$totalFails} failures, importing the original text");
                    return 'failed';
                }

                if (($totalFails % self::AI_TOTAL_FAIL_THRESHOLD) === 0) {
                    Database::query(
                        "UPDATE catalogue_downloads SET status = 'pending', ai_status = 'pending', ai_cooldown_until = DATE_ADD(NOW(), INTERVAL ? SECOND) WHERE id = ?",
                        [self::AI_PAUSE_SECONDS, $id]
                    );
                    error_log("CatalogueProcessor: AI failed {$totalFails}x for #{$id}, cooldown " . self::AI_PAUSE_SECONDS . "s");
                    return null;
                }

                sleep(self::AI_RETRY_DELAY);
            }
        }
    }

    

    private static function applyFormatToken(string $prompt, string $formatLines): string
    {
         
         
        return str_replace('{format}', $formatLines, $prompt);
    }

    

    private static function aiRewriteTitle(array $item, array $ai, array $cfg): array
    {
        $title = $item['title'] ?? '';
        $userPrompt = $ai['title_prompt'] ?? '';
        if ($userPrompt === '') {
            return $item;
        }

        $begin = str_replace('{title}', $title, $cfg['ai_title_begin'] ?? '');
        $end = $cfg['ai_title_end'] ?? '';
        $fullPrompt = trim($begin . "\n\n" . $userPrompt . ($end !== '' ? "\n\n" . $end : ''));

        $result = CatalogueClient::aiCompletion(
            [['role' => 'user', 'content' => $fullPrompt]],
            'auto',
            0.7
        );
        $content = self::extractAiContent($result);
        if ($content === null) {
            throw new \RuntimeException('AI returned null for title rewrite');
        }

        if (!preg_match('/<title>(.*?)<\/title>/si', $content, $m)) {
            throw new \RuntimeException('AI title missing <title> XML format');
        }
        $rewritten = trim($m[1], " \t\n\r\0\x0B\"'");
        if (mb_strlen($rewritten) < 5 || mb_strlen($rewritten) > 200) {
            throw new \RuntimeException('AI title rejected (len=' . mb_strlen($rewritten) . ')');
        }
        $item['title'] = $rewritten;

        return $item;
    }

    

    private static function aiTranslateTitle(array $item, array $ai, array $cfg): array
    {
        if (($ai['translate_prompt'] ?? '') === '' || empty($ai['translate_languages'])) {
            return $item;
        }
        $already = is_array($item['_ai_translations'] ?? null) ? $item['_ai_translations'] : [];
        $persist = null;
        if (!empty($item['id'])) {
            $rowId = (int) $item['id'];
            $persist = static function (array $tr) use ($rowId): void {
                Database::query(
                    'UPDATE catalogue_downloads SET ai_title_translations = ? WHERE id = ?',
                    [json_encode($tr, JSON_UNESCAPED_UNICODE), $rowId]
                );
            };
        }
        $translations = self::translateInChunks('title', $item, $ai, $cfg, $already, $persist);
        if (!empty($translations)) {
            $item['_ai_translations'] = $translations;
        }
        return $item;
    }

    

    private static function aiRewriteDescription(array $item, array $ai, array $cfg): array
    {
        $description = $item['description'] ?? '';
        $userPrompt = $ai['desc_prompt'] ?? '';
        if ($userPrompt === '') {
            return $item;
        }

        $begin = str_replace('{description}', $description, $cfg['ai_desc_begin'] ?? '');
        $end = $cfg['ai_desc_end'] ?? '';
        $fullPrompt = trim($begin . "\n\n" . $userPrompt . ($end !== '' ? "\n\n" . $end : ''));

        $result = CatalogueClient::aiCompletion(
            [['role' => 'user', 'content' => $fullPrompt]],
            'auto',
            0.7
        );
        $content = self::extractAiContent($result);
        if ($content === null) {
            throw new \RuntimeException('AI returned null for description rewrite');
        }

        if (!preg_match('/<desc>(.*?)<\/desc>/si', $content, $m)) {
            throw new \RuntimeException('AI description missing <desc> XML format');
        }
        $rewritten = trim($m[1]);
        if (mb_strlen($rewritten) < 10) {
            throw new \RuntimeException('AI desc rejected (len=' . mb_strlen($rewritten) . ')');
        }
        $item['description'] = $rewritten;

        return $item;
    }

    

    private static function aiTranslateDescription(array $item, array $ai, array $cfg): array
    {
        if (($ai['translate_desc_prompt'] ?? '') === '' || empty($ai['translate_languages']) || ($item['description'] ?? '') === '') {
            return $item;
        }
        $already = is_array($item['_ai_desc_translations'] ?? null) ? $item['_ai_desc_translations'] : [];
        $persist = null;
        if (!empty($item['id'])) {
            $rowId = (int) $item['id'];
            $persist = static function (array $tr) use ($rowId): void {
                Database::query(
                    'UPDATE catalogue_downloads SET ai_desc_translations = ? WHERE id = ?',
                    [json_encode($tr, JSON_UNESCAPED_UNICODE), $rowId]
                );
            };
        }
        $translations = self::translateInChunks('desc', $item, $ai, $cfg, $already, $persist);
        if (!empty($translations)) {
            $item['_ai_desc_translations'] = $translations;
        }
        return $item;
    }

    

    private static function translateFormatLines(array $langs): string
    {
        $lines = [];
        foreach ($langs as $l) {
            $code = strtoupper(trim((string) ($l['code'] ?? '')));
            if ($code === '') {
                continue;
            }
            $name = trim((string) ($l['name'] ?? ''));
            $lines[] = ($name !== '' ? $name : $code) . ': <' . $code . '></' . $code . '>';
        }
        return implode("\n", $lines);
    }

     
    private static function buildTranslateChunkPrompt(string $kind, array $item, array $ai, array $cfg, array $chunk): ?string
    {
        $formatLines = self::translateFormatLines($chunk);
        if ($formatLines === '') {
            return null;
        }
        if ($kind === 'desc') {
            $userPrompt = $ai['translate_desc_prompt'] ?? '';
            if ($userPrompt === '' || ($item['description'] ?? '') === '') {
                return null;
            }
            $begin = str_replace('{description}', $item['description'], $cfg['ai_translate_desc_begin'] ?? 'Description: {description}');
            $end = $cfg['ai_translate_desc_end'] ?? '';
        } else {
            $userPrompt = $ai['translate_prompt'] ?? '';
            if ($userPrompt === '') {
                return null;
            }
            $begin = str_replace('{title}', $item['title'], $cfg['ai_translate_begin'] ?? '');
            $end = $cfg['ai_translate_end'] ?? '';
        }
        return trim(self::applyFormatToken($begin . "\n\n" . $userPrompt . ($end !== '' ? "\n\n" . $end : ''), $formatLines));
    }

    

    private static function translateInChunks(string $kind, array $item, array $ai, array $cfg, array $already, ?callable $persist): array
    {
        $translations = $already;
        $allLangs = [];
        foreach (($ai['translate_languages'] ?? []) as $l) {
            $code = strtolower(trim((string) (is_array($l) ? ($l['code'] ?? '') : $l)));
            if ($code !== '' && !isset($allLangs[$code])) {
                $allLangs[$code] = ['code' => $code, 'name' => (string) (is_array($l) ? ($l['name'] ?? $code) : $l)];
            }
        }
        if ($allLangs === []) {
            return $translations;
        }

        for ($round = 0; $round < self::AI_TRANSLATE_MAX_TRIES; $round++) {
            $missing = array_values(array_filter($allLangs, fn ($l) => !isset($translations[$l['code']])));
            if ($missing === []) {
                break;
            }

            $chunks = array_chunk($missing, self::AI_TRANSLATE_CHUNK_SIZE);
            $requests = [];
            foreach ($chunks as $ci => $chunk) {
                $prompt = self::buildTranslateChunkPrompt($kind, $item, $ai, $cfg, $chunk);
                if ($prompt === null) {
                    return $translations;  
                }
                $requests[$ci] = ['messages' => [['role' => 'user', 'content' => $prompt]], 'temperature' => 0.5];
            }

            $gotAny = false;
            CatalogueClient::aiCompletionPool($requests, function (int $ci, ?array $result) use (&$translations, &$gotAny, $chunks, $persist): void {
                $content = self::extractAiContent($result);
                if ($content === null) {
                    return;
                }
                $parsed = self::parseTranslationXml($content, $chunks[$ci]);
                if ($parsed !== []) {
                    $gotAny = true;
                    $translations = $parsed + $translations;
                    if ($persist !== null) {
                        $persist($translations);  
                    }
                }
            }, self::AI_TRANSLATE_POOL_SIZE);

            if (!$gotAny) {
                if ($round === 0 && $translations === $already && $already === []) {
                     
                    throw new \RuntimeException('AI returned null for ' . ($kind === 'desc' ? 'description' : 'title') . ' translation');
                }
                break;  
            }
        }

        if (count($translations) < count($allLangs)) {
            $missing = array_diff(array_keys($allLangs), array_keys($translations));
            error_log('CatalogueProcessor: ' . $kind . ' translation kept ' . count($translations) . '/' . count($allLangs) . ' languages, still missing: ' . implode(', ', $missing));
        }
        return $translations;
    }

     

    private static function markProcessing(int $videoId, bool $isNewVideo): void
    {
        Database::query(
            $isNewVideo
                ? "UPDATE videos SET status = 'processing', published_at = NULL WHERE id = ? AND status IN ('published','draft')"
                : "UPDATE videos SET status = 'processing' WHERE id = ? AND status IN ('published','draft')",
            [$videoId]
        );
    }

    private static function createVideoRecord(array $item, string $folder, bool $skipMediaDownload = false, ?int $storageId = null, bool &$isNew = false): int
    {
        $isNew = false;
        $autoPublish = Setting::get('catalogue_auto_publish', '1') === '1';
        $downloadThumbs = Setting::get('catalogue_download_thumbnails', '1') === '1';

        $externalId = 'cat_' . $item['remote_video_id'];

        if (!$skipMediaDownload) {
            self::downloadMediaAssets($item, $folder, $downloadThumbs);
        }

        $thumbnail = '';
        if (!file_exists(VideoAsset::dir($folder) . '/thumb.jpg') && !empty($item['thumbnail_url'])) {
            $thumbnail = $item['thumbnail_url'];
        }

        $existingVideo = Database::fetchOne('SELECT id, video_folder FROM videos WHERE external_id = ?', [$externalId]);
        if ($existingVideo) {
            $videoId = (int) $existingVideo['id'];
            $oldFolder = $existingVideo['video_folder'] ?? '';
            if ($oldFolder !== '' && $oldFolder !== $folder) {
                VideoAsset::deleteFolder($oldFolder);
                Database::query("DELETE FROM transcode_jobs WHERE video_id = ?", [$videoId]);
            }
            $updateData = ['video_folder' => $folder];
            if ($thumbnail !== '') $updateData['thumbnail'] = $thumbnail;
            if ($storageId !== null && $storageId > 0) $updateData['storage_id'] = $storageId;
            Database::update('videos', $updateData, 'id = ?', [$videoId]);
            self::syncMetadata($videoId, $item);
            return $videoId;
        }

        $description = $item['description'] ?? '';
        if ($description === '') {
            $remoteId = $item['remote_video_id'] ?? '';
            if ($remoteId !== '') {
                $freshMedia = self::fetchFreshMediaUrls($remoteId);
                if ($freshMedia && !empty($freshMedia['description'])) {
                    $description = $freshMedia['description'];
                }
            }
        }

        $cleanTitle = Sanitizer::decodeEntities($item['title']) ?? 'Untitled';
        $data = [
            'title'        => $cleanTitle,
            'description'  => Sanitizer::decodeEntities($description),
            'slug'         => Slug::unique('videos', $cleanTitle),
            'embed_code'   => $item['embed_url'] ?? '',
            'video_folder' => $folder,
            'thumbnail'    => $thumbnail !== '' ? $thumbnail : null,
            'duration'     => (int) $item['duration'],
            'views'        => (int) ($item['views'] ?? 0),
            'likes'        => 0,
            'video_type'   => $item['video_type'] ?? Video::DEFAULT_TYPE,
            'status'       => $autoPublish ? 'published' : 'draft',
            'published_at' => $autoPublish ? date('Y-m-d H:i:s') : null,
            'user_id'      => (int) $item['user_id'],
            'external_id'  => $externalId,
        ];

        if ($storageId !== null) {
            $data['storage_id'] = $storageId;
        }

        if (isset($item['_original_title']) && $item['_original_title'] !== $item['title']) {
            $data['original_title'] = $item['_original_title'];
        }
        if (isset($item['_original_description']) && $item['_original_description'] !== $description) {
            $data['original_description'] = $item['_original_description'];
        }

        try {
            $videoId = Video::create($data);
        } catch (\PDOException $e) {
            if ($e->getCode() === '23000') {
                $existing = Database::fetchOne('SELECT id, video_folder FROM videos WHERE external_id = ?', [$externalId]);
                if ($existing) {
                    $videoId = (int) $existing['id'];
                    $oldFolder = $existing['video_folder'] ?? '';
                    if ($oldFolder !== '' && $oldFolder !== $folder) {
                        VideoAsset::deleteFolder($oldFolder);
                    }
                    Database::query(
                        'UPDATE videos SET video_folder = ?, thumbnail = ? WHERE id = ?',
                        [$folder, $thumbnail !== '' ? $thumbnail : null, $videoId]
                    );
                    self::syncMetadata($videoId, $item);
                    return $videoId;
                }
            }
            throw $e;
        }

        $isNew = true;
        self::syncMetadata($videoId, $item);
        return $videoId;
    }

    private static function syncMetadata(int $videoId, array $item): void
    {
        $videoType = $item['video_type'] ?? Video::DEFAULT_TYPE;

        if (!empty($item['categories'])) {
            $names = self::parseList($item['categories']);
            $ids = [];
            foreach ($names as $name) {
                $ids[] = self::findOrCreateCategory($name, $videoType);
            }
            if ($ids) {
                Video::syncCategories($videoId, $ids);
            }
        }

        if (!empty($item['tags'])) {
            $names = self::parseList($item['tags']);
            $ids = [];
            foreach ($names as $name) {
                $ids[] = Tag::findOrCreate($name, $videoType);
            }
            if ($ids) {
                Video::syncTags($videoId, $ids);
            }
        }

        if (!empty($item['performers'])) {
            $names = self::parseList($item['performers']);
            $ids = [];
            foreach ($names as $name) {
                $ids[] = self::findOrCreatePerformer($name, $videoType);
            }
            if ($ids) {
                Video::syncPerformers($videoId, $ids);
            }
        }

         
        if (!empty($item['channel'])) {
            $channelName = trim((string) $item['channel']);
            if ($channelName !== '') {
                $channelId = self::findOrCreateChannel($channelName, $videoType);
                if ($channelId) {
                    Video::syncChannels($videoId, [$channelId]);
                }
                 
                 
                if (Setting::get('channels_as_users', '1') === '1') {
                    $channelUserId = self::findOrCreateChannelUser($channelName);
                    if ($channelUserId) {
                        Database::query('UPDATE videos SET user_id = ? WHERE id = ?', [$channelUserId, $videoId]);
                    }
                }
            }
        }
    }

    private static function downloadMediaAssets(array $item, string $folder, bool $downloadThumbs): void
    {
        $freshMedia = null;
        $remoteId = $item['remote_video_id'] ?? '';
        if ($remoteId !== '') {
            $freshMedia = self::fetchFreshMediaUrls($remoteId);
        }

        if ($downloadThumbs && !empty($item['thumbnail_url'])) {
            $data = self::fetchMediaData($item['thumbnail_url']);
            if ($data === '' && $freshMedia && !empty($freshMedia['thumbnail'])) {
                $data = self::fetchMediaData($freshMedia['thumbnail']);
            }
            if ($data !== '') {
                VideoAsset::saveRaw($folder, 'thumb.jpg', $data);
            }
        }

        if ($downloadThumbs) {
            $smallUrl = $item['thumbnail_small_url'] ?? '';
            if ($smallUrl === '' && $freshMedia && !empty($freshMedia['thumbnail_small'])) {
                $smallUrl = $freshMedia['thumbnail_small'];
            }
            if ($smallUrl !== '') {
                $data = self::fetchMediaData($smallUrl);
                if ($data === '' && $freshMedia && !empty($freshMedia['thumbnail_small'])) {
                    $data = self::fetchMediaData($freshMedia['thumbnail_small']);
                }
                if ($data !== '') {
                    VideoAsset::saveRaw($folder, 'thumb_small.jpg', $data);
                }
            }
        }

        $previewUrl = $item['preview_url'] ?? '';
        if ($previewUrl === '' && $freshMedia && !empty($freshMedia['preview'])) {
            $previewUrl = $freshMedia['preview'];
        }
        if ($previewUrl !== '') {
            $data = self::fetchMediaData($previewUrl, 30, 1000);
            if ($data === '' && $freshMedia && !empty($freshMedia['preview'])) {
                $data = self::fetchMediaData($freshMedia['preview'], 30, 1000);
            }
            if ($data !== '') {
                VideoAsset::saveRaw($folder, 'preview.mp4', $data);
            }
        }
    }

     

    private static function findOrCreateCategory(string $name, string $videoType = Video::DEFAULT_TYPE): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM categories WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
        if ($existing) {
            return (int) $existing['id'];
        }
        try {
            return Database::insert('categories', ['name' => trim($name), 'slug' => $slug, 'video_type' => $videoType]);
        } catch (\PDOException $e) {
            if ($e->getCode() === '23000') {
                $existing = Database::fetchOne('SELECT id FROM categories WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
                if ($existing) {
                    return (int) $existing['id'];
                }
            }
            throw $e;
        }
    }

    private static function findOrCreateChannel(string $name, string $videoType = Video::DEFAULT_TYPE): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM channels WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
        if ($existing) {
            return (int) $existing['id'];
        }
        try {
            return Database::insert('channels', ['name' => trim($name), 'slug' => $slug, 'video_type' => $videoType]);
        } catch (\PDOException $e) {
            if ($e->getCode() === '23000') {
                $existing = Database::fetchOne('SELECT id FROM channels WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
                if ($existing) {
                    return (int) $existing['id'];
                }
            }
            throw $e;
        }
    }

     
    private static function findOrCreateChannelUser(string $name): int
    {
        if (!class_exists('User')) { require_once APP_ROOT . '/src/Models/User.php'; }
        return User::findOrCreateForChannel($name);
    }

    private static function findOrCreatePerformer(string $name, string $videoType = Video::DEFAULT_TYPE): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM performers WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
        if ($existing) {
            return (int) $existing['id'];
        }
        try {
            return Database::insert('performers', ['name' => trim($name), 'slug' => $slug, 'video_type' => $videoType]);
        } catch (\PDOException $e) {
            if ($e->getCode() === '23000') {
                $existing = Database::fetchOne('SELECT id FROM performers WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
                if ($existing) {
                    return (int) $existing['id'];
                }
            }
            throw $e;
        }
    }

    private static function markFailed(int $id, array $item, string $error): void
    {
        $error = self::sanitizeErrorMessage($error);
        $attempts = (int) $item['attempt_count'] + 1;
        $maxAttempts = (int) ($item['max_attempts'] ?: 3);

        if ($attempts < $maxAttempts) {
            Database::query(
                "UPDATE catalogue_downloads SET status = 'pending', attempt_count = ?, error_message = ? WHERE id = ?",
                [$attempts, $error, $id]
            );
        } else {
            Database::query(
                "UPDATE catalogue_downloads SET status = 'failed', attempt_count = ?, error_message = ?, completed_at = NOW() WHERE id = ?",
                [$attempts, $error, $id]
            );
        }
    }

    public static function sanitizeErrorMessage(string $error): string
    {
        $patterns = [
            '/SQLSTATE\[23000\]/'                     => 'This video already exists in your library.',
            '/HTTP 403|Access denied|Forbidden/i'     => 'Access denied. The download link may have expired.',
            '/HTTP 404|Not Found/i'                   => 'Video file not found on the source server.',
            '/timed?\s*out|CURLE_OPERATION_TIMEDOUT/i' => 'Connection timed out. Will retry automatically.',
            '/too small|empty or too small/i'         => 'Downloaded file was empty or corrupted.',
            '/HTTP 5\d{2}/i'                          => 'The source server is temporarily unavailable.',
            '/Could not (obtain|refresh)/i'           => 'Could not obtain a fresh download link. Please retry later.',
            '/SSL|certificate/i'                      => 'Secure connection error with the source server.',
            '/DNS|resolve|getaddrinfo/i'              => 'Could not connect to the source server.',
        ];

        foreach ($patterns as $pattern => $message) {
            if (preg_match($pattern, $error)) {
                return $message;
            }
        }

        $cleaned = preg_replace('/[A-Z][a-zA-Z]+\\\\[A-Za-z\\\\]+:?\s*/', '', $error);
        $cleaned = preg_replace('/#\d+\s+.*$/m', '', $cleaned);
        $cleaned = preg_replace('#/[a-zA-Z0-9/_.-]+\.php(:\d+)?#', '', $cleaned);
        $cleaned = trim(preg_replace('/\s+/', ' ', $cleaned));

        if (strlen($cleaned) > 200) {
            $cleaned = substr($cleaned, 0, 197) . '...';
        }

        return $cleaned !== '' ? $cleaned : 'An unexpected error occurred.';
    }

    private static function acquireLock(int $id, string $currentUpdatedAt): bool
    {
        $affected = Database::query(
            "UPDATE catalogue_downloads SET updated_at = NOW() WHERE id = ? AND updated_at = ?",
            [$id, $currentUpdatedAt]
        )->rowCount();

        return $affected > 0;
    }

    private static function coolingDown(string $until): bool
    {
        $row = Database::fetchOne('SELECT ? > NOW() AS still_cooling', [$until]);
        return !empty($row['still_cooling']);
    }

    private static function recoverStalled(): void
    {
        Database::query(
            "UPDATE catalogue_downloads SET status = 'pending'
             WHERE status = 'downloading'
               AND updated_at < DATE_SUB(NOW(), INTERVAL ? SECOND)",
            [self::STALE_SECONDS]
        );
    }

     

    public static function getRecentDownloads(int $limit = 50): array
    {
        return Database::fetchAll(
            "SELECT * FROM catalogue_downloads
             ORDER BY
                CASE status
                    WHEN 'downloading' THEN 1
                    WHEN 'pending'     THEN 2
                    WHEN 'failed'      THEN 3
                    WHEN 'completed'   THEN 4
                    WHEN 'cancelled'   THEN 5
                END,
                id DESC
             LIMIT ?",
            [$limit]
        );
    }

    public static function getDownloads(string $status = '', string $search = '', string $batch = '', int $limit = 100): array
    {
        $where = [];
        $params = [];

        if ($status !== '' && in_array($status, ['pending', 'downloading', 'completed', 'failed', 'cancelled'])) {
            $where[] = 'status = ?';
            $params[] = $status;
        } elseif ($status === 'active') {
            $where[] = "status IN ('pending', 'downloading')";
        }

        if ($search !== '') {
            $where[] = '(title LIKE ? OR source_site LIKE ?)';
            $params[] = '%' . $search . '%';
            $params[] = '%' . $search . '%';
        }

        if ($batch !== '') {
            $where[] = 'batch_id = ?';
            $params[] = $batch;
        }

        $sql = "SELECT * FROM catalogue_downloads";
        if ($where) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= " ORDER BY
            CASE status
                WHEN 'downloading' THEN 1
                WHEN 'pending'     THEN 2
                WHEN 'failed'      THEN 3
                WHEN 'completed'   THEN 4
                WHEN 'cancelled'   THEN 5
            END,
            id DESC
            LIMIT ?";
        $params[] = $limit;

        $rows = Database::fetchAll($sql, $params);

         
         
        $heights = [];
        $vidIds = array_values(array_filter(array_map(static fn($r) => (int)($r['video_id'] ?? 0), $rows)));
        if ($vidIds) {
            $ph = implode(',', array_fill(0, count($vidIds), '?'));
            foreach (Database::fetchAll("SELECT id, source_height FROM videos WHERE id IN ($ph)", $vidIds) as $vr) {
                $heights[(int)$vr['id']] = (int)($vr['source_height'] ?? 0);
            }
        }

         
         
        foreach ($rows as &$row) {
            $row['src_height'] = !empty($row['video_id']) ? ($heights[(int)$row['video_id']] ?? 0) : 0;
            if (!empty($row['thumbnail_url'])) {
                $row['thumbnail_url'] = CatalogueClient::mediaProxyUrl((string) $row['thumbnail_url'], 'thumb');
            }
        }
        unset($row);
        return $rows;
    }

    public static function getBatches(): array
    {
        return Database::fetchAll(
            "SELECT batch_id,
                    COUNT(*) as total,
                    SUM(status = 'completed') as completed,
                    SUM(status = 'failed') as failed,
                    SUM(status IN ('pending', 'downloading')) as active,
                    SUM(status = 'downloading') as downloading,
                    SUM(status = 'pending') as pending,
                    SUM(status = 'cancelled') as cancelled,
                    SUM(cost_credits) as total_cost,
                    SUM(file_size) as total_size,
                    SUM(bytes_downloaded) as total_bytes_downloaded,
                    MAX(source_site) as source_site,
                    MIN(created_at) as created_at
             FROM catalogue_downloads
             WHERE batch_id IS NOT NULL
             GROUP BY batch_id
             ORDER BY MIN(created_at) DESC
             LIMIT 50"
        );
    }

    public static function getQueueStats(): array
    {
        $rows = Database::fetchAll(
            "SELECT status, COUNT(*) as cnt FROM catalogue_downloads GROUP BY status"
        );
        $stats = ['pending' => 0, 'downloading' => 0, 'completed' => 0, 'failed' => 0, 'cancelled' => 0];
        foreach ($rows as $row) {
            $stats[$row['status']] = (int) $row['cnt'];
        }
        return $stats;
    }

    public static function cancelDownload(int $id): bool
    {
        $affected = Database::query(
            "UPDATE catalogue_downloads SET status = 'cancelled', completed_at = NOW()
             WHERE id = ? AND status IN ('pending', 'downloading')",
            [$id]
        )->rowCount();
        return $affected > 0;
    }

    public static function retryDownload(int $id): bool
    {
        $affected = Database::query(
            "UPDATE catalogue_downloads SET status = 'pending', attempt_count = 0, error_message = NULL
             WHERE id = ? AND status = 'failed'",
            [$id]
        )->rowCount();
        return $affected > 0;
    }

    public static function cancelAll(): int
    {
        return Database::query(
            "UPDATE catalogue_downloads SET status = 'cancelled', completed_at = NOW()
             WHERE status IN ('pending', 'downloading')"
        )->rowCount();
    }

    public static function cancelBatch(string $batchId): int
    {
        return Database::query(
            "UPDATE catalogue_downloads SET status = 'cancelled', completed_at = NOW()
             WHERE batch_id = ? AND status IN ('pending', 'downloading')",
            [$batchId]
        )->rowCount();
    }

    public static function retryBatch(string $batchId): int
    {
        return Database::query(
            "UPDATE catalogue_downloads SET status = 'pending', attempt_count = 0, error_message = NULL
             WHERE batch_id = ? AND status = 'failed'",
            [$batchId]
        )->rowCount();
    }

    public static function retryAllFailed(): int
    {
        return Database::query(
            "UPDATE catalogue_downloads SET status = 'pending', attempt_count = 0, error_message = NULL
             WHERE status = 'failed'"
        )->rowCount();
    }

    

    public static function deleteDownload(int $id): int
    {
         
         
        return Database::query(
            "DELETE FROM catalogue_downloads WHERE id = ? AND status IN ('completed', 'failed', 'cancelled')",
            [$id]
        )->rowCount();
    }

     
    public static function deleteBatch(string $batchId): int
    {
        return Database::query(
            "DELETE FROM catalogue_downloads WHERE batch_id = ? AND status IN ('completed', 'failed', 'cancelled')",
            [$batchId]
        )->rowCount();
    }

    

    public static function clearDownloads(string $status): int
    {
        if ($status === 'finished') {
            return Database::query(
                "DELETE FROM catalogue_downloads WHERE status IN ('completed', 'failed', 'cancelled')"
            )->rowCount();
        }
        if (!in_array($status, ['completed', 'failed', 'cancelled'], true)) {
            return 0;
        }
        return Database::query(
            "DELETE FROM catalogue_downloads WHERE status = ?",
            [$status]
        )->rowCount();
    }

    public static function getImportedGalleryIds(array $galleryIds): array
    {
        if (empty($galleryIds)) {
            return [];
        }
        $externalIds = [];
        foreach ($galleryIds as $gid) {
            $externalIds[] = 'catgal-' . (int) $gid;
        }
        $placeholders = implode(',', array_fill(0, count($externalIds), '?'));
        $rows = Database::fetchAll(
            "SELECT external_id FROM albums WHERE external_id IN ({$placeholders})",
            $externalIds
        );
        $ids = [];
        foreach ($rows as $row) {
            $ids[] = (int) substr((string) $row['external_id'], 7);
        }
        return $ids;
    }

    public static function getExistingRemoteIds(array $remoteIds): array
    {
        if (empty($remoteIds)) {
            return [];
        }
        $remoteIds = array_values($remoteIds);
        $placeholders = implode(',', array_fill(0, count($remoteIds), '?'));
        $rows = Database::fetchAll(
            "SELECT remote_video_id FROM catalogue_downloads
             WHERE remote_video_id IN ({$placeholders})
               AND (video_id IS NOT NULL OR status IN ('pending','downloading'))",
            $remoteIds
        );
        $ids = array_column($rows, 'remote_video_id');




        $externalIds = [];
        foreach ($remoteIds as $rid) {
            $rid = (string) $rid;
            if ($rid !== '') {
                $externalIds[] = 'cat_' . $rid;
            }
        }
        if ($externalIds) {
            $placeholders = implode(',', array_fill(0, count($externalIds), '?'));
            $rows = Database::fetchAll(
                "SELECT external_id FROM videos WHERE external_id IN ({$placeholders})",
                $externalIds
            );
            foreach ($rows as $row) {
                $ids[] = substr((string) $row['external_id'], 4);
            }
        }

        return array_values(array_unique($ids));
    }



    public static function getAllImportedRemoteIds(int $limit = 2000): array
    {
        $limit = max(1, min(5000, $limit));
        $rows = Database::fetchAll(
            "SELECT remote_video_id FROM catalogue_downloads
             WHERE remote_video_id IS NOT NULL AND remote_video_id <> ''
               AND (video_id IS NOT NULL OR status IN ('pending','downloading'))
             ORDER BY id DESC
             LIMIT {$limit}"
        );
        $ids = array_column($rows, 'remote_video_id');



        $rows = Database::fetchAll(
            "SELECT external_id FROM videos
             WHERE external_id LIKE 'cat\\_%'
             ORDER BY id DESC
             LIMIT {$limit}"
        );
        foreach ($rows as $row) {
            $rid = substr((string) $row['external_id'], 4);
            if ($rid !== '') {
                $ids[] = $rid;
            }
        }

        return array_slice(array_values(array_unique($ids)), 0, $limit);
    }

    public static function getAllImportedGalleryIds(int $limit = 2000): array
    {
        $limit = max(1, min(5000, $limit));
        $rows = Database::fetchAll(
            "SELECT external_id FROM albums
             WHERE external_id LIKE 'catgal-%'
             ORDER BY id DESC
             LIMIT {$limit}"
        );
        $ids = [];
        foreach ($rows as $row) {
            $gid = (int) substr((string) $row['external_id'], 7);
            if ($gid > 0) {
                $ids[] = $gid;
            }
        }
        return $ids;
    }

     

    private static function parseList(string $value): array
    {
        if ($value === '') {
            return [];
        }
        $decoded = json_decode($value, true);
        if (is_array($decoded)) {
            $names = [];
            foreach ($decoded as $entry) {
                if (is_string($entry)) {
                    $names[] = trim($entry);
                } elseif (is_array($entry) && isset($entry['name'])) {
                    $names[] = trim($entry['name']);
                }
            }
            return array_filter($names, fn($v) => $v !== '');
        }
        $names = preg_split('/[,;|]+/', $value);
        return array_filter(array_map('trim', $names), fn($v) => $v !== '');
    }

    

    private static function findPhpCli(): string
    {
        return Env::phpCli();
    }

    private static function fetchMediaData(string $url, int $timeout = 10, int $minSize = 100): string
    {
        $data = null;

        if (preg_match('#/admin/catalogue/media/(thumb|thumb_small|preview)/(.+)$#', $url, $m)) {
            $type = $m[1];
            $token = $m[2];
            $media = CatalogueClient::fetchMedia($type, $token);
            if ($media && !empty($media['body'])) {
                $data = $media['body'];
            }
        }

         
         
        if ($data === null && preg_match('#/admin/catalogue/cdn/(.+)$#', $url, $cm)) {
            $cdnPath = CatalogueClient::unpackCdnRef($cm[1]);
            if ($cdnPath !== null) {
                $ch = curl_init('https://cdn.tubepress.io' . $cdnPath);
                curl_setopt_array($ch, [
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_TIMEOUT        => $timeout,
                    CURLOPT_CONNECTTIMEOUT => $timeout,
                    CURLOPT_FOLLOWLOCATION => true,
                    CURLOPT_MAXREDIRS      => 5,
                    CURLOPT_USERAGENT      => 'TubePress/1.0',
                    CURLOPT_SSL_VERIFYPEER => true,
                    CURLOPT_SSL_VERIFYHOST => 2,
                ]);
                $resp = curl_exec($ch);
                $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
                curl_close($ch);
                $data = ($resp !== false && $code < 400) ? $resp : null;
            }
        }

        if ($data === null && str_starts_with($url, 'http')) {
             
            $parsed = parse_url($url);
            $scheme = $parsed['scheme'] ?? '';
            $host = $parsed['host'] ?? '';
            if (in_array($scheme, ['http', 'https'], true) && $host !== '') {
                $ip = gethostbyname($host);
                if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
                     
                     
                    $ch = curl_init($url);
                    curl_setopt_array($ch, [
                        CURLOPT_RETURNTRANSFER => true,
                        CURLOPT_TIMEOUT        => $timeout,
                        CURLOPT_CONNECTTIMEOUT => $timeout,
                        CURLOPT_USERAGENT      => 'TubePress/1.0',
                        CURLOPT_FOLLOWLOCATION => false,
                        CURLOPT_SSL_VERIFYPEER => true,
                        CURLOPT_SSL_VERIFYHOST => 2,
                        CURLOPT_RESOLVE        => [$host . ':443:' . $ip, $host . ':80:' . $ip],
                    ]);
                    $resp = curl_exec($ch);
                    $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
                    curl_close($ch);
                    $data = ($resp !== false && $code < 400) ? $resp : null;
                }
            }
        }

        if ($data === false || $data === null || strlen($data) < $minSize) {
            return '';
        }

        return $data;
    }

     

    private static ?array $aiConfig = null;
    private static int $aiConfigLoadedAt = 0;
    private const AI_CONFIG_TTL = 120;  

    private static function loadAiConfig(): array
    {
        if (self::$aiConfig === null || (time() - self::$aiConfigLoadedAt) >= self::AI_CONFIG_TTL) {
            self::$aiConfig = CatalogueClient::getAiConfig() ?? [
                'ai_title_begin'     => 'Title: {title}',
                'ai_title_end'       => "DO NOT keep series numbers!\nReturn the title in the XML format:\n\n<title>title</title>",
                'ai_translate_begin' => 'Title: {title}',
                'ai_translate_end'   => "Return the translated title in this EXACT format — one XML tag per language, the translation inside each tag, nothing else (no language name, no quotes, no commentary):\n{format}",
                'ai_translate_desc_begin' => 'Description: {description}',
                'ai_translate_desc_end'   => "Return the translated description in this EXACT format — one XML tag per language, the translation inside each tag, nothing else (no language name, no quotes, no commentary):\n{format}",
                'ai_desc_begin'      => 'Description to be rewritten: {description}',
                'ai_desc_end'        => "Return the rewritten description in the XML format:\n\n<desc>description</desc>",
            ];
            self::$aiConfigLoadedAt = time();
        }
        return self::$aiConfig;
    }

    private static function extractAiContent(?array $result): ?string
    {
        if ($result === null) {
            return null;
        }
        if (!empty($result['_error'])) {
            $code = $result['_http_code'] ?? 0;
            throw new \RuntimeException("API error HTTP {$code}: " . ($result['error'] ?? $result['message'] ?? 'unknown'));
        }
        return $result['choices'][0]['message']['content'] ?? null;
    }

    private static function parseTranslationXml(string $content, array $languages): array
    {
        $translations = [];
        foreach ($languages as $lang) {
            $code = strtoupper($lang['code'] ?? '');
            if ($code === '') {
                continue;
            }
            if (preg_match('/<' . preg_quote($code, '/') . '>(.*?)<\/' . preg_quote($code, '/') . '>/si', $content, $m)) {
                $text = trim($m[1]);
                if ($text !== '') {
                    $translations[strtolower($code)] = $text;
                }
            }
        }
        return $translations;
    }

    private static function saveTranslations(int $videoId, array $translations): void
    {
        foreach ($translations as $langCode => $title) {
            $slug = Slug::uniqueVideoTranslation($title, $langCode, $videoId);
            $existing = Database::fetchOne(
                'SELECT id FROM video_translations WHERE video_id = ? AND lang_code = ?',
                [$videoId, $langCode]
            );
            if ($existing) {
                Database::query(
                    'UPDATE video_translations SET title = ?, slug = ? WHERE id = ?',
                    [$title, $slug, (int) $existing['id']]
                );
            } else {
                Database::insert('video_translations', [
                    'video_id'  => $videoId,
                    'lang_code' => $langCode,
                    'title'     => $title,
                    'slug'      => $slug,
                ]);
            }
        }
    }

    

    private static function saveDescriptionTranslations(int $videoId, array $translations, string $sourceTitle): void
    {
        foreach ($translations as $langCode => $description) {
            $description = trim($description);
            if ($description === '') {
                continue;
            }
            $existing = Database::fetchOne(
                'SELECT id FROM video_translations WHERE video_id = ? AND lang_code = ?',
                [$videoId, $langCode]
            );
            if ($existing) {
                Database::query(
                    'UPDATE video_translations SET description = ? WHERE id = ?',
                    [$description, (int) $existing['id']]
                );
            } else {
                $slug = Slug::uniqueVideoTranslation($sourceTitle, $langCode, $videoId);
                Database::insert('video_translations', [
                    'video_id'    => $videoId,
                    'lang_code'   => $langCode,
                    'title'       => $sourceTitle,
                    'slug'        => $slug,
                    'description' => $description,
                ]);
            }
        }
    }

    

    public static function applyAlbumAi(int $albumId, array $ai): array
    {
        $row = Database::fetchOne('SELECT id, title, description FROM albums WHERE id = ?', [$albumId]);
        if (!$row) {
            return ['error' => 'not_found'];
        }
        $cfg = self::loadAiConfig();
        $item = [
            'title'                 => (string) ($row['title'] ?? ''),
            'description'           => (string) ($row['description'] ?? ''),
            '_original_title'       => (string) ($row['title'] ?? ''),
            '_original_description' => (string) ($row['description'] ?? ''),
        ];
        $done = [];

        if (!empty($ai['rewrite_title']) && $item['title'] !== '') {
            try { $item = self::aiRewriteTitle($item, $ai, $cfg); $done[] = 'rewrite_title'; }
            catch (\Throwable $e) { error_log('applyAlbumAi rewrite_title #' . $albumId . ': ' . $e->getMessage()); }
        }
        if (!empty($ai['translate']) && !empty($ai['translate_languages'])) {
            try { $item = self::aiTranslateTitle($item, $ai, $cfg); $done[] = 'translate'; }
            catch (\Throwable $e) { error_log('applyAlbumAi translate #' . $albumId . ': ' . $e->getMessage()); }
        }
        if (!empty($ai['rewrite_description']) && $item['_original_description'] !== '') {
            try { $item = self::aiRewriteDescription($item, $ai, $cfg); $done[] = 'rewrite_description'; }
            catch (\Throwable $e) { error_log('applyAlbumAi rewrite_description #' . $albumId . ': ' . $e->getMessage()); }
        }
        if (!empty($ai['translate_description']) && !empty($ai['translate_languages']) && ($item['description'] ?? '') !== '') {
            try { $item = self::aiTranslateDescription($item, $ai, $cfg); $done[] = 'translate_description'; }
            catch (\Throwable $e) { error_log('applyAlbumAi translate_description #' . $albumId . ': ' . $e->getMessage()); }
        }

         
        $upd = [];
        if (in_array('rewrite_title', $done, true) && $item['title'] !== '' && $item['title'] !== $row['title']) {
            $upd['title'] = mb_substr($item['title'], 0, 255);
        }
        if (in_array('rewrite_description', $done, true) && ($item['description'] ?? '') !== '') {
            $upd['description'] = $item['description'];
        }
        if ($upd) {
            Database::update('albums', $upd, 'id = ?', [$albumId]);
        }

         
        $sourceTitle = $item['title'] !== '' ? $item['title'] : (string) $row['title'];
        if (!empty($item['_ai_translations'])) {
            self::saveAlbumTranslations($albumId, $item['_ai_translations']);
        }
        if (!empty($item['_ai_desc_translations'])) {
            self::saveAlbumDescriptionTranslations($albumId, $item['_ai_desc_translations'], $sourceTitle);
        }

        return ['done' => $done, 'title' => $item['title'] ?? null];
    }

     
    private static function saveAlbumTranslations(int $albumId, array $translations): void
    {
        foreach ($translations as $langCode => $title) {
            $title = trim((string) $title);
            if ($title === '') {
                continue;
            }
            $slug = Slug::uniqueEntityTranslation('album_translations', 'album_id', 'albums', $title, $langCode, $albumId);
            $existing = Database::fetchOne(
                'SELECT id FROM album_translations WHERE album_id = ? AND lang_code = ?',
                [$albumId, $langCode]
            );
            if ($existing) {
                Database::query(
                    'UPDATE album_translations SET title = ?, slug = ? WHERE id = ?',
                    [mb_substr($title, 0, 500), $slug, (int) $existing['id']]
                );
            } else {
                Database::insert('album_translations', [
                    'album_id'  => $albumId,
                    'lang_code' => $langCode,
                    'title'     => mb_substr($title, 0, 500),
                    'slug'      => $slug,
                ]);
            }
        }
    }

     
    private static function saveAlbumDescriptionTranslations(int $albumId, array $translations, string $sourceTitle): void
    {
        foreach ($translations as $langCode => $description) {
            $description = trim((string) $description);
            if ($description === '') {
                continue;
            }
            $existing = Database::fetchOne(
                'SELECT id FROM album_translations WHERE album_id = ? AND lang_code = ?',
                [$albumId, $langCode]
            );
            if ($existing) {
                Database::query(
                    'UPDATE album_translations SET description = ? WHERE id = ?',
                    [$description, (int) $existing['id']]
                );
            } else {
                $slug = Slug::uniqueEntityTranslation('album_translations', 'album_id', 'albums', $sourceTitle, $langCode, $albumId);
                Database::insert('album_translations', [
                    'album_id'    => $albumId,
                    'lang_code'   => $langCode,
                    'title'       => mb_substr($sourceTitle, 0, 500),
                    'slug'        => $slug,
                    'description' => $description,
                ]);
            }
        }
    }

    private static function fetchFreshMediaUrls(string $remoteVideoId): ?array
    {
        $result = CatalogueClient::browse(['ids' => $remoteVideoId]);
        $videos = $result['videos'] ?? [];
        if (empty($videos)) {
            return null;
        }
        $v = $videos[0];
        return [
            'thumbnail'       => $v['thumbnail'] ?? '',
            'thumbnail_small' => $v['thumbnail_small'] ?? '',
            'preview'         => $v['preview'] ?? '',
            'description'     => $v['description'] ?? '',
        ];
    }
}
