bot-28/app/Services/PromotionFacts.php

<?php

namespace App\Services;

use App\Services\Sharing\EffectiveBet;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Validation\ValidationException;

/** Promotion eligibility is independent of commission accrual and never uses raw stake as effective stake. */
class PromotionFacts
{
    private ?array $snapshotCache = null;

    /** Share repeated reads only within one refresh; never retain eligibility across requests or accounts. */
    public function snapshot(callable $work): mixed
    {
        if ($this->snapshotCache !== null) return $work();
        $this->snapshotCache = [];
        try { return $work(); }
        finally { $this->snapshotCache = null; }
    }

    private function remember(string $method, array $arguments, callable $read): mixed
    {
        if ($this->snapshotCache === null) return $read();
        $key = $method . ':' . json_encode($arguments, JSON_THROW_ON_ERROR);
        return $this->snapshotCache[$key] ??= $read();
    }

    public static function now(): string { return now('Asia/Shanghai')->format('Y-m-d H:i:s'); }

    public function policy(): array
    {
        $row = DB::table('share_policies')->where('effective_date', '<=', substr(self::now(), 0, 10))->orderByDesc('effective_date')->first();
        if (!$row || $row->valid_member_min_deposit === null || $row->valid_member_min_effective_bet === null) {
            throw ValidationException::withMessages(['policy' => '请先配置已生效的有效会员充值和有效投注门槛']);
        }
        return ['id' => $row->id, 'effective_date' => $row->effective_date,
            'min_deposit' => (string)$row->valid_member_min_deposit, 'min_effective_bet' => (string)$row->valid_member_min_effective_bet];
    }

    public function children(int $userId): array
    {
        return $this->remember(__FUNCTION__, func_get_args(), fn () => $this->readChildren($userId));
    }

    private function readChildren(int $userId): array
    {
        return DB::table('share_members as m')->join('users as u', 'u.id', '=', 'm.user_id')
            ->where('m.parent_user_id', $userId)->where('u.from', '<>', 2)->where('m.bound_at', '<=', self::now())
            ->get(['u.id', 'u.member_id', 'u.created_at', 'm.bound_at'])->map(fn ($r) => (array)$r)->all();
    }

    public function deposits(array $members, string $from, ?string $until = null, int $cursor = 0): array
    {
        return $this->remember(__FUNCTION__, [$members, $from, $until, $cursor], fn () => $this->readDeposits($members, $from, $until, $cursor));
    }

    private function readDeposits(array $members, string $from, ?string $until, int $cursor): array
    {
        $totals = [];
        if (!$members) return $totals;
        $q = DB::table('balance_logs')->whereIn('member_id', $members)->where('type', 1)->where('amount', '>', 0)
            ->whereIn('change_type', ['充值', '三方充值'])->where('created_at', '>=', $from)->where('created_at', '<=', self::now())->where('id', '>', $cursor);
        if ($until !== null) $q->where('created_at', '<', $until);
        if ($this->usesDecimalAggregates()) {
            foreach ($q->selectRaw('SUBSTR(created_at, 1, 10) AS day, SUM(amount) AS total')->groupByRaw('SUBSTR(created_at, 1, 10)')->get() as $row) {
                $totals[$row->day] = bcadd((string) $row->total, '0', 10);
            }
            return $totals;
        }
        foreach ($q->orderBy('id')->cursor() as $row) {
            $day = substr($row->created_at, 0, 10);
            $totals[$day] = bcadd($totals[$day] ?? '0', (string)$row->amount, 10);
        }
        return $totals;
    }

    public function cursors(): array
    {
        $result = [];
        foreach (['share_bet_facts', 'lhc_order_item', 'sport_order', 'sport_game_order', 'third_game_orders', 'bets', 'jisu_game_order'] as $table) {
            if (Schema::hasTable($table)) $result[$table] = (int)DB::table($table)->max('id');
        }
        return $result;
    }

    public function betting(array $ids, string $from, ?string $until = null, array $cursors = []): array
    {
        return $this->remember(__FUNCTION__, [$ids, $from, $until, $cursors], fn () => $this->readBetting($ids, $from, $until, $cursors));
    }

