from django.db import transaction
from .models import ExamScore, StudentResult, SchoolResult


def convert_score_to_points(score, level):
    rule = GradingRule.objects.filter(
        academic_level=level,
        min_score__lte=score,
        max_score__gte=score
    ).first()

    if rule:
        return rule.points
    return 0


@transaction.atomic
def process_project_results(project_id):

    # 1. Get all scores
    scores = ExamScore.objects.filter(project_id=project_id)

    # 2. Group by student
    students = scores.values_list("student_id", flat=True).distinct()

    for student_id in students:
        student_scores = scores.filter(student_id=student_id)

        total_points = 0
        subject_count = 0

        for s in student_scores:
            # convert score -> grade -> points
            points = convert_score_to_points(s.score)
            total_points += points
            subject_count += 1

        gpa = total_points / subject_count if subject_count else 0
        division = calculate_division(gpa)

        StudentResult.objects.update_or_create(
            project_id=project_id,
            student_id=student_id,
            defaults={
                "total_points": total_points,
                "gpa": gpa,
                "division": division
            }
        )

    # 3. Process school ranking
    process_school_results(project_id)


def process_student(project, student_id):

    level = project.academic_level
    scores = ExamScore.objects.filter(
        project=project,
        student_id=student_id
    )

    subject_points = []

    for s in scores:
        numeric_score = float(s.score)
        points = convert_score_to_points(numeric_score, level)
        subject_points.append(points)

    # Apply BEST SUBJECT RULE
    subject_points.sort()
    best_points = subject_points[:level.best_subjects_count]

    total_points = sum(best_points)

    division = None
    if level.use_division:
        div_rule = DivisionRule.objects.filter(
            academic_level=level,
            min_points__lte=total_points,
            max_points__gte=total_points
        ).first()
        if div_rule:
            division = div_rule.division

    gpa = total_points / level.best_subjects_count if level.use_gpa else 0

    return total_points, gpa, division


def calculate_subject_percentage(student, project, subject):

    papers = ExamSubjectPaper.objects.filter(subject=subject)

    total_scored = 0
    total_max = 0

    for paper in papers:
        score_obj = ExamScore.objects.filter(
            project=project,
            student=student,
            paper=paper
        ).first()

        if not score_obj:
            continue

        total_scored += score_obj.score
        total_max += paper.max_marks

    if total_max == 0:
        return 0

    percentage = (total_scored / total_max) * 100
    return percentage


def process_alevel_student(project, student):

    subjects = ExamSubject.objects.filter(
        academic_level=project.academic_level
    )

    principal_points = []
    subsidiary_points = []

    for subject in subjects:

        percent = calculate_subject_percentage(student, project, subject)

        points = convert_score_to_points(percent, project.academic_level)

        if subject.is_principal:   # add this field
            principal_points.append(points)
        else:
            subsidiary_points.append(points)

    principal_points.sort()

    best_three = principal_points[:3]

    total_points = sum(best_three) + sum(subsidiary_points)

    division = get_division(total_points, project.academic_level)

    return total_points, division



@transaction.atomic
def bulk_upload_marks(project, dataframe):

    # 1️⃣ Load maps into memory (FAST)
    schools_map = {
        s.school_code: s.id
        for s in School.objects.all().only("id", "school_code")
    }

    students_map = {
        (ps.school_id, ps.student_code): ps.id
        for ps in ProjectStudent.objects.filter(project=project)
        .only("id", "school_id", "student_code")
    }

    papers_map = {
        p.paper_code: p.id
        for p in ExamSubjectPaper.objects.all().only("id", "paper_code")
    }

    update_list = []

    for _, row in dataframe.iterrows():

        school_id = schools_map.get(row["school_code"])
        if not school_id:
            continue  # invalid school

        student_id = students_map.get((school_id, row["student_code"]))
        if not student_id:
            continue  # student not registered

        paper_id = papers_map.get(row["paper_code"])
        if not paper_id:
            continue  # invalid paper

        update_list.append(
            ExamScore(
                project=project,
                student_id=student_id,
                paper_id=paper_id,
                score=row["score"]
            )
        )

    # UPSERT (FASTEST METHOD)
    ExamScore.objects.bulk_create(
        update_list,
        update_conflicts=True,
        unique_fields=["student", "paper"],
        update_fields=["score", "updated_at"]
    )



def process_olevel_student(project, student):

    level = project.academic_level

    subjects = ExamSubject.objects.filter(
        academic_level=level
    )

    subject_points = []

    for subject in subjects:
        percent = calculate_subject_percentage(student, project, subject)

        rule = GradingRule.objects.filter(
            academic_level=level,
            min_score__lte=percent,
            max_score__gte=percent
        ).first()

        if rule:
            subject_points.append(rule.points)

    if len(subject_points) < 7:
        return None, "FAIL"

    subject_points.sort()
    best_seven = subject_points[:7]

    total_points = sum(best_seven)

    division_rule = DivisionRule.objects.filter(
        academic_level=level,
        min_points__lte=total_points,
        max_points__gte=total_points
    ).first()

    division = division_rule.division if division_rule else "FAIL"

    return total_points, division


from django.db import transaction

@transaction.atomic
def bulk_register_students(project, dataframe):

    # 1️⃣ Preload school map (FAST)
    schools_map = {
        s.school_code: s.id
        for s in School.objects.all().only("id", "school_code")
    }

    # 2️⃣ Preload combination map (FAST)
    combos_map = {
        c.code: c.id
        for c in ExamCombination.objects.filter(
            academic_level=project.academic_level
        ).only("id", "code")
    }

    insert_list = []

    for _, row in dataframe.iterrows():

        school_id = schools_map.get(row["school_code"])
        if not school_id:
            continue  # invalid school

        combo_id = combos_map.get(row["combination"])
        if not combo_id:
            continue  # invalid combination

        insert_list.append(
            ProjectStudent(
                project=project,
                school_id=school_id,
                student_number=row["student_number"],
                full_name=row["full_name"],
                sex=row["sex"],
                combination_id=combo_id
            )
        )

    ProjectStudent.objects.bulk_create(
        insert_list,
        ignore_conflicts=True,
        batch_size=1000
    )