esign_serve/app/Console/Commands/ReplaceOssUrl.php

<?php

namespace App\Console\Commands;

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

class ReplaceOssUrl extends Command
{
    protected $signature = 'oss:replace-url
                            {--dry-run : 仅预览将要修改的数据，不实际执行更新}
                            {--model= : 仅处理指定模型，例如：ShopModels\\\\Shop}';

    protected $description = '将数据库中所有aliyuncs.com域名统一替换为https://cqzhw.oss-cn-chengdu.aliyuncs.com';

    private $targetDomain = 'https://cqzhw.oss-cn-chengdu.aliyuncs.com';

    private $totalUpdated = 0;
    private $totalRecords = 0;
    private $scannedTables = 0;
    private $scannedColumns = 0;
    private $hitTables = 0;

    public function handle()
    {
        $dryRun = $this->option('dry-run');
        $specificModel = $this->option('model');

        if ($dryRun) {
            $this->warn('=== 预览模式（--dry-run）：不会实际修改数据库 ===');
        }
        $this->line('');

        $this->info("目标域名: {$this->targetDomain}");
        $this->info("匹配规则: 字段值包含 aliyuncs.com 即替换其域名部分");
        $this->line('');

        // ============ Step 1: 发现模型 ============
        $this->info('━━━ Step 1: 发现模型 ━━━');

        if ($specificModel) {
            $models = $this->resolveModels([$specificModel]);
        } else {
            $models = $this->discoverAllModels();
        }

        if (empty($models)) {
            $this->error('未找到任何模型，请检查 app/Models 目录');
            return 1;
        }

        $this->info("发现 " . count($models) . " 个 Eloquent 模型");
        $this->line('');

        // ============ Step 2: 扫描每张表 ============
        $this->info('━━━ Step 2: 扫描数据表 ━━━');

        $bar = $this->output->createProgressBar(count($models));
        $bar->setFormat('%current%/%max% [%bar%] %percent:3s%% -- %message%');
        $bar->setMessage('开始扫描...');
        $bar->start();

        foreach ($models as $modelClass) {
            $shortName = (new \ReflectionClass($modelClass))->getShortName();
            $bar->setMessage($shortName);
            $this->processModel($modelClass, $dryRun);
            $bar->advance();
        }

        $bar->finish();

        // ============ Step 3: 汇总 ============
        $this->line('');
        $this->line('');
        $this->info('━━━ 扫描汇总 ━━━');
        $this->info("模型总数: " . count($models));
        $this->info("已扫表数: {$this->scannedTables}");
        $this->info("已扫字段: {$this->scannedColumns}");
        $this->info("命中表数: {$this->hitTables}");
        $this->info("命中记录: {$this->totalRecords}");

        if (!$dryRun && $this->totalUpdated > 0) {
            $this->info("已更新记录: {$this->totalUpdated}");
        }
    }

