mp_audio_manage/app/Console/Commands/FixNewVideoTasks720pCommand.php

<?php

namespace App\Console\Commands;

use GuzzleHttp\Client;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

/**
 * 处理 id >= 12835 的新数据视频任务（动漫 act/分镜链路）：
 * 1. 仅处理 video_resolution=480p 且 mp_anime_episodes 对应分集设置为 sr_720p 的任务
 *    （通过 alias_act_id/alias_segment_id 关联 mp_episode_segments 再取分集 video_resolution），
 *    分集未设置 sr_720p 的不做超分修复；
 * 2. task_id 为空的任务视为用户手动上传的视频，不做检测与超分处理；
 * 3. 检查上述任务的 result_url、compressed_url 是否真的是 720p：
 *    - result 为 480p 档 -> 本地超分到 720p，重新压缩，更新任务两列 URL；
 *    - result 已是 720p+ 但 compressed 为 480p 档 -> 基于 result 重新压缩；
 *    - 同步关联 mp_episode_segments 的 origin_video_url / video_url（仅 URL，其余字段不动）；
 *    - 同步关联 mp_anime_records 中 assistant 记录（video_url 指向该任务旧 result_url）为修复后的 result_url。
 *
 * 注意：本命令依赖 ffmpeg/ffprobe 且需在 APP_ENV != local 的环境执行
 * （upgrade480pTo720p / compressVideo 在 local 会短路）。
 */
class FixNewVideoTasks720pCommand extends Command
{
    /**
     * @var string
     */
    protected $signature = 'video:fix-new-tasks-720p
                            {--min-id=12835 : 只处理 id 大于等于该值的任务}
                            {--apply : 真正执行超分与更新；不带此参数时仅探测并输出报告}';

    /**
     * @var string
     */
    protected $description = '检查并修复 id>=12835 新数据任务的 720p 交付（超分 result/compressed 并同步分镜 URL）';

