"""Admin analytics dashboard service.

Builds a platform-wide analytics dashboard in a fixed number of SQL aggregation
queries (COUNT / SUM / AVG / GROUP BY / CASE) — never row-by-row, never N+1 — so
it scales to thousands of listings. All calculation happens here; the template
only renders the returned dictionary.
"""

from __future__ import annotations

from datetime import datetime, time, timedelta

from sqlalchemy import case, func
from sqlalchemy.orm import selectinload

from app.admin.audit import get_recent_admin_activity
from app.extensions import db
from app.marketplace.services import is_listing_currently_featured
from app.moderation.services import (
    get_moderation_analytics,
    get_recent_reports,
    get_report_summary,
    pending_reports_count,
)
from app.models import (
    Advertisement,
    Category,
    Conversation,
    Listing,
    ListingStatus,
    ListingView,
    Message,
    Offer,
    Subscription,
    SubscriptionStatus,
    User,
)
from app.models.base import utcnow
from app.premium.analytics import _dow_hour_exprs, _hour_label
from app.premium.services import get_premium_seller_ids

TREND_WINDOW_DAYS = 30
TOP_LIMIT = 10
CAT_CHART_LIMIT = 8

# Seller engagement score weights — kept here so the formula is changed in one
# place (score = views + favorites*5 + offers*10).
SELLER_SCORE_WEIGHTS = {"views": 1, "favorites": 5, "offers": 10}


def _seller_score(views: int, favorites: int, offers: int) -> int:
    w = SELLER_SCORE_WEIGHTS
    return views * w["views"] + favorites * w["favorites"] + offers * w["offers"]


def _pct(numerator, denominator) -> float:
    return round(numerator / denominator * 100, 1) if denominator else 0.0


def _currently_featured():
    return db.and_(
        Listing.is_featured.is_(True),
        Listing.featured_until.isnot(None),
        Listing.featured_until > utcnow(),
    )


def _daily_counts(date_col, start_date, *extra_filters, days: int = TREND_WINDOW_DAYS) -> list[dict]:
    """Zero-filled daily counts for ``date_col`` over ``days`` (one GROUP BY)."""
    start_dt = datetime.combine(start_date, time.min)
    rows = db.session.execute(
        db.select(func.date(date_col), func.count())
        .where(date_col >= start_dt, *extra_filters)
        .group_by(func.date(date_col))
    ).all()
    counts = {str(d)[:10]: int(n) for d, n in rows}
    return [
        {"date": (start_date + timedelta(days=i)).isoformat(),
         "count": counts.get((start_date + timedelta(days=i)).isoformat(), 0)}
        for i in range(days)
    ]


def _series_arrays(series: list[dict]) -> dict:
    return {
        "labels": [d["date"][5:] for d in series],  # MM-DD
        "values": [d["count"] for d in series],
    }


def _overview() -> dict:
    """Platform totals — a handful of aggregation queries."""
    now = utcnow()
    listing_row = db.session.execute(
        db.select(
            func.count(Listing.id),
            func.coalesce(func.sum(Listing.view_count), 0),
            func.coalesce(func.sum(Listing.favorite_count), 0),
            func.coalesce(func.sum(case((Listing.status == ListingStatus.AVAILABLE, 1), else_=0)), 0),
            func.coalesce(func.sum(case((Listing.status == ListingStatus.SOLD, 1), else_=0)), 0),
            func.coalesce(func.sum(case((_currently_featured(), 1), else_=0)), 0),
        ).where(Listing.deleted_at.is_(None))
    ).one()
    total_listings, total_views, total_favorites, active_listings, sold_listings, featured_listings = listing_row

    total_users = db.session.scalar(db.select(func.count(User.id))) or 0
    premium_members = db.session.scalar(
        db.select(func.count(func.distinct(Subscription.user_id)))
        .where(Subscription.status == SubscriptionStatus.ACTIVE, Subscription.end_date > now)
    ) or 0
    advertisements = db.session.scalar(db.select(func.count(Advertisement.id))) or 0
    conversations = db.session.scalar(db.select(func.count(Conversation.id))) or 0
    messages = db.session.scalar(
        db.select(func.count(Message.id)).where(Message.deleted_at.is_(None))
    ) or 0
    offers = db.session.scalar(db.select(func.count(Offer.id))) or 0

    return {
        "total_users": int(total_users),
        "active_listings": int(active_listings),
        "sold_listings": int(sold_listings),
        "total_listings": int(total_listings),
        "premium_members": int(premium_members),
        "featured_listings": int(featured_listings),
        "advertisements": int(advertisements),
        "total_conversations": int(conversations),
        "total_messages": int(messages),
        "total_offers": int(offers),
        "total_views": int(total_views),
        "total_favorites": int(total_favorites),
        "pending_reports": int(pending_reports_count()),
        "pending_verifications": int(_pending_verifications_count()),
    }


def _pending_verifications_count() -> int:
    """Pending student-verification requests (for the admin dashboard card)."""
    from app.auth.verification import get_pending_count

    return get_pending_count()


def _health(overview: dict) -> dict:
    tl = overview["total_listings"]
    tc = overview["total_conversations"]
    return {
        "avg_views_per_listing": round(overview["total_views"] / tl, 1) if tl else 0.0,
        "avg_offers_per_listing": round(overview["total_offers"] / tl, 1) if tl else 0.0,
        "avg_conversations_per_listing": round(overview["total_conversations"] / tl, 1) if tl else 0.0,
        "avg_favorites_per_listing": round(overview["total_favorites"] / tl, 1) if tl else 0.0,
        "avg_messages_per_conversation": round(overview["total_messages"] / tc, 1) if tc else 0.0,
        "featured_pct": _pct(overview["featured_listings"], tl),
        "premium_pct": _pct(overview["premium_members"], overview["total_users"]),
    }


def _offers_by_listing() -> dict:
    return {lid: c for lid, c in db.session.execute(
        db.select(Offer.listing_id, func.count()).group_by(Offer.listing_id)
    ).all()}


def _top_listings(offers_by_listing: dict) -> list[dict]:
    listings = db.session.scalars(
        db.select(Listing)
        .options(selectinload(Listing.images), selectinload(Listing.seller).selectinload(User.profile))
        .where(Listing.deleted_at.is_(None))
        .order_by(Listing.view_count.desc(), Listing.created_at.desc())
        .limit(TOP_LIMIT)
    ).all()
    rows = []
    for l in listings:
        seller = l.seller
        name = (seller.profile.full_name if seller and seller.profile else (seller.email if seller else "—"))
        rows.append({
            "public_id": l.public_id, "title": l.title, "price": l.price,
            "image": l.images[0].image_path if l.images else None,
            "seller": name,
            "views": l.view_count or 0, "favorites": l.favorite_count or 0,
            "offers": offers_by_listing.get(l.id, 0),
            "status": l.status.value, "is_featured": is_listing_currently_featured(l),
        })
    return rows


def _top_sellers() -> tuple[list[dict], dict]:
    """Top sellers by weighted engagement score. Returns (rows, per-seller agg)."""
    listing_rows = db.session.execute(
        db.select(
            Listing.seller_id,
            func.count(Listing.id),
            func.coalesce(func.sum(Listing.view_count), 0),
            func.coalesce(func.sum(Listing.favorite_count), 0),
        ).where(Listing.deleted_at.is_(None)).group_by(Listing.seller_id)
    ).all()
    offers_by_seller = {sid: c for sid, c in db.session.execute(
        db.select(Offer.seller_id, func.count()).group_by(Offer.seller_id)
    ).all()}

    scored = []
    for seller_id, n_listings, views, favorites in listing_rows:
        offers = offers_by_seller.get(seller_id, 0)
        scored.append({
            "seller_id": seller_id, "listings": int(n_listings), "views": int(views),
            "favorites": int(favorites), "offers": int(offers),
            "score": _seller_score(int(views), int(favorites), int(offers)),
        })
    scored.sort(key=lambda s: s["score"], reverse=True)
    top = scored[:TOP_LIMIT]

    ids = [s["seller_id"] for s in top]
    users = {u.id: u for u in db.session.scalars(
        db.select(User).options(selectinload(User.profile)).where(User.id.in_(ids))
    )} if ids else {}
    premium_ids = get_premium_seller_ids(ids) if ids else set()
    for s in top:
        u = users.get(s["seller_id"])
        s["name"] = (u.profile.full_name if u and u.profile else (u.email if u else "—"))
        s["public_id"] = u.public_id if u else None  # for the "View Profile" link (no extra query)
        s["is_premium"] = s["seller_id"] in premium_ids
    return top, {"count": len(scored)}


def _premium(overview: dict) -> dict:
    now = utcnow()
    active_ids = set(db.session.scalars(
        db.select(func.distinct(Subscription.user_id))
        .where(Subscription.status == SubscriptionStatus.ACTIVE, Subscription.end_date > now)
    ).all())
    expired_ids = set(db.session.scalars(
        db.select(func.distinct(Subscription.user_id))
        .where(Subscription.status == SubscriptionStatus.EXPIRED)
    ).all()) - active_ids

    featured_untils = db.session.scalars(
        db.select(Listing.featured_until).where(_currently_featured())
    ).all()
    if featured_untils:
        avg_days = round(sum((fu - now).total_seconds() for fu in featured_untils) / len(featured_untils) / 86400, 1)
    else:
        avg_days = 0.0
    # Share of featured listings owned by premium sellers.
    if active_ids:
        featured_by_premium = db.session.scalar(
            db.select(func.count()).select_from(Listing)
            .where(_currently_featured(), Listing.seller_id.in_(active_ids))
        ) or 0
    else:
        featured_by_premium = 0

    total_users = overview["total_users"]
    premium_members = len(active_ids)
    return {
        "active_members": premium_members,
        "expired_members": len(expired_ids),
        "featured_listings": overview["featured_listings"],
        "avg_featured_days_remaining": avg_days,
        "adoption_pct": _pct(premium_members, total_users),
        "conversion_pct": _pct(premium_members, total_users),
        "featured_owned_by_premium_pct": _pct(featured_by_premium, overview["featured_listings"]),
        "_active_ids": active_ids,  # internal, used by insights
    }


def _advertisements() -> dict:
    now = utcnow()
    row = db.session.execute(
        db.select(
            func.count(Advertisement.id),
            func.coalesce(func.sum(Advertisement.impression_count), 0),
            func.coalesce(func.sum(Advertisement.click_count), 0),
            func.coalesce(func.sum(case((db.and_(
                Advertisement.is_active.is_(True),
                db.or_(Advertisement.start_at.is_(None), Advertisement.start_at <= now),
                db.or_(Advertisement.end_at.is_(None), Advertisement.end_at >= now),
            ), 1), else_=0)), 0),
            func.coalesce(func.sum(case((db.and_(
                Advertisement.end_at.isnot(None), Advertisement.end_at < now
            ), 1), else_=0)), 0),
        )
    ).one()
    total, impressions, clicks, active, expired = (int(x) for x in row)
    return {
        "total": total,
        "active": active,
        "expired": expired,
        "impressions": impressions,   # tracked via impression_count
        "clicks": clicks,             # tracked via click_count
        "ctr": round(clicks / impressions * 100, 2) if impressions else None,
    }


def _moderation() -> dict:
    deleted = db.session.scalar(
        db.select(func.count(Listing.id)).where(Listing.deleted_at.isnot(None))
    ) or 0
    reserved = db.session.scalar(
        db.select(func.count(Listing.id)).where(
            Listing.status == ListingStatus.RESERVED, Listing.deleted_at.is_(None))
    ) or 0
    sold = db.session.scalar(
        db.select(func.count(Listing.id)).where(
            Listing.status == ListingStatus.SOLD, Listing.deleted_at.is_(None))
    ) or 0
    reports = get_report_summary()
    return {
        "reports_available": True,
        "reported_listings": reports["pending"] + reports["under_review"],
        "pending_reports": reports["pending"],
        "resolved_reports": reports["resolved"],
        "dismissed_reports": reports["dismissed"],
        "deleted_listings": int(deleted),
        "reserved_listings": int(reserved),
        "sold_listings": int(sold),
    }


def _traffic(start_dt: datetime) -> dict:
    dow_expr, hour_expr = _dow_hour_exprs()
    day_rows = db.session.execute(
        db.select(dow_expr, func.count()).where(ListingView.viewed_at >= start_dt).group_by(dow_expr)
    ).all()
    hour_rows = db.session.execute(
        db.select(hour_expr, func.count()).where(ListingView.viewed_at >= start_dt).group_by(hour_expr)
    ).all()
    day_counts = {int(d): int(c) for d, c in day_rows if d is not None}
    hour_counts = {int(h): int(c) for h, c in hour_rows if h is not None}
    from app.premium.analytics import _DOW_NAMES

    most_active_day = _DOW_NAMES[max(day_counts, key=day_counts.get)] if day_counts else None
    peak_hour = max(hour_counts, key=hour_counts.get) if hour_counts else None

    total_views_30d = db.session.scalar(
        db.select(func.count()).select_from(ListingView).where(ListingView.viewed_at >= start_dt)
    ) or 0

    # Unique / returning visitors (platform-wide).
    logged_in = db.session.execute(
        db.select(ListingView.viewer_id, func.count())
        .where(ListingView.viewed_at >= start_dt, ListingView.viewer_id.isnot(None))
        .group_by(ListingView.viewer_id)
    ).all()
    anon_key = func.coalesce(ListingView.session_id, ListingView.ip_hash)
    anonymous = db.session.execute(
        db.select(anon_key, func.count())
        .where(ListingView.viewed_at >= start_dt, ListingView.viewer_id.is_(None))
        .group_by(anon_key)
    ).all()
    visits = [c for _, c in logged_in] + [c for k, c in anonymous if k is not None]
    unique_visitors = len(visits)
    returning_visitors = sum(1 for c in visits if c > 1)

    # Top viewed categories (30 days). Category id is selected purely to build a
    # drill-down link — the counts are unchanged.
    cat_rows = db.session.execute(
        db.select(Category.id, Category.name, func.count())
        .select_from(ListingView)
        .join(Listing, ListingView.listing_id == Listing.id)
        .join(Category, Listing.category_id == Category.id)
        .where(ListingView.viewed_at >= start_dt)
        .group_by(Category.id, Category.name)
        .order_by(func.count().desc())
    ).all()

    return {
        "most_active_day": most_active_day,
        "peak_hour": peak_hour,
        "peak_hour_label": _hour_label(peak_hour) if peak_hour is not None else None,
        "total_views_30d": int(total_views_30d),
        "unique_visitors": unique_visitors,
        "returning_visitors": returning_visitors,
        "returning_pct": _pct(returning_visitors, unique_visitors),
        "top_category": cat_rows[0][1] if cat_rows else None,
        "top_category_id": cat_rows[0][0] if cat_rows else None,
        "top_categories": [(name, int(c)) for _cid, name, c in cat_rows[:CAT_CHART_LIMIT]],
    }


