import logging
from typing import Optional, Dict, Any, List

from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession

from models.ticket_sync_states import Ticket_sync_states

logger = logging.getLogger(__name__)


# ------------------ Service Layer ------------------
class Ticket_sync_statesService:
    """Service layer for Ticket_sync_states operations"""

    def __init__(self, db: AsyncSession):
        self.db = db

    async def create(self, data: Dict[str, Any], user_id: Optional[str] = None) -> Optional[Ticket_sync_states]:
        """Create a new ticket_sync_states"""
        try:
            if user_id:
                data['user_id'] = user_id
            obj = Ticket_sync_states(**data)
            self.db.add(obj)
            await self.db.commit()
            await self.db.refresh(obj)
            logger.info(f"Created ticket_sync_states with id: {obj.id}")
            return obj
        except Exception as e:
            await self.db.rollback()
            logger.error(f"Error creating ticket_sync_states: {str(e)}")
            raise

    async def check_ownership(self, obj_id: int, user_id: str) -> bool:
        """Check if user owns this record"""
        try:
            obj = await self.get_by_id(obj_id, user_id=user_id)
            return obj is not None
        except Exception as e:
            logger.error(f"Error checking ownership for ticket_sync_states {obj_id}: {str(e)}")
            return False

    async def get_by_id(self, obj_id: int, user_id: Optional[str] = None) -> Optional[Ticket_sync_states]:
        """Get ticket_sync_states by ID (user can only see their own records)"""
        try:
            query = select(Ticket_sync_states).where(Ticket_sync_states.id == obj_id)
            if user_id:
                query = query.where(Ticket_sync_states.user_id == user_id)
            result = await self.db.execute(query)
            return result.scalar_one_or_none()
        except Exception as e:
            logger.error(f"Error fetching ticket_sync_states {obj_id}: {str(e)}")
            raise

    async def get_list(
        self, 
        skip: int = 0, 
        limit: int = 20, 
        user_id: Optional[str] = None,
        query_dict: Optional[Dict[str, Any]] = None,
        sort: Optional[str] = None,
    ) -> Dict[str, Any]:
        """Get paginated list of ticket_sync_statess (user can only see their own records)"""
        try:
            query = select(Ticket_sync_states)
            count_query = select(func.count(Ticket_sync_states.id))
            
            if user_id:
                query = query.where(Ticket_sync_states.user_id == user_id)
                count_query = count_query.where(Ticket_sync_states.user_id == user_id)
            
            if query_dict:
                for field, value in query_dict.items():
                    if hasattr(Ticket_sync_states, field):
                        query = query.where(getattr(Ticket_sync_states, field) == value)
                        count_query = count_query.where(getattr(Ticket_sync_states, field) == value)
            
            count_result = await self.db.execute(count_query)
            total = count_result.scalar()

            if sort:
                if sort.startswith('-'):
                    field_name = sort[1:]
                    if hasattr(Ticket_sync_states, field_name):
                        query = query.order_by(getattr(Ticket_sync_states, field_name).desc())
                else:
                    if hasattr(Ticket_sync_states, sort):
                        query = query.order_by(getattr(Ticket_sync_states, sort))
            else:
                query = query.order_by(Ticket_sync_states.id.desc())

            result = await self.db.execute(query.offset(skip).limit(limit))
            items = result.scalars().all()

            return {
                "items": items,
                "total": total,
                "skip": skip,
                "limit": limit,
            }
        except Exception as e:
            logger.error(f"Error fetching ticket_sync_states list: {str(e)}")
            raise

    async def update(self, obj_id: int, update_data: Dict[str, Any], user_id: Optional[str] = None) -> Optional[Ticket_sync_states]:
        """Update ticket_sync_states (requires ownership)"""
        try:
            obj = await self.get_by_id(obj_id, user_id=user_id)
            if not obj:
                logger.warning(f"Ticket_sync_states {obj_id} not found for update")
                return None
            for key, value in update_data.items():
                if hasattr(obj, key) and key != 'user_id':
                    setattr(obj, key, value)

            await self.db.commit()
            await self.db.refresh(obj)
            logger.info(f"Updated ticket_sync_states {obj_id}")
            return obj
        except Exception as e:
            await self.db.rollback()
            logger.error(f"Error updating ticket_sync_states {obj_id}: {str(e)}")
            raise

    async def delete(self, obj_id: int, user_id: Optional[str] = None) -> bool:
        """Delete ticket_sync_states (requires ownership)"""
        try:
            obj = await self.get_by_id(obj_id, user_id=user_id)
            if not obj:
                logger.warning(f"Ticket_sync_states {obj_id} not found for deletion")
                return False
            await self.db.delete(obj)
            await self.db.commit()
            logger.info(f"Deleted ticket_sync_states {obj_id}")
            return True
        except Exception as e:
            await self.db.rollback()
            logger.error(f"Error deleting ticket_sync_states {obj_id}: {str(e)}")
            raise

    async def get_by_field(self, field_name: str, field_value: Any) -> Optional[Ticket_sync_states]:
        """Get ticket_sync_states by any field"""
        try:
            if not hasattr(Ticket_sync_states, field_name):
                raise ValueError(f"Field {field_name} does not exist on Ticket_sync_states")
            result = await self.db.execute(
                select(Ticket_sync_states).where(getattr(Ticket_sync_states, field_name) == field_value)
            )
            return result.scalar_one_or_none()
        except Exception as e:
            logger.error(f"Error fetching ticket_sync_states by {field_name}: {str(e)}")
            raise

    async def list_by_field(
        self, field_name: str, field_value: Any, skip: int = 0, limit: int = 20
    ) -> List[Ticket_sync_states]:
        """Get list of ticket_sync_statess filtered by field"""
        try:
            if not hasattr(Ticket_sync_states, field_name):
                raise ValueError(f"Field {field_name} does not exist on Ticket_sync_states")
            result = await self.db.execute(
                select(Ticket_sync_states)
                .where(getattr(Ticket_sync_states, field_name) == field_value)
                .offset(skip)
                .limit(limit)
                .order_by(Ticket_sync_states.id.desc())
            )
            return result.scalars().all()
        except Exception as e:
            logger.error(f"Error fetching ticket_sync_statess by {field_name}: {str(e)}")
            raise