import { Injectable } from '@nestjs/common';
import { and, desc, eq, gte, lte, sql, inArray, asc } from 'drizzle-orm';
import { InventoryQueryDto, MoveStockDto, PlaceHoldDto, PaginationQueryDto, ReleaseHoldSchema, CreateLpnSchema, InventoryStatus } from '@wms/shared';
import { z } from 'zod';
import { DbService, Tx } from '@/common/db/db.service';
import { inventoryBalances, inventoryMovements, lpns, serialUnits, skus, locations, zones, storers, inventoryHolds } 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 { requireRequestContext } from '@/common/context/request-context';
import { DomainError, NotFoundError, ValidationError, ErrorCodes } from '@/common/errors/domain-error';
import { D, paginate, offsetOf, resolveStorerScope, assertWarehouseAccess, serialize } from '@/common/util';
import { LookupService } from '@/modules/master-data/lookup.service';
import { LedgerService, balanceKey } from './ledger.service';
import Decimal from 'decimal.js';

@Injectable()
export class InventoryService {
  constructor(
    private readonly db: DbService,
    private readonly ledger: LedgerService,
    private readonly lookup: LookupService,
    private readonly numbering: NumberingService,
    private readonly audit: AuditService,
    private readonly events: EventBus,
  ) {}

  // ---------------- queries ----------------
  async query(q: InventoryQueryDto & PaginationQueryDto) {
    const storerId = resolveStorerScope(q.storerId);
    if (q.warehouseId) assertWarehouseAccess(q.warehouseId);
    return this.db.run(async (tx) => {
      const where = and(
        q.warehouseId ? eq(inventoryBalances.warehouseId, q.warehouseId) : undefined,
        storerId ? eq(inventoryBalances.storerId, storerId) : undefined,
        q.skuId ? eq(inventoryBalances.skuId, q.skuId) : undefined,
        q.skuCode ? eq(skus.code, q.skuCode) : undefined,
        q.locationId ? eq(inventoryBalances.locationId, q.locationId) : undefined,
        q.locationCode ? eq(locations.code, q.locationCode) : undefined,
        q.zoneId ? eq(locations.zoneId, q.zoneId) : undefined,
        q.lpnNumber ? eq(lpns.lpnNumber, q.lpnNumber) : undefined,
        q.lotNumber ? eq(inventoryBalances.lotNumber, q.lotNumber) : undefined,
        q.status ? eq(inventoryBalances.status, q.status) : undefined,
        q.expiryBefore ? lte(inventoryBalances.expiryDate, q.expiryBefore) : undefined,
        q.nonZeroOnly ? sql`${inventoryBalances.qtyOnHand} > 0` : undefined,
        q.q ? sql`(${skus.code} ilike ${'%' + q.q + '%'} or ${skus.description} ilike ${'%' + q.q + '%'})` : undefined,
      );
      const base = tx
        .select({
          id: inventoryBalances.id,
          warehouseId: inventoryBalances.warehouseId,
          storerId: inventoryBalances.storerId,
          storerCode: storers.code,
          skuId: inventoryBalances.skuId,
          skuCode: skus.code,
          skuDescription: skus.description,
          locationId: inventoryBalances.locationId,
          locationCode: locations.code,
          zoneCode: zones.code,
          lpnId: inventoryBalances.lpnId,
          lpnNumber: lpns.lpnNumber,
          lotNumber: inventoryBalances.lotNumber,
          expiryDate: inventoryBalances.expiryDate,
          status: inventoryBalances.status,
          qtyOnHand: inventoryBalances.qtyOnHand,
          qtyAllocated: inventoryBalances.qtyAllocated,
          qtyAvailable: sql<string>`${inventoryBalances.qtyOnHand} - ${inventoryBalances.qtyAllocated}`,
          firstReceivedAt: inventoryBalances.firstReceivedAt,
          lastMovedAt: inventoryBalances.lastMovedAt,
          cbm: sql<string>`coalesce(${skus.cbm}, 0) * ${inventoryBalances.qtyOnHand}`,
        })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
        .innerJoin(zones, eq(zones.id, locations.zoneId))
        .innerJoin(storers, eq(storers.id, inventoryBalances.storerId))
        .leftJoin(lpns, eq(lpns.id, inventoryBalances.lpnId))
        .where(where);

      if (q.groupBy === 'BALANCE') {
        const [{ count }] = await tx
          .select({ count: sql<number>`count(*)`.mapWith(Number) })
          .from(inventoryBalances)
          .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
          .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
          .leftJoin(lpns, eq(lpns.id, inventoryBalances.lpnId))
          .where(where);
        const rows = await base.orderBy(asc(skus.code), asc(locations.code)).limit(q.pageSize).offset(offsetOf(q));
        return paginate(rows, count, q);
      }

      // grouped summaries
      const groupCols =
        q.groupBy === 'SKU'
          ? [inventoryBalances.storerId, storers.code, inventoryBalances.skuId, skus.code, skus.description]
          : q.groupBy === 'LOT'
            ? [inventoryBalances.storerId, storers.code, inventoryBalances.skuId, skus.code, skus.description, inventoryBalances.lotNumber, inventoryBalances.expiryDate]
            : q.groupBy === 'LOCATION'
              ? [inventoryBalances.locationId, locations.code, zones.code]
              : [inventoryBalances.lpnId, lpns.lpnNumber, inventoryBalances.locationId, locations.code];
      const rows = await tx
        .select({
          storerId: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? sql<string | null>`null` : inventoryBalances.storerId,
          storerCode: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? sql<string | null>`null` : storers.code,
          skuId: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? sql<string | null>`null` : inventoryBalances.skuId,
          skuCode: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? sql<string | null>`null` : skus.code,
          skuDescription: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? sql<string | null>`null` : skus.description,
          lotNumber: q.groupBy === 'LOT' ? inventoryBalances.lotNumber : sql<string | null>`null`,
          expiryDate: q.groupBy === 'LOT' ? inventoryBalances.expiryDate : sql<string | null>`null`,
          locationId: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? inventoryBalances.locationId : sql<string | null>`null`,
          locationCode: q.groupBy === 'LOCATION' || q.groupBy === 'LPN' ? locations.code : sql<string | null>`null`,
          zoneCode: q.groupBy === 'LOCATION' ? zones.code : sql<string | null>`null`,
          lpnId: q.groupBy === 'LPN' ? inventoryBalances.lpnId : sql<string | null>`null`,
          lpnNumber: q.groupBy === 'LPN' ? lpns.lpnNumber : sql<string | null>`null`,
          qtyOnHand: sql<string>`sum(${inventoryBalances.qtyOnHand})`,
          qtyAllocated: sql<string>`sum(${inventoryBalances.qtyAllocated})`,
          qtyAvailable: sql<string>`sum(${inventoryBalances.qtyOnHand} - ${inventoryBalances.qtyAllocated})`,
          cbm: sql<string>`sum(coalesce(${skus.cbm},0) * ${inventoryBalances.qtyOnHand})`,
          buckets: sql<number>`count(*)`.mapWith(Number),
        })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
        .innerJoin(zones, eq(zones.id, locations.zoneId))
        .innerJoin(storers, eq(storers.id, inventoryBalances.storerId))
        .leftJoin(lpns, eq(lpns.id, inventoryBalances.lpnId))
        .where(where)
        .groupBy(...groupCols)
        .orderBy(sql`2`)
        .limit(q.pageSize)
        .offset(offsetOf(q));
      return paginate(rows, rows.length < q.pageSize ? offsetOf(q) + rows.length : offsetOf(q) + q.pageSize + 1, q);
    });
  }

