bot-28/app/Services/ShareEarningReportService.php

<?php

namespace App\Services;

use App\Models\BalanceLog;
use App\Models\ShareCommissionRecord;
use App\Models\ShareSetting;
use App\Models\User;
use App\Models\UserLogin;
use Illuminate\Support\Facades\DB;

class ShareEarningReportService
{
    public function memberDays(array $params): array
    {
        $range = !empty($params['start_date']) || !empty($params['end_date']) ? $this->dateRange($params) : null;
        $page = (int)($params['page'] ?? 1);
        $limit = (int)($params['limit'] ?? 20);
        $dateQuery = User::query()->whereNotNull('agent_user_code')->where('agent_user_code', '<>', '');
        if ($range !== null) {
            $dateQuery->whereBetween('created_at', [$range[0] . ' 00:00:00', $range[1] . ' 23:59:59']);
        }
        $dateQuery->selectRaw('DATE(created_at) AS stat_date')
            ->groupByRaw('DATE(created_at)')
            ->orderByDesc('stat_date');
        $dates = $dateQuery->pluck('stat_date');
        $pageDates = $dates->slice(($page - 1) * $limit, $limit)->values();
        if ($pageDates->isEmpty()) return ['total' => $dates->count(), 'data' => []];
        $pageStart = (string)$pageDates->min();
        $pageEnd = (string)$pageDates->max();

        $basicRows = User::query()->whereNotNull('agent_user_code')->where('agent_user_code', '<>', '')
            ->whereBetween('created_at', [$pageStart . ' 00:00:00', $pageEnd . ' 23:59:59'])
            ->selectRaw('DATE(created_at) AS stat_date')
            ->selectRaw('COUNT(*) AS registered_members')
            ->selectRaw('COUNT(DISTINCT agent_user_code) AS promoter_count')
            ->groupByRaw('DATE(created_at)')->get()->keyBy('stat_date');

        $inviteeBase = User::query()->whereNotNull('agent_user_code')->where('agent_user_code', '<>', '')
            ->whereBetween('created_at', [$pageStart . ' 00:00:00', $pageEnd . ' 23:59:59'])
            ->select(['id', 'member_id'])->selectRaw('DATE(created_at) AS stat_date');
        $memberMetrics = DB::query()->fromSub($inviteeBase, 'invitee')
            ->leftJoin('balance_logs as balance_log', 'balance_log.member_id', '=', 'invitee.member_id')
            ->select(['invitee.id', 'invitee.stat_date'])
            ->selectRaw('SUM(CASE WHEN change_type IN (?, ?) THEN amount ELSE 0 END) AS deposit', ['充值', '三方充值'])
            ->selectRaw('ABS(SUM(CASE WHEN change_type IN (?, ?, ?) THEN amount ELSE 0 END)) AS withdraw', ['提现', '三方提现', '人工提现'])
            ->selectRaw('ABS(SUM(CASE WHEN change_type LIKE ? THEN amount ELSE 0 END)) AS consume', ['%投注%'])
            ->selectRaw('SUM(CASE WHEN change_type IN (?, ?, ?, ?, ?, ?) THEN amount ELSE 0 END) AS bonus', ['注册赠送', '优惠活动', '返水', '回水', '笔笔返', '比比返'])
            ->groupBy(['invitee.id', 'invitee.stat_date']);
        $validMin = (float)(ShareSetting::query()->value('valid_member_min_deposit') ?? 0);
        $metricRows = DB::query()->fromSub($memberMetrics, 'member_metric')
            ->select('stat_date')
            ->selectRaw('SUM(deposit) AS deposit_total')
            ->selectRaw('SUM(withdraw) AS withdraw_total')
            ->selectRaw('SUM(consume) AS consume_total')
            ->selectRaw('SUM(bonus) AS bonus_total')
            ->selectRaw('SUM(CASE WHEN deposit >= ? THEN 1 ELSE 0 END) AS valid_members', [$validMin])
            ->groupBy('stat_date')->get()->keyBy('stat_date');

        $pageMemberIds = User::query()->whereNotNull('agent_user_code')->where('agent_user_code', '<>', '')
            ->whereBetween('created_at', [$pageStart . ' 00:00:00', $pageEnd . ' 23:59:59'])
            ->select('member_id');
        $firstLoginIds = UserLogin::query()->where('status', UserLogin::STATUS_SUCCESS)
            ->whereIn('user_id', $pageMemberIds)
            ->select('user_id')->selectRaw('MIN(id) AS first_login_id')->groupBy('user_id');
        $prefix = DB::getTablePrefix();
        $userAlias = $prefix . 'invitee_user';
        $loginAlias = $prefix . 'first_login_row';
        $platformRows = User::query()->from('users as invitee_user')
            ->leftJoinSub($firstLoginIds, 'first_login', 'first_login.user_id', '=', 'invitee_user.member_id')
            ->leftJoin('user_login as first_login_row', 'first_login_row.id', '=', 'first_login.first_login_id')
            ->whereNotNull('invitee_user.agent_user_code')->where('invitee_user.agent_user_code', '<>', '')
            ->whereBetween('invitee_user.created_at', [$pageStart . ' 00:00:00', $pageEnd . ' 23:59:59'])
            ->selectRaw("DATE({$userAlias}.created_at) AS stat_date")
            ->selectRaw("SUM(CASE WHEN {$loginAlias}.platform = ? THEN 1 ELSE 0 END) AS h5_registered", ['H5'])
            ->selectRaw("SUM(CASE WHEN {$loginAlias}.platform = ? THEN 1 ELSE 0 END) AS ios_registered", ['iOS'])
            ->selectRaw("SUM(CASE WHEN {$loginAlias}.platform = ? THEN 1 ELSE 0 END) AS android_registered", ['Android'])
            ->groupByRaw("DATE({$userAlias}.created_at)")->get()->keyBy('stat_date');

        $rows = $pageDates->map(function ($day) use ($basicRows, $metricRows, $platformRows) {
            $basic = $basicRows->get($day);
            $metric = $metricRows->get($day);
            $platform = $platformRows->get($day);
            $deposit = (float)($metric->deposit_total ?? 0);
            $withdraw = (float)($metric->withdraw_total ?? 0);
            $bonus = (float)($metric->bonus_total ?? 0);
            return [
                'date' => (string)$day,
                'promoter_count' => (int)($basic->promoter_count ?? 0),
                'registered_members' => (int)($basic->registered_members ?? 0),
                'valid_members' => (int)($metric->valid_members ?? 0),
                'h5_registered' => (int)($platform->h5_registered ?? 0),
                'ios_registered' => (int)($platform->ios_registered ?? 0),
                'android_registered' => (int)($platform->android_registered ?? 0),
                'deposit_total' => number_format($deposit, 4, '.', ''),
                'consume_total' => number_format((float)($metric->consume_total ?? 0), 4, '.', ''),
                'company_profit' => number_format($deposit - $withdraw - $bonus, 4, '.', ''),
            ];
        });
        return ['total' => $dates->count(), 'data' => $rows->values()];
    }

