from django.db.models import F, Value
from django.db.models.functions import Concat, Coalesce, Upper, Substr
from apps.permissions.models import UserCompanyRole


class CompanyUserRepository:
    @staticmethod
    def company_users_queryset(company, role_names=None):
        def cap(field):
            return Concat(
                Upper(Substr(field, 1, 1)),
                Substr(field, 2),
            )

        qs = (
            UserCompanyRole.objects
            .filter(
                company=company,
                is_active=True,
                user__is_active=True,
                role__isnull=False,
            )
            .select_related('user', 'user__profile', 'role')
            .annotate(
                user_pk=F('user__id'),
                full_name=Coalesce(
                    Concat(
                        cap(F('user__profile__first_name')),
                        Value(' '),
                        cap(F('user__profile__last_name')),
                    ),
                    cap(F('user__username')),
                ),
                role_name=F('role__name'),
            )
            .values(
                'user_pk',
                'full_name',
                'role_name'
            )
        )

        if role_names:
            qs = qs.filter(role__name__in=role_names)

        return qs