// 盘点任务服务
// 职责：盘点任务CRUD、完成/取消、导出模板、导入Excel、创建盘点明细
// 调用链：Create → createInventoryDetails(快照) → ExportTemplate → ImportInventoryCount → adjustStock
// 紧密耦合：dao.InventoryCount、InventoryCountDetail(明细)、PrivateStock(库存快照)、Capacity(容量更新)
// 注意：同一时间只允许一个盘点任务，Excel导入使用缓存避免N+1查询
package service

import (
	"assets/consts/public"
	"assets/consts/stock"
	dao "assets/dao/stock"
	dto "assets/model/dto/stock"
	entity "assets/model/entity/stock"
	"bytes"
	"context"
	"errors"
	"fmt"
	"time"

	"gitea.redpowerfuture.com/red-future/common/db/mongo"
	"gitea.redpowerfuture.com/red-future/common/utils"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/os/gtime"
	"github.com/gogf/gf/v2/util/gconv"
	"github.com/xuri/excelize/v2"
	"go.mongodb.org/mongo-driver/v2/bson"
)

type inventoryCount struct{}

var InventoryCount = new(inventoryCount)

func (s *inventoryCount) Create(ctx context.Context, req *dto.CreateInventoryCountReq) (res *dto.CreateInventoryCountRes, err error) {
	// 检查是否存在未完成的盘点任务（同一时间只允许一个盘点任务）
	has, err := dao.InventoryCount.HasUncompletedTask(ctx)
	if err != nil {
		return nil, err
	}
	if has {
		return nil, errors.New("存在未完成的盘点任务，请先完成或取消后再创建新任务")
	}

	// 生成盘点单号
	countNo, err := utils.IncrSequence(ctx, public.StockInventoryNoKeyPrefix, 6, "-")
	if err != nil {
		return nil, fmt.Errorf("生成盘点单号失败: %v", err)
	}

	// 创建盘点任务主表
	ids, err := dao.InventoryCount.Insert(ctx, req, countNo)
	if err != nil {
		return
	}
	id := ids[0].(bson.ObjectID)

	// 根据盘点范围查询库存并创建明细记录（记录账面数量快照）
	err = s.createInventoryDetails(ctx, &id, req)
	if err != nil {
		// 回滚：物理删除已插入的主记录，避免残留数据阻塞后续创建
		_, _ = mongo.DB().Delete(ctx, bson.M{"_id": id}, public.InventoryCountCollection)
		return nil, fmt.Errorf("创建盘点明细失败: %v", err)
	}

	res = &dto.CreateInventoryCountRes{
		Id:      &id,
		CountNo: countNo,
	}
	return
}

func (s *inventoryCount) Update(ctx context.Context, req *dto.UpdateInventoryCountReq) error {
	return dao.InventoryCount.Update(ctx, req)
}

func (s *inventoryCount) Delete(ctx context.Context, req *dto.DeleteInventoryCountReq) error {
	// 先删除关联的盘点明细
	if err := dao.InventoryCountDetail.DeleteByCountId(ctx, req.Id); err != nil {
		return fmt.Errorf("删除盘点明细失败: %v", err)
	}
	// 再删除盘点任务本身
	return dao.InventoryCount.Delete(ctx, req)
}

func (s *inventoryCount) GetOne(ctx context.Context, req *dto.GetInventoryCountReq) (res *dto.GetInventoryCountRes, err error) {
	one, err := dao.InventoryCount.GetOne(ctx, req)
	if err != nil {
		return
	}
	err = utils.Struct(one, &res)
	return
}

func (s *inventoryCount) List(ctx context.Context, req *dto.ListInventoryCountReq) (res *dto.ListInventoryCountRes, err error) {
	list, total, err := dao.InventoryCount.List(ctx, req)
	if err != nil {
		return
	}
	res = &dto.ListInventoryCountRes{
		Total: total,
	}
	// 手动构建DTO列表（Entity和DTO字段不完全匹配）
	res.List = make([]dto.InventoryCountListItem, len(list))
	for i, item := range list {
		res.List[i] = dto.InventoryCountListItem{
			Id:               item.Id,
			CountNo:          item.CountNo,
			Title:            item.Title,
			WarehouseIDs:     item.WarehouseIDs,
			ZoneIDs:          item.ZoneIDs,
			CountType:        item.CountType,
			CountTypeText:    item.CountType.String(),
			Scope:            item.Scope,
			ScopeText:        item.Scope.String(),
			Status:           item.Status,
			StatusText:       item.Status.String(),
			Progress:         item.Progress,
			AssigneeID:       item.AssigneeID,
			AssigneeName:     item.AssigneeName,
			ActualStartTime:  item.ActualStartTime,
			ActualEndTime:    item.ActualEndTime,
			TotalItems:       item.TotalItems,
			CompletedItems:   item.CompletedItems,
			DiscrepancyItems: item.DiscrepancyItems,
			CreatedAt:        s.timeToGtime(item.CreatedAt),
			UpdatedAt:        s.timeToGtime(item.UpdatedAt),
		}
	}
	// 批量填充关联名称
	s.fillListItemNames(ctx, list, res.List)
	return
}

