<?php

namespace Modules\EcommerceFinance\Repositories;

use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Carbon;
use Modules\EcommerceFinance\Services\BusinessMirrorDatasetRegistry;
use Throwable;

class OrderDataMirrorRepository
{
    /** 完整店铺候选的缓存键（页面店铺多选使用）。 */
    public const SHOP_NAMES_CACHE_KEY = 'order-data.shop-candidates:v2';

    public const SHOP_NAMES_CURSORS_KEY = 'order-data.shop-candidates:cursors:v1';

    /** 订单号搜索一次最多解析出多少候选订单，超过就退回模糊匹配。 */
    private const ORDER_NUMBER_CANDIDATE_LIMIT = 500;

    /** 批量筛选一次最多接受多少个值（订单号 / 商品编码）。 */
    private const BATCH_FILTER_LIMIT = 50;

    private const DATASETS = [
        'operations_replica.rpt.jst_Orders',
        'operations_replica.rpt.jst_OrderItem',
        'operations_replica.rpt.jst_sku',
        'operations_replica.rpt.jst_warehouseInfo',
        'operations_replica.rpt.jst_returnAndRefundInfo',
        'operations_replica.rpt.jst_returnAndRefundItem',
    ];

    private const PROMOTION_DATASETS = [
        'operations_replica.rpt.ads_itemid_sales_cost_ratio_v2_di',
    ];

    private readonly ConnectionInterface $connection;

    public function __construct(
        private readonly BusinessMirrorDatasetRegistry $datasets,
        private readonly GenericBusinessMirrorRepository $mirror,
    ) {
        $this->connection = $mirror->connection();
    }

    public function ready(): bool
    {
        return $this->datasetsReady(self::DATASETS);
    }

    /**
     * 订单镜像里出现过的店铺名称，按名称排序；供页面上的店铺多选候选使用。
     *
     * @return array<int,string>
     */
    public function shopNames(): array
    {
        if (is_array($cached = Cache::get(self::SHOP_NAMES_CACHE_KEY))) {
            return array_values($cached);
        }

        // 完整候选要扫订单宽表（shop_name 是 LONGTEXT，非淘系约 30 秒、淘系约
        // 2 分钟）。mod_php 下"响应结束后再算"并不会让浏览器提前结束请求，
        // 页面会一直转圈，所以这里只返回毫秒级的店铺目录，完整列表交给
        // order-data:warm-shop-candidates 定时预热。
        return $this->shopDirectoryNames();
    }

    /**
     * 重新计算并缓存完整店铺候选（店铺目录 ∪ 订单镜像里出现过的店铺）。
     *
     * 按镜像同步时间增量补充；夜间 --full 对账移除已不存在的候选。
     *
     * @return array<int,string>
     */
    public function refreshShopNamesCache(bool $full = false): array
    {
        $cached = Cache::get(self::SHOP_NAMES_CACHE_KEY);
        $incremental = ! $full && is_array($cached);
        $cursors = $incremental ? (array) Cache::get(self::SHOP_NAMES_CURSORS_KEY, []) : [];
        [$changed, $nextCursors] = $this->computedShopNames($incremental, $cursors);
        $names = array_values(array_unique(array_merge(
            $incremental ? $cached : [],
            $changed,
            $this->shopDirectoryNames(),
        )));
        sort($names, SORT_STRING);

        // Empty is also a valid full reconciliation result.
        Cache::put(self::SHOP_NAMES_CACHE_KEY, $names, now()->addHours(24));
        Cache::put(self::SHOP_NAMES_CURSORS_KEY, $nextCursors, now()->addHours(24));

        return $names;
    }

    /** 聚水潭店铺目录（约 1300 行）：毫秒级返回，作为冷缓存时的候选兜底。 */
    private function shopDirectoryNames(): array
    {
        try {
            $rows = $this->connection->table('ops_jst_shop_info')
                ->select('shop_name')
                ->distinct()
                ->whereNotNull('shop_name')
                ->where('shop_name', '<>', '')
                ->pluck('shop_name');
        } catch (Throwable $exception) {
            report($exception);

            return [];
        }

        return $this->normalizeShopNames($rows);
    }

