app/Service/business/CatalogueService.php

<?php

namespace App\Service\business;

use App\BaseApp\BaseService;
use App\Models\business\CatalogueModel;
use App\Models\business\CategoryModel;
use App\Models\business\ImageModel;
use App\Models\business\PriceSheetModel;
use App\Service\common\MediaUrlService;
use Exception;
use Illuminate\Support\Facades\DB;
use Psr\Container\ContainerExceptionInterface;
use Psr\Container\NotFoundExceptionInterface;

/**
 * 商品图册
 */
class CatalogueService extends BaseService
{
    private MediaUrlService $media;

    public function __construct()
    {
        parent::__construct();
        $this->model = CatalogueModel::class;
        $this->selectField = ['id', 'title', 'category_id', 'cover', 'pdf', 'video', 'price', 'alias', 'identifier', 'status', 'created_at', 'updated_at'];
        $this->queryField = ['title' => 'like', 'alias' => 'like', 'identifier' => 'like', 'status' => '='];
        $this->media = MediaUrlService::getInstance();
    }

    /**
     * 列表
     *
     * 两处沿用老行为：
     *   1. 按分类筛选时连同该分类的子分类一起查，否则选了父分类会一条都搜不到
     *   2. 报价单里 6 个历史材质列绝大多数为空，只把有值的列名回给前端（show_field），
     *      前端据此决定表格显示哪几列
     * @throws ContainerExceptionInterface
     * @throws NotFoundExceptionInterface
     */
    public function list(): array
    {
        $this->with = ['category', 'priceSheet'];

        $categoryId = (int) request()->get('category_id', 0);
        if ($categoryId > 0) {
            $ids = CategoryModel::where('pid', $categoryId)->where('deleted_at', 0)->pluck('id')->all();
            $ids[] = $categoryId;
            $this->whereIn = ['category_id', $ids];
        }

        $result = $this->getPageList();
        foreach ($result['items'] as &$item) {
            $item['category_name'] = $item['category']['name'] ?? '';
            unset($item['category']);
            $item['cover'] = $this->media->toPublic($item['cover'] ?? '');
            $item['pdf'] = $this->media->toPublic($item['pdf'] ?? '');
            $item['video'] = $this->media->toPublic($item['video'] ?? '');
            $item['show_field'] = $this->pickUsedMaterialFields($item['price_sheet'] ?? []);
        }
        unset($item);
        return $result;
    }

    public function option(): mixed
    {
        $this->optionField = ['id', 'title as name', 'identifier'];
        return $this->getOption();
    }

    /**
     * 详情，带规格与两类相册
     * @throws Exception
     */
    public function detail($id): mixed
    {
        $info = $this->getDetail($id);
        $info->cover = $this->media->toPublic($info->cover);
        $info->pdf = $this->media->toPublic($info->pdf);
        $info->video = $this->media->toPublic($info->video);

        $priceSheet = PriceSheetModel::where('catalogue_id', $id)
            ->where('deleted_at', 0)
            ->orderBy('id')
            ->get()
            ->toArray();
        $info->show_field = $this->pickUsedMaterialFields($priceSheet);
        $info->price_sheet = $priceSheet;
        $info->render_images = $this->imagesOf($id, ImageModel::TYPE_RENDER);
        $info->physical_images = $this->imagesOf($id, ImageModel::TYPE_PHYSICAL);
        return $info;
    }

    public function create($params): mixed
    {
        $params = $this->normalize($params);
        $this->assertIdentifierUnique((string) ($params['identifier'] ?? ''));
        return $this->insert($params);
    }

    /**
     * @throws Exception
     */
    public function update($id, $params): mixed
    {
        $params = $this->normalize($params);
        if (array_key_exists('identifier', $params)) {
            $this->assertIdentifierUnique((string) $params['identifier'], (int) $id);
        }
        return $this->save($id, $params);
    }

    /**
     * 删除商品：连带软删它的规格与相册，否则会留下一堆查不到主体的孤儿数据
     * @throws Exception
     */
    public function delete($id): mixed
    {
        $ids = array_values(array_filter(array_map('intval', is_array($id) ? $id : [$id])));
        if (empty($ids)) {
            $this->utils->errorThrow('参数错误');
        }
        $now = time();
        PriceSheetModel::whereIn('catalogue_id', $ids)->where('deleted_at', 0)
            ->update(['deleted_at' => $now, 'updated_at' => $now]);
        ImageModel::whereIn('catalogue_id', $ids)->where('deleted_at', 0)
            ->update(['deleted_at' => $now, 'updated_at' => $now]);
        return $this->del($ids);
    }

    /**
     * @throws Exception
     */
    public function status($id, $status): mixed
    {
        return $this->save($id, ['status' => (int) $status]);
    }