// timeToGtime 将time.Time转换为gtime.Time
func (s *inventoryCount) timeToGtime(t *time.Time) *gtime.Time {
	if t == nil {
		return nil
	}
	return gtime.New(t)
}

// Complete 完成盘点
func (s *inventoryCount) Complete(ctx context.Context, req *dto.CompleteInventoryCountReq) (res *dto.CompleteInventoryCountRes, err error) {
	// 查询盘点任务详情，验证任务是否存在
	count, err := dao.InventoryCount.GetOne(ctx, &dto.GetInventoryCountReq{Id: req.Id})
	if err != nil {
		return nil, err
	}
	if count.Status != stock.InventoryCountStatusInProgress {
		return nil, fmt.Errorf("盘点任务当前状态为[%s]，只有进行中的任务才能完成", count.Status.String())
	}

	// 查询所有盘点明细记录
	details, err := dao.InventoryCountDetail.ListByCountId(ctx, req.Id)
	if err != nil {
		return nil, err
	}

	// 统计存在差异且未调整的明细数量
	discrepancyCount := 0
	for _, detail := range details {
		if detail.Difference != 0 && !detail.IsAdjusted {
			discrepancyCount++
		}
	}

	// 有未调整的差异项，不允许完成
	if discrepancyCount > 0 {
		return nil, fmt.Errorf("存在%d个未调整的盘点差异，请先调整后再完成", discrepancyCount)
	}

	// 更新状态为已完成
	err = dao.InventoryCount.UpdateStatus(ctx, req.Id, stock.InventoryCountStatusCompleted)
	if err != nil {
		return nil, err
	}

	return &dto.CompleteInventoryCountRes{Id: req.Id}, nil
}

// Cancel 取消盘点
func (s *inventoryCount) Cancel(ctx context.Context, req *dto.CancelInventoryCountReq) (res *dto.CancelInventoryCountRes, err error) {
	// 查询盘点任务，校验状态
	count, err := dao.InventoryCount.GetOne(ctx, &dto.GetInventoryCountReq{Id: req.Id})
	if err != nil {
		return nil, err
	}
	if count.Status != stock.InventoryCountStatusInProgress {
		return nil, fmt.Errorf("盘点任务当前状态为[%s]，只有进行中的任务才能取消", count.Status.String())
	}

	// 回滚已调整的库存（反向$inc）
	details, err := dao.InventoryCountDetail.ListByCountId(ctx, req.Id)
	if err != nil {
		return nil, fmt.Errorf("查询盘点明细失败: %v", err)
	}
	rollbackCount := 0
	for _, detail := range details {
		if detail.IsAdjusted && detail.Difference != 0 {
			// 反向调整：差异取反
			rollbackDetail := detail
			rollbackDetail.Difference = -detail.Difference
			if e := InventoryCountDetail.adjustStock(ctx, &rollbackDetail); e != nil {
				g.Log().Errorf(ctx, "回滚库存失败[SKU=%s]: %v", detail.AssetSkuID.Hex(), e)
				continue
			}
			rollbackCount++
			// 触发库位容量重算
			if !g.IsEmpty(detail.LocationID) {
				if capErr := Capacity.UpdateLocationCapacity(ctx, detail.LocationID); capErr != nil {
					g.Log().Warningf(ctx, "回滚后更新库位容量失败: %v", capErr)
				}
			}
		}
	}
	if rollbackCount > 0 {
		g.Log().Infof(ctx, "取消盘点[%s]已回滚%d条库存调整", req.Id.Hex(), rollbackCount)
	}

	// 更新状态为已取消
	err = dao.InventoryCount.UpdateStatus(ctx, req.Id, stock.InventoryCountStatusCancelled)
	if err != nil {
		return nil, err
	}

	return &dto.CancelInventoryCountRes{Id: req.Id}, nil
}

// ExportTemplate 导出盘点模板
func (s *inventoryCount) ExportTemplate(ctx context.Context, req *dto.ExportInventoryCountTemplateReq) (res *dto.ExportInventoryCountTemplateRes, err error) {
	// 1. 查询盘点任务
	count, err := dao.InventoryCount.GetOne(ctx, &dto.GetInventoryCountReq{Id: req.Id})
	if err != nil {
		return nil, err
	}

	// 2. 根据盘点类型生成Excel模板
	fileData, fileName, err := s.generateExcelTemplate(ctx, count)
	if err != nil {
		return nil, fmt.Errorf("生成Excel模板失败: %v", err)
	}

	return &dto.ExportInventoryCountTemplateRes{
		FileName: fileName,
		FileData: fileData,
	}, nil
}

