bot-28/app/Services/MemberDailyReportService.php

<?php

namespace App\Services;

use App\Models\Agent\Agent;
use App\Models\MemberDailyReport;
use App\Services\Sharing\ShareDefaults;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * 会员每日盈亏报表（每个会员每天一行）。
 * - 资金：充值/提现/人工加扣款/优惠/返水，口径同代理报表（AgentReportService::aggregate）；
 * - 注单：share_bet_facts 中当天结算、未作废的注单，按结算时间归日，按游戏分类拆分；
 * - agent_share：当天合营代理从该会员获得的分成（正为代理收入）。
 * 输赢 win_loss 为会员视角（正为会员赢）。
 */
class MemberDailyReportService
{
    private const MONEY_FIELDS = ['deposit_amount', 'withdraw_amount', 'manual_credit', 'manual_debit', 'bonus_amount',
        'rebate_amount', 'bet_amount', 'valid_bet_amount', 'win_loss', 'agent_share'];

    public function refresh(string $date): int
    {
        $day = Carbon::parse($date)->toDateString();
        $start = $day . ' 00:00:00';
        $end = $day . ' 23:59:59';
        $byUser = [];
        $byMember = [];

        foreach (DB::table('share_bet_facts')->where('settled', 1)->where('voided', 0)->whereBetween('settled_at', [$start, $end])
            ->groupBy('user_id', 'category')
            ->selectRaw('user_id, category, COUNT(*) bet_count, COALESCE(SUM(bet_amount),0) bet_amount, COALESCE(SUM(effective_amount),0) valid_bet_amount, COALESCE(SUM(net_profit),0) win_loss')
            ->get() as $row) {
            $item = &$byUser[(int) $row->user_id];
            $item['bet_count'] = ($item['bet_count'] ?? 0) + (int) $row->bet_count;
            foreach (['bet_amount', 'valid_bet_amount', 'win_loss'] as $field) {
                $item[$field] = bcadd($item[$field] ?? '0', (string) $row->{$field}, 4);
            }
            $item['categories'][(string) $row->category] = [
                'name' => ShareDefaults::categories()[(string) $row->category] ?? (string) $row->category,
                'bet_count' => (int) $row->bet_count,
                'bet_amount' => bcadd((string) $row->bet_amount, '0', 4),
                'valid_bet_amount' => bcadd((string) $row->valid_bet_amount, '0', 4),
                'win_loss' => bcadd((string) $row->win_loss, '0', 4),
            ];
            unset($item);
        }
        if (Schema::hasTable('agent_profit_entries')) {
            foreach (DB::table('agent_profit_entries')->where('status', 'counted')->where('settle_date', $day)
                ->groupBy('user_id')->selectRaw('user_id, COALESCE(SUM(share_amount),0) total')->get() as $row) {
                $byUser[(int) $row->user_id]['agent_share'] = bcadd((string) $row->total, '0', 4);
            }
        }

        $this->addMoney($byMember, DB::table('recharges')->where('status', 1)->whereBetween('created_at', [$start, $end]), 'deposit_amount');
        $this->addMoney($byMember, DB::table('withdraws')->where('status', 1)->whereBetween('created_at', [$start, $end]), 'withdraw_amount');
        if (Schema::hasTable('payment_orders')) {
            $orders = fn () => DB::table('payment_orders')->where('status', PaymentOrderService::STATUS_SUCCESS)->whereBetween('created_at', [$start, $end]);
            $this->addMoney($byMember, $orders()->whereIn('type', [PaymentOrderService::TYPE_PAY, PaymentOrderService::TYPE_SELF_PAY]), 'deposit_amount');
            $this->addMoney($byMember, $orders()->whereIn('type', [PaymentOrderService::TYPE_PAYOUT, PaymentOrderService::TYPE_SELF_PAYOUT]), 'withdraw_amount');
        }
        foreach (DB::table('balance_logs')->whereBetween('created_at', [$start, $end])->groupBy('member_id')
            ->selectRaw(<<<'SQL'
                member_id,
                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)->get() as $row) {
            foreach (['manual_credit', 'manual_debit', 'bonus_amount', 'rebate_amount'] as $field) {
                if (bccomp((string) $row->{$field}, '0', 4) === 0) continue;
                $byMember[(string) $row->member_id][$field] = bcadd($byMember[(string) $row->member_id][$field] ?? '0', (string) $row->{$field}, 4);
            }
        }

        $users = collect();
        foreach (array_chunk(array_keys($byUser), 500) as $ids) {
            $users = $users->merge(DB::table('users')->whereIn('id', $ids)->get(['id', 'member_id', 'agent_id']));
        }
        foreach (array_chunk(array_map('strval', array_keys($byMember)), 500) as $memberIds) {
            $users = $users->merge(DB::table('users')->whereIn('member_id', $memberIds)->get(['id', 'member_id', 'agent_id']));
        }
        $rows = [];
        $now = now();
        foreach ($users->unique('id') as $user) {
            $values = array_merge($byUser[(int) $user->id] ?? [], $byMember[(string) $user->member_id] ?? []);
            $row = ['report_date' => $day, 'user_id' => (int) $user->id, 'member_id' => (string) $user->member_id,
                'agent_id' => $user->agent_id ? (int) $user->agent_id : null, 'bet_count' => (int) ($values['bet_count'] ?? 0),
                'categories' => json_encode($values['categories'] ?? new \stdClass(), JSON_UNESCAPED_UNICODE),
                'created_at' => $now, 'updated_at' => $now];
            foreach (self::MONEY_FIELDS as $field) $row[$field] = bcadd($values[$field] ?? '0', '0', 4);
            $rows[] = $row;
        }
        DB::transaction(function () use ($day, $rows) {
            DB::table('member_daily_reports')->where('report_date', $day)->delete();
            foreach (array_chunk($rows, 500) as $chunk) DB::table('member_daily_reports')->insert($chunk);
        });
        return count($rows);
    }

