"""League repository"""
from typing import Optional, List
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from app.core.db.models.sport import League, Country


class LeagueRepository:
    """Repository for league operations"""
    
    def __init__(self, db: Session):
        self.db = db
    
    def get_by_id(self, league_id: int) -> Optional[League]:
        """Get league by ID"""
        return self.db.query(League).filter(League.id == league_id).first()
    
    def get_by_source_id(self, source_id: str) -> Optional[League]:
        """Get league by source ID"""
        return self.db.query(League).filter(League.source_id == source_id).first()
    
    def get_all(self) -> List[League]:
        """Get all leagues"""
        return self.db.query(League).all()
    
    def create(self, league: League) -> League:
        """Create a new league"""
        self.db.add(league)
        try:
            self.db.commit()
        except IntegrityError:
            # duplicate league (unique constraint country_id+name) -> return existing
            self.db.rollback()
            return (
                self.db.query(League)
                .filter(League.country_id == league.country_id, League.name == league.name)
                .first()
            )
        self.db.refresh(league)
        return league
    
    def get_or_create_country(self, name: str, code: str | None = None) -> Country:
        """Get or create country dictionary row"""
        country = self.db.query(Country).filter(Country.name == name).first()
        if country:
            return country

        country = Country(name=name, code=code)
        self.db.add(country)
        self.db.commit()
        self.db.refresh(country)
        return country