"""
API Usage Tracker - Monitor ScrapeCreators credit consumption.
Tracks every API call, enforces daily limits, provides usage stats.
"""

import json
from datetime import datetime, timedelta
from sqlalchemy.orm import Session
from sqlalchemy import String, Integer, DateTime, Text
from sqlalchemy.orm import Mapped, mapped_column
from backend.database.connection import Base, SessionLocal


class ApiUsageLog(Base):
    """Log each API request for tracking credits."""
    __tablename__ = "api_usage_log"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    service: Mapped[str] = mapped_column(String(50), index=True)  # scrapecreators, creative_center
    endpoint: Mapped[str] = mapped_column(String(200))
    credits_used: Mapped[int] = mapped_column(Integer, default=1)
    keyword: Mapped[str] = mapped_column(String(200), default="")
    videos_found: Mapped[int] = mapped_column(Integer, default=0)
    status: Mapped[str] = mapped_column(String(20), default="success")  # success, error, skipped
    timestamp: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)

    def to_dict(self):
        return {
            "id": self.id,
            "service": self.service,
            "endpoint": self.endpoint,
            "credits_used": self.credits_used,
            "keyword": self.keyword,
            "videos_found": self.videos_found,
            "status": self.status,
            "timestamp": self.timestamp.isoformat() if self.timestamp else None,
        }


def log_usage(db: Session, service: str, endpoint: str, credits: int = 1,
              keyword: str = "", videos_found: int = 0, status: str = "success") -> ApiUsageLog:
    entry = ApiUsageLog(
        service=service, endpoint=endpoint, credits_used=credits,
        keyword=keyword, videos_found=videos_found, status=status,
    )
    db.add(entry)
    db.commit()
    return entry


def get_usage_today(db: Session) -> int:
    today = datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0)
    from sqlalchemy import func
    result = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= today,
        ApiUsageLog.status == "success",
    ).scalar()
    return int(result or 0)


def get_usage_stats(db: Session, days: int = 30) -> dict:
    from sqlalchemy import func
    since = datetime.utcnow() - timedelta(days=days)
    today = datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0)
    this_week = datetime.utcnow() - timedelta(days=7)

    total = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "success"
    ).scalar() or 0

    today_used = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= today, ApiUsageLog.status == "success"
    ).scalar() or 0

    week_used = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= this_week, ApiUsageLog.status == "success"
    ).scalar() or 0

    total_requests = db.query(func.count(ApiUsageLog.id)).filter(
        ApiUsageLog.timestamp >= since
    ).scalar() or 0

    total_videos = db.query(func.sum(ApiUsageLog.videos_found)).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "success"
    ).scalar() or 0

    errors = db.query(func.count(ApiUsageLog.id)).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "error"
    ).scalar() or 0

    # Daily breakdown for chart
    daily = []
    for i in range(min(days, 30)):
        day = (datetime.utcnow() - timedelta(days=i)).replace(hour=0, minute=0, second=0, microsecond=0)
        day_end = day + timedelta(days=1)
        day_credits = db.query(func.sum(ApiUsageLog.credits_used)).filter(
            ApiUsageLog.timestamp >= day, ApiUsageLog.timestamp < day_end,
            ApiUsageLog.status == "success"
        ).scalar() or 0
        daily.append({"date": day.strftime("%d/%m"), "credits": int(day_credits)})
    daily.reverse()

    # Recent log
    recent = db.query(ApiUsageLog).order_by(ApiUsageLog.timestamp.desc()).limit(50).all()

    avg_daily = int(total) / max(days, 1) if total else 0

    return {
        "credits_today": int(today_used),
        "credits_this_week": int(week_used),
        "credits_this_month": int(total),
        "total_requests": int(total_requests),
        "total_videos_found": int(total_videos),
        "errors": int(errors),
        "avg_daily": round(avg_daily, 1),
        "daily_chart": daily,
        "recent_log": [r.to_dict() for r in recent],
    }


