nordabiz/blueprints/admin/routes_user_activity.py

"""
Admin User Activity Routes
============================

User activity dashboard - sessions, logins, page views, active users chart.
"""

import logging
from datetime import date, datetime, timedelta
from collections import defaultdict

from flask import render_template, request, redirect, url_for, flash
from flask_login import login_required
from sqlalchemy import func, desc, cast, Date

from . import bp
from database import (
    SessionLocal, User, UserSession, PageView, SystemRole
)
from utils.decorators import role_required

logger = logging.getLogger(__name__)


@bp.route('/user-activity')
@login_required
@role_required(SystemRole.ADMIN)
def admin_user_activity():
    """User activity dashboard - sessions, logins, pages, daily active users."""
    db = SessionLocal()
    try:
        today = date.today()
        start_date = today - timedelta(days=30)
        start_dt = datetime.combine(start_date, datetime.min.time())

        # ----------------------------------------------------------
        # SUMMARY STATS (last 30 days, non-bot only)
        # ----------------------------------------------------------
        summary_row = db.query(
            func.count(UserSession.id).label('total_sessions'),
            func.count(func.distinct(UserSession.user_id)).label('unique_users'),
            func.coalesce(func.avg(UserSession.duration_seconds), 0).label('avg_duration'),
        ).filter(
            UserSession.started_at >= start_dt,
            UserSession.is_bot == False,
            UserSession.user_id.isnot(None),
        ).first()

        total_sessions = summary_row.total_sessions or 0
        unique_users = summary_row.unique_users or 0
        avg_duration_sec = int(summary_row.avg_duration or 0)

        total_pageviews = db.query(func.count(PageView.id)).filter(
            PageView.viewed_at >= start_dt,
        ).scalar() or 0

        summary = {
            'total_sessions': total_sessions,
            'unique_users': unique_users,
            'avg_duration_min': round(avg_duration_sec / 60, 1) if avg_duration_sec else 0,
            'total_pageviews': total_pageviews,
        }

        # ----------------------------------------------------------
        # RECENT LOGINS (last 50 sessions with user info)
        # ----------------------------------------------------------
        recent_sessions_q = (
            db.query(
                UserSession,
                User.name.label('user_name'),
                User.email.label('user_email'),
            )
            .join(User, UserSession.user_id == User.id)
            .filter(
                UserSession.started_at >= start_dt,
                UserSession.is_bot == False,
            )
            .order_by(UserSession.started_at.desc())
            .limit(50)
            .all()
        )

        recent_sessions = []
        for sess, user_name, user_email in recent_sessions_q:
            duration_min = round((sess.duration_seconds or 0) / 60, 1)
            recent_sessions.append({
                'user_name': user_name or user_email or '?',
                'started_at': sess.started_at,
                'device_type': sess.device_type or '-',
                'browser': sess.browser or '-',
                'is_pwa': getattr(sess, 'is_pwa', False) or False,
                'duration_min': duration_min,
                'page_views_count': sess.page_views_count or 0,
            })

        # ----------------------------------------------------------
        # MOST VISITED PAGES (top 20 paths)
        # ----------------------------------------------------------
        top_pages_q = (
            db.query(
                PageView.path,
                func.count(PageView.id).label('view_count'),
                func.coalesce(func.avg(PageView.time_on_page_seconds), 0).label('avg_time'),
            )
            .filter(PageView.viewed_at >= start_dt)
            .group_by(PageView.path)
            .order_by(desc('view_count'))
            .limit(20)
            .all()
        )

        top_pages = []
        for row in top_pages_q:
            top_pages.append({
                'path': row.path,
                'view_count': row.view_count,
                'avg_time_sec': int(row.avg_time or 0),
            })

        # ----------------------------------------------------------
        # MOST ACTIVE USERS (top 20 by session count)
        # ----------------------------------------------------------
        active_users_q = (
            db.query(
                User.id,
                User.name,
                User.email,
                User.last_login,
                func.count(UserSession.id).label('session_count'),
                func.coalesce(func.sum(UserSession.duration_seconds), 0).label('total_time'),
                func.coalesce(func.sum(UserSession.page_views_count), 0).label('total_pages'),
            )
            .join(UserSession, UserSession.user_id == User.id)
            .filter(
                UserSession.started_at >= start_dt,
                UserSession.is_bot == False,
            )
            .group_by(User.id, User.name, User.email, User.last_login)
            .order_by(desc('session_count'))
            .limit(20)
            .all()
        )

        active_users = []
        for row in active_users_q:
            total_min = round((row.total_time or 0) / 60, 1)
            active_users.append({
                'name': row.name or row.email,
                'session_count': row.session_count,
                'total_time_min': total_min,
                'total_pages': row.total_pages or 0,
                'last_login': row.last_login,
            })

        # ----------------------------------------------------------
        # DAILY ACTIVE USERS (last 30 days for chart)
        # ----------------------------------------------------------
        dau_q = (
            db.query(
                cast(UserSession.started_at, Date).label('day'),
                func.count(func.distinct(UserSession.user_id)).label('users'),
            )
            .filter(
                UserSession.started_at >= start_dt,
                UserSession.is_bot == False,
                UserSession.user_id.isnot(None),
            )
            .group_by('day')
            .order_by('day')
            .all()
        )

        # Events in this period (for chart markers)
        from database import NordaEvent, EventAttendee
        events_in_range = db.query(NordaEvent).filter(
            NordaEvent.event_date >= start_date,
            NordaEvent.event_date <= start_date + timedelta(days=30),
        ).all()
        event_map = {}  # date → {'title': ..., 'attendees': ...}
        for ev in events_in_range:
            att_count = db.query(func.count(EventAttendee.id)).filter(
                EventAttendee.event_id == ev.id
            ).scalar() or 0
            event_map[ev.event_date] = {
                'title': ev.title[:30] + ('...' if len(ev.title) > 30 else ''),
                'attendees': att_count,
            }

        # Fill in missing days with 0
        dau_map = {row.day: row.users for row in dau_q}
        daily_active = []
        max_dau = 1
        for i in range(31):
            d = start_date + timedelta(days=i)
            count = dau_map.get(d, 0)
            if count > max_dau:
                max_dau = count
            is_weekend = d.weekday() >= 5  # 5=Saturday, 6=Sunday
            is_monday = d.weekday() == 0
            ev = event_map.get(d)
            daily_active.append({
                'date': d,
                'label': d.strftime('%d.%m'),
                'count': count,
                'is_weekend': is_weekend,
                'is_monday': is_monday,
                'event': ev,
            })

        # Add percentage for CSS bar heights
        for item in daily_active:
            item['pct'] = round(item['count'] / max_dau * 100) if max_dau > 0 else 0

        return render_template(
            'admin/user_activity.html',
            summary=summary,
            recent_sessions=recent_sessions,
            top_pages=top_pages,
            active_users=active_users,
            daily_active=daily_active,
        )

    except Exception as e:
        logger.error(f"User activity dashboard error: {e}", exc_info=True)
        flash('Blad ladowania aktywnosci uzytkownikow.', 'error')
        return redirect(url_for('admin.admin_users'))
    finally:
        db.close()