"""User repository — all DB queries for users and their SPBU assignments."""

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.role import Role, RolePermission, UserSpbuAssignment
from app.models.user import User


def _assignment_options():
    return selectinload(User.assignments).selectinload(UserSpbuAssignment.role)


async def get_by_email(db: AsyncSession, email: str) -> User | None:
    """Fetch a user by email address."""
    result = await db.execute(
        select(User).where(User.email == email, User.deleted_at.is_(None))
    )
    return result.scalar_one_or_none()


async def get_by_username(db: AsyncSession, username: str) -> User | None:
    """Fetch a user by username."""
    result = await db.execute(
        select(User).where(User.username == username, User.deleted_at.is_(None))
    )
    return result.scalar_one_or_none()


async def get_by_id(db: AsyncSession, user_id: int) -> User | None:
    """Fetch a user by primary key, eagerly loading their SPBU assignments."""
    result = await db.execute(
        select(User)
        .where(User.id == user_id, User.deleted_at.is_(None))
        .options(_assignment_options())
    )
    return result.scalar_one_or_none()


async def get_with_permissions(db: AsyncSession, user_id: int) -> User | None:
    """Fetch a user with all SPBU assignments, roles, and role permissions loaded."""
    from app.models.role import Role, RolePermission  # noqa: F401 (loaded via relationship)
    result = await db.execute(
        select(User)
        .where(User.id == user_id, User.deleted_at.is_(None))
        .options(
            selectinload(User.assignments).options(
                selectinload(UserSpbuAssignment.role).selectinload(Role.permissions),
                selectinload(UserSpbuAssignment.spbu),
            )
        )
    )
    return result.scalar_one_or_none()


async def get_all(
    db: AsyncSession,
    skip: int = 0,
    limit: int = 50,
) -> tuple[list[User], int]:
    """Return a paginated list of all users (superadmin only)."""
    base_query = select(User).where(User.deleted_at.is_(None))
    count_q = await db.execute(select(func.count()).select_from(base_query.subquery()))
    total = count_q.scalar_one()
    result = await db.execute(
        base_query
        .options(_assignment_options())
        .offset(skip)
        .limit(limit)
        .order_by(User.name)
    )
    return list(result.scalars().all()), total


async def get_all_scoped(
    db: AsyncSession,
    actor_spbu_ids: list[int],
    skip: int = 0,
    limit: int = 50,
) -> tuple[list[User], int]:
    """Return users who share at least one SPBU with the actor, have a schedulable role,
    and are not superadmins. Used for SPBU Admin views (operators/OB only)."""
    base_query = (
        select(User)
        .join(UserSpbuAssignment, UserSpbuAssignment.user_id == User.id)
        .join(Role, Role.id == UserSpbuAssignment.role_id)
        .where(
            User.deleted_at.is_(None),
            User.is_superadmin.is_(False),
            UserSpbuAssignment.spbu_id.in_(actor_spbu_ids),
            Role.can_be_scheduled.is_(True),
        )
        .distinct()
    )
    count_q = await db.execute(select(func.count()).select_from(base_query.subquery()))
    total = count_q.scalar_one()
    result = await db.execute(
        base_query
        .options(_assignment_options())
        .offset(skip)
        .limit(limit)
        .order_by(User.name)
    )
    return list(result.scalars().all()), total


async def create(db: AsyncSession, **kwargs) -> User:
    """Create a new user record. Flush only — caller commits."""
    user = User(**kwargs)
    db.add(user)
    await db.flush()
    return await get_by_id(db, user.id)  # reload with assignments


async def update(db: AsyncSession, user: User, **kwargs) -> User:
    """Update scalar fields on an existing user. Flush only — caller commits."""
    for key, value in kwargs.items():
        setattr(user, key, value)
    await db.flush()
    return await get_by_id(db, user.id)  # reload with assignments


def _assignment_with_role():
    return selectinload(UserSpbuAssignment.role)


async def get_assignment(
    db: AsyncSession, user_id: int, spbu_id: int
) -> UserSpbuAssignment | None:
    """Fetch a specific user–SPBU assignment with role eagerly loaded."""
    result = await db.execute(
        select(UserSpbuAssignment)
        .where(
            UserSpbuAssignment.user_id == user_id,
            UserSpbuAssignment.spbu_id == spbu_id,
        )
        .options(_assignment_with_role())
    )
    return result.scalar_one_or_none()


async def get_assignments(
    db: AsyncSession, user_id: int
) -> list[UserSpbuAssignment]:
    """Return all SPBU assignments for a given user with roles eagerly loaded."""
    result = await db.execute(
        select(UserSpbuAssignment)
        .where(UserSpbuAssignment.user_id == user_id)
        .options(_assignment_with_role())
    )
    return list(result.scalars().all())


async def _get_assignment_by_id(db: AsyncSession, assignment_id: int) -> UserSpbuAssignment:
    result = await db.execute(
        select(UserSpbuAssignment)
        .where(UserSpbuAssignment.id == assignment_id)
        .options(_assignment_with_role())
    )
    return result.scalar_one()


async def create_assignment(
    db: AsyncSession, user_id: int, spbu_id: int, role_id: int
) -> UserSpbuAssignment:
    """Create a new user–SPBU role assignment. Flush only — caller commits."""
    assignment = UserSpbuAssignment(user_id=user_id, spbu_id=spbu_id, role_id=role_id)
    db.add(assignment)
    await db.flush()
    return await _get_assignment_by_id(db, assignment.id)


async def update_assignment(
    db: AsyncSession, assignment: UserSpbuAssignment, **kwargs
) -> UserSpbuAssignment:
    """Update fields on an existing SPBU assignment. Flush only — caller commits."""
    for key, value in kwargs.items():
        setattr(assignment, key, value)
    await db.flush()
    await db.refresh(assignment)
    return assignment


async def delete_user(db: AsyncSession, user: User, hard: bool = False) -> None:
    """Delete a user. Flush only — caller commits."""
    from datetime import datetime, timezone
    if hard:
        await db.delete(user)
    else:
        user.deleted_at = datetime.now(timezone.utc)
    await db.flush()


async def delete_assignment(db: AsyncSession, user_id: int, spbu_id: int) -> bool:
    """Delete the user–SPBU assignment. Returns True if deleted, False if not found. Flush only — caller commits."""
    assignment = await get_assignment(db, user_id, spbu_id)
    if not assignment:
        return False
    await db.delete(assignment)
    await db.flush()
    return True