from sqlalchemy.orm import Session, joinedload
from typing import Optional, List
from .models import Checklist, Checklistitem


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


def list_all(db: Session, limit: int = 20, offset: int = 0, model=Checklist, **filters) -> List:
    q = db.query(model)
    
    if model == Checklist:
        if filters.get("job_id") is not None:
            q = q.filter(Checklist.job_id == filters["job_id"])
        return q.order_by(Checklist.display_order.asc(), Checklist.created_at.desc()).limit(limit).offset(offset).all()
    
    elif model == Checklistitem:
        if filters.get("checklist_id") is not None:
            q = q.filter(Checklistitem.checklist_id == filters["checklist_id"])
        if filters.get("is_completed") is not None:
            q = q.filter(Checklistitem.is_completed == filters["is_completed"])
        if filters.get("assigned_to_user_id") is not None:
            q = q.filter(Checklistitem.assigned_to_user_id == filters["assigned_to_user_id"])
        return q.order_by(Checklistitem.display_order.asc(), Checklistitem.created_at.desc()).limit(limit).offset(offset).all()
    
    return q.limit(limit).offset(offset).all()


def count_all(db: Session, model=Checklist, **filters) -> int:
    q = db.query(model.id)
    
    if model == Checklist:
        if filters.get("job_id") is not None:
            q = q.filter(Checklist.job_id == filters["job_id"])
    
    elif model == Checklistitem:
        if filters.get("checklist_id") is not None:
            q = q.filter(Checklistitem.checklist_id == filters["checklist_id"])
        if filters.get("is_completed") is not None:
            q = q.filter(Checklistitem.is_completed == filters["is_completed"])
        if filters.get("assigned_to_user_id") is not None:
            q = q.filter(Checklistitem.assigned_to_user_id == filters["assigned_to_user_id"])
    
    return q.count()


def create(db: Session, data: dict, model=Checklist):
    obj = model(**data)
    db.add(obj)
    db.flush()
    return obj


def update(db: Session, entity_id: str, data: dict, model=Checklist) -> Optional:
    obj = db.query(model).filter(model.id == entity_id).first()
    if not obj:
        return None
    for key, value in data.items():
        setattr(obj, key, value)
    db.flush()
    return obj


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