"""Premium analytics dashboard service.

Builds the entire premium analytics view for a seller in a fixed, small number
of aggregation queries — never one-per-listing — so the dashboard stays fast
with hundreds of listings. All calculation (totals, averages, the featured-vs-
average multiplier, chart series, recent-activity text) happens here; the
template only renders the returned dictionary.

Data sources:
* views  — ``Listing.view_count`` (the only source; no view-event table)
* favorites — ``Listing.favorite_count`` (kept in sync by the favorite service)
* offers — counted from the ``offers`` table (the denormalised counter is unused)
* conversations — counted from the ``conversations`` table
"""

from __future__ import annotations

from datetime import datetime, time, timedelta

from sqlalchemy import Integer, case, cast, func

from app.extensions import db
from app.marketplace.services import (
    featured_days_remaining,
    get_featured_listing,
    is_listing_currently_featured,
)
from app.models import (
    Conversation,
    Favorite,
    Listing,
    ListingStatus,
    ListingView,
    Offer,
    User,
)
from app.models.base import utcnow

# How many listings feed the "top performing" list and the charts.
TOP_LISTINGS_LIMIT = 5
CHART_LISTINGS_LIMIT = 10
RECENT_ACTIVITY_LIMIT = 5
# Historical window for trend analytics.
TREND_WINDOW_DAYS = 30

_DOW_NAMES = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"]


def _hour_label(hour: int) -> str:
    """Format a 0–23 hour as e.g. '7 PM'."""
    period = "AM" if hour < 12 else "PM"
    twelve = hour % 12 or 12
    return f"{twelve} {period}"


def _cover_path(listing: Listing) -> str | None:
    """First image path for a listing, or None (images are eager-loaded)."""
    return listing.images[0].image_path if listing.images else None


def _offers_per_listing(user: User) -> dict[int, int]:
    """``{listing_id: offer_count}`` for the user's listings — one GROUP BY."""
    rows = db.session.execute(
        db.select(Offer.listing_id, func.count())
        .where(Offer.seller_id == user.id)
        .group_by(Offer.listing_id)
    ).all()
    return {listing_id: count for listing_id, count in rows}


def _account_summary(user: User) -> dict:
    """Account-level totals in a single aggregation query (+ two counts)."""
    now = utcnow()
    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(
                        (
                            db.and_(
                                Listing.is_featured.is_(True),
                                Listing.featured_until.isnot(None),
                                Listing.featured_until > now,
                            ),
                            1,
                        ),
                        else_=0,
                    )
                ),
                0,
            ),
        ).where(Listing.seller_id == user.id, Listing.deleted_at.is_(None))
    ).one()
    total_listings, total_views, total_favorites, active_listings, featured_listings = row

    total_offers = db.session.scalar(
        db.select(func.count()).select_from(Offer).where(Offer.seller_id == user.id)
    ) or 0
    total_conversations = db.session.scalar(
        db.select(func.count()).select_from(Conversation).where(Conversation.seller_id == user.id)
    ) or 0

    return {
        "total_listings": int(total_listings or 0),
        "active_listings": int(active_listings or 0),
        "featured_listings": int(featured_listings or 0),
        "total_views": int(total_views or 0),
        "total_favorites": int(total_favorites or 0),
        "total_offers": int(total_offers),
        "total_conversations": int(total_conversations),
    }


def _top_listings(user: User, offers_by_listing: dict[int, int]) -> list[Listing]:
    """The user's listings, most-viewed first, with images eager-loaded."""
    from sqlalchemy.orm import selectinload

    return list(
        db.session.scalars(
            db.select(Listing)
            .options(selectinload(Listing.images))
            .where(Listing.seller_id == user.id, Listing.deleted_at.is_(None))
            .order_by(Listing.view_count.desc(), Listing.created_at.desc())
            .limit(CHART_LISTINGS_LIMIT)
        )
    )


def _listing_row(listing: Listing, offers_by_listing: dict[int, int]) -> dict:
    return {
        "public_id": listing.public_id,
        "title": listing.title,
        "price": listing.price,
        "image": _cover_path(listing),
        "views": listing.view_count or 0,
        "favorites": listing.favorite_count or 0,
        "offers": offers_by_listing.get(listing.id, 0),
        "status": listing.status.value,
        "is_featured": is_listing_currently_featured(listing),
    }


