"""
Instructor Management Routes
Handles instructor-related API endpoints including profiles, course assignments, and analytics
"""
from flask import Blueprint, request, jsonify, make_response
from utils import require_api_key, add_keep_alive, format_api_response, create_error_response
from auth.jwt_manager import require_auth, require_user_access
from database import get_cached_connection, optimized_execute
import logging
import json
from datetime import datetime, timedelta

logger = logging.getLogger(__name__)

instructor_bp = Blueprint('instructor', __name__)

def validate_instructor_data(data, required_fields):
    """Validate instructor data and return error if invalid"""
    missing_fields = [field for field in required_fields if not data.get(field)]
    if missing_fields:
        return f"Missing required fields: {', '.join(missing_fields)}"
    return None

@instructor_bp.route('/api/instructors', methods=['POST'])
@require_api_key
@require_auth
def create_instructor_profile():
    """Create or update instructor profile"""
    try:
        data = request.get_json()
        if not data:
            return create_error_response("Request data is required", 400)
        
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        institute_id = data.get('institute_id')
        if not institute_id:
            return create_error_response("Institute ID is required", 400)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if user is a member of the institute
            cursor.execute("""
                SELECT role FROM institute_members 
                WHERE institute_id = %s AND user_id = %s AND status = 'active'
            """, (institute_id, user_id))
            
            member = cursor.fetchone()
            if not member:
                return create_error_response("You are not a member of this institute", 403)
            
            # Get user details
            cursor.execute("SELECT fullname, email FROM user WHERE id = %s", (user_id,))
            user = cursor.fetchone()
            if not user:
                return create_error_response("User not found", 404)
            
            # Check if instructor profile already exists
            cursor.execute("""
                SELECT id FROM instructors 
                WHERE user_id = %s AND institute_id = %s
            """, (user_id, institute_id))
            
            existing_instructor = cursor.fetchone()
            
            if existing_instructor:
                # Update existing profile
                instructor_id = existing_instructor['id']
                
                update_fields = []
                update_values = []
                
                allowed_fields = [
                    'bio', 'specializations', 'qualifications', 'experience_years',
                    'hourly_rate', 'availability', 'linkedin_url', 'github_url',
                    'portfolio_url', 'profile_image_url'
                ]
                
                for field in allowed_fields:
                    if field in data:
                        if field in ['specializations', 'qualifications', 'availability']:
                            # Handle JSON fields
                            update_fields.append(f"{field} = %s")
                            update_values.append(json.dumps(data[field]) if data[field] else None)
                        else:
                            update_fields.append(f"{field} = %s")
                            update_values.append(data[field])
                
                if update_fields:
                    update_fields.append("updated_at = NOW()")
                    update_values.append(instructor_id)
                    
                    update_query = f"""
                        UPDATE instructors 
                        SET {', '.join(update_fields)}
                        WHERE id = %s
                    """
                    
                    cursor.execute(update_query, update_values)
                    message = "Instructor profile updated successfully"
                else:
                    message = "No changes to update"
            else:
                # Create new instructor profile
                insert_query = """
                    INSERT INTO instructors (
                        institute_id, user_id, name, bio, specializations, qualifications,
                        experience_years, hourly_rate, availability, linkedin_url,
                        github_url, portfolio_url, profile_image_url, status
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'active'
                    )
                """
                
                instructor_data = [
                    institute_id,
                    user_id,
                    user['fullname'],
                    data.get('bio', ''),
                    json.dumps(data.get('specializations', [])) if data.get('specializations') else None,
                    json.dumps(data.get('qualifications', [])) if data.get('qualifications') else None,
                    data.get('experience_years', 0),
                    data.get('hourly_rate'),
                    json.dumps(data.get('availability', {})) if data.get('availability') else None,
                    data.get('linkedin_url'),
                    data.get('github_url'),
                    data.get('portfolio_url'),
                    data.get('profile_image_url')
                ]
                
                cursor.execute(insert_query, instructor_data)
                instructor_id = cursor.lastrowid
                message = "Instructor profile created successfully"
            
            conn.commit()
            
            # Fetch the instructor profile
            cursor.execute("""
                SELECT 
                    ins.*,
                    u.fullname as user_name,
                    u.email as user_email,
                    i.name as institute_name,
                    (SELECT COUNT(*) FROM courses c WHERE c.primary_instructor_id = ins.id) as primary_courses,
                    (SELECT COUNT(*) FROM course_instructors ci WHERE ci.instructor_id = ins.id) as total_courses
                FROM instructors ins
                JOIN user u ON ins.user_id = u.id
                JOIN institutes i ON ins.institute_id = i.id
                WHERE ins.id = %s
            """, (instructor_id,))
            
            instructor = cursor.fetchone()
            
            # Parse JSON fields for response
            if instructor['specializations']:
                instructor['specializations'] = json.loads(instructor['specializations'])
            if instructor['qualifications']:
                instructor['qualifications'] = json.loads(instructor['qualifications'])
            if instructor['availability']:
                instructor['availability'] = json.loads(instructor['availability'])
            
            response_data = {
                "success": True,
                "message": message,
                "instructor": instructor
            }
            
            return jsonify(format_api_response(response_data, include_legacy_fields=response_data)), 201 if not existing_instructor else 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error creating/updating instructor profile: {str(e)}")
        return create_error_response(f"Failed to create/update instructor profile: {str(e)}", 500)

