"""SQLAlchemy implementation of ProjectMembershipRepository."""

from __future__ import annotations

from collections.abc import Sequence
from uuid import UUID

from sqlalchemy import and_, func, select

from noteflow.domain.identity import ProjectMembership, ProjectRole
from noteflow.infrastructure.persistence.models import (
    ProjectMembershipModel,
    ProjectModel,
)
from noteflow.infrastructure.persistence.repositories._base import BaseRepository


class SqlAlchemyProjectMembershipRepository(BaseRepository):
    """SQLAlchemy implementation of ProjectMembershipRepository.

    Manage project memberships with role-based access control.
    """

    @staticmethod
    def _to_domain(model: ProjectMembershipModel) -> ProjectMembership:
        """Convert ORM model to domain entity.

        Args:
            model: SQLAlchemy ProjectMembershipModel.

        Returns:
            Domain ProjectMembership entity.
        """
        return ProjectMembership(
            project_id=model.project_id,
            user_id=model.user_id,
            role=ProjectRole(model.role),
            joined_at=model.joined_at,
        )

    async def get(
        self,
        project_id: UUID,
        user_id: UUID,
    ) -> ProjectMembership | None:
        """Get a user's membership in a project.

        Args:
            project_id: Project UUID.
            user_id: User UUID.

        Returns:
            Membership if user is a member, None otherwise.
        """
        stmt = select(ProjectMembershipModel).where(
            and_(
                ProjectMembershipModel.project_id == project_id,
                ProjectMembershipModel.user_id == user_id,
            ),
        )
        model = await self._execute_scalar(stmt)
        return self._to_domain(model) if model else None

    async def add(
        self,
        project_id: UUID,
        user_id: UUID,
        role: ProjectRole,
    ) -> ProjectMembership:
        """Add a user to a project.

        Args:
            project_id: Project UUID.
            user_id: User UUID.
            role: Role to assign.

        Returns:
            Created membership.
        """
        model = ProjectMembershipModel(
            project_id=project_id,
            user_id=user_id,
            role=role.value,
        )
        await self._add_and_flush(model)
        return self._to_domain(model)

    async def update_role(
        self,
        project_id: UUID,
        user_id: UUID,
        role: ProjectRole,
    ) -> ProjectMembership | None:
        """Update a member's role in a project.

        Args:
            project_id: Project UUID.
            user_id: User UUID.
            role: New role.

        Returns:
            Updated membership if found, None otherwise.
        """
        stmt = select(ProjectMembershipModel).where(
            and_(
                ProjectMembershipModel.project_id == project_id,
                ProjectMembershipModel.user_id == user_id,
            ),
        )
        model = await self._execute_scalar(stmt)

        if model is None:
            return None

        model.role = role.value
        await self._session.flush()
        return self._to_domain(model)

    async def remove(
        self,
        project_id: UUID,
        user_id: UUID,
    ) -> bool:
        """Remove a user from a project.

        Args:
            project_id: Project UUID.
            user_id: User UUID.

        Returns:
            True if removed, False if not found.
        """
        stmt = select(ProjectMembershipModel).where(
            and_(
                ProjectMembershipModel.project_id == project_id,
                ProjectMembershipModel.user_id == user_id,
            ),
        )
        model = await self._execute_scalar(stmt)

        if model is None:
            return False

        await self._delete_and_flush(model)
        return True

    async def list_for_project(
        self,
        project_id: UUID,
        limit: int = 100,
        offset: int = 0,
    ) -> Sequence[ProjectMembership]:
        """List all members of a project.

        Args:
            project_id: Project UUID.
            limit: Maximum members to return.
            offset: Pagination offset.

        Returns:
            List of memberships.
        """
        stmt = (
            select(ProjectMembershipModel)
            .where(ProjectMembershipModel.project_id == project_id)
            .order_by(ProjectMembershipModel.joined_at)
            .limit(limit)
            .offset(offset)
        )
        models = await self._execute_scalars(stmt)
        return [self._to_domain(m) for m in models]

    async def list_for_user(
        self,
        user_id: UUID,
        workspace_id: UUID | None = None,
        limit: int = 100,
        offset: int = 0,
    ) -> Sequence[ProjectMembership]:
        """List all projects a user is a member of.

        Args:
            user_id: User UUID.
            workspace_id: Optional filter by workspace.
            limit: Maximum memberships to return.
            offset: Pagination offset.

        Returns:
            List of memberships.
        """
        conditions = [ProjectMembershipModel.user_id == user_id]

        if workspace_id is not None:
            # Join with projects to filter by workspace
            stmt = (
                select(ProjectMembershipModel)
                .join(
                    ProjectModel,
                    ProjectMembershipModel.project_id == ProjectModel.id,
                )
                .where(
                    and_(
                        ProjectMembershipModel.user_id == user_id,
                        ProjectModel.workspace_id == workspace_id,
                    ),
                )
                .order_by(ProjectMembershipModel.joined_at)
                .limit(limit)
                .offset(offset)
            )
        else:
            stmt = (
                select(ProjectMembershipModel)
                .where(and_(*conditions))
                .order_by(ProjectMembershipModel.joined_at)
                .limit(limit)
                .offset(offset)
            )

        models = await self._execute_scalars(stmt)
        return [self._to_domain(m) for m in models]

    async def bulk_add(
        self,
        project_id: UUID,
        memberships: Sequence[tuple[UUID, ProjectRole]],
    ) -> Sequence[ProjectMembership]:
        """Bulk add members to a project.

        Args:
            project_id: Project UUID.
            memberships: List of (user_id, role) tuples.

        Returns:
            Created memberships.
        """
        models = [
            ProjectMembershipModel(
                project_id=project_id,
                user_id=user_id,
                role=role.value,
            )
            for user_id, role in memberships
        ]

        for model in models:
            self._session.add(model)
        await self._session.flush()

        return [self._to_domain(m) for m in models]

    async def count_for_project(
        self,
        project_id: UUID,
    ) -> int:
        """Count members in a project.

        Args:
            project_id: Project UUID.

        Returns:
            Member count.
        """
        stmt = select(func.count()).where(
            ProjectMembershipModel.project_id == project_id
        )
        result = await self._session.execute(stmt)
        return result.scalar() or 0