def _featured_card(user: User, offers_by_listing: dict[int, int]) -> dict | None:
    """Details for the user's currently-featured listing, or None."""
    listing = get_featured_listing(user)
    if listing is None:
        return None
    conversations = db.session.scalar(
        db.select(func.count())
        .select_from(Conversation)
        .where(Conversation.listing_id == listing.id)
    ) or 0
    return {
        "public_id": listing.public_id,
        "title": listing.title,
        "price": listing.price,
        "image": _cover_path(listing),
        "featured_until": listing.featured_until,
        "days_remaining": featured_days_remaining(listing),
        "views": listing.view_count or 0,
        "favorites": listing.favorite_count or 0,
        "offers": offers_by_listing.get(listing.id, 0),
        "conversations": int(conversations),
        "status": listing.status.value,
    }


def _comparison(summary: dict, featured: dict | None) -> dict | None:
    """Average views vs the featured listing. None when there's no featured
    listing or no data to compare against (division handled safely)."""
    if featured is None or summary["total_listings"] == 0:
        return None
    average_views = summary["total_views"] / summary["total_listings"]
    if average_views <= 0:
        return None
    multiplier = round(featured["views"] / average_views, 1)
    return {
        "average_views": int(round(average_views)),
        "featured_views": featured["views"],
        "multiplier": multiplier,
    }


def _charts(listings_rows: list[dict]) -> dict:
    """JSON-ready series for the per-listing bar charts (already O(1) data)."""

    def _short(title: str) -> str:
        return title if len(title) <= 22 else title[:21] + "…"

    return {
        "labels": [_short(r["title"]) for r in listings_rows],
        "views": [r["views"] for r in listings_rows],
        "favorites": [r["favorites"] for r in listings_rows],
        "offers": [r["offers"] for r in listings_rows],
    }


def _recent_activity(user: User) -> list[dict]:
    """Merged latest-N activity across favorites, offers and new chats.

    Three bounded queries (each LIMIT-ed), merged and re-sorted in memory — no
    per-row lookups. Summarises existing data; not a notification system.
    """
    items: list[dict] = []

    favorites = db.session.execute(
        db.select(Favorite.created_at, Listing.title)
        .join(Listing, Favorite.listing_id == Listing.id)
        .where(Listing.seller_id == user.id)
        .order_by(Favorite.created_at.desc())
        .limit(RECENT_ACTIVITY_LIMIT)
    ).all()
    for created_at, title in favorites:
        items.append({
            "type": "favorite",
            "icon": "bi-heart-fill",
            "text": f"Someone favorited {title}",
            "timestamp": created_at,
        })

    offers = db.session.execute(
        db.select(Offer.created_at, Offer.amount, Listing.title)
        .join(Listing, Offer.listing_id == Listing.id)
        .where(Offer.seller_id == user.id)
        .order_by(Offer.created_at.desc())
        .limit(RECENT_ACTIVITY_LIMIT)
    ).all()
    for created_at, amount, title in offers:
        items.append({
            "type": "offer",
            "icon": "bi-tag-fill",
            "text": f"Offer received: ₹{amount:,.0f} on {title}",
            "timestamp": created_at,
        })

    chats = db.session.execute(
        db.select(Conversation.created_at, Listing.title)
        .join(Listing, Conversation.listing_id == Listing.id)
        .where(Conversation.seller_id == user.id)
        .order_by(Conversation.created_at.desc())
        .limit(RECENT_ACTIVITY_LIMIT)
    ).all()
    for created_at, title in chats:
        items.append({
            "type": "chat",
            "icon": "bi-chat-dots-fill",
            "text": f"New chat started on {title}",
            "timestamp": created_at,
        })

    items.sort(key=lambda i: i["timestamp"], reverse=True)
    return items[:RECENT_ACTIVITY_LIMIT]


# --- Historical / trend analytics (Phase 4.2) ------------------------------

