import { BadRequestException, Injectable } from "@nestjs/common";
import { InjectModel } from "@nestjs/mongoose";
import { Model, Types } from "mongoose";
import * as ExcelJS from "exceljs";
import { Batch, BatchDocument } from "./schemas/batch.schema";
import { ImportError, ImportLog, ImportLogDocument, ImportSummary } from "./schemas/import-log.schema";
import { InventoryBalance, InventoryBalanceDocument } from "./schemas/inventory-balance.schema";
import { Item, ItemDocument } from "./schemas/item.schema";
import { RotationInLine, RotationOutLine, RotationTransaction, RotationTransactionDocument } from "./schemas/rotation-transaction.schema";
import { StockMovementSheet, StockMovementSheetDocument } from "./schemas/stock-movement-sheet.schema";
import { TransferLine, TransferTransaction, TransferTransactionDocument } from "./schemas/transfer-transaction.schema";
import { Vendor, VendorDocument } from "./schemas/vendor.schema";
import { Warehouse, WarehouseDocument } from "./schemas/warehouse.schema";

const MT_TOLERANCE = 0.001;

const COLUMNS = [
  "SL", "Date", "Voucher", "Type", "Item Code", "Barcode", "Item Name", "Vendor",
  "Main Location", "Warehouse", "Batch", "Destination Batch", "Sample Reg. No.",
  "Blend", "Grain Type", "Variant", "Process Type", "COO", "Unit",
  "Qty (Bags)", "Qty (MT)", "Production Date", "Expiry Date",
] as const;

interface RawRow {
  rowNumber: number;
  date: string;
  voucher: string;
  type: string;
  itemCode: string;
  vendor: string;
  warehouse: string;
  batch: string;
  destinationBatch: string;
  sampleRegNo: string;
  qtyBags: number | null;
  qtyMt: number | null;
  productionDate: string;
  expiryDate: string;
}

interface GrnLine {
  itemCode: string;
  batchNumber: string;
  productionDate: string | null;
  expiryDate: string;
  vendor: string;
  sampleRegNo: string | null;
  warehouseBags: Record<string, number>; // warehouse name -> bags
  rowNumbers: number[];
}

export interface GrnGroup {
  action: "create" | "update";
  voucher: string;
  date: string;
  lines: GrnLine[];
  rowNumbers: number[];
}

interface RotationLine {
  itemCode: string;
  batchNumber: string;
  quantityMt: number;
  productionDate: string | null;
  expiryDate: string | null;
  warehouse?: string; // rotation-out only: which warehouse's batch this draws from
}

export interface RotationGroup {
  action: "create" | "update";
  voucher: string;
  date: string;
  warehouse: string;
  vendor: string;
  rotationIn: RotationLine[];
  rotationOut: RotationLine[];
  rowNumbers: number[];
}

export interface TransferGroup {
  action: "create" | "update";
  key: string;
  date: string;
  itemCode: string;
  batchNumber: string;
  destinationBatchNumber: string;
  sourceWarehouse: string;
  destinationWarehouse: string;
  vendor: string | null;
  quantityMt: number;
  rowNumbers: number[];
}

export interface ImportPreviewResult {
  fileName: string;
  grn: GrnGroup[];
  rotation: RotationGroup[];
  transfer: TransferGroup[];
  errors: ImportError[];
  warnings: ImportError[];
}

export interface ImportCommitResult {
  summary: ImportSummary;
  errors: ImportError[];
}

function excelDateToIso(value: unknown): string {
  if (value == null || value === "") return "";
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  if (typeof value === "number") {
    // Excel serial date (days since 1899-12-30)
    const ms = Math.round((value - 25569) * 86400 * 1000);
    return new Date(ms).toISOString().slice(0, 10);
  }
  const s = String(value).trim();
  const d = new Date(s);
  if (!isNaN(d.getTime())) return d.toISOString().slice(0, 10);
  return s;
}

function cellString(value: unknown): string {
  if (value == null) return "";
  if (typeof value === "object" && "text" in (value as Record<string, unknown>)) {
    return String((value as { text: unknown }).text ?? "").trim();
  }
  return String(value).trim();
}

function cellNumber(value: unknown): number | null {
  if (value == null || value === "") return null;
  const n = typeof value === "number" ? value : parseFloat(String(value));
  return isNaN(n) ? null : n;
}

@Injectable()
export class ImportService {
  constructor(
    @InjectModel(Item.name) private readonly itemModel: Model<ItemDocument>,
    @InjectModel(Warehouse.name) private readonly warehouseModel: Model<WarehouseDocument>,
    @InjectModel(Vendor.name) private readonly vendorModel: Model<VendorDocument>,
    @InjectModel(Batch.name) private readonly batchModel: Model<BatchDocument>,
    @InjectModel(InventoryBalance.name) private readonly balanceModel: Model<InventoryBalanceDocument>,
    @InjectModel(StockMovementSheet.name) private readonly sheetModel: Model<StockMovementSheetDocument>,
    @InjectModel(RotationTransaction.name) private readonly rotationModel: Model<RotationTransactionDocument>,
    @InjectModel(TransferTransaction.name) private readonly transferModel: Model<TransferTransactionDocument>,
    @InjectModel(ImportLog.name) private readonly importLogModel: Model<ImportLogDocument>,
  ) {}

