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

<?php

namespace App\Services\Reporting;

use App\Services\Sharing\BetFactSynchronizer;
use App\Services\Sharing\EffectiveBet as Money;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/** Reporting projections only. Never settles orders, moves money or publishes sharing facts. */
class ReportSync
{
    public const ORDER_SOURCES = ['lhc_order', 'sport_game_order', 'sport_order', 'third_game_orders', 'bets', 'jisu_game_order'];
    public const MONEY_SOURCES = ['balance_logs', 'withdraws', 'payment_orders', 'agent_withdrawals', 'agent_balance_logs', 'share_ledger', 'agent_rebate_records'];

    public function sync(bool $full = false): array
    {
        $result = ['scanned' => 0, 'published' => 0, 'errors' => 0, 'sources' => []];
        foreach (array_merge(self::ORDER_SOURCES, self::MONEY_SOURCES, ['lhc_order_item']) as $source) {
            if (!Schema::hasTable($source)) { $result['sources'][$source] = 'absent'; continue; }
            $start = now('Asia/Shanghai')->format('Y-m-d H:i:s');
            $state = DB::table('reporting_sync_state')->where('source', $source)->first();
            $cursor = $state ? json_decode($state->state, true, 512, JSON_THROW_ON_ERROR) : null;
            $query = DB::table($source); $hasUpdate = Schema::hasColumn($source, 'updated_at');
            $column = $source === 'sport_order' ? 'update_time' : 'updated_at';
            if (!$full && $cursor && ($hasUpdate || $source === 'sport_order')) {
                $cut = Carbon::parse($cursor['checked_at'], 'Asia/Shanghai')->subMinutes(5);
                $value = in_array($source, ['lhc_order', 'lhc_order_item', 'sport_order'], true) ? $cut->timestamp : $cut->format('Y-m-d H:i:s');
                $query->where(function ($q) use ($column, $value, $cursor) { $q->where($column, '>=', $value)->orWhereNull($column)->orWhere('id', '>', $cursor['last_id']); });
            } elseif (!$full && $cursor && in_array($source, ['share_ledger', 'agent_balance_logs'], true)) $query->where('id', '>', $cursor['last_id']);
            $max = (int) DB::table($source)->max('id'); $errors = 0;
            $query->where('id', '<=', $max)->orderBy('id')->chunkById(200, function ($rows) use ($source, &$result, &$errors) {
                foreach ($rows as $row) {
                    ++$result['scanned'];
                    try {
                        if ($source === 'lhc_order_item') {
                            $parents = DB::table('lhc_order')->where('ordernum', $row->ordernum)->limit(2)->get();
                            if ($parents->count() !== 1) throw new \RuntimeException('六合彩子单对应的父单不明确');
                            $this->publish('lhc_order', (int) $parents[0]->id);
                            DB::table('reporting_errors')->where('source', $source)->where('source_id', (string) $row->id)->delete();
                        } else $this->publish($source, (int) $row->id);
                        ++$result['published'];
                    }
                    catch (\Throwable $e) {
                        ++$errors; ++$result['errors'];
                        DB::table('reporting_errors')->updateOrInsert(['source' => $source, 'source_id' => (string) $row->id],
                            ['candidate_at' => $this->time($row->created_at ?? $row->create_time ?? null), 'reason' => mb_substr($e->getMessage(), 0, 500), 'created_at' => now(), 'updated_at' => now()]);
                    }
                }
            });
            if (!$errors) DB::table('reporting_sync_state')->updateOrInsert(['source' => $source], ['state' => json_encode(['checked_at' => $start, 'last_id' => $max]), 'updated_at' => $start]);
            $result['sources'][$source] = $errors ? 'errors' : 'ready';
        }
        DB::table('reporting_sync_state')->updateOrInsert(['source' => '__run__'], ['state' => json_encode(['complete' => $result['errors'] === 0, 'sources' => $result['sources']]), 'updated_at' => now()]);
        return $result;
    }