// generateExcelTemplate 生成Excel模板
func (s *inventoryCount) generateExcelTemplate(ctx context.Context, count *entity.InventoryCount) (fileData []byte, fileName string, err error) {
	// 创建Excel文件
	f := excelize.NewFile()
	defer func() {
		if closeErr := f.Close(); closeErr != nil {
			err = closeErr
		}
	}()

	// 设置工作表名称
	sheetName := "盘点模板"
	f.SetSheetName("Sheet1", sheetName)

	// 根据盘点类型设置不同表头
	// 明盘：有账面数量列，可以看到历史库存
	// 盲盘：没有账面数量列，只填实盘数量
	var headers []string
	if count.CountType == stock.OpenPrice {
		headers = []string{"资产名称", "SKU名称", "仓库", "库区", "库位", "账面数量", "实盘数量", "备注"}
	} else {
		headers = []string{"资产名称", "SKU名称", "仓库", "库区", "库位", "实盘数量", "备注"}
	}
	for i, header := range headers {
		cell, _ := excelize.CoordinatesToCellName(i+1, 1)
		f.SetCellValue(sheetName, cell, header)
	}

	// 设置样式
	lastCol, _ := excelize.CoordinatesToCellName(len(headers), 1)
	headerStyle, err := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{
			Bold: true,
		},
		Fill: excelize.Fill{
			Type:    "pattern",
			Color:   []string{"#E6E6FA"},
			Pattern: 1,
		},
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
	})
	if err != nil {
		return nil, "", err
	}

	f.SetCellStyle(sheetName, "A1", lastCol, headerStyle)

	// 明盘：填充数据行（含账面数量列）
	// 盲盘：填充数据行（无账面数量列，工人看不到系统库存）
	err = s.fillTemplateData(ctx, f, sheetName, count)
	if err != nil {
		return nil, "", err
	}

	// 生成文件名
	fileName = fmt.Sprintf("盘点模板_%s_%s.xlsx", count.CountNo, count.CountType.String())

	// 保存到缓冲区
	var buf bytes.Buffer
	err = f.Write(&buf)
	if err != nil {
		return nil, "", err
	}

	return buf.Bytes(), fileName, nil
}

// fillTemplateData 填充模板数据（明盘包含账面数量，盲盘不包含）
func (s *inventoryCount) fillTemplateData(ctx context.Context, f *excelize.File, sheetName string, count *entity.InventoryCount) error {
	// 查询盘点明细（包含关联名称信息）
	req := &dto.ListInventoryCountDetailReq{
		CountID: count.Id.Hex(),
	}
	listRes, err := InventoryCountDetail.List(ctx, req)
	if err != nil {
		return err
	}

	// 填充数据
	for i, detail := range listRes.List {
		row := i + 2 // 从第2行开始
		f.SetCellValue(sheetName, fmt.Sprintf("A%d", row), detail.AssetName)
		f.SetCellValue(sheetName, fmt.Sprintf("B%d", row), detail.AssetSkuName)
		f.SetCellValue(sheetName, fmt.Sprintf("C%d", row), detail.WarehouseName)
		f.SetCellValue(sheetName, fmt.Sprintf("D%d", row), detail.ZoneName)
		f.SetCellValue(sheetName, fmt.Sprintf("E%d", row), detail.LocationName)
		if count.CountType == stock.OpenPrice {
			// 明盘：F=账面数量, G=实盘数量(空), H=备注
			f.SetCellValue(sheetName, fmt.Sprintf("F%d", row), detail.BookQuantity)
			f.SetCellValue(sheetName, fmt.Sprintf("G%d", row), "")
			f.SetCellValue(sheetName, fmt.Sprintf("H%d", row), "")
		} else {
			// 盲盘：F=实盘数量(空), G=备注
			f.SetCellValue(sheetName, fmt.Sprintf("F%d", row), "")
			f.SetCellValue(sheetName, fmt.Sprintf("G%d", row), "")
		}
	}

	return nil
}

