bot-28/app/Services/Reporting/ReportReader.php

<?php

namespace App\Services\Reporting;

use App\Services\Sharing\EffectiveBet as Money;
use App\Services\Sharing\ShareMemberQualification;
use App\Services\ThirdGameCommissionService;
use App\Support\MaintenancePolicy;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use Illuminate\Validation\ValidationException;

class ReportReader
{
    private const AMOUNTS = ['deposit_amount', 'withdraw_amount', 'member_withdraw_amount', 'agent_withdraw_amount', 'deposit_fee', 'withdraw_fee',
        'customer_fee_income', 'configured_fee', 'bonus_amount', 'agent_commission', 'share_commission', 'agent_accrual', 'share_accrual', 'manual_credit', 'manual_debit',
        'bet_amount', 'settled_bet_amount', 'payout_amount', 'refund_amount', 'game_gross_profit', 'third_game_commission'];
    private const CASH = ['deposit_amount', 'withdraw_amount', 'member_withdraw_amount', 'agent_withdraw_amount', 'deposit_fee', 'withdraw_fee', 'customer_fee_income', 'configured_fee', 'bonus_amount', 'manual_credit', 'manual_debit'];

    public function params(array $input): array
    {
        $p = Validator::make($input, ['start_date' => ['nullable', 'date_format:Y-m-d'], 'end_date' => ['nullable', 'date_format:Y-m-d'],
            'report_type' => ['nullable', Rule::in(['business', 'promotion', 'sharing'])], 'platform' => ['nullable', Rule::in(['all', 'H5', 'PC', 'APP', 'unknown'])],
            'category' => ['nullable', Rule::in(array_merge(['all'], array_keys(MaintenancePolicy::CATEGORIES)))],
            'settlement_status' => ['nullable', Rule::in(['all', 'pending', 'settled'])], 'member_account' => ['nullable', 'string', 'max:100'],
            'agent_account' => ['nullable', 'string', 'max:100'], 'inviter_account' => ['nullable', 'string', 'max:100'],
            'group_id' => ['nullable', 'string', 'max:40'], 'page' => ['nullable', 'integer', 'min:1', 'max:1000000'], 'limit' => ['nullable', 'integer', 'min:1', 'max:100'],
            'metric' => ['nullable', Rule::in(['deposit_amount', 'bet_amount', 'payout_amount', 'withdraw_amount', 'activity_minutes'])],
            'kind' => ['nullable', Rule::in(['deposit', 'withdraw', 'agent_withdraw', 'bonus', 'agent_commission', 'share_commission', 'agent_accrual', 'share_accrual', 'manual_credit', 'manual_debit'])]])->validate();
        $p = array_filter($p, fn ($v) => $v !== null && $v !== '');
        $allTime = !isset($p['start_date']) && !isset($p['end_date']);
        $p += ['report_type' => 'business', 'platform' => 'all', 'category' => 'all', 'settlement_status' => 'all',
            'start_date' => now('Asia/Shanghai')->toDateString(), 'end_date' => now('Asia/Shanghai')->toDateString(), 'page' => 1, 'limit' => 20, 'metric' => 'deposit_amount'];
        $p['all_time'] = (int) $allTime;
        if ($allTime) $p['start_date'] = $this->firstReportDate($p['end_date']);
        $start = Carbon::parse($p['start_date'], 'Asia/Shanghai'); $end = Carbon::parse($p['end_date'], 'Asia/Shanghai')->addDay();
        if ($end->lte($start) || (!$allTime && $start->diffInDays($end) > 366) || $p['end_date'] > now('Asia/Shanghai')->toDateString()) throw ValidationException::withMessages(['end_date' => '日期范围必须有效、不超过366天且不能查询未来日期']);
        $p['start'] = $start->toDateTimeString(); $p['end'] = $end->toDateTimeString(); return $p;
    }

