# repository/activity_percentage_repo.py

import datetime
import asyncio
from typing import Optional
from sqlalchemy import select, desc
from ws_package.models.base import ActivityPercentage, async_session
from ws_package.entities.activity_entity import ActivityPercentageEntity
from ws_package.log.writer import LogWriter
from ws_package.utils.db_lock_manager import db_lock_manager

logger = LogWriter.file(__name__)

# Database operation timeout to prevent indefinite hanging
DB_OPERATION_TIMEOUT = 5.0  # 5 seconds timeout for database operations


class ActivityPercentageManager:
    # Using single database approach - ActivityPercentage stored in ws_monitor.db
    # Connection pooling ensures efficient connection management
    
    async def get_latest_activity_percentage(self):
        """
        Get the latest activity percentage record.
        
        CRITICAL FIX: Extracts all attributes while session is still open and returns
        ActivityPercentageEntity instead of SQLAlchemy ORM object. This prevents lazy-loading
        issues after the session closes and ensures the read lock releases immediately.
        """
        # Use main database lock manager - single database approach
        try:
            with db_lock_manager.read_lock():
                async with async_session() as session:
                    query = select(ActivityPercentage).order_by(desc(ActivityPercentage.id)).limit(1)
                    try:
                        result = await asyncio.wait_for(
                            session.scalars(query),
                            timeout=DB_OPERATION_TIMEOUT
                        )
                        record = result.first()
                        
                        # CRITICAL FIX: Extract all attributes while session is still open
                        # This prevents lazy-loading issues after the session closes
                        if record is None:
                            return None
                        
                        # Create ActivityPercentageEntity with all values loaded
                        # This ensures no lazy loading happens after session closes
                        return ActivityPercentageEntity(
                            start=record.start,
                            stop=record.stop,
                            keyboard=record.keyboard,
                            mouse=record.mouse,
                            average=record.average,
                            minute=record.minute
                        )
                    except asyncio.TimeoutError:
                        logger.error("Database query timed out after %ss in get_latest_activity_percentage", DB_OPERATION_TIMEOUT)
                        return None
        except Exception as e:
            logger.error("Failed to get latest activity percentage: %s", e, exc_info=True)
            return None

    async def save_activity_percentage(self, activity_percentage_entity: ActivityPercentageEntity):
        """Save a new activity percentage record"""
        try:
            # Use main database lock manager - single database approach
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    activity_percentage = ActivityPercentage(
                        **activity_percentage_entity.model_dump()
                    )
                    session.add(activity_percentage)
                    try:
                        await asyncio.wait_for(session.commit(), timeout=DB_OPERATION_TIMEOUT)
                        
                        # Refresh is optional - if it fails, the commit already succeeded
                        try:
                            await asyncio.wait_for(session.refresh(activity_percentage), timeout=DB_OPERATION_TIMEOUT)
                        except Exception as refresh_error:
                            # Refresh failure is non-critical - commit already succeeded
                            logger.warning(
                                "Could not refresh ActivityPercentage after commit (non-critical): %s. Commit succeeded, data is persisted.",
                                refresh_error
                            )

                        logger.info("Activity percentage saved: %s to %s", activity_percentage.start, activity_percentage.stop)
                        return activity_percentage
                    except asyncio.TimeoutError:
                        logger.error("Database operation timed out after %ss in save_activity_percentage", DB_OPERATION_TIMEOUT)
                        return False
        except Exception as e:
            logger.error("Failed to save activity percentage: %s", e, exc_info=True)
            return False

    async def update_latest_activity_percentage(self, activity_percentage_entity: ActivityPercentageEntity):
        """Update the latest activity percentage record with new rolling averages"""
        try:
            # Use main database lock manager - single database approach
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    # Get the latest record
                    query = select(ActivityPercentage).order_by(desc(ActivityPercentage.id)).limit(1)
                    try:
                        result = await asyncio.wait_for(session.scalars(query), timeout=DB_OPERATION_TIMEOUT)
                        latest_record = result.first()
                    except asyncio.TimeoutError:
                        logger.error("Database query timed out after %ss in update_latest_activity_percentage", DB_OPERATION_TIMEOUT)
                        return False
                    
                    if latest_record:
                        # Update the existing record
                        latest_record.start = activity_percentage_entity.start
                        latest_record.stop = activity_percentage_entity.stop
                        latest_record.keyboard = activity_percentage_entity.keyboard
                        latest_record.mouse = activity_percentage_entity.mouse
                        latest_record.average = activity_percentage_entity.average
                        latest_record.minute = activity_percentage_entity.minute

                        try:
                            await asyncio.wait_for(session.commit(), timeout=DB_OPERATION_TIMEOUT)

                            # Refresh is optional - if it fails, the commit already succeeded
                            try:
                                await asyncio.wait_for(session.refresh(latest_record), timeout=DB_OPERATION_TIMEOUT)
                            except Exception as refresh_error:
                                # Refresh failure is non-critical - commit already succeeded
                                logger.warning(
                                    "Could not refresh ActivityPercentage after commit (non-critical): %s. "
                                    "Commit succeeded, data is persisted.",
                                    refresh_error
                                )

                            return latest_record
                        except asyncio.TimeoutError:
                            logger.error("Database commit timed out after %ss in update_latest_activity_percentage", DB_OPERATION_TIMEOUT)
                            return False
                    else:
                        # No existing record, create a new one
                        activity_percentage = ActivityPercentage(
                            **activity_percentage_entity.model_dump()
                        )
                        session.add(activity_percentage)
                        try:
                            await asyncio.wait_for(session.commit(), timeout=DB_OPERATION_TIMEOUT)

                            # Refresh is optional - if it fails, the commit already succeeded
                            try:
                                await asyncio.wait_for(session.refresh(activity_percentage), timeout=DB_OPERATION_TIMEOUT)
                            except Exception as refresh_error:
                                # Refresh failure is non-critical - commit already succeeded
                                logger.warning(
                                    "Could not refresh ActivityPercentage after commit (non-critical): %s. "
                                    "Commit succeeded, data is persisted.",
                                    refresh_error
                                )

                            return activity_percentage
                        except asyncio.TimeoutError:
                            logger.error("Database commit timed out after %ss in update_latest_activity_percentage", DB_OPERATION_TIMEOUT)
                            return False
        except Exception as e:
            logger.error("Failed to update activity percentage: %s", e, exc_info=True)
            return False

    async def update_latest_activity_percentage_atomic(self, activity_percentage_entity: ActivityPercentageEntity):
        """Atomic update operation that prevents deadlocks by using a single write lock"""
        try:
            # Use main database lock manager - single database approach
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    # Get the latest record within the same lock
                    query = select(ActivityPercentage).order_by(desc(ActivityPercentage.id)).limit(1)
                    try:
                        result = await asyncio.wait_for(session.scalars(query), timeout=DB_OPERATION_TIMEOUT)
                        latest_record = result.first()
                    except asyncio.TimeoutError:
                        logger.error("Database query timed out after %ss in update_latest_activity_percentage_atomic", DB_OPERATION_TIMEOUT)
                        return False
                    
                    if latest_record:
                        # Update the existing record
                        latest_record.start = activity_percentage_entity.start
                        latest_record.stop = activity_percentage_entity.stop
                        latest_record.keyboard = activity_percentage_entity.keyboard
                        latest_record.mouse = activity_percentage_entity.mouse
                        latest_record.average = activity_percentage_entity.average
                        latest_record.minute = activity_percentage_entity.minute

                        try:
                            await asyncio.wait_for(session.commit(), timeout=DB_OPERATION_TIMEOUT)

                            # Refresh is optional - if it fails, the commit already succeeded
                            try:
                                await asyncio.wait_for(session.refresh(latest_record), timeout=DB_OPERATION_TIMEOUT)
                            except Exception as refresh_error:
                                # Refresh failure is non-critical - commit already succeeded
                                logger.warning(
                                    "Could not refresh ActivityPercentage after commit (non-critical): %s. "
                                    "Commit succeeded, data is persisted.",
                                    refresh_error
                                )

                            return latest_record
                        except asyncio.TimeoutError:
                            logger.error("Database commit timed out after %ss in update_latest_activity_percentage_atomic", DB_OPERATION_TIMEOUT)
                            return False
                    else:
                        # No existing record, create a new one
                        activity_percentage = ActivityPercentage(
                            **activity_percentage_entity.model_dump()
                        )
                        session.add(activity_percentage)
                        try:
                            await asyncio.wait_for(session.commit(), timeout=DB_OPERATION_TIMEOUT)

                            # Refresh is optional - if it fails, the commit already succeeded
                            try:
                                await asyncio.wait_for(session.refresh(activity_percentage), timeout=DB_OPERATION_TIMEOUT)
                            except Exception as refresh_error:
                                # Refresh failure is non-critical - commit already succeeded
                                logger.warning(
                                    "Could not refresh ActivityPercentage after commit (non-critical): %s. "
                                    "Commit succeeded, data is persisted.",
                                    refresh_error
                                )

                            return activity_percentage
                        except asyncio.TimeoutError:
                            logger.error("Database commit timed out after %ss in update_latest_activity_percentage_atomic", DB_OPERATION_TIMEOUT)
                            return False
        except Exception as e:
            logger.error("Failed to atomically update activity percentage: %s", e, exc_info=True)
            return False

    async def get_activity_percentage_by_date_range(self, start_date: Optional[datetime.datetime] = None, end_date: Optional[datetime.datetime] = None):
        """Get activity percentage records within a date range"""
        # Use main database lock manager - single database approach
        try:
            with db_lock_manager.read_lock():
                async with async_session() as session:
                    query = select(ActivityPercentage)
                    if start_date is not None:
                        query = query.where(ActivityPercentage.start >= start_date)
                    
                    if end_date is not None:
                        query = query.where(ActivityPercentage.stop <= end_date)

                    try:
                        result = await asyncio.wait_for(
                            session.scalars(query),
                            timeout=DB_OPERATION_TIMEOUT
                        )
                        return result.all()
                    except asyncio.TimeoutError:
                        logger.error("Database query timed out after %ss in get_activity_percentage_by_date_range", DB_OPERATION_TIMEOUT)
                        return []
        except Exception as e:
            logger.error("Failed to get activity percentage by date range (start=%s, end=%s): %s", start_date, end_date, e, exc_info=True)
            return []

    async def get_all_activity_percentages(self):
        """Get all activity percentage records"""
        # Use main database lock manager - single database approach
        try:
            with db_lock_manager.read_lock():
                async with async_session() as session:
                    query = select(ActivityPercentage).order_by(desc(ActivityPercentage.id))
                    try:
                        result = await asyncio.wait_for(
                            session.scalars(query),
                            timeout=DB_OPERATION_TIMEOUT
                        )
                        return result.all()
                    except asyncio.TimeoutError:
                        logger.error("Database query timed out after %ss in get_all_activity_percentages", DB_OPERATION_TIMEOUT)
                        return []
        except Exception as e:
            logger.error("Failed to get all activity percentages: %s", e, exc_info=True)
            return []

    async def find_overlapping_activity_percentage(self, start_time: datetime.datetime, end_time: datetime.datetime) -> Optional[ActivityPercentageEntity]:
        """
        Find activity percentage record that overlaps with the given time range.
        Overlap condition: start < end_time AND stop > start_time
        Returns ActivityPercentageEntity or None if not found.
        """
        try:
            with db_lock_manager.read_lock():
                async with async_session() as session:
                    query = select(ActivityPercentage).where(
                        ActivityPercentage.start < end_time,
                        ActivityPercentage.stop > start_time
                    ).order_by(desc(ActivityPercentage.minute)).limit(1)
                    
                    try:
                        result = await asyncio.wait_for(
                            session.scalars(query),
                            timeout=DB_OPERATION_TIMEOUT
                        )
                        record = result.first()
                    except asyncio.TimeoutError:
                        logger.error("Database query timed out after %ss in find_overlapping_activity_percentage", DB_OPERATION_TIMEOUT)
                        return None
                    
                    if record:
                        return ActivityPercentageEntity(
                            start=record.start,
                            stop=record.stop,
                            keyboard=record.keyboard,
                            mouse=record.mouse,
                            average=record.average,
                            minute=record.minute
                        )
                    return None
        except Exception as e:
            logger.error("Failed to find overlapping activity percentage (start=%s, end=%s): %s", start_time, end_time, e, exc_info=True)
            return None


activity_percentage_manager = ActivityPercentageManager()