    /**
     * @return int
     */
    public function handle(): int
    {
        if (env('APP_ENV') === 'local') {
            $this->error('本命令需要在 APP_ENV != local 且安装 ffmpeg/ffprobe 的环境执行（local 下超分/压缩 helper 会短路）。');
            return 1;
        }

        $apply = (bool)$this->option('apply');
        $minId = (int)$this->option('min-id');

        $this->info($apply ? '开始执行修复（--apply）...' : '检查模式：仅探测与输出报告，加 --apply 才会超分并更新。');

        $tasks = DB::table('mp_generate_video_tasks')
            ->select([
                'id', 'video_resolution', 'status', 'api_type',
                'alias_segment_id', 'alias_act_id', 'task_id', 'result_url', 'compressed_url',
            ])
            ->where('id', '>=', $minId)
            // 仅处理 480p 任务；task_id 为空 = 用户手动上传的视频，不处理
            ->whereRaw('LOWER(TRIM(video_resolution)) = ?', ['480p'])
            ->whereNotNull('task_id')
            ->where('task_id', '<>', '')
            ->where('status', 'success')
            ->whereNotNull('result_url')
            ->where('result_url', '<>', '')
            ->orderBy('id')
            ->get();

        $this->line(sprintf('候选任务数：%d', $tasks->count()));

        $alreadyOk = 0;
        $needResultFix = 0;
        $needCompressedFix = 0;
        $probeFailed = 0;
        $updated = 0;
        $skippedManual = 0;
        $skippedEpisode = 0;

        foreach ($tasks as $task) {
            // 防御：task_id 为空视为人工上传视频（查询已排除，双保险）
            if (empty($task->task_id)) {
                $skippedManual++;
                $this->warn(sprintf('#%d task_id 为空（疑似用户手动上传），跳过检测与超分', $task->id));
                continue;
            }

            // 仅当对应分集设置的 video_resolution 为 sr_720p 时才需要修复
            $episodeResolution = $this->resolveEpisodeResolution($task);
            if ($episodeResolution !== 'sr_720p') {
                $skippedEpisode++;
                $this->line(sprintf(
                    '#%d 对应分集 video_resolution=%s（非 sr_720p），跳过超分修复',
                    $task->id,
                    $episodeResolution === '' ? '未知' : $episodeResolution
                ));
                continue;
            }

            $resultProbe = $this->probeUrl($task->result_url);
            if (!$resultProbe['ok']) {
                $probeFailed++;
                $this->warn(sprintf('#%d 探测 result_url 失败：%s', $task->id, $task->result_url));
                continue;
            }

            $resultShort = min($resultProbe['width'], $resultProbe['height']);
            $resultIs480 = $resultShort > 0 && abs($resultShort - 480) <= 60;

            $compressedShort = null;
            if (!empty($task->compressed_url)) {
                $compProbe = $this->probeUrl($task->compressed_url);
                if ($compProbe['ok']) {
                    $compressedShort = min($compProbe['width'], $compProbe['height']);
                }
            }
            $compressedIs480 = $compressedShort !== null && abs($compressedShort - 480) <= 60;

            $this->line(sprintf(
                '#%d [%s] result=%dx%d compressed=%s%s',
                $task->id,
                $task->video_resolution,
                $resultProbe['width'],
                $resultProbe['height'],
                $compressedShort === null ? '?' : $compressedShort,
                $compressedShort === null ? ' (探测失败)' : ''
            ));

            $needChange = false;
            $newResult = $task->result_url;
            $newCompressed = $task->compressed_url;

            if ($resultIs480) {
                $needResultFix++;
                $needChange = true;
                if ($apply) {
                    $enhanced = upgrade480pTo720p($task->result_url, 'video', false, '720p');
                    if ($enhanced && $enhanced !== $task->result_url) {
                        $newResult = $enhanced;
                        $compressed = compressVideo($enhanced);
                        $newCompressed = $compressed ?: $enhanced;
                        $this->line(sprintf(
                            '  -> 超分完成：%s%s',
                            $newResult,
                            $compressed ? '' : '（重新压缩失败，compressed_url 使用超分结果）'
                        ));
                    } else {
                        $this->warn(sprintf('#%d 超分失败，保留原 result_url', $task->id));
                        $needChange = false;
                    }
                }
            } elseif ($compressedIs480 && !empty($task->compressed_url)) {
                $needCompressedFix++;
                $needChange = true;
                if ($apply) {
                    $compressed = compressVideo($task->result_url);
                    if ($compressed) {
                        $newCompressed = $compressed;
                        $this->line('  -> 基于已合格的 result 重新压缩完成');
                    } else {
                        $this->warn(sprintf('#%d 重新压缩失败，compressed_url 保持不变', $task->id));
                        $needChange = false;
                    }
                }
            } else {
                $alreadyOk++;
            }

            if (!$needChange) {
                continue;
            }

            if ($apply) {
                DB::table('mp_generate_video_tasks')
                    ->where('id', $task->id)
                    ->update([
                        'result_url' => $newResult,
                        'compressed_url' => $newCompressed,
                    ]);

                $segmentSynced = $this->syncSegmentUrls($task, $newResult, $newCompressed);
                $recordSynced = $this->syncAnimeRecordUrls($task, $newResult);

                // 只更新不新增；完全没有 assistant 记录时需要即时通知人工确认
                if ($recordSynced === 0) {
                    $assistantTotal = DB::table('mp_anime_records')
                        ->where('video_task_id', $task->id)
                        ->where('role', 'assistant')
                        ->count();
                    if ($assistantTotal === 0) {
                        $notice = sprintf(
                            '任务#%d 在 mp_anime_records 中完全没有 assistant 记录（只更新不新增），请人工确认是否需要补录',
                            $task->id
                        );
                        $this->warn($notice);
                        dLog('command')->warning($notice, [
                            'task_id' => $task->id,
                            'old_result_url' => $task->result_url,
                            'new_result_url' => $newResult,
                        ]);
                        logDB('command', 'warning', $notice, [
                            'task_id' => $task->id,
                            'old_result_url' => $task->result_url,
                            'new_result_url' => $newResult,
                        ]);
                    } else {
                        $this->warn(sprintf(
                            '任务#%d 有 %d 条 assistant 记录但均未指向旧 result_url，跳过 anime_records 同步',
                            $task->id,
                            $assistantTotal
                        ));
                    }
                }

                $this->line(sprintf(
                    '#%d 已更新任务 URL（分镜同步：%s；anime_records 同步：%d 条）',
                    $task->id,
                    $segmentSynced ? '是' : '跳过/无关联',
                    $recordSynced
                ));
                $updated++;
            }
        }

        $this->info(sprintf(
            '汇总：待超分 result=%d，需重压 compressed=%d，已合格=%d，探测失败=%d，已更新任务=%d，跳过人工上传=%d，跳过非sr_720p分集=%d',
            $needResultFix,
            $needCompressedFix,
            $alreadyOk,
            $probeFailed,
            $updated,
            $skippedManual,
            $skippedEpisode
        ));

        if (!$apply) {
            $this->line('以上为检查结果；确认后在生产环境加 --apply 执行。');
        }

        return 0;
    }

