import { Injectable, Logger } from '@nestjs/common';
import { and, desc, eq, inArray, sql, isNull } from 'drizzle-orm';
import Decimal from 'decimal.js';
import { ReceiveLineDto, CloseAsnDto, ConfirmTaskDto, ResolveDiscrepancySchema, InventoryStatus, ItemCondition, hasPermission } from '@wms/shared';
import { z } from 'zod';
import { DbService, Tx } from '@/common/db/db.service';
import { asns, asnLines, receiptLines, receivingDiscrepancies, grns, tasks, storers, skus, locations, lpns } from '@/db/schema';
import { AuditService } from '@/common/audit/audit.service';
import { NumberingService } from '@/common/numbering/numbering.service';
import { EventBus } from '@/common/events/event-bus.service';
import { MailService } from '@/common/mail/mail.service';
import { requireRequestContext } from '@/common/context/request-context';
import { DomainError, NotFoundError, ValidationError, ForbiddenError, ErrorCodes } from '@/common/errors/domain-error';
import { D, ZERO, serialize, assertWarehouseAccess, resolveStorerScope } from '@/common/util';
import { LookupService } from '@/modules/master-data/lookup.service';
import { LedgerService } from '@/modules/inventory/ledger.service';
import { InventoryService } from '@/modules/inventory/inventory.service';
import { PutawayService } from '@/modules/inventory/putaway.service';
import { TasksService, TaskRow, TaskConfirmResult } from '@/modules/tasks/tasks.service';
import { AsnService } from './asn.service';
import { DocumentsService } from '@/modules/documents/documents.service';

const CONDITION_STATUS: Record<ItemCondition, InventoryStatus> = { GOOD: 'AVAILABLE', DAMAGED: 'DAMAGED', REJECTED: 'QUARANTINE' };

/**
 * Receiving against an ASN on the handheld, directed putaway, GRN posting and
 * the Receipt Confirmation Report (RCR) to the storer.
 */
@Injectable()
export class ReceivingService {
  private readonly logger = new Logger(ReceivingService.name);

  constructor(
    private readonly db: DbService,
    private readonly lookup: LookupService,
    private readonly ledger: LedgerService,
    private readonly inventory: InventoryService,
    private readonly putaway: PutawayService,
    private readonly tasksService: TasksService,
    private readonly asnService: AsnService,
    private readonly numbering: NumberingService,
    private readonly audit: AuditService,
    private readonly events: EventBus,
    private readonly mail: MailService,
    private readonly documents: DocumentsService,
  ) {
    this.tasksService.registerHandler('PUTAWAY', (tx, task, dto) => this.confirmPutaway(tx, task, dto));
  }

