import math

from django.core.cache import cache
from django.db.models import Case, Count, F, FloatField, Q, Sum, Value, When
from django.db.models.fields.json import KeyTextTransform
from django.db.models.functions import Cast, Lower
from django.utils import timezone

from .access import allowed_columns, crm_level, visible_schools
from .cache import FINDER_CACHE_TIMEOUT, finder_cache_version
from .phone_numbers import PHONE_NUMBER_PATTERNS

PAGE_SIZE = 100
FACILITY_KEYS = {
    "hasLibrary", "electricity", "internet", "hasComputer", "scienceLab",
    "playgroundStatus", "handWashingArea", "hasMidDayMeal", "schoolBus",
    "hasMedicalFacility", "easyAccessForDisabledStudents", "hasSmartDigitalBoard",
    "hasChildClub",
}
CRM_COLUMNS = [
    "crm_stage", "crm_priority", "crm_owner", "crm_last_contacted", "crm_next_followup",
]


def _cached_metadata(user, schools):
    key = f"schools:finder:metadata:{finder_cache_version()}:user:{user.pk}"
    return cache.get_or_set(key, lambda: _metadata(user, schools), FINDER_CACHE_TIMEOUT)


def _number(value):
    if value is None or value == "" or isinstance(value, bool):
        return None
    try:
        result = float(value)
    except (TypeError, ValueError, OverflowError):
        return None
    return result if math.isfinite(result) else None


def _range_value(params, name):
    return _number(params.get(name))


def _metadata(user, schools):
    columns = allowed_columns(user)
    if crm_level(user) != "none":
        columns = [*columns, *CRM_COLUMNS]

    facets = schools.order_by().values_list(
        "district_name", "municipality", "school_type"
    ).distinct()
    districts = set()
    municipalities = {}
    types = set()
    for district, municipality, school_type in facets:
        if district:
            districts.add(district)
        if municipality:
            municipalities.setdefault(district or "", set()).add(municipality)
        if school_type:
            types.add(school_type)
    owners = []
    if crm_level(user) != "none":
        owners = schools.filter(lead__owner__isnull=False).values_list(
            "lead__owner__username", flat=True
        ).distinct()
    sample = list(schools.values_list("data", flat=True)[:200])
    numeric_columns = {
        key for key in columns
        if any(isinstance(row.get(key), (int, float)) and not isinstance(row.get(key), bool)
               for row in sample)
    }
    return {
        "columns": columns,
        "districts": sorted(districts, key=str.casefold),
        "municipalities": {district: sorted(values, key=str.casefold)
                           for district, values in municipalities.items()},
        "types": sorted(types, key=str.casefold),
        "owners": sorted(owners, key=str.casefold),
        "numeric_columns": sorted(numeric_columns),
    }


def _filtered_schools(schools, params, user, allowed):
    query = (params.get("q") or "").strip().casefold()
    if query:
        schools = schools.filter(
            Q(name__icontains=query)
            | Q(data__organizationCode__icontains=query)
            | Q(data__headTeacherName__icontains=query)
        )
    for param, key in (("dist", "district_name"), ("muni", "municipality"), ("type", "school_type")):
        value = params.get(param)
        if value:
            schools = schools.filter(**{key: value})

    minimum, maximum = _range_value(params, "tmin"), _range_value(params, "tmax")
    if minimum is not None:
        schools = schools.filter(total_students__gte=math.ceil(minimum))
    if maximum is not None:
        schools = schools.filter(total_students__lte=math.floor(maximum))

    class_key = params.get("cls")
    if class_key and class_key in allowed and class_key.startswith("class_"):
        class_minimum, class_maximum = _range_value(params, "cmin"), _range_value(params, "cmax")
        if class_minimum is not None or class_maximum is not None:
            schools = schools.alias(_finder_class=Cast(
                KeyTextTransform(class_key, "data"), FloatField()
            ))
            if class_minimum is not None:
                schools = schools.filter(_finder_class__gte=class_minimum)
            if class_maximum is not None:
                schools = schools.filter(_finder_class__lte=class_maximum)

    up_minimum, up_maximum = _range_value(params, "umin"), _range_value(params, "umax")
    if up_minimum is not None or up_maximum is not None:
        if up_minimum is not None:
            schools = schools.filter(up_to_class__gte=math.ceil(up_minimum))
        if up_maximum is not None:
            schools = schools.filter(up_to_class__lte=math.floor(up_maximum))

    needed = {key for key in (params.get("need") or "").split(",")
              if key in FACILITY_KEYS and key in allowed}
    for key in needed:
        schools = schools.filter(
            Q(**{f"data__{key}": True})
            | Q(**{f"data__{key}": 1})
            | Q(**{f"data__{key}__iexact": "true"})
            | Q(**{f"data__{key}__iexact": "1"})
            | Q(**{f"data__{key}__iexact": "yes"})
        )

    if crm_level(user) != "none":
        stage, priority, owner = params.get("fstage"), params.get("fprio"), params.get("fowner")
        if stage:
            stage_filter = Q(lead__stage=stage)
            if stage == "new":
                stage_filter |= Q(lead__stage__isnull=True)
            schools = schools.filter(stage_filter)
        if priority:
            schools = schools.filter(lead__priority=priority)
        if owner == "__me__":
            schools = schools.filter(lead__owner__username=user.get_username())
        if owner == "__none__":
            schools = schools.filter(lead__owner__isnull=True)
        if owner and owner not in {"__me__", "__none__"}:
            schools = schools.filter(lead__owner__username=owner)
        if params.get("fnc") == "1":
            schools = schools.filter(lead__last_contacted_at__isnull=True)
        if params.get("fdue") == "1":
            schools = schools.filter(lead__next_followup__lte=timezone.localdate())
            schools = schools.exclude(lead__stage__in=("won", "lost", "dnc"))
    phone_type = params.get("phone")
    phone_pattern = PHONE_NUMBER_PATTERNS.get(phone_type)
    if phone_pattern and "contactNumber" in allowed:
        schools = schools.alias(
            _finder_phone=KeyTextTransform("contactNumber", "data")
        ).filter(_finder_phone__regex=phone_pattern)
    return schools