    /**
     * 导出商品 + 关联报价单：两个工作表，xlsx 由前端 ExcelJS 美化
     */
    public function exportExcel(): array
    {
        $rows = CatalogueModel::where('deleted_at', 0)->orderByDesc('id')->get([
            'id', 'title', 'identifier', 'alias', 'category_id', 'price', 'status',
        ])->toArray();
        $byId = [];
        foreach ($rows as &$row) {
            $row['status'] = (int) ($row['status'] ?? 0) === 0 ? '上架' : '下架';
            $byId[(int) $row['id']] = $row;
        }
        unset($row);

        $ids = array_keys($byId);
        $priceRows = [];
        if (!empty($ids)) {
            $sheets = PriceSheetModel::where('deleted_at', 0)
                ->whereIn('catalogue_id', $ids)
                ->orderBy('catalogue_id')
                ->orderBy('id')
                ->get(['id', 'catalogue_id', 'specification', 'dimension', 'routine', 'stock', 'status'])
                ->toArray();
            foreach ($sheets as $sheet) {
                $cat = $byId[(int) $sheet['catalogue_id']] ?? [];
                $priceRows[] = [
                    'id' => $sheet['id'],
                    'catalogue_id' => $sheet['catalogue_id'],
                    'identifier' => $cat['identifier'] ?? '',
                    'title' => $cat['title'] ?? '',
                    'specification' => $sheet['specification'] ?? '',
                    'dimension' => $sheet['dimension'] ?? '',
                    'routine' => $sheet['routine'] ?? '',
                    'stock' => (int) ($sheet['stock'] ?? -1),
                    'status' => (int) ($sheet['status'] ?? 0) === 0 ? '启用' : '禁用',
                ];
            }
        }

        return [
            'filename' => 'catalogue.xlsx',
            'title' => '商品图册',
            'sheets' => [
                [
                    'title' => '商品图册',
                    'sheet' => '商品',
                    'columns' => [
                        ['key' => 'id', 'header' => 'ID', 'width' => 10, 'align' => 'center'],
                        ['key' => 'title', 'header' => '商品名称', 'width' => 28],
                        ['key' => 'identifier', 'header' => '商品编号', 'width' => 16],
                        ['key' => 'alias', 'header' => '别名', 'width' => 24],
                        ['key' => 'category_id', 'header' => '分类ID', 'width' => 12, 'align' => 'center'],
                        ['key' => 'price', 'header' => '参考价', 'width' => 14, 'align' => 'right'],
                        ['key' => 'status', 'header' => '状态', 'width' => 10, 'align' => 'center'],
                    ],
                    'rows' => $rows,
                ],
                [
                    'title' => '关联报价单',
                    'sheet' => '报价单',
                    'subtitle' => '按商品编号回写；库存 -1 表示不限',
                    'columns' => [
                        ['key' => 'id', 'header' => 'ID', 'width' => 10, 'align' => 'center'],
                        ['key' => 'catalogue_id', 'header' => '商品ID', 'width' => 12, 'align' => 'center'],
                        ['key' => 'identifier', 'header' => '商品编号', 'width' => 16],
                        ['key' => 'title', 'header' => '商品名称', 'width' => 24],
                        ['key' => 'specification', 'header' => '规格', 'width' => 20],
                        ['key' => 'dimension', 'header' => '尺寸', 'width' => 16],
                        ['key' => 'routine', 'header' => '常规价', 'width' => 14, 'align' => 'right'],
                        ['key' => 'stock', 'header' => '库存', 'width' => 10, 'align' => 'center'],
                        ['key' => 'status', 'header' => '状态', 'width' => 10, 'align' => 'center'],
                    ],
                    'rows' => $priceRows,
                ],
            ],
        ];
    }

    /**
     * 导入商品 + 报价单：先落商品，再按编号挂规格
     */
    public function importRows(array $rows, array $priceSheets = []): array
    {
        $created = 0;
        $updated = 0;
        $priceCreated = 0;
        $priceUpdated = 0;
        $now = time();
        DB::connection('business')->beginTransaction();
        try {
            foreach ($rows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $identifier = trim((string) ($row['identifier'] ?? $row['商品编号'] ?? ''));
                $title = trim((string) ($row['title'] ?? $row['商品名称'] ?? ''));
                if ($title === '') {
                    continue;
                }
                $payload = [
                    'title' => $title,
                    'identifier' => $identifier,
                    'alias' => (string) ($row['alias'] ?? $row['别名'] ?? ''),
                    'category_id' => (int) ($row['category_id'] ?? $row['分类ID'] ?? 0),
                    'price' => (string) ($row['price'] ?? $row['参考价'] ?? ''),
                    'status' => $this->parseStatus($row['status'] ?? $row['状态'] ?? 0),
                    'updated_at' => $now,
                ];
                $exists = $identifier !== ''
                    ? CatalogueModel::where('identifier', $identifier)->where('deleted_at', 0)->first()
                    : null;
                if (!empty($exists)) {
                    CatalogueModel::where('id', $exists['id'])->update($payload);
                    $updated++;
                } else {
                    $payload['created_at'] = $now;
                    CatalogueModel::insert($payload);
                    $created++;
                }
            }
            [$priceCreated, $priceUpdated] = $this->importPriceSheets($priceSheets, $now);
            DB::connection('business')->commit();
        } catch (Exception $e) {
            DB::connection('business')->rollBack();
            $this->utils->errorThrow($e->getMessage());
        }
        return [
            'created' => $created,
            'updated' => $updated,
            'price_created' => $priceCreated,
            'price_updated' => $priceUpdated,
        ];
    }

