"""
Admin Dashboard API Endpoints
"""
from fastapi import APIRouter, Depends, Query
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func
from datetime import datetime, timedelta

from app.database import get_db
from app.auth.dependencies import get_current_admin
from app.models.user import User
from app.models.client import Client
from app.models.subscription import Subscription
from app.models.invoice import Invoice, InvoiceStatus
from app.models.usage import UsageLog
from app.models.audit_log import AuditLog
from app.models.customer_query import CustomerQuery

router = APIRouter()


@router.get("/dashboard")
async def get_admin_dashboard(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get admin dashboard statistics
    """
    # Total counts
    clients_count = await db.scalar(select(func.count()).select_from(Client))
    users_count = await db.scalar(select(func.count()).select_from(User))
    active_subs = await db.scalar(
        select(func.count())
        .select_from(Subscription)
        .where(Subscription.status == "active")
    )

    # Revenue this month
    first_of_month = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    monthly_revenue = await db.scalar(
        select(func.coalesce(func.sum(Invoice.total_amount), 0))
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= first_of_month)
    )

    # Pending payments
    pending_payments = await db.scalar(
        select(func.coalesce(func.sum(Invoice.amount_due), 0))
        .where(Invoice.status.in_([InvoiceStatus.PENDING, InvoiceStatus.OVERDUE]))
    )

    # New clients this month
    new_clients = await db.scalar(
        select(func.count())
        .select_from(Client)
        .where(Client.created_at >= first_of_month)
    )

    # Service distribution
    service_dist = await db.execute(
        select(Subscription.service_type, func.count())
        .where(Subscription.status == "active")
        .group_by(Subscription.service_type)
    )
    services_breakdown = {row[0]: row[1] for row in service_dist}

    return {
        "stats": {
            "total_clients": clients_count,
            "total_users": users_count,
            "active_subscriptions": active_subs,
            "monthly_revenue": float(monthly_revenue or 0),
            "pending_payments": float(pending_payments or 0),
            "new_clients_this_month": new_clients
        },
        "services_breakdown": services_breakdown,
        "currency": "INR"
    }


@router.get("/dashboard/stats")
async def get_dashboard_stats(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get dashboard statistics for admin panel
    """
    # Total clients
    total_clients = await db.scalar(select(func.count()).select_from(Client))

    # Active subscriptions
    active_subscriptions = await db.scalar(
        select(func.count())
        .select_from(Subscription)
        .where(Subscription.status == "active")
    )

    # Revenue this month
    first_of_month = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    monthly_revenue = await db.scalar(
        select(func.coalesce(func.sum(Invoice.total_amount), 0))
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= first_of_month)
    )

    # Last month revenue for growth calculation
    first_of_last_month = (first_of_month - timedelta(days=1)).replace(day=1)
    last_month_revenue = await db.scalar(
        select(func.coalesce(func.sum(Invoice.total_amount), 0))
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= first_of_last_month)
        .where(Invoice.paid_at < first_of_month)
    )

    # Pending invoices count
    pending_invoices = await db.scalar(
        select(func.count())
        .select_from(Invoice)
        .where(Invoice.status.in_([InvoiceStatus.PENDING, InvoiceStatus.OVERDUE]))
    )

    # Pending invoice amount
    pending_amount = await db.scalar(
        select(func.coalesce(func.sum(Invoice.amount_due), 0))
        .where(Invoice.status.in_([InvoiceStatus.PENDING, InvoiceStatus.OVERDUE]))
    )

    # Clients last month for growth
    clients_last_month = await db.scalar(
        select(func.count())
        .select_from(Client)
        .where(Client.created_at < first_of_month)
    )

    # Calculate growth percentages
    client_growth = 0
    if clients_last_month and clients_last_month > 0:
        new_this_month = total_clients - clients_last_month
        client_growth = round((new_this_month / clients_last_month) * 100, 1)

    revenue_growth = 0
    if last_month_revenue and last_month_revenue > 0:
        revenue_growth = round(((float(monthly_revenue or 0) - float(last_month_revenue)) / float(last_month_revenue)) * 100, 1)

    # New clients today
    today_start = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
    new_clients_today = await db.scalar(
        select(func.count())
        .select_from(Client)
        .where(Client.created_at >= today_start)
    )

    # Churned this month (cancelled subscriptions)
    churned_this_month = await db.scalar(
        select(func.count())
        .select_from(Subscription)
        .where(Subscription.status == "cancelled")
        .where(Subscription.updated_at >= first_of_month)
    )

    return {
        "total_clients": total_clients or 0,
        "active_subscriptions": active_subscriptions or 0,
        "monthly_revenue": float(monthly_revenue or 0),
        "pending_invoices": pending_invoices or 0,
        "pending_amount": float(pending_amount or 0),
        "client_growth": client_growth,
        "revenue_growth": revenue_growth,
        "new_clients_today": new_clients_today or 0,
        "churned_this_month": churned_this_month or 0
    }


