"""Match repository"""
from typing import Optional, List
from datetime import datetime
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_
from app.core.db.models.match import Match, MatchStatus
from app.core.db.models.team import Team
from app.core.db.models.sport import League
from app.core.db.models.feature import Feature


class MatchRepository:
    """Repository for match operations"""
    
    def __init__(self, db: Session):
        self.db = db
    
    def get_by_id(self, match_id: int) -> Optional[Match]:
        """Get match by ID"""
        return self.db.query(Match).filter(Match.id == match_id).first()
    
    def get_by_source_id(self, source_id: str) -> Optional[Match]:
        """Get match by source ID"""
        return self.db.query(Match).filter(Match.source_id == source_id).first()
    
    def get_upcoming(
        self,
        league_id: Optional[int] = None,
        from_date: Optional[datetime] = None,
        to_date: Optional[datetime] = None,
        limit: int = 100
    ) -> List[Match]:
        """Get upcoming matches"""
        query = self.db.query(Match).filter(
            Match.status == MatchStatus.SCHEDULED.value
        )
        
        if league_id:
            query = query.filter(Match.league_id == league_id)
        
        if from_date:
            query = query.filter(Match.match_date >= from_date)
        
        if to_date:
            query = query.filter(Match.match_date <= to_date)
        
        return query.order_by(Match.match_date).limit(limit).all()
    
    def get_finished(
        self,
        league_id: Optional[int] = None,
        from_date: Optional[datetime] = None,
        to_date: Optional[datetime] = None,
        limit: int = 1000
    ) -> List[Match]:
        """Get finished matches"""
        query = self.db.query(Match).filter(
            Match.status == MatchStatus.FINISHED.value
        )
        
        if league_id:
            query = query.filter(Match.league_id == league_id)
        
        if from_date:
            query = query.filter(Match.match_date >= from_date)
        
        if to_date:
            query = query.filter(Match.match_date <= to_date)
        
        return query.order_by(Match.match_date.desc()).limit(limit).all()
    
    def create(self, match: Match) -> Match:
        """Create a new match"""
        self.db.add(match)
        self.db.commit()
        self.db.refresh(match)
        return match
    
    def update(self, match: Match) -> Match:
        """Update a match"""
        match.updated_at = datetime.utcnow()
        self.db.commit()
        self.db.refresh(match)
        return match
    
    def get_matches_for_training(
        self,
        league_id: Optional[int] = None,
        min_date: Optional[datetime] = None,
        max_date: Optional[datetime] = None
    ) -> List[Match]:
        """Get finished matches for training (must have scores + features)"""
        query = (
            self.db.query(Match)
            .join(Feature, Feature.match_id == Match.id)
            .filter(
                Match.status == "finished",
                Match.home_score.isnot(None),
                Match.away_score.isnot(None),
            )
        )

        if league_id:
            query = query.filter(Match.league_id == league_id)

        if min_date:
            query = query.filter(Match.match_date >= min_date)

        if max_date:
            query = query.filter(Match.match_date <= max_date)

        return query.order_by(Match.match_date).all()