Inventory Management System

-- Available quantity calculation
SELECT
    item_id,
    warehouse_id,
    quantity_on_hand,
    quantity_reserved,
    quantity_on_order,
    quantity_in_transit,
    (quantity_on_hand - quantity_reserved) AS available_qty,
    (quantity_on_hand - quantity_reserved + quantity_on_order + quantity_in_transit) AS projected_qty
FROM inventory.stock_levels
WHERE item_id = :item_id;

-- FEFO: First Expiry, First Out - for items with expiry tracking
SELECT
    sb.batch_number,
    sb.quantity,
    sb.expiry_date
FROM inventory.stock_batches sb
WHERE
    sb.item_id = :item_id
    AND sb.warehouse_id = :warehouse_id
    AND sb.status = 'AVAILABLE'
    AND sb.quantity > 0
    AND sb.expiry_date > CURRENT_DATE  -- Not expired
ORDER BY
    sb.expiry_date ASC,  -- Earliest expiry first
    sb.created_at ASC;   -- Then FIFO within same expiry

-- Stock adjustment item with auto-calculated variance
CREATE TABLE inventory.stock_adjustment_items (
    ...
    system_qty DECIMAL(18, 4) NOT NULL,
    actual_qty DECIMAL(18, 4) NOT NULL,
    variance_qty DECIMAL(18, 4) GENERATED ALWAYS AS
        (actual_qty - system_qty) STORED,
    ...
);

-- Items below reorder level
SELECT
    i.item_code,
    i.item_name,
    sl.warehouse_id,
    w.warehouse_name,
    sl.quantity_on_hand,
    sl.quantity_reserved,
    (sl.quantity_on_hand - sl.quantity_reserved) AS available,
    sl.quantity_on_order,
    i.reorder_level,
    i.reorder_qty,
    i.lead_time_days,
    CASE
        WHEN (sl.quantity_on_hand - sl.quantity_reserved + sl.quantity_on_order) <= i.reorder_level
        THEN 'REORDER_NOW'
        WHEN (sl.quantity_on_hand - sl.quantity_reserved) <= i.min_stock
        THEN 'CRITICAL'
        ELSE 'OK'
    END AS stock_status
FROM inventory.items i
JOIN inventory.stock_levels sl ON i.id = sl.item_id
JOIN inventory.warehouses w ON sl.warehouse_id = w.id
WHERE
    i.is_active = true
    AND i.is_stockable = true
    AND (sl.quantity_on_hand - sl.quantity_reserved + sl.quantity_on_order) <= i.reorder_level
ORDER BY
    stock_status DESC,
    available ASC;