def _status_distribution() -> list[tuple[str, int]]:
    rows = db.session.execute(
        db.select(Listing.status, func.count()).where(Listing.deleted_at.is_(None)).group_by(Listing.status)
    ).all()
    return [(status.value if hasattr(status, "value") else str(status), int(c)) for status, c in rows]


def _insights(overview, health, traffic, premium) -> list[str]:
    insights: list[str] = []
    new_users = overview.get("new_users_30d", 0)
    if new_users > 0:
        insights.append(f"Marketplace grew by {new_users} new users this month.")
    if traffic["top_category"]:
        insights.append(f"{traffic['top_category']} received the highest traffic.")
    if traffic["most_active_day"]:
        insights.append(f"{traffic['most_active_day']} generated the most activity.")
    if premium["featured_owned_by_premium_pct"] > 0:
        insights.append(
            f"Premium members own {premium['featured_owned_by_premium_pct']}% of featured listings."
        )
    if health["avg_views_per_listing"] > 0:
        insights.append(f"Average listing receives {round(health['avg_views_per_listing'])} views.")
    return insights


def get_admin_dashboard() -> dict:
    """Assemble the full admin analytics dashboard (platform-wide)."""
    now = utcnow()
    today = now.date()
    start_date = today - timedelta(days=TREND_WINDOW_DAYS - 1)
    start_dt = datetime.combine(start_date, time.min)

    overview = _overview()

    # 30-day trend series.
    users_series = _daily_counts(User.created_at, start_date)
    listings_series = _daily_counts(Listing.created_at, start_date)
    views_series = _daily_counts(ListingView.viewed_at, start_date)
    messages_series = _daily_counts(Message.created_at, start_date, Message.deleted_at.is_(None))
    offers_series = _daily_counts(Offer.created_at, start_date)
    overview["new_users_30d"] = sum(d["count"] for d in users_series)

    health = _health(overview)
    offers_by_listing = _offers_by_listing()
    top_listings = _top_listings(offers_by_listing)
    top_sellers, _ = _top_sellers()
    premium = _premium(overview)
    advertisements = _advertisements()
    moderation = _moderation()
    traffic = _traffic(start_dt)
    status_dist = _status_distribution()
    insights = _insights(overview, health, traffic, premium)
    moderation_analytics = get_moderation_analytics()

    premium.pop("_active_ids", None)  # drop internal key before returning

    charts = {
        "users": _series_arrays(users_series),
        "listings": _series_arrays(listings_series),
        "views": _series_arrays(views_series),
        "messages": _series_arrays(messages_series),
        "offers": _series_arrays(offers_series),
        "top_categories": {
            "labels": [name for name, _ in traffic["top_categories"]],
            "values": [c for _, c in traffic["top_categories"]],
        },
        "top_sellers": {
            "labels": [s["name"] for s in top_sellers],
            "values": [s["score"] for s in top_sellers],
        },
        "status": {
            "labels": [s.title() for s, _ in status_dist],
            "values": [c for _, c in status_dist],
        },
        "premium_free": {
            "labels": ["Premium", "Free"],
            "values": [overview["premium_members"], max(overview["total_users"] - overview["premium_members"], 0)],
        },
        "reports": moderation_analytics["trend"],
    }

    return {
        "overview": overview,
        "activity": {
            "new_users": users_series, "new_listings": listings_series,
            "views": views_series, "messages": messages_series, "offers": offers_series,
        },
        "health": health,
        "top_listings": top_listings,
        "top_sellers": top_sellers,
        "premium": premium,
        "advertisements": advertisements,
        "moderation": moderation,
        "moderation_analytics": moderation_analytics,
        "recent_reports": get_recent_reports(limit=5),
        "traffic": traffic,
        "insights": insights,
        "recent_admin_activity": get_recent_admin_activity(limit=5),
        "charts": charts,
    }