def _school_rows(schools, allowed, with_crm):
    if with_crm:
        for data, stage, priority, owner, contacted, followup in schools.values_list(
                "data", "lead__stage", "lead__priority", "lead__owner__username",
                "lead__last_contacted_at", "lead__next_followup"):
            data = {key: value for key, value in data.items() if key in allowed}
            data["crm_stage"] = stage or "new"
            data["crm_priority"] = priority or ""
            data["crm_owner"] = owner
            data["crm_last_contacted"] = (
                timezone.localtime(contacted).date().isoformat() if contacted else None
            )
            data["crm_next_followup"] = followup.isoformat() if followup else None
            yield data
    else:
        for data in schools.values_list("data", flat=True):
            yield {key: value for key, value in data.items() if key in allowed}


def finder_result(user, params, *, include_all=False):
    schools = visible_schools(user).order_by("district_id", "organization_id")
    metadata = _cached_metadata(user, schools)
    allowed = set(metadata["columns"])
    matching = _filtered_schools(schools, params, user, allowed)

    sort_key = params.get("sort") or "total_students"
    if sort_key not in allowed:
        sort_key = "total_students"
    descending = params.get("dir", "-1") != "1"

    summary = matching.aggregate(
        _total=Count("pk"),
        _student_total=Sum("total_students"),
    )
    total = summary["_total"]
    student_total = summary["_student_total"] or 0

    if params.get("ids_only") == "1":
        ordered = _ordered_schools(matching, sort_key, descending, metadata["numeric_columns"])
        return {"ids": list(ordered.values_list("organization_id", flat=True)), "total": total}

    ordered = _ordered_schools(matching, sort_key, descending, metadata["numeric_columns"])
    if include_all:
        return {
            "results": list(_school_rows(ordered, allowed, crm_level(user) != "none")),
            "total": total,
            "student_total": student_total,
            "metadata": metadata,
        }

    try:
        page = max(1, int(params.get("page", "1")))
    except (TypeError, ValueError):
        page = 1
    page_count = max(1, math.ceil(total / PAGE_SIZE))
    page = min(page, page_count)
    start = (page - 1) * PAGE_SIZE
    first_page = list(_school_rows(
        ordered[start:start + PAGE_SIZE], allowed, crm_level(user) != "none"
    ))
    return {
        "results": first_page,
        "total": total,
        "student_total": student_total,
        "page": page,
        "page_size": PAGE_SIZE,
        "page_count": page_count,
        "metadata": metadata,
    }


def _ordered_schools(schools, sort_key, descending, numeric_columns):
    core_fields = {
        "organizationName": "name",
        "organizationId": "organization_id",
        "districtName": "district_name",
        "districtId": "district_id",
        "municipalityName": "municipality",
        "organizationType": "school_type",
        "total_students": "total_students",
        "classRegisteredUpto": "up_to_class",
    }
    core_field = core_fields.get(sort_key)
    if core_field:
        if core_field in {"organization_id", "district_id", "total_students", "up_to_class"}:
            sort_order = F(core_field).desc(nulls_last=True) if descending else F(
                core_field
            ).asc(nulls_last=True)
            return schools.order_by(sort_order, "district_id", "organization_id")

        missing_conditions = [When(**{f"{core_field}__isnull": True}, then=Value(1))]
        if core_field in {"name", "district_name", "municipality", "school_type"}:
            missing_conditions.append(When(**{core_field: ""}, then=Value(1)))
        schools = schools.annotate(
            _sort_missing=Case(*missing_conditions, default=Value(0))
        )
        direction = f"-{core_field}" if descending else core_field
        return schools.order_by("_sort_missing", direction, "district_id", "organization_id")

    sort_value = KeyTextTransform(sort_key, "data")
    schools = schools.annotate(
        _sort_missing=Case(
            When(**{f"data__{sort_key}__isnull": True}, then=Value(1)),
            When(**{f"data__{sort_key}": ""}, then=Value(1)),
            default=Value(0),
        )
    )
    if sort_key in numeric_columns:
        schools = schools.annotate(_sort_value=Cast(sort_value, FloatField()))
    else:
        schools = schools.annotate(_sort_value=Lower(sort_value))
    direction = "-_sort_value" if descending else "_sort_value"
    return schools.order_by("_sort_missing", direction, "district_id", "organization_id")