def _user_listing_ids(user: User):
    """Scalar subquery of the user's non-deleted listing ids (for view scoping)."""
    return db.select(Listing.id).where(
        Listing.seller_id == user.id, Listing.deleted_at.is_(None)
    )


def _dow_hour_exprs():
    """Dialect-safe day-of-week (0=Sunday) and hour expressions for GROUP BY."""
    name = db.engine.dialect.name
    if name == "sqlite":
        dow = cast(func.strftime("%w", ListingView.viewed_at), Integer)
        hour = cast(func.strftime("%H", ListingView.viewed_at), Integer)
    elif name in ("mysql", "mariadb"):
        dow = func.dayofweek(ListingView.viewed_at) - 1  # MySQL: 1=Sun..7=Sat
        hour = func.hour(ListingView.viewed_at)
    else:  # postgres / other
        dow = cast(func.extract("dow", ListingView.viewed_at), Integer)
        hour = cast(func.extract("hour", ListingView.viewed_at), Integer)
    return dow, hour


def _daily_views(user: User, start_date, days: int = TREND_WINDOW_DAYS) -> list[dict]:
    """Zero-filled daily view series (SQL GROUP BY date) for ``days`` days."""
    start_dt = datetime.combine(start_date, time.min)
    rows = db.session.execute(
        db.select(func.date(ListingView.viewed_at), func.count())
        .where(
            ListingView.listing_id.in_(_user_listing_ids(user)),
            ListingView.viewed_at >= start_dt,
        )
        .group_by(func.date(ListingView.viewed_at))
    ).all()
    counts = {str(day)[:10]: int(n) for day, n in rows}
    series = []
    for i in range(days):
        iso = (start_date + timedelta(days=i)).isoformat()
        series.append({"date": iso, "views": counts.get(iso, 0)})
    return series