    private function firstReportDate(string $today): string
    {
        $dates = [$today]; $grammar = DB::connection()->getQueryGrammar();
        $effectiveAt = 'COALESCE(' . $grammar->wrap('r.occurred_at') . ', ' . $grammar->wrap('e.occurred_at') . ')';
        $earliest = [DB::table('reporting_orders')->min('bet_at'), DB::table('reporting_orders')->min('settled_at'),
            DB::table('reporting_events as e')->leftJoin('reporting_reconciliations as r', 'r.event_id', '=', 'e.id')
                ->min(DB::raw($effectiveAt)),
            DB::table('reporting_activity')->min('observed_at'),
            DB::table('users')->where(function ($q) { $q->whereNull('from')->orWhere('from', '<>', 2); })->min('created_at')];
        foreach ($earliest as $at) if ($at !== null) $dates[] = substr((string) $at, 0, 10);
        return min($dates);
    }

    public function summary(array $input): array
    {
        $p = $this->params($input); $result = $this->compute($p, 'summary');
        return $result['meta'] + ['summary' => $result['groups']['all'], 'categories' => array_values(array_filter($result['groups'], fn ($k) => $k !== 'all', ARRAY_FILTER_USE_KEY))];
    }

    public function details(array $input, bool $ranking = false): array
    {
        $p = $this->params($input); if ($ranking) $p['report_type'] = 'business';
        $result = $this->compute($p, 'details'); $rows = array_values($result['groups']);
        if ($ranking) {
            $metric = $p['metric']; if ($metric === 'activity_minutes') $rows = array_values(array_filter($rows, fn ($row) => $row[$metric] !== null));
            usort($rows, fn ($a, $b) => bccomp((string) ($b[$metric] ?? 0), (string) ($a[$metric] ?? 0), 10) ?: strcmp($a['group_id'], $b['group_id']));
            foreach ($rows as $i => &$row) $row['rank'] = $i + 1; unset($row);
        } else usort($rows, fn ($a, $b) => bccomp((string) $b['bet_amount'], (string) $a['bet_amount'], 10) ?: strcmp($a['group_id'], $b['group_id']));
        $total = count($rows); $rows = array_slice($rows, ((int) $p['page'] - 1) * (int) $p['limit'], (int) $p['limit']);
        return $result['meta'] + ['total' => $total, 'data' => $rows, 'unallocated' => $result['unallocated'] ?? null];
    }

    public function funds(array $input): array
    {
        $p = $this->params($input + ['report_type' => 'business']); $p['report_type'] = 'business'; $p['category'] = 'all'; $p['settlement_status'] = 'all';
        $result = $this->compute($p, 'daily'); $all = $result['groups']['all']; unset($result['groups']['all']);
        $days = array_values($result['groups']); usort($days, fn ($a, $b) => strcmp($b['group_id'], $a['group_id']));
        $keep = array_flip(array_merge(['group_id', 'label', 'net_cash_flow', 'company_profit', 'known_net_cash_flow', 'known_company_profit', 'known_third_game_commission', 'complete', 'quality'], self::AMOUNTS));
        $all = array_intersect_key($all, $keep);
        $days = array_map(fn ($row) => ['date' => $row['group_id']] + array_intersect_key($row, $keep), $days);
        return $result['meta'] + ['summary' => $all, 'total' => (int) Carbon::parse($p['start'])->diffInDays(Carbon::parse($p['end'])), 'data' => $days];
    }

    public function orders(array $input): array
    {
        $p = $this->params($input); [$users, $agents] = $this->people($p); $query = $this->orderQuery($p);
        $rows = []; $total = 0;
        foreach ($query->orderByDesc('bet_at')->orderByDesc('id')->cursor() as $record) {
            $row = (array) $record;
            if (!isset($users[$row['user_id']]) || !$this->scope($row, $p, $users, $agents)) continue;
            if ($p['category'] !== 'all' && $row['category'] !== $p['category']) continue;
            if (isset($p['group_id']) && $this->group($row, $p) !== $p['group_id']) continue;
            ++$total;
            if ($total > ((int) $p['page'] - 1) * (int) $p['limit'] && count($rows) < (int) $p['limit']) $rows[] = $row + ['member_account' => $this->account($users[$row['user_id']])];
        }
        return ['total' => $total, 'data' => $rows, 'timezone' => 'Asia/Shanghai', 'filters' => array_diff_key($p, ['start' => 1, 'end' => 1])];
    }

