CaiWuXiTong/modules/EcommerceFinance/Repositories/JdDailyBillRepository.php

<?php

namespace Modules\EcommerceFinance\Repositories;

use Illuminate\Database\DatabaseManager;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\Cache;
use Modules\EcommerceFinance\JdBillPlatform;
use Modules\EcommerceFinance\Services\LedgerRemarkFilter;
use RuntimeException;

class JdDailyBillRepository
{
    /** 平台口径（JdBillPlatform::platformForBillType）；null = 不做平台拆分，保持原行为。 */
    private ?string $platform = null;

    public function __construct(private readonly DatabaseManager $databases) {}

    /** 京东账单/京喜账单共用本仓库，用平台口径区分数据范围。 */
    public function forPlatform(?string $platform): static
    {
        $clone = clone $this;
        $clone->platform = $platform;

        return $clone;
    }

    public function paginate(array $filters, string $sort, string $direction, int $page, int $perPage): array
    {
        $periods = $this->periods();
        $query = $this->query($filters, $periods);
        $summary = Cache::remember($this->cacheKey('summary', $periods, $filters), 3600,
            fn () => (clone $query)->selectRaw('COUNT(*) AS count, COALESCE(SUM(income), 0) AS income, COALESCE(SUM(expense), 0) AS expense')->first());
        $total = (int) $summary->count;
        $page = max(1, $page);
        $perPage = min(100, max(1, $perPage));
        // Skip deep pages using narrow indexed keys, then load JSON only for the visible rows.
        $keys = $this->ordered($query, $sort, $direction)->forPage($page, $perPage)->get(['generation', 'record_code']);
        $rows = [];
        foreach ($keys->groupBy('generation') as $generation => $group) {
            $records = $this->db()->table('jd_daily_bill_rows')->where('generation', $generation)
                ->whereIn('record_code', $group->pluck('record_code')->all())->get(['record_code', 'row_data']);
            foreach ($records as $record) $rows[$generation . ':' . $record->record_code] = json_decode($record->row_data, true, 512, JSON_THROW_ON_ERROR);
        }
        return [
            'data' => $keys->map(fn ($key) => $rows[$key->generation . ':' . $key->record_code])->all(),
            'total' => $total, 'current_page' => $page, 'last_page' => max(1, (int) ceil($total / $perPage)), 'per_page' => $perPage,
            'summary' => ['count' => $total, 'income' => round((float) $summary->income, 2), 'expense' => round((float) $summary->expense, 2),
                'balance' => round((float) $summary->income - (float) $summary->expense, 2)],
            'sync' => ['status' => 'imported', 'source' => '胜算原始导出',
                'last_succeeded_at' => $periods->max('imported_at'),
                'periods' => $periods->map(fn ($p) => substr($p->period_start, 0, 7))->values()->all()],
        ];
    }

    public function exportRows(array $filters, string $sort, string $direction): iterable
    {
        $query = $this->ordered($this->query($filters, $this->periods()), $sort, $direction);
        return $this->stream($query);
    }

    private function stream(Builder $query): iterable
    {
        foreach ($query->select('row_data')->cursor() as $row) yield json_decode($row->row_data, true, 512, JSON_THROW_ON_ERROR);
    }

    public function filterOptions(): array
    {
        $periods = $this->periods();
        return Cache::remember($this->cacheKey('options', $periods), 3600, function () use ($periods): array {
            $q = $this->query([], $periods);
            return [
                'shops' => (clone $q)->distinct()->orderBy('store_name')->pluck('store_name')->all(),
                'bill_items' => (clone $q)->distinct()->orderBy('bill_item')->pluck('bill_item')->all(),
            ];
        });
    }

    /** Imported generations are immutable; replacing a month invalidates both caches. */
    private function cacheKey(string $scope, \Illuminate\Support\Collection $periods, array $filters = []): string
    {
        ksort($filters);
        return 'jd-daily-bill:' . $scope . ':v1:' . hash('sha256', json_encode([
            $this->db()->getDatabaseName(), $this->platform,
            $periods->pluck('active_generation')->sort()->values()->all(), $filters,
        ], JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR));
    }

    private function periods(): \Illuminate\Support\Collection
    {
        if (! $this->db()->getSchemaBuilder()->hasTable('jd_daily_bill_periods')) throw new RuntimeException('jd_daily_bill_mirror_not_ready');
        $periods = $this->db()->table('jd_daily_bill_periods')->orderBy('period_start')->get();
        if ($periods->isEmpty()) throw new RuntimeException('jd_daily_bill_mirror_not_ready');
        return $periods;
    }

    private function query(array $filters, \Illuminate\Support\Collection $periods): Builder
    {
        $q = $this->db()->table('jd_daily_bill_rows')->whereIn('generation', $periods->pluck('active_generation')->all());
        $this->applyPlatformScope($q);
        foreach (['store_name', 'bill_item'] as $field) {
            if (($filters[$field] ?? '') !== '') $q->where($field, $filters[$field]);
        }
        LedgerRemarkFilter::itemsWhereIn($q, (array) ($filters['bill_items'] ?? []), 'bill_item');
        if (($filters['start'] ?? '') !== '') $q->where('occurred_at', '>=', $filters['start'] . ' 00:00:00');
        if (($filters['end'] ?? '') !== '') $q->where('occurred_at', '<', date('Y-m-d', strtotime($filters['end'] . ' +1 day')) . ' 00:00:00');
        if (($filters['keyword'] ?? '') !== '') {
            $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $filters['keyword']) . '%';
            $q->where(fn (Builder $sub) => $sub->where('record_code', 'like', $like)->orWhere('order_no', 'like', $like)->orWhere('row_data', 'like', $like));
        }
        return $q;
    }

    /** 按平台口径收窄数据；两个口径互补，笔数与金额之和恒等于拆分前。 */
    private function applyPlatformScope(Builder $query): void
    {
        $scope = JdBillPlatform::storeNameScope($this->platform);
        if ($scope === null) {
            return;
        }

        $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $scope['keyword']) . '%';
        $start = $scope['start_date'];

        if ($scope['mode'] === 'only') {
            $query->where('store_name', 'like', $like);
            if ($start !== null) $query->where('occurred_at', '>=', $start . ' 00:00:00');

            return;
        }

        // 起始日期之前取到的京喜行仍留在京东账单，保证两边相加等于拆分前。
        $query->where(function (Builder $sub) use ($like, $start): void {
            $sub->where('store_name', 'not like', $like);
            if ($start !== null) $sub->orWhere('occurred_at', '<', $start . ' 00:00:00');
        });
    }

    private function ordered(Builder $q, string $sort, string $direction): Builder
    {
        $sort = in_array($sort, ['occurred_at', 'record_code', 'income', 'expense'], true) ? $sort : 'occurred_at';
        $direction = $direction === 'asc' ? 'asc' : 'desc';
        return $q->orderBy($sort, $direction)->orderBy('record_code', $direction);
    }

    private function db(): \Illuminate\Database\Connection
    {
        return $this->databases->connection(config('upstream_mirrors.connection', 'business_mirror'));
    }
}