    /**
     * 下载并探测视频分辨率
     *
     * @param string $url
     * @return array{ok:bool, width:int, height:int}
     */
    private function probeUrl(string $url): array
    {
        $tempDir = storage_path('app/temp/video_fix_check');
        if (!is_dir($tempDir)) {
            mkdir($tempDir, 0775, true);
        }

        $inputFile = '';
        try {
            $ext = getVideoExtFromUrl($url) ?: '.mp4';
            $inputFile = $tempDir . '/' . uniqid('vfix_') . bin2hex(random_bytes(3)) . $ext;

            $client = new Client(['timeout' => 300, 'verify' => false]);
            $client->get($url, ['sink' => $inputFile]);

            if (!file_exists($inputFile) || filesize($inputFile) <= 0) {
                return ['ok' => false, 'width' => 0, 'height' => 0];
            }

            $ffprobe = env('FFPROBE_PATH', 'ffprobe');
            $cmd = '"' . $ffprobe . '" -v quiet -print_format json -show_streams "' . $inputFile . '" 2>&1';
            $output = shell_exec($cmd);
            $info = json_decode((string)$output, true);

            $width = 0;
            $height = 0;
            if (isset($info['streams']) && is_array($info['streams'])) {
                foreach ($info['streams'] as $stream) {
                    if (isset($stream['codec_type']) && $stream['codec_type'] === 'video') {
                        $width = (int)($stream['width'] ?? 0);
                        $height = (int)($stream['height'] ?? 0);
                        break;
                    }
                }
            }

            return ['ok' => $width > 0 && $height > 0, 'width' => $width, 'height' => $height];
        } catch (\Throwable $e) {
            return ['ok' => false, 'width' => 0, 'height' => 0];
        } finally {
            if ($inputFile && file_exists($inputFile)) {
                @unlink($inputFile);
            }
        }
    }

    /**
     * 通过任务的 alias_act_id / alias_segment_id 关联分镜，再取对应分集的 video_resolution
     *
     * @param object $task
     * @return string 小写分集分辨率；无法关联时返回空字符串
     */
    private function resolveEpisodeResolution($task): string
    {
        $segment = null;
        if (!empty($task->alias_act_id)) {
            $segment = DB::table('mp_episode_segments')
                ->where('id', $task->alias_act_id)
                ->first(['episode_id']);
        } elseif (!empty($task->alias_segment_id)) {
            $segment = DB::table('mp_episode_segments')
                ->where('segment_id', $task->alias_segment_id)
                ->first(['episode_id']);
        }

        if (!$segment || empty($segment->episode_id)) {
            return '';
        }

        $episode = DB::table('mp_anime_episodes')
            ->where('id', $segment->episode_id)
            ->first(['video_resolution']);

        return strtolower(trim((string)($episode->video_resolution ?? '')));
    }

    /**
     * 同步 mp_anime_records 中 assistant 对话记录的 video_url
     * 仅更新 video_task_id 匹配、role=assistant 且 video_url 仍指向该任务旧 result_url 的行，
     * 避免覆盖同 act 其他任务写入的记录。
     *
     * @param object $task
     * @param string $newResultUrl
     * @return int 更新的记录数
     */
    private function syncAnimeRecordUrls($task, string $newResultUrl): int
    {
        return DB::table('mp_anime_records')
            ->where('video_task_id', $task->id)
            ->where('role', 'assistant')
            ->where('video_url', $task->result_url)
            ->update(['video_url' => $newResultUrl]);
    }

    /**
     * 同步关联 mp_episode_segments 的 URL（仅 origin_video_url / video_url）
     * 仅当分镜当前指向的就是该任务旧 URL 时才更新，避免覆盖同一分镜更新任务的产物。
     *
     * @param object $task
     * @param string $newResultUrl
     * @param string $newCompressedUrl
     * @return bool
     */
    private function syncSegmentUrls($task, string $newResultUrl, string $newCompressedUrl): bool
    {
        if (!empty($task->alias_segment_id)) {
            $segment = DB::table('mp_episode_segments')
                ->where('segment_id', $task->alias_segment_id)
                ->first(['id', 'origin_video_url', 'video_url']);
            if (!$segment) {
                return false;
            }
            $pointsToThisTask = $segment->origin_video_url === $task->result_url
                || $segment->video_url === $task->compressed_url;
            if (!$pointsToThisTask) {
                return false;
            }
            DB::table('mp_episode_segments')
                ->where('id', $segment->id)
                ->update([
                    'origin_video_url' => $newResultUrl,
                    'video_url' => $newCompressedUrl,
                ]);
            return true;
        }

        if (!empty($task->alias_act_id)) {
            $segment = DB::table('mp_episode_segments')
                ->where('id', $task->alias_act_id)
                ->first(['id', 'origin_video_url', 'video_url']);
            if (!$segment) {
                return false;
            }
            $pointsToThisTask = $segment->origin_video_url === $task->result_url
                || $segment->video_url === $task->compressed_url;
            if (!$pointsToThisTask) {
                return false;
            }
            DB::table('mp_episode_segments')
                ->where('id', $segment->id)
                ->update([
                    'origin_video_url' => $newResultUrl,
                    'video_url' => $newCompressedUrl,
                ]);
            return true;
        }

        return false;
    }
}