    public function events(array $input): array
    {
        $p = $this->params($input); [$users, $agents] = $this->people($p); $rows = []; $total = 0;
        $query = $this->eventQuery($p); if (isset($p['kind'])) $query->where('e.kind', $p['kind']);
        foreach ($query->orderByDesc('e.id')->cursor() as $record) {
            $row = (array) $record;
            if (!$this->scope($row, $p, $users, $agents)) continue;
            if (isset($p['group_id']) && $this->group($row, $p) !== $p['group_id']) continue;
            if ($p['category'] !== 'all' && $row['category'] !== $p['category']) continue;
            ++$total;
            if ($total > ((int) $p['page'] - 1) * (int) $p['limit'] && count($rows) < (int) $p['limit']) $rows[] = $row;
        }
        return ['total' => $total, 'data' => $rows, 'timezone' => 'Asia/Shanghai', 'filters' => array_diff_key($p, ['start' => 1, 'end' => 1])];
    }

    private function compute(array $p, string $mode): array
    {
        [$users, $agents] = $this->people($p); $settings = (new ReportAdmin())->settings();
        $qualification = (new ShareMemberQualification())->read($p['end_date'], $mode !== 'daily'); $policy = $qualification['policy_meta'];
        $groups = []; $unallocated = null; $now = now('Asia/Shanghai')->toDateTimeString(); $rates = null;
        if ($mode === 'summary') foreach (['all' => '所有订单'] + MaintenancePolicy::CATEGORIES as $key => $label) $groups[$key] = $this->empty($key, $label);
        if ($mode === 'daily') {
            $groups['all'] = $this->empty('all', '总计');
            $offset = ((int) $p['page'] - 1) * (int) $p['limit'];
            if ($offset < Carbon::parse($p['start'])->diffInDays(Carbon::parse($p['end']))) {
                $day = Carbon::parse($p['end'])->subDay()->subDays($offset);
                for ($i = 0; $i < (int) $p['limit'] && $day->toDateTimeString() >= $p['start']; ++$i, $day->subDay()) {
                    $groups[$day->toDateString()] = $this->empty($day->toDateString(), $day->toDateString());
                }
            }
        }
        $globalMissingTime = 0;
        foreach ($this->orderQuery($p)->orderBy('id')->cursor() as $record) {
            $row = (array) $record;
            if (!isset($users[$row['user_id']]) || !$this->scope($row, $p, $users, $agents)) continue;
            if ($p['category'] !== 'all' && $row['category'] !== $p['category']) continue;
            if (isset($p['group_id']) && $this->group($row, $p) !== $p['group_id']) continue;
            $keys = $mode === 'summary' ? ['all', $row['category']] : ($mode === 'details' ? [$this->group($row, $p)] : ['all']);
            if ($mode === 'daily') {
                if ($this->within($row['bet_at'], $p) && isset($groups[substr($row['bet_at'], 0, 10)])) $keys[] = substr($row['bet_at'], 0, 10);
                if ($this->within($row['settled_at'], $p) && isset($groups[substr($row['settled_at'], 0, 10)])) $keys[] = substr($row['settled_at'], 0, 10);
            }
            foreach (array_unique($keys) as $key) {
                if ($key === null) continue;
                if (!isset($groups[$key])) $groups[$key] = $this->empty($key, $this->label($key, $p, $users, $agents));
                $window = $mode === 'daily' && $key !== 'all' ? ['start' => $key . ' 00:00:00', 'end' => Carbon::parse($key)->addDay()->toDateTimeString()] + $p : $p;
                $this->addOrder($groups[$key], $row, $window, $rates);
                if ($p['report_type'] === 'promotion' && $row['attribution_quality'] === 'historical_current_agent') ++$groups[$key]['quality']['unverified_attribution'];
            }
        }
        $events = $this->eventQuery($p);
        foreach ($events->orderBy('e.id')->cursor() as $record) {
            $row = (array) $record;
            if (!$this->scope($row, $p, $users, $agents)) continue;
            if (isset($p['group_id']) && $this->group($row, $p) !== $p['group_id']) continue;
            if ($row['reconciliation_id'] !== null) { $row['amount'] = $row['verified_amount']; $row['occurred_at'] = $row['verified_at']; }
            $keys = $mode === 'summary' ? ['all'] : ($mode === 'details' ? [$this->group($row, $p)] : ['all']);
            if ($mode === 'summary' && $row['category'] !== null) $keys[] = $row['category'];
            if ($mode === 'daily' && $row['occurred_at'] !== null && isset($groups[substr($row['occurred_at'], 0, 10)])) $keys[] = substr($row['occurred_at'], 0, 10);
            if ($mode === 'daily' && $row['occurred_at'] === null) ++$globalMissingTime;
            foreach (array_unique($keys) as $key) {
                if ($key === null) $key = 'unallocated';
                if (!isset($groups[$key])) $groups[$key] = $this->empty($key, $this->label($key, $p, $users, $agents));
                $this->addEvent($groups[$key], $row);
                if ($p['report_type'] === 'promotion' && $row['user_id'] !== null && $row['attribution_quality'] === 'historical_current_agent') ++$groups[$key]['quality']['unverified_attribution'];
            }
        }
        $activity = $mode === 'daily' ? ['members' => [], 'groups' => []] : $this->activity($p, $users, $agents);
        foreach ($activity['groups'] as $key => $members) {
            if ($mode === 'details' && !isset($groups[$key])) $groups[$key] = $this->empty($key, $this->label($key, $p, $users, $agents));
        }
        $stateRows = DB::table('reporting_sync_state')->get(); $hasErrors = DB::table('reporting_errors')->where(function ($q) use ($p) { $q->whereNull('candidate_at')->orWhere('candidate_at', '<', $p['end']); })->count();
        $run = $stateRows->firstWhere('source', '__run__'); $ready = $run && (json_decode($run->state, true)['complete'] ?? false);
        $syncAt = $run->updated_at ?? null;
        $scopePopulation = []; $groupPopulation = [];
        if ($mode !== 'daily') foreach ($users as $uid => $u) {
            $person = ['user_id' => $u['id'], 'agent_id' => $u['agent_id'] ?? null, 'inviter_id' => $u['inviter_id'] ?? null, 'kind' => 'activity', 'client_platform' => $p['platform']];
            if (!$this->scope($person, $p, $users, $agents)) continue;
            if (isset($p['group_id']) && $this->group($person, $p) !== $p['group_id']) continue;
            $scopePopulation[$uid] = $u;
            $group = $this->group($person, $p); if ($group !== null) $groupPopulation[$group][$uid] = $u;
        }
        foreach ($groups as $key => &$value) {
            $population = $mode === 'details' ? ($groupPopulation[$key] ?? []) : $scopePopulation;
            if ($mode === 'details') {
                $person = $users[$key] ?? [];
                $value['member_account'] = $p['report_type'] === 'promotion' ? null : ($person ? $this->account($person) : null);
                $value['nickname'] = $p['report_type'] === 'promotion' ? null : ($person['first_name'] ?? '');
                $value['agent_account'] = $p['report_type'] === 'promotion' ? ($agents[$key]['username'] ?? null) : ($agents[$person['agent_id'] ?? 0]['username'] ?? null);
                $parent = $person['inviter_id'] ?? null; $value['inviter_account'] = isset($users[$parent]) ? $this->account($users[$parent]) : null;
            }
            $value['member_count'] = count($population); $value['valid_member_count'] = $policy['available'] ? 0 : null; $value['active_member_count'] = 0; $seconds = 0;
            if ($policy['available']) foreach ($value['_bettors'] as $uid => $_) if (isset($qualification['qualified'][$uid])) ++$value['valid_member_count'];
            $active = $mode === 'details' ? ($activity['groups'][$key] ?? []) : $activity['members'];
            $value['active_member_count'] = count($active);
            foreach ($active as $item) $seconds += $item['seconds'];
            // Current membership cannot prove a historical population after a reassignment.
            $value['activity_population_complete'] = !array_diff_key($active, $population);
            $covered = $p['start'] >= $settings['activity_started_at'];
            $value['known_activity_minutes'] = intdiv($seconds, 60); $value['activity_minutes'] = $covered ? $value['known_activity_minutes'] : null;
            $value['active_rate'] = $covered && $value['activity_population_complete'] ? ($value['member_count'] ? bcdiv((string) $value['active_member_count'], (string) $value['member_count'], 6) : '0.000000') : null;
            $value['order_count'] = count($value['_orders']); unset($value['_orders'], $value['_bettors']);
            $value['quality']['sync_errors'] = (int) $hasErrors; $value['quality']['not_synchronized'] = !$ready;
            if ($mode === 'daily') $value['quality']['missing_time'] += $key === 'all' ? 0 : $globalMissingTime;
            $complete = !array_sum(array_map('intval', $value['quality'])); $value['complete'] = $complete;
            $value['known_net_cash_flow'] = bcsub(bcsub($value['deposit_amount'], $value['withdraw_amount'], 10), bcadd($value['deposit_fee'], $value['withdraw_fee'], 10), 10);
            $value['known_third_game_commission'] = $value['third_game_commission'];
            $cost = '0'; foreach (['bonus_amount', 'agent_commission', 'share_commission', 'deposit_fee', 'withdraw_fee', 'third_game_commission'] as $f) $cost = bcadd($cost, $value[$f], 10);
            $value['known_company_profit'] = bcsub($value['game_gross_profit'], $cost, 10);
            if ($value['quality']['missing_platform_ratio']) $value['third_game_commission'] = null;
            $cashComplete = !array_sum(array_map('intval', array_diff_key($value['quality'], ['missing_platform_ratio' => true])));
            $value['net_cash_flow'] = $cashComplete ? $value['known_net_cash_flow'] : null;
            $value['company_profit'] = $complete && $p['settlement_status'] !== 'pending' ? $value['known_company_profit'] : null;
            if (($mode === 'summary' && $key !== 'all') || $p['category'] !== 'all') {
                foreach (self::CASH as $f) $value[$f] = null;
                $value['net_cash_flow'] = $value['company_profit'] = $value['known_net_cash_flow'] = $value['known_company_profit'] = null;
            }
            if ($mode === 'daily' && $key !== 'all') { $value['activity_minutes'] = $value['active_rate'] = null; }
        } unset($value);
        if (isset($groups['unallocated'])) { $unallocated = $groups['unallocated']; unset($groups['unallocated']); }
        return ['groups' => $groups, 'unallocated' => $unallocated, 'meta' => ['filters' => array_diff_key($p, ['start' => 1, 'end' => 1]),
            'timezone' => 'Asia/Shanghai', 'currency' => (new ReportSync())->currency(), 'data_updated_at' => $syncAt,
            'activity_started_at' => $settings['activity_started_at'], 'valid_deposit_min' => $policy['min_deposit'],
            'valid_effective_bet_min' => $policy['min_effective_bet'], 'valid_member_policy' => $policy,
            'commission_balances' => $this->commissionBalances($p, $users, $agents),
            'third_game_commission_meta' => ['basis' => ThirdGameCommissionService::BASIS, 'estimated' => true,
                'rate_date' => $rates['date'] ?? null, 'rates_fetched_at' => $rates['fetched_at'] ?? null], 'server_time' => $now]];
    }

