from django.db.models import Q, F
from django.utils import timezone
from datetime import timedelta

# Multiple filter conditions (AND by default)
posts = Post.objects.filter(
    status='published',
    author__is_active=True,
    created_at__gte=timezone.now() - timedelta(days=30)
)

# Chaining filters (equivalent to above)
posts = Post.objects.filter(status='published') \
                   .filter(author__is_active=True) \
                   .filter(created_at__gte=timezone.now() - timedelta(days=30))

# Exclude conditions
posts = Post.objects.exclude(status='draft') \
                   .exclude(author__email__endswith='@spam.com')

# Combining filter and exclude
posts = Post.objects.filter(status='published') \
                   .exclude(tags__name='deprecated')

# Complex Q object combinations
search_query = Q(title__icontains='django') | Q(content__icontains='django')
category_filter = Q(category__slug='programming') | Q(category__slug='web-dev')
date_filter = Q(created_at__gte=timezone.now() - timedelta(days=90))

posts = Post.objects.filter(search_query & category_filter & date_filter)

# Nested Q objects
posts = Post.objects.filter(
    Q(status='published') & 
    (Q(author__name__icontains='john') | Q(author__email__icontains='john')) &
    ~Q(tags__name='archived')
)

# Text field lookups
posts = Post.objects.filter(title__exact='Django Tutorial')
posts = Post.objects.filter(title__iexact='django tutorial')  # Case-insensitive
posts = Post.objects.filter(title__contains='Django')
posts = Post.objects.filter(title__icontains='django')       # Case-insensitive
posts = Post.objects.filter(title__startswith='How to')
posts = Post.objects.filter(title__endswith='Tutorial')
posts = Post.objects.filter(title__istartswith='how to')     # Case-insensitive
posts = Post.objects.filter(title__iendswith='tutorial')     # Case-insensitive

# Numeric field lookups
posts = Post.objects.filter(view_count__exact=1000)
posts = Post.objects.filter(view_count__gt=1000)            # Greater than
posts = Post.objects.filter(view_count__gte=1000)           # Greater than or equal
posts = Post.objects.filter(view_count__lt=1000)            # Less than
posts = Post.objects.filter(view_count__lte=1000)           # Less than or equal
posts = Post.objects.filter(view_count__range=(100, 1000))  # Between values

# Date and time lookups
from datetime import date, datetime

posts = Post.objects.filter(created_at__year=2023)
posts = Post.objects.filter(created_at__month=12)
posts = Post.objects.filter(created_at__day=25)
posts = Post.objects.filter(created_at__week=52)
posts = Post.objects.filter(created_at__week_day=1)         # Sunday = 1
posts = Post.objects.filter(created_at__quarter=4)          # Q4
posts = Post.objects.filter(created_at__hour=14)
posts = Post.objects.filter(created_at__minute=30)
posts = Post.objects.filter(created_at__second=45)

# Date-only comparisons
today = date.today()
posts = Post.objects.filter(created_at__date=today)
posts = Post.objects.filter(created_at__date__gte=today)

# Time-only comparisons
from datetime import time
morning = time(9, 0)
posts = Post.objects.filter(created_at__time__gte=morning)

# ISO week date
posts = Post.objects.filter(created_at__iso_week_day=1)     # Monday = 1
posts = Post.objects.filter(created_at__iso_year=2023)

# Null and boolean lookups
posts = Post.objects.filter(published_at__isnull=True)     # Unpublished posts
posts = Post.objects.filter(published_at__isnull=False)    # Published posts
posts = Post.objects.filter(is_featured=True)
posts = Post.objects.filter(is_featured__exact=True)       # Equivalent

# List membership
post_ids = [1, 2, 3, 4, 5]
posts = Post.objects.filter(id__in=post_ids)

categories = ['tech', 'programming', 'web-development']
posts = Post.objects.filter(category__slug__in=categories)

# Subquery filtering
popular_authors = Author.objects.filter(posts__view_count__gt=10000)
posts = Post.objects.filter(author__in=popular_authors)

# Regular expressions (database-dependent)
posts = Post.objects.filter(title__regex=r'^(How|What|Why)')
posts = Post.objects.filter(title__iregex=r'^(how|what|why)')  # Case-insensitive

# Full-text search (PostgreSQL)
# posts = Post.objects.filter(title__search='django tutorial')

# Forward relationship filtering
posts = Post.objects.filter(author__name='John Doe')
posts = Post.objects.filter(author__email__endswith='@company.com')
posts = Post.objects.filter(category__parent__name='Technology')

# Reverse relationship filtering
authors = Author.objects.filter(posts__status='published')
categories = Category.objects.filter(posts__view_count__gt=1000)

# Many-to-many filtering
posts = Post.objects.filter(tags__name='Django')
posts = Post.objects.filter(tags__name__in=['Django', 'Python'])

