
from typing import Optional, List, Tuple
from sqlalchemy import select, func
from sqlalchemy.orm import Session
from .models import CanadianTariffFraction
from .schemas import CanadianTariffFractionCreate, CanadianTariffFractionUpdate

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

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

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

    def create(self, obj_in: CanadianTariffFractionCreate, tenant_id: int, company_id: int) -> CanadianTariffFraction:
        db_obj = CanadianTariffFraction(
            **obj_in.model_dump(),
            tenant_id=tenant_id,
            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: CanadianTariffFraction,
        obj_in: CanadianTariffFractionUpdate
    ) -> CanadianTariffFraction:
        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[CanadianTariffFraction]:
        obj = self.get(id, tenant_id, company_id)
        if obj:
            self.db.delete(obj)
            self.db.commit()
        return obj
