"""
routes_admin.py — Endpoint manajemen admin.
"""

import logging
from datetime import datetime, timezone, timedelta
from pathlib import Path
from typing import Optional, List

from fastapi import APIRouter, Depends, HTTPException, Request, Form, Query
from fastapi.responses import HTMLResponse
from app.core.templates import templates
from sqlalchemy.orm import Session
from sqlalchemy import func, or_
from pydantic import BaseModel

from app.core.database import get_db
from app.core.constants import VALID_TASK_STATUSES, VALID_ROLES, MIN_PASSWORD_LEN
from app.models.user import User
from app.models.transkripsi import TranskripsiTask
from app.models.service_rate import ServiceRate
from app.services.auth_service import (
    get_current_admin,
    get_user_by_id,
    get_user_by_username,
    hash_password,
)
from app.services.task_service import request_cancel_task, get_task_by_id
from app.utils.file_utils import delete_file
from app.utils.task_formatter import task_to_admin_response
from app.utils.billing import add_balance, deduct_balance, get_transcription_rate, get_ba_rate
from app.services.feature_flag_service import get_all_flags, set_flag, invalidate_cache
from app.core.config import UPLOAD_DIR

router    = APIRouter()
logger    = logging.getLogger(__name__)


# ── Pydantic schemas ─────────────────────────────────────────────────
class AddBalanceRequest(BaseModel):
    amount: int
    description: Optional[str] = None


class DeductBalanceRequest(BaseModel):
    amount: int
    description: Optional[str] = None


class RateUpdateRequest(BaseModel):
    transcription_rate_per_minute: int
    ba_rate: int


class BulkDeleteAudioRequest(BaseModel):
    task_ids: List[int]


# ================================================================
# GET /admin — Dashboard admin (HTML)
# ================================================================
@router.get("/admin", response_class=HTMLResponse)
def admin_dashboard(
    request:       Request,
    current_admin: User = Depends(get_current_admin),
):
    return templates.TemplateResponse("admin.html", {
        "request": request,
        "admin":   current_admin,
        "user":    current_admin,
    })


