"""Admin-wide market & customer behavior analytics — aggregates real order
history into the signals an admin needs to actually run the business: what
sells, where customers are, when they order, how much delivery really
costs, how pickers are rated, and what's missing from the catalog.
Everything here reads existing operational tables (orders, order items,
ratings, known areas, markets) plus two small new logs (contact messages,
market searches) — there's no separate "analytics database" to drift out
of sync with the real one."""

from collections import defaultdict
from datetime import datetime, timedelta, timezone
from decimal import Decimal

from accounts.supabase_client import get_client

# EAT (East Africa Time, Dar es Salaam) is UTC+3 year-round — no DST — so a
# fixed offset is exactly right for converting created_at (stored in UTC)
# into the local hour a customer/picker actually experienced.
_EAT_OFFSET = timedelta(hours=3)
_WEEKDAY_LABELS = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']


def _parse_dt(value):
    return datetime.fromisoformat(value.replace('Z', '+00:00'))


def _shift_month(year, month, delta):
    total = year * 12 + (month - 1) + delta
    return total // 12, total % 12 + 1


def get_all_orders(days=365):
    """Every non-cancelled order in the window, with just the fields the
    functions below need — fetched once and reused, since PostgREST has no
    server-side GROUP BY for us to lean on."""
    since = (datetime.now(timezone.utc) - timedelta(days=days)).isoformat()
    resp = (
        get_client()
        .table('pickker_orders')
        .select('id, customer_id, picker_id, delivery_lat, delivery_lng, distance_km, '
                'delivery_fee, picking_fee, package_fee, total_amount, '
                'delivery_date, is_quick, status, created_at')
        .gte('created_at', since)
        .execute()
    )
    return [o for o in resp.data if o['status'] != 'cancelled']


def get_overview_stats(orders):
    delivered = [o for o in orders if o['status'] == 'delivered']
    total_revenue = sum((Decimal(str(o['total_amount'])) for o in delivered), Decimal('0'))
    return {
        'total_orders': len(orders),
        'delivered_count': len(delivered),
        'total_revenue': total_revenue,
        'avg_order_value': (total_revenue / len(delivered)) if delivered else Decimal('0'),
        'unique_customers': len({o['customer_id'] for o in orders}),
    }


def get_top_products(orders, limit=15):
    """Ranked by revenue, platform-wide — same shape as the customer-facing
    spending_analytics.get_top_products, just without the customer filter.
    Includes current stock so admin can spot a bestseller running low."""
    order_ids = [o['id'] for o in orders]
    if not order_ids:
        return []

    client = get_client()
    items = []
    CHUNK = 300
    for i in range(0, len(order_ids), CHUNK):
        chunk = order_ids[i:i + CHUNK]
        resp = client.table('pickker_order_items').select('product_id, product_name, quantity, line_total, order_id').in_('order_id', chunk).execute()
        items.extend(resp.data)

    agg = defaultdict(lambda: {'quantity': 0, 'revenue': Decimal('0'), 'order_ids': set(), 'product_id': None})
    for it in items:
        name = it['product_name']
        agg[name]['quantity'] += it['quantity']
        agg[name]['revenue'] += Decimal(str(it['line_total']))
        agg[name]['order_ids'].add(it['order_id'])
        agg[name]['product_id'] = it.get('product_id')

    ranked = sorted(agg.items(), key=lambda kv: kv[1]['revenue'], reverse=True)
    total_revenue = sum((d['revenue'] for _, d in ranked), Decimal('0')) or Decimal('1')
    top = ranked[:limit]

    product_ids = [d['product_id'] for _, d in top if d['product_id']]
    stock_by_id = {}
    if product_ids:
        stock_resp = client.table('pickker_products').select('id, stock_qty').in_('id', product_ids).execute()
        stock_by_id = {p['id']: p['stock_qty'] for p in stock_resp.data}

    return [
        {
            'name': name,
            'quantity': d['quantity'],
            'revenue': d['revenue'],
            'order_count': len(d['order_ids']),
            'pct_of_revenue': round(float(d['revenue'] / total_revenue * 100), 1),
            'stock_qty': stock_by_id.get(d['product_id']),
        }
        for name, d in top
    ]


