"""
Views for monitoring database performance and query optimization
"""
from django.shortcuts import render, redirect
from django.contrib.admin.views.decorators import staff_member_required
from django.contrib import messages
from django.http import JsonResponse
from django.utils import timezone
from django.views.decorators.http import require_http_methods
from django.views.decorators.csrf import csrf_exempt
from django.db import connection
from users.database_optimization_service import db_optimizer
from users.decorators import require_permission
import json
import logging

logger = logging.getLogger(__name__)


@staff_member_required
@require_permission('admin', 'database_monitoring')
def database_performance_dashboard(request):
    """
    Display database performance monitoring dashboard
    """
    try:
        # Get performance statistics
        performance_stats = db_optimizer.get_performance_statistics()
        
        # Get recent query performance data
        query_performance = _get_query_performance_data()
        
        # Get database health metrics
        health_metrics = _get_database_health_metrics()
        
        # Get optimization recommendations
        recommendations = db_optimizer._generate_performance_recommendations()
        
        context = {
            'performance_stats': performance_stats,
            'query_performance': query_performance,
            'health_metrics': health_metrics,
            'recommendations': recommendations,
            'page_title': 'Database Performance Monitoring',
            'last_updated': timezone.now()
        }
        
        return render(request, 'users/database_performance_dashboard.html', context)
        
    except Exception as e:
        logger.error(f"Error loading database performance dashboard: {e}")
        messages.error(request, f"Error loading database dashboard: {str(e)}")
        return redirect('admin:index')


@staff_member_required
@require_permission('admin', 'database_optimization')
@require_http_methods(["POST"])
def run_database_optimization(request):
    """
    Run database optimization tasks
    """
    try:
        optimization_type = request.POST.get('optimization_type')
        
        if optimization_type == 'create_indexes':
            results = db_optimizer.optimize_permission_queries()
            
            if 'error' in results:
                messages.error(request, f"Index creation failed: {results['error']}")
            else:
                messages.success(
                    request,
                    f"Created {results['indexes_created']} index groups, "
                    f"optimized {results['queries_optimized']} query types"
                )
        
        elif optimization_type == 'optimize_queries':
            db_optimizer._optimize_role_template_queries()
            db_optimizer._optimize_user_permission_queries()
            db_optimizer._optimize_analytics_queries()
            
            messages.success(request, "Query optimization completed successfully")
        
        elif optimization_type == 'vacuum_analyze':
            _run_vacuum_analyze()
            messages.success(request, "VACUUM ANALYZE completed on permission tables")
        
        elif optimization_type == 'clear_cache':
            from django.core.cache import cache
            cache.clear()
            messages.success(request, "Database query cache cleared")
        
        else:
            messages.error(request, "Invalid optimization type")
        
        return redirect('database_performance_dashboard')
        
    except Exception as e:
        logger.error(f"Error in database optimization: {e}")
        messages.error(request, f"Optimization failed: {str(e)}")
        return redirect('database_performance_dashboard')


@staff_member_required
@require_http_methods(["GET"])
def database_statistics_api(request):
    """
    API endpoint for real-time database statistics
    """
    try:
        stats = db_optimizer.get_performance_statistics()
        
        # Add real-time connection info
        with connection.cursor() as cursor:
            cursor.execute("SELECT count(*) FROM pg_stat_activity WHERE state = 'active'")
            active_connections = cursor.fetchone()[0]
            
            cursor.execute("SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'")
            idle_connections = cursor.fetchone()[0]
            
            stats['real_time'] = {
                'active_connections': active_connections,
                'idle_connections': idle_connections,
                'total_connections': active_connections + idle_connections,
                'timestamp': timezone.now().isoformat()
            }
        
        return JsonResponse(stats)
        
    except Exception as e:
        logger.error(f"Error getting database statistics: {e}")
        return JsonResponse({
            'error': str(e),
            'timestamp': timezone.now().isoformat()
        }, status=500)


@staff_member_required
@require_http_methods(["GET"])
def query_performance_api(request):
    """
    API endpoint for query performance metrics
    """
    try:
        performance_data = _get_query_performance_data()
        
        return JsonResponse({
            'query_performance': performance_data,
            'timestamp': timezone.now().isoformat()
        })
        
    except Exception as e:
        logger.error(f"Error getting query performance data: {e}")
        return JsonResponse({
            'error': str(e),
            'timestamp': timezone.now().isoformat()
        }, status=500)


