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

<?php

namespace App\Services\Agent;

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

/**
 * 代理周期报表：按日 / 周 / 月汇总，数据来自会员每日报表 member_daily_reports（每 10 分钟刷新当天，04:30 重算前 3 天）。
 * - 合营代理：自己名下会员（scope=own），带 agent_share 合营分成；
 * - 入股代理（股东）：全平台会员，不含试玩账号 users.from=2（scope=platform）。
 * 只返回汇总数，不返回会员明细。周为周一至周日；首尾周期按查询日期截断。
 */
class AgentPeriodReportService
{
    public const PERIODS = ['day', 'week', 'month'];
    private const MONEY = ['deposit_amount', 'withdraw_amount', 'manual_credit', 'manual_debit', 'bonus_amount',
        'rebate_amount', 'bet_amount', 'valid_bet_amount', 'win_loss', 'agent_share'];
    private const CATEGORY_FIELDS = ['bet_amount', 'valid_bet_amount', 'win_loss'];

    public function report(Agent $agent, array $params): array
    {
        $period = (string) ($params['period'] ?? 'day');
        if (!in_array($period, self::PERIODS, true)) throw new \InvalidArgumentException('统计周期错误');
        $today = now()->toDateString();
        $start = Carbon::parse((string) ($params['start_date'] ?? now()->subDays(6)->toDateString()))->startOfDay();
        $end = Carbon::parse((string) ($params['end_date'] ?? $today))->startOfDay();
        if ($start->gt($end)) throw new \InvalidArgumentException('开始日期不能大于结束日期');
        if ($end->toDateString() > $today) throw new \InvalidArgumentException('不能查询未来日期');
        if ($start->diffInDays($end) > 366) throw new \InvalidArgumentException('单次查询不能超过366天');
        $platform = $agent->isEquity();
        $from = $start->toDateString();
        $to = $end->toDateString();

        $buckets = $this->buckets($period, $start, $end);
        $bucketOf = fn (string $date) => $this->key($period, Carbon::parse($date));

        // 资金 / 注单：先按天汇总，再归到周期
        $g = DB::connection()->getQueryGrammar();
        $col = fn (string $c) => $g->wrap('member_daily_reports.' . $c);
        $daily = $this->reports($agent)->whereBetween('member_daily_reports.report_date', [$from, $to])
            ->groupBy('member_daily_reports.report_date')
            ->selectRaw($col('report_date') . ' AS d, COALESCE(SUM(' . $col('bet_count') . '),0) AS bet_count, '
                . 'COUNT(DISTINCT CASE WHEN ' . $col('bet_count') . ' > 0 THEN ' . $col('user_id') . ' END) AS active_members, '
                . implode(', ', array_map(fn ($f) => 'COALESCE(SUM(' . $col($f) . "),0) AS {$f}", self::MONEY)))
            ->get();
        foreach ($daily as $row) {
            $key = $bucketOf(substr((string) $row->d, 0, 10));
            if (!isset($buckets[$key])) continue;
            $buckets[$key]['bet_count'] += (int) $row->bet_count;
            if ($period === 'day') $buckets[$key]['active_members'] = (int) $row->active_members;
            foreach (self::MONEY as $f) $buckets[$key][$f] = bcadd($buckets[$key][$f], (string) $row->{$f}, 4);
        }
        // 周 / 月的投注人数要跨天去重，逐个周期查询（最多 53 周 / 13 个月）
        if ($period !== 'day') {
            foreach ($buckets as $key => $bucket) {
                $buckets[$key]['active_members'] = $this->activeMembers($agent, $bucket['start_date'], $bucket['end_date']);
            }
        }

        // 新增会员（按注册时间）
        foreach ($this->members($agent)->whereBetween('created_at', [$from . ' 00:00:00', $to . ' 23:59:59'])
            ->groupByRaw('DATE(' . $g->wrap('created_at') . ')')
            ->selectRaw('DATE(' . $g->wrap('created_at') . ') AS d, COUNT(*) AS c')->get() as $row) {
            $key = $bucketOf(substr((string) $row->d, 0, 10));
            if (isset($buckets[$key])) $buckets[$key]['new_members'] += (int) $row->c;
        }

        // 游戏分类（来源同会员日报：已结算、未作废的注单，按结算时间）
        $categoryNames = ShareDefaults::categories();
        $totalCategories = [];
        $settled = $g->wrap('settled_at');
        foreach (DB::table('share_bet_facts')->where('settled', 1)->where('voided', 0)
            ->whereBetween('settled_at', [$from . ' 00:00:00', $to . ' 23:59:59'])
            ->whereIn('user_id', $this->members($agent)->select('id'))
            ->groupByRaw('DATE(' . $settled . '), ' . $g->wrap('category'))
            ->selectRaw('DATE(' . $settled . ') AS d, ' . $g->wrap('category') . ' AS category, COUNT(*) AS bet_count, '
                . 'COALESCE(SUM(' . $g->wrap('bet_amount') . '),0) AS bet_amount, COALESCE(SUM(' . $g->wrap('effective_amount') . '),0) AS valid_bet_amount, '
                . 'COALESCE(SUM(' . $g->wrap('net_profit') . '),0) AS win_loss')->get() as $row) {
            $key = $bucketOf(substr((string) $row->d, 0, 10));
            if (!isset($buckets[$key])) continue;
            $category = (string) $row->category;
            foreach ([&$buckets[$key]['categories'], &$totalCategories] as &$target) {
                $target[$category] ??= ['category' => $category, 'name' => $categoryNames[$category] ?? $category,
                    'bet_count' => 0, 'bet_amount' => '0.0000', 'valid_bet_amount' => '0.0000', 'win_loss' => '0.0000'];
                $target[$category]['bet_count'] += (int) $row->bet_count;
                foreach (self::CATEGORY_FIELDS as $f) $target[$category][$f] = bcadd($target[$category][$f], (string) $row->{$f}, 4);
            }
            unset($target);
        }

        $totals = $this->emptyRow('total', '合计', $from, $to);
        foreach ($buckets as $bucket) {
            $totals['bet_count'] += $bucket['bet_count'];
            $totals['new_members'] += $bucket['new_members'];
            foreach (self::MONEY as $f) $totals[$f] = bcadd($totals[$f], $bucket[$f], 4);
        }
        $totals['active_members'] = $this->activeMembers($agent, $from, $to);
        $totals['categories'] = $totalCategories;

        $list = array_map(fn ($row) => $this->finish($row, $platform), array_reverse(array_values($buckets)));
        $refreshed = $this->reports($agent)->max('member_daily_reports.updated_at');
        return [
            'scope' => $platform ? 'platform' : 'own',
            'period' => $period,
            'start_date' => $from,
            'end_date' => $to,
            'refreshed_at' => $refreshed ? Carbon::parse($refreshed)->format('Y-m-d H:i:s') : null,
            'totals' => $this->finish($totals, $platform),
            'list' => $list,
        ];
    }

