mediaplayer/cli/admin.php

#!/usr/bin/env php
<?php
/**
 * Website admin CLI for Ty Clifford Media.
 */

if (PHP_SAPI !== 'cli') {
    http_response_code(404);
    exit;
}

require_once __DIR__ . '/../includes/db.php';

try {
    exit(cli_main($argv));
} catch (Throwable $e) {
    fwrite(STDERR, "ERROR: " . $e->getMessage() . PHP_EOL);
    exit(1);
}

function cli_main(array $argv): int {
    array_shift($argv);
    $command = array_shift($argv) ?? 'help';
    $parsed = cli_parse_args($argv);

    if (isset($parsed['options']['help']) || $command === '-h' || $command === '--help') {
        $command = 'help';
    }

    if (cli_command_requires_database_write($command)) {
        cli_assert_database_writable();
    }

    return match ($command) {
        'help' => cli_help(),
        'site:status', 'status' => cli_site_status(),
        'settings:list' => cli_settings_list($parsed),
        'settings:get' => cli_settings_get($parsed),
        'settings:set' => cli_settings_set($parsed),
        'admin:password', 'password:set' => cli_admin_password($parsed),
        'video:list', 'videos:list' => cli_video_list($parsed),
        'video:show', 'videos:show' => cli_video_show($parsed),
        'video:add' => cli_video_add($parsed),
        'video:update' => cli_video_update($parsed),
        'video:publish' => cli_video_set_published($parsed, 1),
        'video:unpublish' => cli_video_set_published($parsed, 0),
        'video:toggle' => cli_video_toggle_published($parsed),
        'video:delete' => cli_video_delete($parsed),
        'source:list', 'sources:list' => cli_source_list($parsed),
        'source:add' => cli_source_add($parsed),
        'source:update' => cli_source_update($parsed),
        'source:delete' => cli_source_delete($parsed),
        'maintenance:catalogue-order' => cli_maintenance_catalogue_order(),
        'maintenance:youtube-timestamps' => cli_maintenance_youtube_timestamps($parsed),
        'maintenance:external-views' => cli_maintenance_external_views($parsed),
        'maintenance:all' => cli_maintenance_all($parsed),
        'db:path' => cli_db_path(),
        'db:doctor' => cli_db_doctor(),
        'db:backup' => cli_db_backup($parsed),
        'db:vacuum' => cli_db_vacuum(),
        default => cli_unknown($command),
    };
}

function cli_parse_args(array $tokens): array {
    $options = [];
    $args = [];
    $count = count($tokens);

    for ($i = 0; $i < $count; $i++) {
        $token = $tokens[$i];

        if ($token === '--') {
            for ($j = $i + 1; $j < $count; $j++) $args[] = $tokens[$j];
            break;
        }

        if ($token === '-h') {
            $options['help'] = true;
            continue;
        }
        if ($token === '-y') {
            $options['yes'] = true;
            continue;
        }

        if (str_starts_with($token, '--no-')) {
            $options[cli_option_key(substr($token, 5))] = false;
            continue;
        }

        if (str_starts_with($token, '--')) {
            $body = substr($token, 2);
            if (str_contains($body, '=')) {
                [$key, $value] = explode('=', $body, 2);
                $options[cli_option_key($key)] = $value;
                continue;
            }

            $key = cli_option_key($body);
            if ($i + 1 < $count && !str_starts_with($tokens[$i + 1], '-')) {
                $options[$key] = $tokens[++$i];
            } else {
                $options[$key] = true;
            }
            continue;
        }

        $args[] = $token;
    }

    return ['options' => $options, 'args' => $args];
}

function cli_option_key(string $key): string {
    return str_replace('-', '_', trim($key));
}

