bot-28/app/Services/ThirdPartyDepositService.php

<?php

namespace App\Services;

use App\Constants\HttpStatus;
use App\Models\BalanceLog;
use App\Models\PaymentOrder;
use App\Models\UserLogin;
use App\Services\Payment\JdPayService;
use App\Services\Payment\NoPayService;
use App\Services\Payment\ZimuPayService;
use Carbon\Carbon;
use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class ThirdPartyDepositService
{
    private const COMPANIES = [
        'sanjin' => '三斤支付',
        'jd' => 'JD支付',
        'no' => 'NO支付',
        'zimu' => '808支付',
    ];

    private const STATUSES = [
        PaymentOrderService::STATUS_STAY => '待处理',
        PaymentOrderService::STATUS_PROCESS => '处理中',
        PaymentOrderService::STATUS_SUCCESS => '成功',
        PaymentOrderService::STATUS_FAIL => '失败',
    ];

    private static ?bool $hasPlatform = null;

    private static ?bool $hasLoginStatus = null;

    public static function list(array $params): array
    {
        $page = max(1, (int)($params['page'] ?? 1));
        $limit = min(200, max(1, (int)($params['limit'] ?? 20)));
        $query = self::query($params);

        $total = (clone $query)->count('payment_orders.id');
        $rows = $query
            ->orderByDesc('payment_orders.id')
            ->forPage($page, $limit)
            ->get();
        $creditLogs = self::creditLogs($rows->pluck('id')->all());

        return [
            'total' => $total,
            'data' => $rows->map(static fn(PaymentOrder $row): array => self::format(
                $row,
                false,
                $creditLogs[(int)$row->id] ?? null
            ))->all(),
            'options' => self::options(),
        ];
    }

    public static function detail(int $id): array
    {
        $row = self::query(['id' => $id], true)->first();
        if (!$row) {
            throw new Exception('第三方入款记录不存在', HttpStatus::CUSTOM_ERROR);
        }

        $creditLogs = self::creditLogs([$id]);

        return self::format($row, true, $creditLogs[$id] ?? null);
    }

    public static function paymentCompany(?string $channel): array
    {
        if (ZimuPayService::isRechargeChannel($channel)) {
            $code = 'zimu';
        } elseif (NoPayService::isRechargeChannel($channel)) {
            $code = 'no';
        } elseif (JdPayService::isChannel($channel)) {
            $code = 'jd';
        } else {
            $code = 'sanjin';
        }

        return [
            'code' => $code,
            'name' => self::COMPANIES[$code],
        ];
    }

    public static function statusText(int $status): string
    {
        return self::STATUSES[$status] ?? '未知';
    }

    private static function query(array $params = [], bool $withRawData = false): Builder
    {
        $query = PaymentOrder::query()
            ->join('users', 'users.member_id', '=', 'payment_orders.member_id')
            ->where('payment_orders.type', PaymentOrderService::TYPE_PAY)
            ->select([
                'payment_orders.id',
                'payment_orders.member_id',
                'payment_orders.order_no',
                'payment_orders.pay_no',
                'payment_orders.amount',
                'payment_orders.fee',
                'payment_orders.channel',
                'payment_orders.bank_name',
                'payment_orders.status',
                'payment_orders.created_at as submitted_at',
                'users.user_id',
                'users.username',
                'users.first_name',
            ]);

        if ($withRawData) {
            $query->addSelect([
                'payment_orders.pay_data',
                'payment_orders.callback_data',
                'payment_orders.remark',
                'payment_orders.admin_note',
            ]);
        }

        self::selectPlatform($query);
        self::applyFilters($query, $params);

        return $query;
    }

    /**
     * @return array<int, BalanceLog>
     */
    private static function creditLogs(array $orderIds): array
    {
        if ($orderIds === []) {
            return [];
        }

        $logs = [];
        $rows = BalanceLog::query()
            ->select(['id', 'related_id', 'before_balance', 'after_balance', 'created_at'])
            ->whereIn('related_id', $orderIds)
            ->where('balance_logs.change_type', '三方充值')
            ->orderBy('balance_logs.id')
            ->get();

        foreach ($rows as $row) {
            $logs[(int)$row->related_id] = $row;
        }

        return $logs;
    }

    private static function selectPlatform(Builder $query): void
    {
        if (!self::hasPlatformColumn()) {
            $query->selectRaw("'' AS platform");
            return;
        }

        $platformSql = self::platformSql();
        $query->selectRaw("({$platformSql}) AS platform");
    }

    private static function applyFilters(Builder $query, array $params): void
    {
        if (!empty($params['id'])) {
            $query->where('payment_orders.id', (int)$params['id']);
        }
        if (!empty($params['member_id'])) {
            $query->where('payment_orders.member_id', (string)$params['member_id']);
        }
        if (!empty($params['payment_company'])) {
            self::filterPaymentCompany($query, (string)$params['payment_company']);
        }
        if (!empty($params['payment_method'])) {
            $query->where('payment_orders.bank_name', (string)$params['payment_method']);
        }
        if (!empty($params['channel'])) {
            $query->where('payment_orders.channel', (string)$params['channel']);
        }
        if (array_key_exists('status', $params) && $params['status'] !== null && $params['status'] !== '') {
            $query->where('payment_orders.status', (int)$params['status']);
        }
        if (!empty($params['submit_start_date']) && !empty($params['submit_end_date'])) {
            [$start, $end] = self::dateRange($params['submit_start_date'], $params['submit_end_date']);
            $query->whereBetween('payment_orders.created_at', [$start, $end]);
        }
        if (!empty($params['credit_start_date']) && !empty($params['credit_end_date'])) {
            [$start, $end] = self::dateRange($params['credit_start_date'], $params['credit_end_date']);
            $query->whereIn('payment_orders.id', function ($subquery) use ($start, $end) {
                $subquery->select('related_id')
                    ->from('balance_logs')
                    ->where('balance_logs.change_type', '三方充值')
                    ->whereBetween('balance_logs.created_at', [$start, $end]);
            });
        }
        if (!empty($params['platform'])) {
            if (!self::hasPlatformColumn()) {
                $query->whereRaw('1 = 0');
            } else {
                $query->whereRaw('(' . self::platformSql() . ') = ?', [(string)$params['platform']]);
            }
        }
    }

    private static function filterPaymentCompany(Builder $query, string $company): void
    {
        $zimuChannels = [
            ZimuPayService::CHANNEL_RECHARGE,
            '808pay',
            '808支付',
        ];
        $noChannels = [
            NoPayService::CHANNEL_SCAN,
            NoPayService::CHANNEL_BALANCE,
        ];
        $jdChannels = [
            JdPayService::CHANNEL,
            'JD钱包',
        ];
        $knownChannels = array_merge($zimuChannels, $noChannels, $jdChannels);

        if ($company === 'zimu') {
            $query->whereIn('payment_orders.channel', $zimuChannels);
        } elseif ($company === 'no') {
            $query->whereIn('payment_orders.channel', $noChannels);
        } elseif ($company === 'jd') {
            $query->whereIn('payment_orders.channel', $jdChannels);
        } else {
            $query->where(function (Builder $companyQuery) use ($knownChannels) {
                $companyQuery->whereNull('payment_orders.channel')
                    ->orWhereNotIn('payment_orders.channel', $knownChannels);
            });
        }
    }

    private static function format(PaymentOrder $row, bool $withRawData, ?BalanceLog $creditLog): array
    {
        $company = self::paymentCompany((string)$row->channel);
        $callbackData = self::decodeJson($row->callback_data ?? null);
        $payData = self::decodeJson($row->pay_data ?? null);

        $data = [
            'id' => (int)$row->id,
            'member_id' => (string)$row->member_id,
            'username' => (string)($row->username ?? ''),
            'first_name' => (string)($row->first_name ?? ''),
            'payment_company' => $company['code'],
            'payment_company_name' => $company['name'],
            'payment_method' => (string)($row->bank_name ?? ''),
            'channel_code' => (string)($row->channel ?? ''),
            'amount' => self::money($row->amount),
            'fee' => self::money($row->fee),
            'before_balance' => self::nullableMoney($creditLog?->before_balance),
            'after_balance' => self::nullableMoney($creditLog?->after_balance),
            'order_no' => (string)($row->order_no ?? ''),
            'gateway_order_no' => (string)($row->pay_no ?? ''),
            'gateway_response' => self::gatewayReference($callbackData, $payData, (string)($row->pay_no ?? '')),
            'submitted_at' => self::formatTime($row->submitted_at ?? null),
            'credited_at' => self::formatTime($creditLog?->created_at),
            'platform' => (string)($row->platform ?? ''),
            'status' => (int)$row->status,
            'status_text' => self::statusText((int)$row->status),
        ];

        if ($withRawData) {
            $data['remark'] = (string)($row->remark ?? '');
            $data['admin_note'] = (string)($row->admin_note ?? '');
            $data['pay_data'] = self::sanitizePayload($payData);
            $data['callback_data'] = self::sanitizePayload($callbackData);
        }

        return $data;
    }

    private static function options(): array
    {
        $paymentMethods = PaymentOrder::query()
            ->where('type', PaymentOrderService::TYPE_PAY)
            ->whereNotNull('bank_name')
            ->where('bank_name', '<>', '')
            ->distinct()
            ->orderBy('bank_name')
            ->pluck('bank_name')
            ->map(static fn(string $name): array => ['label' => $name, 'value' => $name])
            ->values()
            ->all();

        $platforms = [];
        if (self::hasPlatformColumn()) {
            $platforms = UserLogin::query()
                ->whereNotNull('platform')
                ->where('platform', '<>', '')
                ->distinct()
                ->orderBy('platform')
                ->pluck('platform')
                ->map(static fn(string $platform): array => ['label' => $platform, 'value' => $platform])
                ->values()
                ->all();
        }

        return [
            'payment_companies' => array_map(static fn(string $code, string $name): array => [
                'label' => $name,
                'value' => $code,
            ], array_keys(self::COMPANIES), array_values(self::COMPANIES)),
            'payment_methods' => $paymentMethods,
            'statuses' => array_map(static fn(int $value, string $label): array => [
                'label' => $label,
                'value' => $value,
            ], array_keys(self::STATUSES), array_values(self::STATUSES)),
            'platforms' => $platforms,
        ];
    }

    private static function platformSql(): string
    {
        $prefix = DB::getTablePrefix();
        $login = $prefix . 'user_login';
        $users = $prefix . 'users';
        $paymentOrders = $prefix . 'payment_orders';
        $statusCondition = self::hasLoginStatusColumn()
            ? 'AND ul.status = ' . (int)UserLogin::STATUS_SUCCESS
            : '';

        return "SELECT ul.platform
            FROM `{$login}` ul
            WHERE ul.user_id COLLATE utf8mb4_general_ci = `{$users}`.`user_id` COLLATE utf8mb4_general_ci
              {$statusCondition}
              AND ul.created_at <= `{$paymentOrders}`.`created_at`
            ORDER BY ul.id DESC
            LIMIT 1";
    }

    private static function hasPlatformColumn(): bool
    {
        if (self::$hasPlatform === null) {
            self::$hasPlatform = Schema::hasTable('user_login')
                && Schema::hasColumn('user_login', 'platform');
        }

        return self::$hasPlatform;
    }

    private static function hasLoginStatusColumn(): bool
    {
        if (self::$hasLoginStatus === null) {
            self::$hasLoginStatus = Schema::hasTable('user_login')
                && Schema::hasColumn('user_login', 'status');
        }

        return self::$hasLoginStatus;
    }

    private static function decodeJson($value)
    {
        if (is_array($value) || $value === null || $value === '') {
            return $value ?: null;
        }

        $decoded = json_decode((string)$value, true);
        return json_last_error() === JSON_ERROR_NONE ? $decoded : (string)$value;
    }

    private static function gatewayReference($callbackData, $payData, string $fallback): string
    {
        if ($fallback !== '') {
            return $fallback;
        }

        $keys = [
            'tradeNo', 'trade_no', 'transactionId', 'transaction_id', 'txid',
            'gatewayOrderNo', 'gateway_order_no', 'platformOrderNo', 'platform_order_no',
            'orderNo', 'order_no', 'orderId', 'order_id',
        ];

        foreach ([$callbackData, $payData] as $payload) {
            $reference = self::findValueByKeys($payload, $keys);
            if ($reference !== '') {
                return $reference;
            }
        }

        return '';
    }

    private static function findValueByKeys($payload, array $keys): string
    {
        if (!is_array($payload)) {
            return '';
        }

        foreach ($keys as $key) {
            if (isset($payload[$key]) && is_scalar($payload[$key]) && (string)$payload[$key] !== '') {
                return (string)$payload[$key];
            }
        }
        foreach ($payload as $value) {
            $reference = self::findValueByKeys($value, $keys);
            if ($reference !== '') {
                return $reference;
            }
        }

        return '';
    }

    private static function sanitizePayload($payload)
    {
        if (!is_array($payload)) {
            return $payload;
        }

        $sensitiveKeys = ['sign', 'signature', 'key', 'secret', 'password'];
        foreach ($payload as $key => $value) {
            if (in_array(strtolower((string)$key), $sensitiveKeys, true)) {
                unset($payload[$key]);
                continue;
            }
            if (is_array($value)) {
                $payload[$key] = self::sanitizePayload($value);
            }
        }

        return $payload;
    }

    private static function money($amount): string
    {
        return number_format((float)$amount, 2, '.', '');
    }

    private static function nullableMoney($amount): ?string
    {
        return $amount === null || $amount === '' ? null : self::money($amount);
    }

    private static function formatTime($value): ?string
    {
        if (empty($value)) {
            return null;
        }

        return Carbon::parse($value)
            ->setTimezone(config('app.timezone', 'Asia/Shanghai'))
            ->format('Y-m-d H:i:s');
    }

    /**
     * @return array{0: Carbon, 1: Carbon}
     */
    private static function dateRange(string $startDate, string $endDate): array
    {
        $timezone = config('app.timezone', 'Asia/Shanghai');

        return [
            Carbon::createFromFormat('Y-m-d', $startDate, $timezone)->startOfDay(),
            Carbon::createFromFormat('Y-m-d', $endDate, $timezone)->endOfDay(),
        ];
    }
}