// ImportInventoryCount 上传盘点Excel（一步完成：解析→更新明细→$inc调整库存→自动完成）
func (s *inventoryCount) ImportInventoryCount(ctx context.Context, req *dto.ImportInventoryCountReq, fileData []byte) (res *dto.ImportInventoryCountRes, err error) {
	// 1. 查询盘点任务详情并校验状态
	count, err := dao.InventoryCount.GetOne(ctx, &dto.GetInventoryCountReq{Id: req.Id})
	if err != nil {
		err = fmt.Errorf("查询盘点任务失败: %v", err)
		return
	}
	if count.Status != stock.InventoryCountStatusInProgress {
		err = fmt.Errorf("盘点任务当前状态为[%s]，只有进行中的任务才能导入Excel", count.Status.String())
		return
	}

	// 2. 解析Excel文件，匹配已创建的detail记录并计算差异
	details, err := s.parseExcelFile(ctx, fileData, count)
	if err != nil {
		err = fmt.Errorf("解析Excel失败: %v", err)
		return
	}

	if ctx.Err() != nil {
		err = fmt.Errorf("客户端已断开连接: %v", ctx.Err())
		return
	}

	// 3. 验证所有负差异项调整后库存不为负
	for _, detail := range details {
		if detail.Difference < 0 {
			if e := InventoryCountDetail.validateStockAfterAdjust(ctx, detail); e != nil {
				err = fmt.Errorf("SKU库存校验失败: %v", e)
				return
			}
		}
	}

	// 4. 批量更新明细的实盘数据（不标记完成，先记录实盘数量和差异）
	now := gtime.Now()
	countBy := ""
	if user, e := utils.GetUserInfo(ctx); e == nil {
		countBy = fmt.Sprintf("%v", user.UserName)
	}

	dataFilters := make([]bson.M, 0, len(details))
	dataUpdates := make([]bson.M, 0, len(details))
	for _, detail := range details {
		var discrepancyType stock.DiscrepancyType
		if detail.Difference > 0 {
			discrepancyType = stock.DiscrepancyTypeOverflow
		} else if detail.Difference < 0 {
			discrepancyType = stock.DiscrepancyTypeShortage
		} else {
			discrepancyType = stock.DiscrepancyTypeNone
		}
		var differenceRate float64
		if detail.BookQuantity > 0 {
			differenceRate = float64(detail.Difference) / float64(detail.BookQuantity) * 100
		}

		dataFilters = append(dataFilters, bson.M{"_id": detail.Id})
		dataUpdates = append(dataUpdates, bson.M{
			"actualQuantity":    detail.ActualQuantity,
			"difference":        detail.Difference,
			"differenceRate":    differenceRate,
			"discrepancyType":   discrepancyType,
			"discrepancyReason": detail.DiscrepancyReason,
			"countAt":           now,
			"countBy":           countBy,
		})
	}

	if _, err = mongo.DB().SaveOrUpdate(ctx, dataFilters, dataUpdates, public.InventoryCountDetailCollection); err != nil {
		return nil, fmt.Errorf("批量更新盘点明细失败: %v", err)
	}

	// 5. 逐条处理：调整库存 + 成功后标记完成
	successCount := 0
	failCount := 0
	completedFilters := make([]bson.M, 0, len(details))
	completedUpdates := make([]bson.M, 0, len(details))

	for _, detail := range details {
		// 无差异项直接标记完成（无需调整库存）
		if detail.Difference == 0 {
			completedFilters = append(completedFilters, bson.M{"_id": detail.Id})
			completedUpdates = append(completedUpdates, bson.M{
				"status": stock.InventoryDetailStatusCompleted, "isAdjusted": true, "adjustedAt": now, "adjustedBy": countBy,
			})
			successCount++
			continue
		}

		// 有差异项：先调整库存，成功后才标记完成
		if e := InventoryCountDetail.adjustStock(ctx, detail); e != nil {
			g.Log().Warningf(ctx, "调整库存失败[SKU=%s]: %v", detail.AssetSkuID.Hex(), e)
			failCount++
			continue
		}
		completedFilters = append(completedFilters, bson.M{"_id": detail.Id})
		completedUpdates = append(completedUpdates, bson.M{
			"status": stock.InventoryDetailStatusCompleted, "isAdjusted": true, "adjustedAt": now, "adjustedBy": countBy,
		})
		successCount++

		// 库存变动后触发库位容量重算
		if !g.IsEmpty(detail.LocationID) {
			if capErr := Capacity.UpdateLocationCapacity(ctx, detail.LocationID); capErr != nil {
				g.Log().Warningf(ctx, "更新库位容量失败: %v", capErr)
			}
		}
	}

	// 批量标记成功项为已完成
	if len(completedFilters) > 0 {
		if _, e := mongo.DB().SaveOrUpdate(ctx, completedFilters, completedUpdates, public.InventoryCountDetailCollection); e != nil {
			g.Log().Warningf(ctx, "批量标记完成状态失败: %v", e)
		}
	}

	g.Log().Infof(ctx, "盘点Excel导入完成: 成功=%d, 失败=%d", successCount, failCount)

	// 6. 更新盘点任务统计
	if e := InventoryCountDetail.updateCountStats(ctx, req.Id); e != nil {
		g.Log().Warningf(ctx, "更新统计失败: %v", e)
	}

	// 7. 所有明细已调整，自动完成盘点
	if e := InventoryCountDetail.autoCompleteIfNoDifference(ctx, req.Id); e != nil {
		g.Log().Warningf(ctx, "自动完成检查失败: %v", e)
	}

	// 8. 返回结果
	res = &dto.ImportInventoryCountRes{
		SuccessCount: successCount,
		FailCount:    failCount,
	}
	return
}

// excelImportCache Excel导入缓存（预加载基础数据，避免逐行查询数据库）
// 性能优化：1000行Excel从4000次查询降至4次批量查询
type excelImportCache struct {
	SkuMap       map[string]*skuCacheItem  // skuName -> {privateSkuId}
	WarehouseMap map[string]*bson.ObjectID // warehouseName -> id
	ZoneMap      map[string]*bson.ObjectID // warehouseId_zoneName -> id
	LocationMap  map[string]*bson.ObjectID // zoneId_locationName -> id
}

type skuCacheItem struct {
	ID *bson.ObjectID
}