# Multiple conditions on same relationship
# Posts that have both 'Django' and 'Python' tags
posts = Post.objects.filter(tags__name='Django').filter(tags__name='Python')

# Posts by authors who have written about Django
authors_writing_django = Author.objects.filter(posts__tags__name='Django')
posts = Post.objects.filter(author__in=authors_writing_django)

# Complex relationship queries
# Authors who have posts in multiple categories
multi_category_authors = Author.objects.filter(
    posts__category__name='Programming'
).filter(
    posts__category__name='Web Development'
).distinct()

# Posts by prolific authors (more than 10 posts)
from django.db.models import Count
prolific_authors = Author.objects.annotate(
    post_count=Count('posts')
).filter(post_count__gt=10)

posts = Post.objects.filter(author__in=prolific_authors)

# Single field ordering
posts = Post.objects.order_by('created_at')          # Ascending
posts = Post.objects.order_by('-created_at')         # Descending

# Multiple field ordering
posts = Post.objects.order_by('-is_featured', '-created_at', 'title')

# Ordering by related fields
posts = Post.objects.order_by('author__name', '-created_at')
posts = Post.objects.order_by('category__name', 'author__last_name')

# Random ordering
posts = Post.objects.order_by('?')

# Clear existing ordering
posts = Post.objects.order_by('created_at').order_by()  # Removes ordering

from django.db.models import F, Case, When, Value, IntegerField
from django.db.models.functions import Lower, Length

# Ordering by computed values
posts = Post.objects.annotate(
    engagement_score=F('view_count') + F('like_count') * 2
).order_by('-engagement_score')

# Case-insensitive ordering
authors = Author.objects.order_by(Lower('name'))

# Ordering by field length
posts = Post.objects.order_by(Length('title'))

# Custom ordering with Case/When
posts = Post.objects.annotate(
    status_order=Case(
        When(status='featured', then=Value(1)),
        When(status='published', then=Value(2)),
        When(status='draft', then=Value(3)),
        default=Value(4),
        output_field=IntegerField()
    )
).order_by('status_order', '-created_at')

# Null values ordering (database-dependent)
posts = Post.objects.order_by(F('published_at').asc(nulls_last=True))
posts = Post.objects.order_by(F('published_at').desc(nulls_first=True))

# Ordering by aggregated values
from django.db.models import Avg, Count, Max

authors = Author.objects.annotate(
    avg_views=Avg('posts__view_count'),
    post_count=Count('posts'),
    latest_post=Max('posts__created_at')
).order_by('-avg_views', '-post_count')

# Ordering by related field aggregation
categories = Category.objects.annotate(
    total_views=Count('posts__view_count'),
    post_count=Count('posts')
).order_by('-total_views')

def get_posts(sort_by='created_at', order='desc'):
    """Dynamic post ordering based on parameters"""
    
    valid_sort_fields = {
        'created_at': 'created_at',
        'title': 'title',
        'author': 'author__name',
        'category': 'category__name',
        'views': 'view_count',
        'likes': 'like_count'
    }
    
    sort_field = valid_sort_fields.get(sort_by, 'created_at')
    
    if order == 'desc':
        sort_field = f'-{sort_field}'
    
    return Post.objects.filter(status='published').order_by(sort_field)

# Usage
recent_posts = get_posts('created_at', 'desc')
popular_posts = get_posts('views', 'desc')
alphabetical_posts = get_posts('title', 'asc')

# Advanced dynamic ordering with multiple criteria
def get_sorted_posts(primary_sort=None, secondary_sort=None, **filters):
    """Flexible post sorting with multiple criteria"""
    
    queryset = Post.objects.filter(**filters)
    
    order_fields = []
    
    if primary_sort:
        order_fields.append(primary_sort)
    
    if secondary_sort:
        order_fields.append(secondary_sort)
    
    # Default ordering
    if not order_fields:
        order_fields = ['-created_at']
    
    return queryset.order_by(*order_fields)

# Usage
posts = get_sorted_posts(
    primary_sort='-is_featured',
    secondary_sort='-view_count',
    status='published'
)

# Get first 10 posts
posts = Post.objects.all()[:10]

# Get posts 10-20 (pagination)
posts = Post.objects.all()[10:20]

# Get first post
first_post = Post.objects.all()[0]  # Raises IndexError if empty
first_post = Post.objects.first()   # Returns None if empty

# Get last post
last_post = Post.objects.all()[-1]  # Not supported, use:
last_post = Post.objects.last()

# Skip first 5 posts
posts = Post.objects.all()[5:]

# Negative indexing (not supported directly)
# Use reverse() and positive indexing instead
last_5_posts = Post.objects.order_by('-created_at')[:5]

from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger

def paginated_posts(request, posts_per_page=10):
    """Basic pagination example"""
    
    posts = Post.objects.filter(status='published').order_by('-created_at')
    paginator = Paginator(posts, posts_per_page)
    
    page = request.GET.get('page', 1)
    
    try:
        posts_page = paginator.page(page)
    except PageNotAnInteger:
        posts_page = paginator.page(1)
    except EmptyPage:
        posts_page = paginator.page(paginator.num_pages)
    
    return posts_page

# Advanced pagination with search and filtering
def filtered_paginated_posts(request):
    """Pagination with search and filtering"""
    
    # Get filter parameters
    search = request.GET.get('search', '')
    category = request.GET.get('category', '')
    author = request.GET.get('author', '')
    
    # Build query
    posts = Post.objects.filter(status='published')
    
    if search:
        posts = posts.filter(
            Q(title__icontains=search) | Q(content__icontains=search)
        )
    
    if category:
        posts = posts.filter(category__slug=category)
    
    if author:
        posts = posts.filter(author__slug=author)
    
    posts = posts.order_by('-created_at')
    
    # Paginate
    paginator = Paginator(posts, 12)
    page = request.GET.get('page', 1)
    
    try:
        posts_page = paginator.page(page)
    except (PageNotAnInteger, EmptyPage):
        posts_page = paginator.page(1)
    
    return {
        'posts': posts_page,
        'search': search,
        'category': category,
        'author': author,
        'total_count': paginator.count
    }

# Cursor-based pagination for large datasets
def cursor_paginated_posts(cursor=None, limit=20):
    """Cursor-based pagination for better performance on large datasets"""
    
    posts = Post.objects.filter(status='published').order_by('-id')
    
    if cursor:
        posts = posts.filter(id__lt=cursor)
    
    posts_list = list(posts[:limit + 1])  # Get one extra to check if more exist
    
    has_next = len(posts_list) > limit
    if has_next:
        posts_list = posts_list[:-1]  # Remove the extra item
    
    next_cursor = posts_list[-1].id if posts_list and has_next else None
    
    return {
        'posts': posts_list,
        'next_cursor': next_cursor,
        'has_next': has_next
    }

# Efficient counting for pagination
def efficient_paginated_posts(page=1, per_page=10):
    """Efficient pagination that avoids counting when possible"""
    
    offset = (page - 1) * per_page
    limit = per_page + 1  # Get one extra to check if more exist
    
    posts = Post.objects.filter(status='published') \
                       .order_by('-created_at')[offset:offset + limit]
    
    posts_list = list(posts)
    has_next = len(posts_list) > per_page
    
    if has_next:
        posts_list = posts_list[:-1]  # Remove the extra item
    
    return {
        'posts': posts_list,
        'has_next': has_next,
        'has_previous': page > 1,
        'current_page': page
    }

# Batch processing with slicing
def process_posts_in_batches(batch_size=1000):
    """Process large datasets in batches to avoid memory issues"""
    
    total_processed = 0
    
    while True:
        posts = Post.objects.filter(
            status='published',
            processed=False
        ).order_by('id')[:batch_size]
        
        posts_list = list(posts)
        
        if not posts_list:
            break
        
        # Process batch
        for post in posts_list:
            # Do some processing
            post.processed = True
            post.save(update_fields=['processed'])
        
        total_processed += len(posts_list)
        print(f"Processed {total_processed} posts...")
        
        # If we got fewer than batch_size, we're done
        if len(posts_list) < batch_size:
            break
    
    return total_processed

# Optimized slicing with select_related
def get_post_previews(limit=10):
    """Get post previews with optimized queries"""
    
    return Post.objects.select_related('author', 'category') \
                      .prefetch_related('tags') \
                      .filter(status='published') \
                      .only('title', 'slug', 'excerpt', 'created_at',
                            'author__name', 'category__name') \
                      .order_by('-created_at')[:limit]

# Avoid these patterns for large datasets:

# DON'T: Count all records for simple pagination
# total_count = Post.objects.count()  # Expensive on large tables

# DON'T: Use offset for deep pagination
# posts = Post.objects.all()[10000:10010]  # Slow for large offsets

# DO: Use cursor-based pagination for large datasets
# DO: Limit the fields you select
# DO: Use database indexes on ordering fields
# DO: Consider using database-level pagination

# Example of optimized query for large datasets
def get_recent_posts_optimized(limit=20):
    """Optimized query for recent posts"""
    
    return Post.objects.select_related('author') \
                      .only('title', 'slug', 'created_at', 'author__name') \
                      .filter(status='published') \
                      .order_by('-created_at')[:limit]

# Index recommendations for the above query:
# CREATE INDEX idx_post_status_created ON blog_post(status, created_at DESC);
# CREATE INDEX idx_author_name ON auth_user(first_name, last_name);