    /** 总后台查看全部会员；代理端传入 $agent 只看自己的会员。 */
    public function paginate(array $params, ?Agent $agent = null): array
    {
        $page = max(1, (int) ($params['page'] ?? 1));
        $limit = min(100, max(1, (int) ($params['limit'] ?? 20)));
        $startDate = (string) ($params['start_date'] ?? now()->subDays(6)->toDateString());
        $endDate = (string) ($params['end_date'] ?? now()->toDateString());
        if (Carbon::parse($startDate)->diffInDays(Carbon::parse($endDate)) > 92) {
            throw new \InvalidArgumentException('单次查询不能超过92天');
        }
        $query = MemberDailyReport::query()->from('member_daily_reports as r')
            ->leftJoin('users as u', 'u.id', '=', 'r.user_id')
            ->leftJoin('agents as a', 'a.id', '=', 'r.agent_id')
            ->whereBetween('r.report_date', [$startDate, $endDate]);
        if ($agent) {
            $query->where('r.agent_id', $agent->id);
        } else {
            if (!empty($params['agent_id'])) $query->where('r.agent_id', (int) $params['agent_id']);
            if (!empty($params['agent_type'])) $query->where('a.type', (int) $params['agent_type']);
        }
        if (!empty($params['member_id'])) $query->where('r.member_id', (string) $params['member_id']);
        if (!empty($params['username'])) $query->where('u.username', 'like', '%' . $params['username'] . '%');
        // 原生 SQL 中的别名也要带表前缀（r -> bot_r），交给 grammar 包裹。
        $grammar = DB::connection()->getQueryGrammar();
        $summary = (clone $query)->selectRaw('COUNT(*) row_count, COALESCE(SUM(' . $grammar->wrap('r.bet_count') . '),0) bet_count, '
            . implode(', ', array_map(fn ($field) => 'COALESCE(SUM(' . $grammar->wrap("r.{$field}") . "),0) {$field}", self::MONEY_FIELDS)))
            ->toBase()->first();
        $total = (int) ($summary->row_count ?? 0);
        $list = $query->select('r.*', 'u.username', 'u.first_name', 'a.username as agent_username', 'a.type as agent_type')
            ->orderByDesc('r.report_date')->orderByDesc('r.id')->forPage($page, $limit)->get();
        $totals = ['bet_count' => (int) ($summary->bet_count ?? 0)];
        foreach (self::MONEY_FIELDS as $field) $totals[$field] = bcadd((string) ($summary->{$field} ?? 0), '0', 4);
        return compact('total', 'page', 'limit', 'totals', 'list');
    }

    private function addMoney(array &$byMember, $query, string $field): void
    {
        foreach ($query->groupBy('member_id')->selectRaw('member_id, COALESCE(SUM(amount),0) total')->get() as $row) {
            $byMember[(string) $row->member_id][$field] = bcadd($byMember[(string) $row->member_id][$field] ?? '0', (string) $row->total, 4);
        }
    }
}