"""Performance optimization utilities for RateRight application"""
import functools
import hashlib
import json
import time
from datetime import datetime, timedelta
from typing import Any, Callable, Dict, List, Optional, Union
from flask import current_app, g, request, session
from sqlalchemy import text
from app.extensions import db, cache
import redis


class DatabaseOptimizations:
    """Database performance optimizations"""
    
    @staticmethod
    def optimize_user_queries():
        """Optimize common user-related queries with indexes"""
        optimization_queries = [
            # Index for user lookups by email (most common)
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_active ON users(email) WHERE is_active = true;",
            
            # Index for user role-based queries
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_role_location ON users(role, location) WHERE is_active = true;",
            
            # Index for gamification queries
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_points_league ON users(total_points DESC, seasonal_league) WHERE is_active = true;",
            
            # Index for job searches
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_location_budget ON jobs(location, budget_min, budget_max) WHERE status = 'open';",
            
            # Index for job status and date queries
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_status_created ON jobs(status, created_date DESC);",
            
            # Index for contract queries
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contracts_status_dates ON contracts(status, start_date, end_date);",
            
            # Index for rating calculations
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reviews_reviewee_rating ON reviews(reviewee_id, overall_rating, created_date DESC);",
            
            # Index for messaging queries
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_participants ON messages(sender_id, receiver_id, created_date DESC);",
            
            # Index for notification queries
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_notifications_user_unread ON notifications(user_id, is_read, created_date DESC);",
            
            # Index for gamification point activities
            "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_point_activities_user_date ON point_activities(user_id, created_date DESC);",
        ]
        
        try:
            for query in optimization_queries:
                db.session.execute(text(query))
            db.session.commit()
            current_app.logger.info("Database indexes optimized successfully")
        except Exception as e:
            db.session.rollback()
            current_app.logger.error(f"Database optimization failed: {str(e)}")
    
    @staticmethod
    def setup_connection_pooling():
        """Configure optimal database connection pooling"""
        engine = db.engine
        
        # Optimize connection pool settings
        engine.pool._pool_size = 20  # Adjust based on expected concurrent users
        engine.pool._max_overflow = 30
        engine.pool._pool_recycle = 3600  # Recycle connections every hour
        engine.pool._pool_pre_ping = True  # Verify connections before use
        
        current_app.logger.info("Database connection pooling optimized")
    
    @staticmethod
    def analyze_slow_queries():
        """Analyze and log slow queries for optimization"""
        slow_query_config = """
        ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries taking > 1 second
        ALTER SYSTEM SET log_statement = 'all';
        ALTER SYSTEM SET log_duration = on;
        SELECT pg_reload_conf();
        """
        
        try:
            db.session.execute(text(slow_query_config))
            db.session.commit()
            current_app.logger.info("Slow query logging enabled")
        except Exception as e:
            current_app.logger.error(f"Failed to enable slow query logging: {str(e)}")


class CachingOptimizations:
    """Caching performance optimizations"""
    
    @staticmethod
    def cache_key_generator(*args, **kwargs) -> str:
        """Generate consistent cache keys"""
        cache_data = {
            'args': args,
            'kwargs': kwargs,
            'user_id': getattr(g, 'user_id', None),
            'endpoint': request.endpoint if request else None
        }
        cache_string = json.dumps(cache_data, sort_keys=True, default=str)
        return hashlib.sha256(cache_string.encode()).hexdigest()[:16]
    
    @staticmethod
    def cached_query(expiration: int = 300):
        """Decorator for caching database query results"""
        def decorator(func: Callable) -> Callable:
            @functools.wraps(func)
            def wrapper(*args, **kwargs):
                cache_key = f"query_{func.__name__}_{CachingOptimizations.cache_key_generator(*args, **kwargs)}"
                
                # Try to get from cache first
                try:
                    cached_result = cache.get(cache_key)
                    if cached_result is not None:
                        return cached_result
                except Exception:
                    pass  # Cache miss or error, continue with query
                
                # Execute query and cache result
                result = func(*args, **kwargs)
                
                try:
                    cache.set(cache_key, result, timeout=expiration)
                except Exception:
                    pass  # Cache set failed, but we have the result
                
                return result
            return wrapper
        return decorator
    
    @staticmethod
    def cache_user_data(user_id: int, data: Dict, expiration: int = 900):
        """Cache user-specific data"""
        cache_key = f"user_data_{user_id}"
        try:
            cache.set(cache_key, data, timeout=expiration)
        except Exception as e:
            current_app.logger.error(f"Failed to cache user data: {str(e)}")
    
    @staticmethod
    def get_cached_user_data(user_id: int) -> Optional[Dict]:
        """Retrieve cached user data"""
        cache_key = f"user_data_{user_id}"
        try:
            return cache.get(cache_key)
        except Exception as e:
            current_app.logger.error(f"Failed to retrieve cached user data: {str(e)}")
            return None
    
    @staticmethod
    def cache_leaderboard_data(league: str, data: List, expiration: int = 600):
        """Cache leaderboard data"""
        cache_key = f"leaderboard_{league}"
        try:
            cache.set(cache_key, data, timeout=expiration)
        except Exception as e:
            current_app.logger.error(f"Failed to cache leaderboard: {str(e)}")
    
    @staticmethod
    def invalidate_user_cache(user_id: int):
        """Invalidate all cache entries for a user"""
        patterns = [
            f"user_data_{user_id}",
            f"query_*user*{user_id}*",
            "leaderboard_*",  # Invalidate leaderboards when user data changes
        ]
        
        try:
            for pattern in patterns:
                cache.delete_many(cache.cache.keys(pattern))
        except Exception as e:
            current_app.logger.error(f"Failed to invalidate user cache: {str(e)}")


class AssetOptimizations:
    """Static asset optimization utilities"""
    
    @staticmethod
    def generate_asset_version() -> str:
        """Generate version string for cache busting"""
        return hashlib.md5(str(time.time()).encode()).hexdigest()[:8]
    
    @staticmethod
    def compress_response():
        """Configure gzip compression for responses"""
        # This would typically be handled by nginx in production
        # But we can set headers for development
        return {
            'Vary': 'Accept-Encoding',
            'Content-Encoding': 'gzip'
        }


class QueryOptimizations:
    """Optimized database query patterns"""
    
    @staticmethod
    @CachingOptimizations.cached_query(expiration=300)
    def get_user_with_stats(user_id: int) -> Dict:
        """Optimized user query with statistics"""
        query = text("""
            SELECT 
                u.*,
                COUNT(DISTINCT c.id) as total_contracts,
                COUNT(DISTINCT CASE WHEN c.status = 'completed' THEN c.id END) as completed_contracts,
                COALESCE(AVG(r.overall_rating), 0) as avg_rating,
                COALESCE(SUM(pa.points), 0) as total_points,
                COUNT(DISTINCT n.id) as unread_notifications
            FROM users u
            LEFT JOIN contracts c ON (u.id = c.contractor_id OR u.id = c.worker_id)
            LEFT JOIN reviews r ON r.reviewee_id = u.id
            LEFT JOIN point_activities pa ON pa.user_id = u.id
            LEFT JOIN notifications n ON (n.user_id = u.id AND n.is_read = false)
            WHERE u.id = :user_id AND u.is_active = true
            GROUP BY u.id
        """)
        
        result = db.session.execute(query, {'user_id': user_id}).fetchone()
        
        if result:
            return dict(result._mapping)
        return {}
    
    @staticmethod
    @CachingOptimizations.cached_query(expiration=600)
    def get_job_matches_for_worker(worker_id: int, limit: int = 20) -> List[Dict]:
        """Optimized job matching query for workers"""
        query = text("""
            SELECT 
                j.*,
                u.first_name as contractor_name,
                u.average_rating as contractor_rating,
                ST_Distance(
                    ST_Point(j.longitude, j.latitude),
                    ST_Point(w.longitude, w.latitude)
                ) as distance
            FROM jobs j
            JOIN users u ON j.contractor_id = u.id
            JOIN users w ON w.id = :worker_id
            WHERE j.status = 'open'
            AND j.budget_max >= (
                SELECT COALESCE(AVG(proposed_rate) * 0.8, j.budget_min)
                FROM applications a 
                WHERE a.worker_id = :worker_id 
                AND a.created_date > CURRENT_DATE - INTERVAL '30 days'
            )
            AND (j.location_flexible = true OR j.location ILIKE CONCAT('%', w.location, '%'))
            ORDER BY 
                CASE WHEN j.priority = 'high' THEN 1 ELSE 2 END,
                distance ASC,
                j.budget_max DESC,
                j.created_date DESC
            LIMIT :limit
        """)
        
        results = db.session.execute(query, {
            'worker_id': worker_id, 
            'limit': limit
        }).fetchall()
        
        return [dict(row._mapping) for row in results]
    
    @staticmethod
    @CachingOptimizations.cached_query(expiration=300)
    def get_leaderboard_data(league: str, limit: int = 50) -> List[Dict]:
        """Optimized leaderboard query"""
        query = text("""
            SELECT 
                u.id,
                u.first_name,
                u.last_name,
                u.location,
                u.total_points,
                u.current_level,
                u.jobs_completed,
                u.average_rating,
                RANK() OVER (ORDER BY u.total_points DESC) as rank,
                COALESCE(recent_points.points_this_week, 0) as points_this_week
            FROM users u
            LEFT JOIN (
                SELECT 
                    user_id,
                    SUM(points) as points_this_week
                FROM point_activities
                WHERE created_date >= CURRENT_DATE - INTERVAL '7 days'
                GROUP BY user_id
            ) recent_points ON recent_points.user_id = u.id
            WHERE u.is_active = true
            AND (:league = 'all' OR u.seasonal_league = :league)
            AND u.total_points > 0
            ORDER BY u.total_points DESC
            LIMIT :limit
        """)
        
        results = db.session.execute(query, {
            'league': league,
            'limit': limit
        }).fetchall()
        
        return [dict(row._mapping) for row in results]


