import { BadRequestException, Injectable } from "@nestjs/common";
import { InjectModel } from "@nestjs/mongoose";
import { Model, Types } from "mongoose";
import { NotificationsService } from "./notifications.service";
import { Batch, BatchDocument } from "./schemas/batch.schema";
import { InventoryBalance, InventoryBalanceDocument } from "./schemas/inventory-balance.schema";
import { InventoryThresholdState, InventoryThresholdStateDocument } from "./schemas/inventory-threshold-state.schema";
import { Item, ItemDocument } from "./schemas/item.schema";
import { StockInvoice, StockInvoiceDocument } from "./schemas/stock-invoice.schema";
import { StockMovementSheet, StockMovementSheetDocument } from "./schemas/stock-movement-sheet.schema";
import { StockTransaction, StockTransactionDocument } from "./schemas/stock-transaction.schema";
import { User, UserDocument } from "./schemas/user.schema";
import { Warehouse, WarehouseDocument } from "./schemas/warehouse.schema";

const GLOBAL_MT_THRESHOLD = 26000;
const THRESHOLD_STATE_KEY = "global-stock-in-invoice";

/** Parse kg-per-bag from unit string e.g. "BAG/1x40kg" → 40, "BAG/4x10kg" → 40 */
function parseKgPerBag(unit: string | null | undefined): number {
  if (!unit) return 0;
  // Match patterns like "1x40kg", "4x10kg", "1x25kg"
  const match = unit.match(/(\d+)x(\d+(?:\.\d+)?)kg/i);
  if (!match) return 0;
  const count = parseFloat(match[1]);
  const kgEach = parseFloat(match[2]);
  return count * kgEach;
}

/** Get kg-per-bag from item — prefer bagWeightKg field, fall back to parsing unit string */
// eslint-disable-next-line @typescript-eslint/no-explicit-any
function getKgPerBag(item: any): number {
  if (item.bagWeightKg && item.bagWeightKg > 0) return item.bagWeightKg;
  return parseKgPerBag(item.unit ?? null);
}

/** Get display name from item — prefer description, fall back to itemName */
// eslint-disable-next-line @typescript-eslint/no-explicit-any
function getItemName(item: any): string | null {
  return item.description ?? item.itemName ?? null;
}

@Injectable()
export class StockMovementSheetsService {
  constructor(
    @InjectModel(StockMovementSheet.name)
    private readonly sheetModel: Model<StockMovementSheetDocument>,
    @InjectModel(StockInvoice.name)
    private readonly invoiceModel: Model<StockInvoiceDocument>,
    @InjectModel(Item.name)
    private readonly itemModel: Model<ItemDocument>,
    @InjectModel(Batch.name)
    private readonly batchModel: Model<BatchDocument>,
    @InjectModel(Warehouse.name)
    private readonly warehouseModel: Model<WarehouseDocument>,
    @InjectModel(InventoryBalance.name)
    private readonly balanceModel: Model<InventoryBalanceDocument>,
    @InjectModel(User.name)
    private readonly userModel: Model<UserDocument>,
    @InjectModel(InventoryThresholdState.name)
    private readonly thresholdStateModel: Model<InventoryThresholdStateDocument>,
    @InjectModel(StockTransaction.name)
    private readonly stockTxModel: Model<StockTransactionDocument>,
    private readonly notificationsService: NotificationsService,
  ) {}

  // ── Create ────────────────────────────────────────────────────────────────