@staff_member_required
@require_http_methods(["POST"])
def test_query_performance(request):
    """
    Test query performance with sample operations
    """
    try:
        import time
        from users.models import CustomUser
        
        test_results = {
            'tests': [],
            'total_time': 0,
            'performance_grade': 'Unknown'
        }
        
        # Test 1: User permission lookup
        start_time = time.time()
        users = CustomUser.objects.filter(is_active=True)[:5]
        
        for user in users:
            permissions = db_optimizer.get_optimized_user_permissions(str(user.id), 'loans')
        
        end_time = time.time()
        permission_time = (end_time - start_time) * 1000
        
        test_results['tests'].append({
            'name': 'User Permission Lookup',
            'time_ms': round(permission_time, 2),
            'operations': len(users),
            'avg_time_ms': round(permission_time / len(users), 2) if users else 0
        })
        
        # Test 2: Role template lookup
        start_time = time.time()
        roles = ['admin', 'team_leader', 'loan_officer', 'secretary', 'auditor']
        
        for role in roles:
            template = db_optimizer.get_optimized_role_template(role)
        
        end_time = time.time()
        template_time = (end_time - start_time) * 1000
        
        test_results['tests'].append({
            'name': 'Role Template Lookup',
            'time_ms': round(template_time, 2),
            'operations': len(roles),
            'avg_time_ms': round(template_time / len(roles), 2)
        })
        
        # Test 3: Bulk permission check
        if users:
            start_time = time.time()
            test_permissions = [
                ('loans', 'create_application'),
                ('loans', 'approve_application'),
                ('clients', 'view_list'),
                ('reports', 'export_pdf')
            ]
            
            bulk_results = db_optimizer.bulk_permission_check(str(users[0].id), test_permissions)
            
            end_time = time.time()
            bulk_time = (end_time - start_time) * 1000
            
            test_results['tests'].append({
                'name': 'Bulk Permission Check',
                'time_ms': round(bulk_time, 2),
                'operations': len(test_permissions),
                'avg_time_ms': round(bulk_time / len(test_permissions), 2)
            })
        
        # Calculate total time and grade
        test_results['total_time'] = sum(test['time_ms'] for test in test_results['tests'])
        
        if test_results['total_time'] < 50:
            test_results['performance_grade'] = 'Excellent'
        elif test_results['total_time'] < 200:
            test_results['performance_grade'] = 'Good'
        elif test_results['total_time'] < 500:
            test_results['performance_grade'] = 'Fair'
        else:
            test_results['performance_grade'] = 'Poor'
        
        return JsonResponse({
            'test_results': test_results,
            'timestamp': timezone.now().isoformat()
        })
        
    except Exception as e:
        logger.error(f"Error testing query performance: {e}")
        return JsonResponse({
            'error': str(e),
            'timestamp': timezone.now().isoformat()
        }, status=500)