@instructor_bp.route('/api/instructors/<int:instructor_id>', methods=['GET'])
@require_api_key
def get_instructor_profile(instructor_id):
    """Get detailed instructor profile"""
    try:
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Get instructor details
            cursor.execute("""
                SELECT 
                    ins.*,
                    u.fullname as user_name,
                    u.email as user_email,
                    u.profile_img_url as user_profile_image,
                    i.name as institute_name,
                    i.slug as institute_slug,
                    i.logo_url as institute_logo,
                    (SELECT COUNT(*) FROM courses c WHERE c.primary_instructor_id = ins.id AND c.status = 'published') as primary_courses_count,
                    (SELECT COUNT(*) FROM course_instructors ci JOIN courses c ON ci.course_id = c.id WHERE ci.instructor_id = ins.id AND c.status = 'published') as total_courses_count,
                    (SELECT COUNT(DISTINCT e.user_id) FROM enrollments e JOIN courses c ON e.course_id = c.id WHERE c.primary_instructor_id = ins.id) as total_students_taught,
                    (SELECT AVG(c.rating) FROM courses c WHERE c.primary_instructor_id = ins.id AND c.rating > 0) as average_course_rating
                FROM instructors ins
                JOIN user u ON ins.user_id = u.id
                JOIN institutes i ON ins.institute_id = i.id
                WHERE ins.id = %s AND ins.status = 'active'
            """, (instructor_id,))
            
            instructor = cursor.fetchone()
            if not instructor:
                return create_error_response("Instructor not found", 404)
            
            # Parse JSON fields
            if instructor['specializations']:
                instructor['specializations'] = json.loads(instructor['specializations'])
            if instructor['qualifications']:
                instructor['qualifications'] = json.loads(instructor['qualifications'])
            if instructor['availability']:
                instructor['availability'] = json.loads(instructor['availability'])
            
            # Get instructor's courses
            cursor.execute("""
                SELECT 
                    c.*,
                    ci.role as instructor_role,
                    (SELECT COUNT(*) FROM enrollments e WHERE e.course_id = c.id) as enrollment_count
                FROM courses c
                LEFT JOIN course_instructors ci ON c.id = ci.course_id AND ci.instructor_id = %s
                WHERE (c.primary_instructor_id = %s OR ci.instructor_id = %s) 
                AND c.status = 'published'
                ORDER BY c.created_at DESC
            """, (instructor_id, instructor_id, instructor_id))
            
            courses = cursor.fetchall()
            
            # Get recent student feedback/reviews
            cursor.execute("""
                SELECT 
                    cr.*,
                    u.fullname as student_name,
                    c.title as course_title
                FROM course_reviews cr
                JOIN user u ON cr.user_id = u.id
                JOIN courses c ON cr.course_id = c.id
                WHERE c.primary_instructor_id = %s
                ORDER BY cr.created_at DESC
                LIMIT 10
            """, (instructor_id,))
            
            recent_reviews = cursor.fetchall()
            
            # Get instructor statistics
            cursor.execute("""
                SELECT 
                    COUNT(DISTINCT c.id) as total_courses,
                    COUNT(DISTINCT e.user_id) as total_students,
                    AVG(c.rating) as average_rating,
                    SUM(e.price_paid) as total_revenue
                FROM courses c
                LEFT JOIN enrollments e ON c.id = e.course_id
                WHERE c.primary_instructor_id = %s AND c.status = 'published'
            """, (instructor_id,))
            
            stats = cursor.fetchone()
            
            response_data = {
                "success": True,
                "instructor": instructor,
                "courses": courses,
                "recent_reviews": recent_reviews,
                "statistics": stats
            }
            
            return jsonify(format_api_response(response_data, include_legacy_fields=response_data)), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching instructor profile: {str(e)}")
        return create_error_response(f"Failed to fetch instructor profile: {str(e)}", 500)

