cleveragents-core/simulations/sim2-bookstore-api/generated.py

I'll create a production-ready FastAPI bookstore API with SQLite database and comprehensive CRUD operations. Here's the complete implementation:

## requirements.txt
```txt
fastapi>=0.104.0
uvicorn[standard]>=0.24.0
sqlalchemy>=2.0.0
pydantic>=2.0.0
python-multipart
```

## database.py
```python
"""Database configuration and session management."""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os

# Database URL
SQLALCHEMY_DATABASE_URL = "sqlite:///./bookstore.db"

# Create engine with connection pooling and foreign key support
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    connect_args={
        "check_same_thread": False,  # Allow multiple threads
        "timeout": 20,  # 20 second timeout
    },
    pool_pre_ping=True,  # Verify connections before use
    echo=False  # Set to True for SQL debugging
)

# Session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for models
Base = declarative_base()

def get_db():
    """Dependency for database sessions."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()
```

## models.py
```python
"""SQLAlchemy database models."""

from sqlalchemy import Column, Integer, String, Float, DateTime, Text
from sqlalchemy.sql import func
from database import Base
import re

class Book(Base):
    """Book model with validation and indexing."""
    
    __tablename__ = "books"
    
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(255), nullable=False, index=True)
    author = Column(String(255), nullable=False, index=True)
    isbn = Column(String(17), unique=True, nullable=False, index=True)  # ISBN-13 format
    price = Column(Float, nullable=False)
    publication_year = Column(Integer, nullable=True)
    genre = Column(String(100), nullable=True, index=True)
    description = Column(Text, nullable=True)
    stock_quantity = Column(Integer, default=0, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    def __repr__(self):
        return f"<Book(id={self.id}, title='{self.title}', author='{self.author}')>"
    
    @staticmethod
    def validate_isbn(isbn: str) -> bool:
        """Validate ISBN-10 or ISBN-13 format."""
        if not isbn:
            return False
        
        # Remove hyphens and spaces
        isbn_clean = re.sub(r'[-\s]', '', isbn)
        
        # Check ISBN-10 or ISBN-13
        if len(isbn_clean) == 10:
            return re.match(r'^\d{9}[\dX]$', isbn_clean) is not None
        elif len(isbn_clean) == 13:
            return re.match(r'^\d{13}$', isbn_clean) is not None
        
        return False
```

## schemas.py
```python
"""Pydantic schemas for request/response validation."""

from pydantic import BaseModel, Field, validator
from typing import Optional
from datetime import datetime
from models import Book as BookModel

class BookBase(BaseModel):
    """Base schema for book data."""
    title: str = Field(..., min_length=1, max_length=255, description="Book title")
    author: str = Field(..., min_length=1, max_length=255, description="Book author")
    isbn: str = Field(..., min_length=10, max_length=17, description="ISBN-10 or ISBN-13")
    price: float = Field(..., gt=0, description="Book price (must be positive)")
    publication_year: Optional[int] = Field(None, ge=1000, le=2100, description="Publication year")
    genre: Optional[str] = Field(None, max_length=100, description="Book genre")
    description: Optional[str] = Field(None, description="Book description")
    stock_quantity: int = Field(0, ge=0, description="Stock quantity (non-negative)")
    
    @validator('isbn')
    def validate_isbn(cls, v):
        """Validate ISBN format."""
        if not BookModel.validate_isbn(v):
            raise ValueError('Invalid ISBN format. Must be valid ISBN-10 or ISBN-13')
        return v
    
    @validator('title', 'author')
    def validate_strings(cls, v):
        """Validate string fields are not empty after stripping."""
        if not v or not v.strip():
            raise ValueError('Field cannot be empty or whitespace only')
        return v.strip()

class BookCreate(BookBase):
    """Schema for creating a new book."""
    pass

class BookUpdate(BaseModel):
    """Schema for updating a book (all fields optional)."""
    title: Optional[str] = Field(None, min_length=1, max_length=255)
    author: Optional[str] = Field(None, min_length=1, max_length=255)
    isbn: Optional[str] = Field(None, min_length=10, max_length=17)
    price: Optional[float] = Field(None, gt=0)
    publication_year: Optional[int] = Field(None, ge=1000, le=2100)
    genre: Optional[str] = Field(None, max_length=100)
    description: Optional[str] = None
    stock_quantity: Optional[int] = Field(None, ge=0)
    
    @validator('isbn', allow_reuse=True)
    def validate_isbn(cls, v):
        if v is not None and not BookModel.validate_isbn(v):
            raise ValueError('Invalid ISBN format')
        return v
    
    @validator('title', 'author', allow_reuse=True)
    def validate_strings(cls, v):
        if v is not None and (not v or not v.strip()):
            raise ValueError('Field cannot be empty or whitespace only')
        return v.strip() if v else v

class BookResponse(BookBase):
    """Schema for book responses."""
    id: int
    created_at: datetime
    updated_at: Optional[datetime] = None
    
    class Config:
        from_attributes = True

class BookListResponse(BaseModel):
    """Schema for paginated book list responses."""
    books: list[BookResponse]
    total: int
    page: int
    per_page: int
    total_pages: int

class ErrorResponse(BaseModel):
    """Schema for error responses."""
    error: str
    detail: Optional[str] = None
    status_code: int
```