  async create(payload: {
    companyName?: string;
    reportTitle?: string;
    stockReportFor?: string;
    documentDate?: string;
    documentNumber?: string;
    referenceNumber?: string;
    preparedBy?: string;
    reviewedBy?: string;
    approvedByField?: string;
    submittedBy?: string;
    // Optional invoice data from Royal AI extraction — saved separately
    invoice?: {
      documentNumber?: string | null;
      documentDate?: string | null;
      referenceNumber?: string | null;
      warehouseName?: string | null;
      itemCode?: string | null;
      itemName?: string | null;
      batchNumber?: string | null;
      allBatches?: string | null;
      expiryDate?: string | null;
      quantityMt?: number | null;
      unit?: string | null;
      unitPrice?: number | null;
      supplierName?: string | null;
      supplierTrn?: string | null;
      buyerName?: string | null;
      buyerTrn?: string | null;
      totalAmount?: number | null;
      vatAmount?: number | null;
      netAmount?: number | null;
      currency?: string | null;
      paymentTerms?: string | null;
      dueDate?: string | null;
    };
    lineItems: Array<{
      itemId: string;
      batchNumber: string;
      productionDate?: string;
      expiryDate: string;
      warehouseBags: Record<string, number>;
      totalMtOverride?: number | null; // user-supplied override for Total MT
      shipmentNo?: string;
      qualityReportNo?: string;
    }>;
  }) {
    if (!payload.lineItems || payload.lineItems.length === 0) {
      throw new BadRequestException("At least one line item is required");
    }

    // Resolve item master data and compute totals for each row
    const resolvedItems = await Promise.all(
      payload.lineItems.map(async (li, idx) => {
        if (!li.itemId) throw new BadRequestException(`Line ${idx + 1}: itemId is required`);
        if (!li.batchNumber) throw new BadRequestException(`Line ${idx + 1}: batchNumber is required`);
        if (!li.expiryDate) throw new BadRequestException(`Line ${idx + 1}: expiryDate is required`);

        const item = await this.itemModel.findById(li.itemId).lean().exec();
        if (!item) throw new BadRequestException(`Line ${idx + 1}: Item not found`);

        const kgPerBag = getKgPerBag(item);
        const totalBags = Object.values(li.warehouseBags ?? {}).reduce((s, v) => s + (v || 0), 0);
        const calcMt = kgPerBag > 0 ? Number(((totalBags * kgPerBag) / 1000).toFixed(4)) : 0;
        // Use user-supplied override if provided, otherwise use calculated value
        const totalMt = (li.totalMtOverride != null && li.totalMtOverride > 0)
          ? Number(li.totalMtOverride.toFixed(4))
          : calcMt;

        return {
          lineNumber: idx + 1,
          itemId: new Types.ObjectId(li.itemId),
          itemCode: item.itemCode ?? null,
          barcode: item.barcode ?? null,
          itemName: getItemName(item),
          blend: item.blend ?? null,
          grainType: item.grainType ?? null,
          varietyType: item.varietyType ?? null,
          processType: item.processType ?? null,
          coo: item.countryOfOrigin ?? null,
          unit: item.unit ?? (item.bagWeightKg ? `BAG/1x${item.bagWeightKg}kg` : null),
          batchNumber: li.batchNumber,
          productionDate: li.productionDate ?? null,
          expiryDate: li.expiryDate,
          warehouseBags: li.warehouseBags ?? {},
          totalBags,
          totalMt,
          shipmentNo: li.shipmentNo ?? null,
          qualityReportNo: li.qualityReportNo ?? null,
        };
      }),
    );

    const totalBagsAll = resolvedItems.reduce((s, r) => s + r.totalBags, 0);
    const totalMtAll = Number(resolvedItems.reduce((s, r) => s + r.totalMt, 0).toFixed(4));
    const currentTotalMt = await this._getTotalAvailableMt();
    const thresholdBasisMt = await this._getHistoricalThresholdBasis(currentTotalMt);
    const thresholdState = await this._syncThresholdState(thresholdBasisMt);
    const projectedTotalMt = currentTotalMt + totalMtAll;

    // Temporarily disabled: frontend can still show REQUIRED, but stock-in sheets should submit without invoice.
    // if (!thresholdState.hasReachedThreshold && projectedTotalMt < GLOBAL_MT_THRESHOLD && !payload.invoice) {
    //   throw new BadRequestException(
    //     `Invoice upload is required until inventory first reaches ${GLOBAL_MT_THRESHOLD.toLocaleString()} MT. ` +
    //       `Current: ${currentTotalMt.toFixed(2)} MT, Adding: ${totalMtAll.toFixed(2)} MT, ` +
    //       `Projected: ${projectedTotalMt.toFixed(2)} MT.`,
    //   );
    // }

    // Save invoice separately if provided, then link to sheet
    let invoiceId: Types.ObjectId | null = null;
    if (payload.invoice) {
      const inv = payload.invoice;
      const savedInvoice = await this.invoiceModel.create({
        sheetId: null, // will be updated after sheet is created
        documentNumber: inv.documentNumber ?? null,
        documentDate: inv.documentDate ?? null,
        referenceNumber: inv.referenceNumber ?? null,
        warehouseName: inv.warehouseName ?? null,
        itemCode: inv.itemCode ?? null,
        itemName: inv.itemName ?? null,
        batchNumber: inv.batchNumber ?? null,
        allBatches: inv.allBatches ?? null,
        expiryDate: inv.expiryDate ?? null,
        quantityMt: inv.quantityMt ?? null,
        unit: inv.unit ?? null,
        unitPrice: inv.unitPrice ?? null,
        supplierName: inv.supplierName ?? null,
        supplierTrn: inv.supplierTrn ?? null,
        buyerName: inv.buyerName ?? null,
        buyerTrn: inv.buyerTrn ?? null,
        totalAmount: inv.totalAmount ?? null,
        vatAmount: inv.vatAmount ?? null,
        netAmount: inv.netAmount ?? null,
        currency: inv.currency ?? null,
        paymentTerms: inv.paymentTerms ?? null,
        dueDate: inv.dueDate ?? null,
        submittedBy: payload.submittedBy ?? null,
        createdAt: new Date().toISOString(),
      });
      invoiceId = savedInvoice._id as Types.ObjectId;
    }

    const sheet = await this.sheetModel.create({
      companyName: payload.companyName ?? null,
      reportTitle: payload.reportTitle ?? null,
      stockReportFor: payload.stockReportFor ?? null,
      documentDate: payload.documentDate ?? null,
      documentNumber: payload.documentNumber ?? null,
      referenceNumber: payload.referenceNumber ?? null,
      preparedBy: payload.preparedBy ?? null,
      reviewedBy: payload.reviewedBy ?? null,
      approvedByField: payload.approvedByField ?? null,
      lineItems: resolvedItems,
      totalBagsAll,
      totalMtAll,
      approvalStatus: "pending_approval",
      submittedBy: payload.submittedBy ?? null,
      invoiceId,
      createdAt: new Date().toISOString(),
    });

    // Back-link the invoice to the sheet
    if (invoiceId) {
      await this.invoiceModel.findByIdAndUpdate(invoiceId, { sheetId: sheet._id });
    }

    await this.notificationsService.notifyManagers(
      `New Strategic Stock Movement Sheet is pending your approval (${totalMtAll} MT total).`,
      (sheet._id as Types.ObjectId).toString(),
    );

    return this._mapSheet(sheet.toObject(), false);
  }

