from typing import List, Optional, Tuple, Dict, Any
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from fastapi import HTTPException

from .models import Equivalency, EquivalencyItem
from .dto import EquivalencyCreate, EquivalencyUpdate, EquivalencyItemCreate, EquivalencyItemUpdate


class EquivalencyItemService:
    """CRUD simple sobre el pool global de items."""

    @staticmethod
    def get_all(
        db: Session,
        tenant_id: int,
        company_id: int,
        skip: int = 0,
        limit: int = 100,
        filters: Optional[Dict[str, Any]] = None
    ) -> Tuple[List[EquivalencyItem], int]:
        query = db.query(EquivalencyItem).filter(
            EquivalencyItem.tenant_id == tenant_id,
            EquivalencyItem.company_id == company_id,
        )

        if filters:
            if "original_field" in filters:
                query = query.filter(
                    EquivalencyItem.original_field.ilike(f"%{filters['original_field']}%")
                )
            if "external_field" in filters:
                query = query.filter(
                    EquivalencyItem.external_field.ilike(f"%{filters['external_field']}%")
                )

        total = query.count()
        items = query.offset(skip).limit(limit).all()
        return items, total

    @staticmethod
    def get_by_id(
        db: Session,
        id: int,
        tenant_id: int,
        company_id: int
    ) -> Optional[EquivalencyItem]:
        return db.query(EquivalencyItem).filter(
            EquivalencyItem.id == id,
            EquivalencyItem.tenant_id == tenant_id,
            EquivalencyItem.company_id == company_id
        ).first()

    @staticmethod
    def create(
        db: Session,
        data: EquivalencyItemCreate,
        tenant_id: int,
        company_id: int
    ) -> EquivalencyItem:
        try:
            db_obj = EquivalencyItem(
                original_field=data.original_field,
                external_field=data.external_field,
                conversion_factor=data.conversion_factor,
                tenant_id=tenant_id,
                company_id=company_id,
            )
            db.add(db_obj)
            db.commit()
            db.refresh(db_obj)
            return db_obj
        except IntegrityError as e:
            db.rollback()
            error_msg = str(e.orig) if hasattr(e, "orig") else str(e)
            if "uq_equivalency_item_fields" in error_msg:
                raise HTTPException(
                    status_code=400,
                    detail=f"Ya existe un item con los mismos campos '{data.original_field}' → '{data.external_field}'."
                ) from e
            raise HTTPException(status_code=400, detail=f"Error al guardar: {error_msg}") from e

    @staticmethod
    def update(
        db: Session,
        id: int,
        tenant_id: int,
        data: EquivalencyItemUpdate,
        company_id: int
    ) -> Optional[EquivalencyItem]:
        db_obj = EquivalencyItemService.get_by_id(db, id, tenant_id, company_id)
        if not db_obj:
            return None

        try:
            update_dict = data.model_dump(exclude_unset=True)
            for key, value in update_dict.items():
                setattr(db_obj, key, value)
            db.commit()
            db.refresh(db_obj)
            return db_obj
        except IntegrityError as e:
            db.rollback()
            error_msg = str(e.orig) if hasattr(e, "orig") else str(e)
            raise HTTPException(status_code=400, detail=f"Error al actualizar: {error_msg}") from e

    @staticmethod
    def delete(
        db: Session,
        id: int,
        tenant_id: int,
        company_id: int
    ) -> bool:
        db_obj = EquivalencyItemService.get_by_id(db, id, tenant_id, company_id)
        if not db_obj:
            return False
        db.delete(db_obj)
        db.commit()
        return True


class EquivalencyService:
    """CRUD del catálogo de equivalencias. Cada registro referencia un EquivalencyItem."""

    @staticmethod
    def get_all(
        db: Session,
        tenant_id: int,
        company_id: int,
        skip: int = 0,
        limit: int = 100,
        filters: Optional[Dict[str, Any]] = None
    ) -> Tuple[List[Equivalency], int]:
        query = db.query(Equivalency).filter(
            Equivalency.tenant_id == tenant_id,
            Equivalency.company_id == company_id,
        )

        if filters:
            if "from_unit_code" in filters:
                query = query.filter(
                    Equivalency.identifier.ilike(f"%{filters['from_unit_code']}%")
                )
            if "identifier" in filters:
                query = query.filter(
                    Equivalency.identifier.ilike(f"%{filters['identifier']}%")
                )
            if "description" in filters:
                query = query.filter(
                    Equivalency.description.ilike(f"%{filters['description']}%")
                )

        total = query.count()
        items = query.offset(skip).limit(limit).all()
        return items, total

    @staticmethod
    def get_by_id(
        db: Session,
        id: int,
        tenant_id: int,
        company_id: int
    ) -> Optional[Equivalency]:
        return db.query(Equivalency).filter(
            Equivalency.id == id,
            Equivalency.tenant_id == tenant_id,
            Equivalency.company_id == company_id
        ).first()

    @staticmethod
    def create(
        db: Session,
        data: EquivalencyCreate,
        tenant_id: int,
        company_id: int
    ) -> Equivalency:
        try:
            db_obj = Equivalency(
                identifier=data.identifier,
                description=data.description,
                item_id=data.item_id,
                tenant_id=tenant_id,
                company_id=company_id,
            )
            db.add(db_obj)
            db.commit()
            db.refresh(db_obj)
            return db_obj
        except IntegrityError as e:
            db.rollback()
            error_msg = str(e.orig) if hasattr(e, "orig") else str(e)
            if "uq_equivalency_identifier" in error_msg:
                raise HTTPException(
                    status_code=400,
                    detail=f"Ya existe un registro para el identificador '{data.identifier}'.",
                ) from e
            raise HTTPException(status_code=400, detail=f"Error al guardar: {error_msg}") from e

    @staticmethod
    def update(
        db: Session,
        id: int,
        tenant_id: int,
        data: EquivalencyUpdate,
        company_id: int
    ) -> Optional[Equivalency]:
        db_obj = EquivalencyService.get_by_id(db, id, tenant_id, company_id)
        if not db_obj:
            return None

        try:
            update_dict = data.model_dump(exclude_unset=True)
            for key, value in update_dict.items():
                setattr(db_obj, key, value)
            db.commit()
            db.refresh(db_obj)
            return db_obj
        except IntegrityError as e:
            db.rollback()
            error_msg = str(e.orig) if hasattr(e, "orig") else str(e)
            raise HTTPException(status_code=400, detail=f"Error al actualizar: {error_msg}") from e

    @staticmethod
    def delete(
        db: Session,
        id: int,
        tenant_id: int,
        company_id: int
    ) -> bool:
        db_obj = EquivalencyService.get_by_id(db, id, tenant_id, company_id)
        if not db_obj:
            return False
        db.delete(db_obj)
        db.commit()
        return True