  // ── Preview (dry run, no writes) ────────────────────────────────────────

  async preview(buffer: Buffer, fileName: string): Promise<ImportPreviewResult> {
    const rawRows = await this._parseWorkbook(buffer);
    const [items, warehouses, vendors, sheets, rotations, transfers] = await Promise.all([
      this.itemModel.find().lean().exec(),
      this.warehouseModel.find().lean().exec(),
      this.vendorModel.find().lean().exec(),
      this.sheetModel.find().lean().exec(),
      this.rotationModel.find().lean().exec(),
      this.transferModel.find().lean().exec(),
    ]);

    const errors: ImportError[] = [];
    const warnings: ImportError[] = [];

    const itemByCode = new Map(items.map((i) => [i.itemCode, i]));
    const warehouseByName = this._buildWarehouseLookup(warehouses);
    const vendorByName = new Map(vendors.map((v) => [v.name.toLowerCase(), v]));
    const sheetByVoucher = new Map(sheets.filter((s) => s.voucher).map((s) => [s.voucher as string, s]));
    const rotationByVoucher = new Map(rotations.map((r) => [r.voucher, r]));

    const { grn, rotation, transfer } = this._groupRows(rawRows, errors);

    // Resolve + validate GRN groups
    const grnGroups: GrnGroup[] = [];
    for (const g of grn) {
      let ok = true;
      for (const line of g.lines) {
        if (!itemByCode.has(line.itemCode)) {
          errors.push({ type: "grn", groupKey: g.voucher, message: `Row ${line.rowNumbers.join(",")}: item code "${line.itemCode}" not found` });
          ok = false;
        }
        if (!vendorByName.has(line.vendor.toLowerCase())) {
          errors.push({ type: "grn", groupKey: g.voucher, message: `Row ${line.rowNumbers.join(",")}: vendor "${line.vendor}" not found` });
          ok = false;
        }
        for (const whName of Object.keys(line.warehouseBags)) {
          if (!warehouseByName.has(whName.toLowerCase())) {
            errors.push({ type: "grn", groupKey: g.voucher, message: `Row ${line.rowNumbers.join(",")}: warehouse "${whName}" not found` });
            ok = false;
          }
        }
      }
      if (ok) {
        grnGroups.push({
          action: sheetByVoucher.has(g.voucher) ? "update" : "create",
          voucher: g.voucher,
          date: g.date,
          lines: g.lines,
          rowNumbers: g.rowNumbers,
        });
      }
    }

    // Resolve + validate Rotation groups
    const rotationGroups: RotationGroup[] = [];
    for (const g of rotation) {
      let ok = true;
      if (!warehouseByName.has(g.warehouse.toLowerCase())) {
        errors.push({ type: "rotation", groupKey: g.voucher, message: `Rows ${g.rowNumbers.join(",")}: destination warehouse "${g.warehouse}" not found` });
        ok = false;
      }
      if (!g.vendor) {
        errors.push({ type: "rotation", groupKey: g.voucher, message: `Rows ${g.rowNumbers.join(",")}: vendor is required (missing on Rotation In row)` });
        ok = false;
      } else if (!vendorByName.has(g.vendor.toLowerCase())) {
        errors.push({ type: "rotation", groupKey: g.voucher, message: `Rows ${g.rowNumbers.join(",")}: vendor "${g.vendor}" not found` });
        ok = false;
      }
      for (const line of [...g.rotationIn, ...g.rotationOut]) {
        if (!itemByCode.has(line.itemCode)) {
          errors.push({ type: "rotation", groupKey: g.voucher, message: `Rows ${g.rowNumbers.join(",")}: item code "${line.itemCode}" not found` });
          ok = false;
        }
      }
      for (const line of g.rotationOut) {
        if (line.warehouse && !warehouseByName.has(line.warehouse.toLowerCase())) {
          errors.push({ type: "rotation", groupKey: g.voucher, message: `Rows ${g.rowNumbers.join(",")}: source warehouse "${line.warehouse}" not found` });
          ok = false;
        }
      }
      const inTotal = g.rotationIn.reduce((s, l) => s + l.quantityMt, 0);
      const outTotal = g.rotationOut.reduce((s, l) => s + l.quantityMt, 0);
      if (Math.abs(inTotal - outTotal) > MT_TOLERANCE) {
        errors.push({ type: "rotation", groupKey: g.voucher, message: `Rotation In (${inTotal.toFixed(4)} MT) does not equal Rotation Out (${outTotal.toFixed(4)} MT)` });
        ok = false;
      }
      if (ok) {
        rotationGroups.push({
          action: rotationByVoucher.has(g.voucher) ? "update" : "create",
          voucher: g.voucher,
          date: g.date,
          warehouse: g.warehouse,
          vendor: g.vendor,
          rotationIn: g.rotationIn,
          rotationOut: g.rotationOut,
          rowNumbers: g.rowNumbers,
        });
      }
    }

    // Resolve + validate Transfer groups
    const transferGroups: TransferGroup[] = [];
    for (const g of transfer) {
      let ok = true;
      if (!itemByCode.has(g.itemCode)) {
        errors.push({ type: "transfer", groupKey: g.key, message: `Rows ${g.rowNumbers.join(",")}: item code "${g.itemCode}" not found` });
        ok = false;
      }
      if (!warehouseByName.has(g.sourceWarehouse.toLowerCase())) {
        errors.push({ type: "transfer", groupKey: g.key, message: `Rows ${g.rowNumbers.join(",")}: source warehouse "${g.sourceWarehouse}" not found` });
        ok = false;
      }
      if (!warehouseByName.has(g.destinationWarehouse.toLowerCase())) {
        errors.push({ type: "transfer", groupKey: g.key, message: `Rows ${g.rowNumbers.join(",")}: destination warehouse "${g.destinationWarehouse}" not found` });
        ok = false;
      }
      if (g.vendor && !vendorByName.has(g.vendor.toLowerCase())) {
        errors.push({ type: "transfer", groupKey: g.key, message: `Rows ${g.rowNumbers.join(",")}: vendor "${g.vendor}" not found` });
        ok = false;
      }
      if (ok) {
        const existing = await this._findTransferMatch(g, transfers, itemByCode, warehouseByName);
        transferGroups.push({
          action: existing ? "update" : "create",
          key: g.key,
          date: g.date,
          itemCode: g.itemCode,
          batchNumber: g.batchNumber,
          destinationBatchNumber: g.destinationBatchNumber || g.batchNumber,
          sourceWarehouse: g.sourceWarehouse,
          destinationWarehouse: g.destinationWarehouse,
          vendor: g.vendor || null,
          quantityMt: g.quantityMt,
          rowNumbers: g.rowNumbers,
        });
      }
    }

    // Chronological balance-shortfall simulation (warnings only — mirrors the
    // live app's own Math.max(0, ...) clamping behavior, doesn't block import)
    this._simulateBalances(grnGroups, rotationGroups, transferGroups, itemByCode, warehouseByName, warnings);

    return { fileName, grn: grnGroups, rotation: rotationGroups, transfer: transferGroups, errors, warnings };
  }

