"""
Query Optimizer for Reports System

Provides optimized querysets with select_related and prefetch_related
to minimize database queries and improve performance.
"""

from django.db.models import QuerySet, Prefetch
from loans.models import Loan, LoanApplication, Repayment
from users.models import CustomUser


class QueryOptimizer:
    """
    Centralized query optimization for reports system.
    
    Provides methods to get optimized querysets with proper
    select_related and prefetch_related calls to avoid N+1 queries.
    """
    
    @staticmethod
    def get_optimized_loans_queryset(base_queryset: QuerySet = None) -> QuerySet:
        """
        Get optimized loans queryset with all related data preloaded.
        
        This method adds select_related for foreign keys and prefetch_related
        for reverse relationships to minimize database queries.
        
        Args:
            base_queryset: Optional base queryset to optimize (defaults to Loan.objects.all())
            
        Returns:
            Optimized QuerySet with related data preloaded
        """
        if base_queryset is None:
            base_queryset = Loan.objects.all()
        
        # Select related foreign keys (1-to-1 and many-to-1 relationships)
        optimized_qs = base_queryset.select_related(
            'borrower',  # Loan -> CustomUser
            'borrower__branch',  # CustomUser -> Branch
            'application',  # Loan -> LoanApplication
            'application__loan_product',  # LoanApplication -> LoanProduct
        )
        
        # Prefetch related reverse relationships (1-to-many relationships)
        optimized_qs = optimized_qs.prefetch_related(
            'repayments',  # Loan -> Repayment (reverse)
            'penalty_charges',  # Loan -> PenaltyCharge (reverse)
        )
        
        return optimized_qs
    
    @staticmethod
    def get_optimized_clients_queryset(base_queryset: QuerySet = None) -> QuerySet:
        """
        Get optimized clients queryset with related loan data preloaded.
        
        Args:
            base_queryset: Optional base queryset to optimize (defaults to CustomUser.objects.all())
            
        Returns:
            Optimized QuerySet with related data preloaded
        """
        if base_queryset is None:
            base_queryset = CustomUser.objects.filter(role='borrower')
        
        # Select related foreign keys
        optimized_qs = base_queryset.select_related(
            'branch',  # CustomUser -> Branch
        )
        
        # Prefetch related loans with their own optimizations
        optimized_loans = Loan.objects.select_related(
            'application',
            'application__loan_product'
        ).prefetch_related(
            'repayments',
            'penalty_charges'
        )
        
        optimized_qs = optimized_qs.prefetch_related(
            Prefetch('loans', queryset=optimized_loans),
        )
        
        return optimized_qs
    
    @staticmethod
    def get_optimized_repayments_queryset(base_queryset: QuerySet = None) -> QuerySet:
        """
        Get optimized repayments queryset with related loan and borrower data.
        
        Args:
            base_queryset: Optional base queryset to optimize (defaults to Repayment.objects.all())
            
        Returns:
            Optimized QuerySet with related data preloaded
        """
        if base_queryset is None:
            base_queryset = Repayment.objects.all()
        
        # Select related foreign keys
        optimized_qs = base_queryset.select_related(
            'loan',  # Repayment -> Loan
            'loan__borrower',  # Loan -> CustomUser
            'loan__borrower__branch',  # CustomUser -> Branch
            'loan__application',  # Loan -> LoanApplication
            'loan__application__loan_product',  # LoanApplication -> LoanProduct
        )
        
        return optimized_qs
    
    @staticmethod
    def get_optimized_applications_queryset(base_queryset: QuerySet = None) -> QuerySet:
        """
        Get optimized loan applications queryset with related data.
        
        Args:
            base_queryset: Optional base queryset to optimize (defaults to LoanApplication.objects.all())
            
        Returns:
            Optimized QuerySet with related data preloaded
        """
        if base_queryset is None:
            base_queryset = LoanApplication.objects.all()
        
        # Select related foreign keys
        optimized_qs = base_queryset.select_related(
            'borrower',  # LoanApplication -> CustomUser
            'borrower__branch',  # CustomUser -> Branch
            'loan_product',  # LoanApplication -> LoanProduct
        )
        
        # Prefetch related loans
        optimized_qs = optimized_qs.prefetch_related(
            'loans',  # LoanApplication -> Loan (reverse)
        )
        
        return optimized_qs
    
    @staticmethod
    def optimize_for_report_type(queryset: QuerySet, report_type: str) -> QuerySet:
        """
        Optimize queryset based on specific report type requirements.
        
        Different reports need different related data, so this method
        applies optimizations specific to each report type.
        
        Args:
            queryset: Base queryset to optimize
            report_type: Type of report ('loans_due', 'processing_fees', etc.)
            
        Returns:
            Optimized QuerySet for the specific report type
        """
        # Common optimizations for all loan-based reports
        if report_type in ['loans_due', 'delinquent', 'arrears', 'overdue']:
            return QueryOptimizer.get_optimized_loans_queryset(queryset)
        
        # Client-based reports
        elif report_type in ['client_reports', 'borrower_reports']:
            return QueryOptimizer.get_optimized_clients_queryset(queryset)
        
        # Repayment-based reports
        elif report_type in ['collection_reports', 'repayment_reports']:
            return QueryOptimizer.get_optimized_repayments_queryset(queryset)
        
        # Application-based reports
        elif report_type in ['application_reports']:
            return QueryOptimizer.get_optimized_applications_queryset(queryset)
        
        # Financial reports (processing fees, interest income, etc.)
        elif report_type in ['processing_fees', 'interest_income', 'registration_fees']:
            return QueryOptimizer.get_optimized_loans_queryset(queryset)
        
        # Default: apply basic loan optimization
        else:
            return QueryOptimizer.get_optimized_loans_queryset(queryset)