Build a Warehouse Inventory System

// src/warehouse/inventory.ts — Real-time inventory with reservations, bin locations, and multi-warehouse
import { pool } from "../db";
import { Redis } from "ioredis";

const redis = new Redis(process.env.REDIS_URL!);

interface InventoryItem {
  sku: string;
  name: string;
  warehouseId: string;
  binLocation: string;         // e.g., "A-03-12" (aisle-rack-shelf)
  quantityOnHand: number;
  quantityReserved: number;    // allocated to pending orders
  quantityAvailable: number;   // onHand - reserved
  quantityInbound: number;     // purchase orders in transit
  reorderPoint: number;
  reorderQuantity: number;
  lastCountedAt: string;
  updatedAt: string;
}

type MovementType = "receive" | "ship" | "transfer" | "adjust" | "return" | "damage" | "cycle_count";

interface StockMovement {
  id: string;
  sku: string;
  warehouseId: string;
  type: MovementType;
  quantity: number;            // positive = in, negative = out
  referenceId: string;         // order ID, PO number, etc.
  binFrom: string | null;
  binTo: string | null;
  userId: string;
  notes: string;
  createdAt: string;
}

// Reserve stock for an order (atomic operation)
export async function reserveStock(
  items: Array<{ sku: string; quantity: number }>,
  orderId: string,
  warehouseId?: string
): Promise<{ success: boolean; errors: string[]; reservations: Array<{ sku: string; warehouseId: string; quantity: number }> }> {
  const errors: string[] = [];
  const reservations: Array<{ sku: string; warehouseId: string; quantity: number }> = [];

  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    for (const item of items) {
      // Lock the row for update
      const { rows } = await client.query(
        `SELECT warehouse_id, quantity_on_hand, quantity_reserved, bin_location
         FROM inventory WHERE sku = $1 ${warehouseId ? "AND warehouse_id = $2" : ""}
         ORDER BY (quantity_on_hand - quantity_reserved) DESC
         FOR UPDATE`,
        warehouseId ? [item.sku, warehouseId] : [item.sku]
      );

      let remaining = item.quantity;
      for (const row of rows) {
        const available = row.quantity_on_hand - row.quantity_reserved;
        if (available <= 0) continue;

        const toReserve = Math.min(remaining, available);
        await client.query(
          `UPDATE inventory SET quantity_reserved = quantity_reserved + $3, updated_at = NOW()
           WHERE sku = $1 AND warehouse_id = $2`,
          [item.sku, row.warehouse_id, toReserve]
        );

        reservations.push({ sku: item.sku, warehouseId: row.warehouse_id, quantity: toReserve });
        remaining -= toReserve;
        if (remaining <= 0) break;
      }

      if (remaining > 0) {
        errors.push(`Insufficient stock for ${item.sku}: need ${item.quantity}, can reserve ${item.quantity - remaining}`);
      }
    }

    if (errors.length > 0) {
      await client.query("ROLLBACK");
      return { success: false, errors, reservations: [] };
    }

    // Store reservation for release if order is cancelled
    await client.query(
      `INSERT INTO stock_reservations (order_id, items, created_at) VALUES ($1, $2, NOW())`,
      [orderId, JSON.stringify(reservations)]
    );

    await client.query("COMMIT");

    // Update Redis cache
    for (const res of reservations) {
      await updateCachedStock(res.sku, res.warehouseId);
    }

    // Check reorder points
    for (const item of items) {
      await checkReorderPoint(item.sku);
    }

    return { success: true, errors: [], reservations };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

// Release reservation (order cancelled)
export async function releaseReservation(orderId: string): Promise<void> {
  const { rows: [reservation] } = await pool.query(
    "SELECT items FROM stock_reservations WHERE order_id = $1", [orderId]
  );
  if (!reservation) return;

  const items: Array<{ sku: string; warehouseId: string; quantity: number }> = JSON.parse(reservation.items);
  for (const item of items) {
    await pool.query(
      `UPDATE inventory SET quantity_reserved = GREATEST(quantity_reserved - $3, 0), updated_at = NOW()
       WHERE sku = $1 AND warehouse_id = $2`,
      [item.sku, item.warehouseId, item.quantity]
    );
    await updateCachedStock(item.sku, item.warehouseId);
  }

  await pool.query("DELETE FROM stock_reservations WHERE order_id = $1", [orderId]);
}

// Receive stock (from purchase order or return)
export async function receiveStock(
  sku: string,
  warehouseId: string,
  quantity: number,
  binLocation: string,
  referenceId: string,
  type: "receive" | "return" = "receive",
  userId: string
): Promise<void> {
  await pool.query(
    `INSERT INTO inventory (sku, warehouse_id, bin_location, quantity_on_hand, quantity_reserved, reorder_point, reorder_quantity, updated_at)
     VALUES ($1, $2, $3, $4, 0, 10, 50, NOW())
     ON CONFLICT (sku, warehouse_id)
     DO UPDATE SET quantity_on_hand = inventory.quantity_on_hand + $4, bin_location = $3, updated_at = NOW()`,
    [sku, warehouseId, binLocation, quantity]
  );

  await recordMovement(sku, warehouseId, type, quantity, referenceId, null, binLocation, userId, "");
  await updateCachedStock(sku, warehouseId);

  // Update inbound quantity if receiving a PO
  if (type === "receive") {
    await pool.query(
      `UPDATE inventory SET quantity_inbound = GREATEST(quantity_inbound - $3, 0) WHERE sku = $1 AND warehouse_id = $2`,
      [sku, warehouseId, quantity]
    );
  }
}

// Ship stock (fulfill order)
export async function shipStock(
  items: Array<{ sku: string; warehouseId: string; quantity: number }>,
  orderId: string,
  userId: string
): Promise<void> {
  for (const item of items) {
    await pool.query(
      `UPDATE inventory SET
         quantity_on_hand = quantity_on_hand - $3,
         quantity_reserved = GREATEST(quantity_reserved - $3, 0),
         updated_at = NOW()
       WHERE sku = $1 AND warehouse_id = $2`,
      [item.sku, item.warehouseId, item.quantity]
    );

    await recordMovement(item.sku, item.warehouseId, "ship", -item.quantity, orderId, null, null, userId, "");
    await updateCachedStock(item.sku, item.warehouseId);
  }

  await pool.query("DELETE FROM stock_reservations WHERE order_id = $1", [orderId]);
}

// Transfer between warehouses
export async function transferStock(
  sku: string,
  fromWarehouse: string,
  toWarehouse: string,
  quantity: number,
  userId: string
): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    // Deduct from source
    await client.query(
      `UPDATE inventory SET quantity_on_hand = quantity_on_hand - $3, updated_at = NOW()
       WHERE sku = $1 AND warehouse_id = $2 AND quantity_on_hand - quantity_reserved >= $3`,
      [sku, fromWarehouse, quantity]
    );

    // Add to destination
    await client.query(
      `INSERT INTO inventory (sku, warehouse_id, quantity_on_hand, updated_at)
       VALUES ($1, $2, $3, NOW())
       ON CONFLICT (sku, warehouse_id)
       DO UPDATE SET quantity_on_hand = inventory.quantity_on_hand + $3, updated_at = NOW()`,
      [sku, toWarehouse, quantity]
    );

    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }

  await recordMovement(sku, fromWarehouse, "transfer", -quantity, `transfer-to-${toWarehouse}`, null, null, userId, "");
  await recordMovement(sku, toWarehouse, "transfer", quantity, `transfer-from-${fromWarehouse}`, null, null, userId, "");
}