    /**
     * 商品推广费用明细的店铺候选：指定统计日期出现过的店铺。
     *
     * 推广表的店铺名带下划线（"天猫_卓禾家具旗舰店"），和订单镜像的"天猫-…"不是同一套
     * 命名，不能复用订单店铺候选；按日期过滤后有 400 行左右，结果缓存 24 小时。
     *
     * @return array<int,string>
     */
    public function promotionShopNames(string $date): array
    {
        $date = trim($date);
        if ($date === '') {
            return [];
        }

        $key = 'order-data.promotion.shop-options:' . $date;
        if (is_array($cached = Cache::get($key))) {
            return array_values($cached);
        }

        try {
            $names = $this->connection->table('ops_item_sales_cost_ratio')
                ->select('shop_name')
                ->distinct()
                ->where('stat_date', '>=', $date . ' 00:00:00')
                ->where('stat_date', '<', date('Y-m-d 00:00:00', strtotime($date . ' +1 day')))
                ->whereNotNull('shop_name')
                ->where('shop_name', '<>', '')
                ->orderBy('shop_name')
                ->pluck('shop_name');
        } catch (Throwable $exception) {
            report($exception);

            return [];
        }

        $names = $this->normalizeShopNames($names);
        if ($names !== []) {
            Cache::put($key, $names, now()->addHours(24));
        }

        return $names;
    }

    /**
     * 店铺筛选支持多选（shop_name[]=A&shop_name[]=B），也兼容原来的单个 shop_name。
     *
     * @return list<string>
     */
    private function selectedShopNames(array $filters): array
    {
        $values = $filters['shop_names'] ?? [];
        if (! is_array($values) || $values === []) {
            $values = ($filters['shop_name'] ?? '') !== '' ? [$filters['shop_name']] : [];
        }

        $names = [];
        foreach ($values as $value) {
            if (! is_scalar($value)) {
                continue;
            }

            $name = trim((string) $value);
            if ($name !== '') {
                $names[$name] = true;
            }

            // 与页面上的多选上限一致，避免拼出过长的 WHERE。
            if (count($names) >= 50) {
                break;
            }
        }

        return array_keys($names);
    }

    /**
     * 非淘系 + 淘系两个来源合并去重（淘系店铺名形如"天猫-xx/淘宝-xx"）。
     *
     * @param array<string,string|null> $cursors
     * @return array{0:list<string>,1:array<string,string|null>}
     */
    private function computedShopNames(bool $incremental, array $cursors): array
    {
        $names = [];
        $nextCursors = [];

        foreach (['ops_jst_orders', 'ops_jst_qmcust_orders'] as $table) {
            $hasTimestamp = $this->connection->getSchemaBuilder()->hasColumn($table, '_mirror_synced_at');
            $high = $hasTimestamp ? $this->connection->table($table)->max('_mirror_synced_at') : null;
            $since = $cursors[$table] ?? null;
            if ($incremental && $since === null && $high !== null) {
                // Upgrade the existing 24-hour complete cache without another full scan.
                $since = Carbon::parse($high)->subDay()->format('Y-m-d H:i:s');
            }
            $query = $this->connection->table($table)
                ->select('shop_name')
                ->distinct()
                ->whereNotNull('shop_name')
                ->where('shop_name', '<>', '');
            if ($incremental && $hasTimestamp && $since !== null && $high !== null) {
                // Inclusive lower bound retains writes sharing the same timestamp.
                $query->where('_mirror_synced_at', '>=', $since)
                    ->where('_mirror_synced_at', '<=', $high);
            }
            $rows = $query->pluck('shop_name');
            $nextCursors[$table] = $high;

            foreach ($this->normalizeShopNames($rows) as $name) {
                $names[$name] = true;
            }
        }

        $names = array_keys($names);
        sort($names, SORT_STRING);

        return [$names, $nextCursors];
    }

    /** @param iterable<mixed> $rows @return array<int,string> */
    private function normalizeShopNames(iterable $rows): array
    {
        $names = [];

        foreach ($rows as $name) {
            $name = trim((string) $name);
            if ($name !== '') {
                $names[$name] = true;
            }
        }

        $names = array_keys($names);
        sort($names, SORT_STRING);

        return $names;
    }

    public function promotionReady(): bool
    {
        return $this->datasetsReady(self::PROMOTION_DATASETS);
    }

    private function datasetsReady(array $datasets): bool
    {
        try {
            foreach ($datasets as $dataset) {
                if (! $this->mirror->ready($this->datasets->get($dataset))) {
                    return false;
                }
            }
            return true;
        } catch (\Throwable) {
            return false;
        }
    }