    public function promoterDetails(array $params): array
    {
        $day = $params['date'];
        $promoterCodes = User::query()->whereNotNull('agent_user_code')->where('agent_user_code', '<>', '')
            ->whereBetween('created_at', [$day . ' 00:00:00', $day . ' 23:59:59'])
            ->select('agent_user_code')->distinct();
        $promoterAlias = DB::getTablePrefix() . 'promoter';
        $entries = DB::query()->fromSub($promoterCodes, 'referral')
            ->leftJoin('users as promoter', 'promoter.user_code', '=', 'referral.agent_user_code')
            ->select('referral.agent_user_code')
            ->selectRaw("COUNT({$promoterAlias}.id) AS promoter_matches")
            ->groupBy('referral.agent_user_code');
        // Keep historical codes even when the inviter was removed or changed their code.
        // An ambiguous code cannot safely identify one member or their paid commission.
        foreach (['id' => 'promoter_id', 'member_id' => 'member_id', 'account' => 'account', 'first_name' => 'nickname'] as $column => $alias) {
            $entries->selectRaw("CASE WHEN COUNT({$promoterAlias}.id) = 1 THEN MAX({$promoterAlias}.{$column}) END AS {$alias}");
        }
        $query = DB::query()->fromSub($entries, 'promoter_entry');
        if (!empty($params['member_id'])) $query->where('promoter_entry.member_id', $params['member_id']);
        if (!empty($params['account'])) $query->where('promoter_entry.account', 'like', '%' . $params['account'] . '%');
        $total = $query->count();
        $promoters = $query->orderByDesc('promoter_entry.promoter_id')->orderByDesc('promoter_entry.agent_user_code')
            ->forPage((int)($params['page'] ?? 1), (int)($params['limit'] ?? 20))->get();
        $invitees = User::query()->whereBetween('created_at', [$day . ' 00:00:00', $day . ' 23:59:59'])
            ->whereIn('agent_user_code', $promoters->pluck('agent_user_code'))
            ->get(['id', 'member_id', 'agent_user_code']);
        $records = ShareCommissionRecord::query()->where('stat_date', $day)->where('status', 1)
            ->whereIn('promoter_user_id', $promoters->pluck('promoter_id')->filter())->get()->groupBy('promoter_user_id');
        $setting = ShareSetting::query()->first();
        $metricsByMember = $this->moneyMetricsByMember($invitees->pluck('member_id')->all());
        $rows = $promoters->map(function ($promoter) use ($invitees, $records, $setting, $day, $metricsByMember) {
            $children = $invitees->whereStrict('agent_user_code', (string)$promoter->agent_user_code);
            $metrics = $this->sumMetrics($metricsByMember, $children->pluck('member_id')->all());
            $commission = $records->get($promoter->promoter_id, collect())->where('type', 'commission')->sum('amount');
            return [
                'date' => $day,
                'promoter_code' => (string)$promoter->agent_user_code,
                'promoter_status' => (int)$promoter->promoter_matches === 1 ? 'matched'
                    : ((int)$promoter->promoter_matches === 0 ? 'missing' : 'ambiguous'),
                'member_id' => (string)($promoter->member_id ?? ''),
                'account' => (string)($promoter->account ?? ''),
                'nickname' => (string)($promoter->nickname ?? ''),
                'deposit_total' => $metrics['deposit'],
                'registered_count' => $children->count(),
                'commission' => number_format((float)$commission, 4, '.', ''),
                'current_fee_rate' => $setting ? (string)$setting->tier_one_rate : '0.0000',
            ];
        });
        return ['total' => $total, 'data' => $rows->values()];
    }