    public function publish(string $source, int $id): void
    {
        if (!in_array($source, array_merge(self::ORDER_SOURCES, self::MONEY_SOURCES), true)) throw new \InvalidArgumentException('不支持的数据来源');
        DB::transaction(function () use ($source, $id) {
            $record = DB::table($source)->where('id', $id)->lockForUpdate()->first();
            if (!$record) throw new \RuntimeException('原记录不存在');
            $row = (array) $record;
            $member = $row['member_id'] ?? null;
            $virtual = $member !== null && DB::table('users')->where('member_id', (string) $member)->where('from', 2)->exists();
            if ($virtual) {
                DB::table('reporting_orders')->where('parent_source', $source)->where('parent_id', (string) $id)->delete();
                DB::table('reporting_events')->where('source', $source)->where('source_id', (string) $id)->delete();
                DB::table('reporting_errors')->where('source', $source)->where('source_id', (string) $id)->delete(); return;
            }
            if (in_array($source, self::ORDER_SOURCES, true)) $this->orders($source, $row);
            else $this->money($source, $row);
            DB::table('reporting_errors')->where('source', $source)->where('source_id', (string) $id)->delete();
        });
    }

    private function orders(string $source, array $row): void
    {
        $at = $this->time($row['created_at'] ?? $row['create_time'] ?? null);
        if ((int) ($row['is_faker'] ?? 0) === 1) { DB::table('reporting_orders')->where('parent_source', $source)->where('parent_id', (string) $row['id'])->delete(); return; }
        $facts = [];
        if (in_array($source, ['bets', 'jisu_game_order'], true)) {
            $identity = ReportContext::identity($row, $at ?? now()->toDateTimeString(), $source);
            if ($identity['user_id'] === null || $at === null) throw new \RuntimeException('旧订单缺少会员或下单时间');
            $bet = Money::decimal($row['amount']);
            $settled = $source === 'bets' ? (int) $row['status'] === 2 : in_array((int) $row['status'], [1, 2], true);
            $void = (int) $row['status'] === 3;
            $payout = $settled ? Money::decimal($source === 'bets' ? ($row['profit'] ?? 0) : ($row['win_amount'] ?? 0)) : Money::zero();
            $facts[] = ['source' => $source, 'source_id' => (string) $row['id'], 'user_id' => $identity['user_id'],
                'category' => 'self:lottery', 'bet_at' => $at, 'bet_amount' => $bet, 'settled' => $settled, 'voided' => $void,
                'net_profit' => $settled ? bcsub($payout, $bet, 10) : Money::zero(), 'currency' => $this->currency()];
        } else $facts = (new BetFactSynchronizer())->projectForReporting($source, (object) $row);
        $keys = [];
        foreach ($facts as $fact) {
            $identity = ReportContext::identity($row + ['report_user_id' => $fact['user_id']], $fact['bet_at'], $source);
            $context = DB::table('reporting_contexts')->where('source', $source)->where('source_id', (string) $row['id'])->first();
            $settledAt = $context->succeeded_at ?? null;
            if (!(bool) ($context->settlement_verified ?? false)) {
                if ($source === 'third_game_orders') $settledAt = $this->time($row['last_update_time'] ?? null);
                if ($source === 'sport_order') $settledAt = $this->time($row['settlement_time'] ?? null) ?? $this->time($row['return_operation_time'] ?? null) ?? $settledAt;
            }
            $settledAt = $fact['settled'] || $fact['voided'] ? $settledAt : null;
            $payout = $fact['settled'] ? bcadd($fact['bet_amount'], $fact['net_profit'], 10) : Money::zero();
            $refund = $fact['voided'] ? $fact['bet_amount'] : Money::zero();
            if ($source === 'sport_order' && $fact['settled']) {
                $refund = Money::decimal($row['return_amount'] ?? 0);
                if (bccomp($refund, '0', 10) < 0 || bccomp($refund, $fact['bet_amount'], 10) > 0) throw new \RuntimeException('部分退款金额不正确');
                $payout = bcsub($payout, $refund, 10);
            }
            if (bccomp($payout, '0', 10) < 0) throw new \RuntimeException('派奖金额不能为负');
            $key = ['source' => $fact['source'], 'source_id' => $fact['source_id']]; $keys[] = $fact['source_id'];
            $values = $identity + ['parent_source' => $source, 'parent_id' => (string) $row['id'], 'category' => $fact['category'],
                'currency' => $source === 'third_game_orders' ? $fact['currency'] : $this->currency(), 'bet_at' => $fact['bet_at'], 'settled_at' => $settledAt,
                'settlement_candidate_at' => $this->time($row['updated_at'] ?? $row['update_time'] ?? null),
                'bet_amount' => $fact['bet_amount'], 'payout_amount' => $payout, 'net_profit' => $fact['net_profit'],
                'refund_amount' => $refund, 'settled' => (int) $fact['settled'], 'voided' => (int) $fact['voided'],
                'time_quality' => (!$fact['settled'] && !$fact['voided']) || $settledAt !== null ? 'confirmed' : 'missing_settlement_time'];
            $old = DB::table('reporting_orders')->where($key)->first();
            if (!$old) $values['created_at'] = now(); $values['updated_at'] = now();
            DB::table('reporting_orders')->updateOrInsert($key, $values);
        }
        DB::table('reporting_orders')->where('parent_source', $source)->where('parent_id', (string) $row['id'])->when($keys, fn ($q) => $q->whereNotIn('source_id', $keys))->delete();
    }

