"""Customer ratings of pickers, one per delivered order."""

from accounts.supabase_client import get_client


def get_rating_for_order(order_id):
    resp = get_client().table('pickker_ratings').select('*').eq('order_id', order_id).execute()
    return resp.data[0] if resp.data else None


def submit_rating(order_id, picker_id, customer_id, rating, comment=''):
    if get_rating_for_order(order_id):
        return
    get_client().table('pickker_ratings').insert({
        'order_id': order_id,
        'picker_id': picker_id,
        'customer_id': customer_id,
        'rating': int(rating),
        'comment': (comment or '').strip()[:500],
    }).execute()


def get_picker_rating_summary(picker_id):
    rows = get_client().table('pickker_ratings').select('rating').eq('picker_id', picker_id).execute().data
    if not rows:
        return {'average': None, 'count': 0}
    total = sum(r['rating'] for r in rows)
    return {'average': round(total / len(rows), 1), 'count': len(rows)}


def get_platform_rating_summary():
    rows = get_client().table('pickker_ratings').select('rating').execute().data
    if not rows:
        return {'average': None, 'count': 0}
    total = sum(r['rating'] for r in rows)
    return {'average': round(total / len(rows), 1), 'count': len(rows)}


def get_customer_picker_history(customer_id, picker_id):
    """This customer's own delivered-order count and average rating with
    this specific picker — 'how has this fleet served me', not the
    platform-wide summary. Returns None if they've never used this picker
    before, so the caller can omit the line entirely rather than show 0s."""
    rows = (
        get_client()
        .table('pickker_ratings')
        .select('rating')
        .eq('customer_id', customer_id)
        .eq('picker_id', picker_id)
        .execute()
        .data
    )
    orders_resp = (
        get_client()
        .table('pickker_orders')
        .select('id', count='exact')
        .eq('customer_id', customer_id)
        .eq('picker_id', picker_id)
        .eq('status', 'delivered')
        .execute()
    )
    order_count = orders_resp.count or 0
    if not order_count:
        return None
    return {
        'order_count': order_count,
        'average_rating': round(sum(r['rating'] for r in rows) / len(rows), 1) if rows else None,
    }


def get_picker_rating_summary_batch(picker_ids):
    """{picker_id: {'average': float|None, 'count': int}} for every id in
    picker_ids, computed from one query — the batch counterpart to
    get_picker_rating_summary, used by market/picker_ranking.py so scoring
    N candidate pickers costs one query instead of N."""
    picker_ids = list({pid for pid in picker_ids if pid})
    if not picker_ids:
        return {}
    rows = get_client().table('pickker_ratings').select('picker_id, rating').in_('picker_id', picker_ids).execute().data
    by_picker = {}
    for row in rows:
        by_picker.setdefault(row['picker_id'], []).append(row['rating'])
    return {
        pid: {'average': round(sum(vals) / len(vals), 1), 'count': len(vals)}
        for pid, vals in by_picker.items()
    }