    public function registrationDetails(array $params): array
    {
        $query = User::query()->whereNotNull('agent_user_code')->where('agent_user_code', '<>', '')
            ->whereBetween('created_at', [$params['date'] . ' 00:00:00', $params['date'] . ' 23:59:59']);
        if (!empty($params['member_id'])) $query->where('member_id', $params['member_id']);
        if (!empty($params['account'])) $query->where('account', 'like', '%' . $params['account'] . '%');
        $total = $query->count();
        $users = $query->orderByDesc('id')->forPage((int)($params['page'] ?? 1), (int)($params['limit'] ?? 20))->get();
        $metricsByMember = $this->moneyMetricsByMember($users->pluck('member_id')->all());
        $rows = $users->map(function ($user) use ($params, $metricsByMember) {
            $metrics = $metricsByMember[(string)$user->member_id] ?? $this->emptyMetrics();
            return [
                'date' => $params['date'],
                'member_id' => (string)$user->member_id,
                'account' => (string)$user->account,
                'nickname' => (string)$user->first_name,
                'deposit_total' => $metrics['deposit'],
                'bet_total' => $metrics['consume'],
                'withdraw_total' => $metrics['withdraw'],
            ];
        });
        return ['total' => $total, 'data' => $rows->values()];
    }

    public function summary(array $params): array
    {
        $range = !empty($params['start_date']) || !empty($params['end_date']) ? $this->dateRange($params) : null;
        $page = (int)($params['page'] ?? 1);
        $limit = (int)($params['limit'] ?? 20);
        $prefix = DB::getTablePrefix();
        $recordAlias = $prefix . 'share_record';
        $query = ShareCommissionRecord::query()->from('share_commission_records as share_record')
            ->leftJoin('users as promoter', 'promoter.id', '=', 'share_record.promoter_user_id')
            ->where('share_record.status', 1);
        if ($range !== null) $query->whereBetween('share_record.stat_date', $range);
        if (!empty($params['account'])) $query->where('promoter.account', 'like', '%' . $params['account'] . '%');
        $totalQuery = clone $query;
        $grouped = $query->select([
            'share_record.stat_date', 'share_record.promoter_user_id',
            'promoter.member_id as promoter_member_id', 'promoter.account as promoter_account',
            'promoter.first_name as promoter_nickname',
        ])->selectRaw("SUM(CASE WHEN {$recordAlias}.type = 'commission' THEN {$recordAlias}.base_amount ELSE 0 END) AS deposit")
            ->selectRaw("SUM(CASE WHEN {$recordAlias}.type = 'commission' THEN {$recordAlias}.amount ELSE 0 END) AS commission")
            ->selectRaw("SUM(CASE WHEN {$recordAlias}.type = 'fee_waiver' THEN {$recordAlias}.base_amount ELSE 0 END) AS sales_amount")
            ->selectRaw("MAX(CASE WHEN {$recordAlias}.type = 'fee_waiver' THEN {$recordAlias}.rate ELSE 0 END) AS fee_waiver_rate")
            ->groupBy([
                'share_record.stat_date', 'share_record.promoter_user_id',
                'promoter.member_id', 'promoter.account', 'promoter.first_name',
            ]);
        $total = DB::query()->fromSub(clone $grouped, 'share_summary')->count();
        $rows = $grouped->orderByDesc('share_record.stat_date')->orderByDesc('share_record.promoter_user_id')
            ->forPage($page, $limit)->get()->map(fn ($row) => [
                'date' => $row->stat_date instanceof \DateTimeInterface
                    ? $row->stat_date->format('Y-m-d')
                    : substr((string)$row->stat_date, 0, 10),
                'promoter_member_id' => (string)($row->promoter_member_id ?? ''),
                'promoter_account' => (string)($row->promoter_account ?? ''),
                'promoter_nickname' => (string)($row->promoter_nickname ?? ''),
                'deposit' => number_format((float)$row->deposit, 4, '.', ''),
                'commission' => number_format((float)$row->commission, 4, '.', ''),
                'sales_amount' => number_format((float)$row->sales_amount, 4, '.', ''),
                'fee_waiver_rate' => number_format((float)$row->fee_waiver_rate, 4, '.', ''),
            ]);

        $totals = $totalQuery->selectRaw("SUM(CASE WHEN {$recordAlias}.type = 'commission' THEN {$recordAlias}.amount ELSE 0 END) AS total_commission")
            ->selectRaw("MAX(CASE WHEN {$recordAlias}.type = 'fee_waiver' THEN {$recordAlias}.rate ELSE 0 END) AS highest_fee_waiver_rate")->first();
        return [
            'total' => $total,
            'data' => $rows->values(),
            'summary' => [
                'total_commission' => number_format((float)($totals->total_commission ?? 0), 4, '.', ''),
                'highest_fee_waiver_rate' => number_format((float)($totals->highest_fee_waiver_rate ?? 0), 4, '.', ''),
            ],
        ];
    }

