from django.shortcuts import render, get_object_or_404
from .models import (
    ExamType,
    School,
    ExamCandidate,
    SchoolSubjectPerformance,
    StudentSubjectMark,
    SchoolYearSummary,
    ExamSubject
)
from django.db.models import Count, Avg, F, Q


def public_schools(request, year, exam_code):
    exam = get_object_or_404(ExamType, code__iexact=exam_code)

    # ONLY schools that exist in school_year_summary
    summaries = (
        SchoolYearSummary.objects
        .select_related("school")
        .filter(year=year, exam_type=exam)
        .order_by("school__name")
    )

    schools = summaries.values(
        "school__school_code",
        "school__name",
        "school__district__name",
        "school__region__name",
        "avg_grade",
        "avg_numeric",
        "avg_gpa",
        "candidates",
    )

    return render(request, "public/schools.html", {
        "year": year,
        "exam": exam,
        "schools": schools,
    })




from django.shortcuts import render, get_object_or_404
from exams.models import (
    School,
    ExamType,
    SchoolYearSummary,
    SchoolSubjectPerformance,
)

from django.shortcuts import render, get_object_or_404
from django.db.models import Count, Avg, Sum
from .models import (
    ExamType, School, ExamCandidate,
    SchoolSubjectPerformance
)














from django.shortcuts import render, get_object_or_404
from django.db.models import Count, Avg, Q

from exams.models import (
    ExamType,
    School,
    ExamCandidate,
    StudentSubjectMark,
    SchoolSubjectPerformance,
)