  // ── Commit (real writes) ─────────────────────────────────────────────────

  async commit(result: ImportPreviewResult, actor: { name: string }): Promise<ImportCommitResult> {
    const errors: ImportError[] = [...result.errors];
    const summary: ImportSummary = {
      grnCreated: 0, grnUpdated: 0, rotationCreated: 0, rotationUpdated: 0,
      transferCreated: 0, transferUpdated: 0, errorCount: 0,
    };

    const [items, warehouses, vendors] = await Promise.all([
      this.itemModel.find().lean().exec(),
      this.warehouseModel.find().lean().exec(),
      this.vendorModel.find().lean().exec(),
    ]);
    const itemByCode = new Map(items.map((i) => [i.itemCode, i]));
    const warehouseByName = this._buildWarehouseLookup(warehouses);
    const vendorByName = new Map(vendors.map((v) => [v.name.toLowerCase(), v]));

    const now = new Date().toISOString();

    // Process chronologically so same-batch dependencies within this import
    // resolve regardless of row order.
    type Event = { date: string; kind: "grn" | "rotation" | "transfer"; group: GrnGroup | RotationGroup | TransferGroup };
    const events: Event[] = [
      ...result.grn.map((g) => ({ date: g.date, kind: "grn" as const, group: g })),
      ...result.rotation.map((g) => ({ date: g.date, kind: "rotation" as const, group: g })),
      ...result.transfer.map((g) => ({ date: g.date, kind: "transfer" as const, group: g })),
    ].sort((a, b) => a.date.localeCompare(b.date));

    for (const event of events) {
      try {
        if (event.kind === "grn") {
          const created = await this._commitGrnGroup(event.group as GrnGroup, itemByCode, warehouseByName, vendorByName, actor, now);
          if (created) summary.grnCreated++; else summary.grnUpdated++;
        } else if (event.kind === "rotation") {
          const created = await this._commitRotationGroup(event.group as RotationGroup, itemByCode, warehouseByName, vendorByName, actor, now);
          if (created) summary.rotationCreated++; else summary.rotationUpdated++;
        } else {
          const created = await this._commitTransferGroup(event.group as TransferGroup, itemByCode, warehouseByName, vendorByName, actor, now);
          if (created) summary.transferCreated++; else summary.transferUpdated++;
        }
      } catch (err) {
        const key = "voucher" in event.group ? event.group.voucher : (event.group as TransferGroup).key;
        errors.push({ type: event.kind, groupKey: key, message: (err as Error).message });
      }
    }

    summary.errorCount = errors.length;

    const log = new this.importLogModel({
      importedBy: actor.name,
      importedAt: now,
      fileName: result.fileName,
      summary,
      errors,
    });
    await log.save();

    return { summary, errors };
  }

