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 (
    JobtypeCreate,
    JobtypeUpdate,
    JobstatusCreate,
    JobstatusUpdate,
    JobpriorityCreate,
    JobpriorityUpdate,
    TagCreate,
    TagUpdate,
)


def create_jobtype(db: Session, data: JobtypeCreate):
    try:
        obj = repository.create_jobtype(db, data.model_dump())
        db.commit()
        db.refresh(obj)
        return obj
    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 type with this name 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 get_jobtype_by_id(db: Session, entity_id: str):
    obj = repository.get_jobtype_by_id(db, entity_id)
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job type not found.")
    return obj


def list_jobtypes(db: Session, limit: int, offset: int, is_active: Optional[bool], search: Optional[str]):
    filters = {}
    if is_active is not None:
        filters["is_active"] = is_active
    if search:
        filters["search"] = search
    items = repository.list_jobtypes(db, limit=limit, offset=offset, **filters)
    total = repository.count_jobtypes(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_jobtype(db: Session, entity_id: str, data: JobtypeUpdate):
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="No fields to update."
        )
    try:
        obj = repository.update_jobtype(db, entity_id, update_data)
        if not obj:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job type not found.")
        db.commit()
        db.refresh(obj)
        return obj
    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 type with this name 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_jobtype(db: Session, entity_id: str):
    try:
        success = repository.delete_jobtype(db, entity_id)
        if not success:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job type not found.")
        db.commit()
        return {"detail": "Job type deleted successfully."}
    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_409_CONFLICT,
                detail="Cannot delete job type that is referenced by existing jobs.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error."
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def create_jobstatus(db: Session, data: JobstatusCreate):
    try:
        obj = repository.create(db, data.model_dump())
        db.commit()
        db.refresh(obj)
        return obj
    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 status with this name 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 get_jobstatus_by_id(db: Session, entity_id: str):
    obj = repository.get_by_id(db, entity_id)
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job status not found.")
    return obj


def list_jobstatuses(db: Session, limit: int, offset: int, is_active: Optional[bool], search: Optional[str]):
    filters = {}
    if is_active is not None:
        filters["is_active"] = is_active
    if search:
        filters["search"] = search
    items = repository.list_all(db, limit=limit, offset=offset, **filters)
    total = repository.count_all(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_jobstatus(db: Session, entity_id: str, data: JobstatusUpdate):
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="No fields to update."
        )
    try:
        obj = repository.update(db, entity_id, update_data)
        if not obj:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job status not found.")
        db.commit()
        db.refresh(obj)
        return obj
    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 status with this name 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_jobstatus(db: Session, entity_id: str):
    try:
        success = repository.delete(db, entity_id)
        if not success:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job status not found.")
        db.commit()
        return {"detail": "Job status deleted successfully."}
    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_409_CONFLICT,
                detail="Cannot delete job status that is referenced by existing jobs.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error."
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def create_jobpriority(db: Session, data: JobpriorityCreate):
    try:
        obj = repository.create_jobpriority(db, data.model_dump())
        db.commit()
        db.refresh(obj)
        return obj
    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 priority with this name 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 get_jobpriority_by_id(db: Session, entity_id: str):
    obj = repository.get_jobpriority_by_id(db, entity_id)
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job priority not found.")
    return obj


def list_jobpriorities(db: Session, limit: int, offset: int, is_active: Optional[bool], search: Optional[str]):
    filters = {}
    if is_active is not None:
        filters["is_active"] = is_active
    if search:
        filters["search"] = search
    items = repository.list_jobpriorities(db, limit=limit, offset=offset, **filters)
    total = repository.count_jobpriorities(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_jobpriority(db: Session, entity_id: str, data: JobpriorityUpdate):
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="No fields to update."
        )
    try:
        obj = repository.update_jobpriority(db, entity_id, update_data)
        if not obj:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job priority not found.")
        db.commit()
        db.refresh(obj)
        return obj
    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 priority with this name 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_jobpriority(db: Session, entity_id: str):
    try:
        success = repository.delete_jobpriority(db, entity_id)
        if not success:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Job priority not found.")
        db.commit()
        return {"detail": "Job priority deleted successfully."}
    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_409_CONFLICT,
                detail="Cannot delete job priority that is referenced by existing jobs.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error."
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


def create_tag(db: Session, data: TagCreate):
    try:
        obj = repository.create_tag(db, data.model_dump())
        db.commit()
        db.refresh(obj)
        return obj
    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="Tag with this name 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 get_tag_by_id(db: Session, entity_id: str):
    obj = repository.get_tag_by_id(db, entity_id)
    if not obj:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Tag not found.")
    return obj


def list_tags(db: Session, limit: int, offset: int, search: Optional[str]):
    filters = {}
    if search:
        filters["search"] = search
    items = repository.list_tags(db, limit=limit, offset=offset, **filters)
    total = repository.count_tags(db, **filters)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_tag(db: Session, entity_id: str, data: TagUpdate):
    update_data = data.model_dump(exclude_unset=True)
    if not update_data:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="No fields to update."
        )
    try:
        obj = repository.update_tag(db, entity_id, update_data)
        if not obj:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Tag not found.")
        db.commit()
        db.refresh(obj)
        return obj
    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="Tag with this name 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_tag(db: Session, entity_id: str):
    try:
        success = repository.delete_tag(db, entity_id)
        if not success:
            raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Tag not found.")
        db.commit()
        return {"detail": "Tag deleted successfully."}
    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_409_CONFLICT,
                detail="Cannot delete tag that is referenced by existing job tags.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST, detail="Data integrity error."
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise