کاردکس کالا و روش FIFO در نرم‌افزار انبارداری: از تئوری تا کوئری

CREATE TABLE stock_layer (
  id           BIGSERIAL PRIMARY KEY,
  item_id      BIGINT NOT NULL REFERENCES item(id),
  warehouse_id BIGINT NOT NULL REFERENCES warehouse(id),
  received_at  TIMESTAMPTZ NOT NULL,
  qty_in       NUMERIC(18, 3) NOT NULL,
  qty_left     NUMERIC(18, 3) NOT NULL CHECK (qty_left >= 0),
  unit_cost    NUMERIC(20, 2) NOT NULL
);

CREATE INDEX idx_layer_fifo
  ON stock_layer (item_id, warehouse_id, received_at)
  WHERE qty_left > 0;

async function consumeFifo(tx: Tx, itemId: bigint, whId: bigint, qty: Decimal) {
  const layers = await tx.query(
    `SELECT id, qty_left, unit_cost FROM stock_layer
      WHERE item_id = $1 AND warehouse_id = $2 AND qty_left > 0
      ORDER BY received_at, id
      FOR UPDATE`, [itemId, whId]);

  let remaining = qty;
  let cost = new Decimal(0);
  for (const l of layers.rows) {
    if (remaining.isZero()) break;
    const take = Decimal.min(remaining, l.qty_left);
    await tx.query('UPDATE stock_layer SET qty_left = qty_left - $1 WHERE id = $2',
                   [take.toString(), l.id]);
    cost = cost.plus(take.times(l.unit_cost));
    remaining = remaining.minus(take);
  }
  if (!remaining.isZero()) throw new Error('موجودی کافی نیست');
  return cost; // بهای کالای فروش‌رفته
}