@instructor_bp.route('/api/institutes/<int:institute_id>/instructors', methods=['GET'])
@require_api_key
def get_institute_instructors(institute_id):
    """Get all instructors for a specific institute"""
    try:
        # Get query parameters
        page = int(request.args.get('page', 1))
        limit = min(50, int(request.args.get('limit', 20)))
        offset = (page - 1) * limit
        
        search = request.args.get('search')
        specialization = request.args.get('specialization')
        min_experience = request.args.get('min_experience')
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Build query conditions
            conditions = ["ins.institute_id = %s", "ins.status = 'active'"]
            params = [institute_id]
            
            if search:
                conditions.append("(ins.name LIKE %s OR ins.bio LIKE %s)")
                search_pattern = f"%{search}%"
                params.extend([search_pattern, search_pattern])
            
            if specialization:
                conditions.append("JSON_CONTAINS(ins.specializations, %s)")
                params.append(f'"{specialization}"')
            
            if min_experience:
                conditions.append("ins.experience_years >= %s")
                params.append(int(min_experience))
            
            where_clause = "WHERE " + " AND ".join(conditions)
            
            # Get total count
            count_query = f"""
                SELECT COUNT(*) as total 
                FROM instructors ins 
                {where_clause}
            """
            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']
            
            # Get instructors
            query = f"""
                SELECT 
                    ins.*,
                    u.fullname as user_name,
                    u.email as user_email,
                    u.profile_img_url as user_profile_image,
                    (SELECT COUNT(*) FROM courses c WHERE c.primary_instructor_id = ins.id AND c.status = 'published') as course_count,
                    (SELECT COUNT(DISTINCT e.user_id) FROM enrollments e JOIN courses c ON e.course_id = c.id WHERE c.primary_instructor_id = ins.id) as student_count,
                    (SELECT AVG(c.rating) FROM courses c WHERE c.primary_instructor_id = ins.id AND c.rating > 0) as average_rating
                FROM instructors ins
                JOIN user u ON ins.user_id = u.id
                {where_clause}
                ORDER BY ins.is_featured DESC, ins.total_students DESC, ins.created_at DESC
                LIMIT %s OFFSET %s
            """
            
            params.extend([limit, offset])
            cursor.execute(query, params)
            instructors = cursor.fetchall()
            
            # Parse JSON fields for each instructor
            for instructor in instructors:
                if instructor['specializations']:
                    instructor['specializations'] = json.loads(instructor['specializations'])
                if instructor['qualifications']:
                    instructor['qualifications'] = json.loads(instructor['qualifications'])
                if instructor['availability']:
                    instructor['availability'] = json.loads(instructor['availability'])
            
            # Calculate pagination info
            total_pages = (total + limit - 1) // limit
            
            response_data = {
                "success": True,
                "instructors": instructors,
                "pagination": {
                    "current_page": page,
                    "total_pages": total_pages,
                    "total_items": total,
                    "items_per_page": limit,
                    "has_next": page < total_pages,
                    "has_prev": page > 1
                }
            }
            
            return jsonify(format_api_response(response_data, include_legacy_fields=response_data)), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching institute instructors: {str(e)}")
        return create_error_response(f"Failed to fetch instructors: {str(e)}", 500)