class RealTimeOptimizations:
    """Optimizations for real-time features"""
    
    @staticmethod
    def setup_redis_connection():
        """Optimize Redis connection for real-time features"""
        redis_client = redis.Redis(
            host=current_app.config.get('REDIS_HOST', 'localhost'),
            port=current_app.config.get('REDIS_PORT', 6379),
            db=current_app.config.get('REDIS_DB', 0),
            decode_responses=True,
            socket_keepalive=True,
            socket_keepalive_options={},
            connection_pool_class_kwargs={
                'max_connections': 50,
                'retry_on_timeout': True
            }
        )
        return redis_client
    
    @staticmethod
    def optimize_message_polling(user_id: int) -> List[Dict]:
        """Optimized message polling for real-time chat"""
        cache_key = f"recent_messages_{user_id}"
        
        # Try cache first
        try:
            cached_messages = cache.get(cache_key)
            if cached_messages:
                return cached_messages
        except Exception:
            pass
        
        # Query recent messages
        query = text("""
            SELECT 
                m.id,
                m.sender_id,
                m.receiver_id,
                m.content,
                m.message_type,
                m.created_date,
                m.is_read,
                sender.first_name as sender_name,
                sender.avatar_url as sender_avatar
            FROM messages m
            JOIN users sender ON sender.id = m.sender_id
            WHERE (m.sender_id = :user_id OR m.receiver_id = :user_id)
            AND m.created_date >= CURRENT_TIMESTAMP - INTERVAL '1 hour'
            ORDER BY m.created_date DESC
            LIMIT 100
        """)
        
        results = db.session.execute(query, {'user_id': user_id}).fetchall()
        messages = [dict(row._mapping) for row in results]
        
        # Cache for 1 minute
        try:
            cache.set(cache_key, messages, timeout=60)
        except Exception:
            pass
        
        return messages


class APIOptimizations:
    """API performance optimizations"""
    
    @staticmethod
    def paginate_response(query, page: int = 1, per_page: int = 20) -> Dict:
        """Optimized pagination with metadata"""
        total = query.count()
        items = query.offset((page - 1) * per_page).limit(per_page).all()
        
        return {
            'items': [item.to_dict() if hasattr(item, 'to_dict') else item for item in items],
            'pagination': {
                'page': page,
                'per_page': per_page,
                'total': total,
                'pages': (total + per_page - 1) // per_page,
                'has_prev': page > 1,
                'has_next': page * per_page < total
            }
        }
    
    @staticmethod
    def optimize_json_response(data: Any) -> Dict:
        """Optimize JSON responses"""
        if isinstance(data, list):
            # Remove None values and optimize nested objects
            optimized_data = []
            for item in data:
                if isinstance(item, dict):
                    optimized_item = {k: v for k, v in item.items() if v is not None}
                    optimized_data.append(optimized_item)
                else:
                    optimized_data.append(item)
            return {'data': optimized_data}
        
        elif isinstance(data, dict):
            # Remove None values
            return {k: v for k, v in data.items() if v is not None}
        
        return {'data': data}


class PerformanceMonitoring:
    """Performance monitoring utilities"""
    
    @staticmethod
    def log_slow_operations(threshold: float = 1.0):
        """Decorator to log slow operations"""
        def decorator(func: Callable) -> Callable:
            @functools.wraps(func)
            def wrapper(*args, **kwargs):
                start_time = time.time()
                result = func(*args, **kwargs)
                execution_time = time.time() - start_time
                
                if execution_time > threshold:
                    current_app.logger.warning(
                        f"Slow operation detected: {func.__name__} took {execution_time:.2f}s"
                    )
                
                return result
            return wrapper
        return decorator
    
    @staticmethod
    def track_database_queries():
        """Track database query performance"""
        from sqlalchemy import event
        from sqlalchemy.engine import Engine
        
        @event.listens_for(Engine, "before_cursor_execute")
        def receive_before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
            context._query_start_time = time.time()
        
        @event.listens_for(Engine, "after_cursor_execute")
        def receive_after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
            total = time.time() - context._query_start_time
            if total > 0.5:  # Log queries taking more than 500ms
                current_app.logger.warning(f"Slow query: {total:.2f}s - {statement[:100]}...")
    
    @staticmethod
    def get_performance_metrics() -> Dict:
        """Get current performance metrics"""
        import psutil
        import os
        
        return {
            'memory_usage': psutil.Process(os.getpid()).memory_info().rss / 1024 / 1024,  # MB
            'cpu_percent': psutil.Process(os.getpid()).cpu_percent(),
            'active_connections': db.engine.pool.checkedout(),
            'cache_stats': {
                'hits': getattr(cache, '_hits', 0),
                'misses': getattr(cache, '_misses', 0)
            }
        }


# Initialize optimizations
def initialize_performance_optimizations(app):
    """Initialize all performance optimizations"""
    with app.app_context():
        try:
            # Database optimizations
            DatabaseOptimizations.optimize_user_queries()
            DatabaseOptimizations.setup_connection_pooling()
            
            # Enable query monitoring
            PerformanceMonitoring.track_database_queries()
            
            app.logger.info("Performance optimizations initialized successfully")
            
        except Exception as e:
            app.logger.error(f"Failed to initialize performance optimizations: {str(e)}")