from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from fastapi import HTTPException, status
from typing import Optional
from . import repository
from .schema import (
    JobCreate, JobUpdate, JobAssignRequest, JobReassignRequest, JobStatusUpdateRequest,
    CommentCreate, CommentUpdate, AttachmentCreate, AttachmentUpdate,
    JobhistoryCreate, JobhistoryUpdate, JobtagCreate, JobtagUpdate
)
from user_management import repository as user_repo
from job_configuration import repository as job_config_repo
from organization import repository as org_repo
from checklist import repository as checklist_repo
from checklist.models import Checklist, ChecklistItem
from utils.utils import utc_now


def create_job(db: Session, data: JobCreate) -> dict:
    # Validate references
    if not job_config_repo.get_jobtype_by_id(db, data.job_type_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job type does not exist")
    if not job_config_repo.get_jobstatus_by_id(db, data.job_status_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job status does not exist")
    if not job_config_repo.get_jobpriority_by_id(db, data.job_priority_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job priority does not exist")
    if not user_repo.get_by_id(db, data.created_by_user_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Creating user does not exist")
    if not org_repo.get_department_by_id(db, data.department_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Department does not exist")
    if data.assigned_to_user_id and not user_repo.get_by_id(db, data.assigned_to_user_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Assigned user does not exist")
    if data.team_id and not org_repo.get_team_by_id(db, data.team_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Team does not exist")
    if data.parent_job_id and not repository.get_by_id(db, data.parent_job_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Parent job does not exist")

    try:
        job = repository.create(db, data.model_dump())
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "unique" in msg or "duplicate" in msg:
            raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Job already exists.")
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def list_jobs(
    db: Session,
    limit: int,
    offset: int,
    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,
) -> dict:
    items = repository.list_all(
        db,
        limit=limit,
        offset=offset,
        job_status_id=job_status_id,
        job_priority_id=job_priority_id,
        assigned_to_user_id=assigned_to_user_id,
        department_id=department_id,
        team_id=team_id,
    )
    total = repository.count_all(
        db,
        job_status_id=job_status_id,
        job_priority_id=job_priority_id,
        assigned_to_user_id=assigned_to_user_id,
        department_id=department_id,
        team_id=team_id,
    )
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def get_job(db: Session, job_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")
    return job


def get_job_details(db: Session, job_id: str) -> dict:
    job = repository.get_with_details(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")
    return job


def update_job(db: Session, job_id: str, data: JobUpdate) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    update_data = data.model_dump(exclude_unset=True)
    if "job_type_id" in update_data and not job_config_repo.get_jobtype_by_id(db, update_data["job_type_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job type does not exist")
    if "job_status_id" in update_data and not job_config_repo.get_jobstatus_by_id(db, update_data["job_status_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job status does not exist")
    if "job_priority_id" in update_data and not job_config_repo.get_jobpriority_by_id(db, update_data["job_priority_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job priority does not exist")
    if "department_id" in update_data and not org_repo.get_department_by_id(db, update_data["department_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Department does not exist")
    if "assigned_to_user_id" in update_data and update_data["assigned_to_user_id"] and not user_repo.get_by_id(db, update_data["assigned_to_user_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Assigned user does not exist")
    if "team_id" in update_data and update_data["team_id"] and not org_repo.get_team_by_id(db, update_data["team_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Team does not exist")
    if "parent_job_id" in update_data and update_data["parent_job_id"] and not repository.get_by_id(db, update_data["parent_job_id"]):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Parent job does not exist")

    try:
        updated_job = repository.update(db, job_id, update_data)
        db.commit()
        db.refresh(updated_job)
        return updated_job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "unique" in msg or "duplicate" in msg:
            raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Job already exists.")
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def delete_job(db: Session, job_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    try:
        repository.delete(db, job_id)
        db.commit()
        return {"message": "Job deleted successfully"}
    except IntegrityError:
        db.rollback()
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Cannot delete job while referenced by other records.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def assign_job(db: Session, job_id: str, data: JobAssignRequest, current_user_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    if not user_repo.get_by_id(db, data.assigned_to_user_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Assigned user does not exist")

    old_assignee_id = job.assigned_to_user_id

    try:
        repository.update(db, job_id, {"assigned_to_user_id": data.assigned_to_user_id})
        history_data = {
            "job_id": job_id,
            "user_id": current_user_id,
            "action_type": "ASSIGNED",
            "field_name": "assigned_to_user_id",
            "old_value": old_assignee_id,
            "new_value": data.assigned_to_user_id,
        }
        repository.JobhistoryRepository.create(db, history_data)
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def reassign_job(db: Session, job_id: str, data: JobReassignRequest, current_user_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    if not user_repo.get_by_id(db, data.assigned_to_user_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="New assigned user does not exist")

    old_assignee_id = job.assigned_to_user_id

    try:
        repository.update(db, job_id, {"assigned_to_user_id": data.assigned_to_user_id})
        history_data = {
            "job_id": job_id,
            "user_id": current_user_id,
            "action_type": "REASSIGNED",
            "field_name": "assigned_to_user_id",
            "old_value": old_assignee_id,
            "new_value": data.assigned_to_user_id,
        }
        repository.JobhistoryRepository.create(db, history_data)
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def update_job_status(db: Session, job_id: str, data: JobStatusUpdateRequest, current_user_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    if not job_config_repo.get_jobstatus_by_id(db, data.job_status_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job status does not exist")

    old_status_id = job.job_status_id

    try:
        repository.update(db, job_id, {"job_status_id": data.job_status_id})
        history_data = {
            "job_id": job_id,
            "user_id": current_user_id,
            "action_type": "STATUS_CHANGED",
            "field_name": "job_status_id",
            "old_value": old_status_id,
            "new_value": data.job_status_id,
        }
        repository.JobhistoryRepository.create(db, history_data)
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def complete_job_with_checklist_validation(db: Session, job_id: str, current_user_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    checklists = db.query(Checklist).filter(Checklist.job_id == job_id).all()
    checklist_ids = [c.id for c in checklists]

    if checklist_ids:
        checklist_items = db.query(ChecklistItem).filter(ChecklistItem.checklist_id.in_(checklist_ids)).all()
        incomplete_items = [item for item in checklist_items if not item.is_completed]
        if incomplete_items:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="All checklist items must be completed before job can be marked as completed"
            )

    completed_status = job_config_repo.list_jobstatuses(db)
    completed_status_obj = None
    for status_obj in completed_status:
        if status_obj.name.lower() == "completed":
            completed_status_obj = status_obj
            break

    if not completed_status_obj:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Completed status not found in system")

    old_status_id = job.job_status_id

    try:
        update_data = {
            "job_status_id": completed_status_obj.id,
            "completed_at": utc_now()
        }
        repository.update(db, job_id, update_data)
        history_data = {
            "job_id": job_id,
            "user_id": current_user_id,
            "action_type": "COMPLETED",
            "field_name": "job_status_id",
            "old_value": old_status_id,
            "new_value": completed_status_obj.id,
        }
        repository.JobhistoryRepository.create(db, history_data)
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def cancel_job(db: Session, job_id: str, current_user_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    cancelled_status = job_config_repo.list_jobstatuses(db)
    cancelled_status_obj = None
    for status_obj in cancelled_status:
        if status_obj.name.lower() == "cancelled":
            cancelled_status_obj = status_obj
            break

    if not cancelled_status_obj:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Cancelled status not found in system")

    old_status_id = job.job_status_id

    try:
        repository.update(db, job_id, {"job_status_id": cancelled_status_obj.id})
        history_data = {
            "job_id": job_id,
            "user_id": current_user_id,
            "action_type": "CANCELLED",
            "field_name": "job_status_id",
            "old_value": old_status_id,
            "new_value": cancelled_status_obj.id,
        }
        repository.JobhistoryRepository.create(db, history_data)
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def reopen_job(db: Session, job_id: str, current_user_id: str) -> dict:
    job = repository.get_by_id(db, job_id)
    if not job:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job not found")

    in_progress_status = job_config_repo.list_jobstatuses(db)
    in_progress_status_obj = None
    for status_obj in in_progress_status:
        if status_obj.name.lower() == "in progress":
            in_progress_status_obj = status_obj
            break

    if not in_progress_status_obj:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="In Progress status not found in system")

    old_status_id = job.job_status_id

    try:
        update_data = {
            "job_status_id": in_progress_status_obj.id,
            "completed_at": None
        }
        repository.update(db, job_id, update_data)
        history_data = {
            "job_id": job_id,
            "user_id": current_user_id,
            "action_type": "REOPENED",
            "field_name": "job_status_id",
            "old_value": old_status_id,
            "new_value": in_progress_status_obj.id,
        }
        repository.JobhistoryRepository.create(db, history_data)
        db.commit()
        db.refresh(job)
        return job
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


# Comment handlers
def create_comment(db: Session, data: CommentCreate) -> dict:
    if not repository.get_by_id(db, data.job_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job does not exist")
    if not user_repo.get_by_id(db, data.user_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="User does not exist")

    try:
        comment = repository.CommentRepository.create(db, data.model_dump())
        db.commit()
        db.refresh(comment)
        return comment
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def list_comments(db: Session, limit: int, offset: int, job_id: Optional[str] = None) -> dict:
    items = repository.CommentRepository.list_all(db, job_id=job_id, limit=limit, offset=offset)
    total = repository.CommentRepository.count_all(db, job_id=job_id)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def get_comment(db: Session, comment_id: str) -> dict:
    comment = repository.CommentRepository.get_by_id(db, comment_id)
    if not comment:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Comment not found")
    return comment


def update_comment(db: Session, comment_id: str, data: CommentUpdate) -> dict:
    comment = repository.CommentRepository.get_by_id(db, comment_id)
    if not comment:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Comment not found")

    try:
        updated_comment = repository.CommentRepository.update(db, comment_id, data.model_dump(exclude_unset=True))
        db.commit()
        db.refresh(updated_comment)
        return updated_comment
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def delete_comment(db: Session, comment_id: str) -> dict:
    comment = repository.CommentRepository.get_by_id(db, comment_id)
    if not comment:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Comment not found")

    try:
        repository.CommentRepository.delete(db, comment_id)
        db.commit()
        return {"message": "Comment deleted successfully"}
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


# Attachment handlers
def create_attachment(db: Session, data: AttachmentCreate) -> dict:
    if not repository.get_by_id(db, data.job_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job does not exist")
    if not user_repo.get_by_id(db, data.uploaded_by_user_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="User does not exist")

    if data.file_size > 10485760:
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="File size exceeds 10MB limit")

    try:
        attachment = repository.AttachmentRepository.create(db, data.model_dump())
        db.commit()
        db.refresh(attachment)
        return attachment
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def list_attachments(db: Session, limit: int, offset: int, job_id: Optional[str] = None) -> dict:
    items = repository.AttachmentRepository.list_all(db, job_id=job_id, limit=limit, offset=offset)
    total = repository.AttachmentRepository.count_all(db, job_id=job_id)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def get_attachment(db: Session, attachment_id: str) -> dict:
    attachment = repository.AttachmentRepository.get_by_id(db, attachment_id)
    if not attachment:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Attachment not found")
    return attachment


def update_attachment(db: Session, attachment_id: str, data: AttachmentUpdate) -> dict:
    attachment = repository.AttachmentRepository.get_by_id(db, attachment_id)
    if not attachment:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Attachment not found")

    try:
        updated_attachment = repository.AttachmentRepository.update(db, attachment_id, data.model_dump(exclude_unset=True))
        db.commit()
        db.refresh(updated_attachment)
        return updated_attachment
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def delete_attachment(db: Session, attachment_id: str) -> dict:
    attachment = repository.AttachmentRepository.get_by_id(db, attachment_id)
    if not attachment:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Attachment not found")

    try:
        repository.AttachmentRepository.delete(db, attachment_id)
        db.commit()
        return {"message": "Attachment deleted successfully"}
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


# Job history handlers
def list_job_history(db: Session, limit: int, offset: int, job_id: Optional[str] = None) -> dict:
    items = repository.JobhistoryRepository.list_all(db, job_id=job_id, limit=limit, offset=offset)
    total = repository.JobhistoryRepository.count_all(db, job_id=job_id)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def get_jobhistory(db: Session, history_id: str) -> dict:
    history = repository.JobhistoryRepository.get_by_id(db, history_id)
    if not history:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job history entry not found")
    return history


# Job tag handlers
def create_jobtag(db: Session, data: JobtagCreate) -> dict:
    if not repository.get_by_id(db, data.job_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Job does not exist")
    from job_configuration import repository as tag_repo
    if not tag_repo.get_tag_by_id(db, data.tag_id):
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Tag does not exist")

    try:
        jobtag = repository.JobtagRepository.create(db, data.model_dump())
        db.commit()
        db.refresh(jobtag)
        return jobtag
    except IntegrityError as exc:
        db.rollback()
        msg = str(exc.orig).lower() if exc.orig else ""
        if "unique" in msg or "duplicate" in msg:
            raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail="Job tag relationship already exists.")
        if "foreign key" in msg:
            raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Referenced resource does not exist.")
        raise HTTPException(status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error.")
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def list_jobtags(db: Session, limit: int, offset: int, job_id: Optional[str] = None) -> dict:
    items = repository.JobtagRepository.list_all(db, job_id=job_id, limit=limit, offset=offset)
    total = repository.JobtagRepository.count_all(db, job_id=job_id)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def get_jobtag(db: Session, jobtag_id: str) -> dict:
    jobtag = repository.JobtagRepository.get_by_id(db, jobtag_id)
    if not jobtag:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job tag not found")
    return jobtag


def delete_jobtag(db: Session, jobtag_id: str) -> dict:
    jobtag = repository.JobtagRepository.get_by_id(db, jobtag_id)
    if not jobtag:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job tag not found")

    try:
        repository.JobtagRepository.delete(db, jobtag_id)
        db.commit()
        return {"message": "Job tag deleted successfully"}
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise