"""Student-verification service (moderated Verified-Student badge).

A student submits their university details; an administrator approves or rejects.
All business logic lives here so both the profile route (submission) and the
admin routes (moderation) stay thin. Reuses the notification service and the
verification state stored on :class:`~app.models.auth.UserProfile` — no
duplicate profile info and no new moderation table (the admin queue is a query
over ``user_profiles``).
"""

from __future__ import annotations

import re

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

from app.extensions import db
from app.models import (
    NotificationType,
    StudentVerificationHistory,
    StudentVerificationStatus,
    User,
    UserProfile,
    VerificationHistoryAction,
)
from app.models.base import utcnow
from app.notifications.services import create_notification

# Field limits (kept in sync with the model column widths).
MAX_UNIVERSITY = 150
MAX_STUDENT_ID = 60
MAX_MOBILE = 20

# A permissive international mobile format: optional leading +, then 7–15 digits
# (spaces / hyphens are stripped before checking).
_MOBILE_DIGITS = re.compile(r"^\+?\d{7,15}$")

Status = StudentVerificationStatus
Action = VerificationHistoryAction


class VerificationError(Exception):
    """Raised when a verification action violates a rule (surfaced to the user)."""


# --- Immutable history (audit trail) -------------------------------------------

def _record_history(user, action, *, status, phone=None, university=None,
                    student_id=None, reviewed_by=None, reason=None) -> None:
    """Append one immutable verification-history row. The **only** writer of the
    history table — every lifecycle event flows through here (never routes)."""
    db.session.add(StudentVerificationHistory(
        user_id=user.id, action=action, status=status,
        submitted_phone=phone, submitted_university=university, submitted_student_id=student_id,
        reviewed_by=reviewed_by, review_reason=reason,
    ))
    db.session.commit()


# --- Status helpers ------------------------------------------------------------

def _profile(user: User) -> UserProfile:
    """Return the user's profile, creating a minimal one if somehow missing."""
    if user.profile is None:
        user.profile = UserProfile(full_name=user.email.split("@")[0])
        db.session.commit()
    return user.profile


def can_submit(user: User) -> bool:
    """A student may submit whenever a request isn't already awaiting review.

    Verified students may submit an **update** to their identity information: the
    submission drops them to PENDING (badge removed) for re-review — the same
    single write path, no separate flow. Only PENDING blocks a new submission."""
    return user.student_verification_status != Status.PENDING


# --- Validation ----------------------------------------------------------------

def _validate(mobile: str, university: str, student_id: str) -> tuple[str, str, str]:
    """Trim and validate the three required fields. Raises VerificationError."""
    mobile = (mobile or "").strip()
    university = (university or "").strip()
    student_id = (student_id or "").strip()

    if not mobile:
        raise VerificationError("Mobile number is required.")
    cleaned_mobile = re.sub(r"[\s\-]", "", mobile)
    if not _MOBILE_DIGITS.match(cleaned_mobile):
        raise VerificationError("Enter a valid mobile number (7–15 digits, optional +).")
    if len(mobile) > MAX_MOBILE:
        raise VerificationError("Mobile number is too long.")
    if not university:
        raise VerificationError("University name is required.")
    if len(university) > MAX_UNIVERSITY:
        raise VerificationError("University name is too long.")
    if not student_id:
        raise VerificationError("University student ID is required.")
    if len(student_id) > MAX_STUDENT_ID:
        raise VerificationError("Student ID is too long.")

    return mobile, university, student_id


# --- Student action ------------------------------------------------------------