  movements(q: { warehouseId?: string; storerId?: string; skuId?: string; locationId?: string; lpnId?: string; type?: string; refType?: string; refId?: string; from?: string; to?: string } & PaginationQueryDto) {
    const storerId = resolveStorerScope(q.storerId);
    return this.db.run(async (tx) => {
      const where = and(
        q.warehouseId ? eq(inventoryMovements.warehouseId, q.warehouseId) : undefined,
        storerId ? eq(inventoryMovements.storerId, storerId) : undefined,
        q.skuId ? eq(inventoryMovements.skuId, q.skuId) : undefined,
        q.locationId ? sql`(${inventoryMovements.fromLocationId} = ${q.locationId} or ${inventoryMovements.toLocationId} = ${q.locationId})` : undefined,
        q.lpnId ? sql`(${inventoryMovements.fromLpnId} = ${q.lpnId} or ${inventoryMovements.toLpnId} = ${q.lpnId})` : undefined,
        q.type ? eq(inventoryMovements.type, q.type as never) : undefined,
        q.refType ? eq(inventoryMovements.refType, q.refType) : undefined,
        q.refId ? eq(inventoryMovements.refId, q.refId) : undefined,
        q.from ? gte(inventoryMovements.performedAt, new Date(`${q.from}T00:00:00Z`)) : undefined,
        q.to ? lte(inventoryMovements.performedAt, new Date(`${q.to}T23:59:59Z`)) : undefined,
      );
      const [{ count }] = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(inventoryMovements).where(where);
      const rows = await tx.select().from(inventoryMovements).where(where).orderBy(desc(inventoryMovements.performedAt)).limit(q.pageSize).offset(offsetOf(q));
      const locIds = Array.from(new Set(rows.flatMap((r) => [r.fromLocationId, r.toLocationId]).filter(Boolean))) as string[];
      const skuIds = Array.from(new Set(rows.map((r) => r.skuId)));
      const lpnIds = Array.from(new Set(rows.flatMap((r) => [r.fromLpnId, r.toLpnId]).filter(Boolean))) as string[];
      const [locs, skuRows, lpnRows] = await Promise.all([
        locIds.length ? tx.select({ id: locations.id, code: locations.code }).from(locations).where(inArray(locations.id, locIds)) : [],
        skuIds.length ? tx.select({ id: skus.id, code: skus.code, description: skus.description }).from(skus).where(inArray(skus.id, skuIds)) : [],
        lpnIds.length ? tx.select({ id: lpns.id, lpnNumber: lpns.lpnNumber }).from(lpns).where(inArray(lpns.id, lpnIds)) : [],
      ]);
      const lm = new Map(locs.map((l) => [l.id, l.code]));
      const sm = new Map(skuRows.map((s) => [s.id, s]));
      const pm = new Map(lpnRows.map((l) => [l.id, l.lpnNumber]));
      const items = rows.map((r) => ({
        ...r,
        skuCode: sm.get(r.skuId)?.code,
        skuDescription: sm.get(r.skuId)?.description,
        fromLocationCode: r.fromLocationId ? lm.get(r.fromLocationId) : null,
        toLocationCode: r.toLocationId ? lm.get(r.toLocationId) : null,
        fromLpnNumber: r.fromLpnId ? pm.get(r.fromLpnId) : null,
        toLpnNumber: r.toLpnId ? pm.get(r.toLpnId) : null,
      }));
      return paginate(items, count, q);
    });
  }

  serials(q: { storerId?: string; skuId?: string; serialNumber?: string; status?: string; warehouseId?: string } & PaginationQueryDto) {
    const storerId = resolveStorerScope(q.storerId);
    return this.db.run(async (tx) => {
      const where = and(
        storerId ? eq(serialUnits.storerId, storerId) : undefined,
        q.skuId ? eq(serialUnits.skuId, q.skuId) : undefined,
        q.serialNumber ? sql`${serialUnits.serialNumber} ilike ${'%' + q.serialNumber + '%'}` : undefined,
        q.status ? eq(serialUnits.status, q.status as never) : undefined,
        q.warehouseId ? eq(serialUnits.warehouseId, q.warehouseId) : undefined,
      );
      const [{ count }] = await tx.select({ count: sql<number>`count(*)`.mapWith(Number) }).from(serialUnits).where(where);
      const rows = await tx
        .select({ serial: serialUnits, skuCode: skus.code, locationCode: locations.code, lpnNumber: lpns.lpnNumber })
        .from(serialUnits)
        .innerJoin(skus, eq(skus.id, serialUnits.skuId))
        .leftJoin(locations, eq(locations.id, serialUnits.locationId))
        .leftJoin(lpns, eq(lpns.id, serialUnits.lpnId))
        .where(where)
        .orderBy(serialUnits.serialNumber)
        .limit(q.pageSize)
        .offset(offsetOf(q));
      return paginate(rows.map((r) => ({ ...r.serial, skuCode: r.skuCode, locationCode: r.locationCode, lpnNumber: r.lpnNumber })), count, q);
    });
  }

