<?php

namespace app\support;

use support\Log;
use support\think\Db;

class ExpressCompanyService
{
    public function __construct()
    {
        $this->ensureTable();
        $this->ensureCatalogTable();
        $this->bootstrapDefaults();
    }

    public function list(bool $enabledOnly = false): array
    {
        $query = Db::name('express_companies')
            ->order('is_default', 'desc')
            ->order('sort_order', 'asc')
            ->order('id', 'asc');

        if ($enabledOnly) {
            $query->where('status', 'enabled');
        }

        return array_map(fn (array $item) => $this->format($item), $query->select()->toArray());
    }

    public function save(array $payload, int $id = 0): int
    {
        $now = date('Y-m-d H:i:s');
        $companyName = trim((string)($payload['company_name'] ?? ''));
        $companyCode = trim((string)($payload['company_code'] ?? ''));
        $status = trim((string)($payload['status'] ?? 'enabled'));
        $isDefault = !empty($payload['is_default']);

        if ($companyName === '') {
            throw new \RuntimeException('快递公司名称不能为空');
        }
        if (!in_array($status, ['enabled', 'disabled'], true)) {
            throw new \RuntimeException('快递公司状态无效');
        }
        if ($isDefault && $status !== 'enabled') {
            throw new \RuntimeException('默认快递公司必须保持启用');
        }
        if ($companyCode === '') {
            $companyCode = $this->generateCompanyCode($companyName);
        }

        $existsByName = Db::name('express_companies')
            ->where('company_name', $companyName)
            ->when($id > 0, fn ($query) => $query->where('id', '<>', $id))
            ->find();
        if ($existsByName) {
            throw new \RuntimeException('快递公司名称已存在');
        }

        $existsByCode = Db::name('express_companies')
            ->where('company_code', $companyCode)
            ->when($id > 0, fn ($query) => $query->where('id', '<>', $id))
            ->find();
        if ($existsByCode) {
            throw new \RuntimeException('快递公司编码已存在');
        }

        $data = [
            'company_name' => $companyName,
            'company_code' => $companyCode,
            'status' => $status,
            'is_default' => $isDefault ? 1 : 0,
            'sort_order' => (int)($payload['sort_order'] ?? 0),
            'remark' => trim((string)($payload['remark'] ?? '')),
            'updated_at' => $now,
        ];

        Db::startTrans();
        try {
            if ($isDefault) {
                Db::name('express_companies')->update([
                    'is_default' => 0,
                    'updated_at' => $now,
                ]);
            }

            if ($id > 0) {
                Db::name('express_companies')->where('id', $id)->update($data);
                $companyId = $id;
            } else {
                $data['created_at'] = $now;
                $companyId = (int)Db::name('express_companies')->insertGetId($data);
            }

            $this->ensureEnabledDefault($now);
            Db::commit();
        } catch (\Throwable $e) {
            Db::rollback();
            throw $e;
        }

        return $companyId;
    }

    public function defaultName(): string
    {
        $row = Db::name('express_companies')
            ->where('status', 'enabled')
            ->where('is_default', 1)
            ->find();

        if (!$row) {
            $row = Db::name('express_companies')
                ->where('status', 'enabled')
                ->order('sort_order', 'asc')
                ->order('id', 'asc')
                ->find();
        }

        return trim((string)($row['company_name'] ?? ''));
    }

    public function catalogList(string $keyword = '', int $limit = 30): array
    {
        $keyword = trim($keyword);
        $limit = max(1, min(100, $limit));

        $this->ensureCatalogTable();
        $query = Db::name('kuaidi100_express_company_catalog')
            ->order('sort_order', 'asc')
            ->order('company_name', 'asc')
            ->order('id', 'asc');

        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->whereRaw('(company_name LIKE :keyword OR company_code LIKE :keyword OR company_type LIKE :keyword)', [
                'keyword' => $like,
            ]);
        }

        $rows = $query->limit($limit)->select()->toArray();
        if (!$rows) {
            $rows = $this->localCompanyFallbackList($keyword, $limit);
        }

