<?php

declare(strict_types=1);

class Video
{
     
    public const VALID_TYPES = ['hetero', 'gay', 'hentai'];
    public const DEFAULT_TYPE = 'hetero';

    

    public static function localizeResults(array $videos): array
    {
        if (empty($videos) || Translator::locale() === Translator::getDefaultCode()) {
            return $videos;
        }

        $ids = array_column($videos, 'id');
        if (empty($ids)) {
            return $videos;
        }

        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $params = array_merge([Translator::locale()], $ids);

        $rows = Database::fetchAll(
            "SELECT video_id, title, description, slug FROM video_translations WHERE lang_code = ? AND video_id IN ({$placeholders})",
            $params
        );

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['video_id']] = $row;
        }

        foreach ($videos as &$video) {
            $vid = (int) $video['id'];
            if (isset($map[$vid])) {
                $tr = $map[$vid];
                if ($tr['title'] !== null && $tr['title'] !== '') {
                    $video['title'] = $tr['title'];
                }
                if ($tr['description'] !== null && $tr['description'] !== '') {
                    $video['description'] = $tr['description'];
                }
                if ($tr['slug'] !== null && $tr['slug'] !== '') {
                    $video['slug'] = $tr['slug'];
                }
            }
        }
        unset($video);

        return $videos;
    }

    

    public static function localize(?array $video): ?array
    {
        if ($video === null || Translator::locale() === Translator::getDefaultCode()) {
            return $video;
        }

        $row = Database::fetchOne(
            'SELECT title, description, slug FROM video_translations WHERE video_id = ? AND lang_code = ?',
            [(int) $video['id'], Translator::locale()]
        );

        if ($row) {
            if ($row['title'] !== null && $row['title'] !== '') {
                $video['title'] = $row['title'];
            }
            if ($row['description'] !== null && $row['description'] !== '') {
                $video['description'] = $row['description'];
            }
            if ($row['slug'] !== null && $row['slug'] !== '') {
                $video['slug'] = $row['slug'];
            }
        }

        return $video;
    }

    private static ?array $_availableTypesCache = null;

    public static function resetAvailableTypesCache(): void
    {
        self::$_availableTypesCache = null;
    }

    

    public static function defaultType(): string
    {
        $configured = Setting::get('default_video_type', '');
        if ($configured !== '' && in_array($configured, self::VALID_TYPES, true)) {
            return $configured;
        }
        return self::DEFAULT_TYPE;
    }

    

    public static function typeLabel(string $code): string
    {
        $defaults = ['hetero' => 'Straight', 'gay' => 'Gay', 'hentai' => 'Hentai'];
        return Setting::get('seo_type_label_' . $code, $defaults[$code] ?? ucfirst($code));
    }

    

    public static function typeLabels(): array
    {
        $labels = [];
        foreach (self::VALID_TYPES as $code) {
            $labels[$code] = self::typeLabel($code);
        }
        return $labels;
    }

    

    public static function typeSlug(string $code): string
    {
        $slug = Setting::get('video_type_slug_' . $code, '');
        return ($slug !== '') ? $slug : $code;
    }

    

    public static function typeFromSlug(string $slug): string
    {
        foreach (self::VALID_TYPES as $code) {
            if (self::typeSlug($code) === $slug) {
                return $code;
            }
        }
        return '';
    }

    

    public static function typeFieldOrder(string $column = 'video_type'): string
    {
        $quoted = array_map(fn($t) => "'" . $t . "'", self::VALID_TYPES);
        return 'FIELD(' . $column . ', ' . implode(', ', $quoted) . ')';
    }

    public static function availableTypes(): array
    {
        if (self::$_availableTypesCache !== null) {
            return self::$_availableTypesCache;
        }
        $rows = Database::fetchAll(
            "SELECT DISTINCT video_type FROM videos WHERE status = 'published'"
        );
        $found = array_column($rows, 'video_type');

         
        $default = self::defaultType();
        $ordered = [];
        if (in_array($default, $found, true)) {
            $ordered[] = $default;
        }
        foreach (self::VALID_TYPES as $t) {
            if ($t !== $default && in_array($t, $found, true)) {
                $ordered[] = $t;
            }
        }

        self::$_availableTypesCache = $ordered;
        return self::$_availableTypesCache;
    }

    public static function activeType(): string
    {
        $types = self::availableTypes();
        if (count($types) < 2) {
            return '';
        }
         
        $routerType = Router::typeCode();
        if ($routerType !== '' && in_array($routerType, $types, true)) {
            return $routerType;
        }
        $cookie = $_COOKIE['tp_video_type'] ?? '';
        if ($cookie !== '' && in_array($cookie, $types, true)) {
            return $cookie;
        }
        return $types[0] ?? '';
    }

    public static function find(int $id): ?array
    {
        $video = Database::fetchOne('SELECT * FROM videos WHERE id = ?', [$id]);
        if ($video) {
            self::loadRelations($video);
        }
        return $video;
    }

    public static function findBySlug(string $slug): ?array
    {
         
        if (Translator::locale() !== Translator::getDefaultCode()) {
            $trans = Database::fetchOne(
                'SELECT video_id FROM video_translations WHERE slug = ? AND lang_code = ?',
                [$slug, Translator::locale()]
            );
            if ($trans) {
                $video = self::find((int) $trans['video_id']);
                return $video ? self::localize($video) : null;
            }
        }

         
        $video = Database::fetchOne('SELECT * FROM videos WHERE slug = ?', [$slug]);
        if ($video) {
            self::loadRelations($video);
            $video = self::localize($video);
        }
        return $video;
    }

    

    private static function loadRelations(array &$video): void
    {
        $id = (int) $video['id'];
        $rows = Database::fetchAll(
            "SELECT 'category' as _type, c.id, c.name, c.slug, c.video_type, c.video_count, c.total_views, c.thumbnail, c.parent_id, c.sort_order, c.description, c.created_at FROM categories c JOIN video_categories vc ON c.id = vc.category_id WHERE vc.video_id = ?
             UNION ALL
             SELECT 'tag', t.id, t.name, t.slug, t.video_type, t.video_count, NULL, NULL, NULL, NULL, NULL, t.created_at FROM tags t JOIN video_tags vt ON t.id = vt.tag_id WHERE vt.video_id = ?
             UNION ALL
             SELECT 'performer', p.id, p.name, p.slug, p.video_type, p.video_count, p.total_views, p.photo, NULL, NULL, p.bio, p.created_at FROM performers p JOIN video_performers vp ON p.id = vp.performer_id WHERE vp.video_id = ?
             UNION ALL
             SELECT 'channel', s.id, s.name, s.slug, s.video_type, s.video_count, s.total_views, s.logo, NULL, NULL, s.description, s.created_at FROM channels s JOIN video_channels vs ON s.id = vs.channel_id WHERE vs.video_id = ?",
            [$id, $id, $id, $id]
        );

        $video['categories'] = [];
        $video['tags'] = [];
        $video['performers'] = [];
        $video['channels'] = [];

        foreach ($rows as $row) {
            $type = $row['_type'];
            unset($row['_type']);
            $video[$type === 'category' ? 'categories' : ($type === 'tag' ? 'tags' : ($type === 'performer' ? 'performers' : 'channels'))][] = $row;
        }

         
        $video['categories'] = Category::localizeResults($video['categories']);
        $video['tags']       = Tag::localizeResults($video['tags']);
        $video['performers'] = Performer::localizeResults($video['performers']);
        $video['channels']    = Channel::localizeResults($video['channels']);
    }

    public static function all(array $filters = [], int $limit = 24, int $offset = 0): array
    {
        $where = ['1=1'];
        $whereParams = [];
        $joins = [];
        $joinParams = [];

         
        if (!empty($filters['admin'])) {
            if (!empty($filters['status'])) {
                $where[] = 'v.status = ?';
                $whereParams[] = $filters['status'];
            }
        } elseif (!empty($filters['status'])) {
            $where[] = 'v.status = ?';
            $whereParams[] = $filters['status'];
        } else {
            $where[] = 'v.status = ?';
            $whereParams[] = 'published';
        }

        if (!empty($filters['category_id'])) {
            $joins[] = 'INNER JOIN video_categories vc_filter ON v.id = vc_filter.video_id AND vc_filter.category_id = ?';
            $joinParams[] = $filters['category_id'];
        }

        if (!empty($filters['tag_id'])) {
            $joins[] = 'INNER JOIN video_tags vt_filter ON v.id = vt_filter.video_id AND vt_filter.tag_id = ?';
            $joinParams[] = $filters['tag_id'];
        }

        if (!empty($filters['performer_id'])) {
            $joins[] = 'INNER JOIN video_performers vp_filter ON v.id = vp_filter.video_id AND vp_filter.performer_id = ?';
            $joinParams[] = $filters['performer_id'];
        }

        if (!empty($filters['channel_id'])) {
            $joins[] = 'INNER JOIN video_channels vs_filter ON v.id = vs_filter.video_id AND vs_filter.channel_id = ?';
            $joinParams[] = $filters['channel_id'];
        }

        if (!empty($filters['search'])) {
            $where[] = 'MATCH(v.title, v.description) AGAINST(? IN BOOLEAN MODE)';
            $whereParams[] = $filters['search'];
        }

        if (!empty($filters['is_featured'])) {
            $where[] = 'v.is_featured = 1';
        }

        if (isset($filters['has_thumbnail'])) {
            if ($filters['has_thumbnail'] === 'yes') {
                $where[] = "(v.video_folder IS NOT NULL OR (v.thumbnail IS NOT NULL AND v.thumbnail != ''))";
            } elseif ($filters['has_thumbnail'] === 'no') {
                $where[] = "(v.video_folder IS NULL AND (v.thumbnail IS NULL OR v.thumbnail = ''))";
            }
        }

        if (!empty($filters['video_type'])) {
            $where[] = 'v.video_type = ?';
            $whereParams[] = $filters['video_type'];
        }

         
        $allowedSorts = [
            // A video that has never been published has no publication date, so on the front end
            // (published only) the index-friendly column is enough. The admin list also shows drafts
            // and videos still converting, which would all sink to the last page: order those by the
            // moment they were added instead.
            'newest' => empty($filters['admin'])
                ? 'v.published_at DESC, v.id DESC'
                : 'COALESCE(v.published_at, v.created_at) DESC, v.id DESC',
            'popular' => 'v.views DESC',
            'most_liked' => 'v.likes DESC',
            'top_rated' => 'v.likes DESC',
            'trending' => 'v.views DESC',
            'oldest' => 'v.created_at ASC',
            'most_favorited' => 'v.favorites_count DESC',
        ];

         
        $allowedSorts = HookSystem::applyFilter('video.sort_options', $allowedSorts);

        $sortKey = $filters['sort'] ?? 'newest';
        $sort = $allowedSorts[$sortKey] ?? $allowedSorts['newest'];

        $joinStr = implode("\n", $joins);
        $whereStr = implode(' AND ', $where);
        $sql = "SELECT v.*
                FROM videos v
                {$joinStr}
                WHERE {$whereStr}
                ORDER BY {$sort} LIMIT ? OFFSET ?";
        $params = array_merge($joinParams, $whereParams, [$limit, $offset]);

        $results = Database::fetchAll($sql, $params);
        return empty($filters['admin']) ? self::localizeResults($results) : $results;
    }

    public const COUNT_FAST_PATH_MIN = 50000;

    public static function publishedTotalKey(string $videoType = ''): string
    {
        return 'stats_published_total' . ($videoType !== '' ? '_' . $videoType : '');
    }

    public static function refreshPublishedTotals(): void
    {
        $row = Database::fetchOne("SELECT COUNT(*) AS c FROM videos WHERE status = 'published'");
        Setting::set(self::publishedTotalKey(), (string) (int) ($row['c'] ?? 0));

        foreach (self::VALID_TYPES as $type) {
            $row = Database::fetchOne(
                "SELECT COUNT(*) AS c FROM videos WHERE status = 'published' AND video_type = ?",
                [$type]
            );
            Setting::set(self::publishedTotalKey($type), (string) (int) ($row['c'] ?? 0));
        }
    }

    private static function publishedTotalFastPath(array $filters): ?int
    {
        foreach (['category_id', 'tag_id', 'performer_id', 'channel_id', 'search',
                  'is_featured', 'has_thumbnail', 'admin'] as $k) {
            if (isset($filters[$k])) {
                return null;
            }
        }
        if (($filters['status'] ?? 'published') !== 'published') {
            return null;
        }

        $type = (string) ($filters['video_type'] ?? '');
        if ($type !== '' && !in_array($type, self::VALID_TYPES, true)) {
            return null;
        }

        $raw = Setting::get(self::publishedTotalKey($type), '');
        if ($raw === '' || $raw === null || !is_numeric($raw)) {
            return null;
        }
        $n = (int) $raw;

        return $n >= self::COUNT_FAST_PATH_MIN ? $n : null;
    }

    public const TAXONOMY_FAST_PATH_MIN = 5000;

    private const TAXONOMY_COUNT_SOURCES = [
        'category_id'  => 'categories',
        'tag_id'       => 'tags',
        'performer_id' => 'performers',
        'channel_id'   => 'channels',
    ];

    private static function taxonomyTotalFastPath(array $filters): ?int
    {
        foreach (['search', 'is_featured', 'has_thumbnail', 'admin', 'video_type'] as $k) {
            if (isset($filters[$k])) {
                return null;
            }
        }
        if (($filters['status'] ?? 'published') !== 'published') {
            return null;
        }

        $found = null;
        foreach (self::TAXONOMY_COUNT_SOURCES as $key => $table) {
            if (empty($filters[$key])) {
                continue;
            }
            if ($found !== null) {
                return null;
            }
            $found = [$table, (int) $filters[$key]];
        }
        if ($found === null) {
            return null;
        }

        [$table, $id] = $found;
        $row = Database::fetchOne("SELECT video_count FROM {$table} WHERE id = ?", [$id]);
        if ($row === null || $row['video_count'] === null) {
            return null;
        }
        $n = (int) $row['video_count'];

        return $n >= self::TAXONOMY_FAST_PATH_MIN ? $n : null;
    }

    public static function count(array $filters = []): int
    {
        $fast = self::publishedTotalFastPath($filters);
        if ($fast !== null) {
            return $fast;
        }

        $fast = self::taxonomyTotalFastPath($filters);
        if ($fast !== null) {
            return $fast;
        }

        $where = ['1=1'];
        $whereParams = [];
        $joins = [];
        $joinParams = [];

        if (!empty($filters['status'])) {
            $where[] = 'v.status = ?';
            $whereParams[] = $filters['status'];
        } elseif (empty($filters['admin'])) {
            $where[] = 'v.status = ?';
            $whereParams[] = 'published';
        }

        if (!empty($filters['category_id'])) {
            $joins[] = 'INNER JOIN video_categories vc_filter ON v.id = vc_filter.video_id AND vc_filter.category_id = ?';
            $joinParams[] = $filters['category_id'];
        }

        if (!empty($filters['tag_id'])) {
            $joins[] = 'INNER JOIN video_tags vt_filter ON v.id = vt_filter.video_id AND vt_filter.tag_id = ?';
            $joinParams[] = $filters['tag_id'];
        }

        if (!empty($filters['performer_id'])) {
            $joins[] = 'INNER JOIN video_performers vp_filter ON v.id = vp_filter.video_id AND vp_filter.performer_id = ?';
            $joinParams[] = $filters['performer_id'];
        }

        if (!empty($filters['channel_id'])) {
            $joins[] = 'INNER JOIN video_channels vs_filter ON v.id = vs_filter.video_id AND vs_filter.channel_id = ?';
            $joinParams[] = $filters['channel_id'];
        }

        if (!empty($filters['search'])) {
            $where[] = 'MATCH(v.title, v.description) AGAINST(? IN BOOLEAN MODE)';
            $whereParams[] = $filters['search'];
        }

        if (!empty($filters['is_featured'])) {
            $where[] = 'v.is_featured = 1';
        }

        if (isset($filters['has_thumbnail'])) {
            if ($filters['has_thumbnail'] === 'yes') {
                $where[] = "(v.video_folder IS NOT NULL OR (v.thumbnail IS NOT NULL AND v.thumbnail != ''))";
            } elseif ($filters['has_thumbnail'] === 'no') {
                $where[] = "(v.video_folder IS NULL AND (v.thumbnail IS NULL OR v.thumbnail = ''))";
            }
        }

        if (!empty($filters['video_type'])) {
            $where[] = 'v.video_type = ?';
            $whereParams[] = $filters['video_type'];
        }

        $joinStr = implode("\n", $joins);
        $whereStr = implode(' AND ', $where);
        $sql = "SELECT COUNT(*) FROM videos v {$joinStr} WHERE {$whereStr}";
        $params = array_merge($joinParams, $whereParams);

        $result = Database::fetchOne($sql, $params);
        return (int) ($result ? reset($result) : 0);
    }

    public static function create(array $data): int
    {
        return Database::insert('videos', $data);
    }

    public static function update(int $id, array $data): void
    {
        Database::update('videos', $data, 'id = ?', [$id]);
    }

    public static function delete(int $id): void
    {
        $result = self::deleteMany([$id]);
        foreach ($result['assets'] as $video) {
            if (!empty($video['video_folder'])) {
                VideoAsset::deleteFolder(
                    $video['video_folder'],
                    !empty($video['storage_id']) ? (int) $video['storage_id'] : null
                );
            }
        }
    }

    /**
     * Tables carrying a video_id with no foreign key: nothing cascades them, so a deleted video
     * used to leave rows behind for ever (measured: view log, CTR hourly stats and the homepage
     * pool all kept orphans). They are cleaned explicitly here.
     */
    private const VIDEO_ORPHAN_TABLES = [
        'transcode_jobs', 'ctr_homepage_pool', 'ctr_video_context', 'ctr_video_hourly', 'video_view_log',
    ];

    /** taxonomy table, pivot table, pivot FK column */
    private const VIDEO_TAXONOMIES = [
        ['tags', 'video_tags', 'tag_id'],
        ['categories', 'video_categories', 'category_id'],
        ['performers', 'video_performers', 'performer_id'],
        ['channels', 'video_channels', 'channel_id'],
    ];

    /**
     * Delete a batch of videos with set-based SQL: a fixed handful of queries for the whole batch
     * instead of ~47 per video. Deleting one at a time re-scanned the pivot tables and recomputed
     * the same popular tags once per video; here every taxonomy touched by the batch is cleaned and
     * recounted exactly once.
     *
     * Returns ['deleted' => int, 'assets' => [['video_folder'=>..., 'storage_id'=>...], ...]] so the
     * caller can clean storage AFTER the transaction commits - a remote round trip must never be
     * held inside a database transaction.
     */
    public static function deleteMany(array $ids): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids), static fn($i) => $i > 0)));
        if (!$ids) return ['deleted' => 0, 'assets' => []];

        $ph = implode(',', array_fill(0, count($ids), '?'));

        $assets = Database::fetchAll(
            "SELECT id, video_folder, storage_id FROM videos WHERE id IN ({$ph})",
            $ids
        );
        if (!$assets) return ['deleted' => 0, 'assets' => []];

        // Only touch rows that really exist, so a stale id cannot inflate the progress counter.
        $ids = array_map('intval', array_column($assets, 'id'));
        $ph  = implode(',', array_fill(0, count($ids), '?'));

        Database::transaction(static function () use ($ids, $ph) {
            // Taxonomy ids touched anywhere in this batch, collected once.
            $touched = [];
            foreach (self::VIDEO_TAXONOMIES as [$table, $pivot, $fk]) {
                $touched[$table] = array_map('intval', array_column(
                    Database::fetchAll("SELECT DISTINCT {$fk} AS id FROM {$pivot} WHERE video_id IN ({$ph})", $ids),
                    'id'
                ));
            }

            // Give transcode workers their slots back before the jobs disappear.
            foreach (Database::fetchAll(
                "SELECT worker, COUNT(*) AS cnt FROM transcode_jobs
                  WHERE video_id IN ({$ph}) AND status = 'processing' AND worker IS NOT NULL
                  GROUP BY worker", $ids) as $w) {
                Database::query(
                    'UPDATE transcode_servers SET active_jobs = IF(active_jobs >= ?, active_jobs - ?, 0) WHERE name = ?',
                    [(int) $w['cnt'], (int) $w['cnt'], $w['worker']]
                );
            }

            foreach (self::VIDEO_ORPHAN_TABLES as $table) {
                try {
                    Database::query("DELETE FROM {$table} WHERE video_id IN ({$ph})", $ids);
                } catch (\Throwable $e) {
                    error_log("Video::deleteMany: could not clean {$table}: " . $e->getMessage());
                }
            }
            // Import records are kept as history; they are only unlinked from the dead video.
            try {
                Database::query("UPDATE feed_items SET video_id = NULL WHERE video_id IN ({$ph})", $ids);
            } catch (\Throwable $e) {
                error_log('Video::deleteMany: could not unlink feed_items: ' . $e->getMessage());
            }

            // Cascades take care of the pivots, comments, favourites, history, likes, translations, reports.
            Database::query("DELETE FROM videos WHERE id IN ({$ph})", $ids);

            foreach (self::VIDEO_TAXONOMIES as [$table, $pivot, $fk]) {
                $tids = $touched[$table];
                if (!$tids) continue;
                $tph = implode(',', array_fill(0, count($tids), '?'));

                // NOT EXISTS uses the pivot index; the previous NOT IN (SELECT DISTINCT ...) scanned
                // the whole pivot table, once per video and per taxonomy.
                Database::query(
                    "DELETE FROM {$table} WHERE id IN ({$tph})
                       AND NOT EXISTS (SELECT 1 FROM {$pivot} WHERE {$pivot}.{$fk} = {$table}.id)",
                    $tids
                );
                // One set-based recount for every survivor, instead of two queries per video per row.
                Database::query(
                    "UPDATE {$table} x SET x.video_count = (
                         SELECT COUNT(*) FROM {$pivot} pv JOIN videos v ON v.id = pv.video_id
                          WHERE pv.{$fk} = x.id AND v.status = 'published')
                      WHERE x.id IN ({$tph})",
                    $tids
                );
            }
        });

        return ['deleted' => count($ids), 'assets' => $assets];
    }

    private static function cleanupOrphansAndUpdateCounts(string $table, string $pivotTable, string $fkColumn, array $ids, string $modelClass): void
    {
        if (empty($ids)) {
            return;
        }

        $ids = array_map('intval', $ids);
        $ph = implode(',', array_fill(0, count($ids), '?'));

         
         
         
         
         
        Database::query(
            "DELETE FROM {$table} WHERE id IN ({$ph})
               AND id NOT IN (SELECT DISTINCT {$fkColumn} FROM {$pivotTable})",
            $ids
        );

         
        $survivors = Database::fetchAll(
            "SELECT id FROM {$table} WHERE id IN ({$ph})",
            $ids
        );
        foreach ($survivors as $row) {
            $modelClass::updateCounts((int) $row['id']);
        }
    }

    public static function syncCategories(int $id, array $categoryIds): void
    {
         
         
         
        $categoryIds = array_values(array_unique(array_map('intval', $categoryIds)));
        Database::transaction(function () use ($id, $categoryIds) {
            $oldIds = array_column(
                Database::fetchAll('SELECT category_id FROM video_categories WHERE video_id = ?', [$id]),
                'category_id'
            );
            Database::delete('video_categories', 'video_id = ?', [$id]);
            foreach ($categoryIds as $catId) {
                Database::insert('video_categories', ['video_id' => $id, 'category_id' => (int) $catId]);
            }
            $oldIds = array_map('intval', $oldIds);
            self::shiftEntityCounts(
                'categories',
                array_values(array_diff($categoryIds, $oldIds)),
                array_values(array_diff($oldIds, $categoryIds)),
                $id,
                true
            );
        });
    }

    public static function syncTags(int $id, array $tagIds): void
    {
        $tagIds = array_values(array_unique(array_map('intval', $tagIds)));
        Database::transaction(function () use ($id, $tagIds) {
            $oldIds = array_column(
                Database::fetchAll('SELECT tag_id FROM video_tags WHERE video_id = ?', [$id]),
                'tag_id'
            );
            Database::delete('video_tags', 'video_id = ?', [$id]);
            foreach ($tagIds as $tagId) {
                Database::insert('video_tags', ['video_id' => $id, 'tag_id' => (int) $tagId]);
            }
            $oldIds = array_map('intval', $oldIds);
            self::shiftEntityCounts(
                'tags',
                array_values(array_diff($tagIds, $oldIds)),
                array_values(array_diff($oldIds, $tagIds)),
                $id,
                false
            );
        });
    }



    private static function shiftEntityCounts(string $table, array $added, array $removed, int $videoId, bool $withViews): void
    {
        if (!$added && !$removed) {
            return;
        }
        $video = Database::fetchOne('SELECT status, views FROM videos WHERE id = ?', [$videoId]);
        if (!$video || $video['status'] !== 'published') {
            return;
        }
        $views = (int) $video['views'];
        foreach ([[$added, 1], [$removed, -1]] as [$ids, $sign]) {
            if (!$ids) {
                continue;
            }
            $ph = implode(',', array_fill(0, count($ids), '?'));
            $set = 'video_count = GREATEST(CAST(video_count AS SIGNED) + ?, 0)';
            $params = [$sign];
            if ($withViews) {
                $set .= ', total_views = GREATEST(CAST(total_views AS SIGNED) + ?, 0)';
                $params[] = $sign * $views;
            }
            Database::query(
                "UPDATE {$table} SET {$set} WHERE id IN ({$ph})",
                array_merge($params, array_map('intval', $ids))
            );
        }
    }

    public static function syncPerformers(int $id, array $performerIds): void
    {
        $performerIds = array_values(array_unique(array_map('intval', $performerIds)));
        Database::transaction(function () use ($id, $performerIds) {
            $oldIds = array_column(
                Database::fetchAll('SELECT performer_id FROM video_performers WHERE video_id = ?', [$id]),
                'performer_id'
            );
            Database::delete('video_performers', 'video_id = ?', [$id]);
            foreach ($performerIds as $perfId) {
                Database::insert('video_performers', ['video_id' => $id, 'performer_id' => (int) $perfId]);
            }
            $oldIds = array_map('intval', $oldIds);
            self::shiftEntityCounts(
                'performers',
                array_values(array_diff($performerIds, $oldIds)),
                array_values(array_diff($oldIds, $performerIds)),
                $id,
                true
            );

            self::refreshPerformerName($id);
        });
    }

    

    public static function refreshPerformerName(int $id): void
    {
        $row = Database::fetchOne(
            'SELECT MIN(p.name) as pname FROM video_performers vp JOIN performers p ON vp.performer_id = p.id WHERE vp.video_id = ?',
            [$id]
        );
        Database::query('UPDATE videos SET performer_name = ? WHERE id = ?', [$row['pname'] ?? null, $id]);
    }

    

    public static function refreshFavoritesCount(int $id): void
    {
        $cnt = Database::count('user_favorites', 'video_id = ?', [$id]);
        Database::query('UPDATE videos SET favorites_count = ? WHERE id = ?', [$cnt, $id]);
    }

    

    public static function incrementViews(int $id): void
    {
        $ip = RateLimiter::ip();
        $fp = RateLimiter::fingerprint();
        $hash = md5($ip . $fp);
        $window = 30;  

         
         
         
        $stmt = Database::query(
            'INSERT INTO video_view_log (video_id, viewer_hash, viewed_at)
             VALUES (?, UNHEX(?), NOW())
             ON DUPLICATE KEY UPDATE
                viewed_at = IF(viewed_at <= DATE_SUB(NOW(), INTERVAL ? MINUTE), NOW(), viewed_at)',
            [$id, $hash, $window]
        );

         
        if ($stmt->rowCount() > 0) {
            Database::query('UPDATE videos SET views = views + 1, recent_views = recent_views + 1 WHERE id = ?', [$id]);
            try {
                Database::query(
                    'INSERT INTO video_view_daily (view_date, views) VALUES (?, 1)
                     ON DUPLICATE KEY UPDATE views = views + 1',
                    [date('Y-m-d')]
                );
            } catch (\Throwable) {
            }
        }

         
        if (random_int(1, 100) === 1) {
            Database::query('DELETE FROM video_view_log WHERE viewed_at < DATE_SUB(NOW(), INTERVAL 2 HOUR) LIMIT 10000');
        }
    }

    public static function getCategories(int $id): array
    {
        return Database::fetchAll(
            'SELECT c.* FROM categories c JOIN video_categories vc ON c.id = vc.category_id WHERE vc.video_id = ?',
            [$id]
        );
    }

    public static function getTags(int $id): array
    {
        return Database::fetchAll(
            'SELECT t.* FROM tags t JOIN video_tags vt ON t.id = vt.tag_id WHERE vt.video_id = ?',
            [$id]
        );
    }

    public static function getPerformers(int $id): array
    {
        return Database::fetchAll(
            'SELECT p.* FROM performers p JOIN video_performers vp ON p.id = vp.performer_id WHERE vp.video_id = ?',
            [$id]
        );
    }

    public static function getChannels(int $id): array
    {
        return Database::fetchAll(
            'SELECT s.* FROM channels s JOIN video_channels vs ON s.id = vs.channel_id WHERE vs.video_id = ?',
            [$id]
        );
    }

    public static function syncChannels(int $id, array $channelIds): void
    {
        $channelIds = array_values(array_unique(array_map('intval', $channelIds)));
        Database::transaction(function () use ($id, $channelIds) {
            $oldIds = array_column(
                Database::fetchAll('SELECT channel_id FROM video_channels WHERE video_id = ?', [$id]),
                'channel_id'
            );
            Database::delete('video_channels', 'video_id = ?', [$id]);
            foreach ($channelIds as $channelId) {
                Database::insert('video_channels', ['video_id' => $id, 'channel_id' => (int) $channelId]);
            }
            $oldIds = array_map('intval', $oldIds);
            self::shiftEntityCounts(
                'channels',
                array_values(array_diff($channelIds, $oldIds)),
                array_values(array_diff($oldIds, $channelIds)),
                $id,
                true
            );
        });
    }

    public static function similar(int $id, int $limit = 8): array
    {
        $override = HookSystem::applyFilter('video.similar', null, $id, $limit);
        if ($override !== null) {
            return self::localizeResults($override);
        }

         
        $catIds = array_column(
            Database::fetchAll('SELECT category_id FROM video_categories WHERE video_id = ?', [$id]),
            'category_id'
        );
        $tagIds = array_column(
            Database::fetchAll('SELECT tag_id FROM video_tags WHERE video_id = ?', [$id]),
            'tag_id'
        );

        if (empty($catIds) && empty($tagIds)) {
            return [];
        }

         
        $scoreParts = [];
        $scoreParams = [];

        if (!empty($catIds)) {
            $ph = implode(',', array_fill(0, count($catIds), '?'));
            $scoreParts[] = "SELECT video_id, COUNT(DISTINCT category_id) AS score
                         FROM video_categories WHERE category_id IN ({$ph}) GROUP BY video_id";
            $scoreParams = array_merge($scoreParams, $catIds);
        }
        if (!empty($tagIds)) {
            $ph = implode(',', array_fill(0, count($tagIds), '?'));
            $scoreParts[] = "SELECT video_id, COUNT(DISTINCT tag_id) AS score
                         FROM video_tags WHERE tag_id IN ({$ph}) GROUP BY video_id";
            $scoreParams = array_merge($scoreParams, $tagIds);
        }

        $scoreUnion = implode("\n                         UNION ALL\n                         ", $scoreParts);


        $params = array_merge($scoreParams, ['published', $id], [$limit]);

        return self::localizeResults(Database::fetchAll(
            "SELECT v.*, k._relevance
             FROM (
                 SELECT m.video_id, m._relevance, vs.views
                 FROM (
                     SELECT video_id, SUM(score) AS _relevance
                     FROM (
                         {$scoreUnion}
                     ) s
                     GROUP BY video_id
                 ) m
                 JOIN videos vs ON vs.id = m.video_id
                 WHERE vs.status = ? AND vs.id != ?
                 ORDER BY m._relevance DESC, vs.views DESC
                 LIMIT ?
             ) k
             JOIN videos v ON v.id = k.video_id
             ORDER BY k._relevance DESC, k.views DESC",
            $params
        ));
    }

    public static function recommended(int $excludeId, array $excludeIds = [], int $limit = 12): array
    {
        $override = HookSystem::applyFilter('video.recommended', null, $excludeId, $excludeIds, $limit);
        if ($override !== null) {
            return self::localizeResults($override);
        }

        $allExclude = array_merge([$excludeId], $excludeIds);
        $placeholders = implode(',', array_fill(0, count($allExclude), '?'));

         
        $range = Database::fetchOne(
            "SELECT MIN(id) as min_id, MAX(id) as max_id FROM videos WHERE status = ?",
            ['published']
        );

        if (!$range || !$range['min_id']) {
            return [];
        }

         
        $randId = random_int((int) $range['min_id'], (int) $range['max_id']);
        $params = array_merge($allExclude, ['published', $randId, $limit]);

        $results = Database::fetchAll(
            "SELECT v.*
             FROM videos v
             WHERE v.id NOT IN ({$placeholders}) AND v.status = ? AND v.id >= ?
             ORDER BY v.id LIMIT ?",
            $params
        );

         
        if (count($results) < $limit) {
            $gotIds = array_column($results, 'id');
            $allExclude2 = array_merge($allExclude, $gotIds);
            $ph2 = implode(',', array_fill(0, count($allExclude2), '?'));
            $remaining = $limit - count($results);
            $params2 = array_merge($allExclude2, ['published', $remaining]);
            $more = Database::fetchAll(
                "SELECT v.*
                 FROM videos v
                 WHERE v.id NOT IN ({$ph2}) AND v.status = ?
                 ORDER BY v.id LIMIT ?",
                $params2
            );
            $results = array_merge($results, $more);
        }

        return self::localizeResults($results);
    }

    public static function translations(int $videoId): array
    {
        $rows = Database::fetchAll(
            'SELECT lang_code, title, slug FROM video_translations WHERE video_id = ? ORDER BY lang_code',
            [$videoId]
        );
        $result = [];
        foreach ($rows as $row) {
            $result[$row['lang_code']] = $row;
        }
        return $result;
    }

    public static function trending(int $limit = 12): array
    {
        $override = HookSystem::applyFilter('video.trending', null, $limit);
        if ($override !== null) {
            return self::localizeResults($override);
        }

        return self::localizeResults(Database::fetchAll(
            "SELECT v.*
             FROM videos v
             WHERE v.status = ? AND v.published_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
             ORDER BY v.views DESC LIMIT ?",
            ['published', $limit]
        ));
    }
}
