from sqlalchemy import Select
from src.models import StudentTable, StudentAssesmentResultTable
from src import db


def get_overall_weak_students(school_id, grade, section, limit=10):
    """Get top 10 weakest students overall based on average performance across all concepts"""
    try:
        # Get all students in the grade/section
        stmt_all_students = Select(StudentTable).where(
            StudentTable.school_id == school_id,
            StudentTable.grade == grade,
            StudentTable.section == section
        )
        all_students_in_section = db.session.execute(stmt_all_students).scalars().all()
        
        stmt = Select(StudentAssesmentResultTable).where(
            StudentAssesmentResultTable.school_id == school_id,
            StudentAssesmentResultTable.grade == grade,
            StudentAssesmentResultTable.section == section
        )
        
        results = db.session.execute(stmt).scalars().all()
        
        if not results:
            return []
        
        # Get students who have attempted assessments
        students_with_attempts = set()
        student_performance = {}
        
        for result in results:
            student_id = result.student_id
            students_with_attempts.add(student_id)
            
            if student_id not in student_performance:
                student_performance[student_id] = {
                    'total_correct': 0,
                    'total_questions': 0
                }
            
            # Sum up concept_detail data
            for concept in result.concept_detail:
                student_performance[student_id]['total_correct'] += concept.get('correct', 0)
                student_performance[student_id]['total_questions'] += concept.get('total', 0)
        
        # Calculate percentages for students who attempted
        weak_students = []
        for student_id, perf in student_performance.items():
            if perf['total_questions'] > 0:
                percentage = (perf['total_correct'] * 100.0) / perf['total_questions']
                weak_students.append({
                    'student_id': student_id,
                    'total_correct': perf['total_correct'],
                    'total_questions': perf['total_questions'],
                    'percentage': percentage
                })
        
        # Sort by percentage (ascending - weakest first) and limit
        weak_students.sort(key=lambda x: x['percentage'])
        weak_students = weak_students[:limit]
        
        # Fetch student details
        final_results = []
        for student_perf in weak_students:
            stmt_student = Select(StudentTable).where(
                StudentTable.id == student_perf['student_id']
            )
            student = db.session.execute(stmt_student).scalar_one_or_none()
            
            if student:
                final_results.append({
                    "student_id": student_perf['student_id'],
                    "student_name": f"{student.firstname} {student.lastname or ''}".strip(),
                    "accuracy_percent": round(student_perf['percentage'], 2),
                    "correct_answers": student_perf['total_correct'],
                    "total_questions": student_perf['total_questions']
                })
        
        return final_results

    except Exception as e:
        return []

def get_concept_weak_students(school_id, grade, section, concept_name, limit=10):
    """Get top 10 weakest students for a specific concept (includes all subconcepts)"""
    try:
        stmt = Select(StudentAssesmentResultTable).where(
            StudentAssesmentResultTable.school_id == school_id,
            StudentAssesmentResultTable.grade == grade,
            StudentAssesmentResultTable.section == section
        )
        
        results = db.session.execute(stmt).scalars().all()
        
        if not results:
            return []
        
        # Calculate performance for each student in this concept
        student_performance = {}
        student_assessment_counts = {}
        student_subconcepts = {}  # Track which subconcepts each student attempted
        
        for result in results:
            student_id = result.student_id
            
            # First check concept_detail for direct concept data
            for concept in result.concept_detail:
                if concept.get('conceptname') == concept_name:
                    if student_id not in student_performance:
                        student_performance[student_id] = {
                            'total_correct': 0,
                            'total_questions': 0
                        }
                        student_assessment_counts[student_id] = 0
                        student_subconcepts[student_id] = set()
                    
                    student_performance[student_id]['total_correct'] += concept.get('correct', 0)
                    student_performance[student_id]['total_questions'] += concept.get('total', 0)
                    student_assessment_counts[student_id] += 1
            
            # Also check subconcept_detail for subconcept-level data
            if result.subconcept_detail:
                for subconcept in result.subconcept_detail:
                    if subconcept.get('concept') == concept_name:
                        if student_id not in student_performance:
                            student_performance[student_id] = {
                                'total_correct': 0,
                                'total_questions': 0
                            }
                            student_assessment_counts[student_id] = 0
                            student_subconcepts[student_id] = set()
                        
                        student_performance[student_id]['total_correct'] += subconcept.get('correct', 0)
                        student_performance[student_id]['total_questions'] += subconcept.get('total', 0)
                        
                        # Track subconcept name
                        subconcept_name = subconcept.get('subconcept')
                        if subconcept_name:
                            student_subconcepts[student_id].add(subconcept_name)
        
        # Calculate percentages and create list
        weak_students = []
        for student_id, perf in student_performance.items():
            if perf['total_questions'] > 0:
                percentage = (perf['total_correct'] * 100.0) / perf['total_questions']
                weak_students.append({
                    'student_id': student_id,
                    'total_correct': perf['total_correct'],
                    'total_questions': perf['total_questions'],
                    'percentage': percentage,
                    'assessments': student_assessment_counts[student_id],
                    'subconcepts_attempted': list(student_subconcepts.get(student_id, []))
                })
        
        # Sort by percentage (ascending - weakest first) and limit
        weak_students.sort(key=lambda x: x['percentage'])
        weak_students = weak_students[:limit]
        
        # Fetch student details
        final_results = []
        for student_perf in weak_students:
            stmt_student = Select(StudentTable).where(
                StudentTable.id == student_perf['student_id']
            )
            student = db.session.execute(stmt_student).scalar_one_or_none()
            
            if student:
                result_data = {
                    "student_id": student_perf['student_id'],
                    "student_name": f"{student.firstname} {student.lastname or ''}".strip(),
                    "accuracy_percent": round(student_perf['percentage'], 2),
                    "correct_answers": student_perf['total_correct'],
                    "total_questions": student_perf['total_questions'],
                    "teacher_assessments": student_perf['assessments']
                }
                
                # Add subconcepts_attempted if any
                if student_perf['subconcepts_attempted']:
                    result_data["subconcepts_attempted"] = student_perf['subconcepts_attempted']
                
                final_results.append(result_data)
        
        return final_results

    except Exception as e:
        print(f"Error in get_concept_weak_students: {e}")
        return []
    