// buildImportCache 从已有明细收集ID，按需加载基础数据到内存缓存（避免全量加载OOM）
func (s *inventoryCount) buildImportCache(ctx context.Context, existingDetails []entity.InventoryCountDetail) (cache *excelImportCache, err error) {
	cache = &excelImportCache{
		SkuMap:       make(map[string]*skuCacheItem),
		WarehouseMap: make(map[string]*bson.ObjectID),
		ZoneMap:      make(map[string]*bson.ObjectID),
		LocationMap:  make(map[string]*bson.ObjectID),
	}

	// 从已有明细中收集去重ID
	skuIdSet := make(map[string]*bson.ObjectID)
	warehouseIdSet := make(map[string]*bson.ObjectID)
	zoneIdSet := make(map[string]*bson.ObjectID)
	locationIdSet := make(map[string]*bson.ObjectID)
	for _, d := range existingDetails {
		if d.AssetSkuID != nil {
			skuIdSet[d.AssetSkuID.Hex()] = d.AssetSkuID
		}
		if d.WarehouseID != nil {
			warehouseIdSet[d.WarehouseID.Hex()] = d.WarehouseID
		}
		if d.ZoneID != nil {
			zoneIdSet[d.ZoneID.Hex()] = d.ZoneID
		}
		if d.LocationID != nil {
			locationIdSet[d.LocationID.Hex()] = d.LocationID
		}
	}

	// 1. $in查询盘点范围内的PrivateSku
	if len(skuIdSet) > 0 {
		skuIds := make([]*bson.ObjectID, 0, len(skuIdSet))
		for _, id := range skuIdSet {
			skuIds = append(skuIds, id)
		}
		var skus []struct {
			ID      bson.ObjectID `bson:"_id"`
			SkuName string        `bson:"skuName"`
		}
		_, err = mongo.DB().Find(ctx, bson.M{"_id": bson.M{"$in": skuIds}}, &skus, public.PrivateSkuCollection, nil, nil)
		if err != nil {
			return nil, fmt.Errorf("预加载PrivateSku失败: %v", err)
		}
		for i := range skus {
			cache.SkuMap[skus[i].SkuName] = &skuCacheItem{ID: &skus[i].ID}
		}
	}

	// 2. $in查询盘点范围内的仓库
	if len(warehouseIdSet) > 0 {
		whIds := make([]*bson.ObjectID, 0, len(warehouseIdSet))
		for _, id := range warehouseIdSet {
			whIds = append(whIds, id)
		}
		var warehouses []struct {
			ID            bson.ObjectID `bson:"_id"`
			WarehouseName string        `bson:"warehouseName"`
		}
		_, err = mongo.DB().Find(ctx, bson.M{"_id": bson.M{"$in": whIds}}, &warehouses, public.WarehouseCollection, nil, nil)
		if err != nil {
			return nil, fmt.Errorf("预加载仓库失败: %v", err)
		}
		for i := range warehouses {
			cache.WarehouseMap[warehouses[i].WarehouseName] = &warehouses[i].ID
		}
	}

	// 3. $in查询盘点范围内的库区
	if len(zoneIdSet) > 0 {
		zIds := make([]*bson.ObjectID, 0, len(zoneIdSet))
		for _, id := range zoneIdSet {
			zIds = append(zIds, id)
		}
		var zones []struct {
			ID          bson.ObjectID `bson:"_id"`
			ZoneName    string        `bson:"zoneName"`
			WarehouseID bson.ObjectID `bson:"warehouseId"`
		}
		_, err = mongo.DB().Find(ctx, bson.M{"_id": bson.M{"$in": zIds}}, &zones, public.ZoneCollection, nil, nil)
		if err != nil {
			return nil, fmt.Errorf("预加载库区失败: %v", err)
		}
		for i := range zones {
			key := zones[i].WarehouseID.Hex() + "_" + zones[i].ZoneName
			cache.ZoneMap[key] = &zones[i].ID
		}
	}

	// 4. $in查询盘点范围内的库位
	if len(locationIdSet) > 0 {
		locIds := make([]*bson.ObjectID, 0, len(locationIdSet))
		for _, id := range locationIdSet {
			locIds = append(locIds, id)
		}
		var locations []struct {
			ID           bson.ObjectID `bson:"_id"`
			LocationName string        `bson:"locationName"`
			ZoneID       bson.ObjectID `bson:"zoneId"`
		}
		_, err = mongo.DB().Find(ctx, bson.M{"_id": bson.M{"$in": locIds}}, &locations, public.LocationCollection, nil, nil)
		if err != nil {
			return nil, fmt.Errorf("预加载库位失败: %v", err)
		}
		for i := range locations {
			key := locations[i].ZoneID.Hex() + "_" + locations[i].LocationName
			cache.LocationMap[key] = &locations[i].ID
		}
	}

	g.Log().Infof(ctx, "Excel导入缓存预加载完成: SKU=%d, 仓库=%d, 库区=%d, 库位=%d",
		len(cache.SkuMap), len(cache.WarehouseMap), len(cache.ZoneMap), len(cache.LocationMap))

	return
}

