kknongchang_2506/app/Module/UrsPromotion/Commands/FixReferralFarmUserIdCommand.php

<?php

namespace App\Module\UrsPromotion\Commands;

use App\Module\UrsPromotion\Models\UrsUserMapping;
use App\Module\UrsPromotion\Models\UrsUserReferral;
use App\Module\UrsPromotion\Models\UrsUserRelationCache;
use App\Module\UrsPromotion\Services\UrsReferralService;
use App\Module\UrsPromotion\Logics\UrsRelationCacheLogic;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * 修复推荐关系农场用户ID命令
 * 
 * 用于修复推荐关系表中农场用户ID字段为0的问题
 * 当推荐关系在用户进入农场之前就存在时，农场用户ID字段可能为0，需要更新
 * 
 * php artisan urs:fix-referral-farm-user-id --dry-run
 * php artisan urs:fix-referral-farm-user-id --batch-size=100
 */
class FixReferralFarmUserIdCommand extends Command
{
    /**
     * 命令签名
     */
    protected $signature = 'urs:fix-referral-farm-user-id 
                            {--batch-size=50 : 批处理大小}
                            {--dry-run : 仅模拟运行，不实际执行修复}
                            {--force : 强制修复所有记录，包括已有农场用户ID的}';

    /**
     * 命令描述
     */
    protected $description = '修复推荐关系表中农场用户ID字段为0的问题';