  /** Everything on one LPN (RF "LPN inquiry"). */
  lpnInquiry(lpnNumber: string) {
    return this.db.run(async (tx) => {
      const lpn = await this.lookup.lpnByNumber(tx, lpnNumber);
      if (!lpn) throw new NotFoundError('LPN', lpnNumber);
      assertWarehouseAccess(lpn.warehouseId);
      const scopedStorer = resolveStorerScope();
      if (scopedStorer && lpn.storerId && lpn.storerId !== scopedStorer) throw new NotFoundError('LPN', lpnNumber);
      const contents = await tx
        .select({ balance: inventoryBalances, skuCode: skus.code, skuDescription: skus.description, locationCode: locations.code })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(locations, eq(locations.id, inventoryBalances.locationId))
        .where(and(eq(inventoryBalances.lpnId, lpn.id), sql`${inventoryBalances.qtyOnHand} > 0`, scopedStorer ? eq(inventoryBalances.storerId, scopedStorer) : undefined));
      if (scopedStorer && !lpn.storerId && !contents.length) throw new NotFoundError('LPN', lpnNumber);
      const loc = lpn.locationId ? await this.lookup.location(tx, lpn.locationId) : null;
      return serialize({ ...lpn, locationCode: loc?.code ?? null, contents: contents.map((c) => ({ ...c.balance, skuCode: c.skuCode, skuDescription: c.skuDescription, locationCode: c.locationCode })) });
    });
  }

  /** Everything in one location (RF "location inquiry"). */
  locationInquiry(warehouseId: string, code: string) {
    assertWarehouseAccess(warehouseId);
    const scopedStorer = resolveStorerScope();
    return this.db.run(async (tx) => {
      const loc = await this.lookup.locationByCode(tx, warehouseId, code);
      const contents = await tx
        .select({ balance: inventoryBalances, skuCode: skus.code, skuDescription: skus.description, lpnNumber: lpns.lpnNumber, storerCode: storers.code })
        .from(inventoryBalances)
        .innerJoin(skus, eq(skus.id, inventoryBalances.skuId))
        .innerJoin(storers, eq(storers.id, inventoryBalances.storerId))
        .leftJoin(lpns, eq(lpns.id, inventoryBalances.lpnId))
        .where(and(eq(inventoryBalances.locationId, loc.id), sql`${inventoryBalances.qtyOnHand} > 0`, scopedStorer ? eq(inventoryBalances.storerId, scopedStorer) : undefined));
      return serialize({ ...loc, contents: contents.map((c) => ({ ...c.balance, skuCode: c.skuCode, skuDescription: c.skuDescription, lpnNumber: c.lpnNumber, storerCode: c.storerCode })) });
    });
  }

  // ---------------- LPNs ----------------
  createLpns(dto: z.infer<typeof CreateLpnSchema>) {
    assertWarehouseAccess(dto.warehouseId);
    return this.db.run(async (tx) => {
      const rows = [];
      for (let i = 0; i < dto.count; i++) rows.push(await this.newLpn(tx, dto.warehouseId, dto.type, dto.locationId ?? null, dto.storerId ?? null));
      return rows;
    });
  }

  async newLpn(tx: Tx, warehouseId: string, type: 'PALLET' | 'CASE' | 'TOTE' | 'CAGE' | 'CONTAINER' = 'PALLET', locationId: string | null = null, storerId: string | null = null) {
    const ctx = requireRequestContext();
    const lpnNumber = await this.numbering.next('LPN', tx, warehouseId);
    const [row] = await tx.insert(lpns).values({ tenantId: ctx.tenantId!, warehouseId, lpnNumber, type, locationId, storerId }).returning();
    return row;
  }

  /** Resolve or create an LPN by number inside a warehouse. Accepts external pallet ids (e.g. SSCC). */
  async resolveLpn(tx: Tx, warehouseId: string, lpnNumber: string | undefined, createIfMissing: boolean, storerId: string | null = null) {
    if (!lpnNumber) return null;
    const existing = await this.lookup.lpnByNumber(tx, lpnNumber);
    if (existing) {
      if (existing.warehouseId !== warehouseId) throw new DomainError(ErrorCodes.WRONG_LPN, `LPN ${lpnNumber} belongs to another warehouse`);
      if (existing.status === 'SHIPPED') throw new DomainError(ErrorCodes.WRONG_LPN, `LPN ${lpnNumber} has been shipped`);
      return existing;
    }
    if (!createIfMissing) throw new NotFoundError('LPN', lpnNumber);
    const ctx = requireRequestContext();
    const [row] = await tx.insert(lpns).values({ tenantId: ctx.tenantId!, warehouseId, lpnNumber, type: 'PALLET', storerId }).returning();
    return row;
  }

