"""Repository for abacus_outbox operations."""

from __future__ import annotations

from datetime import datetime

from lambdacommon.common_config import logger
from pymysql.connections import Connection
from pymysql.cursors import DictCursor

from src.connectors.db import handle_mysql_errors
from src.enums import AbacusOutboxStatus
from src.schemas import AbacusOutbox


class Repository:
    """Repository for database operations."""

    def __init__(self, conn: Connection[DictCursor]) -> None:
        """Initialize repository.

        Args:
            conn: MySQL database connection
        """
        self.conn = conn

    def is_autocommit_enabled(self) -> bool:
        """Check if autocommit is enabled on the connection.

        Returns:
            bool: True if autocommit is enabled, False otherwise
        """
        return getattr(self.conn, 'autocommit_mode', False)

    @handle_mysql_errors
    def commit(self) -> None:
        """Commit the current transaction."""
        self.conn.commit()

    @handle_mysql_errors
    def get_pending_events(self, limit: int) -> list[AbacusOutbox]:
        """Get pending outbox events.

        Selects events that are:
        - Pending
        - Failed, awaiting retry

        Args:
            limit: Max number of events to fetch.

        Returns:
            List of AbacusOutbox objects.

        Raises:
            TransientError: If database connection fails (retriable)
            Exception: For other database errors
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                """
                SELECT
                    abacus_outbox_id,
                    target_type,
                    target_id,
                    event_type,
                    correlation_id,
                    details,
                    status,
                    retry_count,
                    max_retries,
                    created_at,
                    processed_at
                FROM abacus_outbox
                WHERE status = %s
                    OR (
                        status = %s
                        AND retry_count < max_retries
                        AND next_retry_at <= NOW()
                    )
                ORDER BY created_at ASC
                LIMIT %s
                FOR UPDATE SKIP LOCKED
                """,
                (AbacusOutboxStatus.PENDING, AbacusOutboxStatus.FAILED, limit),
            )
            return [AbacusOutbox(**row) for row in cursor.fetchall()]

    @handle_mysql_errors
    def rollback(self) -> None:
        """Rollback the current transaction."""
        self.conn.rollback()

    @handle_mysql_errors
    def update_event_status(
        self,
        event_id: int,
        status: str,
        error_message: str | None = None,
        next_retry_at: datetime | None = None,
        processed_at: datetime | None = None,
    ) -> int:
        """Update event status with optimistic locking.

        Only updates the event if it's in a valid state for transition.
        This prevents race conditions when multiple consumers process the same event.

        Args:
            event_id: Event ID
            status: New status
            error_message: Optional error message
            next_retry_at: Optional next retry timestamp
            processed_at: Optional processed timestamp

        Returns:
            int: Number of rows affected (0 if already completed, 1 if updated)
        """
        query_parts = ['UPDATE abacus_outbox SET status = %s']
        params: list[str | int | datetime] = [status]

        if error_message:
            query_parts.append(', error_message = %s')
            params.append(error_message)
            # Increment retry count on error
            query_parts.append(', retry_count = retry_count + 1')

        if next_retry_at:
            query_parts.append(', next_retry_at = %s')
            params.append(next_retry_at)

        if processed_at:
            query_parts.append(', processed_at = %s')
            params.append(processed_at)

        # Optimistic locking: only update if status is PENDING or FAILED
        # This prevents overwriting a COMPLETED status from another consumer
        query_parts.append(' WHERE abacus_outbox_id = %s AND status IN (%s, %s)')
        params.append(event_id)
        params.append(AbacusOutboxStatus.PENDING)
        params.append(AbacusOutboxStatus.FAILED)

        query = ''.join(query_parts)

        with self.conn.cursor() as cursor:
            rows_affected = cursor.execute(query, params)
            if rows_affected == 0:
                logger.info(f'Event {event_id} already completed by another consumer')
            return rows_affected