    private function commissionBalances(array $p, array $users, array $agents): array
    {
        $result = ['date_basis' => 'current_account_balance', 'as_of' => now('Asia/Shanghai')->toDateTimeString(),
            'agent_unpaid' => null, 'share_unclaimed' => null, 'share_reserved' => null, 'share_debt' => null];
        if ($p['platform'] !== 'all' || $p['category'] !== 'all') return $result;
        if ($p['report_type'] !== 'sharing' && Schema::hasTable('agent_rebate_records') && empty($p['member_account']) && empty($p['inviter_account'])) {
            $result['agent_unpaid'] = Money::zero();
            foreach (DB::table('agent_rebate_records')->cursor() as $row) {
                if (!empty($p['agent_account']) && !str_contains($agents[$row->agent_id]['username'] ?? '', $p['agent_account'])) continue;
                foreach (['flow', 'profit'] as $part) if ($row->{$part . '_status'} === 'pending') $result['agent_unpaid'] = bcadd($result['agent_unpaid'], (string) $row->{$part . '_commission'}, 10);
            }
        }
        if (Schema::hasTable('share_accounts')) {
            foreach (['share_unclaimed', 'share_reserved', 'share_debt'] as $f) $result[$f] = Money::zero();
            foreach (DB::table('share_accounts')->cursor() as $row) {
                if (!isset($users[$row->user_id])) continue; $user = $users[$row->user_id];
                $scope = ['user_id' => $row->user_id, 'agent_id' => $user['agent_id'] ?? null, 'inviter_id' => $user['inviter_id'] ?? null,
                    'kind' => 'share_commission', 'client_platform' => 'unknown'];
                if (!$this->scope($scope, $p, $users, $agents)) continue;
                $balance = bcsub((string) $row->earned_amount, (string) $row->paid_amount, 10);
                if (bccomp($balance, '0', 10) >= 0) $result['share_unclaimed'] = bcadd($result['share_unclaimed'], $balance, 10);
                else $result['share_debt'] = bcsub($result['share_debt'], $balance, 10);
                $result['share_reserved'] = bcadd($result['share_reserved'], (string) $row->reserved_amount, 10);
            }
        }
        return $result;
    }

