import math

import pandas as pd
from django.core.management.base import BaseCommand, CommandError
from django.db import transaction

from schools.cache import invalidate_finder_cache
from schools.models import School


def clean(v):
    """Make a pandas/Excel value JSON-safe."""
    if v is None:
        return None
    if isinstance(v, float) and math.isnan(v):
        return None
    if hasattr(v, "item"):                       # numpy scalar -> python
        v = v.item()
    if isinstance(v, float) and v.is_integer():
        return int(v)
    if isinstance(v, str):
        s = v.strip()
        if s.lower() in ("true", "false"):
            return s.lower() == "true"
        return s if s else None
    if isinstance(v, pd.Timestamp):
        return v.isoformat()
    return v


def to_int(v):
    try:
        return int(v)
    except (TypeError, ValueError):
        return None


class Command(BaseCommand):
    help = "Load the Excel made by fetch_schools_slim.py (replaces all schools unless --append)"

    def add_arguments(self, parser):
        parser.add_argument("file")
        parser.add_argument("--append", action="store_true",
                            help="update/add these schools and keep the others")

    def handle(self, *args, file, append, **opts):
        xl = pd.ExcelFile(file)
        sheet = "Schools" if "Schools" in xl.sheet_names else xl.sheet_names[0]
        df = xl.parse(sheet)
        for need in ("organizationId", "organizationName"):
            if need not in df.columns:
                raise CommandError(f"Column '{need}' not found. Use the Excel made by fetch_schools_slim.py")

        objs = []
        for rec in df.to_dict("records"):
            d = {k: clean(v) for k, v in rec.items()}
            oid = to_int(d.get("organizationId"))
            if oid is None:
                continue
            objs.append(School(
                organization_id=oid, name=d.get("organizationName") or "",
                district_id=to_int(d.get("districtId")),
                district_name=d.get("districtName") or "",
                municipality=d.get("municipalityName") or "",
                school_type=d.get("organizationType") or "",
                total_students=to_int(d.get("total_students")),
                up_to_class=to_int(d.get("classRegisteredUpto")),
                data=d))
        with transaction.atomic():
            if append:
                School.all_objects.filter(organization_id__in=[o.organization_id for o in objs]).hard_delete()
            else:
                School.all_objects.all().hard_delete()
            School.objects.bulk_create(objs, batch_size=500)
        invalidate_finder_cache()
        self.stdout.write(self.style.SUCCESS(f"Imported {len(objs)} schools"))