// parseExcelFile 解析Excel文件并更新盘点明细（匹配已有detail记录）
// 新逻辑：从已创建的detail记录中匹配，更新实盘数量和差异
func (s *inventoryCount) parseExcelFile(ctx context.Context, fileData []byte, count *entity.InventoryCount) (details []*entity.InventoryCountDetail, err error) {
	// 打开Excel文件
	f, err := excelize.OpenReader(bytes.NewReader(fileData))
	if err != nil {
		err = fmt.Errorf("打开Excel文件失败: %v", err)
		return
	}
	defer f.Close()

	// 读取第一个工作表的所有行
	sheetName := f.GetSheetName(0)
	rows, err := f.GetRows(sheetName)
	if err != nil {
		err = fmt.Errorf("读取Excel行失败: %v", err)
		return
	}

	if len(rows) < 2 {
		err = errors.New("Excel文件无数据行")
		return
	}

	// 预加载已创建的detail记录，用于匹配Excel中的数据
	existingDetails, err := dao.InventoryCountDetail.ListByCountId(ctx, count.Id)
	if err != nil {
		return nil, fmt.Errorf("查询盘点明细失败: %v", err)
	}
	if len(existingDetails) == 0 {
		return nil, errors.New("盘点任务无明细记录，请检查任务创建流程")
	}

	// 预加载SKU/仓库/库区/库位映射缓存（仅加载盘点范围内数据，避免OOM）
	cache, err := s.buildImportCache(ctx, existingDetails)
	if err != nil {
		return
	}

	// 构建detail Map缓存，key=skuId_warehouseId_zoneId_locationId，O(1)查找替代O(n²)嵌套循环
	detailMap := make(map[string]*entity.InventoryCountDetail, len(existingDetails))
	for j := range existingDetails {
		d := &existingDetails[j]
		key := buildDetailKey(d.AssetSkuID, d.WarehouseID, d.ZoneID, d.LocationID)
		detailMap[key] = d
	}

	// 根据盘点类型确定列索引
	// 明盘：资产名称(0) SKU名称(1) 仓库(2) 库区(3) 库位(4) 账面数量(5) 实盘数量(6) 备注(7)
	// 盲盘：资产名称(0) SKU名称(1) 仓库(2) 库区(3) 库位(4) 实盘数量(5) 备注(6)
	actualQtyCol := 6 // 明盘：实盘数量在第7列
	remarkCol := 7    // 明盘：备注在第8列
	minCols := 7      // 明盘：至少7列
	if count.CountType == stock.HiddenPrice {
		actualQtyCol = 5 // 盲盘：实盘数量在第6列
		remarkCol = 6    // 盲盘：备注在第7列
		minCols = 6      // 盲盘：至少6列
	}

	// 逐行解析Excel数据（从第2行开始，第1行是表头）
	for i := 1; i < len(rows); i++ {
		row := rows[i]
		if len(row) < minCols {
			continue
		}

		// 读取Excel列：SKU名称、仓库、库区、库位
		assetSkuName := row[1]
		warehouseName := row[2]
		zoneName := ""
		if len(row) > 3 {
			zoneName = row[3]
		}
		locationName := ""
		if len(row) > 4 {
			locationName = row[4]
		}

		// 从预加载缓存中查询SKU ID（O(1)查找，避免数据库查询）
		skuItem, ok := cache.SkuMap[assetSkuName]
		if !ok {
			err = fmt.Errorf("第%d行：未找到SKU[%s]", i+1, assetSkuName)
			return
		}
		assetSkuID := skuItem.ID

		// 从缓存查询仓库ID
		warehouseID, ok := cache.WarehouseMap[warehouseName]
		if !ok {
			err = fmt.Errorf("第%d行：未找到仓库[%s]", i+1, warehouseName)
			return
		}

		// 从缓存查询库区ID（可选字段）
		var zoneID *bson.ObjectID
		if zoneName != "" {
			zoneKey := warehouseID.Hex() + "_" + zoneName
			zoneID, ok = cache.ZoneMap[zoneKey]
			if !ok {
				err = fmt.Errorf("第%d行：未找到库区[%s]", i+1, zoneName)
				return
			}
		}

		// 从缓存查询库位ID（可选字段）
		var locationID *bson.ObjectID
		if locationName != "" && zoneID != nil {
			locationKey := zoneID.Hex() + "_" + locationName
			locationID, ok = cache.LocationMap[locationKey]
			if !ok {
				err = fmt.Errorf("第%d行：未找到库位[%s]", i+1, locationName)
				return
			}
		}

		// 从detailMap中O(1)查找匹配项（替代O(n²)嵌套循环）
		detailKey := buildDetailKey(assetSkuID, warehouseID, zoneID, locationID)
		matchedDetail := detailMap[detailKey]

		if matchedDetail == nil {
			err = fmt.Errorf("第%d行：未找到对应的盘点明细记录[SKU=%s,仓库=%s]", i+1, assetSkuName, warehouseName)
			return
		}

		// 读取并验证实盘数量
		actualQty := gconv.Int(row[actualQtyCol])
		if actualQty < 0 {
			err = fmt.Errorf("第%d行：实盘数量不能为负数", i+1)
			return
		}

		// 使用创建任务时记录的账面数量计算差异（而非Excel中的账面数量）
		difference := actualQty - matchedDetail.BookQuantity

		// 读取备注
		remark := ""
		if len(row) > remarkCol {
			remark = row[remarkCol]
		}
		// 明盘+负差异时必须有原因
		if count.CountType == stock.OpenPrice && difference < 0 && remark == "" {
			err = fmt.Errorf("第%d行：明盘且实物少于账面时，必须填写备注", i+1)
			return
		}

		// 更新匹配的detail记录（实盘数量、差异、差异原因）
		matchedDetail.ActualQuantity = actualQty
		matchedDetail.Difference = difference
		matchedDetail.DiscrepancyReason = remark
		details = append(details, matchedDetail)
	}

	return
}

