bot-28/app/Services/Agent/AgentReportService.php

<?php

namespace App\Services\Agent;

use App\Models\Agent\Agent;
use App\Models\Agent\AgentDailyStat;
use App\Models\Agent\AgentDailyGameStat;
use App\Models\Agent\AgentRebateRecord;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use App\Services\PaymentOrderService;

class AgentReportService
{
    public function visibleAgentIds(Agent $root, bool $includeSelf = true): array
    {
        return Agent::query()
            ->when($includeSelf, function ($query) use ($root) {
                $query->where(function ($query) use ($root) {
                    $query->whereKey($root->id)
                        ->orWhere('path', 'like', rtrim((string) $root->path, '/') . '/%');
                });
            }, fn($query) => $query
                ->where('id', '<>', $root->id)
                ->where('path', 'like', rtrim((string) $root->path, '/') . '/%'))
            ->pluck('id')->map(fn($id) => (int) $id)->all();
    }

    public function summaryForTree(Agent $root, string $startDate, string $endDate): array
    {
        $ids = $this->visibleAgentIds($root);
        $summary = $this->summaryForAgentIds($ids, $startDate, $endDate);
        $summary['agent_count'] = max(0, count($ids) - 1);
        return $summary;
    }

    public function summaryForAgentIds(array $agentIds, string $startDate, string $endDate): array
    {
        if ($agentIds === []) {
            return $this->emptySummary();
        }
        return $this->aggregate(
            User::query()->whereIn('agent_id', $agentIds),
            count($agentIds),
            $agentIds,
            $startDate,
            $endDate
        );
    }

    public function summaryForMemberIds(array $memberIds, string $startDate, string $endDate): array
    {
        if ($memberIds === []) {
            return $this->emptySummary();
        }
        return $this->aggregate(User::query()->whereIn('member_id', $memberIds), 0, [], $startDate, $endDate);
    }

    private function aggregate($members, int $agentCount, array $commissionAgentIds, string $startDate, string $endDate): array
    {
        $start = Carbon::parse($startDate)->startOfDay();
        $end = Carbon::parse($endDate)->endOfDay();
        if ($start->gt($end)) {
            throw new \InvalidArgumentException('开始日期不能大于结束日期');
        }
        if ($start->diffInDays($end) > 366) {
            throw new \InvalidArgumentException('单次报表查询不能超过366天');
        }
        $memberCount = (clone $members)->count();
        $memberIds = (clone $members)->select('member_id');
        $memberBalance = DB::table('wallets')->whereIn('member_id', clone $memberIds)->sum('available_balance');
        $depositAmount = DB::table('recharges')->whereIn('member_id', clone $memberIds)
            ->where('status', 1)->whereBetween('created_at', [$start, $end])->sum('amount');
        $withdrawAmount = DB::table('withdraws')->whereIn('member_id', clone $memberIds)
            ->where('status', 1)->whereBetween('created_at', [$start, $end])->sum('amount');
        if (Schema::hasTable('payment_orders')) {
            $depositAmount = bcadd((string) $depositAmount, (string) DB::table('payment_orders')
                ->whereIn('member_id', clone $memberIds)
                ->where('status', PaymentOrderService::STATUS_SUCCESS)
                ->where('type', PaymentOrderService::TYPE_PAY)
                ->whereBetween('created_at', [$start, $end])->sum('amount'), 4);
            $withdrawAmount = bcadd((string) $withdrawAmount, (string) DB::table('payment_orders')
                ->whereIn('member_id', clone $memberIds)
                ->where('status', PaymentOrderService::STATUS_SUCCESS)
                ->whereIn('type', [PaymentOrderService::TYPE_PAYOUT, PaymentOrderService::TYPE_SELF_PAYOUT])
                ->whereBetween('created_at', [$start, $end])->sum('amount'), 4);
        }

        $funds = DB::table('balance_logs')->whereIn('member_id', clone $memberIds)
            ->whereBetween('created_at', [$start, $end])
            ->selectRaw(<<<'SQL'
                COALESCE(SUM(CASE WHEN change_type = '人工充值' AND related_id IS NULL AND amount > 0 THEN amount ELSE 0 END), 0) AS manual_credit,
                COALESCE(SUM(CASE WHEN change_type = '人工扣款' AND amount < 0 THEN ABS(amount) ELSE 0 END), 0) AS manual_debit,
                COALESCE(SUM(CASE WHEN (change_type IN ('注册赠送','优惠活动','即充即送','充值返现','老用户回归') OR (change_type = '人工充值' AND related_id = 0)) AND amount > 0 THEN amount ELSE 0 END), 0) AS bonus_amount,
                COALESCE(SUM(CASE WHEN change_type IN ('比比返','笔笔返','返水','回水') AND amount > 0 THEN amount ELSE 0 END), 0) AS rebate_amount
            SQL)->first();

        $bet = $this->betSummary($memberIds, $start, $end);
        $companyProfit = $this->companyProfit(
            (string) $depositAmount,
            (string) $withdrawAmount,
            (string) ($funds->bonus_amount ?? 0),
            (string) ($funds->rebate_amount ?? 0)
        );
        $commissionAmount = $commissionAgentIds === [] ? 0 : AgentRebateRecord::query()
            ->whereIn('agent_id', $commissionAgentIds)
            ->whereBetween('settlement_date', [$start->toDateString(), $end->toDateString()])
            ->selectRaw("COALESCE(SUM(CASE WHEN flow_status='credited' THEN flow_commission ELSE 0 END + CASE WHEN profit_status='credited' THEN profit_commission ELSE 0 END),0) total")
            ->value('total');

        return [
            'agent_count' => $agentCount,
            'member_count' => $memberCount,
            'member_balance' => $this->decimal($memberBalance),
            'deposit_amount' => $this->decimal($depositAmount),
            'withdraw_amount' => $this->decimal($withdrawAmount),
            'manual_credit' => $this->decimal($funds->manual_credit ?? 0),
            'manual_debit' => $this->decimal($funds->manual_debit ?? 0),
            'bonus_amount' => $this->decimal($funds->bonus_amount ?? 0),
            'rebate_amount' => $this->decimal($funds->rebate_amount ?? 0),
            'bet_count' => $bet['bet_count'],
            'bet_amount' => $bet['bet_amount'],
            'valid_bet_amount' => $bet['valid_bet_amount'],
            'win_loss' => $bet['win_loss'],
            'commission_amount' => $this->decimal($commissionAmount),
            'company_profit' => $companyProfit,
        ];
    }

