<?php

declare(strict_types=1);

class AdminImportController
{
    public static function routes(): void
    {
        Router::get('/admin/import', [self::class, 'index']);
        Router::get('/admin/import/downloads', [self::class, 'downloads']);
        Router::post('/admin/import/parse', [self::class, 'parse']);
        Router::post('/admin/import/batch', [self::class, 'batch']);
        Router::post('/admin/import/delete-test', [self::class, 'deleteTest']);
        Router::get('/admin/import/files', [self::class, 'listFiles']);
        Router::post('/admin/import/reparse', [self::class, 'reparse']);
        Router::post('/admin/import/delete-file', [self::class, 'deleteFile']);
        Router::post('/admin/import/start-job', [self::class, 'startJob']);
        Router::get('/admin/import/jobs', [self::class, 'jobs']);
        Router::post('/admin/import/cancel-job', [self::class, 'cancelJob']);
        Router::post('/admin/import/pause-job', [self::class, 'pauseJob']);
        Router::post('/admin/import/resume-job', [self::class, 'resumeJob']);
        Router::post('/admin/import/delete-job', [self::class, 'deleteJob']);
    }

    public static function index(): void
    {
        Auth::requireAdmin();

        Router::render('admin/import', [
            'pageTitle' => 'Import Videos',
            'activeMenu' => 'import',
            'contentTemplate' => '_import',
        ]);
    }

    public static function downloads(): void
    {
        Auth::requireAdmin();

        Router::render('admin/import', [
            'pageTitle' => 'Download Queue',
            'activeMenu' => 'import',
            'activeSubMenu' => 'downloads',
            'contentTemplate' => '_downloads',
        ]);
    }

     