  // ── Parsing ───────────────────────────────────────────────────────────────

  private async _parseWorkbook(buffer: Buffer): Promise<RawRow[]> {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(buffer as unknown as ExcelJS.Buffer);
    const sheet = workbook.worksheets[0];
    if (!sheet) throw new BadRequestException("No worksheet found in file");

    const headerRow = sheet.getRow(1).values as unknown[];
    const colIndex = new Map<string, number>();
    headerRow.forEach((h, idx) => {
      if (typeof h === "string") colIndex.set(h.trim(), idx);
    });
    for (const required of ["Date", "Voucher", "Type", "Item Code", "Warehouse"]) {
      if (!colIndex.has(required)) {
        throw new BadRequestException(`Missing required column "${required}" — expected columns: ${COLUMNS.join(", ")}`);
      }
    }

    const get = (row: ExcelJS.Row, name: string) => {
      const idx = colIndex.get(name);
      return idx ? row.getCell(idx).value : null;
    };

    const rows: RawRow[] = [];
    sheet.eachRow((row, rowNumber) => {
      if (rowNumber === 1) return;
      const type = cellString(get(row, "Type"));
      const itemCode = cellString(get(row, "Item Code"));
      if (!type && !itemCode) return; // skip blank rows

      rows.push({
        rowNumber,
        date: excelDateToIso(get(row, "Date")),
        voucher: cellString(get(row, "Voucher")),
        type,
        itemCode,
        vendor: cellString(get(row, "Vendor")),
        warehouse: cellString(get(row, "Warehouse")),
        batch: cellString(get(row, "Batch")),
        destinationBatch: cellString(get(row, "Destination Batch")),
        sampleRegNo: cellString(get(row, "Sample Reg. No.")),
        qtyBags: cellNumber(get(row, "Qty (Bags)")),
        qtyMt: cellNumber(get(row, "Qty (MT)")),
        productionDate: excelDateToIso(get(row, "Production Date")),
        expiryDate: excelDateToIso(get(row, "Expiry Date")),
      });
    });

    return rows;
  }

  // ── Grouping ──────────────────────────────────────────────────────────────