@instructor_bp.route('/api/instructors/<int:instructor_id>/courses', methods=['POST'])
@require_api_key
@require_auth
def assign_instructor_to_course():
    """Assign instructor to a course with specific role"""
    try:
        instructor_id = int(request.view_args['instructor_id'])
        data = request.get_json()
        if not data:
            return create_error_response("Request data is required", 400)
        
        course_id = data.get('course_id')
        role = data.get('role', 'secondary')
        responsibilities = data.get('responsibilities', [])
        compensation_type = data.get('compensation_type', 'none')
        compensation_amount = data.get('compensation_amount', 0.00)
        
        if not course_id or role not in ['primary', 'secondary', 'guest', 'assistant']:
            return create_error_response("Valid course_id and role are required", 400)
        
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if user has permission to assign instructors
            cursor.execute("""
                SELECT 
                    c.institute_id,
                    im.role as user_role
                FROM courses c
                JOIN institute_members im ON c.institute_id = im.institute_id
                WHERE c.id = %s AND im.user_id = %s AND im.status = 'active'
            """, (course_id, user_id))
            
            permission = cursor.fetchone()
            if not permission or permission['user_role'] not in ['owner', 'admin']:
                return create_error_response("Insufficient permissions", 403)
            
            # Check if instructor belongs to the same institute
            cursor.execute("""
                SELECT institute_id FROM instructors 
                WHERE id = %s AND status = 'active'
            """, (instructor_id,))
            
            instructor = cursor.fetchone()
            if not instructor or instructor['institute_id'] != permission['institute_id']:
                return create_error_response("Instructor not found or not in same institute", 404)
            
            # Check if assignment already exists
            cursor.execute("""
                SELECT id FROM course_instructors 
                WHERE course_id = %s AND instructor_id = %s AND role = %s
            """, (course_id, instructor_id, role))
            
            if cursor.fetchone():
                return create_error_response("Instructor already assigned to this course with this role", 400)
            
            # If role is primary, check if course already has a primary instructor
            if role == 'primary':
                cursor.execute("""
                    SELECT primary_instructor_id FROM courses WHERE id = %s
                """, (course_id,))
                course = cursor.fetchone()
                if course['primary_instructor_id'] and course['primary_instructor_id'] != instructor_id:
                    return create_error_response("Course already has a primary instructor", 400)
                
                # Update course primary instructor
                cursor.execute("""
                    UPDATE courses SET primary_instructor_id = %s WHERE id = %s
                """, (instructor_id, course_id))
            
            # Insert course instructor assignment
            cursor.execute("""
                INSERT INTO course_instructors (
                    course_id, instructor_id, role, responsibilities,
                    compensation_type, compensation_amount, status, assigned_at
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, 'active', NOW()
                )
            """, (
                course_id, instructor_id, role, 
                json.dumps(responsibilities) if responsibilities else None,
                compensation_type, compensation_amount
            ))
            
            conn.commit()
            
            response_data = {
                "success": True,
                "message": f"Instructor assigned as {role} successfully"
            }
            
            return jsonify(format_api_response(response_data, include_legacy_fields=response_data)), 201
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error assigning instructor to course: {str(e)}")
        return create_error_response(f"Failed to assign instructor: {str(e)}", 500)