  // ── Read ──────────────────────────────────────────────────────────────────

  async findAll() {
    const sheets = await this.sheetModel
      .find()
      .sort({ createdAt: -1 })
      .lean()
      .exec();
    return sheets.map((s) => this._mapSheet(s, false));
  }

  async findOne(id: string) {
    const sheet = await this.sheetModel.findById(id).lean().exec();
    if (!sheet) throw new BadRequestException("Sheet not found");
    const mapped = await this._mapSheetWithWarehouseNames(sheet);
    // Attach invoice if linked
    if (sheet.invoiceId) {
      const invoice = await this.invoiceModel.findById(sheet.invoiceId).lean().exec();
      if (invoice) {
        return { ...mapped, invoice: this._mapInvoice(invoice) };
      }
    }
    return { ...mapped, invoice: null };
  }

  // ── Update (pending only) ─────────────────────────────────────────────────

  async update(
    id: string,
    payload: Parameters<StockMovementSheetsService["create"]>[0],
  ) {
    const sheet = await this.sheetModel.findById(id).exec();
    if (!sheet) throw new BadRequestException("Sheet not found");
    if (sheet.approvalStatus !== "pending_approval") {
      throw new BadRequestException("Only pending sheets can be edited");
    }

    // Re-resolve line items
    const resolvedItems = await Promise.all(
      (payload.lineItems ?? []).map(async (li, idx) => {
        if (!li.itemId) throw new BadRequestException(`Line ${idx + 1}: itemId is required`);
        if (!li.batchNumber) throw new BadRequestException(`Line ${idx + 1}: batchNumber is required`);
        if (!li.expiryDate) throw new BadRequestException(`Line ${idx + 1}: expiryDate is required`);

        const item = await this.itemModel.findById(li.itemId).lean().exec();
        if (!item) throw new BadRequestException(`Line ${idx + 1}: Item not found`);

        const kgPerBag = getKgPerBag(item);
        const totalBags = Object.values(li.warehouseBags ?? {}).reduce((s, v) => s + (v || 0), 0);
        const calcMt = kgPerBag > 0 ? Number(((totalBags * kgPerBag) / 1000).toFixed(4)) : 0;
        const totalMt = (li.totalMtOverride != null && li.totalMtOverride > 0)
          ? Number(li.totalMtOverride.toFixed(4))
          : calcMt;

        return {
          lineNumber: idx + 1,
          itemId: new Types.ObjectId(li.itemId),
          itemCode: item.itemCode ?? null,
          barcode: item.barcode ?? null,
          itemName: getItemName(item),
          blend: item.blend ?? null,
          grainType: item.grainType ?? null,
          varietyType: item.varietyType ?? null,
          processType: item.processType ?? null,
          coo: item.countryOfOrigin ?? null,
          unit: item.unit ?? (item.bagWeightKg ? `BAG/1x${item.bagWeightKg}kg` : null),
          batchNumber: li.batchNumber,
          productionDate: li.productionDate ?? null,
          expiryDate: li.expiryDate,
          warehouseBags: li.warehouseBags ?? {},
          totalBags,
          totalMt,
          shipmentNo: li.shipmentNo ?? null,
          qualityReportNo: li.qualityReportNo ?? null,
        };
      }),
    );

    const totalBagsAll = resolvedItems.reduce((s, r) => s + r.totalBags, 0);
    const totalMtAll = Number(resolvedItems.reduce((s, r) => s + r.totalMt, 0).toFixed(4));

    sheet.set({
      companyName: payload.companyName ?? null,
      reportTitle: payload.reportTitle ?? null,
      stockReportFor: payload.stockReportFor ?? null,
      documentDate: payload.documentDate ?? null,
      documentNumber: payload.documentNumber ?? null,
      referenceNumber: payload.referenceNumber ?? null,
      preparedBy: payload.preparedBy ?? null,
      reviewedBy: payload.reviewedBy ?? null,
      approvedByField: payload.approvedByField ?? null,
      lineItems: resolvedItems,
      totalBagsAll,
      totalMtAll,
    });
    await sheet.save();

    return this._mapSheet(sheet.toObject(), false);
  }