  private _groupRows(rows: RawRow[], errors: ImportError[]) {
    const grnByVoucher = new Map<string, RawRow[]>();
    const rotationByVoucher = new Map<string, RawRow[]>();
    const transferByKey = new Map<string, RawRow[]>();

    for (const row of rows) {
      if (row.type === "Stock IN - GRN") {
        if (!row.voucher) { errors.push({ type: "grn", groupKey: `row-${row.rowNumber}`, message: `Row ${row.rowNumber}: GRN row missing Voucher` }); continue; }
        if (!grnByVoucher.has(row.voucher)) grnByVoucher.set(row.voucher, []);
        grnByVoucher.get(row.voucher)!.push(row);
      } else if (row.type === "Rotation IN" || row.type === "Rotation OUT") {
        if (!row.voucher) { errors.push({ type: "rotation", groupKey: `row-${row.rowNumber}`, message: `Row ${row.rowNumber}: Rotation row missing Voucher` }); continue; }
        if (!rotationByVoucher.has(row.voucher)) rotationByVoucher.set(row.voucher, []);
        rotationByVoucher.get(row.voucher)!.push(row);
      } else if (row.type === "Stock IN - Transfer" || row.type === "Stock OUT - Transfer") {
        const key = `${row.date}|${row.itemCode}|${row.batch}`;
        if (!transferByKey.has(key)) transferByKey.set(key, []);
        transferByKey.get(key)!.push(row);
      } else {
        errors.push({ type: "grn", groupKey: `row-${row.rowNumber}`, message: `Row ${row.rowNumber}: unrecognized Type "${row.type}"` });
      }
    }

    const grn: Array<{ voucher: string; date: string; lines: GrnLine[]; rowNumbers: number[] }> = [];
    for (const [voucher, groupRows] of grnByVoucher) {
      const byLineKey = new Map<string, GrnLine>();
      for (const row of groupRows) {
        if (!row.batch || !row.expiryDate) {
          errors.push({ type: "grn", groupKey: voucher, message: `Row ${row.rowNumber}: missing Batch or Expiry Date` });
          continue;
        }
        const lineKey = `${row.itemCode}|${row.batch}`;
        if (!byLineKey.has(lineKey)) {
          byLineKey.set(lineKey, {
            itemCode: row.itemCode,
            batchNumber: row.batch,
            productionDate: row.productionDate || null,
            expiryDate: row.expiryDate,
            vendor: row.vendor,
            sampleRegNo: row.sampleRegNo || null,
            warehouseBags: {},
            rowNumbers: [],
          });
        }
        const line = byLineKey.get(lineKey)!;
        line.rowNumbers.push(row.rowNumber);
        if (row.warehouse && row.qtyBags != null) {
          line.warehouseBags[row.warehouse] = (line.warehouseBags[row.warehouse] ?? 0) + row.qtyBags;
        }
      }
      grn.push({ voucher, date: groupRows[0].date, lines: [...byLineKey.values()], rowNumbers: groupRows.map((r) => r.rowNumber) });
    }

    const rotation: Array<{ voucher: string; date: string; warehouse: string; vendor: string; rotationIn: RotationLine[]; rotationOut: RotationLine[]; rowNumbers: number[] }> = [];
    for (const [voucher, groupRows] of rotationByVoucher) {
      const rotationIn: RotationLine[] = [];
      const rotationOut: RotationLine[] = [];
      let destWarehouse = "";
      let vendor = "";
      for (const row of groupRows) {
        if (row.qtyMt == null) {
          errors.push({ type: "rotation", groupKey: voucher, message: `Row ${row.rowNumber}: missing Qty (MT)` });
          continue;
        }
        if (row.type === "Rotation IN") {
          destWarehouse = row.warehouse || destWarehouse;
          vendor = row.vendor || vendor;
          rotationIn.push({ itemCode: row.itemCode, batchNumber: row.batch, quantityMt: row.qtyMt, productionDate: row.productionDate || null, expiryDate: row.expiryDate || null });
        } else {
          rotationOut.push({ itemCode: row.itemCode, batchNumber: row.batch, quantityMt: row.qtyMt, productionDate: null, expiryDate: null, warehouse: row.warehouse });
        }
      }
      rotation.push({ voucher, date: groupRows[0].date, warehouse: destWarehouse, vendor, rotationIn, rotationOut, rowNumbers: groupRows.map((r) => r.rowNumber) });
    }

    const transfer: Array<{ key: string; date: string; itemCode: string; batchNumber: string; destinationBatchNumber: string; sourceWarehouse: string; destinationWarehouse: string; vendor: string; quantityMt: number; rowNumbers: number[] }> = [];
    for (const [key, groupRows] of transferByKey) {
      const inRow = groupRows.find((r) => r.type === "Stock IN - Transfer");
      const outRow = groupRows.find((r) => r.type === "Stock OUT - Transfer");
      if (!inRow || !outRow || groupRows.length !== 2) {
        errors.push({ type: "transfer", groupKey: key, message: `Rows ${groupRows.map((r) => r.rowNumber).join(",")}: expected exactly one Stock IN - Transfer and one Stock OUT - Transfer row sharing Date/Item Code/Batch, found ${groupRows.length} row(s)` });
        continue;
      }
      if (outRow.qtyMt == null) {
        errors.push({ type: "transfer", groupKey: key, message: `Row ${outRow.rowNumber}: missing Qty (MT)` });
        continue;
      }
      transfer.push({
        key,
        date: outRow.date,
        itemCode: outRow.itemCode,
        batchNumber: outRow.batch,
        destinationBatchNumber: inRow.destinationBatch || inRow.batch || outRow.batch,
        sourceWarehouse: outRow.warehouse,
        destinationWarehouse: inRow.warehouse,
        vendor: inRow.vendor || outRow.vendor,
        quantityMt: outRow.qtyMt,
        rowNumbers: [inRow.rowNumber, outRow.rowNumber],
      });
    }

    return { grn, rotation, transfer };
  }

  private _buildWarehouseLookup(warehouses: Array<Warehouse & { _id: Types.ObjectId }>) {
    const map = new Map<string, Warehouse & { _id: Types.ObjectId }>();
    for (const w of warehouses) {
      map.set(w.name.toLowerCase(), w);
      for (const alias of w.aliases ?? []) map.set(alias.toLowerCase(), w);
    }
    return map;
  }

  private async _findTransferMatch(
    g: { date: string; itemCode: string; batchNumber: string; sourceWarehouse: string; destinationWarehouse: string },
    transfers: Array<TransferTransaction & { _id: Types.ObjectId }>,
    itemByCode: Map<string, Item & { _id: Types.ObjectId }>,
    warehouseByName: Map<string, Warehouse & { _id: Types.ObjectId }>,
  ) {
    const item = itemByCode.get(g.itemCode);
    const srcWh = warehouseByName.get(g.sourceWarehouse.toLowerCase());
    const destWh = warehouseByName.get(g.destinationWarehouse.toLowerCase());
    if (!item || !srcWh || !destWh) return null;
    const day = g.date;
    return transfers.find((t) => {
      if (t.destinationWarehouseId.toString() !== destWh._id.toString()) return false;
      if (String(t.createdAt ?? "").slice(0, 10) !== day) return false;
      return (t.lines ?? []).some(
        (l) => l.itemId.toString() === item._id.toString() &&
          l.sourceWarehouseId.toString() === srcWh._id.toString() &&
          l.batchNumber === g.batchNumber,
      );
    }) ?? null;
  }

  // ── Balance shortfall simulation (preview-only, warnings) ──────────────────

