"""Campus Overview composition service.

Assembles the per-university admin Campus Overview read model by orchestrating
existing domain services (verification, premium) together with read-only
aggregate SQL over the marketplace, chat and moderation tables.

This module is a *composition / analytics layer*: it never mutates state and it
never duplicates business rules. Every metric is keyed on the seller/owner's
``UserProfile.university_name`` (a nullable free-text field — there is no
``University`` entity), reached through the same
``Entity -> User -> UserProfile`` join the marketplace already uses.

Routes and templates must not query models directly; they consume the dicts
returned here. New widgets (revenue, sales analytics, advertisement performance)
should be added as further methods on this layer.
"""

from __future__ import annotations

from sqlalchemy import case, func

from app.auth import verification as verification_service
from app.extensions import db
from app.models import (
    Category,
    Conversation,
    ConversationStatus,
    Favorite,
    Listing,
    ListingStatus,
    Message,
    Report,
    ReportStatus,
    StudentVerificationHistory,
    StudentVerificationStatus,
    Subscription,
    SubscriptionStatus,
    User,
    UserProfile,
)
from app.models.base import utcnow

# Open reports mirror the moderation module's OPEN_STATUSES.
_OPEN_REPORT_STATUSES = (ReportStatus.PENDING, ReportStatus.UNDER_REVIEW)


# --------------------------------------------------------------------------- #
# Shared join helpers
# --------------------------------------------------------------------------- #
def _seller_university(query):
    """Join ``Listing -> User (seller) -> UserProfile`` onto ``query``."""
    return query.join(User, Listing.seller_id == User.id).join(
        UserProfile, UserProfile.user_id == User.id
    )


def _active_premium():
    """SQL predicate for a currently-active premium subscription."""
    return db.and_(
        Subscription.status == SubscriptionStatus.ACTIVE,
        Subscription.end_date > utcnow(),
    )


def _verified_student_count(name: str) -> int:
    """Verified students at ``name`` — the denominator for adoption rates."""
    return int(
        db.session.scalar(
            db.select(func.count(UserProfile.id)).where(
                UserProfile.university_name == name,
                UserProfile.verification_status == StudentVerificationStatus.VERIFIED,
            )
        )
        or 0
    )


def _pct(part: int, whole: int) -> float:
    """Percentage of ``part`` within ``whole``, rounded to one decimal."""
    return round(part / whole * 100, 1) if whole else 0.0


# --------------------------------------------------------------------------- #
# Listings
# --------------------------------------------------------------------------- #
def get_university_listing_statistics(name: str) -> dict:
    """Listing counts by status for one university (excludes soft-deleted)."""
    S = ListingStatus
    total, active, sold, reserved, expired = db.session.execute(
        _seller_university(
            db.select(
                func.count(Listing.id),
                func.coalesce(func.sum(case((Listing.status == S.AVAILABLE, 1), else_=0)), 0),
                func.coalesce(func.sum(case((Listing.status == S.SOLD, 1), else_=0)), 0),
                func.coalesce(func.sum(case((Listing.status == S.RESERVED, 1), else_=0)), 0),
                func.coalesce(func.sum(case((Listing.status == S.EXPIRED, 1), else_=0)), 0),
            )
        ).where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
    ).one()
    return {
        "total": int(total),
        "active": int(active),
        "sold": int(sold),
        "reserved": int(reserved),
        "expired": int(expired),
    }


def get_university_top_categories(name: str, limit: int = 5) -> list[dict]:
    """Top categories by listing count for one university."""
    rows = db.session.execute(
        _seller_university(
            db.select(Category.name, func.count(Listing.id))
            .select_from(Listing)
            .join(Category, Listing.category_id == Category.id)
        )
        .where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
        .group_by(Category.id, Category.name)
        .order_by(func.count(Listing.id).desc(), Category.name.asc())
        .limit(limit)
    ).all()
    return [{"category": name_, "listings": int(count)} for name_, count in rows]


# --------------------------------------------------------------------------- #
# Premium
# --------------------------------------------------------------------------- #
def get_university_premium_statistics(name: str) -> dict:
    """Current premium members at ``name`` and adoption vs. verified students."""
    premium_members = int(
        db.session.scalar(
            db.select(func.count(func.distinct(Subscription.user_id)))
            .join(User, Subscription.user_id == User.id)
            .join(UserProfile, UserProfile.user_id == User.id)
            .where(UserProfile.university_name == name, _active_premium())
        )
        or 0
    )
    verified = _verified_student_count(name)
    return {
        "premium_members": premium_members,
        "verified_students": verified,
        "premium_rate": _pct(premium_members, verified),
    }


# --------------------------------------------------------------------------- #
# Saved listings (favourites)
# --------------------------------------------------------------------------- #
def get_university_saved_statistics(name: str) -> dict:
    """Total favourites on a university's listings and the per-listing average."""
    total_favorites = int(
        db.session.scalar(
            _seller_university(
                db.select(func.count(Favorite.id))
                .select_from(Favorite)
                .join(Listing, Favorite.listing_id == Listing.id)
            ).where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
        )
        or 0
    )
    total_listings = int(
        db.session.scalar(
            _seller_university(db.select(func.count(Listing.id))).where(
                UserProfile.university_name == name, Listing.deleted_at.is_(None)
            )
        )
        or 0
    )
    return {
        "total_favorites": total_favorites,
        "avg_per_listing": round(total_favorites / total_listings, 1) if total_listings else 0.0,
    }