  // ── Approve ───────────────────────────────────────────────────────────────

  async approve(id: string, approvedByName: string) {
    const sheet = await this.sheetModel.findById(id).exec();
    if (!sheet) throw new BadRequestException("Sheet not found");
    if (sheet.approvalStatus !== "pending_approval")
      throw new BadRequestException("Sheet is not pending approval");

    let recalcTotalMtAll = 0;

    // Apply inventory changes per warehouse per line item.
    // Always look up the live item to get the latest bagWeightKg —
    // the unit snapshot on the line item may be stale (e.g. set before
    // bagWeightKg was configured on the item master).
    for (const li of sheet.lineItems) {
      // Fetch live item so we always use the most up-to-date bag weight
      const liveItem = await this.itemModel
        .findById(li.itemId)
        .lean()
        .exec();

      // Resolve kg-per-bag: prefer live item bagWeightKg, then live unit,
      // then fall back to the unit string frozen in the line item snapshot
      const kgPerBag =
        liveItem ? getKgPerBag(liveItem) : parseKgPerBag(li.unit);

      const totalBags = Object.values(li.warehouseBags ?? {}).reduce(
        (s, v) => s + (Number(v) || 0),
        0,
      );

      // Recalculate totalMt for this line using the live bag weight
      const lineMt =
        kgPerBag > 0
          ? Number(((totalBags * kgPerBag) / 1000).toFixed(4))
          : (li.totalMt ?? 0); // keep stored value as last resort

      recalcTotalMtAll += lineMt;

      // Update the frozen line item so the sheet reflects the correct MT
      li.totalMt = lineMt;

      // Write per-warehouse balances
      for (const [warehouseId, bags] of Object.entries(li.warehouseBags ?? {})) {
        if (!bags || bags <= 0) continue;
        const warehouseMt =
          kgPerBag > 0
            ? Number(((Number(bags) * kgPerBag) / 1000).toFixed(4))
            : 0;
        if (warehouseMt <= 0) continue;

        await this._upsertBalance(
          warehouseId,
          li.itemId.toString(),
          li.batchNumber,
          li.expiryDate,
          warehouseMt,
          "inbound",
        );
      }
    }

    // Persist the recalculated totals back onto the sheet so getSummary()
    // and the sheet list both show the correct MT value
    sheet.totalMtAll = Number(recalcTotalMtAll.toFixed(4));
    sheet.approvalStatus = "approved";
    sheet.approvedBy = approvedByName;
    sheet.approvedAt = new Date().toISOString();
    await sheet.save();
    await this._markThresholdReachedIfNeeded();

    await this._notifyKeepers(
      "approved",
      `Strategic Stock Movement Sheet has been approved by ${approvedByName} (${sheet.totalMtAll} MT).`,
      id,
    );

    return this._mapSheet(sheet.toObject(), false);
  }