    private function orderQuery(array $p)
    {
        $q = DB::table('reporting_orders')->where(function ($q) use ($p) {
            $q->where(function ($q) use ($p) { $q->where('bet_at', '>=', $p['start'])->where('bet_at', '<', $p['end']); })
                ->orWhere(function ($q) use ($p) { $q->where('settled_at', '>=', $p['start'])->where('settled_at', '<', $p['end']); })
                ->orWhere(function ($q) use ($p) { $q->whereNull('settled_at')->where('bet_at', '<', $p['end'])->where(function ($q) { $q->where('settled', 1)->orWhere('voided', 1); }); });
        });
        if ($p['settlement_status'] === 'settled') $q->where('settled', 1)->where('voided', 0);
        if ($p['settlement_status'] === 'pending') $q->where('settled', 0)->where('voided', 0);
        $q->select('reporting_orders.*');
        if (Schema::hasTable('third_game_orders')) {
            $q->selectSub(DB::table('third_game_orders')->select('platform')
                ->whereColumn('third_game_orders.id', 'reporting_orders.parent_id')
                ->where('reporting_orders.parent_source', 'third_game_orders')->limit(1), 'provider_platform');
        }
        return $q;
    }

    private function eventQuery(array $p)
    {
        return DB::table('reporting_events as e')->leftJoin('reporting_reconciliations as r', 'r.event_id', '=', 'e.id')
            ->where(function ($q) use ($p) {
                $q->where(function ($q) use ($p) { $q->where('e.occurred_at', '>=', $p['start'])->where('e.occurred_at', '<', $p['end'])->whereNull('r.id'); })
                    ->orWhere(function ($q) use ($p) { $q->where('r.occurred_at', '>=', $p['start'])->where('r.occurred_at', '<', $p['end']); })
                    ->orWhere(function ($q) use ($p) { $q->whereNull('e.occurred_at')->whereNull('r.id')->where('e.candidate_at', '<', $p['end']); });
            })->select('e.*', 'r.id as reconciliation_id', 'r.actual_amount as verified_amount', 'r.external_fee', 'r.occurred_at as verified_at');
    }

