CaiWuXiTong/modules/EcommerceFinance/Repositories/JushuitanShopBalanceRepository.php

<?php

namespace Modules\EcommerceFinance\Repositories;

use Carbon\CarbonInterface;
use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\DatabaseManager;
use Illuminate\Support\Facades\Schema;

class JushuitanShopBalanceRepository
{
    public const TABLE = 'jushuitan_shop_balance_snapshots';
    public const DATA_SOURCE_JUSHUITAN = 'jushuitan';
    public const DATA_SOURCE_FINANCE = 'finance';
    public const SOURCE_SCOPE_STORE_LEDGER = 'store-ledger';

    private readonly ConnectionInterface $connection;

    public function __construct(DatabaseManager $database)
    {
        $this->connection = $database->connection(
            (string) config('upstream_mirrors.connection', 'business_mirror'),
        );
    }

    /**
     * @param iterable<object> $stores
     * @return array<int,array{status:string,balance:?float,source_store_name:?string,source_platform:?string,data_source:?string,source_file_name:?string}>
     */
    public function matchesForStores(
        iterable $stores,
        string $balanceDate,
        ?string $dataSource = null,
        ?string $sourceScope = null,
    ): array
    {
        $matches = $this->matchesForStoresOnDates($stores, [$balanceDate], $dataSource, $sourceScope);

        return array_map(
            static fn (array $dates): array => $dates[$balanceDate],
            $matches,
        );
    }

    /**
     * @param iterable<object> $stores
     * @param list<string> $balanceDates
     * @return array<int,array<string,array{status:string,balance:?float,source_store_name:?string,source_platform:?string,data_source:?string,source_file_name:?string}>>
     */
    public function matchesForStoresOnDates(
        iterable $stores,
        array $balanceDates,
        ?string $dataSource = null,
        ?string $sourceScope = null,
    ): array
    {
        $stores = is_array($stores) ? array_values($stores) : iterator_to_array($stores, false);
        $balanceDates = array_values(array_unique(array_filter(
            $balanceDates,
            static fn (mixed $date): bool => is_string($date) && $date !== '',
        )));
        $result = [];

        foreach ($stores as $store) {
            $result[(int) $store->id] = array_fill_keys(
                $balanceDates,
                $this->emptyMatch('not_ready'),
            );
        }

        if ($balanceDates === [] || ! $this->hasTable()) {
            return $result;
        }

        $sourceRowsByDate = $this->connection->table(self::TABLE)
            ->whereIn('balance_date', $balanceDates)
            ->when(
                $dataSource !== null,
                static fn ($query) => $query->where('data_source', $dataSource),
            )
            ->when(
                $sourceScope !== null,
                static fn ($query) => $query->where('source_scope', $sourceScope),
                static fn ($query) => $query->where('source_scope', '<>', self::SOURCE_SCOPE_STORE_LEDGER),
            )
            ->orderBy('id')
            ->get([
                'id', 'balance_date', 'data_source', 'source_platform', 'store_name',
                'normalized_store_name', 'platform_account_id', 'balance', 'source_file_name',
            ])
            ->groupBy(static fn (object $row): string => substr((string) $row->balance_date, 0, 10));

        if ($sourceRowsByDate->isEmpty()) {
            return $result;
        }

        foreach ($stores as $store) {
            $names = array_values(array_unique(array_filter([
                self::normalizeStoreName((string) ($store->shop_name ?? '')),
                self::normalizeStoreName((string) ($store->erp_store_name ?? $store->shop_name ?? '')),
            ])));

            foreach ($balanceDates as $balanceDate) {
                $matches = ($sourceRowsByDate[$balanceDate] ?? collect())->filter(
                    static fn (object $row): bool => in_array((string) $row->normalized_store_name, $names, true),
                )->values();

                if ($matches->isEmpty()) {
                    $result[(int) $store->id][$balanceDate] = $this->emptyMatch('unmatched');
                    continue;
                }

                $jushuitanMatches = $matches->filter(
                    static fn (object $row): bool => (string) $row->data_source === self::DATA_SOURCE_JUSHUITAN,
                )->values();
                if ($jushuitanMatches->isNotEmpty()) {
                    $matches = $jushuitanMatches;
                }

                $targetPlatform = self::platformKey((string) ($store->store_platform ?? $store->platform ?? ''));
                if ($targetPlatform !== null) {
                    $matches = $matches->filter(
                        static function (object $row) use ($targetPlatform): bool {
                            $sourcePlatform = self::platformKey((string) ($row->source_platform ?? ''))
                                ?? self::platformKey((string) self::sourcePlatform((string) ($row->store_name ?? '')));

                            return $sourcePlatform !== null && $sourcePlatform === $targetPlatform;
                        },
                    )->values();

                    if ($matches->isEmpty()) {
                        $result[(int) $store->id][$balanceDate] = $this->emptyMatch('unmatched');
                        continue;
                    }
                }

                if ($matches->count() !== 1) {
                    $result[(int) $store->id][$balanceDate] = $this->emptyMatch('ambiguous');
                    continue;
                }

                $match = $matches->first();
                $result[(int) $store->id][$balanceDate] = [
                    'status' => 'matched',
                    'balance' => (float) $match->balance,
                    'source_store_name' => (string) $match->store_name,
                    'source_platform' => $match->source_platform === null ? null : (string) $match->source_platform,
                    'data_source' => (string) $match->data_source,
                    'source_file_name' => $match->source_file_name === null ? null : (string) $match->source_file_name,
                ];
            }
        }

        return $result;
    }