## crud.py
```python
"""CRUD operations for books."""

from sqlalchemy.orm import Session
from sqlalchemy import or_, and_
from models import Book
from schemas import BookCreate, BookUpdate
from typing import Optional, List

class BookCRUD:
    """Book CRUD operations with error handling."""
    
    @staticmethod
    def create_book(db: Session, book: BookCreate) -> Book:
        """Create a new book."""
        try:
            db_book = Book(**book.dict())
            db.add(db_book)
            db.commit()
            db.refresh(db_book)
            return db_book
        except Exception as e:
            db.rollback()
            raise e
    
    @staticmethod
    def get_book(db: Session, book_id: int) -> Optional[Book]:
        """Get a book by ID."""
        return db.query(Book).filter(Book.id == book_id).first()
    
    @staticmethod
    def get_book_by_isbn(db: Session, isbn: str) -> Optional[Book]:
        """Get a book by ISBN."""
        return db.query(Book).filter(Book.isbn == isbn).first()
    
    @staticmethod
    def get_books(
        db: Session,
        skip: int = 0,
        limit: int = 100,
        title: Optional[str] = None,
        author: Optional[str] = None,
        genre: Optional[str] = None,
        min_price: Optional[float] = None,
        max_price: Optional[float] = None
    ) -> tuple[List[Book], int]:
        """Get books with optional filtering and pagination."""
        query = db.query(Book)
        
        # Apply filters
        filters = []
        if title:
            filters.append(Book.title.ilike(f"%{title}%"))
        if author:
            filters.append(Book.author.ilike(f"%{author}%"))
        if genre:
            filters.append(Book.genre.ilike(f"%{genre}%"))
        if min_price is not None:
            filters.append(Book.price >= min_price)
        if max_price is not None:
            filters.append(Book.price <= max_price)
        
        if filters:
            query = query.filter(and_(*filters))
        
        # Get total count
        total = query.count()
        
        # Apply pagination
        books = query.offset(skip).limit(limit).all()
        
        return books, total
    
    @staticmethod
    def update_book(db: Session, book_id: int, book_update: BookUpdate) -> Optional[Book]:
        """Update a book by ID."""
        try:
            db_book = db.query(Book).filter(Book.id == book_id).first()
            if not db_book:
                return None
            
            # Update only provided fields
            update_data = book_update.dict(exclude_unset=True)
            for field, value in update_data.items():
                setattr(db_book, field, value)
            
            db.commit()
            db.refresh(db_book)
            return db_book
        except Exception as e:
            db.rollback()
            raise e
    
    @staticmethod
    def delete_book(db: Session, book_id: int) -> bool:
        """Delete a book by ID."""
        try:
            db_book = db.query(Book).filter(Book.id == book_id).first()
            if not db_book:
                return False
            
            db.delete(db_book)
            db.commit()
            return True
        except Exception as e:
            db.rollback()
            raise e
    
    @staticmethod
    def search_books(db: Session, query: str, limit: int = 50) -> List[Book]:
        """Search books by title, author, or genre."""
        search_filter = or_(
            Book.title.ilike(f"%{query}%"),
            Book.author.ilike(f"%{query}%"),
            Book.genre.ilike(f"%{query}%")
        )
        return db.query(Book).filter(search_filter).limit(limit).all()
```

