app.py

"""
FastAPI application for SheetDB REST API server.

This module provides a RESTful API interface to SheetDB, allowing
any frontend framework to use Google Sheets as a backend database.
"""

from fastapi import FastAPI, HTTPException, Depends, Header, Query, Request
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse
from fastapi.security import APIKeyHeader
from typing import Optional, List, Dict, Any
from pydantic import BaseModel, Field
import logging
from datetime import datetime, timezone
import os

from sheetdb.core import SheetDB


# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("sheetdb.server")


# Pydantic models for request/response
class HealthResponse(BaseModel):
    """Health check response."""
    status: str
    timestamp: str
    version: str


class TableInfo(BaseModel):
    """Table information."""
    name: str
    sheet_name: str
    primary_key: Optional[str]
    columns: List[Dict[str, str]]


class TablesResponse(BaseModel):
    """Response for listing tables."""
    tables: List[TableInfo]


class RowCreate(BaseModel):
    """Request body for creating a row."""
    data: Dict[str, Any] = Field(..., description="Row data as key-value pairs")


class RowUpdate(BaseModel):
    """Request body for updating a row."""
    data: Dict[str, Any] = Field(..., description="Fields to update")


class SyncResponse(BaseModel):
    """Response for sync operations."""
    status: str
    message: str
    timestamp: str


class SyncStatusResponse(BaseModel):
    """Response for sync status check."""
    last_sync: Optional[str]
    is_syncing: bool
    tables_count: int


# Global SheetDB instance
_sheetdb_instance: Optional[SheetDB] = None
_last_sync_time: Optional[datetime] = None
_is_syncing: bool = False
_api_keys: List[str] = []

# API Key security scheme
api_key_header = APIKeyHeader(name="X-API-Key", auto_error=False)


def get_sheetdb() -> SheetDB:
    """Get the configured SheetDB instance.
    
    Returns:
        SheetDB instance
        
    Raises:
        HTTPException: If SheetDB is not configured
    """
    if _sheetdb_instance is None:
        raise HTTPException(
            status_code=500,
            detail="SheetDB not configured. Call create_app() with configuration."
        )
    return _sheetdb_instance


async def verify_api_key(api_key: Optional[str] = Depends(api_key_header)) -> str:
    """Verify API key for authentication.
    
    Args:
        api_key: API key from header
        
    Returns:
        Validated API key
        
    Raises:
        HTTPException: If API key is invalid or missing
    """
    # If no API keys configured, allow all requests
    if not _api_keys:
        return "no-auth"
    
    # Check if API key is provided and valid
    if not api_key or api_key not in _api_keys:
        raise HTTPException(
            status_code=401,
            detail="Invalid or missing API key. Provide X-API-Key header."
        )
    
    return api_key


