"""Admin dashboard aggregates."""

from __future__ import annotations

from datetime import datetime, timezone
from zoneinfo import ZoneInfo

from sqlalchemy import func
from sqlalchemy.orm import Session, joinedload

from app.models import User, UserRole
from app.models_admin import CommunityPost, MemberNotification, MemberNotificationType, Reward
from app.services.admin_service import serialize_community_post


def _format_activity_datetime(dt: datetime | None) -> str | None:
    if dt is None:
        return None
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    return dt.astimezone(ZoneInfo("Europe/London")).strftime("%d %b, %Y %I:%M %p").lstrip("0")


def get_admin_dashboard(db: Session, *, period: str = "monthly") -> dict:
    period_key = (period or "monthly").strip().lower()
    if period_key not in {"monthly", "weekly"}:
        period_key = "monthly"

    total_members = (
        db.query(func.count(User.id))
        .filter(User.role == UserRole.user, User.is_active.is_(True))
        .scalar()
        or 0
    )

    total_badges = db.query(func.count(Reward.id)).scalar() or 0
    achievement_count = (
        db.query(func.count(MemberNotification.id))
        .filter(MemberNotification.notification_type == MemberNotificationType.achievement.value)
        .scalar()
        or 0
    )
    if achievement_count > total_badges:
        total_badges = achievement_count

    total_community = db.query(func.count(CommunityPost.id)).scalar() or 0

    posts = (
        db.query(CommunityPost)
        .options(joinedload(CommunityPost.created_by))
        .order_by(CommunityPost.created_at.desc(), CommunityPost.id.desc())
        .limit(10)
        .all()
    )
    recent_activity = []
    for post in posts:
        serialized = serialize_community_post(post)
        recent_activity.append(
            {
                "id": serialized["id"],
                "title": serialized["title"],
                "description": serialized["content"],
                "post_type": serialized["post_type"],
                "media_url": serialized.get("media_url"),
                "has_media": serialized.get("has_media", False),
                "created_at": serialized["created_at"],
                "created_at_label": _format_activity_datetime(serialized["created_at"]),
                "created_by_name": serialized.get("created_by_name"),
            }
        )

    return {
        "period": period_key,
        "summary": {
            "total_members": int(total_members),
            "sessions_completed": 0,
            "total_badges": int(total_badges),
            "total_community": int(total_community),
        },
        "session_completions": {
            "period": period_key,
            "labels": [],
            "values": [],
        },
        "members_by_level": {
            "labels": [],
            "values": [],
        },
        "recent_community_activity": recent_activity,
    }