    /** @return array{rows:array<int,array<string,mixed>>} */
    public function afterSales(array $filters, int $page, int $limit): array
    {
        $limit = min(max($limit, 1), 100);
        // 两套来源各取够当前页需要的行数再合并，避免把上千万行整体物化后再排序。
        $perBranch = ($page * $limit) + 1;
        $rows = $this->baseQuery($filters, $perBranch)
            // 跨表追加 i.oi_id 会让 MariaDB 对日期范围内全部订单建立临时表
            // 再 filesort；o_id 已唯一确定订单，明细通过 (o_id, Id) 索引稳定读取。
            ->orderByDesc('order_time')->orderByDesc('internal_order_no')
            ->offset((max($page, 1) - 1) * $limit)
            ->limit($limit + 1)
            ->get()->map(static fn (object $row): array => (array) $row)->all();

        $this->hydrateSkus($rows);
        $rows = array_map(fn (array $row): array => $this->baseRow($row), $rows);
        $this->hydrateRefunds($rows);

        return ['rows' => $rows];
    }

    /** @return array{rows:array<int,array<string,mixed>>,date:?string} */
    public function promotionExpenses(array $filters, int $page, int $limit): array
    {
        $limit = min(max($limit, 1), 100);
        $date = trim((string) ($filters['date'] ?? ''));
        if ($date === '') {
            $date = (string) ($this->connection->table('ops_item_sales_cost_ratio')->max('stat_date') ?? '');
            $date = substr($date, 0, 10);
        }
        if ($date === '') {
            return ['rows' => [], 'date' => null];
        }
        $query = $this->connection->table('ops_item_sales_cost_ratio as c')
            ->where('c.stat_date', '>=', $date . ' 00:00:00')
            ->where('c.stat_date', '<', date('Y-m-d 00:00:00', strtotime($date . ' +1 day')))
            ->where('c.push_fee', '<>', 0);
        $shopNames = $this->selectedShopNames($filters);
        if ($shopNames !== []) {
            // 多店铺：每个店铺做一次模糊匹配再取并集，和订单售后费用明细的口径一致。
            $query->where(function (Builder $shop) use ($shopNames): void {
                foreach ($shopNames as $name) {
                    $shop->orWhereRaw("c.shop_name LIKE ? ESCAPE '!'", [$this->contains($name)]);
                }
            });
        }
        if (($filters['sku_id'] ?? '') !== '') {
            // jst_skumap 的业务编码是 LONGTEXT 且有两千多万行，不能在页面
            // 请求中做模糊关联；推广账单自身的 item_id 是稳定可筛选字段。
            // 支持批量：每个编码一次匹配，多个取并集。
            $query->where(function (Builder $sku) use ($filters): void {
                foreach ($this->batchValues((string) $filters['sku_id']) as $value) {
                    $sku->orWhereRaw("c.item_id LIKE ? ESCAPE '!'", [$this->contains($value)]);
                }
            });
        }
        $rows = $query->select(['c.shop_name', 'c.item_id as shop_style_code', 'c.push_fee as promotion_fee'])
            ->orderByDesc('c.push_fee')->orderBy('c.shop_name')->orderBy('c.item_id')
            ->offset((max($page, 1) - 1) * $limit)->limit($limit + 1)
            ->get()->map(static fn (object $row): array => (array) $row)->all();
        foreach ($rows as &$row) {
            $row['product_code'] = null;
            $row['style_code'] = null;
            $row['shop_product_code'] = $row['shop_style_code'];
            $row['online_color_spec'] = null;
            $row['promotion_fee'] = round((float) $row['promotion_fee'], 2);
        }
        unset($row);

        return ['rows' => $rows, 'date' => $date];
    }