def create_app(
    credentials: Optional[str] = None,
    spreadsheet_id: Optional[str] = None,
    sheet_url: Optional[str] = None,
    schema_path: Optional[str] = None,
    cache_mode: str = "aggressive",
    cache_ttl: Optional[int] = None,
    mode: Optional[str] = None,
    allowed_origins: Optional[List[str]] = None,
    api_keys: Optional[List[str]] = None
) -> FastAPI:
    """Create and configure the FastAPI application.
    
    Args:
        credentials: Path to Google service account credentials
        spreadsheet_id: Google Spreadsheet ID
        sheet_url: Public sheet URL (for public mode)
        schema_path: Path to schema YAML file
        cache_mode: Cache mode (aggressive, smart, lazy, none)
        cache_ttl: Cache TTL in seconds (for smart mode)
        mode: Operating mode (public, authenticated, hybrid)
        allowed_origins: List of allowed CORS origins (default: ["*"])
        api_keys: List of valid API keys for authentication
        
    Returns:
        Configured FastAPI application
    """
    global _sheetdb_instance
    
    # Create FastAPI app
    app = FastAPI(
        title="SheetDB REST API",
        description="RESTful API for Google Sheets as a relational database",
        version="0.1.0",
        docs_url="/docs",
        redoc_url="/redoc"
    )
    
    # Configure CORS
    if allowed_origins is None:
        allowed_origins = ["*"]
    
    app.add_middleware(
        CORSMiddleware,
        allow_origins=allowed_origins,
        allow_credentials=True,
        allow_methods=["*"],
        allow_headers=["*"],
    )
    
    # Initialize SheetDB
    try:
        if sheet_url:
            _sheetdb_instance = SheetDB.from_public_url(
                sheet_url,
                schema_path=schema_path,
                cache_mode=cache_mode,
                cache_ttl=cache_ttl
            )
        else:
            _sheetdb_instance = SheetDB(
                credentials=credentials,
                spreadsheet_id=spreadsheet_id,
                schema_path=schema_path,
                cache_mode=cache_mode,
                cache_ttl=cache_ttl,
                mode=mode
            )
        
        # Initial sync
        _sheetdb_instance.sync()
        logger.info("SheetDB initialized and synced successfully")
        
    except Exception as e:
        logger.error(f"Failed to initialize SheetDB: {e}")
        raise
    
    # Store API keys for authentication
    global _api_keys
    _api_keys = api_keys or []
    app.state.api_keys = _api_keys
    
    # Health check endpoint
    @app.get("/health", response_model=HealthResponse, tags=["Health"])
    async def health_check():
        """Health check endpoint.
        
        Returns server status and version information.
        """
        return HealthResponse(
            status="healthy",
            timestamp=datetime.now(timezone.utc).isoformat(),
            version="0.1.0"
        )
    
    # List tables endpoint
    @app.get("/api/tables", response_model=TablesResponse, tags=["Tables"])
    async def list_tables(db: SheetDB = Depends(get_sheetdb)):
        """List all available tables.
        
        Returns information about all configured tables including
        their columns and primary keys.
        """
        schema = db._schema
        if not schema:
            raise HTTPException(status_code=500, detail="Schema not configured")
        
        tables = []
        
        for table in schema.get_all_tables():
            table_info = TableInfo(
                name=table.name,
                sheet_name=table.sheet_name,
                primary_key=table.primary_key,
                columns=[
                    {"name": col.name, "type": col.type}
                    for col in table.columns
                ]
            )
            tables.append(table_info)
        
        return TablesResponse(tables=tables)
    
    # Query rows endpoint
    @app.get("/api/tables/{table}/rows", tags=["CRUD"])
    async def query_rows(
        request: Request,
        table: str,
        limit: Optional[int] = Query(None, ge=1, description="Maximum number of rows to return"),
        offset: Optional[int] = Query(None, ge=0, description="Number of rows to skip"),
        order_by: Optional[str] = Query(None, description="Column to order by (prefix with - for descending)"),
        fields: Optional[str] = Query(None, description="Comma-separated list of fields to return"),
        db: SheetDB = Depends(get_sheetdb),
        api_key: str = Depends(verify_api_key)
    ):
        """Query rows from a table with optional filtering, pagination, and sorting.
        
        Supports filter operators via query parameters:
        - field=value: Exact match
        - field__gte=value: Greater than or equal
        - field__lte=value: Less than or equal
        - field__contains=value: String contains
        
        Args:
            table: Table name
            limit: Maximum number of rows to return
            offset: Number of rows to skip
            order_by: Column to order by (prefix with - for descending)
            fields: Comma-separated list of fields to return
        """
        try:
            # Build SQL query
            sql = f"SELECT * FROM {table}"
            params = {}
            where_clauses = []
            param_counter = 1
            
            # Parse query parameters for filters
            query_params = dict(request.query_params)
            
            # Remove known parameters
            for key in ['limit', 'offset', 'order_by', 'fields']:
                query_params.pop(key, None)
            
            # Add WHERE clauses from remaining query parameters
            for key, value in query_params.items():
                # Parse field and operator
                if '__' in key:
                    field, operator = key.rsplit('__', 1)
                else:
                    field = key
                    operator = 'exact'
                
                # Build SQL condition based on operator
                if operator == 'exact':
                    where_clauses.append(f"{field} = ${param_counter}")
                    params[f"p{param_counter}"] = value
                    param_counter += 1
                elif operator == 'gte':
                    where_clauses.append(f"{field} >= ${param_counter}")
                    params[f"p{param_counter}"] = value
                    param_counter += 1
                elif operator == 'lte':
                    where_clauses.append(f"{field} <= ${param_counter}")
                    params[f"p{param_counter}"] = value
                    param_counter += 1
                elif operator == 'gt':
                    where_clauses.append(f"{field} > ${param_counter}")
                    params[f"p{param_counter}"] = value
                    param_counter += 1
                elif operator == 'lt':
                    where_clauses.append(f"{field} < ${param_counter}")
                    params[f"p{param_counter}"] = value
                    param_counter += 1
                elif operator == 'contains':
                    where_clauses.append(f"{field} LIKE ${param_counter}")
                    params[f"p{param_counter}"] = f"%{value}%"
                    param_counter += 1
            
            if where_clauses:
                sql += " WHERE " + " AND ".join(where_clauses)
            
            # Add ORDER BY
            if order_by:
                if order_by.startswith('-'):
                    sql += f" ORDER BY {order_by[1:]} DESC"
                else:
                    sql += f" ORDER BY {order_by} ASC"
            
            # Add LIMIT and OFFSET
            if limit:
                sql += f" LIMIT {limit}"
            if offset:
                sql += f" OFFSET {offset}"
            
            # Execute query
            results = db.query(sql, params if params else None)
            
            # Filter fields if specified
            if fields:
                field_list = [f.strip() for f in fields.split(',')]
                results = [
                    {k: v for k, v in row.items() if k in field_list}
                    for row in results
                ]
            
            return JSONResponse(content={"data": results, "count": len(results)})
            
        except Exception as e:
            logger.error(f"Error querying table {table}: {e}")
            raise HTTPException(status_code=400, detail=str(e))
    
    # Create row endpoint
    @app.post("/api/tables/{table}/rows", status_code=201, tags=["CRUD"])
    async def create_row(
        table: str,
        row: RowCreate,
        db: SheetDB = Depends(get_sheetdb),
        api_key: str = Depends(verify_api_key)
    ):
        """Create a new row in a table.
        
        Args:
            table: Table name
            row: Row data to insert
        """
        try:
            db.insert(table, row.data)
            return JSONResponse(
                content={"message": "Row created successfully", "data": row.data},
                status_code=201
            )
        except Exception as e:
            logger.error(f"Error creating row in table {table}: {e}")
            raise HTTPException(status_code=400, detail=str(e))
    
    # Update row endpoint
    @app.put("/api/tables/{table}/rows/{row_id}", tags=["CRUD"])
    async def update_row(
        table: str,
        row_id: Any,
        row: RowUpdate,
        db: SheetDB = Depends(get_sheetdb),
        api_key: str = Depends(verify_api_key)
    ):
        """Update an existing row in a table.
        
        Args:
            table: Table name
            row_id: Primary key value of the row to update
            row: Fields to update
        """
        try:
            db.update(table, row_id, row.data)
            return JSONResponse(
                content={"message": "Row updated successfully", "id": row_id}
            )
        except Exception as e:
            logger.error(f"Error updating row {row_id} in table {table}: {e}")
            raise HTTPException(status_code=400, detail=str(e))
    
    # Delete row endpoint
    @app.delete("/api/tables/{table}/rows/{row_id}", tags=["CRUD"])
    async def delete_row(
        table: str,
        row_id: Any,
        db: SheetDB = Depends(get_sheetdb),
        api_key: str = Depends(verify_api_key)
    ):
        """Delete a row from a table.
        
        Args:
            table: Table name
            row_id: Primary key value of the row to delete
        """
        try:
            db.delete(table, row_id)
            return JSONResponse(
                content={"message": "Row deleted successfully", "id": row_id}
            )
        except Exception as e:
            logger.error(f"Error deleting row {row_id} from table {table}: {e}")
            raise HTTPException(status_code=400, detail=str(e))
    
    # Sync endpoint
    @app.post("/api/sync", response_model=SyncResponse, tags=["Sync"])
    async def trigger_sync(
        db: SheetDB = Depends(get_sheetdb),
        api_key: str = Depends(verify_api_key)
    ):
        """Trigger a cache refresh from Google Sheets.
        
        Reloads all table data from sheets into the DuckDB cache.
        """
        global _last_sync_time, _is_syncing
        
        if _is_syncing:
            raise HTTPException(
                status_code=409,
                detail="Sync already in progress"
            )
        
        try:
            _is_syncing = True
            db.sync()
            _last_sync_time = datetime.now(timezone.utc)
            _is_syncing = False
            
            return SyncResponse(
                status="success",
                message="Cache refreshed successfully",
                timestamp=_last_sync_time.isoformat()
            )
        except Exception as e:
            _is_syncing = False
            logger.error(f"Sync failed: {e}")
            raise HTTPException(status_code=500, detail=f"Sync failed: {str(e)}")
    
    # Sync status endpoint
    @app.get("/api/sync/status", response_model=SyncStatusResponse, tags=["Sync"])
    async def sync_status(db: SheetDB = Depends(get_sheetdb)):
        """Check sync status.
        
        Returns information about the last sync operation and current sync state.
        """
        schema = db._schema
        tables_count = len(schema.get_all_tables()) if schema else 0
        
        return SyncStatusResponse(
            last_sync=_last_sync_time.isoformat() if _last_sync_time else None,
            is_syncing=_is_syncing,
            tables_count=tables_count
        )
    
    return app


# Convenience function for running the server
def run_server(
    host: str = "0.0.0.0",
    port: int = 8000,
    **kwargs
):
    """Run the SheetDB REST API server.
    
    Args:
        host: Host to bind to
        port: Port to bind to
        **kwargs: Additional arguments passed to create_app()
    """
    import uvicorn
    
    app = create_app(**kwargs)
    uvicorn.run(app, host=host, port=port)