mp_audio_manage/app/Console/Commands/BackfillPointsScriptCommand.php

<?php

namespace App\Console\Commands;

use App\Services\PointsService;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class BackfillPointsScriptCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'points:backfill-script
                            {--type= : 只回填指定类型（video/image/chat），默认三种类型全部回填}
                            {--dry-run : 只统计将回填的记录数，不实际更新}
                            {--force : 重算已有锚点的记录（默认只处理缺失动漫/剧本的记录）}
                            {--skip-charge-info : 只回填明细列，不改写 charge_info}
                            {--charge-info-only : 只补齐 charge_info 缺失的 anime_id/script_id（依据明细列已回填的值）}
                            {--limit= : 最多处理记录数（默认全部）}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '回填积分明细的动漫ID/动漫名、剧本ID/剧本名，并同步补写 charge_info 的 anime_id/script_id';

    /**
     * 每批处理条数
     */
    const CHUNK_SIZE = 500;

    /**
     * Execute the console command.
     *
     * @param PointsService $pointsService
     * @return int
     */
    public function handle(PointsService $pointsService)
    {
        if (!Schema::hasColumn('mp_user_points_details', 'script_id')) {
            $this->error('mp_user_points_details 缺少 script_id/script_name 字段，请先执行 php artisan migrate');

            return 1;
        }

        $dryRun = (bool)$this->option('dry-run');
        $force = (bool)$this->option('force');
        $skipChargeInfo = (bool)$this->option('skip-charge-info');
        $chargeInfoOnly = (bool)$this->option('charge-info-only');
        $limit = (int)$this->option('limit');
        if ($limit < 0) {
            $limit = 0;
        }

        if ($chargeInfoOnly) {
            return $this->fillChargeInfoOnly($dryRun);
        }

        $type = trim((string)$this->option('type'));
        $types = in_array($type, ['video', 'image', 'chat'], true) ? [$type] : ['video', 'image', 'chat'];

        $query = DB::table('mp_user_points_details')
            ->select('id', 'type', 'task_id', 'charge_info')
            ->whereIn('type', $types)
            ->orderBy('id');

        if (!$force) {
            $query->where(function ($q) {
                $q->whereNull('anime_id')
                    ->orWhereNull('anime_name')
                    ->orWhereNull('script_id')
                    ->orWhere('script_id', 0);
            });
        }

        $total = (clone $query)->count();
        $this->info('待回填明细总数: ' . $total . ($dryRun ? '（dry-run 模式，不更新）' : ''));
        if ($total <= 0) {
            return 0;
        }
        if ($limit > 0) {
            $this->info('本次最多处理: ' . $limit . ' 条');
        }

        $scanned = 0;
        $filled = 0;
        $skipped = 0;
        $chargeInfoUpdated = 0;

        $query->chunkById(self::CHUNK_SIZE, function ($rows) use ($pointsService, $dryRun, $skipChargeInfo, $limit, &$scanned, &$filled, &$skipped, &$chargeInfoUpdated) {
            /** @var array 锚点 => 待更新明细ID */
            $pending = [];

            foreach ($rows as $row) {
                if ($limit > 0 && $scanned >= $limit) {
                    // 达到上限：先跳出明细循环，保证已解析的数据在下方统一落库
                    break;
                }
                $scanned++;

                $chargeInfo = $row->charge_info;
                if (is_string($chargeInfo)) {
                    $chargeInfo = json_decode($chargeInfo, true);
                }
                $chargeInfo = is_array($chargeInfo) ? $chargeInfo : [];

                $context = $pointsService->resolveScriptContext(
                    $chargeInfo,
                    $row->task_id === null ? null : (int)$row->task_id,
                    (string)$row->type
                );

                if (empty($context['anime_id']) && empty($context['script_id'])) {
                    $skipped++;
                    continue;
                }

                // charge_info 中缺失的锚点才补（已有值优先，不改写历史原始值）
                $needChargeInfoAnime = !$skipChargeInfo && !empty($context['anime_id']) && empty(getProp($chargeInfo, 'anime_id', ''));
                $needChargeInfoScript = !$skipChargeInfo && !empty($context['script_id']) && empty(getProp($chargeInfo, 'script_id', ''));
                $mask = ($needChargeInfoAnime ? 1 : 0) + ($needChargeInfoScript ? 2 : 0);

                $key = (int)$context['anime_id'] . '|' . (string)$context['anime_name'] . '|' . (int)$context['script_id'] . '|' . (string)$context['script_name'] . '|' . $mask;
                $pending[$key][] = (int)$row->id;
            }

            foreach ($pending as $key => $ids) {
                list($animeId, $animeName, $scriptId, $scriptName, $mask) = explode('|', $key, 5);
                $animeId = (int)$animeId;
                $scriptId = (int)$scriptId;
                $mask = (int)$mask;
                $filled += count($ids);

                if ($dryRun) {
                    continue;
                }

                DB::table('mp_user_points_details')->whereIn('id', $ids)->update([
                    'anime_id' => $animeId ?: null,
                    'anime_name' => $animeName !== '' ? $animeName : null,
                    'script_id' => $scriptId ?: null,
                    'script_name' => $scriptName !== '' ? $scriptName : null,
                ]);

                // charge_info 补写 anime_id / script_id（JSON 原子写入，不影响其他键）
                if ($mask > 0 && ($animeId || $scriptId)) {
                    $this->updateChargeInfoAnchors($ids, $animeId, $scriptId, $mask);
                    $chargeInfoUpdated += count($ids);
                }
            }

            // 达到处理上限时结束分片遍历
            return !($limit > 0 && $scanned >= $limit);
        });

        $this->info('扫描明细: ' . $scanned . ' 条，可回填: ' . $filled . ' 条，无动漫/剧本线索: ' . $skipped . ' 条');
        if (!$skipChargeInfo) {
            $this->info('charge_info 补写: ' . $chargeInfoUpdated . ' 条' . ($dryRun ? '（dry-run 未写入）' : ''));
        }
        $this->info($dryRun ? 'dry-run 结束，未更新任何数据' : '回填完成');

        return 0;
    }

    /**
     * 只补齐 charge_info 缺失的锚点（依据明细列已有值，不做锚点解析）
     *
     * 用于修正“列已回填但 charge_info 未补写”的行，速度快、可重复执行。
     *
     * @param bool $dryRun
     * @return int
     */
    private function fillChargeInfoOnly(bool $dryRun): int
    {
        $targets = [
            'anime_id' => "anime_id is not null and JSON_CONTAINS_PATH(charge_info, 'one', '$.anime_id') = 0",
            'script_id' => "script_id is not null and JSON_CONTAINS_PATH(charge_info, 'one', '$.script_id') = 0",
        ];

        foreach ($targets as $column => $condition) {
            $count = DB::table('mp_user_points_details')->whereRaw($condition)->count();
            if ($dryRun) {
                $this->info('charge_info 待补 ' . $column . ': ' . $count . ' 条（dry-run 未写入）');
                continue;
            }
            if ($count <= 0) {
                $this->info('charge_info 待补 ' . $column . ': 0 条');
                continue;
            }

            $affected = DB::update(
                'UPDATE mp_user_points_details SET charge_info = JSON_SET(charge_info, \'$.' . $column . '\', ' . $column . ')'
                . ' WHERE ' . $condition
            );
            $this->info('charge_info 补写 ' . $column . ': ' . $affected . ' 条');
        }

        return 0;
    }

    /**
     * 补写 charge_info 的 anime_id / script_id（只补缺失的键，已有值保留）
     *
     * @param array $ids      明细ID
     * @param int   $animeId
     * @param int   $scriptId
     * @param int   $mask     1=补 anime_id，2=补 script_id，3=两者都补
     * @return void
     */
    private function updateChargeInfoAnchors(array $ids, int $animeId, int $scriptId, int $mask): void
    {
        $paths = [];
        $bindings = [];

        if (($mask & 1) === 1 && $animeId > 0) {
            $paths[] = "'$.anime_id', ?";
            $bindings[] = $animeId;
        }
        if (($mask & 2) === 2 && $scriptId > 0) {
            $paths[] = "'$.script_id', ?";
            $bindings[] = $scriptId;
        }
        if (!$paths) {
            return;
        }

        // 用 JSON_SET 原子补写锚点：只影响指定键，charge_info 其他内容保持不变
        $sql = 'UPDATE mp_user_points_details'
            . ' SET charge_info = JSON_SET(COALESCE(charge_info, JSON_OBJECT()), ' . implode(', ', $paths) . ')'
            . ' WHERE id IN (' . implode(',', array_fill(0, count($ids), '?')) . ')';

        DB::update($sql, array_merge($bindings, array_map('intval', $ids)));
    }
}