def submit_verification(user: User, mobile: str, university: str, student_id: str) -> UserProfile:
    """Submit (or resubmit / update) a verification request → status PENDING.

    Single write path for first-time submission, rejected resubmission and a
    verified student updating their details. Verified users transition to PENDING
    here (badge removed) — nothing is changed before this call. Blocks only a
    duplicate pending submission. Server-side validated. Notifies on success."""
    if not can_submit(user):
        raise VerificationError("Your verification request is already pending review.")

    mobile, university, student_id = _validate(mobile, university, student_id)

    # Distinguish a first-time/rejected submission from a verified user updating.
    prior_status = user.student_verification_status

    profile = _profile(user)
    profile.phone_number = mobile                 # mobile reuses the profile phone field
    profile.university_name = university
    profile.university_student_id = student_id
    profile.verification_status = Status.PENDING
    profile.verification_submitted_at = utcnow()
    profile.verification_reason = None            # clear any prior rejection note
    profile.verification_reviewed_at = None
    profile.verification_reviewed_by = None
    db.session.commit()

    action = Action.UPDATED if prior_status == Status.VERIFIED else Action.SUBMITTED
    _record_history(user, action, status=Status.PENDING,
                    phone=mobile, university=university, student_id=student_id)

    create_notification(
        user, NotificationType.SYSTEM,
        "Verification submitted",
        "Your verification request has been submitted and is awaiting administrator review.",
    )
    return profile


# --- Admin actions -------------------------------------------------------------

def approve_verification(user: User, admin: User) -> UserProfile:
    """Approve a pending request → VERIFIED, enabling the badge. Records the admin."""
    profile = user.profile
    if profile is None or profile.verification_status != Status.PENDING:
        raise VerificationError("This verification request is not pending review.")

    now = utcnow()
    profile.verification_status = Status.VERIFIED
    profile.verification_reviewed_at = now
    profile.verification_reviewed_by = admin.id
    profile.verification_reason = None
    db.session.commit()

    _record_history(user, Action.APPROVED, status=Status.VERIFIED, reviewed_by=admin.id)

    create_notification(
        user, NotificationType.SYSTEM,
        "You're a Verified Student",
        "Congratulations! Your account has been verified.",
    )
    return profile


def reject_verification(user: User, admin: User, reason: str | None = None) -> UserProfile:
    """Reject a pending request → REJECTED. Stores the optional reason; the badge
    stays off and the student may edit and resubmit."""
    profile = user.profile
    if profile is None or profile.verification_status != Status.PENDING:
        raise VerificationError("This verification request is not pending review.")

    profile.verification_status = Status.REJECTED
    profile.verification_reviewed_at = utcnow()
    profile.verification_reviewed_by = admin.id
    profile.verification_reason = (reason or "").strip() or None
    db.session.commit()

    _record_history(user, Action.REJECTED, status=Status.REJECTED,
                    reviewed_by=admin.id, reason=profile.verification_reason)

    message = "Your verification request was not approved."
    if profile.verification_reason:
        message += f" Reason: {profile.verification_reason}"
    message += " You can update your details and resubmit."
    create_notification(user, NotificationType.SYSTEM, "Verification not approved", message)
    return profile


# --- Admin queries -------------------------------------------------------------

def get_pending_verifications(university: str | None = None):
    """Users with a PENDING verification request, oldest first (review queue).

    ``university`` optionally narrows the queue to one campus (used by the
    clickable university names on the analytics dashboard)."""
    query = (
        db.select(User)
        .join(UserProfile, UserProfile.user_id == User.id)
        .where(UserProfile.verification_status == Status.PENDING)
    )
    if university:
        query = query.where(UserProfile.university_name == university)
    return db.session.scalars(query.order_by(UserProfile.verification_submitted_at.asc())).all()


def get_pending_count() -> int:
    return _status_count(Status.PENDING)


def get_verified_count() -> int:
    return _status_count(Status.VERIFIED)


def get_rejected_count() -> int:
    return _status_count(Status.REJECTED)


def _status_count(status) -> int:
    return db.session.scalar(
        db.select(db.func.count(UserProfile.id)).where(UserProfile.verification_status == status)
    ) or 0


