Database Developer Guide

# SQLite (Default - Perfect for development)
DATABASE_URL=sqlite:///./.fiberwise-core/fiberwise.db

# PostgreSQL (Production-ready)
DATABASE_URL=postgresql://user:password@localhost:5432/fiberwise

# DuckDB (Analytics-focused)
DATABASE_URL=duckdb:///./.fiberwise-core/analytics.duckdb

from api.core.dependencies import get_database

async def create_user(db, user_data):
    """Create a new user - works with any database"""
    user_id = await db.fetch_val(
        """
        INSERT INTO users (email, username, display_name) 
        VALUES ($1, $2, $3) 
        RETURNING id
        """,
        user_data.email,
        user_data.username, 
        user_data.display_name
    )
    return user_id

async def get_user_by_email(db, email):
    """Fetch user by email - automatic query translation"""
    return await db.fetch_one(
        "SELECT * FROM users WHERE email = $1",
        email
    )

async def update_user(db, user_id, updates):
    """Update user data with dynamic fields"""
    set_clause = ", ".join([f"{k} = ${i+2}" for i, k in enumerate(updates.keys())])
    query = f"UPDATE users SET {set_clause} WHERE id = $1"
    
    await db.execute(query, user_id, *updates.values())

async def delete_user(db, user_id):
    """Delete user - supports all databases"""
    await db.execute("DELETE FROM users WHERE id = $1", user_id)

async def get_user_apps(db, user_id):
    """Complex query with joins - works across all databases"""
    return await db.fetch_all(
        """
        SELECT 
            a.app_id,
            a.name as app_name,
            a.description,
            av.version,
            av.status
        FROM apps a
        JOIN app_versions av ON a.current_live_app_version_id = av.app_version_id
        WHERE a.creator_user_id = $1
        ORDER BY a.created_at DESC
        """,
        user_id
    )

async def update_user_metadata(db, user_id, metadata):
    """JSON operations work across databases"""
    await db.execute(
        """
        UPDATE users 
        SET metadata = $1, updated_at = CURRENT_TIMESTAMP 
        WHERE id = $2
        """,
        json.dumps(metadata),
        user_id
    )

async def get_app_analytics(db, date_range):
    """Analytics query - DuckDB will optimize, others work fine"""
    return await db.fetch_all(
        """
        SELECT 
            DATE(created_at) as date,
            COUNT(*) as activations,
            COUNT(DISTINCT agent_id) as unique_agents,
            AVG(duration_ms) as avg_duration
        FROM agent_activations 
        WHERE created_at >= $1 AND created_at <= $2
        GROUP BY DATE(created_at)
        ORDER BY date DESC
        """,
        date_range.start,
        date_range.end
    )

async def transfer_app_ownership(db, app_id, old_owner_id, new_owner_id):
    """Atomic operations with transactions"""
    async with db.transaction():
        # Verify current ownership
        current_owner = await db.fetch_val(
            "SELECT creator_user_id FROM apps WHERE app_id = $1",
            app_id
        )
        
        if current_owner != old_owner_id:
            raise ValueError("Invalid current owner")
        
        # Transfer ownership
        await db.execute(
            "UPDATE apps SET creator_user_id = $1, updated_at = CURRENT_TIMESTAMP WHERE app_id = $2",
            new_owner_id, app_id
        )
        
        # Log the transfer
        await db.execute(
            """
            INSERT INTO app_transfer_log (app_id, old_owner_id, new_owner_id, transferred_at)
            VALUES ($1, $2, $3, CURRENT_TIMESTAMP)
            """,
            app_id, old_owner_id, new_owner_id
        )

-- migrations/001_add_user_preferences.sql
-- Works on SQLite, PostgreSQL, DuckDB

ALTER TABLE users ADD COLUMN preferences TEXT DEFAULT '{}';
CREATE INDEX idx_users_preferences ON users(preferences);

-- Use standard SQL functions
UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE preferences IS NULL;

async def get_user_search_results(db, query_text):
    """Handle database-specific search capabilities"""
    
    # Try PostgreSQL full-text search first
    try:
        if hasattr(db, 'provider') and db.provider == 'postgresql':
            return await db.fetch_all(
                "SELECT * FROM users WHERE to_tsvector(display_name) @@ plainto_tsquery($1)",
                query_text
            )
    except:
        pass
    
    # Fallback to basic LIKE search for all databases
    return await db.fetch_all(
        "SELECT * FROM users WHERE display_name LIKE $1",
        f"%{query_text}%"
    )