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\AgentRebateRecord;
use App\Models\User;
use App\Support\StatsScope;
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 $includeSelf ? [(int) $root->id] : [];
    }

    /**
     * 代理首页汇总（GET /agent/dashboard）。合营代理：自己名下的会员；入股代理（股东）：全平台会员
     * （不含试玩账号 users.from=2，口径同总后台首页），没有合营分成，commission_amount 为 0。
     * scope 告诉前端统计范围：own / platform。代理报表（reportRows）仍按各自名下会员统计。
     */
    public function dashboard(Agent $agent, string $startDate, string $endDate): array
    {
        if ($agent->isEquity()) {
            // 全平台：排除试玩账号和测试会员（测试代理名下）
            $members = StatsScope::realUsers(User::query(), 'users');
            return $this->aggregate($members, 0, [], $startDate, $endDate, platform: true) + ['scope' => 'platform'];
        }
        return $this->summaryForTree($agent, $startDate, $endDate) + ['scope' => 'own'];
    }

    public function dailyDeposits(Agent $agent, Carbon $start, Carbon $end): array
    {
        $members = $agent->isEquity()
            ? StatsScope::realUsers(User::query(), 'users')
            : User::query()->where('agent_id', $agent->id);
        $deposits = $this->depositSources($members->select('member_id'), $start, $end, Schema::hasTable('payment_orders'));
        $orders = array_shift($deposits)->select('member_id', 'created_at')
            ->selectRaw('amount * COALESCE(exchange_rate, 1) AS amount');
        foreach ($deposits as $source) {
            $orders->unionAll($source->select('member_id', 'created_at', 'amount'));
        }
        $date = 'DATE(' . DB::getQueryGrammar()->wrap('created_at') . ')';
        $daily = DB::query()->fromSub($orders, 'deposits')->groupByRaw($date)
            ->selectRaw("{$date} AS stat_date, COUNT(DISTINCT member_id) AS deposit_members, SUM(amount) AS deposit_amount")
            ->get()->keyBy('stat_date');

        $list = [];
        for ($day = $start->copy(); $day->lte($end); $day->addDay()) {
            $date = $day->toDateString();
            $row = $daily->get($date);
            $list[] = [
                'date' => $date,
                'deposit_members' => (int) ($row->deposit_members ?? 0),
                'deposit_amount' => $this->decimal($row->deposit_amount ?? 0),
            ];
        }
        return $list;
    }

    private function depositSources($memberIds, Carbon $start, Carbon $end, bool $hasPaymentOrders): array
    {
        $endExclusive = $end->copy()->startOfDay()->addDay();
        $inRange = fn (string $table) => DB::table($table)->whereIn('member_id', clone $memberIds)
            ->where('created_at', '>=', $start)->where('created_at', '<', $endExclusive);
        $sources = [$inRange('recharges')->where('status', 1)];
        if ($hasPaymentOrders) {
            $sources[] = $inRange('payment_orders')->where('status', PaymentOrderService::STATUS_SUCCESS)
                ->whereIn('type', [PaymentOrderService::TYPE_PAY, PaymentOrderService::TYPE_SELF_PAY]);
        }
        return $sources;
    }

    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, bool $platform = false): 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');
        $hasPaymentOrders = Schema::hasTable('payment_orders');
        $depositAmount = '0.0000';
        foreach ($this->depositSources($memberIds, $start, $end, $hasPaymentOrders) as $source) {
            $depositAmount = bcadd($depositAmount, (string) $source->sum('amount'), 4);
        }
        $withdrawAmount = DB::table('withdraws')->whereIn('member_id', clone $memberIds)
            ->where('status', 1)->whereBetween('created_at', [$start, $end])->sum('amount');
        if ($hasPaymentOrders) {
            $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])
            ->whereIn('change_type', [
                '人工充值', '订单补偿', '人工扣款', '注册赠送', '优惠活动', '即充即送',
                '充值返现', '老用户回归', '比比返', '笔笔返', '返水', '回水',
            ])
            ->selectRaw(<<<'SQL'
                COALESCE(SUM(CASE WHEN ((change_type = '人工充值' AND related_id IS NULL) OR change_type = '订单补偿') 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($members, $memberIds, $start, $end, $platform);
        $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()])
            ->where('profit_status', AgentRebateRecord::STATUS_CREDITED)
            ->sum('profit_commission');

        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();
        $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' => 0,
                        '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 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();
        if ($scope) {
            $ids = $this->visibleAgentIds($scope);
            $query->whereIn('id', $ids);
        } else {
            // 总后台代理报表：不列出测试代理
            \App\Support\StatsScope::excludeTestAgents($query, 'id');
        }
        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,
                'type' => (int) $agent->type,
                'type_text' => $agent->type_text,
                'balance' => (string) $agent->balance,
                'frozen_balance' => (string) $agent->frozen_balance,
            ], $row);
        })->all();
        return compact('total', 'page', 'limit', 'list');
    }

    private function betSummary($members, $memberIds, Carbon $start, Carbon $end, bool $platform): array
    {
        $total = ['bet_count' => 0, 'bet_amount' => '0.0000', 'valid_bet_amount' => '0.0000', 'win_loss' => '0.0000'];
        $sources = [];
        if (Schema::hasTable('bets')) {
            $orders = DB::table('bets')->where('status', 2)->whereBetween('created_at', [$start, $end]);
            $sources[] = $this->scopedBetTotals($orders, 'member_id', clone $memberIds,
                'COUNT(*) bet_count, SUM(amount) bet_amount, SUM(amount) valid_bet_amount, SUM(profit - amount) win_loss', $platform);
        }
        foreach (['sport_game_order', 'jisu_game_order'] as $table) {
            if (!Schema::hasTable($table)) {
                continue;
            }
            $orders = DB::table($table)->whereIn('status', [1, 2])->whereBetween('created_at', [$start, $end]);
            $sources[] = $this->scopedBetTotals($orders, 'member_id', clone $memberIds,
                'COUNT(*) bet_count, SUM(amount) bet_amount, SUM(amount) valid_bet_amount, SUM(CASE WHEN status = 1 THEN -amount ELSE profit_and_loss END) win_loss', $platform);
        }
        if (Schema::hasTable('lhc_order')) {
            $amountExpression = Schema::hasColumn('lhc_order', 'total_amount') ? 'COALESCE(total_amount, amount)' : 'amount';
            $winLossExpression = '(COALESCE(win_amount, 0) - ' . $amountExpression . ')';
            $orders = DB::table('lhc_order')->whereIn('lottery_status', [1, 2])->whereBetween('created_at', [$start->timestamp, $end->timestamp]);
            $sources[] = $this->scopedBetTotals($orders, 'member_id', clone $memberIds,
                "COUNT(*) bet_count, SUM({$amountExpression}) bet_amount, SUM({$amountExpression}) valid_bet_amount, SUM({$winLossExpression}) win_loss", $platform);
        }
        if (Schema::hasTable('third_game_orders')) {
            $orders = DB::table('third_game_orders')->where('status', 1)->whereBetween('last_update_time', [$start, $end]);
            $sources[] = $this->scopedBetTotals($orders, 'user_id', (clone $members)->select('id'),
                'COUNT(*) bet_count, SUM(bet_amount) bet_amount, SUM(valid_amount) valid_bet_amount, SUM(settled_amount) win_loss', $platform);
        }
        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 scopedBetTotals($orders, string $identity, $eligible, string $totals, bool $platform): object
    {
        if (!$platform) {
            return $orders->whereIn($identity, $eligible)->selectRaw($totals)->first();
        }

        // 全平台先汇总同一会员的注单，避免每笔注单都查询会员表。
        $byMember = $orders->whereNotNull($identity)->select($identity)->selectRaw($totals)->groupBy($identity);
        if ($identity === 'member_id' && $orders->getConnection()->getDriverName() === 'mysql') {
            // 旧注单表的字符排序规则可能不同，保留不同字节的会员号，仍按原规则匹配会员表。
            $byMember->groupByRaw('CAST(member_id AS BINARY)');
        }
        return DB::query()->fromSub($byMember, 'game_member_totals')->whereIn($identity, $eligible)
            ->selectRaw('SUM(bet_count) bet_count, SUM(bet_amount) bet_amount, SUM(valid_bet_amount) valid_bet_amount, SUM(win_loss) win_loss')->first();
    }

    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);
    }
}