Inventory Reports

class DashboardService:
    """Service for dashboard data aggregation"""
    
    def get_dashboard_summary(self, low_stock_threshold: int = 10) -> Dict[str, Any]:
        return {
            "total_inventory_value": self.get_total_inventory_value(),
            "low_stock_products": self.get_low_stock_products(low_stock_threshold),
            "recent_movements": self.get_recent_movements(),
            "rotation_summary": self.get_rotation_summary(30)
        }

def get_total_inventory_value(self) -> float:
    """Calculate total inventory value (available_quantity × unit_cost)"""
    batches = self.db.query(Batch).filter(Batch.available_quantity > 0).all()
    total_value = sum(batch.available_quantity * batch.unit_cost for batch in batches)
    return round(total_value, 2)

def get_inventory_value_at_date(self, target_date: datetime) -> Dict[str, Any]:
    """Calculate inventory value at a specific date using FIFO"""
    batches = self.db.query(Batch).all()
    
    total_value = 0
    inventory_details = []
    
    for batch in batches:
        # Calculate quantity available at target date
        movements = self.db.query(Movement).filter(
            Movement.reference_id.contains(batch.id),
            Movement.created_at <= target_date
        ).all()
        
        available_at_date = batch.available_quantity
        
        if available_at_date > 0:
            batch_value = available_at_date * batch.unit_cost
            total_value += batch_value
            inventory_details.append({...})
    
    return {
        "date": target_date.isoformat(),
        "total_value": round(total_value, 2),
        "details_by_batch": inventory_details,
        "details_by_product": self._aggregate_by_product(inventory_details)
    }

def get_low_stock_products(self, threshold: int = 10) -> List[Dict[str, Any]]:
    """Get products with stock below threshold"""
    low_stock = []
    products = self.db.query(Product).all()
    
    for product in products:
        batches = self.db.query(Batch).filter(
            Batch.product_id == product.id,
            Batch.available_quantity > 0
        ).all()
        
        total_available = sum(batch.available_quantity for batch in batches)
        
        if total_available < threshold:
            low_stock.append({
                "product_id": product.id,
                "name": product.name,
                "sku": product.sku,
                "current_stock": total_available,
                "threshold": threshold,
                "unit_measure": product.unit_measure
            })
    
    return sorted(low_stock, key=lambda x: x['current_stock'])

<div className="overflow-x-auto">
    <table className="w-full">
        <thead>
            <tr>
                <th>Producto</th>
                <th>SKU</th>
                <th>Stock Actual</th>
                <th>Umbral</th>
                <th>Diferencia</th>
            </tr>
        </thead>
        <tbody>
            {lowStockProducts.map(product => (
                <tr key={product.product_id}>
                    <td>{product.name}</td>
                    <td>{product.sku}</td>
                    <td>
                        <span className="badge badge-red">
                            {product.current_stock} {product.unit_measure}
                        </span>
                    </td>
                    <td>{product.threshold}</td>
                    <td className="text-red-400">
                        {product.threshold - product.current_stock}
                    </td>
                </tr>
            ))}
        </tbody>
    </table>
</div>

def get_rotation_index(self, 
                      product_id: Optional[str] = None, 
                      days: int = 30, 
                      category: Optional[str] = None) -> List[Dict[str, Any]]:
    """
    Calculate rotation index: quantity_sold / ((stock_inicial + stock_final) / 2)
    """
    from datetime import timedelta
    
    end_date = datetime.now(timezone.utc)
    start_date = end_date - timedelta(days=days)
    
    # Get products to analyze
    query = self.db.query(Product)
    if product_id:
        query = query.filter(Product.id == product_id)
    if category:
        query = query.filter(Product.category == category)
    
    products = query.all()
    rotation_data = []
    
    for product in products:
        # Quantity sold in period
        exits = self.db.query(Movement).filter(
            Movement.product_id == product.id,
            Movement.type == MovementType.EXIT,
            Movement.created_at >= start_date,
            Movement.created_at <= end_date
        ).all()
        
        total_sold = sum(m.quantity for m in exits)
        
        # Calculate average stock
        stock_inicial = ...  # Stock at start of period
        stock_final = ...    # Stock at end of period
        stock_promedio = (stock_inicial + stock_final) / 2
        
        # Rotation index
        rotation_index = total_sold / stock_promedio if stock_promedio > 0 else 0
        
        rotation_data.append({
            "product_id": product.id,
            "product_name": product.name,
            "quantity_sold": total_sold,
            "stock_promedio": round(stock_promedio, 2),
            "rotation_index": round(rotation_index, 2),
            "period_days": days
        })
    
    return sorted(rotation_data, key=lambda x: x['rotation_index'], reverse=True)

def get_recent_movements(self, limit: int = 10) -> List[Dict[str, Any]]:
    """Get latest movements with details"""
    movements = self.db.query(Movement).order_by(
        Movement.created_at.desc()
    ).limit(limit).all()
    
    result = []
    for m in movements:
        product = self.db.query(Product).filter(Product.id == m.product_id).first()
        result.append({
            "id": m.id,
            "product_name": product.name if product else "Unknown",
            "product_sku": product.sku if product else "Unknown",
            "type": str(m.type),
            "quantity": m.quantity,
            "unit_price": m.unit_price,
            "total_price": m.total_price,
            "created_at": m.created_at.isoformat(),
            "created_by": m.created_by
        })
    
    return result

@router.route('/valorization/export', methods=['GET'])
@require_role('admin', 'gestor', 'consultor')
def export_valorization_report():
    date_str = request.args.get('date')
    export_format = request.args.get('format', 'csv').lower()
    
    report = service.get_inventory_value_at_date(target_date)
    
    if export_format == 'csv':
        output = StringIO()
        writer = csv.writer(output)
        
        # Header
        writer.writerow(['Reporte de Valorización de Inventario'])
        writer.writerow(['Fecha', report['date']])
        writer.writerow(['Valor Total', f"Bs {report['total_value']}"])
        writer.writerow([])
        
        # Product summary
        writer.writerow(['Producto', 'SKU', 'Cantidad', 'Costo Unitario', 'Costo Total'])
        for product in report['details_by_product']:
            writer.writerow([...])
        
        return send_file(output, mimetype='text/csv', 
                        download_name=f"valorization_{date_str}.csv")

const [lowStockThreshold, setLowStockThreshold] = useState(10);

useEffect(() => {
    fetchDashboard(lowStockThreshold);
}, [lowStockThreshold]);

// UI Control
<div className="flex gap-4 items-center">
    <label>Umbral de Stock Bajo:</label>
    <input
        type="number"
        min="1"
        value={lowStockThreshold}
        onChange={(e) => setLowStockThreshold(parseInt(e.target.value))}
        className="px-3 py-2 bg-gray-800 text-white rounded"
    />
    <span>unidades</span>
</div>