  private _simulateBalances(
    grn: GrnGroup[], rotation: RotationGroup[], transfer: TransferGroup[],
    itemByCode: Map<string, Item & { _id: Types.ObjectId }>,
    warehouseByName: Map<string, Warehouse & { _id: Types.ObjectId }>,
    warnings: ImportError[],
  ) {
    // Best-effort: only checks rotation-out / transfer-out legs against the
    // volume added by GRN/rotation-in/transfer-in earlier in this same batch,
    // since checking against live balances here would require an async pass —
    // full shortfall detection happens for real (and is clamped, not blocking)
    // in commit()'s live-balance-seeded pass.
    const seen = new Map<string, number>();
    const key = (itemCode: string, warehouse: string, batch: string) => `${itemCode}|${warehouse.toLowerCase()}|${batch}`;

    type Ev = { date: string; add?: { itemCode: string; warehouse: string; batch: string; mt: number }; sub?: { itemCode: string; warehouse: string; batch: string; mt: number; ctx: string } };
    const events: Ev[] = [];

    for (const g of grn) {
      for (const line of g.lines) {
        for (const [wh, bags] of Object.entries(line.warehouseBags)) {
          events.push({ date: g.date, add: { itemCode: line.itemCode, warehouse: wh, batch: line.batchNumber, mt: bags } }); // bags as a proxy magnitude
        }
      }
    }
    for (const g of rotation) {
      for (const line of g.rotationIn) events.push({ date: g.date, add: { itemCode: line.itemCode, warehouse: g.warehouse, batch: line.batchNumber, mt: line.quantityMt } });
      for (const line of g.rotationOut) events.push({ date: g.date, sub: { itemCode: line.itemCode, warehouse: line.warehouse ?? "", batch: line.batchNumber, mt: line.quantityMt, ctx: `rotation ${g.voucher}` } });
    }
    for (const g of transfer) {
      events.push({ date: g.date, add: { itemCode: g.itemCode, warehouse: g.destinationWarehouse, batch: g.destinationBatchNumber, mt: g.quantityMt } });
      events.push({ date: g.date, sub: { itemCode: g.itemCode, warehouse: g.sourceWarehouse, batch: g.batchNumber, mt: g.quantityMt, ctx: `transfer ${g.key}` } });
    }

    events.sort((a, b) => a.date.localeCompare(b.date));
    for (const ev of events) {
      if (ev.add) {
        const k = key(ev.add.itemCode, ev.add.warehouse, ev.add.batch);
        seen.set(k, (seen.get(k) ?? 0) + ev.add.mt);
      }
      if (ev.sub) {
        const k = key(ev.sub.itemCode, ev.sub.warehouse, ev.sub.batch);
        const available = seen.get(k) ?? 0;
        if (ev.sub.mt > available + MT_TOLERANCE) {
          warnings.push({
            type: ev.sub.ctx.startsWith("transfer") ? "transfer" : "rotation",
            groupKey: ev.sub.ctx,
            message: `Batch "${ev.sub.batch}" at "${ev.sub.warehouse}" may not have ${ev.sub.mt.toFixed(2)} MT available from this import alone (only ${available.toFixed(2)} MT added earlier in this file) — it may already exist in current live stock, which this preview doesn't check line-by-line.`,
          });
        }
        seen.set(k, Math.max(0, available - ev.sub.mt));
      }
    }
  }

  // ── Commit helpers ──────────────────────────────────────────────────────

  private async _findOrCreateBatch(warehouseId: Types.ObjectId, itemId: Types.ObjectId, batchNumber: string, expiryDate: string | null, productionDate: string | null) {
    let batch = await this.batchModel.findOne({ warehouseId, itemId, batchNumber }).exec();
    if (!batch) {
      batch = await this.batchModel.create({ warehouseId, itemId, batchNumber, expiryDate: expiryDate ?? "", productionDate: productionDate ?? null });
    }
    return batch;
  }

  private async _applyBalanceDelta(batchId: Types.ObjectId, warehouseId: Types.ObjectId, itemId: Types.ObjectId, deltaMt: number) {
    let balance = await this.balanceModel.findOne({ batchId }).exec();
    if (balance) {
      balance.currentQuantityMt = Math.max(0, balance.currentQuantityMt + deltaMt);
      balance.lastUpdated = new Date().toISOString();
      await balance.save();
    } else {
      await this.balanceModel.create({
        warehouseId, itemId, batchId,
        currentQuantityMt: Math.max(0, deltaMt),
        thresholdTargetMt: 26000,
        lastUpdated: new Date().toISOString(),
      });
    }
  }

