"""
Async Request Database Operations

Database interface for async invoice processing requests using SQLModel.
"""

import logging
from datetime import datetime, timedelta
from typing import Any
from uuid import UUID

from sqlalchemy import func, text
from sqlmodel import Session, select

from backend.data.database import get_session_context, create_db_and_tables, close_engine
from backend.data.models import ApiKey, AsyncRequest, RateLimitEvent

logger = logging.getLogger(__name__)


# Legacy dataclasses for backward compatibility
from dataclasses import dataclass


@dataclass(frozen=True)
class ApiKeyConfig:
    """API key configuration and limits (legacy compatibility)."""

    api_key: str
    name: str
    is_active: bool
    requests_per_minute: int
    max_concurrent_jobs: int
    max_file_size_mb: int


class AsyncRequestDB:
    """Database interface for async processing requests using SQLModel."""

    def __init__(self, connection_string: str | None = None) -> None:
        # connection_string is kept for backward compatibility but ignored
        # SQLModel uses the global engine from database.py
        self._initialized = False

    def connect(self):
        """Legacy method - returns self for compatibility."""
        return self

    def close(self) -> None:
        """Close database connections."""
        close_engine()

    def __enter__(self) -> "AsyncRequestDB":
        return self

    def __exit__(self, exc_type: Any, exc_val: Any, exc_tb: Any) -> None:
        pass  # Sessions are managed per-operation

    def create_tables(self) -> None:
        """Create async processing tables if they don't exist."""
        create_db_and_tables()
        self._initialized = True

    # ==========================================================================
    # API Key Operations
    # ==========================================================================

    def is_valid_api_key(self, api_key: str) -> bool:
        """Check if API key exists and is active."""
        with get_session_context() as session:
            result = session.get(ApiKey, api_key)
            return result is not None and result.is_active is True

    def get_api_key_config(self, api_key: str) -> ApiKeyConfig | None:
        """Get API key configuration and limits."""
        with get_session_context() as session:
            result = session.get(ApiKey, api_key)
            if result is None:
                return None
            return ApiKeyConfig(
                api_key=result.api_key,
                name=result.name,
                is_active=result.is_active,
                requests_per_minute=result.requests_per_minute,
                max_concurrent_jobs=result.max_concurrent_jobs,
                max_file_size_mb=result.max_file_size_mb,
            )

    def create_api_key(
        self,
        api_key: str,
        name: str,
        requests_per_minute: int = 10,
        max_concurrent_jobs: int = 3,
        max_file_size_mb: int = 50,
    ) -> None:
        """Create a new API key."""
        with get_session_context() as session:
            existing = session.get(ApiKey, api_key)
            if existing:
                existing.name = name
                existing.requests_per_minute = requests_per_minute
                existing.max_concurrent_jobs = max_concurrent_jobs
                existing.max_file_size_mb = max_file_size_mb
                session.add(existing)
            else:
                new_key = ApiKey(
                    api_key=api_key,
                    name=name,
                    requests_per_minute=requests_per_minute,
                    max_concurrent_jobs=max_concurrent_jobs,
                    max_file_size_mb=max_file_size_mb,
                )
                session.add(new_key)

    def update_api_key_usage(self, api_key: str) -> None:
        """Update API key last used timestamp and increment total requests."""
        with get_session_context() as session:
            key = session.get(ApiKey, api_key)
            if key:
                key.last_used_at = datetime.utcnow()
                key.total_requests += 1
                session.add(key)

    # ==========================================================================
    # Async Request Operations
    # ==========================================================================

    def create_request(
        self,
        api_key: str,
        filename: str,
        file_size: int,
        content_type: str,
        expires_at: datetime,
        request_id: str | None = None,
    ) -> str:
        """Create a new async request."""
        with get_session_context() as session:
            request = AsyncRequest(
                api_key=api_key,
                filename=filename,
                file_size=file_size,
                content_type=content_type,
                expires_at=expires_at,
            )
            if request_id:
                request.request_id = UUID(request_id)
            session.add(request)
            session.flush()  # To get the generated ID
            return str(request.request_id)

    def get_request(self, request_id: str) -> AsyncRequest | None:
        """Get a single async request by ID."""
        with get_session_context() as session:
            result = session.get(AsyncRequest, UUID(request_id))
            if result:
                # Detach from session for use outside context
                session.expunge(result)
            return result

    def get_request_by_api_key(
        self,
        request_id: str,
        api_key: str,
    ) -> AsyncRequest | None:
        """Get a request only if it belongs to the given API key."""
        with get_session_context() as session:
            statement = select(AsyncRequest).where(
                AsyncRequest.request_id == UUID(request_id),
                AsyncRequest.api_key == api_key,
            )
            result = session.exec(statement).first()
            if result:
                session.expunge(result)
            return result

    def update_status(
        self,
        request_id: str,
        status: str,
        error_message: str | None = None,
        increment_retry: bool = False,
    ) -> None:
        """Update request status."""
        with get_session_context() as session:
            request = session.get(AsyncRequest, UUID(request_id))
            if request:
                request.status = status
                if status == "processing":
                    request.started_at = datetime.utcnow()
                if error_message is not None:
                    request.error_message = error_message
                if increment_retry:
                    request.retry_count += 1
                session.add(request)

    def complete_request(
        self,
        request_id: str,
        document_id: str,
        result: dict[str, Any],
        processing_time_ms: float,
        visualization_path: str | None = None,
    ) -> None:
        """Mark request as completed with result."""
        with get_session_context() as session:
            request = session.get(AsyncRequest, UUID(request_id))
            if request:
                request.status = "completed"
                request.document_id = document_id
                request.result = result
                request.processing_time_ms = processing_time_ms
                request.visualization_path = visualization_path
                request.completed_at = datetime.utcnow()
                session.add(request)

    def get_requests_by_api_key(
        self,
        api_key: str,
        status: str | None = None,
        limit: int = 20,
        offset: int = 0,
    ) -> tuple[list[AsyncRequest], int]:
        """Get paginated requests for an API key."""
        with get_session_context() as session:
            # Count query
            count_stmt = select(func.count()).select_from(AsyncRequest).where(
                AsyncRequest.api_key == api_key
            )
            if status:
                count_stmt = count_stmt.where(AsyncRequest.status == status)
            total = session.exec(count_stmt).one()

            # Fetch query
            statement = select(AsyncRequest).where(
                AsyncRequest.api_key == api_key
            )
            if status:
                statement = statement.where(AsyncRequest.status == status)
            statement = statement.order_by(AsyncRequest.created_at.desc())
            statement = statement.offset(offset).limit(limit)

            results = session.exec(statement).all()
            # Detach results from session
            for r in results:
                session.expunge(r)
            return list(results), total

    def count_active_jobs(self, api_key: str) -> int:
        """Count active (pending + processing) jobs for an API key."""
        with get_session_context() as session:
            statement = select(func.count()).select_from(AsyncRequest).where(
                AsyncRequest.api_key == api_key,
                AsyncRequest.status.in_(["pending", "processing"]),
            )
            return session.exec(statement).one()

    def get_pending_requests(self, limit: int = 10) -> list[AsyncRequest]:
        """Get pending requests ordered by creation time."""
        with get_session_context() as session:
            statement = select(AsyncRequest).where(
                AsyncRequest.status == "pending"
            ).order_by(AsyncRequest.created_at).limit(limit)
            results = session.exec(statement).all()
            for r in results:
                session.expunge(r)
            return list(results)

    def get_queue_position(self, request_id: str) -> int | None:
        """Get position of a request in the pending queue."""
        with get_session_context() as session:
            # Get the request's created_at
            request = session.get(AsyncRequest, UUID(request_id))
            if not request:
                return None

            # Count pending requests created before this one
            statement = select(func.count()).select_from(AsyncRequest).where(
                AsyncRequest.status == "pending",
                AsyncRequest.created_at < request.created_at,
            )
            count = session.exec(statement).one()
            return count + 1  # 1-based position

    # ==========================================================================
    # Rate Limit Operations
    # ==========================================================================

    def record_rate_limit_event(self, api_key: str, event_type: str) -> None:
        """Record a rate limit event."""
        with get_session_context() as session:
            event = RateLimitEvent(
                api_key=api_key,
                event_type=event_type,
            )
            session.add(event)

    def count_recent_requests(self, api_key: str, seconds: int = 60) -> int:
        """Count requests in the last N seconds."""
        with get_session_context() as session:
            cutoff = datetime.utcnow() - timedelta(seconds=seconds)
            statement = select(func.count()).select_from(RateLimitEvent).where(
                RateLimitEvent.api_key == api_key,
                RateLimitEvent.event_type == "request",
                RateLimitEvent.created_at > cutoff,
            )
            return session.exec(statement).one()

    # ==========================================================================
    # Cleanup Operations
    # ==========================================================================

    def delete_expired_requests(self) -> int:
        """Delete requests that have expired. Returns count of deleted rows."""
        with get_session_context() as session:
            now = datetime.utcnow()
            statement = select(AsyncRequest).where(AsyncRequest.expires_at < now)
            expired = session.exec(statement).all()
            count = len(expired)
            for request in expired:
                session.delete(request)
            logger.info(f"Deleted {count} expired async requests")
            return count

    def cleanup_old_rate_limit_events(self, hours: int = 1) -> int:
        """Delete rate limit events older than N hours."""
        with get_session_context() as session:
            cutoff = datetime.utcnow() - timedelta(hours=hours)
            statement = select(RateLimitEvent).where(
                RateLimitEvent.created_at < cutoff
            )
            old_events = session.exec(statement).all()
            count = len(old_events)
            for event in old_events:
                session.delete(event)
            return count

    def reset_stale_processing_requests(
        self,
        stale_minutes: int = 10,
        max_retries: int = 3,
    ) -> int:
        """
        Reset requests stuck in 'processing' status.

        Requests that have been processing for more than stale_minutes
        are considered stale. They are either reset to 'pending' (if under
        max_retries) or set to 'failed'.
        """
        with get_session_context() as session:
            cutoff = datetime.utcnow() - timedelta(minutes=stale_minutes)
            reset_count = 0

            # Find stale processing requests
            statement = select(AsyncRequest).where(
                AsyncRequest.status == "processing",
                AsyncRequest.started_at < cutoff,
            )
            stale_requests = session.exec(statement).all()

            for request in stale_requests:
                if request.retry_count < max_retries:
                    request.status = "pending"
                    request.started_at = None
                else:
                    request.status = "failed"
                    request.error_message = "Processing timeout after max retries"
                session.add(request)
                reset_count += 1

            if reset_count > 0:
                logger.warning(f"Reset {reset_count} stale processing requests")
            return reset_count