def get_usage_history(db: Session, days: int = 30, page: int = 1, limit: int = 50,
                      service: str = "", status: str = "") -> dict:
    """Full history with pagination, filters, and per-service charts."""
    from sqlalchemy import func

    since = datetime.utcnow() - timedelta(days=days)

    # --- Filtered log with pagination ---
    q = db.query(ApiUsageLog).filter(ApiUsageLog.timestamp >= since)
    if service:
        q = q.filter(ApiUsageLog.service == service)
    if status:
        q = q.filter(ApiUsageLog.status == status)

    total = q.count()
    logs = q.order_by(ApiUsageLog.timestamp.desc()).offset((page - 1) * limit).limit(limit).all()

    # --- Per-service totals ---
    svc_rows = db.query(
        ApiUsageLog.service,
        func.sum(ApiUsageLog.credits_used).label("credits"),
        func.count(ApiUsageLog.id).label("requests"),
        func.sum(ApiUsageLog.videos_found).label("videos"),
    ).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "success"
    ).group_by(ApiUsageLog.service).all()

    by_service = {}
    for row in svc_rows:
        by_service[row.service] = {
            "credits": int(row.credits or 0),
            "requests": int(row.requests or 0),
            "videos": int(row.videos or 0),
        }

    # --- Daily breakdown per service (for stacked chart) ---
    day_label = func.strftime("%d/%m", ApiUsageLog.timestamp).label("day")
    daily_rows = db.query(
        day_label,
        ApiUsageLog.service,
        func.sum(ApiUsageLog.credits_used).label("credits"),
        func.count(ApiUsageLog.id).label("requests"),
    ).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "success"
    ).group_by("day", ApiUsageLog.service).all()

    daily_by_service = {}
    for row in daily_rows:
        day_str = row.day if row.day else "?"
        if day_str not in daily_by_service:
            daily_by_service[day_str] = {}
        daily_by_service[day_str][row.service] = {
            "credits": int(row.credits or 0),
            "requests": int(row.requests or 0),
        }

    # Build ordered daily list
    daily_chart = []
    for i in range(min(days, 30)):
        day = (datetime.utcnow() - timedelta(days=min(days, 30) - 1 - i)).replace(
            hour=0, minute=0, second=0, microsecond=0
        )
        day_str = day.strftime("%d/%m")
        entry = {"date": day_str, "services": daily_by_service.get(day_str, {})}
        daily_chart.append(entry)

    # --- Hourly distribution (for the last 7 days) ---
    week_ago = datetime.utcnow() - timedelta(days=7)
    hourly_rows = db.query(
        func.strftime("%H", ApiUsageLog.timestamp).label("hour"),
        func.count(ApiUsageLog.id).label("count"),
    ).filter(
        ApiUsageLog.timestamp >= week_ago, ApiUsageLog.status == "success"
    ).group_by("hour").all()
    hourly = {str(i).zfill(2): 0 for i in range(24)}
    for row in hourly_rows:
        hourly[row.hour] = int(row.count)

    # --- Summary ---
    today = datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0)
    this_week = datetime.utcnow() - timedelta(days=7)

    today_credits = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= today, ApiUsageLog.status == "success"
    ).scalar() or 0

    week_credits = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= this_week, ApiUsageLog.status == "success"
    ).scalar() or 0

    month_credits = db.query(func.sum(ApiUsageLog.credits_used)).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "success"
    ).scalar() or 0

    total_videos = db.query(func.sum(ApiUsageLog.videos_found)).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "success"
    ).scalar() or 0

    errors = db.query(func.count(ApiUsageLog.id)).filter(
        ApiUsageLog.timestamp >= since, ApiUsageLog.status == "error"
    ).scalar() or 0

    total_requests = db.query(func.count(ApiUsageLog.id)).filter(
        ApiUsageLog.timestamp >= since
    ).scalar() or 0

    return {
        "summary": {
            "credits_today": int(today_credits),
            "credits_week": int(week_credits),
            "credits_month": int(month_credits),
            "total_requests": int(total_requests),
            "total_videos": int(total_videos),
            "errors": int(errors),
            "avg_daily": round(int(month_credits) / max(days, 1), 1),
            "cost_per_video": round(int(month_credits) / max(int(total_videos), 1), 2),
        },
        "by_service": by_service,
        "daily_chart": daily_chart,
        "hourly": hourly,
        "logs": [l.to_dict() for l in logs],
        "total_logs": total,
        "page": page,
        "total_pages": (total + limit - 1) // limit,
    }


def can_use_credits(db: Session, daily_limit: int) -> bool:
    """Check if we're within the daily credit limit."""
    return get_usage_today(db) < daily_limit