    private function people(array $p): array
    {
        $agents = []; if (Schema::hasTable('agents')) foreach (DB::table('agents')->get(['id', 'username']) as $r) $agents[$r->id] = (array) $r;
        $invites = []; if (Schema::hasTable('share_members')) foreach (DB::table('share_members')->where('bound_at', '<', $p['end'])->get(['user_id', 'parent_user_id']) as $r) $invites[$r->user_id] = $r->parent_user_id;
        $users = []; foreach (DB::table('users')->where(function ($q) { $q->whereNull('from')->orWhere('from', '<>', 2); })->where('created_at', '<', $p['end'])->get() as $r) {
            $u = (array) $r; $u['inviter_id'] = $invites[$r->id] ?? null; $users[$r->id] = $u;
        }
        return [$users, $agents];
    }

    private function scope(array $row, array $p, array $users, array $agents): bool
    {
        $uid = $row['user_id'] ?? null; $agent = $row['agent_id'] ?? null; $inviter = $row['inviter_id'] ?? null;
        if ($uid !== null && !isset($users[$uid])) return false;
        if ($p['platform'] !== 'all' && ($row['client_platform'] ?? 'unknown') !== $p['platform']) return false;
        if ($p['report_type'] === 'promotion' && !$agent) return false;
        if ($p['report_type'] === 'sharing' && !$inviter && !in_array($row['kind'] ?? '', ['share_commission', 'share_accrual'], true)) return false;
        if (!empty($p['member_account']) && (!$uid || !str_contains($this->account($users[$uid]), $p['member_account']))) return false;
        if (!empty($p['agent_account']) && (!$agent || !str_contains($agents[$agent]['username'] ?? '', $p['agent_account']))) return false;
        if (!empty($p['inviter_account']) && (!$inviter || !str_contains(isset($users[$inviter]) ? $this->account($users[$inviter]) : '', $p['inviter_account']))) return false;
        return true;
    }