    /** 统计范围内的会员（合营：名下；入股：全平台非试玩）。 */
    private function members(Agent $agent)
    {
        $query = DB::table('users');
        // 入股股东看全平台：排除试玩账号和测试会员；代理看自己名下的会员（测试代理也照常显示自己的）
        return $agent->isEquity()
            ? \App\Support\StatsScope::realUsers($query, 'users')
            : $query->where('agent_id', $agent->id);
    }

    private function reports(Agent $agent)
    {
        $query = DB::table('member_daily_reports');
        return $agent->isEquity()
            ? $query->whereIn('member_daily_reports.user_id', $this->members($agent)->select('id'))
            : $query->where('member_daily_reports.agent_id', $agent->id);
    }

    private function activeMembers(Agent $agent, string $from, string $to): int
    {
        return (int) $this->reports($agent)->whereBetween('member_daily_reports.report_date', [$from, $to])
            ->where('member_daily_reports.bet_count', '>', 0)->distinct()->count('member_daily_reports.user_id');
    }

    /** 查询范围内的全部周期（包括没有数据的周期），首尾按查询日期截断。 */
    private function buckets(string $period, Carbon $start, Carbon $end): array
    {
        $buckets = [];
        for ($cursor = $start->copy(); $cursor->lte($end); ) {
            $bucketEnd = match ($period) {
                'day' => $cursor->copy(),
                'week' => $cursor->copy()->endOfWeek(Carbon::SUNDAY)->startOfDay(),
                'month' => $cursor->copy()->endOfMonth()->startOfDay(),
            };
            if ($bucketEnd->gt($end)) $bucketEnd = $end->copy();
            $key = $this->key($period, $cursor);
            $label = match ($period) {
                'day' => $cursor->toDateString(),
                'week' => $cursor->format('m-d') . ' ~ ' . $bucketEnd->format('m-d'),
                'month' => $cursor->format('Y年m月'),
            };
            $buckets[$key] = $this->emptyRow($key, $label, $cursor->toDateString(), $bucketEnd->toDateString());
            $cursor = $bucketEnd->copy()->addDay();
        }
        return $buckets;
    }

    private function key(string $period, Carbon $date): string
    {
        return match ($period) {
            'day' => $date->toDateString(),
            'week' => $date->format('o-\WW'),
            'month' => $date->format('Y-m'),
        };
    }

    private function emptyRow(string $key, string $label, string $from, string $to): array
    {
        $row = ['period_key' => $key, 'label' => $label, 'start_date' => $from, 'end_date' => $to,
            'new_members' => 0, 'active_members' => 0, 'bet_count' => 0, 'categories' => []];
        foreach (self::MONEY as $f) $row[$f] = '0.0000';
        return $row;
    }

    /** 公司盈亏口径同首页：充值 − 提现 − 优惠 − 返水；入股代理不返回合营分成。 */
    private function finish(array $row, bool $platform): array
    {
        $row['company_profit'] = bcsub(bcsub($row['deposit_amount'], $row['withdraw_amount'], 4),
            bcadd($row['bonus_amount'], $row['rebate_amount'], 4), 4);
        if ($platform) unset($row['agent_share']);
        $row['categories'] = array_values($row['categories']);
        return $row;
    }
}