    private function baseQuery(array $filters, int $perBranch): Builder
    {
        // 同一份报表口径要同时覆盖两个来源：聚水潭非淘系订单（jst_Orders 系列）和
        // 淘系（天猫/淘宝）订单（jst_qmcust 系列）。两边的列名基本一致，淘系明细没有
        // send_warehouse，用订单上的 wms_co_id 兜底；source_branch 用于后续按来源补售后。
        //
        // 筛选条件必须分别下推到两个分支：否则 MariaDB 会先合并上千万行再筛，
        // 既用不上各自订单日期索引，也会把连接拖到超时。
        //
        // 订单号先去两套来源里解析成候选 o_id（单表、走前缀索引），再让分支按 o_id
        // 过滤；直接在订单和明细关联后做 %x% 匹配时优化器会放弃索引，淘系那套
        // 600 多万行明细要全表扫描，实测 120 秒都跑不完。
        $orderCandidates = $this->orderNumberCandidates((string) ($filters['order_no'] ?? ''));

        $plain = $this->sourceQuery('ops_jst_orders', 'ops_jst_order_item', 'COALESCE(i.send_warehouse, o.wms_co_id)', 'i.send_warehouse', 'plain');
        $this->applyFilters($plain, $filters, $orderCandidates['plain'] ?? null);
        $plain->orderByDesc('o.order_date')->orderByDesc('o.o_id')->limit($perBranch);

        $taobao = $this->sourceQuery('ops_jst_qmcust_orders', 'ops_jst_qmcust_order_items', 'o.wms_co_id', 'o.wms_co_id', 'taobao');
        $this->applyFilters($taobao, $filters, $orderCandidates['taobao'] ?? null);
        $taobao->orderByDesc('o.order_date')->orderByDesc('o.o_id')->limit($perBranch);

        return $this->connection->query()->fromSub(
            $plain->unionAll($taobao),
            'u',
        );
    }

    /**
     * 把订单号解析成两套来源各自的候选 o_id。
     *
     * 订单号字段（内部/线上/子订单号）在镜像里都有前缀索引，等值或"从头匹配"能走索引；
     * 返回 null 表示不能用快路径（订单号太短、候选过多，或本身就是模糊匹配），
     * 调用方退回原来的 %x% 匹配。
     *
     * @return array{plain:list<int>,taobao:list<int>}|null
     */
    private function orderNumberCandidates(string $raw): ?array
    {
        $needles = $this->batchValues($raw);
        if ($needles === []) {
            return null;
        }

        // 单个订单号保留"从头匹配"（可以只输前几位）；批量时列表里都是完整单号，
        // 用等值 IN，避免几十个前缀范围把优化器拖垮。
        $single = count($needles) === 1 ? $needles[0] : null;
        if ($single !== null && mb_strlen($single) < 4) {
            return null;
        }

        $prefix = $single !== null ? $this->likePrefix($single) : null;
        $digits = [];
        foreach ($needles as $needle) {
            if (ctype_digit((string) $needle)) {
                $digits[] = (int) $needle;
            }
        }
        $candidates = [];

        foreach ([
            'plain' => ['ops_jst_orders', 'ops_jst_order_item'],
            'taobao' => ['ops_jst_qmcust_orders', 'ops_jst_qmcust_order_items'],
        ] as $branch => [$ordersTable, $itemsTable]) {
            $ids = [];

            $orders = $this->connection->table($ordersTable)
                ->select('o_id')
                ->where(function (Builder $order) use ($prefix, $needles, $digits): void {
                    if ($prefix !== null) {
                        $order->orWhereRaw("so_id LIKE ? ESCAPE '!'", [$prefix]);
                    } else {
                        $order->orWhereIn('so_id', $needles);
                    }
                    if ($digits !== []) {
                        $order->orWhereIn('o_id', $digits);
                    }
                });
            foreach ($orders->limit(self::ORDER_NUMBER_CANDIDATE_LIMIT + 1)->pluck('o_id') as $id) {
                $ids[(int) $id] = true;
            }

            $items = $this->connection->table($itemsTable)->select('o_id');
            if ($prefix !== null) {
                $items->whereRaw("outer_oi_id LIKE ? ESCAPE '!'", [$prefix]);
            } else {
                $items->whereIn('outer_oi_id', $needles);
            }
            foreach ($items->limit(self::ORDER_NUMBER_CANDIDATE_LIMIT + 1)->pluck('o_id') as $id) {
                $ids[(int) $id] = true;
            }

            if (count($ids) > self::ORDER_NUMBER_CANDIDATE_LIMIT) {
                return null;
            }

            $candidates[$branch] = array_map('intval', array_keys($ids));
        }

        return $candidates;
    }

    private function sourceQuery(
        string $ordersTable,
        string $itemsTable,
        string $warehouseJoinKey,
        string $warehouseColumn,
        string $branch,
    ): Builder {
        return $this->connection->table($ordersTable . ' as o')
            ->join($itemsTable . ' as i', 'i.o_id', '=', 'o.o_id')
            ->leftJoin('ops_jst_warehouse_info as wh', function ($join) use ($warehouseJoinKey): void {
                $join->on('wh.wms_co_id', '=', $this->connection->raw($warehouseJoinKey));
            })
            ->select($this->columns($warehouseColumn))
            ->selectRaw('? as source_branch', [$branch]);
    }

    /** @param list<int>|null $orderIds 订单号已解析出的候选 o_id；null 表示退回模糊匹配 */
    private function applyFilters(Builder $query, array $filters, ?array $orderIds = null): void
    {
        if (($filters['from'] ?? '') !== '') {
            $query->where('o.order_date', '>=', $filters['from'] . ' 00:00:00');
        }
        if (($filters['to'] ?? '') !== '') {
            $query->where('o.order_date', '<', date('Y-m-d 00:00:00', strtotime($filters['to'] . ' +1 day')));
        }
        if (($filters['order_no'] ?? '') !== '') {
            if ($orderIds === null) {
                $needles = $this->batchValues((string) $filters['order_no']);
                if ($needles === []) {
                    $query->whereRaw('1 = 0');
                } else {
                    $query->where(function (Builder $order) use ($needles): void {
                        foreach ($needles as $value) {
                            $needle = $this->contains($value);
                            $order->orWhere(function (Builder $group) use ($needle): void {
                                $group->whereRaw("CAST(o.o_id AS CHAR) LIKE ? ESCAPE '!'", [$needle])
                                    ->orWhereRaw("o.so_id LIKE ? ESCAPE '!'", [$needle])
                                    ->orWhereRaw("i.outer_oi_id LIKE ? ESCAPE '!'", [$needle]);
                            });
                        }
                    });
                }
            } elseif ($orderIds === []) {
                // 两套来源都没有候选订单，直接让分支返回空集，避免白扫一遍。
                $query->whereRaw('1 = 0');
            } else {
                $query->whereIn('o.o_id', $orderIds);
            }
        }
        $shopNames = $this->selectedShopNames($filters);

        if ($shopNames !== []) {
            // A release clears the complete cache; the small shop directory remains valid.
            $known = $this->shopNames();
            if (! empty($filters['shop_names_exact']) && array_diff($shopNames, $known) === []) {
                $query->whereIn('o.shop_name', $shopNames);
            } else {
                // Free text and legacy callers keep their existing contains semantics.
                $query->where(function (Builder $shop) use ($shopNames): void {
                    foreach ($shopNames as $name) {
                        $shop->orWhereRaw("o.shop_name LIKE ? ESCAPE '!'", [$this->contains($name)]);
                    }
                });
            }
        }
        if (($filters['sku_id'] ?? '') !== '') {
            // 商品编码同样支持批量：每个编码一组"商品/款式/店铺编码"匹配，多组取并集。
            $needles = $this->batchValues((string) $filters['sku_id']);
            $query->where(function (Builder $sku) use ($needles): void {
                foreach ($needles as $value) {
                    $needle = $this->contains($value);
                    $sku->orWhere(function (Builder $group) use ($needle): void {
                        $group->whereRaw("i.sku_id LIKE ? ESCAPE '!'", [$needle])
                            ->orWhereRaw("i.shop_sku_id LIKE ? ESCAPE '!'", [$needle])
                            ->orWhereRaw("i.shop_i_id LIKE ? ESCAPE '!'", [$needle]);
                    });
                }
            });
        }
    }

    /** @return list<string> */
    private function columns(string $warehouseColumn = 'i.send_warehouse'): array
    {
        return [
            'o.o_id as internal_order_no', 'o.so_id as online_order_no',
            'i.outer_oi_id as sub_order_no', 'o.pay_date as pay_time',
            'o.end_time as order_complete_time', 'o.send_date as send_time',
            'o.order_date as order_time', 'o.shop_name', 'o.shop_id as shop_code',
            'i.sku_id as product_code', 'i.i_id as style_code',
            'i.shop_sku_id as shop_product_code', 'i.shop_i_id as shop_style_code',
            'o.shop_site', 'i.properties_value as color_spec',
            'i.name as online_product_name', 'i.qty as product_quantity',
            'i.amount as product_amount', 'i.buyer_paid_amount as payment_amount',
            'i.seller_income_amount', 'i.is_gift', 'i.src_combine_sku_id',
            'o.type as order_type', 'o.labels as order_tags',
            'o.free_amount as merchant_discount', 'o.freight as freight_income',
            $warehouseColumn . ' as send_warehouse', 'o.wms_co_id', 'o.l_id as express_no',
            'o.logistics_company as express_company', 'o.receiver_state as province',
            'o.receiver_city as city', 'o.creator_name as salesperson',
            'wh.name as shipping_warehouse', 'wh.remark1 as operation_cloud_warehouse',
        ];
    }