// Get real-time stock across all warehouses
export async function getStockLevel(sku: string): Promise<{
  total: { onHand: number; reserved: number; available: number; inbound: number };
  warehouses: Array<{ warehouseId: string; onHand: number; reserved: number; available: number; binLocation: string }>;
}> {
  // Try cache first
  const cached = await redis.get(`stock:${sku}`);
  if (cached) return JSON.parse(cached);

  const { rows } = await pool.query(
    `SELECT warehouse_id, quantity_on_hand, quantity_reserved, quantity_inbound, bin_location
     FROM inventory WHERE sku = $1`, [sku]
  );

  const warehouses = rows.map((r: any) => ({
    warehouseId: r.warehouse_id,
    onHand: r.quantity_on_hand,
    reserved: r.quantity_reserved,
    available: r.quantity_on_hand - r.quantity_reserved,
    binLocation: r.bin_location || "",
  }));

  const total = {
    onHand: warehouses.reduce((s, w) => s + w.onHand, 0),
    reserved: warehouses.reduce((s, w) => s + w.reserved, 0),
    available: warehouses.reduce((s, w) => s + w.available, 0),
    inbound: rows.reduce((s: number, r: any) => s + (r.quantity_inbound || 0), 0),
  };

  const result = { total, warehouses };
  await redis.setex(`stock:${sku}`, 30, JSON.stringify(result)); // 30s cache
  return result;
}

async function checkReorderPoint(sku: string): Promise<void> {
  const { rows } = await pool.query(
    `SELECT warehouse_id, quantity_on_hand, quantity_reserved, reorder_point, reorder_quantity
     FROM inventory WHERE sku = $1`, [sku]
  );

  for (const row of rows) {
    const available = row.quantity_on_hand - row.quantity_reserved;
    if (available <= row.reorder_point) {
      const alertKey = `stock:alert:${sku}:${row.warehouse_id}`;
      const alerted = await redis.get(alertKey);
      if (!alerted) {
        await redis.setex(alertKey, 86400, "1");
        await redis.rpush("notification:queue", JSON.stringify({
          type: "low_stock", sku, warehouseId: row.warehouse_id,
          available, reorderPoint: row.reorder_point, suggestedOrder: row.reorder_quantity,
        }));
      }
    }
  }
}

async function updateCachedStock(sku: string, warehouseId: string): Promise<void> {
  await redis.del(`stock:${sku}`);
}

async function recordMovement(
  sku: string, warehouseId: string, type: MovementType, quantity: number,
  referenceId: string, binFrom: string | null, binTo: string | null,
  userId: string, notes: string
): Promise<void> {
  await pool.query(
    `INSERT INTO stock_movements (sku, warehouse_id, type, quantity, reference_id, bin_from, bin_to, user_id, notes, created_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())`,
    [sku, warehouseId, type, quantity, referenceId, binFrom, binTo, userId, notes]
  );
}