"""
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


class FDADetailsService:
    """Servicio CRUD para los detalles adicionales de FDA (Especificaciones, Lotes, etc)"""

    # FDA01 - Especificaciones
    @staticmethod
    def get_specifications(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDASpecifications
        return db.execute(
            select(FDASpecifications).where(
                (FDASpecifications.fda_catalog_id == fda_catalog_id) &
                (FDASpecifications.tenant_id == tenant_id) &
                (FDASpecifications.company_id == company_id)
            )
        ).scalar_one_or_none()

    @staticmethod
    def save_specifications(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int, data: dict):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDASpecifications
        existing = FDADetailsService.get_specifications(db, tenant_id, company_id, fda_catalog_id)
        if existing:
            for field, value in data.items():
                setattr(existing, field, value)
            entry = existing
        else:
            entry = FDASpecifications(
                tenant_id=tenant_id,
                company_id=company_id,
                fda_catalog_id=fda_catalog_id,
                **data
            )
            db.add(entry)
        db.commit()
        db.refresh(entry)
        return entry

    # FDA04 - Elementos Constitutivos
    @staticmethod
    def get_constituent_elements(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDAConstituentElements
        items = db.execute(
            select(FDAConstituentElements).where(
                (FDAConstituentElements.fda_catalog_id == fda_catalog_id) &
                (FDAConstituentElements.tenant_id == tenant_id) &
                (FDAConstituentElements.company_id == company_id)
            ).order_by(FDAConstituentElements.line.asc())
        ).scalars().all()
        return items

    @staticmethod
    def create_constituent_element(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int, data: dict):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDAConstituentElements
        entry = FDAConstituentElements(
            tenant_id=tenant_id,
            company_id=company_id,
            fda_catalog_id=fda_catalog_id,
            **data
        )
        db.add(entry)
        db.commit()
        db.refresh(entry)
        return entry

    @staticmethod
    def delete_constituent_element(db: Session, tenant_id: int, company_id: int, element_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDAConstituentElements
        entry = db.execute(select(FDAConstituentElements).where(
            (FDAConstituentElements.id == element_id) &
            (FDAConstituentElements.tenant_id == tenant_id) &
            (FDAConstituentElements.company_id == company_id)
        )).scalar_one_or_none()
        if entry:
            db.delete(entry)
            db.commit()
            return True
        return False

    # FDA23 - Códigos de Afirmación
    @staticmethod
    def get_affirmation_codes(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDAAffirmationCodes
        items = db.execute(
            select(FDAAffirmationCodes).where(
                (FDAAffirmationCodes.fda_catalog_id == fda_catalog_id) &
                (FDAAffirmationCodes.tenant_id == tenant_id) &
                (FDAAffirmationCodes.company_id == company_id)
            ).order_by(FDAAffirmationCodes.line.asc())
        ).scalars().all()
        return items

    @staticmethod
    def create_affirmation_code(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int, data: dict):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDAAffirmationCodes
        entry = FDAAffirmationCodes(
            tenant_id=tenant_id,
            company_id=company_id,
            fda_catalog_id=fda_catalog_id,
            **data
        )
        db.add(entry)
        db.commit()
        db.refresh(entry)
        return entry

    @staticmethod
    def delete_affirmation_code(db: Session, tenant_id: int, company_id: int, code_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDAAffirmationCodes
        entry = db.execute(select(FDAAffirmationCodes).where(
            (FDAAffirmationCodes.id == code_id) &
            (FDAAffirmationCodes.tenant_id == tenant_id) &
            (FDAAffirmationCodes.company_id == company_id)
        )).scalar_one_or_none()
        if entry:
            db.delete(entry)
            db.commit()
            return True
        return False

    # FDA25 - Lotes de Producción
    @staticmethod
    def get_lot_productions(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDALotProduction
        items = db.execute(
            select(FDALotProduction).where(
                (FDALotProduction.fda_catalog_id == fda_catalog_id) &
                (FDALotProduction.tenant_id == tenant_id) &
                (FDALotProduction.company_id == company_id)
            ).order_by(FDALotProduction.line.asc())
        ).scalars().all()
        return items

    @staticmethod
    def create_lot_production(db: Session, tenant_id: int, company_id: int, fda_catalog_id: int, data: dict):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDALotProduction
        entry = FDALotProduction(
            tenant_id=tenant_id,
            company_id=company_id,
            fda_catalog_id=fda_catalog_id,
            **data
        )
        db.add(entry)
        db.commit()
        db.refresh(entry)
        return entry

    @staticmethod
    def delete_lot_production(db: Session, tenant_id: int, company_id: int, lot_id: int):
        from api.v1.modules.a76.general_catalogs.fda_catalog.models import FDALotProduction
        entry = db.execute(select(FDALotProduction).where(
            (FDALotProduction.id == lot_id) &
            (FDALotProduction.tenant_id == tenant_id) &
            (FDALotProduction.company_id == company_id)
        )).scalar_one_or_none()
        if entry:
            db.delete(entry)
            db.commit()
            return True
        return False