  // ── Reject ────────────────────────────────────────────────────────────────

  async reject(id: string, rejectedByName: string, reason: string) {
    const sheet = await this.sheetModel.findById(id).exec();
    if (!sheet) throw new BadRequestException("Sheet not found");
    if (sheet.approvalStatus !== "pending_approval")
      throw new BadRequestException("Sheet is not pending approval");

    sheet.approvalStatus = "rejected";
    sheet.rejectedBy = rejectedByName;
    sheet.rejectedAt = new Date().toISOString();
    sheet.rejectedReason = reason ?? "";
    await sheet.save();

    await this._notifyKeepers(
      "rejected",
      `Strategic Stock Movement Sheet was rejected by ${rejectedByName}. Reason: ${reason || "No reason provided"}.`,
      id,
    );

    return this._mapSheet(sheet.toObject(), false);
  }

  // ── Private helpers ───────────────────────────────────────────────────────

  private async _upsertBalance(
    warehouseId: string,
    itemId: string,
    batchNumber: string,
    expiryDate: string,
    mt: number,
    type: "inbound" | "outbound",
  ) {
    // Step 1: Find or create a real Batch document so getBatches() can find it
    let batch = await this.batchModel.findOne({
      warehouseId: new Types.ObjectId(warehouseId),
      itemId: new Types.ObjectId(itemId),
      batchNumber,
    }).exec();

    if (!batch) {
      batch = await this.batchModel.create({
        warehouseId: new Types.ObjectId(warehouseId),
        itemId: new Types.ObjectId(itemId),
        batchNumber,
        expiryDate,
      });
    } else if (batch.expiryDate !== expiryDate) {
      batch.expiryDate = expiryDate;
      await batch.save();
    }

    const batchId = batch._id as Types.ObjectId;

    // Step 2: Find or create balance keyed by batchId
    let balance = await this.balanceModel.findOne({ batchId }).exec();

    if (balance) {
      const delta = type === "inbound" ? mt : -mt;
      balance.currentQuantityMt = Math.max(0, balance.currentQuantityMt + delta);
      balance.lastUpdated = new Date().toISOString();
      await balance.save();
    } else {
      await this.balanceModel.create({
        warehouseId: new Types.ObjectId(warehouseId),
        itemId: new Types.ObjectId(itemId),
        batchId,
        currentQuantityMt: type === "inbound" ? mt : 0,
        thresholdTargetMt: GLOBAL_MT_THRESHOLD,
        lastUpdated: new Date().toISOString(),
      });
    }
  }

  private async _notifyKeepers(type: string, message: string, sheetId: string) {
    const keepers = await this.userModel
      .find({ role: "store_keeper", active: true })
      .lean()
      .exec();
    await Promise.all(
      keepers.map((k) =>
        this.notificationsService.notifyUser(
          (k._id as Types.ObjectId).toString(),
          type,
          message,
          sheetId,
        ),
      ),
    );
  }

  private async _markThresholdReachedIfNeeded() {
    const totalAvailableMt = await this._getTotalAvailableMt();
    await this._syncThresholdState(totalAvailableMt);
  }

  private async _getTotalAvailableMt() {
    const balances = await this.balanceModel.find().lean().exec();
    return balances.reduce((sum, b) => sum + (b.currentQuantityMt ?? 0), 0);
  }

  private async _getHistoricalThresholdBasis(currentTotalMt: number) {
    const outbound = await this.stockTxModel.aggregate([
      { $match: { type: "outbound", approvalStatus: "approved" } },
      { $group: { _id: null, total: { $sum: "$quantityMt" } } },
    ]);
    const approvedOutboundMt = outbound[0]?.total ?? 0;
    return currentTotalMt + approvedOutboundMt;
  }

