from sqlalchemy.orm import Session, joinedload
from typing import Optional, List
from .models import User, Auditlog


# User repository functions
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") is not None:
        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") is not None:
        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:
    obj = User(**data)
    db.add(obj)
    db.flush()
    return obj


def update(db: Session, entity_id: str, data: dict) -> Optional[User]:
    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


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


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


def list_auditlogs(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Auditlog]:
    q = db.query(Auditlog)
    if filters.get("user_id") is not None:
        q = q.filter(Auditlog.user_id == filters["user_id"])
    if filters.get("entity_type") is not None:
        q = q.filter(Auditlog.entity_type == filters["entity_type"])
    if filters.get("entity_id") is not None:
        q = q.filter(Auditlog.entity_id == filters["entity_id"])
    if filters.get("action") is not None:
        q = q.filter(Auditlog.action == filters["action"])
    return q.order_by(Auditlog.created_at.desc()).limit(limit).offset(offset).all()


def count_auditlogs(db: Session, **filters) -> int:
    q = db.query(Auditlog.id)
    if filters.get("user_id") is not None:
        q = q.filter(Auditlog.user_id == filters["user_id"])
    if filters.get("entity_type") is not None:
        q = q.filter(Auditlog.entity_type == filters["entity_type"])
    if filters.get("entity_id") is not None:
        q = q.filter(Auditlog.entity_id == filters["entity_id"])
    if filters.get("action") is not None:
        q = q.filter(Auditlog.action == filters["action"])
    return q.count()


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


def update_auditlog(db: Session, entity_id: str, data: dict) -> Optional[Auditlog]:
    obj = get_auditlog_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_auditlog(db: Session, entity_id: str) -> bool:
    obj = get_auditlog_by_id(db, entity_id)
    if obj is None:
        return False
    db.delete(obj)
    db.flush()
    return True