"""
Repository for queue operations using Tortoise ORM.
"""

from datetime import datetime
from typing import Any

from app.core.logging import get_logger
from app.models.queue import AIFeedbackQueue

logger = get_logger("queue_repository")


class QueueRepository:
    """Repository for queue operations."""

    async def get_pending_jobs(self, limit: int = 10) -> list[AIFeedbackQueue]:
        """Get pending jobs for processing with atomic locking to prevent race conditions."""
        try:
            from tortoise import Tortoise

            # Use raw SQL with SELECT FOR UPDATE SKIP LOCKED for atomic job claiming
            # This prevents race conditions by locking rows during selection
            conn = Tortoise.get_connection("default")

            query = """
                SELECT id FROM mip_ai_feedback_queue_tbl
                WHERE status = '0' AND data_process = '1'
                ORDER BY id ASC
                LIMIT %s
                FOR UPDATE SKIP LOCKED
            """

            rows = await conn.execute_query_dict(query, [limit])

            if not rows:
                logger.info("No pending jobs to process")
                return []

            job_ids = [row["id"] for row in rows]

            # Immediately mark as processing within same transaction to claim them
            updated = await AIFeedbackQueue.filter(id__in=job_ids, status="0").update(
                status="1", process_start_time=datetime.now()
            )

            logger.info(f"Atomically claimed {updated} jobs: {job_ids}")

            # Fetch the full job objects
            jobs = await AIFeedbackQueue.filter(id__in=job_ids).all()
            return jobs

        except Exception as e:
            logger.error(f"Failed to get pending jobs: {e}")
            return []

    async def get_job_by_id(self, job_id: str) -> AIFeedbackQueue | None:
        """Get a specific job by ID."""
        try:
            job = await AIFeedbackQueue.get_or_none(id=job_id)
            return job
        except Exception as e:
            logger.error(f"Failed to get job {job_id}: {e}")
            return None

    async def mark_job_processing(self, job_id: str) -> bool:
        """Mark a job as processing."""
        try:
            # Atomic claim: only update if job is still pending
            updated = await AIFeedbackQueue.filter(id=job_id, status="0").update(
                status="1", process_start_time=datetime.now()
            )
            if updated == 1:
                logger.info(f"Job {job_id} atomically claimed and marked as processing")
                return True
            logger.info(
                f"Job {job_id} was not claimed (already processing or finished)"
            )
            return False
        except Exception as e:
            logger.error(f"Failed to mark job {job_id} as processing: {e}")
            return False

    async def mark_job_completed(self, job_id: str, duration: str = "0") -> bool:
        """Mark a job as completed."""
        try:
            job = await AIFeedbackQueue.get_or_none(id=job_id)
            if job:
                job.status = "3"
                job.duration = duration
                job.process_end_time = datetime.now()
                await job.save()
                logger.info(f"Job {job_id} marked as completed")
                return True
            return False
        except Exception as e:
            logger.error(f"Failed to mark job {job_id} as completed: {e}")
            return False

    async def mark_job_failed(
        self, job_id: str, exception: str, max_retries: int = 3
    ) -> bool:
        """Mark a job as failed with intelligent retry logic."""
        try:
            job = await AIFeedbackQueue.get_or_none(id=job_id)
            if not job:
                logger.error(f"Job {job_id} not found")
                return False

            # Increment retry counter
            job.tries += 1

            # Store exception with attempt number
            job.exception = f"Attempt {job.tries}/{max_retries}: {exception}"
            job.process_end_time = datetime.now()

            # Determine if error is retryable
            is_retryable = self._is_retryable_error(exception)
            should_retry = job.tries < max_retries and is_retryable

            if should_retry:
                # Reset to pending for automatic retry
                job.status = "0"
                job.process_start_time = None
                logger.info(
                    f"Job {job_id} will be retried (attempt {job.tries}/{max_retries})"
                )
            else:
                # Permanently failed
                job.status = "2"
                reason = (
                    "non-retryable error"
                    if not is_retryable
                    else "max retries exceeded"
                )
                logger.error(
                    f"Job {job_id} permanently failed after {job.tries} attempts ({reason})"
                )

            await job.save()
            return True

        except Exception as e:
            logger.error(f"Failed to mark job {job_id} as failed: {e}")
            return False

    def _is_retryable_error(self, exception: str) -> bool:
        """
        Determine if an error is transient and should be retried.

        Retryable errors include:
        - Network/connection issues
        - Timeouts
        - HTTP 5xx errors (server errors)
        - HTTP 429 (rate limit)
        - AWS throttling errors
        """
        exception_lower = exception.lower()

        retryable_patterns = [
            "connection",
            "timeout",
            "network",
            "temporary",
            "throttle",
            "503",  # Service unavailable
            "502",  # Bad gateway
            "500",  # Internal server error (sometimes transient)
            "429",  # Too many requests
            "deadline",
            "unavailable",
        ]

        return any(pattern in exception_lower for pattern in retryable_patterns)

    async def check_timeout_jobs(self, timeout_hours: int = 5) -> list[AIFeedbackQueue]:
        """Check for jobs that have been processing for too long."""
        try:
            from datetime import datetime, timedelta

            timeout_threshold = datetime.now() - timedelta(hours=timeout_hours)

            timeout_jobs = await AIFeedbackQueue.filter(
                status="1", process_start_time__lt=timeout_threshold
            ).all()

            # Debug: check for any jobs with None IDs
            for i, job in enumerate(timeout_jobs):
                if not job.id:
                    logger.warning(f"Found job {i} with None ID: {job}")

            logger.info(f"Found {len(timeout_jobs)} timeout jobs")
            return timeout_jobs
        except Exception as e:
            logger.error(f"Failed to check timeout jobs: {e}")
            return []

    async def reset_timeout_job(self, job: AIFeedbackQueue) -> bool:
        """
        Reset a timed-out job for retry WITHOUT creating a new job ID.

        This maintains data integrity and proper tracking by reusing the same job ID.
        """
        try:
            if not job or not job.id:
                logger.error("Cannot reset job: job is None or has no ID")
                return False

            # Increment tries counter
            job.tries += 1

            # Check if we've exceeded max retries
            if job.tries >= 3:
                job.status = "2"  # Permanently failed
                job.exception = (
                    f"Job timed out after {job.tries} attempts - max retries exceeded"
                )
                await job.save()
                logger.error(
                    f"Job {job.id} permanently failed due to timeout after {job.tries} attempts"
                )
                return False

            # Reset job to pending state for retry (reusing same ID)
            job.status = "0"
            job.process_start_time = None
            job.process_end_time = None
            job.exception = f"Timeout on attempt {job.tries - 1} - queued for retry"

            await job.save()
            logger.info(f"Reset timeout job {job.id} for retry (attempt {job.tries}/3)")
            return True

        except Exception as e:
            logger.error(
                f"Failed to reset timeout job {getattr(job, 'id', 'unknown')}: {e}"
            )
            return False

    async def get_jobs_by_interview(
        self, interview_id: int, interview_type: str
    ) -> list[AIFeedbackQueue]:
        """Get all jobs for a specific interview."""
        try:
            jobs = await AIFeedbackQueue.filter(
                interview_id=interview_id, interview_type=interview_type
            ).all()
            logger.info(f"Found {len(jobs)} jobs for interview {interview_id}")
            return jobs
        except Exception as e:
            logger.error(f"Failed to get jobs for interview {interview_id}: {e}")
            return []

    async def get_jobs_by_status(
        self, status: str, limit: int = 100
    ) -> list[AIFeedbackQueue]:
        """Get jobs by status."""
        try:
            jobs = await AIFeedbackQueue.filter(status=status).limit(limit).all()
            logger.info(f"Found {len(jobs)} jobs with status {status}")
            return jobs
        except Exception as e:
            logger.error(f"Failed to get jobs for status {status}: {e}")
            return []

    async def get_unprocessed_data_jobs(self, limit: int = 50) -> list[AIFeedbackQueue]:
        """Get jobs where data_process = 0 (data not yet in S3)."""
        try:
            jobs = await AIFeedbackQueue.filter(data_process="0").limit(limit).all()
            logger.info(f"Found {len(jobs)} jobs with data_process = 0")
            return jobs
        except Exception as e:
            logger.error(f"Failed to get unprocessed data jobs: {e}")
            return []

    async def mark_data_processed(self, job_id: str) -> bool:
        """Mark a job's data as processed (data_process = 1)."""
        try:
            job = await AIFeedbackQueue.get_or_none(id=job_id)
            if job:
                job.data_process = "1"
                await job.save()
                logger.info(f"Job {job_id} marked as data_processed = 1")
                return True
            logger.warning(f"Job {job_id} not found when marking data_processed")
            return False
        except Exception as e:
            logger.error(f"Failed to mark job {job_id} as data_processed: {e}")
            return False

    async def get_queue_stats(self) -> dict:
        """Get queue statistics grouped by status."""
        try:
            # Get count by status
            stats = {}
            for status in ["0", "1", "2", "3"]:
                count = await AIFeedbackQueue.filter(status=status).count()
                stats[status] = count

            return stats
        except Exception as e:
            logger.error(f"Failed to get queue stats: {e}")
            return {}

    async def get_average_processing_time(self) -> float:
        """Get the average processing time for completed jobs."""
        try:
            completed_jobs = await AIFeedbackQueue.filter(status="3").all()
            if not completed_jobs:
                return 0

            def _to_seconds(value) -> float:
                if value is None:
                    return 0.0
                # Already numeric
                if isinstance(value, int | float):
                    return float(value)
                s = str(value).strip()
                if not s:
                    return 0.0
                # Handle simple integer seconds
                if s.isdigit():
                    return float(int(s))
                # Handle MM:SS or HH:MM:SS formats
                if ":" in s:
                    parts = [p.strip() for p in s.split(":")]
                    try:
                        if len(parts) == 2:
                            m, sec = int(parts[0]), int(parts[1])
                            return float(m * 60 + sec)
                        if len(parts) == 3:
                            h, m, sec = int(parts[0]), int(parts[1]), int(parts[2])
                            return float(h * 3600 + m * 60 + sec)
                    except Exception:
                        return 0.0
                # Fallback: try float
                try:
                    return float(s)
                except Exception:
                    return 0.0

            total_seconds = sum(_to_seconds(job.duration) for job in completed_jobs)
            return total_seconds / len(completed_jobs)
        except Exception as e:
            logger.error(f"Failed to get average processing time: {e}")
            return 0

    async def cleanup_invalid_data(self) -> dict[str, Any]:
        """Clean up any invalid data in the queue table."""
        try:
            logger.info("Starting cleanup of invalid data...")

            # Find all jobs with invalid interview_type lengths
            all_jobs = await AIFeedbackQueue.all()
            invalid_jobs = []
            fixed_jobs = []

            for job in all_jobs:
                if hasattr(job, "interview_type") and job.interview_type:
                    interview_type_str = str(job.interview_type)
                    if len(interview_type_str) > 1:
                        invalid_jobs.append(
                            {
                                "id": job.id,
                                "old_value": interview_type_str,
                                "new_value": interview_type_str[:1],
                            }
                        )

                        # Fix the invalid value
                        job.interview_type = interview_type_str[:1]
                        await job.save()
                        fixed_jobs.append(job.id)

            logger.info(
                f"Found {len(invalid_jobs)} jobs with invalid interview_type values"
            )
            logger.info(f"Fixed {len(fixed_jobs)} jobs")

            if invalid_jobs:
                logger.info("Invalid interview_type values found and fixed:")
                for job_info in invalid_jobs:
                    logger.info(
                        f"  Job {job_info['id']}: '{job_info['old_value']}' -> '{job_info['new_value']}'"
                    )

            return {
                "status": "completed",
                "invalid_jobs_found": len(invalid_jobs),
                "jobs_fixed": len(fixed_jobs),
                "details": invalid_jobs,
            }

        except Exception as e:
            logger.error(f"Failed to cleanup invalid data: {e}")
            return {"status": "error", "message": str(e)}