"""Database-backed gazetteer of Dar es Salaam neighborhood/ward names and
their approximate coordinates, used by geo.fuzzy_match_area to resolve a
customer-typed area name to a map center. Previously this lived as a
hardcoded Python dict (market/geo.py's old DAR_AREAS) — that meant admins
could never see or correct a wrong coordinate, and any new area the AI
fallback resolved for a customer's search was thrown away and re-guessed
from scratch on every future search. Now it's a real table: admin-editable
for accuracy, and every AI-resolved area gets written back so the site
only ever has to ask the AI once per area."""

from datetime import datetime, timezone

from django.core.cache import cache

from accounts.supabase_client import get_client

_CACHE_KEY = 'pickker_known_areas_all'
_CACHE_TTL = 60

# A confirmed customer location further than this from every known area is
# treated as "genuinely new" and worth learning from — closer than this,
# it's assumed to already be covered by an existing area.
_NEW_AREA_MIN_DISTANCE_KM = 2.0


def _invalidate():
    cache.delete(_CACHE_KEY)


def get_all_areas():
    """All areas, cached briefly — this is read on every fuzzy-search
    keystroke (public, no-login endpoint), so it's worth not hitting
    Supabase every time."""
    areas = cache.get(_CACHE_KEY)
    if areas is None:
        resp = get_client().table('pickker_known_areas').select('*').order('name').execute()
        areas = resp.data
        cache.set(_CACHE_KEY, areas, _CACHE_TTL)
    return areas


def create_area(name, lat, lng, aliases=None, region_id=None):
    name = (name or '').strip().lower()
    if not name:
        return
    get_client().table('pickker_known_areas').insert({
        'name': name,
        'lat': lat,
        'lng': lng,
        'aliases': aliases or [],
        'source': 'admin',
        'region_id': region_id,
    }).execute()
    _invalidate()


def update_area(area_id, name, lat, lng, aliases=None, region_id=None):
    get_client().table('pickker_known_areas').update({
        'name': (name or '').strip().lower(),
        'lat': lat,
        'lng': lng,
        'aliases': aliases if aliases is not None else [],
        'region_id': region_id,
        'updated_at': datetime.now(timezone.utc).isoformat(),
    }).eq('id', area_id).execute()
    _invalidate()


def delete_area(area_id):
    get_client().table('pickker_known_areas').delete().eq('id', area_id).execute()
    _invalidate()


def bulk_import_areas(rows):
    """Upserts a batch of {name, lat, lng, aliases} dicts (from an admin CSV
    upload) in one round trip each for the existing-name lookup and the
    insert/update — matches by name (case-insensitive), so re-importing the
    same CSV after correcting a coordinate just updates the existing row
    rather than creating a duplicate. Returns (created_count, updated_count,
    skipped_count)."""
    client = get_client()
    cleaned = []
    skipped = 0
    for row in rows:
        name = (row.get('name') or '').strip().lower()
        try:
            lat = float(row['lat']) if row.get('lat') not in (None, '') else None
            lng = float(row['lng']) if row.get('lng') not in (None, '') else None
        except (TypeError, ValueError):
            skipped += 1
            continue
        if not name:
            skipped += 1
            continue
        aliases_raw = row.get('aliases') or ''
        aliases = [a.strip().lower() for a in aliases_raw.replace(',', ';').split(';') if a.strip()]
        cleaned.append({'name': name, 'lat': lat, 'lng': lng, 'aliases': aliases})

    if not cleaned:
        return 0, 0, skipped

    existing_resp = (
        client.table('pickker_known_areas')
        .select('id, name')
        .in_('name', [r['name'] for r in cleaned])
        .execute()
    )
    existing_by_name = {r['name']: r['id'] for r in existing_resp.data}

    created, updated = 0, 0
    for row in cleaned:
        if row['name'] in existing_by_name:
            client.table('pickker_known_areas').update({
                'lat': row['lat'], 'lng': row['lng'], 'aliases': row['aliases'],
                'source': 'admin', 'updated_at': datetime.now(timezone.utc).isoformat(),
            }).eq('id', existing_by_name[row['name']]).execute()
            updated += 1
        else:
            client.table('pickker_known_areas').insert({
                'name': row['name'], 'lat': row['lat'], 'lng': row['lng'],
                'aliases': row['aliases'], 'source': 'admin',
            }).execute()
            created += 1

    _invalidate()
    return created, updated, skipped


def save_area_coords(name, lat, lng, source='ai'):
    """Upserts coordinates for an area by name — used when the AI fallback
    in fuzzy_area_search resolves a place the gazetteer didn't have coords
    for yet (either a known name with lat=None, or a brand new name). This
    is what makes a customer's search permanently teach the gazetteer,
    instead of re-asking the AI for the same place every time."""
    name_lower = (name or '').strip().lower()
    if not name_lower:
        return
    client = get_client()
    existing = client.table('pickker_known_areas').select('id').eq('name', name_lower).execute()
    if existing.data:
        client.table('pickker_known_areas').update({
            'lat': lat, 'lng': lng, 'source': source,
            'updated_at': datetime.now(timezone.utc).isoformat(),
        }).eq('id', existing.data[0]['id']).execute()
    else:
        client.table('pickker_known_areas').insert({
            'name': name_lower, 'lat': lat, 'lng': lng, 'aliases': [], 'source': source,
        }).execute()
    _invalidate()


def _area_name_from_address(address):
    """Nominatim's reverse-geocoded address is most-specific-first (e.g.
    'Mikocheni Road, Mikocheni, Kinondoni, Dar es Salaam, Tanzania') — the
    first segment is usually a street, so prefer the second one (typically
    the neighbourhood) when there is one."""
    parts = [p.strip() for p in (address or '').split(',') if p.strip()]
    if len(parts) >= 2:
        return parts[1]
    return parts[0] if parts else None


def learn_area_from_location(address, lat, lng):
    """Called when a customer confirms a delivery pin at checkout. If it's
    nowhere near any area already on file, save it (source='customer') so
    the gazetteer grows from real usage, not just admin/AI input. Mirrors
    save_area_coords's self-improving idea, but triggered by a customer's
    own confirmed pin instead of a search."""
    from .geo import haversine_km

    name = _area_name_from_address(address)
    if not name:
        return

    areas_with_coords = [a for a in get_all_areas() if a['lat'] is not None]
    if areas_with_coords:
        nearest_km = min(
            haversine_km(lat, lng, float(a['lat']), float(a['lng']))
            for a in areas_with_coords
        )
        if nearest_km <= _NEW_AREA_MIN_DISTANCE_KM:
            return

    # Don't overwrite an existing area's coordinates just because a
    # customer's address string happens to repeat its name — only fill in
    # truly new names (save_area_coords would upsert onto a same-named row).
    existing = get_client().table('pickker_known_areas').select('id').eq('name', name.strip().lower()).execute()
    if existing.data:
        return

    save_area_coords(name, lat, lng, source='customer')