def _get_query_performance_data():
    """
    Get query performance data from database statistics
    """
    performance_data = {
        'slow_queries': [],
        'frequent_queries': [],
        'table_stats': [],
        'index_usage': []
    }
    
    try:
        with connection.cursor() as cursor:
            # Get slow queries (if pg_stat_statements is available)
            try:
                cursor.execute("""
                    SELECT 
                        substring(query, 1, 100) as query_snippet,
                        calls,
                        total_time,
                        mean_time,
                        rows
                    FROM pg_stat_statements 
                    WHERE query LIKE '%permission%' 
                    OR query LIKE '%user%'
                    ORDER BY mean_time DESC 
                    LIMIT 10
                """)
                
                slow_queries = cursor.fetchall()
                performance_data['slow_queries'] = [
                    {
                        'query': row[0],
                        'calls': row[1],
                        'total_time': round(row[2], 2),
                        'mean_time': round(row[3], 2),
                        'rows': row[4]
                    }
                    for row in slow_queries
                ]
                
            except Exception:
                # pg_stat_statements not available
                performance_data['slow_queries'] = []
            
            # Get table statistics
            cursor.execute("""
                SELECT 
                    schemaname,
                    tablename,
                    n_tup_ins as inserts,
                    n_tup_upd as updates,
                    n_tup_del as deletes,
                    n_live_tup as live_tuples,
                    n_dead_tup as dead_tuples
                FROM pg_stat_user_tables 
                WHERE tablename IN ('page_permissions', 'role_permission_templates', 'user_page_permissions')
                ORDER BY n_live_tup DESC
            """)
            
            table_stats = cursor.fetchall()
            performance_data['table_stats'] = [
                {
                    'table': row[1],
                    'inserts': row[2],
                    'updates': row[3],
                    'deletes': row[4],
                    'live_tuples': row[5],
                    'dead_tuples': row[6]
                }
                for row in table_stats
            ]
            
            # Get index usage statistics
            cursor.execute("""
                SELECT 
                    schemaname,
                    tablename,
                    indexname,
                    idx_tup_read,
                    idx_tup_fetch
                FROM pg_stat_user_indexes 
                WHERE tablename IN ('page_permissions', 'role_permission_templates', 'user_page_permissions')
                ORDER BY idx_tup_read DESC
                LIMIT 20
            """)
            
            index_stats = cursor.fetchall()
            performance_data['index_usage'] = [
                {
                    'table': row[1],
                    'index': row[2],
                    'tuples_read': row[3],
                    'tuples_fetched': row[4]
                }
                for row in index_stats
            ]
            
    except Exception as e:
        logger.warning(f"Could not get complete query performance data: {e}")
    
    return performance_data


def _get_database_health_metrics():
    """
    Get database health metrics
    """
    health_metrics = {
        'connection_health': 'Unknown',
        'cache_hit_ratio': 0,
        'table_bloat': [],
        'lock_conflicts': 0,
        'replication_lag': 0
    }
    
    try:
        with connection.cursor() as cursor:
            # Check connection health
            cursor.execute("SELECT 1")
            health_metrics['connection_health'] = 'Healthy'
            
            # Get cache hit ratio
            cursor.execute("""
                SELECT 
                    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 as cache_hit_ratio
                FROM pg_statio_user_tables
                WHERE schemaname = 'public'
            """)
            
            cache_hit_ratio = cursor.fetchone()[0]
            if cache_hit_ratio:
                health_metrics['cache_hit_ratio'] = round(float(cache_hit_ratio), 2)
            
            # Check for table bloat
            cursor.execute("""
                SELECT 
                    tablename,
                    n_dead_tup,
                    n_live_tup,
                    CASE 
                        WHEN n_live_tup > 0 
                        THEN round((n_dead_tup::float / n_live_tup::float) * 100, 2)
                        ELSE 0 
                    END as bloat_ratio
                FROM pg_stat_user_tables 
                WHERE n_dead_tup > 1000
                ORDER BY bloat_ratio DESC
                LIMIT 10
            """)
            
            bloat_data = cursor.fetchall()
            health_metrics['table_bloat'] = [
                {
                    'table': row[0],
                    'dead_tuples': row[1],
                    'live_tuples': row[2],
                    'bloat_ratio': row[3]
                }
                for row in bloat_data
            ]
            
            # Check for lock conflicts
            cursor.execute("""
                SELECT count(*) 
                FROM pg_stat_database_conflicts 
                WHERE datname = current_database()
            """)
            
            lock_conflicts = cursor.fetchone()[0]
            health_metrics['lock_conflicts'] = lock_conflicts or 0
            
    except Exception as e:
        logger.warning(f"Could not get complete database health metrics: {e}")
        health_metrics['connection_health'] = f'Error: {str(e)}'
    
    return health_metrics


def _run_vacuum_analyze():
    """
    Run VACUUM ANALYZE on permission tables
    """
    permission_tables = [
        'page_permissions',
        'role_permission_templates', 
        'user_page_permissions',
        'portfolio_snapshots',
        'client_growth_metrics'
    ]
    
    with connection.cursor() as cursor:
        for table in permission_tables:
            try:
                cursor.execute(f'VACUUM ANALYZE {table}')
                logger.info(f"VACUUM ANALYZE completed for {table}")
            except Exception as e:
                logger.warning(f"Could not VACUUM ANALYZE {table}: {e}")