    private function empty(string $key, string $label): array
    {
        return ['group_id' => $key, 'label' => $label] + array_fill_keys(self::AMOUNTS, Money::zero()) + ['_orders' => [], '_bettors' => [],
            'quality' => ['missing_time' => 0, 'missing_amount' => 0, 'unverified_fees' => 0, 'foreign_currency' => 0, 'unverified_attribution' => 0, 'missing_platform_ratio' => 0]];
    }
    private function addOrder(array &$value, array $row, array $p, ?array &$rates): void
    {
        if ($row['currency'] !== (new ReportSync())->currency()) { ++$value['quality']['foreign_currency']; return; }
        if ($this->within($row['bet_at'], $p)) {
            $this->add($value, 'bet_amount', $row['bet_amount']); $value['_orders'][$row['parent_source'] . ':' . $row['parent_id']] = true;
            if (!$row['voided'] && bccomp($row['bet_amount'], '0', 10) > 0) $value['_bettors'][$row['user_id']] = true;
        }
        if (($row['settled'] || $row['voided']) && $row['settled_at'] === null) { ++$value['quality']['missing_time']; return; }
        if ($this->within($row['settled_at'], $p)) {
            if ($row['voided']) $this->add($value, 'refund_amount', $row['refund_amount']);
            elseif ($row['settled']) {
                $this->add($value, 'settled_bet_amount', bcsub($row['bet_amount'], $row['refund_amount'], 10)); $this->add($value, 'payout_amount', $row['payout_amount']);
                $this->add($value, 'refund_amount', $row['refund_amount']);
                $this->add($value, 'game_gross_profit', bcsub('0', $row['net_profit'], 10));
                if ($row['parent_source'] === 'third_game_orders' && bccomp($row['net_profit'], '0', 10) !== 0) {
                    $service = app(ThirdGameCommissionService::class); $rates ??= $service->rates();
                    $amount = $service->amount((string) ($row['provider_platform'] ?? ''), bcsub('0', $row['net_profit'], 10), $rates);
                    if ($amount === null) ++$value['quality']['missing_platform_ratio'];
                    else $this->add($value, 'third_game_commission', $amount);
                }
            }
        }
    }
    private function addEvent(array &$value, array $row): void
    {
        if ($row['currency'] !== (new ReportSync())->currency()) { ++$value['quality']['foreign_currency']; return; }
        if ($row['occurred_at'] === null) { ++$value['quality']['missing_time']; return; }
        if ($row['amount'] === null) { ++$value['quality']['missing_amount']; return; }
        $map = ['deposit' => 'deposit_amount', 'withdraw' => 'withdraw_amount', 'agent_withdraw' => 'withdraw_amount', 'bonus' => 'bonus_amount',
            'agent_commission' => 'agent_commission', 'share_commission' => 'share_commission', 'agent_accrual' => 'agent_accrual', 'share_accrual' => 'share_accrual', 'manual_credit' => 'manual_credit', 'manual_debit' => 'manual_debit'];
        $this->add($value, $map[$row['kind']], $row['amount']);
        if (in_array($row['kind'], ['deposit', 'withdraw', 'agent_withdraw'], true)) {
            $this->add($value, 'customer_fee_income', $row['customer_fee']);
            if ($row['kind'] !== 'deposit') $this->add($value, $row['kind'] === 'withdraw' ? 'member_withdraw_amount' : 'agent_withdraw_amount', $row['amount']);
            if ($row['configured_fee'] !== null) $this->add($value, 'configured_fee', $row['configured_fee']);
            if ($row['external_fee'] === null) ++$value['quality']['unverified_fees'];
            else $this->add($value, $row['kind'] === 'deposit' ? 'deposit_fee' : 'withdraw_fee', $row['external_fee']);
        }
    }
    private function activity(array $p, array $users, array $agents): array
    {
        $q = DB::table('reporting_activity')->where('observed_at', '>=', Carbon::parse($p['start'])->subMinutes(5)->toDateTimeString())->where('observed_at', '<', $p['end']);
        if ($p['platform'] !== 'all') $q->where('client_platform', $p['platform']);
        $result = ['members' => [], 'groups' => []]; $observations = []; $userId = null;
        foreach ($q->orderBy('user_id')->orderBy('observed_at')->orderBy('id')->cursor() as $row) {
            if (!isset($users[$row->user_id])) continue;
            if ($userId !== null && $userId !== $row->user_id) {
                $this->memberActivity($observations, $p, $users, $agents, $result); $observations = [];
            }
            $userId = $row->user_id; $observations[] = (array)$row;
        }
        if ($observations) $this->memberActivity($observations, $p, $users, $agents, $result);
        return $result;
    }