    private function readBetting(array $ids, string $from, ?string $until, array $cursors): array
    {
        $totals = ['effective' => [], 'profit' => '0'];
        if (!$ids) return $totals;
        $currency = (string)config('third_game.currency', 'CNY');
        $q = DB::table('share_bet_facts')->whereIn('user_id', $ids)->where('bet_at', '>=', $from)
            ->where('bet_at', '<=', self::now())->where('currency', $currency)->where('settled', 1)->where('voided', 0);
        if ($until !== null) $q->where('bet_at', '<', $until);
        if (isset($cursors['third_game_orders'])) {
            // One indexed semijoin replaces a separate original-order lookup for every fact.
            $q->where(function ($q) use ($cursors) {
                $q->where('source', '<>', 'third_game_orders')->orWhereExists(function ($origin) use ($cursors) {
                    $origin->selectRaw('1')->from('third_game_orders as promotion_origin')
                        ->whereColumn('promotion_origin.order_key', 'share_bet_facts.source_id')->where('promotion_origin.id', '>', $cursors['third_game_orders']);
                });
            });
        }
        if (!$cursors && $this->usesDecimalAggregates()) {
            $this->aggregateBets($q, 'bet_at', 'bet_amount', 'net_profit', $totals);
        } else {
            foreach ($q->cursor() as $row) {
                if (!$this->afterCursor($row, $cursors)) continue;
                $this->addBet($totals, $row->bet_at, (string)$row->bet_amount, (string)$row->net_profit);
            }
        }
        // The legacy Telegram games are not imported into sharing facts. Read only their own sources.
        $members = DB::table('users')->whereIn('id', $ids)->pluck('member_id')->all();
        foreach (['bets', 'jisu_game_order'] as $table) {
            if (!Schema::hasTable($table)) continue;
            $q = DB::table($table)->whereIn('member_id', $members)->where('created_at', '>=', $from)->where('created_at', '<=', self::now())
                ->where('id', '>', $cursors[$table] ?? 0)->whereIn('status', $table === 'bets' ? [2] : [1, 2]);
            if ($until !== null) $q->where('created_at', '<', $until);
            if ($this->usesDecimalAggregates()) {
                $this->aggregateBets($q, 'created_at', 'amount', ($table === 'bets' ? 'profit' : 'win_amount') . ' - amount', $totals);
            } else {
                foreach ($q->cursor() as $row) {
                    $profit = bcsub((string)($table === 'bets' ? $row->profit : $row->win_amount), (string)$row->amount, 10);
                    $this->addBet($totals, $row->created_at, (string)$row->amount, $profit);
                }
            }
        }
        return $totals;
    }

    private function afterCursor(object $fact, array $cursors): bool
    {
        if (!$cursors) return true;
        if ($fact->source === 'third_game_orders' && isset($cursors['third_game_orders'])) {
            return true; // Already checked in the source query.
        }
        if (isset($cursors[$fact->source])) return ctype_digit((string)$fact->source_id) && (int)$fact->source_id > $cursors[$fact->source];
        return (int)$fact->id > ($cursors['share_bet_facts'] ?? 0);
    }

    private function addBet(array &$totals, string $at, string $stake, string $profit): void
    {
        $day = substr($at, 0, 10);
        $totals['effective'][$day] = bcadd($totals['effective'][$day] ?? '0', EffectiveBet::amount($stake, $profit), 10);
        $totals['profit'] = bcadd($totals['profit'], $profit, 10);
    }

    private function usesDecimalAggregates(): bool
    {
        // SQLite SUM uses floating point; keep BCMath there so isolated tests preserve exact amounts.
        return in_array(DB::connection()->getDriverName(), ['mysql', 'mariadb'], true);
    }

