bot-28/app/Services/ThirdGameOrderService.php

<?php

namespace App\Services;

use App\Models\ThirdGameOrder as ThirdGameOrderModel;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class ThirdGameOrderService
{
    private const GAME_TYPE_NAMES = [
        1 => '视讯',
        2 => '老虎机',
        3 => '彩票',
        4 => '体育',
        5 => '电竞',
        6 => '捕猎',
        7 => '棋牌',
    ];

    private const STATUS_NAMES = [
        0 => '未完成',
        1 => '已完成',
        2 => '已取消',
        3 => '已撤单',
    ];

    private ThirdGameBalanceService $provider;

    public function __construct(ThirdGameBalanceService $provider)
    {
        $this->provider = $provider;
    }

    public function sync(
        string $startTime,
        string $endTime,
        int $page = 1,
        int $limit = 2000
    ): array {
        $result = $this->provider->historyOrders($startTime, $endTime, $page, $limit);
        if (empty($result['ok'])) {
            return $result;
        }

        $orders = is_array($result['list'] ?? null) ? $result['list'] : [];
        $users = $this->usersByPlayerId(array_values(array_unique(array_filter(array_map(
            static fn(array $order): string => trim((string) ($order['player_id'] ?? '')),
            $orders
        )))));
        $existing = $this->existingSnapshots($orders);
        $now = Carbon::now(config('app.timezone', 'Asia/Shanghai'))->format('Y-m-d H:i:s');
        $rows = [];
        $matchedUserIds = [];

        foreach ($orders as $order) {
            $platform = trim((string) ($order['platform'] ?? ''));
            $gameOrderId = trim((string) ($order['game_order_id'] ?? ''));
            if ($gameOrderId === '') {
                continue;
            }

            $playerId = trim((string) ($order['player_id'] ?? ''));
            $identity = $this->identity($platform, $playerId, $gameOrderId);
            $user = $users[$playerId] ?? null;
            $previous = $existing[$identity] ?? null;
            $userId = $user->id ?? $previous->user_id ?? null;
            if ($userId !== null) {
                $matchedUserIds[(int) $userId] = true;
            }

            $rows[$identity] = [
                'order_key' => $identity,
                'user_id' => $userId,
                'member_id' => $user->member_id ?? $previous->member_id ?? null,
                'username' => (string) ($user->username ?? $previous->username ?? ''),
                'first_name' => (string) ($user->first_name ?? $previous->first_name ?? ''),
                'player_id' => $playerId,
                'platform' => $platform,
                'currency' => trim((string) ($order['currency'] ?? '')),
                'game_type' => min(255, max(0, (int) ($order['game_type'] ?? 0))),
                'game_name' => (string) ($order['game_name'] ?? ''),
                'round_no' => (string) ($order['round'] ?? ''),
                'table_no' => (string) ($order['table'] ?? ''),
                'seat' => (string) ($order['seat'] ?? ''),
                'bet_amount' => $this->decimal($order['bet_amount'] ?? null),
                'valid_amount' => $this->decimal($order['valid_amount'] ?? null),
                'settled_amount' => $this->decimal($order['settled_amount'] ?? null),
                'bet_content' => $this->text($order['bet_content'] ?? ''),
                'status' => min(255, max(0, (int) ($order['status'] ?? 0))),
                'game_order_id' => $gameOrderId,
                'bet_time' => $this->dateTime($order['bet_time'] ?? null),
                'last_update_time' => $this->dateTime($order['last_update_time'] ?? null),
                'created_at' => $previous->created_at ?? $now,
                'updated_at' => $now,
            ];
        }

        if ($rows !== []) {
            ThirdGameOrderModel::query()->upsert(
                array_values($rows),
                ['order_key'],
                [
                    'user_id', 'member_id', 'username', 'first_name', 'player_id', 'currency',
                    'game_type', 'game_name', 'round_no', 'table_no', 'seat', 'bet_amount',
                    'valid_amount', 'settled_amount', 'bet_content', 'status', 'bet_time',
                    'last_update_time', 'updated_at',
                ]
            );
        }

        $pageNo = (int) ($result['page_no'] ?? $page);
        $pageSize = (int) ($result['page_size'] ?? $limit);
        $total = (int) ($result['total'] ?? count($orders));

        return [
            'ok' => true,
            'received' => count($orders),
            'synced' => count($rows),
            'matched_users' => count($matchedUserIds),
            'unmatched_orders' => count(array_filter($rows, static fn(array $row): bool => $row['user_id'] === null)),
            'total' => $total,
            'page_no' => $pageNo,
            'page_size' => $pageSize,
            'has_more' => $pageSize > 0 && $pageNo * $pageSize < $total,
        ];
    }

    public function paginate(array $params): array
    {
        $page = max(1, (int) ($params['page'] ?? 1));
        $limit = min(200, max(1, (int) ($params['limit'] ?? 20)));
        $query = ThirdGameOrderModel::query()
            ->leftJoin('users', 'users.id', '=', 'third_game_orders.user_id');

        if (!empty($params['user_id'])) {
            $userId = (int) $params['user_id'];
            $query->where(function ($query) use ($userId) {
                $query->where('users.id', $userId)
                    ->orWhere(function ($query) use ($userId) {
                        $query->whereNull('users.id')
                            ->where('third_game_orders.user_id', $userId);
                    });
            });
        }
        if (!empty($params['member_id'])) {
            $memberId = (string) $params['member_id'];
            $query->where(function ($query) use ($memberId) {
                $query->where('users.member_id', $memberId)
                    ->orWhere(function ($query) use ($memberId) {
                        $query->whereNull('users.id')
                            ->where('third_game_orders.member_id', $memberId);
                    });
            });
        }
        if (!empty($params['username'])) {
            $username = '%' . $params['username'] . '%';
            $query->where(function ($query) use ($username) {
                $query->where('users.username', 'like', $username)
                    ->orWhere(function ($query) use ($username) {
                        $query->whereNull('users.id')
                            ->where('third_game_orders.username', 'like', $username);
                    });
            });
        }
        if (!empty($params['first_name'])) {
            $firstName = '%' . $params['first_name'] . '%';
            $query->where(function ($query) use ($firstName) {
                $query->where('users.first_name', 'like', $firstName)
                    ->orWhere(function ($query) use ($firstName) {
                        $query->whereNull('users.id')
                            ->where('third_game_orders.first_name', 'like', $firstName);
                    });
            });
        }
        if (!empty($params['player_id'])) {
            $query->where('third_game_orders.player_id', (string) $params['player_id']);
        }
        if (!empty($params['platform'])) {
            $query->where('third_game_orders.platform', (string) $params['platform']);
        }
        if (!empty($params['game_order_id'])) {
            $query->where('third_game_orders.game_order_id', (string) $params['game_order_id']);
        }
        if (isset($params['game_type']) && $params['game_type'] !== '') {
            $query->where('third_game_orders.game_type', (int) $params['game_type']);
        }
        if (isset($params['status']) && $params['status'] !== '') {
            $query->where('third_game_orders.status', (int) $params['status']);
        }
        if (!empty($params['start_time']) && !empty($params['end_time'])) {
            $query->whereBetween('third_game_orders.last_update_time', [
                $params['start_time'],
                $params['end_time'],
            ]);
        }

        $total = (clone $query)->count('third_game_orders.id');
        $list = $query
            ->select('third_game_orders.*')
            ->addSelect([
                'users.id as current_user_id',
                'users.member_id as current_member_id',
                'users.username as current_username',
                'users.first_name as current_first_name',
            ])
            ->orderByDesc('third_game_orders.last_update_time')
            ->orderByDesc('third_game_orders.id')
            ->forPage($page, $limit)
            ->get()
            ->map(fn(ThirdGameOrderModel $order): array => $this->format($order))
            ->all();

        return [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'list' => $list,
            'options' => [
                'game_types' => $this->options(self::GAME_TYPE_NAMES),
                'statuses' => $this->options(self::STATUS_NAMES),
            ],
        ];
    }

    /**
     * @return array<string, object>
     */
    private function usersByPlayerId(array $playerIds): array
    {
        if ($playerIds === []) {
            return [];
        }

        $sn = (string) config('third_game.sn');
        $placeholders = implode(',', array_fill(0, count($playerIds), '?'));
        $expression = "CONCAT('p', SUBSTRING(MD5(CONCAT(?, '_', `member_id`)), 1, 10))";
        $rows = DB::table('users')
            ->select(['id', 'member_id', 'username', 'first_name'])
            ->selectRaw($expression . ' AS third_game_player_id', [$sn])
            ->whereRaw($expression . " IN ({$placeholders})", array_merge([$sn], $playerIds))
            ->get();

        $result = [];
        foreach ($rows as $row) {
            $result[(string) $row->third_game_player_id] = $row;
        }

        return $result;
    }

    /**
     * @return array<string, ThirdGameOrderModel>
     */
    private function existingSnapshots(array $orders): array
    {
        $orderKeys = [];
        foreach ($orders as $order) {
            $gameOrderId = trim((string) ($order['game_order_id'] ?? ''));
            if ($gameOrderId === '') {
                continue;
            }
            $orderKeys[] = $this->identity(
                trim((string) ($order['platform'] ?? '')),
                trim((string) ($order['player_id'] ?? '')),
                $gameOrderId
            );
        }
        $orderKeys = array_values(array_unique($orderKeys));
        if ($orderKeys === []) {
            return [];
        }

        $rows = ThirdGameOrderModel::query()
            ->whereIn('order_key', $orderKeys)
            ->get(['order_key', 'user_id', 'member_id', 'username', 'first_name', 'created_at']);
        $result = [];
        foreach ($rows as $row) {
            $result[(string) $row->order_key] = $row;
        }

        return $result;
    }

    private function format(ThirdGameOrderModel $order): array
    {
        $userId = $order->current_user_id ?? $order->user_id;
        $memberId = $order->current_member_id ?? $order->member_id;
        $username = $order->current_username ?? $order->username;
        $firstName = $order->current_first_name ?? $order->first_name;
        $gameType = (int) $order->game_type;
        $status = (int) $order->status;

        return [
            'id' => (int) $order->id,
            'user_id' => $userId === null ? null : (int) $userId,
            'member_id' => $memberId === null ? null : (string) $memberId,
            'username' => (string) ($username ?? ''),
            'first_name' => (string) ($firstName ?? ''),
            'player_id' => (string) $order->player_id,
            'platform' => (string) $order->platform,
            'currency' => (string) $order->currency,
            'game_type' => $gameType,
            'game_type_text' => self::GAME_TYPE_NAMES[$gameType] ?? '未知',
            'game_name' => (string) $order->game_name,
            'round' => (string) $order->round_no,
            'table' => (string) $order->table_no,
            'seat' => (string) $order->seat,
            'bet_amount' => $this->number($order->bet_amount),
            'valid_amount' => $this->number($order->valid_amount),
            'settled_amount' => $this->number($order->settled_amount),
            'bet_content' => (string) ($order->bet_content ?? ''),
            'status' => $status,
            'status_text' => self::STATUS_NAMES[$status] ?? '未知',
            'game_order_id' => (string) $order->game_order_id,
            'bet_time' => $this->formatTime($order->bet_time),
            'last_update_time' => $this->formatTime($order->last_update_time),
        ];
    }

    private function identity(string $platform, string $playerId, string $gameOrderId): string
    {
        return hash('sha256', $platform . "\0" . $playerId . "\0" . $gameOrderId);
    }

    private function decimal($value): ?string
    {
        if (!is_numeric($value)) {
            return null;
        }
        $value = trim((string) $value);
        if (preg_match('/^-?\d+(?:\.\d+)?$/D', $value)) {
            return bcadd($value, '0', 10);
        }

        return number_format((float) $value, 10, '.', '');
    }

    private function text($value): string
    {
        if (is_array($value) || is_object($value)) {
            return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) ?: '';
        }

        return is_scalar($value) ? (string) $value : '';
    }

    private function dateTime($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        try {
            return Carbon::parse($value, config('app.timezone', 'Asia/Shanghai'))->format('Y-m-d H:i:s');
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function formatTime($value): ?string
    {
        return $value ? Carbon::parse($value)->format('Y-m-d H:i:s') : null;
    }

    private function number($value)
    {
        return $value === null || $value === '' ? null : (float) $value;
    }

    private function options(array $items): array
    {
        $options = [];
        foreach ($items as $value => $label) {
            $options[] = ['label' => $label, 'value' => $value];
        }

        return $options;
    }
}