"""
Admin service. All functions assume the caller has been authorized via
`require_admin` dependency in the route layer. The service performs NO
authorization checks of its own.
"""
import logging
from datetime import datetime, timedelta, timezone
from typing import List, Optional

from app.core.config import settings
from app.core.supabase_client import supabase
from app.schemas.admin import (
    AdminUser,
    AdminUsersResponse,
    AdminStatsResponse,
    OrphanCv,
    OrphanCvsResponse,
)
from app.services.storage_service import storage_service

logger = logging.getLogger(__name__)


def list_all_users() -> AdminUsersResponse:
    """
    List all candidate users (role != 'admin') with merged profile + auth + cv data.
    Admins are excluded — this feeds UsersTab, which is candidate-only.

    Strategy:
      1. Fetch all non-admin profiles (id, firstname, lastname, phone, profession, city, role, created_at).
      2. Fetch all auth users via supabase.auth.admin.list_users() to get email + email_confirmed_at.
      3. Fetch all cvs user_ids to compute has_cv flag.
      4. Merge in memory keyed by user id.

    Returns AdminUsersResponse with merged list and total count.
    """
    # === 1. All profiles (admins excluded — this list is candidate-only) ===
    try:
        profiles_resp = (
            supabase.table("profiles")
            .select("id,firstname,lastname,phone,profession,city,role,created_at")
            .neq("role", "admin")
            .execute()
        )
    except Exception as e:
        logger.exception("admin_service.list_all_users: profiles fetch failed")
        raise

    profiles = profiles_resp.data or []

    # === 2. All auth users (emails) ===
    try:
        auth_users = supabase.auth.admin.list_users()
    except Exception as e:
        logger.exception("admin_service.list_all_users: auth.list_users failed")
        raise

    # Build a map: user_id -> (email, email_confirmed_at)
    email_map: dict = {}
    for u in auth_users:
        uid = getattr(u, "id", None)
        if uid:
            email_map[uid] = (
                getattr(u, "email", None),
                getattr(u, "email_confirmed_at", None),
            )

    # === 3. All cvs (just user_id for has_cv flag) ===
    try:
        cvs_resp = supabase.table("cvs").select("user_id").execute()
    except Exception as e:
        logger.exception("admin_service.list_all_users: cvs fetch failed")
        raise

    users_with_cv: set = {row["user_id"] for row in (cvs_resp.data or [])}

    # === 4. Merge ===
    merged: List[AdminUser] = []
    for p in profiles:
        pid = p["id"]
        email, confirmed_at = email_map.get(pid, (None, None))
        merged.append(
            AdminUser(
                id=pid,
                firstname=p["firstname"],
                lastname=p["lastname"],
                phone=p.get("phone"),
                profession=p.get("profession"),
                city=p.get("city"),
                role=p["role"],
                email=email,
                email_confirmed_at=confirmed_at,
                has_cv=pid in users_with_cv,
                created_at=p["created_at"],
            )
        )

    return AdminUsersResponse(users=merged, total=len(merged))


def get_stats() -> AdminStatsResponse:
    """
    Compute global counts for the admin dashboard:
      - total_candidates:  profiles with role='candidate'
      - total_admins:      profiles with role='admin'
      - total_cvs:         rows in the cvs table
      - total_active_30d:  users whose last_sign_in_at is within the last 30 days

    The active30d count is computed in-memory from supabase.auth.admin.list_users()
    (the same call already used by list_all_users). No additional round-trip.
    """
    try:
        candidates_resp = (
            supabase.table("profiles")
            .select("id", count="exact")
            .eq("role", "candidate")
            .execute()
        )
        admins_resp = (
            supabase.table("profiles")
            .select("id", count="exact")
            .eq("role", "admin")
            .execute()
        )
        cvs_resp = (
            supabase.table("cvs")
            .select("id", count="exact")
            .execute()
        )
        auth_users = supabase.auth.admin.list_users()
    except Exception:
        logger.exception("admin_service.get_stats: queries failed")
        raise

    # Count users with last_sign_in_at >= now - 30d.
    # Comparison is timezone-aware (last_sign_in_at is tz-aware datetime; we use UTC).
    cutoff = datetime.now(timezone.utc) - timedelta(days=30)
    active_30d_count = 0
    for u in auth_users:
        last_sign_in = getattr(u, "last_sign_in_at", None)
        if last_sign_in is not None and last_sign_in >= cutoff:
            active_30d_count += 1

    return AdminStatsResponse(
        total_candidates=candidates_resp.count or 0,
        total_admins=admins_resp.count or 0,
        total_cvs=cvs_resp.count or 0,
        total_active_30d=active_30d_count,
    )


def delete_user(user_id: str, current_user_id: str, delete_cv: bool = False) -> None:
    """
    Delete a user from Supabase Auth.

    delete_cv=False (default): DB-level constraints handle everything —
    no manual cleanup of profiles/cvs:
      - public.profiles row is removed automatically (profiles_id_fkey ON DELETE CASCADE)
      - public.cvs.user_id is set to NULL for any CV owned by this user
        (cvs_user_id_fkey ON DELETE SET NULL) — the CV becomes orphaned.
    Storage objects ({user_id}/cv.pdf, {user_id}/step_*.m4a) are left untouched.

    delete_cv=True: additionally deletes the CV row and its storage files
    (full cleanup, irreversible). The cv id is captured BEFORE the user is
    deleted, because once auth.admin.delete_user() runs, the cascade sets
    cvs.user_id to NULL and the user_id -> cv link is lost.

    Args:
        user_id: the account to delete.
        current_user_id: the calling admin's own id (from the JWT).
        delete_cv: if True, also delete the CV row + its storage files.

    Raises:
        ValueError: if user_id == current_user_id (admins cannot delete themselves).
        Exception: propagated from Supabase on failure of the cv id lookup
            or the Auth admin delete call itself. Post-deletion cleanup
            (storage files, cv row) is best-effort and does NOT raise —
            the user is already gone by that point, so a cleanup failure
            is logged, not surfaced as a request failure.
    """
    if user_id == current_user_id:
        raise ValueError("Admins cannot delete their own account.")

    cv_id = None
    if delete_cv:
        cv_resp = (
            supabase.table("cvs")
            .select("id")
            .eq("user_id", user_id)
            .maybe_single()
            .execute()
        )
        # supabase-py quirk: maybe_single() returns None (not a response
        # object) when zero rows match. Normalize before reading .data.
        cv_data = cv_resp.data if cv_resp is not None else None
        cv_id = cv_data["id"] if cv_data else None
        logger.info(
            "delete_user: delete_cv=True, cv lookup for user_id=%s -> cv_id=%s",
            user_id, cv_id,
        )

    supabase.auth.admin.delete_user(user_id)
    logger.info(
        "Deleted user_id=%s (cascade: profiles removed, owned cvs orphaned)",
        user_id,
    )

    if delete_cv and cv_id is not None:
        storage_service.delete_user_files(user_id)
        try:
            supabase.table("cvs").delete().eq("id", cv_id).execute()
            logger.info("Deleted cv row id=%s for former user_id=%s", cv_id, user_id)
        except Exception:
            logger.exception(
                "delete_user: failed to delete cv row id=%s for former user_id=%s",
                cv_id, user_id,
            )
    elif delete_cv:
        logger.info(
            "delete_user: delete_cv=True but no cv found for user_id=%s — nothing to clean up",
            user_id,
        )


def delete_user_cv_only(user_id: str) -> None:
    """
    Delete a user's CV (storage files + row) while keeping their account
    intact. Unlike delete_user(delete_cv=True), the auth/profiles rows are
    never touched.
    """
    storage_service.delete_user_files(user_id)
    supabase.table("cvs").delete().eq("user_id", user_id).execute()
    logger.info("Deleted CV (storage + row) for user_id=%s — account kept", user_id)


def delete_orphan_cv(cv_id: str) -> None:
    """
    Delete an orphaned CV row (cvs.user_id IS NULL) and its storage files.

    The former owner's user_id no longer exists anywhere on the row, so it
    is recovered from pdf_url, which stores the storage path in the form
    "{user_id}/cv.pdf" (see storage_service.upload_cv_pdf). That recovered
    id is passed to the existing delete_user_files(user_id) — no new
    storage method needed.
    """
    cv_resp = (
        supabase.table("cvs")
        .select("pdf_url")
        .eq("id", cv_id)
        .maybe_single()
        .execute()
    )
    cv_data = cv_resp.data if cv_resp is not None else None
    pdf_url = cv_data.get("pdf_url") if cv_data else None

    if pdf_url:
        extracted_user_id, sep, _ = pdf_url.partition("/cv.pdf")
        if sep:
            storage_service.delete_user_files(extracted_user_id)
        else:
            logger.warning(
                "delete_orphan_cv: unexpected pdf_url format for cv_id=%s: %s",
                cv_id, pdf_url,
            )

    supabase.table("cvs").delete().eq("id", cv_id).execute()
    logger.info("Deleted orphan cv row id=%s", cv_id)


def list_orphan_cvs() -> OrphanCvsResponse:
    """
    List CV rows whose owning user has been deleted (user_id IS NULL, set by
    cvs_user_id_fkey ON DELETE SET NULL). No join with profiles is possible —
    the owning profile row no longer exists.

    Full CV content (experiences/formations/skills/languages) is included
    so the "Candidats / CV orphelins" toggle in UsersTab can open an
    orphaned CV in the same CVViewerModal used everywhere else, rather
    than a second, data-incomplete preview.

    cvs.pdf_url stores the raw storage path (e.g. "{user_id}/cv.pdf"), not
    a usable URL — a fresh signed URL is generated per row here, mirroring
    build_cv_response()'s pattern in cv_service.py. A failure for one row
    (e.g. file deleted from storage) only nulls that row's pdf_url instead
    of failing the whole listing.
    """
    try:
        resp = (
            supabase.table("cvs")
            .select(
                "id,personal_info,profil,experiences,formations,skills,"
                "languages,pdf_url,created_at,updated_at"
            )
            .is_("user_id", "null")
            .execute()
        )
    except Exception:
        logger.exception("admin_service.list_orphan_cvs: query failed")
        raise

    cvs = [
        OrphanCv(
            id=row["id"],
            personal_info=row.get("personal_info"),
            profil=row.get("profil") or "",
            experiences=row.get("experiences") or [],
            formations=row.get("formations") or [],
            skills=row.get("skills") or [],
            languages=row.get("languages") or [],
            pdf_url=_signed_orphan_pdf_url(row["id"], row.get("pdf_url")),
            created_at=row["created_at"],
            updated_at=row["updated_at"],
        )
        for row in (resp.data or [])
    ]
    return OrphanCvsResponse(cvs=cvs, total=len(cvs))


def _signed_orphan_pdf_url(cv_id: str, storage_path: Optional[str]) -> Optional[str]:
    """Generate a fresh signed URL for an orphan CV's stored PDF, or None on failure."""
    if not storage_path:
        return None
    try:
        return storage_service.get_signed_url(
            bucket=settings.SUPABASE_CVS_BUCKET,
            path=storage_path,
        )
    except Exception:
        logger.warning(
            "admin_service.list_orphan_cvs: signed URL generation failed (cv_id=%s)",
            cv_id,
        )
        return None