Database Operations

def table(self, table_name: str):
    """
    Create a query builder for the specified table.

    Parameters:
    - table_name: Name of the database table

    Returns:
    Query builder for table operations (select, insert, update, delete)
    
    Note: This is the preferred method in Python since 'from' is a reserved keyword.
    """

def from_(self, table_name: str):
    """
    Alternative method for table operations (equivalent to table()).

    Parameters:
    - table_name: Name of the database table

    Returns:
    Query builder for table operations
    """

# Select all records
countries = supabase.table("countries").select("*").execute()

# Select specific columns
countries = supabase.table("countries").select("name, code").execute()

# Insert single record
result = supabase.table("countries").insert({"name": "Germany", "code": "DE"}).execute()

# Insert multiple records
result = supabase.table("countries").insert([
    {"name": "Germany", "code": "DE"},
    {"name": "France", "code": "FR"}
]).execute()

# Update records
result = supabase.table("countries").update({"name": "Deutschland"}).eq("code", "DE").execute()

# Delete records
result = supabase.table("countries").delete().eq("code", "DE").execute()

# Complex filtering
countries = (supabase.table("countries")
    .select("name, population")
    .gt("population", 1000000)
    .order("population", desc=True)
    .limit(10)
    .execute())

def schema(self, schema: str):
    """
    Select a database schema for subsequent operations.

    Parameters:
    - schema: Name of the PostgreSQL schema

    Returns:
    PostgREST client configured for the specified schema

    Note: The schema must be exposed in Supabase settings.
    """

# Switch to private schema
private_schema = supabase.schema("private")
private_data = private_schema.table("sensitive_data").select("*").execute()

# Use different schemas in same session
public_countries = supabase.table("countries").select("*").execute()
private_users = supabase.schema("private").table("users").select("*").execute()

def rpc(
    self,
    fn: str,
    params: Optional[Dict[Any, Any]] = None,
    count: Optional[CountMethod] = None,
    head: bool = False,
    get: bool = False
):
    """
    Execute a stored procedure or function.

    Parameters:
    - fn: Name of the stored procedure/function to call
    - params: Parameters to pass to the function (default: {})
    - count: Method for counting returned rows (exact, planned, estimated)
    - head: When True, only return count without data (useful for count-only queries)
    - get: When True, execute with read-only access mode

    Returns:
    FilterRequestBuilder for applying additional filters to RPC results

    Note: Functions must be defined in the database and exposed via API.
    """

# Simple function call without parameters
result = supabase.rpc("get_total_users").execute()

# Function call with parameters
result = supabase.rpc("get_users_by_country", {"country_code": "US"}).execute()

# Function call with count
result = supabase.rpc("search_users", {"query": "john"}, count="exact").execute()
print(f"Found {result.count} users")

# Read-only function call
result = supabase.rpc("calculate_stats", {"date_range": "2023"}, get=True).execute()

# Function call with additional filtering
filtered_result = (supabase.rpc("get_active_users")
    .gt("last_login", "2023-01-01")
    .order("last_login", desc=True)
    .limit(100)
    .execute())

@property
def postgrest(self) -> SyncPostgrestClient | AsyncPostgrestClient:
    """
    Direct access to PostgREST client instance.

    Returns:
    PostgREST client (sync or async depending on parent client type)
    
    Provides full PostgREST API including:
    - Advanced query building and filtering
    - Custom HTTP headers and authentication
    - Schema selection and table operations
    - Stored procedure execution
    """

# Direct PostgREST access for advanced operations
postgrest_client = supabase.postgrest

# Use PostgREST client directly
result = postgrest_client.from_("countries").select("*").execute()

# Access with custom schema
result = postgrest_client.schema("analytics").from_("reports").select("*").execute()

# Filtering methods
.eq(column, value)          # Equal to
.neq(column, value)         # Not equal to  
.gt(column, value)          # Greater than
.gte(column, value)         # Greater than or equal
.lt(column, value)          # Less than
.lte(column, value)         # Less than or equal
.like(column, pattern)      # Pattern matching
.ilike(column, pattern)     # Case-insensitive pattern matching
.is_(column, value)         # IS comparison (for NULL)
.in_(column, values)        # IN clause
.contains(column, value)    # Contains (for arrays/JSON)
.contained_by(column, value) # Contained by (for arrays/JSON)

# Ordering and limiting
.order(column, desc=False)  # Order by column
.limit(count)               # Limit results
.offset(count)              # Skip results
.range(start, end)          # Range of results

# Data operations
.select(columns)            # Select columns
.insert(data)               # Insert data
.update(data)               # Update data  
.delete()                   # Delete data
.upsert(data)               # Insert or update

# Execution
.execute()                  # Execute the query

# Advanced filtering with multiple conditions
users = (supabase.table("users")
    .select("id, name, email, created_at")
    .gte("created_at", "2023-01-01")
    .like("email", "%@company.com")
    .neq("status", "inactive")
    .order("created_at", desc=True)
    .limit(50)
    .execute())

# JSON column operations
posts = (supabase.table("posts")
    .select("*")
    .contains("tags", ["python", "tutorial"])
    .execute())

# Count with filtering
count_result = (supabase.table("orders")
    .select("*", count="exact")
    .gte("total", 100)
    .head()
    .execute())
print(f"High-value orders: {count_result.count}")

# Database-specific exceptions (from postgrest)
class PostgrestAPIError(Exception):
    """PostgREST API errors including constraint violations, permission errors"""

class PostgrestAPIResponse:
    """PostgREST response wrapper with data and metadata"""

from postgrest import PostgrestAPIError

try:
    # Insert with potential constraint violation
    result = supabase.table("users").insert({
        "email": "duplicate@example.com",
        "name": "User"
    }).execute()
except PostgrestAPIError as e:
    print(f"Database error: {e}")
    # Handle constraint violations, permission errors, etc.

try:
    # Query with potential schema/table issues
    result = supabase.table("nonexistent_table").select("*").execute()
except PostgrestAPIError as e:
    print(f"Table not found: {e}")

# Use select() to limit columns returned
users = supabase.table("users").select("id, name").execute()  # Good
users = supabase.table("users").select("*").execute()         # Avoid for large tables

# Use pagination for large datasets
page_size = 100
offset = 0
while True:
    batch = (supabase.table("large_table")
        .select("*")
        .range(offset, offset + page_size - 1)
        .execute())
    
    if not batch.data:
        break
        
    process_batch(batch.data)
    offset += page_size

# Use indexes and efficient filtering
# Good: Filter on indexed columns
users = supabase.table("users").select("*").eq("email", "user@example.com").execute()

# Less efficient: Pattern matching on non-indexed columns  
users = supabase.table("users").select("*").like("description", "%keyword%").execute()