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 (
    DepartmentCreate,
    DepartmentUpdate,
    TeamCreate,
    TeamUpdate,
    UserteamCreate,
    UserteamUpdate,
)


# Department handlers
def create_department(db: Session, data: DepartmentCreate):
    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="Department 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_department_by_id(db: Session, entity_id: str):
    obj = repository.get_by_id(db, entity_id)
    if obj is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Department not found.",
        )
    return obj


def list_departments(db: Session, limit: int, offset: int, search: Optional[str] = None):
    items = repository.list_all(db, limit=limit, offset=offset, search=search)
    total = repository.count_all(db, search=search)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_department(db: Session, entity_id: str, data: DepartmentUpdate):
    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 obj is None:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Department 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="Department 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_department(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="Department not found.",
            )
        db.commit()
        return {"detail": "Department 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 department with associated users or jobs.",
            )
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Data integrity error.",
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise


# Team handlers
def create_team(db: Session, data: TeamCreate):
    dep = repository.get_by_id(db, data.department_id)
    if dep is None:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Department does not exist.",
        )
    try:
        obj = repository.create_team(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 "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced department 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_team_by_id(db: Session, entity_id: str):
    obj = repository.get_team_by_id(db, entity_id)
    if obj is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Team not found.",
        )
    return obj


def list_teams(
    db: Session, limit: int, offset: int, department_id: Optional[str] = None, search: Optional[str] = None
):
    items = repository.list_teams(db, limit=limit, offset=offset, department_id=department_id, search=search)
    total = repository.count_teams(db, department_id=department_id, search=search)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_team(db: Session, entity_id: str, data: TeamUpdate):
    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.",
        )
    if "department_id" in update_data:
        dep = repository.get_by_id(db, update_data["department_id"])
        if dep is None:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Department does not exist.",
            )
    try:
        obj = repository.update_team(db, entity_id, update_data)
        if obj is None:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Team 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 "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced department 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_team(db: Session, entity_id: str):
    try:
        success = repository.delete_team(db, entity_id)
        if not success:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="Team not found.",
            )
        db.commit()
        return {"detail": "Team 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 team with active jobs or user assignments.",
            )
        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_team_details(db: Session, entity_id: str):
    obj = repository.get_team_with_details(db, entity_id)
    if obj is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="Team not found.",
        )
    return obj


# Userteam handlers
def create_userteam(db: Session, data: UserteamCreate):
    from user_management import repository as user_repo

    user = user_repo.get_by_id(db, data.user_id)
    if user is None:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="User does not exist.",
        )
    team = repository.get_team_by_id(db, data.team_id)
    if team is None:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Team does not exist.",
        )
    try:
        obj = repository.create_user_team(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="User is already assigned to this team.",
            )
        if "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced user or team 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_userteam_by_id(db: Session, entity_id: str):
    obj = repository.get_user_team_by_id(db, entity_id)
    if obj is None:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="User team assignment not found.",
        )
    return obj


def list_userteams(
    db: Session, limit: int, offset: int, user_id: Optional[str] = None, team_id: Optional[str] = None
):
    items = repository.list_user_teams(db, limit=limit, offset=offset, user_id=user_id, team_id=team_id)
    total = repository.count_user_teams(db, user_id=user_id, team_id=team_id)
    return {"items": items, "total": total, "limit": limit, "offset": offset}


def update_userteam(db: Session, entity_id: str, data: UserteamUpdate):
    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.",
        )
    if "user_id" in update_data:
        from user_management import repository as user_repo

        user = user_repo.get_by_id(db, update_data["user_id"])
        if user is None:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="User does not exist.",
            )
    if "team_id" in update_data:
        team = repository.get_team_by_id(db, update_data["team_id"])
        if team is None:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Team does not exist.",
            )
    try:
        obj = repository.update_user_team(db, entity_id, update_data)
        if obj is None:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="User team assignment 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="User is already assigned to this team.",
            )
        if "foreign key" in msg:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail="Referenced user or team 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_userteam(db: Session, entity_id: str):
    try:
        success = repository.delete_user_team(db, entity_id)
        if not success:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail="User team assignment not found.",
            )
        db.commit()
        return {"detail": "User team assignment deleted successfully."}
    except IntegrityError as exc:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Data integrity error.",
        )
    except HTTPException:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise