from datetime import datetime
from decimal import Decimal
from typing import Optional, List, Tuple
from sqlalchemy import select, or_, func
from sqlalchemy.orm import Session
from .models import HistoricalTariffFraction
from .schemas import HistoricalTariffFractionResponse, HistoricalTariffFractionCreate, HistoricalTariffFractionUpdate
from api.v1.modules.sitar.fracciones.service import FraccionesService

class HistoricalTariffFractionService:
    def __init__(self, db: Session):
        self.db = db

    def get(self, id: int, tenant_id: int, company_id: int) -> Optional[HistoricalTariffFraction]:
        return self.db.query(HistoricalTariffFraction).filter(
            HistoricalTariffFraction.id == id,
            HistoricalTariffFraction.tenant_id == tenant_id,
            HistoricalTariffFraction.company_id == company_id
        ).first()

    def get_multi(
        self,
        tenant_id: int,
        company_id: int,
        skip: int = 0,
        limit: int = 100,
        historical_fraction: Optional[str] = None
    ) -> Tuple[List[HistoricalTariffFraction], int]:
        query = select(HistoricalTariffFraction).where(
            HistoricalTariffFraction.tenant_id == tenant_id,
            HistoricalTariffFraction.company_id == company_id
        )
        
        if historical_fraction:
            query = query.where(HistoricalTariffFraction.historical_fraction.ilike(f"%{historical_fraction}%"))
            
        total = self.db.execute(select(func.count()).select_from(query.subquery())).scalar_one()
        # Add deterministic sort order
        query = query.order_by(HistoricalTariffFraction.historical_fraction)
        items = self.db.scalars(query.offset(skip).limit(limit)).all()
        return items, total

    def create(self, obj_in: HistoricalTariffFractionCreate, tenant_id: int, company_id: int) -> HistoricalTariffFraction:
        db_obj = HistoricalTariffFraction(**obj_in.model_dump())
        db_obj.tenant_id = tenant_id
        db_obj.company_id = company_id
        self.db.add(db_obj)
        self.db.commit()
        self.db.refresh(db_obj)
        return db_obj

    def update(
        self,
        db_obj: HistoricalTariffFraction,
        obj_in: HistoricalTariffFractionUpdate
    ) -> HistoricalTariffFraction:
        # db_obj already validated for tenant/company in get()
        update_data = obj_in.model_dump(exclude_unset=True)
        for field, value in update_data.items():
            setattr(db_obj, field, value)
        self.db.add(db_obj)
        self.db.commit()
        self.db.refresh(db_obj)
        return db_obj

    def delete(self, id: int, tenant_id: int, company_id: int) -> Optional[HistoricalTariffFraction]:
        obj = self.get(id, tenant_id, company_id)
        if obj:
            self.db.delete(obj)
            self.db.commit()
        return obj
    async def get_historical_rate(
        self,
        tenant_id: int,
        company_id: int,
        historical_fraction: str,
        nico: str,
        direction: str,
        tariff_type: str,
        invoice_date: datetime,
        is_regime_change: bool = False
    ) -> Optional[Decimal]:
        """
        Gets the historical tax rate based on the fraction, nico, and date.
        Equivalent to Clarion BUSCA_FRACCION_HISTORICA.
        """
        # Normalize input fraction to handle both 8-digit and unpadded (7-digit) versions
        fraction_variants = [historical_fraction]
        unpadded = historical_fraction.lstrip('0')
        if unpadded and unpadded != historical_fraction:
            fraction_variants.append(unpadded)

        query = select(HistoricalTariffFraction).where(
            HistoricalTariffFraction.tenant_id == tenant_id,
            HistoricalTariffFraction.company_id == company_id,
            HistoricalTariffFraction.historical_fraction.in_(fraction_variants),
            HistoricalTariffFraction.fraction_type.ilike(tariff_type), # Match GENERAL, PROSEC, etc.
            or_(
                HistoricalTariffFraction.nico == nico,
                HistoricalTariffFraction.nico.is_(None),
                HistoricalTariffFraction.nico == ''
            ),
            HistoricalTariffFraction.publication_date <= invoice_date
        ).order_by(HistoricalTariffFraction.publication_date.desc())
        
        result = self.db.execute(query).scalars().first()
        
        if result:
            # Special rule: If it's a regime change, always return the import rate (TasaImNum)
            # as per Clarion logic ASIGNA_FRACCION_HISTORICA
            if is_regime_change:
                return result.import_tax_rate
                
            # Otherwise return based on direction
            if direction.lower() == 'import':
                return result.import_tax_rate
            else:
                return result.export_tax_rate
        
        # 2. Priority 2: Try SITAR API (Modern source of truth)
        try:
            sitar_service = FraccionesService.get_instance()
            # Search by 8-digit fraction and 2-digit NICO
            sitar_data = await sitar_service.search(
                fraccion=historical_fraction,
                nico=nico,
                limit=1
            )
            
            if sitar_data:
                first_record = sitar_data[0]
                # Special rule: If it's a regime change, always return the import rate (TasaImNum)
                if is_regime_change:
                    return first_record.ADVIMPONUM
                
                # Otherwise return based on direction
                if direction.lower() == 'import':
                    return first_record.ADVIMPONUM
                else:
                    return first_record.ADVEXPONUM
        except Exception as e:
            # Log error but continue to fallback
            print(f"Error fetching data from SITAR API: {e}")
        
        # 3. Priority 3: Fallback to main TariffFraction catalog if not found in historical or SITAR
        from ..tariff_fractions.models import TariffFraction
        
        # In the main catalog, fractions might be stored with dots (e.g. 0101.90.99)
        # or as code (e.g. 01019099)
        # We search primarily by fraction code (8 digits) and take the first one found.
        # This handles cases where NICO doesn't match perfectly.
        fallback_query = select(TariffFraction).where(
            or_(
                TariffFraction.code == historical_fraction,
                TariffFraction.fraction == f"{historical_fraction[:4]}.{historical_fraction[4:6]}.{historical_fraction[6:8]}"
            )
        ).order_by(TariffFraction.nico) # Order so we get a consistent result if multiple NICOs exist
        
        main_result = self.db.execute(fallback_query).scalars().first()
        
        if main_result:
            # Apply same regime change rule to fallback if found
            if is_regime_change:
                rate_str = main_result.adv_impo
            else:
                rate_str = main_result.adv_impo if direction.lower() == 'import' else main_result.adv_expo
            
            if rate_str:
                try:
                    # Remove non-numeric characters (like % or text)
                    import re
                    clean_rate = re.sub(r'[^\d.]', '', rate_str)
                    return Decimal(clean_rate) if clean_rate else Decimal(0)
                except:
                    return Decimal(0)
        
        return None