  private async _commitGrnGroup(
    g: GrnGroup,
    itemByCode: Map<string, Item & { _id: Types.ObjectId }>,
    warehouseByName: Map<string, Warehouse & { _id: Types.ObjectId }>,
    vendorByName: Map<string, Vendor & { _id: Types.ObjectId }>,
    actor: { name: string },
    now: string,
  ): Promise<boolean> {
    const existing = await this.sheetModel.findOne({ voucher: g.voucher }).exec();

    // If updating, reverse the old sheet's balance effects first.
    if (existing) {
      for (const li of existing.lineItems ?? []) {
        const totalBags = li.totalBags || 0;
        for (const [whId, bags] of Object.entries(li.warehouseBags ?? {})) {
          const mt = totalBags > 0 ? (Number(bags) / totalBags) * (li.totalMt ?? 0) : 0;
          if (mt <= 0) continue;
          const batch = await this.batchModel.findOne({ warehouseId: new Types.ObjectId(whId), itemId: li.itemId, batchNumber: li.batchNumber }).exec();
          if (batch) await this._applyBalanceDelta(batch._id as Types.ObjectId, new Types.ObjectId(whId), li.itemId, -mt);
        }
      }
    }

    const lineItems = [];
    let totalBagsAll = 0;
    let totalMtAll = 0;
    for (const line of g.lines) {
      const item = itemByCode.get(line.itemCode)!;
      const vendor = vendorByName.get(line.vendor.toLowerCase())!;
      const kgPerBag = item.bagWeightKg && item.bagWeightKg > 0 ? item.bagWeightKg : 0;
      const warehouseBags: Record<string, number> = {};
      let totalBags = 0;
      for (const [whName, bags] of Object.entries(line.warehouseBags)) {
        const wh = warehouseByName.get(whName.toLowerCase())!;
        warehouseBags[wh._id.toString()] = bags;
        totalBags += bags;
      }
      const totalMt = kgPerBag > 0 ? Number(((totalBags * kgPerBag) / 1000).toFixed(4)) : 0;
      totalBagsAll += totalBags;
      totalMtAll += totalMt;

      lineItems.push({
        lineNumber: lineItems.length + 1,
        itemId: item._id, itemCode: item.itemCode, barcode: item.barcode ?? null,
        itemName: item.description ?? item.itemName ?? null, 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: line.batchNumber, productionDate: line.productionDate, expiryDate: line.expiryDate,
        warehouseBags, totalBags, totalMt,
        shipmentNo: null, qualityReportNo: null, sampleRegNo: line.sampleRegNo,
        vendorId: vendor._id, vendor: vendor.name,
      });

      // Apply new balance effects per warehouse
      for (const [whName, bags] of Object.entries(line.warehouseBags)) {
        const wh = warehouseByName.get(whName.toLowerCase())!;
        const mt = totalBags > 0 ? kgPerBag > 0 ? Number(((bags * kgPerBag) / 1000).toFixed(4)) : 0 : 0;
        if (mt <= 0) continue;
        const batch = await this._findOrCreateBatch(wh._id, item._id, line.batchNumber, line.expiryDate, line.productionDate);
        await this._applyBalanceDelta(batch._id as Types.ObjectId, wh._id, item._id, mt);
      }
    }

    if (existing) {
      existing.set({ lineItems, totalBagsAll, totalMtAll });
      await existing.save();
      return false;
    }

    await this.sheetModel.create({
      voucher: g.voucher, companyName: null, reportTitle: null, stockReportFor: null,
      documentDate: g.date, documentNumber: null, referenceNumber: null,
      preparedBy: null, reviewedBy: null, approvedByField: null,
      lineItems, totalBagsAll, totalMtAll: Number(totalMtAll.toFixed(4)),
      approvalStatus: "approved", approvedBy: actor.name, approvedAt: now,
      rejectedBy: null, rejectedAt: null, rejectedReason: null,
      submittedBy: actor.name, lastEditedBy: null, lastEditedAt: null, lastEditReason: null,
      invoiceId: null, createdAt: now,
    });
    return true;
  }

  private async _commitRotationGroup(
    g: RotationGroup,
    itemByCode: Map<string, Item & { _id: Types.ObjectId }>,
    warehouseByName: Map<string, Warehouse & { _id: Types.ObjectId }>,
    vendorByName: Map<string, Vendor & { _id: Types.ObjectId }>,
    actor: { name: string },
    now: string,
  ): Promise<boolean> {
    const existing = await this.rotationModel.findOne({ voucher: g.voucher }).exec();

    if (existing) {
      for (const line of existing.rotationIn ?? []) {
        const batch = await this.batchModel.findById(line.batchId).exec();
        if (batch) await this._applyBalanceDelta(line.batchId, batch.warehouseId, line.itemId, -line.quantityMt);
      }
      for (const line of existing.rotationOut ?? []) {
        const batch = await this.batchModel.findById(line.batchId).exec();
        if (batch) await this._applyBalanceDelta(line.batchId, batch.warehouseId, line.itemId, line.quantityMt);
      }
    }

    const destWh = warehouseByName.get(g.warehouse.toLowerCase())!;
    const vendor = vendorByName.get(g.vendor.toLowerCase());
    if (!vendor) throw new Error(`Vendor "${g.vendor}" not found`);

    const rotationIn: RotationInLine[] = [];
    for (const line of g.rotationIn) {
      const item = itemByCode.get(line.itemCode)!;
      const batch = await this._findOrCreateBatch(destWh._id, item._id, line.batchNumber, line.expiryDate, line.productionDate);
      await this._applyBalanceDelta(batch._id as Types.ObjectId, destWh._id, item._id, line.quantityMt);
      rotationIn.push({ itemId: item._id, batchId: batch._id as Types.ObjectId, batchNumber: line.batchNumber, expiryDate: line.expiryDate, productionDate: line.productionDate, quantityMt: line.quantityMt });
    }

    const rotationOut: RotationOutLine[] = [];
    for (const line of g.rotationOut) {
      const item = itemByCode.get(line.itemCode)!;
      const wh = warehouseByName.get((line.warehouse ?? "").toLowerCase())!;
      const batch = await this._findOrCreateBatch(wh._id, item._id, line.batchNumber, null, null);
      await this._applyBalanceDelta(batch._id as Types.ObjectId, wh._id, item._id, -line.quantityMt);
      rotationOut.push({ itemId: item._id, batchId: batch._id as Types.ObjectId, batchNumber: line.batchNumber, quantityMt: line.quantityMt });
    }

    const totalMt = Number(g.rotationIn.reduce((s, l) => s + l.quantityMt, 0).toFixed(4));

    if (existing) {
      existing.set({ warehouseId: destWh._id, vendorId: vendor._id, vendor: vendor.name, rotationIn, rotationOut, totalMt });
      await existing.save();
      return false;
    }

    await this.rotationModel.create({
      warehouseId: destWh._id, vendorId: vendor._id, vendor: vendor.name, voucher: g.voucher,
      rotationIn, rotationOut, totalMt,
      approvalStatus: "approved", approvedBy: actor.name, approvedAt: now,
      rejectedBy: null, rejectedAt: null, rejectedReason: null,
      // createdAt carries the sheet's own business date (not the wall-clock
      // commit time) — RotationTransaction has no separate documentDate
      // field, and buildMovementLegs() reads createdAt as the event date.
      submittedBy: actor.name, createdAt: new Date(g.date).toISOString(),
    });
    return true;
  }