def public_school_results(request, year, exam_code, school_code):

    # --------------------------------------------------
    # BASIC OBJECTS
    # --------------------------------------------------
    exam = get_object_or_404(ExamType, code__iexact=exam_code)
    school = get_object_or_404(School, school_code=school_code)

    district = school.district
    region = school.region

    candidates = ExamCandidate.objects.filter(
        school=school,
        exam_type=exam,
        year=year
    )

    # --------------------------------------------------
    # DIVISION SUMMARY BY SEX
    # --------------------------------------------------
    divisions = ["I", "II", "III", "IV", "0"]
    div_summary = []

    for sex in ["F", "M"]:
        row = {"sex": sex}
        for d in divisions:
            row[d] = candidates.filter(sex=sex, division=d).count()
        div_summary.append(row)

    total_row = {"sex": "T"}
    for d in divisions:
        total_row[d] = candidates.filter(division=d).count()
    div_summary.append(total_row)

    # --------------------------------------------------
    # DYNAMIC GRADES BY EXAM TYPE
    # --------------------------------------------------
    EXAM_GRADES = {
        "CSEE": ["A", "B", "C", "D", "F"],
        "ACSEE": ["A", "B", "C", "D", "E", "S", "F"],
        "FORM-SIX": ["A", "B", "C", "D", "E", "S", "F"],
        "FORM-ONE": ["A", "B", "C", "D", "F"],
        "FORM-THREE": ["A", "B", "C", "D", "F"],
    }

    grades = EXAM_GRADES.get(exam.code.upper(), ["A", "B", "C", "D", "F"])

    # --------------------------------------------------
    # SUBJECT MARKS
    # --------------------------------------------------
    marks = StudentSubjectMark.objects.filter(
        school=school,
        exam_type=exam,
        year=year
    )

    subject_stats = (
        marks
        .values("subject_code")
        .annotate(
            sat=Count("cand_no", filter=Q(grade__in=["A", "B", "C", "D", "E", "F"])),
            **{f"grade_{g}": Count("id", filter=Q(grade=g)) for g in grades}
        )
        .order_by("subject_code")
    )


    # --------------------------------------------------
    # SUBJECT NAME LOOKUP
    # --------------------------------------------------
    subject_lookup = {
        s.subject_code: s.subject_name_eng
        for s in ExamSubject.objects.filter(is_active=True)
    }

    # --------------------------------------------------
    # SCHOOL SUBJECT PERFORMANCE (FOR RANKING)
    # --------------------------------------------------
    perf_map = {
        p.subject_code: p
        for p in SchoolSubjectPerformance.objects.filter(
            school_code=school.school_code,
            exam_type=exam,
            year=year
        )
    }

    all_perf = SchoolSubjectPerformance.objects.filter(
        exam_type=exam,
        year=year
    )

    # --------------------------------------------------
    # HELPERS
    # --------------------------------------------------
    def grade_from_gpa(gpa):
        if gpa <= 2.5:
            return "A"
        elif gpa <= 3.5:
            return "B"
        elif gpa <= 4.0:
            return "C"
        elif gpa <= 4.5:
            return "D"
        return "F"

    def get_rank(qs, value):
        if value is None:
            return None
        better = qs.filter(subj_avg__gt=value).count()
        total = qs.count()
        return f"{better + 1}/{total}"

    # --------------------------------------------------
    # MERGE SUBJECT DATA
    # --------------------------------------------------
    subjects_data = []

    for s in subject_stats:
        subj_code = s["subject_code"]
        sat = s["sat"]

        grade_counts = {g: s.get(f"grade_{g}", 0) for g in grades}

        # Get performance object for the subject
        perf = perf_map.get(subj_code)

        # avg_score from SchoolSubjectPerformance
        avg_score = perf.subj_avg if perf else 0
        avg_grade = perf.subj_grade if perf else ''
        subj_status = perf.subj_status if perf else ''
        # GPA from the same performance record
        gpa = perf.subj_gpa if perf else 0

        # GPA from SchoolSubjectPerformance
        perf = perf_map.get(subj_code)
        gpa = perf.subj_gpa if perf else 0
        pass_count = perf.total_pass if perf else 0

        district_rank = None
        region_rank = None

        if perf:
            district_qs = all_perf.filter(
                subject_code=subj_code,
                school_code__in=School.objects.filter(
                    district=district
                ).values_list("school_code", flat=True)
            )

            region_qs = all_perf.filter(
                subject_code=subj_code,
                school_code__in=School.objects.filter(
                    region=region
                ).values_list("school_code", flat=True)
            )

            district_rank = get_rank(district_qs, perf.subj_avg)
            region_rank = get_rank(region_qs, perf.subj_avg)

        subjects_data.append({
            "code": subj_code,
            "name": subject_lookup.get(subj_code, subj_code),
            "sat": sat,
            "grades": grade_counts,
            "avg_score": round(avg_score, 2),
            "avg_grade": avg_grade,
            "subj_status": subj_status,
            "pass": pass_count,
            "gpa": round(gpa, 4),
            "district_rank": district_rank,
            "region_rank": region_rank,
        })

    # --------------------------------------------------
    # OVERALL PERFORMANCE
    # --------------------------------------------------
    overall = SchoolYearSummary.objects.filter(exam_type=exam, year=year).aggregate(
        registered=Count("id"),
        avg_gpa=Avg("avg_gpa"),  # valid now
        avg_numeric=Avg("avg_numeric")  # if you still need numeric average
    )



    # --------------------------------------------------
    # EXAMINATION CENTRE RANKING
    # --------------------------------------------------

    # PASSED = exclude divisions IV and 0 (NECTA logic)
    passed_candidates = candidates.exclude(division__in=["0","-"]).count()


    centre_gpa = (
        SchoolYearSummary.objects
        .filter(
            school=school,
            year=year
        )
        .values_list("avg_gpa", flat=True)
        .first()
        or 0
    )

    centre_grade = (
        SchoolYearSummary.objects
        .filter(
            school=school,
            year=year
        )
        .values_list("avg_grade", flat=True)
        .first()
        or 0
    )

    centre_status = (
        SchoolYearSummary.objects
        .filter(
            school=school,
            year=year
        )
        .values_list("status", flat=True)
        .first()
        or 0
    )

    # CATEGORY
    category = (
        "CENTRE WITH LESS THAN 40 CANDIDATES"
        if candidates.count() < 40
        else "CENTRE WITH 40 OR MORE CANDIDATES"
    )

    # ALL SCHOOLS SAME EXAM & YEAR
    all_centres = (
        SchoolYearSummary.objects
        .filter(exam_type=exam, year=year)
        .values("school")
        .annotate(avg_gpa=Avg("avg_gpa"))
    )



    # Helper rank function
    def centre_rank(qs, value):
        better = qs.filter(avg_gpa__lt=value).count()
        total = qs.count()
        return f"{better + 1}/{total}"

    # Council (District) rank
    district_centres = all_centres.filter(
        school__district=school.district
    )

    district_rank = centre_rank(district_centres, centre_gpa)

    # Region rank
    region_centres = all_centres.filter(
        school__region=school.region
    )

    region_rank = centre_rank(region_centres, centre_gpa)

    # National rank
    national_rank = centre_rank(all_centres, centre_gpa)

    # --------------------------------------------------
    # RENDER
    # --------------------------------------------------
    return render(request, "public/school_results.html", {
        "exam": exam,
        "school": school,
        "year": year,
        "grades": grades,
        "div_summary": div_summary,
        "subjects_data": subjects_data,
        "candidates": candidates.order_by("cand_no"),
        "overall": overall,
        "passed_candidates": passed_candidates,
        "centre_gpa": round(centre_gpa, 4),
        "centre_grade": centre_grade,
        "centre_status": centre_status,
        "category": category,
        "district_rank": district_rank,
        "region_rank": region_rank,
        "national_rank": national_rank,
    })




from django.shortcuts import render
from .models import ExamType
from datetime import datetime

def public_index(request):
    current_year = datetime.now().year

    exams = ExamType.objects.filter(is_active=True).order_by("name")

    return render(request, "public/index.html", {
        "exams": exams,
        "year": current_year,
    })


from django.http import FileResponse, Http404
from django.conf import settings
import os

def download_joint_pdf(request, filename):
    # Remove trailing slashes just in case
    filename = filename.strip("/")

    file_path = os.path.join(
        settings.MEDIA_ROOT,
        "joint-results",
        filename
    )

    if not os.path.isfile(file_path):
        raise Http404("File not found")

    return FileResponse(
        open(file_path, "rb"),
        as_attachment=True,
        filename=filename
    )