    private function moneyMetricsByMember(array $memberIds): array
    {
        $memberIds = array_values(array_unique(array_filter(array_map('strval', $memberIds))));
        if (!$memberIds) return [];
        $metrics = array_fill_keys($memberIds, $this->emptyMetrics());
        $sets = [
            'deposit' => fn ($query) => $query->whereIn('change_type', ['充值', '三方充值']),
            'withdraw' => fn ($query) => $query->whereIn('change_type', ['提现', '三方提现', '人工提现']),
            'consume' => fn ($query) => $query->where('change_type', 'like', '%投注%'),
            'bonus' => fn ($query) => $query->whereIn('change_type', ['注册赠送', '优惠活动', '返水', '回水', '笔笔返', '比比返']),
        ];
        foreach ($sets as $field => $scope) {
            $query = BalanceLog::query()->whereIn('member_id', $memberIds);
            $rows = $scope($query)->groupBy('member_id')->get(['member_id', DB::raw('SUM(amount) as total')]);
            foreach ($rows as $row) {
                $value = (float)$row->total;
                $metrics[(string)$row->member_id][$field] = number_format(in_array($field, ['withdraw', 'consume'], true) ? abs($value) : $value, 4, '.', '');
            }
        }
        return $metrics;
    }

    private function sumMetrics(array $metricsByMember, array $memberIds): array
    {
        $totals = ['deposit' => 0.0, 'withdraw' => 0.0, 'consume' => 0.0, 'bonus' => 0.0];
        foreach (array_unique(array_map('strval', $memberIds)) as $memberId) {
            foreach ($totals as $field => $value) {
                $totals[$field] += (float)($metricsByMember[$memberId][$field] ?? 0);
            }
        }
        return array_map(fn ($value) => number_format($value, 4, '.', ''), $totals);
    }

    private function emptyMetrics(): array
    {
        return ['deposit' => '0.0000', 'withdraw' => '0.0000', 'consume' => '0.0000', 'bonus' => '0.0000'];
    }

    private function dateRange(array $params): array
    {
        $end = $params['end_date'] ?? date('Y-m-d');
        $start = $params['start_date'] ?? date('Y-m-d', strtotime($end . ' -30 days'));
        if (strtotime($start) > strtotime($end)) {
            throw new \RuntimeException('结束日期不能早于开始日期');
        }
        if (strtotime($end) - strtotime($start) > 366 * 86400) {
            throw new \RuntimeException('单次查询日期范围不能超过 366 天');
        }
        return [$start, $end];
    }
}