example_frontend_queries.py

"""
Exemplos de queries otimizadas para o frontend
Demonstra como buscar dados do banco de forma eficiente
"""
import asyncio
from sqlalchemy import select, func, desc
from scraper.database import Database, Bundle, BundleApp, PriceHistory


async def get_top_deals(limit: int = 20):
    """
    Query otimizada: Top deals com maior desconto
    Frontend: Página inicial / Seção "Melhores Ofertas"
    """
    db = Database()
    async with db.async_session() as session:
        query = (
            select(Bundle)
            .where(Bundle.discount > 0)
            .order_by(desc(Bundle.discount), desc(Bundle.last_updated))
            .limit(limit)
        )
        result = await session.execute(query)
        bundles = result.scalars().all()
        
        return [bundle.to_dict() for bundle in bundles]


async def get_recent_bundles(hours: int = 24, limit: int = 50):
    """
    Query otimizada: Bundles atualizados recentemente
    Frontend: Seção "Novidades"
    """
    db = Database()
    async with db.async_session() as session:
        from datetime import datetime, timedelta
        cutoff = datetime.utcnow() - timedelta(hours=hours)
        
        query = (
            select(Bundle)
            .where(Bundle.last_updated >= cutoff)
            .order_by(desc(Bundle.last_updated))
            .limit(limit)
        )
        result = await session.execute(query)
        bundles = result.scalars().all()
        
        return [bundle.to_dict() for bundle in bundles]


async def get_bundles_by_price_range(min_price: float, max_price: float, limit: int = 50):
    """
    Query otimizada: Filtro por faixa de preço
    Frontend: Filtros laterais
    """
    db = Database()
    async with db.async_session() as session:
        query = (
            select(Bundle)
            .where(Bundle.final_price.between(min_price, max_price))
            .order_by(desc(Bundle.discount))
            .limit(limit)
        )
        result = await session.execute(query)
        bundles = result.scalars().all()
        
        return [bundle.to_dict() for bundle in bundles]


async def get_bundles_by_platform(platform: str = 'windows', limit: int = 50):
    """
    Query otimizada: Filtro por plataforma
    Frontend: Filtro "Disponível em Windows/Mac/Linux"
    """
    db = Database()
    async with db.async_session() as session:
        platform_column = getattr(Bundle, f'platform_{platform.lower()}', None)
        if not platform_column:
            return []
        
        query = (
            select(Bundle)
            .where(platform_column == True)
            .order_by(desc(Bundle.discount))
            .limit(limit)
        )
        result = await session.execute(query)
        bundles = result.scalars().all()
        
        return [bundle.to_dict() for bundle in bundles]


async def get_bundle_with_apps(bundle_id: str):
    """
    Query otimizada: Bundle completo com lista de apps
    Frontend: Página de detalhes do bundle
    """
    db = Database()
    async with db.async_session() as session:
        # Busca bundle
        bundle = await session.get(Bundle, bundle_id)
        if not bundle:
            return None
        
        # Busca apps relacionados
        query = select(BundleApp).where(BundleApp.bundle_id == bundle_id)
        result = await session.execute(query)
        apps = result.scalars().all()
        
        bundle_dict = bundle.to_dict()
        bundle_dict['app_ids'] = [app.app_id for app in apps]
        
        return bundle_dict


async def get_price_history(bundle_id: str, days: int = 30):
    """
    Query otimizada: Histórico de preços
    Frontend: Gráfico de histórico de preços
    """
    db = Database()
    async with db.async_session() as session:
        from datetime import datetime, timedelta
        cutoff = datetime.utcnow() - timedelta(days=days)
        
        query = (
            select(PriceHistory)
            .where(
                PriceHistory.bundle_id == bundle_id,
                PriceHistory.recorded_at >= cutoff
            )
            .order_by(PriceHistory.recorded_at)
        )
        result = await session.execute(query)
        history = result.scalars().all()
        
        return [
            {
                'date': h.recorded_at.isoformat(),
                'final_price': h.final_price,
                'original_price': h.original_price,
                'discount': h.discount
            }
            for h in history
        ]


async def get_stats():
    """
    Query otimizada: Estatísticas gerais
    Frontend: Dashboard / Página de estatísticas
    """
    db = Database()
    async with db.async_session() as session:
        # Total de bundles
        total_query = select(func.count(Bundle.id))
        total_result = await session.execute(total_query)
        total_bundles = total_result.scalar()
        
        # Bundles com desconto
        with_discount_query = select(func.count(Bundle.id)).where(Bundle.discount > 0)
        with_discount_result = await session.execute(with_discount_query)
        with_discount = with_discount_result.scalar()
        
        # Desconto médio
        avg_discount_query = select(func.avg(Bundle.discount)).where(Bundle.discount > 0)
        avg_discount_result = await session.execute(avg_discount_query)
        avg_discount = avg_discount_result.scalar() or 0
        
        # Preço médio
        avg_price_query = select(func.avg(Bundle.final_price))
        avg_price_result = await session.execute(avg_price_query)
        avg_price = avg_price_result.scalar() or 0
        
        return {
            'total_bundles': total_bundles,
            'with_discount': with_discount,
            'avg_discount': round(avg_discount, 1),
            'avg_price': round(avg_price, 2)
        }


async def search_bundles(search_term: str, limit: int = 50):
    """
    Query otimizada: Busca por nome
    Frontend: Barra de busca
    """
    db = Database()
    async with db.async_session() as session:
        query = (
            select(Bundle)
            .where(Bundle.name.ilike(f'%{search_term}%'))
            .order_by(desc(Bundle.discount))
            .limit(limit)
        )
        result = await session.execute(query)
        bundles = result.scalars().all()
        
        return [bundle.to_dict() for bundle in bundles]


async def main():
    """Testa todas as queries"""
    print("=== EXEMPLOS DE QUERIES PARA FRONTEND ===\n")
    
    print("1. Top 5 Deals:")
    deals = await get_top_deals(5)
    for bundle in deals:
        print(f"   - {bundle['name']} ({bundle['price']['discount']}% off)")
    
    print("\n2. Estatísticas:")
    stats = await get_stats()
    print(f"   - Total de bundles: {stats['total_bundles']}")
    print(f"   - Com desconto: {stats['with_discount']}")
    print(f"   - Desconto médio: {stats['avg_discount']}%")
    print(f"   - Preço médio: R$ {stats['avg_price']}")
    
    print("\n3. Bundles recentes (últimas 24h):")
    recent = await get_recent_bundles(24, 3)
    for bundle in recent:
        print(f"   - {bundle['name']}")
    
    print("\n4. Bundles baratos (até R$20):")
    cheap = await get_bundles_by_price_range(0, 20, 3)
    for bundle in cheap:
        print(f"   - {bundle['name']} - {bundle['price']['formatted_final']}")
    
    print("\n✓ Todas as queries executadas com sucesso!")


if __name__ == "__main__":
    asyncio.run(main())