from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Job, Jobtag, Comment, Attachment, Jobhistory


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


def list_all(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    job_status_id: Optional[str] = None,
    job_priority_id: Optional[str] = None,
    assigned_to_user_id: Optional[str] = None,
    department_id: Optional[str] = None,
    team_id: Optional[str] = None,
) -> List[Job]:
    q = db.query(Job)
    if job_status_id is not None:
        q = q.filter(Job.job_status_id == job_status_id)
    if job_priority_id is not None:
        q = q.filter(Job.job_priority_id == job_priority_id)
    if assigned_to_user_id is not None:
        q = q.filter(Job.assigned_to_user_id == assigned_to_user_id)
    if department_id is not None:
        q = q.filter(Job.department_id == department_id)
    if team_id is not None:
        q = q.filter(Job.team_id == team_id)
    return q.order_by(Job.created_at.desc()).limit(limit).offset(offset).all()


def count_all(
    db: Session,
    job_status_id: Optional[str] = None,
    job_priority_id: Optional[str] = None,
    assigned_to_user_id: Optional[str] = None,
    department_id: Optional[str] = None,
    team_id: Optional[str] = None,
) -> int:
    q = db.query(Job.id)
    if job_status_id is not None:
        q = q.filter(Job.job_status_id == job_status_id)
    if job_priority_id is not None:
        q = q.filter(Job.job_priority_id == job_priority_id)
    if assigned_to_user_id is not None:
        q = q.filter(Job.assigned_to_user_id == assigned_to_user_id)
    if department_id is not None:
        q = q.filter(Job.department_id == department_id)
    if team_id is not None:
        q = q.filter(Job.team_id == team_id)
    return q.count()


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


def update(db: Session, entity_id: str, data: dict) -> Optional[Job]:
    obj = db.query(Job).filter(Job.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) -> bool:
    obj = db.query(Job).filter(Job.id == entity_id).first()
    if not obj:
        return False
    db.delete(obj)
    db.flush()
    return True


def get_with_details(db: Session, entity_id: str) -> Optional[Job]:
    return (
        db.query(Job)
        .options(
            joinedload(Job.job_type),
            joinedload(Job.job_status),
            joinedload(Job.job_priority),
            joinedload(Job.created_by_user),
            joinedload(Job.assigned_to_user),
            joinedload(Job.department),
            joinedload(Job.team),
            joinedload(Job.parent_job),
            joinedload(Job.comments).joinedload(Comment.user),
            joinedload(Job.attachments).joinedload(Attachment.uploaded_by_user),
            joinedload(Job.job_history).joinedload(Jobhistory.user),
            subqueryload(Job.time_logs).joinedload("user"),
            joinedload(Job.job_tags).joinedload(Jobtag.tag),
            subqueryload(Job.checklists).joinedload("checklist_items").joinedload("assigned_to_user"),
        )
        .filter(Job.id == entity_id)
        .first()
    )


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

    @staticmethod
    def list_all(db: Session, job_id: Optional[str] = None, limit: int = 20, offset: int = 0) -> List[Jobtag]:
        q = db.query(Jobtag)
        if job_id is not None:
            q = q.filter(Jobtag.job_id == job_id)
        return q.order_by(Jobtag.created_at.desc()).limit(limit).offset(offset).all()

    @staticmethod
    def count_all(db: Session, job_id: Optional[str] = None) -> int:
        q = db.query(Jobtag.id)
        if job_id is not None:
            q = q.filter(Jobtag.job_id == job_id)
        return q.count()

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

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

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


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

    @staticmethod
    def list_all(db: Session, job_id: Optional[str] = None, limit: int = 20, offset: int = 0) -> List[Comment]:
        q = db.query(Comment)
        if job_id is not None:
            q = q.filter(Comment.job_id == job_id)
        return q.order_by(Comment.created_at.desc()).limit(limit).offset(offset).all()

    @staticmethod
    def count_all(db: Session, job_id: Optional[str] = None) -> int:
        q = db.query(Comment.id)
        if job_id is not None:
            q = q.filter(Comment.job_id == job_id)
        return q.count()

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

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

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


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

    @staticmethod
    def list_all(db: Session, job_id: Optional[str] = None, limit: int = 20, offset: int = 0) -> List[Attachment]:
        q = db.query(Attachment)
        if job_id is not None:
            q = q.filter(Attachment.job_id == job_id)
        return q.order_by(Attachment.created_at.desc()).limit(limit).offset(offset).all()

    @staticmethod
    def count_all(db: Session, job_id: Optional[str] = None) -> int:
        q = db.query(Attachment.id)
        if job_id is not None:
            q = q.filter(Attachment.job_id == job_id)
        return q.count()

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

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

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


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

    @staticmethod
    def list_all(db: Session, job_id: Optional[str] = None, limit: int = 20, offset: int = 0) -> List[Jobhistory]:
        q = db.query(Jobhistory)
        if job_id is not None:
            q = q.filter(Jobhistory.job_id == job_id)
        return q.order_by(Jobhistory.created_at.desc()).limit(limit).offset(offset).all()

    @staticmethod
    def count_all(db: Session, job_id: Optional[str] = None) -> int:
        q = db.query(Jobhistory.id)
        if job_id is not None:
            q = q.filter(Jobhistory.job_id == job_id)
        return q.count()

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

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

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