  /** One RF receive confirmation. */
  receive(asnId: string, dto: ReceiveLineDto) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      if (dto.idempotencyKey) {
        const existing = await this.ledger.findByIdempotencyKey(tx, dto.idempotencyKey);
        if (existing) return { idempotent: true, movementId: existing.id };
      }
      const [asn] = await tx.select().from(asns).where(eq(asns.id, asnId)).limit(1).for('update');
      if (!asn) throw new NotFoundError('ASN', asnId);
      assertWarehouseAccess(asn.warehouseId);
      if (!['EXPECTED', 'SCHEDULED', 'ARRIVED', 'RECEIVING'].includes(asn.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `ASN is ${asn.status}`);
      const wh = await this.lookup.warehouse(tx, asn.warehouseId);
      const whSettings = wh.settings as Record<string, unknown>;
      const storer = await this.lookup.storer(tx, asn.storerId);
      const storerSettings = storer.settings as Record<string, unknown>;

      // --- resolve sku / line ---
      let line = dto.asnLineId ? (await tx.select().from(asnLines).where(and(eq(asnLines.id, dto.asnLineId), eq(asnLines.asnId, asnId))).limit(1).for('update'))[0] : undefined;
      if (dto.asnLineId && !line) throw new NotFoundError('ASN line', dto.asnLineId);
      let sku;
      let packLevel = dto.packLevel;
      if (line) {
        sku = await this.lookup.sku(tx, line.skuId);
        if (dto.barcode) {
          const scanned = await this.lookup.skuByBarcode(tx, dto.barcode, asn.storerId);
          if (scanned.sku.id !== sku.id) throw new DomainError(ErrorCodes.WRONG_SKU, `Scanned ${scanned.sku.code} but line expects ${sku.code}`);
          packLevel = packLevel ?? scanned.packLevel;
        }
        packLevel = packLevel ?? line.packLevel;
      } else {
        const ref = dto.skuId ? { sku: await this.lookup.sku(tx, dto.skuId), packLevel: dto.packLevel ?? 'EACH' } : dto.barcode ? await this.lookup.skuByBarcode(tx, dto.barcode, asn.storerId) : null;
        if (!ref) throw new ValidationError('asnLineId, skuId or barcode is required');
        sku = ref.sku;
        packLevel = packLevel ?? ref.packLevel;
        // unexpected sku: try to match an open line for it, else receive as UNEXPECTED
        const open = await tx.select().from(asnLines).where(and(eq(asnLines.asnId, asnId), eq(asnLines.skuId, sku.id), inArray(asnLines.status, ['OPEN', 'PARTIAL']))).orderBy(asnLines.lineNo).limit(1).for('update');
        line = open[0];
      }
      if (sku.storerId !== asn.storerId) throw new DomainError(ErrorCodes.WRONG_SKU, 'SKU belongs to another storer');
      const { baseQty } = await this.lookup.toBaseQty(tx, sku.id, dto.qty, packLevel);

      // --- over-receipt control ---
      if (line) {
        const expected = D(line.expectedQty);
        const already = D(line.receivedQty).plus(line.damagedQty).plus(line.rejectedQty);
        const tolerancePct = D((storerSettings.receivingTolerancePct as number | undefined) ?? (whSettings.receivingTolerancePct as number | undefined) ?? 0);
        const limit = expected.mul(D(100).plus(tolerancePct)).div(100);
        if (dto.allowOver && !hasPermission(ctx.permissions, 'inbound.close')) throw new ForbiddenError('Over-receipt override requires the inbound.close permission (supervisor)', 'OVER_RECEIPT_OVERRIDE');
        if (already.plus(baseQty).gt(limit) && !dto.allowOver) {
          throw new DomainError(ErrorCodes.OVER_RECEIPT, `Receiving ${baseQty} would exceed expected ${expected} (tolerance ${tolerancePct}%); already received ${already}`, 422, { expected: expected.toString(), received: already.toString(), tolerancePct: tolerancePct.toString() });
        }
      }
      if (dto.condition !== 'GOOD' && !dto.reasonCodeId) throw new ValidationError('reasonCodeId is required for damaged or rejected receipts');

      // --- dock location & LPN ---
      const dockId = dto.locationId ?? asn.dockLocationId ?? (whSettings.defaultReceivingLocationId as string | undefined);
      if (!dockId) throw new ValidationError('No receiving location: assign a dock on arrival or set the warehouse default receiving location');
      const dock = await this.lookup.location(tx, dockId);
      if (dock.warehouseId !== asn.warehouseId) throw new ValidationError('Receiving location is in another warehouse');
      let lpn = null;
      if (dto.lpnNumber) lpn = await this.inventory.resolveLpn(tx, asn.warehouseId, dto.lpnNumber, true, asn.storerId);
      else if (whSettings.requireLpnOnReceipt !== false) lpn = await this.inventory.newLpn(tx, asn.warehouseId, 'PALLET', dock.id, asn.storerId);
      if (lpn && lpn.locationId && lpn.locationId !== dock.id && lpn.status === 'ACTIVE') {
        const [{ qty }] = await tx.select({ qty: sql<string>`coalesce(sum(qty_on_hand),0)` }).from(sql`inventory_balances`).where(sql`lpn_id = ${lpn.id}`);
        if (D(qty).gt(0)) throw new DomainError(ErrorCodes.WRONG_LPN, `LPN ${lpn.lpnNumber} already holds stock at another location`);
      }

      // --- ledger ---
      const status = CONDITION_STATUS[dto.condition];
      const movement = await this.ledger.post(tx, {
        type: asn.type === 'RETURN' ? 'RETURN_RECEIPT' : 'RECEIPT',
        qty: baseQty,
        warehouseId: asn.warehouseId,
        storerId: asn.storerId,
        skuId: sku.id,
        to: { locationId: dock.id, lpnId: lpn?.id ?? null, status },
        lotNumber: dto.lotNumber ?? line?.expectedLot ?? '',
        expiryDate: dto.expiryDate ?? line?.expectedExpiry ?? null,
        mfgDate: dto.mfgDate ?? null,
        serials: dto.serials,
        refType: 'ASN',
        refId: asn.id,
        reasonCodeId: dto.reasonCodeId ?? null,
        notes: dto.notes,
        idempotencyKey: dto.idempotencyKey,
        originAsnId: asn.id,
        skipSkuValidation: dto.condition !== 'GOOD', // damaged stock may lack lot/expiry; it is quarantined anyway
      });

      // --- receipt line + ASN line counters ---
      const [receipt] = await tx
        .insert(receiptLines)
        .values({
          tenantId: ctx.tenantId!,
          asnId: asn.id,
          asnLineId: line?.id ?? null,
          skuId: sku.id,
          lpnId: lpn?.id ?? null,
          locationId: dock.id,
          qty: baseQty,
          packLevel: packLevel ?? 'EACH',
          packQty: D(dto.qty),
          lotNumber: movement.lotNumber,
          expiryDate: movement.expiryDate,
          mfgDate: dto.mfgDate ?? null,
          condition: dto.condition,
          reasonCodeId: dto.reasonCodeId ?? null,
          serials: dto.serials,
          movementId: movement.id,
          receivedById: ctx.userId!,
          deviceId: ctx.deviceId ?? null,
          notes: dto.notes ?? null,
        })
        .returning();
      if (line) {
        const col = dto.condition === 'GOOD' ? 'receivedQty' : dto.condition === 'DAMAGED' ? 'damagedQty' : 'rejectedQty';
        const updated = { ...line, [col]: D(line[col]).plus(baseQty) } as typeof line;
        const total = D(updated.receivedQty).plus(updated.damagedQty).plus(updated.rejectedQty);
        await tx.update(asnLines).set({ [col]: updated[col], status: total.gte(line.expectedQty) ? 'COMPLETE' : 'PARTIAL' }).where(eq(asnLines.id, line.id));
      } else {
        await this.upsertDiscrepancy(tx, asn.id, null, sku.id, 'UNEXPECTED_SKU', ZERO, baseQty);
      }
      if (dto.condition === 'DAMAGED') await this.upsertDiscrepancy(tx, asn.id, line?.id ?? null, sku.id, 'DAMAGED', line ? D(line.expectedQty) : ZERO, baseQty, true);
      if (dto.condition === 'REJECTED') await this.upsertDiscrepancy(tx, asn.id, line?.id ?? null, sku.id, 'SHELF_LIFE_BELOW_MIN', line ? D(line.expectedQty) : ZERO, baseQty, true);
      if (line?.expectedLot && movement.lotNumber && movement.lotNumber !== line.expectedLot) await this.upsertDiscrepancy(tx, asn.id, line.id, sku.id, 'LOT_MISMATCH', D(line.expectedQty), baseQty, true);

      if (asn.status !== 'RECEIVING') await tx.update(asns).set({ status: 'RECEIVING', startedAt: asn.startedAt ?? new Date() }).where(eq(asns.id, asn.id));
      await this.asnService.recomputeTotals(tx, asn.id);

      // --- directed putaway task ---
      let putawayTask: TaskRow | null = null;
      if (whSettings.autoCreatePutawayTasks !== false) {
        const suggestion = await this.putaway.suggest(tx, { warehouseId: asn.warehouseId, storerId: asn.storerId, sku, qty: baseQty, packLevel: packLevel ?? 'EACH', lotNumber: movement.lotNumber, lpnId: lpn?.id ?? null, condition: dto.condition, asnType: asn.type });
        putawayTask = await this.tasksService.create(tx, {
          warehouseId: asn.warehouseId,
          type: 'PUTAWAY',
          priority: asn.priority,
          groupKey: asn.id,
          storerId: asn.storerId,
          skuId: sku.id,
          qty: baseQty,
          packLevel: packLevel ?? 'EACH',
          fromLocationId: dock.id,
          toLocationId: suggestion?.locationId ?? null,
          lpnId: lpn?.id ?? null,
          lotNumber: movement.lotNumber,
          expiryDate: movement.expiryDate,
          refType: 'ASN',
          refId: asn.id,
          data: { receiptLineId: receipt.id, asnNumber: asn.asnNumber, condition: dto.condition, inventoryStatus: status, suggestion: suggestion ?? null },
        });
      }

      await this.events.publish('receipt.recorded', { asnId: asn.id, asnNumber: asn.asnNumber, receiptLineId: receipt.id, skuId: sku.id, qty: baseQty.toString(), condition: dto.condition, lpnNumber: lpn?.lpnNumber ?? null }, tx, { warehouseId: asn.warehouseId, storerId: asn.storerId });
      return serialize({ idempotent: false, receipt, lpnNumber: lpn?.lpnNumber ?? null, movementId: movement.id, putawayTask: putawayTask ? (await this.tasksService.enrich(tx, [putawayTask]))[0] : null, line: line ? (await tx.select().from(asnLines).where(eq(asnLines.id, line.id)).limit(1))[0] : null });
    });
  }

  /** Undo a receipt confirmation (wrong scan). Only while the stock is still untouched at the dock. */
  reverseReceipt(receiptLineId: string, reason: string) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [r] = await tx.select().from(receiptLines).where(eq(receiptLines.id, receiptLineId)).limit(1).for('update');
      if (!r) throw new NotFoundError('Receipt line', receiptLineId);
      if (r.reversedAt) throw new DomainError(ErrorCodes.INVALID_STATE, 'Already reversed');
      const [asn] = await tx.select().from(asns).where(eq(asns.id, r.asnId)).limit(1).for('update');
      if (asn.status === 'CLOSED') throw new DomainError(ErrorCodes.INVALID_STATE, 'ASN is closed; use an adjustment');
      assertWarehouseAccess(asn.warehouseId);
      // A receipt can only be reversed while its stock is still where receiving put it.
      const [putaway] = await tx.select({ status: tasks.status }).from(tasks).where(and(eq(tasks.refType, 'ASN'), eq(tasks.refId, asn.id), eq(tasks.type, 'PUTAWAY'), r.lpnId ? eq(tasks.lpnId, r.lpnId) : sql`false`)).orderBy(desc(tasks.createdAt)).limit(1);
      if (putaway && !['PENDING', 'ASSIGNED', 'CANCELLED'].includes(putaway.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `Putaway is ${putaway.status}; reverse through an adjustment instead`);
      const status = CONDITION_STATUS[r.condition];
      const dock = await this.ledger.getBalance(tx, { warehouseId: asn.warehouseId, storerId: asn.storerId, skuId: r.skuId, locationId: r.locationId, lpnId: r.lpnId, lotNumber: r.lotNumber, expiryDate: r.expiryDate, status });
      if (!dock || D(dock.qtyOnHand).lt(r.qty)) throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, 'The received stock is no longer at the receiving location; reverse through an adjustment instead');
      const movement = await this.ledger.post(tx, {
        type: 'RECEIPT_REVERSAL', qty: r.qty, warehouseId: asn.warehouseId, storerId: asn.storerId, skuId: r.skuId,
        from: { locationId: r.locationId, lpnId: r.lpnId, status }, lotNumber: r.lotNumber, expiryDate: r.expiryDate, serials: r.serials,
        refType: 'ASN', refId: asn.id, notes: `Reversal: ${reason}`,
      });
      await tx.update(receiptLines).set({ reversedAt: new Date(), reversedById: ctx.userId, reversalMovementId: movement.id }).where(eq(receiptLines.id, r.id));
      if (r.asnLineId) {
        const [line] = await tx.select().from(asnLines).where(eq(asnLines.id, r.asnLineId)).limit(1).for('update');
        const col = r.condition === 'GOOD' ? 'receivedQty' : r.condition === 'DAMAGED' ? 'damagedQty' : 'rejectedQty';
        const newVal = D(line[col]).minus(r.qty);
        const total = D(line.receivedQty).plus(line.damagedQty).plus(line.rejectedQty).minus(r.qty);
        await tx.update(asnLines).set({ [col]: newVal.lt(0) ? ZERO : newVal, status: total.lte(0) ? 'OPEN' : total.gte(line.expectedQty) ? 'COMPLETE' : 'PARTIAL' }).where(eq(asnLines.id, line.id));
      }
      // cancel the pending putaway task for this receipt
      await tx.update(tasks).set({ status: 'CANCELLED', exceptionNotes: 'Receipt reversed' }).where(and(eq(tasks.refType, 'ASN'), eq(tasks.refId, asn.id), eq(tasks.type, 'PUTAWAY'), inArray(tasks.status, ['PENDING', 'ASSIGNED']), sql`${tasks.data}->>'receiptLineId' = ${r.id}`));
      await this.asnService.recomputeTotals(tx, asn.id);
      await this.audit.log({ action: 'receipt.reverse', entityType: 'receipt_line', entityId: r.id, after: { reason } }, tx);
      await this.events.publish('receipt.reversed', { asnId: asn.id, receiptLineId: r.id, reason }, tx, { warehouseId: asn.warehouseId, storerId: asn.storerId });
      return { ok: true, movementId: movement.id };
    });
  }

  /** PUTAWAY task handler: move the pallet/qty from the dock to the confirmed location. */
  private async confirmPutaway(tx: Tx, task: TaskRow, dto: ConfirmTaskDto): Promise<TaskConfirmResult> {
    const ctx = requireRequestContext();
    const sku = await this.lookup.sku(tx, task.skuId!);
    const data = task.data as { inventoryStatus?: InventoryStatus; condition?: ItemCondition };
    const status = data.inventoryStatus ?? 'AVAILABLE';
    const toLoc = dto.toLocationCode ? await this.lookup.locationByCode(tx, task.warehouseId, dto.toLocationCode) : task.toLocationId ? await this.lookup.location(tx, task.toLocationId) : null;
    if (!toLoc) throw new ValidationError('Scan the destination location');
    if (task.lpnId && dto.lpnNumber) {
      const [lpn] = await tx.select().from(lpns).where(eq(lpns.id, task.lpnId)).limit(1);
      if (lpn.lpnNumber !== dto.lpnNumber) throw new DomainError(ErrorCodes.WRONG_LPN, `Expected LPN ${lpn.lpnNumber}`);
    }
    const override = toLoc.id !== task.toLocationId;
    {
      // Validate the destination even when it is the suggested one: stock may have arrived there since the suggestion was made.
      const problem = await this.putaway.validateDestination(tx, { warehouseId: task.warehouseId, storerId: task.storerId!, sku, qty: D(task.qty ?? 0), packLevel: task.packLevel ?? 'EACH', lotNumber: task.lotNumber ?? '', lpnId: task.lpnId, condition: data.condition ?? 'GOOD' }, toLoc.id);
      if (problem) throw new DomainError(ErrorCodes.WRONG_LOCATION, problem);
      if (task.lpnId) await this.inventory.assertLpnCapacity(tx, toLoc, task.lpnId);
    }
    const remainingTask = D(task.qty ?? 0).minus(task.qtyDone);
    const qty = dto.qty ? (await this.lookup.toBaseQty(tx, sku.id, dto.qty, dto.packLevel ?? task.packLevel ?? 'EACH')).baseQty : remainingTask;
    if (qty.lte(0)) throw new ValidationError('Nothing to put away');
    if (qty.gt(remainingTask)) throw new ValidationError(`Quantity ${qty} exceeds the task remainder ${remainingTask}`);
    await this.ledger.post(tx, {
      type: 'PUTAWAY', qty, warehouseId: task.warehouseId, storerId: task.storerId!, skuId: sku.id,
      from: { locationId: task.fromLocationId!, lpnId: task.lpnId, status }, to: { locationId: toLoc.id, lpnId: task.lpnId, status },
      lotNumber: task.lotNumber ?? '', expiryDate: task.expiryDate, taskId: task.id, refType: 'ASN', refId: task.refId ?? undefined, idempotencyKey: dto.idempotencyKey, notes: override ? `Override: suggested ${task.toLocationId}` : undefined,
    });
    const done = D(task.qtyDone).plus(qty);
    const completed = done.gte(D(task.qty ?? 0));
    const [row] = await tx
      .update(tasks)
      .set({ qtyDone: done, status: completed ? 'COMPLETED' : 'IN_PROGRESS', completedAt: completed ? new Date() : null, completedById: completed ? ctx.userId : null, toLocationId: toLoc.id, data: { ...task.data, override, confirmedLocationId: toLoc.id } })
      .where(eq(tasks.id, task.id))
      .returning();
    return { task: row, completed, message: completed ? `Put away to ${toLoc.code}` : `Partial: ${done} of ${task.qty}` };
  }

  /** Marks physical receiving finished (before GRN). */
  markReceived(asnId: string) {
    return this.db.run(async (tx) => {
      const [asn] = await tx.select().from(asns).where(eq(asns.id, asnId)).limit(1).for('update');
      if (!asn) throw new NotFoundError('ASN', asnId);
      if (!['ARRIVED', 'RECEIVING'].includes(asn.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `ASN is ${asn.status}`);
      const [row] = await tx.update(asns).set({ status: 'RECEIVED', receivedAt: new Date() }).where(eq(asns.id, asnId)).returning();
      await this.audit.log({ action: 'asn.received', entityType: 'asn', entityId: asnId }, tx);
      await this.events.publish('asn.received', { asnId, asnNumber: asn.asnNumber }, tx, { warehouseId: asn.warehouseId, storerId: asn.storerId });
      return serialize(row);
    });
  }

  /** Posts the GRN: raises SHORT/OVER discrepancies, generates the RCR document, optionally emails it, triggers billing via event. */
  close(asnId: string, dto: CloseAsnDto) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [asn] = await tx.select().from(asns).where(eq(asns.id, asnId)).limit(1).for('update');
      if (!asn) throw new NotFoundError('ASN', asnId);
      resolveStorerScope(asn.storerId);
      if (!['RECEIVING', 'RECEIVED', 'ARRIVED'].includes(asn.status)) throw new DomainError(ErrorCodes.INVALID_STATE, `ASN is ${asn.status}`);
      const lines = await tx.select().from(asnLines).where(eq(asnLines.asnId, asnId));
      const anyReceipt = lines.some((l) => D(l.receivedQty).plus(l.damagedQty).plus(l.rejectedQty).gt(0));
      if (!anyReceipt) throw new DomainError(ErrorCodes.INVALID_STATE, 'Nothing has been received on this ASN; cancel it instead');

      for (const l of lines) {
        const received = D(l.receivedQty).plus(l.damagedQty).plus(l.rejectedQty);
        const expected = D(l.expectedQty);
        if (received.lt(expected)) {
          if (!dto.forceCloseShort) throw new DomainError(ErrorCodes.INVALID_STATE, `Line ${l.lineNo} is short (${received}/${expected}). Set forceCloseShort to close with shortages.`, 422, { lineNo: l.lineNo, expected: expected.toString(), received: received.toString() });
          await this.upsertDiscrepancy(tx, asnId, l.id, l.skuId, 'SHORT', expected, received);
          await tx.update(asnLines).set({ status: 'COMPLETE' }).where(eq(asnLines.id, l.id));
        } else if (received.gt(expected)) {
          await this.upsertDiscrepancy(tx, asnId, l.id, l.skuId, 'OVER', expected, received);
        }
      }

      const grnNumber = await this.numbering.next('GRN', tx, asn.warehouseId);
      const summary = await this.buildSummary(tx, asnId);
      const [grn] = await tx.insert(grns).values({ tenantId: ctx.tenantId!, asnId, grnNumber, postedById: ctx.userId!, summary }).returning();
      const storer = await this.lookup.storer(tx, asn.storerId);

      // documents
      const docData = { asn: { ...asn, storerName: storer.name, closedAt: new Date() }, grn, summary };
      const grnDoc = await this.documents.generate(tx, { type: 'GRN', number: grnNumber, refType: 'ASN', refId: asnId, data: docData });
      const rcrNumber = await this.numbering.next('RCR', tx, asn.warehouseId);
      const rcrDoc = await this.documents.generate(tx, { type: 'RCR', number: rcrNumber, refType: 'ASN', refId: asnId, data: docData });
      await tx.update(grns).set({ documentId: grnDoc.id, rcrDocumentId: rcrDoc.id }).where(eq(grns.id, grn.id));
      await tx.update(receivingDiscrepancies).set({ status: 'REPORTED', reportedAt: new Date() }).where(and(eq(receivingDiscrepancies.asnId, asnId), eq(receivingDiscrepancies.status, 'OPEN')));

      const [row] = await tx.update(asns).set({ status: 'CLOSED', closedAt: new Date(), receivedAt: asn.receivedAt ?? new Date(), notes: dto.notes ? `${asn.notes ?? ''}\n[Close] ${dto.notes}`.trim() : asn.notes }).where(eq(asns.id, asnId)).returning();

      const settings = storer.settings as { rcrEmails?: string[]; autoSendRcr?: boolean };
      const recipients = dto.rcrEmails ?? settings.rcrEmails ?? [];
      if ((dto.sendRcr || settings.autoSendRcr) && recipients.length) {
        await this.mail.queue({ to: recipients, subject: `Receipt Confirmation ${rcrNumber} - ${asn.asnNumber}${asn.externalRef ? ` (${asn.externalRef})` : ''}`, bodyHtml: this.rcrEmailBody(asn, summary), attachments: [{ documentId: rcrDoc.id }], refType: 'ASN', refId: asnId }, tx);
        await tx.update(grns).set({ rcrSentAt: new Date() }).where(eq(grns.id, grn.id));
      }
      await this.audit.log({ action: 'asn.close', entityType: 'asn', entityId: asnId, after: { grnNumber, rcrNumber, ...dto } }, tx);
      await this.events.publish('asn.closed', { asnId, asnNumber: asn.asnNumber, grnNumber, externalRef: asn.externalRef, type: asn.type, containerSize: asn.containerSize, summary }, tx, { warehouseId: asn.warehouseId, storerId: asn.storerId });
      return serialize({ ...row, grn: { ...grn, documentId: grnDoc.id, rcrDocumentId: rcrDoc.id } });
    });
  }

  resolveDiscrepancy(id: string, dto: z.infer<typeof ResolveDiscrepancySchema>) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [row] = await tx.update(receivingDiscrepancies).set({ status: dto.status, resolution: dto.resolution, resolvedAt: dto.status === 'RESOLVED' ? new Date() : null, resolvedById: dto.status === 'RESOLVED' ? ctx.userId : null }).where(eq(receivingDiscrepancies.id, id)).returning();
      if (!row) throw new NotFoundError('Discrepancy', id);
      await this.audit.log({ action: 'discrepancy.update', entityType: 'receiving_discrepancy', entityId: id, after: dto }, tx);
      return row;
    });
  }

  // ---- helpers ----
  private async upsertDiscrepancy(tx: Tx, asnId: string, asnLineId: string | null, skuId: string, type: (typeof receivingDiscrepancies.$inferInsert)['type'], expected: Decimal, actual: Decimal, accumulate = false) {
    const ctx = requireRequestContext();
    const [existing] = await tx.select().from(receivingDiscrepancies).where(and(eq(receivingDiscrepancies.asnId, asnId), asnLineId ? eq(receivingDiscrepancies.asnLineId, asnLineId) : isNull(receivingDiscrepancies.asnLineId), eq(receivingDiscrepancies.skuId, skuId), eq(receivingDiscrepancies.type, type))).limit(1);
    // Quantity discrepancies (OVER/SHORT) carry actual - expected; quality discrepancies
    // (DAMAGED, LOT_MISMATCH, SHELF_LIFE...) carry the affected quantity as a negative impact.
    const variance = (a: Decimal) => (accumulate || type === 'UNEXPECTED_SKU' ? a.neg() : a.minus(expected));
    if (existing) {
      const newActual = accumulate ? D(existing.actualQty).plus(actual) : actual;
      await tx.update(receivingDiscrepancies).set({ expectedQty: expected, actualQty: newActual, varianceQty: variance(newActual) }).where(eq(receivingDiscrepancies.id, existing.id));
    } else {
      await tx.insert(receivingDiscrepancies).values({ tenantId: ctx.tenantId!, asnId, asnLineId, skuId, type, expectedQty: expected, actualQty: actual, varianceQty: variance(actual) });
    }
  }

  async buildSummary(tx: Tx, asnId: string) {
    const lines = await tx.select({ l: asnLines, skuCode: skus.code, description: skus.description }).from(asnLines).innerJoin(skus, eq(skus.id, asnLines.skuId)).where(eq(asnLines.asnId, asnId)).orderBy(asnLines.lineNo);
    const receipts = await tx.select({ r: receiptLines, lpnNumber: lpns.lpnNumber, skuCode: skus.code }).from(receiptLines).leftJoin(lpns, eq(lpns.id, receiptLines.lpnId)).innerJoin(skus, eq(skus.id, receiptLines.skuId)).where(and(eq(receiptLines.asnId, asnId), isNull(receiptLines.reversedAt)));
    const discrepancies = await tx.select({ d: receivingDiscrepancies, skuCode: skus.code }).from(receivingDiscrepancies).innerJoin(skus, eq(skus.id, receivingDiscrepancies.skuId)).where(eq(receivingDiscrepancies.asnId, asnId));
    const totals = { expected: ZERO, good: ZERO, damaged: ZERO, rejected: ZERO };
    const lineRows = lines.map((x) => {
      totals.expected = totals.expected.plus(x.l.expectedQty);
      totals.good = totals.good.plus(x.l.receivedQty);
      totals.damaged = totals.damaged.plus(x.l.damagedQty);
      totals.rejected = totals.rejected.plus(x.l.rejectedQty);
      return { lineNo: x.l.lineNo, skuCode: x.skuCode, description: x.description, expected: x.l.expectedQty.toString(), good: x.l.receivedQty.toString(), damaged: x.l.damagedQty.toString(), rejected: x.l.rejectedQty.toString(), variance: D(x.l.receivedQty).plus(x.l.damagedQty).plus(x.l.rejectedQty).minus(x.l.expectedQty).toString(), expectedLot: x.l.expectedLot, externalLineRef: x.l.externalLineRef };
    });
    return serialize({
      lines: lineRows,
      receipts: receipts.map((r) => ({ skuCode: r.skuCode, qty: r.r.qty.toString(), lpnNumber: r.lpnNumber, lotNumber: r.r.lotNumber, expiryDate: r.r.expiryDate, condition: r.r.condition, receivedAt: r.r.receivedAt, serials: r.r.serials.length })),
      discrepancies: discrepancies.map((d) => ({ type: d.d.type, skuCode: d.skuCode, expected: d.d.expectedQty.toString(), actual: d.d.actualQty.toString(), variance: d.d.varianceQty.toString() })),
      totals: { expected: totals.expected.toString(), good: totals.good.toString(), damaged: totals.damaged.toString(), rejected: totals.rejected.toString(), pallets: new Set(receipts.map((r) => r.lpnNumber).filter(Boolean)).size },
    });
  }

  private rcrEmailBody(asn: typeof asns.$inferSelect, summary: ReturnType<typeof serialize<Record<string, unknown>>>) {
    const t = summary.totals as Record<string, string>;
    const disc = summary.discrepancies as { type: string; skuCode: string; expected: string; actual: string; variance: string }[];
    const rows = disc.map((d) => `<tr><td>${d.type}</td><td>${d.skuCode}</td><td>${d.expected}</td><td>${d.actual}</td><td>${d.variance}</td></tr>`).join('');
    return `<p>Dear Customer,</p><p>Please find attached the Receipt Confirmation Report for ${asn.asnNumber}${asn.externalRef ? ` (your reference ${asn.externalRef})` : ''}.</p>
<p>Expected: ${t.expected} &nbsp; Received good: ${t.good} &nbsp; Damaged: ${t.damaged} &nbsp; Rejected: ${t.rejected} &nbsp; Pallets: ${t.pallets}</p>
${disc.length ? `<p>Discrepancies:</p><table border="1" cellpadding="4"><tr><th>Type</th><th>SKU</th><th>Expected</th><th>Actual</th><th>Variance</th></tr>${rows}</table><p>Kindly acknowledge within 48 hours.</p>` : '<p>No discrepancies were recorded.</p>'}
<p>Regards,<br/>Warehouse Operations</p>`;
  }
}