    private function aggregateBets($query, string $time, string $stake, string $profit, array &$totals): void
    {
        foreach ($query->selectRaw("SUBSTR($time, 1, 10) AS day, SUM(CASE WHEN $stake < ABS($profit) THEN $stake ELSE ABS($profit) END) AS effective_total, SUM($profit) AS profit_total, SUM(CASE WHEN $stake < 0 THEN 1 ELSE 0 END) AS invalid_stakes, SUM(CASE WHEN $stake IS NULL OR ($profit) IS NULL THEN 1 ELSE 0 END) AS invalid_amounts")
            ->groupByRaw("SUBSTR($time, 1, 10)")->get() as $row) {
            if ($row->invalid_amounts > 0) throw new \InvalidArgumentException('Expected a plain decimal amount');
            if ($row->invalid_stakes > 0) throw new \InvalidArgumentException('Bet amount cannot be negative');
            $totals['effective'][$row->day] = bcadd($totals['effective'][$row->day] ?? '0', (string) $row->effective_total, 10);
            $totals['profit'] = bcadd($totals['profit'], (string) $row->profit_total, 10);
        }
    }

    public function qualified(array $child, array $policy, ?string $before = null): bool
    {
        $deposits = $this->deposits([(string)$child['member_id']], '1970-01-01 00:00:00', $before);
        $bet = $this->betting([(int)$child['id']], '1970-01-01 00:00:00', $before);
        return bccomp($this->total($deposits), $policy['min_deposit'], 10) >= 0
            && bccomp($this->total($bet['effective']), $policy['min_effective_bet'], 10) >= 0;
    }

    public function total(array $amounts): string
    {
        $total = '0'; foreach ($amounts as $amount) $total = bcadd($total, (string)$amount, 10); return $total;
    }

    public function logins(string $memberId, string $from): array
    {
        return $this->remember(__FUNCTION__, func_get_args(), fn () => $this->readLogins($memberId, $from));
    }

    private function readLogins(string $memberId, string $from): array
    {
        $days = [];
        foreach (DB::table('user_login')->where('user_id', $memberId)->where('status', 1)->where('created_at', '>=', $from)
            ->where('created_at', '<=', self::now())->cursor() as $row) $days[substr($row->created_at, 0, 10)] = true;
        $userId = DB::table('users')->where('member_id', $memberId)->value('id');
        foreach (['promotion_presence', 'reporting_activity'] as $table) {
            if (!Schema::hasTable($table)) continue;
            foreach (DB::table($table)->where('user_id', $userId)->where('observed_at', '>=', $from)->where('observed_at', '<=', self::now())->cursor() as $row) $days[substr($row->observed_at, 0, 10)] = true;
        }
        return $days;
    }

    public function online(int $userId, string $from): array
    {
        return $this->remember(__FUNCTION__, func_get_args(), fn () => $this->readOnline($userId, $from));
    }

    private function readOnline(int $userId, string $from): array
    {
        $last = []; $intervals = []; $observations = [];
        foreach (['reporting_activity', 'promotion_presence'] as $table) {
            if (!Schema::hasTable($table)) continue;
            foreach (DB::table($table)->where('user_id', $userId)->where('observed_at', '>=', $from)->where('observed_at', '<=', self::now())->cursor() as $row) $observations[] = $row;
        }
        usort($observations, fn ($a, $b) => strcmp($a->observed_at, $b->observed_at));
        foreach ($observations as $row) {
            $at = Carbon::parse($row->observed_at, 'Asia/Shanghai')->timestamp;
            $key = $row->session_hash;
            if (isset($last[$key]) && $at > $last[$key] && $at - $last[$key] <= 300) {
                $start = $last[$key];
                while ($start < $at) {
                    $day = Carbon::createFromTimestamp($start, 'Asia/Shanghai')->toDateString();
                    $end = min($at, Carbon::parse($day, 'Asia/Shanghai')->addDay()->timestamp);
                    $intervals[$day][] = [$start, $end]; $start = $end;
                }
            }
            $last[$key] = $at;
        }
        $result = [];
        foreach ($intervals as $day => $list) {
            usort($list, fn ($a, $b) => $a[0] <=> $b[0]); $end = 0; $seconds = 0;
            foreach ($list as [$a, $b]) { if ($b > max($a, $end)) $seconds += $b - max($a, $end); $end = max($end, $b); }
            $result[$day] = $seconds;
        }
        return $result;
    }
}