szkp-map-service/app/Services/VenueImportService.php

<?php

namespace App\Services;

use App\Models\DictItem;
use App\Models\User;
use App\Models\Venue;
use Illuminate\Support\Facades\Validator;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class VenueImportService
{
    /** 非富文本的说明类字段：导入时去除全部空白（含换行、不间断空格等） */
    private function squishPlain(?string $s): ?string
    {
        if ($s === null || $s === '') {
            return null;
        }
        $t = preg_replace('/\s+/u', '', $s) ?? '';
        if ($t === '') {
            return null;
        }

        return $t;
    }

    /** @var array<string, string>|null 主题：item_label => item_value（与数据字典 venue_type 同步，按请求缓存） */
    private ?array $themeLabelToValueMapCache = null;

    /** @var array<string, string>|null 开放模式：item_label => item_value（与数据字典 venue_open_mode 同步，按请求缓存） */
    private ?array $openModeLabelToValueMapCache = null;

    private const TICKET_TYPE_LABEL = [
        '免费' => 'free',
        '收费' => 'paid',
    ];

    private const APPOINTMENT_LABEL = [
        '仅团队' => 'team_only',
        '个人团队均可' => 'individual_and_team',
    ];

    private const BOOKING_MODE_LABEL = [
        '仅团队预约' => 'team_only',
        '个人团队均需预约' => 'all_required',
        '个人不需预约团队需预约' => 'team_required',
    ];

    /**
     * @return array<string, string> 中文标签 => item_value
     */
    private function themeLabelToValueMap(): array
    {
        if ($this->themeLabelToValueMapCache !== null) {
            return $this->themeLabelToValueMapCache;
        }

        $this->themeLabelToValueMapCache = DictItem::query()
            ->where('dict_type', 'venue_type')
            ->where('is_active', true)
            ->orderBy('sort')
            ->orderBy('id')
            ->get(['item_label', 'item_value'])
            ->mapWithKeys(fn ($r) => [trim((string) $r->item_label) => (string) $r->item_value])
            ->all();

        return $this->themeLabelToValueMapCache;
    }

    /**
     * @return array<string, string> 中文标签 => item_value（与后台数据字典「场馆开放模式」一致，勿硬编码以免改文案后导入失败）
     */
    private function openModeLabelToValueMap(): array
    {
        if ($this->openModeLabelToValueMapCache !== null) {
            return $this->openModeLabelToValueMapCache;
        }

        $this->openModeLabelToValueMapCache = DictItem::query()
            ->where('dict_type', 'venue_open_mode')
            ->where('is_active', true)
            ->orderBy('sort')
            ->orderBy('id')
            ->get(['item_label', 'item_value'])
            ->mapWithKeys(fn ($r) => [trim((string) $r->item_label) => (string) $r->item_value])
            ->all();

        return $this->openModeLabelToValueMapCache;
    }

    public function buildTemplateSpreadsheet(): Spreadsheet
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('导入数据');

        // 列顺序与后台「新增/编辑场馆」表单一致（不含封面、轮播、地图选点）
        $headers = [
            '场馆名称*',
            '主题*',
            '行政区*',
            '预约类型',
            '门票类型',
            '预约模式',
            '开放模式',
            '所属单位',
            '预约方式',
            '参观形式',
            '开放时间',
            '咨询预约时间',
            '咨询预约联系电话',
            '排序',
            '启用',
            '纳入人数统计',
            '场馆地址',
            '经度',
            '纬度',
            '门票说明',
            '场馆详情',
            '预约须知',
        ];
        foreach (range(1, count($headers)) as $i) {
            $col = Coordinate::stringFromColumnIndex($i);
            $sheet->setCellValue($col . '1', $headers[$i - 1]);
        }
        $sheet->setCellValue(
            'A2',
            '说明：主题多个请用英文逗号分隔；启用、纳入人数统计填「是」或「否」；经纬度可留空，新建场馆导入后可在列表中通过「地图选点」填写；经度与纬度须成对填写或同时留空。与已有场馆「场馆名称」完全相同时，按更新处理且不覆盖已保存的经纬度。'
        );

        $opt = $spreadsheet->createSheet();
        $opt->setTitle('选项');

        $districts = DictItem::query()
            ->where('dict_type', 'district')
            ->where('is_active', true)
            ->orderBy('sort')
            ->orderBy('id')
            ->pluck('item_label')
            ->all();
        $opt->setCellValue('A1', '行政区');
        $r = 2;
        foreach ($districts as $d) {
            $opt->setCellValue('A' . $r, $d);
            $r++;
        }
        $lastD = max(2, $r - 1);

        $opt->setCellValue('B1', '主题');
        $tr = 2;
        foreach (array_keys($this->themeLabelToValueMap()) as $label) {
            $opt->setCellValue('B' . $tr, $label);
            $tr++;
        }
        $lastT = max(2, $tr - 1);

        $opt->setCellValue('C1', '门票类型');
        $opt->setCellValue('C2', '免费');
        $opt->setCellValue('C3', '收费');

        $opt->setCellValue('D1', '预约类型');
        $opt->setCellValue('D2', '仅团队');
        $opt->setCellValue('D3', '个人团队均可');

        $opt->setCellValue('E1', '预约模式');
        $opt->setCellValue('E2', '仅团队预约');
        $opt->setCellValue('E3', '个人团队均需预约');
        $opt->setCellValue('E4', '个人不需预约团队需预约');

        $opt->setCellValue('F1', '开放模式');
        $fr = 2;
        foreach (array_keys($this->openModeLabelToValueMap()) as $openLabel) {
            $opt->setCellValue('F' . $fr, $openLabel);
            $fr++;
        }
        $lastF = max(2, $fr - 1);

        $opt->setCellValue('G1', '是否');
        $opt->setCellValue('G2', '是');
        $opt->setCellValue('G3', '否');

        // B=主题 C=行政区 D=预约类型 E=门票类型 F=预约模式 G=开放模式 O=启用 P=纳入人数统计（与表头列位一致）
        $this->applyListValidation($sheet, 'B2:B5000', '=选项!$B$2:$B$' . $lastT);  // 主题
        $this->applyListValidation($sheet, 'C2:C5000', '=选项!$A$2:$A$' . $lastD);  // 行政区
        $this->applyListValidation($sheet, 'D2:D5000', '=选项!$D$2:$D$3');          // 预约类型
        $this->applyListValidation($sheet, 'E2:E5000', '=选项!$C$2:$C$3');          // 门票类型
        $this->applyListValidation($sheet, 'F2:F5000', '=选项!$E$2:$E$4');          // 预约模式
        $this->applyListValidation($sheet, 'G2:G5000', '=选项!$F$2:$F$' . $lastF);  // 开放模式（与字典 label 一致）
        $this->applyListValidation($sheet, 'O2:O5000', '=选项!$G$2:$G$3');            // 启用
        $this->applyListValidation($sheet, 'P2:P5000', '=选项!$G$2:$G$3');            // 纳入人数统计

        $spreadsheet->setActiveSheetIndex(0);

        return $spreadsheet;
    }

    private function applyListValidation(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, string $range, string $formula): void
    {
        $validation = new DataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation->setAllowBlank(true);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setFormula1($formula);
        $sheet->setDataValidation($range, $validation);
    }

    /**
     * @return array{rows: array<int, array<string, mixed>>, summary: array{total: int, valid: int, invalid: int}}
     */
    public function previewFromPath(string $path): array
    {
        $spreadsheet = IOFactory::load($path);
        $sheet = $spreadsheet->getSheet(0);
        $highestRow = (int) $sheet->getHighestRow();
        $out = [];
        $valid = 0;
        $invalid = 0;

        for ($row = 2; $row <= $highestRow; $row++) {
            $cells = [];
            for ($col = 1; $col <= 22; $col++) {
                $colLetter = Coordinate::stringFromColumnIndex($col);
                $cells[] = trim((string) $sheet->getCell($colLetter . $row)->getValue());
            }
            if ($this->rowIsEmpty($cells)) {
                continue;
            }
            $parsed = $this->normalizeRowFromCells($cells);
            $errors = $this->validatePayload($parsed);
            $ok = $errors === [];
            if ($ok) {
                $valid++;
            } else {
                $invalid++;
            }
            $importAction = null;
            if ($ok) {
                $name = trim((string) ($parsed['name'] ?? ''));
                if ($name !== '' && Venue::query()->where('name', $name)->exists()) {
                    $importAction = 'update';
                } else {
                    $importAction = 'create';
                }
            }
            $out[] = [
                'row_index' => $row,
                'ok' => $ok,
                'errors' => $errors,
                'payload' => $parsed,
                'import_action' => $importAction,
            ];
        }

        return [
            'rows' => $out,
            'summary' => [
                'total' => count($out),
                'valid' => $valid,
                'invalid' => $invalid,
            ],
        ];
    }

    /**
     * @param  array<int, string>  $cells
     * @return array<string, mixed>
     */
    public function normalizeRowFromCells(array $cells): array
    {
        $g = fn (int $i) => isset($cells[$i]) ? trim($cells[$i]) : '';

        $themeRaw = str_replace('，', ',', $g(1));
        $themeParts = array_filter(array_map('trim', explode(',', $themeRaw)), fn ($s) => $s !== '');
        $themeMap = $this->themeLabelToValueMap();
        $venueTypes = [];
        foreach ($themeParts as $part) {
            if (isset($themeMap[$part])) {
                $venueTypes[] = $themeMap[$part];
            }
        }

        // 与表头：3 预约类型、4 门票类型、5 预约模式、6 开放模式 一致（从 0 起算列索引）
        $rawApp = $g(3);
        $rawTicket = $g(4);
        $rawBookingMode = $g(5);
        $rawOpen = $g(6);
        $ticketType = $rawTicket === '' ? null : (self::TICKET_TYPE_LABEL[$rawTicket] ?? null);
        $appointmentType = $rawApp === '' ? null : (self::APPOINTMENT_LABEL[$rawApp] ?? null);
        $bookingMode = $rawBookingMode === '' ? null : (self::BOOKING_MODE_LABEL[$rawBookingMode] ?? null);
        $openModeMap = $this->openModeLabelToValueMap();
        $openMode = $rawOpen === '' ? null : ($openModeMap[$rawOpen] ?? null);

        $lng = $this->parseFloat($g(17));
        $lat = $this->parseFloat($g(18));
        $sort = $g(13) === '' ? 0 : (int) $g(13);

        $district = trim($g(2));

        return [
            'name' => $g(0),
            'venue_types' => $venueTypes,
            'district' => $district,
            'appointment_type' => $appointmentType,
            'ticket_type' => $ticketType,
            'booking_mode' => $bookingMode,
            'open_mode' => $openMode,
            'unit_name' => $this->squishPlain($g(7) === '' ? null : $g(7)),
            'booking_method' => $g(8) === '' ? null : $g(8),
            'visit_form' => $this->squishPlain($g(9) === '' ? null : $g(9)),
            'open_time' => $this->squishPlain($g(10) === '' ? null : $g(10)),
            'consultation_hours' => $this->squishPlain($g(11) === '' ? null : $g(11)),
            'contact_phone' => $this->squishPlain($g(12) === '' ? null : $g(12)),
            'sort' => $sort,
            // 启用：留空按「是」与常见新建习惯；纳入人数统计：留空须为「否」，否则全表空单元格会全部被 parse 成 true
            'is_active' => $this->parseYesNoWithEmptyDefault($g(14), true),
            'is_included_in_stats' => $this->parseYesNoWithEmptyDefault($g(15), false),
            'address' => $this->squishPlain($g(16) === '' ? null : $g(16)),
            'lng' => $lng,
            'lat' => $lat,
            'ticket_content' => $g(19) === '' ? null : $g(19),
            'detail_html' => $g(20) === '' ? null : $g(20),
            'reservation_notice' => $g(21) === '' ? null : $g(21),
            '_raw_appointment_label' => $rawApp,
            '_raw_booking_mode_label' => $rawBookingMode,
            '_raw_open_mode_label' => $rawOpen,
            '_raw_ticket_type_label' => $rawTicket,
        ];
    }

    /**
     * @param  array<string, mixed>  $data
     * @return list<string>
     */
    public function validatePayload(array $data): array
    {
        $errors = [];
        if (trim((string) ($data['name'] ?? '')) === '') {
            $errors[] = '场馆名称不能为空';
        }
        if (empty($data['venue_types']) || ! is_array($data['venue_types'])) {
            $errors[] = '主题无效或为空（须为模板「选项」表中的主题名称，多个用英文逗号分隔）';
        }
        if (trim((string) ($data['district'] ?? '')) === '') {
            $errors[] = '行政区不能为空';
        }
        $lng = $data['lng'] ?? null;
        $lat = $data['lat'] ?? null;
        $hasPartialCoord = ($lng === null) !== ($lat === null);
        if ($hasPartialCoord) {
            $errors[] = '经度与纬度须同时填写或同时留空（留空时请在列表编辑中通过地图选点补充）';
        }
        if (($data['_raw_ticket_type_label'] ?? '') !== '' && ($data['ticket_type'] ?? null) === null) {
            $errors[] = '门票类型须为「免费」或「收费」或留空';
        }
        if (($data['_raw_appointment_label'] ?? '') !== '' && ($data['appointment_type'] ?? null) === null) {
            $errors[] = '预约类型须为「仅团队」「个人团队均可」或留空';
        }
        if (($data['_raw_open_mode_label'] ?? '') !== '' && ($data['open_mode'] ?? null) === null) {
            $errors[] = '开放模式须与数据字典「场馆开放模式」中的选项名称一致，或留空（可重新下载模板查看当前选项）';
        }
        if (($data['_raw_booking_mode_label'] ?? '') !== '' && ($data['booking_mode'] ?? null) === null) {
            $errors[] = '预约模式须为三种选项之一或留空';
        }

        $v = Validator::make($this->stripInternalKeys($data), [
            'name' => ['required', 'string', 'max:120'],
            'venue_types' => ['required', 'array', 'min:1'],
            'venue_types.*' => ['string', 'max:80'],
            'unit_name' => ['nullable', 'string', 'max:120'],
            'district' => ['required', 'string', 'max:80'],
            'ticket_type' => ['nullable', 'string', 'max:80'],
            'appointment_type' => ['nullable', 'string', 'max:40'],
            'booking_mode' => ['nullable', 'string', 'max:50'],
            'open_mode' => ['nullable', 'string', 'max:40'],
            'open_time' => ['nullable', 'string', 'max:65535'],
            'ticket_content' => ['nullable', 'string'],
            'booking_method' => ['nullable', 'string'],
            'visit_form' => ['nullable', 'string'],
            'consultation_hours' => ['nullable', 'string'],
            'detail_html' => ['nullable', 'string'],
            'reservation_notice' => ['nullable', 'string'],
            'address' => ['nullable', 'string', 'max:255'],
            'contact_phone' => ['nullable', 'string', 'max:255'],
            'lat' => ['nullable', 'numeric'],
            'lng' => ['nullable', 'numeric'],
            'sort' => ['nullable', 'integer', 'min:0'],
            'is_active' => ['boolean'],
            'is_included_in_stats' => ['boolean'],
        ]);
        if ($v->fails()) {
            foreach ($v->errors()->all() as $msg) {
                $errors[] = $msg;
            }
        }

        return array_values(array_unique($errors));
    }

    /**
     * @param  array<string, mixed>  $data
     * @return array<string, mixed>
     */
    public function stripInternalKeys(array $data): array
    {
        foreach (array_keys($data) as $k) {
            if (is_string($k) && str_starts_with($k, '_')) {
                unset($data[$k]);
            }
        }

        return $data;
    }

    /**
     * 导入单行：与已有场馆「名称」完全相同时更新，否则新增；更新时不写入经纬度，保留库中原值。
     *
     * @return array{action: 'created'|'updated', venue: Venue}
     */
    public function importRowFromPayload(array $payload, User $user): array
    {
        $clean = $this->stripInternalKeys($payload);
        $name = trim((string) ($clean['name'] ?? ''));
        $existing = $name === '' ? null : Venue::query()->where('name', $name)->orderBy('id')->first();
        if ($existing !== null) {
            return [
                'action' => 'updated',
                'venue' => $this->updateVenueFromImportPayload($existing, $clean, $user),
            ];
        }

        return [
            'action' => 'created',
            'venue' => $this->createVenueFromPayload($clean, $user),
        ];
    }

    /**
     * @param  array<string, mixed>  $payload
     */
    public function createVenueFromPayload(array $payload, User $user): Venue
    {
        $clean = $this->stripInternalKeys($payload);
        $auditStatus = $user->isSuperAdmin() ? Venue::AUDIT_APPROVED : Venue::AUDIT_PENDING;
        $venue = Venue::create($clean + [
            'cover_image' => null,
            'gallery_media' => [],
            'detail_html' => $clean['detail_html'] ?? null,
            'live_people_count' => 0,
            'audit_status' => $auditStatus,
            'audit_remark' => null,
            'last_approved_snapshot' => null,
        ]);

        if (! $user->isSuperAdmin()) {
            $user->venues()->syncWithoutDetaching([$venue->id]);
        }

        return $venue->fresh();
    }

    /**
     * 导入覆盖已有场馆；不含经纬度、封面、轮播、在馆人数等仅后台维护字段。
     *
     * @param  array<string, mixed>  $clean
     */
    public function updateVenueFromImportPayload(Venue $venue, array $clean, User $user): Venue
    {
        $data = $clean;
        unset($data['lat'], $data['lng']);
        $venue->fill($data);
        if ($user->isSuperAdmin()) {
            $venue->audit_status = Venue::AUDIT_APPROVED;
            $venue->audit_remark = null;
            $venue->last_approved_snapshot = null;
        } else {
            $venue->audit_status = Venue::AUDIT_PENDING;
            $venue->audit_remark = null;
        }
        $venue->save();

        return $venue->fresh();
    }

    private function rowIsEmpty(array $cells): bool
    {
        foreach ($cells as $c) {
            if (trim((string) $c) !== '') {
                return false;
            }
        }

        return true;
    }

    private function parseFloat(?string $s): ?float
    {
        if ($s === null || trim($s) === '') {
            return null;
        }
        $n = (float) str_replace([',', ' '], '', $s);

        return is_finite($n) ? $n : null;
    }

    /**
     * 解析「是/否」类单元格。空串或无法识别时由 $defaultWhenEmpty 决定，避免与「启用」不同列共用同一套默认导致误纳入统计。
     */
    private function parseYesNoWithEmptyDefault(string $s, bool $defaultWhenEmpty): bool
    {
        $t = mb_strtolower(trim($s), 'UTF-8');
        if ($t === '') {
            return $defaultWhenEmpty;
        }
        if (in_array($t, ['1', '是', 'y', 'yes', 'true', '启用'], true)) {
            return true;
        }
        if (in_array($t, ['0', '否', 'n', 'no', 'false', '禁用'], true)) {
            return false;
        }

        return $defaultWhenEmpty;
    }
}