from sqlalchemy.orm import Session, joinedload
from typing import Optional, List
from .models import Department, Team, UserTeam


# Department repository functions
def get_by_id(db: Session, entity_id: str) -> Optional[Department]:
    return db.query(Department).filter(Department.id == entity_id).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Department]:
    q = db.query(Department)
    if filters.get("search"):
        search = f"%{filters['search']}%"
        q = q.filter(Department.name.ilike(search))
    return q.order_by(Department.created_at.desc()).limit(limit).offset(offset).all()


def count_all(db: Session, **filters) -> int:
    q = db.query(Department.id)
    if filters.get("search"):
        search = f"%{filters['search']}%"
        q = q.filter(Department.name.ilike(search))
    return q.count()


def create(db: Session, data: dict) -> Department:
    obj = Department(**data)
    db.add(obj)
    db.flush()
    return obj


def update(db: Session, entity_id: str, data: dict) -> Optional[Department]:
    obj = get_by_id(db, entity_id)
    if obj is None:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


def delete(db: Session, entity_id: str) -> bool:
    obj = get_by_id(db, entity_id)
    if obj is None:
        return False
    db.delete(obj)
    db.flush()
    return True


# Team repository functions
def get_team_by_id(db: Session, entity_id: str) -> Optional[Team]:
    return db.query(Team).filter(Team.id == entity_id).first()


def list_teams(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Team]:
    q = db.query(Team)
    if filters.get("department_id"):
        q = q.filter(Team.department_id == filters["department_id"])
    if filters.get("search"):
        search = f"%{filters['search']}%"
        q = q.filter(Team.name.ilike(search))
    return q.order_by(Team.created_at.desc()).limit(limit).offset(offset).all()


def count_teams(db: Session, **filters) -> int:
    q = db.query(Team.id)
    if filters.get("department_id"):
        q = q.filter(Team.department_id == filters["department_id"])
    if filters.get("search"):
        search = f"%{filters['search']}%"
        q = q.filter(Team.name.ilike(search))
    return q.count()


def create_team(db: Session, data: dict) -> Team:
    obj = Team(**data)
    db.add(obj)
    db.flush()
    return obj


def update_team(db: Session, entity_id: str, data: dict) -> Optional[Team]:
    obj = get_team_by_id(db, entity_id)
    if obj is None:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


def delete_team(db: Session, entity_id: str) -> bool:
    obj = get_team_by_id(db, entity_id)
    if obj is None:
        return False
    db.delete(obj)
    db.flush()
    return True


def get_team_with_details(db: Session, entity_id: str) -> Optional[Team]:
    return (
        db.query(Team)
        .options(
            joinedload(Team.department),
            joinedload(Team.user_teams).joinedload(UserTeam.user),
        )
        .filter(Team.id == entity_id)
        .first()
    )


# UserTeam repository functions
def get_user_team_by_id(db: Session, entity_id: str) -> Optional[UserTeam]:
    return db.query(UserTeam).filter(UserTeam.id == entity_id).first()


def list_user_teams(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[UserTeam]:
    q = db.query(UserTeam)
    if filters.get("user_id"):
        q = q.filter(UserTeam.user_id == filters["user_id"])
    if filters.get("team_id"):
        q = q.filter(UserTeam.team_id == filters["team_id"])
    return q.order_by(UserTeam.created_at.desc()).limit(limit).offset(offset).all()


def count_user_teams(db: Session, **filters) -> int:
    q = db.query(UserTeam.id)
    if filters.get("user_id"):
        q = q.filter(UserTeam.user_id == filters["user_id"])
    if filters.get("team_id"):
        q = q.filter(UserTeam.team_id == filters["team_id"])
    return q.count()


def create_user_team(db: Session, data: dict) -> UserTeam:
    obj = UserTeam(**data)
    db.add(obj)
    db.flush()
    return obj


def update_user_team(db: Session, entity_id: str, data: dict) -> Optional[UserTeam]:
    obj = get_user_team_by_id(db, entity_id)
    if obj is None:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


def delete_user_team(db: Session, entity_id: str) -> bool:
    obj = get_user_team_by_id(db, entity_id)
    if obj is None:
        return False
    db.delete(obj)
    db.flush()
    return True