def _total_submitted() -> int:
    """Distinct students who have ever submitted (currently pending/verified/
    rejected). Resubmissions don't inflate this — it's per student — so the rate
    stays accurate across resubmissions."""
    return db.session.scalar(
        db.select(db.func.count(UserProfile.id))
        .where(UserProfile.verification_status != Status.NOT_VERIFIED)
    ) or 0


def get_verification_rate() -> float:
    """Verified students ÷ total who ever submitted, as a percentage (1 dp)."""
    total = _total_submitted()
    return round(get_verified_count() / total * 100, 1) if total else 0.0


def _avg_days_from_pairs(rows):
    """Average whole-day gap for ``(submitted_at, reviewed_at)`` pairs, or None."""
    deltas = [(reviewed - submitted).total_seconds() for submitted, reviewed in rows if reviewed >= submitted]
    if not deltas:
        return None
    return round(sum(deltas) / len(deltas) / 86400, 1)


def get_average_review_time(university: str | None = None):
    """Average days between submission and review across reviewed requests, or
    ``None`` when there isn't enough data. Loads only the two timestamp columns
    of reviewed profiles (never all users), so it stays cheap at scale.
    ``university`` optionally scopes the average to one campus."""
    query = db.select(UserProfile.verification_submitted_at, UserProfile.verification_reviewed_at).where(
        UserProfile.verification_reviewed_at.isnot(None),
        UserProfile.verification_submitted_at.isnot(None),
    )
    if university:
        query = query.where(UserProfile.university_name == university)
    return _avg_days_from_pairs(db.session.execute(query).all())


def _user_name_map(user_ids) -> dict:
    """Batch-resolve display names for a set of user ids (single query, no N+1)."""
    if not user_ids:
        return {}
    users = db.session.scalars(
        db.select(User).options(selectinload(User.profile)).where(User.id.in_(user_ids))
    ).all()
    return {u.id: (u.profile.full_name if u.profile else u.email) for u in users}


def get_recent_verification_activity(limit: int = 10) -> list[dict]:
    """Latest verification events (newest first) as ``{name, action, created_at}``."""
    rows = db.session.scalars(
        db.select(StudentVerificationHistory)
        .order_by(StudentVerificationHistory.created_at.desc(), StudentVerificationHistory.id.desc())
        .limit(limit)
    ).all()
    names = _user_name_map({h.user_id for h in rows})
    return [
        {"name": names.get(h.user_id, "—"), "action": h.action.value, "created_at": h.created_at}
        for h in rows
    ]


def get_verification_dashboard() -> dict:
    """All verification KPIs + recent activity in a bounded set of aggregate
    queries — the single method the admin page renders (no template calculation)."""
    return {
        "pending": get_pending_count(),
        "verified": get_verified_count(),
        "rejected": get_rejected_count(),
        "verification_rate": get_verification_rate(),
        "avg_review_days": get_average_review_time(),
        "recent_activity": get_recent_verification_activity(limit=10),
    }


def get_history_timeline(user: User, *, include_moderation: bool) -> list[dict]:
    """One user's verification history, newest first.

    ``include_moderation`` gates admin-only data: when ``False`` (the student's
    own view) reviewer names and rejection reasons are **never** included in the
    returned dicts — enforced here in the service, not just in the template."""
    rows = db.session.scalars(
        db.select(StudentVerificationHistory)
        .where(StudentVerificationHistory.user_id == user.id)
        .order_by(StudentVerificationHistory.created_at.desc(), StudentVerificationHistory.id.desc())
    ).all()

    reviewer_names = {}
    if include_moderation:
        reviewer_names = _user_name_map({h.reviewed_by for h in rows if h.reviewed_by})

    items = []
    for h in rows:
        item = {
            "action": h.action.value,
            "status": h.status.value,
            "created_at": h.created_at,
            "phone": h.submitted_phone,
            "university": h.submitted_university,
            "student_id": h.submitted_student_id,
        }
        if include_moderation:   # admin-only fields
            item["reviewer"] = reviewer_names.get(h.reviewed_by) if h.reviewed_by else None
            item["reason"] = h.review_reason
        items.append(item)
    return items