function cli_help(): int {
    echo <<<HELP
Ty Clifford Media admin CLI

Usage:
  php cli/admin.php <command> [arguments] [options]

Website:
  site:status
      Show database, media, public settings, and video totals.

Settings:
  settings:list [--show-secret]
  settings:get <key> [--show-secret]
  settings:set <key> <value>
      Known booleans accept 1/0, true/false, yes/no, on/off.
      Use admin:password for admin_password.

Admin:
  admin:password [--password=newpass | --stdin]
      Update the admin password with a password_hash() hash.

Videos:
  video:list [--all|--published|--draft] [--search=text] [--page=1] [--per-page=20]
  video:show <id|slug>
  video:add --title=Title [--description=text] [--slug=slug] [--url=https://...] [--file=media/videos/file.mp4] [--copy] [--draft]
  video:update <id|slug> [--title=Title] [--description=text] [--slug=slug] [--thumbnail=path] [--duration=seconds] [--view-count=n] [--external-view-count=n] [--sort-order=n] [--published=1|0]
  video:publish <id|slug>
  video:unpublish <id|slug>
  video:toggle <id|slug>
  video:delete <id|slug> [--yes] [--keep-files]

Sources:
  source:list <video-id|slug>
  source:add <video-id|slug> (--url=https://... | --file=media/videos/file.mp4) [--copy] [--label=text] [--quality=720p] [--sort-order=n]
  source:update <source-id> [--url=https://... | --file=media/videos/file.mp4] [--copy] [--label=text] [--quality=720p] [--sort-order=n] [--mime=type]
  source:delete <source-id> [--yes] [--keep-file]

Maintenance:
  maintenance:catalogue-order
  maintenance:youtube-timestamps [--limit=25]
  maintenance:external-views [--limit=25]
  maintenance:all [--limit=25]

Database:
  db:path
  db:doctor
  db:backup [--output=/path/to/media.db]
  db:vacuum

HELP;
    return 0;
}

function cli_command_requires_database_write(string $command): bool {
    return in_array($command, [
        'settings:set',
        'admin:password',
        'password:set',
        'video:add',
        'video:update',
        'video:publish',
        'video:unpublish',
        'video:toggle',
        'video:delete',
        'source:add',
        'source:update',
        'source:delete',
        'maintenance:catalogue-order',
        'maintenance:youtube-timestamps',
        'maintenance:external-views',
        'maintenance:all',
        'db:vacuum',
    ], true);
}

function cli_assert_database_writable(): void {
    $paths = cli_database_writable_checks();
    $problems = [];

    foreach ($paths as $check) {
        if (!$check['ok']) $problems[] = $check['message'];
    }

    if ($problems) {
        $details = implode(PHP_EOL . '  - ', $problems);
        throw new RuntimeException(
            "Database is not writable by the current CLI user (" . cli_current_user_label() . ")." .
            PHP_EOL . "  - " . $details .
            PHP_EOL . "Run db:doctor for details, then make the data directory and media.db writable by this user."
        );
    }
}

function cli_unknown(string $command): int {
    fwrite(STDERR, "Unknown command: {$command}" . PHP_EOL . PHP_EOL);
    cli_help();
    return 1;
}

function cli_db(): PDO {
    return get_db();
}

function cli_site_status(): int {
    $db = cli_db();
    $stats = [
        'Site title' => setting('site_title', 'Ty Clifford'),
        'Site subtitle' => setting('site_sub', 'tyclifford.com / media'),
        'URL mode' => url_rewrite_mode(),
        'Live enabled' => cli_yes_no(live_enabled()),
        'Catalogue per page' => setting('catalogue_per_page', '10'),
        'Videos total' => (string)$db->query("SELECT COUNT(*) FROM videos")->fetchColumn(),
        'Videos published' => (string)$db->query("SELECT COUNT(*) FROM videos WHERE published=1")->fetchColumn(),
        'Videos draft' => (string)$db->query("SELECT COUNT(*) FROM videos WHERE published=0")->fetchColumn(),
        'Sources total' => (string)$db->query("SELECT COUNT(*) FROM video_sources")->fetchColumn(),
        'Sources remote' => (string)$db->query("SELECT COUNT(*) FROM video_sources WHERE source_type='remote'")->fetchColumn(),
        'Sources local' => (string)$db->query("SELECT COUNT(*) FROM video_sources WHERE source_type!='remote'")->fetchColumn(),
        'Views total' => format_count(total_video_views(false)),
        'Database' => DB_PATH,
        'Database size' => cli_bytes(file_exists(DB_PATH) ? filesize(DB_PATH) : 0),
        'Media directory' => MEDIA_DIR,
        'Media size' => cli_bytes(cli_directory_size(MEDIA_DIR)),
    ];

    foreach ($stats as $label => $value) {
        printf("%-20s %s" . PHP_EOL, $label . ':', $value);
    }
    return 0;
}

function cli_settings_list(array $parsed): int {
    $rows = cli_db()->query("SELECT key, value FROM settings ORDER BY key ASC")->fetchAll();
    $show_secret = cli_bool($parsed['options'], 'show_secret', false);
    $table = [];

    foreach ($rows as $row) {
        $key = (string)$row['key'];
        $value = (string)$row['value'];
        if ($key === 'admin_password' && !$show_secret) $value = '[hidden password hash]';
        $table[] = [$key, $value];
    }

    cli_table(['key', 'value'], $table);
    return 0;
}

function cli_settings_get(array $parsed): int {
    $key = cli_arg($parsed, 0, 'settings:get requires a setting key.');
    $stmt = cli_db()->prepare("SELECT value FROM settings WHERE key=?");
    $stmt->execute([$key]);
    $value = $stmt->fetchColumn();

    if ($value === false) {
        fwrite(STDERR, "Setting not found: {$key}" . PHP_EOL);
        return 1;
    }

    if ($key === 'admin_password' && !cli_bool($parsed['options'], 'show_secret', false)) {
        echo '[hidden password hash]' . PHP_EOL;
    } else {
        echo $value . PHP_EOL;
    }
    return 0;
}

function cli_settings_set(array $parsed): int {
    $key = cli_arg($parsed, 0, 'settings:set requires a setting key.');
    $value = cli_arg($parsed, 1, 'settings:set requires a setting value.');

    if ($key === 'admin_password') {
        throw new RuntimeException('Use admin:password so the password is hashed correctly.');
    }

    $value = cli_normalize_setting($key, $value);
    set_setting($key, $value);
    echo "Updated {$key}." . PHP_EOL;
    return 0;
}

function cli_normalize_setting(string $key, string $value): string {
    $bools = [
        'public_show_video_views',
        'public_show_total_views',
        'public_show_page_stats',
        'live_enabled',
    ];

    if ($key === 'catalogue_per_page') {
        $number = cli_validate_int_value($value, 'catalogue_per_page', 1, 50);
        return (string)$number;
    }

    if ($key === 'url_rewrite_mode') {
        $value = strtolower(trim($value));
        if (!in_array($value, ['pretty', 'query'], true)) {
            throw new RuntimeException('url_rewrite_mode must be pretty or query.');
        }
        return $value;
    }

    if (in_array($key, $bools, true)) {
        return cli_parse_bool_value($value, $key) ? '1' : '0';
    }

    return trim($value);
}

function cli_admin_password(array $parsed): int {
    $options = $parsed['options'];

    if (isset($options['password']) && cli_bool($options, 'stdin', false)) {
        throw new RuntimeException('Use either --password or --stdin, not both.');
    }

    if (isset($options['password'])) {
        $password = (string)$options['password'];
    } elseif (cli_bool($options, 'stdin', false)) {
        $password = trim(stream_get_contents(STDIN));
    } else {
        $password = cli_prompt_secret('New admin password: ');
        $confirm = cli_prompt_secret('Confirm password: ');
        if ($password !== $confirm) throw new RuntimeException('Passwords do not match.');
    }

    if (strlen($password) < 6) {
        throw new RuntimeException('New password must be at least 6 characters.');
    }

    set_setting('admin_password', password_hash($password, PASSWORD_DEFAULT));
    echo "Admin password updated." . PHP_EOL;
    return 0;
}

function cli_video_list(array $parsed): int {
    $options = $parsed['options'];
    $page = cli_int($options, 'page', 1, 1, 1000000);
    $per_page = cli_int($options, 'per_page', 20, 1, 100);
    $search = isset($options['search']) ? trim((string)$options['search']) : '';
    $filter = 'all';

    if (cli_bool($options, 'published', false)) $filter = 'published';
    if (cli_bool($options, 'draft', false)) $filter = 'draft';
    if (cli_bool($options, 'all', false)) $filter = 'all';

    $result = cli_query_videos($filter, $search, $page, $per_page);
    $rows = [];

    foreach ($result['videos'] as $video) {
        $rows[] = [
            (string)$video['id'],
            ((int)$video['published']) ? 'yes' : 'no',
            (string)$video['sort_order'],
            cli_truncate((string)$video['title'], 34),
            (string)$video['slug'],
            (string)$video['source_count'],
            format_count(video_total_views($video)),
            substr((string)$video['created_at'], 0, 10),
        ];
    }

    cli_table(['id', 'pub', 'sort', 'title', 'slug', 'src', 'views', 'created'], $rows);
    echo "Page {$result['page']} of {$result['total_pages']} ({$result['total']} video";
    echo ((int)$result['total'] === 1 ? '' : 's') . ")." . PHP_EOL;
    return 0;
}

function cli_query_videos(string $filter, string $search, int $page, int $per_page): array {
    $db = cli_db();
    $where_parts = [];
    $bind = [];

    if ($filter === 'published') $where_parts[] = 'v.published=1';
    if ($filter === 'draft') $where_parts[] = 'v.published=0';
    if ($search !== '') {
        $where_parts[] = '(v.title LIKE :q OR v.description LIKE :q OR v.slug LIKE :q)';
        $bind[':q'] = '%' . $search . '%';
    }

    $where = $where_parts ? 'WHERE ' . implode(' AND ', $where_parts) : '';
    $count = $db->prepare("SELECT COUNT(*) FROM videos v {$where}");
    foreach ($bind as $key => $value) $count->bindValue($key, $value);
    $count->execute();
    $total = (int)$count->fetchColumn();
    $total_pages = max(1, (int)ceil($total / $per_page));
    $page = min($page, $total_pages);
    $offset = ($page - 1) * $per_page;

    $stmt = $db->prepare("
        SELECT v.*,
               COUNT(vs.id) AS source_count
        FROM videos v
        LEFT JOIN video_sources vs ON vs.video_id = v.id
        {$where}
        GROUP BY v.id
        ORDER BY v.sort_order ASC, datetime(v.created_at) DESC, v.created_at DESC, v.id DESC
        LIMIT :limit OFFSET :offset
    ");
    foreach ($bind as $key => $value) $stmt->bindValue($key, $value);
    $stmt->bindValue(':limit', $per_page, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    return [
        'videos' => $stmt->fetchAll(),
        'total' => $total,
        'page' => $page,
        'per_page' => $per_page,
        'total_pages' => $total_pages,
    ];
}

function cli_video_show(array $parsed): int {
    $video = cli_require_video(cli_arg($parsed, 0, 'video:show requires an id or slug.'));
    cli_print_video($video);
    return 0;
}

function cli_video_add(array $parsed): int {
    $options = $parsed['options'];
    $title = cli_option_string($options, 'title', true, 'video:add requires --title.');
    $description = cli_option_string($options, 'description', false, '');
    $slug = isset($options['slug']) ? trim((string)$options['slug']) : make_slug($title);
    $slug = cli_validate_unique_slug($slug);
    $thumbnail = cli_option_string($options, 'thumbnail', false, '');
    $duration = cli_int($options, 'duration', 0, 0, null);
    $view_count = cli_int($options, 'view_count', 0, 0, null);
    $external_view_count = cli_int($options, 'external_view_count', 0, 0, null);
    $sort_order = cli_int($options, 'sort_order', 0, PHP_INT_MIN, PHP_INT_MAX);
    $published = cli_published_from_options($options, 1);
    $created_at = cli_option_string($options, 'created_at', false, '');
    if ($created_at === '') $created_at = date('Y-m-d H:i:s');

    $db = cli_db();
    $db->beginTransaction();
    try {
        $db->prepare("
            INSERT INTO videos
                (title, description, slug, thumbnail, duration, view_count, external_view_count, sort_order, published, created_at, updated_at)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
        ")->execute([
            $title,
            $description,
            $slug,
            $thumbnail,
            $duration,
            $view_count,
            $external_view_count,
            $sort_order,
            $published,
            $created_at,
        ]);

        $video_id = (int)$db->lastInsertId();
        if (isset($options['url']) || isset($options['file'])) {
            cli_insert_source_from_options($video_id, $options, 0);
        }
        $db->commit();
    } catch (Throwable $e) {
        $db->rollBack();
        throw $e;
    }

    if (isset($options['url'])) {
        media_apply_youtube_timestamp($video_id);
    }

    echo "Added video #{$video_id} ({$slug})." . PHP_EOL;
    return 0;
}

function cli_video_update(array $parsed): int {
    $options = $parsed['options'];
    $video = cli_require_video(cli_arg($parsed, 0, 'video:update requires an id or slug.'));
    $fields = [];
    $values = [];

    $string_fields = [
        'title' => 'title',
        'description' => 'description',
        'thumbnail' => 'thumbnail',
        'created_at' => 'created_at',
    ];
    foreach ($string_fields as $option => $column) {
        if (array_key_exists($option, $options)) {
            $fields[] = "{$column}=?";
            $values[] = (string)$options[$option];
        }
    }

    if (array_key_exists('slug', $options)) {
        $slug = cli_validate_unique_slug((string)$options['slug'], (int)$video['id']);
        $fields[] = 'slug=?';
        $values[] = $slug;
    }

    $int_fields = [
        'duration' => ['duration', 0, null],
        'view_count' => ['view_count', 0, null],
        'external_view_count' => ['external_view_count', 0, null],
        'sort_order' => ['sort_order', PHP_INT_MIN, PHP_INT_MAX],
    ];
    foreach ($int_fields as $option => [$column, $min, $max]) {
        if (array_key_exists($option, $options)) {
            $fields[] = "{$column}=?";
            $values[] = cli_validate_int_value((string)$options[$option], $option, $min, $max);
        }
    }

    if (cli_has_publish_option($options)) {
        $fields[] = 'published=?';
        $values[] = cli_published_from_options($options, (int)$video['published']);
    }

    if (!$fields) {
        throw new RuntimeException('No video fields were provided to update.');
    }

    $fields[] = "updated_at=datetime('now')";
    $values[] = (int)$video['id'];
    $sql = "UPDATE videos SET " . implode(', ', $fields) . " WHERE id=?";
    cli_db()->prepare($sql)->execute($values);

    echo "Updated video #{$video['id']}." . PHP_EOL;
    return 0;
}

function cli_video_set_published(array $parsed, int $published): int {
    $video = cli_require_video(cli_arg($parsed, 0, 'video publish command requires an id or slug.'));
    cli_db()->prepare("UPDATE videos SET published=?, updated_at=datetime('now') WHERE id=?")->execute([$published, (int)$video['id']]);
    echo "Video #{$video['id']} " . ($published ? 'published' : 'unpublished') . "." . PHP_EOL;
    return 0;
}

function cli_video_toggle_published(array $parsed): int {
    $video = cli_require_video(cli_arg($parsed, 0, 'video:toggle requires an id or slug.'));
    $published = (int)$video['published'] ? 0 : 1;
    cli_db()->prepare("UPDATE videos SET published=?, updated_at=datetime('now') WHERE id=?")->execute([$published, (int)$video['id']]);
    echo "Video #{$video['id']} " . ($published ? 'published' : 'unpublished') . "." . PHP_EOL;
    return 0;
}

function cli_video_delete(array $parsed): int {
    $options = $parsed['options'];
    $video = cli_require_video(cli_arg($parsed, 0, 'video:delete requires an id or slug.'));

    if (!cli_confirm($options, "Delete video #{$video['id']} ({$video['title']})?")) {
        throw new RuntimeException('Delete cancelled. Pass --yes to confirm non-interactively.');
    }

    $delete_files = !cli_bool($options, 'keep_files', false);
    if ($delete_files) {
        foreach ($video['sources'] as $source) {
            if (source_is_local($source) && trim((string)$source['file_path']) !== '') {
                cli_unlink_media_path((string)$source['file_path']);
            }
        }
        if (trim((string)$video['thumbnail']) !== '') {
            cli_unlink_media_path((string)$video['thumbnail']);
        }
    }

    cli_db()->prepare("DELETE FROM videos WHERE id=?")->execute([(int)$video['id']]);
    echo "Deleted video #{$video['id']}." . PHP_EOL;
    return 0;
}

function cli_source_list(array $parsed): int {
    $video = cli_require_video(cli_arg($parsed, 0, 'source:list requires a video id or slug.'));
    $rows = [];
    foreach ($video['sources'] as $source) {
        $rows[] = [
            (string)$source['id'],
            (string)$source['source_type'],
            cli_truncate((string)$source['label'], 24),
            (string)$source['quality'],
            (string)$source['mime_type'],
            (string)$source['sort_order'],
            cli_truncate(source_location($source), 58),
        ];
    }
    cli_table(['id', 'type', 'label', 'quality', 'mime', 'sort', 'location'], $rows);
    return 0;
}

function cli_source_add(array $parsed): int {
    $video = cli_require_video(cli_arg($parsed, 0, 'source:add requires a video id or slug.'));
    $sort = cli_next_source_sort((int)$video['id']);
    $source_id = cli_insert_source_from_options((int)$video['id'], $parsed['options'], $sort);

    if (isset($parsed['options']['url'])) {
        media_apply_youtube_timestamp((int)$video['id']);
    }

    echo "Added source #{$source_id} to video #{$video['id']}." . PHP_EOL;
    return 0;
}

function cli_source_update(array $parsed): int {
    $options = $parsed['options'];
    $source = cli_require_source(cli_arg($parsed, 0, 'source:update requires a source id.'));
    $fields = [];
    $values = [];

    if (array_key_exists('label', $options)) {
        $fields[] = 'label=?';
        $values[] = (string)$options['label'];
    }
    if (array_key_exists('quality', $options)) {
        $fields[] = 'quality=?';
        $values[] = (string)$options['quality'];
    }
    if (array_key_exists('sort_order', $options)) {
        $fields[] = 'sort_order=?';
        $values[] = cli_validate_int_value((string)$options['sort_order'], 'sort_order', PHP_INT_MIN, PHP_INT_MAX);
    }
    if (array_key_exists('mime', $options)) {
        $fields[] = 'mime_type=?';
        $values[] = (string)$options['mime'];
    }

    $has_url = array_key_exists('url', $options);
    $has_file = array_key_exists('file', $options);
    if ($has_url && $has_file) {
        throw new RuntimeException('Use either --url or --file, not both.');
    }

    if ($has_url) {
        $url = normalize_media_url((string)$options['url']);
        if ($url === '') throw new RuntimeException('External source URLs must start with http:// or https://.');
        $fields[] = 'source_type=?';
        $values[] = 'remote';
        $fields[] = 'file_path=?';
        $values[] = '';
        $fields[] = 'source_url=?';
        $values[] = $url;
        if (!array_key_exists('mime', $options)) {
            $fields[] = 'mime_type=?';
            $values[] = source_storage_mime_from_url($url);
        }
    }

    if ($has_file) {
        $file_path = cli_media_file_from_option((string)$options['file'], cli_bool($options, 'copy', false), 'source');
        $fields[] = 'source_type=?';
        $values[] = 'local';
        $fields[] = 'file_path=?';
        $values[] = $file_path;
        $fields[] = 'source_url=?';
        $values[] = '';
        if (!array_key_exists('mime', $options)) {
            $fields[] = 'mime_type=?';
            $values[] = mime_from_ext($file_path);
        }
    }

    if (!$fields) {
        throw new RuntimeException('No source fields were provided to update.');
    }

    $values[] = (int)$source['id'];
    cli_db()->prepare("UPDATE video_sources SET " . implode(', ', $fields) . " WHERE id=?")->execute($values);

    if ($has_url) {
        media_apply_youtube_timestamp((int)$source['video_id']);
    }

    echo "Updated source #{$source['id']}." . PHP_EOL;
    return 0;
}

function cli_source_delete(array $parsed): int {
    $options = $parsed['options'];
    $source = cli_require_source(cli_arg($parsed, 0, 'source:delete requires a source id.'));

    if (!cli_confirm($options, "Delete source #{$source['id']}?")) {
        throw new RuntimeException('Delete cancelled. Pass --yes to confirm non-interactively.');
    }

    if (!cli_bool($options, 'keep_file', false) && source_is_local($source) && trim((string)$source['file_path']) !== '') {
        cli_unlink_media_path((string)$source['file_path']);
    }

    cli_db()->prepare("DELETE FROM video_sources WHERE id=?")->execute([(int)$source['id']]);
    echo "Deleted source #{$source['id']}." . PHP_EOL;
    return 0;
}

function cli_insert_source_from_options(int $video_id, array $options, int $default_sort): int {
    $has_url = array_key_exists('url', $options);
    $has_file = array_key_exists('file', $options);
    if ($has_url && $has_file) throw new RuntimeException('Use either --url or --file, not both.');
    if (!$has_url && !$has_file) throw new RuntimeException('Add a source with --url or --file.');

    $label = array_key_exists('label', $options) ? trim((string)$options['label']) : '';
    $quality = array_key_exists('quality', $options) ? trim((string)$options['quality']) : '720p';
    $sort = array_key_exists('sort_order', $options)
        ? cli_validate_int_value((string)$options['sort_order'], 'sort_order', PHP_INT_MIN, PHP_INT_MAX)
        : $default_sort;

    if ($has_url) {
        $url = normalize_media_url((string)$options['url']);
        if ($url === '') throw new RuntimeException('External source URLs must start with http:// or https://.');
        $mime = array_key_exists('mime', $options) ? (string)$options['mime'] : source_storage_mime_from_url($url);
        cli_db()->prepare("
            INSERT INTO video_sources (video_id, label, source_type, file_path, source_url, mime_type, quality, sort_order)
            VALUES (?,?,?,?,?,?,?,?)
        ")->execute([$video_id, $label, 'remote', '', $url, $mime, $quality, $sort]);
        return (int)cli_db()->lastInsertId();
    }

    $file_path = cli_media_file_from_option((string)$options['file'], cli_bool($options, 'copy', false), 'source');
    $mime = array_key_exists('mime', $options) ? (string)$options['mime'] : mime_from_ext($file_path);
    cli_db()->prepare("
        INSERT INTO video_sources (video_id, label, source_type, file_path, source_url, mime_type, quality, sort_order)
        VALUES (?,?,?,?,?,?,?,?)
    ")->execute([$video_id, $label, 'local', $file_path, '', $mime, $quality, $sort]);
    return (int)cli_db()->lastInsertId();
}

function cli_media_file_from_option(string $input, bool $copy, string $context): string {
    $input = trim($input);
    if ($input === '') throw new RuntimeException("--file cannot be empty.");

    if ($copy) {
        if (!is_file($input) || !is_readable($input)) {
            throw new RuntimeException("File is not readable: {$input}");
        }
        $ext = strtolower(pathinfo($input, PATHINFO_EXTENSION));
        if ($ext === '') throw new RuntimeException('Copied media files must have an extension.');
        $directory = MEDIA_DIR . 'videos/';
        if (!is_dir($directory)) mkdir($directory, 0755, true);
        $base = preg_replace('/[^a-z0-9]+/i', '-', pathinfo($input, PATHINFO_FILENAME));
        $base = trim((string)$base, '-') ?: $context;
        $name = strtolower($base) . '_' . date('Ymd_His') . '_' . bin2hex(random_bytes(3)) . '.' . $ext;
        $target = $directory . $name;
        if (!copy($input, $target)) {
            throw new RuntimeException("Unable to copy file into media/videos.");
        }
        return 'videos/' . $name;
    }

    $media_root = realpath(MEDIA_DIR);
    $real_input = realpath($input);
    if ($media_root && $real_input && str_starts_with($real_input, $media_root . DIRECTORY_SEPARATOR)) {
        return str_replace('\\', '/', substr($real_input, strlen($media_root) + 1));
    }

    $relative = ltrim(str_replace('\\', '/', $input), '/');
    if (str_starts_with($relative, 'media/')) {
        $relative = substr($relative, 6);
    }

    return $relative;
}

function cli_maintenance_catalogue_order(): int {
    $result = media_correct_catalogue_sort_order();
    echo "Catalogue order checked {$result['checked']} video";
    echo ((int)$result['checked'] === 1 ? '' : 's') . "; {$result['updated']} reordered." . PHP_EOL;
    return 0;
}

function cli_maintenance_youtube_timestamps(array $parsed): int {
    $limit = cli_int($parsed['options'], 'limit', 25, 1, 100);
    $result = media_correct_youtube_timestamps($limit);
    cli_print_result('YouTube timestamps', $result);
    return 0;
}

function cli_maintenance_external_views(array $parsed): int {
    $limit = cli_int($parsed['options'], 'limit', 25, 1, 100);
    $result = media_correct_external_view_counts($limit);
    cli_print_result('External view counts', $result);
    return 0;
}

function cli_maintenance_all(array $parsed): int {
    $limit = cli_int($parsed['options'], 'limit', 25, 1, 100);
    cli_maintenance_catalogue_order();
    cli_print_result('YouTube timestamps', media_correct_youtube_timestamps($limit));
    cli_print_result('External view counts', media_correct_external_view_counts($limit));
    return 0;
}

function cli_print_result(string $label, array $result): void {
    echo $label . ':' . PHP_EOL;
    foreach ($result as $key => $value) {
        printf("  %-18s %s" . PHP_EOL, $key . ':', (string)$value);
    }
}

function cli_db_path(): int {
    echo DB_PATH . PHP_EOL;
    return 0;
}

function cli_db_doctor(): int {
    $rows = [
        ['Current user', cli_current_user_label()],
        ['Database path', DB_PATH],
        ['Data directory', dirname(DB_PATH)],
        ['Database exists', cli_yes_no(file_exists(DB_PATH))],
    ];

    foreach (cli_database_writable_checks() as $check) {
        $rows[] = [$check['label'], $check['ok'] ? 'ok' : $check['message']];
    }

    cli_table(['check', 'result'], $rows);
    return 0;
}

function cli_database_writable_checks(): array {
    $directory = dirname(DB_PATH);
    $checks = [];

    $checks[] = [
        'label' => 'data directory',
        'ok' => is_dir($directory) && is_writable($directory),
        'message' => is_dir($directory)
            ? "data directory is not writable: {$directory}"
            : "data directory does not exist: {$directory}",
    ];

    if (file_exists(DB_PATH)) {
        $checks[] = [
            'label' => 'media.db',
            'ok' => is_writable(DB_PATH),
            'message' => "database file is not writable: " . DB_PATH,
        ];
    }

    foreach ([DB_PATH . '-wal', DB_PATH . '-shm'] as $path) {
        if (!file_exists($path)) continue;
        $checks[] = [
            'label' => basename($path),
            'ok' => is_writable($path),
            'message' => "SQLite sidecar file is not writable: {$path}",
        ];
    }

    return $checks;
}

function cli_current_user_label(): string {
    if (function_exists('posix_geteuid') && function_exists('posix_getpwuid')) {
        $uid = posix_geteuid();
        $info = posix_getpwuid($uid);
        $name = is_array($info) && isset($info['name']) ? (string)$info['name'] : '';
        return $name !== '' ? "{$name} (uid {$uid})" : "uid {$uid}";
    }

    $user = get_current_user();
    return $user !== '' ? $user : 'unknown';
}

function cli_db_backup(array $parsed): int {
    cli_db()->exec('PRAGMA wal_checkpoint(FULL)');

    $output = isset($parsed['options']['output'])
        ? trim((string)$parsed['options']['output'])
        : __DIR__ . '/../data/backups/media-' . date('Ymd-His') . '.db';
    if ($output === '') throw new RuntimeException('--output cannot be empty.');

    $directory = dirname($output);
    if (!is_dir($directory) && !mkdir($directory, 0755, true)) {
        throw new RuntimeException("Unable to create backup directory: {$directory}");
    }
    if (!copy(DB_PATH, $output)) {
        throw new RuntimeException("Unable to write backup: {$output}");
    }

    echo "Backup written to {$output}." . PHP_EOL;
    return 0;
}

function cli_db_vacuum(): int {
    cli_db()->exec('VACUUM');
    echo "Database vacuum complete." . PHP_EOL;
    return 0;
}

function cli_arg(array $parsed, int $index, string $message): string {
    if (!array_key_exists($index, $parsed['args'])) {
        throw new RuntimeException($message);
    }
    return (string)$parsed['args'][$index];
}

function cli_option_string(array $options, string $key, bool $required, string $message_or_default): string {
    if (!array_key_exists($key, $options)) {
        if ($required) throw new RuntimeException($message_or_default);
        return $message_or_default;
    }
    if (is_bool($options[$key])) {
        throw new RuntimeException("--" . str_replace('_', '-', $key) . " requires a value.");
    }
    return trim((string)$options[$key]);
}

function cli_bool(array $options, string $key, bool $default): bool {
    if (!array_key_exists($key, $options)) return $default;
    $value = $options[$key];
    if (is_bool($value)) return $value;
    return cli_parse_bool_value((string)$value, $key);
}

function cli_parse_bool_value(string $value, string $label): bool {
    $value = strtolower(trim($value));
    if (in_array($value, ['1', 'true', 'yes', 'on'], true)) return true;
    if (in_array($value, ['0', 'false', 'no', 'off'], true)) return false;
    throw new RuntimeException("{$label} must be a boolean value.");
}

function cli_int(array $options, string $key, int $default, ?int $min, ?int $max): int {
    if (!array_key_exists($key, $options)) return $default;
    if (is_bool($options[$key])) {
        throw new RuntimeException("--" . str_replace('_', '-', $key) . " requires a number.");
    }
    return cli_validate_int_value((string)$options[$key], $key, $min, $max);
}

function cli_validate_int_value(string $value, string $label, ?int $min, ?int $max): int {
    $value = trim($value);
    if (!preg_match('/^-?\d+$/', $value)) {
        throw new RuntimeException("{$label} must be an integer.");
    }
    $number = (int)$value;
    if ($min !== null && $number < $min) {
        throw new RuntimeException("{$label} must be at least {$min}.");
    }
    if ($max !== null && $number > $max) {
        throw new RuntimeException("{$label} must be at most {$max}.");
    }
    return $number;
}

function cli_has_publish_option(array $options): bool {
    return array_key_exists('published', $options)
        || array_key_exists('publish', $options)
        || array_key_exists('draft', $options)
        || array_key_exists('unpublished', $options);
}

function cli_published_from_options(array $options, int $default): int {
    if (array_key_exists('draft', $options) && cli_bool($options, 'draft', false)) return 0;
    if (array_key_exists('unpublished', $options) && cli_bool($options, 'unpublished', false)) return 0;
    if (array_key_exists('publish', $options) && cli_bool($options, 'publish', false)) return 1;
    if (array_key_exists('published', $options)) return cli_bool($options, 'published', (bool)$default) ? 1 : 0;
    return $default ? 1 : 0;
}

function cli_find_video(string $ref): ?array {
    $ref = trim($ref);
    if ($ref === '') return null;
    if (ctype_digit($ref)) return get_video_by_id((int)$ref);

    $stmt = cli_db()->prepare("SELECT id FROM videos WHERE slug=?");
    $stmt->execute([$ref]);
    $id = $stmt->fetchColumn();
    return $id ? get_video_by_id((int)$id) : null;
}

function cli_require_video(string $ref): array {
    $video = cli_find_video($ref);
    if (!$video) throw new RuntimeException("Video not found: {$ref}");
    return $video;
}

function cli_require_source(string $ref): array {
    if (!ctype_digit(trim($ref))) throw new RuntimeException('Source id must be numeric.');
    $stmt = cli_db()->prepare("SELECT * FROM video_sources WHERE id=?");
    $stmt->execute([(int)$ref]);
    $source = $stmt->fetch();
    if (!$source) throw new RuntimeException("Source not found: {$ref}");
    return $source;
}

function cli_validate_unique_slug(string $slug, int $ignore_id = 0): string {
    $slug = strtolower(trim(preg_replace('/[^a-z0-9]+/i', '-', $slug), '-'));
    if ($slug === '') throw new RuntimeException('Slug cannot be empty.');

    if ($ignore_id > 0) {
        $stmt = cli_db()->prepare("SELECT id FROM videos WHERE slug=? AND id!=?");
        $stmt->execute([$slug, $ignore_id]);
    } else {
        $stmt = cli_db()->prepare("SELECT id FROM videos WHERE slug=?");
        $stmt->execute([$slug]);
    }

    if ($stmt->fetch()) throw new RuntimeException("Slug already exists: {$slug}");
    return $slug;
}

function cli_next_source_sort(int $video_id): int {
    $stmt = cli_db()->prepare("SELECT COALESCE(MAX(sort_order), -10) + 10 FROM video_sources WHERE video_id=?");
    $stmt->execute([$video_id]);
    return (int)$stmt->fetchColumn();
}

function cli_print_video(array $video): void {
    $fields = [
        'ID' => $video['id'],
        'Title' => $video['title'],
        'Slug' => $video['slug'],
        'Published' => cli_yes_no((bool)$video['published']),
        'Description' => $video['description'],
        'Thumbnail' => $video['thumbnail'],
        'Duration' => $video['duration'],
        'Local views' => $video['view_count'],
        'External views' => $video['external_view_count'],
        'Total views' => video_total_views($video),
        'Sort order' => $video['sort_order'],
        'Created' => $video['created_at'],
        'Updated' => $video['updated_at'],
    ];

    foreach ($fields as $label => $value) {
        printf("%-16s %s" . PHP_EOL, $label . ':', (string)$value);
    }

    if (!empty($video['sources'])) {
        echo PHP_EOL . 'Sources:' . PHP_EOL;
        $rows = [];
        foreach ($video['sources'] as $source) {
            $rows[] = [
                (string)$source['id'],
                (string)$source['source_type'],
                (string)$source['label'],
                (string)$source['quality'],
                (string)$source['mime_type'],
                (string)$source['sort_order'],
                source_location($source),
            ];
        }
        cli_table(['id', 'type', 'label', 'quality', 'mime', 'sort', 'location'], $rows);
    }
}

function cli_table(array $headers, array $rows): void {
    $widths = array_map('strlen', $headers);
    foreach ($rows as $row) {
        foreach ($row as $i => $value) {
            $widths[$i] = max($widths[$i] ?? 0, strlen((string)$value));
        }
    }

    $line = [];
    foreach ($headers as $i => $header) {
        $line[] = str_pad($header, $widths[$i]);
    }
    echo implode('  ', $line) . PHP_EOL;

    $separator = [];
    foreach ($headers as $i => $_) {
        $separator[] = str_repeat('-', $widths[$i]);
    }
    echo implode('  ', $separator) . PHP_EOL;

    foreach ($rows as $row) {
        $line = [];
        foreach ($headers as $i => $_) {
            $line[] = str_pad((string)($row[$i] ?? ''), $widths[$i]);
        }
        echo implode('  ', $line) . PHP_EOL;
    }
}

function cli_confirm(array $options, string $question): bool {
    if (cli_bool($options, 'yes', false)) return true;
    if (!cli_stdin_is_tty()) return false;
    fwrite(STDOUT, $question . ' [y/N] ');
    $answer = strtolower(trim((string)fgets(STDIN)));
    return in_array($answer, ['y', 'yes'], true);
}

function cli_prompt_secret(string $prompt): string {
    if (!cli_stdin_is_tty()) {
        throw new RuntimeException('No password supplied. Use --password or --stdin.');
    }

    fwrite(STDOUT, $prompt);
    $hidden = cli_hide_terminal_input(true);
    $value = rtrim((string)fgets(STDIN), "\r\n");
    if ($hidden) {
        cli_hide_terminal_input(false);
        fwrite(STDOUT, PHP_EOL);
    }
    return $value;
}

function cli_hide_terminal_input(bool $hide): bool {
    if (stripos(PHP_OS_FAMILY, 'Windows') !== false) return false;
    $command = $hide ? 'stty -echo' : 'stty echo';
    @exec($command, $output, $code);
    return $code === 0;
}

function cli_stdin_is_tty(): bool {
    if (function_exists('stream_isatty')) return stream_isatty(STDIN);
    if (function_exists('posix_isatty')) return posix_isatty(STDIN);
    return false;
}

function cli_unlink_media_path(string $path): void {
    $path = ltrim(str_replace('\\', '/', $path), '/');
    if ($path === '' || str_contains($path, '..')) return;
    $full = MEDIA_DIR . $path;
    if (is_file($full)) unlink($full);
}

function cli_directory_size(string $directory): int {
    if (!is_dir($directory)) return 0;
    $size = 0;
    $iterator = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($directory, FilesystemIterator::SKIP_DOTS));
    foreach ($iterator as $file) {
        if ($file->isFile()) $size += $file->getSize();
    }
    return $size;
}

function cli_bytes(int $bytes): string {
    $units = ['B', 'KB', 'MB', 'GB', 'TB'];
    $value = max(0, $bytes);
    $unit = 0;
    while ($value >= 1024 && $unit < count($units) - 1) {
        $value /= 1024;
        $unit++;
    }
    return ($unit === 0 ? (string)(int)$value : number_format($value, 1)) . ' ' . $units[$unit];
}

function cli_yes_no(bool $value): string {
    return $value ? 'yes' : 'no';
}

function cli_truncate(string $value, int $limit): string {
    if ($limit <= 3 || strlen($value) <= $limit) return $value;
    return substr($value, 0, $limit - 3) . '...';
}