    public function refreshDaily(string $date): int
    {
        $day = Carbon::parse($date)->toDateString();
        $this->refreshDailyGameStats($day);
        $count = 0;
        Agent::query()->orderBy('id')->chunkById(100, function ($agents) use ($day, &$count) {
            foreach ($agents as $agent) {
                $summary = $this->summaryForAgentIds([(int) $agent->id], $day, $day);
                AgentDailyStat::query()->updateOrCreate(
                    ['stat_date' => $day, 'agent_id' => $agent->id],
                    [
                        'direct_agents' => Agent::query()->where('parent_id', $agent->id)->count(),
                        'direct_members' => $summary['member_count'],
                        'member_balance' => $summary['member_balance'],
                        'deposit_amount' => $summary['deposit_amount'],
                        'withdraw_amount' => $summary['withdraw_amount'],
                        'manual_credit' => $summary['manual_credit'],
                        'manual_debit' => $summary['manual_debit'],
                        'bonus_amount' => $summary['bonus_amount'],
                        'rebate_amount' => $summary['rebate_amount'],
                        'bet_amount' => $summary['bet_amount'],
                        'valid_bet_amount' => $summary['valid_bet_amount'],
                        'bet_count' => $summary['bet_count'],
                        'win_loss' => $summary['win_loss'],
                        'commission_amount' => $summary['commission_amount'],
                        'company_profit' => $summary['company_profit'],
                    ]
                );
                $count++;
            }
        });
        return $count;
    }

