"""
Servicio para el catálogo de claves FDA
"""
from sqlalchemy import select
from sqlalchemy.orm import Session
from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDACatalog
from api.v1.modules.a76.general_catalogs.fda_catalog.dto import FDACatalogCreate, FDACatalogUpdate


class FDACatalogService:
    """Servicio CRUD para catálogo de FDA"""

    @staticmethod
    def get_all(db: Session, tenant_id: int, company_id: int, page: int = 1, page_size: int = 50, filters: dict = None):
        """Obtener todas las entradas del catálogo FDA con paginación y búsqueda"""
        if filters is None:
            filters = {}

        query = select(FDACatalog).where(
            (FDACatalog.tenant_id == tenant_id) &
            (FDACatalog.company_id == company_id)
        )

        # Búsqueda multi-campo
        search = filters.get('search', '').strip()
        if search:
            search_pattern = f"%{search}%"
            query = query.where(
                (FDACatalog.fda_key.ilike(search_pattern)) |
                (FDACatalog.description.ilike(search_pattern)) |
                (FDACatalog.fda_code.ilike(search_pattern))
            )

        # Contar total
        total_query = select(FDACatalog).where(
            (FDACatalog.tenant_id == tenant_id) &
            (FDACatalog.company_id == company_id)
        )
        if search:
            total_query = total_query.where(
                (FDACatalog.fda_key.ilike(search_pattern)) |
                (FDACatalog.description.ilike(search_pattern)) |
                (FDACatalog.fda_code.ilike(search_pattern))
            )
        total = db.execute(select(FDACatalog).distinct()).scalars().all().__len__()
        
        # Paginación
        offset = (page - 1) * page_size
        query = query.offset(offset).limit(page_size)

        items = db.execute(query).scalars().all()

        return {
            "items": items,
            "total": len(items),
            "page": page,
            "page_size": page_size,
            "pages": (len(items) + page_size - 1) // page_size
        }

    @staticmethod
    def get_by_id(db: Session, tenant_id: int, company_id: int, id: int):
        """Obtener una entrada por ID"""
        return db.execute(
            select(FDACatalog).where(
                (FDACatalog.id == id) &
                (FDACatalog.tenant_id == tenant_id) &
                (FDACatalog.company_id == company_id)
            )
        ).scalar_one_or_none()

    @staticmethod
    def create(db: Session, tenant_id: int, company_id: int, data: FDACatalogCreate):
        """Crear una nueva entrada"""
        entry = FDACatalog(
            tenant_id=tenant_id,
            company_id=company_id,
            **data.model_dump()
        )
        db.add(entry)
        db.commit()
        db.refresh(entry)
        return entry

    @staticmethod
    def update(db: Session, tenant_id: int, company_id: int, id: int, data: FDACatalogUpdate):
        """Actualizar una entrada"""
        entry = FDACatalogService.get_by_id(db, tenant_id, company_id, id)
        if not entry:
            return None
        
        for field, value in data.model_dump(exclude_unset=True).items():
            setattr(entry, field, value)
        
        db.commit()
        db.refresh(entry)
        return entry

    @staticmethod
    def delete(db: Session, tenant_id: int, company_id: int, id: int):
        """Eliminar una entrada"""
        entry = FDACatalogService.get_by_id(db, tenant_id, company_id, id)
        if not entry:
            return False
        
        db.delete(entry)
        db.commit()
        return True