        return array_map(fn (array $item) => $this->formatCatalog($item), $rows);
    }

    public function catalogTotal(): int
    {
        $this->ensureCatalogTable();
        return (int)Db::name('kuaidi100_express_company_catalog')->count();
    }

    public function catalogSyncedAt(): string
    {
        $this->ensureCatalogTable();
        $value = Db::name('kuaidi100_express_company_catalog')
            ->order('synced_at', 'desc')
            ->value('synced_at');
        $value = trim((string)($value ?? ''));
        return $value === '0' ? '' : $value;
    }

    public function syncCatalog(): array
    {
        $client = new Kuaidi100Client();
        $binary = $client->downloadCompanyCatalogWorkbook();

        $tmpFile = tempnam(sys_get_temp_dir(), 'kdbm_');
        if ($tmpFile === false) {
            throw new \RuntimeException('快递100公司码表临时文件创建失败');
        }

        file_put_contents($tmpFile, $binary);

        $now = date('Y-m-d H:i:s');
        Db::startTrans();
        try {
            $rows = $this->parseCatalogWorkbook($tmpFile);
            if (!$rows) {
                throw new \RuntimeException('快递100公司码表解析结果为空');
            }

            $existingCodes = Db::name('kuaidi100_express_company_catalog')
                ->column('company_code');
            $existingCodeMap = array_fill_keys(array_map('strval', $existingCodes), true);
            $inserted = 0;
            $updated = 0;
            $payloadRows = [];
            foreach ($rows as $index => $item) {
                $payload = [
                    'company_name' => $item['company_name'],
                    'company_code' => $item['company_code'],
                    'company_type' => $item['company_type'],
                    'sort_order' => (int)($item['sort_order'] ?? $index + 1),
                    'synced_at' => $now,
                    'created_at' => $now,
                    'updated_at' => $now,
                ];

                if (isset($existingCodeMap[$payload['company_code']])) {
                    $updated++;
                } else {
                    $inserted++;
                }
                $payloadRows[$payload['company_code']] = $payload;
            }

            foreach (array_chunk(array_values($payloadRows), 300) as $chunkIndex => $chunk) {
                $valuesSql = [];
                $bindings = [];
                foreach ($chunk as $rowIndex => $payload) {
                    $prefix = 'r' . $chunkIndex . '_' . $rowIndex;
                    $valuesSql[] = sprintf(
                        '(:%s_company_name, :%s_company_code, :%s_company_type, :%s_sort_order, :%s_synced_at, :%s_created_at, :%s_updated_at)',
                        $prefix,
                        $prefix,
                        $prefix,
                        $prefix,
                        $prefix,
                        $prefix,
                        $prefix
                    );
                    foreach ($payload as $field => $value) {
                        $bindings[$prefix . '_' . $field] = $value;
                    }
                }

                Db::execute(
                    'INSERT INTO kuaidi100_express_company_catalog '
                    . '(company_name, company_code, company_type, sort_order, synced_at, created_at, updated_at) VALUES '
                    . implode(',', $valuesSql)
                    . ' ON DUPLICATE KEY UPDATE '
                    . 'company_name = VALUES(company_name), '
                    . 'company_type = VALUES(company_type), '
                    . 'sort_order = VALUES(sort_order), '
                    . 'synced_at = VALUES(synced_at), '
                    . 'updated_at = VALUES(updated_at)',
                    $bindings
                );
            }

            $backfilled = $this->backfillLocalCompanyCodesFromCatalog($now);
            Db::commit();
        } catch (\Throwable $e) {
            Db::rollback();
            throw $e;
        } finally {
            @unlink($tmpFile);
        }

        return [
            'total' => count($rows),
            'inserted' => $inserted ?? 0,
            'updated' => $updated ?? 0,
            'backfilled' => $backfilled ?? 0,
            'synced_at' => $now,
        ];
    }

    public function resolveCompanyCode(string $companyNameOrCode, string $trackingNo = ''): string
    {
        $result = $this->recognizeCompany($companyNameOrCode, $trackingNo);
        return (string)($result['company_code'] ?? '');
    }

    public function recognizeCompany(string $companyNameOrCode, string $trackingNo = ''): array
    {
        $companyNameOrCode = trim($companyNameOrCode);
        $trackingNo = trim($trackingNo);
        $recognitionError = '';

        $resolved = $this->resolveLocalCompanyMatch($companyNameOrCode);
        if (!$resolved && $trackingNo !== '') {
            try {
                $candidates = $this->recognizeKuaidi100Candidates($trackingNo);
            } catch (\Throwable $e) {
                $candidates = [];
                $recognitionError = $e->getMessage();
            }
            $resolved = $this->pickRecognizedCandidate($candidates, $companyNameOrCode);
            return [
                'input' => $companyNameOrCode,
                'tracking_no' => $trackingNo,
                'company_code' => (string)($resolved['company_code'] ?? ''),
                'company_name' => (string)($resolved['company_name'] ?? $companyNameOrCode),
                'status' => $resolved ? 'resolved' : ($candidates ? 'multiple' : 'none'),
                'status_text' => $resolved ? '已识别' : ($candidates ? '识别到多个候选' : '未识别'),
                'error_message' => $recognitionError,
                'resolved' => $resolved,
                'candidates' => $candidates,
            ];
        }

        if ($resolved) {
            return [
                'input' => $companyNameOrCode,
                'tracking_no' => $trackingNo,
                'company_code' => (string)($resolved['company_code'] ?? ''),
                'company_name' => (string)($resolved['company_name'] ?? $companyNameOrCode),
                'status' => 'resolved',
                'status_text' => '已识别',
                'error_message' => '',
                'resolved' => $resolved,
                'candidates' => [$resolved],
            ];
        }

        return [
            'input' => $companyNameOrCode,
            'tracking_no' => $trackingNo,
            'company_code' => '',
            'company_name' => $companyNameOrCode,
            'status' => 'none',
            'status_text' => '未识别',
            'error_message' => $recognitionError,
            'resolved' => null,
            'candidates' => [],
        ];
    }

    public function resolveCompanyNameByCode(string $companyCode): string
    {
        $companyCode = trim($companyCode);
        if ($companyCode === '') {
            return '';
        }

        $row = Db::name('express_companies')
            ->where('company_code', $companyCode)
            ->find();
        $name = trim((string)($row['company_name'] ?? ''));
        if ($name !== '') {
            return $name;
        }

        $row = Db::name('kuaidi100_express_company_catalog')
            ->where('company_code', $companyCode)
            ->find();
        $name = trim((string)($row['company_name'] ?? ''));
        if ($name !== '') {
            return $name;
        }

        return $this->companyAliasName($companyCode);
    }

    private function resolveLocalCompanyMatch(string $companyNameOrCode): ?array
    {
        $companyNameOrCode = trim($companyNameOrCode);
        if ($companyNameOrCode === '') {
            return null;
        }

        $row = Db::name('express_companies')
            ->where('company_name', $companyNameOrCode)
            ->find();
        $code = trim((string)($row['company_code'] ?? ''));
        if ($this->isValidCompanyCode($code)) {
            return [
                'company_name' => (string)($row['company_name'] ?? $companyNameOrCode),
                'company_code' => $code,
                'source' => 'local',
            ];
        }

        $row = Db::name('express_companies')
            ->where('company_code', $companyNameOrCode)
            ->find();
        $code = trim((string)($row['company_code'] ?? ''));
        if ($this->isValidCompanyCode($code)) {
            $name = trim((string)($row['company_name'] ?? ''));
            if ($name === '') {
                $name = $this->resolveCompanyNameByCode($code);
            }
            if ($name === '') {
                $name = $companyNameOrCode;
            }

            return [
                'company_name' => $name,
                'company_code' => $code,
                'source' => 'local',
            ];
        }

        $catalogRow = Db::name('kuaidi100_express_company_catalog')
            ->where('company_name', $companyNameOrCode)
            ->find();
        $code = trim((string)($catalogRow['company_code'] ?? ''));
        if ($this->isValidCompanyCode($code)) {
            return [
                'company_name' => (string)($catalogRow['company_name'] ?? $companyNameOrCode),
                'company_code' => $code,
                'source' => 'catalog',
            ];
        }

        $catalogRow = Db::name('kuaidi100_express_company_catalog')
            ->where('company_code', $companyNameOrCode)
            ->find();
        $code = trim((string)($catalogRow['company_code'] ?? ''));
        if ($this->isValidCompanyCode($code)) {
            $name = trim((string)($catalogRow['company_name'] ?? ''));
            if ($name === '') {
                $name = $this->resolveCompanyNameByCode($code);
            }
            if ($name === '') {
                $name = $companyNameOrCode;
            }

            return [
                'company_name' => $name,
                'company_code' => $code,
                'source' => 'catalog',
            ];
        }

        $aliasCode = $this->companyAliasCode($companyNameOrCode);
        if ($aliasCode !== '') {
            return [
                'company_name' => $this->resolveCompanyNameByCode($aliasCode) ?: $companyNameOrCode,
                'company_code' => $aliasCode,
                'source' => 'alias',
            ];
        }

        return $this->isValidCompanyCode($companyNameOrCode)
            ? [
                'company_name' => $this->resolveCompanyNameByCode($companyNameOrCode) ?: $companyNameOrCode,
                'company_code' => $companyNameOrCode,
                'source' => 'code',
            ]
            : null;
    }

    private function recognizeKuaidi100Candidates(string $trackingNo): array
    {
        $client = new Kuaidi100Client();
        $response = $client->recognize($trackingNo);
        $items = $this->extractRecognizeItems($response);
        if (!$items) {
            return [];
        }

        $candidates = [];
        foreach ($items as $item) {
            $candidate = $this->normalizeRecognizeCandidate($item);
            if ($candidate) {
                $candidates[] = $candidate;
            }
        }

        return $candidates;
    }

    private function extractRecognizeItems(array $response): array
    {
        if (isset($response['data']) && is_array($response['data'])) {
            $items = $response['data'];
        } else {
            $items = $response;
        }

        if ($this->isAssocArray($items) && (isset($items['comCode']) || isset($items['company_code']))) {
            return [$items];
        }

        return array_values(array_filter($items, static fn ($item) => is_array($item)));
    }

    private function normalizeRecognizeCandidate(array $item): ?array
    {
        $companyCode = trim((string)($item['comCode'] ?? $item['company_code'] ?? $item['code'] ?? ''));
        $officialName = trim((string)($item['name'] ?? $item['company_name'] ?? $item['comName'] ?? ''));
        if ($companyCode === '' && $officialName === '') {
            return null;
        }

        $localName = $companyCode !== '' ? $this->resolveCompanyNameByCode($companyCode) : '';
        $companyName = $localName !== '' ? $localName : ($officialName !== '' ? $officialName : $companyCode);

        return [
            'company_name' => $companyName,
            'company_code' => $companyCode,
            'official_name' => $officialName,
            'display_text' => $companyName !== '' && $companyCode !== ''
                ? sprintf('%s / %s', $companyName, $companyCode)
                : ($companyName !== '' ? $companyName : $companyCode),
            'length_pre' => (int)($item['lengthPre'] ?? $item['length_pre'] ?? 0),
            'source' => $localName !== '' ? 'catalog' : 'kuaidi100',
        ];
    }

    private function pickRecognizedCandidate(array $candidates, string $companyNameOrCode): ?array
    {
        if (!$candidates) {
            return null;
        }

        if (count($candidates) === 1) {
            return $candidates[0];
        }

        $companyNameOrCode = trim($companyNameOrCode);
        if ($companyNameOrCode === '') {
            return null;
        }

        foreach ($candidates as $candidate) {
            if (!is_array($candidate)) {
                continue;
            }

            $candidateName = trim((string)($candidate['company_name'] ?? ''));
            $candidateCode = trim((string)($candidate['company_code'] ?? ''));
            if ($candidateName === $companyNameOrCode || $candidateCode === $companyNameOrCode) {
                return $candidate;
            }
        }

        return null;
    }

    private function isAssocArray(array $items): bool
    {
        return array_keys($items) !== range(0, count($items) - 1);
    }

    private function format(array $item): array
    {
        $status = (string)($item['status'] ?? 'enabled');

        return [
            'id' => (int)$item['id'],
            'company_name' => (string)$item['company_name'],
            'company_code' => (string)$item['company_code'],
            'status' => $status,
            'status_text' => $status === 'enabled' ? '启用中' : '已停用',
            'is_default' => (bool)$item['is_default'],
            'sort_order' => (int)$item['sort_order'],
            'remark' => (string)($item['remark'] ?? ''),
            'created_at' => (string)($item['created_at'] ?? ''),
            'updated_at' => (string)($item['updated_at'] ?? ''),
        ];
    }

    private function formatCatalog(array $item): array
    {
        $source = (string)($item['source'] ?? 'kuaidi100');
        $companyName = (string)($item['company_name'] ?? '');
        $companyCode = (string)($item['company_code'] ?? '');
        $companyType = (string)($item['company_type'] ?? '');

        return [
            'id' => (int)($item['id'] ?? 0),
            'company_name' => $companyName,
            'company_code' => $companyCode,
            'company_type' => $companyType,
            'display_text' => $companyType !== ''
                ? sprintf('%s / %s / %s', $companyName, $companyCode, $companyType)
                : sprintf('%s / %s', $companyName, $companyCode),
            'source' => $source,
            'synced_at' => (string)($item['synced_at'] ?? ''),
        ];
    }

    private function bootstrapDefaults(): void
    {
        $now = date('Y-m-d H:i:s');
        $defaults = [
            ['company_name' => '顺丰速运', 'company_code' => 'shunfeng', 'sort_order' => 1, 'is_default' => 1],
            ['company_name' => '京东快递', 'company_code' => 'jd', 'sort_order' => 2, 'is_default' => 0],
            ['company_name' => 'EMS', 'company_code' => 'ems', 'sort_order' => 3, 'is_default' => 0],
            ['company_name' => '中通快递', 'company_code' => 'zhongtong', 'sort_order' => 4, 'is_default' => 0],
            ['company_name' => '圆通速递', 'company_code' => 'yuantong', 'sort_order' => 5, 'is_default' => 0],
            ['company_name' => '申通快递', 'company_code' => 'shentong', 'sort_order' => 6, 'is_default' => 0],
            ['company_name' => '韵达快递', 'company_code' => 'yunda', 'sort_order' => 7, 'is_default' => 0],
            ['company_name' => '极兔速递', 'company_code' => 'jtexpress', 'sort_order' => 8, 'is_default' => 0],
        ];

        foreach ($defaults as $default) {
            $exists = Db::name('express_companies')->where('company_name', $default['company_name'])->find();
            if (!$exists) {
                Db::name('express_companies')->insert([
                    'company_name' => $default['company_name'],
                    'company_code' => $default['company_code'],
                    'status' => 'enabled',
                    'is_default' => $default['is_default'],
                    'sort_order' => $default['sort_order'],
                    'remark' => '系统默认快递公司',
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
                continue;
            }

            $currentCode = (string)($exists['company_code'] ?? '');
            if ($currentCode === '' || $currentCode === 'sf_express' || str_starts_with($currentCode, 'express_')) {
                Db::name('express_companies')->where('id', $exists['id'])->update([
                    'company_code' => $default['company_code'],
                    'updated_at' => $now,
                ]);
            }
        }

        $this->ensureEnabledDefault($now);
    }

    private function ensureEnabledDefault(string $now): void
    {
        $default = Db::name('express_companies')
            ->where('status', 'enabled')
            ->where('is_default', 1)
            ->find();
        if ($default) {
            return;
        }

        $firstEnabled = Db::name('express_companies')
            ->where('status', 'enabled')
            ->order('sort_order', 'asc')
            ->order('id', 'asc')
            ->find();
        if ($firstEnabled) {
            Db::name('express_companies')->where('id', $firstEnabled['id'])->update([
                'is_default' => 1,
                'updated_at' => $now,
            ]);
        }
    }

    private function backfillLocalCompanyCodesFromCatalog(string $now): int
    {
        $catalogRows = Db::name('kuaidi100_express_company_catalog')
            ->select()
            ->toArray();
        if (!$catalogRows) {
            return 0;
        }

        $catalogMap = [];
        foreach ($catalogRows as $row) {
            $name = trim((string)($row['company_name'] ?? ''));
            $code = trim((string)($row['company_code'] ?? ''));
            if ($name === '' || !$this->isValidCompanyCode($code)) {
                continue;
            }
            $catalogMap[$name] = $code;
        }

        if (!$catalogMap) {
            return 0;
        }

        $updated = 0;
        $rows = Db::name('express_companies')->select()->toArray();
        foreach ($rows as $row) {
            $companyName = trim((string)($row['company_name'] ?? ''));
            $currentCode = trim((string)($row['company_code'] ?? ''));
            if ($companyName === '' || !isset($catalogMap[$companyName])) {
                continue;
            }
            if (!$this->shouldBackfillCompanyCode($currentCode)) {
                continue;
            }

            Db::name('express_companies')->where('id', (int)$row['id'])->update([
                'company_code' => $catalogMap[$companyName],
                'updated_at' => $now,
            ]);
            $updated++;
        }

        return $updated;
    }

    private function shouldBackfillCompanyCode(string $currentCode): bool
    {
        return $currentCode === '' || $currentCode === 'sf_express' || str_starts_with($currentCode, 'express_');
    }

    private function localCompanyFallbackList(string $keyword, int $limit): array
    {
        $query = Db::name('express_companies')
            ->order('is_default', 'desc')
            ->order('sort_order', 'asc')
            ->order('id', 'asc');

        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->whereRaw('(company_name LIKE :keyword OR company_code LIKE :keyword)', [
                'keyword' => $like,
            ]);
        }

        $rows = $query->limit($limit)->select()->toArray();
        return array_map(static fn (array $item) => [
            'id' => (int)$item['id'],
            'company_name' => (string)$item['company_name'],
            'company_code' => (string)$item['company_code'],
            'company_type' => '本地快递公司',
            'source' => 'local',
            'synced_at' => '',
        ], $rows);
    }

    private function parseCatalogWorkbook(string $filePath): array
    {
        $zip = new \ZipArchive();
        if ($zip->open($filePath) !== true) {
            throw new \RuntimeException('快递100公司码表压缩包打开失败');
        }

        $sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
        if (!is_string($sheetXml) || $sheetXml === '') {
            $zip->close();
            throw new \RuntimeException('快递100公司码表缺少工作表数据');
        }

        $sharedStrings = [];
        $sharedStringsXml = $zip->getFromName('xl/sharedStrings.xml');
        if (is_string($sharedStringsXml) && $sharedStringsXml !== '') {
            $sharedStrings = $this->readSharedStrings($sharedStringsXml);
        }

        $zip->close();

        $xml = simplexml_load_string($sheetXml, 'SimpleXMLElement', LIBXML_NOCDATA);
        if ($xml === false) {
            throw new \RuntimeException('快递100公司码表工作表解析失败');
        }

        $xml->registerXPathNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $rows = $xml->xpath('//x:sheetData/x:row') ?: [];

        $headerMap = [];
        $parsedRows = [];
        foreach ($rows as $row) {
            $values = [];
            foreach ($row->c as $cell) {
                $columnIndex = $this->columnIndexFromCellRef((string)$cell['r']);
                if ($columnIndex <= 0) {
                    continue;
                }

                $values[$columnIndex] = $this->readXlsxCellValue($cell, $sharedStrings);
            }

            if (!$values) {
                continue;
            }

            if (!$headerMap) {
                $headerMap = $this->buildCatalogHeaderMap($values);
                if ($headerMap) {
                    continue;
                }
            }

            $companyName = trim((string)($values[$headerMap['company_name'] ?? 1] ?? ''));
            $companyCode = trim((string)($values[$headerMap['company_code'] ?? 2] ?? ''));
            $companyType = trim((string)($values[$headerMap['company_type'] ?? 3] ?? ''));

            if ($companyName === '' || !$this->isValidCompanyCode($companyCode)) {
                continue;
            }

            $parsedRows[] = [
                'company_name' => $companyName,
                'company_code' => $companyCode,
                'company_type' => $companyType,
                'sort_order' => count($parsedRows) + 1,
            ];
        }

        return $parsedRows;
    }

    private function buildCatalogHeaderMap(array $values): array
    {
        $map = [];
        foreach ($values as $columnIndex => $value) {
            $text = trim((string)$value);
            if ($text === '') {
                continue;
            }

            if (in_array($text, ['公司名称', '快递公司名称', '名称'], true)) {
                $map['company_name'] = $columnIndex;
            } elseif (in_array($text, ['公司编码', '快递公司编码', '编码', 'code'], true)) {
                $map['company_code'] = $columnIndex;
            } elseif (in_array($text, ['公司类型', '类型'], true)) {
                $map['company_type'] = $columnIndex;
            }
        }

        if (!$map) {
            return [];
        }

        $map['company_name'] ??= 1;
        $map['company_code'] ??= 2;
        $map['company_type'] ??= 3;

        return $map;
    }

    private function readSharedStrings(string $xml): array
    {
        $document = simplexml_load_string($xml, 'SimpleXMLElement', LIBXML_NOCDATA);
        if ($document === false) {
            throw new \RuntimeException('快递100公司码表共享字符串解析失败');
        }

        $document->registerXPathNamespace('x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
        $items = $document->xpath('//x:si') ?: [];
        $values = [];
        foreach ($items as $item) {
            $text = '';
            foreach ($item->xpath('.//x:t') ?: [] as $textNode) {
                $text .= (string)$textNode;
            }
            $values[] = $text;
        }

        return $values;
    }

    private function readXlsxCellValue(\SimpleXMLElement $cell, array $sharedStrings): string
    {
        $type = (string)$cell['t'];
        if ($type === 'inlineStr') {
            $text = '';
            foreach ($cell->xpath('.//*[local-name()="t"]') ?: [] as $textNode) {
                $text .= (string)$textNode;
            }

            return trim($text);
        }

        if ($type === 's') {
            $index = (int)($cell->v ?? 0);
            return trim((string)($sharedStrings[$index] ?? ''));
        }

        if (isset($cell->v)) {
            return trim((string)$cell->v);
        }

        return trim((string)$cell);
    }

    private function columnIndexFromCellRef(string $cellRef): int
    {
        if (!preg_match('/^([A-Z]+)\d+$/i', $cellRef, $matches)) {
            return 0;
        }

        $letters = strtoupper($matches[1]);
        $index = 0;
        $length = strlen($letters);
        for ($i = 0; $i < $length; $i++) {
            $index = $index * 26 + (ord($letters[$i]) - 64);
        }

        return $index;
    }

    private function isValidCompanyCode(string $code): bool
    {
        return $code !== '' && preg_match('/^[a-z0-9]+$/', $code) === 1 && !str_starts_with($code, 'express_');
    }

    private function companyAliasCode(string $companyName): string
    {
        $aliases = [
            '顺丰速运' => 'shunfeng',
            '顺丰' => 'shunfeng',
            'sf_express' => 'shunfeng',
            '京东快递' => 'jd',
            '京东物流' => 'jd',
            'EMS' => 'ems',
            'ems' => 'ems',
            '中通快递' => 'zhongtong',
            '圆通速递' => 'yuantong',
            '圆通快递' => 'yuantong',
            '申通快递' => 'shentong',
            '韵达快递' => 'yunda',
            '极兔速递' => 'jtexpress',
            '极兔快递' => 'jtexpress',
            '德邦快递' => 'debangwuliu',
            '邮政快递包裹' => 'youzhengguonei',
        ];

        return $aliases[$companyName] ?? '';
    }

    private function companyAliasName(string $companyCode): string
    {
        $aliases = [
            'shunfeng' => '顺丰速运',
            'sf_express' => '顺丰速运',
            'jd' => '京东快递',
            'ems' => 'EMS',
            'zhongtong' => '中通快递',
            'yuantong' => '圆通速递',
            'shentong' => '申通快递',
            'yunda' => '韵达快递',
            'jtexpress' => '极兔速递',
            'debangwuliu' => '德邦快递',
            'youzhengguonei' => '邮政快递包裹',
        ];

        return $aliases[$companyCode] ?? '';
    }

    private function ensureTable(): void
    {
        Db::execute(<<<'SQL'
CREATE TABLE IF NOT EXISTS express_companies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_name VARCHAR(64) NOT NULL DEFAULT '',
  company_code VARCHAR(64) NOT NULL DEFAULT '',
  status VARCHAR(32) NOT NULL DEFAULT 'enabled',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  remark VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_express_companies_name (company_name),
  UNIQUE KEY uk_express_companies_code (company_code),
  KEY idx_express_companies_status (status),
  KEY idx_express_companies_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='快递公司字典'
SQL);
    }

    private function ensureCatalogTable(): void
    {
        Db::execute(<<<'SQL'
CREATE TABLE IF NOT EXISTS kuaidi100_express_company_catalog (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_name VARCHAR(64) NOT NULL DEFAULT '',
  company_code VARCHAR(64) NOT NULL DEFAULT '',
  company_type VARCHAR(64) NOT NULL DEFAULT '',
  sort_order INT NOT NULL DEFAULT 0,
  synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_kuaidi100_express_company_catalog_code (company_code),
  KEY idx_kuaidi100_express_company_catalog_name (company_name),
  KEY idx_kuaidi100_express_company_catalog_type (company_type),
  KEY idx_kuaidi100_express_company_catalog_synced_at (synced_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='快递100官方公司码表'
SQL);
    }
}