  private async _syncThresholdState(totalAvailableMt: number) {
    const now = new Date().toISOString();
    const state = await this.thresholdStateModel.findOne({ key: THRESHOLD_STATE_KEY }).exec();

    if (!state) {
      return this.thresholdStateModel.create({
        key: THRESHOLD_STATE_KEY,
        thresholdTargetMt: GLOBAL_MT_THRESHOLD,
        hasReachedThreshold: totalAvailableMt >= GLOBAL_MT_THRESHOLD,
        reachedAt: totalAvailableMt >= GLOBAL_MT_THRESHOLD ? now : null,
        updatedAt: now,
      });
    }

    if (state.hasReachedThreshold || totalAvailableMt < GLOBAL_MT_THRESHOLD) return state;

    state.thresholdTargetMt = GLOBAL_MT_THRESHOLD;
    state.hasReachedThreshold = true;
    state.reachedAt = now;
    state.updatedAt = now;
    await state.save();
    return state;
  }

  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  private _mapSheet(sheet: any, includeLineItems: boolean) {
    return {
      id: (sheet._id as Types.ObjectId)?.toString() ?? sheet.id,
      companyName: sheet.companyName ?? null,
      reportTitle: sheet.reportTitle ?? null,
      stockReportFor: sheet.stockReportFor ?? null,
      documentDate: sheet.documentDate ?? null,
      documentNumber: sheet.documentNumber ?? null,
      referenceNumber: sheet.referenceNumber ?? null,
      preparedBy: sheet.preparedBy ?? null,
      reviewedBy: sheet.reviewedBy ?? null,
      approvedByField: sheet.approvedByField ?? null,
      totalBagsAll: sheet.totalBagsAll ?? 0,
      totalMtAll: sheet.totalMtAll ?? 0,
      approvalStatus: sheet.approvalStatus,
      approvedBy: sheet.approvedBy ?? null,
      approvedAt: sheet.approvedAt ?? null,
      rejectedBy: sheet.rejectedBy ?? null,
      rejectedAt: sheet.rejectedAt ?? null,
      rejectedReason: sheet.rejectedReason ?? null,
      submittedBy: sheet.submittedBy ?? null,
      invoiceId: sheet.invoiceId?.toString() ?? null,
      createdAt: sheet.createdAt,
      ...(includeLineItems ? { lineItems: sheet.lineItems ?? [] } : {}),
    };
  }

  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  private _mapInvoice(inv: any) {
    return {
      id: (inv._id as Types.ObjectId)?.toString() ?? inv.id,
      documentNumber: inv.documentNumber ?? null,
      documentDate: inv.documentDate ?? null,
      referenceNumber: inv.referenceNumber ?? null,
      warehouseName: inv.warehouseName ?? null,
      itemCode: inv.itemCode ?? null,
      itemName: inv.itemName ?? null,
      batchNumber: inv.batchNumber ?? null,
      allBatches: inv.allBatches ?? null,
      expiryDate: inv.expiryDate ?? null,
      quantityMt: inv.quantityMt ?? null,
      unit: inv.unit ?? null,
      unitPrice: inv.unitPrice ?? null,
      supplierName: inv.supplierName ?? null,
      supplierTrn: inv.supplierTrn ?? null,
      buyerName: inv.buyerName ?? null,
      buyerTrn: inv.buyerTrn ?? null,
      totalAmount: inv.totalAmount ?? null,
      vatAmount: inv.vatAmount ?? null,
      netAmount: inv.netAmount ?? null,
      currency: inv.currency ?? null,
      paymentTerms: inv.paymentTerms ?? null,
      dueDate: inv.dueDate ?? null,
    };
  }

  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  private async _mapSheetWithWarehouseNames(sheet: any) {
    const warehouses = await this.warehouseModel.find().lean().exec();
    const warehouseMap = new Map(
      warehouses.map((w) => [(w._id as Types.ObjectId).toString(), w.name]),
    );

    const lineItems = (sheet.lineItems ?? []).map((li: Record<string, unknown>) => {
      // Convert warehouseBags keys from IDs to names for display
      const warehouseBagsNamed: Record<string, number> = {};
      for (const [whId, bags] of Object.entries(li.warehouseBags as Record<string, number> ?? {})) {
        const name = warehouseMap.get(whId) ?? whId;
        warehouseBagsNamed[name] = bags;
      }
      return { ...li, warehouseBagsNamed };
    });

    return {
      ...this._mapSheet(sheet, false),
      lineItems,
    };
  }
}