@instructor_bp.route('/api/instructors/<int:instructor_id>/analytics', methods=['GET'])
@require_api_key
@require_auth
def get_instructor_analytics(instructor_id):
    """Get instructor performance analytics"""
    try:
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            # Check if user has permission to view analytics
            cursor.execute("""
                SELECT 
                    ins.user_id,
                    im.role as user_role
                FROM instructors ins
                JOIN institute_members im ON ins.institute_id = im.institute_id
                WHERE ins.id = %s AND im.user_id = %s AND im.status = 'active'
            """, (instructor_id, user_id))
            
            permission = cursor.fetchone()
            if not permission or (permission['user_id'] != user_id and permission['user_role'] not in ['owner', 'admin']):
                return create_error_response("Access denied", 403)
            
            # Get course performance metrics
            cursor.execute("""
                SELECT 
                    c.id,
                    c.title,
                    c.rating,
                    c.reviews_count,
                    c.enrollments_count,
                    COUNT(e.id) as actual_enrollments,
                    SUM(e.price_paid) as revenue,
                    AVG(cr.rating) as average_rating,
                    COUNT(cr.id) as review_count
                FROM courses c
                LEFT JOIN enrollments e ON c.id = e.course_id
                LEFT JOIN course_reviews cr ON c.id = cr.course_id
                WHERE c.primary_instructor_id = %s AND c.status = 'published'
                GROUP BY c.id
                ORDER BY c.enrollments_count DESC
            """, (instructor_id,))
            
            course_performance = cursor.fetchall()
            
            # Get overall statistics
            cursor.execute("""
                SELECT 
                    COUNT(DISTINCT c.id) as total_courses,
                    COUNT(DISTINCT e.user_id) as total_students,
                    AVG(c.rating) as average_rating,
                    SUM(e.price_paid) as total_revenue,
                    COUNT(DISTINCT cr.id) as total_reviews
                FROM courses c
                LEFT JOIN enrollments e ON c.id = e.course_id
                LEFT JOIN course_reviews cr ON c.id = cr.course_id
                WHERE c.primary_instructor_id = %s AND c.status = 'published'
            """, (instructor_id,))
            
            overall_stats = cursor.fetchone()
            
            # Get monthly enrollment trends (last 12 months)
            cursor.execute("""
                SELECT 
                    DATE_FORMAT(e.enrolled_at, '%Y-%m') as month,
                    COUNT(e.id) as enrollments,
                    SUM(e.price_paid) as revenue
                FROM enrollments e
                JOIN courses c ON e.course_id = c.id
                WHERE c.primary_instructor_id = %s 
                AND e.enrolled_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
                GROUP BY DATE_FORMAT(e.enrolled_at, '%Y-%m')
                ORDER BY month ASC
            """, (instructor_id,))
            
            monthly_trends = cursor.fetchall()
            
            # Get student feedback summary
            cursor.execute("""
                SELECT 
                    cr.rating,
                    COUNT(*) as count,
                    cr.rating * COUNT(*) as weighted_total
                FROM course_reviews cr
                JOIN courses c ON cr.course_id = c.id
                WHERE c.primary_instructor_id = %s
                GROUP BY cr.rating
                ORDER BY cr.rating DESC
            """, (instructor_id,))
            
            rating_distribution = cursor.fetchall()
            
            response_data = {
                "success": True,
                "analytics": {
                    "overall_stats": overall_stats,
                    "course_performance": course_performance,
                    "monthly_trends": monthly_trends,
                    "rating_distribution": rating_distribution
                }
            }
            
            return jsonify(format_api_response(response_data, include_legacy_fields=response_data)), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching instructor analytics: {str(e)}")
        return create_error_response(f"Failed to fetch analytics: {str(e)}", 500)

@instructor_bp.route('/api/my-instructor-profile', methods=['GET'])
@require_api_key
@require_auth
def get_my_instructor_profile():
    """Get current user's instructor profile"""
    try:
        user_id = request.user_info.get('user_id')
        if not user_id:
            return create_error_response("User authentication required", 401)
        
        conn = get_cached_connection()
        cursor = conn.cursor(dictionary=True)
        
        try:
            cursor.execute("""
                SELECT 
                    ins.*,
                    i.name as institute_name,
                    i.slug as institute_slug,
                    (SELECT COUNT(*) FROM courses c WHERE c.primary_instructor_id = ins.id AND c.status = 'published') as course_count,
                    (SELECT COUNT(DISTINCT e.user_id) FROM enrollments e JOIN courses c ON e.course_id = c.id WHERE c.primary_instructor_id = ins.id) as student_count
                FROM instructors ins
                JOIN institutes i ON ins.institute_id = i.id
                WHERE ins.user_id = %s AND ins.status = 'active'
            """, (user_id,))
            
            instructor_profiles = cursor.fetchall()
            
            # Parse JSON fields
            for profile in instructor_profiles:
                if profile['specializations']:
                    profile['specializations'] = json.loads(profile['specializations'])
                if profile['qualifications']:
                    profile['qualifications'] = json.loads(profile['qualifications'])
                if profile['availability']:
                    profile['availability'] = json.loads(profile['availability'])
            
            response_data = {
                "success": True,
                "instructor_profiles": instructor_profiles
            }
            
            return jsonify(format_api_response(response_data, include_legacy_fields=response_data)), 200
            
        finally:
            cursor.close()
            conn.close()
            
    except Exception as e:
        logger.error(f"Error fetching instructor profile: {str(e)}")
        return create_error_response(f"Failed to fetch instructor profile: {str(e)}", 500)