    /**
     * 执行命令
     */
    public function handle()
    {
        $batchSize = (int) $this->option('batch-size');
        $dryRun = $this->option('dry-run');
        $force = $this->option('force');

        $this->info('=== URS推荐关系农场用户ID修复命令 ===');
        
        if ($dryRun) {
            $this->warn('模拟运行模式 - 不会实际执行修复操作');
        }

        try {
            // 1. 分析问题数据
            $this->analyzeProblematicData($force);

            // 2. 执行修复
            if (!$dryRun) {
                $this->fixReferralFarmUserIds($batchSize, $force);
            } else {
                $this->info('模拟运行完成，实际修复请移除 --dry-run 参数');
            }

            return 0;

        } catch (\Exception $e) {
            $this->error('命令执行失败: ' . $e->getMessage());
            Log::error('URS推荐关系农场用户ID修复命令执行失败', [
                'batch_size' => $batchSize,
                'dry_run' => $dryRun,
                'force' => $force,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            return 1;
        }
    }

    /**
     * 分析问题数据
     *
     * @param bool $force 是否强制分析所有数据
     * @return void
     */
    private function analyzeProblematicData(bool $force): void
    {
        $this->info('正在分析问题数据...');

        // === 推荐关系表统计 ===
        // 统计被推荐人农场用户ID为0的记录
        $zeroUserIdCount = UrsUserReferral::where('status', UrsUserReferral::STATUS_VALID)
            ->where('user_id', 0)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('urs_promotion_user_mappings')
                    ->whereColumn('urs_promotion_user_mappings.urs_user_id', 'urs_promotion_user_referrals.urs_user_id')
                    ->where('urs_promotion_user_mappings.status', UrsUserMapping::STATUS_VALID);
            })
            ->count();

        // 统计推荐人农场用户ID为0的记录
        $zeroReferrerIdCount = UrsUserReferral::where('status', UrsUserReferral::STATUS_VALID)
            ->where('referrer_id', 0)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('urs_promotion_user_mappings')
                    ->whereColumn('urs_promotion_user_mappings.urs_user_id', 'urs_promotion_user_referrals.urs_referrer_id')
                    ->where('urs_promotion_user_mappings.status', UrsUserMapping::STATUS_VALID);
            })
            ->count();

        // 统计总的推荐关系记录数
        $totalReferralCount = UrsUserReferral::where('status', UrsUserReferral::STATUS_VALID)->count();

        // === 缓存表统计 ===
        // 统计缓存表中农场用户ID为0的记录
        $cacheZeroUserIdCount = UrsUserRelationCache::where('user_id', 0)->count();
        $cacheZeroRelatedUserIdCount = UrsUserRelationCache::where('related_user_id', 0)->count();
        $totalCacheCount = UrsUserRelationCache::count();

        // 统计已进入农场的用户数
        $mappedUserCount = UrsUserMapping::where('status', UrsUserMapping::STATUS_VALID)->count();

        $this->table([
            '统计项目', '数量'
        ], [
            ['总推荐关系记录数', number_format($totalReferralCount)],
            ['已进入农场用户数', number_format($mappedUserCount)],
            ['推荐关系表-被推荐人农场用户ID为0', number_format($zeroUserIdCount)],
            ['推荐关系表-推荐人农场用户ID为0', number_format($zeroReferrerIdCount)],
            ['缓存表总记录数', number_format($totalCacheCount)],
            ['缓存表-用户ID为0', number_format($cacheZeroUserIdCount)],
            ['缓存表-关联用户ID为0', number_format($cacheZeroRelatedUserIdCount)],
        ]);

        $totalProblems = $zeroUserIdCount + $zeroReferrerIdCount + $cacheZeroUserIdCount + $cacheZeroRelatedUserIdCount;

        if ($totalProblems > 0) {
            $this->warn("发现 {$totalProblems} 条记录需要修复");
            $this->info("  - 推荐关系表: {$zeroUserIdCount} + {$zeroReferrerIdCount} = " . ($zeroUserIdCount + $zeroReferrerIdCount) . " 条");
            $this->info("  - 缓存表: {$cacheZeroUserIdCount} + {$cacheZeroRelatedUserIdCount} = " . ($cacheZeroUserIdCount + $cacheZeroRelatedUserIdCount) . " 条");
        } else {
            $this->info('✅ 没有发现需要修复的数据');
        }
    }

    /**
     * 执行修复推荐关系农场用户ID
     *
     * @param int $batchSize 批处理大小
     * @param bool $force 是否强制修复所有记录
     * @return void
     */
    private function fixReferralFarmUserIds(int $batchSize, bool $force): void
    {
        $this->info('开始修复推荐关系和缓存表的农场用户ID...');

        // 1. 修复推荐关系表
        $this->info('1. 修复推荐关系表...');
        $result = UrsReferralService::batchUpdateFarmUserIds();

        if ($result['success']) {
            $this->info("✅ 推荐关系表修复完成，共更新 {$result['affected_rows']} 条记录");
        } else {
            $this->error("❌ 推荐关系表修复失败: {$result['message']}");
            return;
        }

        // 2. 修复缓存表
        $this->info('2. 修复缓存表...');
        $cacheResult = $this->batchUpdateCacheFarmUserIds();

        if ($cacheResult['success']) {
            $this->info("✅ 缓存表修复完成，共更新 {$cacheResult['affected_rows']} 条记录");
        } else {
            $this->error("❌ 缓存表修复失败: {$cacheResult['message']}");
        }

        Log::info('URS推荐关系和缓存表农场用户ID批量修复完成', [
            'referral_affected_rows' => $result['affected_rows'],
            'cache_affected_rows' => $cacheResult['affected_rows'] ?? 0,
            'batch_size' => $batchSize,
            'force' => $force
        ]);

        // 验证修复结果
        $this->verifyFixResults();
    }

    /**
     * 批量更新缓存表中的农场用户ID
     *
     * @return array 更新结果
     */
    private function batchUpdateCacheFarmUserIds(): array
    {
        try {
            Log::info('开始批量更新缓存表中的农场用户ID');

            // 使用SQL批量更新缓存表，提高性能
            // 分两步更新：先更新user_id，再更新related_user_id

            // 第一步：更新user_id字段（当前为0且用户已进入农场）
            $updateUserIdSql = "
                UPDATE `kku_urs_promotion_user_relation_cache` c
                INNER JOIN `kku_urs_promotion_user_mappings` um ON c.urs_user_id = um.urs_user_id AND um.status = 1
                SET c.user_id = um.user_id
                WHERE c.user_id = 0
            ";

            $userIdAffectedRows = DB::update($updateUserIdSql);

            // 第二步：更新related_user_id字段（当前为0且推荐人已进入农场）
            $updateRelatedUserIdSql = "
                UPDATE `kku_urs_promotion_user_relation_cache` c
                INNER JOIN `kku_urs_promotion_user_mappings` rm ON c.urs_related_user_id = rm.urs_user_id AND rm.status = 1
                SET c.related_user_id = rm.user_id
                WHERE c.related_user_id = 0
            ";

            $relatedUserIdAffectedRows = DB::update($updateRelatedUserIdSql);

            $affectedRows = $userIdAffectedRows + $relatedUserIdAffectedRows;

            Log::info('批量更新缓存表中的农场用户ID完成', [
                'user_id_affected_rows' => $userIdAffectedRows,
                'related_user_id_affected_rows' => $relatedUserIdAffectedRows,
                'total_affected_rows' => $affectedRows
            ]);

            return [
                'success' => true,
                'affected_rows' => $affectedRows,
                'message' => "成功更新 {$affectedRows} 条缓存记录"
            ];

        } catch (\Exception $e) {
            Log::error('批量更新缓存表中的农场用户ID失败', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);

            return [
                'success' => false,
                'affected_rows' => 0,
                'message' => '批量更新失败: ' . $e->getMessage()
            ];
        }
    }

    /**
     * 验证修复结果
     *
     * @return void
     */
    private function verifyFixResults(): void
    {
        $this->info('正在验证修复结果...');

        // === 验证推荐关系表 ===
        $remainingZeroUserIdCount = UrsUserReferral::where('status', UrsUserReferral::STATUS_VALID)
            ->where('user_id', 0)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('urs_promotion_user_mappings')
                    ->whereColumn('urs_promotion_user_mappings.urs_user_id', 'urs_promotion_user_referrals.urs_user_id')
                    ->where('urs_promotion_user_mappings.status', UrsUserMapping::STATUS_VALID);
            })
            ->count();

        $remainingZeroReferrerIdCount = UrsUserReferral::where('status', UrsUserReferral::STATUS_VALID)
            ->where('referrer_id', 0)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('urs_promotion_user_mappings')
                    ->whereColumn('urs_promotion_user_mappings.urs_user_id', 'urs_promotion_user_referrals.urs_referrer_id')
                    ->where('urs_promotion_user_mappings.status', UrsUserMapping::STATUS_VALID);
            })
            ->count();

        // === 验证缓存表 ===
        $remainingCacheZeroUserIdCount = UrsUserRelationCache::where('user_id', 0)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('urs_promotion_user_mappings')
                    ->whereColumn('urs_promotion_user_mappings.urs_user_id', 'urs_promotion_user_relation_cache.urs_user_id')
                    ->where('urs_promotion_user_mappings.status', UrsUserMapping::STATUS_VALID);
            })
            ->count();

        $remainingCacheZeroRelatedUserIdCount = UrsUserRelationCache::where('related_user_id', 0)
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('urs_promotion_user_mappings')
                    ->whereColumn('urs_promotion_user_mappings.urs_user_id', 'urs_promotion_user_relation_cache.urs_related_user_id')
                    ->where('urs_promotion_user_mappings.status', UrsUserMapping::STATUS_VALID);
            })
            ->count();

        $totalRemaining = $remainingZeroUserIdCount + $remainingZeroReferrerIdCount +
                         $remainingCacheZeroUserIdCount + $remainingCacheZeroRelatedUserIdCount;

        if ($totalRemaining == 0) {
            $this->info('✅ 验证通过：所有推荐关系和缓存的农场用户ID都已正确更新');
        } else {
            $this->warn("⚠️  仍有 {$totalRemaining} 条记录的农场用户ID未修复");
            $this->info("  - 推荐关系表: {$remainingZeroUserIdCount} + {$remainingZeroReferrerIdCount} = " . ($remainingZeroUserIdCount + $remainingZeroReferrerIdCount) . " 条");
            $this->info("  - 缓存表: {$remainingCacheZeroUserIdCount} + {$remainingCacheZeroRelatedUserIdCount} = " . ($remainingCacheZeroUserIdCount + $remainingCacheZeroRelatedUserIdCount) . " 条");
            $this->info('这可能是因为对应的用户尚未进入农场，属于正常情况');
        }

        // 随机抽样验证几条记录
        $this->sampleVerification();
    }

    /**
     * 随机抽样验证
     *
     * @return void
     */
    private function sampleVerification(): void
    {
        $this->info('正在进行随机抽样验证...');

        // 随机选择5条已修复的记录进行验证
        $sampleReferrals = UrsUserReferral::where('status', UrsUserReferral::STATUS_VALID)
            ->where('user_id', '>', 0)
            ->inRandomOrder()
            ->limit(5)
            ->get();

        $this->table([
            'URS用户ID', '农场用户ID', 'URS推荐人ID', '农场推荐人ID', '验证结果'
        ], $sampleReferrals->map(function ($referral) {
            // 验证农场用户ID是否正确
            $mapping = UrsUserMapping::where('urs_user_id', $referral->urs_user_id)
                ->where('status', UrsUserMapping::STATUS_VALID)
                ->first();
            
            $isCorrect = $mapping && $mapping->user_id == $referral->user_id;
            
            return [
                $referral->urs_user_id,
                $referral->user_id,
                $referral->urs_referrer_id,
                $referral->referrer_id,
                $isCorrect ? '✅ 正确' : '❌ 错误'
            ];
        })->toArray());
    }
}