from typing import List, Optional
from sqlalchemy import select, delete, and_
from sqlalchemy.ext.asyncio import AsyncSession
from src.models.usuarios_times import UsuarioTime
from src.core.timezone import get_now_brasil


class UsuarioTimeCRUD:

    async def vincular_usuario_time(
        self,
        db: AsyncSession,
        usuario_id: int,
        time_id: int,
        funcao: Optional[str] = None
    ) -> UsuarioTime:
        """
        Vincula um usuário a um time.
        Se já existir, não faz nada (idempotente).
            """
        stmt = select(UsuarioTime).where(
            and_(
                UsuarioTime.usuario_id == usuario_id,
                UsuarioTime.time_id == time_id
            )
        )
        result = await db.execute(stmt)
        existing = result.scalar_one_or_none()

        if existing:
            return existing

        vinculo = UsuarioTime(
            usuario_id=usuario_id,
            time_id=time_id,
            funcao=funcao,
            data_entrada=get_now_brasil()
        )
        db.add(vinculo)
        await db.commit()
        await db.refresh(vinculo)
        return vinculo

    async def desvincular_usuario_time(
        self,
        db: AsyncSession,
        usuario_id: int,
        time_id: int
    ) -> bool:
        """Remove o vínculo entre usuário e time"""
        stmt = delete(UsuarioTime).where(
            and_(
                UsuarioTime.usuario_id == usuario_id,
                UsuarioTime.time_id == time_id
            )
        )
        result = await db.execute(stmt)
        await db.commit()
        return result.rowcount > 0

    async def atualizar_funcao(
        self,
        db: AsyncSession,
        usuario_id: int,
        time_id: int,
        funcao: str
    ) -> Optional[UsuarioTime]:
        """Atualiza a função do usuário no time"""
        stmt = select(UsuarioTime).where(
            and_(
                UsuarioTime.usuario_id == usuario_id,
                UsuarioTime.time_id == time_id
            )
        )
        result = await db.execute(stmt)
        vinculo = result.scalar_one_or_none()

        if not vinculo:
            return None

        vinculo.funcao = funcao
        await db.commit()
        await db.refresh(vinculo)
        return vinculo

    async def listar_usuarios_do_time(
        self,
        db: AsyncSession,
        time_id: int
    ) -> List[UsuarioTime]:
        """Lista todos os usuários de um time"""
        stmt = select(UsuarioTime).where(UsuarioTime.time_id == time_id)
        result = await db.execute(stmt)
        return list(result.scalars().all())

    async def listar_times_do_usuario(
        self,
        db: AsyncSession,
        usuario_id: int
    ) -> List[UsuarioTime]:
        """Lista todos os times de um usuário"""
        stmt = select(UsuarioTime).where(UsuarioTime.usuario_id == usuario_id)
        result = await db.execute(stmt)
        return list(result.scalars().all())


crud = UsuarioTimeCRUD()