src/customer_intelligence/analytics/sellers.py

"""Seller performance analytics queries."""

import pandas as pd
from sqlalchemy import Engine, text


def top_sellers_by_revenue(engine: Engine, limit: int = 20) -> pd.DataFrame:
    sql = text("""
        SELECT
            ds.seller_id,
            ds.city,
            ds.state,
            ds.total_orders,
            ds.avg_review_score,
            ds.on_time_rate,
            SUM(f.price + f.freight_value) AS revenue
        FROM dim_sellers ds
        JOIN fact_orders f ON ds.seller_key = f.seller_key
        WHERE f.order_status NOT IN ('canceled', 'unavailable')
        GROUP BY ds.seller_id, ds.city, ds.state, ds.total_orders, ds.avg_review_score, ds.on_time_rate
        ORDER BY revenue DESC LIMIT :n
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn, params={"n": limit})


def seller_review_distribution(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT review_score, COUNT(*) AS orders
        FROM fact_orders
        WHERE review_score IS NOT NULL
        GROUP BY 1 ORDER BY 1
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def sellers_with_geo(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT seller_id, city, state, lat, lng, total_orders, avg_review_score
        FROM dim_sellers
        WHERE lat IS NOT NULL AND lng IS NOT NULL
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)


def all_sellers(engine: Engine) -> pd.DataFrame:
    sql = text("""
        SELECT seller_id, city, state, total_orders, avg_review_score, on_time_rate
        FROM dim_sellers
        ORDER BY total_orders DESC
    """)
    with engine.connect() as conn:
        return pd.read_sql(sql, conn)