# ================================================================
# GET /api/admin/stats
# ================================================================
@router.get("/api/admin/stats")
def get_stats(
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    from sqlalchemy import case

    task_stats = db.query(
        func.count().label("total"),
        func.sum(case((TranskripsiTask.status == "completed", 1), else_=0)).label("completed"),
        func.sum(case((TranskripsiTask.status == "failed", 1), else_=0)).label("failed"),
        func.sum(case((TranskripsiTask.status == "cancelled", 1), else_=0)).label("cancelled"),
        func.sum(case((TranskripsiTask.status.in_(["pending", "processing"]), 1), else_=0)).label("pending"),
        func.coalesce(func.sum(TranskripsiTask.audio_duration), 0).label("total_duration"),
        func.coalesce(func.sum(TranskripsiTask.processing_time), 0).label("total_proc_time"),
    ).one()

    user_stats = db.query(
        func.count().label("total"),
        func.sum(case((User.is_active == True, 1), else_=0)).label("active"),
    ).select_from(User).one()

    return {
        "tasks": {
            "total":     task_stats.total,
            "completed": task_stats.completed,
            "failed":    task_stats.failed,
            "cancelled": task_stats.cancelled,
            "pending":   task_stats.pending,
        },
        "audio": {
            "total_duration_seconds":   round(float(task_stats.total_duration), 2),
            "total_processing_seconds": round(float(task_stats.total_proc_time), 2),
        },
        "users": {
            "total":  user_stats.total,
            "active": user_stats.active,
        },
    }


# ================================================================
# GET /api/admin/tasks
# ================================================================
@router.get("/api/admin/tasks")
def list_all_tasks(
    limit:         int           = Query(default=50, ge=1, le=200),
    offset:        int           = Query(default=0, ge=0),
    status:        Optional[str] = Query(default=None),
    user_id:       Optional[int] = Query(default=None),
    db:            Session       = Depends(get_db),
    current_admin: User          = Depends(get_current_admin),
):
    all_valid = VALID_TASK_STATUSES | frozenset({"cancelled"})
    if status and status not in all_valid:
        raise HTTPException(
            status_code=400,
            detail=f"Status tidak valid. Pilihan: {sorted(all_valid)}",
        )

    query = db.query(TranskripsiTask)
    if status:
        query = query.filter(TranskripsiTask.status == status)
    if user_id is not None:
        query = query.filter(TranskripsiTask.user_id == user_id)

    total = query.count()
    tasks = (
        query
        .order_by(TranskripsiTask.created_at.desc())
        .offset(offset)
        .limit(limit)
        .all()
    )

    return {
        "total":  total,
        "limit":  limit,
        "offset": offset,
        "tasks":  [task_to_admin_response(t) for t in tasks],
    }


# ================================================================
# POST /api/admin/tasks/{task_id}/cancel
# ================================================================
@router.post("/api/admin/tasks/{task_id}/cancel")
def cancel_task(
    task_id:       int,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    task = get_task_by_id(db, task_id)
    if not task:
        raise HTTPException(status_code=404, detail="Task tidak ditemukan.")
    if task.status in ("completed", "failed", "cancelled"):
        raise HTTPException(
            status_code=409,
            detail=f"Task #{task_id} sudah selesai dengan status '{task.status}', tidak bisa dibatalkan.",
        )

    try:
        updated = request_cancel_task(db, task_id)
    except ValueError as exc:
        raise HTTPException(status_code=409, detail=str(exc))

    if not updated:
        raise HTTPException(status_code=404, detail="Task tidak ditemukan.")

    if task.status == "pending":
        db.query(TranskripsiTask).filter(TranskripsiTask.id == task_id).update(
            {"status": "cancelled", "progress": 0, "is_cancelled": True},
            synchronize_session=False,
        )
        db.commit()
        logger.info("[Admin] Task %d (pending) langsung dibatalkan.", task_id)
        return {
            "success": True,
            "message": f"Task #{task_id} berhasil dibatalkan.",
            "status":  "cancelled",
        }

    logger.info("[Admin] Cancel diminta untuk task %d (processing).", task_id)
    return {
        "success": True,
        "message": (
            f"Permintaan cancel untuk task #{task_id} dikirim. "
            "Worker akan berhenti setelah segmen berikutnya selesai."
        ),
        "status": "cancelling",
    }


# ================================================================
# DELETE /api/admin/tasks/{task_id}
# ================================================================
@router.delete("/api/admin/tasks/{task_id}")
def delete_task(
    task_id:       int,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    task = db.query(TranskripsiTask).filter(TranskripsiTask.id == task_id).first()
    if not task:
        raise HTTPException(status_code=404, detail="Task tidak ditemukan.")
    if task.status == "processing":
        raise HTTPException(
            status_code=409,
            detail="Task sedang diproses. Batalkan dulu sebelum menghapus.",
        )

    delete_file(task.audio_file)
    db.delete(task)
    db.commit()
    logger.info("[Admin] Task %d dihapus.", task_id)
    return {"success": True, "message": f"Task {task_id} berhasil dihapus."}


# ================================================================
# DELETE /api/admin/tasks — Bulk delete
# ================================================================
@router.delete("/api/admin/tasks")
def delete_all_tasks(
    status_filter: Optional[str] = Query(default=None),
    db:            Session       = Depends(get_db),
    current_admin: User          = Depends(get_current_admin),
):
    all_valid = VALID_TASK_STATUSES | frozenset({"cancelled"})
    if status_filter and status_filter not in all_valid:
        raise HTTPException(status_code=400, detail="Status filter tidak valid.")

    query = db.query(TranskripsiTask).filter(TranskripsiTask.status != "processing")
    if status_filter:
        query = query.filter(TranskripsiTask.status == status_filter)

    tasks = query.all()
    for task in tasks:
        delete_file(task.audio_file)
        db.delete(task)
    db.commit()
    logger.info("[Admin] %d task dihapus (filter=%s).", len(tasks), status_filter)
    return {"success": True, "deleted": len(tasks)}


# ================================================================
# GET /api/admin/tasks/auto-cleanup/preview
# ================================================================
@router.get("/api/admin/tasks/auto-cleanup/preview")
def preview_auto_cleanup(
    older_than_days: int           = Query(..., ge=1, le=3650),
    status_filter:   Optional[str] = Query(default=None),
    db:              Session       = Depends(get_db),
    current_admin:   User          = Depends(get_current_admin),
):
    all_valid = VALID_TASK_STATUSES | frozenset({"cancelled"})
    if status_filter and status_filter not in all_valid:
        raise HTTPException(status_code=400, detail="Status filter tidak valid.")

    cutoff = datetime.now(timezone.utc) - timedelta(days=older_than_days)
    query = db.query(TranskripsiTask).filter(
        TranskripsiTask.created_at < cutoff,
        TranskripsiTask.status != "processing",
    )
    if status_filter:
        query = query.filter(TranskripsiTask.status == status_filter)

    count = query.count()
    return {
        "preview":         True,
        "older_than_days": older_than_days,
        "cutoff_date":     cutoff.isoformat(),
        "status_filter":   status_filter or "semua (kecuali processing)",
        "tasks_to_delete": count,
        "message": f"Akan menghapus {count} task yang dibuat sebelum {cutoff.date()}.",
    }


# ================================================================
# DELETE /api/admin/tasks/auto-cleanup
# ================================================================
@router.delete("/api/admin/tasks/auto-cleanup")
def auto_cleanup_tasks(
    older_than_days: int           = Query(..., ge=1, le=3650),
    status_filter:   Optional[str] = Query(default=None),
    delete_files:    bool          = Query(default=True),
    db:              Session       = Depends(get_db),
    current_admin:   User          = Depends(get_current_admin),
):
    all_valid = VALID_TASK_STATUSES | frozenset({"cancelled"})
    if status_filter and status_filter not in all_valid:
        raise HTTPException(status_code=400, detail="Status filter tidak valid.")

    cutoff = datetime.now(timezone.utc) - timedelta(days=older_than_days)
    query = db.query(TranskripsiTask).filter(
        TranskripsiTask.created_at < cutoff,
        TranskripsiTask.status != "processing",
    )
    if status_filter:
        query = query.filter(TranskripsiTask.status == status_filter)

    tasks = query.all()
    deleted_count = 0
    files_deleted = 0
    files_failed  = 0

    for task in tasks:
        if delete_files and task.audio_file:
            try:
                delete_file(task.audio_file)
                files_deleted += 1
            except Exception as exc:
                files_failed += 1
                logger.warning("Gagal hapus file task %d: %s", task.id, exc)
        db.delete(task)
        deleted_count += 1

    db.commit()
    logger.info("Auto-cleanup: %d task dihapus.", deleted_count)
    return {
        "success":       True,
        "tasks_deleted": deleted_count,
        "files_deleted": files_deleted,
        "files_failed":  files_failed,
        "message":       f"Berhasil menghapus {deleted_count} task.",
    }


# ================================================================
# GET /api/admin/users — with search
# ================================================================
@router.get("/api/admin/users")
def list_users(
    limit:         int           = Query(default=50, ge=1, le=200),
    offset:        int           = Query(default=0, ge=0),
    search:        Optional[str] = Query(default=None),
    db:            Session       = Depends(get_db),
    current_admin: User          = Depends(get_current_admin),
):
    query = db.query(User)
    if search:
        term = f"%{search}%"
        query = query.filter(
            or_(User.username.ilike(term), User.full_name.ilike(term))
        )
    total = query.count()
    users = query.order_by(User.created_at.desc()).offset(offset).limit(limit).all()
    return {
        "total":  total,
        "limit":  limit,
        "offset": offset,
        "users":  [u.to_dict() for u in users],
    }


# ================================================================
# POST /api/admin/users
# ================================================================
@router.post("/api/admin/users")
def create_user(
    username:  str = Form(...),
    password:  str = Form(...),
    full_name: str = Form(default=""),
    email:     str = Form(default=""),
    role:      str = Form(default="user"),
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    if len(password) < MIN_PASSWORD_LEN:
        raise HTTPException(400, f"Password minimal {MIN_PASSWORD_LEN} karakter.")
    if role not in VALID_ROLES:
        raise HTTPException(400, "Role tidak valid.")
    if get_user_by_username(db, username):
        raise HTTPException(400, "Username sudah digunakan.")

    email_clean = email.strip() or None
    if email_clean and ("@" not in email_clean or "." not in email_clean):
        raise HTTPException(400, "Format email tidak valid.")

    user = User(
        username=username.strip(),
        email=email_clean,
        full_name=full_name.strip() or None,
        hashed_password=hash_password(password),
        role=role,
        is_active=True,
    )
    db.add(user)
    db.commit()
    db.refresh(user)
    logger.info("User '%s' dibuat oleh admin %d.", username, current_admin.id)
    return {"success": True, "user": user.to_dict()}


# ================================================================
# PATCH /api/admin/users/{user_id}
# ================================================================
@router.patch("/api/admin/users/{user_id}")
def update_user(
    user_id:      int,
    full_name:    Optional[str]  = Form(default=None),
    email:        Optional[str]  = Form(default=None),
    role:         Optional[str]  = Form(default=None),
    is_active:    Optional[bool] = Form(default=None),
    new_password: Optional[str]  = Form(default=None),
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    user = get_user_by_id(db, user_id)
    if not user:
        raise HTTPException(404, "User tidak ditemukan.")

    if user.id == current_admin.id:
        if is_active is False:
            raise HTTPException(400, "Tidak bisa menonaktifkan akun sendiri.")
        if role is not None and role != current_admin.role:
            raise HTTPException(400, "Tidak bisa mengubah role akun sendiri.")

    if full_name is not None:
        user.full_name = full_name.strip() or None
    if email is not None:
        email_clean = email.strip() or None
        if email_clean and ("@" not in email_clean or "." not in email_clean):
            raise HTTPException(400, "Format email tidak valid.")
        user.email = email_clean
    if role is not None:
        if role not in VALID_ROLES:
            raise HTTPException(400, "Role tidak valid.")
        user.role = role
    if is_active is not None:
        user.is_active = is_active
    if new_password is not None and new_password.strip():
        if len(new_password) < MIN_PASSWORD_LEN:
            raise HTTPException(400, f"Password minimal {MIN_PASSWORD_LEN} karakter.")
        user.hashed_password = hash_password(new_password)

    db.commit()
    db.refresh(user)
    logger.info("User ID %d diupdate oleh admin %d.", user_id, current_admin.id)
    return {"success": True, "user": user.to_dict()}


# ================================================================
# DELETE /api/admin/users/{user_id}
# ================================================================
@router.delete("/api/admin/users/{user_id}")
def delete_user(
    user_id:       int,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    user = get_user_by_id(db, user_id)
    if not user:
        raise HTTPException(404, "User tidak ditemukan.")
    if user.id == current_admin.id:
        raise HTTPException(400, "Tidak bisa menghapus akun sendiri.")

    active_task_count = (
        db.query(func.count(TranskripsiTask.id))
        .filter(
            TranskripsiTask.user_id == user_id,
            TranskripsiTask.status.in_(["pending", "processing"]),
        )
        .scalar() or 0
    )
    if active_task_count > 0:
        raise HTTPException(
            409,
            detail=f"User masih memiliki {active_task_count} task berjalan. Batalkan atau tunggu selesai.",
        )

    username = user.username
    db.delete(user)
    db.commit()
    logger.info("User '%s' (ID %d) dihapus oleh admin %d.", username, user_id, current_admin.id)
    return {"success": True, "message": f"User '{username}' berhasil dihapus."}


# ================================================================
# BILLING: Tambah Saldo
# ================================================================
@router.post("/api/admin/users/{user_id}/balance")
def admin_add_balance(
    user_id:       int,
    payload:       AddBalanceRequest,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    if payload.amount <= 0:
        raise HTTPException(400, "Jumlah harus lebih dari 0.")
    user = get_user_by_id(db, user_id)
    if not user:
        raise HTTPException(404, "User tidak ditemukan.")

    new_balance = add_balance(
        db=db,
        user_id=user_id,
        amount=payload.amount,
        description=payload.description or f"Top-up oleh admin {current_admin.username}",
    )
    logger.info(
        "Admin %d menambah saldo user %d sebesar %d. Saldo baru: %d.",
        current_admin.id, user_id, payload.amount, new_balance,
    )
    return {"success": True, "new_balance": new_balance}


# ================================================================
# BILLING: Kurangi Saldo
# ================================================================
@router.post("/api/admin/users/{user_id}/balance/deduct")
def admin_deduct_balance(
    user_id:       int,
    payload:       DeductBalanceRequest,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    if payload.amount <= 0:
        raise HTTPException(400, "Jumlah harus lebih dari 0.")

    user = get_user_by_id(db, user_id)
    if not user:
        raise HTTPException(404, "User tidak ditemukan.")

    if user.balance < payload.amount:
        raise HTTPException(
            400,
            f"Saldo tidak cukup. Saldo user saat ini: Rp {user.balance:,}, "
            f"diminta: Rp {payload.amount:,}",
        )

    try:
        new_balance = deduct_balance(
            db=db,
            user_id=user_id,
            amount=payload.amount,
            service_type="admin_adjustment",
            description=payload.description or f"Pengurangan oleh admin {current_admin.username}",
        )
    except ValueError as exc:
        raise HTTPException(400, str(exc))

    logger.info(
        "Admin %d mengurangi saldo user %d sebesar %d. Saldo baru: %d.",
        current_admin.id, user_id, payload.amount, new_balance,
    )
    return {"success": True, "new_balance": new_balance}


# ================================================================
# BILLING: Lihat Tarif
# ================================================================
@router.get("/api/admin/rates")
def get_rates(
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    return {
        "transcription_rate_per_minute": get_transcription_rate(db),
        "ba_rate": get_ba_rate(db),
    }


# ================================================================
# BILLING: Set Tarif
# ================================================================
@router.put("/api/admin/rates")
def set_rates(
    payload:       RateUpdateRequest,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    if payload.transcription_rate_per_minute <= 0 or payload.ba_rate <= 0:
        raise HTTPException(400, "Tarif harus bernilai positif.")

    rate_trans = db.query(ServiceRate).filter(ServiceRate.key == "transcription_rate_per_minute").first()
    if rate_trans:
        rate_trans.value = payload.transcription_rate_per_minute
    else:
        db.add(ServiceRate(key="transcription_rate_per_minute", value=payload.transcription_rate_per_minute))

    rate_ba = db.query(ServiceRate).filter(ServiceRate.key == "ba_rate").first()
    if rate_ba:
        rate_ba.value = payload.ba_rate
    else:
        db.add(ServiceRate(key="ba_rate", value=payload.ba_rate))

    db.commit()
    logger.info(
        "Admin %d update tarif: transkripsi=%d/menit, BA=%d.",
        current_admin.id, payload.transcription_rate_per_minute, payload.ba_rate,
    )
    return {"success": True}


# ================================================================
# BA PROMPT endpoints
# ================================================================
@router.get("/api/admin/ba-prompt")
def get_ba_prompt(
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    from app.models.ba_prompt import BAPrompt
    prompt = db.query(BAPrompt).first()
    if not prompt:
        return {"prompt_text": "", "is_default": True, "updated_at": None}
    return {
        "prompt_text": prompt.prompt_text,
        "is_default":  False,
        "updated_at":  prompt.updated_at,
    }


@router.get("/api/admin/ba-prompt/default")
def get_default_ba_prompt(
    current_admin: User = Depends(get_current_admin),
):
    from app.services.ba_service import DEFAULT_PROMPT
    return {"prompt_text": DEFAULT_PROMPT}


@router.post("/api/admin/ba-prompt")
def save_ba_prompt(
    payload:       dict,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    from app.models.ba_prompt import BAPrompt
    text = (payload.get("prompt_text") or "").strip()
    if not text:
        raise HTTPException(400, "Prompt tidak boleh kosong.")

    prompt = db.query(BAPrompt).first()
    if prompt:
        prompt.prompt_text = text
        prompt.updated_at  = datetime.now(timezone.utc)
    else:
        prompt = BAPrompt(prompt_text=text)
        db.add(prompt)
    db.commit()
    db.refresh(prompt)
    return {"success": True, "updated_at": prompt.updated_at}


# ================================================================
# GET /api/admin/features — Daftar semua feature flags
# ================================================================
@router.get("/api/admin/features")
def list_features(
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    return {"features": get_all_flags(db)}


# ================================================================
# POST /api/admin/features/refresh-cache
# PENTING: Harus di atas PATCH /{key} agar tidak tertimpa!
# ================================================================
@router.post("/api/admin/features/refresh-cache")
def refresh_feature_cache(
    current_admin: User = Depends(get_current_admin),
):
    invalidate_cache()
    return {"success": True, "message": "Cache feature flag berhasil di-refresh."}


# ================================================================
# PATCH /api/admin/features/{key} — Toggle satu feature flag
# ================================================================
@router.patch("/api/admin/features/{key}")
def toggle_feature(
    key:           str,
    enabled:       bool,
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    """
    Aktifkan atau nonaktifkan sebuah fitur.
    Query param: ?enabled=true atau ?enabled=false
    """
    try:
        updated = set_flag(db, key, enabled, admin_id=current_admin.id)
    except ValueError as exc:
        raise HTTPException(status_code=404, detail=str(exc))
    state = "diaktifkan" if enabled else "dinonaktifkan"
    logger.info(
        "[Admin] Feature '%s' %s oleh admin ID %d.",
        key, state, current_admin.id,
    )
    return {
        "success": True,
        "message": f"Fitur '{updated['label']}' berhasil {state}.",
        "feature": updated,
    }


# ================================================================
# STORAGE — Manajemen file audio di disk
# ================================================================

# ── GET /api/admin/storage/stats ────────────────────────────────
@router.get("/api/admin/storage/stats")
def get_storage_stats(
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    """
    Statistik total ukuran direktori upload.
    Menghitung file terasosiasi task vs file orphan (tidak ada task-nya di DB).
    """
    upload_path = Path(UPLOAD_DIR)
    total_bytes  = 0
    total_files  = 0
    orphan_bytes = 0
    orphan_count = 0

    # Kumpulkan semua path audio yang tercatat di DB
    db_audio_files = set(
        row[0] for row in
        db.query(TranskripsiTask.audio_file)
        .filter(TranskripsiTask.audio_file.isnot(None))
        .all()
    )

    if upload_path.exists():
        for f in upload_path.iterdir():
            if f.is_file():
                size         = f.stat().st_size
                total_bytes += size
                total_files += 1
                if str(f) not in db_audio_files:
                    orphan_bytes += size
                    orphan_count += 1

    return {
        "total_files":  total_files,
        "total_bytes":  total_bytes,
        "total_mb":     round(total_bytes / (1024 * 1024), 2),
        "orphan_files": orphan_count,
        "orphan_bytes": orphan_bytes,
        "orphan_mb":    round(orphan_bytes / (1024 * 1024), 2),
        "upload_dir":   str(UPLOAD_DIR),
    }


# ── GET /api/admin/storage ──────────────────────────────────────
@router.get("/api/admin/storage")
def list_storage(
    limit:         int            = Query(default=50, ge=1, le=200),
    offset:        int            = Query(default=0, ge=0),
    status:        Optional[str]  = Query(default=None),
    has_file:      Optional[bool] = Query(default=None),
    db:            Session        = Depends(get_db),
    current_admin: User           = Depends(get_current_admin),
):
    """
    Daftar semua task beserta info file audio-nya (ukuran, ada/tidak di disk).
    Filter:
      - status   : filter berdasarkan status task
      - has_file : true = hanya yang file-nya ada, false = file tidak ada di disk
    """
    query = db.query(TranskripsiTask)
    if status:
        query = query.filter(TranskripsiTask.status == status)

    total = query.count()
    tasks = (
        query
        .order_by(TranskripsiTask.created_at.desc())
        .offset(offset)
        .limit(limit)
        .all()
    )

    results = []
    for task in tasks:
        file_path   = task.audio_file
        file_exists = False
        file_size   = 0
        file_name   = None

        if file_path:
            p           = Path(file_path)
            file_exists = p.exists()
            file_name   = p.name
            if file_exists:
                file_size = p.stat().st_size

        # Filter has_file dilakukan setelah cek disk (tidak bisa di SQL)
        if has_file is True  and not file_exists:
            continue
        if has_file is False and file_exists:
            continue

        results.append({
            "task_id":         task.id,
            "status":          task.status,
            "engine":          task.engine,
            "model_type":      task.model_type,
            "user_id":         task.user_id,
            "created_at":      task.created_at.isoformat() if task.created_at else None,
            "audio_duration":  task.audio_duration,
            "file_path":       file_path,
            "file_name":       file_name,
            "file_exists":     file_exists,
            "file_size_bytes": file_size,
            "file_size_mb":    round(file_size / (1024 * 1024), 2) if file_size else 0,
        })

    return {
        "total":   total,
        "limit":   limit,
        "offset":  offset,
        "records": results,
    }


# ── DELETE /api/admin/storage/file/{task_id} ────────────────────
@router.delete("/api/admin/storage/file/{task_id}")
def delete_audio_file(
    task_id:       int,
    delete_task:   bool    = Query(default=False, description="Jika true, hapus juga record task dari DB"),
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    """
    Hapus file audio dari disk untuk satu task.
    - delete_task=false (default): file di disk dihapus, kolom audio_file di-set NULL,
      record task dipertahankan sehingga hasil transkripsi masih bisa diakses.
    - delete_task=true: file + record task dihapus sepenuhnya dari DB.
    - Tidak bisa menghapus file task yang sedang processing.
    """
    task = db.query(TranskripsiTask).filter(TranskripsiTask.id == task_id).first()
    if not task:
        raise HTTPException(status_code=404, detail="Task tidak ditemukan.")
    if task.status == "processing":
        raise HTTPException(
            status_code=409,
            detail="Task sedang diproses. Batalkan dulu sebelum menghapus file.",
        )

    file_deleted = False
    if task.audio_file:
        p = Path(task.audio_file)
        if p.exists():
            try:
                delete_file(task.audio_file)
                file_deleted = True
            except Exception as exc:
                logger.warning("[Admin] Gagal hapus file task %d: %s", task_id, exc)
        task.audio_file = None

    if delete_task:
        db.delete(task)
        db.commit()
        logger.info(
            "[Admin] Task %d dan file audio dihapus oleh admin %d.",
            task_id, current_admin.id,
        )
        return {
            "success":      True,
            "file_deleted": file_deleted,
            "task_deleted": True,
            "message":      f"Task #{task_id} dan file audio berhasil dihapus.",
        }

    db.commit()
    logger.info(
        "[Admin] File audio task %d dihapus oleh admin %d.",
        task_id, current_admin.id,
    )
    return {
        "success":      True,
        "file_deleted": file_deleted,
        "task_deleted": False,
        "message":      f"File audio task #{task_id} berhasil dihapus. Record task dipertahankan.",
    }


# ── DELETE /api/admin/storage/bulk ──────────────────────────────
@router.delete("/api/admin/storage/bulk")
def bulk_delete_audio(
    payload:       BulkDeleteAudioRequest,
    delete_tasks:  bool    = Query(default=False),
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    """
    Hapus file audio dari banyak task sekaligus.
    - delete_tasks=false (default): hanya file di disk yang dihapus,
      record task dipertahankan (audio_file di-set NULL).
    - delete_tasks=true: file + record task dihapus dari DB.
    Menolak jika ada task berstatus processing di dalam daftar.
    """
    if not payload.task_ids:
        raise HTTPException(status_code=400, detail="task_ids tidak boleh kosong.")
    if len(payload.task_ids) > 100:
        raise HTTPException(status_code=400, detail="Maksimal 100 task per bulk delete.")

    tasks = db.query(TranskripsiTask).filter(
        TranskripsiTask.id.in_(payload.task_ids)
    ).all()

    # Tolak jika ada task yang masih processing
    processing = [t.id for t in tasks if t.status == "processing"]
    if processing:
        raise HTTPException(
            status_code=409,
            detail=f"Task berikut sedang diproses dan tidak bisa dihapus: {processing}",
        )

    files_deleted = 0
    files_missing = 0
    tasks_deleted = 0

    for task in tasks:
        if task.audio_file:
            p = Path(task.audio_file)
            if p.exists():
                try:
                    delete_file(task.audio_file)
                    files_deleted += 1
                except Exception as exc:
                    logger.warning("[Admin] Bulk: gagal hapus file task %d: %s", task.id, exc)
            else:
                files_missing += 1
            task.audio_file = None

        if delete_tasks:
            db.delete(task)
            tasks_deleted += 1

    db.commit()
    logger.info(
        "[Admin] Bulk delete: %d file dihapus, %d task dihapus oleh admin %d.",
        files_deleted, tasks_deleted, current_admin.id,
    )
    return {
        "success":       True,
        "files_deleted": files_deleted,
        "files_missing": files_missing,
        "tasks_deleted": tasks_deleted,
        "message":       f"Berhasil menghapus {files_deleted} file audio.",
    }


# ── DELETE /api/admin/storage/orphans ───────────────────────────
@router.delete("/api/admin/storage/orphans")
def delete_orphan_files(
    db:            Session = Depends(get_db),
    current_admin: User    = Depends(get_current_admin),
):
    """
    Scan direktori upload dan hapus semua file yang tidak terasosiasi
    dengan task manapun di database (file orphan).
    """
    upload_path = Path(UPLOAD_DIR)
    if not upload_path.exists():
        return {
            "success": True,
            "deleted": 0,
            "message": "Direktori upload tidak ditemukan.",
        }

    db_audio_files = set(
        row[0] for row in
        db.query(TranskripsiTask.audio_file)
        .filter(TranskripsiTask.audio_file.isnot(None))
        .all()
    )

    deleted = 0
    failed  = 0
    for f in upload_path.iterdir():
        if f.is_file() and str(f) not in db_audio_files:
            try:
                f.unlink()
                deleted += 1
            except Exception as exc:
                failed += 1
                logger.warning("[Admin] Gagal hapus orphan '%s': %s", f, exc)

    logger.info(
        "[Admin] Orphan cleanup: %d file dihapus oleh admin %d.",
        deleted, current_admin.id,
    )
    return {
        "success": True,
        "deleted": deleted,
        "failed":  failed,
        "message": f"Berhasil menghapus {deleted} file orphan.",
    }