## main.py
```python
"""FastAPI bookstore application."""

from fastapi import FastAPI, HTTPException, Depends, Query, status
from fastapi.responses import JSONResponse
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
import math

from database import SessionLocal, engine, get_db
from models import Base, Book
from schemas import (
    BookCreate, BookUpdate, BookResponse, BookListResponse, 
    ErrorResponse
)
from crud import BookCRUD

# Create database tables
Base.metadata.create_all(bind=engine)

# Initialize FastAPI app
app = FastAPI(
    title="Bookstore API",
    description="A comprehensive RESTful API for managing a bookstore inventory",
    version="1.0.0",
    docs_url="/docs",
    redoc_url="/redoc"
)

# Initialize CRUD operations
book_crud = BookCRUD()

@app.exception_handler(ValueError)
async def value_error_handler(request, exc):
    """Handle validation errors."""
    return JSONResponse(
        status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
        content={"error": "Validation Error", "detail": str(exc)}
    )

@app.exception_handler(IntegrityError)
async def integrity_error_handler(request, exc):
    """Handle database integrity errors."""
    return JSONResponse(
        status_code=status.HTTP_409_CONFLICT,
        content={"error": "Conflict", "detail": "Book with this ISBN already exists"}
    )

@app.get("/", tags=["Root"])
async def root():
    """Root endpoint with API information."""
    return {
        "message": "Bookstore API",
        "version": "1.0.0",
        "docs": "/docs",
        "endpoints": {
            "books": "/books",
            "search": "/books/search"
        }
    }

@app.post(
    "/books",
    response_model=BookResponse,
    status_code=status.HTTP_201_CREATED,
    tags=["Books"],
    summary="Create a new book"
)
async def create_book(book: BookCreate, db: Session = Depends(get_db)):
    """
    Create a new book in the inventory.
    
    - **title**: Book title (required)
    - **author**: Book author (required)  
    - **isbn**: Valid ISBN-10 or ISBN-13 (required, unique)
    - **price**: Book price in USD (required, positive)
    - **publication_year**: Year of publication (optional)
    - **genre**: Book genre (optional)
    - **description**: Book description (optional)
    - **stock_quantity**: Number in stock (default: 0)
    """
    try:
        # Check if ISBN already exists
        if book_crud.get_book_by_isbn(db, book.isbn):
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Book with this ISBN already exists"
            )
        
        db_book = book_crud.create_book(db, book)
        return db_book
    except IntegrityError:
        raise HTTPException(
            status_code=status.HTTP_409_CONFLICT,
            detail="Book with this ISBN already exists"
        )
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Failed to create book"
        )

@app.get(
    "/books",
    response_model=BookListResponse,
    tags=["Books"],
    summary="Get all books with optional filtering and pagination"
)
async def get_books(
    page: int = Query(1, ge=1, description="Page number"),
    per_page: int = Query(10, ge=1, le=100, description="Books per page"),
    title: str = Query(None, description="Filter by title (partial match)"),
    author: str = Query(None, description="Filter by author (partial match)"),
    genre: str = Query(None, description="Filter by genre (partial match)"),
    min_price: float = Query(None, ge=0, description="Minimum price filter"),
    max_price: float = Query(None, ge=0, description="Maximum price filter"),
    db: Session = Depends(get_db)
):
    """
    Retrieve books with optional filtering and pagination.
    
    Supports filtering by:
    - Title (partial text search)
    - Author (partial text search) 
    - Genre (partial text search)
    - Price range (min_price, max_price)
    """
    try:
        skip = (page - 1) * per_page
        
        books, total = book_crud.get_books(
            db=db,
            skip=skip,
            limit=per_page,
            title=title,
            author=author,
            genre=genre,
            min_price=min_price,
            max_price=max_price
        )
        
        total_pages = math.ceil(total / per_page) if total > 0 else 1
        
        return BookListResponse(
            books=books,
            total=total,
            page=page,
            per_page=per_page,
            total_pages=total_pages
        )
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Failed to retrieve books"
        )

@app.get(
    "/books/{book_id}",
    response_model=BookResponse,
    tags=["Books"],
    summary="Get a specific book by ID"
)
async def get_book(book_id: int, db: Session = Depends(get_db)):
    """Get a specific book by its ID."""
    try:
        book = book_crud.get_book(db, book_id)
        if not book:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Book not found"
            )
        return book
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Failed to retrieve book"
        )

@app.put(
    "/books/{book_id}",
    response_model=BookResponse,
    tags=["Books"],
    summary="Update a book completely"
)
async def update_book_put(
    book_id: int,
    book: BookCreate,
    db: Session = Depends(get_db)
):
    """Update all fields of a book (complete replacement)."""
    try:
        # Check if book exists
        existing_book = book_crud.get_book(db, book_id)
        if not existing_book:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Book not found"
            )
        
        # Check ISBN uniqueness (if different from current)
        if book.isbn != existing_book.isbn:
            isbn_book = book_crud.get_book_by_isbn(db, book.isbn)
            if isbn_book:
                raise HTTPException(
                    status_code=status.HTTP_409_CONFLICT,
                    detail="Book with this ISBN already exists"
                )
        
        # Convert BookCreate to BookUpdate for consistency
        book_update = BookUpdate(**book.dict())
        updated_book = book_crud.update_book(db, book_id, book_update)
        return updated_book
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Failed to update book"
        )

@app.patch(
    "/books/{book_id}",
    response_model=BookResponse,
    tags=["Books"],
    summary="Partially update a book"
)
async def update_book_patch(
    book_id: int,
    book: BookUpdate,
    db: Session = Depends(get_db)
):
    """Update specific fields of a book (partial update)."""
    try:
        # Check if book exists
        existing_book = book_crud.get_book(db, book_id)
        if not existing_book:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Book not found"
            )
        
        # Check ISBN uniqueness (if being updated)
        if book.isbn and book.isbn != existing_book.isbn:
            isbn_book = book_crud.get_book_by_isbn(db, book.isbn)
            if isbn_book:
                raise HTTPException(
                    status_code=status.HTTP_409_CONFLICT,
                    detail="Book with this ISBN already exists"
                )
        
        updated_book = book_crud.update_book(db, book_id, book)
        return updated_book
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Failed to update book"
        )

@app.delete(
    "/books/{book_id}",
    status_code=status.HTTP_204_NO_CONTENT,
    tags=["Books"],
    summary="Delete a book"
)
async def delete_book(book_id: int, db: Session = Depends(get_db)):
    """Delete a book from the inventory."""
    try:
        deleted = book_crud.delete_book(db, book_id)
        if not deleted:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Book not found"
            )
        return None
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Failed to delete book"
        )

@app.get(
    "/books/search/{query}",
    response_model=list[BookResponse],
    tags=["Books"],
    summary="Search books by title, author, or genre"
)
async def search_books(
    query: str,
    limit: int = Query(50, ge=1, le=100, description="Maximum results to return"),
    db: Session = Depends(get_db)
):
    """
    Search books by query string across title, author, and genre fields.
    
    Returns books where the query matches any part of:
    - Book title
    - Author name
    - Genre
    """
    try:
        if len(query.strip()) < 2:
            raise HTTPException(
                status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
                detail="Search query must be at least 2 characters long"
            )
        
        books = book_crud.search_books(db, query.strip(), limit)
        return books
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail="Search failed"
        )

@app.get("/health", tags=["Health"])
async def health_check():
    """Health check endpoint."""
    return {"status": "healthy", "service": "bookstore-api"}

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(
        "main:app",
        host="0.0.0.0",
        port=8000,
        reload=True,
        log_level="info"
    )
```

## Usage Instructions

1. **Install dependencies**:
   ```bash
   pip install -r requirements.txt
   ```

2. **Run the application**:
   ```bash
   uvicorn main:app --reload
   ```

3. **Access the API**:
   - API: http://localhost:8000
   - Interactive docs: http://localhost:8000/docs
   - ReDoc: http://localhost:8000/redoc

## Key Features

- **Complete CRUD operations** with proper HTTP methods
- **Advanced filtering and pagination** for book listings
- **ISBN validation** for both ISBN-10 and ISBN-13 formats
- **Comprehensive error handling** with appropriate HTTP status codes
- **Database connection pooling** and session management
- **Input validation** using Pydantic schemas
- **Search functionality** across multiple fields
- **Auto-generated OpenAPI documentation**
- **Production-ready error handling** and logging setup

The API provides robust data validation, proper error responses, and follows REST principles with comprehensive documentation.