# exams/services/geo_importer.py
import re
from django.db import transaction
from exams.models import Region, District, Ward, School

def clean_string(value):
    """Clean and normalize string values"""
    if value is None:
        return None
    # Convert to string and clean
    str_value = str(value).strip()
    # Return None if empty string
    return str_value.upper() if str_value else None

def import_geo_data(file_obj, file_extension):
    """
    Import geographic data from Excel or CSV file without pandas
    Returns: (created_dict, updated_dict, errors_list)
    """
    
    if file_extension == '.xlsx':
        import openpyxl
        workbook = openpyxl.load_workbook(file_obj)
        sheet = workbook.active
        
        # Get headers from first row
        headers = []
        for cell in sheet[1]:
            if cell.value:
                headers.append(str(cell.value).strip().upper())
            else:
                headers.append(None)
        
        # Get data rows
        data_rows = []
        for row in sheet.iter_rows(min_row=2, values_only=True):
            row_dict = {}
            for idx, value in enumerate(row):
                if idx < len(headers) and headers[idx]:
                    row_dict[headers[idx]] = value
            if any(row_dict.values()):  # Skip completely empty rows
                data_rows.append(row_dict)
                
    elif file_extension == '.csv':
        import csv
        import io
        
        # Read CSV content
        content = file_obj.read().decode('utf-8-sig')  # Handle BOM
        csv_reader = csv.DictReader(io.StringIO(content))
        
        # Clean headers
        cleaned_reader = []
        for row in csv_reader:
            cleaned_row = {}
            for key, value in row.items():
                if key:
                    cleaned_key = key.strip().upper()
                    cleaned_row[cleaned_key] = value
            if any(cleaned_row.values()):  # Skip empty rows
                cleaned_reader.append(cleaned_row)
        data_rows = cleaned_reader
    else:
        raise ValueError("Unsupported file format")
    
    # Initialize counters
    created = {"regions": 0, "districts": 0, "wards": 0, "schools": 0}
    updated = {"schools": 0}
    errors = []
    
    # Define required columns
    required_cols = ["REGION", "COUNCIL", "WARD", "SCHOOL NAME", "SCHOOL_CODE", "OWNERSHIP"]
    
    with transaction.atomic():
        for row_num, row in enumerate(data_rows, start=2):  # Start at row 2 because row 1 is headers
            try:
                # Check for required columns
                missing_cols = [col for col in required_cols if col not in row]
                if missing_cols:
                    errors.append(f"Row {row_num}: Missing columns: {', '.join(missing_cols)}")
                    continue
                
                # Extract and clean values
                region_name = clean_string(row.get("REGION"))
                district_name = clean_string(row.get("COUNCIL"))
                ward_name = clean_string(row.get("WARD"))
                school_name = clean_string(row.get("SCHOOL NAME"))
                school_code = clean_string(row.get("SCHOOL_CODE"))
                ownership = clean_string(row.get("OWNERSHIP"))
                
                # Validate required fields are not empty
                if not region_name:
                    errors.append(f"Row {row_num}: REGION is empty")
                    continue
                if not district_name:
                    errors.append(f"Row {row_num}: COUNCIL is empty")
                    continue
                if not ward_name:
                    errors.append(f"Row {row_num}: WARD is empty")
                    continue
                if not school_name:
                    errors.append(f"Row {row_num}: SCHOOL NAME is empty")
                    continue
                if not school_code:
                    errors.append(f"Row {row_num}: SCHOOL_CODE is empty")
                    continue
                if not ownership:
                    errors.append(f"Row {row_num}: OWNERSHIP is empty")
                    continue
                
                # Get or create region
                region, r_created = Region.objects.get_or_create(name=region_name)
                if r_created:
                    created["regions"] += 1
                
                # Get or create district
                district, d_created = District.objects.get_or_create(
                    region=region,
                    name=district_name
                )
                if d_created:
                    created["districts"] += 1
                
                # Get or create ward
                ward, w_created = Ward.objects.get_or_create(
                    district=district,
                    name=ward_name
                )
                if w_created:
                    created["wards"] += 1
                
                # Handle optional fields
                school_regno = None
                if "SCHOOL_REGNO" in row:
                    school_regno_val = clean_string(row["SCHOOL_REGNO"])
                    if school_regno_val:
                        school_regno = school_regno_val
                
                # Handle latitude/longitude
                latitude = None
                if "LATITUDE" in row and row["LATITUDE"]:
                    try:
                        lat_val = str(row["LATITUDE"]).strip()
                        if lat_val and lat_val.lower() not in ['nan', 'none', '']:
                            latitude = float(lat_val)
                    except (ValueError, TypeError):
                        errors.append(f"Row {row_num}: Invalid LATITUDE value: {row['LATITUDE']}")
                
                longitude = None
                if "LONGITUDE" in row and row["LONGITUDE"]:
                    try:
                        lon_val = str(row["LONGITUDE"]).strip()
                        if lon_val and lon_val.lower() not in ['nan', 'none', '']:
                            longitude = float(lon_val)
                    except (ValueError, TypeError):
                        errors.append(f"Row {row_num}: Invalid LONGITUDE value: {row['LONGITUDE']}")
                
                # Build defaults dictionary
                defaults = {
                    "name": school_name,
                    "region": region,
                    "district": district,
                    "ward": ward,
                    "ownership": ownership,
                    "latitude": latitude,
                    "longitude": longitude,
                }
                
                # Add school_regno if available
                if school_regno:
                    defaults["school_regno"] = school_regno
                
                # Update or create school
                school, s_created = School.objects.update_or_create(
                    school_code=school_code,
                    defaults=defaults
                )
                
                if s_created:
                    created["schools"] += 1
                else:
                    updated["schools"] += 1
                    
            except Exception as e:
                errors.append(f"Row {row_num}: {str(e)}")
                continue
    
    return created, updated, errors