  private async _commitTransferGroup(
    g: TransferGroup,
    itemByCode: Map<string, Item & { _id: Types.ObjectId }>,
    warehouseByName: Map<string, Warehouse & { _id: Types.ObjectId }>,
    vendorByName: Map<string, Vendor & { _id: Types.ObjectId }>,
    actor: { name: string },
    now: string,
  ): Promise<boolean> {
    const item = itemByCode.get(g.itemCode)!;
    const srcWh = warehouseByName.get(g.sourceWarehouse.toLowerCase())!;
    const destWh = warehouseByName.get(g.destinationWarehouse.toLowerCase())!;
    const vendor = g.vendor ? vendorByName.get(g.vendor.toLowerCase()) : null;

    const transfers = await this.transferModel.find().lean().exec();
    const existing = await this._findTransferMatch(g, transfers as unknown as Array<TransferTransaction & { _id: Types.ObjectId }>, itemByCode, warehouseByName);

    if (existing) {
      const existingDoc = await this.transferModel.findById(existing._id).exec();
      if (existingDoc) {
        for (const line of existingDoc.lines ?? []) {
          await this._applyBalanceDelta(line.sourceBatchId, line.sourceWarehouseId, line.itemId, line.quantityMt);
          const destBatch = await this.batchModel.findOne({ warehouseId: destWh._id, itemId: line.itemId, batchNumber: line.destinationBatchNumber ?? line.batchNumber }).exec();
          if (destBatch) await this._applyBalanceDelta(destBatch._id as Types.ObjectId, destWh._id, line.itemId, -line.quantityMt);
        }
      }
    }

    const srcBatch = await this._findOrCreateBatch(srcWh._id, item._id, g.batchNumber, null, null);
    await this._applyBalanceDelta(srcBatch._id as Types.ObjectId, srcWh._id, item._id, -g.quantityMt);

    const destBatch = await this._findOrCreateBatch(destWh._id, item._id, g.destinationBatchNumber, srcBatch.expiryDate, srcBatch.productionDate);
    await this._applyBalanceDelta(destBatch._id as Types.ObjectId, destWh._id, item._id, g.quantityMt);

    const line: TransferLine = {
      itemId: item._id, sourceBatchId: srcBatch._id as Types.ObjectId, sourceWarehouseId: srcWh._id,
      batchNumber: g.batchNumber, destinationBatchNumber: g.destinationBatchNumber,
      expiryDate: srcBatch.expiryDate ?? null, productionDate: srcBatch.productionDate ?? null,
      quantityMt: g.quantityMt,
    };

    if (existing) {
      await this.transferModel.findByIdAndUpdate(existing._id, {
        destinationWarehouseId: destWh._id, vendorId: vendor?._id ?? null, vendor: vendor?.name ?? null,
        lines: [line], totalMt: g.quantityMt,
      });
      return false;
    }

    await this.transferModel.create({
      destinationWarehouseId: destWh._id, vendorId: vendor?._id ?? null, vendor: vendor?.name ?? null,
      lines: [line], totalMt: g.quantityMt,
      approvalStatus: "approved", approvedBy: actor.name, approvedAt: now,
      rejectedBy: null, rejectedAt: null, rejectedReason: null,
      // createdAt carries the transfer's own business date, matching
      // _findTransferMatch()'s date-based lookup and buildMovementLegs().
      submittedBy: actor.name, createdAt: new Date(g.date).toISOString(),
    });
    return true;
  }
}