// createInventoryDetails 创建盘点明细，根据盘点范围查询private_stock库存并批量生成detail记录，锁定账面数量快照
func (s *inventoryCount) createInventoryDetails(ctx context.Context, countId *bson.ObjectID, req *dto.CreateInventoryCountReq) error {
	// 根据盘点范围查询private_stock库存
	stocks, err := s.queryStocksByScope(ctx, req)
	if err != nil {
		return err
	}

	if len(stocks) == 0 {
		return errors.New("查询范围内无库存数据，请检查盘点范围设置")
	}

	// 收集所有privateSkuID，准备批量查询
	skuIDs := make([]*bson.ObjectID, 0, len(stocks))
	for _, stk := range stocks {
		skuIDs = append(skuIDs, stk.PrivateSkuID)
	}

	// 批量查询private_sku表获取assetId（skuIDs是privateSkuId，查PrivateSkuCollection）
	var skuList []struct {
		ID      *bson.ObjectID `bson:"_id"`
		AssetID *bson.ObjectID `bson:"assetId"`
	}
	filter := bson.M{"_id": bson.M{"$in": skuIDs}}
	_, err = mongo.DB().Find(ctx, filter, &skuList, public.PrivateSkuCollection, nil, nil)
	if err != nil {
		return fmt.Errorf("批量查询PrivateSku失败: %v", err)
	}

	// 构建Map缓存，用于O(1)查找privateSkuID对应的assetID
	skuMap := make(map[string]*bson.ObjectID, len(skuList))
	for i := range skuList {
		skuMap[skuList[i].ID.Hex()] = skuList[i].AssetID
	}

	// 遍历库存快照，构建盘点明细记录
	details := make([]*entity.InventoryCountDetail, 0, len(stocks))
	for _, stk := range stocks {
		assetID, ok := skuMap[stk.PrivateSkuID.Hex()]
		if !ok {
			g.Log().Warningf(ctx, "未找到私域SKU[%s]的assetId，跳过", stk.PrivateSkuID.Hex())
			continue
		}

		detail := &entity.InventoryCountDetail{
			CountID:        countId,
			AssetID:        assetID,
			AssetSkuID:     stk.PrivateSkuID,
			WarehouseID:    stk.WarehouseID,
			ZoneID:         stk.ZoneID,
			LocationID:     stk.LocationID,
			BookQuantity:   stk.AvailableQty,
			ActualQuantity: 0,
			Difference:     0,
			Status:         stock.InventoryDetailStatusPending,
		}
		details = append(details, detail)
	}

	// 批量插入盘点明细记录
	_, err = dao.InventoryCountDetail.InsertBatch(ctx, details)
	if err != nil {
		return fmt.Errorf("批量插入盘点明细失败: %v", err)
	}

	g.Log().Infof(ctx, "盘点任务[%s]创建明细成功，共%d条", countId.Hex(), len(details))
	return nil
}

// queryStocksByScope 根据仓库/库区/库位/SKU范围过滤条件查询private_stock表，返回库存快照列表
func (s *inventoryCount) queryStocksByScope(ctx context.Context, req *dto.CreateInventoryCountReq) (stocks []*stockSnapshot, err error) {
	filter := bson.M{}

	// 必填：仓库ID列表（$in批量查询）
	warehouseIDs, err := hexSliceToObjectIDs(req.WarehouseIDs)
	if err != nil {
		return nil, fmt.Errorf("仓库ID格式错误: %v", err)
	}
	if len(warehouseIDs) > 0 {
		filter["warehouseId"] = bson.M{"$in": warehouseIDs}
	}

	// 可选：库区ID列表
	if len(req.ZoneIDs) > 0 {
		zoneIDs, e := hexSliceToObjectIDs(req.ZoneIDs)
		if e != nil {
			return nil, fmt.Errorf("库区ID格式错误: %v", e)
		}
		if len(zoneIDs) > 0 {
			filter["zoneId"] = bson.M{"$in": zoneIDs}
		}
	}

	// 可选：库位ID列表
	if len(req.LocationIDs) > 0 {
		locationIDs, e := hexSliceToObjectIDs(req.LocationIDs)
		if e != nil {
			return nil, fmt.Errorf("库位ID格式错误: %v", e)
		}
		if len(locationIDs) > 0 {
			filter["locationId"] = bson.M{"$in": locationIDs}
		}
	}

	// 可选：资产SKU ID列表
	if len(req.AssetSkuIDs) > 0 {
		assetSkuIDs, e := hexSliceToObjectIDs(req.AssetSkuIDs)
		if e != nil {
			return nil, fmt.Errorf("资产SKU ID格式错误: %v", e)
		}
		if len(assetSkuIDs) > 0 {
			filter["privateSkuId"] = bson.M{"$in": assetSkuIDs}
		}
	}

	// 查询private_stock表，获取库存快照
	_, err = mongo.DB().Find(ctx, filter, &stocks, public.PrivateStockCollection, nil, nil)
	if err != nil {
		return nil, fmt.Errorf("查询库存失败: %v", err)
	}

	return stocks, nil
}