# --- University analytics ------------------------------------------------------
#
# All figures come from GROUP BY aggregates over ``user_profiles`` — never a
# per-user Python loop — so the dashboard stays fast with thousands of students.
# ``_named_university`` is the shared "has a real university name" filter; the
# ``university_name`` GROUP BY is the reusable aggregation layer future per-
# university metrics (new students / premium / listings / sales) can extend by
# joining additional tables onto the same grouping.

def _named_university():
    """Rows with a non-empty university name (ignores NULL/blank)."""
    return db.and_(UserProfile.university_name.isnot(None), UserProfile.university_name != "")


def _submitted():
    """Rows for students who have submitted at least one verification request."""
    return UserProfile.verification_status != Status.NOT_VERIFIED


def get_top_verified_universities(limit: int = 10) -> list[dict]:
    """Universities with the most VERIFIED students (desc), with each university's
    share of all verified students. One GROUP BY query."""
    rows = db.session.execute(
        db.select(UserProfile.university_name, func.count(UserProfile.id))
        .where(UserProfile.verification_status == Status.VERIFIED, _named_university())
        .group_by(UserProfile.university_name)
        .order_by(func.count(UserProfile.id).desc(), UserProfile.university_name.asc())
        .limit(limit)
    ).all()
    total_verified = get_verified_count()
    return [
        {
            "rank": rank,
            "university": name,
            "verified": int(count),
            "pct": round(int(count) / total_verified * 100, 1) if total_verified else 0.0,
        }
        for rank, (name, count) in enumerate(rows, start=1)
    ]


def get_university_breakdown(search: str | None = None) -> list[dict]:
    """Per-university request breakdown (pending / verified / rejected / total),
    ordered by total desc. One GROUP BY query; ``search`` optionally filters by
    name (the table is normally filtered client-side)."""
    query = (
        db.select(
            UserProfile.university_name,
            func.coalesce(func.sum(case((UserProfile.verification_status == Status.PENDING, 1), else_=0)), 0),
            func.coalesce(func.sum(case((UserProfile.verification_status == Status.VERIFIED, 1), else_=0)), 0),
            func.coalesce(func.sum(case((UserProfile.verification_status == Status.REJECTED, 1), else_=0)), 0),
            func.count(UserProfile.id),
        )
        .where(_named_university(), _submitted())
        .group_by(UserProfile.university_name)
        .order_by(func.count(UserProfile.id).desc(), UserProfile.university_name.asc())
    )
    if search and search.strip():
        query = query.where(UserProfile.university_name.ilike(f"%{search.strip()}%"))
    return [
        {"university": name, "pending": int(p), "verified": int(v), "rejected": int(r), "total": int(t)}
        for name, p, v, r, t in db.session.execute(query).all()
    ]


def get_total_universities() -> int:
    """Distinct universities that have submitted at least one verification."""
    return db.session.scalar(
        db.select(func.count(func.distinct(UserProfile.university_name)))
        .where(_named_university(), _submitted())
    ) or 0


def _university_by_verified(order):
    """Single university row (name, verified count) ordered as given, among
    universities with at least one VERIFIED student."""
    row = db.session.execute(
        db.select(UserProfile.university_name, func.count(UserProfile.id))
        .where(UserProfile.verification_status == Status.VERIFIED, _named_university())
        .group_by(UserProfile.university_name)
        .order_by(order(func.count(UserProfile.id)), UserProfile.university_name.asc())
        .limit(1)
    ).first()
    return {"university": row[0], "verified": int(row[1])} if row else None


def get_largest_university():
    """University with the most verified students, or ``None``."""
    return _university_by_verified(lambda col: col.desc())


def get_smallest_active_university():
    """Active (≥1 verified) university with the fewest verified students, or ``None``."""
    return _university_by_verified(lambda col: col.asc())