@router.get("/dashboard/activity")
async def get_dashboard_activity(
    limit: int = Query(10, ge=1, le=50),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get recent activity for dashboard
    """
    from sqlalchemy.orm import selectinload

    activities = []

    # Get recent clients
    recent_clients = await db.execute(
        select(Client)
        .order_by(Client.created_at.desc())
        .limit(5)
    )
    for client in recent_clients.scalars():
        time_ago = get_time_ago(client.created_at)
        activities.append({
            "type": "new_client",
            "message": f"New client registered: {client.company_name}",
            "time": time_ago,
            "timestamp": client.created_at.isoformat()
        })

    # Get recent paid invoices
    recent_invoices = await db.execute(
        select(Invoice)
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at != None)
        .order_by(Invoice.paid_at.desc())
        .limit(5)
    )
    for inv in recent_invoices.scalars():
        time_ago = get_time_ago(inv.paid_at)
        activities.append({
            "type": "payment",
            "message": f"Payment received: ₹{inv.total_amount:,.0f}",
            "time": time_ago,
            "timestamp": inv.paid_at.isoformat() if inv.paid_at else inv.created_at.isoformat()
        })

    # Get recent subscription changes
    recent_subs = await db.execute(
        select(Subscription)
        .order_by(Subscription.updated_at.desc())
        .limit(5)
    )
    for sub in recent_subs.scalars():
        time_ago = get_time_ago(sub.updated_at)
        if sub.status == "cancelled":
            activities.append({
                "type": "churn",
                "message": f"Subscription cancelled: {sub.plan_name}",
                "time": time_ago,
                "timestamp": sub.updated_at.isoformat()
            })
        elif sub.status == "active":
            activities.append({
                "type": "subscription",
                "message": f"Subscription activated: {sub.plan_name}",
                "time": time_ago,
                "timestamp": sub.updated_at.isoformat()
            })

    # Sort by timestamp and limit
    activities.sort(key=lambda x: x["timestamp"], reverse=True)
    return activities[:limit]


@router.get("/dashboard/health")
async def get_system_health(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get system health status
    """
    import time

    # Check database
    db_status = "healthy"
    db_response_time = 0
    try:
        start = time.time()
        await db.execute(select(func.count()).select_from(Client))
        db_response_time = int((time.time() - start) * 1000)
    except Exception:
        db_status = "unhealthy"

    return {
        "api_status": "healthy",
        "database_status": db_status,
        "queue_status": "healthy",
        "avg_response_time": db_response_time + 50,  # Add estimated API overhead
        "uptime": 99.98
    }


@router.get("/dashboard/revenue")
async def get_revenue_data(
    period: str = Query("6months"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get revenue data for charts
    """
    # Determine date range
    if period == "30days":
        days = 30
    elif period == "3months":
        days = 90
    elif period == "6months":
        days = 180
    elif period == "1year":
        days = 365
    else:
        days = 180

    start_date = datetime.now() - timedelta(days=days)

    # Get monthly revenue data
    result = await db.execute(
        select(
            func.date_trunc('month', Invoice.paid_at).label("month"),
            func.sum(Invoice.total_amount).label("revenue")
        )
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= start_date)
        .group_by(func.date_trunc('month', Invoice.paid_at))
        .order_by(func.date_trunc('month', Invoice.paid_at))
    )

    data = []
    for row in result:
        if row.month:
            data.append({
                "month": row.month.strftime("%b"),
                "revenue": float(row.revenue or 0)
            })

    return data


@router.get("/dashboard/client-growth")
async def get_client_growth_data(
    period: str = Query("6months"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get client growth data for charts
    """
    if period == "30days":
        days = 30
    elif period == "3months":
        days = 90
    elif period == "6months":
        days = 180
    else:
        days = 180

    start_date = datetime.now() - timedelta(days=days)

    # Get monthly client counts
    result = await db.execute(
        select(
            func.date_trunc('month', Client.created_at).label("month"),
            func.count().label("new_clients")
        )
        .where(Client.created_at >= start_date)
        .group_by(func.date_trunc('month', Client.created_at))
        .order_by(func.date_trunc('month', Client.created_at))
    )

    # Calculate cumulative
    total_before = await db.scalar(
        select(func.count())
        .select_from(Client)
        .where(Client.created_at < start_date)
    ) or 0

    data = []
    cumulative = total_before
    for row in result:
        if row.month:
            cumulative += row.new_clients
            data.append({
                "month": row.month.strftime("%b"),
                "clients": cumulative
            })

    return data


@router.get("/dashboard/plan-distribution")
async def get_plan_distribution(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get subscription plan distribution
    """
    result = await db.execute(
        select(
            Subscription.plan_tier,
            func.count().label("count")
        )
        .where(Subscription.status == "active")
        .group_by(Subscription.plan_tier)
    )

    colors = {
        "starter": "#3b82f6",
        "professional": "#8b5cf6",
        "enterprise": "#10b981",
        "trial": "#f59e0b"
    }

    data = []
    for row in result:
        plan_name = row.plan_tier or "Unknown"
        data.append({
            "name": plan_name.capitalize(),
            "value": row.count,
            "color": colors.get(plan_name.lower(), "#6b7280")
        })

    return data


def get_time_ago(dt: datetime) -> str:
    """Helper to format time ago string"""
    if not dt:
        return "Unknown"

    now = datetime.now()
    diff = now - dt

    if diff.days > 0:
        if diff.days == 1:
            return "1 day ago"
        return f"{diff.days} days ago"

    hours = diff.seconds // 3600
    if hours > 0:
        if hours == 1:
            return "1 hour ago"
        return f"{hours} hours ago"

    minutes = diff.seconds // 60
    if minutes > 0:
        if minutes == 1:
            return "1 minute ago"
        return f"{minutes} minutes ago"

    return "Just now"


@router.get("/recent-activity")
async def get_recent_activity(
    limit: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get recent activity logs
    """
    result = await db.execute(
        select(AuditLog)
        .order_by(AuditLog.created_at.desc())
        .limit(limit)
    )
    logs = result.scalars().all()

    return [{
        "id": log.id,
        "action": log.action,
        "user_email": log.user_email,
        "resource_type": log.resource_type,
        "resource_id": log.resource_id,
        "description": log.description,
        "ip_address": log.ip_address,
        "status": log.status,
        "performed_at": log.performed_at
    } for log in logs]


@router.get("/revenue-chart")
async def get_revenue_chart(
    days: int = Query(30, ge=7, le=365),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get revenue data for chart
    """
    start_date = datetime.now() - timedelta(days=days)

    result = await db.execute(
        select(
            func.date(Invoice.paid_at).label("date"),
            func.sum(Invoice.total_amount).label("revenue")
        )
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= start_date)
        .group_by(func.date(Invoice.paid_at))
        .order_by(func.date(Invoice.paid_at))
    )

    data = [{"date": str(row.date), "revenue": float(row.revenue)} for row in result]

    return {"data": data, "currency": "INR"}


@router.get("/top-clients")
async def get_top_clients(
    limit: int = Query(10, ge=1, le=50),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get top clients by revenue
    """
    result = await db.execute(
        select(
            Client.id,
            Client.company_name,
            Client.company_email,
            func.coalesce(func.sum(Invoice.total_amount), 0).label("total_revenue")
        )
        .outerjoin(Invoice, Client.id == Invoice.client_id)
        .where(Invoice.status == InvoiceStatus.PAID)
        .group_by(Client.id, Client.company_name, Client.company_email)
        .order_by(func.sum(Invoice.total_amount).desc())
        .limit(limit)
    )

    return [{
        "id": row.id,
        "company_name": row.company_name,
        "email": row.company_email,
        "total_revenue": float(row.total_revenue)
    } for row in result]


@router.get("/usage-stats")
async def get_usage_stats(
    days: int = Query(30, ge=1, le=365),
    service_type: str = None,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get platform usage statistics
    """
    start_date = datetime.now() - timedelta(days=days)

    query = select(
        UsageLog.service_type,
        UsageLog.resource_type,
        func.sum(UsageLog.quantity).label("total_quantity"),
        func.count().label("request_count")
    ).where(UsageLog.created_at >= start_date)

    if service_type:
        query = query.where(UsageLog.service_type == service_type)

    query = query.group_by(UsageLog.service_type, UsageLog.resource_type)

    result = await db.execute(query)

    usage_data = {}
    for row in result:
        if row.service_type not in usage_data:
            usage_data[row.service_type] = {}
        usage_data[row.service_type][row.resource_type] = {
            "quantity": int(row.total_quantity),
            "requests": row.request_count
        }

    return {"usage": usage_data, "period_days": days}


@router.get("/expiring-subscriptions")
async def get_expiring_subscriptions(
    days: int = Query(30, ge=1, le=90),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get subscriptions expiring soon
    """
    expiry_date = datetime.now().date() + timedelta(days=days)

    result = await db.execute(
        select(Subscription)
        .join(Client)
        .where(Subscription.status == "active")
        .where(Subscription.end_date != None)
        .where(Subscription.end_date <= expiry_date)
        .order_by(Subscription.end_date)
    )
    subscriptions = result.scalars().all()

    return [{
        "id": sub.id,
        "client_id": sub.client_id,
        "service_type": sub.service_type,
        "plan_name": sub.plan_name,
        "end_date": sub.end_date,
        "days_remaining": (sub.end_date - datetime.now().date()).days if sub.end_date else None,
        "auto_renew": sub.auto_renew
    } for sub in subscriptions]


@router.get("/overdue-invoices")
async def get_overdue_invoices(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get overdue invoices
    """
    result = await db.execute(
        select(Invoice)
        .join(Client)
        .where(Invoice.status == InvoiceStatus.OVERDUE)
        .order_by(Invoice.due_date)
    )
    invoices = result.scalars().all()

    return [{
        "id": inv.id,
        "client_id": inv.client_id,
        "invoice_number": inv.invoice_number,
        "total_amount": float(inv.total_amount),
        "amount_due": float(inv.amount_due),
        "due_date": inv.due_date,
        "days_overdue": (datetime.now().date() - inv.due_date).days
    } for inv in invoices]


# ============= CLIENT MANAGEMENT ENDPOINTS =============

@router.get("/clients")
async def get_admin_clients(
    search: str = Query(None),
    status: str = Query(None),
    plan: str = Query(None),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get all clients with their subscription and user info for admin panel
    """
    from sqlalchemy.orm import selectinload

    query = select(Client).options(
        selectinload(Client.users),
        selectinload(Client.subscriptions)
    )

    # Apply filters
    if search:
        query = query.where(
            (Client.company_name.ilike(f"%{search}%")) |
            (Client.company_email.ilike(f"%{search}%"))
        )

    if status:
        query = query.where(Client.status == status)

    # Get total count
    count_query = select(func.count()).select_from(Client)
    if search:
        count_query = count_query.where(
            (Client.company_name.ilike(f"%{search}%")) |
            (Client.company_email.ilike(f"%{search}%"))
        )
    if status:
        count_query = count_query.where(Client.status == status)

    total = await db.scalar(count_query)

    # Apply pagination and ordering
    query = query.order_by(Client.created_at.desc())
    query = query.offset((page - 1) * page_size).limit(page_size)

    result = await db.execute(query)
    clients = result.scalars().unique().all()

    clients_data = []
    for client in clients:
        # Get primary user (owner)
        owner = next((u for u in client.users if u.role.value in ['client_owner', 'owner']), None)
        if not owner and client.users:
            owner = client.users[0]

        # Get active subscription
        active_sub = next((s for s in client.subscriptions if s.status == 'active'), None)

        # Calculate monthly spend from invoices
        monthly_spend_result = await db.scalar(
            select(func.coalesce(func.sum(Invoice.total_amount), 0))
            .where(Invoice.client_id == client.id)
            .where(Invoice.status == InvoiceStatus.PAID)
            .where(Invoice.paid_at >= datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0))
        )

        clients_data.append({
            "id": client.id,
            "company_name": client.company_name,
            "email": client.company_email,
            "first_name": owner.name.split()[0] if owner and owner.name else "",
            "last_name": " ".join(owner.name.split()[1:]) if owner and owner.name and len(owner.name.split()) > 1 else "",
            "status": client.status,
            "plan_name": active_sub.plan_name if active_sub else "No Plan",
            "subscription_status": active_sub.status if active_sub else "none",
            "monthly_spend": float(monthly_spend_result or 0),
            "created_at": client.created_at.isoformat(),
            "last_active": owner.last_login.isoformat() if owner and hasattr(owner, 'last_login') and owner.last_login else None,
            "is_verified": client.is_verified,
            "industry": client.industry,
            "company_phone": client.company_phone
        })

    return clients_data


@router.get("/clients/{client_id}")
async def get_admin_client_detail(
    client_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get detailed client information for admin panel
    """
    from sqlalchemy.orm import selectinload

    result = await db.execute(
        select(Client)
        .options(
            selectinload(Client.users),
            selectinload(Client.subscriptions),
            selectinload(Client.invoices),
            selectinload(Client.usage_logs)
        )
        .where(Client.id == client_id)
    )
    client = result.scalar_one_or_none()

    if not client:
        from fastapi import HTTPException, status
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Client not found"
        )

    # Get owner
    owner = next((u for u in client.users if u.role.value in ['client_owner', 'owner']), None)
    if not owner and client.users:
        owner = client.users[0]

    # Get active subscription
    active_sub = next((s for s in client.subscriptions if s.status == 'active'), None)

    # Calculate total revenue (total spent)
    total_spent = sum(
        float(inv.total_amount or 0) for inv in client.invoices
        if inv.status == InvoiceStatus.PAID
    )

    # Monthly spend from active subscription
    monthly_spend = float(active_sub.price_monthly) if active_sub else 0

    # Get subscription dates
    subscription_start = active_sub.start_date.isoformat() if active_sub and active_sub.start_date else None
    subscription_end = active_sub.end_date.isoformat() if active_sub and active_sub.end_date else None

    # Get usage data from subscription
    usage_limits = active_sub.usage_limits if active_sub else {}
    current_usage = active_sub.current_usage if active_sub else {}

    # Get last active from usage logs
    last_active = None
    if client.usage_logs:
        latest_log = max(client.usage_logs, key=lambda x: x.created_at)
        last_active = latest_log.created_at.isoformat()

    # Format invoices for frontend
    invoices_data = []
    for inv in sorted(client.invoices, key=lambda x: x.created_at or datetime.min, reverse=True)[:5]:
        invoices_data.append({
            "id": inv.id,
            "number": inv.invoice_number,
            "amount": float(inv.total_amount or 0),
            "status": inv.status,
            "date": inv.invoice_date.isoformat() if inv.invoice_date else inv.created_at.isoformat() if inv.created_at else None
        })

    # Mock activity data (would need an activity log table for real data)
    activity_data = []
    for log in sorted(client.usage_logs, key=lambda x: x.created_at, reverse=True)[:10]:
        activity_data.append({
            "action": f"Used {log.resource_type}: {log.quantity}",
            "timestamp": log.created_at.isoformat()
        })

    # Parse owner name
    first_name = ""
    last_name = ""
    if owner and owner.name:
        name_parts = owner.name.split(" ", 1)
        first_name = name_parts[0]
        last_name = name_parts[1] if len(name_parts) > 1 else ""

    return {
        "id": client.id,
        "company_name": client.company_name,
        "email": client.company_email,
        "company_email": client.company_email,
        "phone": client.company_phone,
        "company_phone": client.company_phone,
        "first_name": first_name,
        "last_name": last_name,
        "company_website": client.company_website,
        "industry": client.industry,
        "company_size": client.company_size,
        "status": client.status,
        "is_verified": client.is_verified,
        "plan_name": active_sub.plan_name if active_sub else "No Plan",
        "subscription_status": active_sub.status if active_sub else None,
        "subscription_start": subscription_start,
        "subscription_end": subscription_end,
        "monthly_spend": monthly_spend,
        "total_spent": total_spent,
        "last_active": last_active,
        "created_at": client.created_at.isoformat(),
        "usage": {
            "messages_used": current_usage.get("messages_per_month", 0),
            "messages_limit": usage_limits.get("messages_per_month", 10000),
            "chatbots": len([s for s in client.subscriptions if s.service_type == "chatbot" and s.status == "active"]),
            "chatbots_limit": 10,
            "storage_mb": current_usage.get("storage_mb", 0),
            "storage_limit_mb": 2048
        },
        "invoices": invoices_data,
        "activity": activity_data,
        "address": {
            "line1": client.address_line1,
            "line2": client.address_line2,
            "city": client.city,
            "state": client.state,
            "country": client.country,
            "postal_code": client.postal_code
        },
        "billing": {
            "gstin": client.gstin,
            "pan": client.pan,
            "billing_email": client.billing_email
        },
        "owner": {
            "id": owner.id if owner else None,
            "name": owner.name if owner else None,
            "email": owner.email if owner else None
        },
        "subscription": {
            "id": active_sub.id if active_sub else None,
            "plan_name": active_sub.plan_name if active_sub else None,
            "plan_tier": active_sub.plan_tier if active_sub else None,
            "status": active_sub.status if active_sub else None,
            "price_monthly": float(active_sub.price_monthly) if active_sub else 0
        },
        "stats": {
            "users_count": len(client.users),
            "subscriptions_count": len(client.subscriptions),
            "total_revenue": total_spent,
            "invoices_count": len(client.invoices)
        },
        "referral_code": client.referral_code
    }


@router.post("/clients/{client_id}/suspend")
async def suspend_client(
    client_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Suspend a client
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(Client).where(Client.id == client_id))
    client = result.scalar_one_or_none()

    if not client:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Client not found"
        )

    client.status = "suspended"
    await db.commit()

    return {"message": "Client suspended successfully"}


@router.post("/clients/{client_id}/activate")
async def activate_client(
    client_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Activate a client
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(Client).where(Client.id == client_id))
    client = result.scalar_one_or_none()

    if not client:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Client not found"
        )

    client.status = "active"
    await db.commit()

    return {"message": "Client activated successfully"}


@router.delete("/clients/{client_id}")
async def delete_admin_client(
    client_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Delete a client (Admin only)
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(Client).where(Client.id == client_id))
    client = result.scalar_one_or_none()

    if not client:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Client not found"
        )

    await db.delete(client)
    await db.commit()

    return {"message": "Client deleted successfully"}


# ============= SUBSCRIPTION MANAGEMENT ENDPOINTS =============

@router.get("/subscriptions")
async def get_admin_subscriptions(
    search: str = Query(None),
    status: str = Query(None),
    plan: str = Query(None),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get all subscriptions with client info for admin panel
    """
    from sqlalchemy.orm import selectinload

    query = select(Subscription).options(selectinload(Subscription.client))

    # Apply filters
    if status:
        query = query.where(Subscription.status == status)

    if plan:
        query = query.where(Subscription.plan_tier == plan)

    # Get total count
    count_query = select(func.count()).select_from(Subscription)
    if status:
        count_query = count_query.where(Subscription.status == status)
    if plan:
        count_query = count_query.where(Subscription.plan_tier == plan)

    total = await db.scalar(count_query)

    # Apply pagination and ordering
    query = query.order_by(Subscription.created_at.desc())
    query = query.offset((page - 1) * page_size).limit(page_size)

    result = await db.execute(query)
    subscriptions = result.scalars().unique().all()

    subs_data = []
    for sub in subscriptions:
        client = sub.client

        # Filter by search on client name/email if provided
        if search:
            if client:
                if search.lower() not in client.company_name.lower() and search.lower() not in client.company_email.lower():
                    continue
            else:
                continue

        subs_data.append({
            "id": sub.id,
            "client_id": sub.client_id,
            "client_name": client.company_name if client else "Unknown",
            "client_email": client.company_email if client else "",
            "plan_name": sub.plan_name or f"{sub.plan_tier.capitalize()} Plan",
            "plan_tier": sub.plan_tier,
            "status": sub.status,
            "amount": float(sub.price_monthly or 0),
            "billing_cycle": sub.billing_cycle or "monthly",
            "current_period_start": sub.start_date.isoformat() if sub.start_date else None,
            "current_period_end": sub.end_date.isoformat() if sub.end_date else None,
            "next_billing_date": sub.next_billing_date.isoformat() if sub.next_billing_date else None,
            "created_at": sub.created_at.isoformat(),
            "auto_renew": sub.auto_renew,
            "service_type": sub.service_type
        })

    return subs_data


@router.get("/subscriptions/{subscription_id}")
async def get_admin_subscription_detail(
    subscription_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get detailed subscription information
    """
    from sqlalchemy.orm import selectinload
    from fastapi import HTTPException, status as http_status

    result = await db.execute(
        select(Subscription)
        .options(selectinload(Subscription.client))
        .where(Subscription.id == subscription_id)
    )
    sub = result.scalar_one_or_none()

    if not sub:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Subscription not found"
        )

    client = sub.client

    return {
        "id": sub.id,
        "client_id": sub.client_id,
        "client": {
            "id": client.id if client else None,
            "company_name": client.company_name if client else None,
            "company_email": client.company_email if client else None
        },
        "service_type": sub.service_type,
        "plan_name": sub.plan_name,
        "plan_tier": sub.plan_tier,
        "price_monthly": float(sub.price_monthly or 0),
        "billing_cycle": sub.billing_cycle,
        "currency": sub.currency,
        "status": sub.status,
        "start_date": sub.start_date.isoformat() if sub.start_date else None,
        "end_date": sub.end_date.isoformat() if sub.end_date else None,
        "trial_end_date": sub.trial_end_date.isoformat() if sub.trial_end_date else None,
        "next_billing_date": sub.next_billing_date.isoformat() if sub.next_billing_date else None,
        "auto_renew": sub.auto_renew,
        "usage_limits": sub.usage_limits or {},
        "current_usage": sub.current_usage or {},
        "features": sub.features or [],
        "created_at": sub.created_at.isoformat()
    }


@router.post("/subscriptions/{subscription_id}/cancel")
async def cancel_admin_subscription(
    subscription_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Cancel a subscription (Admin)
    """
    from fastapi import HTTPException, status as http_status
    from datetime import date

    result = await db.execute(select(Subscription).where(Subscription.id == subscription_id))
    sub = result.scalar_one_or_none()

    if not sub:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Subscription not found"
        )

    sub.status = "cancelled"
    sub.auto_renew = False
    sub.updated_at = datetime.now()

    await db.commit()

    return {"message": "Subscription cancelled successfully"}


@router.put("/subscriptions/{subscription_id}")
async def update_admin_subscription(
    subscription_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Update a subscription (Admin)
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(Subscription).where(Subscription.id == subscription_id))
    sub = result.scalar_one_or_none()

    if not sub:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Subscription not found"
        )

    # Update logic would go here based on request body
    await db.commit()

    return {"message": "Subscription updated successfully"}


# ============= INVOICE MANAGEMENT ENDPOINTS =============

@router.get("/invoices")
async def get_admin_invoices(
    search: str = Query(None),
    status: str = Query(None),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get all invoices with client info for admin panel
    """
    from sqlalchemy.orm import selectinload

    query = select(Invoice).options(selectinload(Invoice.client))

    # Apply filters
    if status:
        query = query.where(Invoice.status == status)

    # Get total count
    count_query = select(func.count()).select_from(Invoice)
    if status:
        count_query = count_query.where(Invoice.status == status)

    total = await db.scalar(count_query)

    # Apply pagination and ordering
    query = query.order_by(Invoice.created_at.desc())
    query = query.offset((page - 1) * page_size).limit(page_size)

    result = await db.execute(query)
    invoices = result.scalars().unique().all()

    invoices_data = []
    for inv in invoices:
        client = inv.client

        # Filter by search on client name/email or invoice number if provided
        if search:
            search_lower = search.lower()
            match = False
            if inv.invoice_number and search_lower in inv.invoice_number.lower():
                match = True
            if client:
                if client.company_name and search_lower in client.company_name.lower():
                    match = True
                if client.company_email and search_lower in client.company_email.lower():
                    match = True
            if not match:
                continue

        invoices_data.append({
            "id": inv.id,
            "invoice_number": inv.invoice_number,
            "client_id": inv.client_id,
            "client_name": client.company_name if client else "Unknown",
            "client_email": client.company_email if client else "",
            "amount": float(inv.total_amount or 0),
            "status": inv.status,
            "due_date": inv.due_date.isoformat() if inv.due_date else None,
            "paid_at": inv.paid_at.isoformat() if inv.paid_at else None,
            "created_at": inv.created_at.isoformat() if inv.created_at else None,
            "invoice_date": inv.invoice_date.isoformat() if inv.invoice_date else None,
            "subtotal": float(inv.subtotal or 0),
            "tax_amount": float(inv.tax_amount or 0),
            "discount_amount": float(inv.discount_amount or 0),
            "amount_paid": float(inv.amount_paid or 0),
            "amount_due": float(inv.amount_due or 0),
            "currency": inv.currency
        })

    return invoices_data


@router.get("/invoices/{invoice_id}")
async def get_admin_invoice_detail(
    invoice_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get detailed invoice information
    """
    from sqlalchemy.orm import selectinload
    from fastapi import HTTPException, status as http_status

    result = await db.execute(
        select(Invoice)
        .options(selectinload(Invoice.client))
        .where(Invoice.id == invoice_id)
    )
    inv = result.scalar_one_or_none()

    if not inv:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Invoice not found"
        )

    client = inv.client

    return {
        "id": inv.id,
        "invoice_number": inv.invoice_number,
        "client_id": inv.client_id,
        "client": {
            "id": client.id if client else None,
            "company_name": client.company_name if client else None,
            "company_email": client.company_email if client else None,
            "address": {
                "line1": client.address_line1 if client else None,
                "city": client.city if client else None,
                "state": client.state if client else None,
                "country": client.country if client else None,
                "postal_code": client.postal_code if client else None
            }
        },
        "invoice_date": inv.invoice_date.isoformat() if inv.invoice_date else None,
        "due_date": inv.due_date.isoformat() if inv.due_date else None,
        "billing_period_start": inv.billing_period_start.isoformat() if inv.billing_period_start else None,
        "billing_period_end": inv.billing_period_end.isoformat() if inv.billing_period_end else None,
        "subtotal": float(inv.subtotal or 0),
        "discount_amount": float(inv.discount_amount or 0),
        "discount_percentage": float(inv.discount_percentage or 0),
        "tax_rate": float(inv.tax_rate or 0),
        "tax_amount": float(inv.tax_amount or 0),
        "total_amount": float(inv.total_amount or 0),
        "amount_paid": float(inv.amount_paid or 0),
        "amount_due": float(inv.amount_due or 0),
        "currency": inv.currency,
        "status": inv.status,
        "line_items": inv.line_items or [],
        "payment_method": inv.payment_method,
        "payment_reference": inv.payment_reference,
        "paid_at": inv.paid_at.isoformat() if inv.paid_at else None,
        "notes": inv.notes,
        "created_at": inv.created_at.isoformat() if inv.created_at else None
    }


@router.post("/invoices/{invoice_id}/refund")
async def refund_admin_invoice(
    invoice_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Refund an invoice (Admin)
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(Invoice).where(Invoice.id == invoice_id))
    inv = result.scalar_one_or_none()

    if not inv:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Invoice not found"
        )

    if inv.status != InvoiceStatus.PAID:
        raise HTTPException(
            status_code=http_status.HTTP_400_BAD_REQUEST,
            detail="Only paid invoices can be refunded"
        )

    inv.status = InvoiceStatus.REFUNDED
    inv.updated_at = datetime.now()

    await db.commit()

    return {"message": "Invoice refunded successfully"}


# ============= SUPPORT TICKETS (CUSTOMER QUERIES) ENDPOINTS =============

@router.get("/support-tickets")
async def get_admin_support_tickets(
    search: str = Query(None),
    status: str = Query(None),
    priority: str = Query(None),
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get all support tickets (customer queries) for admin panel
    """
    query = select(CustomerQuery)

    # Apply filters
    if status:
        query = query.where(CustomerQuery.status == status)
    if priority:
        query = query.where(CustomerQuery.priority == priority)

    # Get total count
    count_query = select(func.count()).select_from(CustomerQuery)
    if status:
        count_query = count_query.where(CustomerQuery.status == status)
    if priority:
        count_query = count_query.where(CustomerQuery.priority == priority)

    total = await db.scalar(count_query)

    # Apply pagination and ordering
    query = query.order_by(CustomerQuery.created_at.desc())
    query = query.offset((page - 1) * page_size).limit(page_size)

    result = await db.execute(query)
    tickets = result.scalars().all()

    tickets_data = []
    for ticket in tickets:
        # Filter by search
        if search:
            search_lower = search.lower()
            match = False
            if ticket.customer_name and search_lower in ticket.customer_name.lower():
                match = True
            if ticket.email and search_lower in ticket.email.lower():
                match = True
            if ticket.subject and search_lower in ticket.subject.lower():
                match = True
            if not match:
                continue

        # Generate ticket number from ID
        ticket_number = f"TKT-{ticket.created_at.year}-{str(ticket.id).zfill(5)}"

        tickets_data.append({
            "id": ticket.id,
            "ticket_number": ticket_number,
            "client_name": ticket.customer_name,
            "client_email": ticket.email,
            "phone": ticket.phone,
            "subject": ticket.subject or "No Subject",
            "description": ticket.message,
            "status": ticket.status,
            "priority": ticket.priority,
            "category": "General",  # Default category since not in model
            "assigned_to": ticket.assigned_to,
            "messages_count": 1,  # Single message in current model
            "created_at": ticket.created_at.isoformat() if ticket.created_at else None,
            "updated_at": ticket.updated_at.isoformat() if ticket.updated_at else None
        })

    return tickets_data


@router.get("/support-tickets/{ticket_id}")
async def get_admin_support_ticket_detail(
    ticket_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get detailed support ticket information
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(CustomerQuery).where(CustomerQuery.id == ticket_id))
    ticket = result.scalar_one_or_none()

    if not ticket:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Ticket not found"
        )

    ticket_number = f"TKT-{ticket.created_at.year}-{str(ticket.id).zfill(5)}"

    # Create initial message from the customer query
    messages = [{
        "id": 1,
        "sender_type": "client",
        "sender_name": ticket.customer_name,
        "message": ticket.message,
        "created_at": ticket.created_at.isoformat() if ticket.created_at else None
    }]

    return {
        "id": ticket.id,
        "ticket_number": ticket_number,
        "client_name": ticket.customer_name,
        "client_email": ticket.email,
        "phone": ticket.phone,
        "subject": ticket.subject or "No Subject",
        "description": ticket.message,
        "status": ticket.status,
        "priority": ticket.priority,
        "category": "General",
        "assigned_to": ticket.assigned_to,
        "messages": messages,
        "created_at": ticket.created_at.isoformat() if ticket.created_at else None,
        "updated_at": ticket.updated_at.isoformat() if ticket.updated_at else None
    }


@router.patch("/support-tickets/{ticket_id}")
async def update_admin_support_ticket(
    ticket_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Update a support ticket status/priority (Admin)
    """
    from fastapi import HTTPException, status as http_status, Request
    from pydantic import BaseModel

    result = await db.execute(select(CustomerQuery).where(CustomerQuery.id == ticket_id))
    ticket = result.scalar_one_or_none()

    if not ticket:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Ticket not found"
        )

    # Update logic would be enhanced with request body
    await db.commit()

    return {"message": "Ticket updated successfully"}


@router.delete("/support-tickets/{ticket_id}")
async def delete_admin_support_ticket(
    ticket_id: int,
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Delete a support ticket (Admin)
    """
    from fastapi import HTTPException, status as http_status

    result = await db.execute(select(CustomerQuery).where(CustomerQuery.id == ticket_id))
    ticket = result.scalar_one_or_none()

    if not ticket:
        raise HTTPException(
            status_code=http_status.HTTP_404_NOT_FOUND,
            detail="Ticket not found"
        )

    await db.delete(ticket)
    await db.commit()

    return {"message": "Ticket deleted successfully"}


# ============= USAGE MONITORING ENDPOINTS =============

@router.get("/usage-monitoring")
async def get_usage_monitoring(
    search: str = Query(None),
    plan: str = Query(None),
    time_range: str = Query("24h"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get usage monitoring data for all clients
    """
    from sqlalchemy.orm import selectinload

    # Calculate time range
    if time_range == "1h":
        hours = 1
    elif time_range == "24h":
        hours = 24
    elif time_range == "7d":
        hours = 24 * 7
    elif time_range == "30d":
        hours = 24 * 30
    else:
        hours = 24

    start_time = datetime.now() - timedelta(hours=hours)

    # Get clients with their subscriptions
    query = select(Client).options(
        selectinload(Client.subscriptions),
        selectinload(Client.usage_logs)
    )

    if search:
        query = query.where(Client.company_name.ilike(f"%{search}%"))

    result = await db.execute(query)
    clients = result.scalars().unique().all()

    clients_data = []
    for client in clients:
        # Get active subscription
        active_sub = next((s for s in client.subscriptions if s.status == 'active'), None)
        plan_name = active_sub.plan_tier.capitalize() if active_sub else "No Plan"

        # Filter by plan if specified
        if plan and plan_name.lower() != plan.lower():
            continue

        # Calculate usage from usage_logs
        recent_logs = [log for log in client.usage_logs if log.created_at >= start_time]

        # Messages used
        messages_used = sum(
            log.quantity for log in recent_logs
            if log.resource_type in ['message', 'conversation']
        )

        # API calls
        api_calls = sum(
            log.quantity for log in recent_logs
            if log.resource_type in ['api_call', 'request']
        )

        # Get limits from subscription
        usage_limits = active_sub.usage_limits if active_sub else {}
        messages_limit = usage_limits.get('messages_per_month', 10000)
        api_calls_limit = usage_limits.get('api_calls_per_day', 5000)

        # Storage (estimated from document/training data logs)
        storage_logs = [log for log in recent_logs if log.resource_type in ['training_document', 'document_processed']]
        storage_used_mb = len(storage_logs) * 5  # Estimate 5MB per document

        # Determine usage trend
        mid_time = start_time + timedelta(hours=hours/2)
        first_half_usage = sum(log.quantity for log in recent_logs if log.created_at < mid_time)
        second_half_usage = sum(log.quantity for log in recent_logs if log.created_at >= mid_time)

        if second_half_usage > first_half_usage * 1.1:
            usage_trend = "up"
        elif second_half_usage < first_half_usage * 0.9:
            usage_trend = "down"
        else:
            usage_trend = "stable"

        # Generate alerts
        alerts = []
        if messages_limit > 0 and messages_used >= messages_limit * 0.95:
            alerts.append(f"Approaching message limit ({int(messages_used/messages_limit*100)}%)")
        if messages_limit > 0 and messages_used >= messages_limit:
            alerts.append("Message limit exceeded")

        # Get last active from most recent usage log
        last_log = max(recent_logs, key=lambda x: x.created_at) if recent_logs else None
        if last_log:
            minutes_ago = (datetime.now() - last_log.created_at).total_seconds() / 60
            if minutes_ago < 5:
                last_active = f"{int(minutes_ago)} minutes ago"
            elif minutes_ago < 60:
                last_active = f"{int(minutes_ago)} minutes ago"
            elif minutes_ago < 1440:
                last_active = f"{int(minutes_ago/60)} hours ago"
            else:
                last_active = f"{int(minutes_ago/1440)} days ago"
        else:
            last_active = "No recent activity"

        clients_data.append({
            "id": client.id,
            "client_name": client.company_name,
            "plan": plan_name,
            "messages_used": messages_used,
            "messages_limit": messages_limit,
            "storage_used_mb": storage_used_mb,
            "storage_limit_mb": 1024 if plan_name.lower() in ['starter', 'trial'] else 5120,
            "api_calls_today": api_calls,
            "api_calls_limit": api_calls_limit,
            "chatbots_count": len([s for s in client.subscriptions if s.service_type == 'chatbot' and s.status == 'active']),
            "chatbots_limit": 2 if plan_name.lower() == 'starter' else 5 if plan_name.lower() == 'professional' else -1,
            "last_active": last_active,
            "usage_trend": usage_trend,
            "alerts": alerts
        })

    return clients_data


@router.get("/usage-monitoring/platform-stats")
async def get_platform_usage_stats(
    time_range: str = Query("24h"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get platform-wide usage statistics
    """
    # Calculate time range
    if time_range == "1h":
        hours = 1
    elif time_range == "24h":
        hours = 24
    elif time_range == "7d":
        hours = 24 * 7
    elif time_range == "30d":
        hours = 24 * 30
    else:
        hours = 24

    start_time = datetime.now() - timedelta(hours=hours)

    # Total messages
    total_messages = await db.scalar(
        select(func.coalesce(func.sum(UsageLog.quantity), 0))
        .where(UsageLog.resource_type.in_(['message', 'conversation']))
        .where(UsageLog.created_at >= start_time)
    )

    # Total API calls
    total_api_calls = await db.scalar(
        select(func.coalesce(func.sum(UsageLog.quantity), 0))
        .where(UsageLog.resource_type.in_(['api_call', 'request']))
        .where(UsageLog.created_at >= start_time)
    )

    # Total documents processed
    total_documents = await db.scalar(
        select(func.count())
        .select_from(UsageLog)
        .where(UsageLog.resource_type.in_(['training_document', 'document_processed']))
        .where(UsageLog.created_at >= start_time)
    )

    # Clients with high usage (over 90% of limits)
    # This is simplified - in production would need more complex query
    clients_with_alerts = await db.scalar(
        select(func.count(func.distinct(UsageLog.client_id)))
        .where(UsageLog.created_at >= start_time)
    )

    return {
        "total_messages": int(total_messages or 0),
        "total_storage_mb": int(total_documents or 0) * 5,  # Estimate
        "total_api_calls": int(total_api_calls or 0),
        "clients_with_alerts": min(int(clients_with_alerts or 0) // 5, 5)  # Estimate
    }


@router.get("/usage-monitoring/usage-over-time")
async def get_usage_over_time(
    time_range: str = Query("24h"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get usage data over time for charts
    """
    # Calculate time range and interval
    if time_range == "1h":
        hours = 1
        interval_minutes = 10
    elif time_range == "24h":
        hours = 24
        interval_minutes = 240  # 4 hours
    elif time_range == "7d":
        hours = 24 * 7
        interval_minutes = 1440  # 1 day
    elif time_range == "30d":
        hours = 24 * 30
        interval_minutes = 1440 * 5  # 5 days
    else:
        hours = 24
        interval_minutes = 240

    start_time = datetime.now() - timedelta(hours=hours)

    # Get usage logs grouped by time intervals
    result = await db.execute(
        select(
            func.date_trunc('hour', UsageLog.created_at).label("time_bucket"),
            UsageLog.resource_type,
            func.sum(UsageLog.quantity).label("total")
        )
        .where(UsageLog.created_at >= start_time)
        .group_by(func.date_trunc('hour', UsageLog.created_at), UsageLog.resource_type)
        .order_by(func.date_trunc('hour', UsageLog.created_at))
    )

    # Aggregate by time bucket
    time_data = {}
    for row in result:
        time_str = row.time_bucket.strftime("%H:%M") if row.time_bucket else "Now"
        if time_str not in time_data:
            time_data[time_str] = {"time": time_str, "messages": 0, "api_calls": 0}

        if row.resource_type in ['message', 'conversation']:
            time_data[time_str]["messages"] += int(row.total or 0)
        elif row.resource_type in ['api_call', 'request']:
            time_data[time_str]["api_calls"] += int(row.total or 0)

    # Convert to list and sort
    data = list(time_data.values())
    data.sort(key=lambda x: x["time"])

    # If no data, return sample structure
    if not data:
        data = [
            {"time": "00:00", "messages": 0, "api_calls": 0},
            {"time": "Now", "messages": 0, "api_calls": 0}
        ]

    return data


@router.get("/usage-monitoring/resource-allocation")
async def get_resource_allocation(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get platform-wide resource allocation summary
    """
    # Get all active subscriptions
    active_subs = await db.execute(
        select(Subscription)
        .where(Subscription.status == "active")
    )
    subscriptions = active_subs.scalars().all()

    # Calculate totals from subscriptions
    total_messages_limit = 0
    total_messages_used = 0
    total_chatbots_limit = 0
    total_chatbots_used = 0

    for sub in subscriptions:
        limits = sub.usage_limits or {}
        usage = sub.current_usage or {}

        total_messages_limit += limits.get('messages_per_month', 0)
        total_messages_used += usage.get('messages_per_month', 0)

    # Get actual chatbot counts
    total_chatbots_used = len([s for s in subscriptions if s.service_type == 'chatbot'])
    total_chatbots_limit = total_chatbots_used + 10  # Estimate available capacity

    # Estimate storage
    storage_used = total_messages_used // 100  # Rough estimate
    storage_limit = 12544  # 12GB total platform

    # Calculate API calls
    api_calls_today = await db.scalar(
        select(func.coalesce(func.sum(UsageLog.quantity), 0))
        .where(UsageLog.resource_type.in_(['api_call', 'request']))
        .where(UsageLog.created_at >= datetime.now().replace(hour=0, minute=0, second=0, microsecond=0))
    )

    resources = [
        {
            "name": "Messages",
            "used": total_messages_used,
            "limit": max(total_messages_limit, 1),
            "percentage": min(int(total_messages_used / max(total_messages_limit, 1) * 100), 100)
        },
        {
            "name": "Storage",
            "used": storage_used,
            "limit": storage_limit,
            "percentage": min(int(storage_used / storage_limit * 100), 100)
        },
        {
            "name": "API Calls",
            "used": int(api_calls_today or 0),
            "limit": 96000,  # Platform limit
            "percentage": min(int((api_calls_today or 0) / 96000 * 100), 100)
        },
        {
            "name": "Chatbots",
            "used": total_chatbots_used,
            "limit": total_chatbots_limit,
            "percentage": min(int(total_chatbots_used / max(total_chatbots_limit, 1) * 100), 100)
        }
    ]

    return resources


# ============= ANALYTICS ENDPOINTS =============

@router.get("/analytics/overview")
async def get_analytics_overview(
    period: str = Query("30d"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get analytics overview with key metrics
    """
    from sqlalchemy.orm import selectinload

    # Calculate time range
    if period == "7d":
        days = 7
    elif period == "30d":
        days = 30
    elif period == "90d":
        days = 90
    elif period == "1y":
        days = 365
    else:
        days = 30

    start_time = datetime.now() - timedelta(days=days)
    prev_start = start_time - timedelta(days=days)

    # Get total clients
    total_clients = await db.scalar(
        select(func.count()).select_from(Client).where(Client.status == "active")
    )

    # Get clients created in period
    new_clients = await db.scalar(
        select(func.count()).select_from(Client)
        .where(Client.created_at >= start_time)
    )

    # Get clients created in previous period for growth calc
    prev_clients = await db.scalar(
        select(func.count()).select_from(Client)
        .where(Client.created_at >= prev_start)
        .where(Client.created_at < start_time)
    )
    client_growth = ((new_clients - prev_clients) / max(prev_clients, 1)) * 100 if prev_clients else 0

    # Get total revenue from paid invoices
    total_revenue = await db.scalar(
        select(func.coalesce(func.sum(Invoice.total_amount), 0))
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= start_time)
    )

    prev_revenue = await db.scalar(
        select(func.coalesce(func.sum(Invoice.total_amount), 0))
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= prev_start)
        .where(Invoice.paid_at < start_time)
    )
    revenue_growth = ((float(total_revenue or 0) - float(prev_revenue or 0)) / max(float(prev_revenue or 1), 1)) * 100

    # Get total messages
    total_messages = await db.scalar(
        select(func.coalesce(func.sum(UsageLog.quantity), 0))
        .where(UsageLog.resource_type.in_(['message', 'conversation']))
        .where(UsageLog.created_at >= start_time)
    )

    prev_messages = await db.scalar(
        select(func.coalesce(func.sum(UsageLog.quantity), 0))
        .where(UsageLog.resource_type.in_(['message', 'conversation']))
        .where(UsageLog.created_at >= prev_start)
        .where(UsageLog.created_at < start_time)
    )
    message_growth = ((int(total_messages or 0) - int(prev_messages or 0)) / max(int(prev_messages or 1), 1)) * 100

    # Average revenue per client
    avg_revenue = float(total_revenue or 0) / max(int(total_clients or 1), 1)

    return {
        "total_revenue": float(total_revenue or 0),
        "revenue_growth": round(revenue_growth, 1),
        "total_clients": int(total_clients or 0),
        "client_growth": round(client_growth, 1),
        "total_messages": int(total_messages or 0),
        "message_growth": round(message_growth, 1),
        "avg_revenue_per_client": round(avg_revenue, 2),
        "arpc_growth": round(revenue_growth / 2, 1)  # Simplified
    }


@router.get("/analytics/revenue")
async def get_analytics_revenue(
    period: str = Query("30d"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get revenue analytics data for charts
    """
    # Calculate time range
    if period == "7d":
        days = 7
    elif period == "30d":
        days = 30
    elif period == "90d":
        days = 90
    elif period == "1y":
        days = 365
    else:
        days = 30

    start_time = datetime.now() - timedelta(days=days)

    # Get revenue by day
    result = await db.execute(
        select(
            func.date_trunc('day', Invoice.paid_at).label("date"),
            func.sum(Invoice.total_amount).label("revenue"),
            func.count().label("subscriptions")
        )
        .where(Invoice.status == InvoiceStatus.PAID)
        .where(Invoice.paid_at >= start_time)
        .group_by(func.date_trunc('day', Invoice.paid_at))
        .order_by(func.date_trunc('day', Invoice.paid_at))
    )

    data = []
    for row in result:
        if row.date:
            data.append({
                "date": row.date.strftime("%b %d"),
                "revenue": float(row.revenue or 0),
                "subscriptions": int(row.subscriptions or 0)
            })

    # If no data, generate sample dates
    if not data:
        from datetime import date as dt_date
        for i in range(min(days, 6)):
            d = datetime.now() - timedelta(days=days - i * (days // 6))
            data.append({
                "date": d.strftime("%b %d"),
                "revenue": 0,
                "subscriptions": 0
            })

    return data


@router.get("/analytics/growth")
async def get_analytics_client_growth(
    period: str = Query("30d"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get client growth analytics
    """
    # Calculate time range - use months for growth
    months_back = 6

    data = []
    for i in range(months_back - 1, -1, -1):
        month_start = (datetime.now().replace(day=1) - timedelta(days=30 * i)).replace(day=1)
        if i > 0:
            next_month = (datetime.now().replace(day=1) - timedelta(days=30 * (i - 1))).replace(day=1)
        else:
            next_month = datetime.now() + timedelta(days=1)

        # New clients in this month
        new_count = await db.scalar(
            select(func.count()).select_from(Client)
            .where(Client.created_at >= month_start)
            .where(Client.created_at < next_month)
        )

        # Churned (cancelled subscriptions)
        churned_count = await db.scalar(
            select(func.count()).select_from(Subscription)
            .where(Subscription.status == "cancelled")
            .where(Subscription.cancelled_at >= month_start)
            .where(Subscription.cancelled_at < next_month)
        )

        data.append({
            "month": month_start.strftime("%b"),
            "new": int(new_count or 0),
            "churned": int(churned_count or 0),
            "net": int(new_count or 0) - int(churned_count or 0)
        })

    return data


@router.get("/analytics/usage")
async def get_analytics_usage(
    period: str = Query("30d"),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get usage analytics data
    """
    # Calculate time range
    if period == "7d":
        days = 7
    elif period == "30d":
        days = 30
    elif period == "90d":
        days = 90
    elif period == "1y":
        days = 365
    else:
        days = 30

    start_time = datetime.now() - timedelta(days=days)

    # Get usage by day
    result = await db.execute(
        select(
            func.date_trunc('day', UsageLog.created_at).label("date"),
            UsageLog.resource_type,
            func.sum(UsageLog.quantity).label("total")
        )
        .where(UsageLog.created_at >= start_time)
        .group_by(func.date_trunc('day', UsageLog.created_at), UsageLog.resource_type)
        .order_by(func.date_trunc('day', UsageLog.created_at))
    )

    # Aggregate by date
    date_data = {}
    for row in result:
        if row.date:
            date_str = row.date.strftime("%b %d")
            if date_str not in date_data:
                date_data[date_str] = {"date": date_str, "messages": 0, "api_calls": 0}

            if row.resource_type in ['message', 'conversation']:
                date_data[date_str]["messages"] += int(row.total or 0)
            elif row.resource_type in ['api_call', 'request']:
                date_data[date_str]["api_calls"] += int(row.total or 0)

    data = list(date_data.values())

    # If no data, generate sample dates
    if not data:
        for i in range(min(days, 6)):
            d = datetime.now() - timedelta(days=days - i * (days // 6))
            data.append({
                "date": d.strftime("%b %d"),
                "messages": 0,
                "api_calls": 0
            })

    return data


@router.get("/analytics/plan-breakdown")
async def get_analytics_plan_breakdown(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get revenue breakdown by plan
    """
    # Get active subscriptions grouped by plan
    result = await db.execute(
        select(
            Subscription.plan_tier,
            func.count().label("count"),
            func.sum(Subscription.price_monthly).label("revenue")
        )
        .where(Subscription.status == "active")
        .group_by(Subscription.plan_tier)
    )

    colors = {
        "enterprise": "#10b981",
        "professional": "#8b5cf6",
        "starter": "#3b82f6",
        "trial": "#f59e0b",
        "free": "#6b7280"
    }

    data = []
    for row in result:
        plan_name = (row.plan_tier or "Other").capitalize()
        data.append({
            "name": plan_name,
            "value": int(row.count or 0),
            "revenue": float(row.revenue or 0),
            "color": colors.get((row.plan_tier or "").lower(), "#6b7280")
        })

    # Sort by revenue descending
    data.sort(key=lambda x: x["revenue"], reverse=True)

    return data


@router.get("/analytics/top-clients")
async def get_analytics_top_clients(
    limit: int = Query(5, ge=1, le=20),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get top clients by revenue
    """
    from sqlalchemy.orm import selectinload

    # Get clients with their paid invoices
    result = await db.execute(
        select(
            Client.id,
            Client.company_name,
            func.sum(Invoice.total_amount).label("total_revenue")
        )
        .join(Invoice, Invoice.client_id == Client.id)
        .where(Invoice.status == InvoiceStatus.PAID)
        .group_by(Client.id, Client.company_name)
        .order_by(func.sum(Invoice.total_amount).desc())
        .limit(limit)
    )

    data = []
    for row in result:
        # Get client's active subscription plan
        sub_result = await db.execute(
            select(Subscription.plan_tier)
            .where(Subscription.client_id == row.id)
            .where(Subscription.status == "active")
            .limit(1)
        )
        sub = sub_result.scalar_one_or_none()
        plan = (sub or "No Plan").capitalize() if sub else "No Plan"

        data.append({
            "name": row.company_name,
            "revenue": float(row.total_revenue or 0),
            "plan": plan
        })

    return data


# ============= REPORTS ENDPOINTS =============

@router.get("/reports/list")
async def get_reports_list(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get list of generated reports
    """
    # In a real system, this would query a reports table
    # For now, return recent audit logs as proxy for report activity
    result = await db.execute(
        select(AuditLog)
        .where(AuditLog.action.like('%export%'))
        .order_by(AuditLog.created_at.desc())
        .limit(10)
    )
    logs = result.scalars().all()

    reports = []
    for log in logs:
        reports.append({
            "id": log.id,
            "report_name": f"Export - {log.resource_type}",
            "format": "csv",
            "status": "ready",
            "file_size": "1.2 MB",
            "generated_at": log.created_at.isoformat(),
            "expires_at": (log.created_at + timedelta(days=7)).isoformat()
        })

    return reports


@router.get("/reports/stats")
async def get_reports_stats(
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Get report generation statistics
    """
    # Count export-related audit logs
    total_exports = await db.scalar(
        select(func.count()).select_from(AuditLog)
        .where(AuditLog.action.like('%export%'))
    )

    this_month = await db.scalar(
        select(func.count()).select_from(AuditLog)
        .where(AuditLog.action.like('%export%'))
        .where(AuditLog.created_at >= datetime.now().replace(day=1, hour=0, minute=0, second=0))
    )

    return {
        "total_generated": int(total_exports or 0),
        "this_month": int(this_month or 0),
        "total_size_mb": round((total_exports or 0) * 1.5, 1)  # Estimate
    }


@router.post("/reports/generate")
async def generate_report(
    report_type: str = Query(...),
    format: str = Query("pdf"),
    start_date: str = Query(None),
    end_date: str = Query(None),
    current_user: User = Depends(get_current_admin),
    db: AsyncSession = Depends(get_db)
):
    """
    Generate a report (placeholder - actual generation would be async)
    """
    # Log the report generation request
    audit_log = AuditLog(
        user_id=current_user.id,
        action=f"export_{report_type}",
        resource_type=report_type,
        resource_id=0,
        details={
            "format": format,
            "start_date": start_date,
            "end_date": end_date
        },
        ip_address="system"
    )
    db.add(audit_log)
    await db.commit()
    await db.refresh(audit_log)

    return {
        "id": audit_log.id,
        "report_name": f"{report_type.replace('-', ' ').title()} Report",
        "format": format,
        "status": "generating",
        "generated_at": datetime.now().isoformat(),
        "expires_at": (datetime.now() + timedelta(days=7)).isoformat()
    }