    public function refreshDailyGameStats(string $date): int
    {
        $day = Carbon::parse($date)->toDateString();
        $start = Carbon::parse($day)->startOfDay();
        $end = Carbon::parse($day)->endOfDay();
        $queries = [];
        if (Schema::hasTable('third_game_orders')) {
            $queries[] = DB::table('third_game_orders as o')->join('users as u', 'u.id', '=', 'o.user_id')
                ->whereNotNull('u.agent_id')->where('o.status', 1)->whereBetween('o.last_update_time', [$start, $end])
                ->selectRaw('u.agent_id, LOWER(o.platform) platform, o.game_type, COUNT(*) bet_count, COALESCE(SUM(o.bet_amount),0) bet_amount, COALESCE(SUM(o.valid_amount),0) valid_bet_amount, COALESCE(SUM(o.settled_amount),0) win_loss')
                ->groupBy('u.agent_id', 'o.platform', 'o.game_type');
        }
        if (Schema::hasTable('bets')) {
            $queries[] = DB::table('bets as o')->join('users as u', 'u.member_id', '=', 'o.member_id')
                ->whereNotNull('u.agent_id')->where('o.status', 2)->whereBetween('o.created_at', [$start, $end])
                ->selectRaw("u.agent_id, 'pc28' platform, 3 game_type, COUNT(*) bet_count, COALESCE(SUM(o.amount),0) bet_amount, COALESCE(SUM(o.amount),0) valid_bet_amount, COALESCE(SUM(o.profit-o.amount),0) win_loss")
                ->groupBy('u.agent_id');
        }
        foreach ([['sport_game_order', 'sport', 4], ['jisu_game_order', 'jisu', 3]] as [$table, $platform, $gameType]) {
            if (!Schema::hasTable($table)) continue;
            $queries[] = DB::table("{$table} as o")->join('users as u', 'u.member_id', '=', 'o.member_id')
                ->whereNotNull('u.agent_id')->whereIn('o.status', [1, 2])->whereBetween('o.created_at', [$start, $end])
                ->selectRaw("u.agent_id, '{$platform}' platform, {$gameType} game_type, COUNT(*) bet_count, COALESCE(SUM(o.amount),0) bet_amount, COALESCE(SUM(o.amount),0) valid_bet_amount, COALESCE(SUM(CASE WHEN o.status=1 THEN -o.amount ELSE o.profit_and_loss END),0) win_loss")
                ->groupBy('u.agent_id');
        }
        if (Schema::hasTable('lhc_order')) {
            $amount = Schema::hasColumn('lhc_order', 'total_amount') ? 'COALESCE(o.total_amount,o.amount)' : 'o.amount';
            $queries[] = DB::table('lhc_order as o')->join('users as u', 'u.member_id', '=', 'o.member_id')
                ->whereNotNull('u.agent_id')->whereIn('o.lottery_status', [1, 2])
                ->whereBetween('o.created_at', [$start->timestamp, $end->timestamp])
                ->selectRaw("u.agent_id, 'lhc' platform, 3 game_type, COUNT(*) bet_count, COALESCE(SUM({$amount}),0) bet_amount, COALESCE(SUM({$amount}),0) valid_bet_amount, COALESCE(SUM(COALESCE(o.win_amount,0)-{$amount}),0) win_loss")
                ->groupBy('u.agent_id');
        }
        $rows = collect();
        if ($queries !== []) {
            $union = array_shift($queries);
            foreach ($queries as $query) $union->unionAll($query);
            $rows = DB::query()->fromSub($union, 'game_stats')
                ->selectRaw('agent_id, platform, game_type, SUM(bet_count) bet_count, SUM(bet_amount) bet_amount, SUM(valid_bet_amount) valid_bet_amount, SUM(win_loss) win_loss')
                ->groupBy('agent_id', 'platform', 'game_type')->get();
        }
        DB::transaction(function () use ($day, $rows) {
            AgentDailyGameStat::query()->where('stat_date', $day)->delete();
            $now = now();
            foreach ($rows->chunk(500) as $chunk) {
                AgentDailyGameStat::query()->insert($chunk->map(fn($row) => [
                    'stat_date' => $day, 'agent_id' => (int) $row->agent_id,
                    'platform' => (string) $row->platform, 'game_type' => (int) $row->game_type,
                    'bet_count' => (int) $row->bet_count, 'bet_amount' => (string) $row->bet_amount,
                    'valid_bet_amount' => (string) $row->valid_bet_amount, 'win_loss' => (string) $row->win_loss,
                    'created_at' => $now, 'updated_at' => $now,
                ])->all());
            }
        });
        return $rows->count();
    }

    public function reportRows(?Agent $scope, array $params): array
    {
        $page = max(1, (int) ($params['page'] ?? 1));
        // 报表每行需要聚合整棵代理树；上线批量聚合前先限制单页，避免一次请求放大到上千条 SQL。
        $limit = min(20, max(1, (int) ($params['limit'] ?? 10)));
        $query = Agent::query()->with('parent:id,username');
        if ($scope) {
            $ids = $this->visibleAgentIds($scope);
            $query->whereIn('id', $ids);
        }
        if (!empty($params['username'])) {
            $query->where('username', 'like', '%' . $params['username'] . '%');
        }
        $total = (clone $query)->count();
        $startDate = (string) ($params['start_date'] ?? now()->toDateString());
        $endDate = (string) ($params['end_date'] ?? $startDate);
        $list = $query->orderBy('id')->forPage($page, $limit)->get()->map(function (Agent $agent) use ($startDate, $endDate) {
            $row = $this->summaryForTree($agent, $startDate, $endDate);
            return array_merge([
                'id' => (int) $agent->id,
                'username' => $agent->username,
                'real_name' => $agent->real_name,
                'parent_username' => $agent->parent?->username,
                'balance' => (string) $agent->balance,
                'frozen_balance' => (string) $agent->frozen_balance,
            ], $row);
        })->all();
        return compact('total', 'page', 'limit', 'list');
    }