    /** @param array<int,array<string,mixed>> $rows */
    private function hydrateSkus(array &$rows): void
    {
        $skuIds = array_values(array_unique(array_filter(array_map(
            static fn (array $row): string => trim((string) ($row['product_code'] ?? '')),
            $rows,
        ))));
        $skus = $skuIds === [] ? collect() : $this->connection->table('ops_jst_sku')
            ->where('sku_type', 'normal')->whereIn('sku_id', $skuIds)
            ->orderByDesc('autoid')
            ->get(['sku_id', 'brand', 'category', 'vc_name', 'cost_price', 'name', 'supplier_id', 'supplier_name'])
            ->unique('sku_id')->keyBy('sku_id');

        foreach ($rows as &$row) {
            $sku = $skus->get((string) ($row['product_code'] ?? ''));
            $row['brand'] = $sku->brand ?? null;
            $row['category'] = $sku->category ?? null;
            $row['virtual_category'] = $sku->vc_name ?? null;
            $row['unit_cost'] = $sku->cost_price ?? 0;
            $row['sku_name'] = $sku->name ?? null;
            $row['supplier_code'] = $sku->supplier_id ?? null;
            $row['supplier_name'] = $sku->supplier_name ?? null;
            unset($row['sku_id']);
        }
        unset($row);
    }

    private function baseRow(array $row): array
    {
        $quantity = (float) ($row['product_quantity'] ?? 0);
        $amount = (float) ($row['product_amount'] ?? 0);
        $cost = (float) ($row['unit_cost'] ?? 0);
        $payment = (float) ($row['payment_amount'] ?? $amount);
        $sellerIncome = (float) ($row['seller_income_amount'] ?? 0);
        $row += [
            'after_sales_no' => null, 'refund_type' => null, 'question_type' => null,
            'detail_return_type' => null, 'after_sales_tags' => null,
        ];
        $row['product_name'] = ($row['sku_name'] ?? '') !== '' ? $row['sku_name'] : ($row['color_spec'] ?? null);
        $row['product_quantity'] = $quantity;
        $row['product_amount'] = $amount;
        $row['product_cost'] = $cost * $quantity;
        $row['reversal_order_cost'] = $amount < 0 ? $cost * abs($quantity) : 0.0;
        $row['merchant_discount'] = (float) ($row['merchant_discount'] ?? 0);
        $row['freight_income'] = (float) ($row['freight_income'] ?? 0);
        $row['payment_amount'] = $payment;
        $row['platform_discount'] = $sellerIncome > $payment ? $sellerIncome - $payment : 0.0;
        $row['reversal_order_amount'] = $amount < 0 ? abs($amount) : null;
        $row['actual_shipped_amount'] = $amount;
        $row['actual_shipped_quantity'] = $quantity;
        $row['actual_shipped_cost'] = $cost * $quantity;
        foreach (['pre', 'post'] as $phase) {
            foreach (['application_quantity', 'application_amount', 'application_cost', 'return_quantity',
                'return_amount', 'return_cost', 'refund_amount', 'refund_cost', 'platform_subsidy'] as $field) {
                $row[$phase . '_' . $field] = 0.0;
            }
        }
        $row['defective_quantity'] = 0.0;
        $row['is_gift'] = (int) ($row['is_gift'] ?? 0) === 1 ? '是' : '否';
        $row['combine_type'] = empty($row['src_combine_sku_id']) ? '非组合' : '组合装';
        $row['shipping_warehouse_code'] = $row['send_warehouse'] ?: ($row['wms_co_id'] ?? null);
        $row['product_link'] = $this->productLink(
            (string) ($row['shop_site'] ?? ''), (string) ($row['shop_name'] ?? ''),
            (string) ($row['shop_style_code'] ?? ''),
        );
        foreach (['offline_customer_code', 'offline_customer_name', 'unique_code', 'registration_time',
            'confirmation_time', 'warehouse_entry_time', 'classification_order',
            'receiving_warehouse_code', 'receiving_warehouse'] as $field) {
            $row[$field] = null;
        }
        foreach ($this->expenseFields() as $field) {
            $row[$field] = null;
        }
        // 500112 冲减收入：暂以「退货红冲金额(reversal_order_amount)」作为收入冲减口径，
        // 待财务确认最终取数规则后替换。其余 account_* 科目字段仍为空（科目归集未接入）。
        $row['account_500112'] = $row['reversal_order_amount'] === null
            ? null
            : round((float) $row['reversal_order_amount'], 2);
        unset($row['shop_site'], $row['unit_cost'], $row['sku_name'], $row['seller_income_amount'],
            $row['src_combine_sku_id'], $row['send_warehouse'], $row['wms_co_id']);
        return $row;
    }