    /**
     * 报价单里真正有值的材质列
     */
    private function pickUsedMaterialFields(array $priceSheetRows): array
    {
        $used = [];
        foreach ($priceSheetRows as $row) {
            foreach (PriceSheetModel::MATERIAL_FIELDS as $field) {
                if (!empty($row[$field] ?? '')) {
                    $used[$field] = true;
                }
            }
        }
        return array_keys($used);
    }

    private function imagesOf(int|string $catalogueId, int $type): array
    {
        $rows = ImageModel::where('catalogue_id', $catalogueId)
            ->where('type', $type)
            ->where('deleted_at', 0)
            ->orderBy('id')
            ->get(['id', 'url', 'type'])
            ->toArray();
        $this->media->publicEach($rows, ['url']);
        return $rows;
    }

    private function normalize(array $params): array
    {
        foreach (['cover', 'pdf', 'video'] as $field) {
            if (array_key_exists($field, $params)) {
                $params[$field] = $this->media->firstOf($params[$field]);
            }
        }
        return $params;
    }

    /**
     * 商品编号是小程序搜索和线下对单的依据，重复了就没法定位货品
     * @throws Exception
     */
    private function assertIdentifierUnique(string $identifier, int $exceptId = 0): void
    {
        $identifier = trim($identifier);
        if ($identifier === '') {
            return;
        }
        $exists = CatalogueModel::where('identifier', $identifier)
            ->where('deleted_at', 0)
            ->when($exceptId > 0, fn ($q) => $q->where('id', '<>', $exceptId))
            ->exists();
        if ($exists) {
            $this->utils->errorThrow('商品编号已存在：' . $identifier);
        }
    }

    /**
     * 导入报价单：优先商品编号，其次商品ID；同商品同规格同尺寸则更新
     */
    private function importPriceSheets(array $rows, int $now): array
    {
        $created = 0;
        $updated = 0;
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $catalogueId = $this->resolveCatalogueId($row);
            $specification = trim((string) ($row['specification'] ?? $row['规格'] ?? ''));
            if ($catalogueId <= 0 || $specification === '') {
                continue;
            }
            $dimension = (string) ($row['dimension'] ?? $row['尺寸'] ?? '');
            $payload = [
                'catalogue_id' => $catalogueId,
                'specification' => $specification,
                'dimension' => $dimension,
                'routine' => (string) ($row['routine'] ?? $row['常规价'] ?? ''),
                'stock' => $this->parseStock($row['stock'] ?? $row['库存'] ?? -1),
                'status' => $this->parseStatus($row['status'] ?? $row['状态'] ?? 0),
                'updated_at' => $now,
            ];
            $id = (int) ($row['id'] ?? $row['ID'] ?? 0);
            $exists = $id > 0
                ? PriceSheetModel::where('id', $id)->where('catalogue_id', $catalogueId)->where('deleted_at', 0)->first()
                : null;
            if (empty($exists)) {
                $exists = PriceSheetModel::where('catalogue_id', $catalogueId)
                    ->where('specification', $specification)
                    ->where('dimension', $dimension)
                    ->where('deleted_at', 0)
                    ->first();
            }
            if (!empty($exists)) {
                PriceSheetModel::where('id', $exists['id'])->update($payload);
                $updated++;
            } else {
                $payload['created_at'] = $now;
                PriceSheetModel::insert($payload);
                $created++;
            }
        }
        return [$created, $updated];
    }

    /**
     * 报价单行挂到哪件商品：编号优先，兼容导出里的商品ID
     */
    private function resolveCatalogueId(array $row): int
    {
        $identifier = trim((string) ($row['identifier'] ?? $row['商品编号'] ?? ''));
        if ($identifier !== '') {
            $cat = CatalogueModel::where('identifier', $identifier)->where('deleted_at', 0)->first();
            if (!empty($cat)) {
                return (int) $cat['id'];
            }
        }
        $id = (int) ($row['catalogue_id'] ?? $row['商品ID'] ?? 0);
        if ($id <= 0) {
            return 0;
        }
        $exists = CatalogueModel::where('id', $id)->where('deleted_at', 0)->exists();
        return $exists ? $id : 0;
    }

    /**
     * Excel 状态列可能是「上架/下架」或 0/1，统一成库里的数字
     */
    private function parseStatus(mixed $value): int
    {
        $raw = trim((string) $value);
        if (in_array($raw, ['上架', '启用', '正常', '0', ''], true)) {
            return 0;
        }
        if (in_array($raw, ['下架', '禁用', '1'], true)) {
            return 1;
        }
        return ((int) $raw) === 1 ? 1 : 0;
    }

    /**
     * 库存：空 / 不限 → -1
     */
    private function parseStock(mixed $value): int
    {
        $raw = trim((string) $value);
        if ($raw === '' || in_array($raw, ['不限', '无限', '-1'], true)) {
            return -1;
        }
        return (int) $raw;
    }
}