
from typing import Optional, List, Tuple
from sqlalchemy import select, or_, func
from sqlalchemy.orm import Session
from .models import HistoricalTariffFraction
from .schemas import HistoricalTariffFractionCreate, HistoricalTariffFractionUpdate

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
