재고 관리 시스템

1. 실제 재고 (physical_stock)
   - 창고에 있는 실제 물량

2. 가용 재고 (available)
   - 실제 - 예약 (장바구니·결제 중)

3. 표시 재고 (display)
   - 가용 + 안전 마진
   - 0이어도 "주문 가능" 표시 가능 (재입고 알림 유도)

CREATE TABLE warehouses (
  id UUID PRIMARY KEY,
  name VARCHAR NOT NULL,
  code VARCHAR UNIQUE,
  address JSONB
);

CREATE TABLE inventory (
  id UUID PRIMARY KEY,
  variant_id UUID NOT NULL REFERENCES product_variants(id),
  warehouse_id UUID NOT NULL REFERENCES warehouses(id),
  quantity INT NOT NULL DEFAULT 0,    -- 실재고
  reserved INT NOT NULL DEFAULT 0,    -- 예약 (15분 차감)
  reorder_point INT DEFAULT 10,
  reorder_quantity INT DEFAULT 100,
  updated_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (variant_id, warehouse_id)
);

CREATE TABLE inventory_movements (
  id UUID PRIMARY KEY,
  inventory_id UUID NOT NULL REFERENCES inventory(id),
  type VARCHAR NOT NULL,  -- 'in', 'out', 'adjust', 'reserve', 'release'
  quantity INT NOT NULL,
  reference_type VARCHAR,  -- 'order', 'return', 'adjustment'
  reference_id UUID,
  note TEXT,
  created_by UUID,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX ON inventory_movements(inventory_id, created_at DESC);

async function moveInventory({
  variantId, warehouseId, type, quantity, referenceType, referenceId, userId,
}: MoveInput) {
  return db.$transaction(async (tx) => {
    const inv = await tx.inventory.findUnique({
      where: { variantId_warehouseId: { variantId, warehouseId } },
    });
    if (!inv) throw new NotFoundError('Inventory');

    let newQuantity = inv.quantity;
    let newReserved = inv.reserved;

    switch (type) {
      case 'in':
        newQuantity += quantity;
        break;
      case 'out':
        if (inv.quantity < quantity) throw new ConflictError('Insufficient stock');
        newQuantity -= quantity;
        break;
      case 'reserve':
        if (inv.quantity - inv.reserved < quantity) {
          throw new ConflictError('Insufficient available');
        }
        newReserved += quantity;
        break;
      case 'release':
        newReserved = Math.max(0, newReserved - quantity);
        break;
      case 'adjust':
        newQuantity = quantity;  // 절대값
        break;
    }

    await tx.inventory.update({
      where: { id: inv.id },
      data: { quantity: newQuantity, reserved: newReserved, updatedAt: new Date() },
    });

    await tx.inventoryMovement.create({
      data: {
        inventoryId: inv.id,
        type, quantity,
        referenceType, referenceId,
        createdBy: userId,
      },
    });

    return { quantity: newQuantity, reserved: newReserved };
  }, { isolationLevel: 'Serializable' });
}

async function findFulfillmentWarehouse(
  variantId: string,
  quantity: number,
  customerLocation: { lat: number; lng: number }
) {
  const inventories = await db.inventory.findMany({
    where: {
      variantId,
      quantity: { gte: quantity },
    },
    include: { warehouse: true },
  });

  // 가용 재고 있는 창고 중 가장 가까운 곳
  const candidates = inventories
    .map(inv => ({
      ...inv,
      available: inv.quantity - inv.reserved,
      distance: haversine(
        customerLocation,
        { lat: inv.warehouse.lat, lng: inv.warehouse.lng }
      ),
    }))
    .filter(c => c.available >= quantity)
    .sort((a, b) => a.distance - b.distance);

  return candidates[0];
}

// 사용자가 품절 상품 알림 신청
CREATE TABLE restock_subscriptions (
  user_id UUID NOT NULL,
  variant_id UUID NOT NULL,
  email VARCHAR NOT NULL,
  notified_at TIMESTAMP,
  PRIMARY KEY (user_id, variant_id)
);


// 재고 입고 이벤트
eventBus.subscribe('inventory.restocked', async ({ variantId, quantity }) => {
  if (quantity < 5) return;  // 너무 적으면 스킵

  const subscribers = await db.restockSubscription.findMany({
    where: { variantId, notifiedAt: null },
  });

  for (const sub of subscribers) {
    await emailQueue.add('restock', {
      email: sub.email,
      variantId,
    });
    await db.restockSubscription.update({
      where: { userId_variantId: { userId: sub.userId, variantId } },
      data: { notifiedAt: new Date() },
    });
  }
});

// 매시간 - 재고 부족 알림
const reorderCheck = new Worker('reorder-check', async () => {
  const lowStock = await db.inventory.findMany({
    where: {
      quantity: { lte: db.inventory.fields.reorderPoint },
    },
    include: { variant: { include: { product: true } } },
  });

  for (const inv of lowStock) {
    await emailQueue.add('low_stock_alert', {
      to: 'inventory@example.com',
      product: inv.variant.product.name,
      variant: inv.variant.name,
      current: inv.quantity,
      reorderPoint: inv.reorderPoint,
      suggested: inv.reorderQuantity,
    });
  }
});

// 주기적 실재고 vs DB 비교
async function reconcileInventory(warehouseId: string, counts: { variantId: string; actualQuantity: number }[]) {
  const adjustments = [];

  for (const count of counts) {
    const inv = await db.inventory.findUnique({
      where: { variantId_warehouseId: { variantId: count.variantId, warehouseId } },
    });
    if (!inv) continue;

    if (inv.quantity !== count.actualQuantity) {
      const diff = count.actualQuantity - inv.quantity;
      adjustments.push({ variantId: count.variantId, diff });

      await moveInventory({
        variantId: count.variantId,
        warehouseId,
        type: 'adjust',
        quantity: count.actualQuantity,
        referenceType: 'cycle_count',
        userId: 'system',
      });
    }
  }

  return adjustments;
}