    private function memberActivity(array $observations, array $p, array $users, array $agents, array &$result): void
    {
        $last = []; $intervals = []; $timeline = []; $windowStart = Carbon::parse($p['start'], 'Asia/Shanghai')->timestamp;
        foreach ($observations as $row) {
            $time = Carbon::parse($row['observed_at'], 'Asia/Shanghai')->timestamp; $session = $row['session_hash'];
            if ($time >= $windowStart) $this->addActivity($result, $row, 0, $p, $users, $agents);
            if (isset($last[$session]) && $time > $last[$session] && $time - $last[$session] <= 300) $intervals[] = [max($last[$session], $windowStart), $time];
            $last[$session] = $time;
            // Across devices, the latest observation owns time until the next observation.
            // If observations share a minute, the last persisted snapshot wins consistently.
            $timeline[$time] = $row;
        }
        usort($intervals, fn ($a, $b) => $a[0] <=> $b[0]); $merged = [];
        foreach ($intervals as [$start, $end]) {
            $i = count($merged) - 1;
            if ($i >= 0 && $start <= $merged[$i][1]) $merged[$i][1] = max($merged[$i][1], $end); else $merged[] = [$start, $end];
        }
        $times = array_keys($timeline); $index = 0;
        foreach ($merged as [$start, $end]) {
            while (($times[$index + 1] ?? PHP_INT_MAX) <= $start) ++$index;
            while ($start < $end) {
                $next = min($end, $times[$index + 1] ?? $end);
                $this->addActivity($result, $timeline[$times[$index]], $next - $start, $p, $users, $agents);
                $start = $next;
                if (isset($times[$index + 1]) && $start === $times[$index + 1]) ++$index;
            }
        }
    }

    private function addActivity(array &$result, array $row, int $seconds, array $p, array $users, array $agents): void
    {
        $row['kind'] = 'activity';
        if (!$this->scope($row, $p, $users, $agents)) return;
        $group = $this->group($row, $p);
        if ($group === null || (isset($p['group_id']) && $group !== $p['group_id'])) return;
        $uid = $row['user_id'];
        $result['members'][$uid]['seconds'] = ($result['members'][$uid]['seconds'] ?? 0) + $seconds;
        $result['groups'][$group][$uid]['seconds'] = ($result['groups'][$group][$uid]['seconds'] ?? 0) + $seconds;
    }
    private function within(?string $at, array $p): bool { return $at !== null && $at >= $p['start'] && $at < $p['end']; }
    private function add(array &$value, string $field, string $amount): void { $value[$field] = bcadd($value[$field], $amount, 10); }
    private function account(array $user): string { return (string) (($user['username'] ?? '') ?: (($user['account'] ?? '') ?: ($user['member_id'] ?? $user['user_id'] ?? $user['id']))); }
    private function group(array $row, array $p): ?string
    {
        if ($p['report_type'] === 'business') return isset($row['user_id']) ? (string) $row['user_id'] : null;
        if ($p['report_type'] === 'promotion') return isset($row['agent_id']) ? (string) $row['agent_id'] : null;
        return in_array($row['kind'] ?? '', ['share_commission', 'share_accrual'], true) ? (string) $row['user_id'] : (isset($row['inviter_id']) ? (string) $row['inviter_id'] : null);
    }
    private function label(string $id, array $p, array $users, array $agents): string
    {
        if ($id === 'unallocated') return '未分配公共资金与成本';
        if ($p['report_type'] === 'promotion') return (string) ($agents[$id]['username'] ?? $id);
        return isset($users[$id]) ? $this->account($users[$id]) : $id;
    }
}