from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import User, Notification
from organization.models import Department


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


def get_by_email(db: Session, email: str) -> Optional[User]:
    return db.query(User).filter(User.email == email).first()


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[User]:
    q = db.query(User)
    if filters.get("role") is not None:
        q = q.filter(User.role == filters["role"])
    if filters.get("is_active") is not None:
        q = q.filter(User.is_active == filters["is_active"])
    if filters.get("department_id") is not None:
        q = q.filter(User.department_id == filters["department_id"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        q = q.filter(
            (User.first_name.ilike(search_term))
            | (User.last_name.ilike(search_term))
            | (User.email.ilike(search_term))
        )
    return q.order_by(User.created_at.desc()).limit(limit).offset(offset).all()


def count_all(db: Session, **filters) -> int:
    q = db.query(User.id)
    if filters.get("role") is not None:
        q = q.filter(User.role == filters["role"])
    if filters.get("is_active") is not None:
        q = q.filter(User.is_active == filters["is_active"])
    if filters.get("department_id") is not None:
        q = q.filter(User.department_id == filters["department_id"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        q = q.filter(
            (User.first_name.ilike(search_term))
            | (User.last_name.ilike(search_term))
            | (User.email.ilike(search_term))
        )
    return q.count()


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


def update(db: Session, entity_id: str, data: dict) -> Optional[User]:
    user = db.query(User).filter(User.id == entity_id).first()
    if user:
        for key, value in data.items():
            setattr(user, key, value)
        db.flush()
    return user


def delete(db: Session, entity_id: str) -> bool:
    user = db.query(User).filter(User.id == entity_id).first()
    if user:
        db.delete(user)
        db.flush()
        return True
    return False


def get_with_details(db: Session, entity_id: str) -> Optional[User]:
    return (
        db.query(User)
        .options(
            joinedload(User.department),
            joinedload(User.user_teams)
            .joinedload("team")
            .joinedload("department"),
        )
        .filter(User.id == entity_id)
        .first()
    )


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

    @staticmethod
    def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Notification]:
        q = db.query(Notification)
        if filters.get("user_id") is not None:
            q = q.filter(Notification.user_id == filters["user_id"])
        if filters.get("is_read") is not None:
            q = q.filter(Notification.is_read == filters["is_read"])
        if filters.get("notification_type") is not None:
            q = q.filter(Notification.notification_type == filters["notification_type"])
        return q.order_by(Notification.created_at.desc()).limit(limit).offset(offset).all()

    @staticmethod
    def count_all(db: Session, **filters) -> int:
        q = db.query(Notification.id)
        if filters.get("user_id") is not None:
            q = q.filter(Notification.user_id == filters["user_id"])
        if filters.get("is_read") is not None:
            q = q.filter(Notification.is_read == filters["is_read"])
        if filters.get("notification_type") is not None:
            q = q.filter(Notification.notification_type == filters["notification_type"])
        return q.count()

    @staticmethod
    def create(db: Session, data: dict) -> Notification:
        notification = Notification(**data)
        db.add(notification)
        db.flush()
        return notification

    @staticmethod
    def update(db: Session, entity_id: str, data: dict) -> Optional[Notification]:
        notification = db.query(Notification).filter(Notification.id == entity_id).first()
        if notification:
            for key, value in data.items():
                setattr(notification, key, value)
            db.flush()
        return notification

    @staticmethod
    def delete(db: Session, entity_id: str) -> bool:
        notification = db.query(Notification).filter(Notification.id == entity_id).first()
        if notification:
            db.delete(notification)
            db.flush()
            return True
        return False


notification_repository = NotificationRepository()