def _visitor_stats(user: User, start_dt: datetime) -> dict:
    """Unique / returning visitor counts over the window.

    Two GROUP BY queries (logged-in by ``viewer_id``; anonymous by
    ``coalesce(session_id, ip_hash)``) — reduced to per-visitor rows, never
    per-view. A returning visitor has more than one view in the window.
    """
    scope = ListingView.listing_id.in_(_user_listing_ids(user))
    logged_in = db.session.execute(
        db.select(ListingView.viewer_id, func.count())
        .where(scope, 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(scope, ListingView.viewed_at >= start_dt, ListingView.viewer_id.is_(None))
        .group_by(anon_key)
    ).all()

    counts = [c for _, c in logged_in] + [c for k, c in anonymous if k is not None]
    unique_visitors = len(counts)
    returning_visitors = sum(1 for c in counts if c > 1)
    returning_pct = round(returning_visitors / unique_visitors * 100, 1) if unique_visitors else 0.0
    return {
        "total_visits": sum(counts),
        "unique_visitors": unique_visitors,
        "returning_visitors": returning_visitors,
        "returning_pct": returning_pct,
        "new_pct": round(100 - returning_pct, 1) if unique_visitors else 0.0,
    }


def _trends(daily_views: list[dict], visitor_stats: dict) -> dict:
    """Summary trend cards — derived from the daily series (no extra queries)."""
    views_30d = sum(d["views"] for d in daily_views)
    return {
        "views_today": daily_views[-1]["views"] if daily_views else 0,
        "views_7d": sum(d["views"] for d in daily_views[-7:]),
        "views_30d": views_30d,
        "avg_views_per_day": round(views_30d / len(daily_views), 1) if daily_views else 0.0,
        **visitor_stats,
    }


def _traffic(user: User, start_dt: datetime, summary: dict, top_rows: list[dict]) -> dict:
    """Peak day/hour and per-listing averages (SQL GROUP BY on dow/hour)."""
    scope = ListingView.listing_id.in_(_user_listing_ids(user))
    dow_expr, hour_expr = _dow_hour_exprs()
    day_rows = db.session.execute(
        db.select(dow_expr, func.count())
        .where(scope, ListingView.viewed_at >= start_dt).group_by(dow_expr)
    ).all()
    hour_rows = db.session.execute(
        db.select(hour_expr, func.count())
        .where(scope, 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}

    most_active_day = least_active_day = peak_hour = peak_hour_label = None
    if day_counts:
        full = {i: day_counts.get(i, 0) for i in range(7)}
        most_active_day = _DOW_NAMES[max(full, key=full.get)]
        least_active_day = _DOW_NAMES[min(full, key=full.get)]
    if hour_counts:
        peak_hour = max(hour_counts, key=hour_counts.get)
        peak_hour_label = _hour_label(peak_hour)

    total_listings = summary["total_listings"]
    avg_views_per_listing = round(summary["total_views"] / total_listings) if total_listings else 0
    return {
        "most_active_day": most_active_day,
        "least_active_day": least_active_day,
        "peak_hour": peak_hour,
        "peak_hour_label": peak_hour_label,
        "avg_views_per_listing": avg_views_per_listing,
        "top_listing": top_rows[0]["title"] if top_rows else None,
    }


def _conversion(summary: dict) -> list[dict]:
    """Views → Favorites → Chats → Offers funnel with stage conversion %s."""
    def pct(numerator, denominator):
        return round(numerator / denominator * 100, 1) if denominator else 0.0

    views = summary["total_views"]
    favorites = summary["total_favorites"]
    chats = summary["total_conversations"]
    offers = summary["total_offers"]
    return [
        {"label": "Views", "value": views, "pct": None},
        {"label": "Favorites", "value": favorites, "pct": pct(favorites, views)},
        {"label": "Chats", "value": chats, "pct": pct(chats, favorites)},
        {"label": "Offers", "value": offers, "pct": pct(offers, chats)},
    ]


def _build_insights(summary, comparison, traffic, visitors, top_rows) -> list[str]:
    """Data-driven text insights (nothing hardcoded); empty parts are skipped."""
    insights: list[str] = []
    if comparison:
        insights.append(
            f"Your featured listing receives {comparison['multiplier']}× more views "
            f"than your average listing."
        )
    if traffic["most_active_day"]:
        insights.append(f"{traffic['most_active_day']} generates the highest traffic.")
    if traffic["peak_hour"] is not None:
        nxt = _hour_label((traffic["peak_hour"] + 1) % 24)
        insights.append(f"Most visitors arrive between {traffic['peak_hour_label']} and {nxt}.")
    if top_rows and summary["total_views"] > 0:
        share = round(top_rows[0]["views"] / summary["total_views"] * 100)
        if share > 0:
            insights.append(f"Your top listing accounts for {share}% of all views.")
    if visitors["unique_visitors"] > 0 and visitors["returning_visitors"] > 0:
        insights.append(
            f"Returning visitors represent {visitors['returning_pct']}% of your audience."
        )
    return insights


def get_premium_dashboard(user: User) -> dict:
    """Return the full premium analytics dashboard for ``user``.

    A fixed, small number of aggregation queries regardless of listing count
    (no N+1). The returned dict is everything the template needs — no
    calculation happens in the view.
    """
    offers_by_listing = _offers_per_listing(user)
    summary = _account_summary(user)

    listings = _top_listings(user, offers_by_listing)
    listing_rows = [_listing_row(listing, offers_by_listing) for listing in listings]

    featured = _featured_card(user, offers_by_listing)
    comparison = _comparison(summary, featured)

    # Historical / trend analytics over the last 30 days.
    today = utcnow().date()
    start_date = today - timedelta(days=TREND_WINDOW_DAYS - 1)
    start_dt = datetime.combine(start_date, time.min)

    daily_views = _daily_views(user, start_date)
    visitors = _visitor_stats(user, start_dt)
    trends = _trends(daily_views, visitors)
    traffic = _traffic(user, start_dt, summary, listing_rows)
    conversion = _conversion(summary)
    insights = _build_insights(summary, comparison, traffic, visitors, listing_rows)

    return {
        "summary": summary,
        "featured": featured,
        "top_listings": listing_rows[:TOP_LISTINGS_LIMIT],
        "comparison": comparison,
        "recent_activity": _recent_activity(user),
        "charts": _charts(listing_rows),
        "daily_views": daily_views,
        "trends": trends,
        "traffic": traffic,
        "conversion": conversion,
        "insights": insights,
    }