    /** @param array<int,array<string,mixed>> $rows */
    private function hydrateRefunds(array &$rows): void
    {
        // 两个来源的售后表不同：非淘系读 ops_jst_return_refund_*，淘系读 ops_jst_qmcust_refund*。
        // 按来源分组取数，再用 "来源:o_id" 匹配，避免两个来源的 o_id 撞号。
        $idsByBranch = ['plain' => [], 'taobao' => []];
        foreach ($rows as $row) {
            $orderId = (string) ($row['internal_order_no'] ?? '');
            if ($orderId === '') {
                continue;
            }
            $idsByBranch[self::branchOf($row)][$orderId] = true;
        }

        if ($idsByBranch['plain'] === [] && $idsByBranch['taobao'] === []) {
            foreach ($rows as &$row) {
                unset($row['source_branch']);
            }
            unset($row);

            return;
        }

        $refunds = collect();
        $sources = [
            'plain' => ['ops_jst_return_refund_info', 'ops_jst_return_refund_item'],
            'taobao' => ['ops_jst_qmcust_refunds', 'ops_jst_qmcust_refund_items'],
        ];

        foreach ($sources as $branch => [$infoTable, $itemTable]) {
            $orderIds = array_keys($idsByBranch[$branch]);
            if ($orderIds === []) {
                continue;
            }

            $grouped = $this->connection->table($infoTable . ' as a')
                ->join($itemTable . ' as ri', 'ri.as_id', '=', 'a.as_id')
                ->whereIn('a.o_id', $orderIds)
                ->orderByDesc('a.as_date')->orderByDesc('a.as_id')
                ->get([
                    'a.o_id as internal_order_no', 'a.as_id as after_sales_id',
                    'a.outer_as_id as outside_after_sales_id', 'a.as_date as after_sales_time',
                    'a.type as refund_type', 'a.question_type', 'a.labels as after_sales_tags',
                    'a.refund as refund_amount', 'a.free_amount as platform_subsidy',
                    'ri.outer_oi_id as sub_order_no', 'ri.sku_id as product_code',
                    'ri.type as detail_return_type', 'ri.qty as application_quantity',
                    'ri.amount as application_amount', 'ri.r_qty as return_quantity',
                    'ri.shop_amount as return_amount', 'ri.defective_qty as defective_quantity',
                ])->groupBy('internal_order_no');

            foreach ($grouped as $orderId => $items) {
                $refunds->put($branch . ':' . $orderId, $items);
            }
        }

        foreach ($rows as &$row) {
            $matches = collect($refunds->get(self::branchOf($row) . ':' . ($row['internal_order_no'] ?? ''), collect()))->filter(
                static function (object $refund) use ($row): bool {
                    return (string) ($refund->sub_order_no ?? '') !== ''
                        ? (string) $refund->sub_order_no === (string) ($row['sub_order_no'] ?? '')
                        : (string) ($refund->product_code ?? '') === (string) ($row['product_code'] ?? '');
                },
            )->values();
            $first = $matches->first();
            if ($first !== null) {
                $row['after_sales_no'] = $first->outside_after_sales_id ?: $first->after_sales_id;
                $row['refund_type'] = $first->refund_type;
                $row['question_type'] = $first->question_type;
                $row['detail_return_type'] = $first->detail_return_type;
                $row['after_sales_tags'] = $first->after_sales_tags;
            }
            $unitCost = $row['product_quantity'] == 0 ? 0 : $row['product_cost'] / $row['product_quantity'];
            $preReturned = 0.0;
            foreach ($matches as $refund) {
                $phase = empty($row['send_time']) || (string) $refund->after_sales_time < (string) $row['send_time']
                    ? 'pre' : 'post';
                $application = (float) ($refund->application_quantity ?? 0);
                $returned = (float) ($refund->return_quantity ?? 0);
                $row[$phase . '_application_quantity'] += $application;
                $row[$phase . '_application_amount'] += (float) ($refund->application_amount ?? 0);
                $row[$phase . '_application_cost'] += $application * $unitCost;
                $row[$phase . '_return_quantity'] += $returned;
                $row[$phase . '_return_amount'] += (float) ($refund->return_amount ?? 0);
                $row[$phase . '_return_cost'] += $returned * $unitCost;
                $row[$phase . '_refund_amount'] += (float) ($refund->refund_amount ?? 0);
                $row[$phase . '_refund_cost'] += ($application - $returned) * $unitCost;
                $row[$phase . '_platform_subsidy'] += (float) ($refund->platform_subsidy ?? 0);
                $row['defective_quantity'] += (float) ($refund->defective_quantity ?? 0);
                if ($phase === 'pre') {
                    $preReturned += $returned;
                }
            }
            $row['actual_shipped_quantity'] = $row['product_quantity'] - $preReturned;
            $row['actual_shipped_amount'] = $row['product_amount'] - $row['pre_return_amount'];
            $row['actual_shipped_cost'] = $row['actual_shipped_quantity'] * $unitCost;
            unset($row['source_branch']);
        }
        unset($row);
    }

