from flask import request
from sqlalchemy import text, func, desc
from datetime import datetime, timedelta
from src.utils import custom_response
import logging
from src.models import DatabaseContextManager
from flask_smorest import Blueprint


logger = logging.getLogger(__name__)

tutor_analytics = Blueprint('tutor_analytics', __name__)

@tutor_analytics.route('/analytics/tutors', methods=['GET'])
def get_tutor_analytics():
    """Get comprehensive tutor analytics data"""
    try:
        with DatabaseContextManager() as ctx:
            period = request.args.get('period', 'month')
            department = request.args.get('department', 'all')
            
            # Calculate date range based on period
            end_date = datetime.now()
            if period == 'week':
                start_date = end_date - timedelta(days=7)
            elif period == 'month':
                start_date = end_date - timedelta(days=30)
            elif period == 'quarter':
                start_date = end_date - timedelta(days=90)
            elif period == 'year':
                start_date = end_date - timedelta(days=365)
            else:
                start_date = end_date - timedelta(days=30)
            
            # Use the session from DatabaseContextManager
            session = ctx.session
            
            # Summary statistics
            summary_query = text("""
                SELECT 
                    COUNT(DISTINCT t.id) as total_tutors,
                    COUNT(DISTINCT CASE WHEN t.is_on_leave = 0 THEN t.id END) as active_tutors,
                    COUNT(DISTINCT CASE WHEN t.is_on_leave = 1 THEN t.id END) as on_leave_tutors,
                    AVG(COALESCE(tc.total_credits, 0)) as average_workload,
                    COUNT(DISTINCT c.id) as total_courses,
                    COUNT(DISTINCT s.id) as total_students,
                    AVG(COALESCE(t.verification_success_rate, 0)) as average_rating,
                    AVG(COALESCE(att.attendance_rate, 0)) as attendance_rate
                FROM tutors t
                LEFT JOIN (
                    SELECT 
                        tutor_id,
                        SUM(credits) as total_credits
                    FROM tutor_course_association tca
                    JOIN courses c ON tca.course_id = c.id
                    GROUP BY tutor_id
                ) tc ON t.id = tc.tutor_id
                LEFT JOIN courses c ON c.id IN (
                    SELECT course_id FROM tutor_course_association WHERE tutor_id = t.id
                )
                LEFT JOIN students s ON s.speciality_id IN (
                    SELECT speciality_id FROM courses WHERE id IN (
                        SELECT course_id FROM tutor_course_association WHERE tutor_id = t.id
                    )
                )
                LEFT JOIN (
                    SELECT 
                        tutor_id,
                        AVG(CASE WHEN status = 'present' THEN 100.0 ELSE 0.0 END) as attendance_rate
                    FROM attendance a
                    WHERE a.timestamp >= :start_date AND a.timestamp <= :end_date
                    GROUP BY tutor_id
                ) att ON t.id = att.tutor_id
            """)
            
            summary_result = session.execute(summary_query, {
                'start_date': start_date,
                'end_date': end_date
            }).fetchone()
            
            # Workload distribution
            workload_query = text("""
                SELECT 
                    CASE 
                        WHEN COALESCE(tc.total_credits, 0) < 24 THEN 'Under Utilized (<24)'
                        WHEN COALESCE(tc.total_credits, 0) BETWEEN 24 AND 28 THEN 'Optimal (24-28)'
                        ELSE 'Over Utilized (>28)'
                    END as range,
                    COUNT(*) as count,
                    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM tutors), 2) as percentage
                FROM tutors t
                LEFT JOIN (
                    SELECT 
                        tutor_id,
                        SUM(credits) as total_credits
                    FROM tutor_course_association tca
                    JOIN courses c ON tca.course_id = c.id
                    GROUP BY tutor_id
                ) tc ON t.id = tc.tutor_id
                GROUP BY 
                    CASE 
                        WHEN COALESCE(tc.total_credits, 0) < 24 THEN 'Under Utilized (<24)'
                        WHEN COALESCE(tc.total_credits, 0) BETWEEN 24 AND 28 THEN 'Optimal (24-28)'
                        ELSE 'Over Utilized (>28)'
                    END
                ORDER BY count DESC
            """)
            
            workload_results = session.execute(workload_query).fetchall()
            
            # Teaching hours trend (monthly)
            hours_query = text("""
                SELECT 
                    strftime('%Y-%m', dts.session_date) as month,
                    SUM(CAST((julianday(dts.end_time) - julianday(dts.start_time)) * 24 AS INTEGER)) as hours,
                    COUNT(*) as sessions
                FROM daily_teaching_sessions dts
                WHERE dts.session_date >= :start_date 
                AND dts.session_date <= :end_date
                AND dts.status = 'completed'
                GROUP BY strftime('%Y-%m', dts.session_date)
                ORDER BY month
            """)
            
            hours_results = session.execute(hours_query, {
                'start_date': start_date,
                'end_date': end_date
            }).fetchall()
            
            # Department distribution
            dept_query = text("""
                SELECT 
                    COALESCE(td.department_name, 'Unassigned') as department,
                    COUNT(DISTINCT t.id) as tutors,
                    COUNT(DISTINCT c.id) as courses,
                    COUNT(DISTINCT s.id) as students
                FROM tutors t
                LEFT JOIN tutor_departments td ON t.id = td.tutor_id AND td.is_primary = 1
                LEFT JOIN tutor_course_association tca ON t.id = tca.tutor_id
                LEFT JOIN courses c ON tca.course_id = c.id
                LEFT JOIN students s ON s.speciality_id = c.speciality_id
                GROUP BY COALESCE(td.department_name, 'Unassigned')
                ORDER BY tutors DESC
            """)
            
            dept_results = session.execute(dept_query).fetchall()
            
            # Top performing tutors
            performance_query = text("""
                SELECT 
                    u.first_name || ' ' || u.last_name as tutor,
                    COALESCE(t.verification_success_rate, 0) as rating,
                    COALESCE(att.attendance_rate, 0) as attendance,
                    COALESCE(tc.total_credits, 0) as workload,
                    COALESCE(tc.course_count, 0) as courses
                FROM tutors t
                JOIN users u ON t.id = u.id
                LEFT JOIN (
                    SELECT 
                        tutor_id,
                        SUM(credits) as total_credits,
                        COUNT(*) as course_count
                    FROM tutor_course_association tca
                    JOIN courses c ON tca.course_id = c.id
                    GROUP BY tutor_id
                ) tc ON t.id = tc.tutor_id
                LEFT JOIN (
                    SELECT 
                        tutor_id,
                        AVG(CASE WHEN status = 'present' THEN 100.0 ELSE 0.0 END) as attendance_rate
                    FROM attendance a
                    WHERE a.timestamp >= :start_date AND a.timestamp <= :end_date
                    GROUP BY tutor_id
                ) att ON t.id = att.tutor_id
                WHERE t.is_on_leave = 0
                ORDER BY rating DESC, attendance DESC
                LIMIT 10
            """)
            
            performance_results = session.execute(performance_query, {
                'start_date': start_date,
                'end_date': end_date
            }).fetchall()
            
            # Attendance trends (daily)
            attendance_query = text("""
                SELECT 
                    DATE(a.timestamp) as date,
                    SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END) as present,
                    SUM(CASE WHEN a.status = 'absent' THEN 1 ELSE 0 END) as absent,
                    SUM(CASE WHEN a.status = 'late' THEN 1 ELSE 0 END) as late
                FROM attendance a
                WHERE a.timestamp >= :start_date 
                AND a.timestamp <= :end_date
                GROUP BY DATE(a.timestamp)
                ORDER BY date DESC
                LIMIT 30
            """)
            
            attendance_results = session.execute(attendance_query, {
                'start_date': start_date,
                'end_date': end_date
            }).fetchall()
            
            # Course performance
            course_query = text("""
                SELECT 
                    c.title as course,
                    COUNT(DISTINCT e.student_id) as enrollment,
                    COUNT(DISTINCT CASE WHEN cp.completion_percentage >= 80 THEN e.student_id END) as completion,
                    AVG(COALESCE(cp.overall_performance, 0)) as rating
                FROM courses c
                LEFT JOIN enrollment_courses ec ON c.id = ec.course_id
                LEFT JOIN enrollments e ON ec.enrollment_id = e.id
                LEFT JOIN course_progress cp ON e.id = cp.enrollment_id
                WHERE c.is_active = 1
                GROUP BY c.id, c.title
                HAVING enrollment > 0
                ORDER BY enrollment DESC
                LIMIT 10
            """)
            
            course_results = session.execute(course_query).fetchall()
            
            # Format the response
            analytics_data = {
                'summary': {
                    'totalTutors': summary_result.total_tutors or 0,
                    'activeTutors': summary_result.active_tutors or 0,
                    'onLeaveTutors': summary_result.on_leave_tutors or 0,
                    'averageWorkload': round(summary_result.average_workload or 0, 1),
                    'totalCourses': summary_result.total_courses or 0,
                    'totalStudents': summary_result.total_students or 0,
                    'averageRating': round(summary_result.average_rating or 0, 1),
                    'attendanceRate': round(summary_result.attendance_rate or 0, 1)
                },
                'workloadDistribution': [
                    {
                        'range': row.range,
                        'count': row.count,
                        'percentage': row.percentage
                    } for row in workload_results
                ],
                'teachingHours': [
                    {
                        'month': row.month,
                        'hours': row.hours or 0,
                        'sessions': row.sessions or 0
                    } for row in hours_results
                ],
                'departmentDistribution': [
                    {
                        'department': row.department,
                        'tutors': row.tutors,
                        'courses': row.courses,
                        'students': row.students
                    } for row in dept_results
                ],
                'performanceMetrics': [
                    {
                        'tutor': row.tutor,
                        'rating': round(row.rating, 1),
                        'attendance': round(row.attendance, 1),
                        'workload': row.workload,
                        'courses': row.courses
                    } for row in performance_results
                ],
                'attendanceTrends': [
                    {
                        'date': row.date,
                        'present': row.present,
                        'absent': row.absent,
                        'late': row.late
                    } for row in attendance_results
                ],
                'coursePerformance': [
                    {
                        'course': row.course,
                        'enrollment': row.enrollment,
                        'completion': row.completion,
                        'rating': round(row.rating, 1)
                    } for row in course_results
                ]
            }
            
            return custom_response(
                success=True,
                data=analytics_data,
                status_code=200
            )
            
    except Exception as e:
        logger.error(f"Error fetching tutor analytics: {str(e)}")
        return custom_response(
            success=False,
            data={'error': str(e)},
            status_code=500
        )