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

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


class EquivalencyService:
    @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
        ).options(joinedload(Equivalency.items))

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

        total = query.count()
        items = query.offset(skip).limit(limit).all()
        
        # Map internal fields to DTO fields
        for item in items:
            item.fraccion_mex = item.identifier
            # Try to find the first item to get fraccion_us
            if item.items:
                item.fraccion_us = item.items[0].external_field
            else:
                item.fraccion_us = ""
                
        return items, total

    @staticmethod
    def get_by_id(
        db: Session,
        id: int,
        tenant_id: int,
        company_id: int
    ) -> Optional[Equivalency]:
        item = db.query(Equivalency).filter(
            Equivalency.id == id,
            Equivalency.tenant_id == tenant_id,
            Equivalency.company_id == company_id
        ).options(joinedload(Equivalency.items)).first()
        
        if item:
            item.fraccion_mex = item.identifier
            if item.items:
                item.fraccion_us = item.items[0].external_field
            else:
                item.fraccion_us = ""
                
        return item

    @staticmethod
    def create(
        db: Session,
        data: EquivalencyCreate,
        tenant_id: int,
        company_id: int
    ) -> Equivalency:
        try:
            # Create Parent
            db_obj = Equivalency(
                identifier=data.fraccion_mex,
                description=data.description,
                tenant_id=tenant_id,
                company_id=company_id
            )
            db.add(db_obj)
            db.flush() # Flush to get ID

            # Create Child Item (mapping fraccion_mex -> original_field, fraccion_us -> external_field)
            item = EquivalencyItem(
                equivalency_id=db_obj.id,
                original_field=data.fraccion_mex, # Must exist in units_of_measure
                external_field=data.fraccion_us,
                tenant_id=tenant_id,
                company_id=company_id
            )
            db.add(item)
            
            db.commit()
            db.refresh(db_obj)
            
            # Map for response
            db_obj.fraccion_mex = db_obj.identifier
            db_obj.fraccion_us = item.external_field
            
            return db_obj
        except IntegrityError as e:
            db.rollback()
            error_msg = str(e.orig) if hasattr(e, 'orig') else str(e)
            print(f"IntegrityError in create: {error_msg}")
            
            if "units_of_measure" in error_msg:
                raise HTTPException(
                    status_code=400, 
                    detail=f"La Fracción MX '{data.fraccion_mex}' no es válida. Debe existir en el catálogo de Unidades de Medida."
                )
            if "uq_equivalency_identifier" in error_msg:
                raise HTTPException(
                    status_code=400, 
                    detail=f"Ya existe una equivalencia para la Fracción MX '{data.fraccion_mex}'."
                )
            raise HTTPException(status_code=400, detail=f"Error al guardar: {error_msg}")
        except Exception as e:
            db.rollback()
            print(f"Error in create: {str(e)}")
            raise 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:
            if data.fraccion_mex:
                db_obj.identifier = data.fraccion_mex
            if data.description:
                db_obj.description = data.description

            # Update Item
            item = None
            if db_obj.items:
                item = db_obj.items[0]
            
            if item:
                if data.fraccion_us:
                    item.external_field = data.fraccion_us
                if data.fraccion_mex:
                    item.original_field = data.fraccion_mex
            else:
                # Create if missing
                if data.fraccion_us or data.fraccion_mex:
                    item = EquivalencyItem(
                        equivalency_id=db_obj.id,
                        original_field=data.fraccion_mex or db_obj.identifier,
                        external_field=data.fraccion_us or "",
                        tenant_id=tenant_id,
                        company_id=company_id
                    )
                    db.add(item)

            db.commit()
            db.refresh(db_obj)
            
            # Map for response
            db_obj.fraccion_mex = db_obj.identifier
            if db_obj.items:
                 db_obj.fraccion_us = db_obj.items[0].external_field
            else:
                 db_obj.fraccion_us = ""
                 
            return db_obj
        except IntegrityError as e:
            db.rollback()
            error_msg = str(e.orig) if hasattr(e, 'orig') else str(e)
            print(f"IntegrityError in update: {error_msg}")
            
            if "units_of_measure" in error_msg:
                raise HTTPException(
                    status_code=400, 
                    detail=f"La Fracción MX '{data.fraccion_mex or db_obj.identifier}' no es válida. Debe existir en el catálogo de Unidades de Medida."
                )
            raise HTTPException(status_code=400, detail=f"Error al actualizar: {error_msg}")
        except Exception as e:
            db.rollback()
            print(f"Error in update: {str(e)}")
            raise 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

class EquivalencyItemService:
    @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
        )
        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:
        db_obj = EquivalencyItem(
            **data.model_dump(),
            tenant_id=tenant_id,
            company_id=company_id
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

    @staticmethod
    def create_nested(
        db: Session,
        equivalency_id: int,
        data: EquivalencyItemCreate,
        tenant_id: int,
        company_id: int
    ) -> EquivalencyItem:
        db_obj = EquivalencyItem(
            equivalency_id=equivalency_id,
            original_field=data.original_field,
            external_field=data.external_field,
            tenant_id=tenant_id,
            company_id=company_id
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj

    @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
        
        for key, value in data.model_dump(exclude_unset=True).items():
            setattr(db_obj, key, value)
            
        db.commit()
        db.refresh(db_obj)
        return db_obj

    @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