    public static function parse(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'Your session has expired. Please refresh the page and try again.'], 403);
        }

        if (empty($_FILES) && !empty($_SERVER['CONTENT_LENGTH'])) {
            $maxPost = self::parseSize(ini_get('post_max_size'));
            $maxUpload = self::parseSize(ini_get('upload_max_filesize'));
            $maxMb = (int) floor(min($maxPost, $maxUpload) / 1048576);
            $fileMb = round((int) $_SERVER['CONTENT_LENGTH'] / 1048576, 1);
            Router::json(['error' => "Your file is too large ({$fileMb} MB). Maximum allowed: {$maxMb} MB. Try splitting it into smaller parts."], 400);
        }

        if (empty($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
            $errors = [
                UPLOAD_ERR_INI_SIZE   => 'The file exceeds the maximum upload size (' . ini_get('upload_max_filesize') . '). Try a smaller file or split it into parts.',
                UPLOAD_ERR_FORM_SIZE  => 'The file is too large. Try a smaller file or split it into parts.',
                UPLOAD_ERR_PARTIAL    => 'The upload was interrupted. Please check your connection and try again.',
                UPLOAD_ERR_NO_FILE    => 'No file was selected. Please choose a CSV or JSON file.',
                UPLOAD_ERR_NO_TMP_DIR => 'Server configuration error: missing temporary folder. Please contact the administrator.',
                UPLOAD_ERR_CANT_WRITE => 'Server configuration error: cannot write to disk. Please contact the administrator.',
            ];
            $code = $_FILES['file']['error'] ?? UPLOAD_ERR_NO_FILE;
            Router::json(['error' => $errors[$code] ?? 'The file could not be uploaded. Please try again.'], 400);
        }

        $file = $_FILES['file'];
        $origName = $file['name'];
        $ext = strtolower(pathinfo($origName, PATHINFO_EXTENSION));

        if (!in_array($ext, ['csv', 'json'], true)) {
            Router::json(['error' => 'Unsupported file format. Please upload a .csv or .json file.'], 400);
        }

        $importDir = APP_ROOT . '/storage/imports';
        if (!is_dir($importDir)) {
            mkdir($importDir, 0775, true);
        }

        $uniqueName = uniqid('import_', true) . '.' . $ext;
        $destPath = $importDir . '/' . $uniqueName;

        if (!move_uploaded_file($file['tmp_name'], $destPath)) {
            Router::json(['error' => 'The server could not save the file. Please check disk space and permissions, or contact the administrator.'], 500);
        }

        try {
            if ($ext === 'csv') {
                $result = self::parseCsv($destPath);
            } else {
                $result = self::parseJson($destPath);
            }
        } catch (\Throwable $e) {
            @unlink($destPath);
            $hint = $ext === 'csv'
                ? 'Make sure your CSV has a header row and uses commas as separators.'
                : 'Make sure your JSON is a valid array of objects.';
            $detail = $e->getMessage();
            Router::json(['error' => 'Could not read the file: ' . $detail . '. ' . $hint], 400);
        }

         
        file_put_contents($destPath . '.meta.json', json_encode([
            'originalName' => $origName,
            'uploadedAt'   => date('Y-m-d H:i:s'),
            'totalRows'    => $result['totalRows'],
        ]));

        Router::json([
            'filename'    => $uniqueName,
            'originalName' => $origName,
            'columns'     => $result['columns'],
            'preview'     => $result['preview'],
            'totalRows'   => $result['totalRows'],
        ]);
    }

     

    

    private static function aiCostPerVideo(array $options): float
    {
        $cost = 0.0;
        if (!empty($options['aiRewriteTitle']))   $cost += 0.30;
        if (!empty($options['aiTranslate']))      $cost += 0.40;
        if (!empty($options['aiRewriteDesc']))    $cost += 0.30;
        if (!empty($options['aiTranslateDesc']))  $cost += 0.40;
        return $cost;
    }

    

    private static function checkCreditBalance(array $options, int $videoCount): ?string
    {
        $costPerVideo = self::aiCostPerVideo($options);
        if ($costPerVideo <= 0) {
            return null;  
        }

        $totalCost = $costPerVideo * $videoCount;

        if (!CatalogueClient::isConfigured()) {
            return 'API not configured. Please connect your TubePress account to use AI features.';
        }

        $balance = CatalogueClient::getBalance();
        if ($balance === null) {
             
             
            return null;
        }

        $credits = (float) ($balance['credits'] ?? 0);
        if ($credits < $totalCost) {
            return sprintf(
                'Insufficient credits. You need %.2f credits (%.2f/video x %d videos) but only have %.2f. Please top up your balance.',
                $totalCost, $costPerVideo, $videoCount, $credits
            );
        }

        return null;
    }

    public static function batch(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'Your session has expired. Please refresh the page and try again.'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        if (!$input) {
            Router::json(['error' => 'Invalid request.'], 400);
        }

        $filename = $input['filename'] ?? '';
        $mapping  = $input['mapping'] ?? [];
        $options  = $input['options'] ?? [];
        $offset   = max(0, (int) ($input['offset'] ?? 0));
        $limit    = max(1, min(500, (int) ($input['limit'] ?? 200)));

        if (!$filename || empty($mapping['title'])) {
            Router::json(['error' => 'Missing filename or title mapping.'], 400);
        }

         
        $creditError = self::checkCreditBalance($options, $limit);
        if ($creditError !== null) {
            Router::json(['error' => $creditError], 402);
        }

        if (basename($filename) !== $filename || !preg_match('/^import_[a-f0-9.]+\.(csv|json)$/', $filename)) {
            Router::json(['error' => 'Invalid file reference. Please re-upload your file.'], 400);
        }

        $filePath = APP_ROOT . '/storage/imports/' . $filename;
        if (!file_exists($filePath)) {
            Router::json(['error' => 'The uploaded file has expired. Please upload it again.'], 400);
        }

        $ext = pathinfo($filename, PATHINFO_EXTENSION);

         
        $metaPath = $filePath . '.meta.json';
        $meta = file_exists($metaPath) ? (json_decode(file_get_contents($metaPath), true) ?: []) : [];

        try {
            if ($ext === 'csv') {
                $batch = self::readCsvBatch($filePath, $offset, $limit);
                $total = $meta['totalRows'] ?? self::countCsvRows($filePath);
            } else {
                $allRows = self::readAllJson($filePath);
                $total = count($allRows);
                $batch = array_slice($allRows, $offset, $limit);
            }
        } catch (\Throwable $e) {
            Router::json(['error' => 'The file could not be read: ' . $e->getMessage()], 400);
        }

        $returnIds = !empty($input['returnIds']);
        $imported = 0;
        $skipped = 0;
        $errors = 0;
        $videoIds = [];
        $errorDetails = [];

        foreach ($batch as $rowIndex => $row) {
            try {
                $result = self::importRow($row, $mapping, $options);
                if (is_array($result)) {
                    if ($result['status'] === 'imported' || $result['status'] === 'updated') {
                        $imported++;
                        if ($returnIds && !empty($result['videoId'])) {
                            $videoIds[] = $result['videoId'];
                        }
                    } else {
                        $skipped++;
                    }
                }
            } catch (\Throwable $e) {
                $errors++;
                if (count($errorDetails) < 10) {
                    $rowNum = $offset + $rowIndex + 1;
                    $rowTitle = trim((string) ($row[$mapping['title'] ?? ''] ?? ''));
                    $label = $rowTitle !== '' ? '"' . mb_substr($rowTitle, 0, 50) . '"' : "#{$rowNum}";
                    $errorDetails[] = "Row {$label}: " . mb_substr($e->getMessage(), 0, 200);
                }
            }
        }

        $processed = $offset + count($batch);
        $done = $processed >= $total;

        $response = [
            'imported'  => $imported,
            'skipped'   => $skipped,
            'errors'    => $errors,
            'processed' => $processed,
            'total'     => $total,
            'done'      => $done,
        ];

        if ($returnIds) {
            $response['videoIds'] = $videoIds;
        }

        if ($errorDetails) {
            $response['errorDetails'] = $errorDetails;
        }

        Router::json($response);
    }

     

    public static function importRow(array $row, array $mapping, array $options = [], ?int $userId = null): array
    {
        $title = trim((string) ($row[$mapping['title']] ?? ''));
        if ($title === '') {
            return ['status' => 'skipped', 'videoId' => null];
        }

         
         
        if (self::isAlbumRow($row, $mapping)) {
            return self::importAlbumRow($row, $mapping, $options, $userId);
        }

        $cacheThumbs = !empty($options['cacheThumbs']);
        $duplicateMode = $options['duplicateMode'] ?? 'skip';

         
        $existingVideoId = null;
        if (!empty($mapping['video_id'])) {
            $extId = trim((string) ($row[$mapping['video_id']] ?? ''));
            if ($extId !== '') {
                $existing = Database::fetchOne('SELECT id FROM videos WHERE external_id = ?', [$extId]);
                if ($existing) {
                    if ($duplicateMode === 'skip') {
                        return ['status' => 'skipped', 'videoId' => null];
                    }
                    $existingVideoId = (int) $existing['id'];
                }
            }
        }

         
        $embedCode = '';
        if (!empty($mapping['embed_code'])) {
            $raw = trim((string) ($row[$mapping['embed_code']] ?? ''));
            if ($raw !== '') {
                 
                 
                if (stripos($raw, '<iframe') !== false) {
                    $embedCode = Sanitizer::embedCode($raw);
                } else {
                    $url = Sanitizer::url($raw);
                    $embedCode = $url !== ''
                        ? '<iframe src="' . htmlspecialchars($url, ENT_QUOTES | ENT_HTML5, 'UTF-8') . '" width="100%" height="100%" frameborder="0"></iframe>'
                        : '';
                }
            }
        }

         
        $duration = 0;
        if (!empty($mapping['duration'])) {
            $raw = trim((string) ($row[$mapping['duration']] ?? ''));
            if ($raw !== '') {
                if (preg_match('/^(\d+):(\d{2}):(\d{2})$/', $raw, $m)) {
                    $duration = (int) $m[1] * 3600 + (int) $m[2] * 60 + (int) $m[3];
                } elseif (preg_match('/^(\d+):(\d{2})$/', $raw, $m)) {
                    $duration = (int) $m[1] * 60 + (int) $m[2];
                } else {
                    $duration = max(0, (int) $raw);
                }
            }
        }

         
        $publishedAt = date('Y-m-d H:i:s');
        if (!empty($mapping['published_at'])) {
            $raw = trim((string) ($row[$mapping['published_at']] ?? ''));
            if ($raw !== '') {
                $parsed = self::parseDate($raw);
                if ($parsed !== null) {
                    $publishedAt = $parsed;
                }
            }
        }

         
        $defaultStorageId = MediaStorage::activeDefaultId();
        $thumbnail = '';
        $videoFolder = null;
        if (!empty($mapping['thumbnail'])) {
            $raw = trim((string) ($row[$mapping['thumbnail']] ?? ''));
            if ($raw !== '') {
                if ($cacheThumbs && (str_starts_with($raw, 'http://') || str_starts_with($raw, 'https://'))) {
                    $imgData = self::downloadImageData($raw);
                    if ($imgData !== null) {
                        $videoFolder = VideoAsset::createFolder();
                         
                         
                         
                        if (!VideoAsset::saveRaw($videoFolder, 'thumb.jpg', $imgData, $defaultStorageId > 0 ? $defaultStorageId : null)) {
                            $videoFolder = null;
                            $thumbnail = $raw;
                        }
                    } else {
                        $thumbnail = $raw;
                    }
                } else {
                    $thumbnail = $raw;
                }
            }
        }

         
        $data = [
            'title'        => Sanitizer::decodeEntities($title) ?? '',
            'description'  => Sanitizer::decodeEntities(!empty($mapping['description']) ? trim((string) ($row[$mapping['description']] ?? '')) : ''),
            'embed_code'   => $embedCode,
            'thumbnail'    => $thumbnail !== '' ? $thumbnail : null,
            'video_folder' => $videoFolder,
            'storage_id'   => $defaultStorageId > 0 ? $defaultStorageId : null,
            'duration'     => $duration,
            'views'        => !empty($mapping['views']) ? max(0, (int) ($row[$mapping['views']] ?? 0)) : 0,
            'likes'        => !empty($mapping['likes']) ? max(0, (int) ($row[$mapping['likes']] ?? 0)) : 0,
            'status'       => 'published',
            'published_at' => $publishedAt,
            'user_id'      => $userId ?? Auth::id(),
        ];

        if (!empty($mapping['video_id'])) {
            $extId = trim((string) ($row[$mapping['video_id']] ?? ''));
            if ($extId !== '') {
                $data['external_id'] = $extId;
            }
        }

         
        if (empty($data['external_id']) && $existingVideoId === null) {
            $autoId = 'auto_' . md5($title . '|' . $embedCode);
             
            $existing = Database::fetchOne('SELECT id FROM videos WHERE external_id = ?', [$autoId]);
            if ($existing) {
                if ($duplicateMode === 'skip') {
                    return ['status' => 'skipped', 'videoId' => null];
                }
                $existingVideoId = (int) $existing['id'];
            }
            $data['external_id'] = $autoId;
        }

         
        $slugSource = $title;
        if (!empty($mapping['slug'])) {
            $raw = trim((string) ($row[$mapping['slug']] ?? ''));
            if ($raw !== '') {
                $slugSource = $raw;
            }
        }

         
        if ($existingVideoId !== null) {
            unset($data['user_id']);
            Video::update($existingVideoId, $data);
            $videoId = $existingVideoId;
            $result = 'updated';
        } else {
            $data['slug'] = Slug::unique('videos', $slugSource);
            try {
                $videoId = Video::create($data);
            } catch (\PDOException $e) {
                 
                if ($e->getCode() === '23000' && !empty($data['external_id'])) {
                    $existing = Database::fetchOne('SELECT id FROM videos WHERE external_id = ?', [$data['external_id']]);
                    if ($existing) {
                        if ($duplicateMode === 'skip') {
                            return ['status' => 'skipped', 'videoId' => null];
                        }
                        unset($data['user_id'], $data['slug']);
                        Video::update((int) $existing['id'], $data);
                        return ['status' => 'updated', 'videoId' => (int) $existing['id']];
                    }
                }
                throw $e;  
            }
            $result = 'imported';
        }

         
         
        $performerExtra = [];
        if (!empty($mapping['performer_bio'])) {
            $performerExtra['bio'] = trim((string) ($row[$mapping['performer_bio']] ?? ''));
        }
        if (!empty($mapping['performer_photo'])) {
            $raw = trim((string) ($row[$mapping['performer_photo']] ?? ''));
            if ($raw !== '') {
                if (str_starts_with($raw, 'http://') || str_starts_with($raw, 'https://')) {
                    $downloaded = self::downloadImage($raw, 'performers');
                    $performerExtra['photo'] = $downloaded ?: $raw;
                } else {
                    $performerExtra['photo'] = $raw;
                }
            }
        }
        if (!empty($mapping['performer_country'])) {
            $performerExtra['country'] = trim((string) ($row[$mapping['performer_country']] ?? ''));
        }
        if (!empty($mapping['performer_birth_date'])) {
            $raw = trim((string) ($row[$mapping['performer_birth_date']] ?? ''));
            if ($raw !== '') {
                $parsed = self::parseDate($raw);
                if ($parsed !== null) {
                    $performerExtra['birth_date'] = substr($parsed, 0, 10);
                }
            }
        }
        if (!empty($mapping['performer_link'])) {
            $raw = trim((string) ($row[$mapping['performer_link']] ?? ''));
            if ($raw !== '') {
                $performerExtra['social_links'] = json_encode(array_filter(['link' => $raw]));
            }
        }

         
        $channelExtra = [];
        if (!empty($mapping['channel_description'])) {
            $channelExtra['description'] = trim((string) ($row[$mapping['channel_description']] ?? ''));
        }
        if (!empty($mapping['channel_logo'])) {
            $raw = trim((string) ($row[$mapping['channel_logo']] ?? ''));
            if ($raw !== '') {
                if (str_starts_with($raw, 'http://') || str_starts_with($raw, 'https://')) {
                    $downloaded = self::downloadImage($raw, 'channels');
                    $channelExtra['logo'] = $downloaded ?: $raw;
                } else {
                    $channelExtra['logo'] = $raw;
                }
            }
        }
        if (!empty($mapping['channel_website'])) {
            $channelExtra['website'] = trim((string) ($row[$mapping['channel_website']] ?? ''));
        }

        $relations = [
            'categories' => fn($name) => self::findOrCreateCategory($name),
            'tags'       => fn($name) => Tag::findOrCreate($name),
            'performers' => fn($name) => self::findOrCreatePerformer($name, $performerExtra),
            'channels'    => fn($name) => self::findOrCreateChannel($name, $channelExtra),
        ];

        $syncMethods = [
            'categories' => [Video::class, 'syncCategories'],
            'tags'       => [Video::class, 'syncTags'],
            'performers' => [Video::class, 'syncPerformers'],
            'channels'    => [Video::class, 'syncChannels'],
        ];

        foreach ($relations as $field => $findOrCreate) {
            if (!empty($mapping[$field])) {
                $rawVal = trim((string) ($row[$mapping[$field]] ?? ''));
                if ($rawVal !== '') {
                    $names = preg_split('/[,;|]+/', $rawVal);
                    $ids = [];
                    foreach ($names as $name) {
                        $name = trim($name);
                        if ($name !== '') {
                            $ids[] = $findOrCreate($name);
                        }
                    }
                    if ($ids) {
                        $syncMethods[$field]($videoId, $ids);
                    }
                }
            }
        }

         
         
        if (Setting::get('channels_as_users', '1') === '1' && !empty($mapping['channels'])) {
            $rawChannel = trim((string) ($row[$mapping['channels']] ?? ''));
            if ($rawChannel !== '') {
                $firstChannel = trim((string) (preg_split('/[,;|]+/', $rawChannel)[0] ?? ''));
                if ($firstChannel !== '') {
                    $channelUserId = User::findOrCreateForChannel($firstChannel);
                    if ($channelUserId) {
                        Database::query('UPDATE videos SET user_id = ? WHERE id = ?', [$channelUserId, $videoId]);
                    }
                }
            }
        }

         
         
        $translationData = [];
        foreach ($mapping as $key => $col) {
            if (preg_match('/^(title|description)_([a-z]{2,5})$/', $key, $m)) {
                $field = $m[1];  
                $langCode = $m[2];
                $value = trim((string) ($row[$col] ?? ''));
                if ($value !== '') {
                    if (!isset($translationData[$langCode])) {
                        $translationData[$langCode] = ['title' => '', 'description' => ''];
                    }
                    $translationData[$langCode][$field] = $field === 'title'
                        ? mb_substr($value, 0, 500)
                        : $value;
                }
            }
        }

        foreach ($translationData as $langCode => $fields) {
            if ($fields['title'] === '') {
                continue;  
            }
            $slug = Slug::uniqueVideoTranslation($fields['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 = ?, description = ? WHERE video_id = ? AND lang_code = ?',
                    [$fields['title'], $slug, $fields['description'], $videoId, $langCode]
                );
            } else {
                Database::query(
                    'INSERT INTO video_translations (video_id, lang_code, title, slug, description) VALUES (?, ?, ?, ?, ?)',
                    [$videoId, $langCode, $fields['title'], $slug, $fields['description']]
                );
            }
        }

         
        if (!empty($options['aiRewriteTitle']) && !empty($options['aiTitlePrompt'])) {
            try {
                $rewrittenTitle = self::aiRewriteTitle($title, $options);
                if ($rewrittenTitle !== null && $rewrittenTitle !== '') {
                    $title = $rewrittenTitle;
                     
                    $newSlug = Slug::uniqueVideo($title, $videoId);
                    Database::update('videos', ['title' => $title, 'slug' => $newSlug], 'id = ?', [$videoId]);
                }
            } catch (\Throwable $e) {
                 
            }
        }

         
        if (!empty($options['aiTranslate']) && !empty($options['aiTranslateLanguages'])) {
             
            $aiLanguages = [];
            foreach ($options['aiTranslateLanguages'] as $lang) {
                $code = $lang['code'] ?? '';
                if ($code !== '' && !isset($translationData[$code])) {
                    $aiLanguages[] = $lang;
                }
            }

            if (!empty($aiLanguages)) {
                try {
                    $aiTranslations = self::aiTranslateTitle($title, $aiLanguages, $options);
                    foreach ($aiTranslations as $langCode => $transTitle) {
                        $transTitle = mb_substr(trim($transTitle), 0, 500);
                        if ($transTitle === '') {
                            continue;
                        }
                        $slug = Slug::uniqueVideoTranslation($transTitle, $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 video_id = ? AND lang_code = ?',
                                [$transTitle, $slug, $videoId, $langCode]
                            );
                        } else {
                            Database::query(
                                'INSERT INTO video_translations (video_id, lang_code, title, slug) VALUES (?, ?, ?, ?)',
                                [$videoId, $langCode, $transTitle, $slug]
                            );
                        }
                    }
                } catch (\Throwable $e) {
                     
                }
            }
        }

         
         
         
        $finalDescription = trim((string) ($row[$mapping['description'] ?? ''] ?? ''));
        if (!empty($options['aiRewriteDesc']) && !empty($options['aiDescPrompt'])) {
            if ($finalDescription !== '') {
                try {
                    $rewrittenDesc = self::aiRewriteDescription($finalDescription, $options);
                    if ($rewrittenDesc !== null && $rewrittenDesc !== '') {
                        $finalDescription = $rewrittenDesc;
                        Database::update('videos', ['description' => $rewrittenDesc], 'id = ?', [$videoId]);
                    }
                } catch (\Throwable $e) {
                     
                }
            }
        }

         
         
         
         
         
        if (!empty($options['aiTranslateDesc']) && !empty($options['aiTranslateLanguages']) && $finalDescription !== '') {
             
            $aiDescLanguages = [];
            foreach ($options['aiTranslateLanguages'] as $lang) {
                $code = $lang['code'] ?? '';
                if ($code !== '' && empty($translationData[$code]['description'])) {
                    $aiDescLanguages[] = $lang;
                }
            }

            if (!empty($aiDescLanguages)) {
                try {
                    $aiDescTranslations = self::aiTranslateDescription($finalDescription, $aiDescLanguages, $options);
                    foreach ($aiDescTranslations as $langCode => $transDesc) {
                        $transDesc = trim($transDesc);
                        if ($transDesc === '') {
                            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 video_id = ? AND lang_code = ?',
                                [$transDesc, $videoId, $langCode]
                            );
                        } else {
                             
                            $slug = Slug::uniqueVideoTranslation($title, $langCode, $videoId);
                            Database::query(
                                'INSERT INTO video_translations (video_id, lang_code, title, slug, description) VALUES (?, ?, ?, ?, ?)',
                                [$videoId, $langCode, $title, $slug, $transDesc]
                            );
                        }
                    }
                } catch (\Throwable $e) {
                     
                }
            }
        }

        if ($videoId && $videoFolder !== null && TranscodeManager::isEnabled()) {
            TranscodeManager::queue($videoId);
        }

        return ['status' => $result, 'videoId' => $videoId];
    }

     

     
    public static function isAlbumRow(array $row, array $mapping): bool
    {
        if (!empty($mapping['media_type'])) {
            $t = strtolower(trim((string) ($row[$mapping['media_type']] ?? '')));
            if (in_array($t, ['album','albums','gallery','galleries','image','images','photo','photos','pic','pics','picture','pictures'], true)) return true;
            if (in_array($t, ['video','videos','clip','clips','movie','scene'], true)) return false;
        }
        $hasImages = !empty($mapping['images']) && trim((string) ($row[$mapping['images']] ?? '')) !== '';
        $hasEmbed  = !empty($mapping['embed_code']) && trim((string) ($row[$mapping['embed_code']] ?? '')) !== '';
        return $hasImages && !$hasEmbed;
    }

     
    public static function parseImageList(string $raw): array
    {
        $raw = trim($raw);
        if ($raw === '') return [];
        if ($raw[0] === '[') {
            $j = json_decode($raw, true);
            if (is_array($j)) {
                $out = [];
                foreach ($j as $it) {
                    $u = trim(is_array($it) ? (string) ($it['url'] ?? $it['src'] ?? $it['f'] ?? '') : (string) $it);
                    if ($u !== '') $out[] = $u;
                }
                if ($out) return array_values(array_unique($out));
            }
        }
        $out = [];
        foreach (preg_split('/[\r\n,|]+| {2,}/', $raw) ?: [] as $p) {
            $p = trim($p);
            if ($p !== '' && (str_starts_with($p, 'http://') || str_starts_with($p, 'https://'))) $out[] = $p;
        }
        return array_values(array_unique($out));
    }

     
    public static function findOrCreateAlbumTaxonomy(string $table, string $model, string $name, string $videoType): int
    {
        $name = trim($name);
        if ($name === '') return 0;
        $slug = Slug::generate($name);
        if ($slug === '') return 0;
        $existing = Database::fetchOne("SELECT id FROM {$table} WHERE slug = ? AND video_type = ?", [$slug, $videoType]);
        if ($existing) return (int) $existing['id'];
        try {
            return (int) $model::create(['name' => $name, 'slug' => $slug, 'video_type' => $videoType]);
        } catch (\PDOException $e) {
            if ($e->getCode() === '23000') {
                $existing = Database::fetchOne("SELECT id FROM {$table} WHERE slug = ? AND video_type = ?", [$slug, $videoType]);
                if ($existing) return (int) $existing['id'];
            }
            throw $e;
        }
    }

     
    private static function saveAlbumTranslation(int $albumId, string $langCode, string $title, string $description, string $sourceTitle): void
    {
        $langCode = trim($langCode);
        if ($langCode === '' || ($title === '' && $description === '')) return;
        $existing = Database::fetchOne('SELECT id, title, description FROM album_translations WHERE album_id = ? AND lang_code = ?', [$albumId, $langCode]);
        $newTitle = $title !== '' ? $title : ($existing['title'] ?? $sourceTitle);
        $newDesc  = $description !== '' ? $description : ($existing['description'] ?? '');
        $slug = Slug::uniqueEntityTranslation('album_translations', 'album_id', 'albums', $newTitle !== '' ? $newTitle : $sourceTitle, $langCode, $albumId);
        if ($existing) {
            Database::query('UPDATE album_translations SET title = ?, slug = ?, description = ? WHERE id = ?', [$newTitle, $slug, $newDesc, (int) $existing['id']]);
        } else {
            Database::insert('album_translations', ['album_id' => $albumId, 'lang_code' => $langCode, 'title' => $newTitle, 'slug' => $slug, 'description' => $newDesc]);
        }
    }

     
    public static function importAlbumRow(array $row, array $mapping, array $options = [], ?int $userId = null): array
    {
        $title = trim((string) ($row[$mapping['title']] ?? ''));
        if ($title === '') return ['status' => 'skipped', 'videoId' => null];

        $duplicateMode = $options['duplicateMode'] ?? 'skip';
        $videoType = (isset($options['videoType']) && in_array($options['videoType'], Video::VALID_TYPES, true)) ? $options['videoType'] : Album::DEFAULT_TYPE;

         
        $imageUrls = self::parseImageList(!empty($mapping['images']) ? (string) ($row[$mapping['images']] ?? '') : '');
        $coverRaw = !empty($mapping['thumbnail']) ? trim((string) ($row[$mapping['thumbnail']] ?? '')) : '';
        if ($coverRaw !== '' && (str_starts_with($coverRaw, 'http://') || str_starts_with($coverRaw, 'https://'))) {
            array_unshift($imageUrls, $coverRaw);
            $imageUrls = array_values(array_unique($imageUrls));
        }
        if (empty($imageUrls)) return ['status' => 'skipped', 'videoId' => null];
        $images = array_map(static fn($u) => ['f' => $u], $imageUrls);

        $extId = !empty($mapping['video_id']) ? trim((string) ($row[$mapping['video_id']] ?? '')) : '';
        if ($extId === '') $extId = 'auto_album_' . md5($title . '|' . $imageUrls[0]);
        $existingAlbumId = null;
        $existing = Database::fetchOne('SELECT id FROM albums WHERE external_id = ?', [$extId]);
        if ($existing) {
            if ($duplicateMode === 'skip') return ['status' => 'skipped', 'videoId' => null];
            $existingAlbumId = (int) $existing['id'];
        }

        $publishedAt = date('Y-m-d H:i:s');
        if (!empty($mapping['published_at'])) { $p = self::parseDate(trim((string) ($row[$mapping['published_at']] ?? ''))); if ($p !== null) $publishedAt = $p; }

        $data = [
            'title'        => Sanitizer::decodeEntities($title) ?? $title,
            'description'  => Sanitizer::decodeEntities(!empty($mapping['description']) ? trim((string) ($row[$mapping['description']] ?? '')) : '') ?? '',
            'images'       => json_encode($images, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
            'cover_file'   => $imageUrls[0],
            'image_count'  => count($images),
            'video_type'   => $videoType,
            'status'       => 'published',
            'views'        => !empty($mapping['views']) ? max(0, (int) ($row[$mapping['views']] ?? 0)) : 0,
            'published_at' => $publishedAt,
            'external_id'  => $extId,
            'user_id'      => $userId ?? Auth::id(),
        ];

        if ($existingAlbumId !== null) {
            unset($data['user_id']);
            Album::update($existingAlbumId, $data);
            $albumId = $existingAlbumId;
            $result = 'updated';
        } else {
            $slugSource = (!empty($mapping['slug']) && trim((string) ($row[$mapping['slug']] ?? '')) !== '') ? trim((string) ($row[$mapping['slug']])) : $title;
            $data['slug'] = Slug::unique('albums', $slugSource);
            try {
                $albumId = Album::create($data);
            } catch (\PDOException $e) {
                if ($e->getCode() === '23000') {
                    $ex = Database::fetchOne('SELECT id FROM albums WHERE external_id = ?', [$extId]);
                    if ($ex) {
                        if ($duplicateMode === 'skip') return ['status' => 'skipped', 'videoId' => null];
                        unset($data['user_id'], $data['slug']);
                        Album::update((int) $ex['id'], $data);
                        return ['status' => 'updated', 'videoId' => (int) $ex['id']];
                    }
                }
                throw $e;
            }
            $result = 'imported';
        }

         
        $albumRel = [
            'categories' => ['album_categories', 'AlbumCategory', [Album::class, 'syncCategories']],
            'tags'       => ['album_tags', 'AlbumTag', [Album::class, 'syncTags']],
            'performers' => ['album_performers', 'AlbumPerformer', [Album::class, 'syncPerformers']],
            'channels'   => ['album_channels', 'AlbumChannel', [Album::class, 'syncChannels']],
        ];
        foreach ($albumRel as $field => [$table, $model, $sync]) {
            if (!empty($mapping[$field])) {
                $rawVal = trim((string) ($row[$mapping[$field]] ?? ''));
                if ($rawVal !== '') {
                    $ids = [];
                    foreach (preg_split('/[,;|]+/', $rawVal) as $name) {
                        $name = trim($name);
                        if ($name !== '') { $id = self::findOrCreateAlbumTaxonomy($table, $model, $name, $videoType); if ($id) $ids[] = $id; }
                    }
                    if ($ids) $sync($albumId, $ids);
                }
            }
        }

        if (Setting::get('channels_as_users', '1') === '1' && !empty($mapping['channels'])) {
            $rawChannel = trim((string) ($row[$mapping['channels']] ?? ''));
            if ($rawChannel !== '') {
                $firstChannel = trim((string) (preg_split('/[,;|]+/', $rawChannel)[0] ?? ''));
                if ($firstChannel !== '') {
                    $channelUserId = User::findOrCreateForChannel($firstChannel);
                    if ($channelUserId) Database::query('UPDATE albums SET user_id = ? WHERE id = ?', [$channelUserId, $albumId]);
                }
            }
        }

         
        $translationData = [];
        foreach ($mapping as $key => $col) {
            if (preg_match('/^(title|description)_([a-z]{2,5})$/', $key, $m)) {
                $value = trim((string) ($row[$col] ?? ''));
                if ($value !== '') {
                    $translationData[$m[2]] ??= ['title' => '', 'description' => ''];
                    $translationData[$m[2]][$m[1]] = $m[1] === 'title' ? mb_substr($value, 0, 500) : $value;
                }
            }
        }
        foreach ($translationData as $langCode => $fields) {
            self::saveAlbumTranslation($albumId, $langCode, $fields['title'], $fields['description'], $title);
        }

         
        if (!empty($options['aiRewriteTitle']) && !empty($options['aiTitlePrompt'])) {
            try { $rt = self::aiRewriteTitle($title, $options); if ($rt !== null && $rt !== '') { $title = $rt; Database::update('albums', ['title' => $title, 'slug' => Slug::unique('albums', $title, $albumId)], 'id = ?', [$albumId]); } } catch (\Throwable $e) {}
        }
        $finalDescription = $data['description'];
        if (!empty($options['aiRewriteDesc']) && !empty($options['aiDescPrompt']) && $finalDescription !== '') {
            try { $rd = self::aiRewriteDescription($finalDescription, $options); if ($rd !== null && $rd !== '') { $finalDescription = $rd; Database::update('albums', ['description' => $rd], 'id = ?', [$albumId]); } } catch (\Throwable $e) {}
        }
         
        if (!empty($options['aiTranslate']) && !empty($options['aiTranslateLanguages'])) {
            $aiLangs = array_values(array_filter($options['aiTranslateLanguages'], static fn($l) => !empty($l['code']) && !isset($translationData[$l['code']])));
            if ($aiLangs) { try { foreach (self::aiTranslateTitle($title, $aiLangs, $options) as $lc => $tt) { $tt = mb_substr(trim((string) $tt), 0, 500); if ($tt !== '') self::saveAlbumTranslation($albumId, $lc, $tt, '', $title); } } catch (\Throwable $e) {} }
        }
        if (!empty($options['aiTranslateDesc']) && !empty($options['aiTranslateLanguages']) && $finalDescription !== '') {
            $aiLangs = array_values(array_filter($options['aiTranslateLanguages'], static fn($l) => !empty($l['code']) && empty($translationData[$l['code']]['description'])));
            if ($aiLangs) { try { foreach (self::aiTranslateDescription($finalDescription, $aiLangs, $options) as $lc => $td) { $td = trim((string) $td); if ($td !== '') self::saveAlbumTranslation($albumId, $lc, '', $td, $title); } } catch (\Throwable $e) {} }
        }

        return ['status' => $result, 'videoId' => $albumId];
    }


    public static function deleteTest(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $ids = array_filter(array_map('intval', $input['ids'] ?? []), fn($id) => $id > 0);

        foreach ($ids as $id) {
            Video::delete($id);
        }

        Router::json(['deleted' => count($ids)]);
    }

     

    public static function listFiles(): void
    {
        Auth::requireAdmin();

        $dir = APP_ROOT . '/storage/imports';
        $files = [];

        if (is_dir($dir)) {
            foreach (glob($dir . '/import_*.*') as $path) {
                if (str_ends_with($path, '.meta.json')) {
                    continue;
                }
                $filename = basename($path);
                $meta = [];
                $metaPath = $path . '.meta.json';
                if (file_exists($metaPath)) {
                    $meta = json_decode(file_get_contents($metaPath), true) ?: [];
                }
                $files[] = [
                    'filename'     => $filename,
                    'originalName' => $meta['originalName'] ?? $filename,
                    'uploadedAt'   => $meta['uploadedAt'] ?? date('Y-m-d H:i:s', filemtime($path)),
                    'size'         => filesize($path),
                    'extension'    => strtoupper(pathinfo($filename, PATHINFO_EXTENSION)),
                ];
            }
        }

        usort($files, fn($a, $b) => strcmp($b['uploadedAt'], $a['uploadedAt']));
        Router::json($files);
    }

    public static function reparse(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $filename = $input['filename'] ?? '';

        if (!$filename || basename($filename) !== $filename || !preg_match('/^import_[a-f0-9.]+\.(csv|json)$/', $filename)) {
            Router::json(['error' => 'Invalid file reference.'], 400);
        }

        $path = APP_ROOT . '/storage/imports/' . $filename;
        if (!file_exists($path)) {
            Router::json(['error' => 'File not found. It may have been deleted.'], 400);
        }

        $ext = pathinfo($filename, PATHINFO_EXTENSION);

        try {
            $result = $ext === 'csv' ? self::parseCsv($path) : self::parseJson($path);
        } catch (\Throwable $e) {
            Router::json(['error' => 'Could not read the file: ' . $e->getMessage()], 400);
        }

        $metaPath = $path . '.meta.json';
        $meta = file_exists($metaPath) ? (json_decode(file_get_contents($metaPath), true) ?: []) : [];
        $meta['totalRows'] = $result['totalRows'];
        file_put_contents($metaPath, json_encode($meta));

        Router::json([
            'filename'     => $filename,
            'originalName' => $meta['originalName'] ?? $filename,
            'columns'      => $result['columns'],
            'preview'      => $result['preview'],
            'totalRows'    => $result['totalRows'],
        ]);
    }

    public static function deleteFile(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $filename = $input['filename'] ?? '';

        if (!$filename || basename($filename) !== $filename || !preg_match('/^import_[a-f0-9.]+\.(csv|json)$/', $filename)) {
            Router::json(['error' => 'Invalid filename.'], 400);
        }

        $path = APP_ROOT . '/storage/imports/' . $filename;
        if (file_exists($path)) {
            @unlink($path);
            @unlink($path . '.meta.json');
        }

        Router::json(['success' => true]);
    }

     

    private static function parseCsv(string $path): array
    {
        $handle = fopen($path, 'r');
        if (!$handle) {
            throw new \RuntimeException('Cannot open CSV file. The file may be corrupted or permissions are incorrect.');
        }

        $headers = fgetcsv($handle, 0, ',', '"', '');
        if (!$headers) {
            fclose($handle);
            throw new \RuntimeException('CSV file is empty — no header row found. The file must have at least one row of column headers.');
        }

        $headers[0] = preg_replace('/^\x{FEFF}/u', '', $headers[0]);
        $headers = array_map('trim', $headers);

         
        if (count($headers) === 1 && str_contains($headers[0], "\t")) {
            fclose($handle);
            throw new \RuntimeException('This CSV appears to use tabs as separators instead of commas. Please convert it to comma-separated format or save as a proper CSV.');
        }
        if (count($headers) === 1 && str_contains($headers[0], ';')) {
            fclose($handle);
            throw new \RuntimeException('This CSV appears to use semicolons as separators instead of commas. Please convert it to comma-separated format.');
        }

         
        $dupes = array_filter(array_count_values($headers), fn($c) => $c > 1);
        if ($dupes) {
            fclose($handle);
            $names = implode(', ', array_keys($dupes));
            throw new \RuntimeException("Duplicate column names found: {$names}. Each column must have a unique header name.");
        }

        $preview = [];
        $totalRows = 0;

        while (($row = fgetcsv($handle, 0, ',', '"', '')) !== false) {
            $totalRows++;
            if (count($preview) < 5) {
                $assoc = [];
                foreach ($headers as $i => $col) {
                    $assoc[$col] = $row[$i] ?? '';
                }
                $preview[] = $assoc;
            }
        }

        if ($totalRows === 0) {
            fclose($handle);
            throw new \RuntimeException('CSV has headers but no data rows. The file must contain at least one row of data after the header.');
        }

        fclose($handle);
        return ['columns' => $headers, 'preview' => $preview, 'totalRows' => $totalRows];
    }

    

    private static function unwrapJsonList(array $data): array
    {
        if ($data === [] || array_keys($data) === range(0, count($data) - 1)) {
            return $data;  
        }
        foreach (['videos', 'data', 'items', 'rows', 'results', 'records', 'entries'] as $k) {
            if (isset($data[$k]) && is_array($data[$k])
                && ($data[$k] === [] || array_keys($data[$k]) === range(0, count($data[$k]) - 1))) {
                return $data[$k];
            }
        }
        if (count($data) === 1) {
            $only = reset($data);
            if (is_array($only) && ($only === [] || array_keys($only) === range(0, count($only) - 1))) {
                return $only;
            }
        }
        return [$data];  
    }

    private static function parseJson(string $path): array
    {
        $content = file_get_contents($path);
        if ($content === false || $content === '') {
            throw new \RuntimeException('JSON file is empty or could not be read.');
        }
        $content = preg_replace('/^\xEF\xBB\xBF/', '', $content);  

        $data = json_decode($content, true);

        if ($data === null) {
            $jsonErr = json_last_error_msg();
            throw new \RuntimeException("Invalid JSON syntax: {$jsonErr}. Please validate your JSON file (check for missing commas, brackets, or quotes).");
        }

        if (!is_array($data)) {
            throw new \RuntimeException('JSON must contain an array of objects, e.g. [{"title":"...","embed":"..."},...]');
        }

        if (empty($data)) {
            throw new \RuntimeException('JSON array is empty — no data to import.');
        }

        $data = self::unwrapJsonList($data);

         
        if (!is_array($data[0]) || empty($data[0])) {
            throw new \RuntimeException('Each item in the JSON array must be an object with key-value pairs, e.g. {"title":"My Video","embed":"<iframe...>"}');
        }

        return [
            'columns'   => array_keys($data[0]),
            'preview'   => array_slice($data, 0, 5),
            'totalRows' => count($data),
        ];
    }

    public static function readCsvBatch(string $path, int $offset, int $limit): array
    {
        $handle = fopen($path, 'r');
        if (!$handle) {
            throw new \RuntimeException('Cannot open CSV file.');
        }

        $headers = fgetcsv($handle, 0, ',', '"', '');
        if (!$headers) {
            fclose($handle);
            throw new \RuntimeException('CSV file is empty.');
        }

        $headers[0] = preg_replace('/^\x{FEFF}/u', '', $headers[0]);
        $headers = array_map('trim', $headers);

         
        $skipped = 0;
        while ($skipped < $offset && ($row = fgetcsv($handle, 0, ',', '"', '')) !== false) {
            $skipped++;
        }

         
        $rows = [];
        $count = 0;
        while ($count < $limit && ($row = fgetcsv($handle, 0, ',', '"', '')) !== false) {
            $assoc = [];
            foreach ($headers as $i => $col) {
                $assoc[$col] = $row[$i] ?? '';
            }
            $rows[] = $assoc;
            $count++;
        }

        fclose($handle);
        return $rows;
    }

    

    public static function readCsvBatchFast(string $path, int $byteOffset, int $limit, int $skipRows = 0): array
    {
        $handle = fopen($path, 'r');
        if (!$handle) {
            throw new \RuntimeException('Cannot open CSV file.');
        }

         
        $headers = fgetcsv($handle, 0, ',', '"', '');
        if (!$headers) {
            fclose($handle);
            throw new \RuntimeException('CSV file is empty.');
        }

        $headers[0] = preg_replace('/^\x{FEFF}/u', '', $headers[0]);
        $headers = array_map('trim', $headers);

        if ($byteOffset > 0) {
             
            fseek($handle, $byteOffset);
        } elseif ($skipRows > 0) {
             
            $skipped = 0;
            while ($skipped < $skipRows && fgetcsv($handle, 0, ',', '"', '') !== false) {
                $skipped++;
            }
        }
         

        $rows = [];
        $count = 0;
        while ($count < $limit && ($row = fgetcsv($handle, 0, ',', '"', '')) !== false) {
            $assoc = [];
            foreach ($headers as $i => $col) {
                $assoc[$col] = $row[$i] ?? '';
            }
            $rows[] = $assoc;
            $count++;
        }

        $newOffset = ftell($handle);
        fclose($handle);

        return ['rows' => $rows, 'newOffset' => $newOffset];
    }

    public static function countCsvRows(string $path): int
    {
        $handle = fopen($path, 'r');
        if (!$handle) return 0;
        fgetcsv($handle, 0, ',', '"', '');  
        $count = 0;
        while (fgetcsv($handle, 0, ',', '"', '') !== false) {
            $count++;
        }
        fclose($handle);
        return $count;
    }

     
    private static ?array $_jsonCache = null;
    private static ?string $_jsonCachePath = null;

    public static function readAllJson(string $path): array
    {
         
        if (self::$_jsonCachePath === $path && self::$_jsonCache !== null) {
            return self::$_jsonCache;
        }

        $fileSize = filesize($path);
        $memoryLimit = self::parseSize(ini_get('memory_limit'));
         
        if ($memoryLimit > 0 && $fileSize > $memoryLimit * 0.2) {
            throw new \RuntimeException(
                'JSON file is too large (' . round($fileSize / 1048576, 1) . ' MB) to process safely. '
                . 'Maximum recommended: ' . round($memoryLimit * 0.2 / 1048576) . ' MB. '
                . 'Please split the file into smaller parts or use CSV format for large imports.'
            );
        }

        $content = file_get_contents($path);
        $content = preg_replace('/^\xEF\xBB\xBF/', '', $content);  
        $data = json_decode($content, true);
        unset($content);  

        if (!is_array($data) || empty($data)) {
            throw new \RuntimeException('JSON must be an array of objects.');
        }

        $data = self::unwrapJsonList($data);

        self::$_jsonCache = $data;
        self::$_jsonCachePath = $path;

        return $data;
    }

     

    public static function startJob(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'Your session has expired. Please refresh the page and try again.'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        if (!$input) {
            Router::json(['error' => 'Invalid request.'], 400);
        }

        $filename = $input['filename'] ?? '';
        $mapping  = $input['mapping'] ?? [];
        $options  = $input['options'] ?? [];
        $startOffset = max(0, (int) ($input['startOffset'] ?? 0));

        if (!$filename || empty($mapping['title'])) {
            Router::json(['error' => 'Missing filename or title mapping.'], 400);
        }

        if (basename($filename) !== $filename || !preg_match('/^import_[a-f0-9.]+\.(csv|json)$/', $filename)) {
            Router::json(['error' => 'Invalid file reference. Please re-upload your file.'], 400);
        }

        $filePath = APP_ROOT . '/storage/imports/' . $filename;
        if (!file_exists($filePath)) {
            Router::json(['error' => 'The uploaded file has expired. Please upload it again.'], 400);
        }

         
        $metaPath = $filePath . '.meta.json';
        $meta = file_exists($metaPath) ? (json_decode(file_get_contents($metaPath), true) ?: []) : [];
        $ext = pathinfo($filename, PATHINFO_EXTENSION);
        $totalRows = $meta['totalRows'] ?? ($ext === 'csv' ? self::countCsvRows($filePath) : count(self::readAllJson($filePath)));
        $originalName = $meta['originalName'] ?? $filename;

        if ($totalRows <= 0) {
            Router::json(['error' => 'The file contains no data rows. Please check that your file has content beyond the header row.'], 400);
        }

        if ($startOffset >= $totalRows) {
            Router::json(['error' => 'Start offset (' . $startOffset . ') is beyond the total rows (' . $totalRows . '). Nothing to import.'], 400);
        }

         
        $videosToProcess = $totalRows - $startOffset;
        $creditError = self::checkCreditBalance($options, $videosToProcess);
        if ($creditError !== null) {
            Router::json(['error' => $creditError], 402);
        }

        $jobId = ImportProcessor::createJob($filename, $originalName, $mapping, $options, $totalRows, Auth::id(), $startOffset);

        $job = ImportProcessor::getJob($jobId);

         
        register_shutdown_function(function () {
            if (function_exists('fastcgi_finish_request')) {
                fastcgi_finish_request();
            }
            ImportProcessor::tick();
        });

        Router::json(['job' => $job]);
    }

    public static function jobs(): void
    {
        Auth::requireAdmin();

        $jobs = ImportProcessor::getActiveJobs();

         
        register_shutdown_function(function () {
            if (function_exists('fastcgi_finish_request')) {
                fastcgi_finish_request();
            }
            ImportProcessor::tick();
        });

        Router::json(['jobs' => $jobs]);
    }

    public static function cancelJob(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $jobId = (int) ($input['id'] ?? 0);

        if ($jobId <= 0) {
            Router::json(['error' => 'Invalid job ID.'], 400);
        }

        $cancelled = ImportProcessor::cancelJob($jobId);
        Router::json(['success' => $cancelled]);
    }

    public static function pauseJob(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $jobId = (int) ($input['id'] ?? 0);

        if ($jobId <= 0) {
            Router::json(['error' => 'Invalid job ID.'], 400);
        }

        $paused = ImportProcessor::pauseJob($jobId);
        Router::json(['success' => $paused]);
    }

    public static function resumeJob(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $jobId = (int) ($input['id'] ?? 0);

        if ($jobId <= 0) {
            Router::json(['error' => 'Invalid job ID.'], 400);
        }

        $resumed = ImportProcessor::resumeJob($jobId);

         
        if ($resumed) {
            register_shutdown_function(function () {
                if (function_exists('fastcgi_finish_request')) {
                    fastcgi_finish_request();
                }
                ImportProcessor::tick();
            });
        }

        Router::json(['success' => $resumed]);
    }

    public static function deleteJob(): void
    {
        Auth::requireAdmin();
        if (!Router::verifyCsrf()) {
            Router::json(['error' => 'CSRF'], 403);
        }

        $input = json_decode(file_get_contents('php://input'), true);
        $jobId = (int) ($input['id'] ?? 0);

        if ($jobId <= 0) {
            Router::json(['error' => 'Invalid job ID.'], 400);
        }

        $deleted = ImportProcessor::deleteJob($jobId);
        Router::json(['success' => $deleted]);
    }

     

    public static function findOrCreateCategory(string $name): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM categories WHERE slug = ?', [$slug]);
        if ($existing) {
            return (int) $existing['id'];
        }
        return Database::insert('categories', ['name' => trim($name), 'slug' => $slug]);
    }

    public static function findOrCreatePerformer(string $name, array $extra = []): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM performers WHERE slug = ?', [$slug]);
        if ($existing) {
            $id = (int) $existing['id'];
            if (!empty($extra)) {
                $update = array_filter($extra, fn($v) => $v !== '' && $v !== null);
                if ($update) {
                    Performer::update($id, $update);
                }
            }
            return $id;
        }
        $data = array_merge(['name' => trim($name), 'slug' => $slug], array_filter($extra, fn($v) => $v !== '' && $v !== null));
        return Database::insert('performers', $data);
    }

    public static function findOrCreateChannel(string $name, array $extra = []): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM channels WHERE slug = ?', [$slug]);
        if ($existing) {
            $id = (int) $existing['id'];
            if (!empty($extra)) {
                $update = array_filter($extra, fn($v) => $v !== '' && $v !== null);
                if ($update) {
                    Channel::update($id, $update);
                }
            }
            return $id;
        }
        $data = array_merge(['name' => trim($name), 'slug' => $slug], array_filter($extra, fn($v) => $v !== '' && $v !== null));
        return Database::insert('channels', $data);
    }

     

     
    private static ?array $_aiConfig = null;

    

    private static function aiTranslateTitle(string $title, array $languages, array $options): array
    {
         
        if (self::$_aiConfig === null) {
            self::$_aiConfig = CatalogueClient::getAiConfig() ?: [];
        }
        $cfg = self::$_aiConfig;

        $userPrompt = !empty($options['aiTranslatePrompt'])
            ? $options['aiTranslatePrompt']
            : Setting::get('import_ai_translate_prompt', '');

        if ($userPrompt === '') {
            return [];
        }

         
        $begin = str_replace('{title}', $title, $cfg['ai_translate_begin'] ?? '');
        $end = $cfg['ai_translate_end'] ?? '';
        $formatLines = implode("\n", array_map(
            fn($l) => '<' . strtoupper($l['code']) . '>' . $l['name'] . '</' . strtoupper($l['code']) . '>',
            $languages
        ));

        $fullPrompt = trim(
            $begin . "\n\n" . $userPrompt . "\n\n"
            . "Output format (XML tags required):\n" . $formatLines
            . ($end !== '' ? "\n\n" . $end : '')
        );

         
         
         
        $best = [];
        for ($attempt = 1; $attempt <= 3; $attempt++) {
            $result = CatalogueClient::aiCompletion(
                [['role' => 'user', 'content' => $fullPrompt]],
                'auto',
                0.5
            );
            if (!is_array($result) || !empty($result['_error'])) {
                continue;
            }
            $content = $result['choices'][0]['message']['content'] ?? '';
            if ($content === '') {
                continue;
            }
            $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;
                    }
                }
            }
            if (count($translations) > count($best)) {
                $best = $translations;
            }
            if (count($best) >= count($languages)) {
                break;
            }
        }

        return $best;
    }

    

    private static function aiTranslateDescription(string $description, array $languages, array $options): array
    {
         
        if (self::$_aiConfig === null) {
            self::$_aiConfig = CatalogueClient::getAiConfig() ?: [];
        }
        $cfg = self::$_aiConfig;

        $userPrompt = !empty($options['aiTranslateDescPrompt'])
            ? $options['aiTranslateDescPrompt']
            : Setting::get('import_ai_translate_desc_prompt', '');

        if ($userPrompt === '') {
            return [];
        }

         
        $begin = str_replace('{description}', $description, $cfg['ai_translate_desc_begin'] ?? 'Description: {description}');
        $end = $cfg['ai_translate_desc_end'] ?? '';
        $formatLines = implode("\n", array_map(
            fn($l) => '<' . strtoupper($l['code']) . '>' . $l['name'] . '</' . strtoupper($l['code']) . '>',
            $languages
        ));

        $fullPrompt = trim(
            $begin . "\n\n" . $userPrompt . "\n\n"
            . "Output format (XML tags required):\n" . $formatLines
            . ($end !== '' ? "\n\n" . $end : '')
        );

         
         
        $best = [];
        for ($attempt = 1; $attempt <= 3; $attempt++) {
            $result = CatalogueClient::aiCompletion(
                [['role' => 'user', 'content' => $fullPrompt]],
                'auto',
                0.5
            );
            if (!is_array($result) || !empty($result['_error'])) {
                continue;
            }
            $content = $result['choices'][0]['message']['content'] ?? '';
            if ($content === '') {
                continue;
            }
            $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)] = mb_substr($text, 0, 5000);
                    }
                }
            }
            if (count($translations) > count($best)) {
                $best = $translations;
            }
            if (count($best) >= count($languages)) {
                break;
            }
        }

        return $best;
    }

    

    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 aiRewriteTitle(string $title, array $options): ?string
    {
        if (self::$_aiConfig === null) {
            self::$_aiConfig = CatalogueClient::getAiConfig() ?: [];
        }
        $cfg = self::$_aiConfig;

        $userPrompt = $options['aiTitlePrompt'] ?? '';
        if ($userPrompt === '') {
            return null;
        }

        $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) {
            return null;
        }

        if (!preg_match('/<title>(.*?)<\/title>/si', $content, $m)) {
            return null;
        }

        $rewritten = trim($m[1], " \t\n\r\0\x0B\"'");
        if (mb_strlen($rewritten) < 5 || mb_strlen($rewritten) > 200) {
            return null;
        }

        return $rewritten;
    }

    

    private static function aiRewriteDescription(string $description, array $options): ?string
    {
        if (self::$_aiConfig === null) {
            self::$_aiConfig = CatalogueClient::getAiConfig() ?: [];
        }
        $cfg = self::$_aiConfig;

        $userPrompt = $options['aiDescPrompt'] ?? '';
        if ($userPrompt === '') {
            return null;
        }

        $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) {
            return null;
        }

        if (!preg_match('/<desc>(.*?)<\/desc>/si', $content, $m)) {
            return null;
        }

        $rewritten = trim($m[1]);
        if (mb_strlen($rewritten) < 10) {
            return null;
        }

        return $rewritten;
    }

    public static function parseDate(string $raw): ?string
    {
         
        $formats = [
            'Y-m-d H:i:s',        
            'Y-m-d\TH:i:s',       
            'Y-m-d H:i',          
            'Y-m-d',              
            'd/m/Y H:i:s',        
            'd/m/Y H:i',          
            'd/m/Y',              
            'm/d/Y',              
            'd-m-Y H:i:s',        
            'd-m-Y',              
            'M d, Y',             
            'F d, Y',             
            'M d Y',              
            'd M Y',              
            'd F Y',              
            'U',                  
        ];

        foreach ($formats as $fmt) {
            $dt = \DateTimeImmutable::createFromFormat($fmt, $raw);
            if ($dt !== false) {
                return $dt->format('Y-m-d H:i:s');
            }
        }

         
        $ts = strtotime($raw);
        if ($ts !== false && $ts > 0) {
            return date('Y-m-d H:i:s', $ts);
        }

        return null;
    }

    

    private static function isAllowedUrl(string $url): bool
    {
        $parsed = parse_url($url);
        if (!$parsed || !in_array($parsed['scheme'] ?? '', ['http', 'https'], true)) {
            return false;
        }
        $host = $parsed['host'] ?? '';
        if ($host === '') {
            return false;
        }
         
         
        $ips = gethostbynamel($host);
        if ($ips === false || $ips === []) {
            return false;
        }
        foreach ($ips as $ip) {
            if (!filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
                return false;
            }
        }
        return true;
    }

    private static function downloadImageData(string $url): ?string
    {
        if (!self::isAllowedUrl($url)) {
            return null;
        }

        $ctx = stream_context_create([
            'http' => [
                'timeout' => 10,
                'user_agent' => 'TubePress/1.0',
                'follow_location' => true,
                'max_redirects' => 3,
            ],
            'ssl' => ['verify_peer' => true, 'verify_peer_name' => true],
        ]);

        $data = @file_get_contents($url, false, $ctx);
        if ($data === false || strlen($data) < 100) {
            return null;
        }

        $mime = Mime::fromBuffer($data, $url);
        $allowed = ['image/jpeg', 'image/png', 'image/gif', 'image/webp'];

        if (!in_array($mime, $allowed, true)) {
            return null;
        }

        return $data;
    }

    private static function downloadImage(string $url, string $subdir = 'thumbnails'): ?string
    {
        if (!self::isAllowedUrl($url)) {
            return null;
        }

        $dir = APP_ROOT . '/public/uploads/' . $subdir;
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }

        $ctx = stream_context_create([
            'http' => [
                'timeout' => 10,
                'user_agent' => 'TubePress/1.0',
                'follow_location' => true,
                'max_redirects' => 3,
            ],
            'ssl' => ['verify_peer' => true, 'verify_peer_name' => true],
        ]);

        $data = @file_get_contents($url, false, $ctx);
        if ($data === false || strlen($data) < 100) {
            return null;
        }

         
        $mime = Mime::fromBuffer($data, $url);
        $allowed = ['image/jpeg' => 'jpg', 'image/png' => 'png', 'image/gif' => 'gif', 'image/webp' => 'webp'];

        if (!isset($allowed[$mime])) {
            return null;
        }

        $ext = $allowed[$mime];
        $name = time() . '_' . bin2hex(random_bytes(8)) . '.' . $ext;

        if (file_put_contents($dir . '/' . $name, $data) === false) {
            return null;
        }

        return $name;
    }

    private static function parseSize(string $size): int
    {
        $unit = strtolower(substr(trim($size), -1));
        $val = (int) $size;
        return match ($unit) {
            'g' => $val * 1073741824,
            'm' => $val * 1048576,
            'k' => $val * 1024,
            default => $val,
        };
    }
}