    public function countForDate(string $balanceDate, ?string $sourceScope = null): int
    {
        if (! $this->hasTable()) {
            return 0;
        }

        return $this->connection->table(self::TABLE)
            ->where('balance_date', $balanceDate)
            ->when(
                $sourceScope !== null,
                static fn ($query) => $query->where('source_scope', $sourceScope),
                static fn ($query) => $query->where('source_scope', '<>', self::SOURCE_SCOPE_STORE_LEDGER),
            )
            ->count();
    }

    /**
     * 取不晚于今天的最新余额日期；没有任何快照时返回 null。
     */
    public function latestBalanceDate(?CarbonInterface $today = null, ?string $sourceScope = null): ?string
    {
        if (! $this->hasTable()) {
            return null;
        }

        $today = ($today ?? now())->copy()->startOfDay();
        $latest = $this->connection->table(self::TABLE)
            ->where('balance_date', '<=', $today->toDateString())
            ->when(
                $sourceScope !== null,
                static fn ($query) => $query->where('source_scope', $sourceScope),
                static fn ($query) => $query->where('source_scope', '<>', self::SOURCE_SCOPE_STORE_LEDGER),
            )
            ->max('balance_date');

        return $latest === null ? null : substr((string) $latest, 0, 10);
    }

    /**
     * 取最早余额日期，供页面日期筛选下限使用；没有快照时返回 null。
     */
    public function earliestBalanceDate(): ?string
    {
        if (! $this->hasTable()) {
            return null;
        }

        $earliest = $this->connection->table(self::TABLE)->min('balance_date');

        return $earliest === null ? null : substr((string) $earliest, 0, 10);
    }

    /**
     * 取所有有余额快照的日期（倒序），供页面日期下拉使用。
     *
     * @return list<string>
     */
    public function availableDates(?CarbonInterface $today = null, ?string $sourceScope = null): array
    {
        if (! $this->hasTable()) {
            return [];
        }

        $today = ($today ?? now())->copy()->startOfDay();

        return $this->connection->table(self::TABLE)
            ->where('balance_date', '<=', $today->toDateString())
            ->when(
                $sourceScope !== null,
                static fn ($query) => $query->where('source_scope', $sourceScope),
                static fn ($query) => $query->where('source_scope', '<>', self::SOURCE_SCOPE_STORE_LEDGER),
            )
            ->distinct()
            ->orderByDesc('balance_date')
            ->pluck('balance_date')
            ->map(static fn (mixed $date): string => substr((string) $date, 0, 10))
            ->values()
            ->all();
    }

    public static function normalizeStoreName(string $value): string
    {
        $value = trim(str_replace(['－', '—', '–'], '-', $value));
        $value = preg_replace(
            '/^(支付宝|天猫|淘宝|阿里巴巴|1688|京东商城|京东|京喜小店|京喜|拼多多|抖音|快手|小红书|唯品会|得物|淘工厂)[\s\-_:：]+/u',
            '',
            $value,
        ) ?? $value;
        $value = preg_replace('/\s+/u', '', $value) ?? $value;

        return mb_substr(mb_strtolower($value), 0, 255);
    }

    public static function sourcePlatform(string $storeName): ?string
    {
        if (preg_match(
            '/^(支付宝|天猫|淘宝|阿里巴巴|1688|京东商城|京东|京喜小店|京喜|拼多多|抖音|快手|小红书|唯品会|得物|淘工厂)[\s\-—–_:：]+/u',
            trim($storeName),
            $matches,
        ) === 1) {
            return $matches[1];
        }

        return str_ends_with(mb_strtolower(trim($storeName)), '1688') ? '1688' : null;
    }

    public static function platformKey(string $platform): ?string
    {
        $platform = mb_strtolower(trim($platform));
        foreach ([
            'tmall' => ['天猫'],
            'taobao' => ['淘宝'],
            '1688' => ['1688', '阿里巴巴'],
            'jd' => ['京东'],
            'jingxi' => ['京喜'],
            'pinduoduo' => ['拼多多'],
            'douyin' => ['抖音', '头条放心购'],
            'kuaishou' => ['快手'],
            'xiaohongshu' => ['小红书'],
            'vipshop' => ['唯品会'],
            'dewu' => ['得物'],
            'taogongchang' => ['淘工厂'],
        ] as $key => $aliases) {
            foreach ($aliases as $alias) {
                if (str_contains($platform, mb_strtolower($alias))) {
                    return $key;
                }
            }
        }

        return null;
    }

    /** @return array{status:string,balance:null,source_store_name:null,source_platform:null,data_source:null,source_file_name:null} */
    private function emptyMatch(string $status): array
    {
        return [
            'status' => $status,
            'balance' => null,
            'source_store_name' => null,
            'source_platform' => null,
            'data_source' => null,
            'source_file_name' => null,
        ];
    }

    private function hasTable(): bool
    {
        return Schema::connection($this->connection->getName())->hasTable(self::TABLE);
    }

}