def get_subconcept_weak_students(school_id, grade, section, concept_name, subconcept_name, limit=10):
    """Get top 10 weakest students for a specific subconcept"""
    try:
        stmt = Select(StudentAssesmentResultTable).where(
            StudentAssesmentResultTable.school_id == school_id,
            StudentAssesmentResultTable.grade == grade,
            StudentAssesmentResultTable.section == section,
            StudentAssesmentResultTable.subconcept_detail.isnot(None)
        )
        
        results = db.session.execute(stmt).scalars().all()
        
        if not results:
            return []
        
        # Calculate performance for each student in this subconcept
        student_performance = {}
        student_assessment_counts = {}
        
        for result in results:
            student_id = result.student_id
            
            # Find the subconcept in subconcept_detail
            if result.subconcept_detail:
                for subconcept in result.subconcept_detail:
                    if (subconcept.get('concept') == concept_name and 
                        subconcept.get('subconcept') == subconcept_name):
                        
                        if student_id not in student_performance:
                            student_performance[student_id] = {
                                'total_correct': 0,
                                'total_questions': 0
                            }
                            student_assessment_counts[student_id] = 0
                        
                        student_performance[student_id]['total_correct'] += subconcept.get('correct', 0)
                        student_performance[student_id]['total_questions'] += subconcept.get('total', 0)
                        student_assessment_counts[student_id] += 1
        
        # Calculate percentages and create list
        weak_students = []
        for student_id, perf in student_performance.items():
            if perf['total_questions'] > 0:
                percentage = (perf['total_correct'] * 100.0) / perf['total_questions']
                weak_students.append({
                    'student_id': student_id,
                    'total_correct': perf['total_correct'],
                    'total_questions': perf['total_questions'],
                    'percentage': percentage,
                    'assessments': student_assessment_counts[student_id]
                })
        
        # Sort by percentage (ascending - weakest first) and limit
        weak_students.sort(key=lambda x: x['percentage'])
        weak_students = weak_students[:limit]
        
        # Fetch student details
        final_results = []
        for student_perf in weak_students:
            stmt_student = Select(StudentTable).where(
                StudentTable.id == student_perf['student_id']
            )
            student = db.session.execute(stmt_student).scalar_one_or_none()
            
            if student:
                final_results.append({
                    "student_id": student_perf['student_id'],
                    "student_name": f"{student.firstname} {student.lastname or ''}".strip(),
                    "accuracy_percent": round(student_perf['percentage'], 2),
                    "correct_answers": student_perf['total_correct'],
                    "total_questions": student_perf['total_questions'],
                    "teacher_assessments": student_perf['assessments']
                })
        
        return final_results

    except Exception as e:
        print(f"Error in get_subconcept_weak_students: {e}")
        return []