    /** 判断订单行来自哪一套来源表。 */
    private static function branchOf(array $row): string
    {
        return ($row['source_branch'] ?? 'plain') === 'taobao' ? 'taobao' : 'plain';
    }

    private function productLink(string $site, string $shopName, string $itemId): ?string
    {
        if ($itemId === '') {
            return null;
        }
        $platform = mb_strtolower($site . ' ' . $shopName);
        if (str_contains($platform, '京东') || str_contains($platform, '京喜') || str_contains($platform, 'jd')) {
            return 'https://item.jd.com/' . rawurlencode($itemId) . '.html';
        }
        if (str_contains($platform, '抖音')) {
            return 'https://haohuo.jinritemai.com/ecommerce/trade/detail/index.html?id=' . rawurlencode($itemId);
        }
        return null;
    }

    /** @return list<string> */
    private function expenseFields(): array
    {
        return [
            'account_500112', 'account_60031', 'account_800101', 'account_800103',
            'account_800104', 'account_800105', 'account_800106', 'account_800107',
            'account_800108', 'account_800109', 'account_800110', 'account_800114',
            'account_8001201', 'account_8001202', 'account_800122', 'account_800125',
            'account_800127', 'account_800129', 'account_800130', 'account_800132',
            'account_800304', 'account_800501', 'account_800503',
        ];
    }

    private function contains(string $value): string
    {
        return '%' . str_replace(['!', '%', '_'], ['!!', '!%', '!_'], trim($value)) . '%';
    }

    /** 转义后的"从头匹配"模式，配合 so_id / outer_oi_id 的前缀索引使用。 */
    private function likePrefix(string $value): string
    {
        return str_replace(['!', '%', '_'], ['!!', '!%', '!_'], trim($value)) . '%';
    }

    /**
     * 批量筛选值：支持换行、逗号、分号、顿号、制表符和空格分隔，去重后最多
     * BATCH_FILTER_LIMIT 个，避免拼出上千个条件。
     *
     * @return list<string>
     */
    private function batchValues(string $raw): array
    {
        $raw = trim($raw);
        if ($raw === '') {
            return [];
        }

        $parts = preg_split('/[\s,;，；、]+/u', $raw) ?: [];
        $values = [];

        foreach ($parts as $part) {
            $part = trim((string) $part);
            if ($part === '') {
                continue;
            }

            // 键加 'v' 前缀：纯数字单号直接当数组键会被 PHP 转成 int，
            // 后面按数值去比对就用不上 so_id / outer_oi_id 的前缀索引了。
            if (! isset($values['v' . $part])) {
                $values['v' . $part] = $part;
            }
            if (count($values) >= self::BATCH_FILTER_LIMIT) {
                break;
            }
        }

        return array_values($values);
    }
}