"""Admin-managed catalog of physical produce markets (Dar es Salaam to
start) — seeded from a real market survey CSV. B2C pickers are based out
of a market, not an arbitrary pin: signup and profile-edit let a picker
pick their market from this list instead of hand-placing a pin every
time, and admin can register new markets (or bulk-import more) as the
platform expands to new areas."""

from datetime import datetime, timezone

from django.core.cache import cache

from accounts.supabase_client import get_client

_CACHE_KEY = 'pickker_markets_all'
_CACHE_TTL = 300


def _invalidate():
    cache.delete(_CACHE_KEY)


def get_all_markets():
    """All markets (active and inactive), cached — read on every signup/
    profile-edit page render, so worth not hitting Supabase every time."""
    markets = cache.get(_CACHE_KEY)
    if markets is None:
        resp = get_client().table('pickker_markets').select('*').order('region').order('name').execute()
        markets = resp.data
        cache.set(_CACHE_KEY, markets, _CACHE_TTL)
    return markets


def get_active_markets():
    return [m for m in get_all_markets() if m['is_active']]


def get_market(market_id):
    if not market_id:
        return None
    return next((m for m in get_all_markets() if m['id'] == market_id), None)


def get_picker_origin_coords(profile):
    """Where a picker's distance/fee/route calculations should originate
    from for a new order — their registered market's own coordinates when
    they have one set, never wherever they physically happen to be (e.g.
    mid-delivery on another order). Falls back to their profile pin only
    for pickers with no registered market (warehouse/shop sourced)."""
    market = get_market(profile.get('market_id')) if profile.get('market_id') else None
    if market:
        return float(market['lat']), float(market['lng'])
    if profile.get('location_lat') is not None:
        return float(profile['location_lat']), float(profile['location_lng'])
    return None, None


def get_nearest_market(lat, lng):
    """Whichever registered, active market is closest to a given point, and
    how far — used both to flag a picker whose picking-phase location is far
    from any designated market (a real signal they may be sourcing from
    somewhere else, off quality-standard) and, for a mixed order (one tied
    market plus generic items), to suggest this nearest market as an extra
    stop for the generic portion (see market/views.py::_checkout_picker_b2c).
    Includes the full market row (id/lat/lng) so callers can use it as a
    route waypoint, not just display its name/distance."""
    from .geo import haversine_km

    markets = get_active_markets()
    if not markets or lat is None or lng is None:
        return None

    nearest = min(markets, key=lambda m: haversine_km(lat, lng, float(m['lat']), float(m['lng'])))
    distance_km = haversine_km(lat, lng, float(nearest['lat']), float(nearest['lng']))
    return {**nearest, 'distance_km': round(distance_km, 2)}


def get_market_categories(market):
    """A market's registered categories() list — what it's known to sell,
    admin-tagged from the same category catalog products use (see
    market/categories.py). Stored as a comma-separated string on the row
    rather than a join table, matching this codebase's existing convention
    for small admin-editable multi-value fields (e.g. delivery_weekdays)."""
    raw = (market or {}).get('categories') or ''
    return [c.strip() for c in raw.split(',') if c.strip()]


_UNSET = object()


def create_market(region, name, notes, lat, lng, contact_phone='', contact_person='', landmark_note='', categories=None, logo_url=None, region_id=None):
    get_client().table('pickker_markets').insert({
        'region': (region or '').strip(),
        'region_id': region_id,
        'name': name.strip(),
        'notes': (notes or '').strip(),
        'lat': lat,
        'lng': lng,
        'contact_phone': (contact_phone or '').strip(),
        'contact_person': (contact_person or '').strip(),
        'landmark_note': (landmark_note or '').strip(),
        'categories': ','.join(categories or []),
        'logo_url': logo_url,
    }).execute()
    _invalidate()


def update_market(market_id, region, name, notes, lat, lng, contact_phone='', contact_person='', landmark_note='', categories=None, logo_url=None, region_id=_UNSET):
    """categories=None (the default) leaves whatever categories are already
    registered untouched — important since this is also called from the
    admin picker-location-sync flow (accounts/views.py) with no knowledge of
    categories at all; passing [] explicitly is what clears them. logo_url
    works the same way — only overwritten when the admin actually uploaded a
    new file this submit, never cleared just because a form round-tripped
    without one. region_id follows the same "not provided means leave
    untouched" shape, but since None is itself a meaningful value here (the
    admin cleared the region-search box), a real sentinel is used instead of
    None as the default — the picker-location-sync call site never passes
    it, so a picker-triggered coordinate correction never wipes out a
    region an admin deliberately set (see market/regions.py::
    get_or_create_region, resolved by the caller before this is called)."""
    row = {
        'region': (region or '').strip(),
        'name': name.strip(),
        'notes': (notes or '').strip(),
        'lat': lat,
        'lng': lng,
        'contact_phone': (contact_phone or '').strip(),
        'contact_person': (contact_person or '').strip(),
        'landmark_note': (landmark_note or '').strip(),
    }
    if categories is not None:
        row['categories'] = ','.join(categories)
    if logo_url is not None:
        row['logo_url'] = logo_url
    if region_id is not _UNSET:
        row['region_id'] = region_id
    get_client().table('pickker_markets').update(row).eq('id', market_id).execute()
    _invalidate()


def toggle_market_active(market_id):
    client = get_client()
    resp = client.table('pickker_markets').select('is_active').eq('id', market_id).execute()
    if resp.data:
        current = resp.data[0]['is_active']
        client.table('pickker_markets').update({'is_active': not current}).eq('id', market_id).execute()
        _invalidate()
        return not current
    return None


def delete_market(market_id):
    get_client().table('pickker_markets').delete().eq('id', market_id).execute()
    _invalidate()


def bulk_import_markets(rows):
    """Upserts a batch of {region, name, notes, lat, lng} dicts from an
    admin CSV upload — matches by name (case-insensitive), so re-importing
    an updated CSV corrects existing rows instead of duplicating them.
    Returns (created_count, updated_count, skipped_count)."""
    client = get_client()
    cleaned = []
    skipped = 0
    for row in rows:
        name = (row.get('name') or row.get('Market Name & Location') or '').strip()
        try:
            lat = float(row.get('lat') or row.get('Latitude'))
            lng = float(row.get('lng') or row.get('Longitude'))
        except (TypeError, ValueError):
            skipped += 1
            continue
        if not name:
            skipped += 1
            continue
        region = (row.get('region') or row.get('Region') or '').strip()
        notes = (row.get('notes') or row.get('Primary Focus / Notes') or '').strip()
        cleaned.append({'region': region, 'name': name, 'notes': notes, 'lat': lat, 'lng': lng})

    if not cleaned:
        return 0, 0, skipped

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

    created, updated = 0, 0
    for row in cleaned:
        key = row['name'].strip().lower()
        if key in existing_by_lower_name:
            client.table('pickker_markets').update({
                'region': row['region'], 'notes': row['notes'], 'lat': row['lat'], 'lng': row['lng'],
            }).eq('id', existing_by_lower_name[key]).execute()
            updated += 1
        else:
            client.table('pickker_markets').insert(row).execute()
            created += 1

    _invalidate()
    return created, updated, skipped