// hexSliceToObjectIDs 将hex字符串切片转换为ObjectID切片（跳过空值）
func hexSliceToObjectIDs(hexIDs []string) ([]bson.ObjectID, error) {
	result := make([]bson.ObjectID, 0, len(hexIDs))
	for _, hex := range hexIDs {
		if g.IsEmpty(hex) {
			continue
		}
		id, err := bson.ObjectIDFromHex(hex)
		if err != nil {
			return nil, fmt.Errorf("值:%s, %v", hex, err)
		}
		result = append(result, id)
	}
	return result, nil
}

// buildDetailKey 构建盘点明细复合key（skuId_warehouseId_zoneId_locationId），用于O(1) Map查找
func buildDetailKey(skuID, warehouseID, zoneID, locationID *bson.ObjectID) string {
	sk, wk, zk, lk := "", "", "", ""
	if skuID != nil {
		sk = skuID.Hex()
	}
	if warehouseID != nil {
		wk = warehouseID.Hex()
	}
	if zoneID != nil {
		zk = zoneID.Hex()
	}
	if locationID != nil {
		lk = locationID.Hex()
	}
	return sk + "_" + wk + "_" + zk + "_" + lk
}

// stockSnapshot 库存快照结构体，映射private_stock表字段，用于盘点明细的账面数量记录
type stockSnapshot struct {
	PrivateSkuID *bson.ObjectID `bson:"privateSkuId"`
	WarehouseID  *bson.ObjectID `bson:"warehouseId"`
	ZoneID       *bson.ObjectID `bson:"zoneId"`
	LocationID   *bson.ObjectID `bson:"locationId"`
	AvailableQty int            `bson:"availableQty"`
}

// fillListItemNames 批量填充列表项的关联名称（避免N+1查询）
func (s *inventoryCount) fillListItemNames(ctx context.Context, entities []entity.InventoryCount, items []dto.InventoryCountListItem) {
	if len(entities) == 0 {
		return
	}

	// 1. 收集所有需要查询的ID（去重）
	warehouseIdSet := make(map[string]*bson.ObjectID)
	zoneIdSet := make(map[string]*bson.ObjectID)
	for _, e := range entities {
		for _, id := range e.WarehouseIDs {
			if id != nil {
				warehouseIdSet[id.Hex()] = id
			}
		}
		for _, id := range e.ZoneIDs {
			if id != nil {
				zoneIdSet[id.Hex()] = id
			}
		}
	}

	// 2. 批量查询仓库名称
	warehouseNameMap := make(map[string]string)
	if len(warehouseIdSet) > 0 {
		ids := make([]*bson.ObjectID, 0, len(warehouseIdSet))
		for _, id := range warehouseIdSet {
			ids = append(ids, id)
		}
		var warehouses []struct {
			Id   *bson.ObjectID `bson:"_id"`
			Name string         `bson:"warehouseName"`
		}
		if _, e := mongo.DB().Find(ctx, bson.M{"_id": bson.M{"$in": ids}}, &warehouses, public.WarehouseCollection, nil, nil); e == nil {
			for _, w := range warehouses {
				warehouseNameMap[w.Id.Hex()] = w.Name
			}
		}
	}

	// 3. 批量查询库区名称
	zoneNameMap := make(map[string]string)
	if len(zoneIdSet) > 0 {
		ids := make([]*bson.ObjectID, 0, len(zoneIdSet))
		for _, id := range zoneIdSet {
			ids = append(ids, id)
		}
		var zones []struct {
			Id   *bson.ObjectID `bson:"_id"`
			Name string         `bson:"zoneName"`
		}
		if _, e := mongo.DB().Find(ctx, bson.M{"_id": bson.M{"$in": ids}}, &zones, public.ZoneCollection, nil, nil); e == nil {
			for _, z := range zones {
				zoneNameMap[z.Id.Hex()] = z.Name
			}
		}
	}

	// 4. 填充名称到列表项
	for i := range items {
		// 填充仓库名称列表
		if len(entities[i].WarehouseIDs) > 0 {
			names := make([]string, 0, len(entities[i].WarehouseIDs))
			for _, id := range entities[i].WarehouseIDs {
				if id != nil {
					if name, ok := warehouseNameMap[id.Hex()]; ok {
						names = append(names, name)
					}
				}
			}
			items[i].WarehouseNames = names
		}
		// 填充库区名称列表
		if len(entities[i].ZoneIDs) > 0 {
			names := make([]string, 0, len(entities[i].ZoneIDs))
			for _, id := range entities[i].ZoneIDs {
				if id != nil {
					if name, ok := zoneNameMap[id.Hex()]; ok {
						names = append(names, name)
					}
				}
			}
			items[i].ZoneNames = names
		}
	}
}