def get_spend_by_area(orders, limit=20):
    """Matches each order's delivery pin to its nearest known area (same
    haversine-nearest approach as markets.get_nearest_market) — the
    closest thing to "population/demand by area" this app can answer
    without real census data: how many orders (and how much revenue) come
    from each neighborhood."""
    from .geo import haversine_km
    from .known_areas import get_all_areas

    areas = [a for a in get_all_areas() if a.get('lat') is not None and a.get('lng') is not None]
    if not areas:
        return [], len(orders)

    agg = defaultdict(lambda: {'order_count': 0, 'revenue': Decimal('0')})
    unmatched = 0
    for o in orders:
        lat, lng = o.get('delivery_lat'), o.get('delivery_lng')
        if lat is None or lng is None:
            unmatched += 1
            continue
        nearest = min(areas, key=lambda a: haversine_km(float(lat), float(lng), float(a['lat']), float(a['lng'])))
        agg[nearest['name']]['order_count'] += 1
        if o['status'] == 'delivered':
            agg[nearest['name']]['revenue'] += Decimal(str(o['total_amount']))

    ranked = sorted(agg.items(), key=lambda kv: kv[1]['order_count'], reverse=True)
    return [{'area': name, 'order_count': d['order_count'], 'revenue': d['revenue']} for name, d in ranked[:limit]], unmatched


def get_orders_by_market(orders):
    """Which registered physical market fed the most orders — derived via
    the assigned picker's own registered market, since an order doesn't
    record a market directly (a B2B/truck order or an off-market picker
    shows up under "Unregistered / other")."""
    from .markets import get_all_markets

    client = get_client()
    picker_ids = list({o['picker_id'] for o in orders if o.get('picker_id')})
    market_id_by_picker = {}
    if picker_ids:
        profiles_resp = client.table('pickker_picker_profiles').select('user_id, market_id').in_('user_id', picker_ids).execute()
        market_id_by_picker = {p['user_id']: p.get('market_id') for p in profiles_resp.data}
    markets_by_id = {m['id']: m['name'] for m in get_all_markets()}

    agg = defaultdict(lambda: {'order_count': 0, 'revenue': Decimal('0')})
    for o in orders:
        name = markets_by_id.get(market_id_by_picker.get(o.get('picker_id')), 'Unregistered / other')
        agg[name]['order_count'] += 1
        if o['status'] == 'delivered':
            agg[name]['revenue'] += Decimal(str(o['total_amount']))

    ranked = sorted(agg.items(), key=lambda kv: kv[1]['order_count'], reverse=True)
    return [{'market': name, 'order_count': d['order_count'], 'revenue': d['revenue']} for name, d in ranked]


def get_day_of_week_pattern(orders):
    """Order volume by weekday — uses delivery_date (the day that actually
    matters operationally), falling back to the order's created date for
    quick-delivery orders scheduled same-day."""
    counts = [0] * 7
    for o in orders:
        date_str = o.get('delivery_date') or (o['created_at'][:10] if o.get('created_at') else None)
        if not date_str:
            continue
        try:
            counts[datetime.fromisoformat(date_str[:10]).weekday()] += 1
        except ValueError:
            continue
    max_count = max(counts) or 1
    return [
        {'label': label, 'count': counts[i], 'bar_height_px': round(counts[i] / max_count * 140)}
        for i, label in enumerate(_WEEKDAY_LABELS)
    ]