    private function money(string $source, array $row): void
    {
        $candidate = $this->time($row['created_at'] ?? null);
        if ($candidate === null) throw new \RuntimeException('资金记录缺少创建时间');
        $context = DB::table('reporting_contexts')->where('source', $source)->where('source_id', (string) $row['id'])->first();
        $at = $context->succeeded_at ?? null; $kind = null; $amount = null; $customerFee = Money::zero(); $configuredFee = null;
        $category = null; $identityRow = $row;
        if ($source === 'balance_logs') {
            $amount = Money::decimal($row['amount']); $type = (string) $row['change_type'];
            if (bccomp($amount, '0', 10) > 0 && in_array($type, ['充值', '三方充值'], true)) $kind = 'deposit';
            elseif (bccomp($amount, '0', 10) > 0 && in_array($type, ['注册赠送', '优惠活动', '即充即送', '充值返现', '老用户回归', '比比返', '笔笔返', '返水', '回水', '余额宝利息', '流水解冻'], true) && (int) ($row['type'] ?? 1) === 1) $kind = 'bonus';
            elseif ($type === '人工充值') $kind = isset($row['related_id']) && (string) $row['related_id'] === '0' ? 'bonus' : 'manual_credit';
            elseif ($type === '人工扣款') { $kind = 'manual_debit'; $amount = bcsub('0', $amount, 10); }
            elseif ($type === '分享佣金领取') {
                $claim = DB::table('share_claims')->where('credited_log_id', $row['id'])->where('status', 'approved')->first();
                if (!$claim) throw new \RuntimeException('分享领取缺少审核通过的原记录');
                $kind = 'share_commission';
            }
            $at = $candidate;
            if ($kind === 'deposit' && ($row['related_id'] ?? null)) {
                $origin = $type === '三方充值' ? 'payment_orders' : 'recharges';
                $originContext = DB::table('reporting_contexts')->where('source', $origin)->where('source_id', (string) $row['related_id'])->first();
                if ($originContext && !$context) $context = $originContext;
                if ($origin === 'payment_orders' && Schema::hasTable($origin)) {
                    $original = DB::table($origin)->where('id', $row['related_id'])->where('member_id', $row['member_id'])->whereIn('type', [1, 3])->where('status', 2)->first();
                    if ($original) $configuredFee = $original->fee ?? null;
                }
            }
        } elseif ($source === 'withdraws' && (int) $row['status'] === 1) {
            $kind = 'withdraw'; $rate = Money::decimal($row['exchange_rate']);
            if (bccomp($rate, '0', 10) <= 0) throw new \RuntimeException('提现历史汇率无效');
            $amount = isset($row['to_account']) && is_numeric($row['to_account']) ? bcmul((string) $row['to_account'], $rate, 10) : null;
            $customerFee = bcmul(Money::decimal($row['service_charge'] ?? 0), $rate, 10);
        } elseif ($source === 'payment_orders' && (int) $row['status'] === 2 && in_array((int) $row['type'], [2, 4], true)) {
            $kind = 'withdraw'; $amount = (int) $row['type'] === 2 ? Money::decimal($row['amount']) : null; $configuredFee = $row['fee'] ?? null;
        } elseif ($source === 'agent_withdrawals' && $row['status'] === 'paid') {
            $kind = 'agent_withdraw'; $amount = Money::decimal($row['actual_amount']); $customerFee = Money::decimal($row['fee'] ?? 0); $at = $this->time($row['paid_at'] ?? null);
        } elseif ($source === 'agent_balance_logs' && $row['type'] === 'commission' && $row['reference_type'] === 'agent_rebate_record') {
            $kind = 'agent_commission'; $amount = Money::decimal($row['amount']); $at = $candidate;
            $record = DB::table('agent_rebate_records')->where('id', $row['reference_id'])->first();
            if ($record) $category = $this->agentCategory((string) $record->platform, (int) $record->game_type);
        } elseif ($source === 'share_ledger') {
            $kind = 'share_accrual'; $amount = Money::decimal($row['amount']); $at = $row['bet_date'] . ' 00:00:00'; $category = $row['category'];
            $identityRow['report_user_id'] = $row['user_id'];
        } elseif ($source === 'agent_rebate_records') {
            $kind = 'agent_accrual'; $amount = Money::zero(); $at = $row['settlement_date'] . ' 00:00:00';
            foreach (['flow', 'profit'] as $part) $amount = bcadd($amount, (string) $row[$part . '_commission'], 10);
            $category = $this->agentCategory((string) $row['platform'], (int) $row['game_type']);
        }
        if (!$kind) {
            $existing = DB::table('reporting_events')->where('source', $source)->where('source_id', (string) $row['id'])->first();
            $verified = $existing ? DB::table('reporting_reconciliations')->where('event_id', $existing->id)->first() : null;
            if ($verified) {
                if (bccomp($verified->actual_amount, '0', 10) !== 0) throw new \RuntimeException('来源状态与已核实的收付款冲突，请重新核对');
                return; // Confirmed zero payout may still have an actual external fee; preserve its audit and event identity.
            }
            DB::table('reporting_events')->where('source', $source)->where('source_id', (string) $row['id'])->delete(); return;
        }
        $identity = ReportContext::identity($identityRow, $at ?? $candidate, $source);
        if ($context) $identity = ['user_id' => $context->user_id, 'agent_id' => $context->agent_id, 'inviter_id' => $context->inviter_id,
            'client_platform' => $context->client_platform, 'attribution_quality' => $context->attribution_quality];
        if (in_array($source, ['agent_withdrawals', 'agent_balance_logs', 'agent_rebate_records'], true)) $identity['agent_id'] = (int) $row['agent_id'];
        if ($identity['user_id'] === null && $identity['agent_id'] === null) throw new \RuntimeException('资金记录没有可识别的归属');
        $key = ['source' => $source, 'source_id' => (string) $row['id'], 'kind' => $kind];
        $old = DB::table('reporting_events')->where($key)->first();
        $values = $identity + ['category' => $category, 'currency' => $this->currency(), 'amount' => $amount,
            'customer_fee' => $customerFee, 'configured_fee' => $configuredFee, 'occurred_at' => $at, 'candidate_at' => $candidate, 'updated_at' => now()];
        if (!$old) $values['created_at'] = now();
        DB::table('reporting_events')->updateOrInsert($key, $values);
    }

    private function agentCategory(string $platform, int $type): ?string
    {
        if ($platform === 'sport') return 'self:sport';
        if (in_array($platform, ['lhc', 'jisu', 'pc28'], true)) return 'self:lottery';
        return $type >= 1 && $type <= 7 && $platform !== 'legacy' ? 'third:' . $type : null;
    }
    public function currency(): string { return strtoupper((string) config('reporting.currency', 'CNY')); }
    public function time($value): ?string
    {
        if ($value === null || $value === '' || $value === 0 || $value === '0') return null;
        try { return Money::time($value)->setTimezone('Asia/Shanghai')->format('Y-m-d H:i:s'); } catch (\Throwable $e) { return null; }
    }
}