  // ---------------- moves ----------------
  async move(dto: MoveStockDto, outerTx?: Tx) {
    assertWarehouseAccess(dto.warehouseId);
    return this.db.run(async (tx) => {
      if (dto.idempotencyKey) {
        const existing = await this.ledger.findByIdempotencyKey(tx, dto.idempotencyKey);
        if (existing) return { movements: [existing], idempotent: true };
      }
      const toLoc = dto.toLocationId ? await this.lookup.location(tx, dto.toLocationId) : dto.toLocationCode ? await this.lookup.locationByCode(tx, dto.warehouseId, dto.toLocationCode) : null;
      if (!toLoc) throw new ValidationError('Destination location is required');
      if (toLoc.warehouseId !== dto.warehouseId) throw new ValidationError('Destination is in another warehouse');

      const results = [];
      if (dto.mode === 'LPN') {
        if (!dto.lpnNumber) throw new ValidationError('lpnNumber is required for LPN moves');
        const lpn = await this.lookup.lpnByNumber(tx, dto.lpnNumber);
        if (!lpn || lpn.warehouseId !== dto.warehouseId) throw new NotFoundError('LPN', dto.lpnNumber);
        const contents = await tx.select().from(inventoryBalances).where(and(eq(inventoryBalances.lpnId, lpn.id), sql`${inventoryBalances.qtyOnHand} > 0`)).for('update');
        if (!contents.length) throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, `LPN ${dto.lpnNumber} is empty`);
        if (contents.some((c) => D(c.qtyAllocated).gt(0))) throw new DomainError(ErrorCodes.INVALID_STATE, 'LPN has allocated stock; cancel the allocation or pick it first');
        if (!toLoc.maxLpns || toLoc.maxLpns >= 1) await this.assertLpnCapacity(tx, toLoc, lpn.id);
        for (let i = 0; i < contents.length; i++) {
          const b = contents[i];
          results.push(
            await this.ledger.post(tx, {
              type: 'MOVE',
              qty: b.qtyOnHand,
              warehouseId: dto.warehouseId,
              storerId: b.storerId,
              skuId: b.skuId,
              from: { locationId: b.locationId, lpnId: b.lpnId, status: b.status },
              to: { locationId: toLoc.id, lpnId: lpn.id, status: b.status },
              lotNumber: b.lotNumber,
              expiryDate: b.expiryDate,
              reasonCodeId: dto.reasonCodeId ?? null,
              notes: dto.notes,
              idempotencyKey: dto.idempotencyKey ? `${dto.idempotencyKey}:${i}` : undefined,
              refType: 'MOVE',
            }),
          );
        }
      } else {
        const fromLoc = dto.fromLocationId ? await this.lookup.location(tx, dto.fromLocationId) : dto.fromLocationCode ? await this.lookup.locationByCode(tx, dto.warehouseId, dto.fromLocationCode) : null;
        if (!fromLoc) throw new ValidationError('Source location is required for quantity moves');
        const skuRef = dto.skuId ? { sku: await this.lookup.sku(tx, dto.skuId), packLevel: dto.packLevel ?? 'EACH' } : dto.barcode ? await this.lookup.skuByBarcode(tx, dto.barcode) : null;
        if (!skuRef) throw new ValidationError('skuId or barcode is required');
        if (!dto.qty) throw new ValidationError('qty is required');
        const { baseQty } = await this.lookup.toBaseQty(tx, skuRef.sku.id, dto.qty, dto.packLevel ?? skuRef.packLevel);
        const fromLpn = dto.fromLpnNumber ? await this.lookup.lpnByNumber(tx, dto.fromLpnNumber) : null;
        if (dto.fromLpnNumber && !fromLpn) throw new NotFoundError('LPN', dto.fromLpnNumber);
        const toLpn = await this.resolveLpn(tx, dto.warehouseId, dto.toLpnNumber, true, skuRef.sku.storerId);
        if (toLpn && toLpn.locationId && toLpn.locationId !== toLoc.id) throw new DomainError(ErrorCodes.WRONG_LPN, `LPN ${toLpn.lpnNumber} is at another location`);
        const status = (dto.inventoryStatus ?? 'AVAILABLE') as InventoryStatus;
        results.push(
          await this.ledger.post(tx, {
            type: 'MOVE',
            qty: baseQty,
            warehouseId: dto.warehouseId,
            storerId: skuRef.sku.storerId,
            skuId: skuRef.sku.id,
            from: { locationId: fromLoc.id, lpnId: fromLpn?.id ?? null, status },
            to: { locationId: toLoc.id, lpnId: toLpn?.id ?? null, status },
            lotNumber: dto.lotNumber,
            expiryDate: dto.expiryDate ?? null,
            reasonCodeId: dto.reasonCodeId ?? null,
            notes: dto.notes,
            idempotencyKey: dto.idempotencyKey,
            refType: 'MOVE',
          }),
        );
      }
      await this.events.publish('inventory.moved', { count: results.length, toLocationId: toLoc.id, lpnNumber: dto.lpnNumber }, tx, { warehouseId: dto.warehouseId });
      return { movements: results, idempotent: false };
    }, { tx: outerTx });
  }

  async assertLpnCapacity(tx: Tx, loc: typeof locations.$inferSelect, incomingLpnId: string | null) {
    if (!loc.maxLpns) return;
    const [{ count }] = await tx
      .select({ count: sql<number>`count(distinct ${inventoryBalances.lpnId})`.mapWith(Number) })
      .from(inventoryBalances)
      .where(and(eq(inventoryBalances.locationId, loc.id), sql`${inventoryBalances.qtyOnHand} > 0`, incomingLpnId ? sql`${inventoryBalances.lpnId} <> ${incomingLpnId}` : undefined));
    if (count >= loc.maxLpns) throw new DomainError(ErrorCodes.LOCATION_CAPACITY, `Location ${loc.code} is full (${count}/${loc.maxLpns} pallet positions)`);
  }

  // ---------------- holds ----------------
  placeHold(dto: PlaceHoldDto) {
    assertWarehouseAccess(dto.warehouseId);
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const targets = await this.resolveHoldTargets(tx, dto);
      if (!targets.length) throw new DomainError(ErrorCodes.INSUFFICIENT_STOCK, 'No available stock matched the hold target');
      const affected: string[] = [];
      for (const b of targets) {
        if (D(b.qtyAllocated).gt(0)) throw new DomainError(ErrorCodes.INVALID_STATE, `Balance at ${b.locationId} has allocated stock; cancel allocations before holding`);
        const m = await this.ledger.post(tx, {
          type: 'STATUS_CHANGE',
          qty: b.qtyOnHand,
          warehouseId: b.warehouseId,
          storerId: b.storerId,
          skuId: b.skuId,
          from: { locationId: b.locationId, lpnId: b.lpnId, status: b.status },
          to: { locationId: b.locationId, lpnId: b.lpnId, status: dto.toStatus },
          lotNumber: b.lotNumber,
          expiryDate: b.expiryDate,
          reasonCodeId: dto.reasonCodeId ?? null,
          notes: dto.notes,
          refType: 'HOLD',
          firstReceivedAt: b.firstReceivedAt,
        });
        affected.push(m.id);
      }
      const [hold] = await tx
        .insert(inventoryHolds)
        .values({ tenantId: ctx.tenantId!, warehouseId: dto.warehouseId, scope: dto.scope, targetId: dto.targetId, storerId: dto.storerId ?? targets[0].storerId, skuId: dto.skuId ?? null, reasonCodeId: dto.reasonCodeId ?? null, toStatus: dto.toStatus, affectedBalanceIds: affected, notes: dto.notes, placedById: ctx.userId! })
        .returning();
      await this.audit.log({ action: 'inventory.hold.place', entityType: 'inventory_hold', entityId: hold.id, after: dto }, tx);
      await this.events.publish('inventory.hold_placed', { holdId: hold.id, scope: dto.scope, targetId: dto.targetId, toStatus: dto.toStatus }, tx, { warehouseId: dto.warehouseId, storerId: hold.storerId ?? undefined });
      return hold;
    });
  }

  releaseHold(holdId: string, dto: z.infer<typeof ReleaseHoldSchema>) {
    const ctx = requireRequestContext();
    return this.db.run(async (tx) => {
      const [hold] = await tx.select().from(inventoryHolds).where(eq(inventoryHolds.id, holdId)).limit(1);
      if (!hold) throw new NotFoundError('Hold', holdId);
      if (hold.releasedAt) throw new DomainError(ErrorCodes.INVALID_STATE, 'Hold already released');
      assertWarehouseAccess(hold.warehouseId);
      resolveStorerScope(hold.storerId);
      // Release exactly what this hold moved: the status-change movements recorded at placement carry the
      // full balance identity, so overlapping holds on the same target do not release each other's stock.
      const moved = await tx.select().from(inventoryMovements).where(inArray(inventoryMovements.id, hold.affectedBalanceIds ?? []));
      const targets: { warehouseId: string; storerId: string; skuId: string; locationId: string; lpnId: string | null; lotNumber: string | null; expiryDate: string | null; status: InventoryStatus; qtyOnHand: Decimal; firstReceivedAt: Date | null }[] = [];
      for (const m of moved) {
        const key = balanceKey({ warehouseId: m.warehouseId, storerId: m.storerId, skuId: m.skuId, locationId: m.toLocationId!, lpnId: m.toLpnId, lotNumber: m.lotNumber, expiryDate: m.expiryDate, status: hold.toStatus });
        const [b] = await tx.select().from(inventoryBalances).where(eq(inventoryBalances.balanceKey, key)).for('update');
        if (!b || D(b.qtyOnHand).lte(0)) continue;
        const qty = D(b.qtyOnHand).lt(m.qty) ? D(b.qtyOnHand) : D(m.qty);
        targets.push({ warehouseId: b.warehouseId, storerId: b.storerId, skuId: b.skuId, locationId: b.locationId, lpnId: b.lpnId, lotNumber: b.lotNumber, expiryDate: b.expiryDate, status: b.status, qtyOnHand: qty, firstReceivedAt: b.firstReceivedAt });
      }
      for (const b of targets) {
        await this.ledger.post(tx, {
          type: 'STATUS_CHANGE',
          qty: b.qtyOnHand,
          warehouseId: b.warehouseId,
          storerId: b.storerId,
          skuId: b.skuId,
          from: { locationId: b.locationId, lpnId: b.lpnId, status: b.status },
          to: { locationId: b.locationId, lpnId: b.lpnId, status: dto.toStatus },
          lotNumber: b.lotNumber ?? undefined,
          expiryDate: b.expiryDate ?? undefined,
          notes: dto.notes,
          refType: 'HOLD',
          refId: hold.id,
          firstReceivedAt: b.firstReceivedAt ?? undefined,
        });
      }
      const [row] = await tx.update(inventoryHolds).set({ releasedAt: new Date(), releasedById: ctx.userId }).where(eq(inventoryHolds.id, holdId)).returning();
      await this.audit.log({ action: 'inventory.hold.release', entityType: 'inventory_hold', entityId: holdId, after: dto }, tx);
      await this.events.publish('inventory.hold_released', { holdId, toStatus: dto.toStatus, balances: targets.length }, tx, { warehouseId: hold.warehouseId, storerId: hold.storerId ?? undefined });
      return row;
    });
  }

  listHolds(warehouseId: string, active = true) {
    assertWarehouseAccess(warehouseId);
    return this.db.run((tx) =>
      tx
        .select()
        .from(inventoryHolds)
        .where(and(eq(inventoryHolds.warehouseId, warehouseId), active ? sql`${inventoryHolds.releasedAt} is null` : undefined))
        .orderBy(desc(inventoryHolds.placedAt)),
    );
  }

  private async resolveHoldTargets(tx: Tx, dto: PlaceHoldDto, fromStatus: InventoryStatus = 'AVAILABLE') {
    const base = and(eq(inventoryBalances.warehouseId, dto.warehouseId), eq(inventoryBalances.status, fromStatus), sql`${inventoryBalances.qtyOnHand} > 0`);
    switch (dto.scope) {
      case 'BALANCE':
        return tx.select().from(inventoryBalances).where(and(base, eq(inventoryBalances.id, dto.targetId))).for('update');
      case 'LPN':
        return tx.select().from(inventoryBalances).where(and(base, eq(inventoryBalances.lpnId, dto.targetId))).for('update');
      case 'SKU':
        return tx.select().from(inventoryBalances).where(and(base, eq(inventoryBalances.skuId, dto.targetId))).for('update');
      case 'LOCATION':
        return tx.select().from(inventoryBalances).where(and(base, eq(inventoryBalances.locationId, dto.targetId))).for('update');
      case 'LOT':
        if (!dto.storerId) throw new ValidationError('storerId is required for LOT holds');
        return tx
          .select()
          .from(inventoryBalances)
          .where(and(base, eq(inventoryBalances.storerId, dto.storerId), eq(inventoryBalances.lotNumber, dto.targetId), dto.skuId ? eq(inventoryBalances.skuId, dto.skuId) : undefined))
          .for('update');
    }
  }
}