Inventory Optimization

interface ReorderParameters {
  itemCode: string;
  reorderPoint: number;        // Trigger level for reordering
  reorderQuantity: number;     // Amount to order when triggered
  safetyStock: number;         // Buffer stock for demand variability
  leadTimeDays: number;        // Supplier lead time
  minOrderQuantity: number;    // Supplier MOQ
  maxOrderQuantity: number;    // Maximum order size
  orderMultiple: number;       // Order in multiples (cases, pallets)
}

interface ForecastConfiguration {
  itemCode: string;
  method: 'MovingAverage' | 'WeightedAverage' | 'ExponentialSmoothing' | 'Seasonal';
  periodsToAnalyze: number;    // Historical periods to consider
  forecastHorizon: number;      // Periods to forecast ahead
  seasonalityFactor: number;    // Seasonal adjustment multiplier
  trendFactor: number;          // Trend adjustment factor
  safetyStockPercent: number;   // Safety stock as % of forecast
}

-- Automatic ABC classification based on annual value
UPDATE inventory_items
SET abc_class = 
  CASE 
    WHEN annual_value_rank <= 0.20 THEN 'A'
    WHEN annual_value_rank <= 0.60 THEN 'B'
    ELSE 'C'
  END
FROM (
  SELECT 
    item_code,
    PERCENT_RANK() OVER (ORDER BY annual_usage_value DESC) as annual_value_rank
  FROM inventory_items
) ranked;

interface SLOBCriteria {
  daysWithoutMovement: number;    // No transactions for X days
  monthsOfStockOnHand: number;    // Months of supply exceeds threshold
  turnoverRatio: number;          // Annual turns below threshold
  obsolescenceIndicators: [
    'noSalesLast12Months',
    'replacedByNewModel',
    'supplierDiscontinued',
    'excessStockLevel'
  ];
}

-- Calculate fill rate by item
SELECT 
  item_code,
  SUM(quantity_shipped) / SUM(quantity_ordered) * 100 as fill_rate_percent,
  COUNT(CASE WHEN quantity_shipped < quantity_ordered THEN 1 END) as stockout_count
FROM sales_order_lines
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
GROUP BY item_code
HAVING fill_rate_percent < 95
ORDER BY fill_rate_percent;