    private function betSummary($memberIds, Carbon $start, Carbon $end): array
    {
        $total = ['bet_count' => 0, 'bet_amount' => '0.0000', 'valid_bet_amount' => '0.0000', 'win_loss' => '0.0000'];
        $sources = [];
        if (Schema::hasTable('bets')) {
            $sources[] = DB::table('bets')->whereIn('member_id', clone $memberIds)->where('status', 2)
                ->whereBetween('created_at', [$start, $end])
                ->selectRaw('COUNT(*) bet_count, COALESCE(SUM(amount),0) bet_amount, COALESCE(SUM(amount),0) valid_bet_amount, COALESCE(SUM(profit - amount),0) win_loss')->first();
        }
        foreach (['sport_game_order', 'jisu_game_order'] as $table) {
            if (!Schema::hasTable($table)) {
                continue;
            }
            $sources[] = DB::table($table)->whereIn('member_id', clone $memberIds)->whereIn('status', [1, 2])
                ->whereBetween('created_at', [$start, $end])
                ->selectRaw('COUNT(*) bet_count, COALESCE(SUM(amount),0) bet_amount, COALESCE(SUM(amount),0) valid_bet_amount, COALESCE(SUM(CASE WHEN status = 1 THEN -amount ELSE profit_and_loss END),0) win_loss')->first();
        }
        if (Schema::hasTable('lhc_order')) {
            $amountExpression = Schema::hasColumn('lhc_order', 'total_amount') ? 'COALESCE(total_amount, amount)' : 'amount';
            $winLossExpression = '(COALESCE(win_amount, 0) - ' . $amountExpression . ')';
            $sources[] = DB::table('lhc_order')->whereIn('member_id', clone $memberIds)->whereIn('lottery_status', [1, 2])
                ->whereBetween('created_at', [$start->timestamp, $end->timestamp])
                ->selectRaw("COUNT(*) bet_count, COALESCE(SUM({$amountExpression}),0) bet_amount, COALESCE(SUM({$amountExpression}),0) valid_bet_amount, COALESCE(SUM({$winLossExpression}),0) win_loss")->first();
        }
        if (Schema::hasTable('third_game_orders')) {
            $sources[] = DB::table('third_game_orders')->whereIn('member_id', clone $memberIds)->where('status', 1)
                ->whereBetween('last_update_time', [$start, $end])
                ->selectRaw('COUNT(*) bet_count, COALESCE(SUM(bet_amount),0) bet_amount, COALESCE(SUM(valid_amount),0) valid_bet_amount, COALESCE(SUM(settled_amount),0) win_loss')->first();
        }
        foreach ($sources as $row) {
            $total['bet_count'] += (int) ($row->bet_count ?? 0);
            foreach (['bet_amount', 'valid_bet_amount', 'win_loss'] as $field) {
                $total[$field] = bcadd($total[$field], (string) ($row->{$field} ?? 0), 4);
            }
        }
        return $total;
    }

    private function emptySummary(): array
    {
        return [
            'agent_count' => 0, 'member_count' => 0, 'member_balance' => '0.0000',
            'deposit_amount' => '0.0000', 'withdraw_amount' => '0.0000',
            'manual_credit' => '0.0000', 'manual_debit' => '0.0000',
            'bonus_amount' => '0.0000', 'rebate_amount' => '0.0000',
            'bet_count' => 0, 'bet_amount' => '0.0000', 'valid_bet_amount' => '0.0000',
            'win_loss' => '0.0000', 'commission_amount' => '0.0000', 'company_profit' => '0.0000',
        ];
    }

    private function decimal($value): string
    {
        return bcadd((string) ($value ?? 0), '0', 4);
    }

    public function companyProfit(string $deposit, string $withdraw, string $bonus, string $rebate): string
    {
        return bcsub(bcsub($deposit, $withdraw, 4), bcadd($bonus, $rebate, 4), 4);
    }
}