    private function processModel($modelClass, $dryRun)
    {
        try {
            if (!class_exists($modelClass)) {
                $this->line('');
                $this->warn("  [跳过] 类不存在: {$modelClass}");
                return;
            }

            /** @var \Illuminate\Database\Eloquent\Model $instance */
            $instance = new $modelClass;

            $table = $instance->getTable();
            $connection = $instance->getConnectionName() ?: config('database.default');

            // 跳过视图
            if ($table === 'member_perfor_view') {
                return;
            }

            // 获取该表所有文本类型字段
            $textColumns = $this->getTextColumns($table, $connection);

            if (empty($textColumns)) {
                $this->line('');
                $this->warn("  [{$connection}:{$table}] 无文本字段或查询失败");
                return;
            }

            $this->scannedTables++;
            $this->scannedColumns += count($textColumns);

            $tableHit = false;

            foreach ($textColumns as $column) {
                $count = DB::connection($connection)
                    ->table($table)
                    ->where($column, 'like', '%aliyuncs.com%')
                    ->count();

                if ($count === 0) {
                    continue;
                }

                if (!$tableHit) {
                    $tableHit = true;
                    $this->hitTables++;
                    $this->line('');
                }

                $this->totalRecords += $count;
                $this->info("  [{$connection}:{$table}.{$column}] 发现 {$count} 条");

                if ($dryRun) {
                    $samples = DB::connection($connection)
                        ->table($table)
                        ->select('id', $column)
                        ->where($column, 'like', '%aliyuncs.com%')
                        ->limit(3)
                        ->get();

                    foreach ($samples as $sample) {
                        $original = $sample->$column;
                        $replaced = $this->replaceOssDomain($original);
                        $this->line("    #{$sample->id}");
                        $this->line("      旧: " . mb_strcut($original, 0, 150));
                        $this->line("      新: " . mb_strcut($replaced, 0, 150));
                    }
                } else {
                    $updated = DB::connection($connection)
                        ->table($table)
                        ->where($column, 'like', '%aliyuncs.com%')
                        ->update([
                            $column => DB::raw("
                                CASE
                                    WHEN {$column} LIKE '%aliyuncs.com/%' THEN
                                        CONCAT('{$this->targetDomain}/', SUBSTRING_INDEX({$column}, 'aliyuncs.com/', -1))
                                    ELSE
                                        '{$this->targetDomain}'
                                END
                            ")
                        ]);
                    $this->totalUpdated += $updated;
                    $this->line("    → 已更新 {$updated} 条");
                }
            }
        } catch (\Exception $e) {
            $this->line('');
            $this->error("  [异常] {$modelClass}: " . $e->getMessage());
        }
    }

    /**
     * 获取指定表的所有文本类型字段
     *
     * 注意：information_schema 存的是物理表名（含前缀 x_），
     * 而 getTable() 返回的是不含前缀的逻辑表名，查询时必须补上。
     */
    private function getTextColumns($table, $connection)
    {
        try {
            $db = DB::connection($connection);
            $database = $db->getDatabaseName();
            $prefix = $db->getTablePrefix();
            $physicalTable = $prefix . $table;

            $rows = $db->select(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = ?
                 AND TABLE_NAME = ?
                 AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext', 'json')",
                [$database, $physicalTable]
            );

            return array_column($rows, 'COLUMN_NAME');
        } catch (\Exception $e) {
            $this->line('');
            $this->error("  [information_schema查询失败] connection={$connection} table={$table}: " . $e->getMessage());
            return [];
        }
    }

    /**
     * PHP层面的域名替换（预览用）
     */
    private function replaceOssDomain($value)
    {
        if (empty($value) || !is_string($value)) {
            return $value;
        }
        return preg_replace(
            '#https?://[^/]*aliyuncs\.com#i',
            $this->targetDomain,
            $value
        );
    }

    /**
     * 解析用户指定的模型类名
     */
    private function resolveModels($input)
    {
        $models = [];
        foreach ($input as $name) {
            // 完整类名
            if (strpos($name, 'App\\Models\\') === 0 && class_exists($name)) {
                $models[] = $name;
                continue;
            }
            // 相对写法 App\Models\ShopModels\Shop
            $fullName = 'App\\Models\\' . ltrim($name, '\\');
            if (class_exists($fullName)) {
                $models[] = $fullName;
                continue;
            }
            $this->error("模型类不存在: {$name}");
        }
        return $models;
    }

    /**
     * 自动发现 app/Models 下所有 Eloquent 模型
     */
    private function discoverAllModels()
    {
        $models = [];
        $basePath = app_path('Models');

        if (!is_dir($basePath)) {
            $this->error("目录不存在: {$basePath}");
            return $models;
        }

        $this->line("  扫描目录: {$basePath}");

        $iterator = new \RecursiveIteratorIterator(
            new \RecursiveDirectoryIterator($basePath, \RecursiveDirectoryIterator::SKIP_DOTS)
        );

        $fileCount = 0;
        foreach ($iterator as $file) {
            if ($file->getExtension() !== 'php') {
                continue;
            }

            $fileCount++;
            $pathname = $file->getPathname();
            // 统一处理 Windows / Linux 路径分隔符
            $relative = str_replace([$basePath . DIRECTORY_SEPARATOR, $basePath . '/', $basePath . '\\'], '', $pathname);
            $relative = str_replace(['/', '\\', '.php'], ['\\', '\\', ''], $relative);
            $class = 'App\\Models\\' . $relative;

            if (class_exists($class) && is_subclass_of($class, \Illuminate\Database\Eloquent\Model::class)) {
                $models[] = $class;
            }
        }

        $this->line("  扫描到 {$fileCount} 个 PHP 文件，其中 " . count($models) . " 个是 Eloquent 模型");

        // 打印前5个模型作为样例
        $samples = array_slice($models, 0, 5);
        foreach ($samples as $m) {
            $instance = new $m;
            $conn = $instance->getConnectionName() ?: config('database.default');
            $this->line("    - {$m}  [{$conn}:{$instance->getTable()}]");
        }
        if (count($models) > 5) {
            $this->line("    ... 还有 " . (count($models) - 5) . " 个");
        }

        return $models;
    }
}