def get_university_statistics(top_limit: int = 10) -> dict:
    """All University Analytics figures in a bounded set of GROUP BY queries —
    the single method the dashboard renders (templates do no calculation)."""
    return {
        "top_verified": get_top_verified_universities(limit=top_limit),
        "breakdown": get_university_breakdown(),
        "total_universities": get_total_universities(),
        "largest": get_largest_university(),
        "smallest_active": get_smallest_active_university(),
    }


# --- Campus Overview (per-university) ------------------------------------------
#
# Per-university figures reuse the same aggregate patterns as the University
# Analytics dashboard, scoped to one ``university_name``. No new tables, no
# duplicate SQL, no marketplace queries (those are placeholders for a future
# version).

def university_exists(name: str) -> bool:
    """True when at least one student from ``name`` has submitted a request."""
    if not name:
        return False
    return (db.session.scalar(
        db.select(func.count(UserProfile.id)).where(UserProfile.university_name == name, _submitted())
    ) or 0) > 0


def get_university_verification_statistics(name: str) -> dict:
    """Per-university verification KPIs in one aggregate row (+ two small reads).

    ``total_requests`` counts every submission event — initial, resubmission and
    update — from the immutable history (not just current distinct students)."""
    p, v, r = db.session.execute(
        db.select(
            func.coalesce(func.sum(case((UserProfile.verification_status == Status.PENDING, 1), else_=0)), 0),
            func.coalesce(func.sum(case((UserProfile.verification_status == Status.VERIFIED, 1), else_=0)), 0),
            func.coalesce(func.sum(case((UserProfile.verification_status == Status.REJECTED, 1), else_=0)), 0),
        ).where(UserProfile.university_name == name, _submitted())
    ).one()
    pending, verified, rejected = int(p), int(v), int(r)
    total_students = pending + verified + rejected

    total_requests = db.session.scalar(
        db.select(func.count(StudentVerificationHistory.id)).where(
            StudentVerificationHistory.submitted_university == name,
            StudentVerificationHistory.action.in_([Action.SUBMITTED, Action.UPDATED]),
        )
    ) or 0

    return {
        "pending": pending,
        "verified": verified,
        "rejected": rejected,
        "total_students": total_students,
        "verification_rate": round(verified / total_students * 100, 1) if total_students else 0.0,
        "avg_review_days": get_average_review_time(university=name),
        "total_requests": int(total_requests),
    }


def get_university_recent_activity(name: str, limit: int = 10) -> list[dict]:
    """Latest verification events (any action) for students of ``name``, newest
    first — reuses the history table (no duplicate timeline logic)."""
    rows = db.session.scalars(
        db.select(StudentVerificationHistory)
        .join(UserProfile, UserProfile.user_id == StudentVerificationHistory.user_id)
        .where(UserProfile.university_name == name)
        .order_by(StudentVerificationHistory.created_at.desc(), StudentVerificationHistory.id.desc())
        .limit(limit)
    ).all()
    names = _user_name_map({h.user_id for h in rows})
    return [
        {"name": names.get(h.user_id, "—"), "action": h.action.value, "created_at": h.created_at}
        for h in rows
    ]


def get_university_students(name: str, page: int = 1, per_page: int = 20):
    """Paginated verified students from ``name`` (newest verification first).
    Returns a Flask-SQLAlchemy pagination of :class:`User` (profile eager-loaded)."""
    query = (
        db.select(User)
        .join(UserProfile, UserProfile.user_id == User.id)
        .options(selectinload(User.profile))
        .where(UserProfile.university_name == name, UserProfile.verification_status == Status.VERIFIED)
        .order_by(UserProfile.verification_reviewed_at.desc(), User.id.desc())
    )
    return db.paginate(query, page=page, per_page=per_page, error_out=False)


def get_university_overview(name: str) -> dict:
    """Composer for the Campus Overview header/body: per-university stats +
    recent activity (students are paginated separately by the route)."""
    return {
        "stats": get_university_verification_statistics(name),
        "recent_activity": get_university_recent_activity(name),
    }