import pandas as pd
from django.db import transaction
from .models import Region, District, Ward, School


def import_geo_data(file_path):
    """
    Import REGION → DISTRICT → WARD → SCHOOL structure from Excel/CSV.
    Required columns: REGION, COUNCIL, WARD, SCHOOL NAME, SCHOOL_CODE, OWNERSHIP
    """

    print("📌 Reading file:", file_path)
    df = pd.read_excel(file_path) if file_path.endswith(".xlsx") else pd.read_csv(file_path)

    required_cols = ["REGION", "COUNCIL", "WARD", "SCHOOL NAME", "SCHOOL_CODE", "OWNERSHIP"]
    for col in required_cols:
        if col not in df.columns:
            raise ValueError(f"Missing required column: {col}")

    print("📌 Importing... Please wait")

    with transaction.atomic():
        for _, row in df.iterrows():

            # 1. REGION
            region, _ = Region.objects.get_or_create(
                name=row["REGION"].strip().upper()
            )

            # 2. DISTRICT
            district, _ = District.objects.get_or_create(
                region=region,
                name=row["COUNCIL"].strip().upper()
            )

            # 3. WARD
            ward, _ = Ward.objects.get_or_create(
                district=district,
                name=row["WARD"].strip().upper()
            )

            # 4. SCHOOL 
            School.objects.update_or_create(
                school_code=row["SCHOOL_CODE"].strip(),
                defaults={
                    "name": row["SCHOOL NAME"].strip(),
                    "ward": ward,
                    "district": district,
                    "region": region,
                    "ownership": row["OWNERSHIP"].strip(),
                }
            )

    print("✅ GEO Import Completed Successfully!")