from django.shortcuts import render, get_object_or_404
from django.db.models import Count, Q
from exams.models import (
    JointExamProject,
    ExamSchoolResult,
)

def project_school_rank_router(request, project_id):
    project = get_object_or_404(JointExamProject, id=project_id)

    category = request.GET.get("category", "all")
    scope = request.GET.get("scope", "overall")
    layout = request.GET.get("layout", "normal")
    fmt = request.GET.get("format", "html")

    region_id = request.GET.get("region_id")
    district_id = request.GET.get("district_id")

    qs = (
        ExamSchoolResult.objects
        .filter(project=project, sch_gpa__isnull=False)
        .select_related("school", "school__district", "school__district__region")
    )

    # CATEGORY FILTER
    if category == "above_40":
        qs = qs.filter(with_results__gte=40)
    elif category == "below_40":
        qs = qs.filter(with_results__lt=40)

    # SCOPE FILTER
    if scope == "region" and region_id:
        qs = qs.filter(school__district__region_id=region_id)

    if scope == "district" and district_id:
        qs = qs.filter(school__district_id=district_id)

    # RANKING (LOWER GPA IS BETTER)
    qs = qs.order_by("sch_gpa")

    context = {
        "project": project,
        "schools": qs,
        "scope": scope,
        "layout": layout,
    }

    # FORMAT ROUTING
    if fmt == "excel":
        return render(request,
            "exams/rank/export_school_rank_excel.html", context)

    if fmt == "pdf":
        return render(request,
            "exams/rank/school_rank_pdf.html", context)

    # HTML
    template = (
        "exams/rank/school_rank_extended.html"
        if layout == "extended"
        else "exams/rank/school_rank_normal.html"
    )

    return render(request, template, context)



def project_school_rank_router(request, project_id):
    project = get_object_or_404(JointExamProject, id=project_id)

    category = request.GET.get("category", "all")
    scope = request.GET.get("scope", "overall")
    layout = request.GET.get("layout", "normal")
    fmt = request.GET.get("format", "html")

    region_id = request.GET.get("region_id")
    district_id = request.GET.get("district_id")

    schools = build_school_rank_data(
        project=project,              # ✅ PASS PROJECT
        category=category,
        scope=scope,
        region_id=region_id,
        district_id=district_id
    )

    context = {
        "project": project,
        "schools": schools,
        "layout": layout,
        "scope": scope,
    }

    return render(request, "exams/rank/school_rank_normal.html", context)


def build_school_rank_data(project, category, scope, region_id, district_id):
    qs = (
        ExamSchoolResult.objects
        .filter(project=project, sch_gpa__isnull=False)
        .select_related("school", "school__district", "school__district__region")
    )

    if category == "above_40":
        qs = qs.filter(with_results__gte=40)
    elif category == "below_40":
        qs = qs.filter(with_results__lt=40)

    if scope == "region" and region_id:
        qs = qs.filter(school__district__region_id=region_id)

    if scope == "district" and district_id:
        qs = qs.filter(school__district_id=district_id)

    return qs.order_by("sch_gpa")