# --------------------------------------------------------------------------- #
# Reports
# --------------------------------------------------------------------------- #
def get_university_report_statistics(name: str) -> dict:
    """Report counts (total / open / resolved) for sellers at one university."""
    total, pending, resolved = db.session.execute(
        db.select(
            func.count(Report.id),
            func.coalesce(
                func.sum(case((Report.status.in_(_OPEN_REPORT_STATUSES), 1), else_=0)), 0
            ),
            func.coalesce(func.sum(case((Report.status == ReportStatus.RESOLVED, 1), else_=0)), 0),
        )
        .join(User, Report.reported_user_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .where(UserProfile.university_name == name, Report.reported_user_id.isnot(None))
    ).one()
    return {"total": int(total), "pending": int(pending), "resolved": int(resolved)}


# --------------------------------------------------------------------------- #
# Messages / conversations
# --------------------------------------------------------------------------- #
def get_university_message_statistics(name: str) -> dict:
    """Conversation and message counts for sellers at one university."""
    total_conversations, active_conversations = db.session.execute(
        db.select(
            func.count(Conversation.id),
            func.coalesce(
                func.sum(case((Conversation.status == ConversationStatus.ACTIVE, 1), else_=0)), 0
            ),
        )
        .join(User, Conversation.seller_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .where(UserProfile.university_name == name)
    ).one()
    total_messages = int(
        db.session.scalar(
            db.select(func.count(Message.id))
            .join(Conversation, Message.conversation_id == Conversation.id)
            .join(User, Conversation.seller_id == User.id)
            .join(UserProfile, UserProfile.user_id == User.id)
            .where(UserProfile.university_name == name, Message.deleted_at.is_(None))
        )
        or 0
    )
    return {
        "total_conversations": int(total_conversations),
        "active_conversations": int(active_conversations),
        "total_messages": total_messages,
    }


# --------------------------------------------------------------------------- #
# Meetups (not yet implemented)
# --------------------------------------------------------------------------- #
def get_university_meetup_statistics(name: str) -> dict:
    """Meetup metrics.

    The meet-up module is not implemented yet (no model / table), so this card
    stays "Coming Soon". ``available`` gates the template; the shape matches the
    other stats dicts so live data can drop in without a redesign.
    """
    return {"available": False, "scheduled": None, "completed": None, "cancelled": None}


# --------------------------------------------------------------------------- #
# Marketplace insights (ranked tables)
# --------------------------------------------------------------------------- #
def get_university_top_sellers(name: str, limit: int = 10) -> list[dict]:
    """Top sellers at one university by total listings."""
    S = ListingStatus
    sold_expr = func.coalesce(func.sum(case((Listing.status == S.SOLD, 1), else_=0)), 0)
    rows = db.session.execute(
        db.select(
            User.public_id,
            UserProfile.full_name,
            User.email,
            func.count(Listing.id),
            func.coalesce(func.sum(case((Listing.status == S.AVAILABLE, 1), else_=0)), 0),
            sold_expr,
        )
        .join(User, Listing.seller_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
        .group_by(User.id, User.public_id, UserProfile.full_name, User.email)
        .order_by(func.count(Listing.id).desc(), sold_expr.desc(), User.id.asc())
        .limit(limit)
    ).all()
    return [
        {
            "public_id": public_id,
            "name": full_name or email,
            "listings": int(listings),
            "active": int(active),
            "sold": int(sold),
        }
        for public_id, full_name, email, listings, active, sold in rows
    ]


def get_university_popular_categories(name: str, limit: int = 5) -> list[dict]:
    """Categories ranked by listing count, with total favourites, for a university."""
    listing_count = func.count(func.distinct(Listing.id))
    rows = db.session.execute(
        db.select(Category.name, listing_count, func.count(Favorite.id))
        .select_from(Listing)
        .join(Category, Listing.category_id == Category.id)
        .join(User, Listing.seller_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .outerjoin(Favorite, Favorite.listing_id == Listing.id)
        .where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
        .group_by(Category.id, Category.name)
        .order_by(listing_count.desc(), Category.name.asc())
        .limit(limit)
    ).all()
    return [
        {"category": cat, "listings": int(listings), "favorites": int(favorites)}
        for cat, listings, favorites in rows
    ]


def get_university_most_saved_listings(name: str, limit: int = 10) -> list[dict]:
    """Listings with the most saves (favourites) for one university."""
    saves = func.count(Favorite.id)
    rows = db.session.execute(
        db.select(Listing.public_id, Listing.title, UserProfile.full_name, User.email, saves)
        .select_from(Listing)
        .join(User, Listing.seller_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .join(Favorite, Favorite.listing_id == Listing.id)
        .where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
        .group_by(Listing.id, Listing.public_id, Listing.title, UserProfile.full_name, User.email)
        .order_by(saves.desc(), Listing.id.asc())
        .limit(limit)
    ).all()
    return [
        {"public_id": public_id, "title": title, "seller": full_name or email, "saves": int(count)}
        for public_id, title, full_name, email, count in rows
    ]


def get_university_most_reported_listings(name: str, limit: int = 10) -> list[dict]:
    """Listings with the most reports for one university."""
    reports = func.count(Report.id)
    rows = db.session.execute(
        db.select(Listing.public_id, Listing.title, reports)
        .select_from(Report)
        .join(Listing, Report.listing_id == Listing.id)
        .join(User, Listing.seller_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .where(UserProfile.university_name == name)
        .group_by(Listing.id, Listing.public_id, Listing.title)
        .order_by(reports.desc(), Listing.id.asc())
        .limit(limit)
    ).all()
    return [
        {"public_id": public_id, "title": title, "reports": int(count)}
        for public_id, title, count in rows
    ]


def get_university_marketplace_insights(name: str) -> dict:
    """Ranked marketplace tables for the Marketplace Insights section."""
    return {
        "top_sellers": get_university_top_sellers(name),
        "popular_categories": get_university_popular_categories(name),
        "most_saved": get_university_most_saved_listings(name),
        "most_reported": get_university_most_reported_listings(name),
    }


# --------------------------------------------------------------------------- #
# Campus health
# --------------------------------------------------------------------------- #
def get_university_health_metrics(name: str, *, listing=None, premium=None, report=None) -> dict:
    """Health ratios for one university.

    Accepts already-computed listing/premium/report dicts to avoid re-querying
    when called from :func:`get_campus_overview`; computes them otherwise.
    """
    listing = listing if listing is not None else get_university_listing_statistics(name)
    premium = premium if premium is not None else get_university_premium_statistics(name)
    report = report if report is not None else get_university_report_statistics(name)
    verification_rate = verification_service.get_university_verification_statistics(name)[
        "verification_rate"
    ]
    return {
        "verification_rate": verification_rate,
        "premium_adoption": premium["premium_rate"],
        "report_rate": _pct(report["total"], listing["total"]),
        "active_listing_rate": _pct(listing["active"], listing["total"]),
    }


# --------------------------------------------------------------------------- #
# Merged activity feed
# --------------------------------------------------------------------------- #
def _naive(dt):
    """Drop tzinfo so naive/aware timestamps from different tables sort together."""
    return dt.replace(tzinfo=None) if dt is not None and dt.tzinfo else dt


def get_university_activity_feed(name: str, limit: int = 15) -> list[dict]:
    """Chronological feed merging verification, listing and report events.

    Reuses the verification history already recorded per university; meet-up
    events are omitted because the module is not implemented yet.
    """
    events: list[dict] = []

    for act in verification_service.get_university_recent_activity(name, limit=limit):
        events.append(
            {
                "type": "verification",
                "icon": "bi-patch-check",
                "tone": "primary",
                "text": f"{act['name']} — verification {act['action']}",
                "created_at": _naive(act["created_at"]),
            }
        )

    listings = db.session.execute(
        _seller_university(
            db.select(Listing.title, Listing.created_at, UserProfile.full_name)
        )
        .where(UserProfile.university_name == name, Listing.deleted_at.is_(None))
        .order_by(Listing.created_at.desc())
        .limit(limit)
    ).all()
    for title, created_at, seller in listings:
        events.append(
            {
                "type": "listing",
                "icon": "bi-box-seam",
                "tone": "success",
                "text": f"{seller or '—'} listed “{title}”",
                "created_at": _naive(created_at),
            }
        )

    reports = db.session.execute(
        db.select(Listing.title, Report.created_at)
        .join(Listing, Report.listing_id == Listing.id)
        .join(User, Listing.seller_id == User.id)
        .join(UserProfile, UserProfile.user_id == User.id)
        .where(UserProfile.university_name == name, Report.listing_id.isnot(None))
        .order_by(Report.created_at.desc())
        .limit(limit)
    ).all()
    for title, created_at in reports:
        events.append(
            {
                "type": "report",
                "icon": "bi-flag",
                "tone": "danger",
                "text": f"Report filed on “{title}”",
                "created_at": _naive(created_at),
            }
        )

    events.sort(key=lambda e: e["created_at"], reverse=True)
    return events[:limit]


# --------------------------------------------------------------------------- #
# Orchestration
# --------------------------------------------------------------------------- #
def get_campus_overview(name: str) -> dict:
    """Assemble the full Campus Overview marketplace read model for ``name``."""
    listing = get_university_listing_statistics(name)
    premium = get_university_premium_statistics(name)
    report = get_university_report_statistics(name)
    return {
        "listing": listing,
        "top_categories": get_university_top_categories(name),
        "premium": premium,
        "saved": get_university_saved_statistics(name),
        "report": report,
        "message": get_university_message_statistics(name),
        "meetup": get_university_meetup_statistics(name),
        "insights": get_university_marketplace_insights(name),
        "health": get_university_health_metrics(
            name, listing=listing, premium=premium, report=report
        ),
        "activity_feed": get_university_activity_feed(name),
    }