def get_hour_of_day_pattern(orders):
    """What hour customers actually place orders (local Dar es Salaam
    time), grouped into 2-hour bands so the chart stays readable."""
    counts = [0] * 12  # 12 bands of 2 hours each
    for o in orders:
        if not o.get('created_at'):
            continue
        try:
            local_hour = (_parse_dt(o['created_at']) + _EAT_OFFSET).hour
        except ValueError:
            continue
        counts[local_hour // 2] += 1
    max_count = max(counts) or 1
    return [
        {'label': f'{b*2:02d}h', 'count': counts[b], 'bar_height_px': round(counts[b] / max_count * 140)}
        for b in range(12)
    ]


def get_monthly_trend(orders, months=12):
    """Order volume + revenue per calendar month, oldest first — the
    season/pattern-over-time view."""
    now = datetime.now(timezone.utc)
    keys = [_shift_month(now.year, now.month, -i) for i in range(months - 1, -1, -1)]
    buckets = {k: {'order_count': 0, 'revenue': Decimal('0')} for k in keys}
    for o in orders:
        if not o.get('created_at'):
            continue
        dt = _parse_dt(o['created_at'])
        key = (dt.year, dt.month)
        if key in buckets:
            buckets[key]['order_count'] += 1
            if o['status'] == 'delivered':
                buckets[key]['revenue'] += Decimal(str(o['total_amount']))

    max_count = max((b['order_count'] for b in buckets.values()), default=0) or 1
    return [
        {
            'label': datetime(y, m, 1).strftime('%b %Y'),
            'order_count': buckets[(y, m)]['order_count'],
            'revenue': buckets[(y, m)]['revenue'],
            'bar_height_px': round(buckets[(y, m)]['order_count'] / max_count * 140),
        }
        for (y, m) in keys
    ]


def get_customer_trend(months=12):
    """New vs. returning customers per month. Needs each customer's true
    first-ever order date (which can be well before the display window),
    so this fetches lightweight columns across all history rather than
    reusing the days-limited order set the other functions share."""
    resp = (
        get_client()
        .table('pickker_orders')
        .select('customer_id, created_at, status')
        .neq('status', 'cancelled')
        .execute()
    )
    rows = resp.data
    first_order = {}
    for r in rows:
        dt = _parse_dt(r['created_at'])
        cid = r['customer_id']
        if cid not in first_order or dt < first_order[cid]:
            first_order[cid] = dt

    now = datetime.now(timezone.utc)
    keys = [_shift_month(now.year, now.month, -i) for i in range(months - 1, -1, -1)]
    buckets = {k: {'new': set(), 'returning': set()} for k in keys}
    for r in rows:
        dt = _parse_dt(r['created_at'])
        key = (dt.year, dt.month)
        if key not in buckets:
            continue
        cid = r['customer_id']
        bucket_key = 'new' if (first_order[cid].year, first_order[cid].month) == key else 'returning'
        buckets[key][bucket_key].add(cid)

    return [
        {
            'label': datetime(y, m, 1).strftime('%b %Y'),
            'new_customers': len(buckets[(y, m)]['new']),
            'returning_customers': len(buckets[(y, m)]['returning']),
        }
        for (y, m) in keys
    ]


def get_delivery_cost_analysis(orders):
    """What delivery actually costs on average — fee, distance, fee-per-km
    realized, and how much of the volume is quick/premium delivery."""
    delivered = [o for o in orders if o['status'] == 'delivered']
    if not delivered:
        return None
    total = len(delivered)
    avg_fee = sum((Decimal(str(o['delivery_fee'])) for o in delivered), Decimal('0')) / total
    avg_distance = sum((Decimal(str(o.get('distance_km') or 0)) for o in delivered), Decimal('0')) / total
    avg_picking_fee = sum((Decimal(str(o.get('picking_fee') or 0)) for o in delivered), Decimal('0')) / total
    avg_package_fee = sum((Decimal(str(o.get('package_fee') or 0)) for o in delivered), Decimal('0')) / total
    quick_count = sum(1 for o in delivered if o.get('is_quick'))
    return {
        'delivered_count': total,
        'avg_delivery_fee': avg_fee,
        'avg_distance_km': avg_distance,
        'avg_picking_fee': avg_picking_fee,
        'avg_package_fee': avg_package_fee,
        'fee_per_km': (avg_fee / avg_distance) if avg_distance else Decimal('0'),
        'quick_delivery_pct': round(quick_count / total * 100, 1),
    }


def get_rating_breakdown():
    """1-5 star distribution platform-wide, plus the pickers most in need
    of coaching (lowest average, with at least 2 ratings so a single bad
    day doesn't unfairly flag someone)."""
    client = get_client()
    rows = client.table('pickker_ratings').select('rating, picker_id').execute().data
    distribution = [0] * 5
    per_picker = defaultdict(list)
    for r in rows:
        rating = r['rating']
        if 1 <= rating <= 5:
            distribution[rating - 1] += 1
        per_picker[r['picker_id']].append(rating)

    picker_ids = [pid for pid, rs in per_picker.items() if len(rs) >= 2]
    names_by_id = {}
    if picker_ids:
        users_resp = client.table('pickker_users').select('id, first_name, last_name').in_('id', picker_ids).execute()
        names_by_id = {u['id']: f"{u['first_name']} {u['last_name']}".strip() for u in users_resp.data}

    needs_improvement = sorted(
        (
            {'name': names_by_id.get(pid, f'Picker #{pid}'), 'average': round(sum(rs) / len(rs), 1), 'count': len(rs)}
            for pid, rs in per_picker.items() if len(rs) >= 2
        ),
        key=lambda p: p['average'],
    )[:10]

    total = sum(distribution)
    return {
        'distribution': [{'stars': i + 1, 'count': c, 'pct': round(c / total * 100, 1) if total else 0} for i, c in enumerate(distribution)],
        'total_ratings': total,
        'needs_improvement': needs_improvement,
    }


def get_product_demand_signals(limit=20):
    """What customers wanted but couldn't get: recent "can't find it"
    contact messages, plus the most common product searches that came
    back empty — the most direct "what to add to the catalog" signal
    available."""
    client = get_client()
    contact_resp = client.table('pickker_contact_messages').select('*').order('created_at', desc=True).limit(limit).execute()
    search_resp = client.table('pickker_search_log').select('query, result_count').order('created_at', desc=True).limit(1000).execute()

    zero_result_counts = defaultdict(int)
    all_search_counts = defaultdict(int)
    for row in search_resp.data:
        q = (row['query'] or '').strip().lower()
        if not q:
            continue
        all_search_counts[q] += 1
        if row['result_count'] == 0:
            zero_result_counts[q] += 1

    return {
        'recent_contact_messages': contact_resp.data,
        'top_zero_result_searches': [
            {'query': q, 'count': c} for q, c in sorted(zero_result_counts.items(), key=lambda kv: kv[1], reverse=True)[:15]
        ],
        'top_searches': [
            {'query': q, 'count': c} for q, c in sorted(all_search_counts.items(), key=lambda kv: kv[1], reverse=True)[:15]
        ],
    }


def get_admin_analytics(days=365):
    orders = get_all_orders(days=days)
    spend_by_area, unmatched_area_count = get_spend_by_area(orders)
    return {
        'window_days': days,
        'overview': get_overview_stats(orders),
        'top_products': get_top_products(orders),
        'spend_by_area': spend_by_area,
        'unmatched_area_count': unmatched_area_count,
        'orders_by_market': get_orders_by_market(orders),
        'day_pattern': get_day_of_week_pattern(orders),
        'hour_pattern': get_hour_of_day_pattern(orders),
        'monthly_trend': get_monthly_trend(orders),
        'customer_trend': get_customer_trend(),
        'delivery_cost': get_delivery_cost_analysis(orders),
        'rating_breakdown': get_rating_breakdown(),
        'demand_signals': get_product_demand_signals(),
    }


def log_search(query, result_count):
    """Fire-and-forget search logging for the market page — a failure here
    must never break the page a real customer is looking at."""
    query = (query or '').strip()
    if not query:
        return
    try:
        get_client().table('pickker_search_log').insert({'query': query, 'result_count': result_count}).execute()
    except Exception:
        pass